Creating a Reality SQL Catalog

Naming of Tables, Columns and Indexes

Table, column and index names, defined in SQLTABLES, SQLCOLUMNS and SQLINDEXES, must conform to the following rules:

• A name cannot be a reserved word (See SQL Reserved Words for a list of these).
• Names can only include letters, numbers, and the underscore character (no other characters, such as punctuation marks or special characters, are allowed).
• The first character of each name must be a letter.
• Names can comprise up to 49 characters.
• Table names should not be prefixed with the string 'ODBC_'. This is reserved for system use.

The SQLM utility ensures that names are consistent with these rules. When converting existing Reality file and attribute names as discussed in Using Existing Reality Data Dictionaries, it prefixes names starting with a number with the letter X and changes special characters to underscores. It also ensures that the table names are unique within an SQL catalog and that column and index names are unique within a table.

Reviewing Reality Dictionaries

The easiest way to create SQL tables is to base them on existing data dictionaries. It is recommended that you first review, and possibly update, these dictionaries, before carrying out the conversion. The following steps are suggested:

1. Look at the data and index definition items to see if any are obsolete and can be removed.
2. Look at any alternative definitions for the same attribute and see whether they can be rationalised.
3. Look at how the definitions match the columns/indexes required within the SQL table and modify or add definitions to match.

Using Existing Reality Data Dictionaries

If a file has existing data and index definition items in its dictionary, SQLM uses these as a starting point by proposing them as SQL column and index definitions. If you select these initially, you can then use the update function to modify them as you want. You can also add further column and index definitions not based on dictionary items. If a file has no data definition items in its dictionary, SQLM enables you to create any number of new column definitions based on a default column definition.

Checking a Column Definition

An SQL column definition specifies the characteristics of the data in an SQL column. It is much more precise than a data definition in Reality. In particular, it contains fields which specify:

• The type of data allowed in the column (Column Type). This can be an alphanumeric string (CHAR, VARCHAR or LONGVARCHAR), a number (BIGINT, BIT, DECIMAL, DOUBLE, FLOAT, INTEGER, NUMERIC, REAL, SMALLINT or TINYINT), or a date/time (DATE, TIME or TIMESTAMP).

  Note

  SQLM does not currently support the binary data types: BINARY, VARBINARY and LONGVARBINARY.

• The maximum length of data allowed in the column (Precision).
• The number of decimal places allowed in a DECIMAL or NUMERIC column (Scale).

It also specifies:

• The attribute number in Reality used as the source for the column.
• Codes specifying the action to be taken when the attribute contains multivalues and subvalues.
• Whether the column is the primary key, or part of the primary key. The primary key is the column, or set of columns, that contains values which uniquely identify the rows in the table. The column(s) defined as the primary key should match the item-id (attribute 0) for the Reality file.

When converting an existing data definition to a column definition, SQLM inserts a data type and size based on the contents of the Reality data. Alternatively, if there are no data definitions, SQLM creates column definitions based on a default definition. In each case, it is necessary to check that the fields in the newly created column definition describe as closely as possible the actual SQL data defined by the column definition.

Refer to SQL Column and Index Definitions for a detailed description of column definition fields.

Indexes

The subject of indexes is discussed fully in Application Index Design. The section Recommendations for Creating Indexes provides guidance for designing indexes to facilitate the optimisation process.