In this chapter, we describe the IBM Content Manager OnDemand (Content Manager OnDemand) database structure and relationships between the tables. We list the system control tables and the important data table structures. We explain the relationship between the tables when loading data, show how a search is performed on the database tables, describe the system log, and describe special considerations for DB2 on z/OS.

In this chapter, we cover the following topics:

Content Manager OnDemand uses system tables and a set of application group data tables. All system control tables are created by the arsdb command (except for the ASM tables that are used by Content Manager OnDemand for i). The data tables are created when you load data into the Content Manager
OnDemand system.

Table 4-1 shows the Content Manager OnDemand system control tables with their descriptions.

The Content Manager OnDemand data tables can grow rapidly. You should understand the structure of the data tables and the relationship between them.

There are two important tables that you must examine here: the segment table (ARSSEG) and the application group data table (ag_internal_id). The segment table contains one row for each segment of each application group table. Table 4-2 shows the first four columns of the ARSSEG table structure.

The ARSEG table points to the application group data table name (second column of the table, table_name). The application group data table is created or updated during the arsload process. It contains a row for each item that is stored in the application group.

The name of the application group data table is ag_internal_id, which is the identifier that Content Manager OnDemand assigns to the application group when it is created with the Administrator Client. The three-digit application group identifier is listed in the Storage Management window of the Administrator Client, as shown in Figure 4-1. In this case, the application group identifier is WBA, AGID 5185.

Table 4-3 shows the first five columns of the application group data
table structure.

The application group data table is indexed on one or more of the user-defined fields, from field_1 to field_n.

Three more tables might grow rapidly during the lifetime of a Content Manager OnDemand system:

Table 4-4 shows the first four columns for the ARSLOAD table structure.

In this section, we present an example that shows the relationships between the Content Manager OnDemand tables when loading data to a Content Manager OnDemand system. This example is based on a check application that has four index fields that are defined as customer_name, account_nbr, check_nbr, and balance. There is a one-to-one relationship between the application group and
the application.

After the application group and the application are defined, the application group gets an application group identifier, agid, in the ARSAG table and an internal application group identifier, agid_name. Figure 4-2 shows the data that is created in the ARSAG table; the agid is 5018, and the agid_name is HAA.

This application group creates an application group data table every 10 million rows. During the data loading, Content Manager OnDemand uses the agid and the agid_name to add a row into the segment table (ARSSEG) for every 10 million rows that are created in the application group data table. The important pointer in the ARSSEG table is the name of the application group data table, table_name, where the index values (in this case, the four defined index values) are stored. The table_name is composed of the agid_name from the ARSAG table plus a counter.

Figure 4-2 on page 94 shows the two rows that are created in the ARSSEG table: one row with table_name HAA1, another row with HAA2. Both HAA1 and HAA2 are the actual names of the application group data tables that are created.

The application group data table contains the doc_name, which is the actual container for the individual document. The offset and the document length are also kept in this table. Figure 4-2 on page 94 shows that the first row has an offset of 0, and the second row has an offset of the document length of the
first row.

Figure 4-2 on page 94 shows the relationship between the tables.

The architecture of relating one application group to one or more application group data tables allows Content Manager OnDemand an unlimited growth of index space. The maximum table size is a limitation of the database subsystem and should be configured for optimal performance. Because this architecture enables a system to create tables when the maximum table size is reached, there is no logical limitation to the system; rather, the limitation is on the physical resources, such as processing power, disk space, object servers, and
storage hardware.

To better understand the relationship between the various Content Manager OnDemand tables, we describe a search sequence within a Content Manager OnDemand system in this section. A search sequence scans through multiple Content Manager OnDemand tables. We describe the logical flow that the system goes through during a Content Manager OnDemand search.

Using the Content Manager OnDemand standard Windows client, you can open a search criteria window (see Figure 4-3). In our example, there are four index fields: Name, Account, Statement Date, and Balance. The example shows a search for a specific date and a balance amount.

After you enter these values, Content Manager OnDemand searches its internal tables to find the segment in the segment table ARSEG. Based on the data that is stored in Content Manager OnDemand internal format in the table, Content Manager OnDemand gets the table_name to search for the index values (1994-03-07 and 104.18) in the application group data table (HAA1) and finds the matching Statement_date and the Balance and returns these values to the client in a search result list.

