Example REXX programs for LOB data
Db2 programs in REXX can use LOB host variables and file reference variables, but not LOB locator variables.
Example of using simple LOB host variables in a REXX program
/* REXX exec to use a LOB in a host var */
ssid = "VA1A" ;
Address TSO "SUBCOM DSNREXX" ;
if rc then s_rc = RXSUBCOM("ADD","DSNREXX","DSNREXX") ;
say "rc from rxsubcom add=" rc
Address DSNREXX ;
"CONNECT" ssid ;
if sqlcode \= 0 then do ;
say "CONNECT to" ssid "failed.";
call sqlca
exit 8 ;
end ;
stmt = "DROP TABLE REXXCLOB" ;
Address DSNREXX ,
"EXECSQL EXECUTE IMMEDIATE :STMT" ;
say "RC/SQLCODE after DROP is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
stmt = "CREATE TABLE REXXCLOB (" || ,
"C1 CLOB(2M))" ;
Address DSNREXX ,
"EXECSQL EXECUTE IMMEDIATE :STMT" ;
say "RC/SQLCODE after CREATE is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
/* Insert into the CLOB table */
data = "THIS IS A SHORT CLOB, BUT IT IS A CLOB" ;
stmt = "INSERT INTO REXXCLOB (C1) VALUES(?) " ;
Address DSNREXX "EXECSQL PREPARE S1 FROM :STMT" ;
say "RC/SQLCODE after PREPARE is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
mydata = copies('Z',75000) ;
say "length of :mydata="length(mydata) ;
Address DSNREXX "EXECSQL EXECUTE S1 USING :MYDATA" ;
say "RC/SQLCODE after EXECUTE is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
/*
var1 = copies(' ',2048000) ;
say "length of :var1="length(var1) ;
*/
stmt = "SELECT C1, LENGTH(C1) FROM REXXCLOB" ;
Address DSNREXX "EXECSQL PREPARE S1 FROM :STMT" ;
say "RC/SQLCODE after PREPARE (SELECT C1) is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
Address DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1" ;
say "RC/SQLCODE after DECLARE is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
Address DSNREXX "EXECSQL OPEN C1" ;
say "RC/SQLCODE after OPEN is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
Address DSNREXX "EXECSQL FETCH C1 INTO :VAR1, :VAR2" ;
say "RC/SQLCODE after FETCH is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
say "length(var1)="length(var1) ;
say "var2="var2 ;
Address DSNREXX "EXECSQL CLOSE C1" ;
say "RC/SQLCODE after CLOSE is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
/**********************************************************************\
* Disconnect from the DB2 system. *
\**********************************************************************/
"DISCONNECT" ;
say "RC after DISCONNECT is" rc
s_rc = RXSUBCOM("DELETE","DSNREXX","DSNREXX") ;
exit 0 ;
sqlca_error:
call sqlca
exit 16
/*********************************************************************/
/* Error handling routine for bad SQL codes - just report and end. */
/*********************************************************************/
SQLCA:
/*********************************************************************/
SAY "Error. SQLCODE = >"SQLCODE"<"
SAY " SQLSTATE = >"SQLSTATE"<"
SAY " SQLERRMC = >"SQLERRMC"<"
SAY " SQLERRP = >"SQLERRP"<"
SAY " SQLERRD.1= >"SQLERRD.1"<"
SAY " SQLERRD.2= >"SQLERRD.2"<"
SAY " SQLERRD.3= >"SQLERRD.3"<"
SAY " SQLERRD.4= >"SQLERRD.4"<"
SAY " SQLERRD.5= >"SQLERRD.5"<"
SAY " SQLERRD.6= >"SQLERRD.6"<"
SAY " SQLWARN.0= >"SQLWARN.0"<"
SAY " SQLWARN.1= >"SQLWARN.1"<"
SAY " SQLWARN.2= >"SQLWARN.2"<"
SAY " SQLWARN.3= >"SQLWARN.3"<"
SAY " SQLWARN.4= >"SQLWARN.4"<"
SAY " SQLWARN.5= >"SQLWARN.5"<"
SAY " SQLWARN.6= >"SQLWARN.6"<"
SAY " SQLWARN.7= >"SQLWARN.7"<"
SAY " SQLWARN.8= >"SQLWARN.8"<"
SAY " SQLWARN.9= >"SQLWARN.9"<"
SAY " SQLWARN.10= >"SQLWARN.10"<"
return ;Example of using LOB data with an SQLDA in a REXX program
/* REXX EXEC TO INSERT A LOB USING SQLDA */
Address TSO "SUBCOM DSNREXX" ;
if rc then s_rc = RXSUBCOM("ADD","DSNREXX","DSNREXX") ;
say "rc from rxsubcom add=" rc
ssid = "VA1A" ;
Address DSNREXX "CONNECT" ssid ;
if sqlcode \= 0 then do ;
say "CONNECT to" ssid "failed.";
call sqlca
exit 8 ;
end ;
stmt = "DROP TABLE REXXCLOB" ;
Address DSNREXX "EXECSQL EXECUTE IMMEDIATE :STMT" ;
say "RC/SQLCODE after DROP is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
stmt = "CREATE TABLE REXXCLOB (" || ,
"C1 CLOB(1M))" ;
Address DSNREXX "EXECSQL EXECUTE IMMEDIATE :STMT" ;
say "RC/SQLCODE after CREATE is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
/* Insert into the CLOB table */
stmt = "INSERT INTO REXXCLOB (C1) VALUES(?) " ;
Address DSNREXX "EXECSQL PREPARE S1 INTO :D1 FROM :STMT" ;
say "RC/SQLCODE after PREPARE is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
mydata = copies('A',1048560) ; /* ~1M */
d1.sqld = 1 ;
d1.1.sqltype = 408 ;
d1.1.sqllongl= length(mydata) ;
d1.1.sqldata = mydata ;
say "length of mydata is" length(mydata) ;
Address DSNREXX "EXECSQL EXECUTE S1 USING DESCRIPTOR :D1" ;
say "RC/SQLCODE after EXECUTE S1, USING D1 is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
stmt = "SELECT C1, LENGTH(C1) AS LENGTH FROM REXXCLOB" ;
Address DSNREXX "EXECSQL PREPARE S1 INTO :OUTDA FROM :STMT"
say "RC/SQLCODE after PREPARE (SELECT C1) is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
say "After PREPARE INTO, SQLDA looks like:"
say " outda.sqld=>"outda.sqld"<" ;
do i = 1 to outda.sqld ;
say " "
say " outda."i".sqlname=>"outda.i.sqlname"<" ;
say " outda."i".sqltype=>"outda.i.sqltype"<" ;
end ;
say " "
Address DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1" ;
say "RC/SQLCODE after DECLARE is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
Address DSNREXX "EXECSQL OPEN C1" ;
say "RC/SQLCODE after OPEN is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
Do forever ;
Address DSNREXX "EXECSQL FETCH C1 INTO DESCRIPTOR :OUTDA" ;
say "RC/SQLCODE after FETCH is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
if sqlcode = 100 then leave ; /* do forever */
say "outda.sqld=>"outda.sqld"<" ;
do i = 1 to outda.sqld ;
say i": sqlname =>"outda.i.sqlname"<" ;
say i": sqltype =>"outda.i.sqltype"<" ;
say i": sqllen =>"outda.i.sqllen"<" ;
say i": sqllongl=>"outda.i.sqllongl"<"
say i": length =>"length(outda.i.sqldata)"<" ;
if length(outda.i.sqldata) > 62 then
say i": sqldata =>"substr(outda.i.sqldata,1,62)"<..." ;
else
say i": sqldata =>"outda.i.sqldata"<" ;
say ' ' ;
end ;
end ; /* do forever */
Address DSNREXX "EXECSQL CLOSE C1" ;
say "RC/SQLCODE after CLOSE is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
/**********************************************************************\
* Disconnect from the DB2 system. *
\**********************************************************************/
Address DSNREXX "DISCONNECT" ;
say "RC after DISCONNECT is" rc
s_rc = RXSUBCOM("DELETE","DSNREXX","DSNREXX") ;
exit 0 ;
sqlca_error:
call sqlca
exit 16
/*********************************************************************/
/* Error handling routine for bad SQL codes - just report and end. */
/*********************************************************************/
SQLCA:
/*********************************************************************/
SAY " SQLCODE = >"SQLCODE"<"
SAY " SQLSTATE = >"SQLSTATE"<"
SAY " SQLERRMC = >"SQLERRMC"<"
SAY " SQLERRP = >"SQLERRP"<"
SAY " SQLERRD.1= >"SQLERRD.1"<"
SAY " SQLERRD.2= >"SQLERRD.2"<"
SAY " SQLERRD.3= >"SQLERRD.3"<"
SAY " SQLERRD.4= >"SQLERRD.4"<"
SAY " SQLERRD.5= >"SQLERRD.5"<"
SAY " SQLERRD.6= >"SQLERRD.6"<"
SAY " SQLWARN.0= >"SQLWARN.0"<"
SAY " SQLWARN.1= >"SQLWARN.1"<"
SAY " SQLWARN.2= >"SQLWARN.2"<"
SAY " SQLWARN.3= >"SQLWARN.3"<"
SAY " SQLWARN.4= >"SQLWARN.4"<"
SAY " SQLWARN.5= >"SQLWARN.5"<"
SAY " SQLWARN.6= >"SQLWARN.6"<"
SAY " SQLWARN.7= >"SQLWARN.7"<"
SAY " SQLWARN.8= >"SQLWARN.8"<"
SAY " SQLWARN.9= >"SQLWARN.9"<"
SAY " SQLWARN.10= >"SQLWARN.10"<"
return ;Example of using LOB File Reference Variables in a REXX program
/* REXX EXEC TO USE A CLOB FILE REFERENCE VARIABLE */
ssid = "VA1A" ;
Address TSO "SUBCOM DSNREXX" ;
if rc then s_rc = RXSUBCOM("ADD","DSNREXX","DSNREXX") ;
say "rc from rxsubcom add=" rc
Address DSNREXX ;
"CONNECT" ssid ;
if sqlcode \= 0 then do ;
say "CONNECT to" ssid "failed.";
call sqlca_error
exit 8 ;
end ;
stmt = "DROP TABLE REXXFRV" ;
Address DSNREXX ,
"EXECSQL EXECUTE IMMEDIATE :STMT" ;
say "RC/SQLCODE after DROP is" rc"/"sqlcode ;
if rc <> 0 & sqlcode <> -204 then call sqlca_error ;
stmt = "CREATE TABLE REXXFRV (" || ,
"C1 CLOB(2M))" ;
Address DSNREXX ,
"EXECSQL EXECUTE IMMEDIATE :STMT" ;
say "RC/SQLCODE after CREATE is" rc"/"sqlcode ;
if rc <> 0 then call sqlca_error ;
/*
Write the CLOB to the preallocated file
*/
lines = 1500; /* enough 80 byte lines to make 2,000,000 bytes */
data.1 = "THIS IS A SHORT CLOB, BUT IT IS A CLOB 01" ;
data.2 = "THIS IS A SHORT CLOB, BUT IT IS A CLOB 02" ;
data.3 = "THIS IS A SHORT CLOB, BUT IT IS A CLOB 03" ;
data.4 = "THIS IS A SHORT CLOB, BUT IT IS A CLOB 04" ;
data.5 = "THIS IS A SHORT CLOB, BUT IT IS A CLOB 05" ;
data.6 = "THIS IS A SHORT CLOB, BUT IT IS A CLOB 06" ;
data.7 = "THIS IS A SHORT CLOB, BUT IT IS A CLOB 07" ;
data.8 = "THIS IS A SHORT CLOB, BUT IT IS A CLOB 08" ;
data.9 = "THIS IS A SHORT CLOB, BUT IT IS A CLOB 09" ;
data.10= "THIS IS A SHORT CLOB, BUT IT IS A CLOB 10" ;
data.0 = 10 ;
say 'data. stem initialized' ;
Do i = 1 to data.0 ;
data.i = left(data.i,131) ;
end ;
say 'data. stem padded to 131' ;
Do i = 1 to lines ;
Address MVS "EXECIO" data.0 "DISKW FRVFILE (stem data." ;
if rc <> 0 then do ;
say 'rc from execio='rc ;
signal bad_write ;
end ;
end ;
/* Close the file */
Address MVS "EXECIO 0 DISKW FRVFILE (FINIS" ;
/*
The file now has to be freed. Otherwise, a
SQLCODE -452, reason 12 at location 210 will be
issued.
*/
Address TSO "FREE FI(FRVFILE)" ;
if rc <> 0 then signal bad_free ;
stmt = "INSERT INTO REXXFRV (C1) VALUES(?) " ;
Address DSNREXX "EXECSQL PREPARE S1 FROM :STMT" ;
say "RC/SQLCODE after PREPARE is" rc"/"sqlcode ;
if rc <> 0 then call sqlca_error ;
/*
Build the special SQLDA used by REXX for working with
LOBs.
*/
mysqlda.sqld = 1 ;
mysqlda.1.sqltype = 920 /* clob file ref var */
mysqlda.1.sqlind = 0 ; /* not null */
/*
Note for a file reference variable, there is
no SQLDATA value. Just use SQLDATA as part of the stem
for .name and .fileoption, which are required for FRVs.
*/
mysqlda.1.sqldata.name = "SYSADM.FRV" ; /* file name */
/*
There are 4 fileoptions that can be set, and you can
specify the value via text or a number. Here are the
allowable values:
SQL_FILE_READ or 2
SQL_FILE_CREATE or 8
SQL_FILE_OVERWRITE or 16
SQL_FILE_APPEND or 32
*/
mysqlda.1.sqldata.fileoption = "SQL_FILE_READ" ;
/*
sqllen is the length of the file name
*/
mysqlda.1.sqllen = length(mysqlda.1.sqldata.name) ;
Address DSNREXX "EXECSQL EXECUTE S1 USING DESCRIPTOR :MYSQLDA";
say "RC/SQLCODE after EXECUTE is" rc"/"sqlcode ;
if rc <> 0 then call sqlca_error ;
stmt = "SELECT C1, LENGTH(C1) FROM REXXFRV" ;
Address DSNREXX "EXECSQL PREPARE S1 FROM :STMT" ;
say "RC/SQLCODE after PREPARE (SELECT C1) is" rc"/"sqlcode ;
if rc <> 0 then call sqlca_error ;
Address DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1" ;
say "RC/SQLCODE after DECLARE is" rc"/"sqlcode ;
if rc <> 0 then call sqlca_error ;
Address DSNREXX "EXECSQL OPEN C1" ;
say "RC/SQLCODE after OPEN is" rc"/"sqlcode ;
if rc <> 0 then call sqlca_error;
Address DSNREXX "EXECSQL FETCH C1 INTO :VAR1, :VAR2" ;
say "RC/SQLCODE after FETCH is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
say "length(var1)="length(var1) ;
say "var1=" ;
say var1 ;
Address DSNREXX "EXECSQL CLOSE C1" ;
say "RC/SQLCODE after CLOSE is" rc"/"sqlcode ;
if rc <> 0 then call sqlca ;
/**********************************************************************\
* Disconnect from the DB2 system. *
\**********************************************************************/
"DISCONNECT" ;
say "RC after DISCONNECT is" rc
s_rc = RXSUBCOM("DELETE","DSNREXX","DSNREXX") ;
exit 0 ;
sqlca_error:
call sqlca
if sqlcode > 0 then return ;
exit 8 ;
/*********************************************************************/
/* Error handling routine for bad SQL codes - just report and end. */
/*********************************************************************/
SQLCA:
/*********************************************************************/
SAY "Error. SQLCODE = >"SQLCODE"<"
SAY " SQLSTATE = >"SQLSTATE"<"
SAY " SQLERRMC = >"SQLERRMC"<"
SAY " SQLERRP = >"SQLERRP"<"
SAY " SQLERRD.1= >"SQLERRD.1"<"
SAY " SQLERRD.2= >"SQLERRD.2"<"
SAY " SQLERRD.3= >"SQLERRD.3"<"
SAY " SQLERRD.4= >"SQLERRD.4"<"
SAY " SQLERRD.5= >"SQLERRD.5"<"
SAY " SQLERRD.6= >"SQLERRD.6"<"
SAY " SQLWARN.0= >"SQLWARN.0"<"
SAY " SQLWARN.1= >"SQLWARN.1"<"
SAY " SQLWARN.2= >"SQLWARN.2"<"
SAY " SQLWARN.3= >"SQLWARN.3"<"
SAY " SQLWARN.4= >"SQLWARN.4"<"
SAY " SQLWARN.5= >"SQLWARN.5"<"
SAY " SQLWARN.6= >"SQLWARN.6"<"
SAY " SQLWARN.7= >"SQLWARN.7"<"
SAY " SQLWARN.8= >"SQLWARN.8"<"
SAY " SQLWARN.9= >"SQLWARN.9"<"
SAY " SQLWARN.10= >"SQLWARN.10"<"
return ;