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
 
Query with insert statement takes around 30 minutes

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

New User


Joined: 01 Apr 2005
Posts: 11

PostPosted: Thu Dec 07, 2006 4:09 pm    Post subject: Query with insert statement takes around 30 minutes
Reply with quote

Hi All,

My requirement is as follows:

We have a job which has around 10 inserts into table X. Out of the 10 inserts one particilar insert takes around 30 minutes and the rest together takes 30 minutes. Total execution time of the job is 1 hr.

If we split the job into two, the query which takes more query in JOB1 and the rest in JOB2, will the execution time get reduced?

I have read that the table is locked in exclusive mode during insert. If so then JOB2 has to wait till for JOB1 to finish. Is it true? icon_question.gif

Regards,
Jasmine
Back to top
View user's profile Send private message

MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Thu Dec 07, 2006 5:52 pm    Post subject: Re: query with insert statement
Reply with quote

How many rows does this rows have?
How many indexes does this table have?
Are other applications reading this table while inserts are going on, if so do those applicatoins perform commits at regular interval.

If table you are inserting is a partioned table then you can create jobs seperated by partioning key ( or a set of partioning keys). Sort/distribute data by same partioning key.

If you go with above suggested way, also check application if it has any other statements, SELECT, DELETE and these better be using whole clustering index key in WHERE clause. Otherwise split jobs will be contending with each other.
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 Dec 08, 2006 3:12 am    Post subject:
Reply with quote

Hello,

How is an insert defined?

One (1) row inserted into any database should be sub-second.

What kind of processing does the program have to do before issuing the insert?
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Fri Dec 08, 2006 5:07 pm    Post subject: Re: Query with insert statement takes around 30 minutes
Reply with quote

Check when was the last RE-ORG, RUNSTATS done on the table which is taking long time
If the stats are not up to date,the performance decreases.

don't forget to Rebind the program after the Re-org & Runstats.


Thanks,
Ravi
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts How to insert a value on specific pos... Poha Eater DFSORT/ICETOOL 9 Sun Oct 01, 2017 3:04 am
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts WER268A OUTREC STATEMENT : SYNTAX E... frozenblood87 SYNCSORT 12 Sat Aug 26, 2017 9:45 pm

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