Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Increasing the Column width

2 posts in Windows NT Last posting was on 1998-03-26 20:10:04.0Z
Mohan Patil Posted on 1998-03-25 19:22:26.0Z
Message-ID: <35195972.6336@writeme.com>
Date: Thu, 26 Mar 1998 00:52:26 +0530
From: Mohan Patil <mapatil@writeme.com>
X-Mailer: Mozilla 3.01 (Win95; I)
MIME-Version: 1.0
Subject: Increasing the Column width
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 7
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5096
Article PK: 1092459

Hi
I have to increase the width of Narration column from 30 characters to
100 chaaracters.
This table is referencing out to three tables
What are the options available to me
I am using Sybase 11 on NT 4.0

TIA

Mohan Patil
Ispl Pune.


Mark A. Parsons Posted on 1998-03-26 20:10:04.0Z
Message-ID: <351AB61C.E69@compuserve.com>
Date: Thu, 26 Mar 1998 15:10:04 -0500
From: "Mark A. Parsons" <Iron_Horse@compuserve.com>
Reply-To: Iron_Horse@compuserve.com
Organization: Iron Horse, Inc.
X-Mailer: Mozilla 3.01 (Win95; I)
MIME-Version: 1.0
Subject: Re: Increasing the Column width
References: <35195972.6336@writeme.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 96
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5095
Article PK: 1092460


Mohan Patil wrote:
>
> Hi
> I have to increase the width of Narration column from 30 characters to
> 100 chaaracters.
> This table is referencing out to three tables
> What are the options available to me
> I am using Sybase 11 on NT 4.0
>
> TIA
>
> Mohan Patil
> Ispl Pune.

How many rows in the table?

Is this a production system? If so, how much 'down time' can you get
from your users to do the conversion? (Yeah, yeah, yeah, I know ... the
users say you can't have *any* time, right?? :-)

Can the column be nullable?

When you say 'is referencing out to three tables' ... do you mean there
are RI constraints in place?

Do you have enough room in the database for 2 copies of your table
(copies with no indices on them!!)?

Are you looking to keep the same indices on the table, in particular,
will the clustered index (if it exists) remain on the same column?

1) Make a backup of your database before hand ... just in case you have
to roll back this process.

2) Make sure users are off the system, or at least make sure they won't
be able to reference this table and those 3 referenced/referencing
tables.

3) Reverse engineer all indices, triggers, keys, constraints and
permissions on the table. Reverse engineer any constraints and keys on
other tables which reference this table.

4) Drop all indices from your table. Drop all constraints on the table
or which reference the table.

5) Assuming you can leave the 100-byte column nullable, run a 'select
... into' to move the data from your table to a new table
(<table>_new??).

6) Verify the rowcounts and structure of the new table.

7) Drop the old table.

8) Rename the new table to have the same name as the 'old' (now dropped)
table.

9) Rebuild all indices on the new table, making sure to recreate the
clustered index first (also look at using the 'sorted data' option to
speed up the index build) (also look at having 'extent i/o buffers'
configured so as to speed up the index builds, too).

10) Reload all triggers, constraints. keys and permissions.

11) Make a new backup of your system (the 'select ... into' in step #5
blows your recoverability options).

12) Let your users back into the system.

-----------------------------

Caveats:

1) If the 100-byte column must be non-nullable ... you can still use the
'select ... into' ... but I don't remember, right off the bat, what you
have to do to make sure the column remains non-nullable.

2) If the 100-byte column must be non-nullable but of variable length
... you'll have to replace step #5 (above) with ... a) create the new
table and b) run a 'insert ... select' command.

3) If you don't have a lot of free time and you don't mind a 'messy'
table you could alter your current table adding a new 100-byte column,
rename the 'old' column to something bogus, rename the new 100-byte
column to the 'old' column name, populate the new column with the
appropriate 'update' commands from the 'old' column's value(s), then
drop and recreate all triggers, views, stored procs, constraints and
permissions which reference this 'new' column. Oh, yeah, if you had an
index on the 'old' column ... you'll need to drop and recreate it for
your new column. Of course, now you're left with that 'bogus'
30-character column in your table ... but you could clean that up at a
later date if/when you get more time.

4) It's possible that directly modifying system tables would work ...
but since this is not supported by Sybase TS ... well ... we won't go
into that. (Why'd I mention it? Just in case someone else does ...
just make sure that if you go this route ... you're on your own so don't
expect any/much help from TS if things don't go as planned.)


--
Mark Parsons
Iron Horse, Inc.