Configuring the Adapter for DB2

In this section:

x

Configuring the adapter consists of specifying connection and authentication information for each of the connections you want to establish.


Top of page

x
Declaring Connection Attributes

How to:

Reference:

In order to connect to the DB2 database server, the adapter requires connection and authentication information. You supply this information using the SET CONNECTION_ATTRIBUTES command. You can:

You can declare connections to more than one DB2 database server by including multiple SET CONNECTION_ATTRIBUTES commands. The actual connection to the DB2 Server takes place when the first query that references the connection is issued. If you issue multiple SET CONNECTION_ATTRIBUTES commands:



x
Procedure: How to Declare Connection Attributes

You can configure the adapter from either the Web Console or the Data Management Console.

  1. From the Web Console menu bar, click Adapters.

    or

    From the Data Management Console, expand the Adapters folder.

    The Adapters folder opens.

  2. Expand the Available folder, if it is not already expanded.
  3. Expand the appropriate group folder and the specific adapter folder. The group folder is described in the connection attributes reference.
  4. Right-click the adapter name and/or version and select Configure.

    The Add Adapter to Configuration pane opens.

  5. Enter values for the parameters required by the adapter, as described in the connection attributes reference.
  6. Click Configure. The configured adapter is added to the Adapters list in the navigation pane.


x
Reference: Connection Attributes for DB2 With CLI

The DB2 adapter in under the SQL group folder.

The following list describes the connection attributes for which you can supply values. To complete the attribute declaration, click the Configure button.

Connection name

Logical name used to identify this particular set of connection attributes. The default is CON01.

Datasource

DB2 data source name (DSN). There is no default data source name. You must enter a value.

For IBM i, this is the Remote Database Directory entry or *LOCAL (for local host).

For z/OS, this is the DB2 location name as specified in the DB2 communications data source.

Security

There are three methods by which a user can be authenticated when connecting to a database server:

  • Explicit. The user ID and password are explicitly specified for each connection and passed to the database, at connection time, for authentication.
  • Password Passthru. The user ID and password received from the client application are passed to the database, at connection time, for authentication.
  • Trusted. The adapter connects to the database using the database rules for an impersonated process that are relevant to the current operating system.
User

Primary authorization ID by which you are known to the data source.

Password

Password associated with the primary authorization ID.

Select profile

Select a profile from the drop-down menu to indicate the level of profile in which to store the CONNECTION_ATTRIBUTES command. The global profile, edasprof.prf, is the default.

If you wish to create a new profile, either a user profile (user.prf) or a group profile if available on your platform (using the appropriate naming convention), choose New Profile from the drop-down menu and enter a name in the Profile Name field (the extension is added automatically).

Store the connection attributes in the server profile (edasprof).



x
Reference: Connection Attributes for DB2 With CAF

This chart describes the connection attributes for which you can supply values. To complete the attribute declaration, click the Configure button.

SSID

DB2 SSID that is to be accessed.

Plan

Plan name to be bound to DB2.

Execute DB2 BIND Command

For HFS deployment, indicates whether to bind the plan. Yes is the default value.

DSNCLST Library Name

Name of the DSNCLST library. For example: DSN710.SDSNCLST

This attribute is available only if you choose to bind your program.

ENCODING

Specifies the application encoding for all host variables in the plan. Valid values are ASCII, EBCDIC, UNICODE, ccsid.

DSNLOAD Library Name

Name of the DSNLOAD Library. For example: DSN710.SDSNLOAD

If this value was supplied at installation time, it will be displayed here.

Owner

Authorization ID of the Owner of the Plan.

This attribute is available only if you choose to bind your program

Isolation Level

Isolation level. CS is the default value.

This attribute is available only if you choose to bind your program.

Grant

Grants plan execution to public.

Select profile

Select a profile from the drop-down list to indicate the level of profile in which to store the CONNECTION_ATTRIBUTES command. The global profile, edasprof.prf, is the default.

If you wish to create a new profile, either a user profile (user.prf) or a group profile if available on your platform (using the appropriate naming convention), choose New Profile from the drop-down list and enter a name in the Profile Name input box (The extension is added automatically).



x
Reference: Connection Attributes for DB2 With SQL

