Customizing the Db2 Environment

The Adapter for Db2 provides several parameters for customizing the environment and optimizing performance. This topic provides an overview of customization options.

Improving Response Time

The Adapter for Db2 on z/OS supports parallel processing if you issue the SET CURRENT DEGREE command prior to the request.

Improve Response Time

ENGINE DB2 SET CURRENT DEGREE {'1'|'ANY'}

where:

DB2

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

1

Invokes serial processing. 1 is the default value.

ANY

Invokes parallel processing for dynamic requests. If the thread to Db2 is closed during the session, the value resets to 1.

Designating a Default Tablespace

You can use the SET DBSPACE command to designate a default tablespace for tables you create. For the duration of the session, the adapter places these tables in the Db2 tablespace that you identify with the SET DBSPACE command. If the SET DBSPACE command is not used, Db2 uses the default tablespace for the connected user.

Designate a Default Storage Space for Tables

ENGINE DB2 SET DBSPACE {datasource.tablespace|DATABASE datasource}

where:

DB2

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

datasource

Is the data source name. DSNDB04 is the default value, which is a public data source.

tablespace

Is a valid table space name in the data source.

Note: This command will only affect CREATE FILE requests issued by Table Services. It does not affect Passthru CREATE TABLE commands.

On Linux, UNIX, and Windows, if you want to place a volatile or global temporary table in a user-created tablespace, issue the following command.

ENGINE DB2 SET DBSPACE usertemp

where:

usertemp

Is the name of a user temporary tablespace.

Note: When this setting is in effect, all tables created in the session will automatically be created in this tablespace. However, permanent tables cannot be placed in this type of tablespace and will result in an error. Therefore, you should not place this setting in any profile. You can add it to a procedure creates a temporary table. After the procedure executes, the setting will no longer be in effect.

Controlling the Types of Locks

You can use the SET ISOLATION command to specify the isolation level of transactions created by the adapter. The isolation level controls the types of locks for objects referenced in the requests executed within the transaction.

If you are working in the IBM i environment, see Controlling Types of Locks on IBM i for OS-specific variations of SET ISOLATION syntax. For related information about another use for the SET ISOLATION syntax, see Creating and Updating Db2 Files on IBM i.

Control the Lock Type

ENGINE DB2 SET ISOLATION level

where:

DB2

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

level

Sets the Db2 isolation level, which is mapped to the IBM RDBMS isolation level. If you do not specify an isolation level, the level is reset to the adapter default.

Db2 Isolation Level (CLI)

IBM RDBMS Isolation Level

RC (SQL_TXN_READ_COMMITTED)

CS (Cursor Stability)

SE (SQL_TXN_SERIALIZABLE_READ)

RR (Repeatable Read)

RR (SQL_TXN_REPEATABLE_READ)

RS (Read Stability)

RU (SQL_TXN_READ_UNCOMMITTED)

UR (Uncommitted Read)

NC (SQL_TXN_NO_COMMIT)

Not applicable

  • RC. Releases shared locks as the cursor moves on in the table. Use for read-only requests. RC is the default value. Maps to CS (Cursor Stability).
  • SE. Locks the retrieved data until it is released by an SQL COMMIT WORK or SQL ROLLBACK WORK statement. Maps to RR (Repeatable Read).
  • RR. Maps to RS (Read Stability). For more information, see the Db2 Command and Utility Reference.
  • RU. Provides read-only access to records even if they are locked. However, these records may not yet be committed to the data source. Maps to UR (Uncommitted Read).
  • NC. Commit and rollback operations have no effect on SQL statements. Any changes are effectively committed at the end of each successful change operation and can be immediately accessed. For more information, see Creating and Updating Db2 Files on IBM i and the Db2 Command and Utility Reference.

Note:  
  • The adapter does not validate the isolation level values. If you issue the SET ISOLATION command with an invalid value, the adapter will not return a message.
  • To display the isolation level setting, issue the ENGINE DB2 ? CONNECTINFO query command.

Controlling Types of Locks on IBM i

For the IBM i environment, the isolation level is preset to NC (no commit) so no action is required.

For a full IBM i installation, two forms of the SET ISOLATION command are supported: a long form that executes immediately in passthru mode, and a short form that is held until an actual SQL request is processed.

The two command variations are very similar, however, the short form, under an SQL adapter configuration (rather than a CLI configuration), generates the following Db2 message in the IBM i system process log if a COMMIT, ROLLBACK, or SAVEPOINT command is issued before any real work is done (possibly due to an AUTOCOMMIT command):

SQL7007: COMMIT, ROLLBACK, or SAVEPOINT not valid

The syntax variations are:

Short Form 

ENGINE DB2 SET ISOLATION level

Long Form 

ENGINE DB2 SET TRANSACTION ISOLATION LEVEL level

Standard options for level apply (see Control the Lock Type). However, the instruction to display the current isolation level by issuing the following query command applies only for the short form setting:

ENGINE DB2 ? CONNECTINFO

There is no way to display the current long form setting.

Creating and Updating Db2 Files on IBM i

