----------------------------------------------------------------------------
Sybase Technical News
----------------------------------------------------------------------------
Volume 6, Number 2 May,1997
----------------------------------------------------------------------------
This issue of Sybase Technical News contains new information about your
Sybase software. This newsletter is intended for Sybase customers. All
issues of Sybase Technical News and the troubleshooting guides are included
on the AnswerBase CD, SupportPlus Online Services Web pages, and the Sybase
PrivateLine forum of CompuServe.
In This Issue
-------------
Message from the Publishers
Tip of the Month: Setting Image Size for bcp
TechNote Summaries
Other Useful Information
Feedback
Message from the Publishers
---------------------------
Last month, we told you about the return of Sybase Technical News and its
change to a monthly publication.
Now we've taken the spirit of openness a step further. As of April 6, the
Technical Information Library is available to anyone browsing the Sybase
public web pages. Either link from the Sybase home page at
http://www.sybase.com or set a bookmark for http://www-esnt.sybase.com. You
don't have to be a registered support contact to get information about using
Sybase products.
As always, we look forward to hearing from you; send comments to
technews@sybase.com.
Tip of the Month: Setting Image Size for bcp
--------------------------------------------
If you want to bulk copy image and text datatypes with bcp, specify the
size, in bytes, of each datatype. The default is 32K, which may be too small
for images.
Rather than arbitrarily setting the image value to a large number, use
datalength() to determine the row length, in bytes, of the data.
1> select datalength(imagerow) from imagetable
2> go
A similar system function, col_length, returns only the column- defined
length. See the Sybase SQL Server Reference Manual: Volume 1 for more
information on system functions.
Other Useful Information
* SQL Server 11.x Bug Report
* Compatibility
* Availability
* End-of-Life/End-of-Support
* Error Messages
* SQL Server 11 Migration Guide: Moving from 10.x or 4.x to 11.x
* What's New
This information also appears on the AnswerBase CD-ROM, published twice a
year. The current AnswerBase CD-ROM is Number 3, 1996. To order the CD-ROM,
contact Sybase Technical Support at 1-800-8SYBASE.
For frequent updates, visit the Sybase Technical Information Library on the
World Wide Web at http://www-esnt.sybase.com.
Electronic Case Management and Electronic Software Delivery access remains
limited to Sybase support contacts who are registered with SupportPlus
Online Services. ECM and ESD requires SSL web browser support. ECM and ESD
users also may access the Tech Info Library from behind the firewall in the
usual manner, allowing them to retain the security benefits of using SSL
with their web browser.
TechNote Summaries
------------------
2696 How to Stop Excessive Paging Activity on SQL Server for Windows NT
---- http://www-esnt.sybase.com/28f6.htm
Summary
-------
When the Windows NT system cache conflicts with SQL Server, excessive paging
occurs. This document describes how to identify the problem and its simple
solution.
Background
----------
The Windows NT system cache can grow to consume almost all available memory,
slowing SQL Server because both compete for the same memory.
Windows NT allocates SQL Server physical memory by default, which SQL Server
uses to run processes. This default value is known as the "process working
set".
The working set is a collection of those pages in Windows NT's virtual
address space that have been recently referenced. The working set includes
shared and private data.
Windows NT also allocates physical memory to the NT system cache, also known
as the buffer cache. The system cache stores recently used data as long as
possible to permit access to the data without having to read from the disk.
Problem
-------
By default, Windows NT first allocates memory to the system cache. SQL
Server may page excessively and appear to hang when SQL Server contends for
system memory with the NT system cache.
Symptoms
--------
* The hard drive is always making noise and seems like the SQL Server is
paging constantly.
* It appears as if the SQL Server is hanging and the current SQL Server
tasks are taking too long.
* When the SQL Server is started up, the following warning appears in the
errorlog/eventlog:
"Operating system may favor the system cache when memory is at a
premium. To disable this warning, set the registry parameter
'LargeSystemCache' to 0 in
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession
ManagerMemory Management."
Resolution
----------
Take these steps to stop excessive paging activity:
1. Examine the following subkey in the Registry Editor:
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession
ManagerMemory Management
2. Examine the registry parameter LargeSystemCache.
3. Set LargeSystemCache to 0.
When the LargeSystemCache setting is greater than zero, the operating system
favors the system cache rather than the process working set.
Example
-------
Consider a scenario in which you are running both SQL Server and bcp on the
same machine and you much bcp a huge table=AF10MB=AF into SQL Server.
As the table is read, the NT system cache grows. Eventually its size
conflicts with the SQL Server working set size. If the LargeSystemCache is
activated (it is greater than zero), SQL Server loses the conflict and
starts to page.
For more information on buffer cache conflicts, see Tuning and Measuring
Performance of Sybase SQL Server 11 on Windows NT.
---------------------------------------------------------------------------
2743 Starting SQL Server with Trace Flag 3607 or 3608 or Database Status
---- -32768
http://www-esnt.sybase.com/2792.htm
Summary
-------
This TechNote covers the correct use of trace flags 3607 and 3608, and the
consequences of using these trace flags.
Contents
--------
If you are unable to access a system or user database, but need to access
the database to look at the problem, SQL Server provides some options that
allow you to do this:
Option Impact
-------------------------------------------------------------------------
Trace Flag 3607 Starts SQL Server without recovering any
databases
-------------------------------------------------------------------------
Trace Flag 3608 Starts SQL Server, recovering master only
-------------------------------------------------------------------------
User Database Status -32768 Starts SQL Server without recovering the user
database
-------------------------------------------------------------------------
Once you have started SQL Server with one of these options, the unrecovered
databases are open and accessible. How you subsequently shut down SQL Server
can impact recovery of these databases. If you do an ordinary shutdown, an
automatic checkpoint will be performed on all unrecovered databases. The
syslogs table is truncated and you lose the ability to recover any
transactions in the log. For example, if you use trace flag 3608 and do a
normal shutdown, you may lose recovery of all databases other than master.
After starting SQL Server by using trace flags 3607 or 3608, or by setting
the database status to -32768, therefore, it is critical that you do a
shutdown with nowait. No checkpoint is performed, so that transactions in
syslogs are preserved and you may be able to recover the database(s) later.
Finally, be sure to issue a checkpoint in master if you have made any
changes to system tables.
------------------------------------------------------------------
WARNING!
Do not attempt a normal shutdown after using trace flags 3607 or
3608, or after setting the database status to -32768. By doing so
you run the risk of being unable to recover the affected
database(s). Always do a shutdown with nowait in these situations.
------------------------------------------------------------------
For more information about starting SQL Server with trace flags, see the SQL
Server Troubleshooting Guide.
---------------------------------------------------------------------------
2824 lct_admin("unsuspend") Not For Use in SQL Server 11.x
---- http://www-esnt.sybase.com/28ca.htm
Summary
-------
Database administrators should not use the lct_admin("unsuspend") command in
SQL Server version 11.x.
Background
----------
Sybase SQL Server versions 10.x and later provide a "last chance threshold"
on transaction log space. This threshold is an estimate of the minimum
amount of space required to back up the transaction log using the dump
transaction command.
When a Sybase SQL Server client thread (spid) crosses the last chance
threshold, that thread becomes "LOG SUSPEND"ed until enough space in the
transaction log is available to continue. In most situations, a threshold
procedure or manually generated dump transaction command will free
sufficient space to allow suspended processes to awaken automatically and
continue processing.
Sometimes, however, a very large or long-running transaction may prevent
dump transaction from freeing sufficient space for processing to continue.
When this problem arises in SQL Server 10.x, there are two ways to make
space available in the transaction log:
* Extend the transaction log with the alter database command
or
* Kill the large or long-running transaction and then issue the select
lct_admin("unsuspend",dbid) command to awaken the suspended threads.
Problem
-------
SQL Server version 11.x includes a memory-based private log cache to reduce
contention on transaction log writes, which can result in the following
problematic scenario when using lct_admin("unsuspend"):
1. SQL Server cannot complete the kill and roll back the large or
long-running transaction because the transaction log is full.
2. The full transaction log prevents SQL Server from flushing the contents
of the private log cache to disk.
3. The database administrator must reboot SQL Server in order to terminate
the transaction and free up transaction log space.
Therefore, Sybase SQL Server 11.x database administrators must not execute
select lct_admin("unsuspend", dbid) when processes have become "LOG
SUSPEND"ed.
Solution
--------
If a very large or long-running transaction has prevented dump transaction
from freeing sufficient space for processing to continue, SQL Server 11.x
database administrators should take one of the following two approaches:
Approach 1
----------
Use alter database to add transaction log space to the database with the
"LOG SUSPENDED"ed users:
alter database database_name
log on logdevice_name =3D size
Approach 2
----------
Free up transaction log space held by the large or long-running transaction
by following these steps:
1. Obtain the process id (spid) of the large or long-running transaction:
select spid from master..syslogshold
where dbid =3D dbid
where dbid is the id of the database with "LOG SUSPEND"ed users.
2. Kill the large or long-running transaction:
kill spid
where spid is the output from step 1.
3. Monitor the master..syslogshold table for a change in spid for the
database with "LOG SUSPEND"ed users to determine whether the large or
long-running transaction has been killed.
------------------------------------------------------------------
Note
It can take some time for SQL Server to kill the large or
long-running transaction. If you are concerned about the time,
consider using Approach 1 instead of Approach 2.
------------------------------------------------------------------
1. Once the large or long-running transaction has been killed, issue a
dump transaction command for the database with the "LOG SUSPEND"ed
users.
2. Issue the sp_who command to determine whether there are still LOG
SUSPENDed users for that database. If so, return to step 1, above.
------------------------------------------------------------------------
1224 SQL Server 11.x Product Interoperability and Platform Compatibility
---- (revised)
http://www-esnt.sybase.com/2106.htm
Summary
-------
This note contains SQL Server 11.x product interoperability and platform
compatibility information. The listed Sybase products have been verified to
work with SQL Server 11.x.
Contents
--------
This document was revised to include
* jConnect
* ODBC Driver Kit 10.0.4 and 11.1
* Sybase IQ 11.1 and 11.2
* Replication Server 11.0.2 and 11.0.3
Sybase SQL Server 11.x has been extensively tested against other Sybase
products, as listed in matrices under:
* Verified Sybase Product Interoperabilities page 9
* Tested Open Client/Server Platform Compatibilities page 12
------------------------------------------------------------------
Note
Although these matrices include SQL Server releases 4.9.2 and
10.0.x, Sybase support and maintenance for these releases ends
Dec. 30, 1997 (excluding Japan). For information on migration
support and end of support status, see our Migration Resource
Guide web page http://www.sybase.com/products/system11/migration/
or call our support hotline, 1-800-88SYBASE. Outside of the US and
Canada, dial your local Sybase office.
------------------------------------------------------------------
Verified Sybase Product Interoperabilities
The following table shows Sybase product releases that work with SQL Server
releases.
SQL SQL SQL
Products Server Server Server Comments
4.9.2 10.0.2 11.x
---------------------------------------------------------------------------
DB-Library
4.2.5 X X X
DB-Library
4.6.2 X X X
All versions of DB-Library
DB-Library X X X supported in SQL Server 4.9.2 and
10.0.2 10.0.2 are supported in 11.x. For
DB-Library complete compatibility
10.0.3 X X information, see the Client/Server
Supplement for your platform.
DB-Library
10.0.4 X X
DB-Library
11.1 X X
---------------------------------------------------------------------------
Client-Library
10.0.2 X X
Client-Library =20
10.0.3 X X See "Distributed Services
Client-Library Platforms" in this technote.
10.0.4 X X
Client-Library
11.1 X X
---------------------------------------------------------------------------
Open Server
10.0.2 X X
Open Server
10.0.3 X X To move from SQL Server 4.9.2,
upgrade Open Server applications
Open Server X X to Open Server 10.0.3 or higher.
10.0.4
Open Server
11.1 X X
---------------------------------------------------------------------------
Embedded SQL/C
10.0.3 X X
Embedded SQL/C
10.0.4 X X
Embedded SQL/C
11.1 X X
Embedded
SQL/Cobol X X
10.0.3
Embedded
SQL/Cobol X X
10.0.4
Embedded
SQL/Cobol 11.1 X X
---------------------------------------------------------------------------
XA Server for
Tuxedo X X
XA Library X X
---------------------------------------------------------------------------
Replication
Server 10.0.3 X X X
Replication
Server 10.1 X X X
Replication
Server 11.0.1 X X X
Replication
Server 11.0.2 X X
Replication
Server 11.0.3 X X
---------------------------------------------------------------------------
SQL Server
Manager 10.0.1 X SQL Server Manager 11.0 is
required for SQL Server 11.x. They
SQL Server X X X are bundled together.
Manager 11.0
---------------------------------------------------------------------------
SQL Server
Monitor 10.0.1 X X X SQL Monitor 11.0 is required for
SQL Server SQL Server 11.x.
Monitor 11.0 X X X
---------------------------------------------------------------------------
SQL Debug SQL Debug does not support SQL
10.0.3 X X Server 11.x.
---------------------------------------------------------------------------
SA Companion SQL Server 11.x does not support
10.0 X X SA Companion. However, SQL Server
Manager is bundled with 11.x.
---------------------------------------------------------------------------
OmniCONNECT
10.1.2 X X
OmniCONNECT
10.5 X X
---------------------------------------------------------------------------
IQ does not support pre- 11.0.1
Sybase IQ SQL Server releases as a catalog
11.0.3 X X server. However, attached database
servers can include SQL Server
releases 4.9.1 or higher.
---------------------------------------------------------------------------
Sybase IQ 11.1 X Requires SQL Server 11.0.2 or
higher.
---------------------------------------------------------------------------
Requires SQL Server 11.0.2.2 for
Sun, IBM, Dec, HP; requires SQL
Sybase IQ 11.2 X Server 11.0.2.1 for Windows NT;
and for Siemens Nixdorf, requires
SQL Server 11.0.2.0.
---------------------------------------------------------------------------
PowerBuilder 4 X X X
PowerBuilder 5 X
---------------------------------------------------------------------------
ODBC Driver
Kit 10.0.4 X X ODBC Driver 2.0.x compliant.
---------------------------------------------------------------------------
ODBC Driver 3.0 compliant. For SQL
ODBC Driver Server 4.9.2, works only with
Kit 11.1 X X X Sybase's catalog stored
procedures, (included in kit).
---------------------------------------------------------------------------
Requires JDK 1.0.2 from Sun
Microsystems. You can download
jConnect X this from
http://www.javasoft.com/pr
oducts/jdk/1.0.2/index.html .
---------------------------------------------------------------------------
Tested Open Client/Server Platform Compatibilities
--------------------------------------------------
This section shows the tested platform combinations for Open Client/Server
and SQL Server:
* Open Client/Server 10.0.3 and 11.1
* Open Client/Server 10.0.3 and 10.0.4
* Sybase IQ 11.1=20
* Sybase IQ 11.2=20
* Replication Server 11.0.1=20
* Replication Server 11.0.2
* Replication Server 11.0.3
Open Client/Server 10.0.3 and 11.1
----------------------------------
This table shows Open Client/Server (OCS) 10.0.3, 11.1 and SQL Server 4.9.2,
10.0.2.x, 11.1 with the tested platform combinations.
Client-
Platform/ SQL Server Library DB-Library Open
Server
Operating System
11.x 10.0.2.x 4.9.2 11.1 10.0.3 11.1 10.0.3 11.1
10.0.3
Digital UNIX 3.2 X X
---------------------------------------------------------------------------
HP 9000/800 HP-UX
10.0.1 (SQL Server
on HP-UX 10.0, OCSX X X X X X X X
on HP-UX 10.0.1)
---------------------------------------------------------------------------
IBM RS/6000 AIX
4.1.4 (SQL Server
on AIX 4.1.3, OCS X X X X X
on AIX 4.1.4)
---------------------------------------------------------------------------
Solaris 2.4 SPARC X X X X X X X X X
---------------------------------------------------------------------------
Windows 3.1 n/a n/a n/a X X X X n/a n/a
---------------------------------------------------------------------------
Windows NT 3.5
(SQL Server on NT
3.5, OCS on NT X X X X X X X
3.5.1)
---------------------------------------------------------------------------
Windows 95 n/a n/a n/a X X X X X
---------------------------------------------------------------------------
a. With symmetric multiprocessing (SMP) systems only.
Distributed Services Platforms=20
------------------------------
The following table shows the Open
Client/Server platform support for Distributed Services. These services are
optional for Release 11.1. Existing applications work the same as for
Release 10.x without Distributed Services.
The abbreviations are:
* C=AFclients only
* S=AFservers only
* CS=AFboth clients and servers
Novell
Platform DCE CyberSafe Microsoft NetWare Banyan
Challenger NT Registry StreetTalk
4.0
HP 9000/800
HP-UX 10.0.1 CS CS
---------------------------------------------------------------------------
IBM RS/6000
AIX 4.1.4 CS
---------------------------------------------------------------------------
Sun Solaris
2.4 (SPARC) CS CS
---------------------------------------------------------------------------
Windows 3.1 C C C C
---------------------------------------------------------------------------
Windows 95 CS
---------------------------------------------------------------------------
Windows NT
3.5 CS
---------------------------------------------------------------------------
Security Guardian
-----------------
Client applications require Client-Library 11.1 to use Security Guardian, a
separate product that allows SQL Server 11.x to use third-party security
services. One Security Guardian is required for each SQL Server, and it must
reside on the same node/system.
Security Guardian is available on these platforms:
* HP9000/800 HP-UX 10.0.1
* IBM RS/6000 AIX 4.1.4
* Sun Solaris 2.4 (SPARC)
* Windows NT 3.5
Open Client/Server 10.0.3 and 10.0.4
This table shows OCS 10.0.3, 10.0.4 and SQL Server 4.9.2, 10.2.x, 11.x with
platform combinations that have been tested. Blank cells indicate
combinations that have not been tested.
Platform/ SQL Server Client-Library DB-Library Open
Server
Operating System 11.x 10.0.2.x 4.9.2 10.0.3 10.0.4 10.0.3 10.0.4 10.0.3
10.0.4
Digital UNIX 3.2 X X X X X
---------------------------------------------------------------------------
HP 9000/800
HP-UX 10.0.1
(SQL Server on X X X X X X X =
X
HP-UX 10.0, OCS
on HP-UX 10.0.1)
---------------------------------------------------------------------------
IBM RS/6000 AIX
4.1.4 (SQL
Server on AIX X X X X X X
4.1.3, OCS on
AIX 4.1.4)
---------------------------------------------------------------------------
Solaris 2.4
SPARC X X X X X X X X =
X
---------------------------------------------------------------------------
SunOS 4.1.3 n/a X X X =
X
---------------------------------------------------------------------------
Windows 3.1 n/a n/a n/a X X X X n/a
n/a
---------------------------------------------------------------------------
Windows NT 3.5
(SQL Server on
NT 3.5, OCS on X X n/a X X X X X =
X
NT 3.5.1)
---------------------------------------------------------------------------
Windows 95 n/a n/a n/a X X X X X =
X
---------------------------------------------------------------------------
a. With symmetric multiprocessing (SMP) systems only.
ODBC Driver Kit 10.0.4/ODBC 2.x Compliance
PC client applications may use Sybase ODBC driver kit that connects to SQL
Server 10.0.2 and 11.x on these platforms.
* Windows 3.1
* Windows 95
* Windows NT 3.5.1
* Windows NT 4.0
ODBC Driver Kit 11.1/ODBC 3.0 Compliance
PC client applications may use Sybase ODBC driver kit that connects to SQL
Server releases 4.9.2 (when used with the catalog stored procedures), 10.0.2
and 11.x on these platforms.
* Windows 95
* Windows NT 3.5.1
* Windows NT 4.0
Sybase IQ 11.1
--------------
Sybase IQ 11.1 has been tested with Open Client and SQL Server for these
versions and platforms:
SQL Server Open Client
Platform/ Operating System
11.0.2.x 10.0.2.x 4.9.2 11.1 10.0.4
HP 9000/800 HP-UX 10.20 X n/a n/a X
---------------------------------------------------------------------------
Solaris 2.4 SPARC X n/a n/a X
---------------------------------------------------------------------------
IBM RS/6000 AIX 4.1.4 (Requires
AIX patches, detailed in Sybase X n/a n/a X
IQ Release Bulletin.)
---------------------------------------------------------------------------
Digital-Unix 4.0.A (Open Client
on 10.0.3 only) X n/a n/a see note
---------------------------------------------------------------------------
Windows NT 4.0 X n/a n/a X
---------------------------------------------------------------------------
Sybase IQ 11.2
--------------
Sybase IQ 11.2 has been tested with Open Client and SQL Server for these
versions and platforms:
SQL Server Open Client
Platform/ Operating System
11.0.2.x 10.0.2.x 4.9.2 11.1 10.0.4
HP 9000/800 HP-UX 10.10 and 10.20 X n/a n/a X
---------------------------------------------------------------------------
Solaris 2.5.1 SPARC X n/a n/a X
---------------------------------------------------------------------------
IBM RS/6000 AIX 4.1 and 4.2 X n/a n/a X
---------------------------------------------------------------------------
Siemens Nixdorf SINIX 5.4.3B X n/a n/a X
---------------------------------------------------------------------------
Digital-Unix 4.0.A X n/a n/a X
---------------------------------------------------------------------------
Windows NT 4.0 X n/a n/a X
---------------------------------------------------------------------------
Replication Server 11.0.1
-------------------------
Replication Server 11.0.1 has been tested with Open Client/Server and SQL
Server for these versions and platforms:
Platform/ SQL Server Client-Library DB-Library Open Server
Operating
System 11.x 10.0.2.x 4.9.2 11.1 10.0.3 11.1 10.0.3 11.1 10.0.3
HP 9000/800
HP-UX 10.0.1 X X X X X X
---------------------------------------------------------------------------
Solaris 2.4
SPARC X X X X X X
---------------------------------------------------------------------------
Windows NT
3.5 X X X X
---------------------------------------------------------------------------
Replication Server 11.0.2
-------------------------
Replication Server 11.0.2 has been tested with Open Client/Server and SQL
Server for these versions and platforms:
Platform/ SQL Server Client-Library DB-Library Open Server
Operating
System 11.x 10.0.2.x 4.9.2 11.1 10.0.3 11.1 10.0.3 11.1 10.0.3
HP 9000/800
HP-UX 10.0.1 X X X X X
---------------------------------------------------------------------------
Solaris 2.4
SPARC X X X X X
---------------------------------------------------------------------------
Windows NT
3.5 X X X X X
---------------------------------------------------------------------------
Replication Server 11.0.3
-------------------------
Replication Server 11.0.3 has been tested with Open Client/Server and SQL
Server for these versions and platforms:
Platform/ SQL Server Client-Library DB-Library Open Server
Operating
System 11.x 10.0.2.x 4.9.2 11.1 10.0.3 11.1 10.0.3 11.1 10.0.3
HP 9000/800
HP-UX 10.0.1 X X X X X
---------------------------------------------------------------------------
Solaris 2.4
SPARC X X X X X
---------------------------------------------------------------------------
Windows NT
3.5 X X X X
---------------------------------------------------------------------------
Windows NT
4.0 X X X
---------------------------------------------------------------------------
----------------------------------------------------------------------------
Feedback
--------
Staff
Principal editor: Betsy Brazy
Contributing writers: Hal Kempthorne, Vinaye Misra, Jenny Schaffer
Send comments and suggestions to: Sybase Technical News, 6475 Christie
Avenue, Emeryville, CA 94608 or email technews@sybase.com.
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.
--------------------------------------------------
If you ever want to remove yourself from this mailing list, you can send
mail to "majordomo@sybase.com" with the following command in the body of
your email message:
unsubscribe inews-technews-full
If you ever want to add yourself from this mailing list, you can send
mail to "majordomo@sybase.com" with the following command in the body of
your email message:
subscribe inews-technews-full