The Online Schema (R)evolution

1. The Online Schema (R)evolution Ken McDonald BMC Software Written by Steven Thomas

2. Agenda • Changing Schemas Online before DB2 10 • Introducing Deferred ALTER in DB2 10 for z/OS • Deferred ALTER in more detail • Describe each type of change using new feature • Including highlighting some of the less obvious side effects • What’s still missing?

3. What is OLS or Online Schema Change? “The ability to alter characteristics of DB2 objects online, without the need for an Unload, Drop, Create & Load” • Before V8 most object changes required the following: • Analyze the impact of the changes; resolve all conflicts; extract object definitions and retain authorizations • Unload the data • Drop & Recreate the object with the new definitions • Re-create dependent objects such as indexes, views and triggers • Re-establish authorizations for the newly created objects • Reload the data; copy, collect statistics, check RI as needed • Rebind packages and plans

4. Every ALTER has an Impact • Even Immediate changes require exclusive use of the Catalog & Directory and can have a performance impact • DB2 10 Catalog changes will help here – see session A17 • AREO* Status should set alarm bells ringing • Some require a Utility (usually REORG) to implement • e.g. Alter Tablespace COMPRESS YES (DB2 9) • Others make the object inaccessible until action taken • e.g. Changing PIECESIZE places Index into PSRBD • Another example for DB2 10 as we’ll see is converting to Hash • A few even require objects to be stopped • e.g. Changing Bufferpool for object when Data Sharing (DB2 9) • I’m not aware of any of these in DB2 10 for z/OS

5. Even if an ALTER is effective immediately • Need to Synchronize Application Code Changes • Data Type changes can often be accommodated (temporarily) • New data types have to be compatible and large enough • Some Numeric changes more difficult due to Indexes • Renamed columns and tables usually need code changes • OLS Changes may impact other objects • Plans, Packages and Dynamic Statement Cache • Dependent Views • Referential Integrity • Check Constraints or other methods of Data Validation • Triggers • Stored Procedures, UDFs & UDTs

6. Package Invalidation & Auto Rebind • Occurs when changing items referenced in Static SQL • The next invocation causes an Auto Rebind to occur • Dynamic SQL in Statement Cache invalidated • Care required with Auto Rebind • Access path can switch – the index may be unavailable • Some Statistics may get invalidated and need to be refreshed • You only get one shot at a Rebind • If it fails you get an INOPERATIVE Plan or Package • Watch for timeouts in Catalog after a change to a busy object Add REBIND explicitly to any activity that invalidates packages

7. Deferred ALTER in DB2 10 for z/OS • ALTER Statement Validated & Authorization is checked • Change registered in a new Catalog Table • SYSIBM.SYSPENDINGDDL • Tablespace goes into new AREOR State (Advisory) • Non-restrictive state indicating Changes are Pending • ALTER statement returns SQLCODE +610 • Regular DML activity continues as normal • Also get +610 when a more restrictive Pending state set • Such as RBDP or REORP

8. Deferred ALTER in DB2 10 for z/OS • Changes implemented by next REORG • SHRLEVEL REFERENCE and CHANGE only • SHRLEVEL NONE allowed but does not implement changes • REORG can be at Tablespace or Index level • REORG TABLESPACE also implements any Index Changes • Almost all changes require UTS objects • Except for migration of objects to UTS • Option to cancel any Pending changes available • ALTER TABLESPACE ... DROP PENDING CHANGES

9. So what can you change? • Convert older style structures to UTS • Convert Single Table Segmented TS => UTS PBG • Convert Single Table Simple TS => UTS PBG • Convert Classic Partitioned TS => UTS PBR • Change the following Tablespace Attributes: • DSSIZE • SEGSIZE • MEMBER CLUSTER • Page Size via Bufferpool (Tablespace and Index) • Was available for Indexes in DB2 9 but went into RDBP • Convert Tablespaces to and from HASH access

10. Changing Tablespace Types Source: DB2 10 for z/OS Technical Overview (Redbook)

11. Restrictions • Can’t mix Pending and Immediate ALTER in single SQL • SQLCODE -20385 • Many immediate ALTERS are not possible while changes are pending • Also SQLCODE -20385 • See Manuals for details – almost 2 pages of restrictions! • Generally safer to materialize the Pending Change first • Must be using Single Table Tablespaces • Requires UTS unless converting to a UTS • Primary exception is changing Pagesize for LOB Tablespaces

