Performance issue
Moderators: chulett, rschirm, roy
Performance issue
Hi
I am reading source data and write in to target table using DB2 API stage.
While writing data in to target table , deleting existing records from target table. It is a daily process. When i ask my lead why do not we truncate and load the data at traget table. They are not accepting.
The existing job is taking around 2hrs in production.
Over 2 milion records.
How can increase the performance. Please help me out....
I am reading source data and write in to target table using DB2 API stage.
While writing data in to target table , deleting existing records from target table. It is a daily process. When i ask my lead why do not we truncate and load the data at traget table. They are not accepting.
The existing job is taking around 2hrs in production.
Over 2 milion records.
How can increase the performance. Please help me out....
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
If really there is any problem with truncate, they should give an explanation.
delete is always slow and dead slow when there are referential integrity constraints and other factors like rollback space and all.
Hence you might like to ask your lead again if you can use truncate option.
delete is always slow and dead slow when there are referential integrity constraints and other factors like rollback space and all.
Hence you might like to ask your lead again if you can use truncate option.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
Remember that DB2 has no "truncate" function, there is a "LOAD FROM /DEV/NULL OF DEL REPLACE INTO YourTable" equivalent, though.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Please have a look at this page, (DB2 9.1 for z/OS Topic date: April 2009)
http://publib.boulder.ibm.com/infocente ... tewnew.htm
http://publib.boulder.ibm.com/infocente ... tewnew.htm
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
also there is a stored procedure in DB2 which is used like:
call truncate(tablename) which inturn calls the statement
'IMPORT FROM /dev/null OF DEL REPLACE INTO tablename'
Similar to what you have mentioned.
or
alter table schema.table_name activate not logged initially with empty table
call truncate(tablename) which inturn calls the statement
'IMPORT FROM /dev/null OF DEL REPLACE INTO tablename'
Similar to what you have mentioned.
or
alter table schema.table_name activate not logged initially with empty table
Last edited by priyadarshikunal on Thu Aug 06, 2009 9:54 am, edited 1 time in total.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia