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
 

 

Going in Infinite loop while Running DB2 with REXX!!!!!!

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

New User


Joined: 13 Jul 2011
Posts: 9
Location: India

PostPosted: Sat Jul 16, 2011 2:35 pm    Post subject: Going in Infinite loop while Running DB2 with REXX!!!!!!
Reply with quote

I am running below REXX+ DB2 program. It is running fine but it is going in INFINITE LOOP. Please have a loop at it and Help me :

Below is the REXX program:
Code:

/*- REXX ------------------------------------------------------------*/
       
Signal On Syntax Name Oops                /* Syntax error handler    */         
                                                                               
/*---------------------------*/                                                 
/* 1 - Set DB2 sub-system ID */                                                 
/*---------------------------*/                                                 
ssid = 'xxxx'                                                               
                                                                               
/*-----------------------*/
/* 2 - Set SQL statement */
/*-----------------------*/
sqlstmt = , 
     "SELECT",
     "col_1,col_2,col_3,col_4",
     "FROM hlq.lev2_table_name"
 
/*-----------------------*/               /* Is DSNREXX available?   */
/* 3 - Establish DSNREXX */               /* DSNREXX is the API      */
/*-----------------------*/                                           
'SUBCOM DSNREXX'
If rc,                                    /* rc from SUBCOM is 0 or 1*/
Then Do                                   /* 0 - is there, 1 is not  */
                                                                               
   s_rc = RXSUBCOM('ADD','DSNREXX','DSNREXX')
   If s_rc <> 0,
   Then Call DB2error 1 s_rc
                                                                               
   remove_dsnrexx = 1
                                                                               
End
Else remove_dsnrexx = 0

/*-----------------------------------------------------*/
/* 4 - Make DSNREXX default external command processor */
/*-----------------------------------------------------*/
Address DSNREXX  /* All external commands go to DSNREXX unless spec. */
                                                                               
/*-------------------------------*/
/* 5 - Connect to DB2 sub-system */
/*-------------------------------*/
'CONNECT 'ssid
If rc <> 0,
Then Call DB2error 2 rc ssid
                                                                   
/*--------------------------------------*/                         
/* 6 - Declare cursor - always required */                         
/*--------------------------------------*/                       
'EXECSQL DECLARE C1 CURSOR FOR S1'    /* C1 for S1; C2 for S2 etc -  */
If rc <> 0,                           /* See docco for specials      */
Then Call DB2error 3 sqlcode sqlerrmc
                                                                               
/*-----------------------------*/
/* 7 - Prepare statement       */
/*-----------------------------*/
'EXECSQL PREPARE S1 INTO :outsqlda FROM :sqlstmt'
If rc <> 0,
Then Call DB2error 4 sqlcode sqlerrmc
                                                                               
/*-----------------------------*/
/* 8 - Open cursor             */
/*-----------------------------*/
'EXECSQL OPEN C1'
If sqlcode <> 0,
Then Call DB2error 5 sqlcode sqlerrmc
                                                                               
/*-----------------------------*/
/* 9 - Fetch cursor            */
/*-----------------------------*/
'EXECSQL FETCH C1 INTO :col_1, :col_2, :col_3, :col_4'
                                                                     
End
                                                                   
If sqlcode <> 100,                                                   
Then Call DB2error 6 sqlcode sqlerrmc                                 
                                                                     
/*---------------------------*/                                       
/* 10 - Close cursor         */                                       
/*---------------------------*/                                       
'EXECSQL CLOSE C1'                                                   
If rc <> 0,                                                           
Then Call DB2error 7 sqlcode sqlerrmc                                 
                                          /* Cleanup and exit        */
door:                                                                 
/*---------------------------*/                                       
/* 11 - Disconnect from DB2  */                                       
/*---------------------------*/                                       
'DISCONNECT'                                                           
                                                                       
door1:                                                                 
/*--------------------------------------------------*/                 
/* 12 - If we added DSNREXX then we must remove it. */                 
/*--------------------------------------------------*/                 
If remove_dsnrexx ,                                                     
Then s_rc = RXSUBCOM('DELETE','DSNREXX','DSNREXX')                     
                                                                       
