I have two versions of Select SQL - One is dynamic and the other is static.

When I run the static SQL by using C program, it run very slow, use 11 seconds to run the statement, but for dynamic one, it use only 1 second. The table contains about 980,000 records.

I have done reorg, runstats on the corresponding table and indexes, and also rebind on the static SQL. Then I run explain on both static and dynamic SQL and found that the plan are the same. (using the same index for data retrieving)

Then I apply logging in the C program and found that almost all the time is spent on open cursor, fetch only consume very little of time.

Does anyone have idea why open cursor consume a lots of time, and how to minimize the time on opening cursor, thx in advance.

You say that you have two versions of the SQL statement. Please post them exactly, with host variables or literals and they occur in the SQL statement.

Sometimes DB2 will need to materialize the answer set in the temporary tablespace (into a table which is hidden to you) before the first row is fetched into your program. This can happen with an ORDER BY or other clause where DB2 needs to read all of the rows before the first one is returned. This "might be" the explanation, but it is difficult to understand why the static statement would do this, and not the dynamic version, without seeing the two SQL statements.

Also, in accordance with posting rules, please provide DB2 version (and fixpak level) and OS.

Marcus_A,
Thx for your help.
We are using DB2 v8.1/AIX with FP2.

Pls. find below the Static and Dynamic SQL:
Static SQL
------------
SELECT MCID, MID, ICPRF1, ICPRF2, ICNUM, CHAR(DTCLEAR, ISO),
TMMRCLEAR, CHAR(DTTRAN, ISO), LOCID, TERMNO, UIDTX,
TRANID, TRANSPID , POEDEST, ICMRST, CNTRTY, TARHITIND,
SYNBATCHNO, CHAR(TMTRAN, ISO), PGRCD, CNLREA, CNLRMK,
TMSTMPLSTUPD, SAERRIND
FROM WICMRD
WHERE (TMSTMPLSTUPD > :H00027 ) OR (TMSTMPLSTUPD = :H00027 AND
MCID > :H00028 ) OR (TMSTMPLSTUPD = :H00027 AND MCID =
:H00028 AND MID > :H00029 )
ORDER BY TMSTMPLSTUPD, MCID, MID
OPTIMIZE
FOR 1 ROWS

Explain Plan for Static SQL
-------------------------------
Estimated Cost = 162144.656250
Estimated Cardinality = 100254.265625

Access Table Name = DB2ICA.LICMRD ID = 7,4
| #Columns = 24
| Index Scan: Name = DB2ICA.XICMRD2 ID = 4
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: TMSTMPLSTUPD (Ascending)
| | | 2: MCID (Ascending)
| | | 3: MID (Ascending)
| | #Key Columns = 0
| | | Start Key: Beginning of Index
| | | Stop Key: End of Index
| | Data Prefetch: None
| | Index Prefetch: None
| | Sargable Index Predicate(s)
| | | #Predicates = 6
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
Return Data to Application
| #Columns = 24

End of section


Dynamic SQL
----------------
SELECT MCID, MID, ICPRF1, ICPRF2, ICNUM, CHAR(DTCLEAR, ISO),
TMMRCLEAR, CHAR(DTTRAN, ISO), LOCID, TERMNO, UIDTX,
TRANID, TRANSPID , POEDEST, ICMRST, CNTRTY, TARHITIND,
SYNBATCHNO, CHAR(TMTRAN, ISO), PGRCD, CNLREA, CNLRMK,
TMSTMPLSTUPD, SAERRIND
FROM db2ica.WICMRD
WHERE (TMSTMPLSTUPD > '2003-01-01-00.00.00.000000' ) OR (TMSTMPLSTUPD = '2003-01-01-00.00.00.000000' AND
MCID > 'G1' ) OR (TMSTMPLSTUPD = '2003-01-01-00.00.00.000000' AND MCID =
'G1' AND MID > 10000 )
ORDER BY TMSTMPLSTUPD, MCID, MID
OPTIMIZE
FOR 1 ROWS;

Explain Plan for Dynamic SQL
----------------------------------
Estimated Cost = 366530.593750
Estimated Cardinality = 980307.000000

Access Table Name = DB2ICA.LICMRD ID = 7,4
| #Columns = 24
| Index Scan: Name = DB2ICA.XICMRD2 ID = 4
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: TMSTMPLSTUPD (Ascending)
| | | 2: MCID (Ascending)
| | | 3: MID (Ascending)
| | #Key Columns = 0
| | | Start Key: Beginning of Index
| | | Stop Key: End of Index
| | Data Prefetch: None
| | Index Prefetch: None
| | Sargable Index Predicate(s)
| | | #Predicates = 6
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
Return Data to Application
| #Columns = 24

End of section


