An ODBC System DSN data source is defined with Access Mode Read only.
In the SYSGEN, the SYSTEM statement specifies RETRIEVAL NOLOCK.
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?
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.
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?