Preparing a DB2 for z/OS database

Before installing the TADDM server, prepare the DB2® for z/OS® database.

You must first install the DB2 for z/OS software on the database server. TADDM creates database objects using the implicit table space creation feature, which means that the default buffer pools defined during the DB2 installation and SYSDEFLT are used. DB2 uses the integrated catalog facility catalog (VCAT) name associated with the SYSDEFLT as a high-level qualifier for database sets. To easily identify the data sets associated with the SYSDEFLT storage group, assign a separate VCAT name to the SYSDEFLT storage group.

Two or more logical databases are required for each TADDM schema (primary and archive.) Therefore, you need four or more logical databases for the TADDM installation. A user with the correct privileges must create these databases before TADDM is started. The TADDM server db2zos.xml file contains the default database names: TADDMP1, TADDMP2, TADDMA1, TADDMA2. You can edit this file and change the database names, storage group, and buffer pools for TADDM database indexes. The default values are SYSDEFLT for the index storage group and BP32K1 for the index buffer. Any configuration changes must be completed before TADDM is started.

You can use the ALTER command to change the buffer pools and storage groups for TADDM database indexes and table spaces after the TADDM objects are created. This change is not necessary, if you assigned a separate VCAT name to the SYSDEFLT storage group and configured accurately the default buffer pools for user data.

To prepare a DB2 for z/OS database, complete the following steps:

  1. In your DB2 for z/OS configuration, modify the following parameters.
    (The buffer pool names might be different from the names listed here.)
    Table 1. Buffer pool sizes panel 1: DSNTIP1
    Parameter Value
    DEFAULT 4-KB BUFFER POOL FOR USER DATA (DSN6SYSP TBSBPOOL) Any 4-KB buffer pool names (BP0 used in examples)
    DEFAULT 8-KB BUFFER POOL FOR USER DATA (DSN6SYSP TBSBP8K) Any 8-KB buffer pool names (BP8K0 used in examples)
    DEFAULT 16-KB BUFFER POOL FOR USER DATA (DSN6SYSP TBSBP16K) Any 16-KB buffer pool names (BP16K0 used in examples)
    DEFAULT 32-KB BUFFER POOL FOR USER DATA (DSN6SYSP TBSBP32K) Any 32-KB buffer pool names (BP32K used in examples)
    DEFAULT BUFFER POOL FOR USER LOB DATA (DSN6SYSP TBSBPLOB) Any 4 KB, 8 KB, 16 KB, or 32-KB buffer pool names (BP0 used in examples)
    BP0 bufferpool size 90000
    BP8K0 bufferpool size 12000
    BP16K0 bufferpool size 12000
    BP32K bufferpool size 1500
    Table 2. Sizes panel 2: DSNTIP7
    Parameter Value
    DEFINE DATA SETS (DSN6SYSP IMPDSDEF) NO
    USE DATA COMPRESSION (DSN6SYSP IMPTSCMP) YES (but not required)
    TABLE SPACE ALLOCATION (DSN6SYSP TSQTY) 0
    INDEX SPACE ALLOCATION (DSN6SYSP IXQTY) 0
    VARY DS CONTROL INTERVAL (DSN6SYSP DSVCI) YES
    OPTIMIZE EXTENT SIZING (DSN6SYSP MGEXTSZ) YES
    Table 3. Performance and optimization panel: DSNTIP8
    Parameter Value
    EVALUATE UNCOMMITTED (DSN6SPRM EVALUNC) YES
    SKIP UNCOMM INSERTS (DSN6SPRM SKIPUNCI) YES
    Table 4. IRLM panel 1: DSNTIPI
    Parameter Value
    RESOURCE TIMEOUT (DSN6SPRM IRLMRWT) 150 (seconds)
    U LOCK FOR RR/RS (DSN6SPRM RRULOCK) YES
    Table 5. IRLM panel 2: DSNTIPJ
    Parameter Value
    LOCKS PER USER (DSN6SPRM NUMLKUS) 120000
    DEADLOCK TIME (DEADLOK) 2
    DEADLOCK CYCLE (DEADLOK) 1
    Table 6. Distributed data facility panel 1: DSNTIPR
    Parameter Value
    IDLE THREAD TIMEOUT (DSN6FAC IDTHTOIN) 0 (disabled)
    Table 7. Additional parameter values
    Parameter Value
    RID POOL SIZE 0
  2. On the system where the DB2 for z/OS server is installed, create for TADDM a primary DB2 database user ID (for example, DB_USER) and a secondary DB2 database user ID (for example, ARCHIVE_USER). Each user ID must have a password. The database schemas for TADDM must contain TADDM database objects only.
    See the DB2 for z/OS documentation for more information.
  3. Optional: On the system where the DB2 for z/OS server is installed, create storage groups for TADDMs tables and indexes. For example, TADDMTSG for table spaces and TADDMISG for indexes. This step is not required, if you intend to use the SYSDEFLT storage group. This task must be executed by a user with additional privileges.
  4. On the system where the DB2 for z/OS server is installed, create the DB2 databases for TADDM. This task must be executed by a user with additional privileges. You can change the database names, storage group, and 4K buffer pool name. SYSDEFLT storage group also can be used instead of TADDMTSG. 
    CREATE DATABASE TADDMP1 STOGROUP TADDMTSG BUFFERPOOL BP0 CCSID UNICODE;