The following information applies to HOLD FORMAT DB2 and procedures that update a Db2 object in a non-journaled collection.

While Db2 on IBM i supports CREATE TABLE operations to a non-journaled collection (a library with no journal receivers), Db2 normally considers this a commitment control error and issues an error message. When a HOLD FORMAT DB2 command is issued, the same error condition triggers an error message to the adapter. In response, the adapter creates the table, but does not perform the load step. However, if the server is configured with Db2 as a CLI-based adapter, you can use the ISOLATION setting of NC (No Commit) to prevent Db2 from triggering the error message, thereby enabling the table to load.

You can set ISOLATION to NC on a request-by-request basis before issuing HOLD FORMAT DB2:

SQL DB2 SET ISOLATION NC

Alternatively, you can set the NC option server wide from the Adapter for Db2's Change Settings pane. (To access this pane, click Adapters on the menu bar, right-click the name of the configured adapter, and choose Change Settings from the menu.)

After completing this task, revert to the original ISOLATION setting, if appropriate.

Note: An error message like the following will be generated if you issue a HOLD FORMAT DB2 ... DROP command but do not have the authority to DROP an existing file. 
(FOC1400) SQLCODE IS -601 (HEX: FFFFFDA7): [42710] ABC in XYZ type *FILE already exists. 
(FOC1414) EXECUTE IMMEDIATE ERROR.
  • If you receive this message, make sure that you have DB2 DROP/CREATE authority and reissue the command.
  • When connecting from a non-IBM i platform through a local Db2 client, the SET isolation does not work. You can use the following specific platform syntax.
Note:  

  • From a Windows platform:

    Add a CLI parameter TxnIsolation with the value 32 within your ODBC settings under Administrative Tools.

  • From a Unix/Linux platform:

    You can run the following Db2 command on your database:

    DB2 update CLI CFG for section <tablename> using TxnIsolation 32

    Verify these settings with the following command:

    DB2 get CLI CFG

    As an SQL alternative (not OS-specific), you can add WITH NONE to the end of your SQL UPDATE command.

Overriding Default Parameters for Index Space

You can use the SET IXSPACE command to override the default parameters for the index space implicitly created by the CREATE FILE and HOLD FORMAT commands.

Set IXSPACE

ENGINE DB2 SET IXSPACE [index-spec]

where:

DB2

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

index-spec

Is the portion of the DB2 CREATE INDEX statement that defines the parameters for the index. It can consist of up to 94 bytes of valid Oracle index space parameters. To reset the index space parameters to their default values, issue the SET IXSPACE command with no parameters.

Note: Refer to the Db2 documentation for more information on this command.

The long form of SQL Passthru syntax for commands exceeding one line is:

ENGINE DB2
SET IXSPACE index-spec 
END

For example, to specify the NOSORT, NOLOGGING, and TABLESPACE portions of the DB2 CREATE INDEX statement, enter the following commands:

ENGINE DB2
SET IXSPACE NOSORT NOLOGGING
TABLESPACE TEMP
END
Note: This command will only affect CREATE INDEX requests issued by CREATE FILE and HOLD FORMAT DB2 commands. It does not affect Passthru CREATE INDEX commands, for example:
ENGINE DB2 SET IXSPACE TABLESPACE tablespace_name 
TABLE FILE table_name 
PRINT *
ON TABLE HOLD AS file_name FORMAT DB2
END

Activating NONBLOCK Mode

The Adapter for Db2 has the ability to issue calls in NONBLOCK mode. The default behavior is BLOCK mode.

This feature allows the adapter to react to a client request to cancel a query while the adapter is waiting on engine processing. This wait state usually occurs during SQL parsing, before the first row of an answer set is ready for delivery to the adapter or while waiting for access to an object that has been locked by another application.

Tip: You can change this setting manually or from the WebFOCUS Reporting Server browser interface by clicking Application on the menu bar, clicking Get Data, clicking a configured adapter, and choosing Change Settings from the shortcut menu. The Change Settings pane opens.
Note: This setting is not supported for the z/OS Unified Server Db2 CAF Adapter (all deployment modes).

Activate NONBLOCK Mode

ENGINE DB2 SET NONBLOCK {0|n}

where:

DB2

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

n

Is a positive numeric number. 0 is the default value, which means that the adapter will operate in BLOCK mode. A value of 1 or greater activates the NONBLOCK calling and specifies the time, in seconds, that the adapter will wait between each time it checks to see if the:

  • Query has been executed.
  • Client application has requested the cancellation of a query.
  • Kill Session button on the WebFOCUS Reporting Server browser interface is pressed.
Note: A value of 1 or 2 should be sufficient for normal operations.

Controlling Column Names

You can use the SET NOCOLUMNTITLE command to control the column names in a report when executing a stored procedure.

Control Column Names

ENGINE DB2 SET NOCOLUMNTITLE {ON|OFF}

where:

DB2

Indicates the adapter. You can omit this parameter value if you previously issued the SET SQLENGINE command.

ON

Uses generated column names (for example, E01, E02, and so on) instead of the column names returned by Db2.

OFF

Uses the column names returned by Db2. OFF is the default value.

