Chapter 3: Import Database Definitions

This chapter contains the following sections:

Importer Concepts
Importing IMS PSBs and DBDs
Importing SQL DB2 Objects
Importing VSAM Files
Importing IDMS Database Definitions

Importer Concepts

Transfer database definitions to APS

APS Import Facilities allow you to transfer information about your database definitions and their copybook records to APS programs. You can import:

IMS DBDs and PSBs
IDMS subschemas
SQL DB2 objects
VSAM files

You can also use APS Import Facilities to transfer BMS and MFS screens. For more information about importing screens, see Paint Character Screens.

Translate database information

The APS Import Facilities translate database information such as data definitions and/or subschemas into a format usable for generating and precompiling through APS. When the APS Import Facility transfers database information, the Database Definition Interface (DDI) formats the database information to use with APS programs.

Combine multiple databases

If required you can combine multiple database environments into a single subschema by giving each subschema the same name when you import it using the APS database importers. Then simply reference that name in any application that requires it.

The database importers:

Extract information from your database definition.
Load extracted information into the DDIFILE.
Generate a DDISYMB file for use by Online Express and the appropriate APS Generator.
Generate record description copybooks of SQL DDL statements that contain database and COBOL descriptions of each table or view in the imported subschema.

Importing IMS PSBs and DBDs

Code DDI statements before you import

Before you import IMS database definitions, you must code DDI statements to identify which IMS segments and COBOL copylib record descriptions to import. You can import the following IMS database information:.

Input	Library	Description
PSB	project.group.PSBSRC	Native PSB source; no modification is necessary.
DBD	project.group.DBDSRC	Native DBD source; no modification is necessary.
copylibs	project.group.COPYLIB	For each IMS segment in your DBD, you must have a copylib containing a COBOL record description.

Input

Library

Description

PSB

project.group.PSBSRC

Native PSB source; no modification is necessary.

DBD

project.group.DBDSRC

Native DBD source; no modification is necessary.

copylibs

project.group.COPYLIB

For each IMS segment in your DBD, you must have a copylib containing a COBOL record description.

Code DDI statements and import IMS database information as follows:

Copy the PSB and DBD into project.group.PSBSRC and DBDSRC files respectively, and specify the PSB on the Application Painter. For more information on the Application Painter, see Paint the Application Definition.
Copy into project.group.COPYLIB one or more COBOL copylib files containing COBOL record descriptions for each IMS segment in your DBD.
Code a DDI DBD statement to correspond to the DBD statement in your database. For syntax information, see the "DDI Statements" topic in the APS Reference.
Code a DDI REC statement for each segment you want to import to correspond to the SEGM statement in your database and its copylib as follows:
1. Specify the name of the copylib record that corresponds to the DBD segment identified on the DDI DBD statement using the NAME parameter. The name of the copylib member that contains the segment copylib record should be the same as the segment name. If it is not, specify the copylib member name with the COPY parameter.
2. Set the &GEN-DB-REC-01 NAMES flag in the APS CNTL file, APSDBDC, to indicates the level number of your top level copylib records.
  - If your top level copylib records begin with 01, set flag to 0.
  - If your top-level copylib records do not begin with 01, set flag to 1 and assign a unique 01-level name using the NAME parameter. Alternately, use the GEN01 parameter to override the value of this flag. Specify GEN01=n to indicate that the top level copylib record level number begins with an 01 level number or specify GEN01=y to indicate that the top level numbers do not begin with 01.
    For example, if your copylib record looks like this:
```
05  WS-EMPLOYEE-INFO. 
    10  WS-EMPLOYEE-NO               PIC 9 (06).
```
    Code the DDI REC statement as:
```
DDI REC NAME=WS-EMPLOYEE-STUFF, SEG=EMPLSEG, COPY=EMPLDATA
```
    The generated output would look like this:
```
01  WS-EMPLOYEE-STUFF. 
    05  WS-EMPLOYEE-INFO. 
      10  WS-EMPLOYEE-NO              PIC 9 (06).
```
    For syntax information, see the "DDI Statements" topic in the APS Reference.
Code a DDI FLD statement to correspond to the copylib record field and each field statement in the DBD. If your database contains secondary indexes, you can search on a secondary index field without having to generate DDISYMB for the index database. For syntax information, see the "DDI Statements" topic in the APS Reference.
- If the XDFLD has multiple SRCH fields, do one of the following to include SRCH fields:
  - Code a DDI FLD statement. When there are multiple SRCH fields, APS defaults the name value to the XDFLD value. Code a dummy COBOL name that is unique from any copylib field name of the SRCH fields in the NAME parameter to override the default.
  - Specify the DBD value of XDFLD using the IMSNAME parameter.
    For example, for the following DBD source:
