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.

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

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 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

{ENGINE|SQL} [sqlengine] SET DBSPACE storage 

where:

sqlengine

Indicates the target RDBMS. Valid values are DB2, 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 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 adapter may have been installed with a default, site-specific, DBSPACE specification. Use the SQL ? query command to display the setting.

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

{ENGINE|SQL} [sqlengine] SET DEFDATE {NEW|OLD}

where:

sqlengine

Indicates the target RDBMS. Valid values are DB2, 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.

You can instruct the 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

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

where:

sqlengine

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

OFF

Is the default. The adapter proceeds as usual.

ON

Instructs the 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 adapter EXP facilities.)

n

Is the query number to use in the DB2 EXPLAIN tables only. The default value is 1.

To use the ON setting, you must satisfy all RDBMS requirements for executing an EXPLAIN, such as having EXPLAIN tables in DB2 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.

The Adapters for DB2 and Oracle support array retrieval from result sets produced by executing SELECT queries or stored procedures. This technique substantially reduces network traffic and CPU utilization.

Using high values increases the efficiency of requests involving many rows, at the cost of higher virtual storage requirements. A value higher than 100 is not recommended because the increased efficiency it would provide is generally negligible.

{ENGINE|SQL} sqlengine SET FETCHSIZE n

In combination with LOADONLY, the block size for an INSERT applies to MODIFY INCLUDE requests. INSERTSIZE is also supported for parameterized DIRECT SQL INSERT statements.

{ENGINE|SQL} sqlengine SET INSERTSIZE n

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

{ENGINE|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.

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

{ENGINE|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.

When you issue the SET OPTIFTHENELSE command, the 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.

{ENGINE|SQL} [sqlengine] SET OPTIFTHENELSE {ON|OFF}

where:

sqlengine

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

ON

Enables IF-THEN-ELSE optimization. ON is the default value.

OFF

Disables IF-THEN-ELSE optimization.

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

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

{ENGINE}SQL} [sqlengine] SET {OPTIMIZATION|SQLJOIN} optsetting 

where:

sqlengine

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

SQLJOIN

Is an alias for OPTIMIZATION.

optsetting

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

OFF instructs the 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 adapter to create SQL statements that take advantage of RDBMS join, sort, and aggregation capabilities. ON 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 adapter-managed native join logic. See The 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 adapter-managed native join logic. See The 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.

NOAGGR disables optimization of calculations (DEFINE fields) without disabling optimization of join and sort operations.

AGGR enables optimization of calculations (DEFINE fields). This is the default value for optimization of calculations.

You can issue the SET OWNERID command to designate a creator name for all unqualified table names. The 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

{ENGINE|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 adapter uses the owner ID whenever there is ambiguity about the creator name. For example, the 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:

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

{ENGINE|SQL} [sqlengine] SET PASSRECS {OFF|ON}

where:

sqlengine

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

OFF

The 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 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.
• The &ROWSAFFECTED variable is populated with the number of rows affected by a Direct SQL Passthru INSERT, UPDATE, or DELETE command. It is populated regardless of the PASSRECS setting. &ROWSAFFECTED is initialized to -1 and is set to -1 by any Direct SQL Passthru command that does not return the number of rows affected, such as a SELECT statement. The value of &ROWSAFFECTED is overwritten each time a Direct SQL Passthru INSERT, UPDATE, or DELETE command is executed, so if you want to retain it, you must copy it to another variable or store it in a calculated field.

With the SET SQLJOIN OUTER command you can control when the 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 adapter and enables you to fine-tune your applications.

When join optimization is in effect, the 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 adapter generates a separate SQL SELECT statement for each table, and FOCUS processes the join.

The syntax is

{ENGINE|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 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 adapter behavior. It assumes that SET ALL = ON:

By default, literal contents are preserved, including trailing blanks in string literals and the fractional part and exponential notation in numeric literals. This allows greater control over the generated SQL. In some cases, when trailing blanks are not needed, you can issue the adapter SET TRIM_LITERALS ON command to trim them.

ENGINE sqlengine SET TRIM_LITERALS {ON|OFF}

where:

sqlengine

Indicates the adapter. Acceptable values are DB2, SQLDBC, or SQLORA. You can omit this value if you previously issued the SET SQLENGINE command.

ON

Trims trailing blanks.

OFF

Preserves trailing blanks. OFF is the default value.

The SET parameter VARCHAR controls the mapping of the VARCHAR data type. By default, the server maps this data type as variable character (AnV).

ENGINE sqlengine SET VARCHAR {ON|OFF}