SQL Concepts and Language Elements › Supported SQL Language Elements › Host Variables › Qualified Table and View Names

Qualified Table and View Names

You can qualify the names of SQL objects with authorization IDs of up to eight characters. For example, you can qualify the table PAYROLL with the authorization ID HOU:

HOU.PAYROLL

The dataviews corresponding to SQL objects must have authorization IDs. You can use the dataview authorization ID to qualify the object name in the SQL presented to the database or you can code an authorization ID explicitly in the embedded SQL. In each program resource table that includes a dataview for an SQL object, you must perform the following tasks:

• Specify a dataview authorization ID. This authorization ID identifies the cataloged dataview corresponding to the object.
• Set the Q (qualifier) column for the dataview to either Y or N. This tells CA Ideal to use the dataview authorization ID in the SQL when the object name is not qualified in the embedded SQL or allow the database to supply its default authorization ID.

You have several options for qualifying an object name based on how its dataview is defined:

• You can always use the authorization ID specified in the program resource fill‑in. Specify Y in the Q (Qualifier) column. Qualifying the name in embedded SQL is allowed, but not necessary.
• You can have CA Datacom/DB supply its default authorization ID. Specify N in the Q (Qualifier) column and do not qualify the name in embedded SQL.
• You can override the default for selected SQL statements by specifying N in the Q (Qualifier) column and, in those statements, qualifying the object name with the authorization ID from the program resource fill‑in.

  This lets you access multiple objects with the same name but different authorization IDs using one CA Ideal dataview. Their structures must be compatible. For more information about defining a program, see the Creating Programs Guide.

Example

Consider the following program resource table definition:

Dataview              Auth‑id   Q?

EMPLOYEE              SBL       Y
PAYROLL               HOU       N

Example

Consider also the following embedded SQL statements:

EXEC SQL                            EXEC SQL
    SELECT                           SELECT ...
     FROM EMPLOYEE, PAYROLL            FROM SBL.EMPLOYEE, HOU.PAYROLL
END‑EXEC                            END‑EXEC

               EXEC SQL
                  INSERT INTO PAYROLL
                    (SELECT * FROM HOU.PAYROLL ...)
               END‑EXEC

Example

CA Ideal generates the following clauses:

SELECT ...                          SELECT ...
  FROM SBL.EMPLOYEE, PAYROLL          FROM SBL.EMPLOYEE, HOU.PAYROLL

           INSERT ... INTO PAYROLL
                        (SELECT * FROM HOU.PAYROLL ...)

In the first example, the database qualifies the table name PAYROLL with an authorization ID.

In the second example, both table names are qualified with the dataview authorization ID from the program resource fill‑in.

In the third example, two tables, HOU.PAYROLL and xxx.PAYROLL (where xxx is the CA Datacom/DB default authorization ID and is not HOU) are accessed using the one PAYROLL dataview specified in the program resource fill‑in.

The ASSIGN AUTHORIZATION command lets you specify an authorization ID to use for all tables or views specified as unqualified in the resource table.

For DB2: The ASSIGN AUTHORIZATION command also lets you replace the dataview authorization ID specified in the resource table with a new authorization ID or generate an unqualified table or view name. The interactions of the resource table, embedded SQL, and the ASSIGN AUTHORIZATION command are explained in the Command Reference Guide and the Administration Guide.

For CA Datacom SQL access: The ASSIGN AUTHORIZATION command lets you select an alternate access plan at runtime.