By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

ORACLE MIGRATION - CASE Statements.

P: n/a
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?

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
db*****@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
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.