| Craig S. Mullins
Database Performance Management
Preparing for DB2 Version 6
To find all type 1 indexes issues the following SQL statement:
SELECT CREATOR, NAME FROM SYSIBM.SYSINDEXES WHERE INDEXTYPE = ' ';Shared Read Only Data
Shared read only data (SROD) was provided as a new feature of DB2 in Version 2.3. SROD provided a way for the same DB2 database to be read by multiple DB2 subsystems without implementing distributed data or Sysplex data sharing. However, the shared object must be started ACCESS(RO) and all data access is read only. When the data needs to be updated only one of the subsystems, the one marked as the owner, can update the data.
SROD is complex to implement, limited in functionality, and not frequently implemented. As of DB2 V6, SROD support is removed. In order to support SROD-like functionality you will need to convert to data distribution or data sharing.
To find all SROD databases issue the following SQL statement:
SELECT NAME, BPOOL, ROSHARE FROM SYSIBM.SYSDATABASE WHERE ROSHARE IN ('O', 'R');RECOVER INDEX
Through DB2 V5, the RECOVER INDEX utility is used to recreate indexes from current data. RECOVER INDEX scans the table on which the index is based and regenerates the index based on the actual data. Indexes are always recovered from actual table data, not from image copy and log data.
With DB2 V6, the function of the RECOVER INDEX utility changes. Instead of rebuilding indexes from the current data, RECOVER INDEX will actually recover the index by reading an image copy of the index data set. So, with DB2 V6 you can use the COPY utility to make backups of DB2 indexes and the RECOVER utility to restore them.
To provide equivalent functionality for recreating an index from the current data, IBM provides a new utility called REBUILD INDEX. The REBUILD INDEX utility works exactly like RECOVER INDEX used to function.
Organizations should begin changing all of their current RECOVER INDEX jobs to use REBUILD INDEX syntax instead. The REBUILD INDEX syntax is available in DB2 V5 and V4 (with PTF PQ09842) and will work exactly like RECOVER INDEX. After you migrate to DB2 V6, the RECOVER INDEX utility will cease to function if the proper index backup copies are not available to use during recovery.
Host Variables Without Colons
All DB2 programmers know that host variables used in SQL statements in a program should be preceded by a colon. So, if a host variable is named HV it should be coded in the SQL statement as :HV. However, most programmers do not know that through V5, DB2 programs tolerate host variables that are not preceded by a colon. This "feature" ends as of DB2 V6.
The reasoning from IBM for eliminating this feature is that it is getting too difficult for DB2 to differentiate host variables from SQL. This is due to the rising complexity of SQL and the new features being added to DB2. As such, for DB2 V6 and onward, all host variable must be prefixed with a colon.
This change should not impact many programs because most organizations have DB2 standards that dictate all host variables will begin with a colon. However, because DB2 has tolerated host variables without a colon through DB2 V5, you should inspect all DB2 SQL statements in application programs to ensure compliance prior to migrating to DB2 V6.
A little-used feature of DB2 is the ability to provide security via dataset passwords. Using the DSETPASS key word of the CREATE TABLESPACE and CREATE INDEX statement, it is possible to password protect DB2 datasets.
This feature disappears with DB2 V6. If you need to protect your DB2 datasets outside of DB2 security you can use RACF, ACF2, Top Secret, or whatever security package you have installed at your site to accomplish this.
To find datasets that are password protected using DSETPASS, issue the following SQL statement:
SELECT 'INDEX ', CREATOR, NAME FROM SYSIBM.SYSINDEXES WHERE DSETPASS <> ' ' UNION ALL SELECT 'TSPACE', DBNAME, NAME FROM SYSIBM.SYSTABLESPACE WHERE DSETPASS <> ' 'Stored Procedure Registration
After coding a stored procedure, you must register information about that stored procedures in the DB2 system catalog. This process is in sharp contrast to the manner in which other database objects are recorded in the system catalog. Typically, when an object is created, DB2 automatically stores the metadata description of that object in the appropriate DB2 Catalog tables. For example, to create a new table the CREATE TABLE statement is issued and DB2 automatically records the information in multiple system catalog tables (SYSIBM.SYSTABLES, SYSIBM.SYSCOLUMNS, and possibly SYSIBM.SYSFIELDS). Because stored procedures are not created within DB2, nor are they created using DDL, the database administrator must use SQL INSERT statements to populate the SYSIBM.SYSPROCEDURES system catalog table with the meta-data for the stored procedure.
The following SQL provides an example of an INSERT to register a stored procedure:
INSERT INTO SYSIBM.SYSPROCEDURES (PROCEDURE, AUTHID, LUNAME, LOADMOD, LINKAGE, COLLID, LANGUAGE, ASUTIME, STAYRESIDENT, IBMREQD, RUNOPTS, PARMLIST, RESULT_SETS, WLM_ENV, PGM_TYPE, EXTERNAL_SECURITY, COMMIT_ON_RETURN) VALUES ('PROCNAME', ' ', ' ', 'LOADNAME', ' ', 'COLL0001', 'COBOL', 0, 'Y', 'N', ' ', 'NAME CHAR(20) INOUT', 1, ' ', 'M', 'N', 'N');This SQL statement registers a stored procedure written in COBOL and named PROCNAME with a load module named LOADNAME. It uses a package with a collection ID of COLL0001. Any location can execute this procedure. The program stays resident and uses the DB2 SPAS (not Workload Manager), and no limit is set on the amount of time it can execute before being canceled. Furthermore, the stored procedure uses one input/output parameter, and the parameter cannot be null.
This method of registering stored procedures changes in DB2 V6. Instead of the INSERT statement CREATE and ALTER statements are provided for registering stored procedures to the DB2 system catalog. Additionally, a new catalog table named SYSIBM.SYSROUTINES replaces SYSIBM.SYSPROCEDURES. This new table will store information on triggers, user-defined functions, and stored procedures. The metadata for all of these "routines" will be provided to the system catalog by means of DDL statements.
A number of organizations have implemented processes for creating and updating stored procedures that includes registration. These processes will need to be modified for DB2 V6. Additionally, if your organization uses a third party tool to register or change stored procedure information be sure that it will be changed to support the new DB2 V6 DDL syntax.
Other Concerns DB2 private protocol distributed data
Distributed data support was added to DB2 as of V2.2. At that point in time, IBM had not yet formulated its DRDA framework. In DB2 V2.2, distributed unit of work (DUW) capability was provided solely through a private protocol which did not support any industry standards. As of DB2 V3, both the private protocol DUW and full DRDA DUW is supported. Private protocol is also referred to as system-directed access and DRDA protocol is also referred to as application-directed access.
Application-directed data access is the more powerful of the two options. With application-directed access, explicit connections are required. Furthermore, application-directed distributed access conforms to the DRDA standard.
But, DB2 also provides system-directed access to distributed DB2 data. The system-directed access is less flexible than application-directed access because:
As of DB2 V6, three part names can be used with DRDA. This provides static SQL support for distributed requests using three part names. DB2 private protocol distribution is still available with DB2 V6, but IBM has indicated that it will be removed in a future release. As such, consider migrating away from DB2 private protocol distribution as of V6.
Version 6 is the first release of DB2 to take features out of the product. As such, organizations must understand what is being removed, know how to provide similar functionality with other DB2 features, and develop a plan to migrate away from the non-supported features. It is not too early to begin planning and migration now. The sooner you remove the old technology, the sooner you can move to the latest and greatest version of DB2 when it is available.
From DB2 Update (Xephon), October 1998.