How to ALTER a table using the ALTOBJ procedure
Serge Rielau
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/ondemandbusiness/conf2006/
Serge Rielau
Unlike SQL submitted normally ALTOBJ does NOT uppercase any names.
CREATE TABLE T(c1 INT);
ALTOBJ('VALIDATE', 'CREATE TABLE t(c1 smallint)', -1 , ?)
will give a -204 since it's looking for a lowercase table "t".
ALTOBJ('VALIDATE', 'CREATE TABLE T(c1 smallint)', -1 , ?)
will RENAME the column "C1" to "c1"
Unfortunate, but right now the way it is....
Ian Boyd
> refactor the schema.
i'm guessing that DB2 has early schema binding, and that's the way it is?
Are there any 3rd party GUI management tools for DB2?
> CALL SYSPROC.ALTOBJ ('VALIDATE', ..., -1, ?);
> 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
"WHERE ALTER_ID=2"
Was this a copy an paste problem, or did ALTOBJ really return 2 and 3 as the
ticket?
> 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;
We'll just take the SQL ALTOBJ spits out, and test run those until they
work; rolling back after each step.
Does Control Center not have this ability to generate the SQL?
> Lots of power comes with responsibility.
Perhaps a tool that is only 95% as powerful, doing exactly what we need but
in orders of magnitude easier. i got two guys over my shoulder who sit in
disbelief over the way we should do things.
Serge Rielau
>> Due to these hard problems we decided that ...only...human can properly
>> refactor the schema.
>
> i'm guessing that DB2 has early schema binding, and that's the way it is?
>
> Are there any 3rd party GUI management tools for DB2?
>> CALL SYSPROC.ALTOBJ ('VALIDATE', ..., -1, ?);
>
>> 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
>
> "WHERE ALTER_ID=2"
> Was this a copy an paste problem, or did ALTOBJ really return 2 and 3 as the
> ticket?
Yes, copy paste bug, the value is coming from a sequence and keep
incrementing. I was interrupted half way through.
Sure you can do it yourself.
> Does Control Center not have this ability to generate the SQL?
It does. Remember the screenshot you sent me? It has a list of all the
SQL statements it ran (with x and checkmarks). You should be able to
simply cut and paste from there.
>
>> Lots of power comes with responsibility.
>
> Perhaps a tool that is only 95% as powerful, doing exactly what we need but
> in orders of magnitude easier. i got two guys over my shoulder who sit in
> disbelief over the way we should do things.
I thought you don't have time for these times of pauses.
Note that we don't come up with these processes on our own.
We confer with our (DB2) customers. They have different requirements
from your normal environment (SQL Server).
SQL Server is traditionally used for smaller apps. DB2's heritage is
coming from bigger database, bigger apps. In the middle cultures clash.
IBM is in the solutions business the bigger, the more complicated the
better. Microsoft is in the consumer business. Completely different
philosophy.
Now, you got a contract work from a DB2 customer. Either adapt or stop
contracting for DB2 jobs. It's that simple.
Ian Boyd
> It does. Remember the screenshot you sent me? It has a list of all the SQL
> statements it ran (with x and checkmarks). You should be able to simply
> cut and paste from there.
No, that screen only appears if there was a failure. i want to see the SQL
without having to hit "OK"; because, as we saw, the potentional of hitting
"OK" and corrupting identity seeds (and perhaps other things) exists. i'd
rather only *see* it, and absoutly not touch anything.
>> Perhaps a tool that is only 95% as powerful, doing exactly what we need
>> but in orders of magnitude easier. i got two guys over my shoulder who
>> sit in disbelief over the way we should do things.
> I thought you don't have time for these times of pauses.
i'm not a manager, i don't get to make those calls. i'm sure that if you
just showed up, then suddenly they'd have the time - but you didn't hear
that from me - i clearly said we're too busy :)
> Either adapt or stop contracting for DB2 jobs. It's that simple.
If wishing made it so.
But in fairness, perhaps IBM needs a different product - one geared for
small/mid-size companies. i understand that the graphical tool uses ALTOBJ,
and ALTOBJ has all this dangerous power. Perhaps a non-advanced mode of
Control Center that generates DDL schema changes itself, and runs it in a
transaction. Walking up to DB2 today, you have to know the fairly low-level
esoteric implementation details of how it goes about making a schema
change - when all i really wanted was to change a column type. The rule of
our house today is that unless you understand all the implications, you
can't just use Control Center - because you'll end up with a corrupt
database. The average person (even DBA's employed by the customer) i'm sure
would rather not have to have the fear of god in them everytime they used
Control Center.
"Lots of power comes with responsibility".
ALTOBJ really is powerful, but i think it really should be an 'advanced'
feature - that power DBA's who love to get into every detail can use. And
because they are power users, they will know how to find it. Meanwhile, the
average person will use DB2 in "easy mode" (ghe frost-shock+windfury of
databases if you will).
The fundamental difference here (which forces a whole system design for
schema changes in DB2) is the feature of non-logged bulk table copy when the
table cannot simply be altered; (kind of like supporting
character-by-character command line tools, and having to design the entire
language around the semi-colon). SQL Server takes the safer, but slower,
approach.
An important consideration you might want to make when thinking about
features in DB2 is: i *could* perform the same non-logged bulk table copies
in SQL Server, if i had sufficient time, energy, motivation, and need to
shave minutes of DDL change times. (Granted, there is no tool to generate
the SQL steps for me, or a whole designed interface to help me recover when
something goes wrong in the non-logged operations). Nevertheless, i *could*
do it in SQL Server, but i don't *have* to do it, and what's really
important, i don't *want* to do it. When dealing with one of our databases,
and especially a customer's live database, i want it all to be in a
transaction.
i can practice the update scripts on our side all i want, but if something
does go wrong after we Email them the scripts, we won't have access to their
side to try to salvage the update it. In this case, i want, need, and
prefer, safety over speed. It's the tradeoff i want.
DB2 could have it both ways - you could have your cake and eat it too.
Serge Rielau
it's being looked at as if it were designed by madmen since I was one of
them. :-)
Anyway in DB2 9 you can do many of these changes without dropping the
table. Isn't that even better?
Ian Boyd
>it's being looked at as if it were designed by madmen since I was one of
>them. :-)
User testing is the most painful thing ever!
> Isn't that even better?
Better, although i'm still nervous making changes.
Ian Boyd
while developing. You have to checkout of source control the scripts that
can drop and re-create the entire database, copying over values and things.
And you don't just add something to a table. You check out of source control
the scripts the create that table and ensure your changes work seamlessly in
the existing script.
"Adapt, or quit using DB2."