door2:                                    /* No cleanup required/done*/
Exit                                                                   
/*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
/*-*-*-*-*-*-*-*-*-*-*-* Sub-routines follow here -*-*-*-*-*-*-*-*-*-*/
/*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
Oops:                                                                   
/*-------------------------------------------------------------------*/
/* Rexx Error routine. Called on Syntax error. Issue error details & */
/* terminate the script with the return code.                        */
/*-------------------------------------------------------------------*/
_errorrc = rc                                                         
Parse source . . _exec .                                               
Say 'An error has been encountered in '||_exec                         
Say 'Return code is: ' _errorrc                                       
Say 'The line of code in question is:' Sourceline(Sigl)'.'             
Say Errortext(_errorrc)                                               
Say ' '                                                               
Exit rc                                                               
Return /* We never get here! */                                       
/*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
DB2Error: Procedure                                                   
/*-------------------------------------------------------------------*/
/* Error routine called when an error is obtained from SQL           */
/*-------------------------------------------------------------------*/
Parse arg _errpos _errorrc _extra                                     
/*-------------------------------------------------------------------*/
/* The short (smsg) and long (lmsg) error messages to be displayed   */
/* using the ISPF SETTMSG facility                                   */
/*-------------------------------------------------------------------*/
smsg.0 = 7                                                             
smsg.1 = 'RXSUBCOM rc = '_errorrc                                     
smsg.2 = 'CONNECT rc = '_errorrc                                       
smsg.3 = 'DECLARE code = '_errorrc                                     
smsg.4 = 'PREPARE code = '_errorrc                                     
smsg.5 = 'OPEN code = '_errorrc                                       
smsg.6 = 'FETCH code = '_errorrc                                       
smsg.7 = 'CLOSE code = '_errorrc                                       
lmsg.0 = 7                                                             
lmsg.1 = 'RXSUBCOM failed to add the DSNREXX environment'             
lmsg.2 = 'Failed to connect to '_extra                                 
lmsg.3 = 'Failed to declare cursor C1. SQLERRMC = '_extra             
lmsg.4 = 'SQLERRMC from PREPARE = '_extra                             
lmsg.5 = 'SQLERRMC from OPEN = '_extra                                 
lmsg.6 = 'SQLERRMC from FETCH = '_extra                               
lmsg.7 = 'SQLERRMC from CLOSE = '_extra                               
                                                                       
/*---------------------------------*/                                 
/* Assign the appropriate messages */                                 
/*---------------------------------*/                                 
zedsmsg = smsg._errpos                                                 
zedlmsg = lmsg._errpos                                                 
Address ISPEXEC 'SETMSG MSG(ISRZ001)'                                 
                                                                       
/*------------------------------------------------------*/             
/* Exit the script at the appropriate point for cleanup */             
/*------------------------------------------------------*/             
If errpos = 1                                                         
Then Signal door2         /* no housekeeping required */               
Else If errpos = 2        /* remove DSNREXX */                         
     Then Signal door1                                                 
     Else Signal door     /* Disconnect and remove DSNREXX */         
                                                                       
Return


=================================

Please help me
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Sat Jul 16, 2011 2:42 pm    Post subject: Re: Going in Infinite loop while Running DB2 with REXX!!!!!!
Reply with quote

nmodi85 wrote:
I am running below REXX+ DB2 program. It is running fine but it is going in INFINITE LOOP. Please have a loop at it and Help me :

[...]


Please try to use the Code tags (button above the input box) for you program.

If it is "running fine" are you sure the loop matters? I'll try to loop at it later :-)
Back to top
View user's profile Send private message
nmodi85

New User


Joined: 13 Jul 2011
Posts: 9
Location: India

PostPosted: Sat Jul 16, 2011 2:46 pm    Post subject:
Reply with quote

Yes loop do matters icon_smile.gif

It should not go in infinite loop because it is fetching only first that too going in infinite loop. Please help me !!!![/code]
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Sat Jul 16, 2011 2:55 pm    Post subject:
Reply with quote

So what did you find out when you ran with TRACE ?
Back to top
View user's profile Send private message
nmodi85

New User


Joined: 13 Jul 2011
Posts: 9
Location: India

PostPosted: Sat Jul 16, 2011 3:02 pm    Post subject:
Reply with quote

Thanks For your help !!!!

My code ran with different solution icon_smile.gif
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Sat Jul 16, 2011 3:04 pm    Post subject:
Reply with quote