To retrieve the individual document within this result list, the system goes back into the application group data table and locates the document offset and dataset (object) and retrieves the object for display at the client.

Figure 4-4 on page 97 shows the details of this search sequence from a folder.

A system log is an application group. It is created by using the ARSSYSCR program. The application group identification name is SL and a 4-byte agid is added. You find SLXX in the ARSEG table depending on how large your system log is growing. The creation of a new system log table is based on the number of rows on the Storage Management setting. The default is 10 million rows. This configuration can be modified.

The database creation and the allocation of space for tables and the tablespace of the Content Manager OnDemand product is different in the z/OS environment. The database administrator (DBA) is responsible for the allocation, creation, maintenance, backup, and recovery of the database subsystem.

For the Content Manager OnDemand z/OS DB2 database environment, standard database backup and recovery procedures can be used for the Content Manager OnDemand -created databases, tablespaces, and tables. To minimize the effort of creating and monitoring the Content Manager OnDemand data tables, several automated table creation and space allocation procedures are implemented into the product.

All system tables, as mentioned in 4.1, “System control tables” on page 88, are created by the arsdb program. Each table is created in its own tablespace. During installation, the tablespace is created by member ARSTSPAC in dataset V9R0M0.SARSINST. The size of each tablespace is specified there. Before you run ARSTSPAC to create the Content Manager OnDemand tablespaces, you must create the storage group and the database. The CREATE for the storage group and database is in member ARSDB2 in dataset V9R0M0.SARSINST. The owner of the database (the submitter of the job or the user ID that is set by the “Set current SQLID =’username’) must match the entry SRVR_INSTANCE_OWNER in the ARS.INI file.

The arsdb program provides an interface between the database manager and Content Manager OnDemand. Several parameters are used in the creation and dropping of tables. For more information about arsdb, see IBM Content Manager OnDemand for z/OS and OS/390 - Configuration Guide, GC27-1373.

The arsdb program is in the UNIX System Services file system /usr/lpp/ars/V9R0M0/bin. When you create the Content Manager OnDemand tables or indexes, the arsdb command can build the tables and indexes in the default tablespace or in tablespaces that you create by using the
ARSTSPAC member.

When you run the arsdb command, Content Manager OnDemand validates the existence of the tablespace. If the tablespace does not exist, the arsdb command creates the Content Manager OnDemand system tables in the default tablespace. After creating the Content Manager OnDemand tablespaces, if changes must be made, the best way to change the tablespace is by running the ALTER TABLESPACE command.

The data tables in Content Manager OnDemand are created under the control of DB2 on z/OS. Like the system tables, this is done dynamically during the arsload process. It is important to understand how Content Manager OnDemand on z/OS is allocating space for these tables because they can grow large.

During the creation of an application group, a parameter limits the maximum rows for one table. If this limit is reached, Content Manager OnDemand creates another data table during the arsload process. The maximum row value for an application group table is customized in the Advanced section of the General tab in the AG configuration windows. The field is called Maximum Rows.

The allocation of space is done automatically. No further action needs to be performed by the DBA except to set up the backup of the newly created tables and plan for the new resources that are needed for the next couple of months.

Four major factors influence the amount of storage that is needed for the Content Manager OnDemand database:

For more information about space requirements, see IBM Content Manager OnDemand for z/OS and OS/390 - Introduction and Planning Guide, GC27-1438.

Content Manager OnDemand for z/OS allocates its tablespace during the creation of a new table based on the following space allocation parameters:

The allocation of the database is done in kilobytes. The allocation values depend on the maximum row limit that is set when you create the application group. The DBSIZE depends on the number of index fields that are defined in the application.

In general, here is how DBSIZE is calculated:

Maximum number of rows * Default Table Factor / records per page

The Default Table Factor is set to 1,2 by the program. The records per page value is a DB2 parameter. For more information about records per page, see Chapter 8, “Estimating Disk Storage”, in DB2 Version 10 for z/OS Administration Guide, SC19-2968.