Useful Sybase IQ commands

Connect with:
You could also use isql, e.g. isql -Usybadmin -P******** -SSYBEMA_FDA_PRD_IW02.

Another way to connect locally is by using dbisql as follows: dbisql -c “uid=dba;pwd=*********;eng=SYBEMA_FDA_UAT_IW02;dbn=SYBEMA_FDA_UAT_IW02? -nogui

Commands:
List active users
sp_iqwho

IQ Server info
sp_iqstatus

IQ Servername
select @@servername

Physical disk devices
sp_iqfile
Currently running statements
sp_iqcontext

Currently active transactions
sp_iqtransaction

dbspaces
sp_iqdbspace

dbfiles
sp_iqfile

Table columns and Indexes info
sp_iqhelp

How to create new user in Sybase IQ
create user identified by ;
grant connect to identified by ;
How to change/reset user password in Sybase IQ
Alter user identified by
How to check database space usage in Sybase IQ
select substr(DBSpaceName,0,15),DBSpaceType,Usage,TotalSize from sp_iqdbspace()

How to list down tables in Sybase IQ
select distinct table_name from sp_iqtable()

How to list down tables in Sybase IQ of specific schema
select distinct table_name from sp_iqtable() where table_owner=”

How to check server startup time in Sybase IQ
select convert(char(30),@@servername),convert(char(30),property(‘StartTime’))

How to check list of users with their last login time and locked status in Sybase IQ
select convert(char(30),(user_name)),convert(char(30),(last_login_time)),convert(char(6),(locked)),convert(char(30),(reason_locked)) from sa_get_user_status()

How to check blocking on Sybase IQ
select ConnHandle,IQconnID,BlockedOn,BlockUserid from sp_iqwho() where BlockUserid != ‘NULL’

How to check Active transaction running on Sybase IQ
select substr(Userid,0,10) as UserID,substr(ConnHandle,0,5) as ConnHandle,substr(state,0,10) as STATE,substr(TxnCreateTime,0,20) as RunningTime from sp_iqtransaction();

How to kill specific connHandle in Sybase IQ
drop connection

How to show SQLText of specific Connection Handle in Sybase IQ
sp_iqcontext

How to Check Version space in Sybase IQ
select * from sp_iqstatus() where name like ‘%Other Versions:%’

How to unlock user in Sybase IQ
Alter user RESET LOGIN POLICY

How to reset user password in Sybase IQ
grant connect to identified by

How to Check Login Locked Status for all logins in Sybase IQ
select user_id,convert(char(25),user_name),locked from sa_get_user_status()

How to Check Login Locked Status for all specific user in Sybase IQ
select user_id,convert(char(25),user_name),locked from sa_get_user_status() where user_name='{user_name}’

How to Check Login policy and options of a user in Sybase IQ
select a.user_id,convert(char(25),a.user_name),a.login_policy_id,convert(char(25),b.login_policy_name),convert(char(15),c.login_option_name),convert(char(15),c.login_option_value) from sysuser a,sysloginpolicy b,sysloginpolicyoption c where a.user name='{user name}’

How to Check temp space usage details in Sybase IQ
select Top 5 ConnHandle,IQconnID,name,IQCmdType,LastIQCmdTime,ConnCreateTime,NodeAddr,(TempTableSpaceKB+TempWorkSpaceKB) as TempSpaceUsed from sp_iqconnection() order by TempSpaceUsed desc

How to Check DB Options in Sybase IQ
select convert(char(15),User_name) as User_Name,convert(char(35),Option_name) as Option_Name,convert(char(15),Current_value) as Current_values,convert(char(15),Default_value) as Default_value,convert(char(25),Option_type) as Option_type from sp_iqcheckoptions() order by User_Name

How to Check Multiplexing details in Sybase IQ
select substring(server_name,1,20) as server_name,substring(connection_info,1,30) as connection_info,mpx_mode,inc_state,status,substring(coordinator_failover,1,20) as coordinator_failover,substring(db_path,1,40)as db_path from sp_iqmpxinfo();

How to Check remote/external logins details in Sybase IQ
select convert(char(15),s.user_name) as username,convert(char(20),ss.srvname) as srvname,remlogin from sysuser as s,sysserver as ss,sysexternlogin as el where el.srvid=ss.srvid and el.user_id=s.user_id

How to Check remote/external logins details in Sybase IQ
select convert(char(15),s.user_name) as username,convert(char(20),ss.srvname) as srvname,remlogin from sysuser as s,sysserver as ss,sysexternlogin as el where el.srvid=ss.srvid and el.user_id=s.user_id

How to add temp store file in Sybase IQ
alter DBSPACE add file ” size(MB/GB)

