Online Schema Evolution in DB2 for z/OS
Steve Thomas, CA Technologies
Session Code: 4086
Thursday, September 17th at 13:15 Platform: DB2 for z/OS
Agenda
• Changing Schemas Online before DB2 10
• Introducing Deferred ALTER in DB2 10 for z/OS
• Deferred ALTER in more detail
• Highlighting some of the less obvious side effects
• Items that change in DB2 11 will be highlighted in Green
• What has been added in DB2 11 for z/OS
• What’s still missing?
2
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
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
• 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
• 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
4
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
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 – unavailable index or missing statistics
• Does not invoke the DB2 Plan Stability feature if you’re using it
• 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
6
Add REBIND explicitly to any activity
that invalidates packages
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
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
8
So what can you change (using DB2 10 for z/OS)?
• 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) 10
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!
• Suggestion – Run immediate changes first. It could save you a REORG
• Must be using Single Table Tablespaces
• Requires UTS unless converting to a UTS
• Primary exception is changing Pagesize for LOB Tablespaces
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
12
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)
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 a different 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
14
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 in DB2 10!
• Run a REORG to remove this
• or REPAIR ... NOAREORPEND - not recommended
• AREOR is reset in DB2 11 unless the table is being converted to Hash
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
• Note these can also reset REORP in DB2 10
• No Recovery to point before changes materialized in DB2 10
• You can UNLOAD from old Copies
• Note that the REORG will generate an Imagecopy
• See later for significant changes in this area added by DB2 11
16
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 using ICTYPE = ‘A’
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!
18
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!
Example: 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
20
Example: 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
Example: 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
22
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
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
24
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
DB2 11 for z/OS – Improved Recovery Options
• Probably the biggest change is to enable RECOVER to a PiT prior to the materializing REORG of a Schema Change
• Numerous restrictions
• Read Manuals for full details
• Mainly apply to LOB and XML Auxiliary spaces
• Also some restrictions on PBG objects if the Reorg pruned partitions
• Materializing REORG must be run in DB2 11 NFM
• This means most changes made while in DB2 10 won’t be supported
26
What happens?
• Object is placed into a restrictive state – REORP
• Required as Recover will back out materialization of the Change
• Run a REORG afterwards to remove this
• Reinstates materialization of the Online Change
• Must be on the entire Tablespace
• Use SHRLEVEL REFERENCE or CHANGE (gets converted to REFERENCE)
• Many restrictions while the Object is in REORP status
• DDL is highly restricted – even DROP TABLE is not allowed
• Very restricted ability to run Utilities
• REORG, Some RECOVER, REPORT RECOVERY and REPAIR DBD
• Basic rule is to run the REORG immediately after the RECOVER
More details
• Rows are added to several Catalog Tables by the RECOVER
• SYSCOPY Tells DB2 what has happened
• SYSOBDS Added in DB2 8 and documented in Diagnosis Guide
• SYSPENDINGDDL Reinstates the Pending change
• This row cannot be removed with ALTER …. DROP PENDING CHANGES
• Significant complications with LOB and AUX objects
• Read the Manuals for details otherwise I could use another hour!
• Report Recovery does not tell you whether a Recovery is possible
• You need to examine SYSCOPY rows to determine this
28
Other DB2 11 changes
• ALTER TABLE… DROP COLUMN
• Cannot be any Dependent objects on the Column
• No Recovery prior to the Materializing REORG
• ALTER Limit Keys is now a Deferred Change
• Only for UTS PBR and Table Controlled Range Partitioned objects
• Still restrictive change for Index Controlled Objects
• Some new DSNZPARMs (added by PM89655) prevent this happen
• Also allows you to prevent creating new Index-Controlled objects
• ALTER the Partitioning Index to NOT CLUSTER and then immediately back to CLUSTER to get the object into Table controlled Partitioning
• Note LOAD REPLACE does not implement new limit keys
• RUNSTATS… RESET ACCESS PATH
Schema Items that still cannot be changed
• ALIAS
• AUXILIARY TABLE
• Including Drop – handled via Base Table
• DISTINCT TYPE
• GLOBAL TEMPORARY TABLE
• ROLE
• SYNONYM
• TRIGGER
• There is an ALTER TRIGGER but it only allows (NOT) SECURED
• VIEW
• There is an ALTER VIEW but it only allows REGENERATE
30
Wouldn’t it be nice if we could....?
• Implement a change that required a combination of immediate and deferred alters at the same time
• Reorder columns
• Used to say Remove Columns but that was added in DB2 11
