DB2 STANDARDS ,TIPS & GUIDELINES

• Introduction
• SQL General Information
• Using SQL
• SQL Error Handling
• DB2 Tools & Environment
• Design Considerations
• Focus Considerations
• DB2 Program Development Procedures
• Internal DB2 Processing Considerations

A. INTRODUCTION

In order to standardize DB2 development and improve maintainability, sample DB2 programs and program
shells have been created for both online and batch environments. Before beginning a new program, it
would be helpful to review both the shell and the sample program. Ideally, either the shell or the sample
program will serve as a starting point for creating new programs. The shells and sample programs are found
in DS.TEST.PANVALET as follows:

Online:

Shell program DB2SHELLC

Sample program DB2SAMPLEC

Error routine DB2SQLERRC

Common CICS program messages DB2WSMSG

CICS error display program DB2019DB

Batch:

Shell program DB2SHELLB

Sample program DB2SAMPLEB

Error routine DB2SQLERRB

Other Components:

DB2 error working storage DB2WSERROR

Edit date routine DB2EDATE

DB2/edit date working storage DB2WS

The shells, sample programs, and common modules have been made available to give application
programmers a starting point when creating DB2 programs. They were meant to enhance, not replace the
following standards. Please refer to the following sections for more detail on programming with DB2. If
you still can't find an answer, contact the DBA team for assistance.

Prepared By P.Srinath Reddy 26th march 1999 1/17

 DB2 STANDARDS ,TIPS & GUIDELINES

Sample tables and a sample application program that accesses these tables were also shipped with DB2.
Copies of the sample program in several different languages can be found in Appendix B of the DB2
Application Programming and SQL Guide. The programs are also stored in library 'DS.PANVALET'
accessible through ISPF. The Cobol batch program is 'DB2COBOL'.

B. SQL GENERAL INFORMATION

SQL is the language that will be used for all data definition, data manipulation, queries, and authorization
administration for all database functions. SQL is a set-processor, non-procedural, and non-navigational
language and consists of 3 components:

•Data Manipulation Language (DML) - statements that are used to retrieve,

change, and delete data
(i.e. SELECT, UPDATE, INSERT, DELETE).
• Data Definition Language (DDL) - statements used to define DB2 objects
such as databases and tables (i.e. CREATE, ALTER, DROP).
• 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. In our environment we will be using SPUFI and FOCUS to execute SQL statements
interactively.

Static (Embedded) SQL

SQL statements that are embedded in programs using a language that is compiled and link-edited. SQL can
be embedded in CICS, VS Cobol, COBOL II, C, PL/1, and Assembler languages. Using static SQL is much
more efficient than interactive or dynamic SQL.

Dynamic SQL in Application Programs

SQL statements that are prepared and executed within a program while the program is executing. The SQL
source is contained in host language variables rather than being coded into the application program. The
difference between dynamic and static SQL is the time at which the BIND occurs. In static mode, the bind
is done prior to the execution and is stored in a PLAN. In dynamic mode, the BIND occurs at execution
time. This results in additional overhead which degrades performance. This form of dynamic SQL will
NOT be used in our environment.

FOCUS/DB2 Interface (Read-only)

FOCUS, a 4th generation language, can access a multitude of file structures including DB2 tables, VSAM,
and sequential files. A report (Table) request issued against a DB2 table passes the request to the interface
which prepares SQL commands to retrieve the requested data. DB2 processes the request, returns the data
(or error code) to the interface which then sends the data to the Report Writer component of FOCUS for
completion of processing. Trace listings and access to the DB2 Explain utility are available through the
interface. The FOCUS/DB2 interface can be utilized interactively or in batch, although only batch
execution of FOCUS is currently available in the Information Center environment.

Prepared By P.Srinath Reddy 26th march 1999 2/17

 DB2 STANDARDS ,TIPS & GUIDELINES

C. USING SQL

General Standards & Guidelines

• Application Development Standards & Guidelines

• Batch Programming Standards & Guidelines
• Online Programming Standards & Guidelines

I. General Standards & Guidelines

Pretest all embedded SQL

Before embedding SQL in an application program, you should test it using SPUFI. This reduces the amount
of program testing by ensuring that all SQL code is syntactically correct and efficient before it is placed in
an application program.

Never use SELECT *

As a general rule, a query should never ask DB2 for anything more than is required to satisfy the desired
task. For maximum flexibility and efficiency, each query should access only the columns needed for the
function that will be performed.

Singleton SELECT versus the cursor