```
XDFLD NAME=SOUCOX2, SRCH= (EMPLASNA,SOURCODE)
```
    Code the DDI FLD statement as follows:
```
*DDI FLD NAME=INDEX-NO2,IMSNAME=SOUCOX2
```
    Hint: Use the dummy name you specified on the DDI FLD statement in database commands to qualify on a secondary index composed of multiple SRCH fields.
- If the XDFLD has one SRCH field, code DDI DBD and DDI REC statements only. It is not necessary to code a DDI FLD statement for the XDFLD because the APS Generator refers to the SRCH field definition. Note: If you write a database command against a PCB that uses a secondary index, use the proper COBOL name for the index field to be qualified upon. The APS Generator recognizes a secondary index by the presence of the PROCSEQ or INDICES parameters, and generates segment search arguments (SSA) naming the IMS XDFLD.
Code DDI for logical relationships
If necessary, you can code DDI statements for IMS logical DBDs and PSBs that reference IMS logical relationships as follows:
1. Code DDI DBD statement.
2. Code a DDI REC statement. Do not include information for segments in a logical DBD if the logical segment has a single physical source segment or the same IMS name as its physical source segment. Note: The APS/IMS Generator does not validate IMS logical Insert/Delete/Replace rules.
Place DDI statements in project.group.DDISRC.
Access the IMS Database Importer. To do so, from the APS Main Menu, enter 2, Dictionary Services in the Command field. On the Dictionary Services screen, enter 1, Import Facilities in the Command field. Enter 2, IMS on the Import Facilities screen.
On the IMS Importer screen, type the DBD member name in the Member field and enter 1, Load DBD Definitions and *DDI statements, and generate DBD in the Option field. APS reads, extracts and stores the DBD and DDI statement information in the DDIFILE.
After option 1 completes, enter 2, Load PSB definitions and Generate PSB and DDISYMB in the Option field. This option references the information in the DDIFILE and reads, extracts, and translates PSB information into DDI symbols and stores it in project.group.DDISYMB.

Supplementing or Overriding DDI Statements

When you import a DBD, you can write DDI statements that assign a set of COBOL record and field names to each segment and field in the PSB. Depending on what you want your program to do, you can supplement or override these names with additional DDI statements. For example, if you must maintain multiple positioning on a segment type, use more than one PCB to reference the same segment thereby maintaining multiple positioning. You need at least two areas in Working-Storage in which to retrieve the same segment. This way the retrieval of one segment will not overlay the Working-Storage area of the same segment retrieved at a different position in the database. Another example is multiple programs that reference the same segment type, but some programs must use different record descriptions of the segment. In this case, you can override the names defined in the original DDI statements on a program by program basis.

To supplement or override record and field names write additional DDI statements in a separate DDI statement member. Modify JCL in the APS PSB utility member and reimport the database to include the new DDI statements. To do so, follow the steps below.

Create a member in project.group.DDISRC dataset giving it the same name as the program PSB.

Write DDI statements to assign the new set of names to the segment. Use the format below, starting each statement in column 7.

-KYWD-      12-*----20----*----30---*----40----*----50---*--
 *DDI    PSB NAME=psbname
 *DDI    PCB
 *DDI    PCB
 *DDI    PCB
 .
 .
 .
 *DDI    REC SEG=segmentname,NAME=new-COBOL-recordname,
 COPY=new-copylibname
 *DDI    FLD IMSNAME=name,NAME=new-COBOL-fieldname
 *DDI    FLD IMSNAME=name,NAME=new-COBOL-fieldname
 *DDI    FLD IMSNAME=name,NAME=new-COBOL-fieldname

The DDI statements are described below:

DDI Statement	Description
*DDI PSB	Specifies the program PSB.
*DDI PCB	Positional or placeholder statements that indicate the PCB for which you are assigning an additional set of names. For example, to assign names to the fourth PCB in the PCB, write four DDI PCB statements; do not write a DDI PSB statement for any subsequent PSB.
*DDI REC	Specifies the following: Segment name as it appears in the program PSB. New COBOL record name of the segment. New copylib name for the new COBOL record.
*DDI FLD	Specifies the following: IMS field name as it appears in the program PSB. New COBOL name of a field in the new COBOL record. Write one statement per field.

