1) Non -Select Dynamic SQL
2) Fixed-List Select Dynamic SQL
3) Varying-List Select Dynamic SQL
Each of these can have 2 flavors each i.e. "without parameter markers" and "with parameter markers"
Below I will show you how to accomplish all 6 types of dynamic SQL using REXX(coz its darn easy to do them REXX)
Ohh and did I mention.. I hate teaching :( .. so for "understanding" please refer to this article and this REDBOOK "SQUEEZING THE MOST OUT OF DYNAMIC SQL"(believe me the REDBOOK is excellent).
Some precursor info:
1) You should have the DSNREXX package bound to your DB and you must have auth to use it.. if its not there ask your system admin to run the job DSNTIJRX in the DB2 Sample library.. in my case DSN810.SDSNSAMP
2) In REXX all SQL's are DYNAMIC.. believe me there is not STATIC sql in REXX. and there is not absolute need to declare a special SQLCA/SQLDA as in COBOL(INCLUDE SQLCA).
3) In REXX even if you are going to insert null's into a column by placing a negative value in your indicator variable keep in mid that the data field variable must have a value else you'll land in error.. there is a saving grace though coz in REXX an uninitialized variable's value is the variable name itself.
4) Create a table.. any will do but I have one like this:
CREATE TABLE IBMGRP.EMP
(EMP_ID CHAR(6) FOR SBCS DATA NOT NULL,
FIRST_NAME CHAR(20) FOR SBCS DATA NOT NULL,
MIDDLE_NAME CHAR(20) FOR SBCS DATA WITH DEFAULT NULL,
LAST_NAME CHAR(20) FOR SBCS DATA NOT NULL,
CONSTRAINT EMP_ID
PRIMARY KEY (EMP_ID))
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE;
COMMIT;
CREATE UNIQUE INDEX IBMGRP.EMP_KOB
ON IBMGRP.EMP
(EMP_ID ASC)
USING STOGROUP SYSDEFLT
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP0
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
You can get this easily using the program ADB2GEN..
Now that we have the object and the relevant DSNREXX package bound to the DB..lets start querying:
1) Non -Select Dynamic SQL without parameter markers:
/* REXX */
ADDRESS TSO
'SUBCOM DSNREXX'
IF RC = 1 THEN DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX
"CONNECT DB2R"
QRY="DELETE" ,
"FROM IBMGRP.EMP" ,
"WHERE EMP_ID = '051029'"
'EXECSQL PREPARE S1 FROM :QRY'
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL EXECUTE S1"
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL ROLLBACK"
IF SQLCODE /= 0 THEN CALL SQLCA
"DISCONNECT"
IF SQLCODE /= 0 THEN CALL SQLCA
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
SQLCA:
SAY "SQLCODE = " SQLCODE
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
SAY "SQLSTATE = " SQLSTATE
EXIT
(have a rollback in there so that I do not have to insert the deleted row repeatedly during testing :P )
Output:
SQLCODE = 0
SQLERRMC =
SQLERRP = DSNTZALI
SQLERRD.1= 0
SQLERRD.2= 0
SQLERRD.3= 0
SQLERRD.4= 0
SQLERRD.5= 0
SQLERRD.6= 0
SQLWARN.0=
SQLWARN.1=
SQLWARN.2=
SQLWARN.3=
SQLWARN.4=
SQLWARN.5=
SQLWARN.6=
SQLWARN.7=
SQLWARN.8=
SQLWARN.9=
SQLWARN.10=
SQLSTATE = 00000
2) Non -Select Dynamic SQL with parameter markers:
/* REXX */
EMP_ID="'051029'"
ADDRESS TSO
'SUBCOM DSNREXX'
IF RC = 1 THEN DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX
"CONNECT DB2R"
QRY="DELETE" ,
"FROM IBMGRP.EMP" ,
"WHERE EMP_ID = CAST ( ? AS CHAR(6) )"
'EXECSQL DECLARE C1 CURSOR FOR S1'
IF SQLCODE /= 0 THEN CALL SQLCA
'EXECSQL PREPARE S1 FROM :QRY'
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL EXECUTE S1 USING :EMP_ID"
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL ROLLBACK"
IF SQLCODE /= 0 THEN CALL SQLCA
"DISCONNECT"
IF SQLCODE /= 0 THEN CALL SQLCA
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
SQLCA:
SAY "SQLCODE = " SQLCODE
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
SAY "SQLSTATE = " SQLSTATE
EXIT
Output:
SQLCODE = 0
SQLERRMC =
SQLERRP = DSNTZALI
SQLERRD.1= 0
SQLERRD.2= 0
SQLERRD.3= 0
SQLERRD.4= 0
SQLERRD.5= 0
SQLERRD.6= 0
SQLWARN.0=
SQLWARN.1=
SQLWARN.2=
SQLWARN.3=
SQLWARN.4=
SQLWARN.5=
SQLWARN.6=
SQLWARN.7=
SQLWARN.8=
SQLWARN.9=
SQLWARN.10=
SQLSTATE = 00000
3) Fixed-List Select Dynamic SQL without parameter markers:
/* REXX */
ADDRESS TSO
'SUBCOM DSNREXX'
IF RC = 1 THEN DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX
"CONNECT DB2R"
QRY="SELECT FIRST_NAME,MIDDLE_NAME,LAST_NAME" ,
"FROM IBMGRP.EMP" ,
"WHERE EMP_ID = '139333'"
'EXECSQL DECLARE C1 CURSOR FOR S1'
IF SQLCODE /= 0 THEN CALL SQLCA
'EXECSQL PREPARE S1 FROM :QRY'
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL OPEN C1"
IF SQLCODE /= 0 THEN CALL SQLCA
DO UNTIL(SQLCODE /= 0)
"EXECSQL FETCH C1 INTO :FIRST_NAME, :MIDDLE_NAME :MIND, :LAST_NAME"
IF SQLCODE /= 0 THEN
CALL SQLCA
ELSE
DO
SAY 'FIRST NAME: ' FIRST_NAME
IF MIND == 0 THEN
SAY 'MIDDLE NAME: ' MIDDLE_NAME
ELSE
SAY 'NULL VALUE RECEIVED'
SAY 'LAST NAME: ' LAST_NAME
SAY
END
END
"EXECSQL CLOSE C1"
IF SQLCODE /= 0 THEN CALL SQLCA
"DISCONNECT"
IF SQLCODE /= 0 THEN CALL SQLCA
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
SQLCA:
SAY "SQLCODE = " SQLCODE
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
SAY "SQLSTATE = " SQLSTATE
EXIT
Output:
FIRST NAME: JAGAMOHAN
NULL VALUE RECEIVED
LAST NAME: TRIPATHY
SQLCODE = 100
SQLERRMC =
SQLERRP = DSNXRFN
SQLERRD.1= -110
SQLERRD.2= 0
SQLERRD.3= 0
SQLERRD.4= -1
SQLERRD.5= 0
SQLERRD.6= 0
SQLWARN.0=
SQLWARN.1=
SQLWARN.2=
SQLWARN.3=
SQLWARN.4=
SQLWARN.5=
SQLWARN.6=
SQLWARN.7=
SQLWARN.8=
SQLWARN.9=
SQLWARN.10=
SQLSTATE = 02000
4) Fixed-List Select Dynamic SQL with parameter markers:
/* REXX */
EMP_ID="'051029'"
ADDRESS TSO
'SUBCOM DSNREXX'
IF RC = 1 THEN DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX
"CONNECT DB2R"
QRY="SELECT FIRST_NAME,MIDDLE_NAME,LAST_NAME" ,
"FROM IBMGRP.EMP" ,
"WHERE EMP_ID = CAST ( ? AS CHAR(6) )"
'EXECSQL DECLARE C1 CURSOR FOR S1'
IF SQLCODE /= 0 THEN CALL SQLCA
'EXECSQL PREPARE S1 FROM :QRY'
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL OPEN C1 USING :EMP_ID"
IF SQLCODE /= 0 THEN CALL SQLCA
DO UNTIL(SQLCODE /= 0)
"EXECSQL FETCH C1 INTO :FIRST_NAME, :MIDDLE_NAME :MIND, :LAST_NAME"
IF SQLCODE /= 0 THEN
CALL SQLCA
ELSE
DO
SAY 'FIRST NAME: ' FIRST_NAME
IF MIND == 0 THEN
SAY 'MIDDLE NAME: ' MIDDLE_NAME
ELSE
SAY 'NULL VALUE RECEIVED'
SAY 'LAST NAME: ' LAST_NAME
SAY
END
END
"EXECSQL CLOSE C1"
IF SQLCODE /= 0 THEN CALL SQLCA
"DISCONNECT"
IF SQLCODE /= 0 THEN CALL SQLCA
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
SQLCA:
SAY "SQLCODE = " SQLCODE
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
SAY "SQLSTATE = " SQLSTATE
EXIT
Output:
FIRST NAME: KOBID
MIDDLE NAME: KUMAR
LAST NAME: DAS
SQLCODE = 100
SQLERRMC =
SQLERRP = DSNXRFN
SQLERRD.1= -110
SQLERRD.2= 0
SQLERRD.3= 0
SQLERRD.4= -1
SQLERRD.5= 0
SQLERRD.6= 0
SQLWARN.0=
SQLWARN.1=
SQLWARN.2=
SQLWARN.3=
SQLWARN.4=
SQLWARN.5=
SQLWARN.6=
SQLWARN.7=
SQLWARN.8=
SQLWARN.9=
SQLWARN.10=
SQLSTATE = 02000
5) Varying-List Select Dynamic SQL without parameter markers:
It might appear that the Fixed list as well as the Varying list examples have similar queries but varying list gives you the freedom to choose columns and their order.. say from a panel the user highlights only those columns to be selected which he wants to see and then you have to build the SQL without any prior knowledge of what columns he may ask for and in what order.
/* REXX */
ADDRESS TSO
'SUBCOM DSNREXX'
IF RC = 1 THEN DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX
"CONNECT DB2R"
QRY="SELECT FIRST_NAME,MIDDLE_NAME,LAST_NAME" ,
"FROM IBMGRP.EMP" ,
"WHERE EMP_ID = '139333'"
"EXECSQL DECLARE C1 CURSOR FOR S1"
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :QRY"
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL OPEN C1"
IF SQLCODE /= 0 THEN CALL SQLCA
DO UNTIL(SQLCODE /= 0)
"EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
IF SQLCODE /= 0 THEN
CALL SQLCA
ELSE
DO
DO I=1 TO OUTSQLDA.SQLD
SAY "COLUMN NAME: "OUTSQLDA.I.SQLNAME
SAY "COLUMN TYPE: "OUTSQLDA.I.SQLTYPE
SAY "COLUMN LENG: "OUTSQLDA.I.SQLLEN
SAY "COLUMN INDC: "OUTSQLDA.I.SQLIND
IF OUTSQLDA.I.SQLIND >= 0 THEN
SAY "COLUMN DATA: "OUTSQLDA.I.SQLDATA
ELSE
SAY "NULL DATA RETRIEVED"
SAY
END I
SAY
SAY "END OF RECORD"
SAY
END
END
"EXECSQL CLOSE C1"
IF SQLCODE /= 0 THEN CALL SQLCA
"DISCONNECT"
IF SQLCODE /= 0 THEN CALL SQLCA
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
SQLCA:
SAY "SQLCODE = " SQLCODE
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
SAY "SQLSTATE = " SQLSTATE
EXIT
Output:
COLUMN NAME: FIRST_NAME
COLUMN TYPE: 452
COLUMN LENG: 20
COLUMN INDC: 0
COLUMN DATA: JAGAMOHAN
COLUMN NAME: MIDDLE_NAME
COLUMN TYPE: 453
COLUMN LENG: 20
COLUMN INDC: -1
NULL DATA RETRIEVED
COLUMN NAME: LAST_NAME
COLUMN TYPE: 452
COLUMN LENG: 20
COLUMN INDC: 0
COLUMN DATA: TRIPATHY
END OF RECORD
SQLCODE = 100
SQLERRMC =
SQLERRP = DSNXRFN
SQLERRD.1= -110
SQLERRD.2= 0
SQLERRD.3= 0
SQLERRD.4= -1
SQLERRD.5= 0
SQLERRD.6= 0
SQLWARN.0=
SQLWARN.1=
SQLWARN.2=
SQLWARN.3=
SQLWARN.4=
SQLWARN.5=
SQLWARN.6=
SQLWARN.7=
SQLWARN.8=
SQLWARN.9=
SQLWARN.10=
SQLSTATE = 02000
6) Varying-List Select Dynamic SQL with parameter markers:
/* REXX */
EMP_ID='139333'
ADDRESS TSO
'SUBCOM DSNREXX'
IF RC = 1 THEN DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX
"CONNECT DB2R"
INSQLDA.SQLD = 1
INSQLDA.1.SQLTYPE = 452
INSQLDA.1.SQLLEN = LENGTH(EMP_ID)
INSQLDA.1.SQLDATA = EMP_ID
INSQLDA.1.SQLIND = 0
QRY="SELECT FIRST_NAME,MIDDLE_NAME,LAST_NAME" ,
"FROM IBMGRP.EMP" ,
"WHERE EMP_ID = CAST ( ? AS CHAR(6) )"
'EXECSQL DECLARE C1 CURSOR FOR S1'
IF SQLCODE /= 0 THEN CALL SQLCA
'EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :QRY'
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL OPEN C1 USING DESCRIPTOR :INSQLDA"
IF SQLCODE /= 0 THEN CALL SQLCA
DO UNTIL(SQLCODE /= 0)
"EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
IF SQLCODE /= 0 THEN
CALL SQLCA
ELSE
DO
DO I=1 TO OUTSQLDA.SQLD
SAY "COLUMN NAME: "OUTSQLDA.I.SQLNAME
SAY "COLUMN TYPE: "OUTSQLDA.I.SQLTYPE
SAY "COLUMN LENG: "OUTSQLDA.I.SQLLEN
SAY "COLUMN INDC: "OUTSQLDA.I.SQLIND
IF OUTSQLDA.I.SQLIND >= 0 THEN
SAY "COLUMN DATA: "OUTSQLDA.I.SQLDATA
ELSE
SAY "NULL DATA RETRIEVED"
SAY
END I
SAY
SAY "END OF RECORD"
SAY
END
END
"EXECSQL CLOSE C1"
IF SQLCODE /= 0 THEN CALL SQLCA
"DISCONNECT"
IF SQLCODE /= 0 THEN CALL SQLCA
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
SQLCA:
SAY "SQLCODE = " SQLCODE
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
SAY "SQLSTATE = " SQLSTATE
EXIT
Output:
COLUMN NAME: FIRST_NAME
COLUMN TYPE: 452
COLUMN LENG: 20
COLUMN INDC: 0
COLUMN DATA: JAGAMOHAN
COLUMN NAME: MIDDLE_NAME
COLUMN TYPE: 453
COLUMN LENG: 20
COLUMN INDC: -1
NULL DATA RETRIEVED
COLUMN NAME: LAST_NAME
COLUMN TYPE: 452
COLUMN LENG: 20
COLUMN INDC: 0
COLUMN DATA: TRIPATHY
END OF RECORD
SQLCODE = 100
SQLERRMC =
SQLERRP = DSNXRFN
SQLERRD.1= -110
SQLERRD.2= 0
SQLERRD.3= 0
SQLERRD.4= -1
SQLERRD.5= 0
SQLERRD.6= 0
SQLWARN.0=
SQLWARN.1=
SQLWARN.2=
SQLWARN.3=
SQLWARN.4=
SQLWARN.5=
SQLWARN.6=
SQLWARN.7=
SQLWARN.8=
SQLWARN.9=
SQLWARN.10=
SQLSTATE = 02000
If you know REXX and DB2 the above programs are self-explanatory ;)
And last but not the least being an Engineer myself.. I believe in re-engineering and thus have attempted to give a head start to you.. hopefully it clicked.. Cya :)
1) You should have the DSNREXX package bound to your DB and you must have auth to use it.. if its not there ask your system admin to run the job DSNTIJRX in the DB2 Sample library.. in my case DSN810.SDSNSAMP
2) In REXX all SQL's are DYNAMIC.. believe me there is not STATIC sql in REXX. and there is not absolute need to declare a special SQLCA/SQLDA as in COBOL(INCLUDE SQLCA).
3) In REXX even if you are going to insert null's into a column by placing a negative value in your indicator variable keep in mid that the data field variable must have a value else you'll land in error.. there is a saving grace though coz in REXX an uninitialized variable's value is the variable name itself.
4) Create a table.. any will do but I have one like this:
CREATE TABLE IBMGRP.EMP
(EMP_ID CHAR(6) FOR SBCS DATA NOT NULL,
FIRST_NAME CHAR(20) FOR SBCS DATA NOT NULL,
MIDDLE_NAME CHAR(20) FOR SBCS DATA WITH DEFAULT NULL,
LAST_NAME CHAR(20) FOR SBCS DATA NOT NULL,
CONSTRAINT EMP_ID
PRIMARY KEY (EMP_ID))
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE;
COMMIT;
CREATE UNIQUE INDEX IBMGRP.EMP_KOB
ON IBMGRP.EMP
(EMP_ID ASC)
USING STOGROUP SYSDEFLT
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP0
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
You can get this easily using the program ADB2GEN..
Now that we have the object and the relevant DSNREXX package bound to the DB..lets start querying:
1) Non -Select Dynamic SQL without parameter markers:
/* REXX */
ADDRESS TSO
'SUBCOM DSNREXX'
IF RC = 1 THEN DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX
"CONNECT DB2R"
QRY="DELETE" ,
"FROM IBMGRP.EMP" ,
"WHERE EMP_ID = '051029'"
'EXECSQL PREPARE S1 FROM :QRY'
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL EXECUTE S1"
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL ROLLBACK"
IF SQLCODE /= 0 THEN CALL SQLCA
"DISCONNECT"
IF SQLCODE /= 0 THEN CALL SQLCA
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
SQLCA:
SAY "SQLCODE = " SQLCODE
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
SAY "SQLSTATE = " SQLSTATE
EXIT
(have a rollback in there so that I do not have to insert the deleted row repeatedly during testing :P )
Output:
SQLCODE = 0
SQLERRMC =
SQLERRP = DSNTZALI
SQLERRD.1= 0
SQLERRD.2= 0
SQLERRD.3= 0
SQLERRD.4= 0
SQLERRD.5= 0
SQLERRD.6= 0
SQLWARN.0=
SQLWARN.1=
SQLWARN.2=
SQLWARN.3=
SQLWARN.4=
SQLWARN.5=
SQLWARN.6=
SQLWARN.7=
SQLWARN.8=
SQLWARN.9=
SQLWARN.10=
SQLSTATE = 00000
2) Non -Select Dynamic SQL with parameter markers:
/* REXX */
EMP_ID="'051029'"
ADDRESS TSO
'SUBCOM DSNREXX'
IF RC = 1 THEN DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX
"CONNECT DB2R"
QRY="DELETE" ,
"FROM IBMGRP.EMP" ,
"WHERE EMP_ID = CAST ( ? AS CHAR(6) )"
'EXECSQL DECLARE C1 CURSOR FOR S1'
IF SQLCODE /= 0 THEN CALL SQLCA
'EXECSQL PREPARE S1 FROM :QRY'
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL EXECUTE S1 USING :EMP_ID"
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL ROLLBACK"
IF SQLCODE /= 0 THEN CALL SQLCA
"DISCONNECT"
IF SQLCODE /= 0 THEN CALL SQLCA
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
SQLCA:
SAY "SQLCODE = " SQLCODE
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
SAY "SQLSTATE = " SQLSTATE
EXIT
Output:
SQLCODE = 0
SQLERRMC =
SQLERRP = DSNTZALI
SQLERRD.1= 0
SQLERRD.2= 0
SQLERRD.3= 0
SQLERRD.4= 0
SQLERRD.5= 0
SQLERRD.6= 0
SQLWARN.0=
SQLWARN.1=
SQLWARN.2=
SQLWARN.3=
SQLWARN.4=
SQLWARN.5=
SQLWARN.6=
SQLWARN.7=
SQLWARN.8=
SQLWARN.9=
SQLWARN.10=
SQLSTATE = 00000
3) Fixed-List Select Dynamic SQL without parameter markers:
/* REXX */
ADDRESS TSO
'SUBCOM DSNREXX'
IF RC = 1 THEN DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX
"CONNECT DB2R"
QRY="SELECT FIRST_NAME,MIDDLE_NAME,LAST_NAME" ,
"FROM IBMGRP.EMP" ,
"WHERE EMP_ID = '139333'"
'EXECSQL DECLARE C1 CURSOR FOR S1'
IF SQLCODE /= 0 THEN CALL SQLCA
'EXECSQL PREPARE S1 FROM :QRY'
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL OPEN C1"
IF SQLCODE /= 0 THEN CALL SQLCA
DO UNTIL(SQLCODE /= 0)
"EXECSQL FETCH C1 INTO :FIRST_NAME, :MIDDLE_NAME :MIND, :LAST_NAME"
IF SQLCODE /= 0 THEN
CALL SQLCA
ELSE
DO
SAY 'FIRST NAME: ' FIRST_NAME
IF MIND == 0 THEN
SAY 'MIDDLE NAME: ' MIDDLE_NAME
ELSE
SAY 'NULL VALUE RECEIVED'
SAY 'LAST NAME: ' LAST_NAME
SAY
END
END
"EXECSQL CLOSE C1"
IF SQLCODE /= 0 THEN CALL SQLCA
"DISCONNECT"
IF SQLCODE /= 0 THEN CALL SQLCA
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
SQLCA:
SAY "SQLCODE = " SQLCODE
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
SAY "SQLSTATE = " SQLSTATE
EXIT
Output:
FIRST NAME: JAGAMOHAN
NULL VALUE RECEIVED
LAST NAME: TRIPATHY
SQLCODE = 100
SQLERRMC =
SQLERRP = DSNXRFN
SQLERRD.1= -110
SQLERRD.2= 0
SQLERRD.3= 0
SQLERRD.4= -1
SQLERRD.5= 0
SQLERRD.6= 0
SQLWARN.0=
SQLWARN.1=
SQLWARN.2=
SQLWARN.3=
SQLWARN.4=
SQLWARN.5=
SQLWARN.6=
SQLWARN.7=
SQLWARN.8=
SQLWARN.9=
SQLWARN.10=
SQLSTATE = 02000
4) Fixed-List Select Dynamic SQL with parameter markers:
/* REXX */
EMP_ID="'051029'"
ADDRESS TSO
'SUBCOM DSNREXX'
IF RC = 1 THEN DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX
"CONNECT DB2R"
QRY="SELECT FIRST_NAME,MIDDLE_NAME,LAST_NAME" ,
"FROM IBMGRP.EMP" ,
"WHERE EMP_ID = CAST ( ? AS CHAR(6) )"
'EXECSQL DECLARE C1 CURSOR FOR S1'
IF SQLCODE /= 0 THEN CALL SQLCA
'EXECSQL PREPARE S1 FROM :QRY'
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL OPEN C1 USING :EMP_ID"
IF SQLCODE /= 0 THEN CALL SQLCA
DO UNTIL(SQLCODE /= 0)
"EXECSQL FETCH C1 INTO :FIRST_NAME, :MIDDLE_NAME :MIND, :LAST_NAME"
IF SQLCODE /= 0 THEN
CALL SQLCA
ELSE
DO
SAY 'FIRST NAME: ' FIRST_NAME
IF MIND == 0 THEN
SAY 'MIDDLE NAME: ' MIDDLE_NAME
ELSE
SAY 'NULL VALUE RECEIVED'
SAY 'LAST NAME: ' LAST_NAME
SAY
END
END
"EXECSQL CLOSE C1"
IF SQLCODE /= 0 THEN CALL SQLCA
"DISCONNECT"
IF SQLCODE /= 0 THEN CALL SQLCA
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
SQLCA:
SAY "SQLCODE = " SQLCODE
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
SAY "SQLSTATE = " SQLSTATE
EXIT
Output:
FIRST NAME: KOBID
MIDDLE NAME: KUMAR
LAST NAME: DAS
SQLCODE = 100
SQLERRMC =
SQLERRP = DSNXRFN
SQLERRD.1= -110
SQLERRD.2= 0
SQLERRD.3= 0
SQLERRD.4= -1
SQLERRD.5= 0
SQLERRD.6= 0
SQLWARN.0=
SQLWARN.1=
SQLWARN.2=
SQLWARN.3=
SQLWARN.4=
SQLWARN.5=
SQLWARN.6=
SQLWARN.7=
SQLWARN.8=
SQLWARN.9=
SQLWARN.10=
SQLSTATE = 02000
5) Varying-List Select Dynamic SQL without parameter markers:
It might appear that the Fixed list as well as the Varying list examples have similar queries but varying list gives you the freedom to choose columns and their order.. say from a panel the user highlights only those columns to be selected which he wants to see and then you have to build the SQL without any prior knowledge of what columns he may ask for and in what order.
/* REXX */
ADDRESS TSO
'SUBCOM DSNREXX'
IF RC = 1 THEN DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX
"CONNECT DB2R"
QRY="SELECT FIRST_NAME,MIDDLE_NAME,LAST_NAME" ,
"FROM IBMGRP.EMP" ,
"WHERE EMP_ID = '139333'"
"EXECSQL DECLARE C1 CURSOR FOR S1"
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :QRY"
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL OPEN C1"
IF SQLCODE /= 0 THEN CALL SQLCA
DO UNTIL(SQLCODE /= 0)
"EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
IF SQLCODE /= 0 THEN
CALL SQLCA
ELSE
DO
DO I=1 TO OUTSQLDA.SQLD
SAY "COLUMN NAME: "OUTSQLDA.I.SQLNAME
SAY "COLUMN TYPE: "OUTSQLDA.I.SQLTYPE
SAY "COLUMN LENG: "OUTSQLDA.I.SQLLEN
SAY "COLUMN INDC: "OUTSQLDA.I.SQLIND
IF OUTSQLDA.I.SQLIND >= 0 THEN
SAY "COLUMN DATA: "OUTSQLDA.I.SQLDATA
ELSE
SAY "NULL DATA RETRIEVED"
SAY
END I
SAY
SAY "END OF RECORD"
SAY
END
END
"EXECSQL CLOSE C1"
IF SQLCODE /= 0 THEN CALL SQLCA
"DISCONNECT"
IF SQLCODE /= 0 THEN CALL SQLCA
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
SQLCA:
SAY "SQLCODE = " SQLCODE
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
SAY "SQLSTATE = " SQLSTATE
EXIT
Output:
COLUMN NAME: FIRST_NAME
COLUMN TYPE: 452
COLUMN LENG: 20
COLUMN INDC: 0
COLUMN DATA: JAGAMOHAN
COLUMN NAME: MIDDLE_NAME
COLUMN TYPE: 453
COLUMN LENG: 20
COLUMN INDC: -1
NULL DATA RETRIEVED
COLUMN NAME: LAST_NAME
COLUMN TYPE: 452
COLUMN LENG: 20
COLUMN INDC: 0
COLUMN DATA: TRIPATHY
END OF RECORD
SQLCODE = 100
SQLERRMC =
SQLERRP = DSNXRFN
SQLERRD.1= -110
SQLERRD.2= 0
SQLERRD.3= 0
SQLERRD.4= -1
SQLERRD.5= 0
SQLERRD.6= 0
SQLWARN.0=
SQLWARN.1=
SQLWARN.2=
SQLWARN.3=
SQLWARN.4=
SQLWARN.5=
SQLWARN.6=
SQLWARN.7=
SQLWARN.8=
SQLWARN.9=
SQLWARN.10=
SQLSTATE = 02000
6) Varying-List Select Dynamic SQL with parameter markers:
/* REXX */
EMP_ID='139333'
ADDRESS TSO
'SUBCOM DSNREXX'
IF RC = 1 THEN DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX
"CONNECT DB2R"
INSQLDA.SQLD = 1
INSQLDA.1.SQLTYPE = 452
INSQLDA.1.SQLLEN = LENGTH(EMP_ID)
INSQLDA.1.SQLDATA = EMP_ID
INSQLDA.1.SQLIND = 0
QRY="SELECT FIRST_NAME,MIDDLE_NAME,LAST_NAME" ,
"FROM IBMGRP.EMP" ,
"WHERE EMP_ID = CAST ( ? AS CHAR(6) )"
'EXECSQL DECLARE C1 CURSOR FOR S1'
IF SQLCODE /= 0 THEN CALL SQLCA
'EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :QRY'
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL OPEN C1 USING DESCRIPTOR :INSQLDA"
IF SQLCODE /= 0 THEN CALL SQLCA
DO UNTIL(SQLCODE /= 0)
"EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
IF SQLCODE /= 0 THEN
CALL SQLCA
ELSE
DO
DO I=1 TO OUTSQLDA.SQLD
SAY "COLUMN NAME: "OUTSQLDA.I.SQLNAME
SAY "COLUMN TYPE: "OUTSQLDA.I.SQLTYPE
SAY "COLUMN LENG: "OUTSQLDA.I.SQLLEN
SAY "COLUMN INDC: "OUTSQLDA.I.SQLIND
IF OUTSQLDA.I.SQLIND >= 0 THEN
SAY "COLUMN DATA: "OUTSQLDA.I.SQLDATA
ELSE
SAY "NULL DATA RETRIEVED"
SAY
END I
SAY
SAY "END OF RECORD"
SAY
END
END
"EXECSQL CLOSE C1"
IF SQLCODE /= 0 THEN CALL SQLCA
"DISCONNECT"
IF SQLCODE /= 0 THEN CALL SQLCA
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
SQLCA:
SAY "SQLCODE = " SQLCODE
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
SAY "SQLSTATE = " SQLSTATE
EXIT
Output:
COLUMN NAME: FIRST_NAME
COLUMN TYPE: 452
COLUMN LENG: 20
COLUMN INDC: 0
COLUMN DATA: JAGAMOHAN
COLUMN NAME: MIDDLE_NAME
COLUMN TYPE: 453
COLUMN LENG: 20
COLUMN INDC: -1
NULL DATA RETRIEVED
COLUMN NAME: LAST_NAME
COLUMN TYPE: 452
COLUMN LENG: 20
COLUMN INDC: 0
COLUMN DATA: TRIPATHY
END OF RECORD
SQLCODE = 100
SQLERRMC =
SQLERRP = DSNXRFN
SQLERRD.1= -110
SQLERRD.2= 0
SQLERRD.3= 0
SQLERRD.4= -1
SQLERRD.5= 0
SQLERRD.6= 0
SQLWARN.0=
SQLWARN.1=
SQLWARN.2=
SQLWARN.3=
SQLWARN.4=
SQLWARN.5=
SQLWARN.6=
SQLWARN.7=
SQLWARN.8=
SQLWARN.9=
SQLWARN.10=
SQLSTATE = 02000
If you know REXX and DB2 the above programs are self-explanatory ;)
And last but not the least being an Engineer myself.. I believe in re-engineering and thus have attempted to give a head start to you.. hopefully it clicked.. Cya :)
Your blogs depicts your image, you will soon become a "Guru"
ReplyDelete