A Quick Review of Schemas

In Chapter 4, "Working with Databases and Database Objects," we saw that schemas are objects that are used to logically classify and group other objects in the database. Schemas also make it possible to create multiple objects in a database without encountering namespace collisions. Most objects in a DB2 database are named using a two-part naming convention. The first (leftmost) part of the name is called the schema name or qualifier, and the second (rightmost) part is called the object name. Syntactically, these two parts are concatenated and delimited with a period (for example, PAYROLL.STAFF). When any object that can be qualified by a schema name (such as a table, view, index, user-defined data type, user-defined function, nickname, package, or trigger) is first created, it is assigned to a particular schema based on the qualifier in its name. Figure 6-15 illustrates how a table named EMPLOYEES would be assigned to the HR schema during the table creation process.

Figure 6-15: Assigning a table object to a schema.

The CURRENT SCHEMA (or CURRENT_SCHEMA) special register contains a value that identifies the schema name that is to be used to qualify references to database objects if no schema/qualifier name is specified. (With DB2 for z/OS, the CURRENT SQLID, or CURRENT_SQLID, special register is used instead.) The initial value of the CURRENT SCHEMA register is the authorization ID of the current session user. However, this value can be changed by executing the SET SCHEMA SQL statement. The syntax for the SET SCHEMA SQL statement is:

 SET <CURRENT> SCHEMA <=>   [['SchemaName']|   USER |   SESSION_USER |   SYSTEM_USER |   CURRENT_USER] 

where:

SchemaName

Identifies the name of an existing schema. (The name specified must be enclosed in single quotes and cannot exceed 30 characters in length.)

Thus, if you wanted to change the value of the CURRENT SCHEMA register from the authentication ID of the current user to "PAYROLL", you could do so by executing a SET SCHEMA SQL statement that looks something like this:

 SET CURRENT SCHEMA = 'PAYROLL' 

It is important to note that the value assigned to the CURRENT SCHEMA special register is not persistent across database restarts. Therefore, if you assign a value to the CURRENT SCHEMA special register, disconnect from the database, and reconnect, the CURRENT SCHEMA special register will contain your authentication ID, not the value you assigned it earlier.