MVSFORUMS.com

Catherine · Posted: Fri Dec 07, 2007 8:32 am Post subject: EXECUTE IMMEDIATE

Hi all,

Please let me know how and when a EXECUTE IMMEDIATE statement is used. Will it help to improve my performance of the Query?
I understand that EXECUTE IMMEDIATE can be used starting from DB2 version 8.
I want to improve the performance of my insert statement as im going to add more columns to my index.
Performance of the insert operation decreases with the no: of columns added in the index as well as the no: of indexes

vivek1983 · Intermediate Joined: 20 Apr 2006 Posts: 222 Topics: 24

Catherine,

Following is a link that gives you an insight of EXECUTE IMMEDIATE

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPJ12/6.1.5.1?DT=20050328013405

EXECUTE IMMEDIATE pertains to dynamic SQL. Following link shows the factors determining the performance.

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPJ12/6.1.1.5?DT=20050328013405
_________________
Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay)

jsharon1248 · Posted: Fri Dec 07, 2007 9:08 am Post subject:

Depends. Are you using an INSERT with an embedded SELECT? If that's the case and you're adding indexes on the SELECT table, the DB2 optimizer will evaluate current stats and indexes in the PREPARE. Then, if DB2 decides to utilize the new indexes, you would most likely get better performance compared to static SQL bound before the new indexes were created. Adding indexes on the target table would have the same impact on performance no matter how you issue the INSERT.