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
 

 

Dynamically write FETCH statement

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

Active User


Joined: 05 Feb 2014
Posts: 159
Location: India

PostPosted: Mon Jun 15, 2015 3:36 pm    Post subject: Dynamically write FETCH statement
Reply with quote

Hi,

I want to write the FETCH statement dynamically in REXX that which is connected to DB2.

Like first time i might fetch records from the table which has 5 columns and next time when i use this tool, i might be in need to fetch another table which has 10 columns. So in that case FETCH has to take the host variable names dynamically.

So, What i thought first was to use the macro separately in a different member that which uses the DCLGEN of the table and insert the columns to the main rexx code which is taken from the DCLGEN .

But the problem with this which i found was after FETCH statement line in the main REXX code , I would call this macro but the columns will be inserted only after the comand which calls the macro.

can some one please suggest me any idea....I hope i have written clearly with what is my doubt....


Thanks
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Jun 15, 2015 3:58 pm    Post subject:
Reply with quote

Do you need Dynamic Fetch or Dynamic SQL?
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 159
Location: India

PostPosted: Mon Jun 15, 2015 4:44 pm    Post subject:
Reply with quote

Pandora-Box wrote:
Do you need Dynamic Fetch or Dynamic SQL?



columns in the Table will vary so while FETCHING the host variable will also vary....I need to know how can I write REXX-DB2 code for FETCH cursor INTO in that case....
Back to top
View user's profile Send private message
Smita.t2

New User


Joined: 17 Apr 2012
Posts: 22
Location: Bangalore

PostPosted: Mon Jun 15, 2015 5:01 pm    Post subject: Reply to: Dynamically write FETCH statement
Reply with quote

for Dynamic SQL in Rexx,please refer to

http://www-01.ibm.com/support/knowledgecenter/#!/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_samplerexxapp.dita
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 159
Location: India

PostPosted: Mon Jun 15, 2015 5:11 pm    Post subject: Re: Reply to: Dynamically write FETCH statement
Reply with quote

Smita.t2 wrote:
for Dynamic SQL in Rexx,please refer to

http://www-01.ibm.com/support/knowledgecenter/#!/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_samplerexxapp.dita


But Here, when i try with DRAW command I get command not found. Also I want the query in REXX code where i am connecting to DB2 and fetching records from table.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Mon Jun 15, 2015 5:27 pm    Post subject:
Reply with quote

Did you look at the code? DRAW connects to DB2 and DESCRIBES a table into the SQLDA. These techniques are what you need for when you want to write a general purpose query program.

I have used DRAW as a model for the many dynamic SQL Rexx applications that I have created.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 159
Location: India

PostPosted: Mon Jun 15, 2015 6:06 pm    Post subject:
Reply with quote

don.leahy wrote:
Did you look at the code? DRAW connects to DB2 and DESCRIBES a table into the SQLDA. These techniques are what you need for when you want to write a general purpose query program.

I have used DRAW as a model for the many dynamic SQL Rexx applications that I have created.


Can you please provide basic syntax of how to write dynamic fetch statement in in REXX using DRAW....I saw the code but it looks difficult for me to understand...If i start with basic may i will be able....
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Mon Jun 15, 2015 7:33 pm    Post subject:
Reply with quote

As you need to fetch rows from different tables and these tables have different columns, you have to be able to built a valid WHERE statement.

This is one (small) problem by itself but I will put it aside for now.
Let's suppose that you want to fetch the whole contents of the table.

First, build the SQL statement:
Code:
SQLStmt = "SELECT * FROM "tablename

After CONNECTing to DB2, declare and prepare the cursor:
Code:
"EXECSQL DECLARE C1 CURSOR FOR S1"                     
If SQLCode ¬= 0 Then Call DisplaySQLError('DECLARE C1')
"EXECSQL PREPARE S1 INTO :SQLDA_C1 FROM :SQLStmt"     
If SQLCode ¬= 0 Then Call DisplaySQLError('PREPARE S1')

At this stage, you can already know which columns to expect:
Code:
do I = 1 to SQLDA_C1.SQLD                 
   Say 'Column 'I' is 'SQLDA_C1.I.SqlName
end

This may be useful, for example, if you want to create an ISPF table to store the table values.
Now, you can start to fetch your data. It can be done with a very simple loop:
Code:
"EXECSQL OPEN C1"                                             
If SQLCode ¬= 0 Then Call DisplaySQLError('OPEN C1')         
Do Forever                                                   
   "EXECSQL FETCH C1 USING DESCRIPTOR :SQLDA_C1"             
   Select                                                     
      When SqlCode = 0   Then Call Store_Row                 
      When SqlCode = 100 Then Leave                           
      Otherwise               Call DisplaySQLError('FETCH C1')
   End                                                       
