----------------------------------------------------------------------------

                              SYBASE TECHNICAL NEWS

           Volume 7, Number 12                     December 1998

----------------------------------------------------------------------------

Introduction

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 are included on the Technical Library 
CD and web pages.

Wondering About Device Size Limits? 

Technical Note 800 discusses devices sizes for all plaforms, from Sybase 
SQL Server 11.0.x through Adaptive Server Enterprise 11.5.1. 

This document is published in the Technical Library CD-ROM. It also is 
available online at the Technical Documents website, 
http://techinfo.sybase.com To find the document, select DOCID# and enter
800 in the text-entry field, then press "Go." 

This document is updated as new information arises. The next version of 
TechNote 800, to be released by Dec. 31, will include 11.9.2 information. 

Additionally, you can customize the Sybase Support website to retrieve 
only information - including product manuals and downloads - that matches 
your profile, via the MySupport Sybase portal at 
http://mysupport.sybase.com. 

This site is open to customers and non-customers.

------------------------------------------------

In This Issue

------------------------------------------------

Tip of the Month

TechNotes:

   * Configuring Two Servers for Remote Dumps and Loads
     http://techinfo.sybase.com/css/techinfo.nsf/DocId/ID=20334

     Doc Summary:
     This document describes how to configure and test two servers for
     remote dumps and loads

   * FAQs about Datatypes
     http://techinfo.sybase.com/css/techinfo.nsf/DocId/ID=20316
     Doc Summary:

     This technote answers questions that customers frequently ask about
     certain SQL Server/Adaptive Server datatypes.

Other Useful Information

Subscription

-----------------------------------------------

Tip of the Month: Determining Unused Log Space

-----------------------------------------------

When you need to check free space in the server logs, users typically use
the stored procedure sp_helpdb. While sp_helpdb is useful for a general
estimation of free space, for a precise figure use one of the following
methods:

   * dbcc checktable (syslogs)

   * Determine the number of data pages in the transaction log via isql
     script, for example:

     select data_pgs (8, doampg)
     from sysindexes where id=8
     go

Each method has advantages.

Sybase recommends sp_helpdb for most situations because it reports quickly.
sp_helpdb uses the unreserved page count in sysusages. However, unreserved
page count is updated intermittently and therefore may not accurately
reflect the actual state of the database. Thus, when sp_helpdb reports free
space, when you perform an insert you may run out of space, resulting in
error message 1105, which reads in part:

Can't allocate space for object ... because log segment full

If this error occurs, follow the instructions in Runtime 1105 Errors: State
3 in the "Error Message Writeups" chapter of the Adaptive Server Enterprise
Troubleshooting and Error Messages Guide.

The dbcc checktable (syslogs) command also checks for possible corruption as
well as the size of the log. However, it can take a long time to run,
depending on the size of the log. For more information about dbcc
checktable, see the chapter, "Checking Database Consistency" in the Adaptive
Server Enterprise System Administration Guide.

The isql script is more accurate than sp_helpdb. It is described in the
Error 1105 section in "Error Message Writeups" chapter of the Adaptive
Server Enterprise Troubleshooting and Error Messages Guide.

----------------------------------------------------

TechNotes:

----------------------------------------------------
----------------------------------------------------

Configuring Two Servers for Remote Dumps and Loads

----------------------------------------------------

This document describes how to configure and test two servers for remote
dumps and loads.

Preparing the interfaces files

Perform the following steps so that the servers' interfaces files reference
each other to allow remote dumps and loads:

  1. Determine the names of the servers and backup servers.
  2. Examine both servers' interfaces files.
  3. Modify the serverA interfaces file, if necessary, so that it includes
     the server names for serverB and serverB_backup.
  4. Modify the serverB interfaces file, if necessary, so that the file
     includes the server names for serverA and serverA_backup.

Following these modifications, you will be able to log into each server
using isql -S.

PREPARING sysservers

This section includes

   * Examining sysservers
   * Checking sysservers Entries
   * Adding Server Names

EXAMINING sysservers

Examine each server's sysservers table to ensure that the servers and backup
servers are properly referenced. Generally, you will want four entries in
sysservers for each of the servers:

1. Local server name
2. Local backup server
3. Remote server name
4. Remote backup server

If either the local or remote server doesn't have a backup server, use
sybinit or sybsetup to install a backup server. Remember that the server
names must be unique to each other.

