Sybase FAQ: 1/19 - index

Archive-name: databases/sybase-faq/part1
URL: http://www.isug.com/Sybase_FAQ
Version: 1.7
Maintainer: David Owen
Last-modified: 2003/03/02
Posting-Frequency: posted every 3rd month
A how-to-find-the-FAQ article is posted on the intervening months.

Sybase Frequently Asked Questions


Sybase FAQ Home PageAdaptive Server Enterprise FAQAdaptive Server Anywhere FAQ
Replication Server FAQSearch the FAQ
Sybase FAQ

Main Page



* Where can I get the latest release of this FAQ?
* What's new in this release?
* How can I help with the FAQ?
* Who do I tell about problems in the FAQ?
* Acknowledgements and Thanks
* Hall of Fame
* Copyright and Disclaimer
* General Index



Main | ASE | ASA | REP | Search



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

Where can I get the latest release of this FAQ?

International Sybase User Group

The main page for this site is http://www.isug.com/Sybase_FAQ. It is hosted
there by kind permission of the International Sybase User Group (http://
www.isug.com) as a service to the Sybase community.

To get a text version of this FAQ:

ftp://ftp.midsomer.org/pub/FAQ_txt_tar.Z

or

ftp://ftp.midsomer.org/pub/FAQ_txt.zip

If you want uncompressed versions of the various sections, they can be got
from ASE, ASA & REP.

To get the HTML for this FAQ:

ftp://ftp.midsomer.org/pub/FAQ_html_tar.Z

or

ftp://ftp.midsomer.org/pub/FAQ_html.zip

Last major update: 21st February 2003.

Back to Top

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

What's new in this release?

Release 1.9

* Running multiple servers on a single server (UNIX and NT).

Back to Top

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

What's happening with the FAQ?

I have not had a lot of time to spend on the FAQ this year. Mainly, this is
down to work, or the lack of it. I know, we are all in the same boat. Well, it
has meant that I have had a lot less free time than I used to and as a result
the FAQ has not been kept as up to date as I would like. Sadly, the work I have
been doing is with those other database vendors, but we won't name them here.
Anyway, that is the sob story over and done with. If anyone thinks that they
would like to see more effort applied, I would be happy to hand the mantle
over. Since the amount of help that I have actually seen amounts to about
practically none, then I am sure I will not be over-run with offers! I will
definitely have more time come January and plan some serious work on it then.

Back to Top

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

How can I help with the FAQ?

I have had offers from a couple of people to write sections, but if you feel
that you are in a position to add support for a section, or if you have some
FAQs to add, please let me know. This is a resource that we should all
support, so send me the stuff and I will include it.

Typos and specific corrections are always very useful. Less useful is the
general I don't think that section x.y.z is very understandable. Sorry to sound
harsh, but what I need is actual text that is more readable. Better still is
actual HTML that makes it stand out and sing (if necessary)!

Currently I am looking for maintainers of the following sections Replication,
Adaptive Server Anywhere, IQ server, MPP Server and Open Server. I am not sure
whether to add a section for Omni Server. I sort of feel that since Omni has
been subsumed into ASE as CIS that any FAQs should really be incorporated
there. However, if you know of some good Omni gotchas or tips, whether they
are still there in CIS or not, please send them in. I certainly plan to have a
subsection of ASE dealing with CIS even if Omni does not get its own major
section. I also think that we need sections on some of the really new stuff.
Jaguar and the new engines also deserve a spot.

Another very useful way that you can help is in getting people to update their
links. I have seen lots of links recently, some still pointing to Pablo's
original, some pointing to Tom's site but referring to it as coming from the
SGI site.

Back to Top

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

Who do I tell about problems in the FAQ?

The current maintainer is David Owen ( dowen@midsomer.org) and you can send
errors in the FAQ directly to me. If you have an FAQ item (both the question
and the answer) send it to sybfaq@midsomer.org and I will include it.

Do not send email to any of the officials at ISUG, they are simply hosting the
FAQ and are not responsible for its contents.

Also, do not send email to Sybase, they are not responsible for the contents
either. See the Disclaimer.

Back to Top

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

Acknowledgements and Thanks

Special thanks must go to the following people for their help in getting this
FAQ to where it is today.

* Pablo Sanchez for getting the FAQ off the ground in the first place and for
many years of dedicated work in maintaining it.

* Anthony Mandic (am@peppler.org) for a million things. Patiently answering
questions in all of the Sybase news groups, without which most beginners
would be lost. For supporting and encouraging me in getting this FAQ
together and for providing some pretty neat graphics.

* The ISUG, especially Luc Van der Veurst (lucv@az.vub.ac.be) and Michael
Peppler (mpeppler@peppler.org), for hosting this FAQ and providing support
in setting up the website.

* The members of the various news groups and mailing lists who, like Anthony,
provide unstinting support. The list is fairly long, but I think that Bret
Halford (bret@sybase.com) deserves a mention. If you go to Google News
and do a search, he submits almost as many replies as Anthony.

Back to Top

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

Hall of Fame

I am not sure how Pablo chose his select list, there is certainly no question
as to their inclusion. I know that there are a couple of awards that the ISUG
give out each year for the people that the ISUG members believe have
contributed most to the Sybase community that year. I think that this section
should honour those people that deserve an award each and every year. If you
know of a candidate, let me know and I will consider his or her inclusion.
Self nominations are not acceptable :-)

The following people have made it to the Sybase FAQ Hall of Fame:

* Michael Peppler (mpeppler@peppler.org) For Sybperl and all of the other
tools of which he is author or instigator plus the ceaseless support that
he provides through countless mailing lists, newsgroups and directly via
email.

* Scott Gray (gray@voicenet.com) Father of sqsh, much more than simply a
replacement for isql. How anyone developing or administering Sybase can
survive without it, I will never know.

* Pablo Sanchez ( www.hpdbe.com) Pablo got the first web based FAQ off the
ground, wrote most (all?) of the first edition and then maintained it for a
number of years. He did a fantastic job, building a resource that is
worth its weight in gold.

Back to Top

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

Copyright and Disclaimer

Distribution

You are free to copy or distribute this FAQ in whole or in part, on any medium
you choose provided that you:

* include this Copyright and Disclaimer notice;
* do NOT distribute or copy, in any fashion, with the intention of making a
profit from its use;
* give FULL attribution to the original authors.

Disclaimer

This FAQ is provided as is without any express or implied warranties. Whilst
every endeavour has been taken to ensure the accuracy of the information
contained within the articles, the author, nor any of the contributors, assume
responsibility for errors or omissions, or for damages resulting from the use
of the information contained herein.

If you are not happy about performing any of the suggestions contained within
this FAQ, you are probably better off calling Sybase Technical Support.

Copyright

This site and all its contents belongs to the Sybase FAQ (http://www.isug.com/
Sybase_FAQ).

Unless explicitly stated in an article, all material within this FAQ is
copyrighted. The primary copyright holders are David Owen and Pablo Sanchez.
However, all contributed material is, and will remain, the property of the
respective authors and contributors.

Back to Top

-------------------------------------------------------------------------------
ASE

1.1: Basic ASE Administration

1.1.1 What is SQL Server and ASE anyway?
1.1.2 How do I start/stop ASE when the CPU reboots?
1.1.3 How do I move tempdb off of the master device?
1.1.4 How do I correct timeslice -201?
1.1.5 The how's and why's on becoming Certified.
1.1.6 RAID and Sybase
1.1.7 How to swap a db device with another
1.1.8 Server naming and renaming
1.1.9 How do I interpret the tli strings in the interface file?
1.1.10 How can I tell the datetime my Server started?
1.1.11 Raw partitions or regular files?
1.1.12 Is Sybase Y2K (Y2000) compliant?
1.1.13 How can I run the ASE upgrade manually?
1.1.14 We have lost the sa password, what can we do?
1.1.15 How do I set a password to be null?
1.1.16 Does Sybase support Row Level Locking?
1.1.17 What platforms does ASE run on?
1.1.18 How do I backup databases > 64G on ASE prior to 12.x?

1.2: User Database Administration

1.2.1 Changing varchar(m) to varchar(n)
1.2.2 Frequently asked questions on Table partitioning
1.2.3 How do I manually drop a table?
1.2.4 Why not create all my columns varchar(255)?
1.2.5 What's a good example of a transaction?
1.2.6 What's a natural key?
1.2.7 Making a Stored Procedure invisible
1.2.8 Saving space when inserting rows monotonically
1.2.9 How to compute database fragmentation
1.2.10 Tasks a DBA should do...
1.2.11 How to implement database security
1.2.12 How to shrink a database
1.2.13 How do I turn on auditing of all SQL text sent to the server
1.2.14 sp_helpdb/sp_helpsegment is returning negative numbers

1.3: Advanced ASE Administration

