Pathmaker Programming Guide

Generating NonStop SQL Statements
Creating Services and Servers
4–64 067868 Tandem Computers Incorporated
An area for you to specify an access path. An access path is required only for
SELECT and FETCH operations. For a SELECT operation, the access path you
specify must be unique. See “Defining Access Paths for NonStop SQL Tables” in
Section 2 for more information.
In addition, a default name of a Working-Storage host variable is provided (Before-
Image Host Variable). This host variable is used for holding a before-image of the row
to be updated. The before-image is used in the code the Pathmaker product generates
to perform concurrency checking prior to an UPDATE or DELETE operation. This
code compares the before-image of the row to be updated with the same row in the
database to make sure that no intervening updates have occurred. You can change
this name, as long as the new name does not conflict with any other variable name in
the service.
Operation Attributes
Screen
On the Operation Attributes screen, you specify additional information that the
Pathmaker product uses to generate SQL database access statements. On the first page
of this screen, you can specify further information about a SELECT or FETCH
operation, such as the lock mode and access type (browse, stable, or repeatable) . On
the second page of this screen, you can specify further information about an UPDATE,
DELETE, or INSERT operation, such as the access type (stable or repeatable) and
whether a concurrency check should be performed. Refer to the Pathmaker Reference
Manual for details about the Operation Attributes screen.
The following points apply to the Pathmaker generated SQL operations:
UPDATE, INSERT, AND DELETE are not allowed on a shorthand view.
On a protection view, UPDATE, INSERT, AND DELETE are allowed only if all of
the primary key columns (including SYSKEY if necessary) are included in the
view.
If you designate a nonunique access path for a FETCH operation on a shorthand
view, there must also be a unique access path on that shorthand view.
The locking attribute “access type” affects only modification operations (UPDATE,
INSERT, AND DELETE) on unaudited tables. If a table is audited, TMF holds an
exclusive lock on the modified row until the transaction ends, regardless of what
value you have specified in the access type field.
Usage Considerations for
UPDATE Operations
The generated code for an UPDATE operation resides in a paragraph named
SQL-UPDATE. The operation updates all columns (except the primary key) of the
target row with values from the host variables.
To use this paragraph, initialize the host variables, then perform the paragraph of the
SQL I/O Section that contains the operation. Initialize columns that are to have a null
value by setting the null indicator. Initialize all other columns with the appropriate
column value.
An UPDATE operation includes a host variable for the before-image of the row. The
generated code will use the before-image host variable if you request a concurrency
check. Initialize the before-image with the original contents of the row before
performing the UPDATE operation.