Sybase Adaptive Server IQ 12.4.2 Instruction Manual

Administration and Performance Guide
Adaptive Server® IQ
12.4.2

DOCUMENT ID: 38152-01-1242-01
LAST REVISED: April 2000
Copyright © 1989-2000 by Sybase, Inc. All rights reserved.
This publication pertains to Sybase database management software and to any subsequent release until otherwise indicated in new
editions or technical notes. Information in this document is subject to change without notice. The software described herein is furnished
under a license agreement, and it may be used or copied only in accordance with the terms of that agreement.
To order additional documents, U.S. and Canadian customers should call Customer Fulfillment at [phone], fax [phone].
Customers in other countries with a U.S. license agreement may contact Customer Fulfillment 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, the 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 Workbench, APT-Build, APT-Edit, APT-Execute, APT-FORMS, APT-Translator, APT-Library, Backup Server,
ClearConnect, Client-Library, Client Services, Data Pipeline, Data Workbench, DataArchitect, Database Analyzer, DataExpress,
DataServer, DataWindow, DB-Library, dbQueue, Developers Workbench, Direct Connect Anywhere, DirectConnect, Distribution
Director, E-Anywhere, E-Whatever, 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, Enterprise Work Modeler, EWA, Gateway Manager, ImpactNow, InfoMaker, Information
Anywhere, Information Everywhere, InformationConnect, InternetBuilder, iScript, Jaguar CTS, jConnect for JDBC, KnowledgeBase,
MainframeConnect, Maintenance Express, MAP, MDI Access Server, MDI Database Gateway, media.splash, MetaWorks, MySupport,
Net-Gateway, Net-Library, NetImpact, ObjectConnect, ObjectCycle, OmniConnect, OmniSQL Access Module, OmniSQL Toolkit,
Open Client, Open ClientConnect, Open Client/Server, Open Client/Server Interfaces, Open Gateway, Open Server, Open
ServerConnect, Open Solutions, Optima++, PB-Gen, PC APT Execute, PC DB-Net, PC Net Library, Power++, power.stop, PowerAMC,
PowerBuilder, PowerBuilder Foundation Class Library, PowerDesigner, PowerDimensions, PowerDynamo, PowerJ, PowerScript,
PowerSite, PowerSocket, Powersoft, PowerStage, PowerStudio, PowerTips, Powersoft Portfolio, Powersoft Professional, PowerWare
Desktop, PowerWare Enterprise, ProcessAnalyst, Report Workbench, Report-Execute, Replication Agent, Replication Driver,
Replication Server, Replication Server Manager, Replication Toolkit, Resource Manager, RW-DisplayLib, RW-Library, S Designor, S-
Designor, SDF, Secure SQL Server, Secure SQL Toolset, Security Guardian, SKILS, smart.partners, smart.parts, smart.script, SQL
Advantage, SQL Anywhere, SQL AnywhereStudio, SQL CodeChecker, 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, STEP, SupportNow, Sybase Central, Sybase Client/Server Interfaces, Sybase Financial
Server, Sybase Gateways, Sybase MPP, Sybase SQL Desktop, Sybase SQL Lifecycle, Sybase SQL Workgroup, Sybase User Workbench,
SybaseWare, Syber Financial, SyberAssist, SyBooks, System 10, System 11, System XI (logo), SystemTools, Tabular Data Stream,
Transact-SQL, Translation Toolkit, UNIBOM, Unilib, Uninull, Unisep, Unistring, URK Runtime Kit for UniCode, Viewer, Visual
Components, VisualSpeller, VisualWriter, VQL, WarehouseArchitect, Warehouse Control Center, Warehouse Studio, Warehouse
WORKS, Watcom, Watcom SQL, Watcom SQL Server, Web Deployment Kit, Web.PB, Web.SQL, WebSights, WebViewer, WorkGroup
SQL Server, XA-Library, XA-Server and XP Server are trademarks of Sybase, Inc. 9/99
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 DFARS52.227-7013
for the DOD and as set forth in FAR 52.227-19(a)-(d) for civilian agencies.
Sybase, Inc., 6475 Christie Avenue, Emeryville, CA 94608.