To return a single row, an application program can use a cursor or a singleton SELECT. A cursor requires
an OPEN, FETCH, and CLOSE to retrieve one row, whereas a singleton SELECT requires only SELECT...
INTO. Usually, the singleton SELECT outperforms the cursor. When the selected row must be updated
after it is retrieved, however, using a cursor with the FOR UPDATE OF clause is recommended over a
singleton SELECT. The FOR UPDATE OF clause ensures the integrity of the data in the row because it
causes DB2 to hold an exclusive lock on the page containing the row to be updated. The singleton select
provides no such locking.

Use FOR FETCH ONLY

When a SELECT statement will be used only for retrieval, use the FOR FETCH ONLY clause.

Avoid using DISTINCT

The DISTINCT verb removes duplicate rows form an answer set. If duplicates will not cause a problem, do
not code distinct, because it adds overhead by invoking a sort to remove the duplicates.

Limit the data selected

Return the minimum number of columns and rows needed by your application program by making efficient
use of the WHERE (SQL predicate) clause. It is almost always more efficient to allow DB2 to use the
WHERE clause to limit the data returned.

Code predicates on indexed columns

DB2 usually performs more efficiently when it can satisfy a request using an existing index rather than no
index. Design all SQL statements to take advantage of indexes.

Multicolumn indexes
If a table has only multicolumn indexes, try to specify the high-level column in the WHERE clause of your
query. This results in an index scan with at least one matching column.

Use ORDER BY only when sequence is important

Code the ORDER BY clause when the sequence of rows being returned is important. Order only those
columns that are absolutely necessary in order to improve efficiency.

Use equivalent data types

Prepared By P.Srinath Reddy 26th march 1999 3/17

 DB2 STANDARDS ,TIPS & GUIDELINES

Use the same data types and lengths when comparing column values to host variables or literals. This
eliminates the need for data conversion and allows for the use of an index. For example, comparing a
column defined as CHAR(6) to a field which is CHAR(4) or CHAR(7) will cause data conversion and
should be avoided at all costs. The easiest way to ensure datatype consistency is to use the DCLGEN fields
whenever possible.

Use BETWEEN rather than <= and >=

BETWEEN allows the optimizer to select a more efficient access path.

Use IN instead of LIKE

If you know that only a certain number of occurrences exist, using IN with the specific list is more efficient
than using LIKE. The functionality of LIKE can be imitated using a range of values. For example, if you
want to retrieve all employees with a last name starting with "M," use BETWEEN 'maaaaaaaaaaaaaaa' and
'mzzzzzzzzzzzzzzz' instead of LIKE 'm%'

Avoid using NOT (except with EXISTS)

Not should only be used as an alternative to very complex predicates.

Code the most restrictive predicate first

When you code predicates in your select statement, place the predicate that will eliminate the greatest
number of rows first.

Do not use arithmetic expressions in a predicate

DB2 will not use an index for a column when the column is in a predicate that includes arithmetic.Perform
calculations before the SQL statement, then use the result in the query.

Use Date and Time arithmetic with care

DB2 provides the capability to add and subtract DATE, TIME, and TIMESTAMP columns. Keep in mind
the following rules:

•
Both operands must be of the same data type (ie, both dates or both
times)
Example: CURRENT DATE - 1 MONTH or CURRENT DATE + 1 DAY

•
If one operand is a timestamp, the other operand can be a time or a
date, but not a timestamp.
Example: TIMESTAMP + 1 DAY or TIMESTAMP - 24 HOURS

• Date durations are expressed as DECIMAL(8,0).

The valid date durations are: DAY, MONTH, and YEAR and their plural equivalents.

• Time durations are expressed as DECIMAL(6,0).

The valid time durations are:HOUR, MINUTE, SECOND, and MICROSECOND and
their plural equivalents.

Specify the number of rows to be returned

When you are coding a cursor to fetch a predictable number of rows, consider specifying the number of
rows to be retrieved in the OPTIMIZE FOR n ROWS clause of the CURSOR. This gives DB2 the
opportunity to select the optimal access path for the statement based on actual use.

Try to sort only on indexed columns

When using ORDER BY, GROUP BY, DISTINCT, and UNION, it is best to use only indexed columns.

Joins instead of subqueries

Prepared By P.Srinath Reddy 26th march 1999 4/17

 DB2 STANDARDS ,TIPS & GUIDELINES

Joins will give the DB2 optimizer more options for data access than a subquery.

Minimize number of tables used in a join

As a general rule of thumb, avoid using more than 3 tables in a join.

Explicitly specify all column names in any SQL statement.

