Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need to do formatting

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
siva102

New User


Joined: 28 Nov 2007
Posts: 64
Location: Chennai

PostPosted: Tue Aug 19, 2008 7:16 pm    Post subject: Need to do formatting
Reply with quote

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
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Tue Aug 19, 2008 7:22 pm    Post subject:
Reply with quote

Can you try and explain that again, AND Click HERE to see how to use tags to make your post more easily readable
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Aug 19, 2008 9:07 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
sankarsclm

New User


Joined: 11 Oct 2006
Posts: 4

PostPosted: Wed Aug 20, 2008 10:16 am    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
siva102

New User


Joined: 28 Nov 2007
Posts: 64
Location: Chennai

PostPosted: Wed Aug 20, 2008 11:20 am    Post subject: Reply to: Need to do formatting
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Formatting VB File Learncoholic DFSORT/ICETOOL 3 Mon Mar 20, 2017 12:29 pm
No new posts Formatting data from Excel to mainframe kishpra JCL & VSAM 5 Fri Feb 19, 2016 7:12 pm
No new posts Formatting date in DB2 manugeorge2004 DB2 4 Thu Dec 03, 2015 3:18 am
No new posts Formatting a output to required form ... LOKESH DAIVAM All Other Mainframe Topics 2 Thu Aug 13, 2015 12:14 pm
No new posts Formatting Names Upper to Lower Exclu... shajeeth DFSORT/ICETOOL 13 Wed Dec 10, 2014 10:17 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us