On IBM i, the SQL mode of the adapter requires no parameters and is best applied when users can share a single default library as the default DB2 location for read and write. Metadata that explicitly specifies a library or collection for a table is unaffected by this.

To complete the declaration, click the Configure button.

Note: In the IBM i environment, isolation level is preset to NC (No Commit) so no action is required.

For a full IBM i installation, once the adapter is configured for IBM i you must go to the Change Settings pane and set an isolation level. Failure to set an explicit isolation level causes DB2 to generate multiple commit/rollback warning messages in the adapter's job log. Any isolation level may be selected for the purpose of stopping the multiple warning messages. For more information, see Controlling Types of Locks on IBM i.



x
Syntax: How to Declare Connection Attributes Manually

Explicit authentication. The user ID and password are explicitly specified for each connection and passed to DB2, at connection time, for authentication.

ENGINE DB2 SET CONNECTION_ATTRIBUTES connection DSN_name/userid,password

Password passthru authentication. The user ID and password are explicitly specified for each connection and passed to DB2, at connection time, for authentication.

ENGINE DB2 SET CONNECTION_ATTRIBUTES connection DSN_name/

Trusted authentication. The adapter connects to DB2 as a Windows login using the credentials of the Windows user impersonated by the server data access agent.

The available parameters are:

ENGINE DB2 SET CONNECTION_ATTRIBUTES connection DSN_name/,

where:

DB2

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

connection

Is the logical name used to identify this particular set of connection attributes.

Note that one blank space is required between connection and DSN_name.

DSN_name

Is the DB2 Data Source Name (DSN) you wish to access. It must match an entry in the odbc.ini file.

userid

Is the primary authorization ID by which you are known to DB2.

password

Is the password associated with the primary authorization ID.



Example: Declaring Connection Attributes

The following SET CONNECTION_ATTRIBUTES command allows the application to access the DB2 database server named SAMPLESERVER with an explicit user ID (MYUSER) and password (PASS). To ensure security, specify connection attributes from the Web Console, which encrypts the password before adding it to the server profile.

ENGINE DB2 SET CONNECTION_ATTRIBUTES CON01 SAMPLESERVER/MYUSER,PASS

The following SET CONNECTION_ATTRIBUTES command connects to the DB2 database server named SAMPLESERVER using Password Passthru authentication:

ENGINE DB2 SET CONNECTION_ATTRIBUTES CON01 SAMPLESERVER/

The following SET CONNECTION_ATTRIBUTES command connects to a local DB2 database server using operating system authentication:

ENGINE DB2 SET CONNECTION_ATTRIBUTES CON01 SAMPLESERVER/,


x
Reference: Updating the Connection String

The syntax for the CONNECTION_ATTRIBUTES command for this adapter has been enhanced to include a logical connection name that is designed to support the porting of applications from development to production environments. This enhanced syntax may necessitate the migration of existing CONNECTION_ATTRIBUTES commands.

The Migrate option on the Web Console migrates your server settings to the newer release. To access this option, choose Workspace, then Migrate from the menu bar. On the Migrate pane, type the full path of the configuration instance directory (EDACONF) and click the Migrate button. This is the recommended approach.

If you choose not to use the Migrate option, please note the following information:


Top of page

x
DB2 CURRENT SQLID (z/OS)

How to:

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

Any interactive user or batch program that accesses a DB2 subsystem is identified by a primary authorization ID. A security system, such as RACF, normally manages the ID. During the process of connecting to DB2, the primary authorization ID may be associated with one or more secondary authorization IDs (usually RACF groups). Each site controls whether it uses secondary authorization IDs. For more information about using the adapter in conjunction with external security packages, see the server manual for your platform.

The primary authorization ID is the same ID passed to the server at connect time. This user ID is then used to connect to the DB2 subsystem.

The DB2 Data Source 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 a secondary ID and grants the privileges to the secondary ID.)

The DB2 CURRENT SQLID may be the primary authorization ID or any associated secondary authorization ID. At the beginning of the session, the CURRENT SQLID is the primary authorization ID.



x
Syntax: How to Reset CURRENT SQLID

You can reset the CURRENT SQLID in a stored procedure or a profile using the following adapter command using the following parameters:

ENGINE DB2 SET CURRENT SQLID = 'sqlid'

where:

DB2

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

sqlid