1.3.1 How do I clear a log suspend'd connection?
1.3.2 What's the best value for cschedspins?
1.3.3 What traceflags are available?
1.3.4 How do I use traceflags 5101 and 5102?
1.3.5 What is cmaxpktsz good for?
1.3.6 What do all the parameters of a buildmaster -d<device> -yall mean?
1.3.7 What is CIS and how do I use it?
1.3.8 If the master device is full how do I make the master database
bigger?
1.3.9 How do I run multiple versions of Sybase on the same server?
1.3.10 How do I capture a process's SQL?

1.4: General Troubleshooting

1. How do I turn off marked suspect on my database?
2. On startup, the transaction log of a database has filled and recovery has
suspended, what can I do?
3. Why do my page locks not get escalated to a table lock after 200 locks?

1.5: Performance and Tuning

1.5.1 What are the nitty gritty details on Performance and Tuning?
1.5.2 What is best way to use temp tables in an OLTP environment?
1.5.3 What's the difference between clustered and non-clustered indexes?
1.5.4 Optimistic versus pessimistic locking?
1.5.5 How do I force an index to be used?
1.5.6 Why place tempdb and log on low numbered devices?
1.5.7 Have I configured enough memory for ASE?
1.5.8 Why should I use stored procedures?
1.5.9 I don't understand showplan's output, please explain.
1.5.10 Poor man's sp_sysmon.
1.5.11 View MRU-LRU procedure cache chain.
1.5.12 Improving Text/Image Type Performance

1.6: Server Monitoring

1.6.1 What is Monitor Server and how do I configure it?
1.6.2 OK, that was easy, how do I configure a client?

2.1: Platform Specific Issues - Solaris

2.1.1 Should I run 32 or 64 bit ASE with Solaris?
2.1.2 What is Intimate Shared Memory or ISM?

2.2: Platform Specific Issues - NT/2000

2.2.1 How to Start ASE on Remote NT Servers
2.2.2 How to Configure More than 2G bytes of Memory for ASE on NT
2.2.3 Installation Issues

2.3: Platform Specific Issues - Linux

2.3.1 ASE on Linux FAQ

3: DBCC's

3.1 How do I set TS Role in order to run certain DBCCs...?
3.2 What are some of the hidden/trick DBCC commands?
3.3 Other sites with DBCC information.
3.4 Fixing a Munged Log

Performing any of the above may corrupt your ASE installation. Please do
not call Sybase Technical Support after screwing up ASE. Remember, always
take a dump of the master database and any other databases that are to be
affected.

4: isql

4.1 How do I hide my password using isql?
4.2 How do I remove row affected and/or dashes when using isql?
4.3 How do I pipe the output of one isql to another?
4.4 What alternatives to isql exist?
4.5 How can I make isql secure?

5: bcp

5.1 How do I bcp null dates?
5.2 Can I use a named pipe to bcp/dump data out or in?
5.3 How do I exclude a column?

6.1: SQL Fundamentals

6.1.1 Are there alternatives to row at a time processing?
6.1.2 When should I execute an sp_recompile?
6.1.3 What are the different types of locks and what do they mean?
6.1.4 What's the purpose of using holdlock?
6.1.5 What's the difference between an update in place versus a deferred
update? - see Q1.5.9
6.1.6 How do I find the oldest open transaction?
6.1.7 How do I check if log truncation is blocked?
6.1.8 The timestamp datatype
6.1.9 Stored Procedure Recompilation and Reresolution
6.1.10 How do I manipulate binary columns?
6.1.11 How do I remove duplicate rows from a table?

6.2: SQL Advanced

6.2.1 How to emulate the Oracle decode function/crosstab
6.2.2 How to implement if-then-else within a select-clause.
6.2.3 deleted due to copyright hassles with the publisher
6.2.4 How to pad with leading zeros an int or smallint.
6.2.5 Divide by zero and nulls.
6.2.6 Convert months to financial months.
6.2.7 Hierarchy traversal - BOMs.
6.2.8 Is it possible to call a UNIX command from within a stored
procedure or a trigger?
6.2.9 Information on Identities and Rolling your own Sequential Keys
6.2.10 How can I execute dynamic SQL with ASE
6.2.11 Is it possible to concatenate all the values from a column and
return a single row?
6.2.12 Selecting rows N to M without Oracle's rownum?
6.2.13 How can I return number of rows that are returned from a grouped
query without using a temporary table?

6.3: Useful SQL Tricks

6.3.1 How to feed the result set of one stored procedure into another.
6.3.2 Is it possible to do dynamic SQL before ASE 12?

7: Open Client

7.1 What is Open Client?
7.2 What is the difference between DB-lib and CT-lib?
7.3 What is this TDS protocol?
7.4 I have upgraded to MS SQL Server 7.0 and can no longer connect from
Sybase's isql.
7.5 The Basics of Connecting to Sybase
7.6 Connecting to ASE using ODBC
7.7 Which version of Open Client works with which ASE?
7.8 How do I tell the version of Open Client I am running?

9: Freeware

9.0 Where is all the code and why does Section 9 suddenly load in a
reasonable amount of time?

Stored Procedures

9.1.1 sp_freedevice - lists device, size, used and free.
9.1.2 sp_dos - This procedure graphically displays the scope of a
object
9.1.3 sp_whodo - augments sp_who by including additional columns: cpu,
I/O...
9.1.4 sp__revroles - creates DDL to sp_role a mirror of your SQL
Server
9.1.5 sp__rev_configure - creates DDL to sp_configure a mirror of your
SQL Server
9.1.6 sp_servermap - overview of your SQL Server
9.1.7 sp__create_crosstab - simplify crosstable queries
9.1.8 sp_ddl_create_table - creates DDL for all user tables in the
current database
9.1.9 sp_spaceused_table
9.1.10 SQL to determine the space used for an index.
9.1.11 sp_helpoptions - Shows what options are set for a database.
9.1.12 sp_days - returns days in current month.
9.1.13 sp__optdiag - optdiag from within isql
9.1.14 sp_desc - a simple list of a tables' columns
9.1.15 sp_lockconfig - Displays locking schemes for tables.

Shell Scripts

9.2.1 SQL and sh(1)to dynamically generate a dump/load database
command.
9.2.2 update statistics script

Perl/Sybperl

9.3.1 SybPerl - Perl interface to Sybase.
9.3.2 dbschema.pl - Sybperl script to reverse engineer a database.
9.3.3 ddl_insert.pl - creates insert DDL for a table.
9.3.4 int.pl - converts

12: Miscellany

12.1 What can Sybase IQ do for me?
12.2 Net-review of Sybase books
12.3 email lists
12.4 Finding Information at Sybase

ASA

Adaptive Server Anywhere

0.0 Preamble
0.1 What is ASA?
0.2 On what platforms is ASA supported?
0.3 What applications is ASA good for?
0.4 When would I choose ASA over ASE?
0.5 Does ASA Support Replication?
0.6 What is ASA UltraLite?
0.7 Links for further information

REP

Introduction to Replication Server

1.1 Introduction
1.2 Replication Server Components
1.3 What is the Difference Between SQL Remote and Replication
Server?

Replication Server Administration

2.1 How can I improve throughput?
2.2 Where should I install replication server?
2.3 Using large raw partitions with Replication Server on Unix.
2.4 How to replicate col = col + 1
2.5 What is the difference between an LTMs an a RepAgent?
2.6 Which Should I choose, RepAgent or LTM?

Replication Server Trouble Shooting

3.1 Why am I running out of locks on the replicate side?
3.2 Someone was playing with replication and now the transaction log
on OLTP is filling.

Additional Information/Links



4.1 Links
4.2 Newsgroups

Archive-name: databases/sybase-faq/part2
URL: http://www.isug.com/Sybase_FAQ
Version: 1.7
Maintainer: David Owen
Last-modified: 2003/03/02
Posting-Frequency: posted every 3rd month
A how-to-find-the-FAQ article is posted on the intervening months.

Sybase Frequently Asked Questions


Sybase FAQ Home PageAdaptive Server Enterprise FAQAdaptive Server Anywhere FAQ
Repserver FAQSearch the FAQ
[bar]

Adaptive Server Anywhere





0.0 Preamble
0.1 What is ASA?
0.2 On what platforms is ASA supported?
0.3 What applications is ASA good for?
0.4 When would I choose ASA over ASE?
0.5 Does ASA Support Replication?
0.6 What is ASA UltraLite?
0.7 Links for further information

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

0.0 Preamble

I make no claims to be an ASA expert! I am beginning to use it more and more,
and as I use it I am able to add stuff with more authority to this list. All
of what is here is very general. I am pressing people to help write some more
meaty parts. There is nothing here on how to recover from crashes that must
happen, or equivalent sections for those in the the ASE part. Performance and
Tuning would be a good section! If anyone out there knows of a good ASA faq,
then send it to me, and I will get it added. This is a resource that will help
us all. Come on all you TeamSybase/TeamPowerbuilder people, you must know
something on the subject <g>. It is unlikely that this is going to grow into a
particularly useful resource unless I get some serious help!

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

