x

The Adapter for IDMS/SQL uses the user-specified schema name as the first qualifier for all SQL requests involving SQL tables or views. This command overrides the IDMS/SQL current schema in effect and precludes the specification of unqualified table names. This prevents passing unqualified table names to IDMS.

To identify the schema, issue the SET SESSION CURRENT SCHEMA command from the FOCUS command level

SQL [SQLIDMS] SET SESSION CURRENT SCHEMA schema 

where:

schema

Is the name of the schema in SQL requests.

Note: Omit the SQLIDMS target RDBMS qualifier if you previously issued the SET SQLENGINE command for SQLIDMS. Use the SQL ? query command to display this setting.

x

IDMS protects data being read by one user from changes (INSERT, UPDATE, or DELETE) made by others. The isolation level setting governs the duration of the protection. That is, the isolation level determines when shared locks on rows are released, so that those rows or pages become available for updates by other users. IDMS/SQL allows you to dynamically set the isolation level within the FOCUS session using the IDMS SQL SET TRANSACTION command.

Note: For the equivalent command for DB2, see ISOLATION (DB2).

The SET TRANSACTION CURSOR STABILITY or TRANSIENT READ command affects the duration of row or page shared locks on IDMS/SQL tables for the duration of the IDMS/SQL transaction. You can specify the command in your MODIFY procedure or from the FOCUS command level. The setting remains in effect for the FOCUS session or until you reset it.

To set the isolation level from the FOCUS command level, issue

SQL [SQLIDMS] SET TRANSACTION level 

where:

level

Can be one of the following:

CURSOR STABILITY is Cursor Stability, the default. Provides the maximum amount of concurrency while guaranteeing the integrity of the data selected.

TRANSIENT READ is Transient Read. Allows the reading of records locked by other users (allows dirty reads). Recommended for TABLE only. Transient read prevents the SQL transaction from performing updates. Use this only when you do not need the data retrieved to be absolutely consistent and accurate. If you specify Transient Read, IDMS assumes Read Only.

READ ONLY allows data to be retrieved, but does not allow the database to be updated.

READ WRITE allows data to be retrieved, and allows the database to be updated.

Note: Omit the SQLIDMS target RDBMS qualifier from the command when issuing it in a MODIFY procedure or if you previously issued the SET SQLENGINE command for SQLIDMS.

x

An SQL session is a connection between the FOCUS application and the IDMS database. It begins when the application connects to a dictionary. You use the CONNECT command to override the IDMS/SQL default (automatic) connection. The length of time an SQL session stays in effect depends on whether the connection began automatically or a CONNECT command was issued. If the CONNECT command was issued, the SQL session is in effect until a COMMIT RELEASE, ROLLBACK RELEASE, or RELEASE command is executed. All of these commands may be executed within the FOCUS IDMS/SQL session using Direct SQL Passthru. Refer to the appropriate CA-IDMS/DB documentation for more information regarding SQL sessions.

Issue the following from the FOCUS command level or include it in a PROFILE FOCEXEC

SQL [SQLIDMS] CONNECT TO dictname 

where:

dictname

Is the database (dictionary) to start the IDMS SQL session. The default is the dictionary in effect for the user session. This default is set outside of the FOCUS session, for example, with a SYSIDMS DICTNAME parameter. Please refer to the appropriate CA-IDMS/DB documentation for a complete description.

Note: Omit the SQLIDMS target RDBMS qualifier if you previously issued the SET SQLENGINE command for SQLIDMS.

x

Other IDMS SQL commands that affect the IDMS/SQL session can be executed explicitly.

To issue IDMS SQL session commands such as COMMIT, COMMIT RELEASE, ROLLBACK, ROLLBACK RELEASE, and COMMIT CONTINUE, the syntax is:

SQL [SQLIDMS] COMMIT RELEASE