Create databases and users for DB2 for z/OS

Create databases and users for DB2 for z/OS

+
Search Tips | Advanced Search

You are here

Planning for DB2 for z/OS
Installing DB2 for z/OS
Creating databases and users for DB2 for z/OS (Current task)
Transferring DB2 for z/OS manually or Transferring between databases manually
Verifying database connections

This topic includes information on setting up IBM DB2 Universal Database™ for z/OS to work with IBM WebSphere Portal.
You must 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 z/OS.

Before you begin

You should have completed Installing DB2 for z/OS.

Important
Notes:

If WebSphere Portal Version 6.0 and an earlier version of WebSphere Portal coexist, the database user IDs for WebSphere Portal Version 6.0 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 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 z/OS subsystem.

The tablespace LMTABLESPACE is reserved for LikeMinds. LikeMinds and Personalization will drop this tablespace during uninstallation of LikeMinds and certain database transfer tasks.

Create a remote DB2 for z/OS database

A remote database resides on a different machine than WebSphere Portal. You must manually create the required databases before configuring WebSphere Portal to work with DB2 for z/OS.
Before creating these databases, note the following information:

These instructions assume that you will use a local IBM DB2 Universal Database Enterprise Server Edition Connect to connect to a remote DB2 for z/OS server, and that IBM WAS, WebSphere Portal, and DB2 Connect will be installed on the same machine.

To run SQL statements, we 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 we can specify a specific 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 the subsystem. We can 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 z/OS.

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

Create the databases using the following statements. Refer to Planning for DB2 for 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.)

CREATE DATABASE releasenameonzos CCSID UNICODE;

CREATE DATABASE communitynameonzos CCSID UNICODE;

CREATE DATABASE customizationnameonzos CCSID UNICODE;

Execute the instructions in Creating the DB2 Content Manager Runtime Edition database.

CREATE DATABASE fdbkdbnameonzos CCSID UNICODE;

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

CREATE DATABASE lmdbnameonzos CCSID UNICODE;

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

...where:

releasenameonzos, communitynameonzos, and customizationnameonzos are the WebSphere Portal databases 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

Create all database user IDs in the security product you are using on z/OS. For jcrschema, the database schema name for Content Repository data, create a group and connect the database user ID, icmadmin, for Content Repository data to it. The following sample shows the RACF definition of such a user ID and group:
ADDUSER icmadmin DFLTGRP(yourDefaultUserGroup) NAME('WAS DB2 ACCESS USER') PW USER(icmadmin) NOINTERVAL ALU icmadmin PASSWORD(********) NOEXPIRED ADDGROUP jcrschema SUPGROUP(yourDefaultGroup) CONNECT icmadmin GROUP(jcrschema)
where:

icmadmin is the database user ID for Content Repository data

yourDefaultUserGroup is the default RACF group for database user IDs

jcrschema is the database schema name for Content Repository data

yourDefaultGroup is the default RACF group for groups

