Sybase DC35823-01-1500-04 User Manual

Configuration Guide

Adaptive Server® Enterprise

15.0

[ UNIX ]

DOCUMENT ID: DC35823-01-1500-04
LAST REVISED: March 2007

Copyright © 1987-2007 by Sybase, Inc. All rights reserved.
This publication pertains to Sybase software and to any subsequent release until otherwise indicated in new editions or technical notes.

Information in this document is subject to change without notice. The software described herein is furnished under a license agreement,
and it may be used or copied only in accordance with the terms of that agreement.

To order additional documents, U.S. and Canadian customers should call Customer Fulfillment at [phone], fax [phone].
Customers in other countries with a U.S. license agreement ma y co ntac t Customer Fulfillment via the above fax number. All other

international customers should contact their S ybase subsidiary or local distributor. Upgrades are provided only at regularly scheduled
software release dates. No part of this publication may be reproduced, transmitted, or translated in any form or by any means, electronic,
mechanical, manual, optical, or otherwise, without the prior written permission of Sybase, Inc.

Sybase, SYBASE (logo), ADA Workbench, Adaptable Windowing Environment, Adaptive Component Architecture, Adaptive Server, Adaptive Server
Anywhere, Adaptive Server Ente rprise, Ada ptive Ser ver Enter prise Monitor , Adaptive Ser ver Enterprise Replica tion, Adaptive Server Everywhere, Advantage
Database Server, Afaria, Answers Anywhere, Applied Meta, Applied Metacomputing, AppModeler, APT Workbench, APT-Build, APT- Edit, APT-Execute,
APT-Translator, APT-Library, ASEP, Avaki, Avaki (Arrow Design), Avaki Data Grid, AvantGo, Backup Server, BayCam, Beyond Connected, Bit-Wise,
BizTracker, Certified PowerBuilder Developer, Certified SYBASE Professional, Certified SYBASE Professional Logo, ClearConnect, Client-Library, Client
Services, CodeBank, Column Design, ComponentPack, Connection Manager, Convoy/DM, Copernicus, CSP, Data Pipeline, Data Workbench, DataArchitect,
Database Analyzer, DataExpress, DataServer, DataWindow, DataWindow .NET, DB-Library, dbQueue, Dejima, Dejima Direct, Developers Workbench,
DirectConnect Anywhere, DirectConnect, Distribution Director, Dynamic Mobility Model, e-ADK, E-Anywhere, e-Biz Integrator, E-Whatever, EC Gateway ,
ECMAP, ECRTP, eFulfillment Accelerator, EII Plus , Electronic Case Management, Embedded SQL, EMS, Enterpris e Application S tudio, Enter pris e Client/
Server, Enterprise Connect, Enterprise Data Studio, Enterprise Manager, Enterprise Portal (logo), Enterprise SQL Server Manager, Enterprise Work
Architecture, Enterprise Work Designer, Enterprise Work Modeler, eProcurement Accelerator, eremote, Everything Works Better When Everything W orks
Together, EWA, ExtendAssist, Extended Systems, ExtendV iew, Financial Fusion, Financial Fusion (and design), Financial Fusion Server, Formula One, Fusion
Powered e-Finance, Fusion Powered Financial Destinations, Fusion Powered STP, Gateway Manager, GeoPoint, GlobalFIX, iAnywhere, iAnywhere Solutions,
ImpactNow, Industry W a rehouse Studi o, InfoMaker, Information Anywhere, Information Everywhere, InformationConnect, InstaHelp, Intelligent Self-Care,
InternetBuilder, iremote, irLite, iScript, Jaguar CTS, jConnect for JDBC, KnowledgeBase, Legion, Logical Memory Manager, M2M Anywhere, Mach
Desktop, Mail Anywhere Studio, Mainframe Connect, Maintenance Express, Manage Anywhere Studio, MAP, M-Business Anywhere, M-Business Channel,
M-Business Network, M-Business Suite, MDI Access Server, MDI Database Gateway, media.splash, Message Anywhere Server, MetaWorks, MethodSet,
mFolio, Mirror Activator, ML Query, MobiCATS, MobileQ, MySupport, Net-Gateway , Net-Library, New Era of Networks, Next Generation Learning, Next
Generation Learning Studio, O DEVICE, OASiS, OASiS logo, ObjectConnect, ObjectCy cle, OmniConnect, OmniQ, OmniSQL Access Module, OmniSQL
Toolkit, OneBridge, Open Biz, Open Business Interchange, Open Client, Open ClientConnect, Open Client/Server, Open Client/Server Interfaces, Open
Gateway, Open Server, Open ServerConnect, Open Solutions, Optima++, Partnerships that Work, PB-Gen, PC APT Execute, PC DB-Net, PC Net Library,
Pharma Anywhere, PhysicalArchitect, Pocket PowerBuilder, PocketBuilder, Power++, Power Through Knowledge, power.stop, PowerAMC, PowerBuilder,
PowerBuilder Foundation Class Library, PowerDesigner, PowerDimensions, PowerDynamo, Powering the New Economy, PowerScript, PowerSite,
PowerSocket, Powersoft, PowerStage, PowerStudio, PowerTips, Powersoft Portfolio, Powersoft Professional, PowerWare Desktop, PowerWare Enterprise,
ProcessAnalyst, Pylon, Pylon Anywhere, Pylon Application Server, Pylon Conduit, Pylon PIM Server, Pylon Pro, QAnywhere, Rapport, Relational Beans,
RemoteWare, RepConnector , Report Workbench, Report-Execute, Replication Agent, Replication Driver, Replication Server, Replication Server Manager,
Replication Toolkit, Resource Manager, RFID Anywhere, R W-DisplayLib, RW -Librar y, SAFE, SAFE/PRO, Sales Anywhere, Search Anywhere, SDF, Search
Anywhere, Secure SQL Server, Secure SQL Toolset, Security Guardian, ShareSpool, ShareLink, SKILS, smart.partners, smart.parts, smart.script, SOA
Anywhere Trademark,SQL Advantage, SQL Anywhere, SQL Anywhere Studio, SQL Code Checker, SQL Debug, SQL Edit, SQL Edit/TPU, SQL Everywhere,
SQL Modeler, SQL Remote, SQL Server, SQL Server Manager, SQL SMART, SQL Toolset, SQL Server/CFT, SQL Server/DBM, SQL Server SNMP
SubAgent, SQL Station, SQLJ, Stage III Engineering, Startup.Com, STEP, SupportNow, S.W.I.F.T. Message Format Libraries, Sybase Central, Sybase Client/
Server Interfaces, Sybase Development Framework, Sybase Financial Server, Sybase Gateways, Sybase IQ, Sybase Learning Connection, Sybase MPP , Sybase
SQL Desktop, Sybase SQL Lifecycle, Sybase SQL Workgroup, Sybase Synergy Program, Sybase Virtual Server Architecture, Sybase User Workbench,
SybaseWare, Syber Financial, SyberAssist, SybFlex, SybMD, SyBooks, System 10, System 11, System XI (logo), SystemTools, T abular Data Stream, The
Enterprise Client/Server Company, The Extensible Software Platform, The Future Is Wide Open, The Learning Connection, The Model For Client/Server
Solutions, The Online Information Center, The Power of One, TotalFix, TradeForce, Transact-SQL, Translation Toolkit, Turning Imagination Into Reality,
UltraLite, UltraLite.NET, UNIBOM, Unilib, Uninull, Unisep, Unistring, URK Runtime Kit for UniCode, Viafone, Viewer, VisualWriter, VQL,
WarehouseArchitect, Warehouse Control Center, W arehous e Studio, W arehouse WORKS, Watcom, W atcom SQL, W atcom SQL Server , W eb Deployment Kit,
Web.PB, Web.SQL, WebSights, WebViewer, WorkGroup SQL Server, XA-Library, XA-Server, XcelleNet, XP Server, XTNDAccess and XTNDConnect are
trademarks of Sybase, Inc. or its subsidiaries. 01/06

