sybase migration upgrade - liamlamth/blog GitHub Wiki

--to change to data and log type

1. add data or log size on device
2. drop data or log segment

   sp_dropsegment "default", "tempdb", ""
   sp_dropsegment "system", "tempdb", ""  
   

   sp_dropsegment "logsegment", "tempdb", ""
   

3. add back all segment

   sp_extendsegment "default", "tempdb", ""
   sp_extendsegment "system", "tempdb", ""
   sp_extendsegment "logsegment", "tempdb", ""
   

• sp_configure "max memory",0,"9G" (server memory 75%); or
  sp_configure "max memory","4718592" (2k unit, 9 × 1024 × 1024 ÷ 2)
• sp_cacheconfig "default data cache","4G","cache_partition=4" (max memory × 0.5, cpu × 2)
  sp_cacheconfig "tempdb_cache","1G","mixed","cache_partition=4"
  --may need to restart DB if not allowed

  Adding/Deleting cache partitions to an existing cache is not dynamic. To change the number of cache partitions in the same instance, delete and recreate the cache with the required configuration.

--reference only:

Parameter Name                Config Value
----------------------------- ------------
additional network memory           155648
allocate max shared memory               1
deadlock checking period               800
disk i/o structures                   1024
heap memory per user                  6144
number of devices                      100
number of large i/o buffers             50
number of locks                      20000
number of open databases               120
number of open indexes                1000
number of open objects                5000
number of open partitions             600
number of user connections             100
number of worker processes              50
procedure cache size                156672
recovery interval in minutes         32767
statement cache size                 39168
size of process object heap           4000
size of shared class heap             8000
enable literal autoparam                 1
max network packet size               4096

1> select *,null as lpid,null as crsuid into tempdb..syslogins_157 from syslogins where name not in( "probe", "sa")

• check user and manually add user (schema)

1> sp_helpuser

• compare with ASE16 default roles, find out user-defined roles which overlap the srid. manually create the roles, grant roles and check if they are auto-activated
• in this case, ASE 15.7 user-defined role (srid 32) is duplicated

1> select * from syssrvroles
1> select * from sysloginroles where srid = 32
1> sp_displaylogin liam_user1
1> sp_displaylogin liam_user2
1> select *,null as predid into tempdb..sysloginroles_157 from sysloginroles where suid > 1 and srid <> 32

$ bcp tempdb..syslogins_157 out syslogins_157_out -Usa -SLIAMINS -P abcd1234 -c
$ bcp tempdb..sysloginroles_157 out sysloginroles_157_out -Usa -SLIAMINS -P abcd1234 -c

$ ddlgen -SLIAMINS -Usa -Pabcd1234 -Dmaster -TP -N%sp_liam_% -O mas_sp.ddl

• also check whether there are any additional dbopions configured

#!/bin/sh
SEQ=`date '+%w'`
isql -Usa -Pabcd1234 -odb.log.$SEQ << eof
dump database LiamUD01 to "LiamUD01.db.dump"
dump database LiamUD02 to "LiamUD02.db.dump"
sp_helpdb
eof

• ck_rowcount.sql

select substring(ob.name,1,50) table_name,st.rowcnt from sysobjects ob, systabstats st
where ob.type="U"
and st.id=ob.id order by ob.name
go

$ export tmpv1=LiamUD01; isql -SLIAMINST -D${tmpv1} -Usa -Pabcd1234 -w999 -i ck_rowcount.sql -o b4_rowcount_${tmpv1}.txt
$ export tmpv1=LiamUD02; isql -SLIAMINST -D${tmpv1} -Usa -Pabcd1234 -w999 -i ck_rowcount.sql -o b4_rowcount_${tmpv1}.txt

• check stored procedure

1> use LiamUD01
1> select count(*) from sysobjects where type = 'P'
1> use LiamUD02
1> select count(*) from sysobjects where type = 'P'

1> sp_configure "allow updates to system tables", 1
$ bcp master..syslogins in syslogins_157_out -Usa -SLIAMINS -Pabcd12345 -c -b1
$ bcp master..sysloginroles in sysloginroles_157_out -Usa -SLIAMINS -Pabcd12345 -c -b1

1> sp_adduser 'liam_user1','liam_user1','public'

1> create role liam_role
1> grant role liam_role to liam_user1
1> grant role liam_role to liam_user2
1> alter login liam_user1 add auto activated roles liam_role
1> alter login liam_user2 add auto activated roles liam_role

$ isql -Usa -Pabcd12345 -w999 -i mas_sp.ddl -o mas_sp.log 

1> disk init name = "dbdata1", physname = "/dbdata/LIAMBASE/dbdata1.dat",size = "10G"
1> disk init name = "dblog1", physname = "/dbdata/LIAMBASE/dblog1.dat",size = "5G"

1> CREATE DATABASE LiamUD01 ON dbdata1 = '200M' LOG ON dblog1 = '100M'
1> CREATE DATABASE LiamUD02 ON dbdata1 = '100M' LOG ON dblog2 = '200M'

• load dump: normally it would prompt information that the load dump is started.
• if it didn't show any information, could consider the dump file is not complete (e.g. file transfer is terminated due to size limit)

isql -Usa -Pabcd12345 -w9999 -oload_db.log <<  eof
load database LiamUD01 from "LiamUD01.db.dump"
load database LiamUD02 from "LiamUD02.db.dump"
go
online database LiamUD01
online database LiamUD02
go
sp_dboption LiamUD01, "select into/bulkcopy/pllsort", true
go
sp_dboption LiamUD01, "trunc log on chkpt", true
go

• ck_rowcount.sql

  select substring(ob.name,1,50) table_name,st.rowcnt from sysobjects ob, systabstats st
  where ob.type="U"
  and st.id=ob.id order by ob.name
  go
  

  $ export tmpv1=LiamUD01; isql -SLIAMINST -D${tmpv1} -Usa -Pabcd12345 -w999 -i ck_rowcount.sql -o af_rowcount_${tmpv1}.txt
  $ export tmpv1=LiamUD02; isql -SLIAMINST -D${tmpv1} -Usa -Pabcd12345 -w999 -i ck_rowcount.sql -o af_rowcount_${tmpv1}.txt
  $ diff *rowcount_LiamUD01.txt
  $ diff *rowcount_LiamUD02.txt
  

• check stored procedure

  1> use LiamUD01
  1> select count(*) from sysobjects where type = 'P'
  1> use LiamUD02
  1> select count(*) from sysobjects where type = 'P'
  

• compare permission
  excel vba