Sample Db2 REXX application
You can use a REXX application to accept a table name as input and produce a SELECT, INSERT, or UPDATE SQL statement or a LOAD utility statement for the specified table as output.
The following example shows a complete Db2 REXX application named DRAW. DRAW must be invoked from the command line of an ISPF edit session. DRAW takes a table or view name as input and produces a SELECT, INSERT, or UPDATE SQL statement or a LOAD utility control statement that includes the columns of the table as output.
DRAW parameters:
- object-name
- The name of the table or view for which DRAW builds an SQL statement
or utility control statement. The name can be a one-, two-, or three-part
name. The table or view to which object-name refers
must exist before DRAW can run.
object-name is a required parameter.
- SSID=ssid
- Specifies the name of the local Db2 subsystem.
S can be used as an abbreviation for SSID.
If you invoke DRAW from the command line of the edit session in SPUFI, SSID=ssid is an optional parameter. DRAW uses the subsystem ID from the DB2I Defaults panel.
- TYPE=operation-type
- The type of statement that DRAW builds.
T can be used as an abbreviation for TYPE.
operation-type has one of the following values:
- SELECT
- Builds a SELECT statement in which the result table contains all
columns of object-name.
S can be used as an abbreviation for SELECT.
- INSERT
- Builds a template for an INSERT statement that inserts values
into all columns of object-name. The template
contains comments that indicate where the user can place column values.
I can be used as an abbreviation for INSERT.
- UPDATE
- Builds a template for an UPDATE statement that updates columns
of object-name. The template contains comments
that indicate where the user can place column values and qualify the
update operation for selected rows.
U can be used as an abbreviation for UPDATE.
- LOAD
- Builds a template for a LOAD utility control statement for object-name.
L can be used as an abbreviation for LOAD.
TYPE=operation-type is an optional parameter. The default is TYPE=SELECT.
DRAW data sets:
- Edit data set
- The data set from which you issue the DRAW command when you are in an ISPF edit session. If you issue the DRAW command from a SPUFI session, this data set is the data set that you specify in field 1 of the main SPUFI panel (DSNESP01). The output from the DRAW command goes into this data set.
DRAW return codes:
- Return code
- Meaning
- 0
- Successful completion.
- 12
- An error occurred when DRAW edited the input file.
- 20
- One of the following errors occurred:
- No input parameters were specified.
- One of the input parameters was not valid.
- An SQL error occurred when the output statement was generated.
Examples of DRAW invocation:
Generate a SELECT statement for table DSN8C10.EMP at the local subsystem. Use the default DB2I subsystem ID.
The DRAW invocation is:
DRAW DSN8C10.EMP (TYPE=SELECTThe output is:
SELECT "EMPNO" , "FIRSTNME" , "MIDINIT" , "LASTNAME" , "WORKDEPT" ,
"PHONENO" , "HIREDATE" , "JOB" , "EDLEVEL" , "SEX" , "BIRTHDATE" ,
"SALARY" , "BONUS" , "COMM"
FROM DSN8C10.EMPGenerate a template for an INSERT statement that inserts values into table DSN8C10.EMP at location SAN_JOSE. The local subsystem ID is DSN.
The DRAW invocation is:
DRAW SAN_JOSE.DSN8C10.EMP (TYPE=INSERT SSID=DSNThe output is:
INSERT INTO SAN_JOSE.DSN8C10.EMP ( "EMPNO" , "FIRSTNME" , "MIDINIT" ,
"LASTNAME" , "WORKDEPT" , "PHONENO" , "HIREDATE" , "JOB" ,
"EDLEVEL" , "SEX" , "BIRTHDATE" , "SALARY" , "BONUS" , "COMM" )
VALUES (
-- ENTER VALUES BELOW COLUMN NAME DATA TYPE
, -- EMPNO CHAR(6) NOT NULL
, -- FIRSTNME VARCHAR(12) NOT NULL
, -- MIDINIT CHAR(1) NOT NULL
, -- LASTNAME VARCHAR(15) NOT NULL
, -- WORKDEPT CHAR(3)
, -- PHONENO CHAR(4)
, -- HIREDATE DATE
, -- JOB CHAR(8)
, -- EDLEVEL SMALLINT
, -- SEX CHAR(1)
, -- BIRTHDATE DATE
, -- SALARY DECIMAL(9,2)
, -- BONUS DECIMAL(9,2)
) -- COMM DECIMAL(9,2)Generate a template for an UPDATE statement that updates values of table DSN8C10.EMP. The local subsystem ID is DSN.
The DRAW invocation is:
DRAW DSN8C10.EMP (TYPE=UPDATE SSID=DSNThe output is:
UPDATE DSN8C10.EMP SET
-- COLUMN NAME ENTER VALUES BELOW DATA TYPE
"EMPNO"= -- CHAR(6) NOT NULL
, "FIRSTNME"= -- VARCHAR(12) NOT NULL
, "MIDINIT"= -- CHAR(1) NOT NULL
, "LASTNAME"= -- VARCHAR(15) NOT NULL
, "WORKDEPT"= -- CHAR(3)
, "PHONENO"= -- CHAR(4)
, "HIREDATE"= -- DATE
, "JOB"= -- CHAR(8)
, "EDLEVEL"= -- SMALLINT
, "SEX"= -- CHAR(1)
, "BIRTHDATE"= -- DATE
, "SALARY"= -- DECIMAL(9,2)
, "BONUS"= -- DECIMAL(9,2)
, "COMM"= -- DECIMAL(9,2)
WHEREGenerate a LOAD control statement to load values into table DSN8C10.EMP. The local subsystem ID is DSN.
The draw invocation is:
DRAW DSN8C10.EMP (TYPE=LOAD SSID=DSNThe output is:
LOAD DATA INDDN SYSREC INTO TABLE DSN8C10.EMP
( "EMPNO" POSITION( 1) CHAR(6)
, "FIRSTNME" POSITION( 8) VARCHAR
, "MIDINIT" POSITION( 21) CHAR(1)
, "LASTNAME" POSITION( 23) VARCHAR
, "WORKDEPT" POSITION( 39) CHAR(3)
NULLIF( 39)='?'
, "PHONENO" POSITION( 43) CHAR(4)
NULLIF( 43)='?'
, "HIREDATE" POSITION( 48) DATE EXTERNAL
NULLIF( 48)='?'
, "JOB" POSITION( 59) CHAR(8)
NULLIF( 59)='?'
, "EDLEVEL" POSITION( 68) SMALLINT
NULLIF( 68)='?'
, "SEX" POSITION( 71) CHAR(1)
NULLIF( 71)='?'
, "BIRTHDATE" POSITION( 73) DATE EXTERNAL
NULLIF( 73)='?'
, "SALARY" POSITION( 84) DECIMAL EXTERNAL(9,2)
NULLIF( 84)='?'
, "BONUS" POSITION( 90) DECIMAL EXTERNAL(9,2)
NULLIF( 90)='?'
, "COMM" POSITION( 96) DECIMAL EXTERNAL(9,2)
NULLIF( 96)='?'
)DRAW source code:
/* REXX ***************************************************************/
L1 = WHEREAMI()
/*
DRAW creates basic SQL queries by retrieving the description of a
table. You must specify the name of the table or view to be queried.
You can specify the type of query you want to compose. You might need
to specify the name of the DB2 subsystem.
>>--DRAW-----tablename-----|---------------------------|-------><
|-(-|-Ssid=subsystem-name-|-|
| +-Select-+ |
|-Type=-|-Insert-|----|
|-Update-|
+--Load--+
Ssid=subsystem-name
subsystem-name specified the name of a DB2 subsystem.
Select
Composes a basic query for selecting data from the columns of a
table or view. If TYPE is not specified, SELECT is assumed.
Using SELECT with the DRAW command produces a query that would
retrieve all rows and all columns from the specified table. You
can then modify the query as needed.
A SELECT query of EMP composed by DRAW looks like this:
SELECT "EMPNO" , "FIRSTNME" , "MIDINIT" , "LASTNAME" , "WORKDEPT" ,
"PHONENO" , "HIREDATE" , "JOB" , "EDLEVEL" , "SEX" , "BIRTHDATE" ,
"SALARY" , "BONUS" , "COMM"
FROM DSN8C10.EMP
If you include a location qualifier, the query looks like this:
SELECT "EMPNO" , "FIRSTNME" , "MIDINIT" , "LASTNAME" , "WORKDEPT" ,
"PHONENO" , "HIREDATE" , "JOB" , "EDLEVEL" , "SEX" , "BIRTHDATE" ,
"SALARY" , "BONUS" , "COMM"
FROM STLEC1.DSN8C10.EMP To use this SELECT query, type the other clauses you need. If
you are selecting from more than one table, use a DRAW command
for each table name you want represented.
Insert
Composes a basic query to insert data into the columns of a table
or view.
The following example shows an INSERT query of EMP that
DRAW composed:
INSERT INTO DSN8C10.EMP ( "EMPNO" , "FIRSTNME" , "MIDINIT" , "LASTNAME" ,
"WORKDEPT" , "PHONENO" , "HIREDATE" , "JOB" , "EDLEVEL" , "SEX" ,
"BIRTHDATE" , "SALARY" , "BONUS" , "COMM" )
VALUES (
-- ENTER VALUES BELOW COLUMN NAME DATA TYPE
, -- EMPNO CHAR(6) NOT NULL
, -- FIRSTNME VARCHAR(12) NOT NULL
, -- MIDINIT CHAR(1) NOT NULL
, -- LASTNAME VARCHAR(15) NOT NULL
, -- WORKDEPT CHAR(3)
, -- PHONENO CHAR(4)
, -- HIREDATE DATE
, -- JOB CHAR(8)
, -- EDLEVEL SMALLINT
, -- SEX CHAR(1)
, -- BIRTHDATE DATE
, -- SALARY DECIMAL(9,2)
, -- BONUS DECIMAL(9,2)
) -- COMM DECIMAL(9,2)
To insert values into EMP, type values to the left of the
column names.
Update
Composes a basic query to change the data in a table or view.
The following example shows an UPDATE query of EMP composed
by DRAW:UPDATE DSN8C10.EMP SET
-- COLUMN NAME ENTER VALUES BELOW DATA TYPE
"EMPNO"= -- CHAR(6) NOT NULL
, "FIRSTNME"= -- VARCHAR(12) NOT NULL
, "MIDINIT"= -- CHAR(1) NOT NULL
, "LASTNAME"= -- VARCHAR(15) NOT NULL
, "WORKDEPT"= -- CHAR(3)
, "PHONENO"= -- CHAR(4)
, "HIREDATE"= -- DATE
, "JOB"= -- CHAR(8)
, "EDLEVEL"= -- SMALLINT
, "SEX"= -- CHAR(1)
, "BIRTHDATE"= -- DATE
, "SALARY"= -- DECIMAL(9,2)
, "BONUS"= -- DECIMAL(9,2)
, "COMM"= -- DECIMAL(9,2)
WHERE
To use this UPDATE query, type the changes you want to make to
the right of the column names, and delete the lines you do not
need. Be sure to complete the WHERE clause.
Load
Composes a load statement to load the data in a table.
The following example shows a LOAD statement of EMP composed
by DRAW:
LOAD DATA INDDN SYSREC INTO TABLE DSN8C10 .EMP
( "EMPNO" POSITION( 1) CHAR(6)
, "FIRSTNME" POSITION( 8) VARCHAR
, "MIDINIT" POSITION( 21) CHAR(1)
, "LASTNAME" POSITION( 23) VARCHAR
, "WORKDEPT" POSITION( 39) CHAR(3)
NULLIF( 39)='?'
, "PHONENO" POSITION( 43) CHAR(4)
NULLIF( 43)='?'
, "HIREDATE" POSITION( 48) DATE EXTERNAL
NULLIF( 48)='?'
, "JOB" POSITION( 59) CHAR(8)
NULLIF( 59)='?'
, "EDLEVEL" POSITION( 68) SMALLINT
NULLIF( 68)='?'
, "SEX" POSITION( 71) CHAR(1)
NULLIF( 71)='?'
, "BIRTHDATE" POSITION( 73) DATE EXTERNAL
NULLIF( 73)='?'
, "SALARY" POSITION( 84) DECIMAL EXTERNAL(9,2)
NULLIF( 84)='?'
, "BONUS" POSITION( 90) DECIMAL EXTERNAL(9,2)
NULLIF( 90)='?'
, "COMM" POSITION( 96) DECIMAL EXTERNAL(9,2)
NULLIF( 96)='?'
) To use this LOAD statement, type the changes you want to make,
and delete the lines you do not need.
*/
L2 = WHEREAMI()
/**********************************************************************/
/* TRACE ?R */
/**********************************************************************/
Address ISPEXEC
"ISREDIT MACRO (ARGS) NOPROCESS"
If ARGS = "" Then
Do
Do I = L1+2 To L2-2;Say SourceLine(I);End
Exit (20)
End
Parse Upper Var Args Table "(" Parms
Parms = Translate(Parms," ",",")
Type = "SELECT" /* Default */
SSID = "" /* Default */
"VGET (DSNEOV01)"
If RC = 0 Then SSID = DSNEOV01
If (Parms <> "") Then
Do Until(Parms = "")
Parse Var Parms Var "=" Value Parms
If Var = "T" | Var = "TYPE" Then Type = Value
Else
If Var = "S" | Var = "SSID" Then SSID = Value
Else
Exit (20)
End
"CONTROL ERRORS RETURN"
"ISREDIT (LEFTBND,RIGHTBND) = BOUNDS"
"ISREDIT (LRECL) = DATA_WIDTH" /*LRECL*/
BndSize = RightBnd - LeftBnd + 1
If BndSize > 72 Then BndSize = 72
"ISREDIT PROCESS DEST"
Select
When rc = 0 Then
'ISREDIT (ZDEST) = LINENUM .ZDEST'
When rc <= 8 Then /* No A or B entered */
Do
zedsmsg = 'Enter "A"/"B" line cmd'
zedlmsg = 'DRAW requires an "A" or "B" line command'
'SETMSG MSG(ISRZ001)'
Exit 12
End
When rc < 20 Then /* Conflicting line commands - edit sets message */
Exit 12
When rc = 20 Then
zdest = 0
Otherwise
Exit 12
EndSQLTYPE. = "UNKNOWN TYPE"
VCHTYPE = 448; SQLTYPES.VCHTYPE = 'VARCHAR'
CHTYPE = 452; SQLTYPES.CHTYPE = 'CHAR'
LVCHTYPE = 456; SQLTYPES.LVCHTYPE = 'VARCHAR'
VGRTYP = 464; SQLTYPES.VGRTYP = 'VARGRAPHIC'
GRTYP = 468; SQLTYPES.GRTYP = 'GRAPHIC'
LVGRTYP = 472; SQLTYPES.LVGRTYP = 'VARGRAPHIC'
FLOTYPE = 480; SQLTYPES.FLOTYPE = 'FLOAT'
DCTYPE = 484; SQLTYPES.DCTYPE = 'DECIMAL'
INTYPE = 496; SQLTYPES.INTYPE = 'INTEGER'
SMTYPE = 500; SQLTYPES.SMTYPE = 'SMALLINT'
DATYPE = 384; SQLTYPES.DATYPE = 'DATE'
TITYPE = 388; SQLTYPES.TITYPE = 'TIME'
TSTYPE = 392; SQLTYPES.TSTYPE = 'TIMESTAMP'
Address TSO "SUBCOM DSNREXX" /* HOST CMD ENV AVAILABLE? */
IF RC THEN /* NO, LET'S MAKE ONE */
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX') /* ADD HOST CMD ENV */
Address DSNREXX "CONNECT" SSID
If SQLCODE ^= 0 Then Call SQLCA
Address DSNREXX "EXECSQL DESCRIBE TABLE :TABLE INTO :SQLDA"
If SQLCODE ^= 0 Then Call SQLCA
Address DSNREXX "EXECSQL COMMIT"
Address DSNREXX "DISCONNECT"
If SQLCODE ^= 0 Then Call SQLCA
Select
When (Left(Type,1) = "S") Then
Call DrawSelect
When (Left(Type,1) = "I") Then
Call DrawInsert
When (Left(Type,1) = "U") Then
Call DrawUpdate
When (Left(Type,1) = "L") Then
Call DrawLoad
Otherwise EXIT (20)
End
Do I = LINE.0 To 1 By -1
LINE = COPIES(" ",LEFTBND-1)||LINE.I
'ISREDIT LINE_AFTER 'zdest' = DATALINE (Line)'
End
line1 = zdest + 1
'ISREDIT CURSOR = 'line1 0
Exit/**********************************************************************/
WHEREAMI:; RETURN SIGL
/**********************************************************************/
/* Draw SELECT */
/**********************************************************************/
DrawSelect:
Line.0 = 0
Line = "SELECT"
Do I = 1 To SQLDA.SQLD
If I > 1 Then Line = Line ','
ColName = '"'SQLDA.I.SQLNAME'"'
Null = SQLDA.I.SQLTYPE//2
If Length(Line)+Length(ColName)+LENGTH(" ,") > BndSize THEN
Do
L = Line.0 + 1; Line.0 = L
Line.L = Line
Line = " "
End
Line = Line ColName
End I
If Line ^= "" Then
Do
L = Line.0 + 1; Line.0 = L
Line.L = Line
Line = " "
End
L = Line.0 + 1; Line.0 = L
Line.L = "FROM" TABLE
Return
/**********************************************************************/
/* Draw INSERT */
/**********************************************************************/
DrawInsert:
Line.0 = 0
Line = "INSERT INTO" TABLE "("
Do I = 1 To SQLDA.SQLD
If I > 1 Then Line = Line ','
ColName = '"'SQLDA.I.SQLNAME'"'
If Length(Line)+Length(ColName) > BndSize THEN
Do
L = Line.0 + 1; Line.0 = L
Line.L = Line
Line = " "
End
Line = Line ColName
If I = SQLDA.SQLD Then Line = Line ')'
End I
If Line ^= "" Then
Do
L = Line.0 + 1; Line.0 = L
Line.L = Line
Line = " "
End L = Line.0 + 1; Line.0 = L
Line.L = " VALUES ("
L = Line.0 + 1; Line.0 = L
Line.L = ,
"-- ENTER VALUES BELOW COLUMN NAME DATA TYPE"
Do I = 1 To SQLDA.SQLD
If SQLDA.SQLD > 1 & I < SQLDA.SQLD Then
Line = " , --"
Else
Line = " ) --"
Line = Line Left(SQLDA.I.SQLNAME,18)
Type = SQLDA.I.SQLTYPE
Null = Type//2
If Null Then Type = Type - 1
Len = SQLDA.I.SQLLEN
Prcsn = SQLDA.I.SQLLEN.SQLPRECISION
Scale = SQLDA.I.SQLLEN.SQLSCALE
Select
When (Type = CHTYPE ,
|Type = VCHTYPE ,
|Type = LVCHTYPE ,
|Type = GRTYP ,
|Type = VGRTYP ,
|Type = LVGRTYP ) THEN
Type = SQLTYPES.Type"("STRIP(LEN)")"
When (Type = FLOTYPE ) THEN
Type = SQLTYPES.Type"("STRIP((LEN*4)-11) ")"
When (Type = DCTYPE ) THEN
Type = SQLTYPES.Type"("STRIP(PRCSN)","STRIP(SCALE)")"
Otherwise
Type = SQLTYPES.Type
End
Line = Line Type
If Null = 0 Then
Line = Line "NOT NULL"
L = Line.0 + 1; Line.0 = L
Line.L = Line
End I
Return/**********************************************************************/
/* Draw UPDATE */
/**********************************************************************/
DrawUpdate:
Line.0 = 1
Line.1 = "UPDATE" TABLE "SET"
L = Line.0 + 1; Line.0 = L
Line.L = ,
"-- COLUMN NAME ENTER VALUES BELOW DATA TYPE"
Do I = 1 To SQLDA.SQLD
If I = 1 Then
Line = " "
Else
Line = " ,"
Line = Line Left('"'SQLDA.I.SQLNAME'"=',21)
Line = Line Left(" ",20)
Type = SQLDA.I.SQLTYPE
Null = Type//2
If Null Then Type = Type - 1
Len = SQLDA.I.SQLLEN
Prcsn = SQLDA.I.SQLLEN.SQLPRECISION
Scale = SQLDA.I.SQLLEN.SQLSCALE
Select
When (Type = CHTYPE ,
|Type = VCHTYPE ,
|Type = LVCHTYPE ,
|Type = GRTYP ,
|Type = VGRTYP ,
|Type = LVGRTYP ) THEN
Type = SQLTYPES.Type"("STRIP(LEN)")"
When (Type = FLOTYPE ) THEN
Type = SQLTYPES.Type"("STRIP((LEN*4)-11) ")"
When (Type = DCTYPE ) THEN
Type = SQLTYPES.Type"("STRIP(PRCSN)","STRIP(SCALE)")"
Otherwise
Type = SQLTYPES.Type
End
Line = Line "--" Type
If Null = 0 Then
Line = Line "NOT NULL"
L = Line.0 + 1; Line.0 = L
Line.L = Line
End I
L = Line.0 + 1; Line.0 = L
Line.L = "WHERE"
Return/**********************************************************************/
/* Draw LOAD */
/**********************************************************************/
DrawLoad:
Line.0 = 1
Line.1 = "LOAD DATA INDDN SYSREC INTO TABLE" TABLE
Position = 1
Do I = 1 To SQLDA.SQLD
If I = 1 Then
Line = " ("
Else
Line = " ,"
Line = Line Left('"'SQLDA.I.SQLNAME'"',20)
Line = Line "POSITION("RIGHT(POSITION,5)")"
Type = SQLDA.I.SQLTYPE
Null = Type//2
If Null Then Type = Type - 1
Len = SQLDA.I.SQLLEN
Prcsn = SQLDA.I.SQLLEN.SQLPRECISION
Scale = SQLDA.I.SQLLEN.SQLSCALE
Select
When (Type = CHTYPE ,
|Type = GRTYP ) THEN
Type = SQLTYPES.Type"("STRIP(LEN)")"
When (Type = FLOTYPE ) THEN
Type = SQLTYPES.Type"("STRIP((LEN*4)-11) ")"
When (Type = DCTYPE ) THEN
Do
Type = SQLTYPES.Type "EXTERNAL"
Type = Type"("STRIP(PRCSN)","STRIP(SCALE)")"
Len = (PRCSN+2)%2
End
When (Type = DATYPE ,
|Type = TITYPE ,
|Type = TSTYPE ) THEN
Type = SQLTYPES.Type "EXTERNAL"
Otherwise
Type = SQLTYPES.Type
End
If (Type = GRTYP ,
|Type = VGRTYP ,
|Type = LVGRTYP ) THEN
Len = Len * 2
If (Type = VCHTYPE ,
|Type = LVCHTYPE ,
|Type = VGRTYP ,
|Type = LVGRTYP ) THEN
Len = Len + 2
Line = Line Type
L = Line.0 + 1; Line.0 = L Line.L = Line
If Null = 1 Then
Do
Line = " "
Line = Line Left('',20)
Line = Line " NULLIF("RIGHT(POSITION,5)")='?'"
L = Line.0 + 1; Line.0 = L
Line.L = Line
End
Position = Position + Len + 1
End I
L = Line.0 + 1; Line.0 = L
Line.L = " )"
Return
/**********************************************************************/
/* Display SQLCA */
/**********************************************************************/
SQLCA:
"ISREDIT LINE_AFTER "zdest" = MSGLINE 'SQLSTATE="SQLSTATE"'"
"ISREDIT LINE_AFTER "zdest" = MSGLINE 'SQLWARN ="SQLWARN.0",",
|| SQLWARN.1",",
|| SQLWARN.2",",
|| SQLWARN.3",",
|| SQLWARN.4",",
|| SQLWARN.5",",
|| SQLWARN.6",",
|| SQLWARN.7",",
|| SQLWARN.8",",
|| SQLWARN.9",",
|| SQLWARN.10"'"
"ISREDIT LINE_AFTER "zdest" = MSGLINE 'SQLERRD ="SQLERRD.1",",
|| SQLERRD.2",",
|| SQLERRD.3",",
|| SQLERRD.4",",
|| SQLERRD.5",",
|| SQLERRD.6"'"
"ISREDIT LINE_AFTER "zdest" = MSGLINE 'SQLERRP ="SQLERRP"'"
"ISREDIT LINE_AFTER "zdest" = MSGLINE 'SQLERRMC ="SQLERRMC"'"
"ISREDIT LINE_AFTER "zdest" = MSGLINE 'SQLCODE ="SQLCODE"'"
Exit 20