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
 

 

Issues facing while unloading db2 table in csv format

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

New User


Joined: 17 Mar 2010
Posts: 1
Location: Bangalore

PostPosted: Wed Aug 17, 2011 9:42 pm    Post subject: Issues facing while unloading db2 table in csv format
Reply with quote

Hi All,

I am trying to Unload few fields of a DB2 table using DSNTIAUL. I need to
unload into a flat file in a comma separated format.

I have used a query similar to the below one for unloading.

Select Empno||','||Emp_name||','||Emp_addr||','||Emp_city from table.

In this only Empno is integer and all other fields are char. But this query is failing with SQL code -171.
I have tried removing the first field (ie. interger one) and it worked fine.

I am wondering why this query is failing when I am using an Integer field in the query. Can anyone please throw some light into this?

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

dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Aug 17, 2011 9:57 pm    Post subject:
Reply with quote

Quote:
Select Empno||','||Emp_name||','||Emp_addr||','||Emp_city from table.


that is a attempt to concatenate all columns,
but that means ever column needs to be char
and is not a good way to create a comma delimited dataset.

*have been busy at work
and most of the threads lately have been so depressing
have not bothered to comment.)

i should have said something on the post from which you copied this sql.


suggest:
Code:

Select Empno
      ,char(',',1)
      ,Emp_name
      ,char(',',1)
      ,Emp_addr
      ,char(',',1)
      ,Emp_city
 from table


if you have decimal or binary columns, you can also cast them
to cobol pic9.
Back to top
View user's profile Send private message
BrantleyL

New User


Joined: 03 Jun 2010
Posts: 14
Location: Dallas TX

PostPosted: Thu Aug 18, 2011 1:43 am    Post subject:
Reply with quote

Another way would be to select the fields into a working-storage layout with the commas imbeded (casting them as necessary) and write it out.

Another way would be to 'String' them together after selecting.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Aug 18, 2011 3:49 am    Post subject:
Reply with quote

BrantleyL,

I often yell because I am tired of people not reading the question
or information provided.

original post by TS:

Quote:
I am trying to Unload few fields of a DB2 table using DSNTIAUL


The TS is not using a program to select and format the data,
he is using a utility...
not much working storage available.

the idea of using a utility with the flexibility of using sql to format the columns,
is so that a program does not have to be written.
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 SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts output data in CSV Format janmejay COBOL Programming 8 Sat Oct 15, 2016 2:20 pm


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