Defining a Database Using SQL › Overview

Overview

This chapter contains procedures for defining the logical components of an SQL-defined database (the last step in the list).

Steps to Define a Database

To use SQL to define your database, follow these steps:

1. Design and size the database using information provided in the CA IDMS Database Design Guide document.
2. Define in the system dictionary the segments that represent the physical database. Include the segments in your DMCL, and generate, punch, and link edit the DMCL.

   Note: For more information about the physical database, see Chapter 4, “Defining Segments, Files, and Areas".

3. Create and format the operating system files that will contain the table's rows. These files must be accessible to the runtime environment before you define your tables.
4. Copy the segment definition from the system dictionary into the application dictionary in which you wish to define your tables.

   The segment and area names you use in the logical definition must match those defined in the physical definition in the system dictionary. The stamp level, which tells CA IDMS/DB to check the date and time of definition at either the area level or table level, must also match in both definitions. It is recommended that the page range and page size of areas match in both definitions since this information is used for optimization and index sizing. It is not necessary to define the files in the application dictionary.

5. Enter SQL data description (DDL) statements to do the following, in this order:
   • Create the schema
   • Create tables
   • Create CALC keys
   • Create indexes
   • Create referential constraints
   • Drop unneeded default indexes
   • Create views

Note: For complete SQL DDL syntax, see the CA IDMS SQL Reference Guide. For design decisions, see the CA IDMS Database Design Guide document.