SQL catalog

The Model 204 SQL catalog is a facility for storage, retrieval, and modification of the data definition information for SQL objects and SQL users. The SQL catalog contains the SQL schema, table, column, and view definitions and privileges that map to the Model 204 file data accessed with SQL applications.

Surveying the SQL catalog

Bridge to Model 204 data

The SQL catalog is an essential bridge between your SQL application and Model 204 data. The following figure shows the position and function of the SQL catalog in Model 204 SQL processing. The catalog is a Model 204 file that contains an SQL mapping of other Model 204 files.

Before you can run an SQL application against an existing Model 204 file, you must define the file's field descriptions to the Model 204 SQL catalog. The SQL catalog maps Model 204 files to SQL tables and Model 204 fields to SQL columns. The catalog also stores SQL user privileges.

When you issue an SQL query against the Model 204 file data, the Model 204 SQL Server compiler and optimizer read the catalog and translate the query into a physical data request against the Model 204 files. The retrieved records are returned as SQL rows.

The SQL catalog is the CCACAT Model 204 file

Model 204 SQL catalog characteristics

The Model 204 SQL catalog has the following characteristics:

Model 204 SQL catalog is...	Because...
Model 204 file (called CCACAT)	As a Model 204 file, CCACAT is managed by Model 204 system and file managers and governed by Model 204 concurrency control and recovery.
Singular	Only one SQL system catalog is allowed per Model 204 Online. You cannot designate an alternate catalog or use multiple catalogs.
Queriable	SQL applications can access but not manipulate the catalog data. Eighteen nonupdateable views of the catalog data are available to all valid SQL users.
Not an active dictionary-type file	Changes you make to a cataloged Model 204 file are not automatically reflected in the SQL catalog. You must make corresponding changes to the SQL catalog to ensure that it is synchronized with the Model 204 file.
Populated and maintained by the Connect★ Visual Interface (CVI)	Input into the CVI, a Connect★ workstation utility is SQL DDL (data definition language) you create or have generated. Note: All DDL statements input to the CVI must terminate with a semicolon(;).
Independent of the Model 204 Dictionary	You cannot create the SQL catalog with Dictionary facilities; CCATSF helps do this. Nor is SQL catalog information reflected in Dictionary metadata. However, you can use the Dictionary to operate on a Model 204 file that has been cataloged for SQL applications.
Repository of SQL security information.	You provide security definitions to the catalog through SQL GRANT and REVOKE statements. The SQL compiler reads the catalog security information to check the privileges for use of SQL objects.
Repository of SQL view definitions.	A view is an SQL table that is defined in terms of other SQL tables, columns, or views. These view definitions are stored in the SQL catalog and accessed like any other SQL object.

Using the SQL catalog

The Model 204 SQL catalog contains the SQL definition of the Model 204 files that can be accessed by SQL applications. The SQL catalog is loaded, or populated, with this SQL description of Model 204 files through the Connect★ Visual Interface utility (CVI). Other Model 204 SQL utilities are available to help you create the DDL for the catalog and to help you monitor the catalog contents.

Populating the catalog

You populate the SQL catalog by using the CVI utility to do the following:

• Define or update SQL objects in the catalog that map to Model 204 file data.
• Define views of, and the SQL security associated with, these objects.

The CVI utility updates the SQL catalog through DDL statements. The DDL used by CVI can come from the following sources:

• DDL that you generate with mainframe SQL utilities (the Table Specification facility, TSF, or the catalog reporting utility, CCACATREPT)
• DDL that you prepare manually

CVI is available to PC clients as part of the Connect★ Suite.

Generating DDL with SQL utilities

The TSF is an interactive Model 204 subsystem that generates DDL statements you can input to the CVI utility. It is available to mainframe users, and as of RKTools version 7.7, it is accessible from the RKTools main menu and from the RKWeb browser interface.

The TSF displays the current Model 204 file definitions, and it simplifies your specification of corresponding SQL characteristics. From these specifications, the TSF generates DDL statements defining SQL objects that map to the Model 204 files.

The TSF is described in detail in SQL DDL from the Table Specification Facility.

The CCACATREPT subsystem generates DDL statements from the SQL catalog that you can submit to CVI. Also accessible from the RKTools main menu and the RKWeb interface, the CCACATREPT is:

• Discussed in Reporting catalog contents
• Described in SQL catalog reporting and querying.

Preparing DDL manually

You do not have to rely entirely on SQL utilities for the DDL stream you submit to the CVI utility. You can also prepare a DDL stream by:

• Modifying an SQL utility-generated DDL stream. For example, the Model 204 Table Specification facility (TSF) does not produce all DDL statements. You must manually add DDL statements to the TSF-generated DDL stream for certain operations.
• Writing your own stream of DDL statements.

Creating the DDL manually requires that you verify that the Model 204 file definition is consistent with the DDL. You must make sure, for example, that the file definition is current. In addition, you must ensure the proper mapping of Model 204 field attributes to SQL column data types, and you must be aware of proper Model 204 SQL DDL statement syntax and usage rules.

