IBM MAINFRAME HELP & SUPPORT FORUMS

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

I have REXX code which will extract two columns from a table and put that in an output file. The SQL Code is as below:

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

The properties of out file is like below,

ADDRESS TSO "ALLOCATE DS('"opsname"') DSORG(PS) "
"SPACE(10,10) TRACKS LRECL(255) ",
"RECFM(F,B) "

The problem is I'm getting the output in a odd format, for example like below,

ACT_IND CHAR
BGN_DT DATE
CLM_OFC_CD CHAR
CUST_ID INTEGER
CUST_NUM INTEGER
EMPE_ID_TYP_CD CHAR

and my requirement is like below,
COLUMN NAME Data TYPE
ACT_IND CHAR
BGN_DT Date
CLM_OFC_CD char

Let the col field be length = 20 and data type field = 10

expat · Posted: Tue Aug 19, 2008 7:22 pm Post subject:

Can you try and explain that again, AND Click HERE to see how to use tags to make your post more easily readable

Craq Giegerich · Joined: 19 May 2007 Posts: 1001 Location: Virginia, USA

Try
Code:
SELECT
CAST(NAME AS CHAR(20))AS column_NAME,
CAST(COLTYPE AS CHAR(10)) as data_type
FROM SYSIBM.SYSCOLUMNS,
WHERE TBNAME = 'TABNAM',
AND TBCREATOR = 'TABCRE',
WITH UR

sankarsclm · Joined: 11 Oct 2006 Posts: 6

Hi,

When you are writting to the output file you might be capturing the column name and column type.

Before writting it to a output file you can use the right command. Fix a size for each field in the output file.
Suppose if the table name is set as 20 you can say

RIGHT(TABLENAME,40)

By doing this all the table name will be aligned with 40 characters. Same you can do for type also.

Hope this will be useful to you.

Sankar

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

Its working fine.

Thanks all for the reply.

One more guys.....

How to write two SQL querry in the same Rexx prog so that both of their output will be displayed in the same output DS.