0.1 What is ASA?

ASA is a fully featured DBMS with transactional integrity, automatic rollback
and recovery, declarative RI, triggers and stored procedures.

While it comes out of Sybase's "Mobile and Embedded" division, it is NOT
limited to "small, desktop applications". There are many ASA implementations
supporting over 100 concurrent users. While not as scalable as ASE, it does
offer SMP support and versions for various Unix flavors as well as Netware and
NT/w2k. Multi-gigabyte databases are commonly used.

ASA offers a number of features that are not to be found in ASE:

* row level BEFORE and AFTER triggers
* long varchar and BLOB up to 2 gigabytes
* varchar up to 32k
* declarative RI with cascade actions
* all character and decimal data is stored var-len, using only the space
it needs

ASA is designed to be low-maintenance:

* File size automatically grows
* self-tuning
* re-uses space from deletes

ASA also includes:

* Java stored procs
* Stored procedure debugger (I am not sure what sort of debugger, just that
it has one.)

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

0.2 On what platforms is ASA supported?

Lots!

* Windows 95/98/ME, NT, 2000, CE
* Novell NetWare
* Solaris/SPARC
* Solaris/Intel
* IBM AIX
* Linux (RedHat)
* HP-UX

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

0.3 What applications is ASA good for?

ASA seems to have a number of niches. It is generally good at OLTP and can be
used as a basis for a general database project. There are certainly examples
of implementations supporting 100 or more users.

A major area for ASA databases is with applications that need to distribute the
database with the application as a general storage area for internal
components, but the database is not a major part of the deliverable. Sybase
themselves have done this with the IQ meta data storage. Prior to release 11
of IQ, the meta data was stored in an ASE database. Now, with IQ 12, the meta
data has moved to being stored in ASA. This makes the installation of IQ into
production environments much simpler.

ASA has excellent ODBC support, which makes it very attractive to tools
oriented towards ODBC.

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

0.4 When would I choose ASA over ASE?

* Ease of administration,e.g., self-tuning optimizer, db file is an OS file
(not partition).
* Lower footprint - runs on "smaller" machines.
* Lower cost, ASA is definitely cheaper than ASE on the same platform.
* Want to use SQL Remote (asynchronous replication)
* More complete SQL92 implementation.

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

0.5 Does ASA Support Replication?

In short, yes. ASA comes with SQL Remote, an asynchronous replication server.
SQL Remote is intended to be used in applications where the replication is
not intended to happen immediately. In fact, it may well be hours or even days
before the databases are synchronised. This makes it ideal for the roaming
salesman type apps where the guy is on the road all day and then dials in from
home, hotel or beach front to re-synch his pay price list with the master
server.

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

0.6 What is ASA UltraLite?

UltraLite is a version of ASA that runs on handheld devices.

Deployment
Windows 95/98, NT, 2000, CE
Palm Computing platform
WindRiver VxWorks
DOS
Symbian EPOC

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

0.7 I'm interested, where can I find more info?

Breck Carter has a very useful page at http://www.bcarter.com/home.html that is
full of detail.

General information can be found about ASA at:

http://www.sybase.com/products/anywh...oductinfo.html

It is a bit of a marketing page but there are some pointers to white papers
etc.

A very well written reviewers guide can be found at

http://www.sybase.com/products/anywh...ers_guide.html

The page has a link to a pdf document that contains lots of useful information.

Archive-name: databases/sybase-faq/part3
URL: http://www.isug.com/Sybase_FAQ
Version: 1.7
Maintainer: David Owen
Last-modified: 2003/03/02
Posting-Frequency: posted every 3rd month
A how-to-find-the-FAQ article is posted on the intervening months.

Sybase Frequently Asked Questions


Sybase FAQ Home PageAdaptive Server Enterprise FAQAdaptive Server Anywhere FAQ
Repserver FAQSearch the FAQ
[bar]

Sybase Replication Server



1. Introduction to Replication Server
2. Replication Server Administration
3. Troubleshooting Replication Server
4. Additional Information/Links



































Introduction to Replication Server



1.1 Introduction
1.2 Replication Server Components
1.3 What is the Difference Between SQL Remote and Replication Server?



Thanks go to Manish I Shah for major help with this introduction.

next prev ASE FAQ

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

1.1 Introduction

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

What is Replication Server

Replication Server moves transactions (insert, updates and deletes) at the
table level from a source dataserver to one or more destination dataservers.
The dataserver could be ASE or other major DBMS flavour (including DB2,
Informix, Oracle). The source and destinations need not be of the same type.

What can it do ?

* Move data from one source to another.
* Move only a subset of data from source to destination. So, you can
subscribe to a subset of data, or a subset of the columns, in the source
table, e.g. select * from clients where state = NY
* Manipulation/transformation of data when moving from source to destination.
E.g. it can map data from a data-type in DB2 to an equivalent in Sybase.*
* Provide a warm-standby system. Can be incorporated with Open Switch to
provide a fairly seamless fail-over environment.
* Merge data from several source databases into one destination database
(could be for a warehouse type environment for example).
* Move data through a complicated network down to branch offices, say, only
sending the relevant data to each branch.

(* This is one of Sybase replication's real strengths, the ability to define
function string classes which allow the conversion of statements from one SQL
dialect to match the dialect of the destination machine. Ed)

How soon does the data move

The data moves asynchronously. The time it takes to reach the destination
depends on the size of your transaction, level of activity in that particular
database (a database as in Sybase systems), the length of the chain (one or
more replication servers that the transaction has to pass through to reach the
destination), the thickness of pipe (network), how busy your replication server
is etc. Usually, on a LAN, for small transactions, this is about a second.

Back to top

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

1.2 Replication Server Components

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

Basic

Primary Dataserver

The source of data where client applications enter/delete and modify data. As
mentioned before, this need not be ASE, it can be Microsoft SQL Server, Oracle,
DB2, Informix. (I know that I should get a complete list.)

Replication Agent/Log Transfer Manager

Log Transfer Manager (LTM) is a separate program/process which reads
transaction log from the source server and transfers them to the replication
server for further processing. With ASE 11.5, this has become part of ASE and
is now called the Replication Agent. However, you still need to use an LTM for
non-ASE sources. I imagine there is a version of LTM for each kind of source
(DB2, Informix, Oracle etc). When replication is active, you see one
connection per each replicated database in the source dataserver (sp_who).

Replication Server (s)

The replication server is an Open Server/Open Client application. The server
part receives transactions being sent by either the source ASE or the source
LTM. The client part sends these transactions to the target server which could
be another replication server or the final dataserver. As far as I know, the
server does not include the client component of any of the other DBMSes out of
the box.

Replicate (target) Dataserver

Server in which the final replication server (in the queue) will repeat the
transaction done on the primary. You will see a connection, one for each target
database, in the target dataserver when the replication server is actively
transferring data (when idle, the replication server disconnects or fades out
in replication terminology).

Back to top

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

1.3 What is the Difference Between Replication Server and SQL Remote?

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

Both SQL Remote and Replication Server perform replication. SQL Remote was
originally part of the Adaptive Server Anywhere tool kit and is intended for
intermittent replication. (The classic example is that of a salesman
connecting on a daily basis to upload sales and download new prices and
inventory.) Replication Server is intended for near real-time replication
scenarios.

Back to top

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

next prev ASE FAQ

Replication Server Administration



2.1 How can I improve throughput?
2.2 Where should I install replication server?
2.3 Using large raw partitions with Replication Server on Unix.
2.4 How to replicate col = col + 1
2.5 What is the difference between an LTMs an a RepAgent?
2.6 Which Should I choose, RepAgent or LTM?

next prev ASE FAQ

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

2.1 How can I improve throughput?

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

Check the Obvious

First, ensure that you are only replicating those parts of the system that need
to be replicated. Some of this is obvious. Don't replicate any table that
does not need to be replicated. Check that you are only replicating the
columns you need. Replication is very sophisticated and will allow you to
replicate both a subset of the columns as well as a subset of the rows.

Replicate Minimum Columns

Once the replication is set up and synchronised, it is only necessary to
replicate those parts of the primary system that actually change. You are only
replicating those rows and columns that need to be replicated, but you only
need to replicate the actual changes. Check that each replication definition
is defined using the clause:

create replication definition rep_def_name
with primary...
....
replicate minimal columns

Second Replication Server

This might be appropriate in a simple environment on systems with spare cycles
and limited space on the network. When Sybase replicates from a primary to a
replicate using only one replication server the data is transferred across the
network uncompressed. However, the communication between two replication
servers is compressed. By installing a second replication server it is
possible to dramatically reduce the bandwidth needed to replicate your data.

Dedicated Network Card

