IBM Mainframe Forum

by **Viswanathchandru** » Wed Jan 26, 2011 5:34 pm

Hi,
I wrote a small DSNREXX code which involves a select statement and i have used cursor for the display of the output from the database. My code is giving RC=0. But i cant see my output of the select statement. Is there anyother way to find the output. Please let me know the ways. Apologies if m wrong.

REgards,
viswa

by **NicC** » Thu Jan 27, 2011 12:51 am

Possibly you need to FETCH the data into variables and then SAY the variables. Here is my sample Rexx/DB2 program which I use as a base for all my similar programs. It displays to a panel which is attached at the end of the code and would need to be removed before the code is executed, but you could just as easily SAY the olinex.

Select all

/*- REXX ------------------------------------------------------------*/
/* */
/* Script Name: REXXDB2 Script Type: Demonstration */
/* */
/* Function : Communicate with DB2 */
/* */
/* Comment : This script is a demo to show how to retrieve data */
/* from DB2. */
/* It can be used as a guide to creating more useful */
/* scripts. */
/* The code for the panel used to display the data is */
/* attached to the end of the exec as a comment. */
/* */
/* Written By : Nic Clouston Date: September 2005 */
/* */
/* Credit to : S Coalbran - mate and former colleague */
/* */
/* Manual : DB2 UDB for zOS Vx Application Programming and SQL */
/* Guide */
/*------------------------------------------------------------ REXX -*/
Signal On Syntax Name Oops /* Syntax error handler */

/* 'CLEAR' */ /*- not required as I am using ISPF to show the data */

/*---------------------------*/
/* 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"
/*---- My table ----------------
Column Name Data Type Length
------------ --------- ------
COL_1 CHAR 6
COL_2 CHAR 8
COL_3 DECIMAL 13
COL_4 INTEGER 4
-------------------------------*/

/*-----------------------*/ /* 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'
/*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
/* Can also use: */
/* 'EXECSQL FETCH C1 USING DESCRIPTOR :outsqlda' */
/*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
Do While sqlcode = 0 /* Process data */

/*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
/* Use these lines if data was obtained by using: */
/* 'EXECSQL FETCH C1 INTO :col_1,...' */
/*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
oline1 = 'Col1 value = 'col_1
oline2 = 'Col2 value = 'col_2
oline3 = 'Col3 value = 'col_3
oline4 = 'Col4 value = 'col_4
/*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
/* Use these lines if data was obtained by using: */
/* >>> 'EXECSQL FETCH C1 USING DESCRIPTOR :outsqlda' <<< */
/* oline1 = 'Col1 value = 'outsqlda.1.sqldata */
/* oline2 = 'Col2 value = 'outsqlda.2.sqldata */
/* oline3 = 'Col3 value = 'outsqlda.3.sqldata */
/* oline4 = 'Col4 value = 'outsqlda.4.sqldata */
/* */
/* These lines are an alternative showing different values for */
/* outsqlda variables for a column: */
/* */
/* Do i = 1 to outsqlda.sqld */
/* Say "Column Number: " i */
/* Say "Column Name : " outsqlda.i.sqlname */
/* Say "Column Type : " outsqlda.i.sqltype */
/* Say "Column Value : " outsqlda.i.sqldata */
/* End */
/*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/

Address ISPEXEC 'DISPLAY PANEL(REXXDB2)'
'EXECSQL FETCH C1 INTO :col_1, :col_2, :col_3, :col_4'
/*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
/* 'EXECSQL FETCH C1 USING DESCRIPTOR :outsqlda' */
/*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/

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 /* We never get here! */
/* This is is the panel used by the exec.
)PANEL
/*-------------------------------------------------------------------*/
/* */
/* Application: Sample */
/* */
/* Function: Rexx/DB2 sample script */
/* */
/* Panel name: REXXDB2 */
/* */
/* Panel Function: Display the retrieved rows */
/* */
/* Created: August 2005 by Nic Clouston */
/* */
/* Modified: mmmmmmmmm 20xx by xxxxxxxxxxxxxxxxxx */
/* Reason: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx */
/* */
/* (C) Smoogro Software 2007 */
/*-------------------------------------------------------------------*/
)ATTR
! TYPE(OUTPUT) INTENS(HIGH)
)BODY expand(//)
% REXX DB2 /-/ REXX DB2 (ISPF Version) /-/
% Command ===>_zcmd +
+
+/ /%REXX DB2+/ /
+
+
+
+
+ / /Result of your query: / /
+
+
%&oline1 +
%&oline2 +
%&oline3 +
%&oline4 +
+
+
+
+
+
+ Press%ENTER+for next row
+ Press%END+to quit.
)INIT
)REINIT
)PROC
)END
*/

IBM Mainframe Forum

Clarification on rexx code.

Clarification on rexx code.

Re: Clarification on rexx code.