Examples of database conversions

The following examples outline the conversion process for two different application scenarios:

• Database key extension for cross-transactional use of database key values within one database

• Database key extensions for the use of database key values across databases in a multi-DB configuration

The examples refer to the databases TRAVEL, CUSTOMER and SHIPPING. They are based on the assumption that the TRAVEL, CUSTOMER and SHIPPING databases are initially available in 2-Kbyte format. Following the conversion procedure outlined in the examples, the TRAVEL, CUSTOMER and SHIPPING databases will be identical to the 4-Kbyte databases described in section "Sample databases". The AP1, AP2 and AP3 application programs listed in the examples are COBOL programs.

Cross-transactional use of extended database key values

The use of database key values across transactions within one database represents the typical situation in which database key values are used in application programs:

1. The application program TA1 determines the database key value of the CRR of record type CSTMR by means of the DML statement ACCEPT in a transaction TA1 and stores this value in a COBOL item DBK of type USAGE IS DATABASE-KEY:

   ACCEPT DBK FROM CSTMR CURRENCY

   The record type CSTMR is defined in the schema TRAVEL-AGENCY of the TRAVEL database.

2. In a subsequent transaction TA2, the application program AP1 uses the DML statement FIND (format 1) to immediately reposition to the CSTMR record containing the database key value stored in the COBOL item DBK:

   FIND CSTMR DATABASE-KEY IS DBK

   

       Figure 39: Cross-transactional use of database key values by an application program

A maximum of 16 777 215 ( =2²⁴-1) records can be stored for the record type CSTMR in the 2-Kbyte TRAVEL database. In this case, however, more than 16 777 215 records will need to be stored for the record type CSTMR in the future, and these extended database key values will need to be processed by the application program AP1 accordingly. No other capacity bottlenecks are present in the TRAVEL database.

The required conversion and adaptation measures can be performed in the following steps:

1. Use the BPGSIZE utility routine to convert the TRAVEL database to the 4-Kbyte format. Then declare the converted database as the original.

2. The next step is to prepare the TRAVEL database for database operation. This means, in particular, that the BREORG utility routine must be used to extend the population for the record type CSTMR in the schema TRAVEL-AGENCY to the required value. It is now possible to store more than 16 777 215 records for the record type CSTMR in the TRAVEL database.

3. Since database key values for the record type CSTMR are not assigned by the application program (no LOCATION MODE IS DIRECT clause in the DDL record entry for the record type CSTMR), the Schema DDL (and thus the Subschema DDL) for the TRAVEL-AGENCY schema of the TRAVEL database need not be adapted, i.e. the TRAVEL database need not be restructured.

4. Existing application programs are still executable without changes so long as they do not access CSTMR records whose database key values contain an RSQ > 2²⁴-1 in the TRAVEL database. In order to enable the application program AP1 to access CSTMR records in the TRAVEL database even in cases where their database key values have an RSQ > 2²⁴-1, the declaration of the COBOL item DBK in the application program AP1 must be adapted to the data type USAGE IS DATABASE-KEY-LONG. If required, redefinitions of the DBK item must also be taken into account.
   The application program AP1 must now be recompiled and linked again.The adaptation of the application program is not chronologically dependent on the database conversion.

Database key extension in a multi-DB configuration

This example outlines a multi-DB configuration in which an application program AP2 accesses the CUSTOMER and SHIPPING databases via one UDS/SQL DBH. The application program AP2 contains two modules, MODULE1 and MODULE2, which exchange database key values via a globally defined COBOL item DBK of type USAGE IS DATABASE-KEY. MODULE1 accesses the CUSTOMER database; MODULE2 accesses the SHIPPING database (see figure 40):

1. MODULE1 uses the DML statement ACCEPT on the CUSTOMER database to determine the database key value of the CRR of record type CSTMR and stores this value in a globally defined COBOL item DBK of type USAGE IS DATABASE-KEY:

   ACCEPT DBK FROM CSTMR CURRENCY

   The record type CSTMR is defined in the schema CUSTOMER-CARDS of the CUSTOMER database. It is also contained in a subschema of CUSTOMER-CARDS that is used by MODULE2 (transfer to the Subschema DDL with COPY CSTMR).

