|
View previous topic :: View next topic
|
| Author |
Message |
nmodi85
New User
Joined: 13 Jul 2011
Posts: 9
Location: India
|
|
|
|
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 |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| 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 |
|
|
nmodi85
New User
Joined: 13 Jul 2011
Posts: 9
Location: India
|
|
|
|
Yes loop do matters 
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8796
Location: Welsh Wales
|
|
|
|
| So what did you find out when you ran with TRACE ? |
|
| Back to top |
|
|
nmodi85
New User
Joined: 13 Jul 2011
Posts: 9
Location: India
|
|
|
|
Thanks For your help !!!!
My code ran with different solution |
|
| Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8796
Location: Welsh Wales
|
|
|
|
| Would you care to share the solution as this may help others in the future |
|
| Back to top |
|
|
nmodi85
New User
Joined: 13 Jul 2011
Posts: 9
Location: India
|
|
|
|
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 |
|
|
nmodi85
New User
Joined: 13 Jul 2011
Posts: 9
Location: India
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8796
Location: Welsh Wales
|
|
|
|
| What does it say about the SQLCODE issued in the manual |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| 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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| 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 |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
| 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!! 
| the manual wrote: |
| -516 THE DESCRIBE STATEMENT DOES NOT SPECIFY A PREPARED STATEMENT |
Then there must be a DESCRIBE somewhere... |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
... 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 |
|
|
|
|