User Databases
– Log on to the RepServer and run
admin who_is_down – All threads must be up, if not fix this first or verify there is a good reason why something is down.
admin show_connections – Make a note of the maint user for the user database, e.g. _maint
suspend connection to <dataserver>.<database>
alter connection to <dataserver>.<database> set password **********
– Log on to to the Dataserver and run
alter login with password modify password ******* — (same as above)
– Log on to the RepServer and run
resume connection to <dataserver>.<database>
admin who_is_down – Everything should be back up again

RSSD
Pre-implementation

– Save RSSD users and syslogins tables
Login to the RSSD containing the RSSD
bcp out master..syslogins
In the RSSD database, run (Enable select into, if not enabled):
select * into rs_maintusers_save_YYYYMMDD from rs_maintusers
select * into rs_users_save_YYYYMMDD from rs_users where username like ‘%prim’
Disable select into, if enabled before.

– Save /sybxxx/sybase/config/.cfg file, for example:
cp -p /sybxxx/sybase/config/.cfg /sybxxx/sybase/config/.cfg.YYYMMDD.pre_password_change

– Copy /sybxxx/sybase/config/.cfg file to the install directory, For example:
cp -p /sybxxx/sybase/config/.cfg /sybxxx/sybase/rep/REP-16_0/install/.cfg
Important note: this is very important as rs_init only updates .cfg in REP install directory

– There might be an issue where the prim account got locked immediately after changing the password in ASE, even though the connection was suspended. To fix do:
alter login modify max failed attempts 0;

.

Implementation
– Make sure that the Rep Server is quiesced (admin health) and no thread is down.

– Change the password in the Rep Server configuration file.
— If the password encryption is not enabled, the password is displayed as a plain text, and you can modify the /sybxxx/sybase/config/.cfg file directly using any editor, then reboot the Rep Server
— If the password is encrypted, you need to use rs_init manually to change the password in the Rep Server configuration file:
Login to the Rep Server host
cd /sybxxx/sybase/rep
. Source the environment file by running SYBASE.sh
Run rs_init to modify the encrypted password of the RSSD maint user
$SYBASE/$SYBASE_REP/install/rs_init
See rs_init details below
Repeat the rs_init process to modify the encrypted password of the RSSD prim user
$SYBASE/$SYBASE_REP/install/rs_init
See rs_init details below

— Important note:
If you plan to modify the password for multiple Rep Servers one after another, it’s possible to use a resource file to run rs_init, in all the runs following the first manual run. To do so, you will have to use the same sa-equivalent account for all the servers in the list (presumably only the nbe fits this requirement, as sybadmin is likely to be different, and sa might not be available at some point). Also, you will have to accept that you will be using the same password for all the Prim and Maint accounts in this run across multiple Rep Servers.

To take advantage of the resource file, save it to a file (see details in section 6, see sample resource files in Appendix A), and then make copies of it as needed:

For the resource file of the Prim and the Maint accounts using the same Rep Server, the only difference in the resource file is rs.rs_cfg_option:
RSSD_maint_pw_enc for the Maint account
RSSD_primary_pw_enc for the Prim account

For different Rep Servers – copy one of the resource files to the next host and then make a few changes.
You may need to change sybinit.component_directory, sybinit.release_directory, sybinit.boot_directory, depending on the installation directory.
You will need to change the name of the config file specified by rs.rs_rs_cfg_file (and remember, full path name under the installation directory, not our standard config directory).
And of course, you will need to change rs.rs_name.

To run rs_init using the resource file, use:
rs_init -r

— rs_init details
Select option 2 – Configure a Server product
Select option 1 – Replication Server
Select option 7 – Alter a Replication Server configuration file password
ALTER A REPLICATION SERVER CONFIGURATION FILE PASSWORD
Replication Server Name:
RS SA user: sa (or sa-equivalent, like sybadmin or nbe)
RS SA password:
Replication Server configuration file:
Configuration file parameter for password:
New password:

Select option 1 and enter the Rep Server Name
Select option 2 if you wish to change sa to another sa-equivalent account (like nbe)
Select option 3 to enter the Rep Server sa (or nbe) password
Select option 4 to change the location of the config file, if the default one is not displayed – it should be: /sybxxx/sybase/rep/REP-16_0/install/.cfg
Select option 5 to enter the name of the configuration file password parameter you want to modify. It should be either RSSD_maint_pw_enc or RSSD_primary_pw_enc
Select option 6 to enter the new password for the maint or prim user.

At this point you will see:
ALTER A REPLICATION SERVER CONFIGURATION FILE PASSWORD

Replication Server Name: Repserver_Name
RS SA user: nbe…
RS SA password: Rep_Server_nbe_password
Replication Server configuration file: /sybxxx/sybase/rep/REP-16_0/install/.cfg
Configuration file parameter for password: RSSD_maint_pw_enc or RSSD_primary_pw_enc
New password: new_RSSD_maint_user_password or new_RSSD_prim_user_password

