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
 

 

DB2 - Unload File Formatting

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
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    Post subject: DB2 - Unload File Formatting
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: 2161
Location: @my desk

PostPosted: Tue Nov 25, 2008 5:40 pm    Post subject:
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

Senior Member


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

PostPosted: Wed Nov 26, 2008 2:31 am    Post subject:
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    Post subject: Unload Formatting
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

Site Director


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

PostPosted: Thu Nov 27, 2008 9:43 am    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Thu Nov 27, 2008 10:28 am    Post subject:
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    Post subject:
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

Senior Member


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

PostPosted: Thu Nov 27, 2008 2:45 pm    Post subject:
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    Post subject:
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: 2161
Location: @my desk

PostPosted: Thu Nov 27, 2008 3:32 pm    Post subject:
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

Senior Member


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

PostPosted: Thu Nov 27, 2008 4:01 pm    Post subject:
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: 1288
Location: Chennai, India

PostPosted: Thu Nov 27, 2008 4:18 pm    Post subject: Reply to: DB2 - Unload File Formatting
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    Post subject:
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: 1288
Location: Chennai, India

PostPosted: Thu Nov 27, 2008 6:09 pm    Post subject: Reply to: DB2 - Unload File Formatting
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: 2161
Location: @my desk

PostPosted: Thu Nov 27, 2008 8:45 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 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 CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm


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