End                                                           
"EXECSQL CLOSE C1"                                           
If SQLCode ¬= 0 Then Call DisplaySQLError('CLOSE C1')         
"EXECSQL COMMIT"                                             
If SQLCode ¬= 0 Then Call DisplaySQLError('COMMIT')           
The Store_Row procedure look like this:
Code:
Do I = 1 To SQLDA_C1.SQLD               
   ColName = SQLDA_C1.I.SqlName         
   ColValue = SQLDA_C1.I.SqlData
   say 'Column 'ColName' contains <'ColValue'>'
End

The '<' and '>' will only help you to see if there are spaces within ColValue.
If you want to set a variable named after the column (useful if you have this ISPF table I mentioned before), you can code:
Code:
Interpret ColName' = "'ColValue'"'
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Mon Jun 15, 2015 7:35 pm    Post subject:
Reply with quote

If you want info about how to build the WHERE clause, just ask here...
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 159
Location: India

PostPosted: Tue Jun 23, 2015 12:29 pm    Post subject:
Reply with quote

Marso wrote:
As you need to fetch rows from different tables and these tables have different columns, you have to be able to built a valid WHERE statement.

This is one (small) problem by itself but I will put it aside for now.
Let's suppose that you want to fetch the whole contents of the table.

First, build the SQL statement:
Code:
SQLStmt = "SELECT * FROM "tablename

After CONNECTing to DB2, declare and prepare the cursor:
Code:
"EXECSQL DECLARE C1 CURSOR FOR S1"                     
If SQLCode ¬= 0 Then Call DisplaySQLError('DECLARE C1')
"EXECSQL PREPARE S1 INTO :SQLDA_C1 FROM :SQLStmt"     
If SQLCode ¬= 0 Then Call DisplaySQLError('PREPARE S1')

At this stage, you can already know which columns to expect:
Code:
do I = 1 to SQLDA_C1.SQLD                 
   Say 'Column 'I' is 'SQLDA_C1.I.SqlName
end

This may be useful, for example, if you want to create an ISPF table to store the table values.
Now, you can start to fetch your data. It can be done with a very simple loop:
Code:
"EXECSQL OPEN C1"                                             
If SQLCode ¬= 0 Then Call DisplaySQLError('OPEN C1')         
Do Forever                                                   
   "EXECSQL FETCH C1 USING DESCRIPTOR :SQLDA_C1"             
   Select                                                     
      When SqlCode = 0   Then Call Store_Row                 
      When SqlCode = 100 Then Leave                           
      Otherwise               Call DisplaySQLError('FETCH C1')
   End                                                       
End                                                           
"EXECSQL CLOSE C1"                                           
If SQLCode ¬= 0 Then Call DisplaySQLError('CLOSE C1')         
"EXECSQL COMMIT"                                             
If SQLCode ¬= 0 Then Call DisplaySQLError('COMMIT')           
The Store_Row procedure look like this:
Code:
Do I = 1 To SQLDA_C1.SQLD               
   ColName = SQLDA_C1.I.SqlName         
   ColValue = SQLDA_C1.I.SqlData
   say 'Column 'ColName' contains <'ColValue'>'
End

The '<' and '>' will only help you to see if there are spaces within ColValue.
If you want to set a variable named after the column (useful if you have this ISPF table I mentioned before), you can code:
Code:
Interpret ColName' = "'ColValue'"'



Now I able to fetch records from the table that i specify, but there are some columns values where it is packed decimal format so after I fetch the records and write it in a PS file i get . (dot) displayed at the end of the value. is there any way i remove that and how can we unload the correct column value if it is the case of Packed decimal....
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Tue Jun 23, 2015 2:00 pm    Post subject: Reply to: Dynamically write FETCH statement
Reply with quote

The "dot" is just showing you that there is a non-displayable character. You can't get rid of it, as it is part of your (packed-decimal) data.

I think with the assistance you've been given already, you should at least be able to find out how to turn a packed-decimal into something human-readable.
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 -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts Error IEC161I 052(009,XXXRS00)-084 wh... amitc23 JCL & VSAM 10 Wed Oct 12, 2016 5:00 pm
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am
No new posts how to DYNAMICALLY write sort cond to... mohamedmubee COBOL Programming 13 Fri Aug 26, 2016 11:46 pm
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am


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