Db2 사용자 정의 함수(UDF) 성능 관련 옵션

Db2 에는 많은 내장(Built-in) 함수들이 있고, 내장 함수는 시스템에 최적화되어 있기 때문에 가장 빠르게 실행됩니다.

하지만, 원하는 함수를 내장 함수에서 찾을 수 없는 경우, 사용자 정의 함수(UDF:User Defined Function)을 만들어서 활용할 수 있습니다. UDF는 SQL 쿼리로 비즈니스 계산을 간편하게 수행할 수 있는 훌륭한 도구입니다. 이러한 UDF 의 성능을 최적화할 수 있는 몇가지 옵션들이 있습니다.

 

FENCED or NOT FENCED 옵션

디폴트 값은 FENCED로, Db2 for i가 아닌 다른 Db2 제품군에서는 UDF 실행 중에 메모리 사용을 제어하는 옵션이 필요하기 때문에 주로 사용되는 옵션입니다. 하지만, Db2 for i의 경우 데이터베이스 엔진이 운영 체제의 일부이기 때문에 적용되지 않습니다. 

 

FENCED 속성은 다른 쓰레드에서 UDF 호출을 수행할 때 UDF 가 더 느리게 수행될 수 있습니다. 이를 최적화하려면 NOT FENCED 속성을 사용해서 Db2 가 SQL문을 호출하는 동일한 쓰레드에서 UDF 호출을 실행하게 할 수 있습니다. 

 

다음 예문은 화씨 온도를 섭씨 온도로 변환하는 UDF getCelsius 함수 예제입니다. 성능 최적화를 위해 NOT FENCED 옵션을 지정하였습니다.

 

CREATE FUNCTION getCelsius( fahrenheit_temp INT )
  RETURNS DECIMAL(4,0)
LANGUAGE SQL
DETERMINISTIC
NOT FENCED
BEGIN
  RETURN( CEILING( (5*(fahrenheit_temp-32) ) / 9 ) );
END;

 

 

DETERMINISTIC or NOT DETERMINISTIC 옵션

DETERMINISTIC 옵션은 성능 향상에 도움이 될 수 있는 두번째 옵션입니다. DETERMINISTIC 은 위의 UDF가 정확히 동일한 입력 매개변수로 호출될 때 항상 동일한 결과값을 리턴한다는 것을 의미합니다. 예를 들어 getCelsius 함수를 보면, 입력 매개변수 값 32로 호출될 때마다 섭씨 온도 값 0을 리턴하기 때문에 DETERMINISTIC 함수입니다. 

 

이제 NOT DETERMINISTIC 함수 예제인 아래의 GetLocalTemp UDF와 비교해 보겠습니다. 이 함수는 입력으로 우편번호 값을 받은 다음 외부 저장 프로시저를 호출할 때 이 값을 전달합니다. AccessLocalTemp 프로시저는 XML 파일을 읽고 지정된 우편번호의 현재 온도를 찾은 다음 해당 온도값을 UDF로 리턴합니다. 이 XML 파일은 RSS 또는 FTP 프로세스를 통해 5분마다 업데이트 됩니다. GetLocalTemp UDF는 연속 호출에서 동일한 우편번호를 전달해도 동일한 출력값을 리턴하지 않기 때문에 NOT DETERMINISTIC 함수입니다. 07231의 우편번호값에 대한 온도값은 첫번째 호출에서 35를 리턴하고 다음 호출에서 36을 리턴할 수 있습니다.

 

CREATE FUNCTION GetLocalTemp(zipcode VARCHAR(5))
  RETURNS INTEGER
NOT DETERMINISTIC
NOT FENCED
LANGUAGE SQL
BEGIN
  DECLARE localTemp INT;
  CALL AccessLocalTemp(zipcode, localTemp);
  RETURN localTemp;
END;

DETERMINISTIC 함수의 주요 성능 효과는 Db2가 UDF 호출에 대한 입력 매개변수 값과 리턴 값을 캐싱할 수 있다는 점입니다. 동일한 입력 매개변수를 사용하여 해당 UDF에 대한 연속적인 호출에서 Db2 는 UDF를 호출하지 않고 캐시된 리턴값을 리턴할 수 있습니다. UDF 호출의 오버헤드를 줄임으로서 성능을 개선할 수 있는 것입니다. 단, Db2가 UDF를 호출할 때 이는 바인딩되지 않은 외부 프로그램 호출을 실행하는 것과 동일하다는 점을 기억하십시오. 따라서, 호출을 최소화하면 성능이 눈에 띄게 향상될 수 있습니다.

 

아래 쿼리는 UDF가 일부 사용자 정의 계산을 수행하는 방법에 대한 매우 일반적인 예입니다. 365일 동안 얼마나 많은 LOWTEMP 값이 동일한 값으로 되어 있는지를 생각해 보십시오. 동일 값에 대한 호출시 Db2는 함수 호출을 건너뛰고 대신 캐시를 사용할 수 있습니다. 

 

SELECT DATARECORDED, getCelsius(LOWTEMP) FROM TEMPERATURE_HISTORY 
  WHERE YEAR(DATERECORDED) = 2021;