Would you care to share the solution as this may help others in the future
Back to top
View user's profile Send private message
nmodi85

New User


Joined: 13 Jul 2011
Posts: 9
Location: India

PostPosted: Sat Jul 16, 2011 3:15 pm    Post subject:
Reply with quote

Ya sure !!!

Code:
/*REXX*/                                                       
PARSE ARG DBUB                                                 
ADDRESS TSO "SUBCOM DSNREXX"                                   
IF RC THEN                                                     
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')                     
ADDRESS DSNREXX "CONNECT "DBUB                                 
SQLSTMT = ,
(

         DB2 Query

)
                                                   
'SUBCOM DSNREXX'                                               
ADDRESS DSNREXX "EXECSQL PREPARE S4 INTO :TBSQLDA FROM :SQLSTMT"
ADDRESS DSNREXX "EXECSQL DECLARE C4 CURSOR FOR S4"             
ADDRESS DSNREXX "EXECSQL OPEN C4 "                             
ADDRESS DSNREXX "EXECSQL FETCH C4 INTO :COL_1, :COL_2"         

DO WHILE SQLCODE = 0                                           
SAY COL_1                                             
SAY COL_2                                             
ADDRESS DSNREXX "EXECSQL FETCH C4 INTO :COL_1, :COL_2"
IF SQLCODE = 100 THEN LEAVE                           
END                                                   
ADDRESS DSNREXX "EXECSQL CLOSE C4"                   
ADDRESS DSNREXX "DISCONNECT"

========================


However there is simple query in SQL we use GROUP BY and Having together. But how to write this in DB2 ?
Back to top
View user's profile Send private message
nmodi85

New User


Joined: 13 Jul 2011
Posts: 9
Location: India

PostPosted: Sat Jul 16, 2011 4:31 pm    Post subject:
Reply with quote

Need help again!!!!

Above code will work fine for first 2-3 attemps then It will give SQLCODE = -516 . Please help in resolving the same !!!!!

Any suggestions would be appreciated.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Sat Jul 16, 2011 4:43 pm    Post subject:
Reply with quote

What does it say about the SQLCODE issued in the manual
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1717
Location: UK

PostPosted: Sat Jul 16, 2011 5:42 pm    Post subject:
Reply with quote

It is not YOUR code - it is mine - and the original does NOT loop for ever. In fact, as posted, there is no loop in there at all but there is a spurious 'End'. Maybe the forum software is dropping some of the lines?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Sun Jul 17, 2011 1:52 pm    Post subject:
Reply with quote

Nic Clouston wrote:
It is not YOUR code - it is mine - and the original does NOT loop for ever. In fact, as posted, there is no loop in there at all but there is a spurious 'End'. Maybe the forum software is dropping some of the lines?


I thought the code looked very nice for someone managing to "loop" without any looping constructs and not knowing how to trace.

nmodi85, you have to run it with "trace". That is going to tell you where it is disappearing and not coming back for a long time, or even show you some sort of loop.

Why you'd want to abandon the nice code for what is effectively a stripped-down version of the same thing with littler error-checking, I don't know.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Sun Jul 17, 2011 6:09 pm    Post subject: Reply to: Going in Infinite loop while Running DB2 with REXX
Reply with quote

Bill Woodger wrote:
Why you'd want to abandon the nice code for what is effectively a stripped-down version of the same thing with littler error-checking, I don't know.
We write Tools in REXX!! We are tough!! We don't need no indentation and no error checking!! icon_wink.gif

the manual wrote:
-516 THE DESCRIBE STATEMENT DOES NOT SPECIFY A PREPARED STATEMENT
Then there must be a DESCRIBE somewhere...
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Sun Jul 17, 2011 11:19 pm    Post subject:
Reply with quote

... and our tools (sorry, forgot the CAPITAL t) take much longer to understand and debug than they take to write (when we actually write them).

(Perfect for people with time to waste).
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. Rexx to create GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts Replace repeated JCL with looping REXX prino CLIST & REXX 3 Thu Oct 13, 2016 5:30 am
This topic is locked: you cannot edit posts or make replies. Need to code a Rexx PGM sundaram.naveen CLIST & REXX 18 Thu Oct 06, 2016 6:45 pm


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