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
 

 

Multiple Rows Fetch and display in REXX

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

Active User


Joined: 22 May 2009
Posts: 158
Location: Bangalore

PostPosted: Fri Jan 17, 2014 11:38 am    Post subject: Multiple Rows Fetch and display in REXX
Reply with quote

Hi,
I'm connecting to DB2 using the REXX.

I have declared the cursor to fetch the values from the table.
The query should fetch for more than 1 time. But I could see that the ouput is able to get only 1 row.

Code:

 /*---------CURSOR DECL,OPEN,FETCH AND CLOSE------------------*/
                                                                 
 ADDRESS DSNREXX 'EXECSQL PREPARE S1 FROM :SQLSTMT'             
                                                                 
 ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"             
                                                                 
 ADDRESS DSNREXX "EXECSQL OPEN C1"                               
                                                                 
 ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P1,:P2,:P3,:P4,:P5 "
   


Please can you let me know if there is possibility the same query to Run until the SQLCODE is =100. Once it reaches +100 then it should close the cursor.

Till that time it should go on put the values in P1,P2,P3,P4,P5
Back to top
View user's profile Send private message

Pandora-Box

Moderator


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

PostPosted: Fri Jan 17, 2014 11:45 am    Post subject:
Reply with quote

Please post the complete code
Back to top
View user's profile Send private message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 158
Location: Bangalore

PostPosted: Fri Jan 17, 2014 12:31 pm    Post subject: Reply to: Multiple Rows Fetch and display in REXX
Reply with quote

I got the solution
defined multiple FETCH statements

Code:

ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P1,:P2,:P3,:P4,:P5"     
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P11,:P12,:P13,:P14,:P15"
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P21,:P22,:P23,:P24,:P25"
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P31,:P32,:P33,:P34,:P35"
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P41,:P42,:P43,:P44,:P45"
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P51,:P52,:P53,:P54,:P55"
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P61,:P62,:P63,:P64,:P65"
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P71,:P72,:P73,:P74,:P75"
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P81,:P82,:P83,:P84,:P85"


Now when i try to display using the command ZEDLMSG all the records are presented in 7 lines but my output is to be presented in 12 lines.
Is there a way i expand the ZEDLMSG length and width.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Fri Jan 17, 2014 12:36 pm    Post subject:
Reply with quote

icon_sad.gif

Would you place 1000 fetch if you fetch 1000 rows?

You need to process in loop

Unless and until you say what you are trying for you wont get a better solution
Back to top
View user's profile Send private message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 158
Location: Bangalore

PostPosted: Fri Jan 17, 2014 1:02 pm    Post subject: Reply to: Multiple Rows Fetch and display in REXX
Reply with quote

The code is mentioned below which connects to DB2 for the query and fetches the value.

Code:
 CNCT:                                       
 ADDRESS TSO "SUBCOM DSNREXX"               
 S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX') 
 ADDRESS DSNREXX "CONNECT" "PRD1"           

REP_SQL:                                                             
SQLSTMT =  "SELECT *",                                   
 "FROM TABLEA",                                         
  "GROUP BY COL1",                                       
 "WITH UR"                                                           
 /*---------CURSOR DECL,OPEN,FETCH AND CLOSE------------------*/     
                                                                     
 ADDRESS DSNREXX 'EXECSQL PREPARE S1 FROM :SQLSTMT'                   
                                                                     
 ADDRESS DSNREXX "EXECSQL OPEN C1"                                   
                                                                     
 ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"                   
                                                                     
 ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P1,:P2,:P3,:P4,:P5"
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P11,:P12,:P13,:P14,:P15"
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P21,:P22,:P23,:P24,:P25"
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P31,:P32,:P33,:P34,:P35"
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P41,:P42,:P43,:P44,:P45"
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P51,:P52,:P53,:P54,:P55"
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P61,:P62,:P63,:P64,:P65"
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P71,:P72,:P73,:P74,:P75"
ADDRESS DSNREXX  "EXECSQL FETCH C1 INTO :P81,:P82,:P83,:P84,:P85"


  IF SQLCODE \= "0" THEN                                                 
   DO                                                                   
    ZEDLMSG = "REGDT QRY FAILED WITH SQLCODE = " SQLCODE                 
    ADDRESS ISPEXEC "SETMSG MSG(ISRZ000)"                               
   END                                                                   
                                                                         
 OUTPUT:                                                                 
 ZEDLMSG=' ',                                                           
 '---------+---------+---------+---------------------------------------',
 'AMT      CASH       ACTION        CODE                   COUNT',
 '---------+---------+---------+---------------------------------------',
   P1  '     ' P2  '     '      P3  '     '    P4  '     '         P5 , 
   P11 '     ' P12 '     '      P13 '     '    P14 '     '         P15, 
   P21 '     ' P22 '     '      P23 '     '    P24 '     '         P25, 
   P31 '     ' P32 '     '      P33 '     '    P34 '     '         P35, 
   P41 '     ' P42 '     '      P43 '     '    P44 '     '         P45, 
   P51 '     ' P52 '     '      P53 '     '    P54 '     '         P55, 
   P61 '     ' P62 '     '      P63 '     '    P64 '     '         P65, 
   P71 '     ' P72 '     '      P73 '     '    P74 '     '         P75, 
   P81 '     ' P82 '     '      P83 '     '    P84 '     '         P85   
