Session F 13 Shifting Shapes Transforming the way

Session: F 13 Shifting Shapes Transforming the way you evolve your DB schema Serge Rielau IBM Canada Oct 7, 2009 • 3: 45 p. m. – 4: 45 p. m. Platform: DB 2 for LUW

Agenda • Motivation • Task driven feature introduction • Anchoring • Auto revalidation • Forced revalidation • Object replacement • Defaults • Alter table • Recap • Conclusion 2

The Problem • As business needs change, applications evolve to keep up • As the applications evolve, databases schemas must also evolve: • Columns are added, removed, or change data type • View, routine and trigger text changes • Tables are decomposed • In practice, these “simple” changes are not simple at all • Requires a carefully planned change process to manage the impacts on dependent objects Chardin, Jean-Baptiste Siméon The House of Cards 3

Objective • To describe and demonstrate how DB 2 9. 7 for LUW transforms the way you evolve your database schema DB schema Vn+1 4

Why is changing so hard? • So far DB 2 insisted that database objects be in a consistent state at all times • Actions that might affect objects that depend on the object being changed are either: • Restricted (the change fails), or • Cause those dependent objects to be dropped! (CASCADE) • Real world databases have many of these dependent objects (views, functions, triggers, etc. ) • There are some common change tasks that could be simplified • Some dependencies are implied rather than described 5

A sample schema • • • Table Emp and view Empv Sequence Emp. Id Module emp with hire(), terminate() procedures Constants Minimal. Wage and Workhours Function Minimal. Salary() computes salary Trigger Emp. Validate. New enforces minimal salary for newhires Hire() Terminate() Emp. Validate. New Empv Emp. Id Emp Minimal. Salary() Minimal. Wage Workhours 6

Tasks/Problems 1. Increasing last name domain § Change base table column type § Keep derived variables and parameters in synch 2. Increase minimal wage § Recreate variable § Manage dependencies up to the trigger 3. Change salary data type § Reorg needed? § Change ripples through many places 4. Running DDL scripts § Determine order of creation 5. Adding a bonus parameter to a procedures § Deal with numerous invokers 6. Adding a column to a view the empv view § Deal with long running queries 7

Introducing anchored types • Problem • How to keep data types in synchronized • Solution • Distinct types • Requires strong typing • Has never really become popular in SQL • Anchored types • Type variable/parameter based on another object When “root” object changes, so does derived one • Popular with some other DBMS • Can anchor to scalar types and rows 8

CREATE TABLE emp(id lastname firstname salary deptid SMALLINT VARCHAR(20) DECIMAL(8, 2) SMALLINT) NOT NOT NULL, CREATE OR REPLACE VIEW empv(id, lastname, firstname, salary, deptid) AS SELECT id, lastname, firstname, salary, deptid FROM emp CREATE OR REPLACE SEQUENCE empid AS SMALLINT CREATE OR REPLACE MODULE emp ALTER MODULE emp PUBLISH PROCEDURE hire(IN lastname IN firstname IN salary IN deptid OUT id SELECT empid INTO id FROM NEW TABLE(INSERT INTO empv VALUES(NEXT VALUE lastname, firstname, salary, deptid)) ANCHOR ANCHOR emp. lastname, emp. firstname, emp. salary, emp. deptid, emp. id) FOR empid, ALTER MODULE emp PUBLISH PROCEDURE terminate(IN id ANCHOR emp. id) DELETE FROM vemp WHERE emp. id = terminate. id; 9

Increasing domain of lastname • Increase length from 20 to 30 bytes ALTER TABLE emp ALTER COLUMN lastname SET DATA TYPE VARCHAR(30) • Dependent object Hire() gets invalidated SELECT valid FROM SYSCAT. ROUTINES WHERE routinename = 'HIRE' VALID N • Automatic revalidation on first usage CALL emp. hire('Jones', 'Joe', 40000, 2, ? ) Value of output parameters Parameter Name : ID Parameter Value : 1 SELECT valid FROM SYSCAT. ROUTINES WHERE routinename = 'HIRE' VALID Y 10

Introducing object replacement • Problem • Managing conditional dropping of previous versions of an object in DDL script • Preserve previously granted access to objects being re-created • Solution • CREATE OR REPLACE • If object exists drop it first • If objects exists preserve all existing authorizations • Supported for: Functions, procedures, triggers, types (FP 1), sequences, aliases, views, variables 11