Ctrl-a Accept and Continue, Ctrl-x Exit Screen, ? Help.

If you want to save the resource file now, press ctrl-w and you will be prompted to enter the file name to use. Enter the full path of the file name. You can then proceed to execute the current session.

Press Ctrl-a to accept and continue.

Execute the Replication Server tasks now? n
Press y to continue

You should see the following messages:

Running task: alter a Replication Server configuration file password.
Replication Server ‘SYBAMR_MICS_UAT_RS03’ can now be restarted.
Task succeeded: alter a Replication Server configuration file password.

Configuration completed successfully.
Press to continue.
Press Enter.

You will be returned to the main menu, where you can select 7 again to repeat the process for the prim account or press ctrl-x to exit and then use the resource file if you saved it.

When both changes are done, press Ctrl-x (three times) to exit.

– Login to the Rep Server:
suspend connection to RSSD_server.RSSD_database

– Change RSSD prim and maint user password in ASE RSSD database:
Make sure that the passwords are entered without quotes around them.

— login to RSSD_server
— sp_password caller_password, <NEWPASS> , RSSD_maint_user
— sp_password caller_password, <NEWPASS> , RSSD_prim_user

– Change RSSD prim and maint user password in RS server:
Make sure that the passwords are entered without quotes around them.

— alter connection to RSSD_server.RSSD_database set password to <NEWPASS>
— alter user RSSD_prim_user set password <NEWPASS>

– Resume connection to RSSD
— resume connection to RSSD_server.RSSD_database

– Check the Rep Server to make sure that all threads are up, and that the Rep Server is quiesced.

– Check the connection of the prim and the RSSD maint users to the Rep Server and the RSSD ASE with the new passwords to make sure that everything is working properly.
If any of the four connection tests is not working, STOP here and fix any problem before proceeding to Stop/Start the Replication Server.
For example:
isql -S -U -X -P
isql -S -U -X -P
isql -S -U -X -P
isql -S -U -X -P

– Move the updated Rep Server config file from the install directory back to our standard config directory:
This is very important step
— mv /sybxxx/sybase/rep/REP-16_0/install/.cfg /sybxxx/sybase/config
— cd to /sybxxx/sybase/config
— run diff .cfg.YYYMMDD.pre_password_change .cfg
You should see that the passwords for the old and new RSSD accounts are different

– Shutdown and restart the Replication server.

– Login to the Rep Server and run admin who, no thread should be down at this point. Check the error log for any errors.

– alter login modify max failed attempts 5;

The best approach is to add the second port to the Sybase server and then push out the second port to the Global interfaces file via push and then monitor if the old one is still being used, once it is no longer used it can be dropped. Below are the detailed steps for doing this:

Add the new port with the following command sp_listener ‘start’, ‘protocol:host:port’
e.g. sp_listener start, ‘tcp:camifxlddb01.gbcaydc.baml.com:50000’

Update the local interfaces file for the Sybase Server to add the second port, so that it gets loaded at next restart. You’ll also need to update any other local interface entries for this server, like replication server etc.

The usage of the new port can be checked with the query:
select Login, Application, ClientHost, ListenerPort, getdate() as ‘Collection_Date’
from master..monProcessLookup
where Login is not null
and ListenerPort=<port number>

When you are happy simply, shut down the server, remove the old port from the interfaces file and restart the Sybase server.

1 Ad hoc audit record

2 Alter database

3 Alter table

4 BCP in

5 NULL

6 Bind default

7 Bind message

8 Bind rule

9 Create database

10 Create table

11 Create procedure

12 Create trigger

13 Create rule

14 Create default

15 Create message

16 Create view

17 Access to database

18 Delete table

19 Delete view

20 Disk init

21 Disk refit

22 Disk reinit

23 Disk mirror

24 Disk unmirror

25 Disk remirror

26 Drop database

27 Drop table

28 Drop procedure

29 Drop trigger

30 Drop rule

31 Drop default

32 Drop message

33 Drop view

34 Dump database

35 Dump transaction

36 Fatal error

37 Nonfatal error

38 Execution of stored procedure

39 Execution of trigger

40 Grant command

41 Insert table

42 Insert view

43 Load database

44 Load transaction

45 Log in

46 Log out

47 Revoke command

48 RPC in

49 RPC out

50 Server boot

51 Server shutdown

52 Curread modification

53 Curwrite modification

54 Update mode modification

55 Role toggling

56 NULL

57 NULL

58 Truncation of audit table

59 NULL

60 NULL

61 Access to audit table

62 Select table

