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...
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.