Unicode and the Unicode Logo are registered trademarks of Unicode, Inc.
All other company and product names used herein may be trademarks or registered trademarks of their respective companies.
Use, duplication, or disclosure by the government is subject to the restrictions set forth in subparagraph (c)(1)(ii) of DFARS 52.227-7013

for the DOD and as set forth in FAR 52.227-19(a)-(d) for civilian agencies.
Sybase, Inc., One Sybase Drive, Dublin, CA 94568.

Contents

About This Book.......................................................................................................................... vii

CHAPTER 1 Introduction..................................................................................... 1

About Adaptive Server ..................................................................... 1

System-specific issues..................................................................... 2

System user roles ............................................................................ 3

Environment variables...................................................................... 3

Adaptive Server devices and system databases.............................. 5

The master device..................................................................... 5

The sybsystemdb device........................................................... 6

The sysprocsdev device............................................................ 6

Optional devices and databases............................................... 6

dsync option on by default for database device files................. 8

Determining the location, type, and size of a database device. 9

Client/server communication.......................................................... 11

Changing Adaptive Server configuration........................................ 12

Languages other than U.S. English ............................................... 13

Adaptive Server specifications....................................................... 13

CHAPTER 2 Starting and Stopping Servers .................................................... 17

Overview ........................................................................................ 17

Requirements for starting servers ........................................... 17

Starting servers.............................................................................. 18

Server start-up parameters ..................................................... 18

Using a RUN_server_name file............................................... 19

Using the startserver command .............................................. 20

Using the monserver and backupserver commands............... 20

Starting servers when the operating system restarts..................... 20

For HP-UX............................................................................... 21

For IBM RS/6000..................................................................... 21

For Sun Solaris and Linux....................................................... 22

Starting XP Server after initial installation...................................... 24

Stopping servers ............................................................................ 24

Configuration Guide iii

Contents

Stopping Adaptive Server........................................................ 24

Stopping Backup Server.......................................................... 25

Stopping Monitor Server.......................................................... 26

Using the kill command........................................................... 26

Shutdown and shared memory files........................................ 27

CHAPTER 3 Configuring the Operating System .............................................. 29

Setting environment variables........................................................ 29

Configuring new servers with srvbuild............................................ 30

Using the stty setting...................................................................... 33

Restoring correct permissions........................................................ 34

File descriptors and user connections............................................ 34

For HP-UX............................................................................... 34

For AIX.................................................................................... 34

For Linux ................................................................................. 35

For Sun Solaris........................................................................ 35

Displaying current soft and hard limits .................................... 35

Increasing the soft limit............................................................ 35

Increasing the hard limit.......................................................... 36

Sample program...................................................................... 37

Enabling asynchronous disk I/O..................................................... 38

Adjusting the client connection timeout period............................... 41

For HP-UX............................................................................... 41

For IBM RS/6000..................................................................... 41

For Sun Solaris........................................................................ 42

For Linux ................................................................................. 42

Checking for hardware errors......................................................... 42

For HP-UX............................................................................... 42

For IBM RS/6000..................................................................... 43

For Sun Solaris........................................................................ 43

For Linux ................................................................................. 43

Monitoring the use of operating system resources ........................ 43

For HP-UX............................................................................... 43

For IBM RS/6000..................................................................... 44

For Sun Solaris and Linux....................................................... 44

A sample C shell maintenance script............................................. 45

CHAPTER 4 Adaptive Server Default Configuration........................................ 47

Default settings .............................................................................. 47

CHAPTER 5 Setting Up Communications Across the Network...................... 49

How Adaptive Server determines which directory service entry to use

iv

Adaptive Server Enterprise

Contents

50

How a client uses directory services.............................................. 51

Creating a directory services entry................................................. 51

Supported directory drivers............................................................ 52

Contents of an interfaces file.......................................................... 52

Heterogeneous and homogeneous environments......................... 53

Understanding the format of the interfaces file .............................. 55

Components of an interfaces file entry.................................... 56

Creating a master interfaces file .................................................... 58

Using dsedit or dscp to create a master interfaces file............ 58

Using a text editor to create a master interfaces file............... 58

Configuring interfaces files for multiple networks........................... 59

Configuring the server for multiple network handlers.............. 59

Configuring the client connections .......................................... 60

Configuring for query port backup........................................... 62

IPv6 support................................................................................... 63

Understanding IPv6................................................................. 63