63 Select view

64 Truncate table

65 Trusted procedure execution

66 Trusted trigger execution

67 Unbind default

68 Unbind rule

69 Unbind message

70 Update table

71 Update view

72 NULL

73 Auditing enabled

74 Auditing disabled

75 NULL

76 SSO changed password

77 Table change

78 Audit option change

79 NULL

80 Role check performed

81 DBCC command

82 Config

83 Online database

84 Setuser command

85 UDR command

86 Builtin function

87 Disk release

88 Set SSA command

89 Kill/terminate command

90 Connect command

91 Reference

92 Command text

93 JCS install command

94 JCS remove command

95 Unlock admin account

96 Quiesce database command

97 Create SQLJ function

98 Drop SQLJ function

99 SSL administration

100 Disk resize

101 Mount database

102 Unmount database

103 Login command

104 Create index

105 Drop index

Perform the below steps to rollback the patch, if required

• Disable Auditing
  sp_configure “auditing”, 0

• Stop Replication Server, Backup server and Foglight Agents

• Re-start ASE in single user mode by specifying the -m option in RUN File. When starting ASE, you should see the below message in error log
  00:0002:00000:00002:2024/08/13 16:08:05.91 server *** WARNING ******************
  00:0002:00000:00002:2024/08/13 16:08:05.91 server ASE booted single user mode – updates allowed to system catalogs
  00:0002:00000:00002:2024/08/13 16:08:05.91 server WARNING ***************

• Run sp_downgrade_esd on each database. You need not downgrade temporary databases. These include tempdb as well as user-created temporary databases, as they are re-created from a template database when Adaptive Server starts.

Note: Please perform sp_downgrade_esd on all of your non-temporary databases, then downgrade the master database last.

Shown below is an example where the entire instance was downgraded from SP04 PL04 to SP04 PL02
1> use master
2> go
1> select @@version
2> go
—————————————————————————————————————————————————————————————————————————————————————
Adaptive Server Enterprise/16.0 SP04 PL04/EBF 30650 SMP/P/x86_64/SLES 12.4/ase160sp04pl04x/3585/64-bit/FBO/Tue Feb 14 09:59:39 2023
(1 row affected)

1> select name from sysdatabases
2> go
name
——————————
SYBTORE_SYS_DEV_RS09_RSSD
audit_arch
dbccdb
master
model
questdb
sa_tempdb
sybsecurity
sybsystemdb
sybsystemprocs
tempdb
tempdb_surveillance
test_db
(13 rows affected)

1> sp_downgrade_esd SYBTORE_SYS_DEV_RS09_RSSD, “SP04 PL02”
2> go
Reverting database ‘SYBTORE_SYS_DEV_RS09_RSSD’ to SP04 PL02.
Running CHECKPOINT on database ‘SYBTORE_SYS_DEV_RS09_RSSD’ for option ‘downgrade’ to take effect.
Downgrade is complete.
Database ‘SYBTORE_SYS_DEV_RS09_RSSD’ is now suitable for use by SP04 PL02.
(return status = 0)

1> sp_downgrade_esd audit_arch, “SP04 PL02”
2> go
Reverting database ‘audit_arch’ to SP04 PL02.
Running CHECKPOINT on database ‘audit_arch’ for option ‘downgrade’ to take effect.
Downgrade is complete.
Database ‘audit_arch’ is now suitable for use by SP04 PL02.
(return status = 0)

1> sp_downgrade_esd dbccdb, “SP04 PL02”
2> go
Reverting database ‘dbccdb’ to SP04 PL02.
Running CHECKPOINT on database ‘dbccdb’ for option ‘downgrade’ to take effect.
Downgrade is complete.
Database ‘dbccdb’ is now suitable for use by SP04 PL02.
(return status = 0)

1> sp_downgrade_esd model, “SP04 PL02”
2> go
Reverting database ‘model’ to SP04 PL02.
Running CHECKPOINT on database ‘model’ for option ‘downgrade’ to take effect.
Downgrade is complete.
Database ‘model’ is now suitable for use by SP04 PL02.
(return status = 0)

1> sp_downgrade_esd sybsecurity, “SP04 PL02”
2> go
Reverting database ‘sybsecurity’ to SP04 PL02.
Running CHECKPOINT on database ‘sybsecurity’ for option ‘downgrade’ to take effect.
Downgrade is complete.
Database ‘sybsecurity’ is now suitable for use by SP04 PL02.
(return status = 0)

1> sp_downgrade_esd sybsystemdb, “SP04 PL02”
2> go
Reverting database ‘sybsystemdb’ to SP04 PL02.
Running CHECKPOINT on database ‘sybsystemdb’ for option ‘downgrade’ to take effect.
Downgrade is complete.
Database ‘sybsystemdb’ is now suitable for use by SP04 PL02.
(return status = 0)

