How can we turn off the keeping of record locks for SQL retrieval sessions?
Article ID: 40626
Updated On:
Products
Issue/Introduction
Introduction:
An ODBC System DSN 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 can we turn off the keeping of record locks for SQL retrieval sessions?
Answer:
In the SYSGEN, the SYSTEM statement needs to specify RETRIEVAL NOLOCK.
In the ODBC Data source you need to specify in the Advanced Options a connection attribute of:
Transaction Isolation: READ_UNCOMMITTED
If you are 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:
Section Default Connection Attributes in the CA IDMS Server User Guide
Section SET TRANSACTION in the CA IDMS SQL Reference Guide
TEC445821 - What are the pros and cons of using RETRIEVAL NOLOCK?
Environment
Component:
Feedback