Avoid using the substring function in an SQL predicate.

II. Application Development Standards & Guidelines

Code modular DB2 programs

You should design DB2 programs to be modular. One program should accomplish a single, well-defined
task. If multiple tasks need to be executed, structure the programs so that tasks can be strung together by
having the programs call one another. This is preferable to a single, large program that accomplishes many
tasks for two reasons. One, single tasks in separate programs make the programs easier to understand and
maintain. Two, if each task can be executed either alone or with other tasks, isolating the tasks in a program
enables easier execution of any single task or list of tasks.

Avoid host structures

Avoid SELECTing or FETCHing INTO a group-level host variable structure.

Explicitly code literals

When possible, code literals explicitly in the SQL statement rather than moving the literals to host variables
and then processing the SQL statement using the host variables.

Do not use dynamic SQL in application programs.

DDL and DCL in application programs

Do not use data definition (DDL) and data control (DCL) SQL statements in application programs.
CICS/DB2 programs coded in Cobol II should use command-level CICS rather than macro.

Use of DCLGEN
Create the table layout ('include' member) by executing a DCLGEN in SPUFI. The DCLGEN includes a
DECLARE statement and a cobol layout for the table.

Edit the DCLGEN

Remove the qualifier from the table name (eg. testdba or proddba). The qualifier will be specified when the
plan is bound. Optionally, the cobol field names can be prefixed with the DCLGEN name. This might
assist the application programmer when dealing with the same field from different tables. (EX: account-nbr
becomes caacctt-account-nbr in the caacctt DCLGEN member and glglent-account-nbr in the glglent
DCLGEN member)

Move the member from the default library to DS.TEST.PANVALET and use the standard
'++INCLUDE' to bring the definition into your program.
(Do not use the 'EXEC SQL INCLUDE' statement as seen in some sample programs.)

Do not use qualifiers in application programmers

After executing the DCLGEN for a table or view, modify the table/view names by removing the qualifier
(usually 'TESTDBA'). Use only unqualified table and view names in an application program. This will
facilitate an easier migration of programs from test to production since the bind will provide the appropriate
qualifier.

Use SQL built-in functions

Prepared By P.Srinath Reddy 26th march 1999 5/17

 DB2 STANDARDS ,TIPS & GUIDELINES

It is more efficient to use the SQL built-in functions (AVG, COUNT, MAX, MIN, SUM) than the
application program perform these functions (before using these, be sure to zero the variable you select
into and check for SQLCODE 0, -305, or other).

Avoid SQL scalar functions

Avoid the use of the SQL Scalar functions used for data type conversions, character string manipulation,
and date/time conversions (i.e. INTEGER, DECIMAL, HEX, SUBST, etc.).

Use CS instead of RR isolation level

Do not specify repeatable read {isolation (RR)} RR will force DB2 to lock every page accessed and to hold
the lock until a COMMIT is issued. Specify cursor stability {isolation (CS)} where possible.

Use of COMP and COMP-3 fields

When updating or inserting COMP or COMP-3 defined columns, use the DCLGEN fields for the values.
Using your own defined working storage variables is unpredictable.

DB2 date, time, and timestamp

Use current date, time, or timestamp whenever possible instead of reformatting an operating system date
into DB2 format.

III. Batch Programming Standards & Guidelines

Use LOCK TABLE with caution

As a general rule, use the LOCK TABLE only after discussing its implications with your DBA staff.
LOCK TABLE can significantly decrease an applications processing time by eliminating page locks, but
will also make the tablespace unavailable to any other access. It locks ALL tables in a Tablespace. The
locks are held until a COMMIT or DEALLOCATE, and all users are locked out of the tablespace.

Periodically COMMIT WORK in batch update programs

Any batch program that issues more than 500 updates is a candidate for COMMIT processing. COMMIT
does not flush data from the DB2 bufferpool and physically apply the data to the table. It will, however,
ensure that all modifications have been physically applied to the DB2 log, thereby ensuring data integrity
and recoverability.

COMMIT should be issued at the end of each logical unit of work

(Commit early and commit often). This will free all tablespace and page locks caused by the program, but
will also lose cursor positioning unless thecursor has been declared using the WITH HOLD option.

Make programs restartable

In time-critical applications, DB2 batch programs that modify table data should be restartable in case there
is a system error.

Hold cursor rather than reposition

If COMMITs are coded in a program that updates data using cursors, use the WITH HOLD option with the
cursor.

Prepared By P.Srinath Reddy 26th march 1999 6/17

 DB2 STANDARDS ,TIPS & GUIDELINES

