DB2 Server for VSE & VM Application Programming (Version 7 Release 5)

Assigning Field Procedures to Columns

To assign a field procedure to a new column, include the FIELDPROC clause on

either the CREATE TABLE or ALTER TABLE statement. To assign field procedures

to columns in an existing table, you must unload the data, recreate the table to

include the field procedures, and they reload the data back into the table. If you

create a column without a field procedure, you cannot add one later.

Refer to the DB2 Server for VSE & VM SQL Reference manual for the syntax

diagrams for the CREATE and ALTER TABLE statements. The fieldproc-block for

these diagrams is shown below.

►► FIELDPROC program_name

►◄

▼

(

constant

)

Figure 63. fieldproc-block Syntax

For example:

ALTER TABLE SCOTT.SUPPLIERS ADD RATING CHAR(6) FIELDPROC MYFLDPRO (10,5)

The constants (10,5), that follow the program_name MYFLDPRO are optional

parameters, defined when the field procedure is written and passed to the field

procedure when it is invoked.

Understanding Field Procedure Rules

In most cases you will not have to worry about the rules that define when a field

procedure encodes or decodes a short string. However, if you understand when

the database manager calls field procedures, this can help you understand their

performance implications. The less you call field procedures to encode or decode

strings the better your application’s performance will be.

Understanding when field procedures are called can also help you to avoid some

pitfalls. For example, consider a table TABLE_A with a column COLUMN_A that

has fieldproc F1, and consider these two statements:

SELECT SUBSTR(MAX(COLUMN_A,1,5)) FROM TABLE_A

SELECT MAX(SUBSTR(COLUMN_A,1,5)) FROM TABLE_A

You might assume that the two statements should return essentially the same

result; however, different results can be returned depending on your coding. In the

first statement, the database manager does the following:

1. Finds the maximum encoded value in COLUMN_A

2. Decodes the result from MAX with field procedure F1

3. Applies the SUBSTR function to the decoded value of the result from MAX.

In the second statement, the database manager does the following:

1. Decodes the value in COLUMN_A with field procedure F1

2. Applies the SUBSTR function to the decoded value in COLUMN_A

3. Applies the MAX function to the result of the SUBSTR function.

That is, the first statement MAX is applied to encoded values, and the second is

applied to decoded values.

Chapter 11. Special Topics

283

The rest of this section covers the rules that define when a field procedure encodes

or decodes a short string.

Input from an Application Program

The field procedure is called to encode data when your application program inserts

or updates data. This includes the following statements:

v INSERT

v PUT

v UPDATE

Output to an Application Program

The field procedure is called to decode data when your application program

fetches or selects data. This includes the following statements:

v FETCH

v SELECT INTO

Comparison

If a column with a field procedure is compared to a constant, the constant is first

encoded by the field procedure. The comparison is then performed between the

encoded values in the column and the encoded value of the constant.

Host-variables, parameter markers, and the USER special register are treated the

same way.

For example, consider the following SQL statement where COLUMN_A has field

procedure F1:

SELECT * FROM MY_TABLE WHERE COLUMN_A > ’SMITH’

When processing the above statement, the database manager first encodes 'SMITH',

and then for each row in MY_TABLE, compares F1 to the encoded value in

COLUMN_A.

A field procedure can only encode short strings values. If the variable or constant

is of a data type other than CHAR, VARCHAR, GRAPHIC or VARGRAPHIC, a

negative SQLCODE is returned.

If a column with a field procedure is compared to another column, both columns

must have field procedures with the same program_name, comparable encoded data

type, and the same CCSID. If not, a negative SQLCODE is returned.

Referential Integrity

If a primary key column has a field procedure, then the foreign key column must

have the same field procedure, and the CCSIDs of both key columns must be the

same. Otherwise, a negative SQLCODE is returned. For two field procedures to be

the same, their program_names, encoded data type, encoded data length, and input

parameters must be identical.

For example, the following is correct:

CREATE TABLE PRIMARY

(COLUMN_A CHAR(10) FIELDPROC F1 NOT NULL,

COLUMN_B INTEGER)

PRIMARY KEY(COLUMN_A)

CREATE TABLE FOREIGN

(COLUMN_A CHAR(10) FIELDPROC F1 NOT NULL,

COLUMN_B CHAR(10))

284

Application Programming

ALTER TABLE FOREIGN

ADD FOREIGN KEY (COLUMN_A)

REFERENCES PRIMARY ON DELETE SET NULL

Scalar Functions

All scalar functions operate on decoded values. For example, if ’V’ is a string in a

column with a field procedure, HEX(’V’) returns the hexadecimal representation of

’V’. The result is not associated with the original column’s field procedure.

However, if the result of a scalar function is compared to a column that is

associated with a field procedure, this result is encoded by the comparison

column’s field procedure. The comparison is then made between the encoded

value of the column and the encoded result of the scalar function. This is

consistent with how columns with field procedures are compared to constants.

For example:

Consider a table (MY_TABLE) with COLUMN_A that has field procedure F1

and COLUMN_B that has field procedure F2. Consider the following SQL

statement:

SELECT * FROM MY_TABLE WHERE COLUMN_A > SUBSTR(COLUMN_B,3,3)

For each row of MY_TABLE, the following occurs:

a. The encoded values of COLUMN_B are decoded by field procedure F2.

b. The substring operation is applied to the decoded value of COLUMN_B.

c. The result of the substring operation is encoded by field procedure F1.

d. Finally, the encoded value of COLUMN_A is compared to the encoded

result of the substring operation.

Consider a table (MY_TABLE) with three columns, where COLUMN_A has

field procedure F1, COLUMN_B has field procedure F2, and COLUMN_C is

NOT NULL and has field procedure F3. Consider the following SQL statement:

SELECT * FROM MY_TABLE WHERE COLUMN_A > VALUE(COLUMN_B,COLUMN_C)

For each row of MY_TABLE, the following occurs:

a. If the value of COLUMN_B is not null, then COLUMN_B is decoded, using

F2. Call the result ’M’.

b. If the value of B is null, then C is decoded, using F3. Call the result ’M’.

’M’ is then encoded using F1.

d. The encoded result of the VALUE function is then compared to the encoded

value of COLUMN_A.

Note: A field procedure is never called to encode or decode a NULL value. A

NULL value always maps to a NULL.

If a column with a field procedure is the argument of the LENGTH function,

first it is decoded by the field procedure, and then the length of the result is

returned. Of course, if the column data type is a fixed length (for example,

CHAR(15)), there is no need to actually decode the column value. The length

returned by the function is simply the fixed length of the column (15 in this

example).

Column Functions

The column functions MAX and MIN operate on encoded values. The remaining

column functions operate on numeric data, and are not affected by field

procedures.

Concatenation

The concatenation operator is basically a scalar function, and follows the same

rules as a scalar function.

Chapter 11. Special Topics

285

For example, consider a table (MY_TABLE) with COLUMN_A that has field

procedure F1 and COLUMN_B that has field procedure F2. Now, consider the

following SQL statement:

