IBM Mainframe Forum

by **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:

Select all

//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

by **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.

by **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?

by **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

by **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

by **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

by **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 ???

by **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

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

Hi enrico-sorichetti, dick scherrer,

amazing

, 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

by **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

IBM Mainframe Forum

Compress unload data from DB2 table

Compress unload data from DB2 table

Re: Compress unload data from DB2 table

Re: Compress unload data from DB2 table

Re: Compress unload data from DB2 table

Re: Compress unload data from DB2 table

Re: Compress unload data from DB2 table

Re: Compress unload data from DB2 table

Re: Compress unload data from DB2 table

Re: Compress unload data from DB2 table

Re: Compress unload data from DB2 table