Re-Sync after Primary DB load

When loading a dump into a primary database, the connections to the primary and the warm-standby databases need to be dropped and rebuild. In this case, also disable the REP-Agents on both DBs:

use <DB>
go
sp_config_rep_agent <DB>,"disable"
go

Do this step after dropping the replication connections. After loading the dump into the primary database, before setting up replication, don't forget to:

• disable the RepAgent again
• drop the maintenance alias user again and re-add it as a regular user.

When just resyncing a Warm-Standby database, follow these instructions:

Sync WS replication

• On primary replication server, drop physical connections:

drop connection to <rep.srv._ws>.<replicated.db>
go

• disalbe rep_agent on WS

use  <replicated.db>
go
sp_config_rep_agent <replicated.db>, "disable"
go

• drop <replicated.db>_maint user or alias in WS db

use  <replicated.db>
go
sp_dropalias <replicated.db>_maint
go

• drop <prim.db>_maint user or alias in prim. db

sp_dropalias <prim.db>_maint
go

• sp_adduser _maint to db

sp_adduser <prim.db>_maint
go

• check logical connection name on REP.srv.

admin logical_status
go

• rebuild WS connection to WS with rs_init
  • When asked if this DB is a replicated DB, answer YES
  • Setup a "physical connection for a logical connection"
  • Setup a standby database
  • "initialise using dump and load"? -> YES
  • "use dump marker"? -> YES

• drop <replicated.db>_maint user on prim

sp_dropuser <prim.db>_maint
go

• add alias (user <prim.db>_maint) to dbo

sp_addalias <prim.db>_maint, dbo
go

• dump and load db

dump database <prim.db> to "/dumpdir/<prim.db>
go

load database <replicated.db> from "/dumpdir/<prim.db>.bck"
go

• set db online

online database <replicated.db>
go

• resume connection to WS

resume connection to <rep.srv._ws>.<replicated.db>
go

If you have any problems resuming the connection and you find messages like these in the Rep-Errorlog:

"The DSI thread for database '<rep.srv._ws>.<replicated.db>' is being
shutdown. DSI received data server error #17232 which is mapped to
STOP_REPLICATION. See logged data server errors for more information.
"The error was caused by line #158 of stored procedure 'sp_dropuser'
on the data server mapped from input command #2 (or a command before
it) of the failed transaction."

or:

"The DSI thread for database '<rep.srv._ws>.<replicated.db>' is being
shutdown. DSI received data server error #17234 which is mapped to
STOP_REPLICATION. See logged data server errors for more information.
The error was caused by line #167 of stored procedure 'sp_addalias'
on the data server mapped from input command #2 (or a command before
it) of the failed transaction."

then you probably did something wrong while dropping and recreating the 'maintenance user'. Repeat the following command until you find a different error message. Doing this, you will skip the "create-" and "drop-user" commands, which you don't need on the replicated system.

resume connection to <rep.srv._ws>.<replicated.db> skip tran
go

In some cases the utility_db cannot be started due to the following error:

I. 04/20 02:18:25.      Adaptive Server IQ
I. 04/20 02:18:25.       Version 12.7
I. 04/20 02:18:25.        (64bit mode)
I. 04/20 02:18:25. Copyright 1992-2008 by Sybase, Inc. All rights reserved
I. 04/20 02:18:25.
I. 04/20 02:18:25. 40 physical processor(s) detected.
I. 04/20 02:18:25. Running on SunOS 5.10 Generic_138888-07
I. 04/20 02:18:25. 49152K of memory used for caching
I. 04/20 02:18:25. Minimum cache size: 49152K, maximum cache size: 262008K
I. 04/20 02:18:25. Using a maximum page size of 4096 bytes
I. 04/20 02:18:25. Database server started at Tue Apr 20 2010 02:18
I. 04/20 02:18:25. Trying to start SharedMemory link ...
I. 04/20 02:18:25.     SharedMemory link started successfully
I. 04/20 02:18:25. Trying to start TCPIP link ...
I. 04/20 02:18:25. Starting on port 2638
I. 04/20 02:18:25. Server name already in use
I. 04/20 02:18:25. TCPIP communication link not started
E. 04/20 02:18:25. A database server with that name has already started
I. 04/20 02:18:25. Database server stopped at Tue Apr 20 2010 02:18
DBSPAWN ERROR:  -85
Communication error

Possible reasons:

1. The server port number is already in use

Check the output of netstat -a | grep <PortNumber>