SELECT * FROM MY_TABLE WHERE COLUMN_A > ’ADDITION’ CONCAT COLUMN_B

For each row in MY_TABLE, the following occurs:

1. The value of COLUMN_B is decoded by F2.

’ADDITION’ is concatenated with the decoded value in COLUMN_B.

3. The result of the concatenation is encoded by field procedure F1.

4. The encoded result of the concatenation is compared to the encoded value of

COLUMN_A.

The IN and BETWEEN Predicates

These predicates operate the same as a comparison between a column with a field

procedure and a constant.

The LIKE Predicate

This predicate operates on decoded values.

Sorting

Indexes will be based on encoded values. The ORDER BY and GROUP BY clauses

will sort the data according to the encoded format. The database manager also

sorts values during a UNION operation.

Null Values

While a column with a field procedure may be defined to allow null values, the

field procedure is never called to process a null value. A decoded null value

always maps to an encoded null value, and an encoded null always maps to a

decoded null.

Unions and Joins

The rules for comparing two columns with field procedures apply to unions and

joins. The two columns must have the same field procedure.

Sub-SELECTS

All the rules described above apply to sub-SELECTs.

For example:

SELECT * FROM TABLE_1

WHERE COLUMN_A=(SELECT COLUMN_B FROM TABLE_2);

SELECT * FROM TABLE_1

WHERE COLUMN_A IN (SELECT COLUMN_B FROM TABLE_2);

If the columns COLUMN_A and COLUMN_B have different field procedures these

statements are invalid (field procedure comparison rules apply). For example:

INSERT INTO T1 (COLUMN_A) SELECT COLUMN_B FROM T2;

In this statement, the decoded data types for COLUMN_A and COLUMN_B must

be compatible. If so, the value in COLUMN_B will be decoded with F2. The

decoded value is then encoded by F1, and the resulting value is inserted into

COLUMN_A.

Using CMS Work Units (DB2 Server for VM)

Application programs can use the CMS work unit facility, which supports the

following DB2 Server for VM functions:

286

Application Programming

v One application can invoke another, independent of the processing of the

invoked application.

v An application can be invoked in the CMS SUBSET, independent of the program

from which the CMS SUBSET was invoked.

v Applications can issue concurrent server requests for DB2 Server for VM

resources.

v An application can establish more than one path into the same database.

v An application can copy data from one DB2 Server for VM database to another

without first having to write the data to a temporary file.

Note: You should not use the CMS SUBSET function if the WORKUNIT option in

SQLINIT/SQLGLOB is set to NO.

Using Work Units in Application Programs

Associated with each work unit is a unique work unit id assigned by CMS. When

you invoke your program, a default work unit id identifies the currently active

work unit for your program. To switch to a new work unit, you must explicitly

change the currently active work unit.

Use the CMS routines shown in Table 32 to manage work units:

Table 32. Routines to Manage Work Units

CSL Call

Function

Description

DMSGETWU

Get

Obtains and reserves a work unit id from CMS.

You must invoke this routine for each separate

work unit id

work unit you wish to manage.

DMSPUSWU

Push

Pushes the work unit id onto the work unit stack.

Makes the pushed work unit the currently active

work unit id

one.

DMSPOPWU

Pop

Pops the work unit id from the top of the stack.

The next work unit on the stack becomes the

work unit id

currently active one.

Processing the First SQL Statement in the Work Unit

Although a work unit may have been established and made the currently active

work unit, it is not known to the database manager until the first SQL statement in

the work unit is executed. When this SQL statement is processed, the work unit id

is obtained from CMS, a logical path (work unit) is established between the

application and the DB2 Server for VSE & VM resource, and the user is connected

to either the default application server or the explicitly connected application

server. (The default application server is the one established by the SQLINIT

EXEC.) The CONNECT statement can be used to connect to the desired application

server.

If the work unit id is already known, no change occurs in the database to which

the user is connected in that work unit, unless the user explicitly issues a

CONNECT to change the database.

Invoking Another Application Program

One DB2 Server for VSE & VM application can be invoked from another. By

starting a separate CMS work unit before invoking the second application, the

calling application will not be affected by any COMMIT or ROLLBACK statement

issued from the called application. When the called application pops its work unit

Chapter 11. Special Topics

287

id from the top of the stack, control is returned to the first application. The calling

application is in the same state as it was before it called the other application. The

calling application and the called application can access the same database or

different databases.

Figure 64 illustrates how the calling program can be isolated from the work

committed or rolled back by the called program.

Program 1

Program 2

WU1

WU2

Start

Establish WU2

COMMIT/ROLLBACK

Call Program 2

Re-establish WU1

End

Figure 64. Program Transitioning Using CMS Work Units

Invoking Applications in CMS SUBSET

A DB2 Server for VSE & VM application (for example ISQL) can interrupt

processing of its logical unit of work to go into CMS SUBSET and invoke another

DB2 Server for VSE & VM application. The processing done by the invoked

application does not affect the invoking program. When control is returned to the

invoking program, the LUW is in the same state as it was before going into CMS

SUBSET.

To prevent the application in the CMS SUBSET from affecting any work done by

the invoking application in normal CMS, the SQLRMEND EXEC cannot be used

with the COMMIT ALL or ROLLBACK ALL parameters while in CMS SUBSET

mode. (See the DB2 Server for VSE & VM Database Administration manual for more

information on the SQLRMEND EXEC.)

Processing Applications Concurrently

More than one DB2 Server for VSE & VM application can concurrently process

against the same DB2 Server for VSE & VM database or different DB2 Server for

VSE & VM databases. The application server ensures that processing done by one

application is independent of that done by another. In order to do this, the server

acquires and manages work units for each application.

Accessing the Database from Different Points in the Program

By acquiring two or more work units, an application can logically access the same

database from different points in the application. These work units (and their paths

into the database) cannot be processed concurrently.

Copying Data across Databases

Applications can copy data from one database to another by following these steps:

1. Establish a work unit #1.

2. CONNECT to database #1.

3. Establish a work unit #2.

4. CONNECT to database #2.

5. Make work unit #1 the current work unit.

6. Open a cursor and read into an array as many rows as feasible.

7. Make work unit #2 the current work unit.

288

Application Programming

8. Open an insert cursor and put all rows from an array into a table.

9. Repeat until all rows are read and put into a table.

How Locking Works with CMS Work Units

If an active work unit requests a SHARE lock on a DB2 Server for VSE & VM

resource, and a suspended work unit has an EXCLUSIVE lock on the same

resource, the active work unit has to wait until the EXCLUSIVE lock is released.

Since the suspended work unit cannot resume processing until the active work

unit is released or suspended, the user will be in an infinite wait state unless a

cancel is issued or the agent is forced off.

This same locking problem will occur if the suspended work unit has a SHARE

lock on the resource and the active work unit requests an EXCLUSIVE lock on the

same resource.

Environmental Considerations

To use CMS work units, your CMS virtual machine and the database virtual

machine must be running under the VM/ESA operating system, the application

