HOWTO: Make DDL changes?

Ian Boyd

Every time during development we had to make table changes, we use Control
Center. Most of the time, Control Center fails. If you try to "undo all", it
doesn't, and you end up losing your identity seed, or your constraints, or
your triggers, or your table. Talking to developers at other companies who
have had the misfortune of using DB2, they are adamant that you cannot use
the tools; they are buggy and you just have to resign yourself to doing
everything on the command line.

Some of you might argue that if Control Center presents an error while
trying to alter a table, you should just diagnose the error, fix it, and
continue. Problem is, we don't know what the table is; nor will we ever
know. But the fact that "Undo All" doesn't actually Undo All, but instead is
a "you broke it, but run away and pretend you never touched it" mode. Even
more to the point is that the customer's MIS department isn't going to know
what the error is or how to fix it, and with the very real possibility that
identity seeds, constraints, triggers or data will be lost on the live
system: we would rather they (nor anybody) use Control Center for table
structure changes. So we're stuck with trying to alter tables using DB2-SQL.


The first step is to get the DB2-SQL that control center generates to effect
the change. We should be able to take that, fix it, and run it ourselves
inside a transaction so that it either all works or all fails - data
integrity maintained. Normally we see the detailed 15 or 20 step SQL
statement when Control Center fails. In this case, however, we want to see
the DB2-SQL beforehand. You would think this would be no problem, just click
"Show SQL". Unfortunatly, no:

CALL SYSPROC.ALTOBJ ( 'APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE
SUPERDUDE.PATRONS ( ... ) IN USERSPACE1 ', -1, ? );

Damn. No good there. You might try just running this SQL from a command
line, but it requires two output parameters; you cannot declare variables in
DB2-SQL. We can trick DB2 into letting us declare variables by creating a
dummy stored procedure:

CREATE PROCEDURE foo()
DYNAMIC RESULT SETS 1
LANGUAGE SQL
P1: BEGIN

DECLARE msg VARCHAR(2048);
DECLARE alter_id INTEGER;

SET alter_id = -1;

CALL SYSPROC.ALTOBJ ( 'APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE
SUPERDUDE.PATRONS ( ... ) IN USERSPACE1 ', alter_id, msg );

END P1 ~

CALL foo()~

(NOTE: ~ is the statement separator, since ; is a special token in DB2-SQL)

Of course, this fails with errors, sometimes, perhaps, depending on the time
of day and the machine you run it from.

Error: SQL0443N Routine "ALTOBJ" (specific name "") has returned an error
SQLSTATE with diagnostic text "SQL0668 Reason code or token:
3|SUPERDUDE.PATRONS ". SQLSTATE=38553
(State:38553, Native Code: FFFFFE45)


For the hell of it, i also tried a small dummy table:

DROP TABLE TestFoo~
CREATE TABLE TestFoo (
PatronID int NOT NULL,
PatronName varchar(25) NOT NULL)~

DROP PROCEDURE foo~
CREATE PROCEDURE foo()
DYNAMIC RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
DECLARE msg VARCHAR(2048);
DECLARE alter_id INTEGER;
SET alter_id = -1;

CALL SYSPROC.ALTOBJ ( 'APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE TestFoo
(PatronID int NOT NULL, PatronName varchar(25) NULL) ', alter_id, msg );

END P1 ~

CALL foo()~

Error: SQL0443N Routine "SYSPROC.ALTOBJ" (specific name "ALTOBJ") has
returned an error SQLSTATE with diagnostic text "SQL0204 Reason code or
token: SUPERDUDE.TestFoo". SQLSTATE=38553
(State:38553, Native Code: FFFFFE45)


Okay, so using Control Center, and any SQL it generates is out. Also, using
ALTOBJ is out. So now we're left trying to perform manually what other
database products tools can do. In SQL Server:

CREATE TABLE TestFoo (
PatronID int NOT NULL,
PatronName varchar(25) NOT NULL)

In Enterprise Manager make my DDL change, and hit "Save Change Script".
Paste the SQL into my favorite query tool (Query Analyzer):
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_TestFoo
(
PatronID int NOT NULL,
PatronName varchar(25) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.TestFoo)
EXEC('INSERT INTO dbo.Tmp_TestFoo (PatronID, PatronName)
SELECT PatronID, PatronName FROM dbo.TestFoo TABLOCKX')
GO
DROP TABLE dbo.TestFoo
GO
EXECUTE sp_rename N'dbo.Tmp_TestFoo', N'TestFoo', 'OBJECT'
GO
COMMIT

And we're done. i also tried creating the whole real table that is in DB2 in
SQL Server, and having SQL Server generate it's DDL change scripts. It
works, and is a good starting point on how to change table structure in DB2.
Problem is, there are many associated objects (triggers, foreign keys,
views) that i don't want to mirror in SQL Server just so i can generate
change scripts in DB2.

If only we could see the SQL steps that ALTOBJ will run to perform a change,
and then repair them. Looking back at the documentation for ALTOBJ, the
first parameter looks interesting:

GENERATE
Specifies that all the scripts required by the VALIDATE, APPLY, and UNDO
modes are to be generated.

So i try:
DROP TABLE TestFoo~
CREATE TABLE TestFoo (
PatronID int NOT NULL,
PatronName varchar(25) NOT NULL)~

DROP PROCEDURE foo~
CREATE PROCEDURE foo()
DYNAMIC RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
DECLARE msg VARCHAR(2048);
DECLARE alter_id INTEGER;
SET alter_id = -1;

CALL SYSPROC.ALTOBJ ( 'GENERATE', 'CREATE TABLE TestFoo (PatronID
int NOT NULL, PatronName varchar(25) NULL) ', alter_id, msg );

END P1 ~

CALL foo()~

And not surprisingly:
Error: SQL0443N Routine "SYSPROC.ALTOBJ" (specific name "ALTOBJ") has
returned an error SQLSTATE with diagnostic text "SQL0204 Reason code or
token: SUPERDUDE.TestFoo". SQLSTATE=38553
(State:38553, Native Code: FFFFFE45)


Or, perhaps the other parameter value:
VALIDATE
Specifies that the statement syntax is to be validated. ***This option also
generates a script to manage the processing of related objects and
relationships for the table that is to be altered.***

Same thing.


So, from this never-ending nightmare, we've started defining some rules for
DB2 development
1. Never use NOT NULL. If we can't change it, we can't allow it in the first
place.
2. Don't use IDENTITY. If the identity seed is reset to zero most of the
time, and only discoverable when we get a unique index violation, then we
have to stop relying on DB2 to generate values, and will have to maintain a
table ourselves. e.g.
CREATE TABLE AutoInc (
TableName varchar(128) NULL,
IdentitySeed INTEGER NULL)



But back to the update scripts that we need to send to the customer's IT
department - which must not lose any live data. What would you do? How you
use alter tables?



postscript. If this post seems overly hostile, or biased: it is. DB2 has
used up it's point, and i'm now a tired, frustrated, and broken man. i would
take you up on your offer Serge, but you'd have a room of 5 frustrated guys
demanding answers. Perhaps Theresa should be sent first as a sacrifice.
Perhaps alternativly, you could some some user testing and try to impress
upon her the problems. Theresa insists everything is by design.

Jul 18 '06 #1

Serge Rielau

Ian Boyd wrote: Ian, eons ago I arranged a tools developer to work with you. Since then
I haven't heard back from you. If she was not able to help you why
didn't you contact me?
By throwing out generalized comments like that you're not exactly making
friends nor do you encourage help.
Well, let's see....
Did you send in an example? Did you open a PMR?
That is what the SQL control center runs...
CONTINUE ON ERROR? And you wonder about lack of UNDO?
You don't need to. Just leave the parameter markers for the OUT parameters. db2 =? SQL0668;
SQL0668N Operation not allowed for reason code
"<reason-code>" on table "<table-name>".

Explanation:

Access to table "<table-name>" is restricted. The cause is based
on the following reason codes "<reason-code>":

3 The table is in the Load Pending state. A previous LOAD
attempt on this table resulted in failure. No access to the table
is allowed until the LOAD operation is restarted or terminated.

User Response:

3 Restart or terminate the previously failed LOAD operation on
this table by issuing LOAD with the RESTART or TERMINATE option
respectively.

So much for you don't know the table name or how to fix it....
Mixed case.. now that should be quite a hint. I bet your table is
"TESTFOO" and not "TestFoo", right?
Because you didn't look up the error messages and you used ON ERROR
CONTINUE. Oh if all SQL changed would always be so simple. No dependencies, no
privileges, no nothing.
Indeed. Maybe we're not all that dumb after all.
Still that mixed case table name. Ah yes, you never fixed that. What did
you expect?
Nope.. it's still the mixed case table names SQL0204 does not talk about
ALTOBJ. It simply can't find that table. You can try anything, until
you fix that it won't work and it's not ALTOBJ fault, neither the
control center. Surprise...
You can change it in DB2 9, aside ALTOBJ works.. but you have to sit
back and THINK.
I still don't get what your problem was there, but anyway. IDENTITY in
DB2 is a lot more powerful than SQL Server's. For once it can run
descending and wrap... With power comes responsibility.
You use ALTOBJ (after fixing your -204 error) to generate the change
script. Validate and test it in house, assuming the schema is fixed.
Indeed
*lol*Hey, buy a lottery ticket from your customer :-)
First you have to count to ten, then we can talk. I'm no sales rep. I'm
not being paid to get yelled at.
So far all you showed in this thread was by design. And I stand by it.
You have tried aspirin to heal broken arms and got upset that it didn't
work. Don't blame the aspirin.
Sending the ladies to sacrifice is not very gentleman like....
If you disagree why don't you contact me BEFORE burning bridges by
causing a ruckus which make you look at least as bad as DB2.

So, why don't we take this off line and you send me an email.

Cheers
Serge

PS: When a developer kindly agrees (because I ask him/her) to get in
contact with a customer he/she generally doesn't agree to get their
names pasted across the internet.
You use up the good will of my "backstage" experts that way.
Please refrain from posting names/email addresses/phone-numbers. It's
just bad netiquette.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Jul 18 '06 #2

Ian Boyd

Ian, eons ago I arranged a tools developer to work with you. Since then I We emailed back and forth once or twice. The general belief she has was
that everything behaved as designed; and she stopped responding to me.
i didn't mention it to you cause, as you indicated, the UI tools weren't
really your thing. Didn't really want to harass you on something that isn't
yours.

Plus, you were in Japan. :)

No. It's not guaranteed reproducable. One would need to use Control Center
for a few hours for it to happen. i didn't open a PMR (without even knowing
what a PMR is), because i don't know what kind of support options (if any)
we have with IBM.

Considering how common the problems are (five out of five developers here),
i assumed they were just limitations of the tools. Also, considering
her resistance to identify problems, i assumed not much could
or would happen to fix them.
The "Undo All" is a feature exposed in Control Center. When the table
altering
fails in, you have an "Undo All" button.

i don't (shouldn't) need an "undo" option when running ALTOBJ myself, since
it is (should be) atomic. i suppose i could wrap it in BEGIN TRANS/ROLLBACK
TRANS
myself.
It does seems to work from the IBM command line utility. If you actually
send the SQL to the server:
CALL SYSPROC.ALTOBJ ('APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE TestFoo
(PatronID int NOT NULL, PatronName varchar(25) NULL)', -1, ?)

you get the error:
Error: CLI0100E Wrong number of parameters. SQLSTATE=07001 (State:07001,
Native Code: FFFE7961)


....
Ummm....i have no LOAD operation. i called a stored procedure though.
Mixed case? Everything is stored in DB2 as uppercase right?
Everything is converted to uppercase unless quoted?
DB2 is case insensitive that way???

Well we can't have customers using Control Center either way. Also,
Control Center is using "ON ERROR CONTINUE".

But yes, i used ON ERROR CONTINUE. Still, it's not like i know how to fix
it. i'm more from the school that the server should do that - MSSQL can
do such structure changes without failing.

How about this error:
SQL0443N Routine "ALTOBJ" (specific name "*OBJ") has returned an error
SQLSTATE with diagnostic text "SQL0204 Reason code or token:
SUPERDUDE.OCCURRENCE_VIDEOS". SQLSTATE=38553

(although, IBM admits, this one is a bug fixed in FP12; not that we can
convince the customer to move beyond FP9 in any event. "Your query is
bad. Send us another one.")
Are they being generated and stored somewhere? i see no mention of
where they are generated to.
i thought DB2 was case insensitve. i thought that, unless quoted, they are
all converted to uppercase.

Both
SELECT * FROM TestFoo
and
SELECT * FROM TeStFoO
work fine. What's the issue again? They're case insensitive *most* of the
time.

Okay, my contrived example has other errors. Issuing of ALTOBJ directly
is not normal. Normal procedure is to right-click on a table in Control
Center and select "Alter". Later when it saves - it fails. Perhaps Control
Center is using mixed case - i doubt it, since i don't see mixed case in it.
That still doesn't explain it's failures.

i'm fine with waiting for a table structure to applied. It's part of the
downtime
in doing "updates."
i know. You have to see it. i'll try to detail the symptoms (not the steps
to
reproduce it)

1. Create a table with an IDENTITY and unique index on some integer column
2. Add rows to the table.
3. Use Control Center to alter the table (change varchar to CLOB, or,
change a column from NOT NULL to NULL)
4. Save your table structural changes.
5. The save my or may not fail.
6. Insert new rows into the table, and your unique index on the identity
integer column will fail.
7. Examine the table in Control Center and note that your identity seed
value has been reset to zero rather than what it used to be.
8. Select MAX(intColumnYouUsedInStepOne) FROM sysibm.sysdummy1
9. Update the seed value to the highest value currently in the table.

i'll mention that when using SQL Server, if you make table structural
changes,
and you choose to view the SQL that Enterprise Manager will run to effect
the table changes, you see a series of steps, similar to

i. Start a transaction
ii. Drop all constraints on the table
iii. Make a new table with new structure
iv. Add constraints on the new table.
v. Copy data from the old table to the new table
vi. Drop the old table
vii. Rename the new table to what the old table was.
viii. Add PK and FK's to new table
ix. Commit Transaction.

And if anything fails, the entire operation is rolled back. Data integrity.

The most most most common error we see in Control Center when trying
to alter a table in an SQL statement trying to re-create statistics. If this
were SQL Server, and i was getting an error when it was trying to re-create
statistics, i would comment out the CREATE STATISTICS statement, and
let it complete. (i know what stats are)

It is so difficult to explain the problems, when you have to write
everything down. Rather than focusing on the real problems going on
here, instead we're talking about mixed case and ContinueAfterFails...
No yelling involved. But 4 guys standing around, listening, all wanted to
pipe up and explain the same problems in slightly different ways.

i didn't contact you before, cause you was in Japan. Plus, the interface
stuff
was more of her field.

Also, we're fighting a fire trying to update the customer's live system,
while trying to ensure that the scripts run error-free without losing any
data, all the while us not being allowed to touch the live system itself.
Considering we lose database objects, and sometimes data, all the time
here in development, and once on their training side, we're in a bit of a
mode.

Never before have we had to convene a meeting to pull 3 developers off
their regular stuff to try to figure out how to change a column from
NOT NULL to NULL. This has never come up with the other RDBMS
we deal with.
Okay, i'll start an Email.

Oops, i just assumed 'her' name was as unitiquous as "Serge" is. Sorry.

Jul 18 '06 #3

Ian Boyd

Ian Boyd
Avatar Software Creations
ian.db2@avatopia.com
(519) 735-9078

Here's where we work:
http://maps.google.com/maps?f=q&hl=e...7,0.042915&t=k

Here's my house:
http://maps.google.com/maps?q=728+He...,0.042915&om=1

Jul 18 '06 #4

Serge Rielau

Ian Boyd wrote: Call me an interested party. Figuring out what turns an SQL Server
person off on DB2 is part of my job description.
You didn't mention in this thread that the problem appears only after
hours. An important piece of information...
It always depends on how one asks... OK. And it should work unless you "commit" teh change with FINISH
No, you misunderstand how this tool works. You cannot do big loads
without intermittent commits. This is why the ALTOBJ procedure builds
scripts for logical undo and redo. Note that until you run FINISH mode
your data and scripts are preserved.
alter-id
An input and output argument of type INTEGER that identifies 7 all
of the statements that are generated by this call. 7 If -1 is specified,
a new identifier will be generated and returned 7 to the caller. 7 Any
existing statements identified by the specified integer are 7 overwritten.

The CLP is nice to you and actually allows the -1 input for in INOUT
parameter (because, as you say, there are no variables in CLP). In a
regular client an OUT or INOUT paramter requires a paremeter marker. So
you need to bind -1 for the input and on teh output it will return you
the "token" for this DDL script.
Yes you called a stored procedure which did a LOAD.
I don't know what you did in between because recalling ALTOBJ with
'UNDO' should have dropped the table. My assumption is that somewhere
you didn't follow "protocol".
The ANSI SQL requires DB2 (and Oracle and any other ANSI compliant
database) to upper case any non double quoted identifier typed by the
user, BUT tables are case sensitive.
ALTOBJ is somewhat special (much to my dismay). It takes the identifiers
as is and wraps double quotes around it.
While this is a bit of a trap the SQL0204 clearly states what what table
it was looking for: "TestFoo". And your SYSCAT.TABLES.TABNAME = "TESTFOO". Correct. That's why control center with ShowSQL told you that is was
using ALTOBJ and even how it called it.
The whole purpose of ALTOBJ is to assist in application migration.
I'm genuinely interested how MS SQL Server does that short of providing
the features of a full blown IDE.
When you alter a table some interesting things can happen:
E.g. a column name used in a view or trigger may change. Does SQL Server
patch up the column names? If so, what does it do if there is a join and
you suddenly have a name conflict? What happens if teh view generation
fails?
To the best of my knowledge SQL Server (and most other DBMS I know of)
will simply not regenerate the failing object and rely on the first
caller to re-validate.
ALTOBJ goes beyond that. Called with VALIDATE it will perform a "dry
run" of all your DDL (which can be rolled back) and report all errors.
You can now run UPDATE statements against the query it reports back and
fix up statements. E.g. call different functions after a type change,
patch up columns, remove columns from views, figure out with a unique
index which isn't unique anymore, etc, etc...
Once your VALIDATE goes through you can apply the change script and you
can save away the VALIDATE script for automatic deployment at your customer.
Customers can receive bug fixes on specific fixpacks. While this is not
the first choice (more work, divergence from the main path) it business
as usual.
Aha! Here is where you went astray. ALTOBJ actually returns you the
exact query to run:

msg
An output argument of type VARCHAR(2048) containing an SQL query
that you can execute to display all of the SQL statements generated for
or used by the alter table process under the specified execution mode.
See above.
Please define "it fails" It is normal to fail in the validate phase.
Control center has a pane that allows you to click through the errors
and correct the failing statements in the script and that this is indeed
a feature. It makes you fix your schema at alter time and doesn't delay
failure to runtime discovered by some poor schmuck.
That's not what I meant. I meant you have to read the documentation and
look up the errors returned.
This is where things went wrong. You didn't look into the errors. Of course the save failed.... you got half of your alter done.
E.g. the ALTER TABLE RESTART wasn't applied. Does it update column names, what if types changed? So many GB of data can SQL Server copy without doing a commit? And you can roll that back after loading 50GB? I quite frankly, don't believe it. May work for small tables. Would
require exorbitant logging for big tables
Yeah. And that's exactly what Control center and ALTOBJ allow you to do.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Jul 19 '06 #5

Ian Boyd

You didn't mention in this thread that the problem appears only after Oh yes. Happens to everyone here. Mostly not reproducable.

Jul 19 '06 #6

Ian Boyd

You didn't mention in this thread that the problem appears only after Oh yes. Has happened to everyone here, more than once. Mostly not
reproducable, but still keeps happening.

Jul 19 '06 #7