For information on using sybinit and sybsetup, see the installation guide
for your platform.

CHECKING sysservers ENTRIES

Perform these instructions for each server.

Before you begin, determine which servers exist:

1> sp_helpserver
2> go

If the local server and/or the local backup server display, use
sp_dropserver to remove the entries.

If the remote server or remote backup server display, use sp_dropserver to
remove the entries.

----------------------------------------------------------------------------

Warning: if sp_helpserver displays any other servers in sysservers other
than the local or remote servers that you are configuring, do not alter
their sysservers entries.

----------------------------------------------------------------------------

ADDING SERVER NAMES

Perform these instructions for each server.

Use the syntax in this section to add server names for

   * Sybase SQL Server 11.0.x
   * Adaptive Server Enterprise 11.5.x and 11.9.x.

SYBASE SQL SERVER 11.0.x

1> sp_addserver SYB_BACKUP, null, 
2> go
1> sp_addserver , null
2> go
1> sp_addserver , local
2> go
1> sp_addserver , null
2> go

For more information on using sp_addserver, see the Sybase SQL Server 11.0.x
Reference Manual.

ADAPTIVE SERVER ENTERPRISE 11.5.x, 11.9.x

1> sp_addserver SYB_BACKUP, null, 
2> go
1> sp_addserver , null
2> go
1> sp_addserver , local
2> go
1> sp_addserver , sql_server
2> go

For more information on using sp_addserver, see the Adaptive
Server Enterprise Reference Manual.

PERFORMING REMOTE DUMPS AND LOADS

At this point you have updated sysservers information on both servers. All
entries are configured for performing local dumps and loads and remote dumps
and loads.

Recycle both servers because server names are read at start-up.

In order for the remote dumps and loads to work, both servers and their
backup servers must be running.

Example: Dumping a database on the local server to the remote machine using
the remote backup server.

PERFORM THE FOLLOWING:

1> dump database  to "remote_machine_path/..." at
2> REMOTE_BACKUP_SERVER
3> go

Example: Loading a database on the local server from the remote machine
using the remote backup server.

PERFORM THE FOLLOWING:

1> load database  from "remote_machine_path/..." at
2> REMOTE_BACKUP_SERVER
3> go

For information on performing dumps and loads, see the Adaptive Server
Enterprise System Administration Guide.
----------------------------------------------------------------------------

NOTE: You cannot load a Release 11 server dump to a previous-version server.
However, you can load dumps from earlier versions into more recent versions.
For example, an 11.0.2 dump can be loaded to 11.0.3 or Adaptive Server. An
11.5 dump cannot be loaded to an11.0.x server.

----------------------------------------------------------------------------

TESTING FOR CONNECTIVITY

Use these tests to check the connections of the two servers.

  1. Telnet from the local machine to the remote machine.
  2. Log into the remote machine then telnet to the local machine.
  3. From the local machine, test the interfaces file entry by entering the
     following:

     %isql -Usa -P -Sremote_backup_server
     1> exit

If any of these tests fail, re-examine your interfaces file to ensure the
entries are correct and check your SYBASE variable to determine that it is
set correctly.

--------------------------------------

FAQs about Datatypes

--------------------------------------

This TechNote answers the following questions about datatypes for Adaptive
Server Enterprise:

   * What are timestamp values in table columns?

   * Can a query update a timestamp value?

   * How are columns of the bit datatype stored in a table row?

   * What is the rule about converting float to char with the convert
     function?

   * Does the command set dateformat affect output format?

   * Can I get a past date with the dateadd function?

For more information on datatypes, see the Transact-SQL User's Guide. For
details on the float datatype, see TechNote 20313, Using the float Datatype.

WHAT ARE TIMESTAMP VALUES IN TABLE COLUMNS?

The timestamp datatype is an 8-byte binary value. It is a sequence number,
not an absolute value, used with the server's browse mode. Each database has
a value used internally for dump/load and for change logging. For the
database, any time a table with a timestamp column is changed, the value
automatically changes.

CAN A QUERY UPDATE A TIMESTAMP VALUE?

No, a timestamp column cannot be updated directly by a query. The server
automatically updates the timestamp column when any other column changes.

If a query were allowed to update a timestamp column, the server, in doing a
select/update, would have no guarantee that the row remained unchanged
between the select in browse mode and the update.

Attempting to update a timestamp column results in this error:

