1 / 33

The Online Schema (R)evolution

The Online Schema (R)evolution. Ken McDonald BMC Software Written by Steven Thomas. 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

badu
Download Presentation

The Online Schema (R)evolution

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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 Donald BMC Softwareken_mcdonald@bmc.com

More Related