How can we turn off the keeping of record locks for SQL retrieval sessions?

book

Article ID: 40626

calendar_today

Updated On:

Products

CA IDMS CA IDMS - Database CA IDMS - ADS

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

Release: IDADSO00100-18.5-ADS-for CA-IDMS
Component: