Maintaining Plans for CA Datacom SQL Access › Generating CA Datacom SQL Access Plans › Setting Plan Options

Setting Plan Options

The programmer can set plan options in the Environment fill-in of the program definition or by changing the session options with the SET DBSQL command. As the site administrator, you can set DBSQL options for the site using the SET SITE DBSQL command. However, the session options override the site options and the options set in the Environment fill-in override both site and session options.

You can also change the plan options for a specified program after it is compiled with the ALTER PROGRAM ENVIRONMENT command (see Changing the Access Plan in this chapter). However, to incorporate those changes into the plan, you must rebind the plan.

You can set the following plan options in the program Environment fill-in. The equivalent SET command that you can use to set the option for the session or site is shown after each option.

Default Auth-ID

One- to eight-character authorization ID that identifies the program's access plan.

You can also set this option with the following command:

SET [SITE] DBSQL AUTH auth-id

SQL Mode

The mode in which CA Datacom processes the program. The values can be any of the following:

• ANSI86-SQL must follow ANSI86 standards
• Datacom-SQL must follow CA Datacom standards
• FIPS-SQL must follow FIPS standards
• DB2-SQL must follow DB2 standards (this supports CA Datacom Database Transparency Option for DB2)

You can also set this option with the following command:

SET [SITE] DBSQL MODE sqlmode

For more information about definitions of the SQL modes, see the CA Datacom SQL Programming and Reference Guide.

Cursor Isolation Level

Degree to which a unit of recovery is isolated from the updating operations of other units of recovery. Cursor stability is required for updates, deletes, or inserts:

• U-No locks are acquired
• C-Cursor stability
• R-Repeatable read (CA Datacom Release 8.1 only)

You can also set this option with the following command:

SET [SITE] DBSQL ISOLATION-LEVEL level

Optimization Mode

The mode in which CA Datacom optimizes table joins:

• Preptime-(Default) Order joins during bind processing
• Manual-Order joins as specified in FROM clauses
• Exectime-Order joins at execution time. (See the CA Datacom/DB documentation for more information).

You can also set this option with the following command:

SET [SITE] DBSQL OPTMODE optmode

Date Format

Display format for SQL date type items:

• DB-CA Datacom default. The CA Datacom site administrator can set it to ISO, USA, EUR, or JIS.
• ISO-International Standards Organization yyyy-mm-dd
• USA-U.S. standard mm/dd/yyyy
• EUR-European standard dd.mm.yyyy
• JIS-Japanese Industrial Standard yyyy-mm-dd

You can also set this option with the following command:

SET [SITE] DBSQL DATE date-format

Time Format

Display format for SQL time type items:

• DB-CA Datacom default. The CA Datacom site administrator can set it to ISO, USA, EUR, or JIS.
• ISO-International Standards Organization hh.mm.ss
• USA-U.S. standard hh:mm AM or PM
• EUR-European standard hh.mm.ss
• JIS-Japanese Industrial Standard hh:mm:ss

You can also set this option with the following command:

SET [SITE] DBSQL TIME time-format

CBSIO

I/O limit interrupt value for SQL statements that creates a set.

You can also set this option with the following command:

SET [SITE] DBSQL CBSIO nnn

Priority

Priority of the SQL requests:

• 1-15-The lowest priority is 1 and the highest priority is 15.

You can also set this option with the following command:

SET [SITE] DBSQL PRIORITY nn

Wait Time

Exclusive control wait limit.

• 1-120-Number of seconds or minutes
• unit-S for seconds or M for minutes.

You can also set this option with the following command:

SET [SITE] DBSQL WAIT nnn unit

Preptime Optimization Messages

Type of optimization messages CA Datacom produces during bind processing.

• N-None (default)
• D-Detail
• S-Summary

You can also set this option with the following command:

SET [SITE] DBSQL OPTMSGS PREP type

Exectime Optimization Messages

Type of optimization messages CA Datacom produces at runtime.

• N-None (default)
• D-Detail
• S-Summary

You can also set this option with the following command:

SET [SITE] DBSQL OPTMSGS EXEC type

DB/SQL Workspace

Amount of workspace available at plan execution time. (Used for error correction.) This value is multiplied by 1024 to determine the number of bytes to allocate.

• 0-128-0 to 128 bytes, inclusive.

You cannot set this option with a SET DBSQL command. However, you can change it with the ALTER PROGRAM ENVIRONMENT command.