Sybase Adaptive Server, Adaptive Server Enterprise 15.0 Configuration Manual
Configuration Guide
Adaptive Serv er® 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 (800) 685-8225, fax (617) 229-9845.
Customers in other countries with a U.S. license agre eme nt ma y conta c t Custom er Fulfi llme nt via the above fax number. All other
international customers should contact their Sybase 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, Adapt ive Server E nterprise , Adaptive Se rver Ent erprise Monitor , Adaptive Server Enterpris e Replication, Adaptive Se rver 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-Li bra ry, Client
Services, CodeBank, Column Design, ComponentPack, Connection Manager, Convoy/DM, Copernicus, CSP, Data Pipeline, Data Workbench, DataArchitect,
Database Analyzer, DataExpress, DataServer, DataWindow, DataWindo w .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, Enterprise Application S tudio, Enterprise Client/
Server, Enterprise Connect, Enterprise Data Studio, Enterprise Manager, Enterprise Portal (logo), Enterprise SQL Server Manager, Enterprise Work
Architecture, Enterprise Work Designer, Enterprise Wor k Modeler, eProcurement Accelerator, eremote, Everything Works Better When Everything Works
Together , EWA, ExtendAssist, Extended Systems, ExtendView , F inancial 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 Warehouse Studio, 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 Acces s Se rver, 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, ObjectCycle, 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, Pow erTips, 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,
RemoteW are, RepConnector, Report Workbench, Report-Execute, Replication Agent, Replication Driver, Replication Server, Replication Server Manager,
Replication Toolkit, Resource Manager , RFID Anywhere, RW-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), SystemT ools, Tabular 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 Imagin ation I n to Realit y,
UltraLite, UltraLite.NET, UNIBOM, Unilib, Uninull, Unisep, Unistring, URK Runtime Kit for UniCode, Viafone, Viewer, VisualWr iter, VQL,
WarehouseArc hitect, W arehouse Control Center , W arehouse Studi o, W arehouse WORKS, W a tcom, W atcom SQL, W atcom SQL Server, Web 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
Configuration Gu ide iii
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
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 instructio ns 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 .
Sybase Adaptive Server Enterprise, Backup Server™, Adaptive
Server Enterprise Monitor™, and XP Server™ to meet yo ur specific
needs.
Audience
How to use this book
• Configuring optional functionality.
• Performing operating system administration tasks.
• Working with system administration issues that are relevant to
Adaptive Server running on UNIX platforms. This manual
supplements the System Administra tion Guide and the Performance
and Tuning Guide.
Note Before configuring Ad aptive Server according to the instr uctions in
this book, you s hould have a newly install ed or upgraded Adaptive Server
on your system. If you do not, follow the installation and upgrade
instructions in the installation documentation for your p latform.
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 an d the
configuration changes you might want to make to Adaptive Server
after installing or upgrading the server.
Configuration Gu ide vii
• 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 Localizat ion 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.
Related documents
• Chapter 8, “Logging Error Me ssa ges 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, i ncluding both
routine tasks and performance and tuning considerations.
• Chapter 10, “Adding Optional Functionality to Adaptive Server”
provides instructions for adding optional functio nality, su ch as auditing
and the sample databases.
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 inst allation, 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 – des cribes ho w to us e the Adaptive 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 Configurati on Gui de for your platform – provides instructions for
performing specific configuration tasks for Adaptive Server.
• Full-T ext Sear ch Specialty Data S tor e User’ s Guid e – describes how to 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 – descri bes how to us e Historical
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 Gu ide ix
• Locking – describes how the various locki ng schemas can be used for
improving performance in Adaptive 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 Analyzing – exp lains 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, extend ed system
procedures, datatypes, and utilities in a pocket-sized book.
• Refer ence Ma nual – is a series of four books that contains the following
detailed Transact-SQL
®
information:
• Building Blocks – Transact-SQL datatypes, functions, global
variables, expressions, identifiers an d 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 Administratio n Guide – provides in-depth information about
administering servers and databases. This manual includes instructions
and guidelines f or managing physical r esources, s ecurity, 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 Guid e – documents Transact-SQL, Sybase’s
enhanced version of the relati onal datab ase lang uage. This man ual serves
as a textbook for beginning users of the database management system.
This manual also contains descriptions of the
pubs2 and pubs3 sam ple
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 S tarted CD, the SyBooks CD, and the Sybas e Product
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. To read or print documents on the G etting S tarted CD, yo u need
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 installing and
starting SyBooks.
Configuration Gu ide xi
• The Sybase Produ ct Manuals W eb si te is an online versi on 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 Product Man uals 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 “Technical Support Contact”
role to your MySybase profile.
5 Click the Info icon to display the EBF/Maintenance report, or click the
product description to downl oad the softw are.
Conventions
The following sections describe conventions used in this manual.
SQL is a free-form languag e. There are no rules about the numb er 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 n ew line. Clauses that have more than on e part
extend to additional lines, which are indented. Complex commands are
formatted using modified Backus Naur Form (BNF) notation.
T able 1 shows the conventions for syntax statements that appear in this manual:
Table 1: Font and syntax conv enti 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 name s, file nam es, variables , and path n ames are
in italics.
select
sp_configure
System Administra tio n Guid e
sql.ini file
column_name
$SYBASE/ASE dir ect o ry
Configuration Gu ide xiii
Element Example
Variables—or words that stand for values that you fill
select column_name
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 indicate s that the synta x is
compute row_aggregate (column_name)
::=
written in BNF notation. Do not type this symbol.
Indicates “is defined as”.
Curly braces mean that you must choose at least one
{cash, check, credit}
of the enclosed options. Do not type the braces.
Brackets mean that to choose one or more of the
[cash | check | credit]
enclosed options i s optional. Do n ot type the br ackets.
The comma means you may choose as many of the
cash, check, credit
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
cash | check | credit
one of the options sh ow n .
An ellipsis (...) means that you can repeat the last unit
as many times as you like.
buy thing = price [cash | check |
credit]
[, thing = price [cash | check |
credit] ]...
Y ou must buy at least one thing and give its price. You 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:
For a command with more options:
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:
xiv Adaptive Server Enterprise
sp_dropdevice [device_name]
select column_name
from table_name
where search_conditions
select * from publishers
• Examples of output from 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 Transact-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 . You can change
case sensitivity for single-byte character sets by reconfiguring the Adaptive
Server sort order. For more information, see the System Administration 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 example o f the Sybase installation directory.
• T ext ed itor – refers to an ASCII text editor or an y editor 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)
guidel in e s fo r Web site s.
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
convent ions. Consult the documentation for your t o ol.
Configuration Gu ide 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 standards.
If you need help
Each Sybase install ation that has pur chased a support contract has one or more
designated people who are authorized to contact Syb ase Technical Support. If
you cannot resolve a probl em using the manuals or onlin e help, please 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 -bit
Note The instructions in this book assume that Adaptive Server is
installed and running. For information about installing and starting
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 Gu ide 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 des cripti on to physical 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 obtains performance data f rom Monitor Server and saves
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 an d operating system plat forms.
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 administration tasks
• Operating system utilities for monitoring Adaptive Server performance
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 various user roles.
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 indivi du al in charge of Adaptive Server
system administration, creating user accounts, assigning permissions on
databases, and creating new databases. At installation tim e, the System
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 environmen t either interactively 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 Gu ide 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 th e 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 PATH:
$SYBASE/$SYBASE_ASE/bin:$SYBASE/$SYBASE_OCS/bin
• LD_LIBRAR Y_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 following
paths are prepended to the LIBPATH environment variable:
$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 d e vi ces using raw disk partitions or
operating system files.
Adaptive Server requires the following devices:
•
master – to store system databases.
•
sybsystemdb – to store inform at ion about distributed transaction.
•
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:
•
•
•
sysprocsdev – to store system procedures.
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
master database is contained entirely on the master
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.
Adaptive Server supports multiple
create database command.
tempdbs. See “create temporary
database” in the T-SQL User’s Guide. Each time Adaptive Server is
started, the
tempdb database is cleared and rebuilt from the model
database.
Configuration Gu ide 5
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. T he
stores information abou t transactions in progress, and which is also used dur ing
recovery.
The
sybsystemdb database is required to support distributed transaction
management (DTM) features. B efore inst allation, mak e sure you have eno ugh
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 example,
The system procedur es that are need ed durin g recovery situations are stored in
the
master database.
Note sysprocsdev is the default system name for this device. Howev er, it is
frequently referred to as the
sybsystemprocs database.
Optional devices and databases
The devices and databases described in the following sections are optional.
The sample
databases
The sample databases are:
sp_configure.
sybsystemprocs device, since it stores the
6 Adaptive Server Enterprise
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, Chapter 3, “Post-Installation Tasks.”
For information about the contents of the sample databases, see the
Tr ans act -S Q L User’s Guide.
The sybsecurity
device and databa se
dbccdb databa se
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 au diting for your system.
The auditing system records system s ecurity information in an Adaptive Server
audit trail. Y ou can use this audit trail to monitor the use of Adaptive Server or
system resource s .
For instructions on configuring Adaptive Server for auditing, see Chapter 10,
“Adding Optional Functionality to A daptive 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 consist ency of a dat abase. Th e
results of
dbcc checkstorage records consistency information for the target database,
dbcc when dbcc checkstorage or dbcc check verify i ng 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
Database Consistency,” in the System Administration Guide.
sybmgmtdb database
Configuration Gu ide 7
sybmgmtdb is a Job Scheduler database.
dbccdb, see Chapter 25, “Checking
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 informati on ab ou t
sybmgmtdb and Job Scheduler , see 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 th e
file systems. By default, Adaptive Server enables
However,
that experience high write activity. The
the
disk init, disk reinit, and sp_deviceattr commands.
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
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
dsync for file system devices.
dsync is set on for all file
dsync is set:
•
off for all other devices
Immediately after upgrade, set the
also
sp_deviceattr and sp_helpdevice in the Reference Manual
Warning! Data corruption may occur if dsync option is no t set im mediately
after upgrade.
8 Adaptive Server Enterprise
dsync option for the file system devices. See
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 need ed 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.
3MB for 2K pages
6MB for 4K pages
12MB for 8K pages
24MB for 16K pages
5MB 7MB; more for
5 – 24MB
specialized auditing
For All Platforms
You may need to repartition the disk you choose. Contact your operating
system administrator for assistance.
Configuration Gu ide 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 exis ting 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 on the partition beyond the s ize
specified for the device becomes inaccessible, unless you drop and recreate 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 communicati on
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 net work . N ames and addr ess e s of every 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.
CHAPTER 1 Introduction
When you are us ing a client p rogram, 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.
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 Gu ide 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 Server, 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 us ing
sp_configure, see the System Administ ra t i on Gui de
and the Reference Manual.
For information about configuring languages, character sets, and sort orders,
see Chapter 7, “Customizing Localization 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 configur ing Adap tive Server for distributed transaction
management (two-phase commit), see the Using Adaptive Ser ver Dist ri bu t ed
T ransaction 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 langu age other than U.S. English, veri fy 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.
CHAPTER 1 Introduction
srvbuild from supporting the character
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 server – 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, th en Adaptive
Server supports file system devices
up to 4TB
Configuration Gu ide 13
Adaptive Server specifications
Maximum number of users
2146484223
per database
Maximum number of
1032193
groups per database
Table specifications
User object s pe r da ta ba s e 2
31
– 255
Indexes per table 250 (one clustered index)
Rows per table Limited by available
storage
Columns per composite
31
index
Creation of clustered i nd ex 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 siz e 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 qu ery
64 Maximum of 50 user t abl 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 tables and 14
work tables on every side of the
union, for a total of u p to 256 tables
across all sides of the union.
Maximum number of databases
participating in one transaction
Unlimited Includes database where
transaction began, all database s
changed during tr ansaction, and
tempdb, if it is used for results or
worktables
14 Adaptive Server Enterprise
Loading...