Obviously, if replication is sharing the same network resources that all of the
clients are using, there is the possibility for a bottleneck if the network
bandwidth is close to saturation. If a second replication server is not going
to cut it since you already have one or there are no spare cycles, then a
second network card may be the answer.

First, you will need to configure ASE to listen on two network connections.
This is relatively straightforward. There is no change to the client
configuration. They all continue to talk to Sybase using the same connection.
When defining the replication server, ensure that the interfaces/sql.ini entry
that it uses only has the second connection in it. This may involve some
jiggery pokery with environment variables, but should be possible, even on NT!
You need to be a little careful with network configuration. Sybase will
communicate with the two servers on the correct address, but if the underlying
operating system believes that both clients and repserver can be serviced by
the same card, then it will use the first card that it comes to. So, if you
had the situation that all of the clients, ASE and the replication server were
on 192.168.1.0, and the host running ASE had two cards onto this same segment,
then it would choose to route all packets through the first card. OK, so this
is a very simplistic error to correct, but similar things can happen with more
convoluted and, superficially, better thought out configurations.

+---------+ +-----------+ +-----------+
| |--> NE(1) --> All Clients... | | | |
| Primary | | repserver | | replicate |
| |--> NE(2) --------------------->| |-->| |
| | | | | |
+---------+ +-----------+ +-----------+

So, configure NE(1) to be on 192.168.1.0, say, and NE(2) to be on 192.168.2.0
and all should be well. OK, so my character art is not perfect, but I think
that you get the gist!

No Network Card

If RepServer resides on the same physical machine as either the primary or the
replicate, it is possible to use the localhost or loopback network device. The
loopback device is a network interface that connects back to itself without
going through the network interface card. It is almost always uses the IP
address 127.0.0.1. So, by applying the technique described above, but instead
of using a dedicated network card, you use the loopback device. Obviously, the
two servers have to be on the same physical machine or it won't work!

Back to top

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

2.2 Where should I install replication server?

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

A seemingly trivial question, but one that can cause novices a bit of worry.

There are three answers: on the primary machine, on the replicate machine or on
a completely separate machine. There is no right answer, and if you are doing
an initial install it probably pays to consider the future, consider the
proposed configuration and have a look at the load on the available machines.

It is probably fair to say that replication is not power hungry but neither is
it free. If the primary is only just about coping with its current load, then
it might be as well looking into hosting it on another machine. The argument
applies to the replicate. If you think that network bandwidth may be an issue,
and you may have to add a second replication server, you may be better off
starting with repserver running on the primary. It is marginally easier to add
a repserver to an existing configuration if the first repserver is on the
primary.

Remember that a production replication server on Unix will require raw devices
for the stable devices and that these can be more than 2GB in size. If you are
restricted in the number of raw partitions you have available on a particular
machine, then this may have a bearing. See Q2.3.

Installing replication server on its own machine will, of course, introduce all
sorts of problems of its own, as well as answering some. The load on the
primary or the replicate is reduced considerably, but you are definitely going
to add some load to the network. Remember that ASE->Rep and Rep->ASE is
uncompressed. It is only Rep->Rep that is compressed.

Back to top

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

2.3 Using large raw partitions with Replication Server on Unix.

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

It is a good practice with production installations of Replication Server on
Unix that you use raw partitions for the stable devices. This is for just the
same reason that production ASE's use raw partitions. Raw devices can be a
maximum of 2GB with replication server up to release 11.5. (I have not checked
12.)

In order to utilise a raw partition that is greater than 2GB in size you can do
the following (remember all of the cautionary warnings about trying this sort
of stuff out in development first!):

add partition firstpartition on '/dev/rdsk/c0t0d0s0' with size 2024
go
add partition secondpartition on '/dev/rdsk/c0t0d0s0' with size 2024
starting at 2048
go

Notice that the initial partition is sized at 2024MB and not 2048. I have not
found this in the documentation, but replication certainly seems to have a
problem allocating a full 2GB. Interestingly, do the same operation through
Rep Server Manager and Sybase central caused no problems at all.

Back to top

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

2.4 How to replicate col = col + 1

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

Firstly. While the rule that you never update a primary key may be a
philosophical choice in a non-replicated system, it is an architectural
requirement of a replicated system.

If you use simple data replication, and your primary table is:

id
---
1
2
3

and you issue a:

update table set id=id+1

Rep server will do this in the replicate:

begin tran
update table set id=2 where id=1
update table set id=3 where id=2
update table set id=4 where id=3
commit tran

Hands up all who can see a bit of a problem with this! Remember, repserver
doesn't replicate statements, it replicates the results of statements.

One way to perform this update is to build a stored procedure on both sides
that executes the necessary update and replicate the stored procedure call.

Back to top

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

2.5 What is the difference between an LTM and a RepAgent?

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

As described in Section 1.2, Log Transfer Managers (LTMs) and RepAgents are the
processes that transfer data between ASE and the Replication Server.

LTMs were delivered with the first releases of Replication Server. Each LTM is
a separate process at the operating system level that runs along side ASE and
Replication Server. As with ASE and Replication Server, a RUN_<ltm_server> and
configuration file is required for each LTM. One LTM is required for each
database being replicated.

Along with ASE 11.5 a new concept was introduced, that of RepAgent. I am not
sure if you needed to use RepServer 11.5 as well, or whether the RepAgents
could talk to earlier versions of Replication Server. Each RepAgent is, in
effect, a slot-in replacement for an LTM. However, instead of running as
separate operating system process, it runs as a thread within ASE. Pretty much
all of the requirements for replication using an LTM apply to the RepAgents.
One per database being replicated, etc. but now you do not need to have
separate configuration files.

Back to top

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

2.6 Which should I use, RepAgent or LTM?

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

The differences between RepAgents and LTMs are discussed in Section 2.5.
Which then to choose. There are pros and cons to both, however, I think that
it should be stated up front that RepAgents are the latest offering and I
believe that Sybase would expect you you to use that. Certainly the
documentation for LTMs is a little buried implying that they do not consider it
to be as current as LTMs.

LTM Cons:

* Older technology. Not sure if it is being actively supported.
* Not integrated within ASE, so there is a (small) performance penalty.
* Separate processes, so need additional monitoring in production
environments.

LTM Pros:

* Possible to restart LTM without having to restart ASE.

RepAgent Cons

* If it crashes it is possible that you will have to restart ASE in order to
restart RepAgent.

RepAgent Pros

* Latest, and presumably greatest, offering.
* Tightly integrated with ASE so good performance.
* Less to manage, no extra entries in the interfaces file.

Back to top

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

next prev ASE FAQ

Replication Server Trouble Shooting



3.1 Why am I running out of locks on the replicate side?
3.2 Someone was playing with replication and now the transaction log on
OLTP is filling.

next prev ASE FAQ

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

3.1 Why am I running out of locks on the replicate side?

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

Sybase replication works by taking each transaction that occurs in the primary
dataserver and applying to the replicate. Since replication works on the
transaction log, a single, atomic, update on the primary side that updates a
million rows will be translated into a million single row updates. This may
seem very strange but is a simple consequence of how it works. On the primary,
this million row update will attempt to escalate the locks that it has taken
out to an exclusive table lock. However, on the replicate side each row is
updated individually, much as if they were being updated within a cursor loop.
Now, Sybase only tries to escalate locks from a single atomic statement (see
ASE Qx.y), so it will never try to escalate the lock. However, since the
updates are taking place within a single transaction, Sybase will need to take
out enough page locks to lock the million rows.

So, how much should you increase the locks parameter on the replicate side? A
good rule of thumb might be double it or add 40,000 whichever is the larger.
This has certainly worked for us.

Back to top

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

3.2 Someone was playing with replication and now the transaction log on OLTP
is filling.

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

Once replication has been configured, ASE adds another marker to the
transaction log. The first marker is the conventional one that marks which
transactions have had their data written to disk. The second is there to
ensure that the transactions have also been replicated. Clearly, if someone
installed replication and did not clean up properly after themselves, this
marker will still be there and consequently the transaction log will be filling
up. If you are certain that replication is not being used on your system, you
can disable the secondary truncation marker with the following commands:

1> use <database>
2> go
1> dbcc settrunc(ltm, ignore)
2> go

The above code is the normal mechanism for disabling the trucation point. I
have never had a problem with it. However, an alternative mechanism for
disabling the truncation point is given below. I do not know if it will work
in situations that the previous example won't, or if it works for databases
that are damaged or what. If someone knows when you use it and why, please let
me know (mailto:dowen@midsomer.org).

1> sp_role "grant", sybase_ts_role, sa
2> go
1> set role sybase_ts_role on
2> go
1> dbcc dbrepair(dbname, ltmignore)
2> go
1> sp_role "revoke", sybase_ts_role, sa
2> go

This scenario is also very common if you load a copy of your replicated
production database into development.

Back to top

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

next prev ASE FAQ

Additional Information/Links



4.1 Links
4.2 Newsgroups

