Q10.2.2

Technical News Volume 4, Number 2, May 1995

This issue of SYBASE Technical News contains new information about your SYBASE software. If needed, duplicate this newsletter and distribute it to others in your organization. All issues of SYBASE Technical News and the troubleshooting guides are included on the AnswerBase CD.

To receive this document by regular email, send name, full internet address and customer ID to technews@sybase.com.


IN THIS ISSUE


How Technical Support Uses Case Survey Metrics

Sybase Technical Support has been sending out a Technical Support Case Closure Satisfaction Survey for 16 months (September 1993 through January 1995). In October 1994, based on feedback from customers who had responded to the survey, we modified the survey process such that:

The feedback received is being used in the following manner to drive corrective action and process improvements: We want to thank all of you who have taken the time to respond to our survey and ask that you continue. We have heard very clearly that electronic delivery of surveys would make it easier for you to continue to respond and we are pursuing that capability.

HP: Mirror Fail-Over and installasynch80

Question

In testing mirror fail-over on HP, I cannot get the server to switch over when the primary device is powered off. What is the problem?

Answer

This problem has been tracked to the installasynch80 script. The mknod command for async is in error. The incorrect command included in the script is as follows: 
 /etc/mknod /dev/async c 101 0
The command should read as follows: 
 /etc/mknod /dev/async c 101 0x7
Here is the corrected section in the installasynch80 script, which you will find in your $SYBASE/install directory: 
#
# create_devices creates the async i/o devoce file
#
create_devices()
{
echo ""
echo "  We are about to create the asynchronous raw disk i/o device."
echo "  If this device already exists, it will be replaced. "

# prompt user for continuation
#
checkanswer 

echo "  Creating asynchronous i/o device....."
rm -f /dev/async  /dev/asynch[0-9]*
/etc/mknod /dev/async  c  101  0x7
chmod 0660 /dev/async
chown sybase /dev/async
chgrp $GROUP /dev/async
echo ""
echo "  Async device has been created. "
}

buildmaster -y and -r Options to Become Obsolete

A new configuration interface for the next generation of SQL Servers after System 10[TM] will render both the -y and -r command line flags to buildmaster obsolete.

All configurable options (cdbnum, cmemsize, cfgxdes, and so on) will be placed in a configuration file, which is in a text format.

In System 10 and earlier releases, buildmaster -y and -r are used to change the values of options in the config block of SQL Server. They will no longer be needed because the new configuration interface will allow users to modify the values in a flat text config file. Also, the names in the config file will not be cdbnum, cmemsize, and the like, but will be "number of databases", "total memory", and so forth, rendering the information more understandable.

If the config file cannot be booted, SQL Server will still be able to start under the default settings (formerly the -r option) by not specifying a configuration file.

In addition, the dynamically tunable parameters will be changed with the sp_configure command, not when reconfigure is run.

Watch your Technical News for more information as it becomes available.

dump transaction Fails to Truncate Log

WARNING! Although the following procedure has been tested rigorously and used at many customer sites, it uses undocumented and unsupported dbcc commands.

If you are issuing a dump transaction command and find that the log is not being truncated, it is possible that an open transaction is preventing the log from being cleared. This occurs because dump transaction will only truncate the log up to, but not including, the log page that contains the begin transaction for the oldest active transaction. You can use the following procedure to determine the spid, suid, and time of the open transaction (you must be the "sa" user or have the sa role to use this procedure):

  1. Enable dbcc command output to appear on the screen: 
    dbcc traceon(3604)
go
  2. Print the oldest outstanding checkpoint record. 
    dbcc log(dbid,0,0,0,-1,17,0)
go
  3. The output from this allows you to find the ID of the oldest active transaction. Output will be similar to the following: 
    LOG RECORDS:
  CHECKPOINT(262 ,12)
  attcnt=1 rno=3 op=17 padlen=0 xactid=(262 ,12) len=60
  status=0x0000 /* ^^^ length field */
  rows=11407892, pages=79 extents=288
  status=4096 timestamp=0x0001 0x0000811d
  active xacts:(262,3)  /* oldest active xact ID */
    NOTE: This technique is not valid if the length field has a value of 52 or less. In this case, execute a checkpoint and try again.
  4. Display the begin transaction record associated with the output active transaction ID. Note that the third and fourth parameter values of the following command come from the active xacts field above: 
    dbcc log(dbid,1,262,3,1,0,1)
go
    Output in the example case looks like this: 
    LOG RECORDS:
  BEGINXACT(262 , 3)
  attcnt=1 rno=3 op=0 padlen=3 xactid=(262,3) len=76
  status=0x2C20
  masterid=(0,0) lastrec=(0,0) xstat=XBEG_ENDXACT
>>>>>>  spid=5 suid=1 uid=1 masterdbid=0 mastersite=0 endstat=3
^ name=user_transaction    time=Sep 20 1991   1:02PM
^
^ SPID and SUID of process owning outstanding xact
  5. Get the login name that matches the suid: 
    use master
go
select name from syslogins where suid = suid 
go
Now run sp_who to see if there is an entry that contains both the SPID and login name obtained above. If so, follow up with the user to find out what their transaction is and if they can exit their program or isql session or issue a commit tran. Alternatively, you can immediately kill the process yourself. This should terminate the process and resolve the blocking transaction. Issuing the dump transaction command again should clear the log.

If the log is still not being cleared, or if there is not a matching entry in sp_who, then the user that issued the blocking begin tran is no longer logged onto SQL Server but SQL Server is unaware of this. This occurs primarily when a user connecting from a PC client has powered off the PC in the middle of a transaction, has exited a third-party application by pressing ctrl-C, or has encountered network errors on terminating the connection to SQL Server. In this case, if kill did not work, the only recourse is to reboot SQL Server.

If a user was not knowingly holding an open transaction, you can examine your SQL Server error log for clues to help you diagnose why the transaction was left open. Messages to look for include "current process infected", "network error was encountered" (Error 1608), "host process disconnected," and stack traces. Sybase Technical Support can help you analyze these errors. If you have a UNIX environment where this problem is occurring frequently, you may want to adjust your keepalive kernel parameter to a lower value. See your SYBASE System Administration Guide Supplement, operating system documentation, or contact your operating system vendor for more information on the keepalive parameter and how to set it. If your environment consists of a number of users utilizing a single login to access SQL Server, Sybase Technical Support can give you trace flags to help track down the user who is causing the problem.

NOTE: PowerBuilder users: When keepalive fails to clear a connection, it may have occurred because PowerBuilder had a General Protection Fault and the user simply restarted Windows. This leaves the LAN transport software/firmware holding the connection open to SQL Server. keepalive will not clear the connection, since the client side LAN is still holding the old connection. When the user starts PowerBuilder again, it will get a new connection to SQL Server.

You can use sp_who or netstat to see if the old user connection is still active.

The only way to clean up the connection is to turn off the PC that opened the connection; this resets the connection and allows keepalive to do the proper cleanup on the SQL Server side.

How to Read dbcc checkalloc Output

Question

When examining the output of a dbcc checkalloc command, I notice that only one page seems to be used per extent on many allocation pages. Why?

