CREATE USER MAPPING statement - Examples for the Db2 wrapper

Use the CREATE USER MAPPING statement to map a federated server authorization ID to an Db2 server user ID and password.

Complete example

The following example shows how to map a federated server authorization ID to a remote Db2 user ID and password:

CREATE USER MAPPING FOR DB2INST1 SERVER DB2SERVER 
       OPTIONS (REMOTE_AUTHID 'al', REMOTE_PASSWORD 'day2night')

DB2INST1: You can use any valid local Db2 user ID such as a database instance owner or other users to map the authorization ID for the federated database that is specified in the REMOTE_AUTHID user option.
SERVER DB2SERVER: Specifies the name of the Db2 family data source server that you defined in the CREATE SERVER statement.
REMOTE_AUTHID 'al': Specifies the connect user ID at the Db2 family data source server to which you are mapping ALONZO. The value is case-sensitive, unless you set the FOLD_ID server option to 'U' or 'L' in the CREATE SERVER statement.
REMOTE_PASSWORD 'day2night': Specifies the password that is associated with 'al'. The value is case-sensitive, unless you set the FOLD_PW server option to 'U' or 'L' in the CREATE SERVER statement.

Special register example

The following is an example of the CREATE USER MAPPING statement which includes the special register USER:

CREATE USER MAPPING FOR USER SERVER DB2SERVER 
       OPTIONS (REMOTE_AUTHID 'al', REMOTE_PASSWORD 'day2night')

You can use the Db2 special register USER to map the authorization ID of the person issuing the CREATE USER MAPPING statement to the data source user ID specified in the REMOTE_AUTHID user option.

Public user mapping example

In Federation component, you can create a public user mapping to allow all local database users to access a data source through a single remote user ID and password.

In this example, a DRDA wrapper and the Db2 for z/OS® data source server SERVER390 are created. Then the CREATE USER MAPPING statement is specified with PUBLIC to create a public user mapping to the server SERVER390. To map all local database users to the authorization ID of server SERVER390, the authorization ID that is specified in the CREATE SERVER statement is also specified in the OPTIONS of the CREATE USER MAPPING statement.

Example

CREATE WRAPPER DRDA; 

CREATE SERVER SERVER390 
    TYPE DB2/ZOS VERSION 7.1 WRAPPER DRDA 
    AUTHORIZATION "APP_USER" PASSWORD "secret" 
    OPTIONS (DBNAME 'remotedb');

CREATE USER MAPPING FOR PUBLIC SERVER SERVER390 
    OPTIONS (REMOTE_AUTHID ‘APP_USER', REMOTE_PASSWORD ‘secret');