Parameters That Apply to Multiple Data Adapters

The following parameters can be used with all relational data adapters: CONVERSION, DBSPACE, DEFDATE, OPTIFTHENELSE, OPTIMIZATION, PASSRECS, and SQLJOIN OUTER. This topic also describe the EXPLAIN parameter that applies to the DB2, DB2 for VM, and Teradata Data Adapters; the ISOLATION parameter that applies to the DB2 and DB2 for VM Data Adapters; the IXSPACE parameter that applies to the DB2, Oracle, and IDMS/SQL Data Adapters; and the OWNERID parameter that applies to the DB2, Teradata, and Oracle Data Adapters.

In this section: CONVERSION DBSPACE DEFDATE EXPLAIN (DB2, DB2 for VM, Teradata) ISOLATION (DB2 and DB2 for VM) IXSPACE (DB2, IDMS/SQL, Oracle) OPTIFTHENELSE OPTIMIZATION OWNERID (DB2, Teradata, Oracle) PASSRECS SQLJOIN OUTER (DB2, Teradata, Oracle)

CONVERSION

You can use the SET CONVERSION command to alter the length and scale of numeric columns displayed from a SELECT request. That is, you can control the USAGE attribute in the dynamically created Master File.

Syntax: How to Alter Length and Scale of Numeric Columns Returned

SQL [sqlengine] SET CONVERSION {RESET|dtype} [RESET|PRECISION {value|MAX}]

where:

sqlengine

Indicates the target RDBMS. Acceptable values are DB2, SQLDS, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

RESET

Returns precision and scale values that you previously altered back to the data adapter defaults. If you specify RESET immediately following the SET CONVERSION command, all data types return to the defaults. If you specify RESET following a particular data type, only columns of that data type are reset.

dtype

Applies the command only to columns of a specific data type. Valid data types are:

INTEGER

INTEGER (and, for Oracle, SMALLINT).

DECIMAL

DECIMAL.

REAL

Single precision floating point. Not supported for Oracle or Teradata.

FLOAT

Double precision floating point.

value

Is the precision in the following form:

nn [mm]

where:

nn

Must be greater than 1 and less than the maximum allowable value for the data type. (See description of MAX.)

mm

Is the scale. Valid with DECIMAL, REAL, and FLOAT data types. If you do not specify a value for scale, the current scale setting remains in effect.

MAX

Sets the precision to the maximum allowable value for the indicated data type:

DATA TYPE	MAX Precision
INTEGER	11
REAL	9
FLOAT	20
DECIMAL	33

Note:

DBSPACE

Within a FOCUS session, you can designate a default storage space for tables you create with the FOCUS CREATE FILE or HOLD FORMAT SQLengine commands. For the duration of the session, the RDBMS places such tables in the DB2 for VM DBSPACE, IDMS/SQL segment.area, Oracle tablespace, or DB2 database you identify in the SET DBSPACE command.

Issue the SET DBSPACE command from the FOCUS command level

SQL [sqlengine] SET DBSPACE storage

where:

sqlengine

Indicates the target RDBMS. Valid values are DB2, SQLDS, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

storage

For DB2, is databasename.tablespacename or DATABASE databasename. In DB2, the RDBMS default value is DSNDB04, a public database. (DB2 automatically generates a tablespace in DSNDB04.)

For DB2 for VM, is owner.dbspace. In DB2 for VM, the RDBMS default value is the DB2 for VM authorization ID's private DBSPACE.

For IDMS SQL is segment.area. The default is the IDMS default area for the current schema in effect for the user's SQL session.

For Oracle, is tablespacename. If the SET DBSPACE command is not issued, Oracle uses the default tablespace for the connected user.

The data adapter may have been installed with a default, site-specific, DBSPACE specification. Use the SQL ? query command to display the setting.

DEFDATE

Use the SET DEFDATE command to change the value of the default date FOCUS uses for the RDBMS DATE data type. See in Additional Topics, for a complete discussion.

From the FOCUS command line, issue

SQL [sqlengine] SET DEFDATE {NEW|OLD}

where:

sqlengine

