Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Can we tune the program simply by using a time comit

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

New User


Joined: 30 Oct 2006
Posts: 24

PostPosted: Wed Mar 28, 2007 3:11 pm    Post subject: Can we tune the program simply by using a time comit
Reply with quote

I read a input file and with the key fields read in that we
delete the record from the table.
This is increasing the time and also since it hits the table multiple times the performance is badly affected.
Is there's any way to avoid this situation?
Can we do this using some Utility???
or
can we tune the program simply by using a time comit instead of a frequnce comit using the number of records processed??
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 Mar 29, 2007 6:48 am    Post subject:
Reply with quote

Hello,

Why does the table need to be "hit" multiple times for each delete record?

The timing of the commit will not have much (if any) tuning impact. It could impact the time to rollback or perform a restart/recovery.

Let's call your file of keys the "transaction file" and your databsae table the "master file". How many rows are there in the master file? How many records are in the transaction file? If the number of delete trransactions is a significant percent of the number of rows in the master file, it may perform better if the table was unloaded, the files matched sequentially dropping the keys to be deleted, and the table reloaded.

How long does the process run now? Are there multiple keys on this table? If there are multiple keys per row, additional overhead is required to remove them.

Deleting many rows, one row at a time is often a performance issue.
Back to top
View user's profile Send private message
Magna

New User


Joined: 30 Oct 2006
Posts: 24

PostPosted: Sat Mar 31, 2007 11:11 pm    Post subject:
Reply with quote

Well we have more than 3 millinon records and its a nightly update in the table... so unloading it evry night and reloading would be a problem...
We already thought of this but then since we didn't got a negative reply for this idea we drpped that idea...
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: Sat Mar 31, 2007 11:34 pm    Post subject:
Reply with quote

Hello,

Please re-phrase this
Quote:
but then since we didn't got a negative reply for this idea we drpped that idea...
It is not at all clear.

You also did not answer the questions above - for us to provide any good suggestions, you need to provide the details.

With the little you've posted, i would imagine that the unload/load will not be a problem. For your site 3 million rows may be a large table - for many it is rather small. Something that could make a difference is how many keys the table has. The more keys, the slower individual deletes run. . .

If you have not done so already, it would be a good idea to create a full-size test version of the table and see how long it takes to load.
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
No new posts Comparing 2 Files using Current time arunsoods SYNCSORT 9 Fri Sep 22, 2017 6:00 pm
No new posts Regarding time parameter shanthi gude JCL & VSAM 7 Mon Sep 04, 2017 2:31 pm
No new posts Writing a file using online program grvtomar PL/I & Assembler 3 Fri Jun 30, 2017 1:06 pm
No new posts pre-compiler for program in an HFS Pedro DB2 0 Fri Jun 23, 2017 11:13 pm
No new posts Compiling program in endeavor for tra... RALAKKAL Compuware & Other Tools 4 Mon Jun 05, 2017 8:27 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us