About Concurrency
Typically, batch programs are executed in the "batch window" which is a given time frame where online
applications will not be accessing the tables being updated. The LOCK TABLE statement would frequently
be used in the batch window since it provides the least amount of DB2 locking overhead. Care must be
taken as indicated above that online programs will not be attempting to utilize this table space, or that
concurrent batch activity is not being processed.

DB2 provides the ability to execute batch table updates in concurrence with CICS or other batch jobs.
However, there is a performance cost for such an activity. Either the job is quickly run or frequent
COMMIT commands would be needed (see following section).

NOTE: This concurrency feature is quite attractive, and makes batch a better alternative to background
CICS processing in most cases.

About COMMITting
It is important to issue COMMIT commands in batch programs for two reasons; it will improve recovery
time by reducing the amount of work that has to backed out and redone in the case of an abnormal
termination, and it will free locks used by the batch job allowing more concurrency.

A checkpoint/restart routine must be established for batch programs which will store all relevant
information needed to restart the program in a separate DB2 table followed by a COMMIT call. When the
program runs or reruns, an initialization routine will read the DB2 restart table to obtain a starting point. It
will be your responsibility to recover any non-DB2 resources that are updated by the program.

The frequency at which checkpoints should be taken will vary from application to application. There are a
number of considerations for selecting the commit frequency. If the batch program is accessing tables that
are also being accessed by online transactions, you should checkpoint very frequently. If the data accessed
by the batch program is not used concurrently by online transactions, the checkpoint interval should be
such that the restart time is acceptable.

Checkpoint/Restart
Once a COMMIT is issued in the batch program, the program must be written to support a restart at the
point of the COMMIT. This would include repositioning the cursor, repositioning in an input transaction
file, and handling the output files, including reports, so that there will not be any lost data.

It is generally advisable not to write to a non-DB2 file at the same time you are updating a DB2 database. If
an output file must be written DB2 table(s) could be used to contain pertinent information for a later
process. If the output file is too large for DB2 then the program must insure that all data information is
written to the file at the time of the COMMIT (externalize the output records). This can be accomplished
through the MVS checkpoint/restart facility, or by closing and then reopening the files (see Section H for
details and examples of checkpoint/restart).

IV. Online Programming Standards & Guidelines

CICS DB2 ERROR ROUTINE

All CICS DB2 programs should use the standard SQL Error routine found on the Panvalet include member
DB2SQLERRC. The routine uses the sqlcode returned from the most current sql call and calls the program
DSNTIAR. This will return a statement about the error. This information, along with other program
information will be passed to program DB2019DB. This program will then display a screen containing the
passed error information.

Prepared By P.Srinath Reddy 26th march 1999 7/17

 DB2 STANDARDS ,TIPS & GUIDELINES

Steps to follow to use the error routine:

• The Panvalet include DB2WSERROR must be in your working storage

section.

• The include DB2SQLERR should be at the end of the program (it is

sections9999-sql-error).

• Every sql call in your program should check the sqlcode returned by the
call. If this sqlcode is a 'bad' return code, then you should perform S9999-
sql-error. When checking the sqlcode and it is determined to be a bad
sqlcode, the variables in the ws-error-area should be set with the proper
program and DB2 values:

• Move your program name to ws-program.

• Move the programs tranid to ws-tranid.
• Move eibtermid to ws-termid.
• Move sqlcode to ws-sqlcode.
• Move the section you were in to ws-section.
• Move the DB2 command that was executed that caused the
error to ws-command.
• Move the DB2 table name to ws-table.
• Move any data information to ws-related-data.

• S9999-sql-error will call DSNTIAR and fill in ws-error-msg and XCTL to

DB2019DB.

• When calling DSNTIAR from a batch program, the batch program will
need to be compiled with the DYNAM parameter.

D. SQL ERROR HANDLING

Standard Error Routine

Use DB2 error handling routine include member DB2SQLERR (include member DB2WSERR has
working storage variables for the routine).

SQLCA
Always include the SQLCA include member in your application program.

Error Checking
Always check the SQL return code available in the SQLCODE field after every SQL statement:

General checking:

• SQLCODE = zeroes Normal execution

• SQLCODE > zeroes Statement executed; error detected
• SQLCODE < zeroes Statement not executed; error
detected

After OPEN or CLOSE, check

SQLCODE = OTHER Statement not executed; error detected

Prepared By P.Srinath Reddy 26th march 1999 8/17

 DB2 STANDARDS ,TIPS & GUIDELINES

After SELECT or FETCH, check