If you have some other security product such as Top Secret or ACF2 instead of RACF, then translate the sample RACF definition into the appropriate syntax before executing.

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 releasenameonzos TO releaseusr WITH GRANT OPTION; GRANT USE OF ALL BUFFERPOOLS TO releaseusr; GRANT DBADM ON DATABASE communitynameonzos TO communityusr WITH GRANT OPTION; GRANT USE OF ALL BUFFERPOOLS TO communityusr; GRANT DBADM ON DATABASE customizationnameonzos TO customizationusr WITH GRANT OPTION; GRANT USE OF ALL BUFFERPOOLS TO customizationusr; GRANT DBADM ON DATABASE jcrdbnameonzos TO icmadmin WITH GRANT OPTION; GRANT USE OF ALL BUFFERPOOLS TO icmadmin; GRANT DBADM ON DATABASE fdbkdbnameonzos TO feedback WITH GRANT OPTION; GRANT USE OF ALL BUFFERPOOLS TO feedback; GRANT DBADM ON DATABASE lmdbnameonzos TO lmdbusr WITH GRANT OPTION; GRANT USE OF ALL BUFFERPOOLS TO lmdbusr; GRANT SELECT ON SYSIBM.SYSCOLUMNS TO releaseusr; GRANT SELECT ON SYSIBM.SYSCOLUMNS TO communityusr; GRANT SELECT ON SYSIBM.SYSCOLUMNS TO customizationusr; GRANT SELECT ON SYSIBM.SYSTABLES TO releaseusr; GRANT SELECT ON SYSIBM.SYSTABLES TO communityusr; GRANT SELECT ON SYSIBM.SYSTABLES TO customizationusr; 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; GRANT SELECT ON SYSIBM.SYSFOREIGNKEYS TO releaseusr; GRANT SELECT ON SYSIBM.SYSFOREIGNKEYS TO communityusr; GRANT SELECT ON SYSIBM.SYSFOREIGNKEYS TO customizationusr; GRANT SELECT ON SYSIBM.SYSFOREIGNKEYS TO icmadmin; GRANT SELECT ON SYSIBM.SYSFOREIGNKEYS TO fdbkdbusr; GRANT SELECT ON SYSIBM.SYSFOREIGNKEYS TO lmdbusr; GRANT SELECT ON SYSIBM.SYSRELS TO releaseusr; GRANT SELECT ON SYSIBM.SYSRELS TO communityusr; GRANT SELECT ON SYSIBM.SYSRELS TO customizationusr; GRANT SELECT ON SYSIBM.SYSRELS TO icmadmin; GRANT SELECT ON SYSIBM.SYSRELS TO fdbkdbusr; GRANT SELECT ON SYSIBM.SYSRELS TO lmdbusr; GRANT USE OF STOGROUP JCRICMSG TO icmadmin; GRANT CREATEIN, DROPIN ON SCHEMA jcrschema TO icmadmin; GRANT SELECT ON SYSIBM.SYSTABLESPACE TO icmadmin; GRANT SELECT ON SYSIBM.SYSVIEWS TO icmadmin; GRANT SELECT ON SYSIBM.SYSDUMMY1 TO icmadmin; GRANT SELECT ON SYSIBM.SYSTRIGGERS TO icmadmin; GRANT SELECT ON SYSIBM.SYSINDEXPART TO icmadmin; GRANT SELECT ON SYSIBM.SYSINDEXES TO icmadmin; GRANT SELECT ON SYSIBM.SYSSYNONYMS TO icmadmin;
where:

releasenameonzos, communitynameonzos, and customizationnameonzos, and releaseusr, communityusr, and customizationusr represent the databases and database users, respectively, of the WebSphere Portal instance you are setting up. (These users must be created on the host system.)

jcrdbnameonzos and icmadmin are the database and database user, respectively, for Content Repository data.

fdbkdbnameonzos and feedback are the database and database user, respectively, for Feedback data.

lmdbnameonzos and lmdbusr are the database and database user, respectively, for Likeminds data.

jcrschema is the database schema name for Content Repository data.

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

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.

On the local DB2 machine, use a text editor to open 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

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

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"

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 releaselcl as zoslocname db2 catalog database releaselcl as release 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 db2 catalog dcs database communitylcl as zoslocname db2 catalog database communitylcl as community at node zos_node authentication dcs db2 catalog dcs database customizationlcl as zoslocname db2 catalog database customizationlcl as customization at node zos_node authentication dcs
where the zos_node is a name that you are defining to be used internally only and release, community, customization, 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 ones used for the other portals.

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 release user admin_id using admin_password; db2 connect to community user admin_id using admin_password; db2 connect to customization 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 http://www.ibm.com/software/data/db2/os390/library.html for details on required authority for this command.

For more information, see the DB2 Version 8 Connectivity Cheat Sheet : http://www-128.ibm.com/developerworks/db2/library/techarticle/0301chong/0301chong2.html

Next steps

You have completed this step. Continue to the next step:

Transfer DB2 for z/OS manually

Parent topic:
Create databases and users