2. IQ server with the same server name already exists on the subnet

Add the -z option to the start script (e.g. start_asiq -n utility_db -z), check the network diagnostic messages below and try to find another IQ server with the same name on the subnet (ping -s <BroadcastAddress>).

I. 04/20 11:05:35. Looking for server with name utility_db
I. 04/20 11:05:35. Sending broadcast to find server
I. 04/20 11:05:35. Using broadcast address of: 140.16.48.255:2638
I. 04/20 11:05:35. Looking for server with name utility_db
I. 04/20 11:05:35. Sending broadcast to find server
I. 04/20 11:05:35. Using broadcast address of: 127.0.0.1:2638
I. 04/20 11:05:35. Server name already in use

Workarounds:

Continue Reading »

A search argument is the criteria used in a WHERE clause of as query. it is used to restrict a query to an exact match or a range of values. SARG is a acronym for the term Search ARGument.

show_missing_stats shows details of useful statistics missing from SARG / Join columns.

It is important to note there are several situations where show_missing_stats will not print.

1. If you run a "stored-procedure" and there is a current plan in the master..sysqueryplans", the "show_missing_statistics" will not print anything. You can do one of 2 things.
• Run the stored-procedure with the recompile option. i.e. "exec with recompile"
• If the problem is a long running query and you do not want to wait a long time for the result then before you run the query you can execute "set noexec on". The only problem is, if there is a current query plan in sysqueryplans, even with the "with recompile" it won't work. In this situation you would first have to delete the query plan from sysqueryplans.
2. If you run an ad-hoc query the query plans for these are not stored in sysqueryplans. Ad-hoc query plan is created every time you run any ad-hoc query unless you have activated "statement cache". If "statement cache" is active on the server then you have the same problem as with "stored-procedures" and "procedure cache". Fortunately there is an easy solution here. At the session level "set statement_cache off" and the problem is solved.
3. If "capture metrics" is activated on the server this can cause "show_missing_stats" not to print. Again there is a simple solution for this that is to "set metrics capture off" at the session level and the problem is solved.

When trying to extract data from a text or image field, it is not always clear, how many bytes of data can be expected. Here's a way how to determine this:

 
1> SET textsize 1669842
2> go
1> SELECT Xml FROM TradeXml WHERE AsOfDate = '20091019' AND TradeId = 'xxxxxxx'
2> go
 

In order to determine the value that needs to be set for "textsize", use this query:

 
1> SELECT datalength(Xml) FROM TradeXml WHERE AsOfDate = '20091019' AND TradeId = 'xxxxxxx'
2> go
 
 -----------
     1669842
 

This determines the size of bytes for the data field query.

The system stored procedure sp_iqaddlogin is different in IQ 12.7 and IQ 15.1

The main difference ist the syntax and it seems that the 15-version might have a bug.

Neither sp_iqaddlogin, nor sp_iqpassword will accept symbols in passwords, such as "=", "%", "!", "+". Strangely the dollar symbol is accepted. The following error message is generated;

Msg 102, Level 15, State 0:
SQL Anywhere Error -131: Syntax error near '+' ON line 1

Also a password can not start with a number. The following error message is generated;

Msg 102, Level 15, State 0:
SQL Anywhere Error -131: Syntax error near '4' ON line 1

The Sybase documentation contains the recommendation that only 7-bit ASCII characters are used for passwords. These symbols are included in the 7-bit ASCII character set and are acceptable in passwords on IQ 12.7 Servers.

More details can be found here: sp_iqaddlogin - unterschied zwischen IQ 12.7 und IQ 15/ a post on the german blog sybinfo.de.

Log on to the server

$ devinfo -i /dev/md/rdsk/d301
/dev/md/rdsk/d301 0 0 16384 512 1 <-- This is the block size
$ devinfo -p /dev/md/rdsk/d301
/dev/md/rdsk/d301 55 c12d 16384 524288 0 0 <-- this is the device size in blocks


To calculate the device size in bytes, you need both bits of information obtained from the above commands as follows:

