What release of AS is recommended for DB2 on z/OS?

AS keeps pace with developments in IBM DB2 providing support for new DB2 releases in each subsequent release of AS.

• AS V6.3 introduces support for DB2 10
• AS V6.2 introduces DB2 long table name support
• AS V6.1 introduces long column name support and support for the DB2 Governor.  Version 6.1 resolves the problem that users cannot select DB2 tables from DB2 Version 8 or later due to the switch to a Unicode DB2 catalog.
  

The recommended minimum AS releases for use with current DB2 levels are:

• DB2 10              -  AS V6.3 or later
• DB2 Version 9   -  AS V6.2 or later
• DB2 Version 8   -  AS V6.1 or later

If you are upgrading from an old DB2 V7 or earlier release of DB2 you should be aware of the major changes introduced with DB2 Version 8.  These changes to DB2 can impact any application or product that works with DB2 data, including your AS applications.  Please read the below information for further details.

AS Enhancements to Support DB2 Version 8 on z/OS

Changes in DB2 Version 8 prevents an AS user from being able to see a list of DB2 tables, and so unable to select a DB2 table to work with.  AS Version 6.1 includes changes to resolve this problem.  Users are recommended to take the FREE* UPGRADE to AS Version 6.3 or later, which includes enhanced support for DB2 Version 8 and support for DB2 Version 9 and DB2 10.

The main area of change within AS V6.1 is to support long column names of up to 30 characters (Rocket AS Applications and LONG Column Names). In addition there is support for the DB2 Predictive and Reactive Governors. Also new features SQLPREP and SQLEXEC which allow repeated SQL INSERT, UPDATE and DELETE to be processed in AS with better performance.

In AS v6.2 the main change is support for long table names (up to 128 characters). Please email us if you would like to upgrade your AS software.

Are Your AS Applications Ready for IBM DB2 Version 8 or later?

IBM DB2 Version 8 for z/OS have many new features which support a variety of improvements covering:

• Performance
• More flexibility with SQL
• Continuous availability
• Unicode
• Enhanced utilities

Performance

Many of these features are changes that can be made to DB2 which may improve performance and that subsequent improvement will be seen in existing AS Applications without any changes to those Applications.

Areas like larger buffer pools, materialized query tables, sort parallelism, and variable control interval (CI) sizes may all improve the elapsed time for table scans and hence improve Query performance.

Enhanced SQL

Materialized query tables may be used within SQL Queries to bypass the base tables and hence improve Query turnaround.

Common Table Expressions allow for recursive style of processing. A Common Table Expression is like a temporary view that is defined for the duration of an SQL statement. These can, in some circumstances lead to improved performance.

More than one DISTINCT keyword is allowed in a single query. As a result you no longer need to write multiple queries to retrieve multiple distinct columns. The same expressions can be specified in the GROUP BY clause that are in HAVING, SELECT, and ORDER BY clauses.

Continuous Availability

In prior releases data and applications were unavailable during operations to alter definitions of table, table space, and index attributes. In DB2 Version 8 these can be changed without losing availability. This covers areas like changing column types, lengths, adding columns to an index, adding, rotating, or rebalancing partitions dynamically.

More flexibility and faster recovery with system-level point-in-time.

Unicode

Significant support for Unicode has been added which means that data from around the world can be managed.

SQL Statements are now converted to Unicode before parsing. Multiple CCSIDs may be used within a single SQL Statement.

Enhanced Utilities

Many utilities have been changed to support things like Long names, 64-bit addressing, Unicode, data-partitioned secondary indexes, and system-level point-in-time recovery. These affect utilities like REORG, LOAD, UNLOAD.

Improvements to the RUNSTATS utility in DB2 Version 8 let you collect distribution statistics for non-leading indexed columns and non-indexed columns so that DB2 can use these statistics to select better access paths.

For additional information, please visit the IBM DB2 Version 8 for z/OS page on the IBM website.

How DB2 Version 8 Might Affect Your AS Applications

AS Applications which use GROUP BY and/or ORDER BY within AS queries may obtain different results where the data in the tables are held as Unicode format. This also applies to AS SELECT and SEQUENCE statements operating on an AS IN Table which is a DB2 Version 8 Table. These Applications need to be tested and if a different set of results is obtained then the underlying 'Queries' will need changing.

Summary

In conclusion many of the improvements to IBM DB2 Version 8 can be gained without any changes to AS Applications. For the best DB2 support AS v6.3 is recommended, but as a minimum AS v5.3 with the maintenance mentioned above is needed.