Sybase 12.4.2 User manual

Administration and Performance Guide

Adaptive Server® IQ

12.4.2

DOCUMENT ID: 38152-01-1242-01

LAST REVISED: April 2000

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 (800) 685-8225, fax (617) 229-9845.

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 Anywhere Studio, SQL Code Checker, SQL Debug, SQL Edit, SQL Edit/TPU, SQL Everywhere, SQL

Modeler, SQL Remote, SQL Server, SQL Server Manager, SQL SMART, SQL Toolset, SQL Server/CFT, SQL Server/DBM, SQL

Server SNMP SubAgent, SQL Station, SQLJ, 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 DFARS 52.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

decision support server designed specifically for data warehouses and data

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.

Sybase 12.4.2 User manual

Sybase 12.4.2 User manual

Related papers

Other documents