Device size = ( (524288 / (1024 / 512) ) / 1024 = 256MB

It is possible to drop DBSPACEs on an IQ Server, if there is enough space to move all data to other DBSPACEs.
1. Check DBSPACEs
The sp_iqdbspace command should be used to list all DBSPACEs and their status.
(DBA)> sp_iqdbspace
Execution time: 0.053 seconds
Name Path SegmentType RWMode Usage DBSSize Reserve StripeSize BlkTypes FirstBlk LastBlk
----------------------------------------------------------------------------------------------------------
IQ_SYSTEM_MAIN /var/sybase/IQ_Server/devices/IQstore.50 MAIN RW 1 65G 0B 8K 1H,320F,32D,128M 1 8516599
IQ_51 /var/sybase/IQ_Server/devices/IQstore51 MAIN RW 4 65G 0B 8K 1H,32F 9408960 9409959
IQ_52 /var/sybase/IQ_Server/devices/IQstore52 MAIN RW 4 65G 0B 8K 1H,32F 10454400 10455399
IQ_SYSTEM_TEMP /var/sybase/IQ_Server/devices/IQtmp.10 TEMPORARY RW 1 33G 0B 8K 1H,192F,16A 1 4325272
TMP_11 /var/sybase/IQ_Server/devices/IQtmp11 TEMPORARY RW 65 33G 0B 8K 1H,64F 5227200 5227299

2. Change the DBSPACE Status
The DBSPACE to be dropped should be put into "relocate" status using the "alter dbspace" command.
(DBA)> alter dbspace IQ_51 relocate
Execution time: 0.18 seconds

A DBSPACE can have one of three statuses, displayed in the RW column of the sp_iqdbspace output; RW - readwrite, RO - readonly, RR - relocate .

3. Use sp_iqrelocate
(DBA)> sp_iqrelocate database
Execution time: 0.038 seconds
Object Blocks Relocated Status
------------------------------
(First 0 rows)
(DBA)> commit
Execution time: 0.012 seconds

Whilst in relocate mode, no data will be written to the DBSPACE and next time the sp_iqrelocate command is used data on that DBSPACE will be moved to other DBSPACEs. NOTE: sp_iqrelocate can only be used with MAIN DBSPACEs. If used with a TEMP DBSPACE you will see the following error message;
sp_iqrelocate database
Could not execute statement.
There must be at least one readwrite dbspace and one relocate dbspace to
relocate data.
-- (db_iqutility.cxx 6894)
SQLCODE=-1009061, ODBC 3 State="HY000"
Line 1, column 1
sp_iqrelocate database

So, if the DBSPACE is a MAIN DBSPACE execute "sp_iqrelocate database" followed by a commit, because sp_iqrelocate does not automatically commit. If the DBSPACE is a TEMP DBSPACE this step can be skipped.

4. Drop DBSPACE
Execute the "sp_dbspace" command.
(DBA)> drop dbspace IQ_55
Execution time: 0.221 seconds
If the DBSPACE is a TEMP DBSPACE it may be necessary to restart the IQ Server to free Temporary Blocks, before dropping the DBSPACE.

The relocate steps are deeper explained here: IQ sp_iqrelocate database - written in german.

Corrupt transaction log; Build a new ASE transaction log with dbcc rebuild_log

In some cases it is necessary to rebuild the ASE transaction log.

Sybase Technical Support states that this is extremely dangerous. It may cause potential database corruption if the system fails while the timestamp rolls over.

• dump the database first. If "dbcc rebuild_log" corrupts the database you can give it a second try.
• login to the ASE
• run

  SELECT count(*) FROM db_name_with_corrupt_log..syslogs

  the result is an int-value higher than 1.

configure the ASE to allow updates on systemtables:

sp_configure "allow updates",1

• identify the "status" from sysdatabases with:

  SELECT STATUS FROM master..sysdatabases WHERE name = "db_name_with_corrupt_log"

  The result is "your_db_status" which is a int-value. Save it.

• set the status of "db_name_with_corrupt_log" to "bypass recovery mode" (-32786).

  begin tran
  go
  UPDATE sysdatabases SET STATUS = -32768 WHERE name = "db_name_with_corrupt_log"
  go
  commit tran
  go

• if the update was successfull shutdown and reboot the ASE
• than run first

  dbcc rebuild_log (db_name_with_corrupt_log, 0, 0)

• and afterwards

  dbcc rebuild_log (db_name_with_corrupt_log, 1, 1)

• USE db_name_with_corrupt_log

• check the syslogs of db_name_with_corrupt_log. It should be 1.

  SELECT count(*) FROM syslogs

• set the status in sysdatabases back to the original one. Run:

  begin tran
  go
  UPDATE sysdatabases SET STATUS = <your_db_status> WHERE name = "db_name_with_corrupt_log"
  go
  commit tran
  go

• reboot the ASE again.