Remove MSA Database

1) Drop Subscription
2) Drop Rep Def
3) Stop Rep agent
4) Disable Rep Agent, sp_config_rep_agent , ‘disable’
5) Check that secondary truncation point is rmeoved
6) Alter connection to set log transfer off

.

Remove a RepAgent

– First drop any subscriptions and then rep defs associated with the primary Database where you want to drop the RepAgent
You can check this with rs_helpsub and rs_helprep formthe RSSD

– Drop any Subscriptions and RepDefs with e.g drop replication definition for a table rep def or equivalent commands for Database rep defs.

– Next disable the repagent from the Primary DB with sp_config_rep_agent sierra, “disable”

– Finally on the RepServer disable replication/log transfer from Primary DB with alter connection to SYBEMA_SIEPCE_UAT_DS02.sierra set log transfer off

Run these in the relevant RSSD database for the Repserver (ID):
admin config, id_server – To find the Rep ID Server
admin rssd_name – To find RSSD database for RepServer

rs_helprep – Displays a list of table and function repdefs in current RSSD

rs_helpsub – Displays a list of all subscriptions to table and function repdefs in the current replicate RSSD.

rs_helprepdb – Displays list of all replicate databases with subscriptions to primary data in current Repserver

rs_helpdbrep – Displays all database repdefs in the current RRSD

rs_helpdbsub – Displays all subscriptions to database repdefs in current, replicate RSSD.

If you have a situation where the tempdb is full then you can normally bind your login to the sa_tempdb or equivalent, here are the steps on how to do this:

– Do a select * from sysdatabases to see if there are any extra tempdb’s configured, if there aren’t any then you can easily create one as follows:
create temporary database to_tempdb on =’50M’ log on =’50M’

– Bind your elevated login to the extra tempdb with:
sp_tempdb ‘bind’, ‘LG’, zec3yit, ‘DB’, sa_tempdb, NULL, ‘soft’;

– Then log out and back in again and you should be able to do sp_who and other stored procs again.

– To try to clear the tempdb look for long running transactions and kill them:
select * from master..syslogshold

– You can also try to abort all transactions in log suspend on the tempdb with this command:
select lct_admin(“abort”, 0, 2)

To Kill any logins before doing for example a failover use stored proc sp__kill_all:
e.g.
sp__kill_all
or
sp__kill_all ‘kill_m_all’ — which kills all the user logins on the Server.

If you just want to kill Logins for a particular database, if you are loading into it for example, then there is another stored proc sp__kill_db
e.g
sp__kill_db

.

create procedure dbo.sp__kill_all
/* Copyright (c) 2003 Rob Verschoor/Sypron B.V. */
@p_login varchar(30)
as
begin
declare @p int, @cmd varchar(12), @suid int
declare @kpid int, @spidcmd varchar(16), @status varchar(12)
declare @login varchar(30), @xactyn varchar(3)
declare @rc int, @err int
set nocount on

if proc_role(“sa_role”) = 0
begin
print “You need ‘sa_role’ to run this procedure.”
return 0
end

if @p_login = “?”
begin
print ” ”
print “Usage: sp__kill_all { login_name | ‘kill_m_all’ }”
print “”
print “This procedure kills multiple ASE processes as specified:”
print ” ”
print “Arguments:”
print ” login_name – kills all processes for this login name”
print ” ‘kill_m_all’ – kills all processes”
print “”
print “Copyright (c) 2003 Rob Verschoor/Sypron B.V.”
print “Visit http://www.sypron.nl”
print ” ”
return 0
end

select @suid = NULL
if @p_login != “kill_m_all”
begin
select @suid = suser_id(@p_login)
if @suid = NULL
begin
print “‘%1!’ is not a valid login name”, @p_login
return 0
end
end

select spid, kpid, status, cmd, suid, tran_name
into #to_be_killed
from master..sysprocesses
where suid = isnull(@suid, suid)
and suid != 0
and spid != @@spid
select @rc = @@rowcount, @err = @@error

if @p_login != “kill_m_all”
print “Found %1! spids for login ‘%2!’”, @rc, @p_login
else
print “Found %1! spids”, @rc

/* Adaptive Server has expanded all ‘*’ elements in the following statement */ select #to_be_killed.spid, #to_be_killed.kpid, #to_be_killed.status, #to_be_killed.cmd, #to_be_killed.suid, #to_be_killed.tran_name into #tmp_2bkilled
from #to_be_killed

while 1=1
begin
set rowcount 1 — process row by row
select @p = spid, @kpid = kpid, @spidcmd = rtrim(cmd),
@xactyn = case tran_name when NULL then “no” when “” then “no” else “yes” end,
@status = rtrim(status), @login = suser_name(suid)
from #tmp_2bkilled
if @@rowcount = 0 break — exit loop when ready

delete #tmp_2bkilled where spid = @p
set rowcount 0