Contents
iii
About This Book........................................................................................................................ xvii
CHAPTER 1 Overview of Adaptive Server IQ System Administration ............ 1
Introduction to Adaptive Server IQ................................................... 1
System administration tasks............................................................. 2
Security overview............................................................................. 3
Types of users........................................................................... 4
Granting permissions ................................................................ 4
Tools for system administration........................................................ 4
The database server ........................................................................ 5
Catalogs and IQ ............................................................................... 5
The IQ Store.............................................................................. 6
The Temporary Store ................................................................ 6
The Catalog Store ..................................................................... 6
Concurrent operations...................................................................... 7
Stored procedures............................................................................ 7
Adaptive Server IQ stored procedures...................................... 8
Adaptive Server Enterprise system and catalog procedures .... 9
Catalog stored procedures...................................................... 11
System tables and views................................................................ 12
Commands and Functions ............................................................. 16
Types of SQL statements........................................................ 16
Functions................................................................................. 16
Message logging............................................................................ 17
The utility database........................................................................ 18
Compatibility with earlier versions.................................................. 19
CHAPTER 2 Running Adaptive Server IQ ........................................................ 21
Starting the database server.......................................................... 21
Server command lines ................................................................... 22
Starting the server on UNIX ........................................................... 23
Using the startup utility............................................................ 24
Typing the server startup command........................................ 25

Contents
iv
Starting the server on Windows NT ............................................... 26
Starting the server from the NT Start menu ............................ 26
Typing the server startup command........................................ 26
Running the server outside the current session...................... 27
Using command-line switches........................................................ 28
Naming the server and databases .......................................... 31
Controlling performance from the command line .................... 33
Controlling permissions from the command line ..................... 36
Setting a maximum Catalog page size.................................... 37
Setting up a client/server environment.................................... 38
Starting a server in forced recovery mode .............................. 40
Starting a server from DBISQL................................................ 40
Starting multiple servers or clients on the same machine....... 41
Monitoring server activity ............................................................... 41
Stopping the database server........................................................ 43
Who can stop the server? ....................................................... 45
Shutting down operating system sessions.............................. 45
Starting and stopping databases.................................................... 46
Starting the asiqdemo database .................................................... 47
Starting and stopping Sybase Central............................................ 48
Connecting a plug-in ............................................................... 49
Stopping Sybase Central......................................................... 50
Introduction to connections ............................................................ 50
How connections are established............................................ 51
Connection parameters specify connections........................... 52
Connection parameters are passed as connection strings ..... 52
Connection parameters are passed as connection strings ..... 53
Simple connection examples ......................................................... 53
Connecting to a database from DBISQL................................. 54
Connecting to other databases from DBISQL......................... 56
Connecting to an embedded database ................................... 57
Connecting using a data source.............................................. 59
Connecting to a server on a network....................................... 60
Using default connection parameters...................................... 61
Connecting from Adaptive Server IQ utilities........................... 62
Working with ODBC data sources.................................................. 63
DSNs and FILEDSNs.............................................................. 64
Creating and editing ODBC data sources...................................... 65
Configuring ODBC data sources............................................. 67
Creating a File Data Source........................................................... 71
Using ODBC data sources on UNIX .............................................. 72
Connection parameters.................................................................. 73
Connection parameter priorities .............................................. 76
How Adaptive Server IQ makes connections................................. 77

Contents
v
Steps in establishing a connection.......................................... 77
Locating the interface library ................................................... 78
Assembling a list of connection parameters............................ 79
Locating a server..................................................................... 81
Locating the database............................................................. 83
Server name caching for faster connections........................... 84
Interactive SQL connections ................................................... 85
Connecting from other databases.................................................. 85
Using an integrated login ............................................................... 86
Using integrated logins............................................................ 87
Security concerns: unrestricted database access................... 90
Setting temporary public options for added security ............... 91
Network aspects of integrated logins ...................................... 92
Creating a default integrated login user .................................. 92
Troubleshooting startup, shutdown, and connections.................... 93
What to do if you can’t start Adaptive Server IQ...................... 93
What to do if you can’t connect to a database......................... 95
Stopping a database server in an emergency (UNIX)............. 96
Resolving problems with your DBISQL window on UNIX........ 96
CHAPTER 3 Working with Database Objects .................................................. 99
Building Your Adaptive Server IQ Databases................................ 99
Designing your database......................................................... 99
Tools for working with database objects ............................... 100
A step-by-step overview of database setup .......................... 101
Extending data definition privileges....................................... 103
Selecting a device type ......................................................... 104
Allocating space for databases ............................................. 104
Working with databases............................................................... 106
Creating a database.............................................................. 107
Adding dbspaces................................................................... 114
Dropping dbspaces ............................................................... 116
Dropping a database............................................................. 118
Working with tables...................................................................... 118
Creating tables...................................................................... 118
Altering tables........................................................................ 123
Dropping tables ..................................................................... 124
Creating primary and foreign keys ........................................ 125
Table information in the system tables.................................. 127
Working with views....................................................................... 127
Creating views....................................................................... 128
Using views........................................................................... 129
Modifying views..................................................................... 130
Permissions on views............................................................ 130