2. MODULE2 supplies the CSTMR-NO item of record type CSTMR with the database key value stored in the COBOL item DBK in the record area of the UWA (User Work Area) for the SHIPPING database:

   SET CSTMR-NO TO DBK

   The record type CSTMR is defined in the MAIL-ORDERS schema of the 2-Kbyte database SHIPPING, and the item CSTMR-NO is of type DATABASE-KEY. In addition, the record type CSTMR is also contained in a subschema of MAIL-ORDERS that is used by MODULE2 (transfer to the Subschema DDL with COPY CSTMR).

3. After the remaining items of the CSTMR record have also been supplied with values in the UWA by MODULE2, this record is copied by MODULE2 to the SHIPPING database.

   STORE CSTMR [...]

   Since the entry for the record type CSTMR in the Schema DDL for the 2-Kbyte SHIPPING database contains the clause LOCATION MODE IS DIRECT CSTMR-NO OF CSTMR, the value stored in the CSTMR-NO item also serves as the database key value of the stored record.

   

       Figure 40: Use of database key values by an application program across databases

To begin with, only a maximum of 16 777 215 (=2²⁴-1) records can be stored for both the record type CSTMR in the CUSTOMER-CARDS schema of the 2-Kbyte CUSTOMER database and for the record type CSTMR in the MAIL-ORDERS schema of the 2-Kbyte SHIPPING database.

In this case, however, more than 16 777 215 records are expected in the future for the record type CSTMR in the CUSTOMER-CARDS schema of the CUSTOMER database. No other capacity bottlenecks are present in the CUSTOMER database.

The CUSTOMER and SHIPPING databases can be converted and adapted independently of one another. The following sequence is recommended:

Converting and restructuring the SHIPPING database

1. Use the BPGSIZE utility routine to convert the 2-Kbyte SHIPPING database to the 4-Kbyte_format. This conversion is required, since database key values with an RSQ > 2²⁴-1 are to be copied from the CUSTOMER database to the record type CSTMR of the SHIPPING database in the future.
   Then declare the converted database as the original.

2. You can now prepare the SHIPPING database for database operation. This means, in particular, that the BREORG utility routine must be used to extend the population for the record type CSTMR in the MAIL-ORDERS schema to the required value.
   It is now possible to store more than 2²⁴-1 records for the record type CSTMR of the MAIL-ORDERS schema in the SHIPPING database.

3. The next step is to restructure the SHIPPING database:

1. • Change the LOCATION MODE IS DIRECT CSTMR-NO OF CSTMR clause in the record entry of the record type CSTMR in the MAIL-ORDERS schema to LOCATION MODE IS DIRECT-LONG CSTMR-NO OF CSTMR. The data type of the CSTMR-NO item must be change to type DATABASE-KEY-LONG.

   • The subschema required by MODULE2 need not be modified, since the record type CSTMR from the MAIL-ORDERS schema is transferred to it with COPY. The subschema only needs to be recompiled. In addition, all application programs that use this subschema must also be recompiled and linked again.

   • Further restructuring measures can then be performed as described in chapter "Restructuring the database (BCHANGE, BALTER)".

Converting the CUSTOMER database

1. Use the BPGSIZE utility routine to convert the 2-Kbyte CUSTOMER database to the 4-Kbyte format. Then declare the converted database as the original.

2. You can now prepare the CUSTOMER database for database operation. This means, in particular, that the BREORG utility routine must be used to extend the population for the record type CSTMR in the CUSTOMER-CARDS schema to the required value.
   It is now possible to store more than 2²⁴-1 records for the record type CSTMR of the CUSTOMER-CARDS schema in the CUSTOMER database.

3. No changes are required in the CUSTOMER-CARDS schema of the CUSTOMER database, i.e. the CUSTOMER database need not be restructured.

Adapting the AWP2 application program

In the application program AWP2, the declaration of the COBOL item DBK must be changed to type USAGE IS DATABASE-KEY-LONG, while also taking redefinitions of the DBK items into account. The AWP2 application program must then be recompiled and linked again.

Alternative method: converting and restructuring independently

An alternative method to the one above is to convert the databases with BPGSIZE and to restructure them independently at some other time, i.e.:

1. convert the SHIPPING database with BPGSIZE, but differ restructuring to a later stage

2. convert the CUSTOMER database with BPGSIZE, but differ restructuring to a later stage

3. restructure the SHIPPING database and adapt application programs

4. restructure the CUSTOMER database and adapt application programs

Normal database operation is possible between the individual steps outlined above. As far as possible, steps 3.) and 4.) should be combined and performed at the same time, since at least some of the application programs will need to be adapted and compiled for both 3.) and 4.) application program.