server must be running in multiple user mode, and the Work Unit option in the

SQLINIT EXEC must be set to yes (the default) at initialization time. See the DB2

Server for VSE & VM Database Administration manual for more information on

SQLINIT EXEC.

The database manager does not reuse links for different work units. If you no

longer need a work unit, you should enter either COMMIT RELEASE or

ROLLBACK RELEASE, to free the (APPC/VM) path for reuse.

Performance Considerations

There is a degradation in performance when SQLINIT WORKUNIT (YES) is

specified either directly, or indirectly as the default. This applies even if the

application is not using multiple work units.

Ensuring Data Integrity

Data integrity refers to the accuracy and correctness of data in the database. When

related changes are made to a database, the database manager maintains integrity

of the data by ensuring that either all or none of the changes are made. This

protects other users and programs from using inconsistent or wrong data. This

type of integrity is called atomic integrity.

Data integrity is also maintained by ensuring the uniqueness of certain data in the

database. For example, the SUPPLIERS table must not have duplicate supplier

numbers (SUPPNO). Using this integrity rule, the database manager ensures that

duplicates do not exist. This type of integrity is called entity integrity.

For consistency and integrity, when one table references values in another table,

the referenced values must exist in both tables, or the reference is not valid. The

database manager automatically enforces rules that you define on the tables. These

rules are called referential constraints. Enforcement of referential constraints ensures

the referential integrity of the data referenced.

Chapter 11. Special Topics

289

Ensuring Entity Integrity

The rule that each row in the EMPLOYEE table must represent one and only one

employee is an example of entity integrity. By defining a primary key on the table,

you can ensure that duplicate rows do not occur, thereby enforcing entity integrity.

For example, in the following SQL statement, the column EMPNO is defined as a

primary key, so a unique index is automatically created on that column. This

enforces uniqueness of the data in that column.

CREATE TABLE EMPLOYEE

(EMPNO

CHAR(6)

NOT NULL,

FIRSTNME VARCHAR(12) NOT NULL,

LASTNAME VARCHAR(15) NOT NULL,

SALARY

DECIMAL(9,2)

PRIMARY KEY (EMPNO)

)

Using Unique Constraints

A unique constraint enables you to enforce data integrity without having to

enforce entity integrity. While a primary key can ensure that each row in the

EMPLOYEE table represents one and only one employee, a unique constraint can

ensure that each entry in another column is unique. For example, a company has

one telephone for every employee and wants to maintain a set of unique phone

numbers. Its database, however, already uses an employee number as a primary

key. A unique constraint can ensure that no phone numbers are repeated in the

table. Also, if the phone number consists of several columns (area code, 7-digit

number, extension), the unique constraint can include all those columns.

CREATE TABLE EMPLOYEE

(EMPNO

CHAR(6)

NOT NULL,

FIRSTNME VARCHAR(12) NOT NULL,

LASTNAME VARCHAR(15) NOT NULL,

AREACODE CHAR(3)

NOT NULL,

PHONENUM CHAR(7)

NOT NULL,

PHONEEXT CHAR(4)

NOT NULL,

PRIMARY KEY (EMPNO)

UNIQUE PHONE (AREACODE,PHONENUM,PHONEEXT)

)

The ALTER TABLE command can be used to add, activate, deactivate, or remove a

unique constraint. Another way to remove a unique constraint is either by

dropping the table or the dbspace. Although a unique index is created when the

unique constraint is created, the constraint cannot be dropped by dropping the

index.

When Creating a View

The WITH CHECK OPTION clause in the CREATE VIEW statement is an example

of data integrity in the maintenance of data defined by a view. See “Creating a

View” on page 62.

Ensuring Referential Integrity

Defining Terms

Referential integrity defines the condition on a set of tables in which the existence

of values in one table depends on the existence of the same values in another table.

By enforcing referential constraints (referential integrity rules) that are part of the

table definitions, the database manager ensures the referential integrity of the data

in the tables.

290

Application Programming

Figure 65 on page 291 shows examples of relationships supported by the database

manager.

T10

T11

Figure 65. Table Relationships with Referential Integrity. T1, T2, ... are tables. Arrows point

from parent tables to dependent tables.

You should be familiar with the following terms:

Relationship

A relationship is formed by connecting two tables

directly. The tables are related through matching

column values in the tables. For example, in

Figure 65, tables T1 and T2 show a simple

relationship. T3 has two relationships with T4. T5

has two paths to T7 (one directly, the other through

T6), but only one relationship with T7. T5 also has a

relationship with T6. Tables are connected to each

other when relationships are formed.

Referential Constraint

A relationship between a primary key and a

foreign key, along with a set of rules that define

how the relationship is maintained. This

relationship is that every foreign key value must

match a primary key value or be null.

Referential Cycle

A set of referential constraints such that each table

in the set is a descendent of itself.

Referential Structure

A set of tables that are related to each other by

Chapter 11. Special Topics

291

referential constraints. For example, T5 is a parent

of both T6 and T7, which are its dependents. T7 is

also a dependent of T6.

Parent Table

A table whose primary key is referenced in a

referential constraint. For example, T1 is the parent

of T2.

Dependent Table

A table with a foreign key that is related to another

table (the parent) through a referential constraint.

For example, T4 is a dependent of T3.

Delete-Connected Table

A table that may be involved in a delete operation

on another table.

Descendent Table

A table is a descendent table if it is a dependent

table or a dependent of a descendent table. For

example, in Figure 65 on page 291, both T6 and T7

are descendent tables of T5.

Parent Row

A row in a parent table with a primary key value

that is referenced by the foreign key value in at

least one row in a dependent table.

Dependent Row

A row in a dependent table with a foreign key

value that matches a primary key value in the

parent table referenced in the referential constraint.

Self-Referencing Table

A self-referencing table is both the parent and the

dependent table in the same relationship. This

relationship is not supported by the DB2 Server for

VSE & VM product. For example, T11 is a

self-referencing table.

Primary Key

A set of non-null columns that together uniquely

identify every row in a table. The values in these

columns are known as primary key values.

Foreign Key

A set of columns whose values are called foreign

key values. A foreign key only exists as part of a

referential constraint.

Ensuring Referential Integrity in New Tables

To ensure referential integrity in new tables, you must specify a primary key, a

foreign key, and a delete rule that together define the relationship between the

parent table and the dependent table. Delete rules specify what will happen to the

dependent rows if the corresponding parent row is deleted. Insert and update rules

are automatically defined on tables when primary keys and foreign keys are

defined on those tables.

The relationship is defined when the new table is created using the CREATE

TABLE statement.

You should be aware of the referential constraints of the tables you manipulate, as

well as the rules for those tables. In this way you can avoid violating any

referential constraints, and take appropriate action should you inadvertently do so.

In the example below, the EMPLOYEE table is the parent of the DEPARTMENT

table. This relationship is established by specifying a primary key (EMPNO) on the

EMPLOYEE table and a foreign key (MGRNO) on the DEPARTMENT table. This

relationship specifies that every manager listed in the DEPARTMENT table is also