print “Killing spid %1! (login ‘%2!’; %3!,%4!; active Xact:%5!)”, @p, @login, @status, @spidcmd, @xactyn
select @cmd = “kill ” + str(@p, 5)
exec(@cmd)

— In pre-12.0, CIS can be used instead of exec-immediate. See page 60 in the book
— “Tips, Tricks & Recipes for Sybase ASE” (www.sypron.nl/ttr)
–exec sp_remotesql SYB_ALIAS, @cmd
end

set rowcount 0

— wait a sec and see who’s still there
waitfor delay “00:00:01”

select t.spid, t.kpid, t.status, t.cmd, t.suid
into #still_there
from master..sysprocesses sp, #to_be_killed t
where sp.spid = t.spid
and sp.kpid = t.kpid
select @rc = @@rowcount, @err = @@error

if @rc != 0
begin
print “”
print “%1! killed processes are still around:”, @rc
select spid, suser_name(suid) login, cmd, status
from #still_there
order by spid

print ” ”
print “Wait a while until these processes have disappeared…”
end
end

go

.

.

create procedure dbo.sp__kill_db (@DATABASE varchar (30)) as
set ansinull on
set close on endtran on
set flushmessage on
set nocount on
set string_rtruncation on

if (@@trancount = 0)
begin
set chained off

if (@@isolation > 1)
begin
set transaction isolation level 1
end
end
else
begin
print ‘ sp__kill_db CANNOT BE RUN FROM WITHIN A TRANSACTION.’

print ‘ ‘

return 1
end

declare @cmdstring varchar (255),
@errorchar varchar ( 4),
@id int,
@login varchar ( 30),
@spid smallint,
@spidchar varchar ( 11)

print ‘ ‘

declare killdb_cursor cursor for
select spid, suser_name (suid)
from master.dbo.sysprocesses
where dbid = db_id (@DATABASE)
and dbid > 1
and spid @@spid
order by spid
for read only

if (charindex (‘sa_role’, show_role ()) > 0)
begin
if (@DATABASE = ‘master’)
begin
print ‘ YOU CANNOT EXECUTE sp__kill_db FOR DATABASE master.’

print ‘ ‘

return 1
end
else if (db_id (@DATABASE) IS NULL)
begin
print ‘ DATABASE %1! DOES NOT EXIST.’, @DATABASE

print ‘ ‘

return 1
end
else
begin
open killdb_cursor

fetch killdb_cursor into @spid, @login

if (@@sqlstatus = 2)
begin
print ‘ THERE ARE NO ACTIVE PROCESSES FOR DATABASE %1!.’, @DATABASE
end
else
begin
print ‘ SPIDS WILL BE KILLED FOR DATABASE %1!.’, @DATABASE

print ‘ ‘

while (@@sqlstatus = 0)
begin
select @spidchar = convert (varchar (11), @spid)

select @cmdstring = ‘kill ‘ + @spidchar

print ‘ KILLING SPID %1! FOR LOGIN %2!.’,
@spidchar, @login

execute (@cmdstring)

fetch killdb_cursor into @spid, @login
end

print ‘ ‘

if (@@sqlstatus = 2)
begin
— execute sp_status @PARM=@DATABASE
print ‘ ALL APPROPRIATE PROCESSES HAVE BEEN KILLED.’
end
else
begin
select @errorchar = convert (varchar (4), @@sqlstatus)

print ‘ CURSOR ERROR. STATUS = %1!.’, @errorchar

return 1
end
end

close killdb_cursor

deallocate cursor killdb_cursor
end
end
else
begin
print ‘ ONLY A SYSTEM ADMINISTRATOR CAN EXECUTE sp__kill_db.’

print ‘ ‘
end

go

If a user has a proc which needs to do for example a truncate within then the user needs ot have elevated rights, typically dbo owner, to work, if thi si snot possible then you need to use a wrapper stored proc to temporarily elevate the user’s access while running the proc.

The steps are as follows:

– create role sii_role with passwd ‘********’

– grant role sa_role to sii_role

– grant role sii_role to — this gets added as inactive by default so needs to be set to on, i.e its safe

– Create the wrapper stored proc, e.g:
create procedure iSP_bcp_ts_gross_trade_audit_wrap
(
@replay_mode char(1) = NULL –The same variables as in the stored proc being called
)
as
begin
/*
This proc calls SP_bcp_ts_gross_trade_audit.
Call order is:
iSP_bcp_ts_gross_trade_audit_wrap
iSP_bcp_ts_gross_trade_audit
*/

set nocount on

— enable sa_role, DBA’s to replace BLAH with real password and run sp_hidetext
set role sii_role with passwd “********” on
–We have sa_role so run original code
exec iSP_bcp_ts_gross_trade_audit @replay_mode
— disable sa_role
set role sii_role off

return 0
end
GO

– sp_hidetext iSP_bcp_ts_gross_trade_audit_wrap

