SAMPLIB member CBRISQL0, as shown here, provides the DB2 definitions
for the OAM object tables and directories. You must modify and run
the job successfully before you use OAM.
CBRISQL0 SAMPLIB Member
//CBRISQL0 JOB MSGLEVEL=(1,1),MSGCLASS=A
//****************************************************************
//*
//* $SEG(CBRISQL0) COMP(OSR) PROD(OAM):
//*
//* OAM DB2 Database Definition Job (for Object Tables
//* and Directories).
//*
//* This job will create an OSR database, table, and
//* index in DB2 for an object storage group.
//*
//* Before running this job, you must change the following:
//*
//* 1. Change "cat_name" to the DB2 VCAT name used
//* for defining the VSAM data sets in CBRIALC0.
//*
//* 2. Change "auth_id" to the identifier(s)
//* authorized for the respective group.
//*
//* 3. Change the name in the DSN SYSTEM(DB2) statement to
//* the name of the DB2 Subsystem in your installation.
//*
//* 4. Change the data set name in the RUN statement
//* LIB('DB2.RUNLIB.LOAD') phrase to the data set name used
//* in your installation for the DB2 RUNLIB.LOAD data set.
//*
//* 5. Change the PLAN name (DSNTIA91) in the RUN statement to
//* match your current DB2 version and release level.
//*
//* 6. If you plan to use just one collection, reverse the
//* order of ODCLID and ODPENDDT in index OBJDIRX2.
//*
//* 7. Change "osg_hlq" to the high level qualifier to be used
//* for the object storage group definition and tables.
//* This is the qualifier used on the object storage group
//* define through ISMF and used by OAM and OSR for all access
//* to the object storage group's directories and data tables.
//*
//* 8. Add additional job steps, repeating all statements in
//* STEP01, for each object storage group defined in your
//* configuration. In each repeated step, change the qualifier
//* to match the qualifier for each object storage group.
//* //*
//* CHANGE ACTIVITY:
//* $L0=JDP3227 320 890601 TUCJRL: Initial Release
//* $01=OY26530 320 891113 TUCTNN: Removed OTSEG from OBJT04X1
//* $02=OY33596 320 901019 TUCHTT: Changed index OBJDIRX1 to
//* ODCREATS. Changed index
//* OBJDIRX2 to ODPENDDT, ODCLID,
//* ODCREATS. Changed index
//* OBJDIRX3 to ODNAME, ODCLID.
//* $L1=JDP3331 331 910614 TUCKSG: Reverse order of ODCLID
//* and ODPENDDT for index
//* OBJDIRX2.
//* $L2=HDZ11D0 140 970331 TUCSPP: Specify TYPE 1 INDEX for
//* DB2 4.1 or above level
//* $L3=HDZ11E0 150 970812 TUCLJT: GROUP00-GROUP99 qualifier
//* restriction removed. Single
//* set of JCL provided and user
//* to customize to installation.
//* $L4=HDZ11G0 R13 001012 TUCLJT: Add ODBK2LOC and ODBK2SEC for
//* Multiple Object Backup Support
//* Also:
//* - Removed reference to type 1
//* indexes, which are no longer
//* supported by DB2
//* - Removed SUBPAGES from CREATE
//* statements, since they are
//* only for type 1 indexes
//* $P1=K170872 R13 010913 TUCLJT: Correct misplaced commas
//* $P2=K1K0640 R17 041130 TUCVRE: INDEX OBJDIRX1 and OBJDIRX2
//* do not need to be UNIQUE.
//* $L5=HDZ1180 R18 050531 TUCGPW: Add ODLOBFL for LOB support
//* $L6=HDZ1B10 R1B 080627 TUCBLC: Add ODSTATF and ODRETDT and
//* ODINSTID and change
//* OTOBJ to FOR BIT DATA
//* $P3=K1B0208 R1B 080806 TUCBLC: Default ODRETDT to '0001-01-01'
//*
//****************************************************************
//STEP00 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB2)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA91) -
LIB('DB2.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
CREATE DATABASE osg_hlq;
COMMIT;
CREATE TABLESPACE OSMDTS
IN osg_hlq
USING VCAT cat_name
LOCKSIZE ANY
CLOSE NO
SEGSIZE 64
BUFFERPOOL BP0;
CREATE TABLESPACE OSMOTS04
IN osg_hlq
USING VCAT cat_name
LOCKSIZE ANY
CLOSE NO
SEGSIZE 64
BUFFERPOOL BP2;
CREATE TABLESPACE OSMOTS32
IN osg_hlq
USING VCAT cat_name
LOCKSIZE ANY
CLOSE NO
SEGSIZE 64
BUFFERPOOL BP32K;
COMMIT;
CREATE TABLE osg_hlq.OSM_OBJ_DIR
(
ODVER CHAR(1) NOT NULL,
ODSIZE INTEGER NOT NULL,
ODCREATS TIMESTAMP NOT NULL,
ODEXPDT DATE NOT NULL,
ODLREFDT DATE NOT NULL,
ODPENDDT DATE NOT NULL,
ODMCASDT DATE NOT NULL,
ODSCNUM SMALLINT NOT NULL,
ODMCNUM SMALLINT NOT NULL,
ODLOCFL CHAR(1) NOT NULL,
ODLSLOC CHAR(6) NOT NULL,
ODSECLOC INTEGER NOT NULL,
ODBKLOC CHAR(6) NOT NULL,
ODBKSEC INTEGER NOT NULL,
ODCLID INTEGER NOT NULL,
ODNAME VARCHAR(44) NOT NULL,
ODBK2LOC CHAR(6) NOT NULL WITH DEFAULT,
ODBK2SEC INTEGER NOT NULL WITH DEFAULT,
ODLOBFL CHAR(1) NOT NULL WITH DEFAULT,
ODSTATF SMALLINT NOT NULL WITH DEFAULT,
ODRETDT DATE NOT NULL WITH DEFAULT '0001-01-01',
ODINSTID INTEGER NOT NULL WITH DEFAULT
)
IN osg_hlq.OSMDTS;
CREATE INDEX osg_hlq.OBJDIRX1
ON osg_hlq.OSM_OBJ_DIR
(
ODCREATS ASC
)
CLUSTER
USING VCAT cat_name
CLOSE NO
BUFFERPOOL BP1
PCTFREE 10;
CREATE INDEX osg_hlq.OBJDIRX2
ON osg_hlq.OSM_OBJ_DIR
(
ODCLID ASC,
ODPENDDT ASC,
ODCREATS ASC
)
USING VCAT cat_name
CLOSE NO
BUFFERPOOL BP1
PCTFREE 10;
CREATE UNIQUE INDEX osg_hlq.OBJDIRX3
ON osg_hlq.OSM_OBJ_DIR
(
ODNAME ASC,
ODCLID ASC
)
USING VCAT cat_name
CLOSE NO
BUFFERPOOL BP1
PCTFREE 10;
COMMIT;
CREATE TABLE osg_hlq.OSM_04K_OBJ_TBL
(
OTVER CHAR(1) NOT NULL,
OTSEG SMALLINT NOT NULL,
OTCLID INTEGER NOT NULL,
OTNAME VARCHAR(44) NOT NULL,
OTOBJ LONG VARCHAR FOR BIT DATA NOT NULL
)
IN osg_hlq.OSMOTS04;
CREATE UNIQUE INDEX osg_hlq.OBJT04X1
ON osg_hlq.OSM_04K_OBJ_TBL
(
OTCLID ASC,
OTNAME ASC
)
CLUSTER
USING VCAT cat_name
CLOSE NO
BUFFERPOOL BP1
PCTFREE 10;
CREATE TABLE osg_hlq.OSM_32K_OBJ_TBL
(
OTVER CHAR(1) NOT NULL,
OTSEG SMALLINT NOT NULL,
OTCLID INTEGER NOT NULL,
OTNAME VARCHAR(44) NOT NULL,
OTOBJ LONG VARCHAR FOR BIT DATA NOT NULL
)
IN osg_hlq.OSMOTS32;
CREATE UNIQUE INDEX osg_hlq.OBJT32X1
ON osg_hlq.OSM_32K_OBJ_TBL
(
OTCLID ASC,
OTNAME ASC,
OTSEG ASC
)
CLUSTER
USING VCAT cat_name
CLOSE NO
BUFFERPOOL BP1
PCTFREE 10;
COMMIT;
CREATE VIEW
osg_hlq.V_OSM_OBJ_DIR
AS SELECT ALL * FROM
osg_hlq.OSM_OBJ_DIR;
CREATE VIEW
osg_hlq.V_OSM_04K_OBJ_TBL
AS SELECT ALL * FROM
osg_hlq.OSM_04K_OBJ_TBL;
CREATE VIEW
osg_hlq.V_OSM_32K_OBJ_TBL
AS SELECT ALL * FROM
osg_hlq.OSM_32K_OBJ_TBL;
GRANT ALL ON
osg_hlq.V_OSM_OBJ_DIR
TO auth_id;
GRANT ALL ON
osg_hlq.V_OSM_04K_OBJ_TBL
TO auth_id;
GRANT ALL ON
osg_hlq.V_OSM_32K_OBJ_TBL
TO auth_id;
COMMIT;