IPv6 infrastructure................................................................... 64

Starting Adaptive Server Enterprise as IPv6-aware................ 65

Troubleshooting ............................................................................. 66

Server fails to start .................................................................. 66

Error when executing an ESP................................................. 67

CHAPTER 6 Using the Lightweight Directory Access Protocol as a Directory

Service..................................................................................... 69

Overview........................................................................................ 69

LDAP directory services versus the Sybase interfaces file............ 70

The libtcl*.cfg file............................................................................ 73

Enabling LDAP directory services.................................................. 74

Adding a server to the directory services....................................... 76

Multiple directory services.............................................................. 77

Encrypting the password................................................................ 78

Performance................................................................................... 78

Migrating from the interfaces file to LDAP...................................... 79

CHAPTER 7 Customizing Localization for Adaptive Server........................... 81

Overview of localization support .................................................... 81

Language modules.................................................................. 82

Default character sets for servers ........................................... 83

Supported character sets ........................................................ 84

Character set conversion ............................................................... 89

Conversions between server and client .................................. 89

Sort orders ..................................................................................... 90

Configuration Guide v

Contents

Available sort orders................................................................ 90

Language modules......................................................................... 93

Installing a new language module........................................... 93

Message languages................................................................ 93

Localization.................................................................................... 94

Localization directories............................................................ 94

About the directory.................................................................. 95

About the charsets directory.................................................... 95

About the locales.dat file......................................................... 95

Changing the localization configuration ......................................... 97

Adaptive Server localization.................................................... 98

Backup Server localization...................................................... 99

Sort orders............................................................................. 100

Character sets....................................................................... 101

charset utility ......................................................................... 102

CHAPTER 8 Logging Error Messages and Events ........................................ 105

Adaptive Server error logging ...................................................... 105

Enabling and disabling error logging..................................... 106

Setting error log paths.................................................................. 106

Setting the Adaptive Server error log path............................ 106

Managing messages.................................................................... 107

Logging user-defined messages........................................... 107

Logging auditing events ........................................................ 108

CHAPTER 9 Managing Adaptive Server Databases....................................... 109

Managing database devices ........................................................ 109

Device requirements ............................................................. 109

Creating files for database devices....................................... 110

CHAPTER 10 Adding Optional Functionality to Adaptive Server................... 113

Adding auditing ............................................................................ 113

Audit system devices and databases.................................... 113

Overview of audit installation................................................. 114

Pre-installation tasks for auditing devices............................. 115

Installing auditing................................................................... 115

Installing online help for Transact-SQL syntax............................. 121

Online syntax help: sp_syntax............................................... 121

Default device for the sybsyntax database............................ 122

Installing sybsyntax............................................................... 122

Index ........................................................................................................................................... 125

vi

Adaptive Server Enterprise

About This Book

This manual, the Configuration Guide, provides instructions for
performing specific configuration tasks for Sybase® Adaptive Server®
Enterprise on UNIX platforms.

The operating system release levels for the UNIX platforms on which
Adaptive Server is certified to execute is in the installation documentation
or release bulletin for your platform.

This manual covers the following topics:

• Instructions for reconfiguring certain attributes of your existing

• Creating network connections.

• Configuring optional functionality.

• Performing operating system administration tasks.

• Working with system administration issues that are relevant to

Sybase Adaptive Server Enterprise, Backup Server™, Adaptive
Server Enterprise Monitor™, and XP Server™ to meet your specif ic
needs.

Adaptive Server running on UNIX platforms. This manual
supplements the System Administration Guide and the Performance
and Tuning Guide.

Note Before configuring Adaptive Serv er according to the instructions in

this book, you sho uld have a newl y installed or upgraded Adapt ive Server
on your system. If you do not, follow the installation and upgrade
instructions in the installation documentation for your platform.

Audience

How to use this book

Configuration Guide vii

This manual is for System Administrators or other qualified installers who
are familiar with their system’s environment, networks, disk resources,
and media devices.

This manual contains the following chapters:

• Chapter 1, “Introduction” is an overview of Adaptive Server and the
configura tion changes you might want to make to Adaptive Server
after installing or upgrading the server.

• Chapter 2, “Starting and Stopping Servers” describes how to start and
stop Adaptive Server, Backup Server, Monitor Server, and XP Server.

• Chapter 3, “Configuring the Operating System” describes how to set up
your operating system to work with Adaptive Server.

• Chapter 4, “Adaptive Server Default Configuration” provides
information about the default parameter settings of Adaptive Server.

• Chapter 5, “Setting Up Communications Across the Network” describes
how to use the interfaces file to establish network connections for servers
and clients.

• Chapter 6, “Using the Lightweight Directory Access Protocol as a
Directory Service” provides information about using LDAP directory
services to establish connections.

• Chapter 7, “Customizing Localization for Adaptive Server” provides
background information about Sybase’s localization support as well as
task information for reconfiguring character sets, sort orders, and language
modules.

• Chapter 8, “Logging Error Messages and Events” describes how to use
the error logging features of Adaptive Server.

• Chapter 9, “Managing Adaptive Server Databases” provides information
about the administration of Adaptive Server databases, including both
routine tasks and performance and tuning considerations.

• Chapter 10, “Adding Optional Functi onality to Adaptive Server”
provides instructions for adding optional functionality, such as auditing
and the sample databases.

Related documents

The Sybase® Adaptive Server® Enterprise documentation set consists of the
following:

• The release bulletin for your platform – contains last-minute information
that was too late to be included in the books.

A more recent version of the release bulletin may be available on the
World Wide Web. To check for critical product or document information
that was added after the release of the product CD, use the Sybase
Technical Library.

•The Installation Guide for your platform – describes installati on, upgrade,
and configuration procedures for all Adaptive Server and related Sybase
products.

viii Adaptive Server Enterprise

About This Book

• What’s New in Adaptive Server Enterprise? – describes the new features

in Adaptive Server version 15.0, the system changes added to support
those features, and changes that may affect your existing applications.

• ASE Replicator User’s Guide – describes how to us e t he Ad apti ve Server

Replicator feature of Adaptive Server to implement basic replication from
a primary server to one or more remote Adaptive Servers.

• Component Integration Services User’s Guide – explains how to use the

