• 1 reply
  • Latest Post - ‏2014-07-07T17:48:28Z by krmilligan
12 Posts

Pinned topic ? about record locking with SQL

‏2014-07-07T16:29:57Z |

Here's the scenario.  I issued a select on a table using STRSQL in the green screen.  From another session I tried CLRPFM.  The command would not run because the member was in use.

However I was able to issue a Delete via iseriesnavigator/run sql scripts.

I would not have expected the select to lock the table/member.

And this just really highlights that I'm not sure I have an understanding of when/why SQL locks records/tables.

We are now defining tables in DDL and extensively using embedded SQL avoiding F specs when possible. 

Where do I look for a better understanding of SQL and record locking?



  • krmilligan
    450 Posts

    Re: ? about record locking with SQL


    With SQL, the locking requirements are defined by the requirements of the SQL statement being executed and the isolation level (ie, commitment control level) setting of the SQL interface that's being used.  In your example, CLRPFM requires an exclusive object-level lock while an SQL Delete statement doesn't with the isolation level that's being used.

    The DB2 for i Center of Excellence team in Rochester is available to provide services to jumpstart your move to SQL.