Create databases and users for DB2 for OS/390 and z/OS

 

This topic includes information on setting up DB2 for OS/390 and z/OS to work with WebSphere Portal.

complete this task to manually create the databases and database users required by WebSphere Portal before performing database migrating or configuring WebSphere Portal to work with DB2 for OS/390 and z/OS.

 

Before you begin

Important Notes:

  • If WebSphere Portal V5.1 and an earlier version of WebSphere Portal coexist, the database user IDs for WebSphere Portal V5.1 must be different than earlier versions to avoid conflicts during installation.

  • If you are configuring multiple WebSphere Portal instances to use a single DB2 for OS/390 and z/OS subsystem, create a unique database user for each WebSphere Portal instance. By default all database table names include the name of the database user used to access the data. Therefore, to prevent table name conflicts, create a unique database user for each WebSphere Portal instance on the shared DB2 for OS/390 and z/OS subsystem.

 

Create a remote DB2 for OS/390 and z/OS database

A remote database resides on a different machine than WebSphere Portal. manually create the required databases before configuring WebSphere Portal to work with DB2 for OS/390 and z/OS.

Before creating these databases, note the following information:

  • These instructions assume that you will use a local DB2 Connect to connect to a remote DB2 for OS/390 and z/OS server, and that WebSphere Application Server, WebSphere Portal, and DB2 Connect will be installed on the same machine.

  • To run SQL statements, you can use a tool like SPUFI. The specified statements include CREATE STOGROUP, CREATE DATABASE and CREATE TABLESPACE. For CREATE STOGROUP you have to replace the icmvolumes and icmvcat variables with volume serial numbers and a catalog name for your environment. For CREATE TABLESPACE you can specify a sepcifc BUFFERPOOL. Otherwise the default bufferpool BP0 will be used, which is not recommended for performance reasons. Refer to the DB2 Universal Database for OS/390 and z/OS SQL Reference for a description of these options.

  • Ensure that a TEMP database is created for your subsystem. Use the following statements to create a TEMP database: 
    CREATE DATABASE db_name AS TEMP;
CREATE TABLESPACE ts_name IN db_name;

Use the following steps to set up WebSphere Portal with a remote instance of DB2 for OS/390 and z/OS.

  1. Log on to the DB2 subsystem on the host server. DB2 system administrator rights are needed to create the databases.

  2. Create the databases using the following statements. Refer to Plan for DB2 for OS/390 and z/OS for a list of databases and table space names. ( If you are configuring multiple WebSphere Portal instances to use a single DB2 subsystem, be sure to use the database and table space names associated with the WebSphere Portal instance you are setting up.)

    1. CREATE DATABASE wpsdbnameonzos CCSID UNICODE;

    2. Execute the instructions in creating the DB2 Content Manager Runtime Edition database.

    3. CREATE DATABASE fdbkdbnameonzos CCSID UNICODE;

    4. CREATE TABLESPACE fdbkdbts IN fdbkdbnameonzos USING STOGROUP SYSDEFLT PRIQTY 5000 SECQTY 500;

    5. CREATE DATABASE lmdbnameonzos CCSID UNICODE;

    6. CREATE TABLESPACE lmdbts IN lmdbnameonzos USING STOGROUP SYSDEFLT PRIQTY 5000 SECQTY 500;

    where:

    • wpsdbnameonzos is the WebSphere Portal database for WebSphere Portal and Member Manager data.

    • fdbkdbnameonzos and fdbkdbts are the database and table space, respectively, for Feedback data.

    • lmdbnameonzos and lmdbts are the database and table space, respectively, for Likeminds data.

 

Granting access rights for WebSphere Portal administrators

Use the following steps to grant access permissions to the database users.

  1. While logged on to the DB2 subsystem, use a tool like SPUFI to run the following SQL statements to grant appropriate rights on the newly created databases: 
    GRANT DBADM ON DATABASE wpsdbnameonzos TO wpsdbusr;