next prev ASE FAQ

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

4.1 Links

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

Thierry Antinolfi has a replication FAQ at his site http://pro.wanadoo.fr/
dbadevil that covers a lot of good stuff.

Rob Verschoor has a 'Replication Server Tips & Tricks' section on his site, as
well as an indispensible quick reference guide!

Back to top

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

4.2 Newsgroups

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

There are a number of newsgroups that can deal with questions. Sybase have
several in their own forums area.

For Replication Server:

sybase.public.rep-server
sybase.public.rep-agent

for SQL Remote and the issues of replicating with ASA:

sybase.public.sqlanywhere.replication

and of course, there is always the ubiquitous

comp.databases.sybase.

Back to top

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

next prev ASE FAQ

Archive-name: databases/sybase-faq/part4
URL: http://www.isug.com/Sybase_FAQ
Version: 1.7
Maintainer: David Owen
Last-modified: 2003/03/02
Posting-Frequency: posted every 3rd month
A how-to-find-the-FAQ article is posted on the intervening months.
Sybase Frequently Asked Questions


Sybase FAQ Home PageAdaptive Server Enterprise FAQAdaptive Server Anywhere FAQ
Repserver FAQSearch the FAQ
[bar]

Adaptive Server Enterprise



0. What's in a name?
1. ASE Administration
1.1 Basic Administration
1.2 User Database Administration
1.3 Advanced Administration
1.4 General Troubleshooting
1.5 Performance and Tuning
1.6 Server Monitoring
2. Platform Specific Issues
2.1 Solaris
2.2 NT
2.3 Linux
3. DBCC's
4. isql
5. bcp
6. SQL Development
6.1 SQL Fundamentals
6.2 SQL Advanced
6.3 Useful SQL Tricks
7. Open Client
9. Freeware
10. Sybase Technical News
11. Additional Information
12. Miscellany



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

What's in a name?

Throughout this FAQ you will find references to SQL Server and, starting with
this release, ASE or Adaptive Server Enterprise to give it its full name. You
might also be a little further confused, since Microsoft also seem to have a
product called SQL Server.

Well, back at about release 4.2 of Sybase SQL Server, the products were exactly
the same. Microsoft were to do the port to NT. Well, it is pretty well
documented, but there was a falling out. Both companies kept the same name for
their data servers and confusion began to reign. In an attempt to try and sort
this out, Sybase renamed their product Adaptive Server Enterprise (ASE)
starting with version 11.5.

I found this quote in a Sybase manual the other day:

Since changing the name of Sybase SQL Server to Adaptive Server Enterprise,
Sybase uses the names Adaptive Server and Adaptive Server Enterprise to refer
collectively to all supported versions of the Sybase SQL Server and Adaptive
Server Enterprise. Version-specific references to Adaptive Server or SQL Server
include version numbers.

I will endeavour to try and do the same within the FAQ, but the job is far from
complete!

Back to Top

Basic ASE Administration



1.1.1 What is SQL Server and ASE anyway?
1.1.2 How do I start/stop ASE when the CPU reboots?
1.1.3 How do I move tempdb off of the master device?
1.1.4 How do I correct timeslice -201?
1.1.5 The how's and why's on becoming Certified.
1.1.6 RAID and Sybase
1.1.7 How to swap a db device with another
1.1.8 Server naming and renaming
1.1.9 How do I interpret the tli strings in the interface file?
1.1.10 How can I tell the datetime my Server started?
1.1.11 Raw partitions or regular files?
1.1.12 Is Sybase Y2K (Y2000) compliant?
1.1.13 How can I run the ASE upgrade manually?
1.1.14 We have lost the sa password, what can we do?
1.1.15 How do I set a password to be null?
1.1.16 Does Sybase support Row Level Locking?
1.1.17 What platforms does ASE run on?
1.1.18 How do I backup databases > 64G on ASE prior to 12.x?

User Database Administration # ASE FAQ

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

1.1.1: What is SQL Server and ASE?

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

Overview

Before Sybase System 10 (as they call it) we had Sybase 4.x. Sybase System 10
has some significant improvements over Sybase 4.x product line. Namely:

* the ability to allocate more memory to the dataserver without degrading its
performance.
* the ability to have more than one database engine to take advantage of
multi-processor cpu machines.
* a minimally intrusive process to perform database and transaction dumps.

Background and More Terminology

A ASE (SQL Server) is simply a Unix process. It is also known as the database
engine. It has multiple threads to handle asynchronous I/O and other tasks. The
number of threads spawned is the number of engines (more on this in a second)
times five. This is the current implementation of Sybase System 10, 10.0.1 and
10.0.2 on IRIX 5.3.

Each ASE allocates the following resources from a host machine:

* memory and
* raw partition space.

Each ASE can have up to 255 databases. In most implementations the number of
databases is limited to what seems reasonable based on the load on the ASE.
That is, it would be impractical to house all of a large company's databases
under one ASE because the ASE (a Unix process) will become overloaded.

That's where the DBAs experience comes in with interrogation of the user
community to determine how much activity is going to result on a given database
or databases and from that we determine whether to create a new ASE or to house
the new database under an existing ASE. We do make mistakes (and businesses
grow) and have to move databases from one ASE to another. At times ASEs need to
move from one CPU server to another.

With Sybase System 10, each ASE can be configured to have more than one engine
(each engine is again a Unix process). There's one primary engine that is the
master engine and the rest of the engines are subordinates. They are assigned
tasks by the master.

Interprocess communication among all these engines is accomplished with shared
memory.

Some times when a DBA issues a Unix kill command to extinguish a maverick
ASE, the subordinate engines are forgotten. This leaves the shared memory
allocated and eventually we may get in to situations where swapping occurs
because this memory is locked. To find engines that belong to no master
ASE, simple look for engines owned by /etc/init (process id 1). These
engines can be killed -- this is just FYI and is a DBA duty.

Before presenting an example of a ASE, some other topics should be covered.

Connections

An ASE has connections to it. A connection can be viewed as a user login but
it's not necessarily so. That is, a client (a user) can spark up multiple
instances of their application and each client establishes its own connection
to the ASE. Some clients may require two or more per invocation. So typically
DBA's are only concerned with the number of connections because the number of
users typically does not provide sufficient information for us to do our job.

Connections take up ASE resources, namely memory, leaving less memory for
the ASEs' available cache.

ASE Buffer Cache

In Sybase 4.0.1 there was a limit to the amount of memory that could be
allocated to a ASE. It was around 80MB, with 40MB being the typical max. This
was due to internal implementations of Sybase's data structures.

With Sybase System 10 there really was no limit. For instance, we had an ASE
cranked up to 300MB under 10. With System 11 and 12 this has been further
extended. ASE's with 4G bytes of memory are not uncommon. I have not heard of
an 11.9.3 or a 12 server with more that 4G bytes, but I am sure that they are
not far away.

The memory in an ASE is primarily used to cache data pages from disk. Consider
that the ASE is a light weight Operating System: handling user (connections),
allocating memory to users, keeping track of which data pages need to be
flushed to disk and the sort. Very sophisticated and complex. Obviously if a
data page is found in memory it's much faster to retrieve than going out to
disk.

Each connection takes away a little bit from the available memory that is used
to cache disk pages. Upon startup, the ASE pre-allocates the memory that is
needed for each connection so it's not prudent to configure 500 connections
when only 300 are needed. We'd waste 200 connections and the memory associated
with that. On the other hand, it is also imprudent to under configure the
number of connections; users have a way of soaking up a resource (like an ASE)
and if users have all the connections a DBA cannot get into the server to
allocate more connections.

One of the neat things about an ASE is that it reaches (just like a Unix
process) a working set. That is, upon startup it'll do a lot of physical I/O's
to seed its cache, to get lookup information for typical transactions and the
like. So initially, the first users have heavy hits because their requests have
to be performed as a physical I/O. Subsequent transactions have less physical I
/O and more logical I/O's. Logical I/O is an I/O that is satisfied in the ASEs'
buffer cache. Obviously, this is the preferred condition.

DSS vs OLTP

We throw around terms like everyone is supposed to know this high tech lingo.
The problem is that they are two different animals that require a ASE to be
tuned accordingly for each.

Well, here's the low down.

DSS
Decision Support System
OLTP
Online Transaction Processing

What do these mean? OLTP applications are those that have very short orders of
work for each connection: fetch this row and with the results of it update one
or two other rows. Basically, small number of rows affected per transaction in
rapid sucession, with no significant wait times between operations in a
transaction.

DSS is the lumbering elephant in the database world (unless you do some
tricks... out of this scope). DSS requires a user to comb through gobs of data
to aggregate some values. So the transactions typically involve thousands of
rows. Big difference than OLTP.

We never want to have DSS and OLTP on the same ASE because the nature of OLTP
is to grab things quickly but the nature of DSS is to stick around for a long
time reading tons of information and summarizing the results.