Copy the APS PSB utility member, &APSPRE..ISPSLIB(SSMXPSB), to the dataset that is concatenated before &APSPRE..ISPSLIB.
Modify the copy of SSMXPSB as follows:
- Add the parameter parm='ddi' to the //DDIIMS statement so that it reads as follows:
```
//DDIIMS     EXEC PGM=DDIIMS,REGION=1024k,PARM='DDI',
          COND=((0,LT,PSBGEN),(O,LT,LINK))
```
- Change the //DDICARDS DD DUMMY statement to the following:
```
//DDICARD    DD  DISP=SHR,DSN=&DDIPRE..DDISRC(&SSMDDI)
```
Select option 2.1.2 to display the APS IMS Database Importer. Enter the program DBD name in the Member field and execute option 1, Load DBD Definitions and DDI Statements, and Generate DBD.
After option 1 completes, execute option 2, Load PSB Definitions and Generate PSB and DDISYMB.
Return to the READY prompt and restart APS.

Importing SQL DB2 Objects

Import DB2 object to the common data area

The SQL Importer lets you import DB2 objects stored in the DB2 system catalog to a separate staging area in APS. This staging area, known as the APS common data area, is where you create and generate subschemas for imported DB2 objects using the SQL Subschema Maintenance utilities. For more information about these utilities, see the Administrator's Guide chapter Managing APS Facilities and Libraries.

You can import the following DB2 object types using the SQL Importer:

Alias (object only)
Database
Index (object only)
Storage group
Table and table space
View (object only)

Import DB2 objects as follows:

From the APS Main Menu enter 2, in the Option field. APS displays the Dictionary Services screen. Enter 1 in the Option field. APS displays the Import Facilities screen. Enter 1 in the Option field. APS displays the SQL Importer screen. Enter 1 in the Option field to display the DB2/DBP Object Import screen.
In the Command field, type the number that corresponds to the object type you want to import.
Type the name of the object you want to import in the Object Name field. If you do not know the name of the object, leave this field blank and press Enter. The SQL Importer displays the DB2 Object List screen. This screen displays data set information on the object types in the DB2 system catalog.
Select objects from the list by typing an s to the left of each desired object name. You can scroll the screen using the ISPF UPnn and DOWNnn commands and by setting the SCROLL field to page, half or csr. Selecting an object creates utility control cards that direct the batch job to import the object to the common data area. To cancel your selections and return to the DB2/DBP Object Import screen, type cancel or can in the Command field and press Enter.
Type end in the Command field and press Enter or press the appropriate PF key to view a screen that lists the objects you selected from the object list. The SQL Importer displays the list of selected objects. To delete a selection from this list, type d in the selection field.
Return to the DB2/DBP Object Import screen by doing one of the following:
- Press Enter
- Type end in the Command field and press Enter
- Press the appropriate PF key

Complete the remaining fields on the DB2/DBP Object Import screen as described below and press Enter.

Field

Value

Object Creator

If you typed a name in the Object name field, type the object creator's TSO ID. Defaults to your TSO ID.

Job Class

Type the job submission class. Valid values are:

J1-J5	APS defines job cards J1-J5 on the Job control cards screen. To access this screen, type J in this field.
JC	ISPF job card defined on the ISPF Log andLists Defaults (0.2). You must increment the job card letter.

Object Only

Y(es)	Default for Index, View and, Alias. Imports the object specified in the Command field or selected from the Object List screen without associated objects.
N(o)	Default for Storage Group, Data Base, Table Space, Table, and Column. Imports the object specified in the Command field or selected from the Object List screen plus all objects associated with the specified objects.

Report Only

Y(es)	Generates an import report for the object specified but does not import the object.
N(o)	Default. Generates an import report in addition to importing the objects specified. For more information about reports, see Generating a DB2/DBP Object Import Report.

Submit Job Now

Y(es)	Default. Submits a batch job to import the object specified in the Command field or selected from the Object List screen.
N(o)	Generates the import job JCL and stores it in the data set specified in the following fields. This JCL can be used for later job submission.

Generate DDISYMB symbols
Access the APS Generator Options screen. From the APS Main Menu, enter 0 in the Option field. APS displays the Options Menu. Enter 1 in the Option field. APS displays the Generator Options screen. Set the SQL field to a valid SQL target; for example, DB2, SQLDS, or SQL400. .
Access the SQL Importer screen and type 2 in the Option field.
The DDIFILE project and group for the DDISYMBs defaults to your current user project. Ensure that your current project is identical to the project and group under which the subschema was created.
Type the subschema name in the Member field and press Enter.

Note: For more information on setting generator options, see Setting Options.

Generating a DB2/DBP Object Import Report

Determine the impact of importing DB2 objects

You can determine the impact of importing a DB2 object to the common data area by generating a DB2/DBP Object Import report. You can generate this report before, during or after importing DB2 objects. Reports generated after the import reflect all additions, deletions, or name changes made to the dependent common data area objects at the time of report generation.

The DB2/DBP Object report provides a cross reference of the objects in the DB2 catalog and the APS common data area. It illustrates where a DB2 object fits into the hierarchy of the DB2 system catalog versus where it fits into the hierarchy of the common data area. The cross reference report lists objects in groups to illustrate hierarchical dependency.

