Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 6968
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: 6968
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 Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us