What a DSS application does is flush out the ASE's data page cache because of
the tremendous amount of I/O's. This is obviously very bad for OTLP
applications because the small transactions are now hurt by this trauma. When
it was only OLTP a great percentage of I/O was logical (satisfied in the
cache); now transactions must perform physical I/O.

That's why it's good not to mix DSS and OLTP if at all possible.

If mixing them cannot be avoided, then you need to think carefully about how
you configure your server. Use named data caches to ensure that the very
different natures of OLTP and DSS do not conflict with each other. If you
tables that are shared, consider using dirty reads for the DSS applications if
at all possible, since this will help not to block the OLTP side.

Asynchronous I/O

Why async I/O? The idea is that in a typical online transaction processing
(OLTP) application, you have many connections (over 200 connections) and short
transactions: get this row, update that row. These transactions are typically
spread across different tables of the databases. The ASE can then perform each
one of these asynchronously without having to wait for others to finish. Hence
the importance of having async I/O fixed on our platform.

Engines

Sybase System 10 can have more than one engine (as stated above). Sybase has
trace flags to pin the engines to a given CPU processor but we typically don't
do this. It appears that the master engine goes to processor 0 and subsequent
subordinates to the next processor.

Currently, Sybase does not scale linearly. That is, five engines do not make
Sybase perform five times as fast however we do max out with four engines.
After that performance starts to degrade. This is supposed to be fixed with
Sybase System 11.

Putting Everything Together

As previously mentioned, an ASE is a collection of databases with connections
(that are the users) to apply and retrieve information to and from these
containers of information (databases).

The ASE is built and its master device is typically built over a medium sized
(50MB) raw partition. The tempdb is built over a cooked (regular - as opposed
to a raw device) file system to realize any performance gains by buffered
writes. The databases themselves are built over the raw logical devices to
ensure their integrity. (Note: in System 12 you can use the dsync flag to
ensure that writes to file system devices are secure.

Physical and Logical Devices

Sybase likes to live in its own little world. This shields the DBA from the
outside world known as Unix, VMS or NT. However, it needs to have a conduit to
the outside world and this is accomplished via devices.

All physical devices are mapped to logical devices. That is, given a physical
device (such as /lv1/dumps/tempdb_01.efs or /dev/rdsk/dks1ds0) it is mapped by
the DBA to a logical device. Depending on the type of the device, it is
allocated, by the DBA, to the appropriate place (vague enough?).

Okay, let's try and clear this up...

Dump Device

The DBA may decide to create a device for dumping the database nightly. The DBA
needs to create a dump device.

We'll call that logically in the database datadump_for_my_db but we'll map it
to the physical world as /lv1/dumps/in_your_eye.dat So the DBA will write a
script that connects to the ASE and issues a command like this:

dump database my_stinking_db to datadump_for_my_db
go

and the backupserver (out of this scope) takes the contents of my_stinking_db
and writes it out to the disk file /lv1/dumps/in_your_eye.dat

That's a dump device. The thing is that it's not preallocated. This special
device is simply a window to the operating system.

Data and Log Devices

Ah, now we are getting into the world of pre-allocation. Databases are built
over raw partitions. The reason for this is because Sybase needs to be
guaranteed that all its writes complete successfully. Otherwise, if it posted
to a file system buffer (as in a cooked file system) and the machine crashed,
as far as Sybase is concerned the write was committed. It was not, however, and
integrity of the database was lost. That is why Sybase needs raw partitions.
But back to the matter at hand...

When building a new ASE, the DBA determines how much space they'll need for all
the databases that will be housed in this ASE.

Each production database is composed of data and log.

The data is where the actual information resides. The log is where the changes
are kept. That is, every row that is updated/deleted/inserted gets placed into
the log portion then applied to the data portion of the database.

That's why DBA strives to place the raw devices for logs on separate disks
because everything has to single thread through the log.

A transaction is a collection of SQL statements (insert/delete/update) that are
grouped together to form a single unit of work. Typically they map very closely
to the business.

I'll quote the Sybase ASE Administration Guide on the role of the log:

The transaction log is a write-ahead log. When a user issues a statement
that would modify the database, ASE automatically writes the changes to the
log. After all changes for a statement have been recorded in the log, they
are written to an in-cache copy of the data page. The data page remains in
cache until the memory is needed for another database page. At that time,
it is written to disk. If any statement in a transaction fails to complete,
ASE reverses all changes made by the transaction. ASE writes an "end
transaction" record to the log at the end of each transaction, recording
the status (success or failure) of the transaction

As such, the log will grow as user connections affect changes to the database.
The need arises to then clear out the log of all transactions that have been
flushed to disk. This is performed by issuing the following command:

dump transaction my_stinking_db to logdump_for_my_db
go

The ASE will write to the dumpdevice all transactions that have been committed
to disk and will delete the entries from its copy, thus freeing up space in the
log. Dumping of the transaction logs is accomplished via cron (the Unix
scheduler, NT users would have to resort to at or some third party tool) . We
schedule the heavily hit databases every 20 minutes during peak times.

A single user can fill up the log by having begin transaction with no
corresponding commit/rollback transaction. This is because all their
changes are being applied to the log as an open-ended transaction, which is
never closed. This open-ended transaction cannot be flushed from the log,
and therefore grows until it occupies all of the free space on the log
device.

And the way we dump it is with a dump device. :-)

An Example

If the DBA has four databases to plop on this ASE and they need a total of
800MB of data and 100MB of log (because that's what really matters to us), then
they'd probably do something like this:

1. allocate sufficient raw devices to cover the data portion of all the
databases
2. allocate sufficient raw devices to cover the log portion of all the
databases
3. start allocating the databases to the devices.

For example, assuming the following database requirements:

Database
Requirements
+-----------------+
| | | |
|----+------+-----|
| DB | Data | Log |
|----+------+-----|
|----+------+-----|
| a | 300 | 30 |
|----+------+-----|
| b | 400 | 40 |
|----+------+-----|
| c | 100 | 10 |
+-----------------+

and the following devices:

Devices
+---------------------------------+
| Logical | Physical | Size |
|---------------+----------+------|
| | /dev/ | |
| dks3d1s2_data | rdsk/ | 500 |
| | dks3d1s2 | |
|---------------+----------+------|
| | /dev/ | |
| dks4d1s2_data | rdsk/ | 500 |
| | dks4d1s2 | |
|---------------+----------+------|
| | /dev/ | |
| dks5d1s0_log | rdsk/ | 200 |
| | dks5d1s0 | |
+---------------------------------+

then the DBA may elect to create the databases as follows:

create database a on dks3d1s2_data = 300 log on dks5d1s0_log = 30
create database b on dks4d1s2_data = 400 log on dks5d1s0_log = 40
create database c on dks3d1s2_data = 50, dks4d1s2_data = 50 log on dks5d1s0_log = 10

Some of the devices will have extra space available because out database
allocations didn't use up all the space. That's fine because it can be used for
future growth. While the Sybase ASE is running, no other Sybase ASE can
re-allocate these physical devices.

TempDB

TempDB is simply a scratch pad database. It gets recreated when a SQL Server is
rebooted. The information held in this database is temporary data. A query may
build a temporary table to assist it; the Sybase optimizer may decide to create
a temporary table to assist itself.

Since this is an area of constant activity we create this database over a
cooked file system which has historically proven to have better performance
than raw - due to the buffered writes provided by the Operating System.

Port Numbers

When creating a new ASE, we allocate a port to it (currently, DBA reserves
ports 1500 through 1899 for its use). We then map a host name to the different
ports: hera, fddi-hera and so forth. We can actually have more than one port
number for an ASE but we typically don't do this.

Back to top

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

1.1.2: How to start/stop ASE when CPU reboots

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

Below is an example of the various files (on Irix) that are needed to start/
stop an ASE. The information can easily be extended to any UNIX platform.

The idea is to allow as much flexibility to the two classes of administrators
who manage the machine:

* The System Administrator
* The Database Administrator

Any errors introduced by the DBA will not interfere with the System
Administrator's job.

With that in mind we have the system startup/shutdown file /etc/init.d/sybase
invoking a script defined by the DBA: /usr/sybase/sys.config/
{start,stop}.sybase

/etc/init.d/sybase

On some operating systems this file must be linked to a corresponding entry in
/etc/rc.0 and /etc/rc.2 -- see rc0(1M) and rc2(1M)

#!/bin/sh
# last modified: 10/17/95, sr.
#
# Make symbolic links so this file will be called during system stop/start.
# ln -s /etc/init.d/sybase /etc/rc0.d/K19sybase
# ln -s /etc/init.d/sybase /etc/rc2.d/S99sybase
# chkconfig -f sybase on

# Sybase System-wide configuration files
CONFIG=/usr/sybase/sys.config

if $IS_ON verbose ; then # For a verbose startup and shutdown
ECHO=echo
VERBOSE=-v
else # For a quiet startup and shutdown
ECHO=:
VERBOSE=
fi

case "$1" in
'start')
if $IS_ON sybase; then
if [ -x $CONFIG/start.sybase ]; then
$ECHO "starting Sybase servers"
/bin/su - sybase -c "$CONFIG/start.sybase $VERBOSE &"
else
<error condition>
fi
fi
;;

