Compress unload data from DB2 table



IBM's flagship relational database management system

Compress unload data from DB2 table

Postby Kevin Q M Cai » Mon May 06, 2013 12:49 pm

Hello All,

As the table I need to unload has large data, so all, is there any option or method to compress the data such that I can minimize the output data.
the JCL I used to unload data from table to flat file as below:
//XXXX       JOB A31,'TEST DSNTIAUL',CLASS=K,MSGCLASS=X,NOTIFY=&SYSUID
//UNLOAD  EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD  SYSOUT=*
//SYSTSIN  DD  *
 DSN SYSTEM(XXX)
 RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=XXXX.REC00,
//            UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG)
//SYSREC01 DD DSN=XXXX.REC01,
//            UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG)
//SYSPUNCH DD DSN=XXXX.PUNCH,
//            UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
//            VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN    DD *
 SELECT * FROM AA.BB;
 SELECT * FROM AA.CC;
/*                                                                           


Thanks & Regards
Kevin
Kevin Q M Cai
 
Posts: 17
Joined: Fri Mar 15, 2013 3:39 pm
Has thanked: 11 times
Been thanked: 0 time

Re: Compress unload data from DB2 table

 

Re: Compress unload data from DB2 table

Postby NicC » Mon May 06, 2013 4:24 pm

I guess you could try running the output file through ADRDSSU with the compress option or running it through PKZIP - if that is available.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2702
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Compress unload data from DB2 table

Postby Anuj Dhawan » Mon May 06, 2013 7:10 pm

Kevin Q M Cai wrote:As the table I need to unload has large data, so all, is there any option or method to compress the data such that I can minimize the output data.
Somehow I belive that you're not looking forward to what the use of words "compress" and "minimize" convey in your statement. Because, once the data is "compress" - how would you use it? Why do you want to "compress" it -- is it just a back? If yes, why not run a IMAGE COPY isntead of unload?

"minimize" is more confusing - if there are 1 Million rows in the table for your selection criteria -- well, there are 1 Million rows, you cant' do much about it. So did you mean you want to save some DASD sapce?

I'm not sure but can't you use some "filters" for the selection criteria, as you've selecte the entire table -- possibly a WHERE clause with some condition will help you, no?
Anuj
Anuj Dhawan
 
Posts: 274
Joined: Mon Feb 25, 2008 3:53 am
Location: Mumbai, India
Has thanked: 6 times
Been thanked: 4 times

Re: Compress unload data from DB2 table

Postby Kevin Q M Cai » Tue May 07, 2013 12:42 pm

Hi Anuj Dhawan,
"minimize" is more confusing - if there are 1 Million rows in the table for your selection criteria -- well, there are 1 Million rows, you cant' do much about it. So did you mean you want to save some DASD sapce?

yeah, you are right, I indeed mean to save some DASD space, and do you have any idea?

Thank you
Kevin
Kevin Q M Cai
 
Posts: 17
Joined: Fri Mar 15, 2013 3:39 pm
Has thanked: 11 times
Been thanked: 0 time

Re: Compress unload data from DB2 table

Postby enrico-sorichetti » Tue May 07, 2013 12:48 pm

I indeed mean to save some DASD space, and do you have any idea?


we could have an idea if you had cared to post a better description of the requirement...

how are You going to use the compressed data ?
where are You going to store the compressed data ?

a two pass approach for <unload> will require a two pass approach for any future utilization,
( so at a certain time You will need the dasd space anyway )

a single pass, USER PROGRAM, compressing data on the fly will need an <equivalent> USER PROGRAM to decompress data for any future utilization.

at some time You will have to make a tradeoff between space and additional resources needed

so You see that what You posted is not enough to provide a good answer.

PS...
check if You really need all the columns, and do selective unloads
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2648
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 132 times

Re: Compress unload data from DB2 table

Postby Kevin Q M Cai » Tue May 07, 2013 3:41 pm

Hi enrico-sorichetti,

Thank you, as you know, if the table I want to unload has 1 million rows or more, the unload output dataset will be very large, so I think if there are methods to compress the unload output dataset so that I can save some space, and when I need to retore these data to table, I can decompress the dataset and then load it to table.

Thanks & Regards
Kevin
Kevin Q M Cai
 
Posts: 17
Joined: Fri Mar 15, 2013 3:39 pm
Has thanked: 11 times
Been thanked: 0 time

Re: Compress unload data from DB2 table

Postby enrico-sorichetti » Tue May 07, 2013 3:53 pm

if the table I want to unload has 1 million rows or more, the unload output dataset will be very large,


1 million rows does not seem very large to me ...

some rule of thumb computations ...

assuming an unloaded row size of 1000 bytes You will need

1000*1000000 bytes
which divided by a NET track capacity of about 55000 bytes
which will give about 18000 tracks
which will mean about 1200 cylinders

I have seen datasets larger than that :geek:

WHY NOT SPEAK TO YOUR STORAGE SUPPORT
for the practices in place ???
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort

These users thanked the author enrico-sorichetti for the post:
Kevin Q M Cai (Wed May 08, 2013 12:27 pm)
enrico-sorichetti
Global moderator
 
Posts: 2648
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 132 times

Re: Compress unload data from DB2 table

Postby dick scherrer » Tue May 07, 2013 9:28 pm

Hello,

How often do you expect to actually use the backup? How many copies of the backup will be retained?

Does your system have virtual tape (v-tape) or extended carts? Several of my clients backup to v-tape or e-cart.

And as Enrico mentioned, yours is not such a large volume ;)
Hope this helps,
d.sch.

These users thanked the author dick scherrer for the post:
Kevin Q M Cai (Wed May 08, 2013 12:26 pm)
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Compress unload data from DB2 table

Postby Kevin Q M Cai » Wed May 08, 2013 9:01 am

Hi enrico-sorichetti, dick scherrer,

amazing :o , I can not imagine how large you said be, but any way, I just want to save some storage, do you have any idea or sample JCL? please help.

Thank you, Thank you

Kevin
Kevin Q M Cai
 
Posts: 17
Joined: Fri Mar 15, 2013 3:39 pm
Has thanked: 11 times
Been thanked: 0 time

Re: Compress unload data from DB2 table

Postby enrico-sorichetti » Wed May 08, 2013 10:52 am

what is that You do not understand when You are suggested to
ASK YOUR STORAGE SUPPORT !

we do not know Your environment, we do not know Your standards ...
You support might know if SMS data compression is implemented and how ...

so You might just have nothing to do apart writing Your jcl according to
the suggestions given by Your support. AMEN
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort

These users thanked the author enrico-sorichetti for the post:
Kevin Q M Cai (Wed May 08, 2013 12:26 pm)
enrico-sorichetti
Global moderator
 
Posts: 2648
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 132 times

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post