All.

We have LOT of variables declared in the Oracle package as ORACLE
CONSTANTS like,

v_test CONSTANT INTEGER=1;


When converting to DB2, MTK changes each of these CONSTANTs into
functions. This typically increased the count of the functions.

create function v_test()
return int
....
....
return 1


Instead of SO MANY functions in DB2, we have written one single
function with CASE statements for each constant value

select CASE WHEN 'TESTING' THEN 1
WHEN 'CODING' THEN 2

.....
.....
END

Is there any performance implications in doing the above? having
multiple CASE statements in a single function? or shld we go for DGTT?

db2sysc@yahoo.com wrote:
> All.
>
> We have LOT of variables declared in the Oracle package as ORACLE
> CONSTANTS like,
>
> v_test CONSTANT INTEGER=1;
>
>
> When converting to DB2, MTK changes each of these CONSTANTs into
> functions. This typically increased the count of the functions.
>
> create function v_test()
> return int
> ...
> ...
> return 1
>
>
> Instead of SO MANY functions in DB2, we have written one single
> function with CASE statements for each constant value
>
> select CASE WHEN 'TESTING' THEN 1
> WHEN 'CODING' THEN 2
>
> ....
> ....
> END
>
> Is there any performance implications in doing the above? having
> multiple CASE statements in a single function? or shld we go for DGTT?
>
These MTK dudes are quite inventive....
I don't see a problem with the functions.
Especially since this is static SQL the function will have the same
encapuslating effectand performance characteristics as the package
CONSTANT in Oracle.
If you went with a runtime solution (like using a CASE expression or
doing a select from a table) you will hurt performance.
You will need to do enough tuning after the migration as is...

Cheers
Serge

PS: Did I point you to my artcicles? If not search for my last name on
www.ibm.com. You will find references to a profiler and an article on
tracing and tuning. Get familiar with both.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab