The Online Schema (R)evolution

sunfloweremryologistΔιαχείριση Δεδομένων

31 Οκτ 2013 (πριν από 3 χρόνια και 11 μήνες)

85 εμφανίσεις

The Online Schema (R)evolution

Ken McDonald


BMC Software


Written by Steven Thomas


Click to edit Master title style

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?



2

Click to edit Master title style

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

Click to edit Master title style

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



Click to edit Master title style

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


Click to edit Master title style

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

Click to edit Master title style

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


Click to edit Master title style

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


Click to edit Master title style

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







Click to edit Master title style

Changing Tablespace Types

Source: DB2 10 for z/OS Technical Overview (Redbook)

10

Click to edit Master title style

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

Click to edit Master title style

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




12

Click to edit Master title style

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)

Click to edit Master title style

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



Click to edit Master title style

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


Click to edit Master title style

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



Click to edit Master title style

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’

Click to edit Master title style

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!



Click to edit Master title style

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!

Click to edit Master title style

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


Click to edit Master title style

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


Click to edit Master title style

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





Click to edit Master title style

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




Click to edit Master title style

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


Click to edit Master title style

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


Click to edit Master title style

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


Click to edit Master title style

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

Click to edit Master title style

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



Click to edit Master title style

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


Click to edit Master title style

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




Click to edit Master title style

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

Click to edit Master title style

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


Ken Mc Donald


BMC Software

ken_mcdonald@bmc.com