Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> TSO/ISPF
View previous topic :: :: View next topic  
Author Message
vgmurthy

New User


Joined: 19 Feb 2008
Posts: 8
Location: Chennai

PostPosted: Mon Feb 25, 2008 12:29 pm    Post subject: Need to unload some % ( say 10% ) of rows only
Reply with quote

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.
Back to top
View user's profile Send private message

ofer71

Global Moderator


Joined: 27 Dec 2005
Posts: 2360
Location: Israel

PostPosted: Mon Feb 25, 2008 12:46 pm    Post subject:
Reply with quote

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

O.
Back to top
View user's profile Send private message
vgmurthy

New User


Joined: 19 Feb 2008
Posts: 8
Location: Chennai

PostPosted: Mon Feb 25, 2008 12:49 pm    Post subject:
Reply with quote

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

Murthy
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Mon Feb 25, 2008 1:23 pm    Post subject:
Reply with quote

Hi,

Just 10% ...what type of business rule is this?
Back to top
View user's profile Send private message
vgmurthy

New User


Joined: 19 Feb 2008
Posts: 8
Location: Chennai

PostPosted: Mon Feb 25, 2008 1:46 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Feb 25, 2008 7:46 pm    Post subject:
Reply with quote

Sounds like you are trying to get test data.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Mon Feb 25, 2008 7:52 pm    Post subject:
Reply with quote

Have you looked into the SAMPLE parameter in sort's OUTFIL command?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Mon Feb 25, 2008 8:16 pm    Post subject:
Reply with quote

vgmurthy wrote:
Just make a note here.. my delete is only based on the total number of the rows already existing in the table...
Hi Murthy,

(One alternate way) If You need count of the rows, COUNT(*) should work to get the no. of rows. Well, I don't deal much with DB2, however, I don't think there is some direct command to get some % from DB2 table unless some 'criteria' is defined to unload that table which eventually comes out to be 10.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Feb 25, 2008 9:20 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
vgmurthy

New User


Joined: 19 Feb 2008
Posts: 8
Location: Chennai

PostPosted: Tue Feb 26, 2008 12:13 pm    Post subject: Reply to: Need to unload some % ( say 10% ) of rows only
Reply with quote

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.
http://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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Feb 26, 2008 8:35 pm    Post subject: Reply to: Need to unload some % ( say 10% ) of rows only
Reply with quote

Hello Murthy,

Thank you for posting your solution icon_smile.gif

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> TSO/ISPF All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us