Primary Database

Defining a Program › Creating a New Program Definition › Defining the Environment for SQL Access › Primary Database

Primary Database

For sites using both CA Datacom SQL and DB2 SQL, the primary database is the database management system SQL statements access, such as GRANT and REVOKE, which might apply to either database management system. These SQL statements do not reference an object associated with a dataview.

SQL DML statements such as INSERT do not use the primary database. They must reference an object associated with a dataview that specifies the database management system. You can have INSERTs against multiple SQL database management systems in one program, but you cannot mix dataviews for different database systems in one statement.

The SQL statements COMMIT and ROLLBACK apply to all databases that the program accesses. The WHENEVER statement applies to all SQL database management systems.

On the other hand, GRANT, REVOKE, and all similar SQL statements can access only one database management system per program-the primary database.

The primary database is specified on the environment definition fill-in. Use the SET ENVIRONMENT SQL command to specify a default value.

To access the environment definition fill-in, use the ENVIRONMENT option with the DISPLAY or EDIT command or, while the program is displayed, use the ENVIRONMENT command.

  =>
-------------------------------------------------------------------------------
 IDEAL: PGM ENVIRONMENT       PGM TEST (001) TEST             SYS: DOC   FILL-IN

 Primary SQL database:           DB        (DB or DB2)
 DB/SQL options
     Default Auth-id............ X
     SQL Mode................... ANSI86   (ANSI86, DATACOM, or FIPS)
     Cursor Isolation Level..... C        (U=User, C=Cursor, R=Repeatable)
     Optimization Mode.......... M        (M=Manual,P=Preptime,E=Exectime)
     Date Format................ USA      (DB, ISO, USA, EUR, JIS)
     Time Format................ USA      (DB, ISO, USA, EUR, JIS)
     CBSIO...................... 524286   (0-524287)
     Priority................... 15       (1-15)
     Wait Time.................. 030      (1-120)
                                 S        (M=Minutes, S=Seconds)
     Preptime optimization msgs. N        (N=None,D=Detail,S=Summary)
     Exectime optimization msgs. N        (N=None,D=Detail,S=Summary)
     DB/SQL Workspace........... 0000     (0-128)
     Decimal Point.............. P        (P=Period, C=Comma)
     String Delimiter........... A        (A=Apostrophe, Q=Quote)

Primary SQL DBMS (Mixed SQL Sites Only)

For sites with both CA Datacom SQL and DB2 SQL, the database to access by SQL statements that does not reference a dataview.

DB: Specifies the CA Datacom SQL.
DB2: Specifies the IBM DB2 SQL.

Default Auth-ID

Specifies the one- to eight-character authorization ID for the program's CA Datacom/DB plan.

SQL Mode

Specifies the mode in which CA Datacom/DB processes the program. The following are valid entries:

ANSI86
DATACOM
FIPS

Cursor Isolation Level

Specifies the degree to which a unit of recovery is isolated from the updating operations of other units of recovery.

U: Specifies no locks are acquired.
C: Specifies cursor stability (required for updates, deletes, or inserts).
R: Specifies repeatable read.

Optimization Mode

Specifies the mode in which CA Datacom/DB optimizes table joins.

P: Specifies preptime, that is, order joins during bind processing (CA Ideal Compile). This is the default.
M: Specifies manual, that is, order joins as specified in FROM clauses.
E: Specifies exectime, that is, order joins at execution time.

Date Format

Specifies the display format for SQL date type items.

DB: Specifies to use the CA Datacom/DB default. In CA Datacom/DB, the date can be set to ISO, USA, EUR, or JIS formats.
ISO: Specifies the International Standards Organization format: yyyy-mm-dd.
USA: Specifies the U.S. standard format: mm/dd/yyyy.
EUR: Specifies the European standard format: dd.mm.yyyy.
JIS9: Specifies the Japanese Industrial Standard format: yyyy-mm-dd.

Time Format

Specifies the display format for SQL time type items.

DB: Specifies to use the CA Datacom/DB default. In CA Datacom/DB, the date can be set to ISO, USA, EUR, or JIS formats.
ISO: Specifies the International Standards Organization format: hh.mm.ss
USA: Specifies the U.S. standard format: hh:mm AM or PM
EUR: Specifies the European standard format: hh.mm.ss
JIS: Specifies the Japanese Industrial Standard format: hh:mm:ss

CBSIO

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

Priority

Specifies the priority of the SQL requests. The lowest priority is 1. The highest priority is 15.

Wait Time

Specifies the exclusive control wait limit. Enter a number from 1 through 120, then indicate on the next line the unit of measure, for example, S for seconds or M for minutes.

Preptime Optimization Msgs

Specifies the type of optimization messages CA Datacom/DB produces during bind processing.

N: Specifies no messages. This is the default.
D: Specifies detailed messages.
S: Specifies a summary of the messages.

Exectime Optimization Msgs

Specifies the type of optimization messages CA Datacom/DB produces at runtime.

N: Specifies no messages. This is the default.
D: Specifies detailed messages.
S: Specifies a summary of the messages.

DB/SQL Workspace

Specifies the amount of work space available at plan execution time used for error correction. Enter a number from 0 through 1024.

Decimal Point

Specifies the character used as the decimal point when data displays. This has no effect on how the data is stored.

P: Specifies to use the period (.) as the decimal point. The comma (,) is used as the digit separator. This is the default.
C: Specifies to use the comma (,) as the decimal point. The period (.) is used as the digit separator.

String Delimiter

Specifies the character that delimits string values in all SQL statements.

A: Specifies to use the apostrophe (') as the delimiter. This is the default.
Q: Specifies to use the quotation mark (") as the delimiter.