Adaptive Server Component Integration Services feature to connect
remote Sybase and non-Sybase databases.

•The Configuration Gui de for your platform – provides instructions for

performing specific configuration tasks for Adaptive Server.

• Full-T ext Sear ch Specialty Data S tore User’ s Guide – describes how t o use

the Full-Text Search feature with Verity to search Adaptive Server
Enterprise data.

• Glossary – defines technical terms used in the Adaptive Server

documentation.

• Java in Adaptive Server Enterprise – describes how to install and use Java

classes as data types, functions, and stored procedures in the Adaptive
Server database.

• Messaging Service User’s Guide – describes how to useReal Time

Messaging Services to integrate Java Message Service and IBM
WebSphere MQ messaging services with all Adaptive Server database
applications.

• Monitor User's Guide, Historical Server – describes how to use Hi storical

Server to obtain performance information for SQL Server

®

and Adaptive

Server.

• Monitor Client Library Programmer’s Guide – describes how to write

Monitor Client Library applications that access Adaptive Server
performance data.

• Monitor Server User’s Guide – describes how to use Monitor Server to

obtain performance statistics from SQL Server and Adaptive Server.

• Performance and Tuning Guide – is a series of four books that explains

how to tune Adaptive Server for maximum performance:

• Basics – the basics for understanding and investigating performance
questions in Adaptive Server.

Configuration Guide ix

• Locking – describes how the various lockin g schemas can be used for
improving pe rformance in A daptive Server.

• Optimizer and Abstract Plans – describes how the optimizer
processes queries and how abstract plans can be used to change some
of the optimizer plans.

• Monitoring and Anal yzing – explains how statistics are obtained and
used for monitoring and optimizing performance.

• Quick Reference Guide – provides a comprehensive listing of the names
and syntax for commands, functions, system procedures, extended system
procedures, datatypes, and utilities in a pocket-sized book.

• Reference Ma nua l – is a series of four books that contains the following
detailed Transact-SQL

®

information:

• Building Blocks – Transact-SQL datatypes, functions, global

variables, expressions, identifiers and wildcar ds , an d r eser ved word s.

• Commands – Transact-SQL commands.

• Procedures – Transact-SQL system procedures, catalog stored

procedures, system extended stored procedures, and

dbcc stored

procedures.

• Tables – Transact-SQL system tables and

dbcc tables.

• System Admi nistration Guide – provides in-depth information about
administering servers and databases. This manual includes instructions
and guidelines for manag ing phy sical reso urces, secur ity, user and system
databases, and specifying character conversion, international language,
and sort order settings.

• System Tables Diagram – illustrates system tables and their entity
relationships in a poster format. Available only in print version.

• Transact-SQL User’s Guide – documents Transact-SQL, Sybase’s
enhanced version of the relational dat abase la nguage. This manual ser ves
as a textbook for beginning users of the database management system.
This manual also contains descriptions of the

pubs2 and pubs3 sample

databases.

• Using Adaptive Server Distributed Transaction Management Features –
explains how to configure, use, and troubleshoot Adaptive Server DTM
features in distributed transaction processing environments.

x Adaptive Server Enterprise

About This Book

• Using Sybase Failover in a High Availability System – provides

instructions for using Sybase’s Failover to configure an Adaptive Server
as a companion server in a high availability system.

• Unified Agent and Agent Management Console – Describes the Unified

Agent, which provides runtime services to manage, monitor and control
distributed Sybase resour ces.

• Utility Guide – documents the Adaptive Server utility programs, such as

isql and bcp, which are executed at the operating system level.

• Web Services User’s Guide – explains how to configure, use, and

troubleshoot Web Services for Adaptive Server.

• XA Interface Integration Guide for CICS, Encina, and TUXEDO –

provides instructions for using the Sybase DTM XA interface with
X/Open XA transaction managers.

• XML Services in Adaptive Server Enterprise – describes the Sybase native

XML processor and the Sybase Java-based XML support, introduces
XML in the database, and documents the query and mapping functions
that comprise XML Services.

Other sources of
information

Use the Sybase Getting Start ed CD, the SyBo oks CD, and the S ybase Pro duct
Manuals Web site to learn more about your product:

• The Getting Started CD contains release bulletins and installation guides

in PDF format, and may also contain other documents or updated
information not included on the SyBooks CD. It is included with your
software. T o read or prin t documents on t he Gettin g Starte d CD, you nee d
Adobe Acrobat Reader, which you can download at no charge from the
Adobe Web site using a link provided on the CD.

• The SyBooks CD contains product manuals and is included with your

software. The Eclipse-based SyBooks browser allows you to access the
manuals in an easy-to-use, HTML-based format.

Some documentation may be provided in PDF format, which you can
access through the PDF directory on the SyBooks CD. To read or print the
PDF files, you need Adobe Acrobat Reader.

Refer to the SyBooks Installation Guide on the Getting Started CD, or the
README.txt file on the SyBooks CD for instructions on ins tallin g and
starting SyBooks.

Configuration Guide xi

• The Sybase Product Manuals W eb sit e is an online versio n of the SyBooks
CD that you can access using a standard Web browser. In addition to
product manuals, you will find links to EBFs/Maintenance, Technical
Documents, Case Management, Solved Cases, newsgroups, and the
Sybase Developer Network.

Sybase certifications
on the Web

T o access the Sybase Pro duct Manuals Web site, go to

http://www.sybase.com/support/manuals/

.

Product Manuals at

Technical documentation at the Sybase Web site is updated frequently.

v Finding the latest information on product certifications

1 Point your Web browser to Technical Documents at

http://www.sybase.com/support/techdocs/

.
2 Select Products from the navigation bar on the left.
3 Select a product name from the product list and click Go.
4 Select the Certification Report filter, specify a time frame, and click Go.
5 Click a Certification Report title to display the report.

v Finding the latest information on component certifications

1 Point your Web browser to Availability and Certification Reports at

http://certification.sybase.com/

.

2 Either select the product family and product under Search by Product; or

select the platform and product under Search by Platform.

3 Select Search to display the availability and certification report for the

selection.

v Creating a personalized view of the Sybase Web site (including support

pages)

Set up a MySybase profile. MySybase is a free service that allows you to create
a personalized view of Sybase Web pages.