The TSF, however, reads the current data dictionary (Table A) information from the Model 204 file and displays it to you so you can build the SQL definition. Consequently, using the TSF is less likely to introduce inconsistencies between the SQL catalog definition and the actual Model 204 file data.

Reporting catalog contents

You can examine the contents of the Model 204 SQL catalog by:

• Querying the catalog directly
• Using CCACATREPT, the Model 204 SQL catalog reporting utility

The contents of the SQL catalog are represented in user accessible views that are defined in the schema CATALOG in the SQL catalog. Eighteen non-updateable views summarize the SQL catalog objects, naming the schemas, tables, views, columns, constraints, and privileges. Any authorized SQL user can access the views with an SQL query. For a description of each of the views and their contents, see Querying the SQL catalog.

With CCACATREPT, you can produce an online or printed report that provides information (including attributes and corresponding Model 204 file and field names) for any SQL tables or views defined in the SQL catalog. CCACATREPT also has reports for privilege information from the catalog. In addition, CCACATREPT can display DDL generated from the SQL catalog data and in the form of valid DDL syntax.

You can compare the CCACATREPT output to the Model 204 file definition (DISPLAY command output) to determine the updates you need to make to the SQL catalog to obtain consistency with the file. You can modify the report's generated DDL and use it to repopulate your SQL catalog.

For further description of CCACATREPT and how to use it, see SQL catalog reporting and querying.

Monitoring catalog consistency

SQL data definitions in the catalog must correspond to the Model 204 file data they describe. Because the SQL catalog is not constructed to change automatically when Model 204 files are changed, it might not be in sync with the Model 204 file at the time an application is run. SQL DDL updates to the SQL catalog might be required to maintain correspondence between the file and the catalog definition.

The Model 204 TSF subsystem uses the Model 204 file definitions that are current at the time you invoke it. If no changes are made to the file contents before you submit the TSF DDL to the SQL catalog, you can be sure that the catalog and the Model 204 file are consistent. The DDL that you create manually, however, is not checked for consistency with the Model 204 file. The person creating DDL manually must be aware of the current Model 204 file definition.

When you run the CVI utility, it provides error messages for any syntax errors, but it does not report data definitions that are inconsistent with the Model 204 file.

You are responsible for ensuring that if the Model 204 file definition changes, the corresponding SQL definitions are updated. To monitor SQL catalog and Model 204 file consistency, you can use direct SQL catalog queries or the Model 204 SQL catalog reporting utility, CCACATREPT. Both resources provide a display of the contents of the SQL catalog. Querying the catalog and using CCACATREPT are both described in SQL catalog reporting and querying.

Maintaining the SQL catalog

Creation and maintenance of the SQL catalog (the CCACAT file) implies responsibilities for both SQL-specific data management and Model 204-specific data management.

As the SQL catalog, CCACAT must be populated and updated through SQL.

As a Model 204 file, CCACAT is subject to Model 204 file and system management. CCACAT must be installed and created as part of Model 204 installation and must be maintained as part of the Model 204 system (including recovery and security).

As both SQL catalog and Model 204 file, CCACAT is protected both by SQL security and Model 204 security. SQL access to CCACAT is protected by Model 204 login security and by SQL GRANT and REVOKE security. SOUL and Host Language Interface access to CCACAT is protected by normal Model 204 file security and by allowing only system managers to open CCACAT.

Because CCACAT is a system file with the prefix CCA, you need system manager privileges to create, open, or initialize it.

You should not access CCACAT for any purpose other than normal SQL installation, operation, and reporting, and normal Model 204 operations such as sizing, reorganizing, and recovery.

Note: SOUL statements or Model 204 file management commands must not be issued against CCACAT when SQL processing is taking place. CCACAT is a special Model 204 file not unlike CCASYS, for example. The integrity of your SQL processing depends on the integrity of CCACAT.

The rest of this section describes what the Model 204 system manager and file manager need to do to use and maintain the Model 204 file CCACAT.

Creating the CCACAT file

You create the CCACAT file as part of the mainframe installation of Model 204. For information about installing CCACAT, see the Rocket Model 204 installation documentation for your operating system.

The procedure used in the CCACAT installation contains all the file parameter settings and DEFINE statements necessary to create a working version of CCACAT. The installation procedure does the following:

• Creates the Model 204 file CCACAT
• Defines fields in CCACAT
• Includes SOUL procedures that load into CCACAT the SQL catalog definitions (schema, tables, views, columns) that are accessible to user queries

Rocket Software recommends that you use the CCACAT file parameters as installed. The reasons for some of the default parameter settings are discussed later in this topic.

Including CCACAT in an Online

This section summarizes CCACAT file considerations that affect the setting up of your Model 204 Online. For more complete information about Online job requirements for Model 204 SQL processing, see Runtime environment for SQL processing.

