Rules for SQL Dataviews

Creating Dataviews for SQL Access › Rules for SQL Dataviews

Rules for SQL Dataviews

CA Ideal imposes certain rules on dataviews used for SQL access. The rules are enforced by the CA Ideal CATALOG command. For more information, see the Command Reference Guide.

Identifying Columns

CA Ideal accepts authorization IDs of up to eight bytes for SQL dataviews.
CA Ideal dataviews accept column identifiers of up to 32 bytes. However, the maximum length depends on the SQL mode. For DB2, the maximum length is 18 characters.
For the names of columns, CA Ideal uses the character column name.
The column names in an SQL dataview cannot be delimited identifiers. To reference existing columns named with delimited identifiers, you can create a view of the object containing the columns using valid CA Ideal column names and catalog the view in CA Ideal.

Data Types of Columns

CA Ideal supports the following SQL data types:

SQL Data Type	Data Type in CA Ideal
Character	Alphanumeric
Variable string	Variable length
Long variable string	Variable length
Numeric	Zoned numeric (CA Datacom SQL); packed numeric (DB2)
Decimal	Packed numeric
Integer	Binary full word
Small integer	Binary half word
Date	Ten‑byte alphanumeric
Time	Eight‑byte alphanumeric
Timestamp	26‑byte alphanumeric

Date and time take the length defined by a local database management system exit if it is the default. CA Ideal supports the SQL limits on length and precision, except for long variable string columns, which are limited to 16,000 bytes for CA Datacom SQL access or 32,000 bytes for DB2.

Unique Index Requirement

In the following circumstances, dataviews for DB2 and CA Datacom/DB ANSI mode must access a single table (not the result of a join) that has at least one unique index:

In a FOR statement that has an ORDERED BY clause and is used for updating
In a FOR FIRST clause used for updating, with a logical scope that includes a PDL TRANSMIT, CHECKPOINT or BACKOUT statement or an SQL COMMIT or ROLLBACK

Reserved Words

You cannot use PDL reserved words as the names of dataviews.

NonSQL Field Information

For CA Datacom SQL access dataviews, field information provided in the dictionary, such as headings, initial value, and edit pattern, is incorporated into the dataview object, regardless of the intended access method.

SQL Plans

SQL plans are needed for any CA Ideal program that names an SQL access dataview as a resource. For CA Datacom SQL access, plan options are part of the program environment data and are entered as part of the program definition. For more information about setting plan options in the program definition, see the Creating Programs Guide. For more information about plan options for CA Datacom SQL access and their defaults, see the CA Datacom/DB Programming with SQL Guide.

For CA Ideal programs that access DB2 tables and views, the application plan is defined in CA Ideal. The plan is then generated and bound using the CA Ideal command GENERATE PLAN. For more information about defining and generating DB2 plans for CA Ideal applications, see the Administration Guide.

Recatalog and Recompile

CA Ideal captures all information about a dataview, including column attributes, when the dataview is cataloged. This information is incorporated into the program when the program is compiled, and actually becomes part of the program object module.

If a change is made that affects the columns comprising a table or view (for example, a view is dropped and recreated with an additional column), then you must recatalog the table or view to CA Ideal and recompile every program using the dataview.

Note: If you change a view that does not affect existing column definitions, you can run the programs using the associated dataviews without recompiling, but the SQL text that defines the view is not correct on the dataview display panel.

Specifying Which Database to Access in a Mixed SQL Site

A site that has both CA Datacom SQL support and DB2 can access objects in both database management systems from the same program. The catalog process records which database a particular dataview accesses. At installation, a default database type is specified and any dataview is assumed to access the default database type. You can set a new default using the command:

SET [SITE] ENVIRONMENT SQL dbms

The value of dbms is either DB or DB2. You can override the default for a particular dataview using an option on the CATALOG command:

CATALOG dbms DATAVIEW auth‑id.obj‑name