Obtaining the Number of Rows Updated or Deleted

PASSRECS returns the number of rows affected by a successfully executed SQL Passthru INSERT, UPDATE, or DELETE command.

Tip: You can change this setting manually or from the WebFOCUS Reporting Server browser interface by clicking Get Data on the menu bar, clicking a configured adapter, and choosing Change Settings from the shortcut menu. The Change Settings pane opens.

Obtain the Number of Rows Updated or Deleted

ENGINE DB2 SET PASSRECS {ON|OFF}

where:

DB2

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

ON

Provides the number of rows affected in the application program SCB count member after the successful execution of an SQL Passthru INSERT, UPDATE, or DELETE command. ON is the default value.

OFF

Provides no information after the successful execution of an SQL Passthru INSERT, UPDATE, or DELETE command.

Note: This behavior applies for all supported versions of Db2 up to Version 13 on all platforms. In Db2 Version 13 on z/OS, the PASSRECS command does not provide a row count for mass DELETE operations (that is, DELETE statements without a WHERE clause). However, you can obtain a row count in this situation by adding a WHERE phrase to your report request. For example, the following request would generate a delete count:
SQL DB2 
DELETE FROM DB2TAB WHERE F1=15;
END
FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND: 1/DELETE

Setting End-User Information

In the UNIX and Windows WebFOCUS Reporting Server environment, you can set values for end-user information to be passed to a Db2 server when the next SQL request is processed. This information includes the:

  • Client user ID
  • Application program name
  • Workstation name
  • Accounting string

You can then query the information using SELECT statements.

Note: For a Db2 server on z/OS, you can also set the end-user information but only from a UNIX or Windows server connecting to a Db2 on z/OS.

Set End-User Information

ENGINE DB2 SET CLIENT_APPLNAME application_name 
ENGINE DB2 SET CLIENT_USERID userid 
ENGINE DB2 SET CLIENT_WRKSTNNAME workstation 
ENGINE DB2 SET CLIENT_ACCTNG account

where:

application_name

Is the name of an application program.

userid

Is the user ID of a client.

workstation

Is the name associated with the user workstation.

account

Is an accounting string associated with the client user.

Query End-User Information

In the UNIX and Windows WebFOCUS Reporting Server environment, depending on where the Db2 server is running, the end-user information can be queried using the syntax below.

If the Db2 server is on UNIX or Windows:

ENGINE DB2
SELECT CLIENT APPLNAME, CLIENT USERID, CLIENT WRKSTNNAME, CLIENT ACCTNG
FROM SYSIBM.SYSDUMMY1;
END

If the Db2 server is on z/OS:

ENGINE
DB2  SELECT CURRENT CLIENT_APPLNAME,CURRENT CLIENT_USERID,CURRENT
CLIENT_WRKSTNNAME, CURRENT CLIENT_ACCTNG  FROM SYSIBM.SYSDUMMY1;
END
Note: Natively on z/OS, you can also view end-user information by using spufi to submit the following command:
-DISPLAY THREAD(*) DETAIL

Setting Naming Conventions

Controls the separator character used for interpreting multipart table, view, and RPC names. This option is typically used to allow a request coded with "." as the separator to be run on systems that use "/" as the separator (for example, IBM i).

This setting is only valid for use with a server configured as CLI. (This applies to most platforms, including IBM i if so configured). If the server needs to support both naming conventions, you can configure an additional service block with a service block profile that uses the desired naming setting and sends requests to the desired block.

Tip: You can change this setting manually or from the WebFOCUS Reporting Server browser interface by clicking Application on the menu bar, clicking Get Data, clicking a configured adapter, and choosing Change Settings from the right-click menu. The Change Settings pane opens.

Set Naming Conventions

The available parameters are:

ENGINE DB2 SET NAMING {SQL|SYS}

where:

DB2

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

SQL

Standard "." character is used as separator in multipart table names.

SYS

System "/" character is used as separator in multipart table names.

Controlling HOLD DBMS Creation

An extension of the HOLD AS app/name FORMAT DB2 syntax enables you to exercise more precise control over the creation of HOLD DBMS files.

Control DBMS Creation

HOLD AS app/name FORMAT DB2 [TABLENAME dbms_name][CONNECTION conn_name]
  [DROP]

where:

dbms_name

Is the DBMS table to create. It may be a one, two, or three part name, using the separator appropriate to the DBMS (typically "." (dot)).

For IBM i, a "/" (slash) is the separator, unless the SET NAMING SQL command is being used to reset the separator character.

Note: For Db2 running on IBM i, if the TABLENAME parameter and value are omitted, the default location for the resulting table is the user's default login library. This applies to adapters configured for SQL and CLI.
conn_name

Is the DBMS connection name. When multiple DBMS connections have been configured and are in use, conn_name specifies which connection to use.

DROP

Drops the table before creation. This option enables you to delete either a known table or one that was created and stored in a temporary space when persistValue=global_temporary. Without the DROP option, if a table already exists, an error occurs when the table is created, resulting in failure to load the table.

If no table exists, this option is ignored.