'stop')
if $IS_ON sybase; then
if [ -x $CONFIG/stop.sybase ]; then
$ECHO "stopping Sybase servers"
/bin/su - sybase -c "$CONFIG/stop.sybase $VERBOSE &"
else
<error condition>
fi
fi
;;

*)
echo "usage: $0 {start|stop}"
;;
esac

/usr/sybase/sys.config/{start,stop}.sybase

start.sybase

#!/bin/sh -a
#
# Script to start sybase
#
# NOTE: different versions of sybase exist under /usr/sybase/{version}
#
# Determine if we need to spew our output
if [ "$1" != "spew" ] ; then
OUTPUT=">/dev/null 2>&1"
else
OUTPUT=""
fi
# 10.0.2 servers
HOME=/usr/sybase/10.0.2
cd $HOME
# Start the backup server
eval install/startserver -f install/RUN_BU_KEPLER_1002_52_01 $OUTPUT
# Start the dataservers
# Wait two seconds between starts to minimize trauma to CPU server
eval install/startserver -f install/RUN_FAC_WWOPR $OUTPUT
sleep 2
eval install/startserver -f install/RUN_MAG_LOAD $OUTPUT
exit 0

stop.sybase

#!/bin/sh
#
# Script to stop sybase
#
# Determine if we need to spew our output
if [ -z "$1" ] ; then
OUTPUT=">/dev/null 2>&1"
else
OUTPUT="-v"
fi
eval killall -15 $OUTPUT dataserver backupserver sybmultbuf
sleep 2
# if they didn't die, kill 'em now...
eval killall -9 $OUTPUT dataserver backupserver sybmultbuf
exit 0

If your platform doesn't support killall, it can easily be simulated as
follows:

#!/bin/sh
#
# Simple killall simulation...
# $1 = signal
# $2 = process_name
#
#
# no error checking but assume first parameter is signal...
# what ya want for free? :-)
#
kill -$1 `ps -ef | fgrep $2 | fgrep -v fgrep | awk '{ print $1 }'`

Back to top

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

1.1.3: How do I move tempdb off of the Master Device?

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

There used to be a section in the FAQ describing how to drop all of tempdb's
devices physically from the master device. This can make recovery of the
server impossible in case of a serious error and so it strongly recommended
that you do not do this but simply drop the segments as outlined below.

Sybase TS Preferred Method of Moving tempdb off the Master Device.

This is the Sybase TS method of removing most activity from the master device:

1. Alter tempdb on another device:
1> alter database tempdb on ...
2> go
2. Use the tempdb:
1> use tempdb
2> go
3. Drop the segments:
1> sp_dropsegment "default", tempdb, master
2> go
1> sp_dropsegment "logsegment", tempdb, master
2> go
1> sp_dropsegment "system", tempdb, master
2> go

Note that there is still some activity on the master device. On a three
connection test that I ran:

while ( 1 = 1 )
begin
create table #x (col_a int)
drop table #x
end

there was one write per second. Not bad.

An Alternative

(I recently did some bench marks comparing this method, the previous method
and a combination of both. According to sp_sysmon there was no difference
in activity at all. I leave it here just in case it proves useful to
someone.)

The idea of this handy script is to simply fill the first 2MB of tempdb thus
effectively blocking anyone else from using it. The slight gotcha with this
script, since we're using model, is that all subsequent database creates will
also have tempdb_filler installed. This is easily remedied by dropping the
table after creating a new database.

This script works because tempdb is rebuilt every time the ASE is rebooted.
Very nice trick!

/* this isql script creates a table in the model database. */
/* Since tempdb is created from the model database when the */
/* server is started, this effectively moves the active */
/* portion of tempdb off of the master device. */

use model
go

/* note: 2k row size */
create table tempdb_filler(
a char(255) not null,
b char(255) not null,
c char(255) not null,
d char(255) not null,
e char(255) not null
)
go

/* insert 1024 rows */
declare @i int
select @i = 1
while (@i <= 1024)
begin
insert into tempdb_filler values('a','b','c','d','e')
if (@i % 100 = 0) /* dump the transaction every 100 rows */
dump tran model with truncate_only
select @i=@i+1
end
go

Back to top

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

1.1.4: How do I correct timeslice -201

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

(Note, this procedure is only really necessary with pre-11.x systems. In
system 11 systems, these parameters are tunable using sp_configure.)

Why Increase It?

Basically, it will allow a task to be scheduled onto the CPU for a longer time.
Each task on the system is scheduled onto the CPU for a fixed period of time,
called the timeslice, during which it does some work, which is resumed when its
next turn comes around.

The process has up until the value of ctimemax (a config block variable) to
finish its task. As the task is working away, the scheduler counts down
ctimemax units. When it gets to the value of ctimemax - 1, if it gets stuck and
for some reason cannot be taken off the CPU, then a timeslice error gets
generated and the process gets infected.

On the other hand, ASE will allow a server process to run as long as it needs
to. It will not swap the process out for another process to run. The process
will decide when it is "done" with the server CPU. If, however, a process goes
on and on and never relinquishes the server CPU, then Server will timeslice the
process.

Potential Fix

1. Shutdown the ASE
2. %buildmaster -dyour_device -yctimemax=2000
3. Restart your ASE. If the problem persists contact Sybase Technical Support
notifying them what you have done already.

Back to top

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

1.1.5: Certified Sybase Professional

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

There have been changes in the process of becoming a Sybase Certified
Professional. There's a very informative link at http://www.sybase.com/
education/profcert, Professional Certification.

Rob Verschoor has put together some good stuff on his pages ( http://
www.euronet.nl/~syp_rob/certtips.html) that have pretty much all that you need
to know. He also has a quiz which is intended to test each and everyone's
knowledge of ASE and RepServer.

Sybase have released some sample questions (look for them at http://
www.sybase.com/education/). The GUI requires MS Windows (at the time of
writing), but they are definitely a sample of what you will be asked. There are
also a couple of CDs available with yet more questions on them.

The Certification Kickback

There have been a couple of articles recently covering the kickback that seems
to be happening as far as certification is concerned. Serveral HR people have
said that if a person's CV (resume) is sent in covered in certifications then
it goes straight into the bit bucket. I do not know if this is true or not, but
one thing that you might wish to consider is the preparation of two CVs, one
with certifications, one without. If the job request specifies certification is
necessary, then send in the appropriate CV. If it does not specifiy
certification, send in the clean version. If you go into the interview for a
job that did not specify certifications up front and the interviewer starts
going about you not being certificated, you simply produce your card as proof.

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



1.1.6: RAID and Sybase

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

Here's a short summary of what you need to know about Sybase and RAID.

The newsgroup comp.arch.storage has a detailed FAQ on RAID, but here are a few
definitions:

RAID

RAID means several things at once. It provides increased performance through
disk striping, and/or resistance to hardware failure through either mirroring
(fast) or parity (slower but cheaper).

RAID 0

RAID 0 is just striping. It allows you to read and write quickly, but provides
no protection against failure.

RAID 1

RAID 1 is just mirroring. It protects you against failure, and generally reads
and writes as fast as a normal disk. It uses twice as many disks as normal (and
sends twice as much data across your SCSI bus, but most machines have plenty of
extra capacity on their SCSI busses.)

Sybase mirroring always reads from the primary copy, so it does not
increase read performance.

RAID 0+1

RAID 0+1 (also called RAID 10) is striping and mirroring together. This gives
you the highest read and write performance of any of the raid options, but uses
twice as many disks as normal.

RAID 4/RAID 5

RAID 4 and 5 have disk striping and use 1 extra disk to provide parity. Various
vendors have various optimizations, but this RAID level is generally much
slower at writes than any other kind of RAID.

RAID 7

I am not sure if this is a genuine RAID standard, further checking on your part
is required.

Details

Most hardware RAID controllers also provide a battery-backed RAM cache for
writing. This is very useful, because it allows the disk to claim that the
write succeeded before it has done anything. If there is a power failure, the
information will (hopefully) be written to disk when the power is restored. The
cache is very important because database log writes cause the process doing the
writes to stop until the write is successful. Systems with write caching thus
complete transactions much more quickly than systems without.

What RAID levels should my data, log, etc be on? Well, the log disk is
frequently written, so it should not be on RAID 4 or 5. If your data is
infrequently written, you could use RAID 4 or 5 for it, because you don't mind
that writes are slow. If your data is frequently written, you should use RAID
0+1 for it. Striping your data is a very effective way of avoiding any one disk
becoming a hot-spot. Traditionally Sybase databases were divided among devices
by a human attempting to determine where the hot-spots are. Striping does this
in a straight-forward fashion, and also continues to work if your data access
patterns change.

