IKYCDB2 is a sample to create DB2® objects for the object store and issued certificate list (ICL). IKYCDB2 is a member of SYS1.SAMPLIB.

--**********************************************************************
--* SAMPLE: IKYCDB2
--*
--* Licensed Materials - Property of IBM
--* 5650-ZOS
--* Copyright IBM Corp.  2011, 2013
--* Status = HKY7790
--*
--**********************************************************************
--
-- This sample may be used to create the DB2 database using SPUFI that
-- PKI Services utilizes to store certificate requests and issued
-- certificates.
--
--**********************************************************************
--
-- Before using this sample, you may need to make the following
-- modifications:
--
-- 1) Change all the occurences of 'SYSDEFLT' to the storage group you
--    want to contain the PKI Services DB2 tablespaces if SYSDEFLT
--    if SYSDEFLT is not suitable for your installation.
--
-- 2) Change all the occurences of 'MASTERCA' to the package name.
--    The package name should match the first eight characters of
--    the CA domain name.
--
-- 3) Change all the occurences of 'IKYPKIDB' to the database name of
--    your choosing. If you are running multiple PKI CA domains, each
--    domain must have a unique database name.
--
-- You will also need certain DB2 privileges to use this sample.
-- These privileges are indicated in the comments preceding each
-- set of SQL instructions.
--
--**********************************************************************
-- Change Activity:
--
--   $L0=PKIS13       HKY7780   100706  PDWFC1: DB2 support
--   $L1=PKIS21D      HKY7790   120612  PDRRG1: DB2 Enhancements
--
-- Change Description:
--
--   A: Initial code                                               @L0A
--   C: Remove GRANT DBADM for PKISRVD.  Move GRANT EXECUTE for
--      PKISRVD to IKYSGRNT sample.                                @L1A
--
--
--**********************************************************************
--
-- If you have already run this sample before, uncomment these
-- statements to drop existing indexes, tables, and tablespaces.
-- In order to DROP these objects, the user must have at least one
-- of the following privileges:
--     - Ownership of the indices, tables and tablespaces
--     - DBADM authority on the IKYPKIDB database
--     - SYSADM or SYSCTRL authority
--
--**********************************************************************

-- DROP INDEX MASTERCA.TIDAIX;
-- DROP INDEX MASTERCA.OREQAIX;
-- DROP INDEX MASTERCA.OSTATAIX;
-- DROP TABLE MASTERCA.OST;

-- DROP INDEX MASTERCA.IREQAIX;
-- DROP INDEX MASTERCA.ISTATAIX;
-- DROP TABLE MASTERCA.ICL;

-- DROP TABLESPACE IKYPKIDB.OSTSPACE;
-- DROP TABLESPACE IKYPKIDB.ICLSPACE;

--**********************************************************************
--
-- If you have already run this sample before, uncomment these
-- statements to drop existing database.  In order to DROP the
-- database, the user must have at least one of the following
-- privileges:
--     - DROP privilege on the IKYPKIDB database
--     - DBADM or DBCTRL authority on the IKYPKIDB database
--     - SYSADM or SYSCTRL authority
--
--**********************************************************************

-- DROP DATABASE IKYPKIDB;

-- COMMIT;

--**********************************************************************
--
-- The following statement creates the PKI Services DB2 database.
-- To create the database, the user must have at least one of
-- the following privileges:
--     - CREATEDBA privilege
--     - CREATEDBC privilege
--     - SYSADM or SYSCTRL authority
--
--**********************************************************************
CREATE DATABASE IKYPKIDB STOGROUP SYSDEFLT;

--**********************************************************************
--
-- The following statements create the tablespaces used for the
-- PKI Services ObjectStore and ICL tables.  To create these
-- tablespaces, the user must have at least one of the following
-- privileges:
--     - CREATETSA privilege on the IKYPKIDB database
--     - DBADM, DBCTRL, or DBMAINT authority for the
--       IKYPKIDB database
--     - SYSADM or SYSCTRL authority
--
--**********************************************************************
CREATE TABLESPACE OSTSPACE IN IKYPKIDB
     LOCKSIZE       ROW
     SEGSIZE        4
     PCTFREE        0
     BUFFERPOOL     BP32K
     USING STOGROUP SYSDEFLT
     PRIQTY         144400
;
CREATE TABLESPACE ICLSPACE IN IKYPKIDB
     LOCKSIZE       ROW
     SEGSIZE        4
     PCTFREE        0
     BUFFERPOOL     BP32K
     USING STOGROUP SYSDEFLT
     PRIQTY         144400
