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.
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.
Dig Deeper on DB2 UDB (universal databases)
Related Q&A from Kent Milligan
Create a host variable of the where in statement on the fly with dynamic SQL. Continue Reading
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ... Continue Reading
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ... Continue Reading