DB2 Version 8 for z/OS On-Line Schema Evolution

1. DB2 Version 8 for z/OSOn-Line SchemaEvolution Phil GraingerComputer Associates

2. Agenda • What is On-Line Schema Evolution? • What can you ALTER? • How does it work? • What is not yet supported? • Discussion

3. Disclaimer This presentation has been written from documentation from IBM that existed in May 2003 and also takes some examples from early experiences with Version 8 itself Neither of these sources of information is guaranteed to be consistent with the final Generally Available version of Version 8 To quote IBM “this information will …..become progressively less correct as time goes by”! And there are already changes in DB2 that differ from this presentation!

4. Disclaimer (2) Also…. At the time this presentation was written, not all of the promised functionality had yet been delivered So, some of the examples have NOT actually been tried against a real Version 8 subsystem (And those that have been tried didn’t all work!)

5. Online schema changes • Ultimate aim – to reduce the outage necessary when making schema changes • Make everything possible with an ALTER • The next few pages show what is possible new in Version 8

6. Online schema changes • Extension of CHAR(n) column lengths • Changing within character datatypes (CHAR/VARCHAR) • Changing within numeric datatypes (SMALLINT/INTEGER/FLOAT/REAL/FLOAT DOUBLE/DECIMAL) • Changing within graphic datatypes (GRAPHIC/VARGRAPHIC) • Including support for datatype changes of columns referenced in views and indexes

7. Online schema changes • Add column(s) to existing indexes • Drop a partitioning index • Alter the clustering index • Change variable indexes PADDED/NONPADDED • Alter of identity columns • Add partition to table • Rotate partitions • Automatic partition rebalancing during REORG • Ease restrictions on indexes in restrictive states

8. Datatype Changes • ALTER TABLE table-name ALTER COLUMN column-name SET DATATYPE new-data-type • There is no impact on table availability • Tablespace is in Advisory REORg Pending (AREORP) • New data is stored in the new format • Old data is converted when it is referenced • So there will be a performance penalty • Consider a REORG as soon as possible

9. Datatype Changes • BUT when an indexed column is altered and the altered column is a numeric datatype: • Availability of the index is delayed • The sequencing of the data may be affected • The performance overhead would be too great • Index(es) are placed in ReBuilD Pending (RBDP) • However, for dynamic SQL • Deletes are allowed against the base table • Updates/Inserts are allowed provided all RBDP indexes are non-unique • Selects will not consider RBDP indexes for access paths

10. Datatype Changes • Plans/Packages and cached dynamic statements are all invalidated • All views are regenerated immediately • All plans/packages/cached dynamic statements on regenerated views are invalidated

11. Index Changes • Columns can now be added TO THE END of an existing index • However, there may be availability implications • If the column is added to the table and the index in the SAME unit of work, only AREOP is set • Otherwise RBDP is set to force a rebuild of the index • UNLESS the index is not yet defined (DEFINE NO) in which case no restrictive state is EVER set

12. Index Changes • Varying length columns in indexes can now be PADDED or NOT PADDED • For new V8 installations, the default is NOT PADDED • For migrations from V7 the default is PADDED • This is defined in ZPARM (DEFIXPD) • This alter always places the index in a pending state if there are varying columns in the index

13. Index Changes • Indexes can now have the CLUSTER attribute altered • When a new CLUSTERING index is defined with an alter, new rows are immediately ‘placed’ accordingly • Obviously old rows will not be relocated until a REORG happens

14. Versioning • All of this on-line schema stuff is handled internally by ‘versioning’ • Each ALTER that requires new data storage for an object creates a new version • However all ALTERs in the same unit of work can be amalgamated into the same new version • A tablespace can have up to 256 active versions • An index can only have 16 • Active includes all those versions currently in use AND all those in image copies in SYSCOPY • Exceeding the maximum number of versions will result in a –4702

15. Versioning • Version information is contained in the DB2 catalog • As well as in the pagesets themselves • New COPY keyword SYSTEMPAGES YES will force this information to be copied for incrementals • Be VERY careful of versions if using DSN1COPY to move data between objects/subsystems

16. Versioning • Versions can be ‘reclaimed’ in one of two ways • REORG/REBUILD/LOAD of a non-copyable index reclaims ALL versions • Otherwise MODIFY will reclaim all versions NOT currently being used by the object OR any of it’s image copies in SYSCOPY

17. Restrictions • So far so good, but none of this works for: • ROWID, DATE, TIME, TIMESTAMP or BIT DATA columns • Columns included in referential constraints • Columns with EDITPROCs ot VALIDPROCs • The column is in a table referenced in a Materialised Query Table • The column is an IDENTITY column

18. Bibliography • Keep up to date with the IBM web site • Also the Version 8 Technical Preview (SG24-6871)