Thursday, March 08, 2007

Review topic: DB2, binds

A mainframe program accessing DB2 will have associated with it a DBRM, or database request module, which describes the different accesses to the DB2 tables needed in the program. The bind procdure for the program validates your security authorization to use the DB2 commands and functions and defines the physical access methods. The bind attaches the program to a package, which can belong to a collection that in turn belongs to a plan. Or a bind can attach directly to a plan.

A plan can have several isolation levels: RR (Repeatable Read), RS (Read Stability), CS (cursor stability -- often the best choice), and UR (uncommitted read, OK for tables not updated often). The bind parameters can include ACQUIRE(ALLOCATE), ACQUIRE(USE), RELEASE(DEALLOCATE), RELEASE(COMMIT). Allocate-related parameters are recommended for batch; use or commit related are recommended for on-line.

Table lock modes are IS, IX, S, U, SIX, X (S means share, I means intent, X means exclusive); page and row locks may be S, U, or X. The bind parameters that affect these locs are RR (repeatable read), RS (read stability), CS (cursor stability), and UR (uncomitted read).

DB2 recognizes these kinds of joins: merge, nested, and hybrid. Also hash join. A heap table is a hashed index.

A DB2 trigger can execute DB2 "procedures" when certain conditions are encountered, such as overdrafting on a checking account.

DB2 can never update more than one primary key at a time.

A host variable is the COBOL field in which data will be put after a SELECT.

Note the CASCADE and RESTRICT options in the ON DELETE clause of a foreign key deftinition.

Here is a geekinterviews short answer quiz on mainframe DB2, at this link.

1 comment: