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
 

 

Unable to convert COMP-3 data to displayable format in batch

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

New User


Joined: 20 Sep 2007
Posts: 73
Location: India

PostPosted: Mon Nov 22, 2010 9:22 pm    Post subject: Unable to convert COMP-3 data to displayable format in batch
Reply with quote

Hi,

I have a table TABLE1 which has a column SEQ_NO that is declared decimal & the DCLGEN generated shows it as equivalent to COMP-3.
I decided to run a batch SQL program using utility IKJEFT1B. The query execution is successful but the value returned in SEQ_NO is in COMP-3 format & hence un-readable. I have tried to use the cast function but without success.
Kindly let me know if I can get around this problem using some modifications to the SQL. One option is executing another utility to convert the PD format to ZD format in the next step. But handling it within query will be of immense help.
I have executed the same query in SPUFI & the result was in displayable format. So I was just wondering the reason the same query was throwing a different result in batch.
Also it would be great if you can provide me a way to print the column headers to output as well.

Thanks
Back to top
View user's profile Send private message

Rahul Agrawal

New User


Joined: 19 Jul 2010
Posts: 9
Location: Noida

PostPosted: Mon Nov 22, 2010 9:48 pm    Post subject:
Reply with quote

Hi,

In batch, SEQ_NO stored is in hex format (packed decimal) and hence can be seen by doing "HEX ON" in the o/p dataset.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Nov 22, 2010 9:55 pm    Post subject:
Reply with quote

Hello,

Depending on what you mean by "a batch SQL program", you should change the code to produce the desired output rather than packed-decimal output. You can also control the column headers in your code.

Unless there is really no program at all in which case something else will be needed. . .
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Mon Nov 22, 2010 10:08 pm    Post subject:
Reply with quote

Quote:
I have tried to use the cast function but without success.


That doesn't tell us a lot, show us what you used and the results.
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 73
Location: India

PostPosted: Tue Nov 23, 2010 11:44 am    Post subject:
Reply with quote

Hi,
The JCL I have used to get the O/P is:

Code:
//STEP01 EXEC PGM=IKJEFT1B
//STEPLIB DD DSN=<pre-qualifier>.DSNLOAD,DISP=SHR
//        DD DSN=<pre-qualifier>.DSNEXIT,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSDUMP DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN D *
   DSN SYSTEM(sub-system)
   RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL')
/*
//SYSREC00 DD DSN=<dataset name>,DISP=(NEW,CATLG,DELETE),
//            SPACE=(CYL,(2,2),RLSE)
//SYSIN DD *
   SELECT MODEL_NO,SEQ_NO
   FROM creator.TABLE1
   WHERE DISPATCH_DATE < '99999999';
/*


A sample O/P I got on execution of the above query is:

Code:
XXXXXXXXX           ð
YYYYYYYYY           @
ZZZZZZZZZ           æ
AAAAAAAAA           @


The SEQ_NO displayed above is not in displayable format. Hence I need to:
1. Convert the SEQ_NO into displayable format & display it
2. No headers are printed in the O/P dataset. I want to print the column names as the headers as printed if the above query is run in SPUFI.

I tried using CAST function in the above JCL but without success. The modified SQL using cast function is:

Code:
SELECT MODEL_NO,CAST(SEQ_NO AS INT)
FROM creator.TABLE1
WHERE DISPATCH_DATE < '99999999';


Thanks
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Nov 23, 2010 1:51 pm    Post subject:
Reply with quote

try cast( seq_no as char(10)) or char(seq_no)
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Nov 23, 2010 2:02 pm    Post subject:
Reply with quote

and before you ask GuyC why to you should CAST as Char,
RTFM and see what is the datatype for Interger.

If you knew some of the fundamentals, you would not have somany problems.
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 73
Location: India

PostPosted: Tue Nov 23, 2010 2:07 pm    Post subject:
Reply with quote

Hi Guy C,

Thanks for the help. I tried both CHAR function & CAST function as you stated above & both worked great. Thanks for the help once again.

I would also like to know if you can help me in printing the column names for each of the columns as displayed in SPUFI.

I tried using AS operator for printing column names but to no success.

Thanks
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Nov 23, 2010 2:30 pm    Post subject:
Reply with quote

something along the line of:

Code:

SELECT 'Model No '
     , 'Seq_no '
FROM SYSIBM.SYSDUMMY1
UNION
SELECT MODEL_NO,CAST(SEQ_NO AS INT)
FROM creator.TABLE1
WHERE DISPATCH_DATE < '99999999';

would accomplish what you want.
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 73
Location: India

PostPosted: Tue Nov 23, 2010 2:55 pm    Post subject:
Reply with quote

Hi Dick,

I checked you way out & it's working as I intended. Thanks a lot.
By the way, is there any way we can alter the way DSNTIAUL processes (by passing parms maybe) to force it to print column headers?

Thanks for the help once again.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Nov 23, 2010 2:58 pm    Post subject:
Reply with quote

never could find any, that's why I came up with the UNION.
that does not mean there are not any, just that I was too lazy to find them.
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 How does a called pgm know if its cal... Graeme Westerman COBOL Programming 4 Tue Nov 29, 2016 9:25 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Convert +9999999999999.99 to S9(12)V9(3) vnktrrd DFSORT/ICETOOL 8 Thu Nov 17, 2016 8:15 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm


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