GRANT DBADM ON DATABASE jcrdbnameonzos TO icmadmin;
GRANT DBADM ON DATABASE fdbkdbnameonzos TO feedback;
GRANT DBADM ON DATABASE lmdbnameonzos TO lmdbusr;
GRANT SELECT ON SYSIBM.SYSCOLUMNS TO wpsdbusr;
GRANT SELECT ON SYSIBM.SYSTABLES TO wpsdbusr;
GRANT SELECT ON SYSIBM.SYSCOLUMNS TO icmadmin;
GRANT SELECT ON SYSIBM.SYSTABLES TO icmadmin;
GRANT SELECT ON SYSIBM.SYSCOLUMNS TO fdbkdbusr;
GRANT SELECT ON SYSIBM.SYSTABLES TO fdbkdbusr;
GRANT SELECT ON SYSIBM.SYSCOLUMNS TO lmdbusr;
GRANT SELECT ON SYSIBM.SYSTABLES TO lmdbusr
    where:

    If you are configuring multiple WebSphere Portal instances to use a single DB2 for OS/390 and z/OS subsystem, be sure to use the database user associated with the WebSphere Portal instance you are setting up.

  2. Grant the necessary access rights to all users who might require them. Depending on the architecture that you choose, these users might include a Member Manager and DB2 Content Manager Runtime Edition and Feedback users.

Repeat these steps for each WebSphere Portal instance you are setting up.

 

Create a connection to the host database

Use the following instructions to create a connection to the host database.

  1. On the local DB2 machine, edit the /etc/services file. If it does not specify the DB2 connection service port, add the following text to specify the port for the remote DB2 instance (where db2inst1 is the name of the DB2 instance ID on the system): 
    db2cdb2inst1 50000/tcp # DB2 connection service port

  2. On the local DB2, set DB2COMM to TCP/IP by using the db2set command, as follows: 

    db2set DB2COMM=TCPIP

  3. Set up the correct service name by entering the following command on the local DB2 (where svce_name is the connection service name that is specified in step 1): 
    db2 "UPDATE DBM CFG USING svcename svce_name"

  4. Run the following SQL statements in the DB2 Command Line Processor or the DB2 Command Center to set up the connection to the host DB2 subsystem 
    db2 catalog tcpip node zos_node remote hostname server zos_port ostype OS390
db2 catalog dcs database wpsdblcl as zoslocname
db2 catalog database wpsdblcl as wpsdb at node zos_node authentication dcs

    The following is optional. Use only if multiple database aliases and connections are used: 

    db2 catalog dcs database jcrdblcl as zoslocname
db2 catalog database jcrdblcl as jcrdb at node zos_node authentication dcs
db2 catalog dcs database fdbkdblcl as zoslocname
db2 catalog database fdbkdblcl as fdbkdb at node zos_node authentication dcs
db2 catalog dcs database lmdblcl as zoslocname
db2 catalog database lmdblcl as lmdb at node zos_node authentication dcs
    where the zos_node is a name that you are defining to be used internally only and wpsdb, jcrdb, fdbkdb and lmdb are the local database aliases that you are defining and will be entered into the appropriate DbName variables when configuring WebSphere Portal. For additional information, refer to the DB2 Universal Database Command Reference V7.

    If you are configuring multiple WebSphere Portal instances to use a single DB2 subsystem, be sure that the new database names are different from the one(s) used for the other portal(s).

  5. Test the connection and bind one of the databases that you created. Type the following commands from the command line processor to perform these steps: 
    db2 connect to wpsdb user admin_id using admin_password;
db2 bind db2-path-to-bnd-file\@db2ubind.lst blocking all grant public;
db2 bind db2-path-to-bnd-file\@db2cli.lst blocking all grant public;
db2 bind db2-path-to-bnd-file\@ddcsmvs.lst blocking all grant public sqlerror continue
    The admin_id must be a z/OS user ID that has sufficient authority to perform a bind, for example, SYSCTRL or SYSADM authority. See DB2 for OS/390 and z/OS product documentation for details on required authority for this command.

 

Next steps

 

WebSphere is a trademark of the IBM Corporation in the United States, other countries, or both.

 

IBM is a trademark of the IBM Corporation in the United States, other countries, or both.