ORACLE MIGRATION - CASE Statements.

[db2sysc]

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?

[Serge Rielau]

db*****@yahoo.c om 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