SQLCODE = +100 Record not found

Prepared By P.Srinath Reddy 26th march 1999 9/17

 DB2 STANDARDS ,TIPS & GUIDELINES

After INSERT, check

SQLCODE = -181 Timestamp error
SQLCODE = -530 Updating a foreign key that does not have a matching primary
key value in the parent table
SQLCODE = -803 Duplicate primary key

After UPDATE, check

SQLCODE = -181 Timestamp error or bad date
SQLCODE = +100 Record not found
SQLCODE = -531 Updating primary key that has dependent rows in
another table

After DELETE, check

SQLCODE = +100 Record not found
SQLCODE = -181 Timestamp error
SQLCODE = -532 Deleting parent rows with dependent children rows in
another table

DB2 Deadlocks
The use of the DB2 locking scheme may lead to a problem called deadlocking. This situation may arise
when two programs are attempting to update tables that have been previously locked by the other. In a
deadlock situation, DB2 arbitrarily selects a "winner" and a "loser". The "loser" will receive SQL error
code -911 or -913 and the current processing is rolled back. As a precautionary step it is important to update
all tables in related applications in the same sequence.

E. DB2 TOOLS & ENVIRONMENT

1. DB2 Interactive (DB2I)

DB2I is an interactive tool which allows one to invoke DB2 utilities, prepare programs for execution and
issue DB2 commands. DB2I provides an option called SPUFI which facilitates the development and testing
of Structured Query Language (SQL) statements. To invoke DB2I follow the steps below:
• Logon to TSO
• Select Option DB2 at the ISPF Primary Menu by entering 'D' at the
command line.

The first time you invoke DB2I default settings must be established via Option "D", DB2I Defaults, from
the DB2I Primary Option Menu. This will display the DB2I Primary Option Menu:

DB2I PRIMARY OPTION MENU => 1

Select one of the following DB2 functions and press ENTER

* 1 SPUFI (Process SQL statements)

* 2 DCLGEN (Generate SQL and source language declarations
* 3 PROGRAM PREPARATION (Prepare a DB2 application program to run)
* 4 PRECOMPILE (Invoke DB2 precompiler)
* 5 BIND/REBIND/FREE (BIND, REBIND, or FREE application plans)
* 6 RUN (RUN an SQL program)
* 7 DB2 COMMANDS (Issue DB2 commands)
* 8 UTILITIES (Invoke DB2 utilities)
* D DB2I DEFAULTS (Set global parameters)
* X EXIT (Leave DB2I)

PRESS: END to exit HELP for more information

Prepared By P.Srinath Reddy 26th march 1999 10/17

 DB2 STANDARDS ,TIPS & GUIDELINES

DB2I allows you to execute several processes interactively, however, standard JCL procedures will also be
available to execute processes such as performing a DCLGEN, executing, the precompiler, compiler, and
linkage editor, and executing the BIND in batch mode.

2. DB2I and SPUFI Defaults

This section will discuss the recommended parameters to use for DB2I and SPUFI. DB2I Defaults are
selected from Option D on the DB2I Primary Option Menu. The subsystem identifier parameter is set on
this screen. To select the TEST DB2 subsystem, enter "DBT1" on line 1, "DB2 Name". The production
DB2 susbystem can be accessed for authorized users only by entering "DBP1".

To change spufi defaults, select option 1 (SPUFI) from the main DB2I menu. Current SPUFI defaults are
updatable from a selection panel that is displayed when a "Y" is entered on the Option 5 line, "Change
Defaults" , from the SPUFI panel. It is important to select ISOLATION LEVEL ==> CS (CURSOR
STABILITY) to minimize the time a read-only page lock is held.

F. DESIGN CONSIDERATIONS
1. Indexes

Index Usage

Indexes are used in a WHERE clause with the following predicate types:

* column = value
* column is null
* column > value
* column < value
* column >= value
* column <= value
* column BETWEEN value1 and value2
* column LIKE 'char%'
* column IN list

Indexes are NOT used in WHERE clauses with the following predicates:

* column not null

* column not BETWEEN value1 and value2
* column LIKE '%char'
* column LIKE '_char'
* column LIKE host variable
* column not IN list

** Use the EXPLAIN utility to determine if indexes are being used.

Standards

Avoid numeric conversions

Always specify the same data types when comparing column values against host variables or literals. DB2
will not use an index for the named column if the host variable or literal value has a greater precision than
the data type of the column.

Prepared By P.Srinath Reddy 26th march 1999 11/17

 DB2 STANDARDS ,TIPS & GUIDELINES

