/* REXX */
ARG SUBSYS TBNAME
/* */
/* CONTRIBUTED BY : SATHYARAM SANNASI */
/* EMAIL : SATHYARAM AT DB2CLICK DOT COM */
/* */

/***************************************************/
/* THIS PROGRAM SIMULATES DB2 LUW'S'DESCRIBE TABLE'*/
/* CALLING FORMAT */
/* */
/* DESCTAB <SUBSYSTEM> <TABLENAME> */
/* */
/* SUBSYSTEM IS MANDATORY */
/* TABLENAME CAN BE CREATOR.NAME (IF CREATOR */
/* IS NOT SPECIFIED, THE CURRENT AUTHID WILL */
/* BE USED */
/**************************************************/

COL_COUNT=0 /* TO DISPLAY THE NUMBER OF TABLES */
LINENO=0 /* OUTPUT LINE NUMBER */
/* MAIN */
IF TBNAME='' THEN /* SCHEMA */ DO
SAY ' TABLENAME MUST BE SPECIFIED'
EXIT
END
ELSE
DO
IF INDEX(TBNAME,'.')=0 THEN
DO
TSCHEMA=USER
TNAME="'"TBNAME"'"
END
ELSE
DO
TSCHEMA="'"SUBSTR(TBNAME,1,INDEX(TBNAME,'.')-1)"'"
TNAME="'"SUBSTR(TBNAME,INDEX(TBNAME,'.')+1)"'"
END
END
IF SUBSYS='' THEN
DO
SAY "SUBSYSTEM MUST BE SPECIFIED"
EXIT
END
ADDRESS TSO "SUBCOM DSNREXX" /* HOST CMD ENV AVAILABLE ? */
IF RC THEN S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "EXECSQL SET CURRENT PACKAGESET='DSNREXUR'"
ADDRESS DSNREXX "CONNECT" SUBSYS /* CONNECT */
CALL SQLCA CONNECT
/* TLIST STATEMENT */
SQLSTMT1="SELECT CHAR(NAME,30),CHAR(CREATOR,8), TYPE, CREATEDTS"
SQLSTMT1=SQLSTMT1 " FROM SYSIBM.SYSTABLES WHERE "
SQLSTMT1=SQLSTMT1 " CREATOR ="TSCHEMA" ORDER BY CREATOR, NAME"
SQLSTMT1="SELECT CHAR(NAME,30), CHAR(COLTYPE,15)," "CHAR(LENGTH)," ,
"CHAR(SCALE), CHAR(NULLS,5) FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR" ,
"="TSCHEMA "AND TBNAME ="TNAME "ORDER BY COLNO "
/* DECLARE CURSOR */
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
CALL SQLCA DECLARE
/* PREPARE STATEMENT */
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT1"
CALL SQLCA PREPARE
/* GET SQLDA DATA */
ADDRESS DSNREXX "EXECSQL DESCRIBE S1 INTO :OUTSQLDA"
CALL SQLCA DESCRIBE
/* OPEN CURSOR */
ADDRESS DSNREXX "EXECSQL OPEN C1"
CALL SQLCA OPEN

/* FETCH DATA */
ADDRESS DSNREXX "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
OUT.1=" "
OUT.2=" "
OUT.3='COLUMN NAME ' 'DATATYPE ' 'LENGTH ' ,
'SCALE ' 'NULLS'
OUT.4= '------------------------------' '---------------' '---------' ,
'-------' '-----'
DO UNTIL(SQLCODE ^= 0)
IF (SQALCODE=100) THEN
SAY ELSE
DO
LINENO=COL_COUNT+5
OUT.LINENO=OUTSQLDA.1.SQLDATA OUTSQLDA.2.SQLDATA ,
OUTSQLDA.3.SQLDATA " " OUTSQLDA.4.SQLDATA " "OUTSQLDA.5.SQLDATA
ADDRESS DSNREXX "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
IF SUBSTR(OUT.LINENO,1,18)="OUTSQLDA.1.SQLDATA"
THEN
OUT.LINENO=" "
ELSE
COL_COUNT=COL_COUNT+1
END
END
LINENO=LINENO+1
OUT.LINENO=" "
LINENO=LINENO+1
OUT.LINENO=" "
LINENO=LINENO+1
OUT.LINENO= " " COL_COUNT COLUMNS IN TABLE TSCHEMA"."TNAME
"ALLOC DDN(DESCOUT) NEW UNIT(DISK) SPACE(3) TRACKS REUSE "
"EXECIO * DISKW DESCOUT (STEM OUT. FINIS"
ADDRESS ISPEXEC "LMINIT DATAID(DSID) DDNAME(DESCOUT)"
ADDRESS ISPEXEC "BROWSE DATAID(&DSID)"
ADDRESS ISPEXEC "LMFREE DATAID(&DSID)"
EXIT
SAY " COLUMN NAME: " OUTSQLDA.I.SQLNAME
SAY " COLUMN TYPE: " OUTSQLDA.I.SQLTYPE
SQLCA:
STEP=ARG(1)
IF SQLCODE ^= 0 THEN
DO
SAY "STEP " STEP
SAY "SQLCODE " SQLCODE
SAY "SQLSTATE" SQLSTATE
EXIT
END
RETURN