Contents
vi
Deleting views....................................................................... 131
Views in the system tables.................................................... 131
Working with indexes ................................................................... 132
Introduction to indexes.......................................................... 132
Creating indexes ................................................................... 133
Indexes in the system tables................................................. 133
Removing indexes................................................................. 134
CHAPTER 4 Adaptive Server IQ Indexes ........................................................ 135
Overview of indexes..................................................................... 135
Adaptive Server IQ index types............................................. 135
Benefits over traditional indexes ........................................... 137
Creating Adaptive Server IQ indexes........................................... 138
The CREATE INDEX statement............................................ 138
Creating an index with Sybase Central................................. 139
Creating indexes concurrently............................................... 139
Choosing an index type................................................................ 140
Number of unique values in the index................................... 141
Types of queries.................................................................... 141
Indexing criteria: disk space usage....................................... 143
Data types in the index.......................................................... 143
Combining index types.......................................................... 144
Adaptive Server IQ index types.................................................... 144
Default column index............................................................. 145
The Low_Fast (LF) index type............................................... 145
The High_Group (HG) index type.......................................... 146
The High_Non_Group (HNG) index type .............................. 148
Optimizing performance for ad hoc joins............................... 149
Selecting an index................................................................. 150
Adding column indexes after inserting data................................. 151
Using join indexes........................................................................ 151
Join indexes improve query performance ............................. 151
How join indexes are used for queries.................................. 152
Relationships in join indexes................................................. 152
When a join becomes ad hoc................................................ 152
Join hierarchy overview......................................................... 152
Columns in the join index...................................................... 153
The join hierarchy in query resolution ................................... 154
Multiple table joins and performance..................................... 156
Steps in creating a join index ................................................ 157
Synchronizing join indexes.................................................... 158
Defining join relationships between tables............................ 159
Issuing the CREATE JOIN INDEX statement ....................... 162
Creating a join index in Sybase Central................................ 164

Contents
vii
Types of join hierarchies ....................................................... 164
Modifying tables included in a join index............................... 167
Inserting or deleting from tables in a join index..................... 168
Table versioning controls access to join indexes .................. 169
Estimating the size of a join index................................................ 169
CHAPTER 5 Moving Data In and Out of Databases....................................... 171
Import and export overview.......................................................... 171
Import and export methods ................................................... 171
Input and output data formats ............................................... 172
Permissions for modifying data ............................................. 173
Scheduling database updates............................................... 173
Exporting data from a database................................................... 174
Using output redirection ........................................................ 174
NULL value output................................................................. 175
Bulk loading data using the LOAD TABLE statement.................. 175
Interpreting notification messages......................................... 187
Memory message.................................................................. 187
Main IQ Store blocks messages............................................ 188
IQ Temporary Store blocks message.................................... 188
Main buffer cache activity message...................................... 188
Temporary buffer cache message......................................... 189
Controlling message logging................................................. 189
Using the INSERT statement....................................................... 190
Inserting specified values row by row .......................................... 190
Inserting selected rows from the database .................................. 191
Inserting from a different database........................................ 192
Importing data interactively .......................................................... 195
Inserting into tables of a join index............................................... 195
Inserting into primary and foreign key columns............................ 196
Partial-width insertions................................................................. 197
Partial-width insertion rules ................................................... 198
Converting data on insertion ........................................................ 202
Inserting data from pre-Version 12 Adaptive Server IQ ........ 204
Load conversion options ....................................................... 204
Column width issues ............................................................. 208
Using the ASCII conversion option........................................ 208
The DATE Option.................................................................. 210
The DATETIME conversion option........................................ 212
Working With NULLS ............................................................ 215
Other factors affecting the display of data.................................... 216
Matching Adaptive Server Enterprise data types......................... 217
Unsupported Adaptive Server Enterprise data types ............ 217
Adaptive Server Enterprise data type equivalents ................ 218