ADDRESS ISPEXEC "SETMSG MSG(ISRZ000)"         
                                               
ADDRESS DSNREXX "EXECSQL CLOSE C1"             
ADDRESS DSNREXX "DISCONNECT"                   
                                               
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX') 
EXIT 0                                                 


As I know that there are only 9 rows would be fetched i declared the fetch statements 9 times. so wanna know where can I give the recursion statement to perform the query and fetch me the exact number of rows until it finds a SQLCODE +100.

Second Issue:

The Message displayed in the ZEDLMSG is showing me only in the 7 line width but I need the exact n rows to be displayed on the screen.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1713
Location: UK

PostPosted: Fri Jan 17, 2014 2:40 pm    Post subject: Reply to: Multiple Rows Fetch and display in REXX
Reply with quote

Pandora-Box - "no messages or topics meet criteria" when I click on your link.

Tecnokrat - why are you wanting recursion? What you want is a loop.
Code:
Address DSNREXX
Do i = 1 to 9
   EXECSQL FETCH...
   assign data to variables so that you can re-use P!...
End


ZEDLMSG is for informational messages e.g. long error messages. It is not for presenting output. You can format some SAY statements to produce the output.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Fri Jan 17, 2014 2:56 pm    Post subject:
Reply with quote

Opps I posted the link that gives DSNREXX looks that doesnt help
check here
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Mon Jan 20, 2014 8:26 am    Post subject:
Reply with quote

Changed Mr. Nic's loop validation. Since the loop can't always 10 times.

Code:

do until sqlcode ¬= 0
   "EXECSQL FETCH C1 INTO :P1,:P2,:P3,:P4,:P5"
   if sqlcode = 0 then
       move P1-P5 to array_variable
   end
end
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1713
Location: UK

PostPosted: Mon Jan 20, 2014 7:08 pm    Post subject: Reply to: Multiple Rows Fetch and display in REXX
Reply with quote

I made it 9 because of an earlier statement saying that 9 was the number of rows to fetch. OP had coded 9 fetches and stated that this was the requirement.
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Tue Jan 21, 2014 7:44 am    Post subject:
Reply with quote

Of course Mr. Nic, I just gave an alternative suggestion to process as long as data exists.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1713
Location: UK

PostPosted: Tue Jan 21, 2014 2:22 pm    Post subject: Reply to: Multiple Rows Fetch and display in REXX
Reply with quote

My own preferred way of your code, suershpathi10, is this:
Code:
"EXECSQL FETCH C1 INTO :P1,:P2,:P3,:P4,:P5"
Do While sqlcode = 0
   move P1-P5 to array_variable
   "EXECSQL FETCH C1 INTO :P1,:P2,:P3,:P4,:P5"
End

It removes the need for an IF condition to be tested for every row returned and avoids a negative condition controlling the loop. Again, just my preference.
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 What is the command to check MODE of ... rohanthengal CLIST & REXX 6 Fri Nov 18, 2016 1:48 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
This topic is locked: you cannot edit posts or make replies. Rexx to create GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Replace repeated JCL with looping REXX prino CLIST & REXX 3 Thu Oct 13, 2016 5:30 am


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