Sybase Adaptive Server Anywhere Reference
Adaptive Server™ Anywhere
Reference
Last modified: November 2000
Part Number: MC0058
Copyright © 2001 Sybase, Inc. All rights reserved.
Information in this manual may change without notice and does not represent a commitment on the part of
Sybase, Inc. and its subsidiaries.
Sybase, Inc. provides the software described in this manual under a Sybase License Agreement. The software may be
used only in accordance with the terms of the agreem ent.
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 Enterprise, Adaptive Server Enterprise Monitor, Adaptive
Server Enterprise Replication, Adaptive Server Everywhere, Adaptive Server IQ, Adaptive Warehouse, AnswerBase,
Anywhere Studio, Application Manager, AppModeler, APT-Build, APT-Edit, APT-Execute, APT-FORMS, APT-Library,
APT-Translator, APT Workbench, ASEP, Backup Server, BayCam, Bit-Wise, Certified PowerBuilder Developer, Certified
SYBASE Professional, Certified SYBASE Professional (logo), ClearConnect, Client Services, Client-Library, CodeBank,
Cohesion, Column Design, ComponentPack, Connection Manager, CSP, Data Pipeline, Data Workbench, DataArchitect,
Database Analyzer, DataExpress, DataServer, DataWindow, DB-Library, dbQueue, Developers Workbench, Direct
Connect Anywhere, DirectConnect, Distribution Director, Dynamo, E-Anywhere, E-Whatever, Electronic Case
Management, Embedded SQL, EMS, Enterprise Application Server, Enterprise Application Studio, Enterprise
Client/Server, Enterprise Connect, Enterprise Data Studio, Enterprise Manager, Enterprise SQL Server Manager, Enterprise
Work Architecture, Enterprise Work Designer, E nterprise Work Modeler, EWA, Financial Fusion, First Impression,
Formula One, Gateway Manager, GeoPoint, ImpactNow, InfoMaker, Information Anywhere, Information Everywhere,
InformationConnect, InstaHelp, Intellidex, InternetBuilder, iScript, Jaguar CTS, jConnect for JDBC, KnowledgeBase,
Logical Memory Manager, MainframeConnect, Maintenance Express, MAP, MDI Access Server, MDI Database Gateway,
media.splash, MetaWorks, MethodSet, MobiCATS, MySupport, Net-Gateway, Net-Library, NetImpact, Next Generation
Learning, Next Generation Learning Studio, O DEVICE, OASiS, OASiS (logo), ObjectConnect, ObjectCycle,
OmniConnect, OmniSQL Access Module, OmniSQL Toolkit, Open Client, Open Client/Server, Open Client/Server
Interfaces, Open ClientConnect, Open Gateway, Open Server, Open ServerConnect, Open Solutions, Optima++,
Partnerships that Work, PB-Gen, PC APT Execute, PC DB-Net, PC Net Library, PhysicalArchitect, Power Through
Knowledge, Power++, power.stop, PowerAMC, PowerBuilder, PowerBuilder Foundation Class Library, PowerDesigner,
PowerDimensions, PowerDynamo, PowerJ, PowerScript, PowerSite, PowerSocket, Powersoft, Powersoft Portfolio,
Powersoft Professional, PowerStage, PowerStudio, PowerTips, PowerWare Desktop, PowerWare Enterprise,
ProcessAnalyst, Relational Beans, Replication Agent, Replication Driver, Replication Server, Replication Server Manager,
Replication Toolkit, Report Workbench, Report-Execute, Resource Manager, RW-DisplayLib, RW-Library, S-Designor,
S Designor, SAFE, SAFE/PRO, SDF, Secure SQL Server, Secure SQL Toolset, Security Guardian, SKILS, smart.partners,
smart.parts, smart.script, 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 Server
SNMP SubAgent, SQL Server/CFT, SQL Server/DBM, SQL SMART, SQL Station, SQL Toolset, SQLJ, Startup.Com,
STEP, SupportNow, Sybase Central, Sybase Client/Server Interfaces, Sybase Development Framework, Sybase Financial
Server, Sybase Gateways, Sybase Learning Connection, Sybase SQL Desktop, Sybase SQL Lifecycle, Sybase SQL
Workgroup, Sybase Synergy Program, Sybase User Workbench, Sybase Virtual Server Architecture, Sybase MPP,
SybaseWare, Syber Financial, SyberAssist, SyBooks, System XI (logo), System 10, System 11, SystemTools, Tabular Data
Stream, The Enterprise Client/S erver Company, The Extensible Software Platform, The Future Is Wide Op en,
The Learning Con nection, The Mode l F o r Client/Server So l utio ns , The Online Infor m atio n Ce nter, Transact-S Q L,
Translation Toolkit, Turning Imagination Into Reality, UltraLite, UNIBOM, Unilib, Uninull, Unisep, Unistring,
URK Runtime Kit for UniCode, Viewer, Visual Components, VisualSpeller, VisualWriter, VQL, Warehouse Control
Center, Warehouse Studio, Warehouse WORKS, WarehouseArchitect, Watcom, Watcom SQL Server, Watcom SQL,
Web.PB, Web.SQL, Web Deployment Kit, WebSights, WebViewer, WorkGroup SQL Server, XA-Library, XA-Server,
and XP Server are trademarks of Sybase, Inc. or its subsidiaries.
All other trademarks are property of their respective owners.
Last modified: March 2000. Part Number: MC0058.
Contents
About This Manual............................................................xi
Related documentation............................................................xii
Documentation conventions....................................................xiii
The sample database..............................................................xvi
1 File Locations and Installation Settings........................... 1
Installation directory structure...................................................2
How Adaptive Server Anywhere locates files ...........................4
Environment variables ..............................................................6
Registry entries and INI files...................................................10
2 The Database Server .......................................................13
The database server...............................................................14
3 Connection and Communication Parameters................ 45
Connection parameters...........................................................46
Network communications parameters ....................................65
4 Database Administration Utilities................................... 75
Administration utilities overvi e w..............................................77
The Backup utility....................................................................78
The Collation utility..................................................................82
The Compression utility...........................................................85
The Console utility...................................................................87
The Data Source utility ............................................................89
The Erase utility ......................................................................94
The Information utility..............................................................96
The Initialization utility.............................................................98
The Interactive SQL utility.....................................................106
The License utility.................................................................109
The Log Transfer Manager...................................................111
The Log Translation utility.....................................................117
The Ping utility.......................................................................122
The REBUILD utility..............................................................124
iii
The Server Location utility ....................................................125
The Service Creation utility...................................................126
The Spawn utility...................................................................129
The Stop utility ......................................................................130
The Transaction Log utility....................................................132
The Uncompression utility.....................................................136
The Unload utility..................................................................138
The Upgrade utility................................................................145
The Validation utility..............................................................148
The Write File utility ..............................................................151
5 Database Options.......................................................... 155
Introduction to database options...........................................156
General database options.....................................................161
Transact-SQL and SQL/92 compatibility options..................163
Replication options................................................................166
Interactive SQL options.........................................................167
Alphabetical list of options ....................................................169
6 SQL Language Elements .............................................. 219
Keywords ..............................................................................220
Identifiers...............................................................................223
Strings...................................................................................224
Operators..............................................................................225
Expressions...........................................................................230
Search conditions .................................................................239
Special values.......................................................................247
Variables...............................................................................250
Comments .............................................................................259
NULL value ...........................................................................260
7 SQL Data Types............................................................. 263
Character data types.............................................................264
Numeric data types...............................................................268
Money data types..................................................................275
Bit data type..........................................................................276
Date and time data types......................................................277
Binary data types ..................................................................284
Domains................................................................................286
Java class data types............................................................288
Data type conversions ..........................................................293
Java / SQL data type conversion..........................................294
Year 2000 compliance ..........................................................297
iv
8 SQL Functions............................................................... 303
Function types.......................................................................304
Alphabetical list of f unctions..................................................313
9 SQL Statements............................................................. 377
Using the SQL statement reference......................................378
ALLOCATE DESCRIPTOR statement [ESQL].....................381
ALTER DATABASE statement .............................................383
ALTER DBSPACE statement ...............................................385
ALTER EVENT statement.....................................................387
ALTER PROCEDURE statement..........................................389
ALTER SERVER statement..................................................390
ALTER TABLE statement.....................................................392
ALTER TRIGGER statement................................................398
ALTER VIEW statement .......................................................399
ALTER WRITEFILE statement .............................................400
BACKUP statement ..............................................................401
BEGIN statement ..................................................................404
BEGIN TRANSACTION statement.......................................407
CALL statement ....................................................................410
CASE statement....................................................................412
CHECKPOINT statement......................................................414
CLEAR statement [Interactive SQL] .....................................415
CLOSE statement [ESQL] [SP].............................................416
COMMENT statement...........................................................418
COMMIT statement...............................................................420
CONFIGURE statement [Interactive SQL]............................422
CONNECT statement [ESQL] [Interactive SQL]...................423
CREATE COMPRESSED DATABASE statement................426
CREATE DATABASE statement ..........................................427
CREATE DBSPACE statement ............................................431
CREATE DOMAIN statement...............................................433
CREATE EVENT statement..................................................435
CREATE EXISTING TABLE statement ................................441
CREATE EXTERNLOGIN statement....................................443
CREATE FUNCTION statement...........................................445
CREATE INDEX statement...................................................448
CREATE MESSAGE statement [T-SQL]..............................452
CREATE PROCEDURE statement.......................................453
CREATE PROCEDURE statement [T-SQL].........................460
CREATE SCHEMA statement..............................................462
CREATE SERVER statement...............................................464
CREATE TABLE statement..................................................466
CREATE TRIGGER statement.............................................477
CREATE TRIGGER statement [T-SQL] ...............................480
v
CREATE VARIABLE statement............................................481
CREATE VIEW statement ....................................................482
CREATE WRITEFILE statement ..........................................484
DEALLOCATE DESCRIPTOR statement [ESQL]................485
Declaration section [ESQL]...................................................486
DECLARE statement ............................................................487
DECLARE CURSOR statement [ESQL] [SP].......................488
DECLARE CURSOR statement [T-SQL]..............................493
DECLARE LOCAL TEMPORARY TABLE
statement ..............................................................................495
DELETE statement ...............................................................496
DELETE (positioned) statement [ESQL] [SP].......................498
DESCRIBE statement [ESQL]..............................................500
DISCONNECT statement
[ESQL][Interactive SQL]........................................................504
DROP statement...................................................................505
DROP DATABASE statement...............................................507
DROP CONNECTION statement .........................................508
DROP EXTERNLOGIN statement........................................509
DROP OPTIMIZER STATISTICS statement ........................510
DROP SERVER statement...................................................511
DROP STATEMENT statement [ESQL] ...............................512
DROP VARIABLE statement ................................................513
EXECUTE statement [ESQL]................................................514
EXECUTE statement [T-SQL]...............................................516
EXECUTE IMMEDIATE statement [SP] ...............................518
EXIT statement [Interactive SQL] .........................................520
EXPLAIN statement [ESQL] .................................................521
FETCH statement [ESQL] [SP].............................................523
FOR statement......................................................................528
FORWARD TO statement.....................................................530
FROM clause........................................................................532
GET DATA statement [ESQL]...............................................535
GET DESCRIPTOR statement [ESQL] ................................537
GET OPTION statement [ESQL] ..........................................538
GOTO statement [T-SQL].....................................................539
GRANT statement.................................................................540
HELP statement [Interactive SQL]........................................544
IF statement..........................................................................545
IF statement [T-SQL] ............................................................547
INCLUDE statement [ESQL].................................................549
INPUT statement [Interactive SQL] ......................................550
INSERT statement................................................................554
INSTALL statement...............................................................556
LEAVE statement..................................................................558
LOAD TABLE statement.......................................................560
vi
LOCK TABLE statement.......................................................565
LOOP statement ...................................................................567
MESSAGE statement ...........................................................568
OPEN statement [ESQL] [SP]...............................................570
OUTPUT statement [Interactive SQL]...................................573
PARAMETERS statement [Interactive SQL] ........................577
PREPARE statement [ESQL] ...............................................578
PREPARE TO COMMIT statement ......................................580
PRINT statement [T-SQL].....................................................581
PUT statement [ESQL] .........................................................582
RAISERROR statement [T-SQL]..........................................584
READ statement [Interactive SQL] .......................................586
READTEXT statement [T-SQL] ............................................587
RELEASE SAVEPOINT statement.......................................588
REMOVE statement..............................................................589
RESIGNAL statement ...........................................................590
RESTORE statement............................................................591
RESUME statement..............................................................592
RETURN statement ..............................................................593
REVOKE statement ..............................................................595
ROLLBACK statement ..........................................................597
ROLLBACK TO SAVEPOINT statement ..............................598
ROLLBACK TRIGGER statement.........................................599
SAVEPOINT statement.........................................................600
SELECT statement ...............................................................601
SET statement ......................................................................605
SET statement [T-SQL].........................................................607
SET CONNECTION statement
[Interactive SQL][ESQL]........................................................610
SET DESCRIPTOR statement [ESQL].................................611
SET OPTION statement .......................................................612
SET OPTION statement [Interactive SQL] ...........................615
SET SQLCA statement [ESQL] ............................................616
SETUSER statement ............................................................617
SIGNAL statement ................................................................619
START DATABASE statement .............................................620
START ENGINE statement [Interactive SQL].......................622
START JAVA statement .......................................................623
START LOGGING statement [Interactive SQL]....................624
STOP DATABASE statement...............................................625
STOP ENGINE statement.....................................................626
STOP JAVA statement .........................................................627
STOP LOGGING statement [Interactive SQL]......................628
SYSTEM statement [Interactive SQL]...................................629
TRIGGER EVENT statement................................................630
TRUNCATE TABLE statement.............................................631
vii
UNION operation ..................................................................633
UNLOAD statement ..............................................................634
UNLOAD TABLE statement..................................................635
UPDATE statement...............................................................637
UPDATE (positioned) statement...........................................641
VALIDATE INDEX statement................................................643
VALIDATE TABLE statement ...............................................644
WHENEVER statement [ESQL]............................................646
WHILE statement [T-SQL]....................................................647
WRITETEXT statement [T-SQL]...........................................648
10 Database Error Messages............................................. 649
Error messages indexed b y Adapt i ve Ser ver
Anywhere SQLCODE ...........................................................650
Error messages indexed by SQLSTATE..............................669
Error messages indexed by Sybase error code....................687
Alphabetic list of error messages..........................................711
Internal errors (assertion failed)............................................929
11 SQL Preprocessor Error Messages.............................. 931
SQL Preprocessor error messages indexed by
error message value .............................................................932
SQLPP errors........................................................................936
12 Differences from Other SQL Dialects........................... 953
Adaptive Server Anywhere SQL features.............................954
13 Physical Limitations...................................................... 957
Size and number limitations..................................................958
14 System Procedures and Functions .............................. 961
System procedure overview..................................................962
System and catalog stored procedures ................................963
System extended stored procedures....................................981
Adaptive Server Enterprise system and catalog
procedures............................................................................988
15 System Tables............................................................... 991
System tables diagram .........................................................993
System table descriptions.....................................................995
DUMMY system table ...........................................................996
SYSARTICLE system table ..................................................997
viii
SYSARTICLECOL system table...........................................998
SYSCAPABILITY system table.............................................999
SYSCAPABILITYNAME system table ................................1000
SYSCOLLATION system table...........................................1001
SYSCOLLATIONMAPPINGS system table ........................1002
SYSCOLPERM system table..............................................1003
SYSCOLUMN system table................................................1004
SYSDOMAIN system table.................................................1006
SYSEXTENT system table .................................................1007
SYSEXTERNLOGINS system table ...................................1008
SYSFILE system table ........................................................1009
SYSFKCOL system table....................................................1010
SYSFOREIGNKEY system table........................................1011
SYSGROUP system table ..................................................1013
SYSINDEX system table.....................................................1014
SYSINFO system table .......................................................1016
SYSIXCOL system table.....................................................1018
SYSJAR system table.........................................................1019
SYSJARCOMPONENT system table .................................1020
SYSJAVACLASS system table...........................................1021
SYSLOGIN system table ....................................................1023
SYSOPTION system table..................................................1024
SYSPROCEDURE system table.........................................1025
SYSPROCPARM system table...........................................1026
SYSPROCPERM system table...........................................1028
SYSPUBLICATION system table........................................1029
SYSREMOTEOPTION system table ..................................1030
SYSREMOTEOPTIONTYPE system table.........................1031
SYSREMOTETYPE system table.......................................1032
SYSREMOTEUSER system table......................................1033
SYSSERVERS system table ..............................................1035
SYSSQLSERVERTYPE system table ................................1036
SYSSUBSCRIPTION system table.....................................1037
SYSSYNC system table......................................................1038
SYSTABLE system table....................................................1039
SYSTABLEPERM system table..........................................1041
SYSTRIGGER system table...............................................1043
SYSTYPEMAP system table ..............................................1045
SYSUSERMESSAGES system table .................................1046
SYSUSERPERM system table ...........................................1047
SYSUSERTYPE system table............................................1049
16 System Views............................................................... 1051
SYSARTICLECOLS system view.......................................1053
SYSARTICLES system view...............................................1054
ix
SYSCAPABILITIES system view........................................1055
SYSCATALOG system view...............................................1056
SYSCOLAUTH system view...............................................1057
SYSCOLUMNS system view..............................................1058
SYSFOREIGNKEYS system view......................................1059
SYSGROUPS system view.................................................1060
SYSINDEXES system view ................................................1061
SYSOPTIONS system view................................................1062
SYSPROCAUTH system view............................................1063
SYSPROCPARMS system view.........................................1064
SYSPUBLICATIONS system view......................................1065
SYSREMOTEOPTIONS system view ................................1066
SYSREMOTETYPES system views...................................1067
SYSREMOTEUSERS system view ....................................1068
SYSSUBSCRIPTIONS system view...................................1069
SYSTABAUTH system view ...............................................1070
SYSTRIGGERS system view .............................................1071
SYSUSERAUTH system view ............................................1072
SYSUSERLIST system view...............................................1073
SYSUSEROPTIONS system view......................................1074
SYSUSERPERMS system view .........................................1075
SYSVIEWS system view.....................................................1076
Views for Transact-SQL Compatibility................................1077
A Database Performance and Connection Properties...1081
Database performance statistics ........................................1082
Database properties............................................................1090
Index..............................................................................1103
x
About This Manual
Subject
Audience
Contents
This manual provides reference material for all aspects of Adaptive Server
Anywhere, including SQL statements, administration utilities, error
messages, system tables, and so on.
While other manuals provide more motivation and co ntext for how to carry
out particular tasks, this manual is the place to look for complete listings of
available syntax, utility command-line options, and so on.
This manual is for all users of Adaptive Server Anywhere. It is to be used in
conjunction with other manuals in the documentation set.
Topic Page
Related documentation xii
Documentation conventions xiii
The sample database xvi
xi
Related documentation
Adaptive Server Anywhere is a part of SQL Anywhere Studio. For an
overview of the different components of SQL Anywhere Studio, see
Introducing SQL Anywhere Studio.
The Adaptive Server Anywhere documentation consis ts of the following
books:
♦
Getting Started Intended for all users of Adaptive Server Anywhere,
this book describes the following:
♦ New features in Adaptive Server Anywhere
♦ Behavior change s from previous releases
♦ Upgrade procedures
♦ Introductory material for beginning users.
♦
Programming Interfaces Guide Intended for application developers
writing programs that directly access the ODBC, Embedded SQL, or
Open Client interfaces, this book describes how to develop applications
for Adaptive Server Anywhere.
This book is not required for users of Application Development tools
with built-in ODBC support, such as Sybase PowerBuilder.
♦
User’s Guide A comprehensive guide to using SQL, administerin g
databases, and using Adaptive Server Anywhere features.
xii
♦
Quick Reference A handy printed booklet with complete SQL syntax
and other key reference material in a concise format.
♦
Read Me First (UNIX only) A separate booklet is provided with UNIX
versions of Adaptive Server Anywhere, describing installation and
adding some UNIX-specific notes.
The format of these books (printed or online) may depend on the product in
which you o btained Adap tive Server A nywhere. Dep ending on which
package you have purchased, you may have additional books describing
other components of your product.
Documentation conventions
This section lists the typographic and graphical conventions used in this
documentation.
Syntax conventions
The following conventions are used in the SQL syntax descriptions:
Keywords All SQL keywords are shown in UPPER CASE. However,
♦
SQL keywords are case insensitive, so you can enter keywords in any
case you wish; SELECT is the same as Select is the same as s e lect.
♦
Placeholders Items that must be replaced with appropriate identifiers
or expressions are shown in italics.
♦
Continuation Lines beginning with ... are a continuation of the
statements from the previous line.
♦
Repeating items Lists of repeating items appear with an element of
the list followed by an ellipsis (three dots). One or more list elements are
allowed. If you specify more than one, they must be separated by
commas.
♦
Optional portions Optional portions of a statement are enclosed by
square brackets. For example,
RELEASE SAVEPOINT [
savepoint-name
]
indicates that the savepoint-name is optional. Do not type the square
brackets.
♦
Options When none or only one of a list of items must be chosen, the
items are separated by vertical bars and the list enclosed in square
brackets. For example,
[ ASC | DESC ]
indicates that you can choose one of ASC, DESC, or neither. The square
brackets should not be typed.
♦
Alternatives When precisely one of the options must be chosen, the
alternatives are enclosed in curly braces. For example,
QUOTES { ON | OFF }
indicates that exactly one of ON or OFF must be provided. Do not type
the braces.
xiii
Graphic icons
The following icons are used in this docume ntation:
Icon Meaning
A client application.
If the icon is used to represent a particular application
or kind of application, the name is indicated in the
bottom right corner.
A database server, such as Sybase Adaptive Server
Anywhere or Adaptive Server Enterprise.
If the icon is used to represent a particular kind of
database server, such as Adaptive Server Anywhere,
the name is indicated in the bottom right corner.
An UltraLite application and database server.
In UltraLite, the database server and the application
are part of the same process.
A database.
In some high-level diagrams, the icon may be used to
represent both the database and the database server
that manages it.
xiv
Replication or synchronization middleware.
These pieces of software assist in sharing data among
databases. Examples include the MobiLink
Synchronization Server, the SQL Remote Message
Agent, and the Replication Agent (Log Transfer
Manager) for use with Replicat ion Server.
If the particular kind of middleware is not obvious
from the context of the diagram, the name is indicated
in the bottom right hand corner.
A Sybase Replication Server.
Installed files
The following terms are used throughout the manual:
♦
Installation directory The directory into which you install Adaptive
Server Anywhere.
♦
Executable directory The executables and other files for each
operating system are held in an executable subdirectory of the
installation directory. This subdirectory has the following name:
♦
Windows NT and Windows 95/98 win32
♦
UNIX bin
♦
Windows 3.x win
♦
NetWare and Windows CE The executables are held in the
Adaptive Server Anywhere installation directory itself on these
platforms.
xv
The sample database
There is a sample database included with Adaptive Server Anywhere. Many
of the examples throughout the documentation use this sample database.
The sample database represents a small company. It contains internal
information about the company (e mployees, departments, and financial data)
as well as product information (products), sales information (sales orders,
customers, and contacts), and financial information (fin_code, fin_data).
The following figure shows the tab les in the sample database and how they
are related to each other.
asademo.db
id <pk> integer
product
name char(15)
description char(30)
size char(18)
color char(6)
quantity integer
unit_price numeric(15,2)
customer
id <pk> integer
fname char(15)
lname char(20)
address char(35)
city char(20)
state char(2)
zip char(10)
phone char(12)
company_name char(35)
contact
id <pk> integer
last_name char(15)
first_name char(15)
title char(2)
street char(30)
city char(20)
state char(2)
zip char(5)
phone char(10)
fax char(10)
id = prod_id
id = cust_id
sales_order_items
id <pk,fk> integer
line_id <pk> smallint
prod_id <fk> integer
quantity integer
ship_date date
id = id
emp_id = sales_rep
sales_order
id <pk> integer
cust_id <fk> integer
order_date date
fin_code_id <fk> char(2)
region char(7)
sales_rep <fk> integer
code = fin_code_id
fin_code
code <pk> char(2)
type char(10)
description char(50)
code = code
fin_data
year <pk> char(4)
quarter <pk> char(2)
code <pk,fk> char(2)
amount numeric(9)
emp_id <pk> integer
employee
manager_id integer
emp_fname char(20)
emp_lname char(20)
dept_id <fk> integer
street char(40)
city char(20)
state char(4)
zip_code char(9)
phone char(10)
status char(1)
ss_number char(11)
salary numeric(20,3)
start_date date
termination_date date
birth_date date
bene_health_ins char(1)
bene_life_ins char(1)
bene_day_care char(1)
sex char(1)
dept_id = dept_id
emp_id = dept_head_id
department
dept_id <pk> integer
dept_name char(40)
dept_head_id <fk> integer
xvi
The sample database is held in a file named
your installation directory.
asademo.db
, and is located in
xvii
xviii
CHAPTER 1
File Locations and Installation Settings
About this chapter
Contents
This chapter describes the installation and ope rating system settings used by
Adaptive Server Anywhere. Depending on the operating system, these
settings may be stored as environment variables, initialization file entries, or
registry entries.
Topic Page
Installation directory structure 2
How Adaptive Server Anywhere locates files 4
Environment variables 6
Registry entries and INI files 10
1
Installation directory structure
Installation directory structure
When you install Adaptive Server Any where, several directories may be
created. Some of the files in these directories are essential, and others are
not. This section describes the directory structure.
Adaptive Server Anywhere software, whether you receive it as a product or
bundled as part of another product, is installed under a single installation
directory. The tools provided with the Adaptive Server Anywhere product,
however, are installed in other directories. This section describes only the
installation directory structure for Adaptive Server Anywhere itself.
The Adaptive
Server Anywhere
installation
directory
The Adaptive Server Anywhere installation d irectory itself holds several
items, including the followin g:
♦
The sample database The sample database is held in the file
asademo.db
♦
Read Me First A Read Me First file named
.
readme.txt
holds late-
breaking information.
For platforms other than Novell NetWare and Windows CE, there are several
directories under the installation directory:
Executable directories There is a separate directory for each
♦
operating syste m, which holds executables, dynamic link libraries, and
help files.
If you are using Windows 95/98, or NT, these files are installed in the
win32
directory. If you are using UNIX, they are installed in the
bin
directory. On NetWare, the executables are stored in the installation
directory itself.
You will not have all these directories on your machine; you will have
only the ones required for the operating system version you ins talled.
♦
Java directory Java base classes are stored in this directory.
♦
ProcDebug directory The stored procedure debugger is stor ed in this
directory.
♦
Scripts directory The scripts directory contains SQL scripts that are
used by the database administration utilities and as examples. With the
exception of specific scripts (
custom.sql, custmap.sql
), do not edit these
scripts. If the scripts directory is not present, the administration utilities
will not work.
♦
Examples directories There are separate directories for C (
Java (
2
jxmp
) examples.
cxmp
) and
Chapter 1 File Locations and Installation Settings
h directory The h directory contains header files for ESQL and ODBC
♦
database development.
Novell NetWare file
locations
Windows CE file
locations
On Novell NetWare, all files are installed to a single directory on the server.
Throughout this documentation, when reference is made to files in
subdirectories of the installation directory, the file on NetWare is in the
installation directory itself.
On Windows CE, all files are installed to the installation directory, and no
subdirectories are created. The exception is that all DLLs are installed into
the
\Windows
the prefix
directory. To make identification easy, the DLL names all have
ASA_
.
3
How Adaptive Server Anywhere locates files
How Adaptive Server Anywhere locates files
The client library and the database server need to locate files for two main
purposes:
♦ DLLs and initialization files are required to run Adaptive Server
Anywhere. If an incorrect DLL is located, there is the possibility of
version mismatch errors.
♦ Some files are specified in SQL statements and need to be located at run
time, such as INSTALL or LOAD TABLE.
Examples of SQL statements that use file names include the following:
♦
INSTALL statement The name of the file that holds Java classes.
LOAD TABLE and UNLOAD TABLE statements The name of the file
♦
from which data should be loaded or to which the data should be
unloaded.
♦
CREATE DATABASE statement A file name is needed for this
statement and similar statements that can create files (such as CREATE
WRITEFILE).
In some cases, Adaptive Server Anywhere uses a simple algorithm to locate
files. In other cases, a more extensive search is carried out.
Simple file
searching
Extensive file
searching
4
In many SQL statements (such as LOAD TABLE, or CREATE
DATABASE), the file name is interpreted as relative to the current working
directory of the database server.
Also, when a database server is started and a database file name (DBF
parameter) is supplied, the path is interpreted as relative to the current
working directory.
Adaptive Server Anywhere programs, including the database server and
administration utilities, carry out a more extensive search for required files,
such as DLLs or shared libraries. In these cases, Adaptive Server Anywhere
programs look for files in the following order:
1
Executable directory Holds the program executable file.
Related directories Holds directories with the following paths relative
2
to the program executable directory:
♦ Parent of the executable directory
♦ A child of the parent directory named
does not search in this location.
scripts
. The UNIX server
Chapter 1 File Locations and Installation Settings
Current working directory When a program is started, it has a
3
current working directory (the directory from which it is started). This
directory is searched for required files.
4
Location registry entry On installation onto Windows 95/98, and NT,
Adaptive Server Anywhere adds a LOCATION registry entry. The
indicated directory is searched, followed by:
♦ A child named
♦ A child with the operating system name (
System specific directories This includes directories where common
5
operating system files are held, such as the
Windows\system
scripts
win32, win
Windows
, and so on)
directory and the
directory on Windows and Windows NT operating
systems.
6
CLASSPATH directories For Java files, directories listed in the
CLASSPATH environment variable are searched to locate files.
7
PATH directories Directories in the system path and the user’s path
are searched to locate files.
5
Environment variables
Environment variables
Adaptive Server Anywhere uses a set of environment variables to store
various types of information. Not all variables need to be set in all
circumstances. These environment variables are listed in this section.
Setting environment variables
The way you set an environment variable depends on the operating system
you are using.
v To set an environment variable (Windows NT):
1 Right click on My Computer and select Properties from the popup menu.
2 Click the Environment tab. If the environment variable doe s not already
exist, type variable and its value in the spaces provided, and click Set.
If the variable does exist, select it from the list of System Variables or
User Variables, and make any modifications in the Value field. Click Set
to make the setting.
v To set an environment variable (UNIX):
♦ In one of your startup files (
variable.
In some shells (such as sh, bash, ksh) the line is as follows:
export VARIABLE=value
In other shells (such as csh, tsch) the line is as follows:
setenv VARIABLE value
ASTMP environment variable
Syntax
Default
Description
6
ASTMP=
None.
The database server checks the value of the ASTMP environment variable to
determine where to hold the temporary file. If the ASTMP environment
variable does not exist, then the first of the TMP, TMPDIR, and TEMP
environment variables to exist is used.
directory-name
.cshrc, .shrc, .login
), add a line that sets the
Chapter 1 File Locations and Installation Settings
In many circumstances, ASTMP is not needed. It can be of use in securityconscious environments when running the database server as a service, to
enable you to hold the temporary file in a directory that cannot be accessed
by other programs.
LD_LIBRARY_PATH environment variable [UNIX]
Syntax
Description
LD_LIBRARY_PATH=
The LD_LIBRARY_PATH environment variable is used on UNIX only. It is
modified by the installation program to include the directories where
Adaptive Server Anywhere libraries are located.
The executables are located in the
directory (for example,
PATH environment variable
Syntax
Description
PATH=
installation_path
The PATH environment variable is modified by the installation program to
include the directories where Adaptive Server Anywhere executables are
located.
The executables are located in a subdirectory of the installation directory.
In addition, if you are using other Sybase app lications, the
SYBASE\dll
directories are added to your path.
On UNIX, each user must have the directory holding the executables
(
/opt/SYBSasa7/bin
SATMP environment variable
installation_path
lib
subdirectory of the installation
/opt/SYBSasa7/lib
) added to their path.
/lib
).
SYBASE\bin
and
Syntax
Description
SATMP=
temp_directory
The SATMP environment variable is used by UNIX versions of Adaptive
Server Anywhere to indicate a directory where temporary files are kept.
If more than one database server is running on a machine, each user needs
their own temporary directory. Typically, this is set to
/tmp/.userid
, so that
each user has their own directory and conflicts are avoided.
7
Environment variables
SQLCONNECT environment variable
Syntax
Description
SQLCONNECT=
The SQLCONNECT environment variable is optional, and is not set by the
parameter#value
installation program.
SQLCONNECT specifies connection parameters that are used by several of
the database administration utilities when connecting to a database server.
This string is a list of parameter settings, of the form parameter=value,
delimited by semicolons.
The number sign "#" is an alternative to the equals sign, and should be used
if you are setting the connection parameters string in the SQLCONNECT
environment variable. Using "=" inside an environment variable setting is a
syntax erro r. The = sign is allowe d only in Wind ows NT.
$ For a description of the connection parameters, see "Connection
parameters" on page 64 of the book ASA User’s Guide.
SQLLOCALE environment variable
Syntax
See also
Description
SQLLOCALE= Charset=
"Setting the SQLLOCALE environment variable" on page 302 of the book
ASA User’s Guide
The SQLLOCALE environment variable is not set by the installation
program, and is required only in multi-character-set environments.
cslabel
; ...
;Language=
langlabel
;CollationLabel=
colabel
The SQLLOCALE environment variable is a single string that consists of
three semi-colon-separated assignments. The assignments set out the
character set, language, and collation of the environment.
$ For a list of supported character set labels, see "Setting the
SQLLOCALE environment variable" on page 302 of the book ASA User’s
Guide.
SQLPATH environment variable
Syntax
Description
8
SQLPATH=
path
;...
The SQLPATH environment variable is optional, and is not set by the
installation program.
Interactive SQL searches along SQLPATH for command files and Help files
before searching the system path.
Chapter 1 File Locations and Installation Settings
SQLREMOTE environment variable
Syntax
Description
SQLREMOTE=
The SQLREMOTE environment variable is optio nal, and is not set by the
path
installation program.
Addresses for the FILE message link in SQL Remote are subdirectories of
the SQLREMOTE environment variable. This variable should point to a
shared directory.
On 32-bit Windows, an alternative to settin g the SQLREMOTE environment
variable is to set the
directory.
SYBASE environment variable
Syntax
Description
SYBASE=
The SYBASE variable marks the home directory for installation of some
Sybase applications, including Adaptive Server Enterprise and utilities such
as
dsedit
Anywhere together with other members of the Adaptive Server family.
path
. You need this variable only if you are using Adaptive Server
TEMP environment variable
Syntax
TMP=
path
TMPDIR=
TEMP=
path
path
SQL Remote\Directory
registry entry to the proper root
Description
The database server creates a temporary file for various operations such as
sorting and performing unions. Temporary files are placed in the directory
specified by the TMP, TMPDIR, or TEMP environment variable. Adaptive
Server Anywhere takes the first one of the three that it finds.
If none of the environment variables is defined, temporary files are placed in
the current working directory of the server.
On UNIX, the SATMP variable is used instead of the TEMP environment
variable.
9
Registry entries and INI files
Registry entries and INI files
On Windows 95/98 and Windows NT operating systems, Adaptive Server
Anywhere uses several registry settings. On Windows 3.x, UNIX, and
Netware 3.11, these settings are held in initialization files instead.
These settings are made for you by the software, and in general operation
you should not need to access the registry. The settings are provided here for
those people who make modifications to their operating environment.
Current user and local machine settings
Some operating s ystems, such as Windows NT, hold two levels of system
settings. Some settings are specific to an individual user, and are used only
when that user is logged on; these settings are called current user settings.
Some settings are global to the machine, and are available no matter which
user is logged on; these are called local machine settings. You must have
administrator permissions on your machine to make local machine settings.
Adaptive Server Anywhere permits both current user and local machine
settings. For Windows NT, these are held in the HKEY_CURRENT_USER
registry and HKEY_LOCAL_MACHINE registry, respectively.
Current user takes
precedence
When local
machine settings
are needed
10
If a setting is made in both current user and local machine registries, the
current user setting takes precedence over the local machine setting.
If you are running an Adaptive Server Anywhere program as a service under
Windows NT, you should ensure that the settings are made at the local
machine level.
Services can continue to run under a special account when you log off a
machine, as long as you do not shut the machine down entirely. They can be
made independent of individual accounts, and therefore need access to local
machine settings.
In addition to Adaptive Server Anywhere programs, some Web servers run
as services. You must set local machine se ttings in order for PowerDynamo
to work with such a Web server.
In general, the use of local machine settings is recommended.
Registry structure
On Windows 95/98 and Windows NT, you can access the registry directly
with the registry editor. The Adaptive Server Anywhere registry entries are
held in either the HKEY_CURRENT_USER or
HKEY_LOCAL_MACHINE registries, in the followin g location:
Software
Sybase
Adaptive Server Anywhere
7.0
Sybase Central
4.0
Profiles
Providers
Registry settings on installation
The installation program makes the following registry settings in the Sybase
registry:
♦
Location In the
holds the installation directory location. For example:
Location "c:\sybase\asa7"
Chapter 1 File Locations and Installation Settings
Adaptive Server Anywhere\7.0
registry, this entry
♦ Language In the
Adaptive Server Anywhere\7.0
registry, this entry
holds a two-letter code indicating the current language for message s and
errors. For example:
Language "EN"
The default setting is English (EN). The installation program sets this
entry only if the software is installed for a langua ge other than English.
♦
Providers In the
Sybase Central\Providers
registry, this entry stores
the file names of installed plug-ins for Sybase Ce ntral. Adaptive Server
Anywhere has its own Sybase Central plug-in:
Adaptive Server Anywhere 7.0
"c:\sybase\asa7\win32\scasany7.dll"
11
Registry entries and INI files
12
Loading...