Contents
viii
Handling conversion errors on data import ........................... 220
Tuning bulk loading of data.......................................................... 221
Improving load performance during database definition ....... 221
Setting server startup options................................................ 222
Adjusting your environment at load time............................... 222
Reducing Main IQ Store space use in incremental loads...... 223
Changing data using UPDATE..................................................... 224
Deleting data................................................................................ 225
Importing data by replication........................................................ 226
CHAPTER 6 Using Procedures and Batches.................................................. 229
Overview of procedures ............................................................... 229
Benefits of procedures ................................................................. 230
Introduction to procedures ........................................................... 230
Creating procedures.............................................................. 231
Calling procedures ................................................................ 232
Dropping procedures............................................................. 232
Permissions to execute procedures...................................... 233
Returning procedure results in parameters........................... 233
Returning procedure results in result sets............................. 234
Introduction to user-defined functions.......................................... 235
Creating user-defined functions ............................................ 235
Calling user-defined functions............................................... 236
Dropping user-defined functions ........................................... 237
Permissions to execute user-defined functions..................... 237
Introduction to batches................................................................. 238
Control statements....................................................................... 239
Using compound statements................................................. 240
Declarations in compound statements.................................. 241
Atomic compound statements............................................... 242
The structure of procedures......................................................... 243
SQL statements allowed in procedures................................. 243
Declaring parameters for procedures.................................... 244
Passing parameters to procedures ....................................... 245
Passing parameters to functions........................................... 245
Returning results from procedures............................................... 246
Returning a value using the RETURN statement.................. 246
Returning results as procedure parameters.......................... 247
Returning result sets from procedures.................................. 249
Returning multiple result sets from procedures..................... 250
Returning variable result sets from procedures..................... 250
Using cursors in procedures ........................................................ 251
Cursor management overview .............................................. 252
Cursor positioning ................................................................. 252

Contents
ix
Using cursors on SELECT statements in procedures........... 253
Errors and warnings in procedures.............................................. 255
Default error handling in procedures..................................... 256
Error handling with ON EXCEPTION RESUME.................... 258
Default handling of warnings in procedures.......................... 260
Using exception handlers in procedures ............................... 261
Nested compound statements and exception handlers ........ 263
Using the EXECUTE IMMEDIATE statement in procedures ....... 264
Transactions and savepoints in procedures................................. 265
Some tips for writing procedures.................................................. 265
Check if you need to change the command delimiter ........... 265
Remember to delimit statements within your procedure ....... 266
Use fully-qualified names for tables in procedures................ 266
Specifying dates and times in procedures............................. 266
Verifying procedure input arguments .................................... 267
Statements allowed in batches .................................................... 267
Using SELECT statements in batches.................................. 268
Calling external libraries from procedures.................................... 268
Creating procedures and functions with external calls.......... 269
External function declarations............................................... 270
How parameters are passed to the external function............ 271
Special considerations when passing character types.......... 272
CHAPTER 7 Ensuring Data Integrity .............................................................. 273
Data integrity overview................................................................. 273
How data can become invalid ............................................... 273
Integrity constraints belong in the database.......................... 274
How database contents get changed.................................... 275
Data integrity tools................................................................. 275
SQL statements for implementing integrity constraints......... 276
Using table and column constraints............................................. 277
Using UNIQUE constraints on columns or tables.................. 277
Using IQ UNIQUE constraint on columns ............................. 278
Using CHECK conditions on columns................................... 278
Column CHECK conditions from user-defined data types .... 279
Working with column constraints in Sybase Central ............. 280
Using CHECK conditions on tables....................................... 280
Modifying and deleting CHECK conditions............................ 280
Declaring entity and referential integrity....................................... 281
Enforcing entity integrity........................................................ 282
If a client application breaches entity integrity....................... 282
Primary keys enforce entity integrity ..................................... 283
Declaring referential integrity................................................. 283
How you define foreign keys ................................................. 284