Every Model 204 Online with Model 204 SQL applications must have exactly one CCACAT file allocated to it. Two Onlines can share a CCACAT file, but only in read-only mode. If CCACAT is shared, no user can update it with the SQL catalog populating utility, CVI.

When an SQL thread is initialized, CCACAT is opened automatically for the user. When the thread is terminated, CCACAT is closed. An explicit OPEN command for CCACAT can be issued only by a user with system manager privileges.

Job control requirements

Online job control must contain a DD statement or FILEDEF for the CCACAT file. CCACAT cannot be allocated or freed dynamically.

File access and security

The CVI utility is the usual way to update CCACAT. CVI has its own privilege mechanism, controlled by runtime parameters such as login ID.

The system manager can provide additional file security by resetting the OPENCTL parameter and by entering passwords for the CCACAT file entries in the password table (CCASTAT). The default value of the OPENCTL parameter is X`80'. CCASTAT maintenance is described in Storing security information (CCASTAT).

The default setting for PRIVDEF is X'BFFF', which includes all privileges. The recommended minimum settings for the PRIVDEF file parameter are X`8441' for the system administrator and X`0441' for the SQL user.

Consistency and recovery

CCACAT, like other Model 204 files, can participate in recovery and transaction backout processing. If CCACAT is used in a run, it can be recovered by a RESTART command.

The FRCVOPT (file recovery options) parameter for CCACAT can ensure that:

• File cannot be updated if ROLL FORWARD logging is not active.
• File cannot be updated if checkpoint logging is not active.

The parameter settings are distributed with the CREATE procedure. See Creating a file for more information.

See FRCVOPT for more about its settings.

Ongoing CCACAT maintenance

This section describes CCACAT file maintenance issues that are most likely to be post installation, ongoing considerations.

Backup and restore

Back up the CCACAT file frequently, certainly before making any major changes to the catalog. Use the same backup, restore, and reorganization procedures that you use for any Model 204 file.

File organization and sizing

CCACAT is distributed as an entry order file (FILEORG = X`00'). With this setting the CCACAT file might eventually become full even though there is unused space due to deleted catalog entries. In this case, expand CCACAT as you do any Model 204 file. Do not change the FILEORG to Reuse Record Number, however, because the order of entries in the catalog is important for the regeneration of DDL from an existing catalog.

The installation procedure also provides default settings for file parameters such as BSIZE, CSIZE, DSIZE, BRECPPG and BRESERVE. The file manager tunes these settings to conform with application requirements. The optimum settings for file sizing parameters vary depending on factors such as the number of tables and columns within tables, length of table names, complexity of view definitions, the number of privilege records, and the number of users (affects number of GRANTs).

In general, it is important to use the normal mechanisms provided for maintaining CCACAT. Use CCACATREPT to view catalog contents, CVI to update, and standard commands such as SQL DROP to delete catalog definitions.

Large DDL updates

If a CVI user submits a CREATE SCHEMA statement that is several pages long, the system manager might have to increase the amount of space allocated to CCACAT. It might also be necessary to increase the Model 204 SQLIQBSZ, SQLBUFSZ and LHEAP parameters so that there is a large enough buffer available to process the update.

You might be able to avoid increasing these parameters by breaking up the DDL into smaller statements (see SQL Data Definition Language (DDL) for further details). The need to increase the amount of space allocated to CCACAT still remains.

CCACAT implementation for BLOB and CLOB data

Any new SQL Catalog that you create by following the SQL Installation instructions in the Rocket Model 204 installation documentation will be completely compatible with the Binary Large Object (BLOB) and (CLOB) data types.

If you have an older, pre-v7.4 SQL Catalog, you can perform the procedure below to use the catalog without recreating it. This saves the time required to rerun all DDL to recreate the current tables.

To use your SQL catalog from a Model 204 release before Version 7 Release 4:

1. Back up the SQL Catalog.
2. OPEN CATPROC.
3. INCLUDE ODBCTABLES.INSTALL

   This procedure updates the CCACAT file and installs the necessary SQL data types, BLOB/CLOB, for use in your DDL processing.

4. To test this installation:
   1. CREATE a table that includes BLOB or CLOB data types.
   2. Run the following SQL SELECT statement:
   SELECT type_name, data_type FROM CATALOG.ODBC_TYPES

   This returns the following results showing the new BLOB/CLOB data types.

   TYPE_NAME DATA_TYPE CHAR 1 DEC 3 DOUBLE PRECISION 8 FLOAT 6 INT 4 NUMERIC 2 REAL 7 SMALLINT 5 BLOB 30 CLOB 40

See also

• SQL Server overview
• SQL Server installation
• SQL catalog
• SQL representation of Model 204 data
• SQL Data Definition Language (DDL)
• SQL DDL from the Table Specification Facility
• SQL catalog reporting and querying
• SQL Data Manipulation Language (DML)
• SQL DDL syntax
• SQL reserved words
• SQL DDL mapping of the demonstration database