292

Application Programming

listed in the EMPLOYEE table. The REFERENCES privilege is required on the

parent table. The foreign key is nullable.

CREATE TABLE EMPLOYEE

(EMPNO

CHAR(6)

NOT NULL

primary key

FIRSTNME

VARCHAR(12)

NOT NULL

MIDINIT

CHAR(1)

NOT NULL

LASTNAME

VARCHAR(15)

NOT NULL

WORKDEPT

CHAR(3)

PHONENO

CHAR(4)

SALARY

DECIMAL(9,2)

PRIMARY KEY (EMPNO)

)

CREATE TABLE DEPARTMENT

primary key

(DEPTNO

CHAR(3)

NOT NULL

DEPTNAME

VARCHAR(36)

NOT NULL

MGRNO

CHAR(6)

foreign key

PRIMARY KEY (DEPTNO)

FOREIGN KEY MNUM (MGRNO)

REFERENCES EMPLOYEE ON DELETE SET NULL)

Adding Referential Integrity to Existing Tables

To add referential integrity to existing tables, you must add a primary key, a

foreign key, and a delete rule that together define the relationship between the

parent table and the dependent table. Delete rules specify what will happen to the

dependent rows if the corresponding parent row is deleted. Insert and update rules

are implicitly defined on tables when primary keys and foreign keys are defined

on those tables.

The relationship is defined using the ALTER TABLE statement.

When keys (primary or foreign) are added to an existing table, any packages that

depend on the table are invalidated. When the application programs are run again,

the packages will be dynamically repreprocessed. Refer to “Running the Program”

on page 144 (DB2 Server for VM) or “Running the Program” on page 182 (DB2

Server for VSE) for more information on dynamic repreprocessing.

As in the case of new tables, you should be aware of the referential constraints of

the tables you manipulate as well as the rules for those tables, in order to avoid

violating any referential constraints or to take appropriate action should you

inadvertently do so.

Consider the existing DEPARTMENT and PROJECT tables. The PROJECT table

was created by the following CREATE TABLE statement:

CREATE TABLE PROJECT

(PROJNO

CHAR(6)

NOT NULL

primary key

PROJNAME

VARCHAR(24)

NOT NULL

DEPTNO

CHAR(3)

NOT NULL

foreign key (To be added)

RESPEMP

CHAR(6)

NOT NULL

PRSTAFF

DECIMAL(5,2)

PRIMARY KEY (PROJNO)

)

The following ALTER TABLE statement adds a referential constraint to the

PROJECT table, thereby establishing a relationship between it and the existing

DEPARTMENT table:

Chapter 11. Special Topics

293

ALTER TABLE PROJECT

ADD FOREIGN KEY DNUM (DEPTNO)

REFERENCES DEPARTMENT ON DELETE CASCADE;

In this relationship, DEPARTMENT is the parent table and PROJECT is the

dependent table. This specifies that every department that is responsible for a

project is also in the DEPARTMENT table.

Note: The ALTER TABLE statement can also be used to defer the enforcement of

referential constraints or cause the removal of referential constraints. These

topics are discussed in the section “Enforcing Referential Integrity” on page

299.

Managing Table Relationships

The ALTER TABLE statement can be used to add, drop, activate, or deactivate

primary and foreign keys. Various clauses of the statement alter the keys that

establish relationships between tables. When the ALTER TABLE statement

establishes or changes relationships, specific privileges are required on parent

tables and dependent tables. Table 33 shows the privileges that are required.

Table 33. Privileges to Use the ALTER TABLE Statement

Privilege on

ALTER TABLE Clause

Parent Table

Dependent Table

Add Column

ALTER

Add Primary Key

ALTER

Add Foreign Key

REFERENCES

ALTER

Drop Primary Key

ALTER

REFERENCES¹

Drop Foreign Key

REFERENCES

ALTER

Deactivate Primary Key

ALTER

REFERENCES¹

Deactivate Foreign Key

REFERENCES

ALTER

Activate Primary Key

ALTER

REFERENCES¹

Activate Foreign Key

REFERENCES

ALTER

Note: The REFERENCES privilege is required only if the parent table has

dependents.

You can grant to or revoke from another user the privilege to add, drop, activate,

or deactivate a relationship between a parent table and its dependent. In order to

enter any of these statements, you must have the REFERENCES privilege on the

parent table whenever a referential constraint is to be:

v Created on a new table (CREATE TABLE)

v Added to an existing table (ALTER TABLE)

v Dropped, activated, or deactivated (ALTER TABLE).

294

Application Programming

By revoking the privileges previously granted on tables in a referential structure,

you can prevent the accidental removal of constraints that your applications may

depend on.

Modifying Applications to Ensure Integrity

Applications that currently enforce consistency and integrity of their data can be

modified to let the database manager do the checking. Using the referential

constraints and the integrity rules that apply to the tables containing the data, the

system checks that the rules are adhered to, and thereby enforces integrity of the

data. As this function can be performed by the database manager, some existing

code can be removed from the application.

Modifying Data in Tables Containing Referential Constraints

To maintain the consistency and integrity of the data, the database manager checks

that integrity rules for insert, update, and delete operations are followed.

Applying Insert Rules: The database manager checks the implicit insert rules when

a row is inserted into either the parent or a dependent table in a referential

structure. When a row is inserted into a parent table, the database manager checks

that the primary key remains unique and does not contain null values. When a

row is inserted into a dependent table, the database manager checks each foreign

key for the following:

v Each has a matching primary key in the parent table, or

v Each contains a null value in one or more of its columns.

Assuming for the moment that department D21 does not already exist in the

parent table (DEPARTMENT), the following INSERT statement adds a new row to

DEPARTMENT.

INSERT INTO DEPARTMENT (DEPTNO,DEPTNAME,MGRNO,ADMRDEPT)

VALUES (‘D21’,‘ADMINISTRATION SYSTEMS’,‘000070’,‘D01’)

Note: The primary key (the DEPTNO column) in the DEPARTMENT table remains

unique and does not contain null values.

Table 34. Part of Department Table

DEPTNO

DEPTNAME

MGRNO

A00

SPIFFY COMPUTER SERVICE DIV.

000010

B01

PLANNING

000020

C01

INFORMATION CENTER

000030

D01

DEVELOPMENT CENTER

D11

MANUFACTURING SYSTEMS

000060

E11

OPERATIONS

000090

D21

ADMINISTRATION SYSTEMS

000070

Assuming for the moment that project IF2000 does not already exist in the

dependent table (PROJECT), the following INSERT statement adds a new row with

DEPTNO = C01 to PROJECT. This value for DEPTNO must exist in the parent

(DEPARTMENT) table.

INSERT INTO PROJECT (PROJNO,PROJNAME,DEPTNO,RESPEMP,PRSTAFF)

VALUES (‘IF2000’,‘USER EDUCATION’,‘C01’,‘000030’,1.00)

Chapter 11. Special Topics

295

Primary

key

Part of parent table (DEPARTMENT)

column