Indicates the target RDBMS. Valid values are DB2, SQLDS, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

NEW

Supplies '1900-12-31' as the base date for RDBMS columns with DATE data types. NEW is the default.

OLD

Supplies '1901-01-01' as the base date for RDBMS columns with DATE data types. OLD was the default in versions of the data adapter prior to FOCUS Version 6.8.

If you use the MODIFY facility to maintain RDBMS tables containing DATE columns, a change in the default date from prior releases may impact existing applications. See Additional Topics, for more information.

EXPLAIN (DB2, DB2 for VM, Teradata)

You can instruct the data adapter to execute an RDBMS EXPLAIN command for the SQL SELECT statements issued by a FOCUS request before it actually issues the FOCUS request. At the FOCUS command level enter

SQL [sqlengine] SET EXPLAIN {OFF|ON [n]}

where:

sqlengine

Indicates the target RDBMS. Valid values are DB2, SQLDS, or SQLDBC. Omit if you previously issued the SET SQLENGINE command.

OFF

Is the default. The data adapter proceeds as usual.

ON

Instructs the data adapter to issue an RDBMS EXPLAIN command for the SQL SELECT statements issued by a FOCUS request, then issue the request itself. (Do not confuse this with the data adapter EXP facilities.)

n

To use the ON setting, you must satisfy all RDBMS requirements for executing an EXPLAIN, such as having EXPLAIN tables in DB2 and DB2 for VM or an XPLTRACE allocation for Teradata. The ON setting applies to TABLE and SQL Passthru SELECT requests only; it is ignored by the MODIFY facility and non-SELECT SQL Passthru requests.

Note: For Teradata, TABLE requests that result in multiple SQL SELECT statements and Direct SQL Passthru requests are not supported with SET EXPLAIN set to ON.

ISOLATION (DB2 and DB2 for VM)

Each RDBMS 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 or data pages are released, so that those rows or pages become available for updates by other users. You can dynamically set the isolation level within the FOCUS session using the SET ISOLATION command for DB2 and DB2 for VM.

How to: Dynamically Change the Isolation Level

Note: For IDMS/SQL, see TRANSACTION.

You can change the isolation level by issuing this command in a MODIFY procedure or at the FOCUS command level. (For Maintain, you must issue the command at the FOCUS command level prior to invoking the Maintain procedure.) The setting remains in effect for the FOCUS session or until you reset it.

Syntax: How to Dynamically Change the Isolation Level

From the FOCUS command level, issue

SQL [sqlengine] SET ISOLATION level

where:

sqlengine

Indicates the target RDBMS. Valid values are DB2 or SQLDS. Omit to issue the command in MODIFY procedures or if you previously issued the SET SQLENGINE command.

level

CS is Cursor Stability, the default. Releases shared locks as the cursor moves on in the table. Use for read-only requests.

RR is Repeatable Read. Use for MODIFY and Maintain read/write routines. Locks the retrieved data until it is released by an SQL COMMIT WORK or SQL ROLLBACK WORK.

UR is Uncommitted Read. Available in DB2 only. It provides read-only access to records even if they are locked; however, these records may not yet be committed to the database.

RS is Read Stability. Available in DB2 only. For more information, see the DB2 Command and Utility Reference.

blank resets the level to the data adapter default.

Note:

• The data adapter does not validate the isolation level values. If you issue the SET ISOLATION command with a level not supported for the version of the RDBMS you are using, the RDBMS will return an SQL code of -104, signifying an SQL syntax error.
• For DB2 for VM, the isolation level you set applies to locks held on tables in public dbspaces. (DB2 for VM always locks private dbspaces at the dbspace level.)
• For DB2:
• The SET ISOLATION command is enabled only for SELECT requests created as a result of FOCUS TABLE requests.
• This setting cannot override the required isolation level of RR for MODIFY and Maintain requests.

To display the isolation level setting, issue the SQL ? query command.

IXSPACE (DB2, IDMS/SQL, Oracle)

You can override the default parameters for the DB2 or Oracle index space or for the IDMS/ SQL default index segment.area and default index specifications implicitly created by FOCUS CREATE FILE and HOLD FORMAT DB2, SQLORA, or SQLIDMS. The syntax is

