changing datatype of a field 1

[babeo]

Hi
my DBA is going "fishing" for a month, so I am temporary acting as a DBA, but my knowledge and experience about db and Sybase is so little, and I am manipulating a production system. I don't know what to do. Please help.

I have a table and there is one field (A) was set up with "smallint", however, now the data change, it is bigger than 2 digits, so I have to change the field (A) to "int".

My questions are:
1) If I just go ahead and change the field name in the table, is there any impact to the old data? Will it stub with 0 or NULL or I lost all of them?

2) What is the best performance to do the change? Should I disconnect all users accessing to this table/database first (How do I do it?), then create a temporary table and dump all the data to this temp table. Next drop the current table and create new table with field (A) changed as above, then "bcp in" or "insert" the data from temp table back to the new table? If I am doing this, what precautions should I pay attention to? Or what other method that I can do?

Thanks

 

[qyllr]

hello,

wait for other dba's to post alternative solutions before you execute my recommendation... if you are using system 12.+ which i am unfamiliar with the new features, the process could be as simple as changing the datatype with an alter table sql or changing it with sybase central...

i use system 11.9.2, and my production environment is CM controlled so i write patches for any production changes... the CM engineer then runs the patches as part of a 'software release'... this ensures no data corruption...

since you are dealing with production, i recommend to wait for your dba or find someone who can double check your work if your change needs to be done immediately...

my orientation is unix specific so i will write a mini procedure based on a unix setup...

1. go to the following website and print out all the materials related to this procedure...

http://www.sybase.com/support/manuals

2. perform this after production hours...if you have 24/7 operation do it during non-peak hours...

3. dump the database to a tape device or a file system subdirectory...consult the sybase manuals for the level of backup you need to perform...whether backing up the master, model and logs are necessary...

4. send out an email to your users that you are performing some tuning on the PROD database during a specified time...

5. you would need to gather data from your database on the following:
a. table structure information (column names, datatypes,
etc...)
b. foreign key constraints on the table
c. constraints
d. check constraints
e. indexes
- use sp_help db {table_name}, sp_helpconstraints {table_name}, sp_helpindex {table_name}
* this is critical - you would need to find out if other tables reference your table via a foreign key... if this is the case you would need to drop those foreign keys from those tables as well... in our sample scripts below i am only considering a foreign key constraint from your table that references another table...

6. you would need to write five scripts...(before validation sql, bcp out, sql script, bcp in, after validation sql) you can actually package this in to one... the scripts are as follows...i leave the script design, error handling and output paths to you:

variables i used for this procedure:

$SYBASE = sybase home directory
$OUT = output path
$SQL_USER = login name(most likely 'sa')
$SQL_USER_PASSWORD = password
$SQL_SERVER = Server name
$DB = database name
$TABLE = table name
$CON = constraint values
$FK = foreign key constraint values
$INDEX = index values
$FN = created file name for your out file
$DB_USER = your user account
$DB_USER_READONLY = select permission account

script1 - before validation:

###################################################
###Note: this count should match the bcp out count
###################################################
use $DB
go
select count(1)
from $TABLE
go

script2 - bcp out:

$SYBASE/bin/bcp $DB..$TABLE out $OUT/$FN.out -U$SQL_USER -P$SQL_USER_PASSWORD -S$SQL_SERVER -c -t "|" -r "\n"

script3 - sql:

use $DB
set nocount on
go
###################################################
###Note: this is the foreign key constraint drop###
###################################################
alter table $TABLE
drop CONSTRAINT $FK
go
###################################################
###Note: this is the table drop
###################################################
drop table $TABLE
go
###################################################
###Note: this is the create table segment...please
###add here the rest of your table schema
###################################################
create table $TABLE (
A int not null
)
lock allpages
on 'default'
go
###################################################
###Note: this is where you add your constraint lines
###################################################
alter table $TABLE
add constraint $CON
go
###################################################
###Note: this is where you add your foreign key
###constraint lines
###################################################
alter table $TABLE
add CONSTRAINT $FK
go
###################################################
###Note: this is where you grant permissions
###################################################
grant all on $TABLE to $DB_USER
go
grant select on sub_shares_reg to $DB_USER_READONLY
go
select @@error
go

script4 - bcp in:

$SYBASE/bin/bcp $DB..$TABLE in $OUT/$FN.out -U$SQL_USER -P$SQL_USER_PASSWORD -S$SQL_SERVER -c -t "|" -r "\n"

script5 - after validation:

###################################################
###Note: this count should match script 1, 2 and 4
###################################################
use $DB
go
select count(1)
from $TABLE
go

it would be good to test your scripts in a test environment before you fire the scripts in production...

good luck...be careful...if you hesitate in your execution...go back and double check your work...and have someone review your scripts...

hope this helps,
q.

 

[qyllr]

hello,

sorry, i forgot to include the create index script after the bcp in script... the syntax is simple:

use $DB
go
create index $INDEX
go

you would need to re-enter the create line for every index you have on that table... this is also true for the drop/add constraints and foreign key constraints...

cya,
q.

 

[qyllr]

hello,

if i haven't confused you enough...please look at item 5 on my first response... the '*this is critical'... i did not mean for you to drop the foreign key but drop the foreign key constraints...

sorry,
q.

 

[babeo]

Thanks

Yeah! It is so confused to me, but I will try to swallow this, and yes, thanks for your response and this helping hand, and also sorry for this late reply due to I am swam and drown at the new tasks !!!!!!!!!!
By the way, I have a new question for you or anyone:
For example, there are 4 fields in table A on my current database, now I need 1 extra field for this database.table, can I just go ahead adding this new field?

Is there any thing happening? or What should I pay attention to ? Should I do it or should not?

I know 1 problem but not very clear about the situation, that recently there is one person updates a table and I don't know what is the relationship of this table with other tables or with the database as a whole, and this table is big also, and it takes one complete day to alter this table, which causes no production, nobody can do anything during that day. It looks bad.

 

[qyllr]

hello,

you would need to perform the same procedure for changing the data type for adding a column... the bcp (in) however will change... you would need to write a migration script to append any new data you may have on the bcp out data... i use perl to do this... you can also program this using bcp...

for your last question, you should consider normalizing your 'big' table using the second or third normal form...

check out this website for tips:

http://www.troubleshooters.com/littstip/ltnorm.html

for a more immediate solution, make sure your update procedures/sql's are hitting the indexes you have on that table... if they are not...you are probably doing a table scan each time you update... create indexes on the most commonly used columns... if you do not have them already...

cya,
q.

 

[chan]

Hi !

What if I change the column name and the field length (it's a character data type) of the table at the same time?
How can I export and import the data into the table?

Thanks.