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.
MORE INFORMATION ON THIS TOPIC
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.
This was first published in August 2004