kfogarty.com – Database Consultants

Identity columns were available in DB2 Version 7 (but came into being after the V6 code refresh). The identity property is applied to a column using the IDENTITY parameter. A column defined with the identity property will cause DB2 to automatically generate a sequential value for that column, when a row is added to the table.

For example, identity columns might be used to generate primary key values.

Using identity columns helps to avoid some of the concurrency and performance problems that can occur when application programs are used to populate sequential values for a “counter” column.

When inserting data into a table that uses an identity column, the program or user will not provide a value for the identity column. Instead, DB2 automatically generates the appropriate value to be inserted.

Notes

• Only one identity column can be defined per DB2 table.
• The data type of the column must be SMALLINT, INTEGER or DECIMAL.

An example creating a table with an identity column follows:

CREATE TABLE SAMPLE
(ID_COL INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
START WITH 10
INCREMENT BY 1
...);

In this example, the identity column is named ID_COL. The initial value stored in the table is 10, and each insert will add 1 to the last value. After five inserts, the values in the identity column will be 10,11,12,13,14 and 15.

The GENERATED parameter indicates how DB2 generates values for the column. GENERATED must be specified if the column is to be considered an identity column or the data type of the column is ROWID. This means that DB2 must be permitted to generate values for all identity columns. There are two options for the GENERATED parameter: ALWAYS and BY DEFAULT.

• GENERATED ALWAYS indicates that DB2 will always generate a value for the column when a row is inserted into the table. ALWAYS is specified for identity columns unless data propagation is being used.
• GENERATED BY DEFAULT indicates that DB2 will generate a value for the column when a row is inserted into the table unless a value is specified. If an explicit value is to be inserted into an identity column GENERATED BY DEFAULT must be specified.

When the maximum value of the column is reached, there are options that need to be considered to allow insertion to continue.

The CYCLE keyword will cause DB2 to begin generating values from the minimum value all over again. This can generate duplicate values in the column, and should only be used when unique values in the identity column are not a requirement.

To ensure uniqueness in an identity column, a unique index needs to be created on the identity column. The IDENTITY property alone does not guarantee uniqueness.

Without identity columns, an application program can implement similar functionality, (there is a performance consideration to account for, as an application program cannot do this as efficiently as DB2).

A common technique is to maintain a one-row table that contains the sequence number. Each transaction locks that table, increments the number, and then commits the change to unlock the table. Only one transaction at a time can increment the sequence number, which could lead to a bottleneck.

Experience of Identity Columns at a Client Site

At one client site, identity columns were not used because of operational restrictions and problems in managing the different environments.

On request of the development teams, data would be copied from PROD(uction) to DEV(elopment) and ACC(eptance) using fast load/DSN1COPY utilities instead of using SQL inserts to refresh some databases, and the data propagation mechanism needed to be taken into account. (See general notes above).

Other site standards meant that all DB2 tables had to have a natural key as a primary key, not an “artificial” (generated by DB2) key. Most applications at the client site managed this by keeping a “highest used key” table. (See notes above).

In DB2 terms, a plan is an executable module containing the access path logic produced by the DB2 optimizer.

A plan can be composed of one or more DBRMs and packages.

Before a DB2 for z/OS program (with static SQL) can be run, it must have a plan associated with it.

Plans are created by the DB2 BIND command. The plan is stored in the DB2 directory and accessed when its program is run. Information about the plan is stored in the DB2 catalog.

A package is a single, bound DBRM with optimized access paths.

By using packages, the table access logic is “packaged” at a lower level of granularity than a plan — at the package (or program) level.

To execute a package, you first must include it in the package list of a plan (usually, there are some exceptions, such as for triggers and user-defined functions). In general, packages are not directly executed, they are only indirectly executed when the plan in which they are contained executes (UDFs and triggers are exceptions to this rule).

A plan can consist of one or more DBRMs, one or more packages or, a combination of packages and DBRMs.

Structured Query Language (SQL) is a language that can be used for data definition, data manipulation, database queries, and authorization administration for all database functions. SQL is a set-processor, non-procedural, and non-navigational language and consists of 3 components:

1. Data Manipulation Language (DML) – statements that are used to retrieve, change, and delete data (i.e. SELECT, UPDATE, INSERT, DELETE).
2. Data Definition Language (DDL) – statements used to define DB2 objects such as databases and tables (i.e. CREATE, ALTER, DROP).
3. Data Control Language (DCL) – statements used to administer security for DB2 objects (i.e. GRANT, REVOKE).

