How To: ALTOBJ

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 Rielau
DB2 Solutions Development
IBM Toronto Lab

It's only fair to share...
Share on FacebookGoogle+Tweet about this on TwitterShare on LinkedIn

One thought on “How To: ALTOBJ

  1. Pingback: Rename column in DB2 v8.1 | i-Proving –

Leave a Reply