Original schema CREATE VARIABLE minimal_wage DECIMAL(4, 2) CONSTANT 7. 50 CREATE VARIABLE workhours SMALLINT CONSTANT 40 CREATE FUNCTION minimal. Salary() RETURNS salary ANCHOR emp. salary RETURN minimal_wage * workhours * 52 CREATE TRIGGER emp_validate_new BEFORE INSERT ON emp REFERENCING NEW AS N FOR EACH ROW BEGIN IF salary < minimalsalary() THEN SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'Salary below legal minimum'; END IF; END • Trigger uses function, uses variable. . 12

Increase minimal wage • Replace the wage constant CREATE OR REPLACE VARIABLE minimal_wage DECIMAL(4, 2) CONSTANT 8. 50 • Function is invalid, trigger remains untouched SELECT valid FROM SYSCAT. ROUTINES WHERE routinename = 'MINIMALSALARY’ VALID N SELECT valid FROM SYSCAT. TRIGGERS WHERE tabname = 'EMP’ VALID Y • Auto revalidation on usage CALL emp. hire('Smith', 'Henry', 10000, 2, ? ) SQL 0438 N Application raised error or warning with diagnostic text: "Salary below legal minimum". SQLSTATE=78000 13

Introducing a “make me whole” routine • Problem • Some table changes place table in reorg pending, • … others do not. • Revalidation of objects on first use can be too late • Solution • Procedure that “does what needs to be done” • To a base object (table, view, routine, variable, …) • A schema • A module 14

Inflation proof salary • Change salary column type to DECFLOAT ALTER TABLE emp ALTER COLUMN salary SET DATA TYPE DECFLOAT(34) • Various objects are not valid anymore SELECT reorg_pending FROM SYSIBMADM. ADMINTABINFO WHERE TABNAME = 'EMP' REORG_PENDING Y SELECT OBJECTMODULENAME, OBJECTNAME, ROUTINENAME FROM SYSCAT. INVALIDOBJECTS OBJECTMODULENAME OBJECTNAME ROUTINENAME - SQL 090812082112200 MINIMALSALARY EMP_VALIDATE_NEW EMP SQL 090830105118000 HIRE EMP SQL 090830105127500 TERMINATE 15

. . now fix it • Fix all objects in my schema CALL ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>'MY_SCHEMA') SELECT reorg_pending FROM SYSIBMADM. ADMINTABINFO WHERE TABNAME = 'EMP' REORG_PENDING N SELECT OBJECTMODULENAME, OBJECTNAME, ROUTINENAME FROM SYSCAT. INVALIDOBJECTS OBJECTMODULENAME OBJECTNAME ROUTINENAME 0 record(s) selected. • Schema sure to be compiled CALL emp. hire('Kennedy', 'Ken', 55000, 3, ? ) Value of output parameters Parameter Name : ID Parameter Value : 5 16

Introducing “create (with error)” • Problem • Scripts creating objects out of order • Required objects missing • Required authorization to available • Linearizing object creation not trivial or confusing • Solution • Tolerate certain kinds of error • Authorization • Ambiguous name • Object does not exist • Store invalid object in the catalog • Validate on first use or via revalidation procedure 17

Adding bonus to the mix • Add a bonus procedure without a bonus column ALTER MODULE emp PUBLISH PROCEDURE paybonus(IN id ANCHOR emp. id, IN percent DEC(4, 2)) UPDATE empv SET bonus = salary * percent / 100 WHERE emp. id = paybonus. id SQL 20480 W The newly defined object "SRIELAU. EMP. PAYBONUS" is marked as invalid because it references an object "BONUS" which is not defined or is invalid, or the definer does not have privilege to access it. SELECT ERRORMESSAGE FROM SYSCAT. INVALIDOBJECTS WHERE ROUTINENAME = 'PAYBONUS' ERRORMESSAGE SQL 0206 N "BONUS" is not valid in the context where it is used. 18

…adding bonus, really now! • Add bonus to the employee view (also out of order) CREATE OR REPLACE VIEW empv (id, lastname, firstname, bonus, salary, deptid) AS SELECT id, lastname, firstname, salary, bonus, deptid FROM emp • Finally add bonus to the employee table ALTER TABLE emp ADD column BONUS DECFLOAT • The procedure and view fix themselves on first use CALL emp. paybonus(5, 8. 5) Return Status = 0 • Note UPDATE DB CFG USING AUTO_REVAL DEFERRED_FORCE required for “create (with error)” • Supports • Views, procedures, functions and triggers 19

Introducing parameter defaulting • Problem • Adding parameters to procedures required patches to all callers • Procedures with many parameters get unwieldy • Solution • Allow DEFAULT clause for procedures in the same way as for columns (but more powerful expressions) • Note • No defaults for function parameters yet • Defaults must be at the end • Also see parameter naming 20

Bonus for all!!! • Define default bonus for new hires ALTER MODULE emp DROP PROCEDURE hire ALTER MODULE emp PUBLISH PROCEDURE hire(IN lastname IN firstname IN salary IN deptid OUT id IN bonus ANCHOR emp. lastname, ANCHOR emp. firstname, ANCHOR emp. salary, ANCHOR emp. deptid, ANCHOR emp. bonus DEFAULT 4. 5) SELECT id INTO id FROM NEW TABLE(INSERT INTO emp VALUES(NEXT VALUE FOR empid, lastname, firstname, salary * bonus / 100, deptid) • Original invocation still works CALL emp. hire('Miller', 'Brian', 55000, 3, ? ) Value of output parameters Parameter Name : ID Parameter Value : 6 21

Introducing “soft invalidation” • Problem • Long running transactions or queries cause partial application outages when performing DDL changes # Processing connections A B Time VIEW DDL Issue DDL Process Commit 22

Introducing “soft invalidation” (cont) • Solution • Allow running transactions to drain asynchronously # Processing connections A B Time VIEW DDL Issue DDL = Process Commit 23

Extend common view • Add initials without impeding running transactions CREATE OR REPLACE VIEW empv (id, lastname, firstname, initials, bonus, salary, deptid) AS SELECT id, lastname, firstname, substr(firstname, 1, 1) || '. ' || substr(lastname, 1, 1) || '. ', salary, bonus, deptid FROM emp • Also works for: • Inline triggers • Inline functions • Drop and/or create or “create or replace” works • Can even replace view with table! 24

Recap: Revalidation • Database configuration AUTO_REVAL • DISABLE maintains previous version behavior • IMMEDIATE revalidates when marked invalid • DEFERRED Revalidate on first use • DEFERRED_FORCE allows “create (with error)” • Deferred object revalidation options • invoke admin_revalidate_db_objects() procedure • test the object yourself • wait for next use (risks compile error for 1 st user) 25

Recap: CREATE OR REPLACE • Supported for alias, function, nickname, procedure sequence, trigger, variable, and view • If object already exists, replace it • Not exactly the same as DROP and CREATE • A replaced object retains • granted privileges • dependencies with other objects • Together with auto-revalidation, significantly less manual effort required when replacing an object (especially a view or routine used extensively) 26

Recap: ALTER TABLE • More support • Any type to any other castable type E. g. from integer -> char ALTER TABLE emp ALTER deptid SET DATA TYPE CHAR(30) • When “down casting” table is prechecked on ALTER • RENAME column support • Unlimited alterations per transaction • Remember • Let admin_revalidate_db_objects() decide whether reorg is needed or not 27

Evolving your schema Best Practices • Pull together all the DDL changes required as a single task • Minimize the number of transactions • especially using ALTER TABLE changing row versions • Last step is to run validation procedure • Avoid late surprises from revalidation errors • Rehearse on non-production database (if possible) • If changes are significant, process at low-usage time or in maintenance window (if possible) 28

Conclusion • DB 2 9. 7 transforms the way you manage schemas • Schema evolution features include: • Relaxed dependencies with automatic revalidation • Extended ALTER TABLE features • Creating objects out of order • Anchored data types • Soft invalidation to maximize uptime • The features described are intended to: • Reduce administration to maintain the schema • Minimize planned outage windows • Provide for in flight “surgical tweaks” of the schema 29

Q&A • ? 30

Session: F 13 Shifting Shapes Transforming the way you evolve your DB schema Serge Rielau IBM Canada srielau@ca. ibm. com 31