Rexx Newbee - DB2 Sqlcode -312
Ed Benoit
This is a REXX DB2 routine. I am attempting to get the tablespace names from
sysibm.systablespace.
This is tbe message I am getting from DB2
48 *-* ADDRESS DSNREXX
49 *-* 'EXECSQL PREPARE S1 FROM:SQLSTMT'
>>> "EXECSQL PREPARE S1 FROM:SQLSTMT"
+++ RC(-1) +++
50 *-* IF SQLCODE <> 0
>>> "1"
*-* THEN
51 *-* DO
52 *-* SAY '*** ERROR PREPARE S1 SQLCODE = 'SQLCODE
>>> "*** ERROR PREPARE S1 SQLCODE = -312"
*** ERROR PREPARE S1 SQLCODE = -312
53 *-* SAY '*** SQLERRMC = ' SQLERRMC
>>> "*** SQLERRMC = DBASE"
*** SQLERRMC = DBASE
Look like it do like my SQLSTMT statement. It do not like my DBASE variable.
I get DBASE for a VGET SHARED which is working OK. What am I doing wrong?
Below is my SQLSTMT.
000023 SQLSTMT = "SELECT NAME FROM SYSIBM.SYSTABLESPACE WHERE "
000024 SQLSTMT = SQLSTMT"DBNAME = :DBASE"
Any help would be appreciated.
Ed.
----------------------------------------------------------------------
For TSO-REXX subscribe / signoff / archive access instructions,
send email to LIST...@VM.MARIST.EDU with the message: INFO TSO-REXX
Schwarzbauer, Joe
Do you need the VALUE of dbase like this:
SQLSTMT = SQLSTMT"DBNAME = "DBASE
Or perhaps you still need the colon too? I'm NOT a db2 guy, just a REXX
journeyman
Ed.
-----------------------------------------
CONFIDENTIALITY NOTICE: The information contained in this e-mail and
attached document(s) may contain confidential information that is intended
only for the addressee(s). If you are not the intended recipient, you are
hereby advised that any disclosure, copying, distribution or the taking of
any action in reliance upon the information is prohibited. If you have
received this e-mail in error, please immediately notify the sender and
delete it from your system.
Robert Zenuk
000024 SQLSTMT = SQLSTMT "DBNAME = '"DBASE"'"
In DSNREXX the SQL WHERE predicate is not evaluated for host variables like
other HLL's. So, use standard REXX syntax like you would with any other host
command.
Here is a working example:
/*********************************************************************/
/* REXX */
/*********************************************************************/
/* Purpose: Sample DB2/REXX */
/*-------------------------------------------------------------------*/
/* Syntax: db2exec ssid isolation */
/*-------------------------------------------------------------------*/
/* Parms: ssid - DB2 Subsystem ID */
/* isolation - DB2 Isolation level */
/* */
/* Notes: RC 00 - OK */
/* 12 - Missing SSID */
/* 20 - DSNREXX Initialization error */
/* 24 - DSNREXX Termination error */
/* */
/*********************************************************************/
/* Change Log */
/* */
/* Author Date Reason */
/* -------- --------- ----------------------------------------- */
/* R. Zenuk Apr 2004 Initial Creation */
/* */
/*********************************************************************/
/* Accept SSID and isolation level (default is UR) */
/*********************************************************************/
arg ssid isolation
if ssid = '' then exit(12)
if isolation = '' then isolation = 'UR'
/*********************************************************************/
/* Initialize the REXX/SQL interface */
/*********************************************************************/
if rxsubcom('ADD','DSNREXX','DSNREXX') <> 0 then exit(20)
/*********************************************************************/
/* Connect */
/*********************************************************************/
if execcmd("CONNECT" ssid) = 0 then say 'Connected to' ssid
/*********************************************************************/
/* Set isolation level */
/*********************************************************************/
call execsql "SET CURRENT PACKAGESET='DSNREX"isolation"'"
/*********************************************************************/
/* Declare the cursor */
/*********************************************************************/
call execsql "DECLARE C1 CURSOR FOR S1"
/*********************************************************************/
/* Prepare the statement */
/*********************************************************************/
dbname = 'MYDB001'
sql = "SELECT NAME FROM SYSIBM.SYSTABLESPACE WHERE DBNAME = '"dbname"'"
call execsql "PREPARE S1 FROM :SQL"
/*********************************************************************/
/* Open the cursor */
/*********************************************************************/
call execsql "OPEN C1"
rowcount = 0
/*********************************************************************/
/* Fetch loop */
/*********************************************************************/
do forever
call execsql "FETCH C1 INTO :HVNAME"
if sqlcode = 100 then leave
rowcount = rowcount + 1
say hvname
end
say rowcount 'rows retrieved'
/*********************************************************************/
/* Close the cursor */
/*********************************************************************/
call execsql "CLOSE C1"
/*********************************************************************/
/* Disconnect */
/*********************************************************************/
call execcmd "DISCONNECT" ssid
/*********************************************************************/
/* Unload the REXX/SQL interface */
/*********************************************************************/
if rxsubcom('DELETE','DSNREXX','DSNREXX') <> 0 then exit(24)
exit 0
/*********************************************************************/
/* DSNREXX Commands */
/*********************************************************************/
execcmd: arg sqlstmt
address DSNREXX sqlstmt
return sqlcode
/*********************************************************************/
/* DSNREXX EXECSQL Commands */
/*********************************************************************/
execsql: arg sqlstmt
address DSNREXX 'EXECSQL' sqlstmt
return sqlcode
Hope This Helps,
Rob
Isaac Yassin
Just remove the : from DBASE
Isaac Yassin
-----Original Message-----
From: TSO REXX Discussion List [mailto:TSO-...@VM.MARIST.EDU] On Behalf Of Ed Benoit
Sent: Wednesday, September 29, 2004 11:59 PM
To: TSO-...@VM.MARIST.EDU
Subject: Rexx Newbee - DB2 Sqlcode -312
Markus Liebenberg
Here's a basic sample. Important: There is an implied relationship between a cursor and the corresponding statement. A cursor must be declared before its corresponding statement is used. This is true even when the statement contains a non-cursor operation such as UPDATE. For example, to process an UPDATE via statement S1, a cursor C1 must be declared.
It may be that the email system you are using changed the message, but in your sample code there is no space between FROM and the host variable name :SQLSTMT, and there should be.
DB2_Select:
-----------
STMT1 = "Select creator, name from sysibm.systables " ,
"where creator = '"creator_name"' " ,
" and type = 'T' "
ADDRESS dsnrexx "EXECSQL DECLARE C1 CURSOR FOR S1"
call Check_SQLCODE
ADDRESS dsnrexx "EXECSQL PREPARE S1 FROM :STMT1"
call Check_SQLCODE
ADDRESS dsnrexx "EXECSQL OPEN C1"
call Check_SQLCODE
ADDRESS dsnrexx "EXECSQL FETCH C1 INTO :CREATOR, :NAME"
call Check_SQLCODE
Do while SQLCODE = 0
ADDRESS dsnrexx "EXECSQL FETCH C1 INTO :CREATOR, :NAME"
If SQLCODE = 0 Then Do
cnt1 = cnt1 + 1
in.cnt1 = creator name /* Load STEM variable */
End
End
in.0 = cnt1 /* Set STEM.0 to record cnt */
Return
Regards,
Markus
Ed.
------------------------------------------------------------------------
Confidentiality Note: The information contained in this email and
document(s) attached are for the exclusive use of the addressee and may
contain confidential, privileged and non-disclosable information. If the
recipient of this email is not the addressee, such recipient is strictly
prohibited from reading, photocopying, distribution or otherwise using this
email or its contents in any way. Please notify the Sapiens (UK) Ltd.
Systems Administrator via e-mail immediately at
network...@sapiens.co.uk, if you have received this email in error.
Disclaimer: The views, opinions and guidelines contained in this
confidential e-mail are those of the originating author and may not be
representative of Sapiens (UK) Ltd.
------------------------------------------------------------------------
Lola Tomillo
AFAIK, you have to define a CURSOR to retrieve de data....
gerard46
| Hope This Helps
I took the liberty of re-writting the comments and some of the code a
bit to make it much easier to read.
_______________________________________________________________________
/*REXX*/
/*--------------------------------------------------+
| purpose: sample DB2/REXX |
| |
| syntax: DB2EXEC ssid isolation |
| |
| parms: ssid - DB2 subsystem ID |
| isolation - DB2 isolation level |
| |
| notes: rc 00 - ok |
| 12 - missing SSID |
| 20 - DSNREXX Initialization error |
| 24 - DSNREXX Termination error |
| |
| change log: |
| |
| programmer date reason |
| -------------- --------- ------------------ |
| R. Zenuk Apr 2004 Initial Creation |
| Gerard S. Sep 2004 compacted comments |
+--------------------------------------------------+
arg ssid isolation /*get SSID and isolation level. */
if ssid='' then exit 12
if isolation='' then isolation='UR' /*assume the isolation default. */
/*initialize REXX/SQL interface.*/
if rxsubcom('ADD','DSNREXX','DSNREXX')<>0 then exit 20
if execcmd("CONNECT" ssid)==0 then say 'Connected to' ssid /*connect*/
/*set isolation level. */
call execsql "SET CURRENT PACKAGESET='DSNREX"isolation"'"
call execsql "DECLARE C1 CURSOR FOR S1" /*declare the cursor. */
/*pepare the statement.*/
dbname='MYDB001'
sql="SELECT NAME FROM SYSIBM.SYSTABLESPACE WHERE DBNAME = '"dbname"'"
call execsql "PREPARE S1 FROM :SQL"
call execsql "OPEN C1" /*open the cursor.*/
rowcount=0
do forever /*fetch loop.*/
call execsql "FETCH C1 INTO :HVNAME"
if sqlcode==100 then leave
rowcount=rowcount+1
say hvname
end
say rowcount 'rows retrieved'
call execsql "CLOSE C1" /*close the cursor.*/
call execcmd "DISCONNECT" ssid /*disconnect. */
/*unload REXX/SQL interface.*/
if rxsubcom('DELETE','DSNREXX','DSNREXX')<>0 then exit 24
exit 0
/*------------------+
| DSNREXX Commands |
+------------------*/
execcmd: parse arg sqlstmt; address DSNREXX sqlstmt
return sqlcode
/*--------------------------+
| DSNREXX EXECSQL Commands |
+--------------------------*/
execsql: parse arg sqlstmt; address DSNREXX 'EXECSQL' sqlstmt
return sqlcode
_______________________________________________________________________
Should there be a blank after DSNREX" in the statement:
call execsql "SET CURRENT PACKAGESET='DSNREX"isolation"'"
? ___________________________________________________________Gerard S.