1 Point your Web browser to

http://www.sybase.com/support/techdocs/

Technical Documents at

.
2 Click MySybase and create a MySybase profile.

xii Adaptive Server Enterprise

Sybase EBFs and
software
maintenance

About This Book

v Finding the latest information on EBFs and software maintenance

1 Point your Web browser to the Sybase Support Page at

http://www.sybase.com/support

.

2 Select EBFs/Maintenance. If prompted, enter your MySybase user name

and password.
3 Select a product.
4 Specify a time frame and click Go. A list of EBF/Maintenance releases is

displayed.

Padlock icons indicate that you do not have download authorization for

certain EBF/Maintenance releases because you are not registered as a

Technical Support Contact. If you have not registered, but have valid

information provided by your Sybase representative or through your

support contract, click Edit Roles to add the “T echn ical Support Contact”

role to your MySybase profile.
5 Click the Info icon to display the EBF/Maintenance report, or click the

product description to download the softwa re.

Conventions

The following sections describe conventions used in this manual.
SQL is a free-form language. Ther e are no rules about the number of words you

can put on a line or where you must break a line. However, for readability, all
examples and most syntax statements in this manual are formatted so that each
clause of a statement begins on a new line. C lauses that have more than one par t
extend to additional lines, which are indented. Complex commands are
formatted using modified Backus Naur Form (BNF) notation.

Table 1 sho ws the conventions fo r syntax statements that appear in this manual:

Table 1: Font and syntax conventi ons for this manua l

Element Example

Command names, procedure names, utility names,
and other keywords display in sans serif font.

Database names and datatypes are in sans serif font. master database
Book names , file names, variables, and path nam es are

in italics.

Configuration Guide xiii

select
sp_configure

System Administratio n Guid e
sql.ini file
column_name
$SYBASE/ASE directo r y

Element Example

V a riables—or words tha t stand for values that you fill
in—when they are part of a query or statement, are in
italics in Courier font.

Type parentheses as part of the command.
Double colon, equals sign indicates that the syntax is

written in BNF notation. Do not type this symbol.
Indicates “is defined as”.

Curly braces mean that you must ch oose at least one
of the enclosed options. Do not type the braces.

Brackets mean that to choose one or more of the
enclosed options is op tional. Do not type the bracke ts.

The comma means you may cho ose as many of the
options shown as you want. Separate your choices
with commas as part of the command.

The pipe or vertical bar( | ) means you may select only
one of the options shown.

An ellipsis (...) means that you can repeat the last unit
as many times as you like.

select column_name

compute row_aggregate (column_name)

::=

{cash, check, credit}

[cash | check | credit]

cash, check, credit

cash | check | credit

buy thing = price [cash | check |
credit]
[, thing = price [cash | check |
credit] ]...

Y ou must buy at least one thing and give its price. Y ou may
choose a method of payment: one of the items enclosed in
square brackets. You may also choose to buy additional
things: as many of them as you like. For each thing you
buy, give its name, its price, and (optionally) a method of
payment.

from table_name
where search_conditions

• Syntax statements (displaying the syntax and all options for a command)
appear as follows:

sp_dropdevice [device_name]

For a command with more options:

select column_name

from table_name
where search_conditions

In syntax statements, keywords (commands) are in normal font and
identifiers are in lowercase. Italic font shows user-supplied words.

• Examples showing the use of Transact-SQL commands are printed like
this:

select * from publishers

xiv Adaptive Server Enterprise

• Examples of output fr om the computer appear as follows:

pub_id pub_name city state

------- --------------------- ----------- ----­0736 New Age Books Boston MA
0877 Binnet & Hardley Washington DC
1389 Algodata Infosystems Berkeley CA

(3 rows affected)

In this manual, most of the examples are in lowercase. However, you can
disregard case when typing Tr ansact-SQL keywords. For example,

Select, and select are the same.

Adaptive Server’s sensitivity to the case of database objects, such as table
names, depends on the sort order installed on Adaptive Server . Y ou can change
case sensitivity for single-byte character sets by reconfiguring the Adaptive
Server sort order . For more infor mation, see the System Admini s t ra tio n Gu id e .

About This Book

SELECT,

Terms

Accessibility
features

The following terms appear repeatedly throughout this book. For more detailed
information about these and other terms, see the Glossary.

• /work/sybase – is given as an ex ample of the Sybas e installation directo ry.

• T ext editor – ref ers to an ASCII tex t editor or any edito r that can save files

to text format.

This document is available in an HTML version that is specialized for
accessibility. You can navigate the HTML with an adaptive technology such as
a screen reader, or view it with a screen enlarger.

Adaptive Server Enterprise and the HTML documentation have been tested for
compliance with U.S. government Section 508 Accessibility requirements.
Documents that comply with Section 508 generally also meet non-U.S.
accessibility guidelines, such as the World Wide Web Consortium (W3C)
guidelin e s f or Web sites.

The online help for this product is also provided in HTML, which you can
navigate using a screen reader.

Note You might need to configure your accessibility tool for optimal use.

Some screen readers pronounce text based on its case; for example, they
pronounce ALL UPPERCASE TEXT as initials, and MixedCase Text as
words. You might find it helpful to configure your tool to announce syntax
conventions. Consult the documentation for your tool.

Configuration Guide xv

For information about how Sybase supports accessibility, see Sybase

Accessibility at http://www.sybase.com/accessibility

. The Sybase Accessibility

site includes links to information on Section 508 and W3C standard s.

If you need help

Each Sybase installati on that has purchased a su pport contract ha s one or more
designated people who are authorized to contact Sybase Technical Support. If
you cannot resolve a problem us ing the manuals or online help, pleas e have the
designated person contact Sybase Technical Support or the Sybase subsidiary
in your area.

xvi Adaptive Server Enterprise

CHAPTER 1

Introduction

Sybase Adaptive Server Enterprise for UNIX is a full-featured Adaptive
Server that runs on the following platforms:

• HP-UX

•IBM AIX

•Sun Solaris

• Linux 32- bi t

Note The instructions in this book assume that Adaptive Server is

installed and running. For information about installin g and s tar ting
Adaptive Server, as well as for an overview of Adaptive Server, see the
Adaptive Server Installation Guide for your platform.

