User Comm Detail
Title: Planning for DB2 Version 9: Release Information and Issues
Number: NA-2009-3177
Filter: All User Comms
After viewing this document, you may return to the Current User Comm Listing or use the record navigation buttons at the bottom of the page to move between User Comms in the "current" or "currently-filtered" listing. Please see the contact information below if you have any questions regarding this User Communication.
Scope: All North American SMCs
Issue Date: 8/20/2009
Effective Date: 8/20/2009
Group Name: Software Services Mainframe DB2
Affected Locations: All North American SMCs
Summary:
This communication details planning information required by application Database Administrator (DBAs), application Information Analysts (IAs), and System Software IAs for the migration to DB2 Version 9. The Release section indicates the improvements in DB2 V9 in performance and availability, security and auditing, SQL additions, consistency and standardization among the DB2 family of products, large object support, and multiple XML improvements. Finally, the Details section will refer to changes required for Version 9 when migrating from DB2 Version 8 New Function Mode (NFM).
Justification:
Proper preparation of the DB2 Version 8 NFM environment is necessary for a successful migration to DB2 Version 9. Preparation will include: ensuring proper software release levels of third-party products used to manage these environments and may include changes to DB2 objects prior to migration; executing IBM provided check jobs to ensure current DB2 objects have complete definitions at the tablespace, table and column level.
Benefits to Users:
Meeting all required prerequisites reduces the client impact. Some changes may present unexpected or unwanted results. DB2 V9 release provides improvements in SQL and applications, utilities, performance, and availability. Please review each of the details below and determine how the changes may benefit your application and DB2 subsystem.
Details:
The schedule for migrations will be that preparations and conversions will begin in 2010.
Mainframe DB2 support staff will periodically run the DB2 pre-migration checks (Job DSNTIJPM in DSNSAMP) to search for many release incompatibilities and send the results to various DBA groups.
New with DB2 V9, CM is now called conversion mode and no longer referred to as compatibility mode.
Prerequisites for DB2 V9: Before the migration to DB2 V9 Conversion Mode (CM) may occur, the subsystem must be running DB2 V8 NFM. The BSDS conversion program must have been run and the V8 fallback PTF must be in place (DB2 level of code must be AH00 level or higher). DB2 ERLY code for DB2 V9 must have been implemented.
Like DB2 V8, DB2 V9 has three "progressive" modes of migration and they are Conversion Mode (CM), Enable New Function Mode (ENFM) and New Function Mode (NFM). Two new phases have been added to allow reversion - Conversion Mode* (CM*) and Enable New Function Mode*(ENFM*).
DB2 V9 does allow FALLBACK to occur from DB2 V9 CM back to DB2 V8 NFM. In addition, after upgrading DB2 V9 CM to DB2 V9 ENFM or DB2 V9 NFM, you may revert back to DB2 V9 CM* or DB2 V9 ENFM* and then convert to DB2 V9 ENFM and convert to DB2 V9 NFM. Please note that you MAY NOT fallback to DB2 V8 NFM once you have upgraded to DB2 V9 ENFM or higher.
Here are diagrams to represent the flow of the DB2 V9 migration:
DB2 V8 NFM --> DB2 V9 CM
migrate
DB2 V8 NFM <--- DB2 V9 CM
fallback
DB2 V9 CM --> DB2 V9 ENFM
convert
DB2 V9 ENFM --> DB2 V9 NFM
convert
DB2 V9 ENFM <--> DB2 V9 CM* may only return to DB2 V9 ENFM
DB2 V9 NFM <--> DB2 V9 CM* may return to DB2 V9 ENFM* or DB2 V9 NFM
DB2 V9 NFM <--> DB2 V9 ENFM* may only return to DB2 V9 NFM
Details of improvements in the DB2 V9 release are:
1. XML: has been improved and enhanced with changes in the following areas: XML document storage, XML document retrieval, application development, database administration support, performance benefits through indexing support.
2. SQL consistency improvements: across the multiple DB2 platforms. Some of the common functions, statements, and clauses are: SELECT FROM DELETE and SELECT FROM UPDATE statements, INSTEAD OF triggers, BIGINT data type and function, BINARY data type and function, file reference variables XML and LOB, INTERSECT in fullselect statement, EXCEPT keyword in subselect, native support for SQL procedures, nested compound statements in SQL procedures, support for NOT LOGGING tablespaces, provide the ability to return ranking, row numbering, and existing aggregate function information as a scalar value in the result of a query, COLLATION KEY function, CREATE INDEX on an expression, automatic creation of database, tablespace, and all system required objects for a CREATE TABLE statement and SPATIAL support for geographical systems.
Further details:
• In DB2 V9 NFM, new support for native SQL procedures simplifies the definition and use of SQL procedures by eliminating the need for generating a C program.
• Stored procedures are also improved for consistency and to meet ANSI standards by: changing name resolution within a procedure body; Using delimited identifiers, including lowercase characters, for SQL condition names/labels/variables, and SQL parameters; Nested compound statements; Versioning and managing source code; Deployment of native SQL procedures to multiple servers; and debugging of native SQL procedures.
• Spatial Support provides the ability to: Invoke spatial queries for local and remote clients based on geographic relationships; Create spatial indexes on spatial columns; Manage geographic coordinate systems, spatial indexes, and spatial column usages through stored procedure interfaces.
3. Large object enhancements: in fetch statement for LOB and XML data, XML and LOB file reference variables, utility enhancements for handling LOBs in: LOAD; UNLOAD; REORG; CHECK LOB; and CHECK DATA and performance improvements for retrieving data for small and medium LOBs and eliminate locks for LOB operations.
4. New SQL additions: TRUNCATE table to remove rows from table; New built in SQL type DECFLOAT, VARBINARY data type and function; New row change timestamp column; New expression ROW CHANGE, MERGE and SELECT from MERGE statements.
5. Availability improvements : Online REORG removes the BUILD2 phase; Replace tables faster through support for clone tables; New Universal Tablespaces Partition by Growth (PBG) or Partition by Range (PBR), Implement DB2 ERLY code without an IPL; ALTER TABLE RENAME COLUMN feature to allow renaming a column without dropping and recreating table; RENAME INDEX ability to rename an index; Ability to suppress LOGGING; Support for SMS data class, management class, and storage class in CREATE STOGROUP, ALTER STOGROUP statements; and Support for Extended Address Volumes (EAV).
6. Performance Improvements: CPU processing time is reduced when addressing non-padded index keys from data rows in COPY, REORG, LOAD, REBUILD INDEX, RECOVER, RUNSTATS, CHECK INDEX; Use of histrogram statistics versus frequency statistics for optimization; Use of global query optimization to optimize a query as a whole rather than break it into individual parts; Global storage pool above 2G line may be used for more queries and may reduce storage contention; Page range screening in queries enhanced; Use of REOPT(AUTO) for dynamic queries; More flexibility in the FETCH FIRST n ROWS ONLY and ORDER BY in FULLSELECT or SUBSELECT; Index compression; Index on expression; Larger page size for indexes (8K, 16K, 32K); Use of RANDOM option in CREATE INDEX or ALTER INDEX to reduce contention for OLTP; Improvements for rows containing variable length columns; Asymmetric page splits to improve sequential key inserts; Data sharing logging improvements for unique LRSN; Use of best sequential access method (BSAM) to access archive log; DSNTYPE=LARGE to support archive log up to 4G of tracks per disk volume; and Data insert performance improvement by use of the APPEND option.
7. Security and Auditing, Compliance: New features called trusted context and roles; Improvements in trace filtering for auditing; Exploitation of the z/OS Application Transparent - Transport Layer Security (AT-TLS) function in the TCP/IP stack and provide TLS for DB2 clients that require secure connections; Use of INSTEAD OF triggers (based on views) to do insert, update, and deletes; and support for Advanced Standard Security Encryption (AES) of userids and passwords.
Highlights of Planning Consideration:
- No tablespace or index may be in AREO* status (may occur due to schema evolution); these objects must be fixed before the migration to DB2 V9 occurs; DBA should fix all the application DB2 objects in AREO* prior to migration. The DB2 system programmer will perform the command as part of a check when preparing to migrate, and again at migration time. No object may be in AREO at migration time. DBA should perform command to find objects in AREO*: -DIS DB(*) SPACENAM(*) ADVISORY(AREO*).
- Now mandatory that all DB2 managed stored procedures be converted to WLM managed stored procedures prior to V9 migration.
- JAVA stored procedures no longer run in resetable JVMs. Please see “Persistent Reusable Java Virtual Machine User's Guide”.
- JDBC/SQLJ Driver for OS/390 and z/OS is no longer supported. Modifications may be required for applications to work with the IBM® DB2® Driver for JDBC and SQLJ (formerly known as the DB2 Universal JDBC Driver). Please see Application Programming Guide and Reference for Java for more details.
- DB2 Connect, current level for DB2 V9 at time of this writing is V9.1 Fix Pack 1, V8.2 FP6, V8.1 FP13, V9.5, and with V9.5 FP3 introduces Sysplex Failover/Workload balancing and XA; please check with IBM for current information.
- Simple tablespaces support has been deprecated. Simple tablespaces may no longer be created. Access to existing simple tablespace will still function. But an inadvertent drop will render the tablespace unrecoverable.
- Requirement for complete definitions for tables, indexes, and columns. A CHECK job will be run to determine list of objects that require complete definition and will be provided to DB2 DBA. These must be addressed prior to beginning the DB2 V9 migration.
- DB2 XML Extender is deprecated and should be replaced by the new XML data type.
- DB2 Administration Server, DB2 Control Center, and DB2 Development Center are deprecated. Use IBM® Data Studio to design, develop, deploy, and manage your data-driven applications.
- All of the DB2 MQ XML functions and stored procedures are deprecated.
- DSNZPARMS that have been removed - SUPPRESS_TS_CONV_WARNING=NO, MAX_OPT_ELAP, TABLES_JOINED_THRESHOLD, MORE_UNION_DISTRIBUTION, RELCURHL, STORPROC.
- DSNZPARMS that have been deprecated – DBPROTOCOL – will always default to DRDA. A warning will be received if using PRIVATE protocol. PRIVATE protocol will be totally removed in a future release of DB2; application DSNTP2DP available to assist in conversion.
- DSNZPARM changes in defaults for CACHEDYN_FREELOCAL(1), OPTIOWGT(ENABLE), MGEXTSZ(YES), MAXARCH(10000), MINSTOR(YES); Changed acceptable values for subsystem parameter NPGTHRSH from -1 to 0 or an integer greater than 1.
- PLANS bound with DBRMs – future release of DB2 will require no DBRMs bound to plans, exclusive use of packages bound to plans. You should start to bind your application as a package, then bind the package to the plan.
- ACQUIRE(ALLOCATE) option in BIND PLAN in a future release may not be supported.
- Interfaces to DSNHDECP in the future will allow the ability to specify a different name than DSNHDECP; in preparation for this, the START DB2 command lists the fully qualified dataset name for DSNHDECP module.
- The START DB2 command will show the fully-qualified data set name for the DB2 subsystem parameter module (DSNZPARM), and security exits (DSN3@ATH, DSN3@SGN, and DSNX@XAC).
- Caution must be taken using the NEWFUN parameter to the precompiler to ensure you are using the correct level of DB2 V9 code for the target environment.
- For a future release, applications that are compiled on unsupported compilers may not support new attributes, such as the COBOL COMP-5 attribute.
- The defaults for parameters in the BIND Package and BIND Plan statements have changed – CURRENTDATA will be NO, ISOLATION will be CS and DBPROTOCOL will be DRDA.
- The DB2 TEMP database will be moved to the WORKFILE database. The original TEMP database will also be dropped , as part of the DB2 V9 upgrade so that the space may be reclaimed.
- At least one 32K pagesize tablespace is required in the WORKFILE database for Declared Global Temporary Tables (DGTT) and scrollable cursors. A 32K bufferpool must be defined.
- User indexes may be defined using 4 KB, 8 KB, 16 KB, or 32 KB buffer pools.
- XML users will need to drop and rename user defined data type that are named XML. A new XML data type is provided with DB2 V9. XMLNAMESPACES function has changed. Empty element serialization has changed.
- JDBC and SQLJ users – use of progressive streaming for XML and LOB changes the duration that the content is available. With progressive streaming, the contents are available until next cursor move or the close of cursor.
- Applications that use DB2 supplied SQLJ JAR stored procedures will have improvements in SQLSTATE and SQLCODE, so that more meaningful values are returned. This may mean application changes for items like SQLCODE checking.
- Additional RESERVED words, such as: CLONE; DOCUMENT; INFINITY; INTERSECT; KEEP; NAN; PUBLIC; ROLE; ROW; ROUND_DOWN; ROUND_UP; STATEMENT; TRUNCATE; TYPE have been added to DB2 V9. See the “DB2 Version 9.1 SQL Reference Guide” appendix to see all the reserved words in this release, and review/modify applications accordingly.
- PL/1 applications must change for host variable and strings. A host variable must be preceded by a colon and a string must not be preceded by a colon.
- Automatic rebind of plans and packages will occur for those, that were bound before DB2 V4. Check RELBOUND column of SYSIBM.SYSPLAN and SYSIBM.SYSPACKAGE for values ‘B” through “F”. This indicates objects bound prior to DB2 V4 and candidates for rebind.
- DB2 enforcement of ROWID incompatibility in comparison with character string.
- You will not be allowed to explicitly create a database beginning with DSN, followed by 5 digits.
- Implicitly created objects that contain LOB columns may require additional grants for privileges.
- Update to the DSNDQJ00 macro for log compensation identification.
- SYSOBJ tablespace will increase page size and will use a different bufferpool; new and changed indexes on SYSPACKSTMT and SYSPACKAUTH tables as well as additional columns to tables. The changes to the DB2 V9 catalog are not as dramatic as the DB2 V8 changes.
- CREATE DATABASE AS TEMP is no longer allowed. TEMP tablespace objects will be moved to workfile database. A mandatory 32K tablespace must be defined in workfile database for V9.
- START TRACE DEST(OPX) has been updated to choose next available buffer.
- CREATE command with an implicit tablespace name will create a partition by growth tablespace. DB2 will choose a bufferpool based on record length, if no bufferpool has been specified.
- Behavior changes for several CREATE statements: CREATE DATABASE, CREATE LOB TABLESPACE, CREATE TABLE, CREATE AUXILIARY TABLE, CREATE INDEX, CREATE AUXILIARY INDEX in such areas as new functionality, roles, implicit or explicit privileges, support of LOBs, XML. For detailed information on changes, please see DB2 Version 9.1 for z/OS SQL Reference (SC18-9854).
- Behavior changes for several DROP statements: DROP TABLESPACE, DROP TABLE, DROP INDEX ; for example, DB2 may drop an index when a unique constraint is removed on a table that is created in an implicitly created tablespace. For detailed information on changes, please see DB2 Version 9.1 for z/OS SQL Reference (SC18-9854).
- You may not ALTER an INDEX and include INSERT, UPDATE, DELETE statements for same table within the same commit scope.
- You may not add a column to a table and issue SELECT, INSERT, UPDATE, DELETE, or MERGE statements, for that table, in the same commit scope.
- Change in behavior of INSERT statements with OVERRIDE USER VALUES specified.
- DESCRIBE statement no longer returns LONG data types.
- HOST VARIABLE ARRAY may only be specified in syntax for multi row use such as MULTI ROW FETCH and MULTI ROW INSERT and for MERGE statements.
- DEBUGSESSION privilege will be needed in DB2 V9 NFM for users in new Unified Debugger client platform.
- DECRYPT_BINARY, DECRYPT_BIT, DECRYPT_CHAR, and DECRYPT_DB functions result length is changed to be 8 bytes less than input value.
- LONG VARCHAR and LONG VARGRAPHIC column type in SYSIBM.SYSCOLUMNS and SYSIBM.SYSCOLUMNS_HIST will be stored as VARCHAR or VARG and no longer as LONG VARCHAR or LONGVARG.
- The DSNWZP subsystem parameter stored procedure will be dropped and recreated. DSNWZP will now return subsystem parameter settings in the same format as the subsystem parameter macros.
- Ability to LOG LOBs greater than 1G.
- DB2 will issue an error when passing LOB as parameter in stored procedure and the argument value is longer than target parameter.
- Several changes to VARCHAR_FORMAT function – formatting; length attribute; and format element.
- Maximum limit of a row used by sort to evaluate MULTIPLE DISTINCT and GROUP BY column functions is decreased to 32600. You will get an error now if the resultant row is larger.
- The CAST FROM, TABLE LIKE, and the AS LOCATOR clauses are unsupported, if specified in an unsupported context, and will cause an error. In addition, the CCSID parameter is not supported by the DECRYPT_BIT and DECRYPT_BINARY built-in functions and will cause a DB2 error. Refer to the DB2 Version 9.1 for z/OS SQL reference for full description of these changes.
- In the CREATE PROCEDURE syntax, fenced and external now mean the same thing. If specified, they indicate external SQL procedure is to be created. If not specified, it indicates a native SQL procedure is to be created.
- You must explicitly qualify the names of any SQL parameters, SQL variables or columns that have non-unique names.
- Make any necessary program changes for possibly different values for: RETURNED_SQLSTATE and DB2_RETURNED_SQLCODE.
- Support is now in place for a compound statement within a handler body.
- Change programs to handle error/warning message from SQL procedures. External stored procedures will still receive DSN* messages, while native stored procedures issue SQL return codes.
- Error checking for CHAR type with length of 0 has been corrected.
- ALTER TABLE ADD COLUMN will now generate a new tablespace version.
- The CAST FROM clause of CREATE FUNCTION statement for SQL functions is no longer supported and returns an error.
- GRAPHIC and NOGRAPHIC SQL processing options are deprecated.
- You are now able to perform ALTER DATABASE STOGROUP for work file databases.
- Enforcement in place on restrictions where an INTO clause can be specified.
- User-defined function names must be fully qualified.
- In DB2 V8 and below, tablespaces were created in basic row format (BRF). In DB2 V9, the REORG or LOAD REPLACE utilities automatically convert the tablespace to reordered row format (RRF). At the time of this usercomm, the RRF feature is still evolving so check with your DB2 system programmer when you convert to DB2 V9 NFM for more highlights on RRF.
- DB2 ignores the LOAD and REORG parameter KEEPDICTIONARY when tables are converted to reordered row format, unless zparm HONOR_KEEPDICTIONARY is YES.
- The changes to REORG SHRLEVEL CHANGE utility should be reviewed for operational impacts. New considerations exist for submitting jobs by partition, in parallel. The BUILD2 phase has been eliminated. The RETRY returns a RC=8, if unable to drain access to the tablespace. NPI datasets are fully rebuilt as part of REORG TABLESPACE PART SHRLEVEL CHANGE,. This may impact applications accessing data using NPI indexes.
- Changes to the REORG utility include ignore FASTSWITCH option in REORG TABLESPACE and REORG INDEX; The 254 partition restriction has been removed.
- DSN1LOGP will issue a RC=4, when the provided RBA or LRSN range are not in the available log files.
- DISPLAY THREAD command output length defaults to 512.
- DISPLAY DDF command location name may be longer than 16 to support IPV6 IP addresses.
- The Real Time Statistics database (DSNRTSDB) objects will be moved into the DB2 catalog as part of the DB2 V9 upgrade.
- For Data Sharing, group attach is randomized. DSNZPARM RANDOMATT can restore V8 functionality if desired.
- The behavior has changed for ODBC data conversion for the SQL_BINARY type.
- The RACF access control authorization exit routine (DSNXRXAC) will be reassembled to enable support for all of the DB2 Version 9.1 authorization functions. z/OS 1.8 provides full support for roles.
- There are two types of resource limit facility tables in V9 available for use: authid.DSNRLSTnn tables and authid.DSNRLMTnn tables; in addition, changes have been made in reactive governing in ASUTIME.
- Connections from VAX machines and the PASCAL L string data type is no longer supported.
- AIV extender, Text Extender, and Net Search Extender support is removed.
- Net.data is no longer supported, Websphere is a replacement.
- DB2 Estimator product is no longer provided.
- DB2 QMF Visionary Studio program is removed from DB2 QMF Enterprise Edition.
- Visual Explain for DB2 for z/OS is not available for Version 9.1, but functionality may be found in Optimization Service Center.
Where to Find Additional Information:
- DB2 Version 9.1 for z/OS What’s New? (GC18-9856)
- DB2 Version 9.1 for z/OS Installation Guide (GC18-9846)
- DB2 Version 9.1 for z/OS SQL Reference (SC18-9854).
- DB2 Version 9.1 for z/OS RACF Access Control Module Guide (SC18-9852)
- IBM Redbook abstract DB2 9 for z/OS Technical Overview (SG24-7330-00)
- Persistent Reusable Java Virtual Machine User's Guide (SC34-6201-04)
- DB2 Version 9.1 for z/OS Application Programming Guide and Reference for JAVA (SC18-9842-01)
Additional DB2 Version 9.1 z/OS Information may be found at website:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp
Primary Contact:
Cathy Puzzuoli
Software Services Mainframe DB2
336-765-9045 [8-]
Secondary Contact:
Rebecca Keller
Software Services Mainframe DB2
719-592-4258 [8-]
|
Usercomm - DB2 V9 Published UserComm