Transcript DB2 Cobra – SQL PL Enhancements

Session: F13
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: DB2 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 DB2 9.7 for
LUW transforms the way you evolve your
database schema
DB schema Vn
DB schema Vn+1
4
Why is changing so hard?
• So far DB2 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 EmpId
Module emp with hire(), terminate() procedures
Constants MinimalWage and Workhours
Function MinimalSalary() computes salary
Trigger EmpValidateNew enforces minimal salary for
newhires
Hire()
Terminate()
EmpValidateNew
Empv
EmpId
Emp
MinimalSalary()
MinimalWage
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)
VARCHAR(20)
DECIMAL(8, 2)
SMALLINT)
NOT
NOT
NOT
NOT
NULL,
NULL,
NULL,
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
ANCHOR
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 (FP1), 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 minimalSalary() 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, ?)
SQL0438N 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
- SQL090812082112200
MINIMALSALARY
EMP_VALIDATE_NEW
EMP SQL090830105118000
HIRE
EMP SQL090830105127500
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
SQL20480W 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
SQL0206N "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.id,
ANCHOR emp.bonus
DEFAULT 4.5)
SELECT id INTO id
FROM NEW TABLE(INSERT INTO emp VALUES(NEXT VALUE FOR empid,
lastname, firstname,
salary,
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 1st 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
• DB2 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: F13
Shifting Shapes
Transforming the way you evolve your DB schema
Serge Rielau
IBM Canada
[email protected]
31