This chapter provides an overview of how to configure Adaptive Server
and the steps you need to take to customize it for your use.

Topic Page

About Adaptive Server 1
System-specific issues 2
System user roles 3
Environment variables 3
Adaptive Server devices and system databases 5
Client/server communication 11
Changing Adaptive Server configuration 12
Languages other than U.S. English 13
Adaptive Server specifications 13

About Adaptive Server

Adaptive Server performs data management and transaction functions,
independent of client applications and user interface functions.

Configuration Guide 1

System-specific issues

Adaptive Server also:

• Manages multiple databases and multiple users

• Keeps track of the data’s location on disks

• Maintains the mapping of logical data descri ption to phys ical data storage

• Maintains data and procedure caches in memory

Adaptive Server uses these auxiliary programs to perform dedicated tasks:

• Backup Server manages database load, dump, backup, and restoration
activities.

• Monitor Server keeps track of performance data.

• Historical Server ob tains performance data from Monitor Server and sav es
the data in files for use at a later time.

• XP Server stores the extended stored procedures (ESPs) that allow
Adaptive Server to run operating-system and user-defined commands.

System-specific issues

Adaptive Server runs on a variety of hardware and op erating system platfor ms.
System-specific issues do not affect the basic functionality of Adaptive Server,
but there are differences among platform implementations. These differences
may include:

• Adaptive Server configuration

• Changes to the operating system that enable or enhance Adaptive Server
performance

• The structure of entries in the interfaces file

• Options for selecting database devices

• Operating system commands or utilities that simplify or automate routine
system administ ration tasks

• Operating system utilities for monitoring Adaptive Ser ver p erformance

System-specific issues are described in this document. For more information
about system-specific issues, see the Installation Guide and release bulletin for
your platform.

2 Adaptive Server Enterprise

System user roles

The Adaptive Server installation and setup process defines var io us user rol es .
Different user roles have different responsibilities and privileges. These user
roles clarify the way Adaptive Server is to be integrated into your system:

• Operating system administrator – the individual who maintains the

operating system. This individual has superuser or “root” privileges.

• System Administrator – the individu al in charge of Adaptive Server

system administration, creating user accounts, assigning permissions on
databases, and creating new databases. At installation time, the Syst em
Administrator’s login name is “sa”. The “sa” login is not a UNIX login.
The “sa” login is specific to Adaptive Server and is used to log in to
Adaptive Server using the

• “sybase” login – the “sybase” login is a UNIX login that owns all the

Sybase installation directories and files, sets permissions on those
directories and files, and performs the installation and upgrading of
Adaptive Server. This login is preferred, but not required.

CHAPTER 1 Introduction

isql command.

Environment variables

It is crucial to the operation of Sybase products that the system environment
variables be set correctly.

Environment variables are set in the user’ s enviro nment either inter actively or
by including them in the user’s .login and .cshrc files (for C shell) or .profile
file (for Bourne shell). The installation instructions in this guide explain when
to set these variables.

Note As part of the installation, the installer setup these environment variables

in SYBASE.csh and SYBASE.sh files. You can source the file to set their
environment.

• DSLISTEN – defines the name Adaptive Server uses to listen for client

connections if no name is given during the Adaptive Server start-up. If
DSLISTEN is not set, and no name is given during the Adaptive Server
start-up, the Adaptive Server name defaults to SYBASE.

Configuration Guide 3

Environment variables

• DSQUERY – defines the Adaptive Server name that client programs
attempt to connect to if no Adaptive Server is specified with a command
line option. If DSQUERY is not set, and you do not supply the Adaptive
Server name with a command line option, clients attempt to connect to
SYBASE.

• SYBASE – defines the path of the Sybase installation directory. The
installation program sets up the variable SYBASE to point to the release
directory specified during installation.

• SYBASE_ASE – defines the subdirectory of the Adaptive Server
component.

• SYBASE_OCS – defines the subdirectory to which Open Client™ is set.

• SYBASE_ FTS – defines the subdirectory for the full-text search
components.

• SYBASE_SYSAM – points to the license-management software
directory.

• PATH – specifies directory path to search for executables. The Sybase
executables are located in the installed_components /bin directory. When
using the source files SYBASE.csh or SYBASE.sh, the following paths are
prepended to PAT H:

$SYBASE/$SYBASE_ASE/bin:$SYBASE/$SYBASE_OCS/bin

• LD_LIBRARY_PATH – this variable specifies the directory to search for
shared libraries. The Sybase shared libraries are located in the installed
component’s /lib directory.

When using the source files: SYBASE.csh or SYBASE.sh, the following
paths are prepended to the LD_LIBRARY_PATH environment variable:

$SYBASE/$SYBASE_ASE/lib:$SYBASE/$SYBASE_OCS/
lib:$SYBASE/SYBASE_FTS/lib,etc.

• LIBPATH – on the IBM RS/6000 platform, this variable specifies which
directory to search for libraries.

When using the source files: SYBASE.csh or SYBASE.sh, the following
paths are prepended to the LIBPATH environment variable:

$SYBASE/$SYBASE_ASE/lib:$SYBASE/$SYBASE_OCS/
lib: $SYBASE/SYBASE_FTS/lib, etc.

• SHLIB_PATH – on HP-UX platforms, this variable specifies which
directory to search for libraries.

4 Adaptive Server Enterprise

CHAPTER 1 Introduction

When using the source files: SYBASE.csh or SYBASE.sh, the followin g
paths are prepended to the LIBPATH environment variabl e:

$SYBASE/$SYBASE_ASE/lib:$SYBASE/$SYBASE_OCS/
lib: $SYBASE/SYBASE_FTS/lib, etc.

Adaptive Server devices and system databases

Devices are files or portions of a disk that are used to store databases and
database objects. You can initialize devices using raw disk partitions or
operating system files.

Adaptive Server requires the following devices:

•

master – to store system databases.

•

sybsystemdb – to store information about distributed transaction.

•

sysprocsdev – to store system procedures.

The master, sybsystemdb, and sysprocsdev devices are created when you
create a new Adaptive Server.

The master device

The master device contains the following databases:

•