Msg 272, Level 16, State 1: Server 'SYBASE', Line 1: Can't update a
TIMESTAMP column.

HOW ARE COLUMNS OF THE BIT DATATYPE STORED IN A TABLE ROW?

SQL Server and Adaptive Server automatically collect multiple bit datatypes
into bytes. One byte of a table row can hold eight bit columns.

WHAT IS THE RULE ABOUT CONVERTING FLOAT TO char WITH THE CONVERT FUNCTION?

When you convert from float to character using the convert function, the
format of the resulting string is dependant upon the magnitude of the float.

The server uses sprintf %g for scientific notation of values with these
exponents:

   * < -4

   * >16

However, float dataypes inherently are imprecise. A better option is to use
the Transact-SQL str function instead of convert. For example:

declare @x float
select @x = 11.3
select str(@x, 6, 2)
go

------
11.30

DOES THE COMMAND SET DATEFORMAT AFFECT OUTPUT FORMAT?

No, the set dateformat command does not affect the display of datetime
values. This command enables you to specify the format used for input of
datetime values, not the output format. For example:

1> set dateformat ydm
2> go
1> insert into u_tab (dtm) values ("91.26.7")
2> go

(1 row affected)

1> select dtm from u_tab
2> go

dtm
--------------------------
       Jul 26 1991 12:00AM
(1 row affected)

You can use the convert function to convert the datetime value to a
character string. Specify the format of the datetime output with the convert
function's style argument.

To convert the current date to a date displayed in style 3, enter the
following:

1> select convert (char (10), getdate (), 3)
2> go

02/10/98

For more information about entering dates, see the Transact-SQL User's
Guide. For more information about the convert function, see the Adaptive
Server Enterprise Reference Manual.

CAN I GET A PAST DATE WITH THE DATEADD FUNCTION?

To get a date some time in the past, use the dateadd function and specify a
negative number for the numeric argument. For example:

1> select year_ago = dateadd(year, -1, getdate()),
2> two_quarters_back = dateadd(quarter, -2, getdate())
3> go

This query gives you last year and two quarters back.

--------------------------------------

Other Useful Information

--------------------------------------

   * SQL Server 11.x Bug Report

             http://techinfo.sybase.com/css/techinfo.nsf/DocID/ID=2434

     This report contains critical, severe and annoying bugs found in Sybase
     SQL Server 11, versions 11.0, 11.0.1, 11.0.1.1, 11.0.2, 11.0.2.1,
     11.0.2.2 and 11.0.3. It includes a description of the bug and a
     workaround if one has been documented.

   * Certification Reports

             http://techinfo.sybase.com/css/techinfo.nsf/Certification

     Browse by certification

   * Product Availability

             http://techinfo.sybase.com/css/techinfo.nsf/New+Products

     These Alerts feature new Sybase products which became available for
     sale during the time span listed on the Alert. Included are
     instructions on actions you need to take and an explanation of
     abbreviations.

   * End-of-Life/End-of-Support

             http://techinfo.sybase.com/css/techinfo.nsf/eolproducts

     Collection of end-of-support letters to customers having contracts for
     various products on different platforms. Each letter describes the
     end-of-support plan for these products.

   * Error Messages

             http://techinfo.sybase.com/css/techinfo.nsf/Topic

   * Migrating to Sybase Adaptive Server Enterprise 11.5

             http://techinfo.sybase.com/css/techinfo.nsf/DocID/ID=34982-01-1150-01

     A guide to help you migrate from 4.x, 10.x and 11.0.x, including
     templates and checklists.

   * What's New

             http://techinfo.sybase.com/css/techinfo.nsf/Whats+New

     Browse the most recently released documents.

For frequent updates, visit the Sybase Technical Library on the World Wide
Web at http://techinfo.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 Technical Library from behind the firewall in the
usual manner, allowing them to retain the security benefits of using SSL
with their web browser.

Subscription Information

--------------------------------------

Send comments to technews@sybase.com. To receive this document by regular
email, send email to majordomo@sybase.com, and in the body of your message
put subscribe inews-technews-full. For a summary version by email, put
subscribe inews-technews-summary. No subject line is necessary.

Staff

Principal Editor: Betsy Brazy

Production Editors: Deborah Blakeney, Rodney Esperanza, Leigh Ann Hussey

Contributors: Sid Khandige, Mike Maas, vinaye Misra, Michael Shields,
Brandon Smith, Sharyn Thollaug

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