IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Dynamically write FETCH statement


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Mon Jun 15, 2015 3:36 pm
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

Global Moderator


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

PostPosted: Mon Jun 15, 2015 3:58 pm
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: 164
Location: India

PostPosted: Mon Jun 15, 2015 4:44 pm
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: 31
Location: Bangalore

PostPosted: Mon Jun 15, 2015 5:01 pm
Reply with quote

for Dynamic SQL in Rexx,please refer to

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: 164
Location: India

PostPosted: Mon Jun 15, 2015 5:11 pm
Reply with quote

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

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: 765
Location: Whitby, ON, Canada

PostPosted: Mon Jun 15, 2015 5:27 pm
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: 164
Location: India

PostPosted: Mon Jun 15, 2015 6:06 pm
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: 1353
Location: Israel

PostPosted: Mon Jun 15, 2015 7:33 pm
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: 1353
Location: Israel

PostPosted: Mon Jun 15, 2015 7:35 pm
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: 164
Location: India

PostPosted: Tue Jun 23, 2015 12:29 pm
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Jun 23, 2015 2:00 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Fetch data from programs execute (dat... DB2 3
This topic is locked: you cannot edit posts or make replies. How To Write, Compile and Execute Cob... COBOL Programming 5
No new posts Compare two files with a key and writ... SYNCSORT 3
Search our Forums:

Back to Top