1> sp_downgrade_esd sybsystemprocs, “SP04 PL02”
2> go
Reverting database ‘sybsystemprocs’ to SP04 PL02.
Running CHECKPOINT on database ‘sybsystemprocs’ for option ‘downgrade’ to take effect.
Downgrade is complete.
Database ‘sybsystemprocs’ is now suitable for use by SP04 PL02.
(return status = 0)

1> sp_downgrade_esd test_db, “SP04 PL02”
2> go
Reverting database ‘test_db’ to SP04 PL02.
Running CHECKPOINT on database ‘test_db’ for option ‘downgrade’ to take effect.
Downgrade is complete.
Database ‘test_db’ is now suitable for use by SP04 PL02.
(return status = 0)

1> sp_downgrade_esd master, “SP04 PL02”
2> go
Reverting database ‘master’ to SP04 PL02.
Running CHECKPOINT on database ‘master’ for option ‘downgrade’ to take effect.
Downgrade is complete.
Database ‘master’ is now suitable for use by SP04 PL02.
(return status = 0)

• Shutdown ASE

• Under /sybdba01/sybase (or equivalent directory) rename ase_16.0 to ase_16.0_. So in your install, you will perform the below move
  cd /sybdba01/sybase
  mv ase_16.0 ase_16.0_sp04_pl05

• Move SP04 PL02 backed up directory under ESD_Rollback to ase_16.0 under Sybase Software File system, as shown below
  cd ESD_Rollback/
  ls -ltr
  total 29280
  -rwxr-x—. 1 sybdba sybase 1024 Sep 28 2023 RUN_SYBTORE_SYS_DEV_DS02_BK
  drwxr-x—. 23 sybdba sybase 4096 Sep 28 2023 ase_16.0_SP04_PL02_EBF29987
  -rwxr-x—. 1 sybdba sybase 1235 Sep 28 2023 RUN_SYBTORE_SYS_DEV_DS02
  -rw-r—–. 1 sybdba sybase 19036 Oct 4 2023 SYBTORE_SYS_DEV_DS02.cfg
  -rw-r—–. 1 sybdba sybase 2336768 Oct 9 2023 SYBTORE_SYS_DEV_RS09_RSSD.master.20231009.065009.dmp
  -rw-r—–. 1 sybdba sybase 26529792 Oct 9 2023 SYBTORE_SYS_DEV_RS09_RSSD.sybsystemprocs.20231009.065009.dmp
  -rw——-. 1 sybdba sybase 1076877 Oct 9 2023 SYBTORE_SYS_DEV_RS09_RSSD_restoremsgs.ebf
  drwxr-x—. 15 sybdba sybase 4096 Oct 9 2023 rep_16.0_EBF_30655_SP04_PL04_rs160sp04pl04

mv ase_16.0_SP04_PL02_EBF29987 ../ase_16.0

• Re-start ASE in regular mode by removing ‘-m’ switch from RUN file, which will start the instance in SP04 PL02 binary

• Run installmaster, installmodel, installcommit, installsecurity, installdbccdb scripts

• Restore the messages by loading _restoremsgs.ebf from ESD_Rollback directory, as shown in below example
  isql -Usa -S SYBTORE_SYS_DEV_DS02 -w2000 -X -i /sybdba02/sybase/ESD_Rollback/SYBTORE_SYS_DEV_DS02_restoremsgs.ebf

• After the downgrade, all the versions should be at SP04 PL02, as shown below
  1> select @@version
  2> go
  —————————————————————————————————————————————————————————————————————————————————————
  Adaptive Server Enterprise/16.0 SP04 PL02/EBF 29987 SMP/P/x86_64/SLES 12.4/ase160sp04pl02x/3545/64-bit/FBO/Thu Mar 17 21:44:05 2022
  (1 row affected)