CREATE DATABASE TADDMP2 STOGROUP TADDMTSG BUFFERPOOL BP0 CCSID UNICODE;
CREATE DATABASE TADDMA1 STOGROUP TADDMTSG BUFFERPOOL BP0 CCSID UNICODE;
CREATE DATABASE TADDMA2 STOGROUP TADDMTSG BUFFERPOOL BP0 CCSID UNICODE;
    Note: Each database name used in this step must be configured in the db2zos.xml file. If you use database names that are different from the default names in this example, you must modify db2zos.xml after the installation. Database names on DB2 for z/OS are limited to eight characters in length.
  5. Grant the required database access to TADDM DB2 users. In the following example, the DB_USER is the primary DB2 database user ID and ARCHIVE_USER is the secondary DB2 database user ID: 
    GRANT DBADM ON DATABASE TADDMP1 TO DB_USER;
GRANT DBADM ON DATABASE TADDMP2 TO DB_USER;
GRANT CREATETAB, CREATETS ON DATABASE TADDMP1 TO DB_USER;
GRANT CREATETAB, CREATETS ON DATABASE TADDMP2 TO DB_USER;
GRANT USE OF STOGROUP SYSDEFLT TO DB_USER;
GRANT USE OF STOGROUP TADDMTSG TO DB_USER;
GRANT USE OF STOGROUP TADDMISG TO DB_USER;
GRANT BINDADD TO DB_USER;
GRANT CREATE IN COLLECTION DB_USER TO DB_USER;
GRANT USE OF BUFFERPOOL BP0 TO DB_USER;
GRANT USE OF BUFFERPOOL BP8K0 TO DB_USER;
GRANT USE OF BUFFERPOOL BP16K0 TO DB_USER;
GRANT USE OF BUFFERPOOL BP32K TO DB_USER;
GRANT USE OF BUFFERPOOL BP32K1 TO DB_USER;
GRANT SELECT ON SYSIBM.SYSCHECKS TO DB_USER;
GRANT SELECT ON SYSIBM.SYSCHECKS2 TO DB_USER;
GRANT SELECT ON SYSIBM.SYSCOLUMNS TO DB_USER;
GRANT SELECT ON SYSIBM.SYSDUMMY1 TO DB_USER;
GRANT SELECT ON SYSIBM.SYSFOREIGNKEYS TO DB_USER;
GRANT SELECT ON SYSIBM.SYSINDEXES TO DB_USER;
GRANT SELECT ON SYSIBM.SYSINDEXPART TO DB_USER;
GRANT SELECT ON SYSIBM.SYSKEYCOLUSE TO DB_USER;
GRANT SELECT ON SYSIBM.SYSKEYS TO DB_USER;
GRANT SELECT ON SYSIBM.SYSRELS TO DB_USER;
GRANT SELECT ON SYSIBM.SYSROUTINES TO DB_USER;
GRANT SELECT ON SYSIBM.SYSSEQUENCES TO DB_USER;
GRANT SELECT ON SYSIBM.SYSSYNONYMS TO DB_USER;
GRANT SELECT ON SYSIBM.SYSTABCONST TO DB_USER;
GRANT SELECT ON SYSIBM.SYSTABLES TO DB_USER;
GRANT SELECT ON SYSIBM.SYSTABLESPACE TO DB_USER;
GRANT SELECT ON SYSIBM.SYSTRIGGERS TO DB_USER;
GRANT SELECT ON SYSIBM.SYSVIEWS TO DB_USER;