12. How changes are registered • One or more rows added to SYSIBM.SYSPENDINGDDL • Each Pending option has one row • Even if defined in the same SQL statement • Some changes can only be specified one at a time • DSSIZE, SEGSIZE and DROP PENDING CHANGES • MAXPARTITIONS & BUFFERPOOL allowed with other changes • But you can’t mix Immediate and Pending anyway • Rows are recorded and applied in the order executed • For example, conversion to UTS must take place before one of the other changes such as DSSIZE or SEGSIZE

13. SYSIBM.SYSPENDINGDDL • DBNAME, TSNAME, DBID, PSID, OBJSCHEMA, OBJNAME, OBJOBID, OBJTYPE Define the object • STATEMENT_TYPE Currently always ‘A’ for ALTER • OPTION_KEYWORD Keyword of Pending Option • (e.g. DSSIZE or SEGSIZE) • OPTION_VALUE Value of new option • OPTION_SEQNO Sequence in Statement • CREATED_TS Timestamp • ROWID ROWID for Text Column • STATEMENT_TEXT Original Statement (2Mb CLOB)

14. Some points to be aware of... • Some changes can be Immediate or Deferred • e.g. ALTER TABLESPACE.... BP is Immediate unless • The New Bufferpool uses the same Page size OR • There’s already a Deferred Action on the Tablespace • Some changes can cause different restrictive states depending upon how Base Tablespace is defined • Changing Index Pagesize causes AREOR if the base object is a UTS but RBDP if it isn’t • Both cause an SQLCODE +610 • Could have a huge impact • No way to tell the difference without a DISPLAY! • Changes on non-materialized objects are Immediate

15. Dropping Pending Changes • ALTER TABLESPACE .... DROP PENDING CHANGES • No equivalent for Indexes • Dropping Pending Changes for Base Tablespace drops them • Cannot Select what to drop – all or nothing • One thing I discovered that really surprised me: • Dropping Pending Changes does NOT remove AREOR! • Run a REORG to remove this • or REPAIR ... NOAREORPEND - not recommended

16. Implementing the Change • All outstanding deferred ALTERS for an objects are implemented by a single REORG of TS or IX • Tablespace REORG also implements Index changes • This includes a migration to UTS with other changes • Not completely clear from Manuals but I’ve tested it! • The change to UTS must come first if you have one • Must use SHRLEVEL CHANGE or REFERENCE • And FASTSWITCH YES • Note these can also reset REORP in DB2 10 • No Recovery to point before the changes materialized • You can UNLOAD from old Copies • Note that the REORG will generate an Imagecopy

17. What happens at Materialization? • Any existing Statistics are invalidated • REORG collects basic Statistics during Execution • Default is TABLE ALL INDEX ALL HISTORY ALL • No COLGROUP, KEYCARD, HISTOGRAM, Extended Indexes or frequency Statistics where NUMCOLS>1 are collected • This causes a RC=4 in the REORG • Dependent Plans and Packages are Invalidated • You will get one shot at an AUTOREBIND • Regenerates Dependent Views • Removes the rows from SYSPENDINGDDL • Adds new entries into SYSCOPY • ITYPE = ‘A’ and STYPE = ‘F’, ‘D’, ‘S’ or ‘M’

18. Partition Growth • It’s possible that a UTS PBG object will require additional partitions when the REORG is executed • For example, reducing SEGSIZE may result in more Free Pages • New partitions will be automatically created as required • Including XML and LOB Auxiliary Objects • Regardless of the setting of the SQLRULES option • SQLRULES(DB2) or SQLRULES(STD) • Remember to check your Backup Strategy if this happens • Hopefully you use LISTDEF & TEMPLATE which pick this up automatically but worth checking!

19. SYSIBM.SYSPENDINGOBJECTS • Used by REORG to Store information about any new LOB or XML objects that need to be created • Generally caused by Partition Growth • Stores information about the name and OBID and PSID that are to be used • Hopefully this table will usually be empty in your shop!

20. Changing Simple/Segmented TS to PBG • ALTER TABLESPACE... MAXPARTITIONS n • Can also change MAXPARTITIONS for a PBG UTS • Changes made when Materialized: • SYSTABLESPACE column TYPE = ‘G’ • SEGSIZE inherited if present but set to at least 32 • DSSIZE set to 4 • MEMBER CLUSTER inherited • If LOCKSIZE was TABLE it is changed to TABLESPACE • Partition Growth may occur on implementation

21. Changing Table Controlled Partitioned to PBR • ALTER TABLESPACE... SEGSIZE n • Object must be Table Controlled not Index Controlled • Can also change SEGSIZE of any UTS object this way • Must be the only option specified on the ALTER • OK to issue multiple ALTERS and implement together • Changes made when Materialized: • SYSTABLESPACE column TYPE = ‘R’ • MEMBER CLUSTER inherited • Number of Partitions is inherited • FREEPAGE adjusted to 1 below SEGIZE if necessary • May result in partition growth on implementation • If DSSIZE = 0 then it is reset to the maximum allowed

