Using qualified and unqualified SQL

Programmers writing CICS DB2 programs can use qualified or unqualified SQL. In qualified SQL, the creator is specified in front of the table or view name. In unqualified SQL, the creator is not specified.

When programmers develop CICS DB2 standards, it is important to determine the use of qualified and unqualified SQL. This decision influences many other aspects of the DB2 environment. The main relationships to other DB2 areas and some consequences for the two types of SQL statements are shown in Table 1.

Table 1. Qualified and unqualified SQL
Relationship to other DB2 areas Qualified SQL Unqualified SQL
Use of synonyms Not possible Possible
Binder ID Any Same as creator
Number of creators for tables and table spaces Any One
Use of VALIDATE(RUN) Is qualified Uses binder to qualify
Use of dynamic SQL Is qualified Uses executor to qualify
Require a separate test DB2 subsystem Yes No
Require same creator in test DB2 and production DB2 Yes No
Possibility of using multiple versions of the test tables in the same test DB2 subsystem No Yes

Some of the limitations shown in Table 1 can be bypassed if you develop your own preprocessor to modify the source code before invoking the DB2 precompiler. This allows you, for example, to change the creator in the SQL statements.

It is recommended that you use qualified SQL for dynamic SQL statements, because it is easier to administer.

If you use unqualified SQL, you must decide how to supply the CREATOR to fully identify the tables and views. There are two possibilities:
  • You can use synonyms. The synonym must be created by the authorization id specified in the DB2ENTRY and DB2CONN. Synonyms can only be created by the authorization ID itself. That means that you must develop a method to create the synonyms. You can use a TSO ID with the same ID as the authorization ID specified in the DB2ENTRY or DB2CONN. Another possibility is to design a CICS transaction ID (using the same authorization ID) that itself could do the CREATE SYNONYM statement. However, neither of these methods is advisable.
  • If you do not use synonyms, the CREATOR used in the bind process is the authorization ID of the binder. All tables and views referenced in the dynamic SQL must then be created with this ID. All transactions using dynamic SQL to access a common set of DB2 resources must then have the same authorization ID specified in the DB2ENTRY or DB2CONN. In most cases, it must be the SIGNID, or a character string. This restriction is normally not acceptable.

For these reasons, the use of unqualified SQL in dynamic SQL statements is not recommended.