Example: Providing Index Space Parameters

SQL [sqlengine] SET IXSPACE [index_spec]

where:

sqlengine

Identifies the target RDBMS. Valid values are DB2, SQLORA, or SQLIDMS. Omit 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:

• The DB2 syntax USING-BLOCK (as specified in the IBM DB2 SQL Reference CREATE INDEX syntax diagram).
• The IDMS/SQL syntax IN segment.area (as specified in the CA-IDMS/DB Release 12 SQL Reference CREATE INDEX syntax diagram).

To reset to the default index space parameters, issue the SET IXSPACE command with no operands.

Example: Providing Index Space Parameters

Use the long form of Direct SQL Passthru syntax for commands that exceed one line:

SQL sqlengine
SET IXSPACE index_spec
END

To specify the USING-BLOCK, FREE-BLOCK, and CLUSTER portions of the CREATE INDEX statement for DB2, enter the following:

SQL DB2
SET IXSPACE USING STOGROUP SYSDEFLT PRIQTY 100
SECQTY 20 FREEPAGE 16 PCTFREE 5 CLUSTER
END

To specify the segment.area and INDEX BLOCK portions of the CREATE INDEX statement for IDMS/SQL, enter the following:

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

To specify the Oracle table space ORATS1 for an index:

SQL SQLORA SET IXSPACE TABLESPACE ORATS1

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 ? output.

OPTIFTHENELSE

When you issue the SET OPTIFTHENELSE command, the data adapter attempts to deliver the construct of FOCUS IF-THEN-ELSE DEFINE fields to the RDBMS as an expression. The DEFINE field must be an object of a selection test or an aggregation request. The DEFINE definition may be specified in the TABLE request or in the Master File.

SQL [sqlengine] SET OPTIFTHENELSE {ON|OFF}

where:

sqlengine

Indicates the target RDBMS. Valid values are DB2, SQLDS, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

ON

Enables IF-THEN-ELSE optimization.

OFF

Disables IF-THEN-ELSE optimization and is the default.

OPTIMIZATION

Depending on the OPTIMIZATION setting, the data adapter may generate SQL SELECT statements that allow the RDBMS to perform operations (such as join and aggregation) and return the data to the data adapter for FOCUS report generation (see The Data Adapter Optimizer).

To invoke data adapter optimization, issue the following at the FOCUS command level

SQL [sqlengine] SET {OPTIMIZATION|SQLJOIN} optsetting

where:

sqlengine

Indicates the target RDBMS. Valid values are DB2, SQLDS, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

SQLJOIN

Is an alias for OPTIMIZATION.

optsetting

Indicates whether the data adapter should pass sort, join, and aggregation operations to the RDBMS for processing. Valid values are as follows:

OFF instructs the data adapter to create SQL statements for simple data retrieval from each table. FOCUS processes the returned sets of data in your address space or virtual machine to produce the report.

ON instructs the data adapter to create SQL statements that take advantage of RDBMS join, sort, and aggregation capabilities. Is compatible with previous releases in regard to the multiplicative effect: misjoined unique segments and multiplied lines in PRINT and LIST based report requests do not disable optimization. Other cases of the multiplicative effect invoke data adapter-managed native join logic. See The Data Adapter Optimizer, for more information. ON is the default.

FOCUS passes join logic to the RDBMS only when the results will be the same as from a FOCUS-managed request. Misjoined unique segments, the multiplicative effect, and multiplied lines in PRINT and LIST based requests invoke data adapter managed native join logic. See The Data Adapter Optimizer, for information.

SQL passes join logic to the RDBMS in all possible cases. The multiplicative effect does not disable optimization, even in cases involving aggregation (SUM, COUNT). Join logic is not passed to the RDBMS for tables residing on multiple subsystems and for tables residing on multiple DBMS platforms.

OWNERID (DB2, Teradata, Oracle)

You can issue the SET OWNERID command to designate a creator name for all unqualified table names. The data adapter will then use the owner ID as the creator name whenever the Access File does not include a creator value as part of the table name. Direct SQL Passthru requests are not affected by this setting. The syntax is

