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
 

 

Static SQL and the Dynamic Sql query.

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

New User


Joined: 30 Jun 2007
Posts: 39
Location: India

PostPosted: Tue Oct 20, 2009 12:19 am    Post subject: Static SQL and the Dynamic Sql query.
Reply with quote

Hi,

I am not very clear with Static and Dynamic SQL. Can someone please explain me the difference between the two and in what scenarios which is better.
Back to top
View user's profile Send private message

Steve Davies

New User


Joined: 15 Oct 2009
Posts: 32
Location: UK

PostPosted: Tue Oct 20, 2009 10:29 am    Post subject:
Reply with quote

Have you tried googling this - there's quite a lot of sites discussing this topic. IBM have some info on this too.

Click Here For IBM's pages.
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Tue Oct 20, 2009 12:13 pm    Post subject:
Reply with quote

Hi arindambanerjee,

Dynamic SQL's are prepared during run time. The whole query will be embedded in working storage sections and will be prepared during run time.Please find the sample decaration as below for dynamic SQL

05 WS-Variable1 PIC X(100) VALUES
'Select * from Table1 Order by'
05 WS-Sort1 PIX S9(04) Usage Comp
05 WS-Variable2 PIC X(2) VALUES
','
05 WS-Sort2 PIX S9(04) Usage Comp

Same when we do using static SQL, we will be embedding the query between

EXEC SQL Select * from Table1 Order by 1,2 END-EXEC

As an example if you see the difference here, in the dynamic SQL, after order by I have kept two variables WS-Sort1 and WS-Sort2, values can be passed to this section ysung MOVE command.
In some case I need to sort the result using 3rd and 4rth column, so I will pass 3 to WS-Sort1 and 4 to WS-Sort2 through COBOL program.In case I want to sort by first and second column, then I MOVE 1 to WS-Sort1 and 2 to WS-Sort2.
But in case of static SQL, I will always be able to order by 1,2.In case we need to change the order, we need to change it in the query.
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Tue Oct 20, 2009 12:33 pm    Post subject: Reply to: Static SQL and the Dynamic Sql query.
Reply with quote

Hi arinthambanerjee,

Please refer the below link too, you can get some more details on Static and Dynamic SQL.

http://www.ibmmainframes.com/viewtopic.php?t=4802
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 Dynamic split of files under groups sril.krishy DFSORT/ICETOOL 4 Mon Apr 17, 2017 1:09 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