The DB2/DBP Object Import report can consist of:

Two side-by-side comparison lists with the following information:
- The imported DB2 object and the DB2 system catalog objects dependent upon it (those lower in the hierarchy).
- The imported object plus common data area objects that are dependent upon it after it is imported.
Two side-by-side comparison lists of the DB2 and common area objects that contain the imported object (those higher in the hierarchy).
A list of the associated common data area objects added since the last import of the object type.

Special Considerations

If your DB2 system supports referential integrity (DB2 version 2 and higher), the SQL Importer also imports tables referenced by foreign keys.
When you generate an Object Import report, enter information in all required fields of the DB2/DBP Object Import screen.
The dependent common data area objects are listed under the report column titled DBP Object.

Importing VSAM Files

Code DDI statements before importing

Before you import VSAM files, you must code DDI statements. The DDI statements identify the VSAM file and the COBOL copylib record descriptions you want to import. For each VSAM file record, you must have a copylib file that contains a COBOL record description. These copylib files must reside in project.group.COPYLIB.

Code DDI statements and import your VSAM file information as follows:

Copy the copylib file(s) that contain the COBOL record descriptions for the VSAM file(s) into project.group.COPYLIB COBOL.
Code a DDI VSM statement to correspond to the VSAM file external ddname. This statement identifies VSAM file attributes. You can specify parameters to generate IDCAMS. For syntax information, see the "DDI Statements" topic in the APS Reference.
Code a DDI REC statement for each copylib record to correspond to the copylib record name and copylib filename with the longest MAXLEN. For syntax information, see the "DDI Statements" topic in the APS Reference.
If your top-level copylib records do not begin with 01, set global flags &VS-GEN-01-USING-RECNAMES and GEN-DB-REC-01-NAMES and GEN-DB-REC-01-NAMESin APS CNTL files APVSAMIN and APSDBDC to yes.
If the VSAM file you want to import is keyed, code a DDI IDX statement for each index that corresponds to keyed copylib field name and copylib file name. All DDI IDX statements must immediately follow the DDI REC statement. Write overlapping record keys as ordinary DDI IDX statements. APS generates IDCAMS KEYS keyword according to the OFFSET and KEYLEN keywords on the DDI IDX statement. For syntax information, see the "DDI Statements" topic in the APS .
Code a DDI SUB statement to correspond to the copylib record name(s) and VSAM file external ddname. This statement defines a subschema for your VSAM file. Note: To define a subschema with multiple VSAM files, assign a RECORD keyword to each VSAM file you include. For syntax information, see the "DDI Statements" topic in the APS .
Assign a unique subschema name and enter it in the Application Painter field, SBSC/PSB.
Place the DDI statements in project.group.DDISRC.
Access the VSAM Importer. From the APS Main Menu screen, enter 2, Dictionary Services in the Option field then enter 1, Import Facility then enter 3, VSAM.
From the APS/VSAM File Importer screen, enter the DDISRC name (the member name) in the member field, and enter 1 Load DDI Information From DDISRC in the option field. APS reads, extracts and stores the DDI statement information in the file DDIFILE.
After option 1 completes, execute option 3 - Generate DDISYMB Symbols From DDIFILE. APS reads, extracts, and translates DDIFILE information into DDI symbols, and stores them in project.group.DDISYMB.
Generate IDCAMS, enter 2, Generate IDCAMS Input Into Amserv in the option field after step 5 completes. The IDCAMS option generates IDCAMS for all files that the subschema references, but more than one subschema can contain a given file. Tailor the IDCAMS so no existing files are deleted. Store the IDCAMS source in your AMSERV data set.

Importing IDMS Database Definitions

Translate database definitions

The IDMS Importer translates IDMS database definitions from your IDMS dictionary into a format usable for generating and precompiling through APS.

Import your IDMS data definitions as follows:

Ensure that the IDMS subschema resides in your IDMS dictionary (IDD).
Access the IDMS Importer. From the APS Main Menu screen, enter 2 in the Option field. APS displays the Dictionary Services screen. On this screen , enter 1 in the Option field. APS displays the Import Facility screen. On the Import Facility screen, enter 5 in the Option field. APS displays the IDMS Importer screen.
Enter the IDMS subschema name in the Member field. You can keep or change the displayed IDMS schema name and version number.
If you are importing IDMS12.0 subschemas, ensure that the dataset name of the subchema appears in the IDMS 12.0 SYSIDSM DSN field on the IDMS Options panel.
Enter the database name that contains the subschema in the IDMS Dictionary field.
Enter 1, Import IDMS Subschema from IDD and Generate DDISYMB, in the Option field.