Avoid character string padding

DB2 will not use an index if the literal value or host variable is longer than the column length.

Avoid the use of LIKE predicates with host variables or predicates beginning with % or _
They may prevent DB2 from using indexes that might be defined on a column to limit the number of rows
scanned.

Avoid an arithmetic expression in a predicate

DB2 does not use an index on a cursor for a column it expects to be updated unless the updated column is
qualified by an equal predicate and the preceding key columns are also qualified by equal predicates.

Do not use the SUBSTR function or concatenating columns in a predicate

They will prevent DB2 from using an index.

Guidelines

Index-based retrievals are generally more efficient than a tablespace scan.

When every page in the tablespace is retrieved, a tablespace scan is more efficient.

Always fully qualify the search criterion that you specify in your SQL statement.

Always specify the same data type, precision, and scale when comparing column values against host
variables and literals.

G. Focus Considerations
1. Background

The primary tool for ad hoc reporting against DB2 data will be Focus. While the Focus syntax is the same
regardless of the data structure accessed (VSAM, sequential files, DB2 tables, etc.), there are special
considerations when accessing DB2 data. It is advisable for the query writer to understand the appropriate
coding techniques for writing efficient queries which will provide accurate information.

2. Master File Description (MFD)

A Master File Description (MFD) is required for each table and view which will be accessed via Focus
queries. This can be generated by a member of Information Access Group (IAG) staff using the Focus
utility 'Auto-DB2'. The utility uses the DB2 catalog tables to generate the Focus code. This is a convenient
way to create an MFD and also ensure that all table/view names and column names exactly match the DB2
objects. This utility will be used both in test and production

3. Access File Description (AFD)

An Access File Description (AFD) is also required for each table and view to be accessed via Focus
queries. An AFD makes the connection between the table name, as used in a Focus query, and the actual,
fully-qualified DB2 table name (owner.tablename). This will also be generated by Auto-DB2. Because the
fully-qualified name will indicate the subsystem by the owner name (TESTDBA for test, PRODDBA for
production), it must agree with the subsystem id being accessed (see item 4 below).

4. Accessing test data with Focus

Prepared By P.Srinath Reddy 26th march 1999 12/17

 DB2 STANDARDS ,TIPS & GUIDELINES

It is possible to access data in the DB2 test subsystem (DBT1) as well as data in the production subsystem
(DBP1). A Focus query will attempt to access data in the production subsystem by default. However, if a
user wishes to 'test' a query against a smaller table - such as a test table, OR if the query must access new
tables not yet implemented in production, the following conditions must be met:

