IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Export columns from DB2 table with pipe delimiter


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Nilanjan Sikdar

New User


Joined: 26 Feb 2016
Posts: 9
Location: India

PostPosted: Sun Mar 22, 2020 12:03 pm
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: 2455
Location: Hampshire, UK

PostPosted: Sun Mar 22, 2020 3:24 pm
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: 9
Location: India

PostPosted: Sun Mar 22, 2020 4:48 pm
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
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1201

PostPosted: Sun Mar 22, 2020 9:06 pm
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: 9
Location: India

PostPosted: Sun Mar 22, 2020 10:30 pm
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
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1201

PostPosted: Sun Mar 22, 2020 11:05 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2789
Location: NYC,USA

PostPosted: Mon Mar 23, 2020 1:55 am
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: 9
Location: India

PostPosted: Mon Mar 23, 2020 3:22 pm
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: 2455
Location: Hampshire, UK

PostPosted: Mon Mar 23, 2020 5:40 pm
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
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1201

PostPosted: Mon Mar 23, 2020 5:44 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2789
Location: NYC,USA

PostPosted: Mon Mar 23, 2020 8:19 pm
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
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1201

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

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

New User


Joined: 26 Feb 2016
Posts: 9
Location: India

PostPosted: Tue Mar 24, 2020 10:49 pm
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
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1201

PostPosted: Wed Mar 25, 2020 3:57 pm
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
Nilanjan Sikdar

New User


Joined: 26 Feb 2016
Posts: 9
Location: India

PostPosted: Tue Apr 07, 2020 5:00 pm
Reply with quote

Thanks all for your help.
I have achieved the solution in a round about way. Extracted CSV formatted data from mainframe using QMF.
Then I used Microsoft excel to convert the comma to pipe. I need to take care about different columns so that original data shouldn't get converted with excel predefined format.

Thanks,
Nilanjan
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts create table with more than 32k recor... DB2 1
This topic is locked: you cannot edit posts or make replies. create table with more than 32k recor... IBM Tools 1
This topic is locked: you cannot edit posts or make replies. COBOL-Common routine that contains al... COBOL Programming 4
No new posts Create table from List CLIST & REXX 8
No new posts Map VSAM Dataset to a Table/Tablespace DB2 5
Search our Forums:

Back to Top