Contents
x
Referential integrity is unenforced......................................... 284
Integrity rules in the system tables............................................... 285
CHAPTER 8 Transactions and Versioning ..................................................... 287
Overview of transactions and versioning ..................................... 287
Introduction to transactions................................................... 287
Introduction to concurrency................................................... 290
Introduction to versioning...................................................... 291
Versioning prevents inconsistencies............................................ 299
How locking works ....................................................................... 299
Locks for DML operations ..................................................... 299
Locks for DDL operations...................................................... 300
Primary keys and locking ...................................................... 302
Isolation levels.............................................................................. 302
Checkpoints, savepoints, and transaction rollback ...................... 303
Checkpoints........................................................................... 304
Savepoints within transactions.............................................. 305
Rolling back transactions ...................................................... 307
System recovery.................................................................... 307
How transaction information aids recovery ........................... 308
Performance implications............................................................. 309
Overlapping versions and deletions...................................... 310
Cursors in transactions ................................................................ 311
Cursors and versioning ......................................................... 312
Cursor sensitivity................................................................... 312
Cursor scrolling ..................................................................... 312
Hold cursors.......................................................................... 313
Positioned operations............................................................ 313
Cursor command syntax and examples................................ 313
Controlling message logging for cursors............................... 313
CHAPTER 9 International Languages and Character Sets ........................... 315
Introduction to international languages and character sets.......... 315
Adaptive Server IQ international features ............................. 315
Using the default collation..................................................... 316
Character set questions and answers................................... 316
Understanding character sets in software.................................... 317
Pieces in the character set puzzle......................................... 317
Language issues in client/server computing ......................... 318
Code pages in Windows and Windows NT........................... 319
Multibyte character sets ........................................................ 321
Sorting characters using collations........................................ 322
International aspects of case sensitivity................................ 322

Contents
xi
Understanding locales.................................................................. 323
Introduction to locales ........................................................... 323
Understanding the locale language....................................... 324
Understanding the locale character set................................. 325
Understanding the locale collation label................................ 328
Setting the SQLLOCALE environment variable .................... 328
Understanding collations.............................................................. 328
Displaying collations.............................................................. 328
Supplied collations ................................................................ 329
ANSI or OEM?....................................................................... 331
Notes on ANSI collations....................................................... 332
Notes on OEM collations....................................................... 334
Using multibyte collations...................................................... 336
Understanding character set translation ...................................... 336
Character translation for database messages....................... 336
Connection strings and character sets.................................. 338
Avoiding character-set translation......................................... 338
Collation internals......................................................................... 339
Comment lines ...................................................................... 340
The title line........................................................................... 340
The collation sequence section............................................. 341
The Encodings section.......................................................... 342
The Properties section .......................................................... 343
International language and character set tasks ........................... 344
Finding the default collation................................................... 344
Configuring your character set environment ......................... 344
Determining locale information.............................................. 345
Setting locales....................................................................... 346
Creating a database with a named collation ......................... 346
Starting a database server using character set translation... 348
Using ODBC code page translation ...................................... 348
Character set translation for Sybase Central and DBISQL ... 349
Creating a custom collation................................................... 349
Creating a database with a custom collation......................... 351
Compatibility issues ..................................................................... 351
Performance issues ..................................................................... 352
CHAPTER 10 Managing User IDs and Permissions........................................ 353
An overview of database permissions.......................................... 353
DBA authority overview......................................................... 354
RESOURCE authority overview............................................ 355
Ownership permissions overview.......................................... 355
Table and views permissions overview................................. 355
Group permissions overview................................................. 356

Contents
xii
Managing individual user IDs and permissions............................ 356
Creating new users ............................................................... 357
Changing a password............................................................ 357
Granting DBA and resource authority ................................... 358
Granting permissions on tables and views............................ 359
Granting users the right to grant permissions ....................... 360
Granting permissions on procedures .................................... 361
Revoking user permissions................................................... 362
Managing groups ......................................................................... 363
Creating groups..................................................................... 363
Granting group membership to users.................................... 364
Permissions of groups........................................................... 365
Referring to tables owned by groups..................................... 365
Groups without passwords.................................................... 366
Special groups....................................................................... 367
Database object names and prefixes........................................... 367
Using views and procedures for extra security ............................ 369
Using views for tailored security............................................ 370
Using procedures for tailored security................................... 371
How user permissions are assessed ........................................... 372
Managing the resources connections use.................................... 372
Users and permissions in the system tables................................ 374
CHAPTER 11 Backup and Data Recovery ........................................................ 377
Backup protects your data ........................................................... 377
Backing up your database............................................................ 378
Types of backups.................................................................. 378
Selecting archive devices...................................................... 380
Preparing for backup............................................................. 381
Concurrency and backups..................................................... 383
The BACKUP statement........................................................ 383
Backup Examples.................................................................. 388
Recovery from errors during backup..................................... 389
After you complete a backup................................................. 390
Performing backups with non-Sybase products.................... 390
Performing system-level backups ................................................ 391
Shutting down the database.................................................. 391
Backing up the right files....................................................... 392
Restoring from a system-level backup.................................. 392
Validating your database.............................................................. 393
Interpreting results................................................................. 394
Concurrency issues for sp_iqcheckdb................................... 395
Restoring your databases ............................................................ 396
Before you restore................................................................. 396