SQL can be used in several modes which are described below:
Dynamic (Interactive) SQL: SQL statements that can be coded and executed interactively from terminals using various tools provided by the DBMS (For example, SPUFI and QMF).

Static (Embedded) SQL

A static SQL statement is an SQL statement that can be hardcoded in an application program at development time because information about the structure and objects (such as tables, column, and data types) with which it is intended to interact with is known in advance.

Since the details of a static SQL statement are known at development time, the work of analyzing the statement and selecting the optimum data access plan for executing the statement is performed by the DB2 optimizer as part of the development process. Because their operational form is stored in the database (as a package) and does not have to be generated at application runtime, static SQL statements execute quickly.

The downside to this approach is that all static SQL statements must be prepared (in other words, their access plans must be generated and stored in the database) before they can be executed. Furthermore, static SQL statements cannot be altered at runtime, and each application that uses static SQL must bind its operational packages to every database with which the application will interact. Additionally, because static SQL applications require prior knowledge of database objects, changes made to those objects after an application has been developed can produce undesirable results.

The following are examples of static SQL statements:

• SELECT COUNT(*) FROM employee
• UPDATE employee SET lastname = ‘Jones’ WHERE empid = ‘001′
• SELECT MAX(salary), MIN(salary) INTO :MaxSalary, :MinSalary FROM employee

Generally, static SQL statements are well suited for high-performance applications that execute predefined operations against a known set of database objects.

Dynamic SQL

Although static SQL statements are relatively easy to incorporate into an application, their use is somewhat limited because their format must be known in advance.

Dynamic SQL statements, offer more flexibility because they can be constructed at application runtime. Information about a dynamic SQL statement’s structure and the objects with which it plans to interact does not need to be known in advance, and because dynamic SQL statements do not have a precoded, fixed format, the data objects they reference can change without affecting the statement (provided that objects referenced by the statement are not deleted).

Even though dynamic SQL statements are generally more flexible than static SQL embedded SQL programming statements, they are usually more complicated to incorporate into an application. And because the work of analyzing the statement to select the best access plan is performed at application runtime (again, by the DB2 optimizer), dynamic SQL statements can take longer to execute than their static SQL counterparts. (Because dynamic SQL statements can take advantage of the database statistics available at application runtime, there are some cases in which a dynamic SQL statement executes faster than an equivalent static SQL statement, but those are the exception and not the norm.)

The following are examples of dynamic SQL statements:

• SELECT COUNT(*) FROM ?
• INSERT INTO EMPLOYEES VALUES (?, ?)
• DELETE FROM DEPARTMENT WHERE DEPTID = ?

Generally, dynamic SQL statements are well suited for applications that interact with a rapidly changing database or that allow users to define and execute ad-hoc queries.

The RUNSTATS utility collects statistics about DB2 objects.

These statistics can be stored in the DB2 catalog and are used during the bind process to choose the path in accessing data. If you never use RUNSTATS and subsequently rebind your packages or plans, DB2 will not have the information it needs to choose the most efficient access path. This can result in unnecessary I/O operations and excessive processor consumption.

RUNSTATS should be run against each table and its associated indexes at least once. How often the utility is rerun depends on how current the data stored in the catalog needs to be. If data characteristics of the table vary significantly over time, you should keep the catalog current with those changes. RUNSTATS is most beneficial for the following:

• Table spaces that contain frequently accessed tables.
• Tables involved in a sort.
• Tables with many rows.
• Tables against which SELECT statements having many search arguments are performed.

It is critical to execute RUNSTATS at these times:

• After a table and indexes have been created and the data loaded.
• After a tablespace or index has been reorganized and statistics have changed significantly.
• After heavy insert, update, and delete activity.
• Before binding and rebinding plans and packages.
• Before monitoring the catalog tables.

It is recommended to collect statistics during LOAD and REBUILD INDEX processing as well as during a tablespace reorganization.

See Also:

READDBRM is a REXX exec which will read a DB2 DBRM and extract allsorts of information from the file, such as the account of the user who submitted the build job that created the DBRM, the time and date it was made, pre-compiler options used,decimal precision and much, much more. This information is written to the user’s screen, so no ISPF panels are used at all.

The READDBRM REXX can be downloaded by clicking here.

Two commands, similar in function, but what’s the difference between them, and when should you use BIND, but not REBIND, and vice-versa?
Invalid Plans and Packages
BIND PLAN

The BIND PLAN command builds an application plan. All DB2 programs require an application plan to allocate DB2 resources and support SQL requests made at run time. In general, BIND is used when the source code has changed. If the source code has changed, then you must precompile and compile the program. The new load module will have a new timestamp/token. To move that same consistency token into DB2, you must do a BIND with the new DBRM in the DBRM library as input.