1> sp_version
2> go
Script Version Status
——————- ———————————————————————————————————————- ——–
ADO.NET MDA Scripts 16.0.04.02.1019/Thu Feb 17 UTC 14:35:44 2022 Complete
ODBC MDA Scripts 16.0.04.02.1019/Tue Feb 15 UTC 16:42:49 2022 Complete
installcommit 16.0 SP04 PL02/EBF 29987 SMP/P/x86_64/SLES 12.4/ase160sp04pl02x/3545/64-bit/OPT/Thu Mar 17 20:15:51 2022 Complete
installdbccdb 16.0 SP04 PL02/EBF 29987 SMP/P/x86_64/SLES 12.4/ase160sp04pl02x/3545/64-bit/OPT/Thu Mar 17 20:15:51 2022 Complete
installjdbc jConnect (TM) for JDBC(TM)/16.0 SP04 PL02 (Build 27518)/P/EBF30180/JDK 1.8.0/jdbcsp04/OPT/Tue Feb 15 01:29:04 PST 2022 Complete
installmaster 16.0 SP04 PL02/EBF 29987 SMP/P/x86_64/SLES 12.4/ase160sp04pl02x/3545/64-bit/OPT/Thu Mar 17 20:15:51 2022 Complete
installmodel 16.0 SP04 PL02/EBF 29987 SMP/P/x86_64/SLES 12.4/ase160sp04pl02x/3545/64-bit/OPT/Thu Mar 17 20:15:51 2022 Complete
installsecurity 16.0 SP04 PL02/EBF 29987 SMP/P/x86_64/SLES 12.4/ase160sp04pl02x/3545/64-bit/OPT/Thu Mar 17 20:15:51 2022 Complete
montables 16.0/29987/P/x86_64/SLES 12.4/ase160sp04pl02x/3545/64-bit/OPT/Thu Mar 17 20:05:28 2022 Complete
(9 rows affected)
(return status = 0)

• Run the Ansible job Sybase Action – SYB_Enable_Audit_Settings to re-enable all audit settings

There are actually 3 tables which will need to be copied over and synced up, they are syslogins, syssrvroles and sysloginroles.

The below steps will vary a bit but for example if you are upgrading from Sybase version 12 to 15 then you would do the following steps, for other ASE versions you might need to change the temp table a bit:

Source Server

The first step is to bcp out these tables (syslogins, syssrvroles and sysloginroles) from the source Sybase server and copy the files over to the destination.

e.g bcp master..syslogins out syslogins.out -U<Username> -S<Servername> -n -X

From destination server scp the files across e.g.

scp zkc3yit@gbzzyxsyordad02.gbcaydc.baml.com:/tmp/t/* /tmp/t

.

.

.

Destination Server

Syslogins

• Create the temporary syslogins table on the new server
  sp_configure ‘allow updates to system tables’,1
  go
  USE tempdb
  go
  /*drop table dbo.temp_syslogins
  go
  */
  CREATE TABLE dbo.temp_syslogins
  (
  suid        int             NOT NULL,
  status      smallint        NOT NULL,
  accdate     datetime        NOT NULL,
  totcpu      int             NOT NULL,
  totio       int             NOT NULL,
  spacelimit  int             NOT NULL,
  timelimit   int             NOT NULL,
  resultlimit int             NOT NULL,
  dbname      sysname          NULL,
  name        sysname     NOT NULL,
  password    varbinary(130)   NULL,
  language    varchar(30)     NULL,
  pwdate      datetime        NULL,
  audflags    int             NULL,
  fullname    varchar(30)     NULL,
  srvname     varchar(30)     NULL,
  logincount  smallint        NULL,
  procid      int             NULL,
  lastlogindate datetime      NULL,
  crdate      datetime        NULL,
  locksuid    int             NULL,
  lockreason  int             NULL,
  lockdate    datetime        NULL,
  lpid           int            NULL,
  crsuid         int            NULL
  )
  LOCK ALLPAGES
  Go
  
  You may need to tweak the column types depending on which version of Sybase you are importing from, check their source syslogins table to double check.
  

• Now bcp in the logins which was bcp’d out on the old Sybase server
  bcp tempdb..temp_syslogins in syslogins.out -Usa -P -SMICOY2 -n -X
  
  
• Make a note of the highest suid currently in master..syslogins
  
  
• Check to see which logins already exist in master..syslogins and identify any clashes
  select s.suid, t.* from tempdb..temp_syslogins t, master..syslogins s
  where t.name =s.name
  go
  
  Make a careful note where there is a discrepancy in suid’s. If any entry in source has a higher suid that the current highest suid in master..syslogins (e.g. if the highest current suid is 7 and ipgbmsed2 is 7 in master..syslogins but 8 in temp-syslogins) then you need to make a note to remove any special privileges away from suid 8 in temp_sysloginroles later on, if it has any. If the suid is within the current highest suid’s then it will be removed automatically in the delete before the insert, so just ignore.
  
  also run
  
  select s.name, t.name, t.*
  from tempdb..temp_syslogins t, master..syslogins s
  where t.suid =s.suid
  and t.name not in (select name from master..syslogins)
  go
  
  Here if you have a situation where an SUID in source clashes with a new one on the destination (i.e they have different names and the source name does not exist in destination) then you will need to increment the suid number in the temp syslogins and also temp sysloginroles table later on.
  
  
• From the second part above Increase the suid of any source login which clashed with destination
  update temp_syslogins
  set suid=3006
  where suid=6
  go
  
  
