IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 - Unload File Formatting


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Thagun

New User


Joined: 25 Jul 2008
Posts: 15
Location: Chennai

PostPosted: Tue Nov 25, 2008 5:33 pm
Reply with quote

Hi,

I am extracting the data from table into a flat file. This process is successful and i am getting the following output,

Code:
1000ABCDEF25/11/2008
2000ABCDEF25/11/2008
3000ABCDEF25/11/2008
4000ABCDEF25/11/2008

Can we manipulate the same step to give an formatted output like,

EMP NO NAME DATE

Code:
1000      ABCDEF  25/11/2008
2000      ABCDEF  25/11/2008
3000      ABCDEF  25/11/2008
4000      ABCDEF  25/11/2008

by changing the select SQL?

Edited: Please use BBcode when You post some code, that's rather readable...Anuj
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Nov 25, 2008 5:40 pm
Reply with quote

Code:
select col1 || ' ' || col2 || ' '  .....


May be you can give this a try. But I think you'll have to use CHAR(..) if the target column is not character.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Wed Nov 26, 2008 2:31 am
Reply with quote

Hi Thagun,

Your code is been edited to show the proper spacing - I was under the impression that you want a single sapce between the columns when you unload them but "BBcoded" output shows a rather different spacing - please confirm - is that what you need else post your expected output again. Please use BBcode when you post and preview it before posting so that the actual spacing, which you need, remains intact.
Back to top
View user's profile Send private message
Thagun

New User


Joined: 25 Jul 2008
Posts: 15
Location: Chennai

PostPosted: Thu Nov 27, 2008 8:59 am
Reply with quote

Hi,

Thanks for your reply. The BBcode formating done by Anuj is how i want the output to look like. Since i am not familiar with BBcode not able to post the same.

Can you let me know how to get the output in the BBcode format provided by Anuj.

Thanks,
Thagun
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Thu Nov 27, 2008 9:43 am
Reply with quote

Hello Thagun and welcome to the forum,

At the top of the reply panel are the bbTags - one of which is the Code tag.

One easy way to use the code tag is to copy/paste jcl, code, data, or whatever you want to preserve alignment into your post. Then (without dong any editing) highlight tis info and click Code.

Use Preview to see how your post will appear to the forum and when you are satisfied with how your post looks, sUBMIT.
Back to top
View user's profile Send private message
Thagun

New User


Joined: 25 Jul 2008
Posts: 15
Location: Chennai

PostPosted: Thu Nov 27, 2008 10:02 am
Reply with quote

Hi D.sch,

Thanks for your tips on posting in BBcode format.

While doing an unload from Table to file I am getting the output format(in my original post) and but i want the result of unload to look like this; Can we manipulate the control card?

Code:


Emp Name     Emp No     Date

ABCDEF       123456     11/27/2008
ABCDEF       123456     11/27/2008
ABCDEF       123456     11/27/2008
ABCDEF       123456     11/27/2008

Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Thu Nov 27, 2008 10:28 am
Reply with quote

Hello,

It should help us help you if you post the query being used for the unload.
Back to top
View user's profile Send private message
Thagun

New User


Joined: 25 Jul 2008
Posts: 15
Location: Chennai

PostPosted: Thu Nov 27, 2008 11:19 am
Reply with quote

Hi,

This is the query used in my unload job.

Code:


//SYSTSIN  DD  *                                                 
 DSN SYSTEM(DB2B)                                               
 RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL')               
/*                                                               
//SYSIN    DD  *                                                 
 SELECT EMP_NO,EMP_NAME,EMP_JOIN_DT FROM EMPLOYEE     
 WITH UR;                                                       
/*                                                               
//SYSPRINT DD  SYSOUT=*                                         
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu Nov 27, 2008 2:45 pm
Reply with quote

Hi,

What do you want can be "visible" if you open the "unloaded" file with the respective copy-book OR you might try running the query in SPUFI.
Back to top
View user's profile Send private message
Thagun

New User


Joined: 25 Jul 2008
Posts: 15
Location: Chennai

PostPosted: Thu Nov 27, 2008 3:01 pm
Reply with quote

Hi,

It will be viewed outside the mainframe so using copybook or SQL option is ruled out. I am planning to format the unload file using a SYNCSORT. Can you let me know how it can be done. I have a working JCL to do it using ICEMAN but its not allowed.

Can you help in SYNCSORT.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Nov 27, 2008 3:32 pm
Reply with quote

Quote:
SQL option is ruled out

I m sorry,I m not getting this point. What's wrong in formatting this in SQL itself?
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu Nov 27, 2008 4:01 pm
Reply with quote

Hi,

You are opening your cards very slowly..

Quote:
It will be viewed outside the mainframe
In what "way" it will be viewd ? Another allication, note pad form, excel..

Quote:
I have a working JCL to do it using ICEMAN but its not allowed
ICEMAN invokes the SORT installed at your shop, it's just another way - change ICEMAN to SORT, job should work fine.
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India

PostPosted: Thu Nov 27, 2008 4:18 pm
Reply with quote

Thagun,

Quote:
Can you help in SYNCSORT


Yep, this can be done. You can make INREC to reformat the fields. You will have to use X for adding spaces. I do not have access to mainframes.

Please go for the above method if the SQL thng is not working.

Quote:
I have a working JCL to do it using ICEMAN but its not allowed


Not allowed to use JCL or Is the system not allowing?
Back to top
View user's profile Send private message
Thagun

New User


Joined: 25 Jul 2008
Posts: 15
Location: Chennai

PostPosted: Thu Nov 27, 2008 5:16 pm
Reply with quote

Hi,

Apologies for the confusion and Thanks to everyone for the tips. I used SYNCSORT instead of ICEMAN and the JCL is working fine formatting.

Regards,
Thagun
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India

PostPosted: Thu Nov 27, 2008 6:09 pm
Reply with quote

Thagun,

Quote:
Apologies for the confusion and Thanks to everyone for the tips.


No worries and you are welcome.

Quote:
I used SYNCSORT instead of ICEMAN and the JCL is working fine formatting


Ideally it should not make any difference.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Nov 27, 2008 8:45 pm
Reply with quote

Thagun,

I personally believe it would have been better if you'd done the formatting in SQL in this case. Why would you need an additional pass of data in sort?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts Load new table with Old unload - DB2 DB2 6
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
Search our Forums:

Back to Top