Here is a sample of the output: 

[...]
Alloc page 16128 (# of extent=1 used pages=1 ref pages=1)
Alloc page 16384 (# of extent=1 used pages=1 ref pages=1)
Alloc page 16640 (# of extent=1 used pages=1 ref pages=1)
[...]

Answer

An extent is a group of eight pages. Every 256th page in a database is an allocation page, which is used to track the space being used.

The above output does not show that one page is used per extent on many allocation units; it shows that one extent is in use and that, on that extent, one page is allocated. That is, exactly one page is allocated out of that group of 256 pages.

That page is, of course, the allocation page itself, which is always allocated. The other 255 pages on each such allocation unit are available to whatever object needs the space.

NOTE: Normally an extent is owned by whatever object/index combination first grabs space on it. The first extent on each allocation unit, which controls the allocation page itself, is an exception: the allocation page is always allocated, but the extent is considered free if that page is the only one allocated.

Replication Server System Database Recovery

Following the recovery procedure outlined in the Replication Server Administration Guide dated October 21, 1993, for Replication Server System Database (RSSD) recovery could possibly put the Log Transfer Manager (LTM) into an infinite loop.

Please substitute the following revised sections for the sections that appear in the chapter, "Replication System Recovery" of any Replication Server Administration Guide dated prior to March 15, 1995.

Recovering an RSSD from Dumps

The procedure you use to recover an RSSD depends on how much RSSD activity there has been since the last RSSD dump. There are four increasingly severe levels of RSSD failure, with corresponding recovery requirements. Use Table 1: Recovering from RSSD failures to locate the RSSD recovery procedure you need.
Table 1: Recovering from RSSD failures
Activity Since Last DumpUse This Procedure
No DDL activity Basic RSSD Recovery Procedure
DDL activity, but no new routes or subscriptions were created Subscription Comparison Procedure
DDL activity, but no new subscriptions were created Subscription Re-Creation Procedure
New routes were created Deintegration/Reintegration Procedure

Basic RSSD Recovery Procedure

Use the basic RSSD recovery procedure to restore the RSSD if you have executed no DDL commands since the last RSSD dump. DDL commands in RCL include those for creating, altering, or deleting routes, replication definitions, subscriptions, function strings, functions, function string classes, or error classes.

Certain steps in this procedure are also referenced by other RSSD recovery procedures in this article and in the Replication Server Administration Guide.

WARNING! Do not execute any DDL commands until you have completed this recovery procedure.
The current Replication Server refers to the one with the RSSD you are recovering. An upstream Replication Server is a Replication Server that has a direct or indirect route to the current Replication Server. A downstream Replication Server is a Replication Server to which the current Replication Server has a direct or indirect route.

To perform basic RSSD recovery, follow these steps:

  1. Shut down all LTMs that connect to the current Replication Server.

  2. Since its RSSD has failed, the current Replication Server is down. If for some reason it is not down, log into it and use the shutdown command to shut it down.

    NOTE: Some messages may still be in the Replication Server stable queues. Data in those queues may be lost when you rebuild these queues in later steps.

  3. Restore the RSSD by loading the most recent RSSD database dump and all transaction dumps.

  4. Restart the Replication Server in standalone mode, using the -M flag.

    You must start the Replication Server in standalone mode, because the stable queues are now inconsistent with the RSSD state. When the Replication Server starts in standalone mode, reading of the stable queues is not automatically activated.

  5. Log into the Replication Server, and get the generation number for the RSSD, using the admin get_generation command: 
     admin get_generation, data_server, rssd_name
    For example, the Replication Server may return a generation number of 100.

  6. In the Replication Server, rebuild the queues with the following command: 
     rebuild queues
    See "Rebuilding Queues Online" in the Replication Server Administration Guide for a description of this process.

  7. Start all LTMs (except the RSSD LTM) that connect to the current Replication Server. Start in recovery mode, using the -M flag. Wait until each LTM logs a message that it is finished with the current log.

  8. Check the loss messages in the Replication Server log and in the logs of all the Replication Servers with direct routes from the current Replication Server.

    If all your routes were active at the time of failure, you probably will not experience any real data loss.

    However, loss detection may indicate real loss. Real data loss may be detected if the database logs were truncated at the primary databases, so that the rebuild process did not have enough information to recover. If you have real data loss, reload database logs from old dumps. See "Recovering from Truncated Primary Database Logs" in the Replication Server Administration Guide.

    See "Loss Detection After Rebuilding Stable Queues" in the Replication Server Administration Guide for background and details on loss detection.

  9. Shut down the LTMs for all primary databases managed by the current Replication Server.

  10. Execute the dbcc settrunc command at the SQL Server for the restored RSSD. Move up the LTM truncation point. 
    	 use rssd_name
	 go
	 dbcc settrunc('ltm', 'ignore')
	 go
	 begin tran commit tran
	 go 40
	 dbcc settrunc('ltm', 'valid')
	 go
    Note: The go 40 command moves the SQL Server log onto the next page.

  11. Execute the dbcc settrunc command at the SQL Server for the restored RSSD to set the generation number to one higher than the number returned by admin get_generation in step 5. 
    	 use rssd_name
	 go
	 dbcc settrunc('ltm', 'gen_id', 101)
	 go
    Make a record of this generation number and of the current time, so that you can return to this RSSD recovery procedure, if necessary. Or, you can dump the database after setting the generation number.

  12. Restart the Replication Server in normal mode.

    If you performed this procedure as part of the subscription comparison or subscription re-creation procedure, note that the upstream RSI outbound queue may contain transactions bound for the RSSD of the current Replication Server that have already been applied by using rs_subcmp. If this is the case, after starting the Replication Server, the error log may contain warnings referring to duplicate inserts. You can safely ignore these warnings.

  13. Restart the LTMs for the RSSD and for user databases in normal mode. If you performed this procedure as part of the subscription comparison or subscription re-creation RSSD recovery procedure, you should expect to see messages regarding RSSD losses being detected in all Replication Servers that have routes from the current Replication Server.

Subscription Comparison Procedure

Follow this RSSD recovery procedure if you have executed some DDL commands since the last transaction dump, but you have not created any new subscriptions or routes. DDL commands in RCL include those for creating, altering, or deleting routes, replication definitions, subscriptions, function strings, functions, function string classes, or error classes.

WARNING! Do not execute any DDL commands until you have completed this recovery procedure.
Following this procedure makes the failed RSSD consistent with upstream RSSDs or consistent with the most recent database and transaction dumps (if there is no upstream Replication Server). It then makes downstream RSSDs consistent with the recovered RSSD.

The current Replication Server refers to the one with the RSSD you are recovering. An upstream Replication Server is a Replication Server that has a direct or indirect route to the current Replication Server. A downstream Replication Server is a Replication Server to which the current Replication Server has a direct or indirect route.

If DDL commands have been executed at the current Replication Server since the last transaction dump, you may have to re-execute them.

To restore an RSSD with subscription comparison, follow these steps:

  1. To prepare the failed RSSD for recovery, perform steps 1-4 of "Basic RSSD Recovery Procedure"

  2. To prepare for recovery the RSSDs for all upstream Replication Servers, execute the admin quiesce_force_rsi command at each upstream Replication Server.

  3. To prepare for recovery the RSSDs for all downstream Replication Servers, execute the admin quiesce_force_rsi command at each downstream Replication Server.

  4. Reconcile the failed RSSD with all upstream RSSDs, using the rs_subcmp program.

    The failed RSSD should now be recovered.

  5. Reconcile all downstream RSSDs with the RSSD for the current Replication Server, which was recovered in the previous step, using the rs_subcmp program.

    All downstream RSSDs should now be fully recovered.

  6. If the recovering Replication Server is an ID Server, you must restore the Replication Server and database IDs in its RSSD.

  7. If the recovering Replication Server is not an ID Server, and a database connection was created at the recovering Replication Server after the last transaction dump, delete the row corresponding to that database connection from the rs_idnames system table in the ID Server's RSSD.

  8. Perform steps 5-13 of "Basic RSSD Recovery Procedure".

  9. To complete RSSD recovery, re-execute any DDL commands that have been executed at the current Replication Server since the last transaction dump.

    Using rs_subcmp on Replicated RSSD System Tables

    When you execute rs_subcmp on replicated RSSD tables, for the subscription comparison and subscription re-creation RSSD recovery procedures, for each system table, the where and order by clauses of the select statement must be formulated to select all rows that must be replicated.

    Table 2 illustrates the general form of these select statements.

    Table 2: select statements for rs_subcmp procedure
    RSSD Table Nameselect Statement
    rs_classes select * from rs_classes where prsid in sub_select order by primary_keys
    rs_columns select * from rs_columns where prsid in sub_select and rowtype = 1 order by primary_keys
    rs_databases select * from rs_databases where prsid in sub_select and rowtype = 1 order by primary_keys
    rs_erroractions select * from rs_erroractions where prsid in sub_select order by primary_keys
    rs_funcstrings select * from rs_funcstrings where prsid in sub_select and rowtype = 1 order by primary_keys
    rs_functions select * from rs_functions where prsid in sub_select and rowtype = 1 order by primary_keys
    rs_objects select * from rs_objects where prsid in sub_select and rowtype = 1 order by primary_keys
    rs_systext select * from rs_systext where prsid in sub_select and texttype in ('O', 'S') order by primary_keys

    In the select statements in Table 2, sub_select represents the following statement, which selects all site IDs that are the source Replication Servers for the current Replication Server: 

    	(select source_rsid from rs_routes
	  where
	    (through_rsid = PRS_site_ID
	or through_rsid = RRS_site_ID)
	    and
	    dest_rsid = RRS_site_ID)
    where PRS_site_ID is the site ID of the Replication Server managing the primary RSSD, and RRS_site_ID is the site ID of the Replication Server managing the replicate RSSD for the rs_subcmp operation.

    For the rs_columns, rs_databases, rs_funcstrings, rs_functions, and rs_objects system tables, if rowtype = 1, then the row is a replicated row. Only replicated rows need be compared using rs_subcmp.

    For each system table, the primary_keys are its unique indexes.

    Classes and System Tables

    The default function string class rs_sqlserver_function_class and the default error class rs_sqlserver_error_class do not initially have a designated primary site, that is, their site ID = 0.

    If the recovering Replication Server was made primary for a function string class or error class since the last transaction dump, the rs_subcmp procedure described earlier in this section would find orphaned rows in downstream RSSDs.

    In that event, run rs_subcmp again on the rs_classes, rs_erroractions, rs_funcstrings, and rs_systext system tables. Set prsid = 0, in order to repopulate these tables with the necessary default settings. For example, use the following select statement for the rs_classes table: 

    	select * from rs_classes where prsid = 0
	  order by primary_keys

    Example

    Suppose you have the following Replication Server sites in your replication system, where an arrow (-->) indicates a route. Site B is the failed site, and there are no indirect routes. 

    A-->B
C-->B
C-->D
B-->E
    The preceeding Replication Server sites have the following site IDs: 
    A = 1
B = 2
C = 3
D = 4
E = 5
    In this example, to bring the RSSDs to a consistent state, you would perform the following tasks, in the order presented, on the rs_classes, rs_erroractions, rs_funcstrings, and rs_systext system tables.
    Reconciling with Upstream RSSDs
    1. Run rs_subcmp against the above tables, specifying site B as the replicate and site A as the primary, with prsid = 1 in the where clauses. For example, the select statement for rs_columns should look like the following: 
      	select * from rs_columns where prsid in
	  (select source_rsid from rs_routes
	    where
	(through_rsid = 1 or through_rsid = 2)
	and dest_rsid = 2)
	    and rowtype = 1
	    order by objid, colname

    2. Run rs_subcmp against the above tables, specifying site B as the replicate and site C as the primary, with prsid = 3 in the where clauses. For example, the select statement for rs_columns should look like the following: 
      	select * from rs_columns where prsid in
	  (select source_rsid from rs_routes
	    where
	(through_rsid = 3 or through_rsid = 2)
	and dest_rsid = 2)
	    and rowtype = 1
	    order by objid, colname
    Reconciling Downstream RSSDs
    1. Run rs_subcmp against the above tables, specifying site B as the primary and site D as the replicate, with prsid = 2 in the where clauses. For example, the select statement for rs_columns should look like the following: 
      	select * from rs_columns where prsid in
	  (select source_rsid from rs_routes
	    where
	(through_rsid = 2 or through_rsid = 4)
	and dest_rsid = 4)
	    and rowtype = 1
	    order by objid, colname

    2. Run rs_subcmp against the above tables, specifying site B as the primary and site E as the replicate, with prsid = 2 in the where clauses. For example, the select statement for rs_columns should look like the following: 
      	select * from rs_columns where prsid in
	  (select source_rsid from rs_routes
	    where
	(through_rsid = 2 or through_rsid = 5)
	and dest_rsid = 5)
	    and rowtype = 1
	    order by objid, colname
      See the Replication Server Commands Reference for more information about the rs_subcmp program and the RSSD system tables.

    Subscription Re-Creation Procedure

    Follow this RSSD recovery procedure if you have created new subscriptions since the last transaction dump, and if you have executed some other DDL commands, but you have not created any new routes. DDL commands in RCL include those for creating, altering, or deleting routes, replication definitions, subscriptions, function strings, functions, function string classes, or error classes.

    WARNING! Do not execute any DDL commands until you have completed this recovery procedure.
    As with the subscription comparison RSSD recovery procedure, following this procedure makes the failed RSSD consistent with upstream RSSDs, or consistent with the most recent database and transaction dumps (if there is no upstream Replication Server). It then makes downstream RSSDs consistent with the recovered RSSD.

    In this procedure, however, you also either delete or re-create subscriptions that are in a limbo state due to the loss of the RSSD.

    The current Replication Server refers to the one with the RSSD you are recovering. An upstream Replication Server is a Replication Server that has a direct or indirect route to the current Replication Server. A downstream Replication Server is a Replication Server to which the current Replication Server has a direct or indirect route.

    If DDL commands have been executed at the current Replication Server since the last transaction dump, you may have to re-execute them.

    To restore an RSSD that requires that lost subscriptions be re-created, follow these steps:

    1. To prepare the failed RSSD for recovery, perform steps 1-4 of "Basic RSSD Recovery Procedure"

    2. To prepare for recovery the RSSDs for all upstream and downstream Replication Servers, perform steps 2-3 of "Subscription Comparison Procedure"

    3. Shut down all upstream and downstream Replication Servers affected by the previous step. Use the shutdown command.

    4. Restart all upstream and downstream Replication Servers in standalone mode, using the -M flag.

      All LTMs connecting to these Replication Servers shut down automatically when you restart the Replication Servers in standalone mode.

    5. To reconcile the failed RSSD with all upstream RSSDs, perform step 4 of "Subscription Comparison Procedure".

      The failed RSSD should now be recovered.

    6. To reconcile all downstream RSSDs with the RSSD for the current Replication Server, perform step 5 of "Subscription Comparison Procedure".

    7. If the recovering Replication Server is an ID Server, to restore the IDs in its RSSD, perform step 6 of "Subscription Comparison Procedure".

    8. If the recovering Replication Server is not an ID Server, and a database connection was created at the recovering Replication Server after the last transaction dump, perform step 7 of "Subscription Comparison Procedure".

    9. Query the rs_subscriptions system table for the current Replication Server.

      • Also query all Replication Servers with subscriptions to primary data managed by the current Replication Server, or with primary data to which the current Replication Server has subscriptions.

      • You can query the rs_subscriptions system table by using the rs_helpsub stored procedure.

    10. For each user subscription in the rs_subscriptions system table, execute the check subscription command.

      • Execute this command at the current Replication Server and at all Replication Servers with subscriptions to primary data managed by the current Replication Server, or with primary data to which the current Replication Server has subscriptions.

      • Subscriptions with a status other than VALID must be deleted or re-created, as described below.

    11. For each Replication Server that has a non-VALID subscription with the current Replication Server as the primary:

      • Note its subid, and delete the appropriate row from the primary rs_subscriptions system table.

      • Use the subid from rs_subscriptions to find the corresponding rows in the rs_rules system table, and also delete those rows.

      For each system table, rs_subscriptions and rs_rules:

      • If a subscription is in the primary table and not in the replicate table (because it was dropped), delete the subscription row from the primary table.

      • If a subscription is in the replicate table and not in the primary table, delete the subscription row from the replicate table. After completing the rest of this procedure, re-create the subscription, as described in steps 17-19.

      • If a subscription is in both the primary and replicate tables, but not VALID at one of the sites, delete the rows from both tables. After completing the rest of this procedure, re-create the subscription, as described in steps 17-19.

    12. For each primary Replication Server for which the current Replication Server has a non-VALID user subscription:

      • Note its subid, and delete the appropriate row from the primary rs_subscriptions system table.

      • Use the subid from rs_subscriptions to find the corresponding rows in the rs_rules system table, and also delete those rows.

      For each system table, rs_subscriptions and rs_rules:

      • If a subscription is in the primary table and not in the replicate table, delete the subscription row from the primary table. After completing the rest of this procedure, re-create the subscription, as described in steps 17-19.

      • If a subscription is in the replicate table and not in the primary table (because it was dropped), delete the subscription row from the replicate table.

      • If a subscription is in both the primary and replicate tables, but not VALID at one of the sites, delete the rows from both tables. After completing the rest of this procedure, re-create the subscription, as described in steps 17-19.

    13. Execute the sysadmin drop_queue command at both the primary and replicate Replication Server, for all existing materialization queues for subscriptions deleted in steps 17-19.

    14. Restart in normal mode all Replication Servers, and their LTMs, that had subscriptions to primary data managed by the current Replication Server, or with primary data to which the current Replication Server had subscriptions.

    15. Perform steps 5-13 of "Basic RSSD Recovery Procedure"

    16. Re-execute any DDL commands executed at the current Replication Server since the last transaction dump.

    17. Enable autocorrection for each replication definition.

    18. Re-create the missing subscriptions using the bulk materialization method.

      Use the define subscription, activate subscription, validate subscription, and check subscription commands for bulk materialization.

    19. For each re-created subscription, restore consistency between the primary and replicate data in either of two ways:

      • Drop a subscription using the drop subscription command and the with purge option. Then re-create the subscription.
      • Use the rs_subcmp program with the -r flag to reconcile replicate and primary subscription data.

    See the Replication Server Commands Reference for more information about the rs_subcmp program and the RSSD system tables.

    Deintegration and Reintegration Procedure

    If you created routes since the last time the RSSD was dumped, you are required to perform the following tasks before you can finish recovering the RSSD:

    1. Remove the current Replication Server from the replication system.

      See "Modifying a Replication System," in the Replication Server Administration Guide for details.

    2. Reinstall the Replication Server.

      See the Replication Server Installation Guide for complete information about reinstalling Replication Server.

    3. Re-create Replication Server routes and subscriptions.

      See the Replication Server Administration Guide Chapter 6, "Subscribing to Replicated Tables," and Chapter 7, "Managing Routes," for details.

    See the Replication Server Commands Reference for more information about the rs_subcmp program and the system tables.

    min() and max() Functionality

    Question

    How can I find min or max of two int or float columns forall rows in a table? Is it possible to do min or max ontwo columns at once?

    Answer

    It is entirely possible to do min or max on two columnsat once. Given a table that was created as follows:

    	create table MINMAX( a int, b int)
    you can use the following sequence of Transact-SQL commands to get theresults you want:
    	1> select a,b,	2> "max"  = (a+b)/2.0 + abs((a-b)/2.0),	3> "min"  = (a+b)/2.0 - abs((a-b)/2.0)	4> from MINMAX	5> go	 a     b     maxmin	 ----------- ----------- -------------------- ------------------- 		 10     510.000000 5.000000 		  5     9 9.000000 5.000000 		  3   -11 3.000000     -11.000000 		 -7   -99-7.000000     -99.000000 	(4 rows affected)
    You may also add convert(int....) if necessary.

    Viewing all Decimal Digits in a select frommoney_column

    Question

    How do I convert a money field so that an entire entry, with allfour decimal digits, is visible?

    Answer

    The following example shows one way to display all the digits in asingle string (preserving leading zeros in decimals), given a tablewith one money column loaded as follows:

    	1> insert into lvmoney2 values ($12345678901.0087)	2> insert into lvmoney2 values ($11170305027104.1195)	3> go
    Structure your query like this:
    	select str(floor(m1),15,0) + convert (char(4), replicate("0", 4	- datalength(convert(varchar(4), convert(int,	(10000*(m1-floor(m1))) )  ))) + convert(varchar(4),	(convert(int, (10000*(m1-floor(m1))) ) ))) from lvmoney2
    The output will look like this:
    	-------------------	    123456789010087	 111703050271041195	(2 rows affected)
    For further information, see "Float and Money Display Methods" inVolume 3, Number 3 of the SYBASE Technical News (available inAnswerBase when you do a full-text query of the form <title technicalnews>).

    Dump/Load Compatibility, 10.0.1 vs. 10.0.2

    Question

    Can a backup done by a release 10.0.1 Backup Server be loaded onto arelease 10.0.2 SQL Server?

    Answer

    There are really two issues here, but the short answer is, yes,10.0.1 dumps are compatible with 10.0.2. The two issues are:

    Loading Dumps from 10.0 or 10.0.1 P1 SQL Server to 10.0.1 P2 or 10.0.2

    If you want to load a dump from a 10.0 SQL Server or from an earlierrelease 10.0.1 SQL Server to a 10.0.1 P2 or 10.0.2 SQL Server, youmust execute a script to update all system tables and user databasescontained in the load. This script is necessary because of changesto the sysreferences table for release 10.0.1 P2 or later.
    NOTE: Do not use the script if you are loading a dump from 10.0.1 P2 to 10.0.2. 10.0.1 P2 is completely compatible with 10.0.2, and no additional steps are needed.
    Use an editor like vi to edit the upgradesysrefperdb.sqlscript, which is located in the $SYBASE/upgrade directory.Insert the following lines at the beginning of the script:
    	use database_name	go
    where database_name is the name of the database to be updated.

    After you have completed the load, execute theupgradesysrefperdb.sql script as System Administrator:

     % isql -Usa -Ppassword -Sserver_name < $SYBASE/upgrade/upgradesysrefperdb.sql
    where sa is the name of a System Administrator account,password is the password for that account, and server_nameis the name of the 10.0.1/P2 or later SQL Server where you loaded the database.

    Repeat steps 1 and 2 for every 10.0 or earlier 10.0.1 databaseloaded on a 10.0.1/P2 or later SQL Server, including the systemdatabases.

    Back up all of your databases.

    Backup Server vs. SQL Server Versions

    If you use a 10.0 Backup Server with a 10.0.1 SQL Server, you willget this error message:

    	Open Server Session Fatal Error: 16227.15.0: Unknown token TDS	stream received by spid
    If you are using the Backup Server that is compatible with your10.0.2 SQL Server, you should be able to load dumps from 10.0 and10.0.1. Be sure to use the compatible Backup Server with your SQLServer: 10.0 with 10.0; 10.0.1 with 10.0.1; 10.0.2 with 10.0.2.
    NOTE: An exception to this is the beta SQL Server and Backup Server release. Changes occurred between the beta and production releases that render dumps made by a beta Backup Server unreadable to a production Backup Server. See "Error 16227.15.0" in Chapter 3, "Backup Server", of the SYBASE SQL Server Troubleshooting Guide dated February 14, 1995 for more details.

    keepalive Redux

    SQL Server users who have started networking with PC clients oftenquestion the issue of "ghost" connections and keepalive timing that cancause a server to run out of connections rapidly.

    The problem is that the PC user is used to running the PC in standalonemode. If the user submits a query to SQL Server which takes too long tofinish, or has made a mistake, the user is likely to reboot the PC, orclose the application with a Ctrl-Alt-Del. The aborted process hangsaround in SQL Server and finishes after about four hours.

    While this may seem like a db_cancel problem--that is, that the querydoes not get cancelled when the PC goes down--it is actually due toincomplete coding in the application combined with the value to whichthe keepalive timeout is set on the SQL Server host machine.

    Ideally, an application allows the PC user to cancel the query and doesa db_cancel when the user chooses this option. Due to aspects both ofapplication design and of Windows itself, this is not always possible.

    Network connections are always peer to peer, meaning that both sidesknow that the other side could go away at any time. However, because ofnetwork traffic and processing considerations, it may take some timefor a machine to respond. This is where keepalive comes in.

    The keepalive timer controls how long the machine will wait for aconnected machine to respond; the keepalive interval controls how longthe host will wait between pings to the remote machine; the keepalivecount controls how many times the host will ping the remote machinebefore it gives up and clears the connection.

    The problem in the PC-to-UNIX world is that the PC expects everythingto behave as though it is local, while UNIX expects WAN-typeconnections. What this means is that a PC generally has keepalive hardcoded at about one minute. A UNIX machine has a keepalive timer whosedefault setting makes more sense in a world of WAN and low-speedconnections-somewhere around two hours.

    To close the connections more quickly, you must decrease the UNIXkeepalive timer to match that of the PC more closely. Depending onnetwork traffic constraints, one to five minutes should be adequate.See your System Administration Guide Supplement oroperating system documentation to find out how to setkeepalive.

    Setting Up Borland IDE to Compile Client-Library

    To compile Open Client Client-Library[TM] applications under BorlandC++ for Windows, you can either modify the Sybase-supplied samplemakefile, borland.mak, for your program or start a new InteractiveDevelopment Environment (IDE) project. This article explains how to dothe latter.

    1. Open a new project for the program: for example, choose Project-->New Project from the menu bar. Browse to where the program is located (for example, $SYBASE\sample\ctlib), and enter the target name (for example, rpc). For our sample programs, change the Target Type to EasyWin[.exe].

    2. Make sure that the Class Library and BWCC checkboxes are not selected, and choose OK.

    3. Choose Options-->Project. Select Directories, and make sure the following directories are listed:
      	Include: %BORLAND%\include; %SYBASE%\include; %SYBASE%\sample\ctlib	Library: %BORLAND%\lib; %SYBASE%\lib
      Where %BORLAND% is the Borland compiler home directory and %SYBASE% is the Sybase home directory (this can be set up as default for the compiler).

    4. Highlight the topic "Compiler/Defines". In the "Defines" window, you must have the following:
      	WIN3;CS_FORCE_PROTOTYPES
      These can be included in your header files as well. CS_FORCE_PROTOTYPES causes prototype functions to be generated, eliminating warning messages about functions being used without prototypes.

    5. Select Linker-->General and make sure that the Case sensitive link checkbox is selected.

    6. Choose OK to accept all of the changes to the project. It is a good idea to select Options-->Save and save the new project at this point.

    7. Choose View-->Project and add the following libraries:
      	wcomnlib.lib	wintllib.lib	wcslib.lib	wctlib.lib
    NOTE: You will also need to add wblklib.lib if you areusing bulk copy routines.
    For the example programs, you will also need to add exutils.c tothe project.

    Macintosh Think-C and ct_init Routine

    If you are running the Open Client/C Developer's Kit for Macintosh andare having trouble with the ct_init routine failing, even thoughyour LANG variable is set correctly and ctlib.loc isavailable, it may be because the default project size for Think-C istoo small. The default value is 396; try setting its value to at least4000 and compiling your application again.

    Certification Reports

    The following certification reports have been passed along to SYBASETechnical News by the appropriate Sybase engineering groups.

    SQL Debug Certified for DEC OSF/1

    SQL Debug® has been certified to work with SQL Server release 10.0.2 forDEC OSF/1. This corrects an existing problem between SQL Debug and DECOSF/1 SQL Server release 10.0.1 or earlier where a control-of-flowlanguage statement, such as if...else or while, causesSQL Debug to hang.

    IPX/SPX Certification for Solaris

    As of the 10.0.2 release of SQL Server on Solaris 2.x, IPX/SPX iscertified and supported. You need to apply Rollup 4222 for thiscombination to be certified; you may order the Rollup from TechnicalSupport in the usual way.

    IPX/SPX is certified at the current release level of Solaris 2.3.

    Certification Report for All Platforms

    <<<<<<<<
    PlatformOperating System VersionServerDateNotes
    AT&T (NCR) QIC tape device10.01 08/94Passed. Tape device is supported on all OS levels currently supported for 10.0x. OS patch required: PSCSI version 94.04.25.19
    SVR4 2.00.02 (SMP)4.9.1 04/93Passed. Certified with WIN-TCP/IP 2.00.04. Requires ASYNCH I/O patch P2ASYNC.
    SVR4 2.00.02 (UP model)4.9.1 04/93Passed. Certified with WIN-TCP: (i386) 02.00.04.02
    SVR4 2.0110.0 02/94N/A. No plans to certify.
    SVR4 2.014.9.1 04/93Passed. Certified with WIN-TCP 2.00.05.
    SVR4 2.01.01 + Pentium 4.9.1 07/93Passed. Certified with WIN-TCP 2.01.00.12
    SVR4 2.02 10.0 02/94Passed. certified with WIN-TCP 2.01.01.08.
    SVR4 2.02 4.9.2 12/93Passed. certified with WIN-TCP 2.01.01.08. and Rollup 2025 (built on OS 2.00.01 with patched libdbt).
    SVR4 2.02 + pentium 10.0 02/94Passed.
    SVR4 2.03 10.01 08/94Passed. Certified w/WIN-TCP 2.02.00.07. OS patch PSCSI203 is required for any customer planning to use the HP DAT 4mm tape drive for backupserver.
    SVR4 2.03 4.9.2 09/94Passed. Rollup 3165 is built with a new libdbt.a so that SQL Server would work with AT&T UNIX 2.02 and later releases without problems and make use of improved system calls.
    SVR4 2.03 + EISA bus 10.01 11/94Passed.
    SVR4 2.03 + EISA bus 4.9.2 12/94Passed.
    SVR4 2.03 + SPX/IPX 10.0.201/95Passed. Bugs entered: sybinit(65591), doc(65604), connectivity(65807,65 817). PC Windows Net-Library EBF #4316 is required for connectivity bug #65807. PC Windows DB-Library (4.2) based clients can connect to the Unix Server.
    SVR4 2.03.0110.01 12/94Passed. A series of patches are needed to support EISA and MCA systems.
    SVR4 2.03.014.9.2 12/94Passed. A series of patches are needed to support EISA and MCA systems.
    DEC Alpha AXP OpenVMS 1.5 4.9.2 Supported. This was handled as a maintenance rollup release. All customers will be shipped the EBF rollup which supports AXP VMS 1.5.
    AXP OpenVMS 1.5 + UCX 4.9.2 N/A, No plans to certify.
    AXP OpenVMS 1.5+UCX 3.1 10.01 09/94Passed. UCX (ECO 2 or higher) is required. SQL Server must be configured for double the bytlm. In addition, if user clients are put into a MUTEX state we recommend doubling the bytlm for the number of users.
    AXP OpenVMS 6.1 10.01 12/94Passed. Network packages certified: DECnet Phase IV, UCX 3.2 and Multinet 3.3
    AXP OpenVMS 6.1 4.9.2 01/95Passed. Network packages certified: DECnet Phase IV, UCX 3.2 and Multinet 3.3
    OSF/1 1.3 10.00 11/93Passed.
    OSF/1 2.0 10.01 09/94Passed.
    OSF/1 2.0b10.01 09/94Passed. The "b" in the OS version indicates that the OS is for the new DEC 2100 sable machines.
    OSF/1 2.1 10.01 11/94Supported.
    OSF/1 3.0 10.0.201/95A new product release is required to support OSF/1 3.0. It will be only sent to those customers who request it via the new PID issued as 19555. Contact Sybase Customer Service.
    DEC RISC Ultrix 4.34.2 11/92Passed.
    Ultrix 4.3a 4.2 06/94Passed. Requires ebf# 2005.
    Ultrix 4.4 4.2 10/94Certified. EBF 2005 or higher required.
    DEC VAX OpenVMS 5.4 + UCX 3.1 10.01 08/94Passed. To run UCX, SQL Server must be configured via the VMS AUTHORIZE utility for double the bytlm stated in the SAG Supplement. In addition the INET device emulator must be loaded before starting SQL Server.
    OpenVMS 5.4 + Wollongong10.01 06/94Passed. Known problem: The SQL Server runs out of AST quota. This problem has been reported against Wollongong.
    OpenVMS 5.5-2 10.0 02/94Passed.
    OpenVMS 5.5-2 4.9.1 Supported.
    OpenVMS 6.0 10.0 04/94 Passed. As noted in the Release Bulletin customers must install System10 product O/S version 5.4 before upgrading to OpenVMS 6.0. VMSINSTALL is not supported at this OS level.
    OpenVMS 6.1 10.01 01/95 Passed. Network packages certified: DECnet Phase IV, UCX 3.2 and Multinet 3.2
    OpenVMS 6.1 4.9.2 Passed, Network packages certified: DECnet Phase IV, UCX 3.2 and Multinet 3.2
    Data General DGUX 5.4 4.2 Passed, This does not include DGUX 5.41 or 5.42
    DGUX 5.4.2 4.2 10/92Failed. We recommend upgrade to 4.9 after upgrading O/S to DGUX 5.4.2
    DGUX 5.4.2 4.9 Passed.
    DGUX 5.4.2.01 10.0 Passed.
    DGUX 5.4R2 4.9.0SPassed.
    DGUX5.4R2.110.0.1Passed.
    DGUX5.4R2.1 10.0sqPassed.
    DGUX5.4R3.1010.0.111/94Passed. Users with 4-mm and 8-mm tapes must have Rollup 3963 to run this combination. For 8-mm you also need DGUX patch NSQA-18785-0. See technical details in the Release Bulletin for Rollup 3963.
    HP300 HP-UX 8.0 4.0.1 10/92 Passed.
    HP700 HP-UX 8.01 4.2 Passed.
    HP-UX 8.05 4.2 Passed.
    HP-UX 8.05 4.9.1 Passed.
    HP-UX 8.07 4.2 09/92Passed.
    HP-UX 8.07 4.9 Passed.
    HP-UX 8.07 4.9.1 Passed.
    HP-UX 9.0 4.9.1 Failed.
    HP-UX 9.0.1 10.0 02/94N/A, no plans to certify.
    HP-UX 9.0.1 4.9.1 03/93Passed, ASYNC I/O patch PHKL_2162 is required.
    HP-UX 9.03 10.01 03/94Passed.
    HP-UX 9.03 4.9.2 03/94Passed. Requires HP700 Asynchronous I/O patch# PHKL_3660
    HP-UX 9.05 10.0.112/94Passed, Requires patch PHKL_4334 and patch PHKL_4269 - Bug 64656 has been entered for 'installasync70' script incompatibility.
    HP-UX 9.05 4.9.2 12/94Passed, Requires patch PHKL_4334 and patch PHKL_4269 - Sybase Bugid 64656 has been entered for 'installasync70' script incompatibility.
    HP800 HP-UX 8.0 4.2 Passed.
    HP-UX 8.0 4.9 Passed.
    HP-UX 8.02 4.9 Passed.
    HP-UX 8.02 4.9.1 Passed.
    HP-UX 8.06 4.9 Passed.
    HP-UX 8.08 BLS (Secure) 4.9.1 N/A, No plans for certification because BLS does not support asychronous I/O.
    HP-UX 8mm tape device 10.0 10/94Passed.
    HP-UX 9.0 4.9.1 Passed.
    HP-UX 9.0 using LVM 4.9.1 05/93Passed. Bug #41610 reported - 'disk init' will only initialize Logical Volume devices %lt;= 2GB.
    HP-UX 9.04 10.0 03/94Passed.
    HP-UX 9.04 4.9.2 Passed.
    HP-UX 9.04 using LVM 10.01 10/94Passed. Requires Asynch I/O patch PHKL_3624 & LVM Sybase Mirroring patch PHKL_4418
    HP-UX 9.04 using LVM 4.9.2 10/94Passed, Requires Asynch I/O patch PHKL_3624 & LVM Sybase Mirroring patch PHKL_4418
    IBM RS60004mm tape device 10.01 11/94Passed. 4mm 2GB (7206-001) has full Backup Server feature support. 4mm 4GB (7206-005) supports all Backup Server features except the ability to write multiple file/volume dumps to a single tape. Bug #63636 has been entered to track this product enhancement.
    AIX 3.2 4.2 Passed.
    AIX 3.2.1 4.9.1 N/A, No plans for certification
    AIX 3.2.2 4.9.1 N/A, No plans for certification
    AIX 3.2.3e 4.9.1 05/93Passed. IBM PTF U418109 is required for AIX 3.2.3e
    AIX 3.2.4 4.9.2 08/93Passed.
    AIX 3.2.5 10.0 02/94Passed. IBM PTFs (Patches) are required for the following IBM APARs: IX45257, IX41600, IX38605, IX43714
    AIX 3.2.5 4.9.2 12/93Passed. IBM PTFs (Patches) are required for the following IBM APARs: IX45257, IX41600, IX38605, IX43714
    AIX 3.2.5 + PowerPC Chip4.9.2 12/93Passed. IBM PTFs (Patches) are required for the following IBM APARs: IX45257, IX41600, IX38605, IX43714
    AIX 3.2.5 - SPX/IPX 10.02 01/95Passed. SQL Server EBF 4283 is required. Bugs entered: sybinit(65590), doc(65603), connectivity(65807,65 817). PC Windows Net-Library EBF #4316 is required for connectivity bug #65807. PC Windows DB-Library (4.2) based clients can connect to the UNIX SQL Server.
    AIX 3.2.X SP/1 SP/2 10.01 Supported.
    AIX 3.2.X SP/1 SP/2 4.9.2 Supported.
    AIX 4.1.1 (SMP) 10.0 01/95No certification is planned. A new product release is required to support the SMP version of AIX 4.1.1, planned for a later date.
    AIX 4.1.1 (UP) 10.01 12/94Passed. This certification is based on the uniprocessor version of the AIX 4.1.1 release.
    ICL DRS 6000 DRS/NX V7L2 10.0.0Passed.
    Motorola SVR4 R$V4.1 10.0.0Passed.
    NEC SVR4 R6.1 10.0.1Passed.
    PC NetWare v 4.02 10.0.2Supported.
    Netware 3.1.1 + SFT III4.2 05/93Failed. Severe OS problems were encountered during the certification.
    Netware 386 v 3.114.2 Passed. This certification also includes the maintenance release of SQL Server 4.2.1 & 4.2.2
    Netware 386 v 3.124.2.2 03/94Passed. Requires EBF# 2388. See release bulletin for NLMs and versions.
    Netware 386 v 4.0.1 4.2.2 12/93Passed. Requires EBF #2404 and new CLIB.NLM versions. See ebf coverletter forthe CLIB.NLM version numbers and dates.
    Netware 4.0.2 4.2.2 12/94Passed. Requires EBF 3617 or higher.
    OS/2 1.2.1 4.2 Passed.
    OS/2 1.3x.x 4.2 Passed.
    OS/2 2.0 4.2 Passed. CSD 6055 recommended
    OS/2 2.1 4.2 N/A, No plans to certify.
    SCO 3.2.4 4.2 Passed.
    SCO 3.2.4.2 4.2 11/93Passed. Requires Rollup 1980 and SCO SLS patch UOD378a. Rollup resolves error 611 encountered during testing. Also includes support for SCO Open Server Enterprise System Release 3.0, and SCO Open Desktop Release 3.0.
    Windows NT 3.5 10.01 12/94Passed, The following certification test run problems have been recorded in BTS: 64109 & 64110.
    PC SCO R3.2 V4.2 10.0.1Passed.
    Pyramid N 1.1 93d067 10.0.1Passed.
    94d07910.0.1In Progress.
    Pyramid Nile D067 10.0 Passed. Binaries built on Pyramid S/C062 will run on this OS version, but binaries built on Pyramid Nile/D067 are not certified to run on Pyramid ES/C062.
    Pyramid S 1.1 93c062 10.0.1Passed.
    1.1 93c062 4.9.1 Passed.
    SVR4 C034 4.9 Passed.
    SVR4 C044 4.9 Passed.
    SVR4 C062 4.9 Passed.
    Pyramid T 5.1a 93a060 4.8 Passed.
    OSx 5.1A 4.8 Passed.
    SCO R3.2 V4.2 10.0.0Passed.
    Sequent DYNIX 1.2 4.8 Passed.
    DYNIX 1.4 4.8 09/92Passed.
    DYNIX 2.0 4.8 Passed. EBF binaries built on this OS version will not run on PTX 1.4 and below.
    DYNIX 2.0.1 4.8 Passed, EBF binaries built on PTX 2.0 and above will not run on PTX 1.4 and below.
    DYNIX 2.1 4.8 N/A, No plans to certify.
    DYNIX 2.1.0 10.0.0Passed.
    DYNIX 2.1.0 4.8 Passed.
    DYNIX 4.0 10.0.1Passed.
    Silicon GraphicIRIX 3.2 4.0.1 Passed.
    IRIX 5.1 10.0 Passed.
    IRIX 5.1.1.110.0.1Passed.
    IRIX 5.2 10.0.107/94Passed.
    SonyNEWS R6.0.1 10.0.1Passed.
    Stratus FTX 2.2.2(y)10.01 Passed.
    VOS 10.5 4.8 Passed.
    VOS 11.6 4.9 Passed.
    Stratus 68K VOS VOS 10.5 4.8 Passed.
    Stratus FTX FTX 2.2.2 10.0 Passed.
    FTX 2.2.2.3 10.0.1Passed.
    Sun4Solaris 2.2 4.9.1 06/93Passed. Requires Rollup 1794 or higher and SunOS patches 100999-09 and 101095-01.
    Solaris 2.3 10.00 11/93Passed.
    Solaris 2.3 4.9.2 12/93Passed.
    Solaris 2.3 + SPARCstor10.0.112/94Passed. Required Sun patches: 102198-01 & 102199-02 for use with Sun's Volume Manager
    Solaris 2.3 + SPARCstor4.9.2 12/94Passed, Required Sun patches: 102198-01 & 102199-02 for use with Sun's Volume Manager
    Solaris 2.3 + SPX/IPX 10.0.201/95Passed. Requires Rollup 4222. Bugs entered: sybinit(65592), doc(65605), connectivity(65807,65 817). PC Windows Net-Library EBF #4316 is required for connectivity bug #65807. PC Windows DB-Library (4.2) based clients can connect to the UNIX SQL Server.
    Solaris 2.3-A+Edition 110.0 N/A, No Plans to certify
    SunOS 4.1.1 4.2 Passed.
    SunOS 4.1.1 4.8 Passed.
    SunOS 4.1.1 + DBE 1.1 4.8 Passed. requires SunOS 4.1.1 patch #100293 or SunDBE 1.1 patch #100322.
    SunOS 4.1.1 + DBE 1.1 4.9 02/92Passed.
    SunOS 4.1.2 4.0.1 Passed.
    SunOS 4.1.2 4.2 Passed.
    SunOS 4.1.2 4.8 Passed. requires SunOS 4.1.2 Kernel patch #100495
    SunOS 4.1.2 + DBE 1.2 4.2 08/92Passed.
    SunOS 4.1.2 + DBE 1.2 4.9 02/92Passed.
    SunOS 4.1.2 + DBE 1.2 4.9.1 Passed.
    SunOS 4.1.3 4.2 N/A, No plans to certify.
    SunOS 4.1.3 4.8 02/93Failed. We recommend upgrade to the 4.9.1 server.
    SunOS 4.1.3 4.9 03/93Passed.
    SunOS 4.1.3 4.9.1 10/92Passed. requires SunOS 4.1.3 Sun-4m Supplement patch #100743-01. Patch is for multi-processor systems only.
    SunOS 4.1.3 + DBE 1.3 10.02 02/95Passed. EBF #4403 is required.
    SunOS 4.1.3 + DBE 1.3 4.9 N/A, No plans to certify.
    SunOS 4.1.3 + DBE 1.3 4.9.1 04/93Passed. requires Sun-4m Supplemental patch #100743-01. Patch is for multi-processors and Sparc 10 systems only.
    SunOS 4.1.3/sparc10 4.9.1 Passed. requires SunOS 4.1.3 Sun-4m Supplement patch #100743-01. Patch for multi-process systems only.
    SunOS 4.1.3X10.01 12/94Passed. The 'X' in 4.1.3X indicates all of the 4.1.3 based releases. (eg 4.1.3c, 4.1.3.u1, 4.1.3u1b, etc.)
    SunOS 4.1.3X4.9.2 03/94Passed. The 'X' in 4.1.3X indicates all of the 4.1.3 based releases. (eg 4.1.3c, 4.1.3.u1, 4.1.3.u1b).
    Unisys 65 SVR4 Ver 1.210.0.1 Passed.
    SVR4 Ver 1.24.9.2 Passed.
    Unisys 75 Dynix 1.3.1 4.8 Passed.
    Unisys U6000 75 SVR4 1.3 4.8 Passed.
    Unisys U6000/65 SRV4 1.2 4.9.2 Passed.

    Bug 61483 - Duplicate Rows in syskeys

    Problem

    select * from syskeys shows apparent duplicate rows. This should, intheory, not be possible, since syskeys has a clustered index on it,which should prevent duplicate rows. These duplicates mean that somestored procedures do not report errors when they should.

    Explanation

    When an insert is done to a table on which there is a clustered index, SQL Server does a byte-by-byte comparison of the entire row. If it finds a duplicate row, it rejects the insert. SQL Server assumes that the complete area between the start and the end of the data is valid. This assumption turns out to be correct for all tables, except syskeys.

    The system catalog for syskeys contains a column that can't be displayed, which contains random or uninitialized data. It is possible for two rows with identical data in the visible columns to differ in the contents of the hidden column.

    This problem occurs on 4.9.x, 10.0.1, and 10.0.2 SQL Servers.

    Solution

    You can correct this problem by running a script that exposes the column (it is called spare1, and is a 2-byte integer column) and binds a default of zero to it. This prevents any future occurrences; the script also corrects any existing duplicates in syskeys.

    The change made by this script will not break any of the system stored procedures that refer to syskeys; this is because the default on the new column ensures that any insert that does not specify the new column gets a default value of zero.

    The script also does sanity checks to see if it has already been run.

    The scripts are available as Rollups for the following platforms and releases:

    Table 4: Rollups correcting bug 61483
    Rollup Number Platform Release Number
    3938SunOS Release 4.x (BSD) 10.0.2
    3939Sun Solaris 2.x10.0.2
    3940HP 9000 Series 300 HP-UX 10.0.2
    3941AT&T (NCR) System 3000 10.0.2
    3942IBM RISC System/6000 AIX 10.0.2
    3943DEC OSF/110.0.2
    3944SunOS Release 4.x (BSD) 4.9.2
    3945Sun Solaris 2.x 4.9.2
    3947HP 9000 Series 300 HP-UX 4.9.2
    3948AT&T (NCR) System 30004.9.2
    3949IBM RISC System/6000 AIX 4.9.2
    These scripts are available only as controlled releases. Many customersare not doing operations that directly involve syskeys, and SybaseEngineering prefers that the problem be sorted out for youtransparently on upgrade to 10.0.3. If you cannot wait, however, Sybasehas provided the script as a Rollup that you can order from TechnicalSupport.

    In order to minimize confusion, the scripts are all the same and willautomatically sort out differences between the SQL Server releases.Release 10.0.2 will run against all 10.0.1 P2 and 10.0.2 SQL Servers.

    For Windows NT, Novell NetWare, and other PC platforms, there are norecorded instances of this problem.

    Disclaimer: No express or implied warranty is made by SYBASE or its subsidiaries with regard to any recommendations or information presented in SYBASE Technical News. SYBASE and its subsidiaries hereby disclaim any and all such warranties, including without limitation any implied warranty of merchantability of fitness for a particular purpose. In no event will SYBASE or its subsidiaries be liable for damages of any kind resulting from use of any recommendations or information provided herein, including without limitation loss of profits, loss or inaccuracy of data, or indirect special incidental or consequential damages. Each user assumes the entire risk of acting on or utilizing any item herein including the entire cost of all necessary remedies.

    Staff

    Principal Editor: Leigh Ann Hussey Contributing Writers:
    Lance Anderson, Paul Dow, Ken Duffy, Aimee Grimes, Joseph Hui, Greg Klinder, Andrzej Sarapuk, Gerald Soulos, Loretta Vibberts, Rob Weaver, Elton Wildermuth

    Send comments and suggestions to:

    SYBASE Technical News
    6475 Christie Avenue
    Emeryville, CA 94608

    or send mail to technews@sybase.com

    Copyright 1996 © Sybase, Inc. All Rights Reserved.