|
Topics: |
Configuring the adapter consists of specifying connection and authentication information for each of the connections you want to establish.
|
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:
You can configure the adapter from either the Web Console or the Data Management Console.
or
From the Data Management Console, expand the Adapters folder.
The Adapters folder opens.
The Add Adapter to Configuration pane opens.
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.
Logical name used to identify this particular set of connection attributes. The default is CON01.
Db2 data source name (DSN), is a locally registered alias of a remote DB2 database. There is no default data source name. You must enter a value. If you do not want to register the DSN, you can use a DSN-less connection on Linux, UNIX, or Windows, where you specify all of the required parameters. For information, see below.
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.
Applies to Linux, UNIX, and Windows. Check this box to specify the following connection parameters instead of a DSN. Note that bulk load is not supported with this type of connection.
Is the name of the host where the Db2 server is running.
Is the port number on which the Db2 server is listening.
Is the Db2 database name.
There are three methods by which a user can be authenticated when connecting to a database server:
Primary authorization ID by which you are known to the data source.
Password associated with the primary authorization ID.
Select this check box to enable a Db2 trusted context, which is a database object that defines a trust relationship for a connection between the database and an external application server. The trust relationship is based upon the attributes defined in the trusted context object.
If you select Enable, the following is added after the password in the SET CONNECTION ATTRIBUTES command:
:'trusted_context=y'
For example, assume the Reporting server is configured with LDAP security and the WebFOCUS Client is configured with SECURITY TRUSTED. From the WebFOCUS Client, the Web Console is opened without an authentication prompt and connection to the server is trusted. The adapter connects to the Db2 database with the user ID in the connection string. It checks for a trusted context object and then switches users.
You can enter any additional connection string keywords separated by semicolons (;). Each keyword consists of an attribute and value pair in the form attribute=value.
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).
This chart describes the connection attributes for which you can supply values. To complete the attribute declaration, click the Configure button.
Db2 SSID that is to be accessed.
Plan name to be bound to Db2.
For HFS deployment, indicates whether to bind the plan. Yes is the default value.
Name of the Db2 installation DSNCLST library.
This attribute is available only if you choose to bind your program.
Specifies the application encoding for all host variables in the plan. Valid values are ASCII, EBCDIC, UNICODE, ccsid.
Name of the Db2 installation DSNLOAD Library.
If this value was supplied at installation time, it will be displayed here.
Authorization ID of the Owner of the Plan.
This attribute is available only if you choose to bind your program
Isolation level. CS is the default value.
This attribute is available only if you choose to bind your program.
Grants plan execution to public.
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).
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.
Note: In the IBM i environment, isolation level is preset to NC (No Commit) so no action is required.
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:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
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.
Is the Db2 data source name (DSN) you wish to access. It must match an entry in the odbc.ini file.
Is the primary authorization ID by which you are known to Db2.
Is the password associated with the primary authorization ID.
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/,
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:
For example, suppose that prior 7.6.1 the connection was defined as:
ENGINE DB2 SET CONNECTION_ATTRIBUTES DSN_A/uid,pwd
When synonyms based on objects stored in this DSN_A are created, the Access Files contains the following description:
CONNECTION=DSN_A
If you then add a new connection, you must change the connection name from the default CON01 to DSN_A and save it as DSN_A in order to reuse the existing synonym. The connection is stored in the profile as:
ENGINE DB2 SET CONNECTION_ATTRIBUTES DSN_A DSN_A/uid,pwd
|
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.
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:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
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.
|
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.
ENGINE DB2 SET DEFAULT_CONNECTION connection
where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
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
FOC1671, Command out of sequence.
The following SET DEFAULT_CONNECTION command selects the database server named SAMPLE as the default database server:
ENGINE DB2 SET DEFAULT_CONNECTION SAMPLE
|
Topics: |
The SET AUTODISCONNECT and AUTOCLOSE commands control the persistence of connections when using the adapter.
|
How to: |
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.
ENGINE DB2 SET AUTOCLOSE ON {FIN|COMMIT}where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
Issues the Db2 Call Attach Facility (CAF) CLOSE operation.
Disconnects automatically only after the server session has been terminated. FIN is the default value.
Disconnects automatically only after COMMIT or ROLLBACK is issued as a native SQL command.
|
How to: |
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.
ENGINE DB2 SET AUTODISCONNECT ON {FIN|COMMIT}where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
Disconnects automatically only after the session has been terminated. FIN is the default value.
Disconnects automatically only after COMMIT or ROLLBACK is issued as a native SQL command.
| WebFOCUS | |
|
Feedback |