Customizing the IDMS/SQL Environment

In this section:

The Adapter for IDMS/SQL provides several parameters for customizing the environment and optimizing performance. This topic provides an overview of customization options.


Top of page

x
Setting the User-specific Schema Name

How to:

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 of unqualified table names to IDMS/SQL.



x
Syntax: How to Set SESSION
ENGINE SQLIDMS SET SESSION CURRENT SCHEMA schema

where:

SQLIDMS

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

schema

Is the name of the schema in SQL requests.


Top of page

x
Controlling Transactions

How to:

IDMS/SQL 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 server session using the IDMS/SQL SET TRANSACTION command.

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 within a stored procedure. The setting remains in effect for the server session or until you reset it.



x
Syntax: How to Control Transactions
ENGINE SQLIDMS SET TRANSACTION 
 {CURSOR STABILITY|TRANSIENT READ|READ ONLY|READ WRITE}

where:

SQLIDMS

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

CURSOR STABILITY

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

TRANSIENT READ

Allows the reading of records locked by other users. This is recommended for SQL request 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/SQL assumes it is read-only.

READ ONLY

Allows data to be retrieved, but does not allow the data source to be updated.

READ WRITE

Allows data to be retrieved, and allows the data source to be updated.


Top of page

x
Designating a Default Tablespace

How to:

You can use the SET DBSPACE command to designate a default tablespace for tables you create.

For the duration of the session, the adapter places these tables in the IDMS/SQL tablespace that you identify with the SET DBSPACE command. If the SET DBSPACE command is not used, IDMS/SQL uses the default tablespace for the connected user.



x
Syntax: How to Set DBSPACE
ENGINE SQLIDMS SET DBSPACE storage

where:

SQLIDMS

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

storage

Is the segment.area name that will contain the IDMS/SQL tables created by the CREATE FILE command. If a name is not specified, the table will be placed in the IDMS/SQL default area for the current schema in effect for the user's SQL session.

Note: This command will only affect CREATE TABLE requests made by Table Services. It does not affect Passthru CREATE TABLE commands.


Top of page

x
Overriding Default Parameters for Index Space

How to:

You can use the SET IXSPACE command to override the default parameters for the IDMS/SQL index space implicitly created by the CREATE FILE and HOLD FORMAT SQLIDMS commands.



x
Syntax: How to Set IXSPACE
ENGINE SQLIDMS SET IXSPACE [index-spec]

where:

SQLIDMS

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

index-spec

Is the portion (up to 94 bytes) of the SQL CREATE INDEX statement beginning with IN segment.area (as specified in the CA-IDMS/DB Reference CREATE INDEX syntax diagram).

Note: To reset to the IDMS/SQL default index space parameters, issue the SET IXSPACE command with no operands.



Example: Specifying Segment.Area Name and Index Block

The following example shows how to set the segment.area name and INDEX BLOCK of the CREATE INDEX statement with IXSPACE:

ENGINE SQLIDMS
SET IXSPACE IN EMPSEG.EMPAREA INDEX BLOCK CONTAINS 5 KEYS
END

You can use the SQL ? query command to determine the current IXSPACE setting. If the current setting is the default, IXSPACE does not display in the SQL SQLIDMS ? output.

Note: This command will only affect CREATE INDEX requests made by Table Services. It does not affect Passthru CREATE INDEX commands.


Top of page

x
Obtaining the Number of Rows Updated or Deleted

How to:

PASSRECS returns the number of rows affected by a successfully executed SQL Passthru INSERT, UPDATE, or DELETE command.

Tip: You can change this setting manually or from the Web Console by clicking Data Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.



x
Syntax: How to Obtain the Number of Rows Updated or Deleted
ENGINE SQLIDMS SET PASSRECS {ON|OFF}

where:

SQLIDMS

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

ON

Provides an informational message after the successful execution of an SQL Passthru UPDATE or DELETE command. ON is the default value.

OFF

Does not provide a message after the successful execution of an SQL Passthru UPDATE or DELETE command.

In addition, the adapter updates the &RECORDS system variable with the number of rows affected. You can access this variable using Dialogue Manager.

Note that since, by definition, the successful execution of an INSERT command always affects one record, INSERT does not generate this information.


WebFOCUS