Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Dynamic query - Access path - for changing where clause

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
cvishu

Active User


Joined: 31 Jul 2007
Posts: 136
Location: india

PostPosted: Wed Jun 13, 2012 12:35 pm    Post subject: Dynamic query - Access path - for changing where clause
Reply with quote

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
View user's profile Send private message

gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Jun 13, 2012 1:04 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 13, 2012 1:21 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 13, 2012 1:41 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Wed Jun 13, 2012 8:00 pm    Post subject:
Reply with quote

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
View user's profile Send private message
cvishu

Active User


Joined: 31 Jul 2007
Posts: 136
Location: india

PostPosted: Fri Jun 15, 2012 12:07 pm    Post subject:
Reply with quote

Thanks every one
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Changing of LRECL of a file abdulrafi DFSORT/ICETOOL 1 Fri Mar 24, 2017 3:25 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us