Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Export columns from DB2 table with pipe delimiter

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

New User


Joined: 26 Feb 2016
Posts: 8
Location: India

PostPosted: Sun Mar 22, 2020 12:03 pm    Post subject: Export columns from DB2 table with pipe delimiter
Reply with quote

Hello,

I have a requirement where I need to join multiple table and need to extract data with pipe ('|') delimiter.
I have used QMF to run the query and try to export data using
EXPORT DATA TO TEST (DATAFORMAT=CSV
but this provides comma separated dataset.

Also tried to execute SPUFI with concatenation operator like below:

select
strip(char(COL1)) ||'|'
||strip(char(COL2)) ||'|'
||strip(char(COL3)) ||'|'
||strip(VARCHAR_FORMAT(CT.DTE,'DD/MM/YYYY')) ||'|'
||strip(VARCHAR_FORMAT(CT.DTE,'DD/MM/YYYY')) ||'|'
||strip(VARCHAR_FORMAT(CT.DTE,'DD/MM/YYYY')) ||'|'
||strip(char(COL7)) ||'|'
||strip(char(COL8)) ||'|'

but this is not helping as the string length is becoming more than 80 characters.

Any help will be appreciated.

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

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2319
Location: Hampshire, UK

PostPosted: Sun Mar 22, 2020 3:24 pm    Post subject: Reply to: Export columns from DB2 table with pipe delimiter
Reply with quote

Why do you have the same date 3 times? Would producing it only once reduce your length to <80 bytes?
If your data is going to be more than 80 bytes whatever you try then it is going to be more than 80 bytes and you just have to live with it.
Back to top
View user's profile Send private message
Nilanjan Sikdar

New User


Joined: 26 Feb 2016
Posts: 8
Location: India

PostPosted: Sun Mar 22, 2020 4:48 pm    Post subject:
Reply with quote

Hi,

Those are 3 different type of Date fields. Like Creation Date, Update date, Authorize date etc.

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

Active Member


Joined: 29 Apr 2008
Posts: 719
Location: Maryland

PostPosted: Sun Mar 22, 2020 9:06 pm    Post subject:
Reply with quote

Use DB2 Unload utility with parameter
FORMAT DELIMITED ...

Example 9 from this IBM’s RTFM
Back to top
View user's profile Send private message
Nilanjan Sikdar

New User


Joined: 26 Feb 2016
Posts: 8
Location: India

PostPosted: Sun Mar 22, 2020 10:30 pm    Post subject:
Reply with quote

Hi sergeyken,

Thanks for the link but it seems the utility works for only 1 table. I have the output from 4 different tables joined together.

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

Active Member


Joined: 29 Apr 2008
Posts: 719
Location: Maryland

PostPosted: Sun Mar 22, 2020 11:05 pm    Post subject:
Reply with quote

Nilanjan Sikdar wrote:
Hi sergeyken,

Thanks for the link but it seems the utility works for only 1 table. I have the output from 4 different tables joined together.

Thanks,
Nilanjan


Utilities do work also with VIEW, and JOIN.

It is always more useful to RTFM carefully, rather than moaning in the forums.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2356
Location: NY,USA

PostPosted: Mon Mar 23, 2020 1:55 am    Post subject:
Reply with quote

Nilanjan Sikdar wrote:
Hi,

Those are 3 different type of Date fields. Like Creation Date, Update date, Authorize date etc.

Thanks,
Nilanjan

By referring the San column ? At least use the correct column names if you needed to get different values for three different dates .

Second , what’s the error message for the original problem ? To get such report it’s always best to write a program and create a report that you need which allows you more flexibility and easy maintenance when it comes to future changes
Back to top
View user's profile Send private message
Nilanjan Sikdar

New User


Joined: 26 Feb 2016
Posts: 8
Location: India

PostPosted: Mon Mar 23, 2020 3:22 pm    Post subject:
Reply with quote

Hi Rohit,

That was a typo and I mentioned same date column for all.
This is a one off requirement hence we are trying the do using SQL extract.
When I run the query in SPUFI/QMF there is no error but output is having one NULL column. When I reduce the number of columns it is coming perfect.

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

Global Moderator


Joined: 10 May 2007
Posts: 2319
Location: Hampshire, UK

PostPosted: Mon Mar 23, 2020 5:40 pm    Post subject: Reply to: Export columns from DB2 table with pipe delimiter
Reply with quote

How come there was a typo? You should be using copy/paste. Also - show your full SQL and sample table DDL/data.
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 719
Location: Maryland

PostPosted: Mon Mar 23, 2020 5:44 pm    Post subject:
Reply with quote

Use the DB2 High Performance Unload for z/OS for the most flexible, and fastest DB2 table/view/join unload, with/without delimiters.

Db2 High Performance Unload for z/OS V5.1 documentation
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2356
Location: NY,USA

PostPosted: Mon Mar 23, 2020 8:19 pm    Post subject:
Reply with quote

Quote:
When I run the query in SPUFI/QMF there is no error but output is having one NULL column. When I reduce the number of columns it is coming perfect.

Try handling the NULL using VALUE or COALESCE function.
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 719
Location: Maryland

PostPosted: Tue Mar 24, 2020 10:10 pm    Post subject:
Reply with quote

12.gif
Back to top
View user's profile Send private message
Nilanjan Sikdar

New User


Joined: 26 Feb 2016
Posts: 8
Location: India

PostPosted: Tue Mar 24, 2020 10:49 pm    Post subject:
Reply with quote

Sergeyken

I was going through the material mentioned above but didn't get a clear syntax for multiple view join under different table space.

If possible then can you please provide a sample with syntax.

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

Active Member


Joined: 29 Apr 2008
Posts: 719
Location: Maryland

PostPosted: Wed Mar 25, 2020 3:57 pm    Post subject:
Reply with quote

Nilanjan Sikdar wrote:
Sergeyken

I was going through the material mentioned above but didn't get a clear syntax for multiple view join under different table space.

If possible then can you please provide a sample with syntax.

Thanks,
Nilanjan


Use command "FIND" to search in the mentioned manual for the following topics:

Example: Unloading a partitioned table space
Example: Unloading partition subsets by using multiple SELECT statements
Example: Unloading a partitioned table space with one output file per partition in DB2 FORCE when an unsupported SELECT statement is used


etc.

I recommend you to start with some very basic examples: how at all you can unload a single table in DELIMITED format?
When done, you may continue to more complicated examples.

It is always a very bad idea to start with resolving any global problem while having no idea about each of required simple steps. (Unless you expect that someone would give you a ready-to-copy-and-paste solution.)
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 Global temporary table Akriti Mishra DB2 1 Fri Apr 03, 2020 3:28 am
No new posts COBOL - Array result in single variab... vinu78 COBOL Programming 3 Thu Feb 20, 2020 5:29 pm
No new posts SFTP to a Unix Pipe file yuvan All Other Mainframe Topics 4 Tue Jan 28, 2020 9:04 pm
No new posts Variable length(Pipe delimter) to Fix... SreekanthMada DFSORT/ICETOOL 8 Mon Jan 06, 2020 4:44 pm
No new posts OMIT first and last column of a pipe-... karthikb_itpro DFSORT/ICETOOL 1 Fri Nov 01, 2019 8:24 pm

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