DEPTNO

DEPTNAME

MGRNO

C01

INFORMATION CENTER

000030

Foreign

key column

Part of dependent table (PROJECT)

PROJNO

PROJNAME

DEPTNO

RESPEMP

PRSTAFF

IF2000

USER EDUCATION

C01

000030

1.00

Applying Update Rules: When a key value is updated, the database manager

checks the implicit update rules. A key value may be updated when a parent row

(primary key) or a dependent row (foreign key) is updated. If the primary key is

updated due to updates made to the parent table, the database manager checks

that the updated primary key is unique and is not null. All rows in the dependent

table that reference the primary key must first be deleted or updated, or an error

will occur. This ensures that the dependent table is not referencing an “old”

primary key.

If foreign keys are updated, the database manager checks that each updated

foreign key has either a matching primary key in the corresponding parent table,

or that the updated foreign key is a null key. A foreign key is null when one or

more of its column values are null.

Notes:

1. If a searched update contains a subquery, any table referenced in the subquery

must not be a dependent of the table in the UPDATE clause. (See the DB2

Server for VSE & VM SQL Reference manual for more information.) In the

example below, the NAME table must not be a descendent of the EMPLOYEE

table:

UPDATE EMPLOYEE

SET SALARY = 65000.00

WHERE LASTNAME = ’SMITH’ AND EXISTS

(SELECT * FROM NAME

WHERE LASTNAME = ’SMITH’)

2. In recoverable storage pools, when a searched update is performed against a

column or set of columns, defined in a unique index, primary key, or unique

constraint, uniqueness is checked after all rows have been updated. If

duplicates exist, then the statement is rolled back.

3. In nonrecoverable storage pools, searched updates are sensitive to the order

(ascending or descending) of the data. Since a unique index is automatically

created on a primary key column, you cannot use a searched update against a

primary key column. This ensures that updates to the primary key are

independent of the order of the data.

4. Positioned updates are sensitive to the order (ascending or descending) of the

data. Since a unique index is automatically created on a primary key column,

you cannot use a positioned update against a primary key column. This

ensures that updates to the primary key are independent of the order of the

data.

The following operations change the DEPTNO B01 to F01 in the DEPARTMENT

table. Since DEPTNO is a primary key in the parent table, the foreign key with

DEPTNO equal to B01 must also be changed in the dependent table (PROJECT).

296

Application Programming

INSERT INTO DEPARTMENT (DEPTNO,DEPTNAME,MGRNO,ADMRDEPT)

VALUES (‘F01’,‘PLANNING’,‘000020’‘,A00’)

UPDATE PROJECT

SET DEPTNO = ’F01’

WHERE DEPTNO = ’B01’

DELETE FROM DEPARTMENT

WHERE DEPTNO = ’B01’

Primary

key

Part of parent table (DEPARTMENT)

column

DEPTNO

DEPTNAME

MGRNO

F01

PLANNING

000020

Foreign

key column

Part of dependent table (PROJECT)

PROJNO

PROJNAME

DEPTNO

RESPEMP

PRSTAFF

PL2100

WELD LINE PLANNING

F01

000020

1.00

The example below changes the DEPTNO A00 to D11 for the ADMIN SERVICES

project in the PROJECT table. Since DEPTNO is a primary key in the parent table,

the database manager ensures that DEPTNO D11 in the dependent table

(PROJECT) also exists in the parent table (DEPARTMENT).

UPDATE PROJECT

SET DEPTNO = ’D11’

WHERE PROJNAME = ’ADMIN SERVICES’

Primary

key

Part of parent table (DEPARTMENT)

column

DEPTNO

DEPTNAME

MGRNO

MANUFACTURING

D11

000060

SYSTEMS

Foreign

key column

Part of dependent table (PROJECT)

PROJNAME

DEPTNO

RESPEMP

PRSTAFF

PROJNO

AD3100

ADMIN SERVICES

D11

000010

6.50

Applying Delete Rules: The database manager does not do any checking when

data is deleted from dependent tables. The delete rule in a referential constraint

clause defines what action should be taken by the database manager when a

parent row is deleted. The delete rules are:

Chapter 11. Special Topics

297

v The RESTRICT rule prevents the deletion of a parent row unless all the

dependent rows have been deleted first. This is the default rule.

v The SET NULL rule sets all nullable columns of the foreign key to null before

deleting the parent row. At least one column of the foreign key must be nullable.

v The CASCADE rule deletes rows at each level containing dependent tables that

have the referential constraint CASCADE.

Restrictions on Using Delete Rules:

v If a table with a referential constraint of CASCADE has dependent tables that

have different delete rules, such as RESTRICT, a delete operation is successful

only if the object row is not found in the dependent table. If the object row is

found in the dependent table, the CASCADE delete operation is rolled back.

That is, the SET NULL and RESTRICT rules maintain their referential integrity

between parent and dependent tables.

v A table cannot be delete-connected to itself in a referential cycle involving two

or more tables.

v If a dependent table is delete-connected to the parent table through multiple

delete paths, each path must have the same delete rule and this rule cannot be

SET NULL.

v If a Searched DELETE contains a subquery, any table referenced in the subquery

and any table that has a referential constraint of CASCADE or SET NULL with

the table referenced in the subquery must not be a dependent of the table in the

FROM clause. (See the DB2 Server for VSE & VM SQL Reference manual for more

information.)

In the following example, the NAME table must not be a descendent of the

EMPLOYEE table:

DELETE FROM EMPLOYEE

WHERE LASTNAME = ’SMITH’ AND EXISTS

(SELECT * FROM NAME

WHERE LASTNAME = ’SMITH’)

In the example below, the row with EMPNO equal to 000050 is deleted from the

EMPLOYEE table:

DELETE FROM EMPLOYEE

WHERE LASTNAME = ’GEYER’

Foreign

key

column

Primary

key

Part of parent table (DEPARTMENT)

column

DEPTNO

DEPTNAME

MGRNO

SUPPORT SERVICES

E01

Foreign

key column

Part of PROJECT table

PROJNO

PROJNAME

DEPTNO

RESPEMP

PRSTAFF

OP1000

OPERATION SUPPORT

E01

6.00

298

Application Programming

Because the EMPLOYEE table is a parent table and the delete rule is SET NULL in

the relationship that exists between the EMPLOYEE table and the DEPARTMENT

table, the database manager sets MGRNO equal 000050 to null in the

DEPARTMENT table. Also, because the EMPLOYEE table is a parent table and the

delete rule is SET NULL in the relationship that exists between the EMPLOYEE

table and the PROJECT table, the database manager sets RESEMP equal 000050 to

null in the PROJECT table. (Refer to Figure 66 on page 300 for more information.)

In the example below, the row with DEPTNO equal to D01 is deleted from the

DEPARTMENT table:

DELETE FROM DEPARTMENT

WHERE DEPTNAME = ’DEVELOPMENT CENTER’

Because the DEPARTMENT table is a parent table and the CASCADE rule was set

in the relationship that exists between the DEPARTMENT table and the PROJECT