Your tempdb is data but it is frequently written, so it should not be on RAID 4
or 5.

If your RAID controller does not allow you to create several different kinds of
RAID volumes on it, then your only hope is to create a huge RAID 0+1 set. If
your RAID controller does not support RAID 0+1, you shouldn't be using it for
database work.

Back to top

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

1.1.7: How to swap a db device with another

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

Here are four approaches. Before attempting any of the following: Backup,
Backup, Backup.

Dump and Restore

1. Backup the databases on the device, drop the databases, drop the devices.
2. Rebuild the new devices.
3. Rebuild the databases (Make sure you recreate the fragments correctly - See
Ed Barlow's scripts (http://www.tiac.net/users/sqltech/) for an sp that
helps you do this if you've lost your notes. Failure to do this will
possibly lead to data on log segments and log on data segments).
4. Reload the database dumps!

Twiddle the Data Dictionary - for brave experts only.

1. Shut down the server.
2. Do a physical dump (using dd(1), or such utility) of the device to be
moved.
3. Load the dump to the new device
4. Edit the data dictionary (sysdevices.physname) to point to the new device.

The Mirror Trick

1. Create a mirror of the old device, on the new device.
2. Unmirror the primary device, thereby making the _backup_ the primary
device.
3. Repeat this for all devices until the old disk is free.

dd (Unix only)

(This option is no use if you need to move a device now, rather if you
anticipate moving a device at some point in the future.)

You may want to use this approach for creating any database.

Create (or use) a directory for symbolic links to the devices you wish to use.
Then create your database, but instead of going to /dev/device, go to /
directory/symlink - When it comes time to move your devices, you shut down the
server, simply dd(1) the data from the old device to the new device, recreate
the symbolic links to the new device and restart the ASE. Simple as that.

Backups are a requisite in all cases, just in case.

Back to top

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

1.1.8: Server naming and renaming

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

There are three totally separate places where ASE names reside, causing much
confusion.

ASE Host Machine interfaces File

A master entry in here for server TEST will provide the network information
that the server is expected to listen on. The -S parameter to the dataserver
executable tells the server which entry to look for, so in the RUN_TEST file,
-STEST will tell the dataserver to look for the entry under TEST in the
interfaces file and listen on any network parameters specified by 'master'
entries.

TEST
master tcp ether hpsrv1 1200
query tcp ether hpsrv1 1200


Note that preceding the master/query entries there's a tab.

This is as far as the name TEST is used. Without further configuration the
server does not know its name is TEST, nor do any client applications.
Typically there will also be query entries under TEST in the local interfaces
file, and client programs running on the same machine as the server will pick
this connection information up. However, there is nothing to stop the query
entry being duplicated under another name entirely in the same interfaces file.

ARTHUR
query tcp ether hpsrv1 1200

isql -STEST or isql -SARTHUR will connect to the same server. The name is
simply a search parameter into the interfaces file.

Client Machine interfaces File

Again, as the server name specified to the client is simply a search parameter
for Open Client into the interfaces file, SQL.INI or WIN.INI the name is
largely irrelevant. It is often set to something that means something to the
users, especially where they might have a choice of servers to connect to. Also
multiple query entries can be set to point to the same server, possibly using
different network protocols. eg. if TEST has the following master entries on
the host machine:

TEST
master tli spx /dev/nspx/ \xC12082580000000000012110
master tcp ether hpsrv1 1200

Then the client can have a meaningful name:

ACCOUNTS_TEST_SERVER
query tcp ether hpsrv1 1200

or alternative protocols:

TEST_IP
query tcp ether hpsrv1 1200
TEST_SPX
query tli spx /dev/nspx/ \xC12082580000000000012110

sysservers

This system table holds information about remote ASEs that you might want to
connect to, and also provides a method of naming the local server.

Entries are added using the sp_addserver system procedure - add a remote server
with this format:

sp_addserver server_name, null, network_name

server_name is any name you wish to refer to a remote server by, but
network_name must be the name of the remote server as referenced in the
interfaces file local to your local server. It normally makes sense to make the
server_name the same as the network_name, but you can easily do:

sp_addserver LIVE, null, ACCTS_LIVE

When you execute for example, exec LIVE.master..sp_helpdb the local ASE will
translate LIVE to ACCTS_LIVE and try and talk to ACCTS_LIVE via the ACCTS_LIVE
entry in the local interfaces file.

Finally, a variation on the sp_addserver command:

sp_addserver LOCALSRVNAME, local

names the local server (after a restart). This is the name the server reports
in the errorlog at startup, the value returned by @@SERVERNAME, and the value
placed in Open Client server messages. It can be completely different from the
names in RUN_SRVNAME or in local or remote interfaces - it has no bearing on
connectivity matters.

Back to top

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

1.1.9: How do I interpret the tli strings in the interface file?

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

The tli string contained with Solaris interface files is a hex string
containing port and IP address. If you have an entry

SYBSRVR
master tli tcp /dev/tcp \x000204018196c4510000000000000000

Then it can be interpreted as follows:

x0002 no user interpretation (header info?)
0401 port number (1025 decimal)
81 first part of IP address (129 decimal)
96 second part of IP address (150 decimal)
c4 third part of IP address (196 decimal)
51 fourth part of IP address (81 decimal)

So, the above tli address is equivalent to

SYBSRVR
master tcp ether sybhost 1025

where sybhost's IP address is 129.150.196.81.

The following piece of Sybperl (courtesy of Michael Peppler) takes a tli entry
and returns the IP address and port number for each server in a Solaris'
interfaces file.

#!/usr/local/bin/perl -w

use strict;

my $server;
my @dat;
my ($port, $ip);

while(<>) {
next if /^\s*$/;
next if /^\s*\#/;
chomp;
if(/^\w/) {
$server = $_;
$server =~ s/\s*$//;
next;
}

@dat = split(' ', $_);
($port, $ip) = parseAddress($dat[4]);
print "$server - $dat[0] on port $port, host $ip\n";
}

sub parseAddress {
my $addr = shift;

my $port;
my $ip;

my (@arr) = (hex(substr($addr, 10, 2)),

hex(substr($addr, 12, 2)),

hex(substr($addr, 14, 2)),

hex(substr($addr, 16, 2)));
$port = hex(substr($addr, 6, 4));
$ip = join('.', @arr);

($port, $ip);
}

Back to top

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

1.1.10: How can I tell the datetime my Server started?

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

Method #1

The normal way would be to look at the errorlog, but this is not always
convenient or even possible. From a SQL session you find out the server startup
time to within a few seconds using:

select "Server Start Time" = crdate
from master..sysdatabases
where name = "tempdb"

Method #2

Another useful query is:

select * from sysengines

which gives the address and port number at which the server is listening.

Back to top

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

1.1.11: Raw partitions or regular files?

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

Hmmm... as always, this answer depends on the vendor's implementation on a
cooked file system for the ASE...

Performance Hit (synchronous vs asynchronous)

If on this platform, the ASE performs file system I/O synchronously then the
ASE is blocked on the read/write and throughput is decreased tremendously.

The way the ASE typically works is that it will issue an I/O (read/write) and
save the I/O control block and continue to do other work (on behalf of other
connections). It'll periodically poll the workq's (network, I/O) and resume
connections when their work has completed (I/O completed, network data
xmit'd...).

Performance Hit (bcopy issue)

Assuming that the file system I/O is asynchronous (this can be done on SGI), a
performance hit may be realized when bcopy'ing the data from kernel space to
user space.

Cooked I/O typically (again, SGI has something called directed I/O which allows
I/O to go directly to user space) has to go from disk, to kernel buffers and
from kernel buffers to user space; on a read. The extra layer with the kernel
buffers is inherently slow. The data is moved from kernel buffers to/from user
space using bcopy(). On small operations this typically isn't that much of an
issue but in a RDBMS scenario the bcopy() layer is a significant performance
hit because it's done so often...

Performance Gain!

It's true, using file systems, at times you can get performance gains assuming
that the ASE on your platform does the I/O asynchronously (although there's a
caveat on this too... I'll cover that later on).

If your machine has sufficient memory and extra CPU capacity, you can realize
some gains by having writes return immediately because they're posted to
memory. Reads will gain from the anticipatory fetch algorithm employed by most
O/S's.

You'll need extra memory to house the kernel buffered data and you'll need
extra CPU capacity to allow bdflush() to write the dirty data out to disk...
eventually... but with everything there's a cost: extra memory and free CPU
cycles.

One argument is that instead of giving the O/S the extra memory (by leaving it
free) to give it to the ASE and let it do its caching... but that's a different
thread...

Data Integrity and Cooked File System

If the Sybase ASE is not ce