22. Changing Pagesize of a UTS Tablespace • ALTER TABLESPACE... BUFFERPOOL BPxxx • Change of page size only supported for UTS & LOB • Not supported for XML objects • Can use Bufferpool with the same page size for non-UTS • Bufferpool must be valid and defined (VPSIZE > 0) • Datasets required may increase or decrease • Empty Partitions are not removed – allocated empty • Immediate change if the Pagesize is identical • Exception if Conversion to UTS is pending • Implemented at next dataset Open (usually a START) • In DB2 10 you no longer need to Stop objects across the Group prior to changing the Bufferpool in a Data Sharing Environment

23. Changing Pagesize of an Index • ALTER INDEX... BUFFERPOOL BPxxx • Supported in DB2 9 but object placed into RBDP • Improved in DB2 10 for Indexes based on UTS objects • For Standard indexes and those based on Auxiliary (LOB) or XML tables defined on base UTS objects • These are treated as Deferred ALTERs • All others remain as they were using RBDP status • You get SQLCODE +610 either way

24. Changing DSSIZE of a UTS object • ALTER TABLESPACE ... DSSIZE n • Change of page size only supported for UTS & LOB • Must be the only option specified on the ALTER • DSSIZE must be valid • Based on Page Size and Number of Partitions • Decreasing DSSIZE may result in Partition Growth

25. Setting MEMBER CLUSTER for a UTS object • ALTER TABLESPACE ... MEMBER CLUSTER YES • Member Cluster was not supported for UTS in DB2 9 • This feature allows you to add it if necessary using a Deferred Alteration • Similar to the rest and only added here for completeness

26. Converting Tables to Hash organization • ALTER TABLE ... ADD ORGANIZE BY HASH UNIQUE (col,col...) HASH SPACE nnnnn • Also amend Hash objects using the following: • ALTER TABLE ... ALTER ORGANIZATION SET HASH SPACE nnnnn • Restrictions: • Column(s) must be defined as NOT NULL • T able cannot be APPEND(YES) or MEMBER CLUSTER • Tablespace must be UTS • Table cannot be a Global Temporary Table

27. Converting to Hash • When you issue this SQL • SQLCODE +610 as normal • Creates the Overflow Index which is placed into PSRBD • Object in placed in AREOR • This situation only allows UPDATE & DELETE statements • No INSERT due to the Dependent Index in PSRBD • Even though the Hash organization is not being used! • Correct by Rebuilding Index or REORG Tablespace • With REBUILD then INSERT is allowed but TS will still not be Hash

28. Implementing Hash Organization • REORG required to Implement Hash organization • Care required with AUTOESTSPACE if adding more data • Estimates the size required for the Hash Space • If you expect to add more data you may get more use of the overflow space than you want for good performance • Unique Index on Hash Key still exists but is redundant • You can Drop this whenever you wish

29. Removing Hash Organization • ALTER TABLE ... DROP ORGANIZATION • Reverts Table back to regular UTS PBR or PBG • This places object into REORP which is restrictive! • Also drops the Hash Overflow Index when executed • If you don’t have a Unique Key you might want to add one before you run the REORG to implement • Otherwise there will be no way check for duplicate rows

30. Immediate ALTERs added in DB2 10 for z/OS • LOB Inline length and default • VERSIONING • ACCESS CONTROL • MASK & PERMISSION • TRIGGER and FUNCTION – SECURED attribute • TIMESTAMP Precision and Timezone • BUFFERPOOL PGSTEAL NONE (in memory objects) • MAXPARTITIONS for PBG objects • INDEX include columns

31. Schema Items that still cannot be changed • ALIAS • AUXILIARY TABLE • Including Drop – handled via Base Table • DISTINCT TYPE • GLOBAL TEMPORARY TABLE • SYNONYM • TRIGGER • There is an ALTER TRIGGER but it only allows (NOT) SECURED • VIEW • There is an ALTER VIEW but it only allows REGENERATE

32. Wouldn’t it be nice if we could....? • Alter Table Columns and other common schema changes using a Deferred Syntax in advance • Reorder and Remove columns • And have them implemented by a REORG when we can plan our Binds and Stats in a more controlled fashion • Please bear in mind I don’t work for IBM and have no access to future plans so this wish list should in no way be taken as an indication of what’s likely to appear in a future release of DB2 

33. Ken Mc DonaldBMC Softwareken_mcdonald@bmc.com