table, the row with DEPTNO D01 is also deleted from the PROJECT table.

Generating SQL Statements in Response to Table Modifications

When INSERT, UPDATE, and DELETE statements are issued against tables in a

referential structure, the database manager generates internal SQL statements,

which it uses to ensure the consistency and integrity of the data in the tables. The

number of rows affected, the cost of processing the INSERT, UPDATE, DELETE,

and the internally generated statements are returned in the SQLERRD fields in the

SQLCA. The SQLERRD(3) gives the number of rows that were processed

successfully. Upon successful completion of the DELETE statement, SQLERRD(5)

contains the number of dependent rows that were successfully deleted or set to

null. For other data-manipulating language (DML) statements, SQLERRD(5) is set

to zero. The relative cost of processing all the statements is given in the

SQLERRD(4) field.

Additional information on internally generated statements can be found in tables

updated by the EXPLAIN statement. (This statement is discussed in the DB2 Server

for VSE & VM SQL Reference manual.) To determine this information, enter the

EXPLAIN statement for the INSERT, UPDATE, or DELETE statement.

Enforcing Referential Integrity

Referential constraints may be enforced as soon as they are defined, or their

enforcement may be deferred. If the constraints are enforced as soon as they are

defined, the insert, update, and delete integrity rules are enforced immediately

when the INSERT, UPDATE, and DELETE statements are issued.

To defer the enforcement of a constraint is to render the constraint inactive so that

it is not immediately enforced when the INSERT, UPDATE, and DELETE

statements are issued. This is done by deactivating either the primary key, the

dependent foreign key(s), or the foreign key(s). If any of these keys are

deactivated, both the parent and the dependent tables become inactive and

unavailable for data manipulation statements to general users (that is, other than

the DBA and the owner of the tables). However, these tables are available for data

definition statements.

When a primary key is deactivated, all active dependent foreign keys are implicitly

deactivated, and the primary key index is dropped from the parent table. Both

parent and dependent tables become inactive. A primary key cannot be implicitly

deactivated.

Chapter 11. Special Topics

299

With a table in an inactive state, only the owner of the table or a database

administrator (DBA) can enter data manipulating language (DML) statements

against it. No one can enter INSERT, UPDATE, and DELETE statements that cause

statements to be generated against an inactive table.

When keys (either primary or foreign) are activated, the constraints are

automatically verified. If they cannot be verified because of integrity problems, an

error message is returned, and the tables remain unavailable for data manipulation

statements entered by users other than the DBA or the owner.

When keys (either primary or foreign) are activated or deactivated, packages that

depend on the table are invalidated. When the program is run again, it is

dynamically repreprocessed.

In general, you would defer the enforcement of referential constraints between

tables when large amounts of data are to be loaded, or when data is to be loaded

in an order that violates the referential constraint at some point during the loading

operation. For further information, refer to the DB2 Server for VSE & VM Database

Administration manual.

The relationships among the EMPLOYEE, DEPARTMENT, and PROJECT tables are

shown in Figure 66.

DEPARTMENT

MGRNO

DEPTNO

(N)

(R)

PROJECT

EMPLOYEE

WORKDEPT

DEPTNO

(N)

EMPNO

RESEMP

Figure 66. Relationships among the TABLES. Arrows point from primary keys in parent tables

to foreign keys in dependent tables. Delete rules are labeled as (C) = CASCADE, (N) = SET

NULL, (R) = RESTRICT.

300

Application Programming

CREATE TABLE EMPLOYEE

(EMPNO

CHAR(6)

NOT NULL,

primary key

FIRSTNME

VARCHAR(12)

NOT NULL,

MIDINIT

CHAR(1)

NOT NULL,

LASTNAME

VARCHAR(15)

NOT NULL,

WORKDEPT

CHAR(3)

PHONENO

CHAR(4)

SALARY

DECIMAL(9,2)

PRIMARY KEY (EMPNO)

)

CREATE TABLE DEPARTMENT

(DEPTNO

CHAR(3)

NOT NULL,

primary key

DEPTNAME

VARCHAR(36)

NOT NULL,

MGRNO

CHAR(6)

foreign key

PRIMARY KEY (DEPTNO)

FOREIGN KEY MNUM (MGRNO)

REFERENCES EMPLOYEE ON DELETE SET NULL)

ALTER TABLE EMPLOYEE ADD FOREIGN KEY WORKNUM (WORKDEPT)

REFERENCES DEPARTMENT ON DELETE SET NULL

CREATE TABLE PROJECT

(PROJNO

CHAR(6)

NOT NULL,

primary key

PROJNAME

VARCHAR(24)

NOT NULL,

DEPTNO

CHAR(3)

NOT NULL,

foreign key

RESPEMP

CHAR(6)

NOT NULL,

PRSTAFF

DECIMAL(5,2)

PRIMARY KEY (PROJNO)

FOREIGN KEY DNUM (DEPTNO)

REFERENCES DEPARTMENT ON DELETE CASCADE)

Then,

ALTER TABLE DEPARTMENT DEACTIVATE PRIMARY KEY

explicitly deactivates the primary key in DEPARTMENT, and implicitly deactivates

the foreign keys DNUM in the PROJECT table and WORKNUM in the EMPLOYEE

table. The DEPARTMENT, EMPLOYEE, and PROJECT tables become inactive.

Therefore, only the owner of these tables or the DBA can enter data manipulation

statements against the tables.

However,

ALTER TABLE DEPARTMENT DEACTIVATE FOREIGN KEY MNUM

will not affect the primary key in the EMPLOYEE table. However, both the

EMPLOYEE table and the DEPARTMENT table become inactive since the foreign

key affects both tables. As mentioned earlier, when tables become inactive, only the

owner of the tables or the DBA can enter data manipulation statements against

them.

Removing Referential Constraints

To remove a referential constraint, you must drop the foreign key. When a table

that contains foreign keys is dropped, the referential constraints associated with

that table are removed. You can drop a table explicitly with the DROP TABLE

statement, or implicitly with the DROP DBSPACE statement. You can also drop the

foreign key with the ALTER TABLE statement, provided that you have the ALTER

privilege on the dependent table and the REFERENCES privilege on the parent

Chapter 11. Special Topics

301

table. For descriptions of the above three statements, see Chapter 9, “Maintaining

Objects Used by a Program,” on page 255.

When a table that contains a primary key is dropped, the database manager drops

the primary key and any foreign keys that reference the primary key and removes

the referential constraints associated with those foreign keys. The ALTER TABLE

statement can also be used to drop a primary key directly. To use the ALTER

TABLE statement for this purpose, you must have the ALTER and REFERENCES

privileges on the parent table as well as the ALTER privilege on all dependent

tables.

When keys are dropped, any packages that depend on the table are invalidated.

When the program is run again, it is dynamically repreprocessed. The new

package no longer contains internally generated statements to enforce referential

integrity.

Switching Application Servers

You can access multiple application servers from within an application program,

but only one application server can be accessed at a time. DB2 Server for VM

