Hi all,
Following Ian's passionate postings on problems with ALTOBJ and the
alter table wizard in the control center I'll try to explain how to use
ALTOBJ with this thread. I'm not going to get into the GUI because it is
hard to describe in text.
First of all what is the purpose of ALTOBJ()?
This procedure was created mostly for ISVs who need to do produce change
scripts to upgrade application from release to release, but it can also
be used by developers during the incremental development process.
Typically I'd think that developers might prefer the GUI, but again hard
to describe this one.
The idea is that we wanted to provide a facility that allows for a
controlled schema evolution. That is when ALTOBJ succeeds there are no
DB2 objects (such as views or triggers) which may fail to "re-validate"
at a later point at first usage. we wanted to make sure that when the
table is altered all dependent objects are modified upfront, no surprises.
Changing a table by adding by renaming a column, dropping a column or
altering a data type can have a cascading effect on the remaining schema:
Columns are typically referenced by name in views, triggers and
constraints, and even if DB2 were to do "search and replace" (a job we
believe is better left to IDEs) fr changed names it would still hit a
wall on how to deal with name conflicts.
Similar hard problems arise when data types change. Most significantly
changed data types cause changes to function resolution due to
overloading rules.
When columns are dropped it is unclear what to e.g. with a unique index
using that column? Downgrade to non-unique? Drop the index?
Due to these hard problems we decided that ALTOBJ must allow for human
intervention. Only a human being (or a sophisticated IDE) can properly
refactor the schema.
ALTOBJ provides such an API than can be used by a wizard (like control
center) and IDE (like perhaps a future version of Rational App
developer) and a developer.
So.. without further delay let me introduce an example:
---
SET SCHEMA SRIELAU;
DROP TABLE T;
CREATE TABLE T (c1 INT NOT NULL GENERATED ALWAYS AS IDENTITY,
c2 FLOAT);
INSERT INTO T(c2) VALUES 10, 20, 30, 40, 50, 60, 70;
DROP VIEW V;
CREATE VIEW V AS SELECT c1, c2 FROM T;
DROP TRIGGER Trg1;
CREATE TRIGGER Trg1 BEFORE INSERT ON T REFERENCING NEW AS N FOR EACH ROW
SET n.c2 = COALESCE(n.c2, 7);
GRANT SELECT ON TABLE V TO JO;
GRANT UPDATE ON TABLE T TO JILL;
--
We have a table T which is used in a trigger and a view along with some
rows in it.
Now we want to alter the table to promote C1 from INT to BIGINT, drop
the identity property and make the column nullable. C2 is being promoted
to DOUBLE and renamed to C3.
The way how ALTOBJ works is that you give it the new CREATE TABLE statement.
CALL SYSPROC.ALTOBJ
('VALIDATE',
'CREATE TABLE T(C1 BIGINT, C3 DOUBLE NOT NULL)',
-1,
?);
We call ALTOBJ with a keyword 'VALIDATE'. This tells ALTOBJ that it
shall generate all the necessary scripting to perform the change and
roll it back if needed. When it has done that it will "rehearse" the
change script and roll it back (using a save point).
The second last argument is a "ticket". By passing it -1 on input DB2
knows that the statement we provide is new. ALTOBJ will return a
ticket-number as a handle to the script.
The last argument is for an OUT parameter to pass back a message.
Here is how the result looks like (on my system):
Value of output parameters
--------------------------
Parameter Name : ALTER_ID
Parameter Value : 3
Parameter Name : MSG
Parameter Value : SELECT OBJ_TYPE, OBJ_SCHEMA, OBJ_NAME,
SQL_OPERATION, SQL_STMT, EXEC_MODE, EXEC_SEQ FROM SYSTOOLS.ALTOBJ_INFO_V
WHERE ALTER_ID=2 AND EXEC_MODE LIKE '1_______' ORDER BY EXEC_SEQ
Return Status = 0
So we got 3 back as a ticket and DB2 passes us back a SELECT statement
in the message we can use to investigate the script DB2 ran and rolled back.
Note that NOTHING has changed in the database. This call was a no-op
besides the side-effect of filling in data into internal SYSTOOLS tables.
Now let's run a variation on the select statement that was passed back.
What I have done is to add a row numbering for readability and also
added the SQL_CODE column (retrieved from 'describe table'):
SELECT SMALLINT(ROWNUMBER() OVER(ORDER BY EXEC_SEQ)),
EXEC_SEQ, SMALLINT(SQL_CODE), SUBSTR(SQL_STMT, 1, 40)
FROM SYSTOOLS.ALTOBJ_INFO_V
WHERE ALTER_ID=3 AND EXEC_MODE LIKE '1_______'
ORDER BY EXEC_SEQ;
1 -147483650 0 DROP TRIGGER "SRIELAU"."TRG1"
2 -147483649 0 DROP VIEW "SRIELAU"."V"
3 -147483648 0 ALTER TABLE "SRIELAU "."T" ALTER COLUMN
4 -147483647 0 RENAME TABLE "SRIELAU "."T" TO T20060721
5 -147483646 0 SET SESSION AUTHORIZATION SRIELAU ALLOW
6 -147483645 0 CREATE TABLE T(C1 BIGINT, C3 DOUBLE NOT
7 0 0 SET SCHEMA SRIELAU
8 1 -206 CREATE VIEW V AS SELECT c1, c2 FROM T
9 2 0 SET SESSION AUTHORIZATION SRIELAU ALLOW
10 3 0 SET SCHEMA SRIELAU
11 4 -206 CREATE TRIGGER Trg1 BEFORE INSERT ON T R
12 5 -204 GRANT SELECT ON TABLE "SRIELAU "."V" TO
13 6 0 GRANT UPDATE ON TABLE "SRIELAU "."T" TO
14 7 0 UPDATE SYSSTAT.TABLES SET CARD=-1,
15 8 0 UPDATE SYSSTAT.COLUMNS SET COLCARD=-1,
16 9 100 UPDATE SYSSTAT.COLUMNS SET COLCARD=-1,
17 2000000003 0 SELECT BIGINT ( "C1" ), "C2" FROM "S
Note how some statements failed, or gave warnings.
The create view failed because c2 was renamed.
We have 2 likely choices:
* Propagate the name change up
* Keep the column names for V stable
Here we decide we want to propagate the name change up and deal with any
fall out from that.
So what we do now is UPDATE the view:
UPDATE SYSTOOLS.ALTOBJ_INFO_V
SET SQL_STMT = 'CREATE VIEW SRIELAU.V AS SELECT C1, C3 FROM T'
WHERE EXEC_SEQ = 1
AND ALTER_ID = 3;
While we're at it we also fix the trigger:
UPDATE SYSTOOLS.ALTOBJ_INFO_V
SET SQL_STMT = 'CREATE TRIGGER Trg1 BEFORE INSERT ON T REFERENCING NEW '
|| ' AS N FOR EACH ROW SET n.c3 = COALESCE(n.c3, 7)'
WHERE EXEC_SEQ = 4
AND ALTER_ID = 3;
The -204 on the GRANT statement is fall out from the failed CREATE VIEW.
Lets say we don't want to inherit the stats (in the latest fixpack stats
are collected by LOAD directly and the UPDATE statements will be
missing). So we simply delete them:
DELETE FROM SYSTOOLS.ALTOBJ_INFO_V
WHERE SQL_STMT LIKE '%SYSSTAT%'
AND ALTER_ID = 3;
After these modifications to the script we re-run ALTOBJ.
This time we will use the ticket-number and ommit the CREATE TABLE
statement.
CALL SYSPROC.ALTOBJ ('VALIDATE', NULL, 3, ?);
We rerun the same select as before:
SELECT SMALLINT(ROWNUMBER() OVER(ORDER BY EXEC_SEQ)),
EXEC_SEQ, SMALLINT(SQL_CODE), SUBSTR(SQL_STMT, 1, 40)
FROM SYSTOOLS.ALTOBJ_INFO_V
WHERE ALTER_ID=3 AND EXEC_MODE LIKE '1_______'
ORDER BY EXEC_SEQ;
1 -147483650 0 DROP TRIGGER "SRIELAU"."TRG1"
2 -147483649 0 DROP VIEW "SRIELAU"."V"
3 -147483648 0 ALTER TABLE "SRIELAU "."T" ALTER COLUMN
4 -147483647 0 RENAME TABLE "SRIELAU "."T" TO T20060721
5 -147483646 0 SET SESSION AUTHORIZATION SRIELAU ALLOW
6 -147483645 0 CREATE TABLE T(C1 BIGINT, C3 DOUBLE NOT
7 0 0 SET SCHEMA SRIELAU
8 1 0 CREATE VIEW SRIELAU.V AS SELECT C1, C3 F
9 2 0 SET SESSION AUTHORIZATION SRIELAU ALLOW
10 3 0 SET SCHEMA SRIELAU
11 4 0 CREATE TRIGGER Trg1 BEFORE INSERT ON T R
12 5 0 GRANT SELECT ON TABLE "SRIELAU "."V" TO
13 6 0 GRANT UPDATE ON TABLE "SRIELAU "."T" TO
14 2000000003 0 SELECT BIGINT ( "C1" ), "C2" FROM "S
No errors! We are ready to do the change.
So we call ALTOBJ again, but this time in APPLY_STOP_ON_ERROR.
CALL SYSPROC.ALTOBJ ('APPLY_STOP_ON_ERROR', NULL, 3, ?);
The SELECT statement ALTOBJ returned this time has changed.
Note the different LIKE predicate.
SELECT SMALLINT(ROWNUMBER() OVER(ORDER BY EXEC_SEQ)),
SUBSTR(SQL_STMT, 1, 50)
FROM SYSTOOLS.ALTOBJ_INFO_V
WHERE ALTER_ID=3 AND EXEC_MODE LIKE '_1______'
ORDER BY EXEC_SEQ;
1 DROP TRIGGER "SRIELAU"."TRG1"
2 DROP VIEW "SRIELAU"."V"
3 ALTER TABLE "SRIELAU "."T" ALTER COLUMN "C1" DROP
4 RENAME TABLE "SRIELAU "."T" TO T20060721_074515
5 SET SESSION AUTHORIZATION SRIELAU ALLOW ADMINISTR
6 CREATE TABLE T(C1 BIGINT, C3 DOUBLE NOT NULL)
7 SET SCHEMA SRIELAU
8 CREATE VIEW SRIELAU.V AS SELECT C1, C3 FROM T
9 SET SESSION AUTHORIZATION SRIELAU ALLOW ADMINISTR
10 SET SCHEMA SRIELAU
11 CREATE TRIGGER Trg1 BEFORE INSERT ON T REFERENCING
12 GRANT SELECT ON TABLE "SRIELAU "."V" TO USER "JO
13 GRANT UPDATE ON TABLE "SRIELAU "."T" TO USER "JIL
14 CREATE TABLE "SRIELAU "."T20060721_074515_EXCEPTIO
15 SELECT BIGINT ( "C1" ), "C2" FROM "SRIELAU "."
16 LOAD FROM CSR20060721_074515 OF CURSOR MESSAGES "
17 SET INTEGRITY FOR "SRIELAU "."T" IMMEDIATE CHECKED
Due to the LOAD this call will NOT be transactional.
If we get errors (run out of disk space perhaps) we need fix that by
calling ALTOBJ with 'UNDO' mode.
CALL SYSPROC.ALTOBJ ('UNDO', NULL, 3, ?);
As expected in this case we did not get errors and the table has been
changed, all dependent objects are up and well.
As an ISV preparing for an application upgrade you can now go and grab
the script above and incorporate it into you installer.
There is one last thing to do and that is cleanup:
CALL SYSPROC.ALTOBJ ('FINISH', NULL, 3, ?);
This call will drop the backup table and all the undo and redo scripts
held in the SYSTOOLS schema for this ticket number.
As long as you follow this recipe ALTOBJ() should work great.
If you start overwriting ticket numbers such as recalling with -1 then
at least the SYSTOOLS schema will start wasting space. In the worst case
(when running APPLY mode you will end up with residual backup tables
etc, etc.
Lots of power comes with responsibility.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/