– grant execute on iSP_bcp_ts_gross_trade_audit_wrap to

– The user can then execute this new wrapper stored proc as they did the old stored proc and it should work fine.

#!/bin/ksh
# *******************************************************************************
#
# Filename: Extract_Users_Permissions.sh
# ———
#
# Description: This script uses ddlgen to extract all the users prior to a refresh.
# ———— These users can then easily be re-added afterwards.
#
# ———–
#
#
# *****************************************************************************/

# Source variables
SERVER=$1
DATABASE=$2
USERNAME=sa
PASSWORD=`/sybdba/sybutils/dba/utils/*******.ksh ${SERVER} ${USERNAME}`
INTERFACES=”/home/sybase/interfaces”
COLLECTION_DATE=`date +”%Y%m%d_%H%M%S”`
BASEDIR=/tmp/USER_EXTRACT
TMPDIR=”${BASEDIR}/tmp”
OUTPUTDIR=”${BASEDIR}/output”
ALIASFILE=”${SERVER}_${DATABASE}_${COLLECTION_DATE}_alias_list.tmp”
LOGFILE=”${SERVER}_${DATABASE}_${COLLECTION_DATE}_logfile.out”
ISQL=”/home/sybdba/ase_16.04-pl02/OCS-16_0/bin/isql -X”

# Check Usage
if [ $# != 2 ]
then
echo “Usage : $0 ”
exit 0
fi

# Check directories exist etc
if [ ! -d “${BASEDIR}” ]; then
mkdir ${BASEDIR}
fi
if [ ! -d “${TMPDIR}” ]; then
mkdir ${TMPDIR}
fi
if [ ! -d “${OUTPUTDIR}” ]; then
mkdir ${OUTPUTDIR}
fi
if [ -f “${OUTPUTDIR}/${SERVER}_${DATABASE}” ]; then
rm -f ${OUTPUTDIR}/${SERVER}_${DATABASE}
fi

# Generate sp_drop and adduser statements
echo “———————————————————-” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “– USERS SECTION” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “———————————————————-” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
${ISQL} -U${USERNAME} -P${PASSWORD} -w999 -S${SERVER} -D${DATABASE} -I${INTERFACES} </dev/null
set flushmessage on
set nocount on
go
select “sp_dropuser ” + u.name + CHAR(10) + “GO”
from sysusers u, sysusers g
where u.gid *= g.uid
and u.uid between 4 and 16382
go
select “sp_adduser ” + u.name + “, ” + u.name + “, ” + g.name + CHAR(10) + “GO”
from sysusers u, sysusers g
where u.gid *= g.uid
and u.uid between 4 and 16382
go
END

# Extract the aliases
${ISQL} -U${USERNAME} -P${PASSWORD} -w999 -S${SERVER} -D${DATABASE} -I${INTERFACES} </dev/null
set flushmessage on
set nocount on
go
select l.name from master..syslogins l, sysalternates a
where a.suid=l.suid
and a.altsuid=1
go
END2

# Run sql to extract User permissions
echo ” ”
echo “Extracting user permissions”
echo “———————————————————-” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “USERS PERMISSIONS” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
${ISQL} -U${USERNAME} -P${PASSWORD} -w999 -S${SERVER} -D${DATABASE} -I${INTERFACES} </dev/null
set flushmessage on
set nocount on
go
select case when a.protecttype =1 then “GRANT” end, ” “,
case when a.action=151 then “references”
when a.action=193 then “select”
when a.action=195 then “insert”
when a.action=196 then “delete”
when a.action=197 then “update”
when a.action=224 then “execute”
when a.action=282 then “delete statistics”
when a.action=320 then “truncate table”
when a.action=326 then “update statistics”
when a.action=368 then “Transfer Table”
end, ” on “, convert(varchar(50),object_name(a.id)), ” to”, user_name(a.uid) + CHAR(10) + ‘GO’
from sysprotects a, sysobjects b, sysusers c
where a.id=b.id
and a.uid=c.uid
and b.type in (‘U’, ‘P’, ‘V’)
and c.uid between 4 and 16382
and c.suid >= 0
order by 3
go
END3

# Add in any Aliases
echo ” ”
echo ” ” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “———————————————————-” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “– ALIASES SECTION” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “———————————————————-” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
for alias_user in `cat ${TMPDIR}/${ALIASFILE}`
do
echo “Creating sp_addalias for Alias ${alias_user}”
echo “sp_addalias ${alias_user}, dbo” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “go” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo ” ”
echo ” ”
done

#
#
# NOW RESTORE THE DATABASE
#
# Recreate the users and aliases by running in the ddlgen extracts from above
echo “Recreate the users and aliases by running the following and supplying the password:”
echo “isql -X -U${USERNAME} -w999 -S${SERVER} -D${DATABASE} -I${INTERFACES} -i${OUTPUTDIR}/${SERVER}_${DATABASE}”
echo ” “

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