Unload a table with column delimiters



IBM's flagship relational database management system

Unload a table with column delimiters

Postby Prasanna G » Mon Apr 06, 2015 8:29 pm

Hi

I would like to unload a DB2 table with each of the columns delimited by ; (semicolon). All decimal and integer values should be in readable format in the unloaded file. I tried to use DSNUPROC with the below control card
DELIMITED COLDEL ';' DECPT '.'   
PUNCHDDN SYSPUNCH               
UNLDDN SYSREC00 EBCDIC           


But the character columns are flanked by " (double quote). Also the length of the unloaded file is in VB format with 32756 as record length. I want the unloaded file to be created in actual length of the table.

Can someone please help me in achieving the above?

Thanks
Prasanna G.
User avatar
Prasanna G
 
Posts: 59
Joined: Tue Apr 12, 2011 9:49 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Unload a table with column delimiters

 

Re: Unload a table with column delimiters

Postby Prasanna G » Tue Apr 07, 2015 5:36 am

Can any of the DB2 gurus please help me to achieve the above?

Thanks
Prasanna G.
User avatar
Prasanna G
 
Posts: 59
Joined: Tue Apr 12, 2011 9:49 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Unload a table with column delimiters

Postby pranav283 » Tue Apr 07, 2015 5:10 pm

Hi Prasanna,

I guess you should try unloading the table using IKJEFT01 instead of DSNUPROC because when you do so, you have the liberty to format the columns according to your needs (not sure of the limitations).
This can be achieved using PARMS parameter in the unload control card for IKJEFT01.

Also, if we are not giving the LRECL of the o/p dataset, it will always take it as VB depending on the size of the table (unloaded columns + 4).
So, I don't think we should worry too much about the LRECL of the o/p dataset.

Thanks,
Pranav
pranav283
 
Posts: 35
Joined: Sat Aug 30, 2014 3:52 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Unload a table with column delimiters

Postby Prasanna G » Tue Apr 07, 2015 6:55 pm

Hi Pranav

Can you please share me the PARM snippet to use in IKJEFT01 for getting the delimiters between the columns and converting Decimal/Integer columns to zoned decimal while unloading?

Thanks
Prasanna G.
User avatar
Prasanna G
 
Posts: 59
Joined: Tue Apr 12, 2011 9:49 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Unload a table with column delimiters

Postby pranav283 » Tue Apr 07, 2015 7:59 pm

Hi Prasanna,

One way to perform an unload using IKJEFT01 is by passing this info in your SYSTSIN through DSN commands :

DSN SYSTEM(<< the DB2 ID at your site>>)

RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL')-
LIB(' specify the lib which contains the load DSNTIAUL)
END

This will quietly unload the the table to your SYSREC ds and place the structure to the SYSPUNCH ds.
The importance of using PARMS('SQL') comes into picture when you want to unload the table using a set of SQL statements that you have to specify in the SYSIN control card.
What I know is that one can specify option like PARMS('ALIGN()') to format the o/p.
However, I am still not sure about the code to place in the PARM option to meet your requirement. I guess we need to dig more to find out how this can be achieved.

But yes, to convert decimal/integer values (I assume you meant PD) to ZD, you can do it by using OUTREC BUILD option of SORT in the next step.

Thanks,
Pranav
pranav283
 
Posts: 35
Joined: Sat Aug 30, 2014 3:52 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Unload a table with column delimiters

Postby Prasanna G » Tue Apr 07, 2015 8:51 pm

Yeah.. Even I thought of using a Sort step for the zoned decimal conversion. But would like to avoid that extra step, if it is possible to get it unloaded from the table itself
User avatar
Prasanna G
 
Posts: 59
Joined: Tue Apr 12, 2011 9:49 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Unload a table with column delimiters

Postby pranav283 » Tue Apr 07, 2015 8:58 pm

Not sure of that at this moment. As discussed, we have to dig in a little more !! :roll:
pranav283
 
Posts: 35
Joined: Sat Aug 30, 2014 3:52 pm
Has thanked: 1 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post