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
 

 

How to unload rows from table?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Help-Me-Out

New User


Joined: 09 Dec 2006
Posts: 56
Location: Pune

PostPosted: Fri Sep 28, 2012 10:03 am    Post subject: How to unload rows from table?
Reply with quote

Hi,

I have requirement where i need to unload the data from the table with the help of input file which has accounts. I trying to explore the better option to get it done either thru rexx or platinum unload, my preference is platinum unload.

Requirement -
I am trying to retrive the rows from the table with some conditions. I have list of accounts in the flat file. I want to unload the accounts macthing in the file. File has around 11 million records.

I am thinking to acheive this with the IN claus. Will the select query with IN clause cause performance issue?

I want to know whether this can be achived through platinum unload (performance wise) since the account are huge in number? The table can have multiple rows as well. This is only one time activity.

Is there any other suggetion on this?
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Sep 28, 2012 10:17 am    Post subject:
Reply with quote

When you can talk about options why not take an extra step and try it ?

Platinum is a third party product and what if your shop decides to stop using it
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Sep 28, 2012 10:37 am    Post subject:
Reply with quote

Also why dont you try DSNTIAUL where the split query in different files can be cocatenated and executed
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Sep 28, 2012 10:45 am    Post subject:
Reply with quote

I think if you put everything in the IN clause it may exceed the Sql query limit.

How big is the table? I mean how many rows does the table has?

If you are unloading around 80% of the data, then its better unload the entire table and then use sort join to filter your records.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Sep 28, 2012 2:15 pm    Post subject:
Reply with quote

Unload/Sort merge is an option.
Another option (if available) is to upload your file into a personal/temporary table and unload from a join.
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: Fri Sep 28, 2012 7:04 pm    Post subject:
Reply with quote

Hello,

If there are to be 11 milliion "matches" and you choose to do this within db2, plan on it running for a while . . . If you upload the accounts number file and then join, this will also add a considerable load on the db2 resources used.

I'd suggest you unload the entire table and then using your sort product, match the table rows against the account number file (look at JOINKEYS).

Do you really want the entire row or only selected columns? The less volume to be passed (maybe multiple times) the better will be the performance. When JOINing the files, you can output some or all of the columns depending on what you need to do with the extracted data.
Back to top
View user's profile Send private message
Help-Me-Out

New User


Joined: 09 Dec 2006
Posts: 56
Location: Pune

PostPosted: Thu Oct 04, 2012 9:57 am    Post subject: Thanks
Reply with quote

Big thanks to all for your reply on this. Now I have decided to go with the COBOL-DB2 program. This is the only way suitable to me for this query.

Thanks a Lot!!!! icon_biggrin.gif icon_biggrin.gif icon_biggrin.gif icon_biggrin.gif
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: Thu Oct 04, 2012 6:55 pm    Post subject: Reply to: How to unload rows from table?
Reply with quote

You're welcome.

If there are performance problems with the cobol/db2 process, you may want to reconsider the JOINKEYS approach.

d
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Fri Oct 05, 2012 2:17 pm    Post subject:
Reply with quote

I would go with GuyC......
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
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
No new posts Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm


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