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
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.