Catalog Administration

This topic describes the use of the SQLM command at TCL to create and maintain an SQL catalog in a Reality account.

Introduction

SQLM is a Reality command that enables you to set up and maintain an SQL catalog in a Reality account. To use SQLM, you must be logged on to the account for which you wish to set up the SQL catalog and your security profile must allow you to modify that SQL catalog.

SQLM allows you to:

• Create new SQL table, column and index definitions to form the catalog.
• Create a new table definition with some or all of the columns of an existing table, so as to provide an alternative SQL view of a Reality file.
• View privilege grants, primary keys, foreign keys, and column and index definitions for a specified table.
• Update privilege grants, primary keys, and foreign keys, column and index definitions for a specified table.
• Verify that the columns and indexes defined in a table's SQLTABLES entry are also defined in the SQLCOLUMNS and SQLINDEXES files.

Syntax

The syntax of the SQLM command is as follows:

SQLM {identifierList {(options}}

where

If you enter SQLM at TCL without parameters, file and table names are prompted for, as appropriate.

Using a Select List

SQLM with a filename can be used to process an active select list of data and index definition items which must be generated, or retrieved, prior to executing SQLM. Suitable commands that generate (or retrieve) a select list, such as SELECT and GET-LIST, are described in English Commands - Generating Implicit Lists.

Examples

:SQLM

Executes the SQLM utility. If the SQL environment files (listed previously) are missing, they are created, after appropriate confirmation is received. Once the files have been created, or if they already exist, the SQLM main menu is displayed.

:SQLM ID1

Activate the SQLM command and 'auto feed' the item ID1 to the option subsequently selected from the main menu.

• If ID1 is a file name, this would only make sense with menu options 1 and 2.
• If ID1 is a table name, this would only make sense with options 3 to 8.

:SQLM MYFILE (A

Automatically create an SQL table with the name MYFILE that refers to the Reality file MYFILE. All Reality dictionary/index items will be converted to SQL column/index items.

:SQLM CUSTOMERS ORDERS PRODUCTS (A

Automatically create an SQL tables with the names CUSTOMERS, ORDERS and PRODUCTS that refer to the Reality files CUSTOMERS, ORDERS and PRODUCTS. All Reality dictionary/index items will be converted to SQL column/index items.

:SELECT DICT USERS 'STATUS' 'TYPE' 'ACCOUNT'

3 Items Selected
>SQLM USERS (A

Selects specified data definitions from file USERS, and then calls SQLM to automatically convert the selected definitions to column or index definitions for table USERS.

:SELECT DICT MYFILE = "A]"

17 Items Selected
>SQLM MYFILE*MYTABLE (A

Selects data definitions that start with "A" from file MYFILE, and then calls SQLM to automatically convert the selected definitions to column or index definitions for table MYTABLE.

Case Sensitivity

SQLM normally converts only case-sensitive files; if you attempt to convert or regenerate a case-insensitive file, that file is ignored and an error message to that effect is displayed.

If you need to create an SQL table based on a case-insensitive Reality file, you can use the W option to force SQLM to create the table. Note, however, that the Reality SQL server does not support case-insensitive comparisons and SQL access to Reality files is therefore always case-sensitive; the use of SQL with case-insensitive files is therefore not recommended.

Creating the SQL Environment

On entering SQLM for the first time on an account, you will see the message:

This Account is not configured or up to date for SQL.
Do you want to set it up or update it (Y/N):

Entering Y at the prompt sets up the SQL catalog in the local account, or updates it from an earlier release of Reality. The catalog is set up by creating files and Q-pointers which define the SQL environment for that account. Refer to the topic SQL Catalog Files

Running SQLM does not alter the existing Reality files or dictionaries, but creates separate table, column and index definition items which provide a self-contained SQL view of the Reality data in the account.

Where there is an existing file dictionary with data definition items, SQLM attempts to make 'best fit' column definitions from user-selected definition items. The user then has the option to modify the definition to suit requirements. SQLM also allows a user to create their own column definitions for files that do not have dictionaries.

SQL Catalog Files

Four Reality files:

SQLTABLES
SQLCOLUMNS
SQLINDEXES
SQLPROCS

define the SQL environment in an account. They form the SQL Catalog.

The four SQL catalog files are created by SQLM when it is first run in an account.

SQLM also creates three Q-pointers which reference SQL system files which should already exist in SYSFILES:

SQLINFO
SQLTYPES
SQLPRIV

Main Menu

Once the SQL environment has been set up, SQLM displays the following menu:

                       SQL MAINTENANCE UTILITY PROGRAM
                       ===============================

1. Create new Table definitions
2. Create alternative view
3. View Table definitions
4. Amend Table definitions
5. Regenerate Table
6. Rename Table
7. Remove Table from SQL environment
8. Verify Table

Enter selection (1 - 8) or <CR> :