SQL Concepts and Language Elements › SQL Dataviews › Mixed SQL Sites

Mixed SQL Sites

A mixed SQL site can access multiple databases using SQL. A single program can access multiple databases, but each dataview name (that is, each SQL object name) must be unique. Across a CA Ideal system, each fully‑qualified object name must be unique.

With a mixed site, the database the SQL statements access depends on the type of statement:

• Each data manipulation (DML) statement, such as INSERT, requires a cataloged dataview for its object. The dataview specifies which database contains the object and processes the statement.
• The COMMIT, ROLLBACK, and WHENEVER statements affect all databases the application accesses.
• Statements such as GRANT and EXECUTE IMMEDIATE that do not access objects defined as CA Ideal dataviews can access only one database from a program. This database is called the primary database. The primary database processes supported SQL statements listed in the following two charts as no dataview required.

You can specify the primary database as a site or session default, and override it for individual programs. For more information, see the SET ENVIRONMENT SQL command in the Command Reference Guide and the program environment fill‑in in the Creating Programs Guide.

Supported SQL Statements

CA Ideal PDL supports the SQL following statements. The CA Datacom SQL statements are listed first, followed by the DB2 statements.

CA Datacom/DB Database SQL Option Statements Supported in PDL:

DB2 SQL Statements Supported in PDL:

WHENEVER Statement

The SQL WHENEVER statement embedded in a PDL program specifies the action to take when a specified condition occurs during the execution of a subsequent embedded SQL statement. The action specified for a given condition applies to all SQL statements that follow in listing sequence until another WHENEVER statement for that condition overrides it.

This statement has the following format:

                          [CONTINUE           ]
            {NOT FOUND }  [DO ERROR           ]
WHENEVER    {SQLERROR  }  [DO procedure       ]
            {SQLWARNING}  [PROCESS‑NEXT‑label ]
                          [QUIT‑label         ]

NOT FOUND| SQLERROR| SQLWARNING

The type of SQL exception condition.

CONTINUE

Default: Specifies that execution continues with the next sequential statement in the program.

DO ERROR

Invokes the CA Ideal error procedure (user‑specified or default) from the embedded SQL statements. $ERROR function values are available in the error procedure.

DO procedure

Executes the procedure identified by the specified procedure label and returns control to the statement following the END‑EXEC.

PROCESS‑NEXT‑label

A PROCESS NEXT statement specifying that the current iteration of the current LOOP or FOR EACH construct terminates. See the PROCESS NEXT statement in the next chapter.

QUIT‑label

A QUIT statement specifying execution of a PDL QUIT with the specified option. See the QUIT statement in the next chapter.

If no WHENEVER statement is included in the program for a given condition, the default is CONTINUE.

The PDL $ERROR functions, error procedure, and the command LIST ERROR do not apply to errors encountered in processing embedded SQL statements.

PDL FOR constructs, even though they generate SQL requests, are bound by PDL error handling rules, not WHENEVER specifications.

For a mixed SQL site, the WHENEVER applies to SQL statements accessing any database. You can determine the database management system accessed by the last SQL statement executed by using the $SQL‑DBMS function.

Example

In this example, if the following conditions are encountered in any embedded SQL after the WHENEVER in listing sequence, the indicated action occurs.

EXEC SQL WHENEVER SQLERROR DO ERRPROC END‑EXEC
EXEC SQL WHENEVER NOT FOUND PROCESS NEXT MAIN‑LOOP END‑EXEC
EXEC SQL WHENEVER SQLWARNING CONTINUE END‑EXEC