• Delete existing logins which match by name
  delete from tempdb..temp_syslogins
  where name in (
  select t.name from tempdb..temp_syslogins t, master..syslogins s
  where t.name =s.name
  )
  go
  

• Now copy the syslogins over to the master..syslogins table
  insert into master..syslogins
  select * from tempdb..temp_syslogins
  go
  

.

.

Syssrvroles

• Create the temp_syssrvroles table
  USE tempdb
  go
  CREATE TABLE tempdb..temp_syssrvroles
  (
      srid       int            NOT NULL,
      name       varchar(30)    NOT NULL,
      password   varbinary(128) NULL,
      pwdate     datetime       NULL,
      status     smallint       NULL,
      logincount smallint       NULL,
       locksuid int            NULL,
  lockreason  int           NULL,
  lockdate   datetime       NULL
  )
  go
  

• bcp in the entries from source
  bcp tempdb..temp_syssrvroles in c1p16_sysroles.out -Usa -P -SMICOY2 -n -X
  

• Check to see if there are any clashing srid’s in the source compared to destination, it should be ok from ASE15.7 to 16
  Select s.srid as master_srid, s.name as master_name,
  t.srid as source_srid, t.name as source_name, t.*
  from tempdb..temp_syssrvroles t, master..syssrvroles s
  where t.srid=s.srid
  go
  
  IF there are any clashes from above, which from 15.7 to 16 there should not be, then Update any srid’s which conflict with existing srid’s in master..sysrvroles e.g.
  update tempdb..temp_syssrvroles
  set srid= 133 where srid=33
  go
  
  
• Check to see if there are clashing role names in the source to destination
  Select s.srid as master_srid, s.name as master_name,
  t.srid as source_srid, t.name as source_name, t.*
  from tempdb..temp_syssrvroles t, master..syssrvroles s
  where t.name =s.name
  go
  
  If there are any clashes from the above query then we delete the srid from the temp table, we will also later need to update sysloginroles to use the srid which already exists in master.
  delete from temp_syssrvroles
  where srid=34
  go
  

• Delete the srvroles which already exist in master from the temp table.
  delete from tempdb..temp_syssrvroles
  where srid <=33 –Biggest srid currently in master..syssrvroles is 33
  go
  

• Copy the roles into master.syssrvroles
  insert into master..syssrvroles
  select * from tempdb..temp_syssrvroles
  go

.

.

Sysloginroles

• Create the sysloginroles table
  USE tempdb
  go
  CREATE TABLE dbo.temp_sysloginroles
  (
  suid   int      NOT NULL,
  srid   int      NOT NULL,
  status smallint NOT NULL,
  predid int      NULL
  )
  Go
  

• BCP in the entries from source
  bcp tempdb..temp_sysloginroles in c1p16_sysloginroles.out -Usa -P -SMICOY2 -n -X
  
  
• Update the same suid as you did for syslogins
  update tempdb..temp_sysloginroles
  set suid = 3006
  where suid=6
  go
  
  
• Delete any suid’s from temp_sysloginroles which already existed at the start in master..syslogins and master..sysloginroles
  delete from tempdb..temp_sysloginroles
  where suid <=8 –highest suid recorded earlier
  go
  
  
• Delete any suid’s from temp_sysloginroles which were previously a mismatch between master..syslogins and temp_syslogins
  delete from tempdb..temp_sysloginroles
  where suid in (10)
  go
  

• Update any srid number which you had to update previously in the syssrvroles step e.g.
  update tempdb..temp_sysloginroles
  set srid= 133
  where srid=33 — Updated srid from tempdb..syssrvroles
  go
  
  
• If there were any incidents of the same role name having different srid’s in syssrvroles (the second check) then you need to update the srid:
  update temp_sysloginroles
  set srid=33
  where srid=34
  go
  
  
• Delete any rows from temp_sysloginroles which doesn’t have a matching suid in master..syslogins
  delete from tempdb..temp_sysloginroles
  where suid not in (select suid from master..syslogins)
  
  
• Make sure everything looks ok
  select * from tempdb..temp_sysloginroles
  go
  

• Now Copy the roles into master.sysloginroles
  insert into master..sysloginroles
  select * from tempdb..temp_sysloginroles
  go
  
  
• Remove Update to system tables
  sp_configure “allow updates to system tables”, 0
  go

.

.

.

Synchronize Logins

The next steps relate to synchronizing the suids after you have loaded the old database into the new server.

• First thing is to enable updates to system catalogs
  sp_configure ‘allow updates to system tables’,1
  go
  
  
• Then if you had to alter/increment any suid’s in syslogins earlier then you will also need to do this in the sysusers for any database that has suid of e.g 6:
  update sysusers set suid=3006
  where suid=6
  go
  
  