;
--**********************************************************************
--
-- The following statement creates the ObjectStore table.
-- To create the table, the user must have at least one of the
-- following privileges:
--     - CREATETAB privilege on the IKYPKIDB database
--     - DBADM, DBCTRL, or DBMAINT authority for the
--       IKYPKIDB database
--     - SYSADM or SYSCTRL authority
--
-- The table name MUST be <package name>.OST
--
--**********************************************************************
CREATE TABLE MASTERCA.OST(
      RECORD_KEY                    BINARY(4)         NOT NULL,
      RECORD_STATE                  BINARY(4)         NOT NULL,
      REQDATA_LEN                   INTEGER           NOT NULL,
      REQUESTOR_NAME                VARCHAR(32)               ,
      TRANS_ID                      CHAR(24)          NOT NULL,
      COMMENT                       VARCHAR(64)               ,
      ISSUED_TIME                   TIMESTAMP         NOT NULL,
      LAST_CHANGE_TIME              TIMESTAMP         NOT NULL,
      TEMPLATE_NICKNAME             VARCHAR(8)                ,
      SERIAL_NUM                    BINARY(4)                 ,
      REQDATA                       VARBINARY(32512)  NOT NULL,
      PRIMARY KEY (RECORD_KEY)
      )
 IN IKYPKIDB.OSTSPACE
 ;
--**********************************************************************
--
-- The following statement creates the indices for the
-- ObjectStore table.  To create these indices, the user must
-- have at least one of the following privileges:
--     - INDEX privilege on the ObjectStore table
--     - Ownership of the ObjectStore table
--     - DBADM authority for the IKYPKIDB database
--     - SYSADM or SYSCTRL authority
--
--**********************************************************************
--
-- The following statement creates the ICL table.  To create the
-- table, the user must have at least one of the following
-- privileges:
--     - CREATETAB privilege on the IKYPKIDB database
--     - DBADM, DBCTRL, or DBMAINT authority for the
--       IKYPKIDB database
--     - SYSADM or SYSCTRL authority
--
-- The table name MUST be <package name>.ICL
--
--**********************************************************************
CREATE TABLE MASTERCA.ICL(
      SERIAL_NUM                    BINARY(4)         NOT NULL,
      CERT_STATE                    BINARY(4)         NOT NULL,
      CERT_LEN                      INTEGER           NOT NULL,
      REQUESTOR_NAME                VARCHAR(32)       NOT NULL,
      REVOKE_DATE                   TIMESTAMP                 ,
      INVALID_DATE                  TIMESTAMP                 ,
      REVOKE_REASON                 INTEGER                   ,
      COMMENT                       VARCHAR(64)               ,
      ISSUED_TIME                   TIMESTAMP         NOT NULL,
      LAST_CHANGE_TIME              TIMESTAMP         NOT NULL,
      TEMPLATE_NICKNAME             VARCHAR(8)                ,
      OBFUS_PW                      VARCHAR(32)               ,
      PROCESS_FLAGS                 BINARY(4)                 ,
      KEYID                         BINARY(20)                ,
      CRLDP_NUM                     INTEGER                   ,
      EXPIRE_EPOCH_DAYS             INTEGER           NOT NULL,
      EXPIRE_DATE                   TIMESTAMP         NOT NULL,
      KU_DIGTSIG                    BINARY(1)                 ,
      KU_NONRPU                     BINARY(1)                 ,
      KU_KEYENC                     BINARY(1)                 ,
      KU_DATAENC                    BINARY(1)                 ,
      KU_KEYAGR                     BINARY(1)                 ,
      KU_CRTSGN                     BINARY(1)                 ,
      KU_CRLSGN                     BINARY(1)                 ,
      KU_ENCONLY                    BINARY(1)                 ,
      KU_DECONLY                    BINARY(1)                 ,
      EKU_SEVAUTH                   BINARY(1)                 ,
      EKU_CLIAUTH                   BINARY(1)                 ,
      EKU_CODESGN                   BINARY(1)                 ,
      EKU_EMLPROT                   BINARY(1)                 ,
      EKU_TMESTMP                   BINARY(1)                 ,
      EKU_OCSPSGN                   BINARY(1)                 ,
      EKU_MSSCLNON                  BINARY(1)                 ,
      PREV_SERIAL_NUM               BINARY(4)                 ,
      SUBJ_DN                       VARCHAR(1024)     NOT NULL,
      X509CERT                      VARBINARY(10240)  NOT NULL,
      PRIMARY KEY (SERIAL_NUM)
      )
 IN IKYPKIDB.ICLSPACE
 ;

--**********************************************************************
--
-- The following statement creates the indices for the ICL
-- table.  To create these indices, the user must have at
-- least one of the following privileges:
--     - INDEX privilege on the ICL table
--     - Ownership of the ICL table
--     - DBADM authority for the IKYPKIDB database
--     - SYSADM or SYSCTRL authority
--
--**********************************************************************
CREATE UNIQUE INDEX MASTERCA.SERIX    ON MASTERCA.ICL (SERIAL_NUM);
CREATE        INDEX MASTERCA.IREQAIX  ON MASTERCA.ICL (REQUESTOR_NAME);
CREATE        INDEX MASTERCA.ISTATAIX ON MASTERCA.ICL (CERT_STATE,
                                                       CERT_LEN,
                                                       REQUESTOR_NAME);

COMMIT;