Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Retrieve the last ROWID value

When I insert a record from "X" job with read committed (CS) isolation level then from "Y" job I can't issue the 'SELECT' with CS isolation level for whole table because the last inserted record stays on lock status. I have to wait a COMMIT or a ROLLBACK in the "Y" job from the "X" job.

In my opinion, this solution is not so good in a interactive program where users have to insert more than one table (parent-child tables), and the users can wait longer before they push the ACCEPT or CANCEL button in "X" job. During this time in "Y" job the users can get lock error.

For example, in Postgres under Linux in this case the "SELECT" only displays that records which not committed. For me, it seems there is something different between Postgres-Linux and DB/400-AS/400 transaction handling. Also, how can I establish what was the last ROWID insert? I need it for a child table as a foreign key.
To retrieve the last ROWID value, you would need to re-read the row that was inserted. That's one advantage of using the Identity column attribute to generate key values, the IDENTITY_VAL_LOCAL function can be used to retrieve the last generated value.

A single row lock should not be preventing the entire table from being read by another job using the Read Committed isolation level. I'd suggest reading the defined behavior of the Read Committed isolation level in the iSeries SQL Reference and if your application is experiencing a different behavior, then that problem should be reported to IBM Support.


Check out this Search400.com Featured Topic: Database issues resolved

Search400.com's targeted search engine: Get relevant information on DB2/400.

The Best Web Links: Tips, tutorials and more.

Dig Deeper on DB2 UDB (universal databases)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.