SQL [sqlengine] SET OWNERID ownerid_value

where:

sqlengine

Indicates the target RDBMS. Valid values are DB2, SQLDBC, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

ownerid_value

Is the owner ID to assign to all unqualified table names.

The data adapter uses the owner ID whenever there is ambiguity about the creator name. For example, the data adapter uses the owner ID as the creator for any table you create using:

• The CREATE FILE command when the Access File does not specify a creator.
• HOLD FORMAT SQLengine when the name of the extract file does not include a period.

If you do not have sufficient rights to create tables using the owner ID you set, an SQL error results and the table is not created.

When this setting is in effect, the following line is added to the output of the SQL ? query:

(FOC1520) SQL CURRENT OWNER ID IS ownerid

PASSRECS

You can use the SET PASSRECS command to display the number of rows affected by a successfully executed Direct SQL Passthru UPDATE or DELETE command. The syntax is

SQL [sqlengine] SET PASSRECS {OFF|ON}

where:

sqlengine

Indicates the target RDBMS. Valid values are DB2, SQLDS, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

OFF

As in previous releases, the data adapter provides no information as to the number of records affected by a successfully executed Direct SQL Passthru UPDATE or DELETE command.

ON

Is the default. Provides the following FOCUS message after the successful execution of a Direct SQL Passthru UPDATE or DELETE command:

(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND: #/operation

For example, a DELETE command that executes successfully and affects 20 rows generates the following message:

(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND: 20/DELETE

In addition to this message, the data adapter updates the FOCUS system variable &RECORDS with the number of rows affected. You can access this variable with Dialogue Manager and display it with the ? STAT query.

Note:

• You must use Direct SQL Passthru syntax to issue UPDATE or DELETE commands in order to invoke the SET PASSRECS command.
• Since, by definition, the successful execution of an INSERT command always affects one record, INSERT does not generate the FOC1364 message.
• The FOC1364 message is for informational purposes only and does not affect the &FOCERRNUM setting.

SQLJOIN OUTER (DB2, Teradata, Oracle)

With the SET SQLJOIN OUTER command you can control when the data adapter optimizes outer joins, without affecting the optimization of other operations. (An outer join is generated when the SET ALL=ON command is in effect.) This parameter provides backward compatibility with prior releases of the data adapter and enables you to fine-tune your applications.

When join optimization is in effect, the data adapter generates one SQL SELECT statement that includes every table involved in the join. The RDBMS can then process the join. When join optimization is disabled, the data adapter generates a separate SQL SELECT statement for each table, and FOCUS processes the join.

The syntax is

SQL sqlengine SET SQLJOIN OUTER {ON|OFF}

where:

sqlengine

Indicates the target RDBMS. Valid values are DB2, SQLDBC, or SQLORA. Omit if you issued the SET SQLENGINE command.

ON

Enables outer join optimization. ON is the default for Teradata, and Oracle.

OFF

Disables outer join optimization. OFF is the default value for DB2.

Note:

• Left outer join optimization is not supported for DB2 for VM or IDMS SQL.
• The SQLJOIN OUTER setting is available only when optimization is enabled (that is, OPTIMIZATION is not set to OFF).
• The SQLJOIN OUTER setting is ignored when SET ALL = OFF.

The following table describes how different combinations of OPTIMIZATION and SQLJOIN OUTER settings affect data adapter behavior. It assumes that SET ALL = ON:

Settings		Results
OPTIMIZATION	SQLJOIN OUTER	Outer Join Optimized?	Other Optimization Features
ON	ON	Yes	Enabled
ON	OFF	No	Enabled
OFF	N/A	No	Disabled
SQL	ON	Yes, in all possible cases	Enabled
SQL	OFF	No	Enabled
FOCUS	ON	Yes if results are equivalent to FOCUS managed request	Enabled
FOCUS	OFF	No	Enabled

If SQLJOIN OUTER is set to OFF, the following message displays when you issue the SQL ? query command:

(FOC1420) OPTIMIZATION OF ALL=ON AS LEFT JOIN - : OFF