Setting Defaults › How to Set Session Options › Setting SQL Options for CA Datacom SQL Access

Setting SQL Options for CA Datacom SQL Access

The following commands set the default plan options for CA Datacom SQL access. DBSQL options control the default values that appear in the program Environment fill‑in when a program that uses CA Datacom SQL access is created or transported in source format.

SET DBSQL AUTH auth‑id

Sets the default one- to eight‑character authorization ID for the access plan.

SET DBSQL CBSIO nnn

Sets the default I/O limit interrupt value for SQL statements that creates a set. Specify a value from 0‑524287.

SET DBSQL CSRCOMIT x

Sets the default behavior of cursors at a COMMIT/CHECKPOINT. Valid values are:

C Close

D Delete

P Preserve

SET DBSQL CSRROLLB x

Sets the default behavior of cursors at a ROLLBACK/BACKOUT. Valid values are:

C Close

D Delete

P Preserve

SET DBSQL DECPOINT x

Sets the default character used as the decimal point when data is displayed. This has no effect on how the data is stored. Valid values are:

P Period (.)

Used as the decimal point and comma is used as the digit separator. This is the default.

C Comma (,)

Used as the decimal point and period (.) is used as the digit separator.

SET DBSQL DATE date‑format

Sets the default display format for SQL date type items.

• DB CA Datacom/DB installed default, one of the following:
  • 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

SET DBSQL ISOLATION‑LEVEL level

Sets the default isolation level that separates one unit of recovery from the updating operations of other units of recovery. You can abbreviate the keyword ISOLATION‑LEVEL to ISO. Valid values are as follows:

U

No locks are acquired

CS

Cursor stability; required for updates, deletes, or inserts

R

Repeatable read.

SET DBSQL MODE sqlmode

Sets the default mode in which CA Datacom/DB processes the program. Allowable values are as follows:

• ANSI86
• Datacom
• FIPS

SET DBSQL OPTMODE optmode

Sets the default mode in which CA Datacom/DB optimizes the plan by ordering joins. Specify one of the following:

• PREP Order joins during bind processing.
• MAN Order joins as specified in FROM clauses.
• EXEC Order joins at execution time.

Note: For more information about these settings, see the CA Datacom/DB Database SQL User Guide.

SET DBSQL OPTMSGS PREP, SET DBSQL OPTMSGS EXEC

Sets the default type of optimization messages CA Datacom/DB produces during bind processing (PREP) or during execution (EXEC). Specify one of the following:

• NON None (default)
• DET Detail
• SUM Summary

SET DBSQL PRIORITY nn

Sets the default priority of the SQL requests. Specify a value from 1 through 15, where the lowest priority is 1 and the highest priority is 15.

SET DBSQL STRDELIM x

Sets the default character that delimits string values in all SQL statements.

• A Apostrophes (') delimit string values. This is the default.
• Q Quotation marks (") delimit string values.

SET DBSQL TIME time‑format

Sets the default display format for SQL time type items.

• DB CA Datacom/DB installed default, one of the following:
  • 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

SET DBSQL WAIT nnn

Sets the default for the exclusive control wait limit. Specify a value from 1 to 120, followed by SEC for seconds or MIN for minutes. For example, for a ten‑second wait, enter the following command:

SET DBSQL WAIT 10 SE