/* 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 DSN8710.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.DSN8710.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 DSN8710.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. See DB2 SQL Reference for more information on
INSERT queries.
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 DSN8710.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 don t
need. Be sure to complete the WHERE clause. For information on
writing queries to update data, refer to DB2 SQL Reference.
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 DSN8710.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 don t need. For information on writing
queries to update data, refer to
DB2 Utility Guide and Reference.
*/

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
    ZDEST = 0
  Otherwise
    Exit 12
End

SQLTYPE. = "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