Contents
xiii
The RESTORE statement..................................................... 399
Restoring in the correct order................................................ 403
Renaming the transaction log after you restore .................... 405
Validating the database after you restore.............................. 406
Restore requires exclusive write access ............................... 406
Displaying header information............................................... 407
Recovery from errors during restore ..................................... 408
Using Symbolic Links (UNIX Only)........................................ 408
Unattended backup...................................................................... 409
Getting information about backups and restores ......................... 410
Locating the backup log ........................................................ 410
Content of the backup log ..................................................... 411
Maintaining the backup log.................................................... 412
Viewing the backup log in Sybase Central............................ 412
Recording dbspace names.................................................... 412
Determining your data backup and recovery strategy.................. 413
Scheduling routine backups.................................................. 414
Designating Backup and Restore Responsibilities................ 415
Improving performance for backup and restore .................... 415
CHAPTER 12 Managing System Resources.................................................... 419
Introduction to performance terms............................................... 419
Designing for performance.......................................................... 419
Overview of memory use ............................................................. 420
Paging increases available memory...................................... 420
Utilities to monitor swapping.................................................. 421
Server memory...................................................................... 421
Managing buffer caches........................................................ 422
Determining the sizes of the buffer caches........................... 422
Setting buffer cache sizes ..................................................... 427
Specifying page size ............................................................. 429
Saving memory ..................................................................... 431
Optimizing for large numbers of users .................................. 432
Platform-specific memory options ......................................... 434
Other ways to get more memory........................................... 438
The process threading model....................................................... 439
Insufficient threads error........................................................ 440
IQ options for managing thread usage.................................. 440
Balancing I/O................................................................................ 441
Raw I/O (on UNIX operating systems).................................. 441
Using disk striping ................................................................. 442
Internal striping...................................................................... 443
Using multiple dbspaces ....................................................... 445
Strategic file locations ........................................................... 446

Contents
xiv
Working space for inserting, deleting, and synchronizing..... 447
Options for tuning resource use................................................... 448
Restricting concurrent queries............................................... 448
Limiting a query’s memory use.............................................. 449
Limiting queries by rows returned ......................................... 449
Forcing cursors to be non-scrolling....................................... 449
Limiting the number of cursors.............................................. 450
Limiting the number of statements........................................ 450
Lowering a connection’s priority............................................ 450
Prefetching cache pages....................................................... 450
Optimizing for typical usage.................................................. 451
Other ways to improve resource use ........................................... 451
Restricting database access ................................................. 451
Disk caching.......................................................................... 451
Using RAM disk..................................................................... 452
Indexing tips................................................................................. 452
Picking the right index type ................................................... 452
Using join indexes................................................................. 453
Allowing enough disk space for deletions ............................. 453
Managing database size and structure........................................ 454
Managing the size of your database ..................................... 454
Denormalizing for performance............................................. 454
Denormalization has risks ..................................................... 455
Disadvantages of denormalization........................................ 455
Performance benefits of denormalization.............................. 455
Deciding to denormalize........................................................ 456
Improving your queries................................................................. 456
Tips for structuring queries.................................................... 456
Planning queries.................................................................... 457
Setting query optimization options ........................................ 458
Network performance................................................................... 459
Improving large data transfers............................................... 459
Isolate heavy network users.................................................. 460
Put small amounts of data in small packets.......................... 461
Put large amounts of data in large packets........................... 462
Process at the server level.................................................... 463
CHAPTER 13 Monitoring and Tuning Performance......................................... 465
Viewing the Adaptive Server IQ environment .............................. 465
Getting information using stored procedures ........................ 465
Monitoring the buffer caches........................................................ 467
Starting the buffer cache monitor.......................................... 467
Stopping the buffer cache monitor ........................................ 472
Examining and saving monitor results................................... 472

