Parameters That Apply to DB2 Only

In this section:

The following adapter environmental commands apply only to DB2, SET BINDOPTIONS, SET CURRENT DEGREE, SET CURRENT SQLID, SET ERRORTYPE, SET ISOLATION, SET PLAN, and SET SSID. (SET AUTOCLOSE also applies to DB2 only and is discussed in Controlling Connection Scope.)


Top of page

x
BINDOPTIONS

You can override the default BIND string that the adapter creates when you compile a static MODIFY procedure (with SET STATIC ON).

The syntax is

{ENGINE|SQL} [DB2] SET BINDOPTIONS [bind_spec]

where:

bind_spec

Is the portion of the BIND command that contains the BIND keywords and parameters (not including the BIND keyword itself). These BIND keywords and parameters must conform to rules governing the BIND PLAN and BIND PACKAGE commands described in the IBM DB2 Command and Utility Reference.

Note: Do not include the word BIND as part of the bind_spec.

To reset the default options, issue the command with no bind_spec:

SQL DB2 SET BINDOPTIONS

Note: Omit the DB2 target RDBMS qualifier if you previously issued the SET SQLENGINE command for DB2.

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

SQL DB2
SET BINDOPTIONS bind_spec 
END

The adapter default BIND string has the form:

BIND PLAN (focexec_name) MEM(focexec_name) ACTION(REPLACE) ISOLATION(CS)

You can determine the current setting for BINDOPTIONS with the adapter SQL DB2 ? query command. If the current setting is the default, BINDOPTIONS does not display in the SQL DB2 ? output.

Note: If the bind string specifies the MEM keyword, the adapter ignores it and supplies the MEM keyword with a parameter value equal to the FOCEXEC name. If the bind string omits any of the other three default keywords, PLAN/PACKAGE, ACTION, or ISOLATION, the adapter adds them automatically with their corresponding default values. To bind a package, you must specify the keyword PACKAGE, as PLAN is the default.


Top of page

x
CURRENT DEGREE

DB2 supports parallel query I/O and parallel query CPU to improve response. You can bind static SQL requests with the DEGREE(ANY) parameter to take advantage of parallel processing. For dynamic SQL requests, the adapter supports parallel processing if you issue the SET CURRENT DEGREE command prior to the request.

The syntax is

SQL [DB2] SET CURRENT DEGREE {'1'|'ANY'}

where:

1

Is the default. Does not invoke parallel processing.

ANY

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

Note:


Top of page

x
CURRENT SQLID

The DB2 RDBMS recognizes two types of ID, the primary authorization ID and one or more optional secondary authorization IDs. It also recognizes the CURRENT SQLID setting.

An ID known as a primary authorization ID identifies an interactive user or batch program accessing a DB2 subsystem. A security system such as RACF normally provides the ID to DB2. During the process of connecting to DB2, the primary authorization ID may be associated with one or more secondary authorization IDs (usually RACF groups). The site controls whether it uses secondary authorization IDs.

The DB2 database administrator may grant privileges to a secondary authorization ID that are not granted to the primary ID. Thus, secondary authorization IDs provide the means for granting the same privileges to a group of users. (The DBA associates individual primary IDs with the same secondary ID and then grants the privileges to the secondary ID.)

The DB2 CURRENT SQLID can be the primary authorization ID or any associated secondary authorization ID. At the beginning of the FOCUS session, the CURRENT SQLID is the primary authorization ID. You can reset it using the following adapter command

SQL [DB2] SET CURRENT SQLID  'sqlid'

where:

sqlid

Is the desired primary or secondary authorization ID, enclosed in single quotation marks. All DB2 security rules are respected.

Note: Omit the DB2 target RDBMS qualifier if you previously issued the SET SQLENGINE command for DB2.

Unless you issue the SET OWNERID command, the CURRENT SQLID is the default owner ID for DB2 objects (such as tables or indexes) created with dynamic SQL statements. (For example, the FOCUS CREATE FILE command issues dynamic SQL statements.) The CURRENT SQLID is also the sole authorization ID for GRANT and REVOKE statements. It must be assigned all the privileges needed to create objects as well as GRANT and REVOKE privileges. If you do not issue the SET OWNERID command, the CURRENT SQLID is assumed to be the owner for unqualified table names.

Other types of requests, such as FOCUS TABLE (SQL SELECT) and MODIFY (SQL SELECT, INSERT, UPDATE, or DELETE) requests, automatically search for the necessary privileges using the combined privileges of the primary authorization ID and all of its associated secondary authorization IDs, regardless of the DB2 CURRENT SQLID setting. The CURRENT SQLID setting stays in effect until the communication thread to DB2 is disconnected, when it reverts to the primary authorization ID.


Top of page

x
ERRORTYPE

With SET ERRORTYPE, you can instruct the Adapter for DB2 to return native DB2 error messages, as well as FOCUS error messages, for those error conditions that are reported by the DBMS. This feature can be enabled both as an installation option and as a run-time SET parameter.

You can override the installation default setting at run time. The syntax is

{ENGINE|SQL} [DB2] SET ERRORTYPE {FOCUS|DBMS}

where:

FOCUS

Generates only FOCUS error messages.

DBMS

Produces native DBMS error messages as well as FOCUS error messages.

Note:


Top of page

x
ISOLATION (DB2)

How to:

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.

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.



x
Syntax: How to Dynamically Change the Isolation Level

From the FOCUS command level, issue

{ENGINE|SQL} [DB2] SET ISOLATION level 

where:

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. 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. For more information, see the DB2 Command and Utility Reference.

blank resets the level to the adapter default.

Note:

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


Top of page

x
PLAN

In addition to the DB2 subsystem-ID, you must identify your DB2 application plan before you execute any requests. The plan is a result of the data adapter installation process or, possibly, the result of compiling a procedure that uses static SQL. See Static SQL (DB2), for a discussion of static SQL procedures.

In a CAF environment, issue the SET PLAN command from the FOCUS command level

SQL [DB2] SET PLAN planname 

where:

planname

Is the name of your application plan.

Note:

You can reset the plan name at any time, regardless of the AUTOCLOSE setting. If you change the plan name, the next native SQL command or FOCUS request uses the new plan by closing and re-opening the thread.

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


Top of page

x
SSID

If the CAF option for the adapter is installed at your site, you must indicate which DB2 system you intend to use. The name for the DB2 system may differ from the default, or your site may have multiple copies of DB2. To specify the DB2 subsystem-ID (SSID), issue the SET SSID command before executing native SQL commands or FOCUS requests.

Issue the following from the FOCUS command level or include it in a PROFILE FOCEXEC

SQL [DB2] SET SSID ssid 

where:

ssid

Is the DB2 subsystem ID. The default is DSN, unless your site changed the default at installation time.

Note:

You can reset the SSID at any time, regardless of the AUTOCLOSE setting. If you change the SSID setting, the next native SQL command or FOCUS request accesses the new DB2 subsystem.

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


Top of page

x
NOCOLUMNTITLE

How to:

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



x
Syntax: How to 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.


Information Builders