master – controls the operation of Adaptive Server as a whole and stores

information about all users, user databases, devices, objects, and system
table entries. The
device and cannot be expanded onto any other device.

•

model – provides a template for new user databases. The model database

contains required system tables, which are copied into a new user database
with the

•

tempdb – the work area for temporary databases in Adaptive Server.

create database command.

Adaptive Server supports multiple
database” in the T-SQL User’s Guide. Each time Adaptive Server is
started, the
database.

Configuration Guide 5

master database is contained entirely on the master

tempdbs. See “create temporary

tempdb database is cleared and rebuilt from the model

Adaptive Server devices and system databases

• The sample databases, pubs2 and pubs 3, are stored on the master device
at installation, but should be moved to a user-defined device after
installation.

Note For recovery purposes, Sybase recommends that you do not create

other system or user databases or user objects on the master device.

The sybsystemdb device

For new installations the master device also contains the sybsystemdb
database. Th e
stores information about trans actions in progress, and which is also used during
recovery.

The

sybsystemdb database is required to support distributed transaction

management (DTM) features. Befor e install ation, make sure you have enough
space available on the default segment to support

sybsystemdb device stores the sybsystemdb database, which

sybsystemdb.

The sysprocsdev device

The sybprocsdev devices stores the sybsystemprocs database, which contains
most of the Sybase-supplied system procedures. System procedures are a
collection of SQL statements and flow-of-control statements that perform
system tasks, for exa mp l e,

sp_configure.

The system procedures that are needed during recovery situa tions are stored in
the

master database.

Note sysprocsdev is the default system name for this device. However, it is

frequently referred to as the

sybsystemprocs database.

sybsystemprocs device, since it stores the

Optional devices and databases

The devices and databases described in the following sections are optional.

The sample
databases

6 Adaptive Server Enterprise

The sample databases are:

CHAPTER 1 Introduction

•

pubs2 and pubs3 – provided as learning tools for Adaptive Server. pubs2

is used for most of the examples in the Adaptive Server documentation;
other examples use the

pubs3 database. Both are available in U.S. English

versions of Adaptive Server.

•

interpubs – contains French and German data.

•

jpubs – contains Japanese data.

For information about installing the sample databases, see the Installation
Guide for your platform, Ch apter 3, “Post-Installation Tasks.”

For information about the contents of the sample databases, see the
Trans act -S Q L User’s Guide.

The sybsecurity
device and database

dbccdb database

sybmgmtdb database

The sybsecurity device is created as part of the auditing installation process.
The

sybsecurity device stores the sybsecurity database and the auditing system

procedures with which you can configure audit ing for your system.
The auditing system records system security information in an Adaptive Server

audit trail. Y ou can use this audit trail to monitor the use of Adaptive Server or
system resources .

For instructions on configuring Adaptive Server for auditing, see Chapter 10,
“Adding Optional Functionality to Adaptive Server.” For information about
installing and using the auditing system, see Chapter 12, “Auditing,” in the
System Administration Guide.

The database consistency checker (dbcc) provides commands for checking the
logical and physical cons istency of a d atabase . The
results of

dbcc checkstorage records consistency information for the target database,

dbcc when dbcc checkstorage or dbcc check verifying is used.

operation activity, and the results of the operation in the
Stored in the database are

dbccdb and for generating reports on the results of dbcc checkstorage

dbcc stored procedures for creating and maintaining

dbccdb database stores the

dbccdb database.

operations.
For information on installing and using

dbccdb, see Chapter 25, “Checking

Database Consistency,” in the System Administration Guide.

sybmgmtdb is a Job Scheduler database.

Configuration Guide 7

Adaptive Server devices and system databases

All the job, schedule, and scheduled job information, and data needed by the
Job Scheduler task for internal processing is stored in the
Most access to data in the
stored procedures make the data available to the GUI, the JS Agent and the
command-line interface. Only the Job Scheduler task accesses data directly
from the

sybmgmtdb database.

sybmgmtdb database.

sybmgmtdb database is via stored procedures. The

For more information ab ou t

sybmgmtdb and Job Scheduler, s ee Job Scheduler

User’s Guide .

dsync option on by default for database device files

The dsync option ensures Adaptive Server can recover data from devices on the
file systems. By default, Adaptive Server enables
However,
that experience high write activity. The
the

Note The dsync option is ignored for raw devices.

dsync may cause performance degredation for file system devices

dsync option can be set or reset using

disk init, disk reinit, and sp_deviceattr commands.

When installing a new Adaptive Server, by default
system devices.

When upgrading from ASE 12.0 or earlier releases on a UNIX server that
stored databases on UNIX file system devices, by default

•

on for the master device

•

off for all other devices

Immediately after upgrade, set the
also

sp_deviceattr and sp_helpdevice in the Reference Manual

dsync option for the file sys tem devices. See

dsync for file system devices.

dsync is set on for all file

dsync is set:

Warning! Data corruption may occur if dsync option is not set immediately

after upgrade.

8 Adaptive Server Enterprise

CHAPTER 1 Introduction

Determining the location, type, and size of a database device

Adaptive Server requires several database devices. Table 1-1 below shows

baseline values for each of the devices. See the release bulletin for any last
minute changes to these values.

Table 1-1: Adaptive Server database devices

Minimum

Device Purpose

master Stores system

databases

sysprocsdev

(also called the

sybsystemprocs

device)

Stores the

sybsystemprocs

database

size

24MB for 2K pages
45MB for 4K pages
89MB for 8K pages
177MB for 16K

pages
120MB 120MB (plus any

Minimum
recommended size

30MB
60MB
120MB
240MB

space for holding
stored procedures that
you have created)

sybsystemdb Transaction

processing

sybsecurity (optional) Required for

auditing

For Solaris, HP-UX, IBM AIX, and Linux

For all databases you can use either a raw partition or a file.
Sybase Adaptive Server supports the database devices on NFS- and

CIFS-mounted devices with Network Appliance Filers for storing data.
Network appliance filers provide the same performance and data integrity as
raw devices. There are no changes needed to the operating system or to Sybase
Adaptive Server to use network appliance filers.

NFS mounted devices have been tested on Solaris, HP-UX, IBM AIX, and
Linux.

For All Platforms