• An MFD and AFD for each table and view must exist in the test libraries
(FOCUS.INFO. MASTER. TEST for MFD's and
FOCUS.ACCESS.MASTER.TEST for AFD's)
• The user must specify the test subsystem (to override the default) using this
command as the first statement in the query: TSO SQL SET SSID DBT1
• The user must select 'DBT1' from the file screen (to indicate usage of the
test library copy of the MFD and AFD).

Selected, representative Focus queries will be used to test access and provide benchmark statistics for test
DB2 tables which will implemented in the Information Center.

H. DB2 PROGRAM DEVELOPMENT PROCEDURES

1. Compile Procedures

Precompile the application

This step scans the program and produces a modified program in which every SQL statement is replaced by
one or more host language statements. The DB2 precompiler also produces a database request module
(DBRM) which contains information about each SQL statement.

Translate command-level statements in a CICS program

This step translates the CICS command-level statements prior to compilation.

Bind the application

This step establishes a relationship between the program and the DB2 data. The bind process validates the
SQL statements, verifies authorization to access DB2 data, determines the access path, and creates the
application plan. It is important to use the EXPLAIN statement during this step to determine how DB2
uses resources during the execution of the SQL statements.

Compile (or assemble) and link-edit the application

This step will run the compile/link-edit procedures appropriate for the application. The dynam parameter is
not allowed when compiling db2 programs.

Execute the application

This step includes the JCL necessary to execute your program. At this time DB2 validates the application
plan. If any problems are detected (changes to the DB2 objects or to the binder's access authorization), DB2
will automatically rebind the application.

Compile Procedures

DSNHCOB use to compile batch COBOL II with DB2 V2.3.0

DSNHCOBC use to compile CICS COBOL II with DB2 V2.3.0
DSNHCBIC use to compile CICS/symbolic Intertest COBOL II with DB2 V2.3.0
DSNHASMH use to compile Assembler H programs

JCL to compile COBOL II programs is in DS.TEST.PANVALET as 'DB2COMPCIC' for CICS programs,

'DB2COMPBAT' for batch programs, and 'DB2COMPINT' to compile under intertest.

Prepared By P.Srinath Reddy 26th march 1999 13/17

 DB2 STANDARDS ,TIPS & GUIDELINES

2. BIND Considerations

Background

To access DB2 data, an SQL statement requires an access path. For dynamic SQL, such as statements
issued through SPUFI, DB2 determines the access path when the statement executes. For statements that do
not execute often, this method is usually acceptable. However, an application typically runs the same SQL
statements repeatedly. In this case, determining the access path at run time wastes system resources,
because the system must determine the same path repeatedly. To conserve your system resources, use static
SQL. Access paths for static SQL are determined only at BIND or REBIND time.

When a DB2 program is compiled, it produces a load module and a DBRM (Database Request Module)
containing the executable SQL. For this program to be executed, the DBRM must either be bound into a
package or directly into plan. A package can contain only 1 DBRM, but a plan can include many DBRMs
and/or packages. To run the program, you must specify a program and it's plan.

Depending upon how you design your DB2 application, you might bind all your DBRMs in one operation,
creating only a single application plan. Or, you might bind some or all of your DBRMs into separate
packages in separate operations. After that, you must still bind the entire application as a single plan, listing
the included packages or collections and binding any DBRMs not already bound into packages. Regardless
of what the plan contains, you must bind a plan before the application can run.

Here are some advantages to using packages:

Ease of Maintenance: When you use packages, you do not need to bind the entire plan again when you
change one SQL statement. You need to bind only the package associated with the changed SQL statement.
This feature is similiar to compiling called and calling programs with the "DYNAM" option.

Incremental Development of Your Program: Binding packages into package collections allows you to add
packages to an existing application plan without having to bind the entire plan again. A collection is a group
of associated packages. If you include a collection name in the package list when you bind a plan, any
package in the collection becomes available to the plan. The collection can even be empty when you first
bind the plan. Later, you can add packages to the collection, and drop or replace existing packages, without
binding the plan again.

Versioning: Maintaining several versions of a plan without using packages requires a separate plan for each
version, and therefore separate plan names and RUN commands. Isolating separate versions of a program
into packages requires only one plan and helps to simplify program migration and fallback. For example,
you can maintain separate development, test, and production levels of a program by binding each level of
the program as a separate version of a package, all within a single plan.

When to use Packages:

For plans which are bound with only one DBRM, packages don't offer any advantage. However, for plans
that use multiple DBRMs, it is sometimes better to bind each DBRM into a package and then bind the
packages into the plan. This feature will be especially valuable for CICS applications where concurrency is
more of an issue.

Bind Standards

• The program, DBRM, and plan names should always be the same.

• The program should use unqualified table names to allow the 'OWNER'
parameter of the bind to supply the qualifier ('TESTDBA' or 'PRODDBA').
This will require the programmer to edit the sql 'declare table' statement

Prepared By P.Srinath Reddy 26th march 1999 14/17

 DB2 STANDARDS ,TIPS & GUIDELINES

created by the DCLGEN.

• All table declarations (DCLGEN output) should be moved from the

programmer's DCLGEN library (PDS) to panvalet. They may then be
included in the program by a regular '++INCLUDE' statement, as for other
file layouts, rather than using the 'EXEC SQL INCLUDE' statement. Only
the SQLCA will then require the 'EXEC SQL INCLUDE'.

• Always BIND the plan again using the Replace option following a DB2
recompile.

• If an index is added to a table, the plans referencing that table must be

rebound.

• DBA approval will be required in order to use the Repeatable Read option
for the Isolation level BIND parameter. It is best to use Cursor Stability.

• Use Platinum Plan Analyzer on new programs. Plan Analyzer uses

EXPLAINS to foresee how well the program will use indexes and how
efficient the SQL is.

Guidelines

The following BIND parameters are recommended:

• Retain Execution Authority: YES

• Isolation Level: CS
• Plan Validation Time: BIND
• Resource Allocation Time: USE
• Resource Release Time: COMMIT
• Explain Path: YES

The parameters for a program named 'AA0100BB' to be bound in test for the first time would be as
follows:
DSN SYSTEM(DBT1)
BIND PLAN(AA0100BB) MEMBER(AA0100BB) -
LIBRARY ('DBT1.DB2.DBRMLIB') -
OWNER(TESTDBA) -
ACTION(ADD) -
RETAIN -
ISOLATION(CS) -
VALIDATE(BIND) -
RELEASE(COMMIT)
Subsequent executions of the bind would specify 'ACTION(REPL)' to replace the previous plan.

Prepared By P.Srinath Reddy 26th march 1999 15/17

 DB2 STANDARDS ,TIPS & GUIDELINES

The parameters for a program named 'AA0100BB' to be bound in production for the first time would be as
follows:

DSN SYSTEM(DBP1)
BIND PLAN(AA0100BB) MEMBER(AA0100BB) -
LIBRARY ('DBP1.DB2.DBRMLIB') -
OWNER(PRODDBA) -
ACTION(ADD) -
RETAIN -
ISOLATION(CS) -
VALIDATE(BIND) -
RELEASE(COMMIT)

Subsequent executions of the bind would specify 'ACTION(REPL)' to replace the previous plan.
Information

All plans which access a DB2 object that has been dropped will be marked as invalid. DB2 will
automatically rebind the plans the next time they execute, however, for performance reasons it is better to
manually rebind invalid plans.

3. Using Explain

The EXPLAIN statement and the EXPLAIN option of the BIND or REBIND commands can be used to get
information about the access paths used by DB2. The results of EXPLAIN are stored in a table that must
first be created by the plan owner or user that executes the EXPLAIN statement. Production information
will be stored in a table named "userid.PLAN.TABLE".

A standard query containing the SQL statements needed to create the plan.table can be copied from the
following member: "DBT1.DB2.SQLLIB(EXPLAIN)".

When the EXPLAIN option of the BIND command is selected, one or more rows for each explainable
statement in the plan will be stored in userid.PLAN.TABLE. The EXPLAIN statement stores information
about a single explainable statement. The data collected canthen be reviewed by querying the following
columns in this table. Some of the important fields are described below:

QUERYNO-Number which identifies the EXPLAIN statement

QBLOCKNO-Number which identifies levels of subselects

PLANNO-Order in which steps of plan are executed

METHOD-Type of activity performed

0 - first table access

1 - nested loop join
2 - merge scan join
3 - additional sorts

ACCESSTYPE-Method of table access

1. I - index
2. I1 - one-fetch index scan
3. R - tablespace scan (sequential)
4. N - index, for IN predicate
5. M - multiple indexes used
6. MX - multiple scans of one index

Prepared By P.Srinath Reddy 26th march 1999 16/17

 DB2 STANDARDS ,TIPS & GUIDELINES

7. MI - intersection of multiple indexes (AND predicate)

8. MU - union of multiple indexes (OR predicate)

MATCHCOLS # columns of index used in predicate match

ACCESSCREATOR Creator of index, if one is used

ACCESSNAME Name of index, if one is used

INDEXONLY Is this step fully serviced by index only or will data be accessed? (Y/N)

SORTN_xxxx Sort to create the "new" (input) table to this step? (Y/N)
(want to see "Y" for most efficient access)

SORTC_xxxx Sort to create the "composite" (results) table (Y/N)

(want to see "Y" for most efficient access)

TSLOCKMODE Type of tablespace lock chosen by DB2

1. S - shared
2. X - exclusive
3. IS - shared page locks
4. IX - exclusive page locks

PREFETCH Are pages fetched ahead?

1. S - sequential (e.g. cursor FETCH)

2. L - list (e.g. union of index entries)

I. INTERNAL DB2 PROCESSING CONSIDERATIONS

1. Thread Utilization

Standards & Guidelines

It is important to minimize the times a thread is allocated to an application to improve system throughput.

Minimize allocation overhead in high-volume, repetitive transactions by specifying the ACQUIRE and
RELEASE BIND parameters and reusing the thread.

Information

The thread concept is how applications access DB2 data. A thread is responsible for the following:
identifies the user, allocates DB2 resources, and executes user requests.

2. Locking & Performance Considerations

Information

The lock size is determined by the creator of the tablespace. A DB2 object may be locked at the page, table
(segmented tablespaces), or tablespace level. The lock type is determined by the type of statement that is
executed. For example, a SELECT or FETCH statement from a read-only cursor uses a shared lock; a

Prepared By P.Srinath Reddy 26th march 1999 17/17

 DB2 STANDARDS ,TIPS & GUIDELINES

cursor with a FOR UPDATE OF clause uses an update lock; and an exclusive lock is used when an
UPDATE, INSERT, OR DELETE statement is executed. The lock duration is determined by the isolation
level specified in the BIND step or in SPUFI. Lock escalation is used by DB2 to increase the size of a lock
for performance reasons.

End Of Document

Prepared By P.Srinath Reddy 26th march 1999 18/17