Sample Database

The examples in this manual use the sample database distributed with Optim, which is described in this topic.

Move

The database contains the following DB2 tables with the Creator ID FOPDEMO.

Note: When used in the examples, the tables are shown without the OPTIM_ prefix.

OPTIM_CUSTOMERS
OPTIM_ORDERS
OPTIM_DETAILS
OPTIM_SALES
OPTIM_FEMALE_RATES
OPTIM_SHIP_INSTR
OPTIM_ITEMS
OPTIM_SHIP_TO
OPTIM_MALE_RATES
STATE_LOOKUP

Optim Legacy

If Optim Legacy is installed, the sample data also includes several legacy files. Like the sample database, these files are created as part of the installation process. A sequential file, FOPDEMO.BKORDER, two VSAM data sets, FOPDEMO.VENDITEM and FOPDEMO.VENDOR, and four IMS data sets, FOPDEMO.FOPDEPDB, FOPDEMO.FOPDEPIX, FOPDEMO.FOPJOBDB, and FOPDEMO.FOPJOBIX, are provided.

Note: These files are named with high-order qualifiers determined at installation. Ask your site administrator for the high-order qualifiers.

For each legacy record, Optim Legacy needs a user-defined object, called a Legacy Table that describes the legacy data. Legacy Tables for records in the IMS and VSAM data sets are included in the sample data. These Legacy Tables are:

VSAM Legacy Tables: FOPDEMO.VENDITEM
: FOPDEMO.VENDOR
IMS Legacy Tables: FOPDEMO.DEPARTMENT
: FOPDEMO.EMPLOYEE
: FOPDEMO.JOBCODE
: FOPDEMO.POSITION

You can create a Legacy Table using COBOL or PL1 copybooks. Three COBOL copybooks are provided as part of the sample data and stored in the Installation Library. The distributed copybooks are:

FOP9BKOR
FOP9DEPT
FOP9EMP
FOP9ITEM
FOP9JOBC
FOP9POS
FOP9VEND

The legacy data has a relational structure that must be replicated with user-defined relationships in order to use the legacy data in Move processes. The Optim relationship RVV (between the VENDOR and VENDITEM VSAM Legacy Tables) and the IMS relationships I1_2 (between the DEPARTMENT and EMPLOYEE IMS Legacy Tables) and I2_3 (between the EMPLOYEE and POSITION IMS Legacy Tables) are distributed with the sample data. Other relationships involving the sample legacy data are created in Scenario 3 – Legacy Tables.

Only the tables listed in the following charts are used in this manual. (The OPTIM_ prefix for each table name is not shown in the charts.) The charts show the tables and the relationships between them for each scenario listed in this manual. The arrows indicate the flow from parent to child.

The tables and DB2 relationships used in Scenarios 1 and 2 are depicted in the following diagram. The five tables used in the scenarios are Customers, Sales, Orders, Items, and Details. The four relationships used in the scenarios are RSC, RCO, ROD, and RID:

In the RSC relationship, Sales is the parent of Customers.
In the RCO relationship, Customers is the parent of Orders.
In the ROD relationship, Orders is the parent of Details.
In the RID relationship, Items is the parent of Details.

Tables and relationships in Scenarios 1 and 2

The tables, files, and Optim relationships used in Scenario 3a are depicted in the following diagram. In this scenario, you will use sample data to extract back orders from the sequential file BKORDER, related items from the DB2 table ITEMS, and related records from the VSAM files VENDITEM and VENDOR. The DB2 table ITEMS is the parent of the Legacy Tables BKORDER and VENDITEM through the Optim relationships RIB and RIV, and VENDOR is the parent of VENDITEM through the RVV relationship.

Tables and relationships used in Scenario 3a

The tables, IMS data sets, and relationships (Optim and IMS) used in Scenario 3b are depicted in the following diagram. The tables and IMS data sets used in the scenario are Department, Employee, Position, and Job Code. The three relationships used in the scenarios are I1_2, I2_3, and RJP:

In the I1_2 relationship, Department is the parent of Employee.
In the I2_3 relationship, Employee is the parent of Position.
In the RJP relationship, Job Code is the parent of Position.

In this scenario, you will use sample data, distributed with Optim, to extract employee records from the IMS data sets, FOPDEPDB and FOPJOBDB.

Tables and relationships used in Scenario 3b

If you use this manual as a tutorial, relationships may have been added in other training functions or by other users. Also, the data in DB2 tables or legacy files may have been changed in other sessions, or by other users.