You may need to repartition the disk you choose. Contact your operating
system administrator for assistance.

3MB for 2K pages
6MB for 4K pages
12MB for 8K pages
24MB for 16K pages
5MB 7MB; more for

5 – 24MB

specialized auditing

Configuration Guide 9

Adaptive Server devices and system databases

When preparing a raw partition device, follow these guidelines:

• Do not initialize a database device on the partition that contains your
Sybase installation software. Doing so destroys all existing files on that
partition.

• A raw partition designated for use by Sybase cannot be mounted for use
by the operating system for any other purpose, such as for file systems or
swap space.

• After a Sybase configuration utility or the

disk init command has initialized

a portion of a partition as a database device, the entire partition cannot be
used for any other purpose. Any space left o n the partition beyond the size
specified for the device becomes inaccessible, unless you drop and re­create the device.

• For best performance, place the Sybase software and all disk devices,
including the

master device, on the same machine.

• T o avoid any possibility of using a partition that contains the partition map,
do not use cylinder 0.

v Choosing a raw partition

1 Determine which raw partitions are available.
2 Determine the sizes of the raw partitions.
3 From the list of available raw partitions, select a raw partition for each

device, based on the size recommendations in Table 1-1 on page 9.

4 Verify with the operating System Administrator that the partition you have

chosen is available.

5 Make sure the “sybase” user has read and write privileges to the raw

partition.

Note For more information on choosing a raw partition, see your

operating system documentation.

10 Adaptive Server Enterprise

Client/server communication

Adaptive Server communicates with other Adaptive Servers, Open Server™
applications (such as Backup Server), and client software on your network.
Clients can talk to one or more servers, and servers can communicate with
other servers by remote procedure calls.

For Sybase products to interact with one another, each product needs to know
where the others reside on t h e netw ork . Na mes and addr ess es of ev ery known
server are listed in a directory services file. This information can be stored in a
directory services file two different ways:

• In an interfaces file, named interfaces on UNIX platforms, located in the

$SYBASE installation directory, or

• In an LDAP server
After your Adaptive Server or client software is installed, it can connect with

any server on the network that is listed in the directory services.
When you are using a client program, and you want to connect with a particular

server, the client program looks up the server name in the directory services
and connects to that server, as shown in Figure 1-1. You can supply the name
of the server by using the DSQUERY environment variable.

CHAPTER 1 Introduction

On TCP/IP networks, the port number gives clients a way to identify the
Adaptive Server, Open Server , Backup Server , or Monitor Server to which they
want to connect. It also tells the server where to listen for incoming connection
attempts from clients. The server uses a single port for these two services
(referred to as query service and listener service).

Configuration Guide 11

Changing Adaptive Server configuration

Figure 1-1: Communicating with a server using interfaces file

During installation, you use the srvbuild utility to create and configure a new
server. The

srvbuild process adds entries to the interfaces file for your new

Adaptive Server, Backup Server, Monitor Serve r, and XP Server.
For instructions on how to modify existing interfaces file entries using

and

dscp or to create new interfaces file entries for existing servers, Chapter 6,

“Using the Lightweight Directory Access Protocol as a Directory Service.”

Changing Adaptive Server configuration

Use sp_configure to change the configuration of Adaptive Server. To change
the configuration of several servers, enter the
script. For details on usi ng

sp_configure, see the Syst em Administration Guide

and the Reference Manual.
For information about configuring languages, character sets, and sort orders,

see Chapter 7, “Customizing Lo calization for Adaptive Server.”
For information about configuring Adaptive Server to use high availability

features, see Using Sybase Failover in a High Availability Environment.
For information about con figur ing Adaptiv e Server for distributed transaction

management (two-phase commit), see the Using Adaptive Server Distribu t ed
Transaction Management Features.

sp_configure information in a

dsedit

12 Adaptive Server Enterprise

Languages other than U.S. English

If you are running srvbuild in a language o ther than U.S. English, verify that any
input you provide uses a character set that is supported by the us_english
character set.

Note The us_english character set does not support accent marks, such as

tildes (~) and umlauts (ü). This prevents
sets that use these characters.

For more information about languages, character sets, and sort orders, see the

Installation Guide for your platform. For more information on srvbuild, see the
Utility Guide.

Adaptive Server specifications

Database specifications

Databases per Adaptive
Server

Maximum database size • 2K page server – 4TB

Minimum allowable

sybsystemprocs database

Maximum size of a
database device (disk
partition)

Maximum number of
database devices per server

Maximum number of
devices or device pieces
per database

Maximum number of
segments per data ba s e

Maximum number of login
IDs per server

A maximum of 32,767
databases per server

• 4K page server – 8TB

• 8K page server – 16TB

• 16K page serve r – 32TB
124MB Required for an upgrade

24

2
(4TB)

31

2

Unlimited Limited by available memory

31

2147516416

If the Operating System supports
file sizes up to 4TB, then Adaptive
Server supports file system devices
up to 4TB

CHAPTER 1 Introduction

srvbuild from supporting the character

Configuration Guide 13

Adaptive Server specifications

Maximum number of users

2146484223

per database
Maximum number of

1032193

groups per database

Table specifications

User objects pe r database 2

31

– 255
Indexes per table 250 (one clustered index)
Rows per table Limited by availa ble

storage

Columns per composite

31

index
Creation of cl us te re d index 1.2*(x + y)

x = total data space in
table,
y = sum of space of all
nonclustered indexes on
table,
and 20 percent overhead
for logging

Maximum size of ob je ct

255

name

Maximum 2

32

For sorted data, approximately 20
percent of the table size needed

Query specifications

Maximum number of tables
participating in a query, for a
query without a union, or for
each side of a union in a query

64 Maximum of 50 user tabl e s,

including result tables, tables
referenced by views (the view
itself is not counted) correlations
and self-joins; maximum of 14
worktables

Maximum number of tables in a
“union” query

256 Includes up to 50 user t ables and 14

work tables on every side of the
union, for a total of up to 256 tables
across all sides of the union.

Maximum number of databases
participating in one transaction

Unlimited Includes database where

transaction began, all databases
changed during transaction, and

tempdb, if it is used for results or

worktables

14 Adaptive Server Enterprise