Is the primary or secondary authorization ID, which must be enclosed in single quotation marks as shown. All DB2 security rules are respected.

The CURRENT SQLID is the default owner ID for DB2 objects, such as tables or indexes, created with dynamic SQL commands. The CURRENT SQLID is also the sole authorization ID for GRANT and REVOKE commands. It must have all the privileges needed to create objects as well as GRANT and REVOKE privileges. In addition, the CURRENT SQLID is the implicit owner for unqualified table names.

Other types of requests, such as SQL SELECT, INSERT, UPDATE, or DELETE requests, automatically search for the necessary authorization 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 remains in effect until the thread to DB2 is disconnected, when it reverts to the primary authorization ID.


Top of page

x
Overriding the Default Connection

How to:

Once connections have been defined, the connection named in the first SET CONNECTION_ATTRIBUTES command serves as the default connection. You can override this default using the SET DEFAULT_CONNECTION command.



x
Syntax: How to Change the Default Connection
ENGINE DB2 SET DEFAULT_CONNECTION connection

where:

DB2

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

connection

Is the connection defined in a previously issued SET CONNECTION_ATTRIBUTES command. If this name was not previously declared, the following message is issued:

FOC1671, Command out of sequence

Note:



Example: Selecting the Default Connection

The following SET DEFAULT_CONNECTION command selects the database server named SAMPLE as the default database server:

ENGINE DB2 SET DEFAULT_CONNECTION SAMPLE

Top of page

x
Controlling Connection Scope

In this section:

The SET AUTODISCONNECT and AUTOCLOSE commands control the persistence of connections when using the adapter.



x
Controlling Connection Scope With AUTOCLOSE (z/OS)

SET AUTOCLOSE initiates the DB2 Call Attachment Facility (CAF) CLOSE operation. It determines how long a thread (the connection between the application program in the user's address space and the DB2 application plan) is open. The thread is not the same as the address space connection to DB2; that connection is controlled by the AUTODISCONNECT setting.

In the server, an application program is one of the following:

Generally speaking, each program has a corresponding plan or package.

A site that installs a DB2 subsystem determines the maximum number of concurrent users (threads) the subsystem will support. Since each user requires enough virtual storage for their application plan, this setting controls the amount of storage the site wants to allocate to active DB2 users at any one time.

The CAF CLOSE command deallocates the DB2 thread, releasing the virtual storage for the application plan. DB2 requires that an existing thread to a plan be closed before a thread to another plan is opened. If a thread is closed without a subsequent OPEN operation, the closed thread becomes "inactive"; the user is still connected to DB2, but not to a particular application plan. The user (task) still owns the thread; it is not available to other users. To release the thread, the user must disconnect completely from DB2.

Note: The term pseudo-conversational describes the type of transaction processing provided when you use AUTOCLOSE ON COMMIT.



x
Syntax: How to Control Connection Scope With AUTOCLOSE
ENGINE DB2 SET AUTOCLOSE ON {FIN|COMMIT}

where:

DB2

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

AUTOCLOSE

Issues the DB2 Call Attach Facility (CAF) CLOSE operation.

FIN

Disconnects automatically only after the server session has been terminated. FIN is the default value.

COMMIT

Disconnects automatically only after COMMIT or ROLLBACK is issued as a native SQL command.



x
Controlling Connection Scope With AUTODISCONNECT

AUTODISCONNECT completely detaches the users address space (or task) from DB2. After a DISCONNECT, the task must re-establish its connection to DB2 before performing any data source work. The tasks that frequently issue the DISCONNECT command are connected to DB2 for shorter periods of time, allowing other tasks to connect and acquire threads as needed. However, there is significant system overhead associated with frequently connecting and disconnecting, and the possibility exists that no thread will be immediately available when the task attempts to reconnect.

Tip: You can change this setting manually or from the Web Console by clicking Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the right-click menu. The Change Settings pane opens.



x
Syntax: How to Control Connection Scope With AUTODISCONNECT
x
ENGINE DB2 SET AUTODISCONNECT ON {FIN|COMMIT}

where:

DB2

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

FIN

Disconnects automatically only after the session has been terminated. FIN is the default value.

COMMIT

Disconnects automatically only after COMMIT or ROLLBACK is issued as a native SQL command.


WebFOCUS