DB2 for z/OS and WebSphere: The Perfect Couple

28 DB2 for z/OS and WebSphere: The Perfect Couple

1.9.2 DB2 Universal Driver stored procedures and metadata

Metadata is a term used to describe characteristics of data. The phrase “data about data” is

generally used to define metadata. For our purposes, metadata refers to information used to

describe data objects that can be retrieved from the DB2 catalog. The JDBC API allows

applications to retrieve database metadata. This metadata fall into three broad categories:

򐂰 Information about the JDBC driver such as driver name, major and minor version

򐂰 Information about the database server such as the supported SQL level and handling of

NULL values

򐂰 Information about database objects such as number of columns in a table, column names,

and the column data type

The metadata is usually retrieved by specifying the schema associated with the desired data

objects. Additional details about DB2 catalog metadata and how to retrieve specific

information is documented in DB2 for z/OS and OS/390: Ready for Java, SG24-6435.

Certain functions of the DB2 Universal Driver expect metadata to be available. In fact, the

metadata is also expected to be in a particular format. This is consistent with the schema

metadata APIs documented in the JDBC and ODBC specifications. This section discusses

required DB2 for OS/390 and z/OS functionality when using the DB2 Universal Driver.

DESCRIBE for static SQL statements

The SQL statement DESCRIBE obtains information about a prepared statement, a table, or a

a view. There are some restrictions when using the DESCRIBE statement, such as:

򐂰 It can only be embedded in an application program.

򐂰 It cannot be dynamically prepared.

򐂰 It cannot be specified in Java.

򐂰 An SQL descriptor area (SQLDA) is required.

The SQLDA is simply a collection of variables and its layout is described in Appendix E of

DB2 Universal Database for z/OS Version 8 SQL Reference, SC18-7426.

The DB2 Universal Driver expects the SQLDA to be available when using SQL statements.

By default, a DESCRIBE (OUTPUT) can only be used against dynamically prepared

statements. To resolve this problem, a new DSNZPARM parameter, DESCSTAT, was

introduced. It allows the driver to issue DESCRIBE statements against statically bound

statements as well.

When set to YES, DESCSTAT generates a DESCRIBE SQLDA at bind time, allowing

DESCRIBE requests for static SQL to be satisfied during execution. When DESCSTAT is set

to YES, packages will slightly increase in size because the DESCRIBE SQLDA is now stored

with each statically bound SQL SELECT statement.

DB2 UDB for OS/390 Version 6 and DB2 UDB for OS/390 and z/OS Version 7 have a default

value of NO for option DESCRIBE FOR STATIC on installation panel DSNTIPF. (This is the

option associated with DSNZPARM DESCSTAT).

Modify DSNZPARM DESCSTAT to YES in member DSNTIJUZ in the DB2 hlq.SDSNSAMP

library. DSNTIJUZ is used to perform DB2 DSNZPARM updates. Generally, to pick up

subsystem changes to DSNZPARM parameters after running DSNTIJUZ, the DB2

subsystem would have to be recycled by stopping and starting DB2. However, DESCSTAT is

a DSNZPARM that can be updated online. This means the SET SYSPARM

LOAD(dsnzparm_name) command can be used as a less disruptive alternative to refreshing

DB2 subsystem parameters to pick up the change.