|
View previous topic :: View next topic
|
| Author |
Message |
cvishu
Active User
Joined: 31 Jul 2007
Posts: 136
Location: india
|
|
|
|
Hi ,
I am preapring a dynamic qury with changing where clause .
Assume the sample below
| Code: |
SELECT PROJNO, PROJNAME, RESPEMP
FROM DSN8810.PROJ
WHERE PROJNO = zzzz AND PRSTDATE = uuuu |
The above query will be in a string , from which i PREPARE my qeryu adn do the fetch
I have a second option as below
| Code: |
SELECT PROJNO, PROJNAME, RESPEMP
FROM DSN8810.PROJ
WHERE PROJNO = ?
AND PRSTDATE = ? |
Move the "SQL to execute" to STRING-VARIABLE
| Code: |
EXEC SQL DECLARE CSR2 CURSOR FOR FLSQL;
EXEC SQL PREPARE FLSQL FROM :STRING-VARIABLE;
EXEC SQL OPEN CSR2 USING :TVAL1, :TVAL2; |
Now the question i have is , will there be any difference in the way or instance at which access path is determined ?
Will it be determined during PREPARE in both the cases or , PREPARE in first case and OPEN in second case.
Please let me know if the question is not clear i will try to better my wordings. |
|
| Back to top |
|
 |
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
I don't think there will be any difference... as access path will be determined when we open the cursor.
Experts... please correct me if i am wrong. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
it is so easy when one refers to the manual,
in this case REDBOOK- Dynamic SQL
Access path is never determined at OPEN.
I suggest that you both read the above mentioned manual. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
cvishu,
you should pay particular attention to the discussion of tokens vs literals in your WHERE clause,
because they do affect the access path chosen. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
There are several big differences between the two options.
- When using literals the access path can change depending on the statistics of the columns (=sometimes better performance)
-before DB2 v10 : Each different dynamic statement needs preparing and PREPARE is an expensive (cpu/elapsed) statement. When using parameter-markers the statement does not change so even if you do prepare, DB2 will find the same statement in the "dynamic statement cache" and reuse (=a lot faster) the previous accesspath.
So in general it is always better to use parameter markers. Some big players on the software market refuse to do so,since v10 DB2 is supposed to be smart enough to treath look-a-like (only literals are different) SQL-statements the same way as identical SQL-statements.
It is advised to use parameter-markers whenever possible.
This is the short answer, It would be wise to follow dbzTHEdinosauer advice and start reading. |
|
| Back to top |
|
|
cvishu
Active User
Joined: 31 Jul 2007
Posts: 136
Location: india
|
|
|
|
| Thanks every one |
|
| Back to top |
|
|
|
|
