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
 

 

Multi row Insert for Busy tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
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    Post subject: Multi row Insert for Busy tables
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: 1013
Location: India

PostPosted: Mon Feb 23, 2009 7:08 pm    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Insert Lines in JCL with Rexx after a... Willy Jensen CLIST & REXX 3 Tue Aug 30, 2016 4:18 pm
No new posts How to insert a lengthy string havin... vidyaa DB2 7 Thu Aug 25, 2016 5:20 pm
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am
No new posts Reading selected volumes of a multi-v... RickBig JCL & VSAM 6 Wed Jul 13, 2016 7:26 pm


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