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
 

 

Unload into a delimited file with join condition

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

New User


Joined: 02 Nov 2006
Posts: 43
Location: London

PostPosted: Thu Aug 12, 2010 8:05 pm    Post subject: Unload into a delimited file with join condition
Reply with quote

Hi,

Couple of questions on logic clarification and need help on my approach here:

I need to UNLOAD a table into a comma-delimited text file and while unloading there should be a table join condition in the WHERE clause of the table.

I tried unloading using DSNUPROC, and it unloaded the complete table perfectly into delimited text file, but it comes with it's disadvantage that it cannot work with table joins? Is that correct ?

Next, I am trying with DSNUTILA, but it seems SELECT statement in SYSIN has a character limit of 72 characters? (Correct me if I am wrong here)What if I need to explicitly mention all the columns of a big file...


Also, how can I mention the column delimiter in SELECT sql without explicitly separating the Selected columns . Eg. if A B C are column names, my SQL for pipe-delmited output is something like :
SELECT A,'|',B,'|',C from sample;
Please suggest a solution?

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

arien

New User


Joined: 02 Nov 2006
Posts: 43
Location: London

PostPosted: Thu Aug 12, 2010 10:04 pm    Post subject:
Reply with quote

Does that mean I need to write the parsing logic in a program instead icon_sad.gif ??
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 Aug 12, 2010 10:47 pm    Post subject:
Reply with quote

Hello,

Suggest you post a few rows from both tables (relevant columns only) and the delimited output you want from the sample data.
Back to top
View user's profile Send private message
arien

New User


Joined: 02 Nov 2006
Posts: 43
Location: London

PostPosted: Thu Aug 12, 2010 11:09 pm    Post subject:
Reply with quote

Thanks D.sch.

Just for example. My sample data has 2 input tables - EMP and SCENARIO

EMP data:
Code:
EMPNO ScNo    Salary  Name                Update_date
1000    1          9000    Erick Verghese  2008-01-10 01:03:00.123456
2000    2          1000    Leo Fiterstein     2007-01-01 01:03:00.123456
3000    3          10000  Diane Lee          2010-01-01 01:03:00.123456

SCENARIO data:
Code:
ScNo      ScName       
1            Has 1 child             
2            Is Single         
3            Has no children



My SELECT statement should contain all data from SCENARIO table where update_date in EMP table > 2008-01-01.

Hence my SELECT statement will be :
SELECT * from SCENARIO where ScNo IN (SELECT ScNo from EMP where UPDATE_DATE > '2008-01-01-00.00.00.000000')

And I need my output in the following pipe-delimited format(only 2 records from SCENARIO table qualify):

1|Has 1 Child
3|Has no children

Hope this helps. As I see from DSNUPROC examples, this nested SELECT is not possible.. pls advise.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Aug 13, 2010 12:37 am    Post subject:
Reply with quote

I have always used APPENDIX1.4.1 Running DSNTIAUL
where I could simply code any sql select.

in your case I would code:

SELECT ScNo
,'|'
, ScName
FROM ...

in the case of ScNo, depending upon the data type, you may need a cast statement.

the nice thing about DSNTIAUL, you can always run the same sql in spufi.
Back to top
View user's profile Send private message
arien

New User


Joined: 02 Nov 2006
Posts: 43
Location: London

PostPosted: Fri Aug 13, 2010 12:47 am    Post subject:
Reply with quote

Thanks Db..

I could unload the table into a pipe-delimited flat file. But this SELECT statement has introduced spaces in between columns.

Eg: my output became:
1 | Has 1 Child
3 | Has no children


And second problem that I am facing is that the SMALLINT, DECIMAL format fields have become unreadable. any way around this? can I convert the output into ASCII format?
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: Fri Aug 13, 2010 12:53 am    Post subject:
Reply with quote

Hello,

You want your output to be plain text. The smallint and decimal fields are unreadable because they are still in the internal numeric format.

When you create the plain text delimited file, an ftp of the data to a unix or win-based system will automatically handle the ebcdic->ascii conversion.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Fri Aug 13, 2010 1:00 am    Post subject:
Reply with quote

Code:
SELECT ScNo
,'|'
, ScName


The literal '|' is a varchar field you have to cast as a char field!
Back to top
View user's profile Send private message
arien

New User


Joined: 02 Nov 2006
Posts: 43
Location: London

PostPosted: Mon Aug 16, 2010 10:34 pm    Post subject:
Reply with quote

DSNUTIAL solves the issue to an extent. There is another utility that I came to know HPU - High Performance Unload. It runs the program INZUTILB. I'm trying to get my output based on this. I will update the forum.
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
This topic is locked: you cannot edit posts or make replies. Need help on If condition continuatio... ashok_uddaraju CLIST & REXX 4 Fri May 19, 2017 8:55 am
No new posts Converting Variable block file to Fix... bhavana yalavarthi DFSORT/ICETOOL 10 Tue May 16, 2017 2:20 pm
No new posts Getting error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm
No new posts Issue with NDM process to transmit ES... chetanambi All Other Mainframe Topics 6 Wed May 03, 2017 10:52 am
No new posts File processing vishwakotin Mainframe Interview Questions 9 Fri Apr 28, 2017 11:38 am


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