The table structure and index:
Table
------
CREATE TABLE "DB2ICA "."LICMRD" (
"MCID" CHAR(2) NOT NULL ,
"MID" INTEGER NOT NULL ,
"ICPRF1" CHAR(1) NOT NULL ,
"ICPRF2" CHAR(1) NOT NULL ,
"ICNUM" INTEGER NOT NULL ,
"DTCLEAR" DATE NOT NULL ,
"DTTRAN" DATE NOT NULL ,
"LOCID" CHAR(3) NOT NULL ,
"TERMNO" SMALLINT NOT NULL ,
"TRANSPID" CHAR(7) NOT NULL ,
"POEDEST" CHAR(1) NOT NULL ,
"ICMRST" CHAR(1) NOT NULL ,
"CNTRTY" CHAR(1) NOT NULL ,
"TARHITIND" CHAR(1) NOT NULL ,
"SYNBATCHNO" INTEGER NOT NULL ,
"TMTRAN" TIME NOT NULL WITH DEFAULT CURRENT TIME ,
"SAERRIND" CHAR(1) NOT NULL WITH DEFAULT '0' ,
"TMMRCLEAR" INTEGER NOT NULL ,
"UIDTX" CHAR(8) NOT NULL ,
"TRANID" CHAR(4) NOT NULL ,
"PGRCD" CHAR(1) NOT NULL ,
"CNLREA" CHAR(2) NOT NULL ,
"CNLRMK" CHAR(150) NOT NULL ,
"TMSTMPLSTUPD" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP )
IN "TPMRC_TBL" INDEX IN "TPMRC_IDX" ;

Index
-------
CREATE UNIQUE INDEX "DB2ICA "."XICMRD" ON "DB2ICA "."LICMRD"
("MCID" ASC,
"MID" ASC);

CREATE INDEX "DB2ICA "."XICMRD1" ON "DB2ICA "."LICMRD"
("TRANID" ASC,
"ICNUM" ASC,
"ICPRF2" ASC);

CREATE UNIQUE INDEX "DB2ICA "."XICMRD2" ON "DB2ICA "."LICMRD"
("TMSTMPLSTUPD" ASC,
"MCID" ASC,
"MID" ASC);

CREATE INDEX "DB2ICA "."XLICMRD2" ON "DB2ICA "."LICMRD"
("TRANID" ASC,
"LOCID" ASC,
"ICPRF1" ASC,
"ICPRF2" ASC,
"ICNUM" ASC,
"TMSTMPLSTUPD" ASC);



View
------
CREATE VIEW DB2ICA.WICMRD AS SELECT MCID, MID, ICPRF1 , ICPRF2, ICNUM, DTCLEAR
, DTTRAN, LOCID, TERMNO , TRANSPID, POEDEST, ICMRST , CNTRTY, TARHITIND,
SYNBATCHNO , TMTRAN, SAERRIND, TMMRCLEAR , UIDTX, TRANID, PGRCD , CNLREA,
CNLRMK, TMSTMPLSTUPD FROM DB2ICA.LICMRD;


From the explain, we found that the cost of Dynamic SQL is much more than Static one, but actually the running time of Dynamic SQL is much less than Static one.....

Matthewlau,

This is a little perplexing and I don’t know what the problem is. But have listed some things to look at. I realize that you may have already looked at these.

1. Since everything looks the same in terms of the access path, I wonder if there is some mismatch of DB2 column types and host-variable definitions. I am not proficient in C so I am not sure how this needs to be handled. I "think" that C variables are varchar and DB2 timestamps are fixed length, and don't know if there is something relating to that whch could cause the problem. I am sure that others can comment on that.

2. I would look at the Bind parameters. Specify QUERYOPT 7. You should probably always use this for static binds since the amount of time it takes to optimize a during the bind is not an issue (except if you use level 9). There may be other bind parameters that need to be looked at.

3. Make sure you do runstats with full statistics.

RUNSTATS ON TABLE table-name WITH DISTRIBUTION
AND DETAILED INDEXES ALL

4. Try getting rid of OPTIMIZE FOR n ROWS. It seems to me that the index should have prefetch enabled and I am not sure why it is not according to the explain. The entire index is read from start to finish (not using the b-tree) because of the predicates used in the where clause.

5. Is anyone else on the system when running the tests? What size are the buffer pools? How many times did you run the tests?

Sorry for not having the answer, and I realize that most of the suggestions above have probably already been considered by you.

Marcus_A,
Thx for your info first, you really help a lots.

For 1, I am also not sure does the type of host variable affect the performance by doing data conversion, I will try to change other data type and test again.

For 2, I have tried it before, using 1, 3, 5, 7, 9 optimization level also got the same result.

For 3, I have done it before.

For 4, if I don't use optimize for n rows, the plan will not use index scan, instead of that, it will use table scan, which degrade the performance, and the actual running time is also longer when using table scan. I don't know why it will use table scan if I don't use optimize for n rows, may be it is optimizer bug.

For 5, the buffer pool size is 100M.

The question is: For the same SQL statement, Why dynamic one is faster than static one by 11 times?

Host variable definitions can make a big difference in certain situations. Don't really know if it makes a difference in your situation.

One thing I would try is to take the "dynamic" SQL statement that you have and put it in a program that is statically bound with the WHERE clause predicates hard-coded instead of using host variables. That would help determine if there is something wrong with static binds vs. dynamic, or if it is likely the host variables that are the problem.

But I don’t understand how they can have the same access path in the explain and then perform so differently. Actually I am not familiar with the explain output you presented and I normally use the Visual Explain (except on the mainframe).

Thx for your help, just change the C program to run the SQL in dynamic mode instead of static mode, work fine, it takes less than 1 second to complete, if using static, use 11 seconds. However, at this moment, still don't know why static much slower than dynamic?