Re: advice on approach for db upgrade script

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Thu, 17 May 2007 07:54:09 +0200
Message-ID: <f2gql5$fpe$1@news2.zwoll1.ov.home.nl>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jeff Kish schreef:

>> No - just widen the column, and carry on

> you mean alter table command in the procedure? The reason I didn't want to do
> that was:

Yes - that is what I meant. You should know your model, and so the next argument isn't one (as you *know* which columns to widen!)

> 1 - if i do it i need to manually find and alter all the referring columns.
> And if for some reason there is a failure I need to handle that (sounds
> complicated). But if a prerequisite is that the dba uses, say, enterprise
> manager to length the primary key and related columns, then they can do that
> better themselves in one fell swoop.

>> Cannot be a problem - you already fixed that in step 1

> no, there is a minimum length column we want the customers to use for this
> version. However it is always possible that this minimum length isn't enough
> for the data they use so I'd need to either tell them to lengthen it or
> lengthen it ourself.

>> I'd rather have a table, holding the old and new values.
>> Allows for undo, and it seems you have other (referenced tables)
>> you need to update, too.
>> Use the cuurent value found in the referenced table,
>> lookup the new value, using the old, and Bob's your uncle!

Ah - that would be within a packaged procedure, running over and over again.
Not the case here - you're doing massive data alterations, modifying (what you call) primary keys. That always carries the danger of compromising data integrity! That's why the mantra "never change primary keys" exists, and I just referred to "(what you call) pk's" - I'm a firm believer of the mantra, but I do recognize there are some situations, where (poorly) designed systems need such a change.

Anyway - my experience is you need this table - and it will be a small one: just 2 columns - for later referral (This [value] used to be [that])

> I keep hearing warnings about using temp tables etc. I thought I should stay
> away from them. If I use these don't I need to create them before the
> procedure is run, otherwise it won't see them at compile time will it? Just
> trying to make sure I take a course here with more wisdom than I inherently
> have.

>>> That way I won't use temp or global temporary tables, and I can query and
>>> store results. I assume I can query varrays?
>>>
>> Nope - data is lost when ending the session

> I was hoping that if everything ran ok, I wouldn't care about the old data,
> and if everything was not ok I would not commit the transaction(s).
>

DDL does implicit commits - so as soon as you do an 'alter table' you will commit!

• -- Regards, Frank van Bortel

Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)

iD8DBQFGS+4ALw8L4IAs830RAkA0AJoCMEbSz73//p0pwZMNKXv6DFgKtACfXcc3 efQuu76gsK1NeMBtEQXCoEE=
=E1x8
-----END PGP SIGNATURE----- Received on Thu May 17 2007 - 00:54:09 CDT