Disable locking for SQL retrieval sessions
Article ID: 40626
Updated On:
Products
Issue/Introduction
An ODBC System data source is defined with Access Mode Read only.
In the SYSGEN, the SYSTEM statement specifies RETRIEVAL NOLOCK.
Question:
When running a SQL query there were millions of locks requested.
How to disable the keeping of record locks for SQL retrieval sessions?
Environment
Release: All supported releases.
Resolution
In the SYSGEN, the SYSTEM statement needs to specify RETRIEVAL NOLOCK.
In the ODBC Data source definition, in the Advanced Options, a connection attribute of Transaction Isolation: READ_UNCOMMITTED must be specified.
If running the SQL query in OCF, use the following command before the SQL statement: SET TRANSACTION TRANSIENT READ;
However, be aware that since no locks are kept when using READ_UNCOMMITTED or TRANSIENT READ, there is no guarantee of the integrity of the data read.
Additional Information
Feedback
