Need to unload some % ( say 10% ) of rows only

vgmurthy · New User Joined: 19 Feb 2008 Posts: 8 Location: Chennai

Hi,
I have a requirement, where I need to unload some % ( say 10% ) of rows only from a table. Fetch first n rows only gives me only the top n rows, but I need to unload only 10% of the total num of rows avlb at the table. Am not sure if I there is any option already available while unloading. Can you please suggest some of the ways where I can unload just 10% of the total rows availble in the table.

Thanks,
Murthy.

ofer71 · Posted: Mon Feb 25, 2008 12:46 pm

Are you talking about ISPF Table, DB2 Table or other kind of table?

O.

vgmurthy · New User Joined: 19 Feb 2008 Posts: 8 Location: Chennai

My Bad..
Db2 table unload I was referring to.

Murthy

Anuj Dhawan · Posted: Mon Feb 25, 2008 1:23 pm

Hi,

Just 10% ...what type of business rule is this?

vgmurthy · New User Joined: 19 Feb 2008 Posts: 8 Location: Chennai

Hi Anuj.... thanks for your reply..
Well all I need is to delete some 10 % of the data that were already existing in the table. What I do is this...
Unload the data -- get the count from that flat file ( DFSORT) -- calculate the 10% of the count (Again DFSORT ) -- Using the value on the ctrl of a sort ( In SKIP REC ) -- get the new flat file with 90% of the data & load back to the table... This is the way that I do... But I guess there should be some other logic that helps me to load only 10% of the total rows that are already existing.. Just make a note here.. my delete is only based on the total number of the rows already existing in the table...

Thanks,
Murthy.

stodolas · Posted: Mon Feb 25, 2008 7:46 pm

Sounds like you are trying to get test data.

CICS Guy · Posted: Mon Feb 25, 2008 7:52 pm

Have you looked into the SAMPLE parameter in sort's OUTFIL command?

Anuj Dhawan · Posted: Mon Feb 25, 2008 8:16 pm

dick scherrer · Posted: Mon Feb 25, 2008 9:20 pm

Hello,

As suggested, yo cold look at sample.

You might also "split" the "input" into 10 output files (9 would be defined as DUMMY) and the one file that was actually created would have 10% of the original data.

vgmurthy · New User Joined: 19 Feb 2008 Posts: 8 Location: Chennai

Hi All,
Thanks much for your suggestions. Finally I got the solution. Here is the code for your reference. Itz tested & working 100%.

PROC Used to UNLOAD:: DSNUPROC.
Use the below ctrl card at your SYSIN.
UNLOAD TABLESPACE DBKW1603.TPKW1603
PUNCHDDN SYSPUNCH UNLDDN SYSREC
FROM TABLE ADMF001.TBKW1605
SAMPLE 75
Please note that SAMPLE 75 is the key here. Whatever number that you plugin here gives you the % of data to be extracted. Just give the table space name & DB Name as appropriate.

For more details please visit the below link.
publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/DSNUGK10/2.30.4?DT=20070125001207

Thanks again for your suggestions,
Have a great day,
Murthy.

dick scherrer · Posted: Tue Feb 26, 2008 8:35 pm

Hello Murthy,

Thank you for posting your solution

d