IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Multi row Insert for Busy tables


IBM Mainframe Forums -> COBOL Programming
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
harijax

New User


Joined: 16 Jul 2008
Posts: 22
Location: Bangalore

PostPosted: Mon Feb 23, 2009 9:15 am
Reply with quote

Hi All,

As yoou all will be aware of a feathure in Db2 where we can insert or fetch multiple records in one Db2 call.

I have transaction table which contains around 164 columns. There is a very huge amount of insertion into this table from various applications (batch as well as online).

I have batch process as well which reads a big file, and for each record inserts into this table.

There will be 4-5 millions of new records every day.

My question is what will be the effect of Multi row insertion in these tables with respect to locking and commit frequency.

Is it advisable to go for multi row insert in such tables. Will it create any dead lock.

Regards,
Jayakrishnan
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Mon Feb 23, 2009 7:08 pm
Reply with quote

Hari,

By implementing multi-row insert, you can reduce a lot of I/O between your application program & DB2. Performance of the application will be improved.

Try, a count of inserting 500-1000 rows, commit frequency for the same.
Quote:
Will it create any dead lock.
Unless appropriate locks already exist, one or more exclusive locks are
acquired at the execution of a successful insert operation. Until a commit or rollback operation releases the locks, only the application process that performed the insert can access the inserted row.The locks can also prevent other application processes from performing operations on the table. However, application processes that are running with uncommitted read can access locked pages and rows.


Thank You,
Sushanth Bobby
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Feb 23, 2009 8:56 pm
Reply with quote

Quote:

I have transaction table which contains around 164 columns
...
There will be 4-5 millions of new records every day


well, convert a vsam record directly to a db2 row, ?????

164 columns inserted everyday 4-5 million times?

this requires a redesign. Hard to fix garbage design.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> COBOL Programming

 


Similar Topics
Topic Forum Replies
No new posts Insert header record with record coun... DFSORT/ICETOOL 14
No new posts Insert system time/date (timestamp) u... DFSORT/ICETOOL 5
No new posts Identify Program Insert DB2 7
No new posts Convert single row multi cols to sing... DFSORT/ICETOOL 6
No new posts Insert trailer for non empty file only DFSORT/ICETOOL 6
Search our Forums:

Back to Top