Alter Table

[NOTE: sorry if this is a multiple copy] > > We have a large table that needs to be altered. We could either alter is by > using an sql alter or we can unload the table in the revised format, drop the > table, create the table in the revised format, and reload. We know the second > method will work, but will be a slightly more complicated procedure. Any > thoughts on the pitfalls of the first method (eg: root dbspace concerns, > shared memory concerns, logging concerns, etc.) > You should have *lots* of concerns about this process. Its a catch-22 mess. First, there is the lock table to worry about. The maximum number of locks that may be configured is 256k. When an ALTER TABLE is executed, a lock is created for *each* row, for *EACH* key. That is, if you have 100k rows, with 3 keys, you get 300k keys (which would overflow your lock table, and cause disastrous results). You could put an exclusive lock on the table, causing only one lock entry. HOWEVER, then your transaction would be so long it would blow out your log files. The problem exists even if logging is set to /dev/null, since the one huge transaction *has* to fit in the log file. You could temporarily create logfiles big enough to hold the transaction, but calculating the necessary size is not easy, and if you are wrong, the transaction will rollback after much time. If you are running Informix Online 4.0 or previous, this may cause you big problems. You could turn off logging on the database, but this is extremely unsafe, unless an archive is done just previous to this procedure. To put logging back on a database, requires an archive (or tricking informix into thinking an archive has been done). You could unload to disk, but when you are dealing with tables that have a million or so rows, this is not great. Reloading the table takes fovever, plus a few hours, and must be done with dbload, or your lock table will overflow. Unloading to tape is also an option, though I have a bias against this media, as it fails on occasion. If you dont have enough room to keep the old table during this process, you could be in jeopardy. Just to add to the fun, we typically load a table without keys, and create them later. Sometimes, we cannot create keys on jibundo size tables, and have to load tables with keys intact (adding to the downtime). Our unfortunate answer is having a program read from one table and write to another, but again, you will need space in your partition, and it takes a *VERY* long time. Informix Note: Are you listening? I know you must have heard this sad saga before. Can you help us out here? We have a few production customers that cannot be done for hours and hours, and have huge tables. I thought I read somewhere you were working on a way to turn off locks during loads, or something in that vain. -- Naomi Walker (aka N7FSA) Anasazi Inc. Phoenix, Arizona naomi%anasaz.UUCP@asuvax.eas.asu.edu Imagine the Universe Beautiful, Just, and Perfect. (Messiahs Handbook) -- Naomi Walker (aka N7FSA) Anasazi Inc. Phoenix, Arizona 602-395-1731 {uunet!pyramid},{sun!sunburn!gtx!},{asuvax} !anasaz!qip!naomi