|
|
|
|
|
All Global Index values associated with a particular Global Index path are stored in a DB2 table. Fields in the table correspond to the fields of Control‑V indexes. An additional field REP_KEY contains the key of the corresponding Control-D report entry. The REP_KEY field should be defined with the FOR BIT DATA clause.
Several Control‑V Index paths can be stored into the same DB2 table if the table contains fields from all the paths. Such a table can be used in the event that there is a Control‑V path that covers all the fields in the table. For example, if there are three Control-V Index paths
you can organize two DB2 tables. One table would contain the ACCOUNT and the ACCOUNT/DATE paths, while the other would contain only the ACCOUNT/NAME path.
If there is a large number of entries to be loaded into the DB2 table, several tables can be created to keep entries for the same Control-V Index path. You can create a new DB2 table, depending on your needs, for each year, quarter, month, multiple years and so on. To identify each table you can use a suffix to the table name to identify the period covered by the table. For example, if you created a new table for each year the names would be CTD.GIRD07 to keep data of year 2007, CTD.GIRD08 to keep data of year 2008, and so on. These tables are joined during data retrieval or you can specify a specific table to process. For more information, see Accessing Reports Through the Global Index Database.
Additional DB2 tables containing only high level Indexes of the Index Path can be created to improve the performance of the Index values list request. For example, if the main DB2 table contains the values of the Index path DEPARTMENT/TEAM/ACCOUNT, and there is not an especially large number of DEPARTMENT values and TEAM values, getting a list of Departments or Teams from a large DB2 table can take a disproportionately long time. In this case, it makes sense to create two additional DB2 tables: CTD.DEP with the DEPARTMENT values only and CTD.DEP_TEAM with two columns - DEPARTMENT and TEAM. The result is, the retrieval of the DEPARTMENT list is done from the CTD.DEP table and retrieval of the TEAM list for each DEPARTMENT from the CTD.DEP_TEAM table instead of performing these requests from the big main table. The additional tables can be handled manually or can be loaded automatically while loading the main table. This is done using the CTVUPGDB utility or from within a decollation mission. Use of such additional tables is optional, it can be specified in the CTDGIDB2 member (For more information, see Relationship Between CTV Index Paths and DB2 Tables.
The database administrator at the site is responsible for the design and organization of the DB2 tables. The database administrator should make needed definitions according to the requirements of the application to be implemented and according to the amount of information which is to be stored in the Global Index Database.
A DB2 index should be created for each DB2 table in order to prevent adding duplicate index values into the table, and in order to provide better performance when retrieving index values. The CTDGBCRT member in the CTD JCL library is a sample job for creating the DB2 table. An example of this job is shown in the following figure.
Figure 62 CTDGBCRT Member Sample Job
//DSNTEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20
//DBRMLIB DD DSN=DSN610.DBRMLIB.DATA,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
CREATE TABLE CTD.GIRAD
(ACCOUNT CHAR(10) NOT NULL,
DATE_ CHAR(8) NOT NULL,
REP_KEY CHAR(24) NOT NULL FOR BIT DATA)
IN DBCTD.TSCTD;
CREATE TYPE 2 UNIQUE INDEX CTD.IXGIRAD ON CTD.GIRAD
(ACCOUNT ASC,
DATE_ ASC,
REP_KEY ASC)
CLUSTER
CLOSE YES
USING STOGROUP SGCTD
PRIQTY 1000000
SECQTY 100000
ERASE NO
FREEPAGE 10
PCTFREE 20;
GRANT SELECT ON TABLE CTD.GIRAD TO PUBLIC
//SYSTSIN DD *
DSN SYSTEM(DSN1)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP61) -
LIB('DSN610.RUNLIB.LOAD')
END
//*
Parent Topic |