BIND PACKAGE

The BIND PACKAGE command builds an application package. DB2 records the description of the package in the catalog tables and saves the prepared package in the directory.

REBIND PLAN

The REBIND PLAN command rebinds an application plan when when changes are made that affect the plan, but do not change the SQL statements in the programs. In general, REBIND is used when something other than the source code has changed.

For example, REBIND PLAN can be used when authorizations are changed, or if a new index has been created for the plan, or if the RUNSTATS utility has been run on tables accessed by the plan.

If the rebind is successful, the process prepares an application plan and updates its description in the SYSIBM.SYSPLAN catalog table.

REBIND PLAN is generally faster and more economical than BIND PLAN. But if the SQL statements have been changed, or the program has been recompiled, the BIND PLAN command should be used with ACTION(REPLACE).

REBIND PACKAGE

The REBIND PACKAGE command rebinds an application package when you make changes that affect the package, but have not changed the SQL statements in the program. For example, you can use REBIND PACKAGE when you change the authorizations, create a new index for the package, or use RUNSTATS.

When the REBIND PACKAGE(*) command is issued, trigger packages will not be affected.

REBIND PACKAGE is generally faster and more economical than BIND PACKAGE. You should use BIND PACKAGE with the ACTION(REPLACE) option under the following conditions:

• When you change the SQL statements
• When you recompile the program
• You previously ran BIND PACKAGE with the SQLERROR(CONTINUE) option

NOTES

The BIND command cannot be issued when the following tablespaces are being reorganised: SYSDBAUT, SYSDBASE, SYSGPAUT, SYSPKAGE, SYSPLAN, SYSSTATS, SYSUSER and SYSVIEWS.

With a REBIND, good results are as likely to occur as bad results.
It is possible to limit potential negative impact of a rebind command by rebinding the affected package into a practice collection with EXPLAIN(YES). This package can then be freed, and the plan_table analysed to confirm the access path has either improved or remained the same.

Remember: not all improvements are reflected in the plan_table. For example, the plan_table cannot show improved index screening with list prefetch or improved lock avoidance that comes from changing REBIND parameters.

Even if the access path hasn’t changed, it is certain that there is no new negative access path, and it might be an idea to rebind into production to take advantage of the hidden, subtle improvements.

Other References:
http://db2mag.com/db_area/archives/2002/q1/programmers.shtml

UTLDDFIP is a REXX exec that can be called to translate a DB2 LUWID from HEX to Decimal and return the TCP/IP address held within the LUWID.

The exec can be viewed by clicking here.

Global trace information is used to service DB2. Global trace records information regarding entries and exits from internal DB2 modules as well as other information about DB2 internals. It is not accessible through tools that monitor DB2 performance. Most sites will never need to use the DB2 global trace. You should avoid it unless an IBM representative requests that your shop initiate it.

Estimated overhead: IBM states that the global trace can add 100 percent CPU overhead to your DB2 subsystem.

The audit trace is useful for installations that must meticulously track specific types of DB2 events. Not every shop needs the audit trace. However, those wanting to audit by authid, specific table accesses, and other DB2 events will find the audit trace invaluable. Eight categories of audit information are provided:

• All instances in which an authorization failure occurs, for example, if USER1 attempts to SELECT information from a table for which he or she has not been granted the appropriate authority
• All executions of the DB2 data control language GRANT and REVOKE statements
• Every DDL statement issued for specific tables created by specifying AUDIT CHANGES or AUDIT ALL
• The first DELETE, INSERT, or UPDATE for an audited table
• The first SELECT for only the tables created specifying AUDIT ALL
• DML statements encountered by DB2 when binding
• All authid changes resulting from execution of the SET CURRENT SQLID statement
• All execution of DB2 utilities

This type of data is often required of critical DB2 applications housing sensitive data, such as payroll or billing applications.
Estimated overhead: Approximately 5 percent CPU overhead per transaction is added when all audit trace classes are started.

Using Audit Trace

If the site standard is to create tables with the AUDIT parameter, all audit trace classes should be started. If there are no audited tables, use the DSNZPARMs at DB2 startup to start only audit classes 1, 2 and 7 to audit authorization failures, DCL and utility execution. Aside from these types of processing, audit classes 1, 2 and 7 add no additional overhead. As must transactions do not result in authorization failures or issue GRANT, REVOKE or utilities, running these traces is cost-effective.