application servers can reside on the same processor as the user, or on another

processor (in the TSAF collection or the SNA network). VSE application servers

must reside on the same processor as the user, if DRDA protocol is not being used.

If the VSE application server does not reside on the same processor as the

CICS/VSE online user, the VSE application server must be accessed during the

DRDA protocol. This VSE server must be defined as a remote DRDA server to the

DB2 Server for VSE Online Resource Adapter. VM application servers, accessed

through VSE guest sharing or using the DRDA protocol, may reside on the same

processor as the user, or on another processor (in the TSAF collection or the SNA

network). If a program is written to access multiple application servers, its package

must exist on all of them.

This section discusses these authorities in more detail, and explains how to switch

application servers from your application program. For a detailed discussion on

establishing communication links between application requesters and application

servers, refer to the DB2 Server for VM System Administration or the DB2 Server for

VSE System Administration manual.

Identifying Switching Options

Use the CONNECT statement to switch among application servers if you want

application programs to connect to different application servers while running. For

more information on the CONNECT statement, see the DB2 Server for VSE & VM

SQL Reference manual.

Comparing Switching to Other Methods (DB2 Server for VM)

Figure 68 on page 303 and Figure 70 on page 306 show how an application

program, indicated by PGM, accesses three application servers with and without

switching application servers in the program. The application servers can reside on

the same processor as the program or on a different processor.

The application server specified by the SQLINIT EXEC is the default application

server. In Figure 70 on page 306 the default application server is DB01.

If you are not switching application servers in the program, to access another

application server you must terminate the program, reissue the SQLINIT EXEC,

302

Application Programming

and run the program again. In Figure 68, for example, to switch from application

server DB01 to application server DB02, you must terminate the program PGM,

reissue SQLINIT, and run the program again.

SQLINIT

PGM

DB(DB02)

DB(DB03)

DB(DB01)

Application

Server

DB01

DB02

DB03

Figure 67. Switching Application Servers NOT Implemented within the Program

When you are switching application servers in the program, an application

program can switch to a new application server during execution with the

CONNECT statement. Like the SQLINIT method, a package for the program must

exist on all application servers it accesses, and each logical unit of work must end

before you switch to a different application server. See “Parameters for SQLPREP

EXEC for Single and Multiple User Modes” on page 118 for the options used to

preprocess the program on multiple application servers.

SQLINIT

PGM

DB(DB01)

Application

Server

DB01

DB02

DB03

Figure 68. Switching Application Servers Implemented

How to Switch Servers (DB2 Server for VSE)

Figure 69 on page 304 shows how an application program, indicated by PGM,

accesses three application servers by switching application servers in the program.

The application servers can reside on the same processor as the program or on a

different processor. When you are switching application servers in the program, an

application program can switch to a new application server during execution with

the CONNECT statement. A package for the program must exist on all application

servers it accesses, and each logical unit of work must end before you switch to a

different application server. See “Preprocessing the Program on Multiple

Application Servers” on page 306 for more details.

Chapter 11. Special Topics

303

PGM

Application

Server

DB01

DB02

DB03

Figure 69. Switching Application Servers Implemented - DB2 Server for VSE

Accessing a New Application Server

An DB2 Server for VM application accesses the application server established by

the SQLINIT command when:

v The first CONNECT statement in an application does not contain the TO clause.

v Either a COMMIT RELEASE or ROLLBACK RELEASE statement is executed

and the next statement is not a CONNECT statement with the application server

name specified in the TO clause.

v No CONNECT statement is executed by an application. That is, an implicit

connect is performed.

An DB2 Server for VSE application accesses the default application server when:

v The first CONNECT statement in an application does not contain the TO clause.

v Either a COMMIT RELEASE or ROLLBACK RELEASE statement is executed by

a batch application and the subsequent CONNECT statement does not contain

the TO clause.

v No CONNECT statement is executed by a CICS/VSE application. That is, an

implicit connect is performed.

DB2 Server for VSE

For more information about the defaults that determine the application server

that is accessed, refer to the DB2 Server for VSE System Administration manual.

The application accesses a new application server after executing: An application

accesses a specific application server after executing:

v a CONNECT statement with the application server name specified in the TO

clause.

DB2 Server for VSE

You must enter a CONNECT statement from a batch application after a

COMMIT RELEASE statement or ROLLBACK RELEASE statement to

reestablish the user ID and target application server. Otherwise subsequent

SQL statements are not successful (SQLCODE -563). A null CONNECT

statement is not sufficient.

For DB2 Server for VM to query the user ID and the identity of the application

server to which you are currently connected, as well as the relational database

management system (RDBMS) running the application server, do one of the

304

Application Programming

following from within an application program. For DB2 Server for VSE to query

the user ID and the identity of the application server to which you are currently

connected, enter one of the following from within an application program.

v A null CONNECT statement, which returns the user ID and the identification of

the RDBMS (DB2 Server for VM) and the application server in the SQLCA. Refer

to the discussion of the CONNECT statement in the DB2 Server for VSE & VM

SQL Reference manual for a description of the format and location of the

information that is returned.

For DB2 Server for VSE if a null CONNECT is issued as the first SQL statement

in a batch application, blanks are returned in the SQLCA for the user ID and

application server name and the execution of subsequent SQL statements are not

successful (SQLCODE -563).

v A SELECT statement requesting the USER and CURRENT SERVER, which

returns the user ID and the identification of the application server in the host

variables associated with the USER and CURRENT SERVER special registers.

If you are using DB2 Server for VM, from your terminal, enter:

v An SQLQRY command, which displays the user ID and the identification of the

RDBMS and the application server on the terminal. Refer to the discussion of the

SQLQRY command in the DB2 Server for VSE & VM Database Administration

manual for a description of the format of the information that is returned and

the restrictions on the use of the SQLQRY command.

Illustrating Sample Code

Figure 70 on page 306 shows how an application can take advantage of switching

application servers.

Chapter 11. Special Topics

305

Program In User's Machine

Declarations, (and so forth)

DB_NAME = 'DB01'

EXEC SQL CONNECT TO :DB_NAME

EXEC SQL DECLARE CUR1 CURSOR FOR SELECT . . .

Application

EXEC SQL OPEN CUR1

Server

DO until all rows fetched:

DB01

EXEC SQL FETCH CUR1 INTO

(Use data)

END DO

EXEC SQL CLOSE CUR1

EXEC SQL COMMIT RELEASE

Application

DB_NAME = 'DB02'

Server

EXEC SQL CONNECT TO :DB_NAME

DB02

EXEC SQL DELETE FROM . . . WHERE . . .

EXEC SQL COMMIT RELEASE

DB_NAME = 'DB03"

Application

EXEC SQL CONNECT TO :DB_NAME

Server

EXEC SQL INSERT INTO . . . VALUES . . .

DB03

EXEC SQL COMMIT RELEASE

Figure 70. Pseudocode Illustrating How to Switch Application Servers

In the above example, the application connects to three application servers (DB01,

DB02, and DB03), and performs a series of operations when accessing each one.