GRANT DBADM ON DATABASE TADDMA1 TO ARCHIVE_USER;
GRANT DBADM ON DATABASE TADDMA2 TO ARCHIVE_USER;
GRANT CREATETAB, CREATETS ON DATABASE TADDMA1 TO ARCHIVE_USER;
GRANT CREATETAB, CREATETS ON DATABASE TADDMA2 TO ARCHIVE_USER;
GRANT USE OF STOGROUP SYSDEFLT TO ARCHIVE_USER;
GRANT USE OF STOGROUP TADDMTSG TO ARCHIVE_USER;
GRANT USE OF STOGROUP TADDMISG TO ARCHIVE_USER;
GRANT BINDADD TO ARCHIVE_USER;
GRANT CREATE IN COLLECTION ARCHIVE_USER TO ARCHIVE_USER;
GRANT USE OF BUFFERPOOL BP0 TO ARCHIVE_USER;
GRANT USE OF BUFFERPOOL BP8K0 TO ARCHIVE_USER;
GRANT USE OF BUFFERPOOL BP16K0 TO ARCHIVE_USER;
GRANT USE OF BUFFERPOOL BP32K TO ARCHIVE_USER;
GRANT USE OF BUFFERPOOL BP32K1 TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSCHECKS TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSCHECKS2 TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSCOLUMNS TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSDUMMY1 TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSFOREIGNKEYS TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSINDEXES TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSINDEXPART TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSKEYCOLUSE TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSKEYS TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSRELS TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSROUTINES TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSSEQUENCES TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSSYNONYMS TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSTABCONST TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSTABLES TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSTABLESPACE TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSTRIGGERS TO ARCHIVE_USER;
GRANT SELECT ON SYSIBM.SYSVIEWS TO ARCHIVE_USER;
GRANT CREATE IN COLLECTION NULLID TO DB_USER;
GRANT CREATE IN COLLECTION NULLID TO ARCHIVE_USER;
GRANT EXECUTE ON PACKAGE NULLID.* to PUBLIC;

    Depending on the system configuration, you might need to grant access to additional system resources required by the DB_USER and ARCHIVE_USER user IDs. Buffer pool names and storage group names might be different from the names listed in this example. If the default SYSDEFLT storage group is used, it is not necessary to grant privileges for TADDMTSG and TADDMISG attributes.

    If DB2 external security is used such as Resource Access Control Facility (RACF®), you must configure these products to match the access provided using the GRANT command. You must consider implicit privileges for the owners of database objects. The following examples are implicit privileges gathered by table owners that are required by TADDM:
    • ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE.
    • CREATE VIEWS. This privilege applies to tables created by TADDM users and database schemas assigned to TADDM users.
    • DELETE, INSERT, UPDATE, DELETE with GRANT option. When you first start TADDM, the primary TADDM DB2 user obtains these privileges from the secondary TADDM DB2 user, during schema creation.
  6. From the system where you plan to install the TADDM server, install and verify the JDBC connection to the remote DB2 for z/OS database.
    Because TADDM uses JDBC metadata functions, installation of the metadata stored procedures that are provided by the DB2 database is required. You can use the DB2 Control Center to connect to the database. Use the following URL to connect to the center:w
    jdbc:db2://hostname:port/database_ssid_name
    Depending on the application, you might need to install additional license .jar files on the client system to support connecting to the DB2 for z/OS database. If you are using DB2 Control Center, you must also configure the required stored procedures and define the corresponding Workload Manager (WLM) on the z/OS system.

    To install the IBM® DB2 Driver for JDBC:

    1. On the DB2 for z/OS system, enable the distributed data facility (DDF) and TCP/IP support.
    2. On the DB2 for z/OS system, customize and run the DSNTIJMS job to enable the stored procedures and define the tables required by the driver.
    3. Create the WLM address space startup procedure for the IBM DB2 Driver for JDBC stored procedures.
    4. Configure RSS.
    For more information, see the DB2 for z/OS documentation
  7. Optional: If you are reinstalling TADDM after previously encountering a problem during installation, remove any existing TADDM database objects. After removing the objects, schemas used by TADDM must not contain any database objects. You can use the catalog tables to generate required drop statements for tables, views, and procedures. Repeat this method for both schemas used by TADDM.
  8. The TADDM database uses the SYSDEFLT storage group by default for all table spaces and index spaces. Attributes PRIQTY and SECQTY are automatically configures by DB2. The storage required is 32 GB, but adjust according to the requirements of your environment.