• Next check whether there are any aliases set up without matching entries in syslogins:
  select * from sysalternates
  where suid not in (select suid from master..syslogins);
  go
  
  If you find any entries which do not match with anything in syslogins then they should be deleted from sysalternates:
  delete from sysalternates
  where suid not in (select suid from master..syslogins)
  go

• The next check is to find situations where the same name in syslogins and sysusers has a different suid.
  select l.suid ‘Login suid’,u.suid ‘User suid’,l.name ‘Login name’, u.name ‘User name’
  from
  master..syslogins l, sysusers u
  where l.name = u.name and l.suid != u.suid and u.name != “dbo”
  go
  
  If this finds any mismatches then the following query will resync the suids in the user database with the suid’s in syslogins.
  update sysusers
  set u.suid = l.suid
  from sysusers u, master..syslogins l
  where l.name=u.name
  go
  
  
• A follow on check is to see whether there are any users who have a different name to their syslogins name but with the same suid’s.
  select l.suid,u.suid,l.name, u.name from master..syslogins l, sysusers u
  where l.suid = u.suid and l.name != u.name and u.name != “dbo”
  go
  
  if that returns some rows then they need to be deleted from sysusers with the following query:
  delete from sysusers
  where suid in (select suid from master..syslogins)
  and name not in (select name from master..syslogins)
  and name != “dbo”
  go
  
  
• You should also check for any users in sysusers which do not match anything in syslogins
  select * from sysusers u
  where u.suid not in (select suid from master..syslogins)
  and name != “dbo”
  and suid > 0
  go
  
  These can be removed with
  delete from sysusers
  where suid not in (select suid from master..syslogins)
  and name != “dbo”
  and suid > 0
  go
  
  
• Disable updates to system tables
  sp_configure ‘allow updates to system tables’,0
  go
  
  

To restore a failed master database, perform the following steps:

1. Run dataserver with the –b and –d options to create a new master device:
   dataserver -d /dev/rsd1b –b 100M
2. Start SAP ASE in single-user mode:
   startserver -f RUN_SYBASE –m
3. You can use the master database, but presently it doesn’t have any references
   to any user databases or sybsystemprocs.
   If master was expanded beyond its default size, run alter database to extend it.
4. Update the backup server name in sysservers if it isn’t SYB_BACKUP:
   begin transaction
   update sysservers set srvnetname = “PROD_BACKUP”
   where servname = “SYB_BACKUP”
   commit transaction
5. Load the backup of the master database; after the load completes, it will shut
   down SAP ASE automatically:
   load database master from “/full/path/name/master.dump”
6. Start SAP ASE in single-user mode, and check that all databases/devices/logins
   appear to be restored correctly.
7. If everything appears okay, you’re nearly done. Shut down and restart SAP ASE
   normally, and skip to step 9.
8. If everything doesn’t appear okay, you have more work to do. If additional
   devices, databases, logins, or users of master were created after the last backup
   of master, you’ll need to re-create them.
   SAP ASE provides two commands to recover existing devices and databases to
   a restored master database:
   – disk reinit
   – disk refit
   Use disk reinit to recover devices created since the last dump of master; disk
   reinit restores information to sysdevices without reinitializing the device,
   which retains the data which is on the device. Values supplied to disk reinit
   should match values supplied to the original disk init command.
   
   The syntax for disk reinit follows:
   disk reinit name = logical_device_name,
   physname = physical_device_name,
   vdevno = virtual_device_number,
   size = number_of_pages | K | G | T
   disk refit is used after running disk reinit to rebuild the sysusages and sysdatabases tables in master from the database fragments found on the restored devices.

9. Assuming you need to run it, after executing disk refit, SAP ASE will automatically
   shut down the server. You should restart SAP ASE in single-user mode
   and verify that all databases are properly restored and are the correct sizes. Run
   dbcc checkalloc() on all recovered databases. If everything appears okay, shut
   down and restart SAP ASE normally. Any changes or additions to logins, configuration
   options, remote servers, remote logins, and roles will still need to be
   re-created.
   
   The moral of the story is to keep frequent backups of the master database! Best
   practice is to schedule a nightly backup of the master database. In addition, back
   up the master database after each command that affects disks, storage, databases,
   segments, logins, roles, and so on.

Since ASE15.7 SP100 you don’t need to worry about creating a database in the same sequence of data and log segments when loading a dump from another database, this is something which you had to do previously.

Now the only consideration is that you have enough space for data device and log device. So for example if you are loading from a database which was created in this sequence; Data 5, Log 5, Log 5, Data 15, Log 5 and finally Data 10. then all you need to do now is;
Create database dbload with data on datadevice=”30M” and log on logdevice=”15M”
and then load the database, the load then sorts the fragments into the correct order automatically.