Contents
xv
Examples of monitor results.................................................. 473
Avoiding buffer manager thrashing.............................................. 476
Monitoring paging on Windows NT systems......................... 477
Monitoring paging on UNIX systems..................................... 477
System utilities to monitor CPU use............................................. 479
CHAPTER 14 Adaptive Server IQ as a Data Server......................................... 481
Client/server interfaces to Adaptive Server IQ............................. 481
Configuring IQ Servers with DSEDIT .................................... 483
Sybase applications and Adaptive Server IQ........................ 488
Open Client applications and Adaptive Server IQ................. 488
Setting up Adaptive Server IQ as an Open Server ...................... 489
System requirements ............................................................ 489
Starting the database server as an Open Server.................. 489
Configuring your database for use with Open Client............. 490
Characteristics of Open Client and jConnect connections........... 491
Servers with multiple databases............................................ 493
Index ........................................................................................................................................... 495

xvi

xvii
About This Book
This book, Adaptive Server IQ Administration and Performance Guide,
presents administrative concepts and procedures and performance tuning
recommendations for Sybase Adaptive Server IQ, a high-performance
decisionsupportserver designedspecificallyfor datawarehouses anddata
marts.
Audience
This guide is for system and database administrators or for anyone who
needs to set up or manage Adaptive Server IQ or understand performance
issues. Familiarity with relational database systems and introductory user-
level experience with Adaptive Server IQ is assumed.
How to use this book
The following table shows which chapters fit a particular interest or need.

Related documents
xviii
Table 1: Guide to using this book
Related documents
Documentation for Adaptive Server IQ:
•Introduction to Adaptive Server IQ
Read and try the hands-on exercises if you are unfamiliar with Adaptive
Server IQ, with the Sybase Central database management tool, or with
Interactive SQL.
To learn how to... Read this chapter...
Understand the role of an Adaptive
Server IQ administrator Chapter 1, “Overview of Adaptive Server IQ System Administration”
Start and stop an IQ database server,
and set up user connections Chapter 2, “Running Adaptive Server IQ”
Createan Adaptive Server IQ database Chapter 3, “Working with Database
Objects”
Select Adaptive Server IQ indexes Chapter 4, “Adaptive Server IQ
Indexes”
Load data into your database Chapter 5, “Moving Data In and Out
of Databases”
Create procedures and batches Chapter 6, “Using Procedures and
Batches”
Add users and assign them privileges Chapter 10, “Managing User IDs and
Permissions”
Specify constraints on the data in your
tables Chapter 7, “Ensuring Data Integrity”
Understand how transactions work Chapter 8, “Transactions and
Versioning”
Set up your database for the language
you work in Chapter 9, “International Languages and Character Sets”
Back up and restore databases Chapter 11, “Backup and Data
Recovery”
Tune Adaptive Server IQ for
maximum performance Chapter 12, “Managing System Resources”; see also performance
tuning hints for specific features in all
chapters
Monitor and tune performance Chapter 13, “Monitoring and Tuning
Performance”

About This Book
xix
•Adaptive Server IQ Reference Manual
Read for a full description of the SQL language, utilities, stored
procedures, data types, and system tables supported by Adaptive Server
IQ.
•Adaptive Server IQ Troubleshooting and Error Messages Guide
Read to solve problems, perform system recovery and database repair, and
understand error messages, which are referenced by by SQLCode,
SQLState and message text.
•Adaptive Server IQ Installation and Configuration Guide
Read the edition for your platform before and while installing Adaptive
Server IQ, when migrating to a new version of Adaptive Server IQ, or
when configuring Adaptive Server IQ for a particular platform.
•Adaptive Server IQ Multiplex User’s Guide
Read if you are using the multiplex feature, which lets you manage a very
large data warehouse consisting of a write server and multiple query
servers.
•Adaptive Server IQ Release Bulletin
Read just before or after purchasing Adaptive Server IQ for an overview
of new features. Read for help if you encounter a problem.
Note Because Adaptive Server IQ is an extension of the Adaptive Server
Anywhere product, IQ and Anywhere support many of the same features. The
IQ documentation set refers the reader to Anywhere documentation where
appropriate.
Documentation for Adaptive Server Anywhere:
•Adaptive Server Anywhere User’s Guide
Intended for all users of Adaptive Server Anywhere, including database
administrators and application developers, this book describes in depth
how to use Adaptive Server Anywhere.
•Adaptive Server Anywhere Programming Interfaces
Intended for application developers writing programs that directly access
theODBC,Embedded SQL, orOpen Clientinterfaces,this bookdescribes
how to develop applications for Adaptive Server Anywhere.

Related documents
xx