When accessing DB01, the program retrieves information from the application

server (with the FETCH statement) and processes the information.

Next, it accesses DB02, and some rows are deleted from a table; then accesses

DB03, and rows are inserted into a table.

Preprocessing the Program on Multiple Application Servers

An application program that allows access to multiple application servers with the

CONNECT statement must exist on every application server that the program is to

access.

The DB2 Server for VSE preprocessors provide the DBNAME parameter to

preprocess a program on different application servers. In addition, the CBND

transaction provides the DBLIST parameter to create a package on different

application servers.

The DB2 Server for VM SQLPREP EXEC provides the option to preprocess a

program on multiple application servers with the DBFile or DBList parameter.

However, an application using either of these parameters is preprocessed on one

application server at a time. Each of the application servers provided in the DBFile

or DBList parameter preprocesses the program separately and consecutively, and

generates a source listing. These source listings are concatenated.

306

Application Programming

When an application that accesses different application servers is being

preprocessed, certain warnings may be issued by the preprocessor. For example, if

TABLE1 exists in DB01, but your application program is preprocessed against

DB02 or DB03, you will receive warning messages that the table does not exist in

those application servers. If your program does not access TABLE1 in DB02 or

DB03, these messages can be ignored; however, if TABLE1 will be accessed in

either DB02 or DB03, you must create TABLE1 in the accessed application server.

You should repreprocess the program on the application servers that you updated

before executing the program. If you are using the preprocessing option

CTOKEN=NO, you only need to preprocess the application program on one

application server. If you specify CTOKEN=YES, you must repreprocess on all

application servers that the program accesses to get the same timestamp.

During execution, the table being referenced in an SQL statement may reside in the

currently accessed application server or in another application server. In fact, a

table of the same name, but with different attributes, may be in the application

server. The database manager issues a warning message that there are

inconsistencies, but preprocessing will continue. The statement causing the

warning remains in the package, and will only cause an application failure if it is

referenced at run time. Conditions that will generate a warning and the

corresponding SQLCODE include:

v Column column was not found in table owner.table. (SQLCODE = +205 and

SQLSTATE='01533')

v Incompatible data types were found in an expression or compare operation.

(SQLCODE = +401 and SQLSTATE='01578')

v The string representation of a date/time value has invalid syntax. (SQLCODE =

+180 and SQLSTATE='01572')

For more information on preprocessing against unlike DB2 Server for VM

application servers, refer to “Preprocessing the Program” on page 114.

Condition Handling with LE/VSE (DB2 Server for VSE)

The DB2 Server for VSE environment is sensitive to errors or conditions. A failing

SQL transaction or application can potentially leave a DB2 Server for VSE database

in an inconsistent state. For this reason, it is essential that DB2 Server for VSE

knows about the failure of a transaction or application that has been updating a

database so that it can perform database rollback.

When a user runs an application with the TRAP(ON) run-time option of LE/VSE

and the DB2 Server for VSE application is running in Single User Mode, LE/VSE

and DB2 Server for VSE keep track of calls to and returns from the database. If a

program interrupt or abend occurs when the application is running, the LE/VSE

condition manager is informed whether the problem occurred in the application or

in the database manager. If the program interrupt or abend occurs in the database

manager, the LE/VSE condition handler passes the condition back to DB2 Server

for VSE.

If a program interrupt or abend occurs in the application outside the database

manager, the LE/VSE condition manager will perform its own condition handling

actions. If the condition manager gets control then the user must do one of the

following:

v Resolve the error completely so that the application can continue.

Chapter 11. Special Topics

307

Make sure that the application terminates abnormally by using the

ABTERMENC(ABEND) run-time option of LE/VSE to transform all abnormal

terminations into operating system abends in order to cause DB2 Server for VSE

to do the necessary recovery processing when the DB2 Server for VSE server is

warm started.

Note: The following methods are available for specifying any LE/VSE run-time

options, including ABTERMENC(ABEND):

1. As an installation wide default through the CEEDOPT assembler

language source file.

2. In the assembler user exit routine CEEBXITA.

3. As an application default through the CEEUOPT assembler language

source file. CEEUOPT is assembled into an object module which is

linked with the application program.

4. In JCL through the PARM parameter of the JCL EXEC statement.

5. In PL/I source code through the PLIXOPT string.

See the IBM Language Environment for VSE/ESA Programming Guide for more

details.

Provide a modified run-time assembler user exit (CEEBXITA) that transforms all

abnormal terminations into operating system abends. The assembler user exit

should check the return code and reason code or the CEEAUE_ABTERM bit,

and request an abend by setting the CEEAUE_ABND flag to ON, if appropriate.

Note: CEEBXITA assembler user exit is intended for use by the application

programmer. It is not intended for DB2 Server for VSE use. See the IBM

Language Environment for VSE/ESA Programming Guide for more details.

308

Application Programming

Appendix A. Using SQL in Assembler Language

Using ARIS6ASD, an Assembler Language Sample

Embedding SQL Statements

316

Program (DB2 Server for VSE Only)

310

Using the INCLUDE Statement

316

Using ARIS6ASC, an Assembler Language Sample

Using Host Variables in SQL Statements . . . 317

Program (DB2 Server for VM Only)

310

Using DBCS Characters in Assembler Language

317

Acquiring the SQLDSECT Area

310

Handling SQL Errors

317

Imposing Usage Restrictions on the SQLDSECT

Using Dynamic SQL Statements in Assembler

Area

312

Language

318

Rules for Using SQL Statements in Assembler

Defining DB2 Server for VSE & VM Data Types for

Language

314

Assembler Language

319

Identifying Rules for Case

314

Using Reentrant Assembler Language Programs

320

Declaring Host Variables

314

Using Stored Procedures

326

309

Using ARIS6ASD, an Assembler Language Sample Program (DB2

Server for VSE Only)

ARIS6ASD is an assembler language sample program for VSE systems that is

shipped with the DB2 Server for VSE product. It resides on the production disk for

the base product. You may find it useful to print this sample program before going

through this appendix as the hard copy will provide an illustration for many of the

topics discussed here.

Note, for example, how the program satisfies the requirements of the application

prolog and epilog. Near the beginning of the program, all the host variables are

declared, the SQLDSECT area is acquired (and set to zero), and error handling is

defined. Near the logical end of the program, the database changes are rolled back,

to assure that the database remains consistent for each use of the sample program.

(For your own applications, of course, you will enter a COMMIT statement.)

The DS and DC statements for the host variables were determined by referring to

Table 35 on page 319, which shows the assembler representation for each of the

DB2 Server for VSE data types supported by assembler programs. When you are

coding your own applications, you must obtain the data types of the columns that

your host variables interact with. This can be done by querying the catalog tables.

These tables are described in the DB2 Server for VSE & VM SQL Reference manual.

Using ARIS6ASC, an Assembler Language Sample Program (DB2

Server for VM Only)

ARIS6ASC is an assembler language sample program for VM systems that is

shipped with the DB2 Server for VM product. It resides on the production disk for