Parameters That Apply to DB2 Only

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

In this section: BINDOPTIONS CURRENT DEGREE CURRENT SQLID ERRORTYPE PLAN SSID

BINDOPTIONS

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

The syntax is

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:

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 data 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 data 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 data 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 data adapter adds them automatically with their corresponding default values. To bind a package, you must specify the keyword PACKAGE, as PLAN is the default.

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

• Omit the DB2 target RDBMS qualifier if you previously issued the SET SQLENGINE command for DB2.
• Closing a DB2 thread restores the setting to '1', the default. Therefore, if you set AUTOCLOSE or AUTODISCONNECT to anything other than ON FIN (the data adapter default), you must reissue the SET CURRENT DEGREE command prior to each request in which you want it to apply.

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

ERRORTYPE

With SET ERRORTYPE, you can instruct the DB2 Data Adapter 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

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:

• Omit the DB2 target RDBMS qualifier if you previously issued the SET SQLENGINE command for DB2.
• The SET option overrides the installation default.
• This command is not available for DB2 for VM.

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 and DB2 for VM), 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:

• Omit the DB2 target RDBMS qualifier if you previously issued the SET SQLENGINE command for DB2.
• The default isolation level for the DB2 Data Adapter is CS. At installation time, your site may have chosen RR as the isolation level. Your DB2 database administrator can provide you with the isolation level for a given data adapter application plan. Refer to ISOLATION (DB2 and DB2 for VM) for more information.

The isolation level called Uncommitted Read (UR) provides read-only access to records even if they are locked; however, these records may not yet be committed to the database. For more information, see the DB2 Command and Utility Reference.

• Sometimes, a site binds two different plans during the installation process; each specifies a different isolation level to control RDBMS locking. You can use the SET PLAN command to, in effect, change your Isolation Level within a FOCUS session. A level of repeatable read (RR) is required for read/write MODIFY and Maintain applications including those that invoke the Change Verify Protocol (CVP); cursor stability (CS) is recommended for TABLE and read-only MODIFY or Maintain operations. Refer to ISOLATION (DB2 and DB2 for VM) for information.

This technique does not apply to non-CAF versions of the data adapter; they require a different CLIST or JCL procedure to invoke different versions of the data adapter.

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.

SSID

If the CAF option for the data 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:

• Omit the DB2 target RDBMS qualifier if you previously issued the SET SQLENGINE command for DB2.
• Non-CAF versions of the data adapter usually supply the subsystem-ID by specifying the DSN SYSTEM parameter and the RUN subcommand in the TSO CLIST that invokes FOCUS.

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 data adapter may have been installed with a default, site-specific, SSID setting. Use the SQL ? query command to display this setting.