473,699 Members | 2,251 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to ALTER a table using the ALTOBJ procedure

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(ROWNUM BER() OVER(ORDER BY EXEC_SEQ)),
EXEC_SEQ, SMALLINT(SQL_CO DE), 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(ROWNUM BER() OVER(ORDER BY EXEC_SEQ)),
EXEC_SEQ, SMALLINT(SQL_CO DE), 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_E RROR.

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(ROWNUM BER() 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_07451 5
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_07 4515_EXCEPTIO
15 SELECT BIGINT ( "C1" ), "C2" FROM "SRIELAU "."
16 LOAD FROM CSR20060721_074 515 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/
Jul 21 '06 #1
7 6982
I knew I'd forget something:
Unlike SQL submitted normally ALTOBJ does NOT uppercase any names.

CREATE TABLE T(c1 INT);
ALTOBJ('VALIDAT E', 'CREATE TABLE t(c1 smallint)', -1 , ?)
will give a -204 since it's looking for a lowercase table "t".
ALTOBJ('VALIDAT E', 'CREATE TABLE T(c1 smallint)', -1 , ?)
will RENAME the column "C1" to "c1"

Unfortunate, but right now the way it is....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 21 '06 #2
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?
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(ROWNUM BER() OVER(ORDER BY EXEC_SEQ)),
EXEC_SEQ, SMALLINT(SQL_CO DE), 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.
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.
Jul 21 '06 #3
Ian Boyd wrote:
>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?
Yes, there are. For example Quest TOAD.
>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.
>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(ROWNUM BER() OVER(ORDER BY EXEC_SEQ)),
EXEC_SEQ, SMALLINT(SQL_CO DE), 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.
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.
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.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 22 '06 #4
>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.
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.
Jul 24 '06 #5
I don't question that there is room for improvement. I am offended when
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?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 24 '06 #6
>I don't question that there is room for improvement. I am offended when
>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.
Jul 25 '06 #7
Oh christ. Now we have a policy that you don't just "make a change on DB2"
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."

If wishing made it so.
Jul 25 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
13559
by: M Simpson | last post by:
/* for the google index */ ALTER TABLE DEFAULT COLUMN DEFAULT VALUE I've worked out several stored procedures for altering the default column values in a table. They were compiled from books and code snippets found here. It was a pain to work out so I've decided to share my work and research here. This post is just my way of saying thanks to several others here for posting with their wisdom and intelligence.
2
21354
by: me | last post by:
I would like to add an Identity to an existing column in a table using a stored procedure then add records to the table and then remove the identity after the records have been added or something similar. here is a rough idea of what the stored procedure should do. (I do not know the syntax to accomplish this can anyone help or explain this? Thanks much, CBL
8
9727
by: David Housman | last post by:
Hi, I'm trying to write a function check if a column exists on a table, and creates it if it doesn't. The line that the query analyzer is citing is noted. It seems unhappy taking variables in the ALTER TABLE command. I can think of a hack around this, but I'm hoping there's a better way? Muchas gracias in advance :) -DJ Code follows
3
4780
by: Randy | last post by:
I am trying to change a column to NOT NULLABLE and I get the following message. =SQL GENERATED================================================================ CONNECT TO TEST; CALL SYSPROC.ALTOBJ ( 'APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE RANDMAN.ACCTADDRESS ( ACCT BIGINT NOT NULL , FY BIGINT NOT NULL , PROV CHARACTER (2) , AMT DECIMAL (8, 2) ) IN DB2_GAST_DATA ', -1, ? ); ;
2
5619
by: tojigneshshah | last post by:
Hi, In a stored procedure, If i use "alter table <tablename> activate not logged intially with empty table", i get SQL104n error. If i use "execute immediate alter table <tablename> activate not logged initially with empty table" then i get SQL0107n (saying max.length is 64 characters)
7
6279
by: db2adm1 | last post by:
I am getting the following error while trying to alter a table's column...I am trying to increase the decimal length of the column from 5 to 6 using altobj procedure. SQL0443N Routine "SYSPROC.ALTOBJ" (specific name "ALTOBJ") has returned an error SQLSTATE with diagnostic text "DBA7904, DBAD". SQLSTATE=38553 I looked up the error details, but no much help... Can anyone tell me what "DBA7904, DBAD" means? Thanks.
3
3384
by: aj | last post by:
DB2 LUW 8.2 FP14 Red Hat AS 2.1 Can I use SYSPROC.ALTOBJ() to simply rename a table that has complex dependencies (RI, functions, triggers, etc...)? Seems like all the logic is in there to tear down all the dependencies, rename the table, then build the dependencies back up again - but I don't see how to do it.. TIA
11
4097
by: raylopez99 | last post by:
Keep in mind this is my first compiled SQL program Stored Procedure (SP), copied from a book by Frasier Visual C++.NET in Visual Studio 2005 (Chap12). So far, so theory, except for one bug (feature?) below. At some point I'm sure I'll be able to laugh about this, akin to forgeting a semi-colon in C/C++, but right now it's frustrating (time to sleep on it for a while). Problem-- For some reason I get the error when trying to save files...
0
3649
temat
by: temat | last post by:
Hi i got a problem when i try to Alter Table in the procedure. I have red that i need to place keyword IMMIDIATE but it dont work in mssql. I need thede procedure to drop FOREIGN KEY, TRUNCATE and add FOREIGN KEY -- PUBLISHERS ALTER PROCEDURE clearPublishers AS BEGIN TRANSACTION ALTER TABLE dbo.Books DROP CONSTRAINT FK_Publishers_Books
0
8697
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8622
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9184
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9045
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8930
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8892
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
4634
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3062
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2359
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.