IBM MAINFRAME HELP & SUPPORT FORUMS

siva102 · Joined: 28 Nov 2007 Posts: 40 Location: Chennai

Hi all,

I am doing a rexx prog where if u will give the table name as input it will display all the properties like column names, col numbers, index names etc.

the prob is i need to give more than one sql querries, like below,

SQLSTMT="SELECT NAME, TYPENAME",
"FROM SYSIBM.SYSCOLUMNS",
"WHERE TBNAME = '"TABNAM"'",
"AND TBCREATOR = '"TABCRE"'"

SQLSTMT="SELECT COUNT(NAME)",
"FROM SYSIBM.SYSCOLUMNS"
"WHERE TBNAME = '"TABNAM"'",
"AND TBCREATOR = '"TABCRE"'"

I dont know how to give two querries in a rexx prog. My DBpart of the rexx prog is like below,

------------------------------------------------------------------------------------
DO S=1 TO NUM.0
/*TRACE I */
PARSE VAR NUM.S TABNAM TABCRE JUNK
ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT DSN "
SQLSTMT="select CAST(NAME AS CHAR(20)),",
"CAST(COLTYPE AS CHAR(10))",
"FROM SYSIBM.SYSCOLUMNS",
"WHERE TBNAME = '"TABNAM"'",
"AND TBCREATOR = '"TABCRE"'"
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
ADDRESS DSNREXX "EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :SQLSTMT"
ADDRESS DSNREXX "EXECSQL OPEN C1"
DO UNTIL(SQLCODE Ź= 0)
------------------------------------------------------------------------------

So anyone can please tell me how to give more than one querry in this prog.

expat · Posted: Wed Aug 20, 2008 12:14 pm Post subject:

Do you really need to know the count from the second SQL, could you just not get that information from the original SQL from DSNREXX ?

siva102 · Joined: 28 Nov 2007 Posts: 40 Location: Chennai

Hi Expat,

Thanks for replying.

That count Querry was just an example. I need to run more than 4 querries in that program.

More over i just able to get the values But in an unformatted manner. like below,

CUST_ID INTEGER
CUST_NUM INTEGER
EMPE_ID_TYP_CD CHAR
END_DT DATE
INTK_MTHD_CD CHAR
MET_ID INTEGER
RSRCH_CD CHAR
UPDT_OPER_ID CHAR
UPDT_TS TIMESTMP
WRKFLW_IND CHAR
13
XUD1CUST
XUD2CUST
XUDCCUST
XUDCCUST F3696DBA CUST_ID 1 1 A N
XUD1CUST F3696DBA CUST_NUM 2 1 A N
XUDCCUST F3696DBC CUST_ID 1 1 A N
XUD1CUST F3696DBC CUST_NUM 2 1 A N
XUDCCUST F3696DBD CUST_ID 1 1 A N

could you please telll me how to do fomatting to this data means giving the heading to each and every coloumn sao that i ll get the output like below,

Columns Data_type col_no Index

CUST_ID INTEGER 13 XUD1CUST
CUST_NUM INTEGER XUD2CUST
EMPE_ID_TYP_CD CHAR XUDCCUST
END_DT DATE
INTK_MTHD_CD CHAR
MET_ID INTEGER
RSRCH_CD CHAR
UPDT_OPER_ID CHAR
UPDT_TS TIMESTMP
WRKFLW_IND CHAR