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

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 contac t Custom er 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, Ente rpr ise M a nag er, Enterprise SQL Serv er M anager, 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, M DI 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, P C AP T Exec ute, 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, SDesignor, SDF, Secure SQL Server, Secure SQL Toolset, Security Guardian, SKILS, smart.partners, smart.par ts, smart.sc ript, 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 Manage r, 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 W orkbench, 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, W atcom SQL, W atcom SQL Server, W eb Deployment Kit, Web.PB, W eb.SQL, W ebSights, WebV iewer , W orkGroup 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

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

iii

Contents

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

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

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

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

vii

Contents

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

viii

Contents

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

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

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

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

xii

Contents

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

xiii

Contents

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

xiv

Contents

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

About This Book

This book, Adaptive Server IQ Administration and Performance Guide, presents administrative concepts and procedures an d perfo rman ce tun ing 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 userlevel experience with Adaptive Server IQ is assumed.

How to use this book

The following table shows which chapters fit a particular interest or need.

xvii

Related documents

CHAPTER 1

Overview of Adaptive Server IQ System Administration

About this chapter

This chapter provides a brief introduction to Adaptive Server IQ and an overview of IQ system administration.

Introduction to Adaptive Server IQ

Adaptive Server IQ is a high-performance decision support server designed specifically for data warehousing. This cross-platform product runs on Windows NT as well as on Sun Solaris (SPARC), HP 9000/800 HP-UX, IBM RISC System/6000 AIX, Silicon Graphics IRIX, and Compaq Tru64 systems.

Adaptive Server IQ is part of the Adaptive Server family that includes Adaptive Server Enterprise for enterprise transaction and mixed workload environments and Adaptive Server Anywhere, a small footprint version of Adaptive Server often used for mobile and occasionally connected computing.

Sybase database architecture

Sybase database architecture provides a common code base for Adaptive Server IQ and Adaptive Server Anywhere , with workload op timized data stores. You use the IQ Store for data warehousing. You can also use Adaptive Server Anywhere for transaction processing. These products share a common command syntax and user interface, allowing easier application development and user access.

System administration tasks

Rapid access to m any data sources

Data warehousi ng and Adaptive Server IQ

Adaptive Server IQ can integrate data from diverse sources—not just IQ databases, but other databases in the Adapti ve Server family, as well as nonSybase databases and flat files. You can import this data into your IQ database, so that you can take advantage of IQ's rapid access capabilities. You can also query other databases directly , using Adaptive Server IQ's remote data access capabilities.

Note Some of these capabilities are currently available on Windows NT only.

See your Adaptive Server IQ Installation and Configuration Guide for more information.

Data warehouses are collections of data designed to allow business analysts to analyze information. They are typically distinct from production databases, to avoid interrupt i ng d ail y operations. Data warehouses are o ft en used as data stores on which to build deci sion suppor t syst ems (DSS). A decision support system is a software application designed to allow an organization to analyze data in order to support business decision making.

All of Adaptive Server IQ's capabilities are designed to facilitate DS S applications. A unique indexing system speeds data analysis. Query optimization gives you rapid responses, even when results include thousands or millions of rows of data. Concurrent data access for multiple query users, and the ability to update the datab a se without interrupting query processing, provide the 24–hour, 7–day access that users expect.

Learning more about Adaptive Server IQ

This book explains how you manag e an Adaptive Serv er IQ system, and gives pointers for tuning your system for maximum performance. It is intended for database administrators, and others who need to understand performance issues. You may also want to refer to the other documentation described in “About This Book”:

System administration tasks

T ypically, the database administrator (DBA) is responsible for the tasks listed on the left side of the following table. Look at the right side of the table to see where these tasks are explained in this or other manuals.

CHAPTER 1 Overview of Adaptive Server IQ System Administration

Table 1-1: Administrative tasks

If you want to know how to... Look in...

Install and configure Adaptive Server IQ for your platform

Start and stop the database server, and set up user connections

Create an Adaptive Server IQ database Chapter 3, “Working with Database

Determine appropriate indexes for your users' queries

Load data into your database Chapter 5, “Moving Data In and Out

Add users and as sign them privilege s Chapter 10, “Managing Us er I Ds and

Ensure the integrity of data in your tables

Understand how transactions impact concurrency

Set up your database for the language you work in

Back up and restore databases Chapter 11, “Backup and Data

Tune Adaptive Server IQ for maximum performance

Monitor IQ performance Chapter 13, “Monitoring and Tuning

Set up and manage a multiplex configuration

Adaptive Server IQ I nstallation and Configuration Guide

Chapter 2, “Running Adaptive Server IQ”

Objects” Chapter 4, “Adaptive Serv er IQ

Indexes”

of Databases”

Permissions” Chapter 7, “Ensuring Data Integrity”

Chapter 8, “Transactions and Versioning”

Chapter 9, “International Languages and Character Sets”

Recovery” Chapter 12, “Managing System

Resources”; see also performance tuning hints for specific feature s in all chapters

Performance”

Adaptive Server IQ Multiplex User’s Guide

Security overview

The DBA is responsible for maintaining database security. Adaptive Server IQ provides security controls by means of the privileges you can assign to users.

Tools for system administration

Types of users

Adaptive Server IQ recognizes three categories of users for each IQ database:

• The database administrator, or DBA, has complete authority to perform all

• The user who creates a particular database object is its owner, and can

• All other users are considered public users. The owner of an object is

Granting permissions

Except for the DBA, who can perform any task, users must be granted the authority to perform sp ecific tasks. For example, you need the p roper authority to:

• Connect to a database.

• Create database objects, such as a database, table, or index.

operations on that database. This guide is addressed primarily to the DBA, who typically carries out most administrative tasks.

perform any operation on that object.

considered a public user for objects owned by other users.

• Alter the structure of database objects.

• Insert or delete data.

• Select (view) data.

• Execute procedures.

The DBA can grant any type of authority to any user. Sometimes other users can grant authority as well. For more information on what users can do, and how the DBA manages users, see Chapter 10, “Managing User IDs and Permissions”.

Tools for system administration

T o help you manage you r dat ab a se, Adapt i ve Ser ver IQ provides two pr im ary tools:

CHAPTER 1 Overview of Adaptive Server IQ System Administration

• Sybase Cent ral is an applicatio n for mana ging Sybase databases . It helps

you manage database objects and perform common administrative tasks such as creating databases, backing up databases, adding users, adding tables and indexes, and monitori ng database perfor mance. Sybase Central has a Java-based graphical user interface, and can be used with any operating system that allows graphical tools.

• DBISQL, also called Interactive SQL, is an application that allows you to

enter SQL statements interactively and send them to a database. DB ISQL has a window-like user interface on all platforms.

The Introduction to Adaptive Server IQ explains how to use S ybase Central and DBISQL to perform simple administrative tasks. If you are not already familiar with these tools, you should read about them in the Introduction to Adaptive Server IQ and use the tutorials provided there.

In addition to these tools, Adaptive Server IQ provides a number of stored procedures that perform system management functions. See “Stored procedures” for more information. You can also create your own procedures and batches.

A few administrative tasks, such as selecting a collation, rely on command-line utilities. These utilities are discussed in other chapters of this book, and described in the Adaptive Server IQ Reference Manual.

The database server

The database server is the “brain” of your Adaptive Server IQ system. Users access data through the database server, never directly. Requests for information from a database are sent to the database server, which carries out the instructions.

Catalogs and IQ

An Adaptive Server IQ database is a joint data store consisting of three parts:

• The permanent IQ Store

• The Temporary Store

Catalogs and IQ

• The Catalog Store

When you create an IQ database, all three stores are created automatically . You create IQ databases using the procedures described in Chapter 3, “Working with Database Objects ”.

The IQ Store

The IQ S tore is the set of Adaptive Server IQ tables. You can have one or more permanent IQ Stores, each in a separate database. E ach IQ S tor e includes a set of tables that organize yo ur data. The table data is store d in indexes, which are structured so as to allow rapid response to various types of analytical queries on very large quantities of data.

The Temporary Store

The Temporary Store consists of a set of temporary tables. The database server uses them for sorting and other temporary processing purposes; you cannot store your data in them directly.

The Catalog Store

The Catalog Store contains all of the information required to manage an IQ database. This information, which includes system tab les an d stored procedures, resides in a set of tables that are compatible with Adaptive Server Anywhere. These tables contain the metadata for the IQ database. Metadata describes the layout of the IQ tables, columns, and indexes. The Catalog Store is sometimes referred to simply as the Catalog.

Adaptive Server Anywhere and Adaptive Server IQ

The Catalog Store closely resembles an Adaptive Server Anywhere store. Adaptive Server Anywhere is a relational database system that can exist with or without IQ. You may have Adaptive Server Anywhere-style tables in your Catalog Store alon g with y our IQ tabl es, or you may h ave a separ ate Adapti ve Server Anywhere database.

CHAPTER 1 Overview of Adaptive Server IQ System Administration

Anywhere tables have a different format than IQ tables. While the commands you use to create objects in an Anywhere database are the same as those for an IQ Store, there are some differences in the features you can specify in those commands. Always use the command syntax in this book or the Adaptive Server

IQ Reference Manual for operations in the IQ Store.

This book explains how you manage your IQ Store and its associated Catalog Store. If you have an Anywhere da tabase, or if you have Anywhere- style tables in your Catalog Store, see the Adaptive Server Anywhere documentation for details of how to create, maintain, and use them.

Concurrent operations

Adaptive Server IQ allows multiple users to query a database at the same time, while another user inserts or deletes data, or backs up the database. Changes to the structure of the database, such as creating, dropping, or altering tables, temporarily exclude other users from those tables, but queries that only access tables elsewhere in the database can proceed.

Adaptive Server IQ keeps your database consistent during these concurrent operations by maintaining multiple versions of table data. To understand this approach, see Chapter 8, “Transactions and Versioning”.

Stored procedures

Adaptive Server IQ stored procedures help you manage your syst em. Stored procedures give you informatio n about you r databas e and users, and carry out various opera tions on the database. This section briefly describes the stored procedures. For more information, see the Adaptive Server IQ Reference Manual.

A stored procedure typically operates on the database in which you execu te it. For example, if you run the stored procedure database, it adds a user to

sp_addlogin in the asiqdemo

asiqdemo.

Stored procedures

You can also create your own stored procedures. See Chapter 6, “Using Procedures and Batches” for details.

Note Statements shown in examples generally use the asiqdemo database, a

sample database installed as part of Adaptive Server IQ. For a diagram of this database’s structure, see Introduction to Adaptive Server IQ.

Adaptive Server IQ stored procedures

The following procedures work specifically on the IQ Store. They are owned by the DBA user ID.

Note Stored procedures th at produce size information assume that the database

was created with the default block size, as described in “Block size”. If a database was created with a non-default block size, the output from the following stored procedures is inaccurate:

sp_iqestspace.

sp_iqestjoin, sp_iqestdbspaces,

CHAPTER 1 Overview of Adaptive Server IQ System Administration

Table 1-2: Stored Procedures for the IQ Store Procedure name Purpose

sp_iqcheckdb Checks the validity of the current

database and repairs indexes

sp_iqcommandstats Gives statistics on execution of

various commands

sp_iqdbsize Gives the size of the current database sp_iqdbstatistics Reports results of the most recent

sp_iqcheckdb

sp_iqestjoin

Estimates the space needed to create join indexes for the tables you specify

sp_iqestdbspaces Estimates the number and size of

dbspaces needed for a given total index size

sp_iqestspace Estimates the amount of space needed

to create a database, based on the number of rows in the underlying database tables.

sp_iqindex Lists indexes and information about

them. Omitting the parameter lists all indexes in the database. Specifying the table_name parameter lists indexes for this table only.

sp_iqindexsize Gives the size of the specified index sp_iqjoinindexsize Gives the size of the specified join

index

sp_iqstatus Displays miscellaneous status

information about the da tabase

sp_iqtable Lists tables and information about

them. Omitting the parameter lists all tables in the database. Specifying the table_name parameter lists columns for this table only.

sp_iqtablesize Gives the size of the specified table

Adaptive Server Enterprise system and catalog procedures

Adaptive Server Enterprise provides system and catalog procedures to carry out many administrative functions and to obtain system information. Adaptive Server IQ has implemented support for some of these procedures.

Stored procedures

System procedures are built-in stored procedures used for getting reports from and updating system tables. Catalog stored procedures retrieve information from the system tables in tabular form.

Note While these procedures perform the same functions as they do in

Adaptive Server Enterprise and pre-Version 12 Adaptive Server IQ, they are not identical. If you have preexisting scripts that use these procedures, you may want to examine the procedures. To see the text of a stored procedure, run

sp_helptext

procedure_name

You may need to reset the width of your DBISQL output to see the full text , by clicking Comma nd→Options an d entering a new Lim i t Display Column s value.

Adaptive Server Enterprise system procedures

The following Adaptive Server Enterprise system procedures are provided in Adaptive Server IQ. These stored procedures perform important system management tasks.

System procedure Description

sp_addgroup Adds a group to a database sp_addlogin Adds a new user account to a database sp_addmessage Adds user-defined messages to

SYSUSERMESSAGES for use by stored procedure PRINT and RAISERROR calls

sp_addtype Creates a user-defined data type sp_adduser Adds a new user to a database sp_changegroup Changes a user’s group or adds a user to a

group

sp_dboption Displays or changes database options sp_dropgroup Drops a group from a database sp_droplogin Drops a user from a database sp_dropmessage Drops user-defined messages sp_droptype Drops a user-defined data type sp_dropuser Drops a user from a database sp_getmessage Retrieves stored message strings from

SYSMESSAGES and SYSUSERMESSAGES for PRINT and RAISERROR statements.

CHAPTER 1 Overview of Adaptive Server IQ System Administration

System procedure Description

sp_helptext Displays the text of a system procedure or

view

sp_password Adds or changes a password for a user ID

Adaptive Server Enterprise catalog procedures

Adaptive Server IQ implements all the Adaptive Server Enterprise catalog procedures with the exception of the implemented catalog procedures are described in the following table.

The following list describes the supported Adaptive Server Enterprise catalog procedures.

Catalog procedure Description

sp_column_privileges Unsupported sp_columns Returns the data types of the specified column sp_fkeys Returns foreign key information about the

specifie d table

sp_pkeys Returns primary key information for a single

table

sp_special_columns Returns the optimal set of columns that

uniquely identify a row in a table

sp_sproc_columns Returns information about a stored procedure’ s

input and return parameters

sp_stored_procedures Returns information about one or more stored

procedures

sp_tables Returns a list of objects that can appear in a

FROM clause

sp_column_privileges procedure. The

Catalog stored procedures

In addition to the Adaptive Server Enterprise Catalog stored procedures, there are other system and catalog stored procedures. The following table lists the

ones you are most likely to use. For a complete list, see Chapter 14, “System Procedures” in Adaptive Server IQ Reference Manual.

Procedure name Purpose

sp_remote_columns List remote tables columns and their

sp_remote_tables List tables on a remote server

data types

System tables and views

Procedure name Purpose

sp_servercaps Display information about a remote

System tables and views

Adaptive Server IQ system tables contain all of the information the database server needs to manage your IQ system. The system tables reside in the Catalog Store, and are sometimes called catalog tables. For some system tables there are also views that make it easier to display the information in the table. The SYS user ID owns the system tables.

Among the information in the system ta bles is:

• Database characteristics

• T able characteristics, including table definitions and information about the size and location of each table

• Information about indexes

server’s capabilities

• Current settings for database and DBISQL options

System tables include:

System table Description

DUMMY A table with exactly one row, useful

for extracting information from the database

SYSARTICLE Describes an article in a SQL Remote

publication

SYSARTICLECOL Describes columns in each article in a

SQL Remote publication

SYSCOLLATION Contains the complete collation

sequences available to Adaptive Server IQ

SYSCOLLA TIONMAPPINGS Lists the collation sequences available

in Adaptive Server IQ and their GPG and JDK mappings

SYSCOLUMN Describes each column in every table

or view

CHAPTER 1 Overview of Adaptive Server IQ System Administration

System table Description

SYSDOMAIN Lists the number, name, ODBC type,

and precision of each predefined data type

SYSFILE Lists operating system files and

dbspace names for the dat abase

SYSFKCOL Associates each foreign key column

with a primary key column

SYSFOREIGNKEY Contains general information about

each foreign key

SYSGROUP Describes a many-to-many

relationship between groups and

members SYSINDEX Describes indexes in the databa se SYSINFO Describes database characteristics SYSIQBACKUP Lists backups and restores SYSIQCOLUMN Lists information on columns in every

table or view in the IQ Store SYSIQFILE Lists in formation on operating system

files for the database SYSIQINDEX Lists internal information on indexes

in the database SYSIQINFO Lists additional database

characteristics SYSIQJINDEX Describes join indexes in the database SYSIQJOINIXCOLUMN Describes columns that participate in

join indexes SYSIQJOINIXT ABLE Lists the tables that participate in each

join index in the database SYSIQT ABLE Describes each table or view in the IQ

Store SYSIXCOL Describes each index for each column

in the database SYSJAR Describes each jar file associated with

the database SYSJARCOMPONENT Describes each jar component

associated with the database SYSJAVACLASS Contains all information related to

Java classes

System tables and views

System table Description

SYSLOGIN Lists User Profile names that can

connect to the database with an integrated login

SYSOPTION Lists current SET OPTION settings

for all users including the PUBLIC user

SYSPROCEDURE Describes each p rocedure in the

database

SYSPROCPARM Describes each paramete r to every

procedure in the da ta ba s e

SYSPROCPERM Lists each user granted permission to

call each procedure in the database

SYSPUBLICATION Describes each SQL Remote

publication

SYSREMOTETYPE Contains information about SQL

Remote

SYSREMOTEUSER Describes user IDs with REMOTE

permissions and the status of their SQL Remote messages

SYSSQLSERVERTYPE Contains information relating to

compatibility with Adaptive Serve r Enterprise

SYSSUBSCRIPTION Relates each user ID with REMOTE

permissions to a publication

SYSTABLE Describes one table or view in the

database

SYSTABLEPERM Describes permissions granted on

each table in the database

SYSSQLSER VER TYPE Contains information on compatibility

with Adaptive Server Enterprise

SYSUSERMESSAGES Lists user-defined error messages and

their creators

SYSUSERPERM Lists characteristics of each user ID.

Because it contains passwords, you need DBA permissions to select from this table

SYSUSERTYPE Describes each user-defined data type

System views present the information from their corresponding system tables in a more readable format. In some cases, they omit password information so that they can be accessible to all users. System views include:

CHAPTER 1 Overview of Adaptive Server IQ System Administration

System view Description

SYSCATALOG Lists all tables and views from

SYSTABLE SYSCOLAUTH Presents column update permission

information from SYSCOLPERM SYSCOLUMNS Presents a readable version o f the table

SYSCOLUMN SYSFOREIGNKEYS Presents foreign key information from

SYSFOREIGNKEY and SYSFKCOL SYSGROUPS Presents group information from

SYSGROUP SYSINDEXES Presents index information from

SYSINDEX and SYSIXCOL SYSOPTIONS Displays optio n settings contained in

the table SYSOPTION SYSPROCPARMS Lists all the procedure parameters

from SYSPROCPARM SYSREMOTEUSERS Lists the information in

SYSREMOTEUSER SYST ABAUTH Presents table permission information

in SYSTABLEPERM SYSUSERAUTH Displays all the information in the

table SYSUSERPERM except for user

numbers. Because it contains

passwords, this system view does not

have PUBLIC select permission SYSUSERLIST Presents all information in

SYSUSERAUTH except for

passwords SYSUSEROPTIONS Display effective permanent option

settings for each user SYSUSERPERMS Contains exactly th e same informat ion

as the table SYS.SYSUSERPERM

except the password is omitted SYSVIEWS Lists views and their definitions

For a complete description of system tables and views and their contents, see the Adaptive Server IQ Reference Manual.

Commands and Functions

All Adaptive Server IQ commands are SQL statements. SQL stands for Structured Query Language, a language commonly used in database applications. Adaptive Server IQ SQL uses the same syntax as Adaptive Server Anywhere SQL; the only differences are for certain product capabilities that are supported only for IQ or for Anywhere. Adaptive Server IQ SQL also offers a high degree of compatibility with Transact-SQL, the SQL dialect used by Adaptive Server Enterprise.

This section introduces the types of commands and functions you can use. Other chapters of this book tell you about the commands you use to perform various administr ative tasks. For complete deta ils of supporte d commands and functions, see the Adaptive Server IQ Reference Manual.

Types of SQL statements

You use three basic types of SQL statements:

• DDL (Data Definition Language) statements let you define and modify your database schema and table and index def i ni tions . Examp le s of DDL statements include

DROP.

CREATE TABLE, CREATE INDEX, ALTER TABLE, and

Functions

• DML (Data Manipulation Language) statements let you query your data, and move data into and out of the database. Examples of DML statements include

SELECT, SET, and INSERT.

• Program control statements control the flow of program execution. They do not operate directly on your IQ tables. Examples include

ROLLBACK.

IF, CALL, and

Functions return informati on from the database. They are allowed any where an expression is allowed. Adaptive Server IQ provides functions that:

• Aggregate data (for example,

• Manipulate numeric data (for example,

TRUNCATE)

• Manipulate string data (for example,

AVG, COUNT, MAX, MIN, SUM)

ABS, CEILING, SQRT,

LENGTH, SOUNDEX, UCASE)

CHAPTER 1 Overview of Adaptive Server IQ System Administration

• Manipulate date and time data (for example,

DATEPART, MINUTES)

• Convert retrieved data from one format to another (

TODAY, DATEDIFF,

CAST, CONVERT)

Message logging

A message log file exists for each database. The default name of this file is dbname.iqmsg. The message log file is created when the database is created.

By default, Adaptive Server IQ logs all messages in the message log file, including:

• Error messages

• Status messages

• Insert notification messages You can examine this file as you would any other text file. At the start of the

file you see output like the following:

2000-03-07 17:20:50 0000 OpenDatabase Completed 2000-03-07 17:20:50 0000 IQ cmd line srv opts: 2000-03-07 17:20:50 0000 DB: r/w, Buffs=1913, Pgsz=4096/512blksz/8bpc 2000-03-07 17:20:50 0000 DB: Frmt#: 23F/2T/1P (FF: 03/18/1999) 2000-03-07 17:20:50 0000 DB: Versn: 12.4.2/(32bit mode)/MS Windows NT 4.0/EBF

0000/Mar 02 2000, 02:17:37 2000-03-07 17:20:50 0000 DB: Name: C:\Program Files\Sybase\ASIQ12\scripts\asiqdemo.db

The fourth line of the file contains version information:

Figure 1-1: Version string in message log

The utility database

The date and time of the software build appears in the version string in ISO datetime format:

YYYY 4-digit year MM 2-digit month number (0- 12) DD 2-digit day of month number (0-31) hh 2-digit numbmer of complete hours that have passed since

mm 2-digit number of complete minutes t hat have pas sed since

ss 2-digit number of complete seconds that have passed since

YYYY-MM-DD hh:mm:ss where

midnight (00-23)

the start of the hour (00- 59)

the start of the minute (00-59)

The message log continues to exist until you drop the database. If your message log ever becomes too large, you can archive it while no users are connected to the database, and then create a new, empty dbname.i qmsg f ile befo re allowing another user to connect.

The utility database

The utility database is essentially a database that never holds data. The database server uses it at times when it needs a database to connect to, but either no real database exists, or none should be running . Adaptive Server IQ installation creates the

Be sure you do not delete this database. You need it to do any of these things:

• Start the database server using the database specified

• Create or drop a database when you have no other database to connect to

• Start the dat abase server or connect to a database when any other d atabases you have are either corrupt or unavailable due to media failure

• Restore a database

By default, the can change these to other values during installation, or later by editin g the connection parameters in the util_db.ini file in your executable directory.

utility database automatically.

START ENGINE command with no

utility database has the user ID dba and the password sql. You

CHAPTER 1 Overview of Adaptive Server IQ System Administration

For more information on the

Client/Server Communications” in your Adaptive Server IQ Installation and Configuration Guide .

utility database, see Chapter 3, “Configuring

Compatibility with earlier versions

Version 12 of Adaptive Server IQ differs marked ly from earlier ver sions of IQ. It offers many important new features, including the ability to update the database concurrently with query use, Transact-SQL and Java support, additional query and view support, and better front end support. It offers syntactic compatibility with Adaptive Server Anywhere, allowing Anywhere users to build on their existing knowle dge base as they begin to use IQ. It also includes a new, more efficient database format.

These last two features have special implications for users migrating from pr eversion 12 Adaptive Server IQ. When you migrate to version 12, you must:

• Examine any scripts, applications, and procedures for differences in

syntax, and make the necessary changes.

• Reload your IQ database, using the special migration procedure. See the Adaptive Server IQ Installation and Configuration Guide and the

Adaptive Server IQ Release Bulletin for your platform for migration details.

Compatibility with earlier versions

CHAPTER 2

Running Adaptive Server IQ

About this chapter

Three steps are required for you to start using Adaptive Server IQ:

• The database server must be started.

• The database must be started.

• You must connect to the database. Adaptive Server IQ gives you great flexibility in perform ing these three

steps. This chapter explains various options for accomplishing each of these steps, and gives s uggestions for which t o choose, depending on your situation.

With Adaptive Server IQ you will run in a client/server environment, in which many users can connect to a database server across a network. You may have multiple databases on a given datab a se server. Likewise, you may be able to connect to more than one database server. The server startup and connection options you choose must take into account these factors.

Starting the database server

The first step in running Adaptive Server IQ is to start the database server . You can start the server in all of these ways:

• Type a server startup command at the operating system prompt. See “Server command lines” on page 22, as well as the section specific to your operating system.

• Start the server from the Windows NT Start menu. See “Starting the server from the NT Start menu” on page 26.

• Start the server with the Sybase-provided utility, the server as a UNIX background process. See “Starting the server on UNIX” on page 23.

start_asiq, that runs

Server command lines

• Start the server and the sample database with a Sybase-provided configuration file. See “Starting the asiqdemo database” on page 47.

• Place a server startup command in a shortcut or desktop icon.

• Include a server startline in an ODBC data source. See “Creating and editing ODBC data sources” on page 65.

• Include a server startline in a utility command.

• Issue a SQL command from Interactive SQL to start an additio nal server. See “Starting a server from DBISQL” on page 40.

Note If you will be using remote data access capabilities to insert data from

other databases or to issue queries to other databases, see the Adaptive Server IQ Release Bulletin for Windows NT for special startup requirements.

Server command lines

The general form for the server command line is as follows:

asiqsrv12 [ [

database-switches ], ...

The elements of this command line are as follows:

server-switches

]

] [

database-file

• server-switches include the database server name and other options that control the behavior of the server , for all databases that are running on that server.

• database-file is the file name of the Catalog Store. You can omit this option, or enter one or more database file names on the command line. Each of these databases is loaded and available for applications. If the starting directory contains the database file, you do not need to specify the path; otherwise, you must specify the path. If you do not specify a file extension in database-file, the extension .db is assumed.

• database-switches are options that you can specify for each database file you start, that control certain aspects of its behavior.

In examples throughout this chapter where there are several command-line options, we show them for clarity on separate lines, as they could be written in a configuration file. If you enter them directly on a command line, you must enter them all on one line (that is, without any carriage returns).

You can choose from many command -line options or switches to specify such features as permissions required to start a database or stop the server, and the network protocols to use. The command-line switches are on e means of tuning Adaptive Server IQ behavior and performance.

There are slight variations in the basic command for different operating systems, as well as a startup utility that runs this command automatically. See the sections that follow for details.

Starting the server on UNIX

This section describes two methods for starting the database server that are specific to UNIX platforms:

CHAPTER 2 Running Adaptive Server IQ

• Use the startup utility

start_asiq. This is the preferred method.

• Enter the server startup command and the appropriate parameters (see

below).

You can also use any of the generic methods described elsewhere in this chapter, provided that you set startup parameter defaults for each platform to

the settings used in

start_asiq. These settings are listed in the Adaptive Server

IQ Installation and Configuration Guide.

Normally, you should always use the

start_asiq utility to start the server on

UNIX platforms. If you do not, among the tasks you must do which the utility normally does for you are:

• Remove all limits, and then set limits on the stack size and descriptors. To

do so, go to the C shell and issue these commands:

% unlimit % limit stacksize 8192 % limit descriptors 4096

Note Be aware that unlimit affects soft limits only. You must change any

hard limits by setting kernel parameters.

• Set all server parameters appropriately in the

asiqsrv12 command.

Starting the server on UNIX

Note the server starting directory

Note what directory you are in when you start the server. The server startup directory determines the location of any database files you create with relative pathnames. If you start the server in a different directory, Adaptive Server IQ cannot find those database files.

Any server startup scri pts s hou l d ch ange di rect ory to a known location before issuing the server startup command.

Using the startup utility

For most situations, the easiest way to start a database server on UNIX is by using the startup script that Sybase provides. Using this script ensures that all required parameters are set correctly, except in special situations described later in this chapter.

❖ To start the server on UNIX using the startup utility:

1 Change to a writable directory. 2Run the

command is:

You can also include server switches or database swi tc hes, as di s cus sed i n the next section.

start_asiq utility at the system prompt. The simple form of this

start_asiq

servername

[

database

]

This command starts the named server as a background process, starts the named database if you specify it, and sets all required startup options. Once the server starts, it sends a message to the window or console where you started the server indicating that the server is running. It also dis plays th e version of the

Open Client communications library that is in use, and “possible problems” messages on failure to start. This message is saved in the stderr log. After that, all server messages go to the server log. The server log is in $ASLOGDIR/servername.nnn.svrlog, where nnn is the number of times the server has been started. See Chapter 1, “Environment Variables and Registry Entries” in Adaptive Server IQ Reference Manual for a description of $ASLOGDIR and other environment variables you may need to set.

The

start_asiq command displays messages as to whether the server started or

not, and The

start_asiq utility also adds the appropriate library path to the environment

and sets parameters that govern Adaptive Server IQ. Parameter settings vary by platform. See yo u r Adaptive Server IQ Installation and Configuration Guide for a list of parameter settings for your platform.

For an explanation of commonly used startup parameters, see “Using command-line switches” on page 28.

Typing the server startup command

You can also start the database server by entering the following command at the UNIX prompt:

CHAPTER 2 Running Adaptive Server IQ

asiqsrv12 [

database-switches

server-switches

]]

] [

database-file

[

This command starts the specified database:

• On the specified server, if one is named in the startup command.

• On the server associated with this database, if the startup parameters

specify a data source.

• On the local server, if one is running and no other server is specified. See “Using command-line switches” on page 28 for a description of

commonly used startup parameters.

Note To start the server without starting any database, you omit the database

file from the

asiqsrv12 command and specify a servername. For ease of use,

however, it is preferable to start the database and server together, by specifyin g the database name when you start the server . The server takes its name from the database name by default, or you can specify a different name for the server. See “Naming the server and databases” on page 31 for more information on server and database names.

If you omit the database name, you must name the server explicitly using the

-n server switch. This method is appropriate when you are creating or restoring

a database. It is also used when you only want to control the starting and stopping of the server, leaving database use to client software.

When you start the server with the

asiqsrv12 command, i t does not run in the

background, and messages do not automatically go to the ser ver log. However , if your include the

-o

filename

server switch, messages are sent to the n a med

file in addition to the server window.

Starting the server on Windows NT

This section describes methods for starting the database server that are specific to Windows NT systems. You can also use any of the generic methods described elsewhere in this chapter.

Note the server starting directory

Be sure to make note of what directory you are in when you start the server. The location of any database files you create with relative pathnames depends on the server startup directory. If you start the server in a different directory, Adaptive Server IQ looks for those database files in the new startup directory.

Any server startup scri pts s hou ld ch ange directory to a known location before issuing the server startup command.

Starting the server from the NT Start menu

The easiest way to start the server on NT is from the Start menu. Click Start on the T ask bar , and select Programs → Sybase → Adaptive Server

IQ 12. From here, you can start the sample database, Sybase C entral, Interactive SQL,

and the ODBC Administrator. You can also place databases of your own in the Program group.

Typing the server startup command

You can use a Program Manager icon to hold a command line, or enter the following command at the system command prompt:

asiqsrv12 [

database-switches

Y ou must either enter the databa se-file or include the servername as one of th e server-switches.

This command starts the specified database:

• On the specified server, if one is named in the startup command

• On the server associated with this database, if the startup parameters specify a data source

• On the local server, if one is running and no other server is specified

server-switches

]]

] [

path\database-file

[

CHAPTER 2 Running Adaptive Server IQ

See “Using command-line switches” on page 28 for a description of commonly used startup parameters.

Note To start the server without starting any database, you omit the database

file from the

asiqsrv12 command and specify a servername. See “Naming the

server and databases” on page 31 for a discussion of why it is preferable to include both the database and server in the startup command.

If you supply no switches and no database file on Windows NT, a dialog box is displayed, allowing you to use a Browse button to loca te your databas e file.

To start the server in a separate session, use the Windows NT

start asiqsrv12 [

database-switches

server-switches

]

Running the server outside the current session

When you log on t o a comp ut er us in g a user ID and a passwor d, you establish a session. When you start a database server, or any other application, it runs within that session. When you log of f the com puter , all application s associated with the session terminate.

In a production environment, IQ database servers must be available all the time. To make this easier, you can run Adaptive Server IQ in such a way that, when you log off the computer, the database server remains runn ing. The way you do this depends on your operating system.

• Windows NT service You can run the Windows NT database server

as a service. This has many convenient properties for running high availability servers.

• UNIX daemon You can run the UNIX database server as a daemon by

using the the background, and to continue running after you log off.

-ud command-line option, enabling the database server to run in

]

start command:

database

[

Using command-line switches

Running the UNIX database server as a daemon

To run the UNIX database server in the background, and enable it to run independently of the current session, you run it as a daemon.

Note Do not use ’&’ to run the database server in the background. It will not

work. You must instead run the database server as a daemon.

❖ To run the UNIX database server as a daemon:

•Use the -ud command-line option when starting the database server. For example:

start_asiq -ud asiqdemo.db

Running the server as a Windows NT service

You can run the server as a service under Windows NT. This allows it to keep running even when you log off the machine. For details of this and other NTspecific features, see the Adaptive Server IQ Installation and Configuration Guide.

Using command-line switches

You use command-line switches to define your Adaptive Server IQ environment.

This section describes some of the most common command-line switches, and points out when you may wish to use them. Switches described in this chapter include:

For this switch See this section

-c “Catalog Store cache size”

-gb “Other performance-related switches” (Windows NT

only)

-gc “Checkpoint interval”

-gd “Controlling permissions from the command line”

-gk “Controlling permissions from the command line”

-gm “Concurrent users”

-gn “Controlling performance from the command line”

CHAPTER 2 Running Adaptive Server IQ

For this switch See this section

-gp “Setting a maximum Catalog page size”

-gr “Recovery time”

-gu “Controlling permissions from the command line”

-iqgovern “Concurrent queries”

-iqmc “Buffer caches and physical memory”

-iqtc “Buffer caches and physical memory”

-iqsmem “Unwired memor y” ( A I X , Compaq Tru64 U N I X , a nd

HP UNIX only)

-iqwmem “Wired memory” (Compaq Tru64 UNIX, HP UNIX,

and Sun UNIX only)

-n “Naming the server and databases”

-p “Other performance-related switches”

-ti “Setting the default client timeout”

-tl “Setting the default network timeout”

-x “Selecting communications protocols”

-z “Debugging network communications startup

problems”

Some of the values you can set with command-line switches can also be changed with the

SET OPTION command. For details of this command and its

options, an d fo r a complete list of command-line switches and full reference information on them, see the Adaptive Server IQ Reference Manual.

Displaying command line options

Case sensitivity Using configuration

files

To display all of the available command-line options, enter one of the following commands at the operating system prompt:

• On UNIX systems, enter:

asiqsrv12 -h

• On Windows NT systems, enter:

asiqsrv12 /?

Command-line parameters are case sensitive. If you use an extensive set of command-line options, you can store them in a

configuration file, and invoke that file on a server command line. Specify switches in the configuration file as you would on the command line, with these exceptions:

• You can enter switches on multiple lines.

• Y ou must not include either single or double quotes in a configuration file.

Using command-line switches

For example, the following configuration file starts the database mydb.db, on the database server named Elora, with a Catalog cache size of 16MB, TCP/IP as a network protocol and a specified port number, user connections limited to 10, and a Catalog page size of 4096 bytes.

If you name the file mydb.cfg, you could use these command-line options as follows:

In examples throughout this chapter where there are several command-line options, we show them for clarity on separate lines as they could be written in a configuration file. If you enter them directly on a command line, you must enter them all on one line.

Note When you stop the server with the DBSTOP command, you need to

specify the same parameters as when you started the server. Using a configuration file to start the server ensures that you will be able to find these parameters when you need them.

-n Elora

-c 16M

-x tcpip(port=2367

-gm 10

-gp 4096 path\mydb.db

asiqsrv12 @mydb.cfg

Required commandline switches

While most of the command-line switches described in the sections that follow are optional, you must specify the

-n, -c, -gp, and -gm switches to run Adaptive

Server IQ effectively. For this release, recommended server startup values are:

asiqsrv12 -n

-gp 4096 -gr 6000 -ti 4400 -tl 300

servername

-c 16M -gc 6000 -gd all -gm 10

database

If you use TCP/IP to connect to the server, you should include network connection parameters as well. If you start the server without the parameter

’tcpip(port=nnnn)’

On UNIX platforms, if you start the database server with the

set, then the server uses the default TCP/IP port number 2638.

start_asiq

-x

command, these parameters are included automatically with values shown above, along with others specific to your platform. You can override these values and include other parameters by specifying them on the

start_asiq

command line.

CHAPTER 2 Running Adaptive Server IQ

Configuration file for the sample database

A note about defaults

Naming restrictions

The asiqdemo.cfg file, which you use to start the sample database, sets startup parameters to the recommended defaults. You can also use this file as a

template for your own configuration files. Chapter 3, “Running and Connecting to Servers”, Introduction to Adap t ive Se rver IQ gives an example of the sample database configuration file. This file is found in demo/asiqdemo.cfg in your installation directory.

In the discussion of individual server options that follows, “default” means the value that applies if you start the server with the

asiqsrv12 command, or from

the Windows NT Start menu, and do not specify a different value. If you start the server with the

start_asiq UNIX startup utility, or with

asiqdemo.cfg or your own configuration file, many of these options are set to other values.

Do not use hyphenated names or reserved words for database names, user identifiers or server names, even enclosed in quotation marks. For example, the following are not allowed:

grant june-1999-prospects "foreign" For a complete list of reserved words (keywords), see the Adaptive Server IQ

Reference Manual.

Naming the server and databases

You can use the -n command-line option as a database switch (to name the database) or as a server switch (to name the server). The server switch is required if you do not supply a database.

The server and database names are among the connection parameters that client applications can use when connecting to a database. On Windows NT, the server name appears on the desktop icon and on the title b ar of the server window.

Default names

If no server name is provided, the default server name is the name of the first database started.

The default database name is the root of the Catalog Store file name (the file name without a directory path or the .db extension). For example, in the following command line the first database is named is

sample, and the server is named mydb.

mydb, the second database

Using command-line switches

asiqsrv12 mydb.db sample.db

Naming databa se s

Naming the server

You can name databases by supplying a -n switch following the database file. For example, the following command line starts a database and names it:

asiqsrv12 mydb.db -n MyDB

Naming a database lets you use a nickname in place of a file name that may be difficult to remember.

You name the server by supplying a -n switch before the first database file. For example, the following command line starts a ser ver named and the

sample database on that server:

asiqsrv12 -n Cambridge_sample sample.db -gm 10 -gp 4096

Cambridge_sample

Putting the host name, in this case Cambridge, at the start of the server name is a useful convention. It is especially important in a multiuser, networked environment where shared memory will be used for local database connections. This convention ensures that all users will be able to connect to the correct database, even when other databases with the same name have been started on other host systems.

To allow Adaptive Server IQ to locate the server no matter what character set is in use, include only seven-bit ASCII (lower page) characters in the server name. For more information on character sets, see Chapter 9, “International

Languages and Character Sets” Specifying a server name lets you start a database server with no database

loaded. The following command starts a server named

Galt with no database

loaded:

Case sensitivity and naming conventions

asiqsrv12 -n Galt -gm 10 -gp 4096

Note Although you can start a server by rely ing o n the defa ult ser ver name, i t

is better to include both the server name and the database name, and to make the two names different. This approach helps users distinguish between the server and the databases running on it. You must specify the server name in order to start the server without starting a specific database.

For information about starting databases on a r unning server , see “Starting and stopping databases”.

Server names and database names are case insensitive on Windows NT, and case sensitive on UNIX.

CHAPTER 2 Running Adaptive Server IQ

You should ado pt a s et of nami n g co nven tions for your servers and dat abas e s , as well as for all other database objects, that includes a case specification. Enforcing naming conventions can prevent problems for users.

Controlling performance from the command line

Several command-line options can affect database server performance. Most of the switches described in this section control resources for operations on the IQ Store, which can have a major impact on performance. Switches that affect only the resources available for operations on the Catalog Store may have a minor impact on overall performance. If you need to specify switches that affect the Catalog St ore only , see the Adaptive Server IQ Reference Manual for more information.

Performance tuning sugges ti ons are gi ven t hro ugh ou t t his gui de. See Chapter

12, “Managing System Resources” for a full discussion of how Adaptive Server IQ uses memory, disk, and processors, the effect of u ser connections o n resource use, and options you can set to control resource use.

Some platform-specific tuning suggestions are presented in this guide. See also the Adaptive Server IQ Installation and Configuration Guide for your platform.

Setting memory switches

Adaptive Server IQ uses memory for a variety of purposes:

• Buffers for data read from disk to resolve queries

• Buffers for data read from disk when loading from flat files

• Overhead for managing connections, transactions, buffers, and database

The switches discussed below, as well as other options you can set once the server is running, determine how much memory is available for these purposes.

IQ buffer cache sizes

Normally, you set the buffer cache size for the IQ main and temporary stores using the

Temp_Cache_Memory_MB options. If you set IQ buffer cache sizes higher than

your system will accommodate, however, Adaptive Server IQ cannot open the database.

objects

SET OPTION command to set the Main_Cache_Memory_MB and

Using command-line switches

To override these settings for the current server session, specify the server startup options database and reset the defaults. The default sizes are 8MB for the main cache and 4MB for the temporary cache, which are too low for any active database use.

-iqmc (main cache size) and -iqtc (temp cache size) to open the

Concurrent users

Concurrent queries

Your license sets the absolute number of concurren t users. Ho wever , you must also set the

-gm switch. This required switch lets you limit the number of

concurrent user connections on a particular server. The

-gn switch sets the number of execution threads that will be used for the

Catalog Store and connectivity while running with multiple users. It applies to all operating systems and servers.

On Windows NT you need to specify this parameter in the

asiqsrv12 command.

To calculate its value use the following formula:

gn_value

gm_value

- (( 2 *

num_CPUs

) + 10)

Specify a minimum of 25. On UNIX platforms, the

start_asiq utility sets this parameter. See the Adaptive

Server IQ Installation and Configuration Guide for your platform for more

information. There may be times when you want to tune performance for a particular

operation by limit ing the number of us er connections to fe wer than your license allows. Alternatively, you may want to use the

-iqgovern switch to control

query use; see “Concurrent queries.” The -iqgovern switch lets you specify the number of concurrent queries on a

particular server. This is not the same as the number of connections, which is controlled by your licen se. By specifying the

-iqgovern switch, you can help IQ

optimize paging of buff er data out to disk, and avoid o vercommitting memo ry . The default value of

-iqgovern is (2 x the number of CPUs) +10.

Wired memory

The -iqwmem switch creates a pool of “wired” memory on certain UNIX platforms only. This memory is locked down so that it cannot be paged. Wired memory can improve performance on Tru64, HP and Sun platforms. Specify this switch as the number of megabytes of wired memory.

Warning! Use this switch only if you have enough memory to dedicate some

of it for this purpose. Otherwise, you can cause serious performance degradation.

CHAPTER 2 Running Adaptive Server IQ

Unwired memory

Number of processing threads

Catalog Store cache size

The -iqsmem switch creates a memory pool to increase total available memor y . This switch is available on all UNIX platforms, but is required in some cases:

• On HP systems use

-iqsmem if you want to use more th an 2GB of memory .

The value should be between 500 and 1400MB.

• On AIX systems always use

-iqsmem. The value for -iqsmem should be

between 356 and 2560; otherwise, the server aborts.

Specify this switch as the number of megabytes of memory. The maximum value for

-iqsmem is 2000. For example, to add 1GB of unwired memory you

specify:

-iqsmem 1000

Use the -iqmt switch to set the number of processing threads that Adaptive Server IQ can use. Adaptive Server IQ assigns varying numbers of kernel threads to each user connection, based on the type of processing being done by that process, the total number of threads available, and the setting of various options. Increasing the number of threads can improve performance.

Use the -c switch to set the amount of memory in the cache for the Catalog Store. The default initial cache size is computed based on the amount of physical memory, the operating system, and the size of the database files. On Windows NT, the database server takes additional cache for the Catalog when the available cache is exhausted.

For many Adaptive Server IQ and Java appl ications, you need to raise the size of the Catalog cache above the default value of 2MB. Any cache size less than 10000 is assumed to be in KB (1K =1024 bytes); any cache size 10000 or greater is assumed to be in bytes. You can also specify the cache size as nK or nM.

Both

start_asiq and the asiqdemo.cfg configuration file set this parameter to

16MB.

Note The cache size for the IQ Store does not rely on the Catalog cache size.

See “IQ buffer cache sizes.”

Setting switches that affect timing

Checkpoint interval

Adaptive Server IQ uses checkpoints to generate reference points and other information that it needs to recover databases. Use th e maximum desired length of time (in minutes) that the database server will run without doing a checkpoint. The default value is 60 minutes.

-gc switch to set the

Using command-line switches

When a database server is running with multiple databases, the checkpoint time specified by the first database started will be used unless overridden by this switch. If a value of 0 is entered, the default value of 60 minutes is used.

Recovery time

The -gr parameter lets you set the maximum number of minutes that the database server will take to recover from system failure. When a database server is running with multiple databases, the recove ry time specified by the first database started will be used unless overridden by this switch.

Other performance-related switches

Several switches help you tune network performance. They include -gb (database process priority on Windows NT), and -p (maximum packet size).

Controlling permissions from the command line

Some command-line options control the permissions required to carry out certain global operations.

Starting and stopping databases

The -gd optio n allows you to limit the users who can start a database on a running server to those with a certain level of permission in the database to which they are already connected:

•

DBA (the default) —Only the DBA can start an extra database.

•

ALL—Any user can start and stop databases.

•

NONE—No one can start or stop a database on a running server.

Sybase recommends that only the DBA be allowed to start and stop production databases.

Creating and deleting databases

Note If you do not set -gd ALL when you start the server, only the DBA can

start additional databases on that server. This means that users cann ot con nect to databases that are not already started, either at the same time as the server, or since then by the DBA.

The -gu option allows you to limit the users who can stop the server to users with a certain level of permission in the database to which they are connected.

•

DBA—Only the DBA can create and drop databases.

•

ALL (default)—Any user can create and drop databases.

•

NONE—No user can create or drop a database.

CHAPTER 2 Running Adaptive Server IQ

•

UTILITY_DB—Only those users who can connect to the utility_db database

can create and drop databases. See “The utility database” on page 18 for information.

Stopping the server

The -gk option limits the users who can shut down a server to those with a certain level of permission in the database.

•

DBA (default) —Only the DBA can stop the server.

•

ALL (default)—Any user can stop the server.

•

NONE—No user can shut down the server with the STOP ENGINE

command.

Setting a maximum Catalog page size

The database server cache is arranged in pages, which are fixed-size areas of memory. Since the server uses a single cache for the Catalog Store until it is shut down, all Catalog pages must have the same size.

A Catalog file is also arranged in pages, of size 1024, 2048, or 4096 bytes. Every database page must fit into a cache page.

You use the the maximum size, 4096, you maximize the number of col u mns per tab le that Adaptive Server IQ can support.

By default, the server page size is the same as the largest page size of the databases on the command line. The server starts, you cannot load a database with a larger Catalog page size than the server. Unless you specify Catalog page size larger than the databases started on the comma nd line will fail.

-gp option to set the Catalog page size explicitly. By setting -gp to

-gp option overrides this default. Once the

-gp , an attempt to load a database file with a

If you use larger page sizes, remember to increase yo ur cache size. A cache of the same size will accommodate only a fraction of the number of the larger pages, leaving less flexibility in arranging the space.

Note The -gp optio n and the page sizes listed here apply to the Catalog Store

only. You set the page size for the IQ Store in the the

CREATE DATABASE command. See “Choosing an IQ page size” for more

IQ PAGE SIZE parameter of

information.

Using command-line switches

Setting up a client/server environment

Three switches can help you set up your client/server environment.

•

-x specifies communication protocol options.

•

-tl sets the network connection timeout.

•

-ti sets the client connection timeout.

See the sections that follow for details.

Selecting communications protocols

Any communications between a client application and a database server require a communications protocol. Adaptive Server IQ supports a set of communications prot ocol s for communications across networks and for samemachine communications.

The database server supports the following protocols:

• Shared memory is used for same-mach ine communication s, and is loaded by default (unless the

• TCP/IP is supported on all platforms.

-hs parameter is specified on startup).

Specifying protocols

• IPX is supported on Windows NT (client and server) and Windows 95 (client only).

• NetBIOS is supported on Windows NT (client and server) and Windows 95 (client only).

• Named pipes is supported o n W indows NT only. Named Pipes is provided for same machine communications to and from Windows 3.x client applications using ODBC or Embedded SQL.

By default, the database server starts up all available protocols. You can limit the protocols available to a database server by using the

–x command-line

switch. At the client side, many of the same options can be controlled us ing the

CommLinks connection parameter.

The following command starts a server using the TCP/IP protocol:

asiqsrv12 -x "tcpip"

The quotes are not strictly required in this example, but are needed if there are spaces in any of the arguments to

–x. If you omit this switch and you are using

TCP/IP, or if you do not specify a port number, the default port 2638 is used.

Additional parameters can be added to tune the behavior of the server for each protocol. For example, the following command line instructs the server to use two network cards, one with a specified port number. This command must be entered all on one line, even though it appears on multiple li nes here.

asiqsrv12

-x "tcpip(MyIP=192.75.209.12:2367,192.75.209.32)"

-gm 10 -gp 4096 path\asiqdemo.db

For detailed descriptions of network communications parameters that can serve as part of the

the Adaptive Server IQ Reference Manual.

Limiting inactive connections

Adaptive Server IQ uses two parameters, -tl and -ti, to determine when it should close user connections.

CHAPTER 2 Running Adaptive Server IQ

–x switch, see “Network communications parameters” in

Setting the default network timeout

A liveness packet is sent periodically across a client/server TCP/IP or IPX communications protocol to confirm that a connection is intact. If the server runs for a liveness timeout period (default 2 minutes) without detecting a liveness packet, the communication is severed. The server drops any connections associated with that client. There is no warning. All activity that falls within any open transaction is rolled back.

The

–tl switch on the server sets the liveness timeout, in seconds, for all clients

that do not specify a

–tl switch when they connect. Liveness packets are sent at

an interval of the (liveness timeout)/4. You may want to set a higher value for this switch at the server level. Many

users, especially those who have used earlier versions of Adaptive Server IQ, will not expect to be disconnected after only 2 minutes of inactivity.

Try setting the liveness timeout to 300, together with the recommended value for

–ti discussed in the next sect ion. Set this switch as follows:

-tl 300

If this value does not work well, try -tl 1200, which sets the liveness timeout to 20 minutes.

Note Users who are running a client and server on the same machine do not

experience a liveness timeout.

Using command-line switches

Setting the default client timeout

Adaptive Server IQ disconnects client connections that have not submitted a request for the number of minutes you specify with the disconnecting inactive connection s, this option frees any locks those connections hold. The default is 240 (4 hours). Raising this to the recommended value, 4400 (about 73 hours), lets you start long runs at the beginning of a weekend, for example, and ensure that any interim results will not be rolled back.

Starting a server in forced recovery mode

Should you need to restart your server after a failure, you can usually do so using the same startup options as usual.

On rare occasions, you may need to supply startup options to force recovery or to recover leaked storage. T o start the server with these options, see the chapter

“System Recovery and Database Repair” in the Adaptive Server IQ Troubleshooting and Error Messages Guide.

Starting a server from DBISQL

If you are already connected to a running database server, you can start a new server from DBISQL. Use the server from DBISQL.

START ENGINE command to start a named

-ti switch. By

Example

Note This method is not recommended for most situations. If you use it be sure

you are starting the server on the system you intend, that you include appropriate server parameters in the

STARTLINE, and that environment

variables are set appropriately on the system where the server will start.

The following DBISQL command, entered on o ne line, starts a database server, names it

jill_newserv, and specifies the network connection, number of

connections, and Catalog page size.

START ENGINE AS jill_newserv STARTLINE ’asiqsrv12 -x tcpip(port=5678) -gm 10 -gp 4096’

CHAPTER 2 Running Adaptive Server IQ

Starting multiple servers or clients on the same machine

In a production envi ronment, it wo uld be unusual to have more th an one server running on the same system. In a development en vironment, however, this situation can occur.

If you are running more than one server or client on the same UNIX machine, and shared memory is enabled, you must take certain precautions to prevent users from connecting to the wrong server. To avoid conflicts when using shared memory, consider doing one or more of the following:

• Create a temporary directory dedi cated to each server . Make sure that each

client uses the same temporary directory as its server by setting the ASTMP environment variable explicitly on bo th systems. For details about setting environment variables, see the Adaptive Server IQ Reference Manual.

• Create a data source name in the .odbc.ini file (on UNIX) for each server

and provide detailed connection information. For details, see the Adaptive Server IQ Installation and Configuration Guide.

• Use connection strings that specify explicit parameters instead of relying

on defaults.

• Confirm connections by issuing the following command:

SELECT "database name is" = db_name(), "servername_is" = @@servername

Monitoring server activity

It may be helpful, especially for new users, to monitor server activity. Using commands appropriate for your platform, you can direct Adaptive Server IQ to capture server activity in a log file.

Unix server log file

When you start a server on a UNIX system with the start_asiq utility, server activity is logged in an ASCII text file placed in the directory defined by $ASLOGDIR. (If $ASLOGDIR is not defined, it defaults to $ASDIR/logfiles.)

The log file name has this format:

your_server_name.###.srvlog

Each time you start the server, the number is incremented. For example, your directory may look like this:

Monitoring server activity

demo.001.srvlog demo.002.srvlog janedemo.001.srvlog

For information about your most recent session, choose the log with the largest number for the desired server . Issue a

tail –f command to view the log contents.

For example:

% tail -f demo.002.srvlog

When you run start_asiq, specify the –z option to enhance the log file with additional information about connections. This will help new users or those troubleshooti ng conn ect io n problems.

On UNIX systems, there are two ways to check if a particular server is running:

• Log into the machine where the server was started, and issue the command:

% ps -eaf | grep asiqsrv12 maryc 24836 25554 0 Feb 09 - 17:36 asiqsrv12 -c 16m -gc 6000 -gd all

-gr 6000 -gm 10 -gp 4096 -ti 4400

-tl 300 -iqmt 450 -iqsmem 2560 @fnma.cfg asiqdemo.db janed 28932 38122 0 11:39:24 - 2:10 asiqsrv12 -c 16m -gc 6000 -gr 6000

-gm 10 -gp 4096 -ti 4400

-tl 300 -iqsmem 2560 -n janedemo -gd all

-iqmt 256 -x tcpip(port=1872)

Windows server log file

•Use the stop_asiq utility, described in the following section, which displays all Adaptive Server IQ processes running.

On Windows systems, look in the system tray for one or m ore Adaptive Server IQ icons. Place the cursor over each icon and read the server name.

On Windows systems, use the -o para meter on the asiqserv12 startup command to create a log file of server activity. For example, to save output to a file named results, start the server as follows:

asiqsrv12 -o results

Stopping the database server

The preferred ways to stop the database server are:

CHAPTER 2 Running Adaptive Server IQ

• In UNIX, use the

stop_asiq utility. For details, see “Example — Stop a

server with stop_asiq”. Note that when

"Please note that ’stop_asiq’ will shutdown a server completely without regard for users connections or load processes status. For a finer level of detail the utility ’dbstop’ has the options to control whether a server is stopped based on active connections."

stop_asiq is used, the following message appears:

• In Windows NT, click Shutdown on the database server display or right-

click the IQ icon in the system tray and select Exit.

Normally, you should not shut down a server while it is still connected to one or more clients. If you try this, you get a warning that any un committed transactions will be lost. Disconnect or close all the clients and try again.

You can also stop the database server in the following ways:

• At the operating system command line, issue the

DBSTOP command with

appropriate parameters. Use the same parameters as when you started the server . Without the proper connection parameters

DBSTOP doesn't know

how to connect to the server to tell it to shutdown. For details on using

DBSTOP, see Chapter 4, “Database Administration Utilities” in the

Adaptive Server IQ Reference Manual.

• In a DBISQL window or command file, issue the

STOP ENGINE

command to stop a named database server.

• In UNIX, in the window where the database server was started, type:

This command does not work if you have redirected input to a different device.

Stopping the database server

Example — Stop a server with stop_asiq

The following example uses the stop_asiq utility on UNIX systems to shut down an Adaptive Server IQ server and close all user connections to it.

When you issue the

stop_asiq command, Adaptive Server IQ lists all the

servers owned by other users, followed by the server(s) you own. It then asks if you want to stop your server. For example:

% stop_asiq Checking system for ASIQ 12 Servers ... The following 3 server(s) are owned by other users.

## Owner PID Started CPU_Time

-- --------- ----- -------- -------- -----------------------

hsin 19895 Mar.21 1:33 asiqsrv12 -c 16m -gd all -gm 10 -gn 25 -gp 4096 -ti 4400 -tl 300

-n hsin -x tcp qadaily 24754 01:25:07 1286:53 asiqsrv12 -gn 25 @/express1/qa/daily/engine/new.cfg asiqdemo.db

-o /express1/qa washburn 28350 Apr.11 0:20 asiqsrv12 -gn 25 @asiqdemo.cfg -o /express1/users/washburn/mysybase12.4.0/asiq1

The following 1 server(s) are owned by ’janed’ ## Owner PID Started CPU_Time

-- --------- ----- -------- -------- ----------------------1: janed 2838 15:11:37 0:07 asiqsrv12 -c 16m -gd all -gm 10 -gn 25 -gp 4096 -ti 4400 -tl 300 @asiqdemo.cfg

-Please note that ’stop_asiq’ will shutdown a server completely without regard for users connections or load processes status. For a finer level of detail the utility ’dbstop’ has the options to control whether a server is stopped based on active connections.

Do you want to stop the server displayed above <Y/N>?

To shut down the server, type Y (yes). Messages like the following display:

Shutting down server (2838) ... Checkpointing server (2838) ... Server shutdown.

T o leave the ser ver ru nnin g, type N (no). You return to the system prompt and IQ does not shut down the server.

If no running servers were started by your user ID, Adaptive Server IQ displays information about servers run by other users, then a message like the following:

There are no servers owned by ’janed’

CHAPTER 2 Running Adaptive Server IQ

Example —Stop a server from DBISQL

The following example stops a server from DBISQL:

STOP ENGINE Ottawa UNCONDITIONALLY

The optional ke yword UNCONDITIONAL LY specifies that the database server will be stopped even if there are connections to it.

Note Y ou can sto p a server from DBISQL if you are connected as DBA to one

of the databases running on that server (including the the server was started with the -gk ALL option.

Who can stop the server?

When you start a server, you can use the -gk option to set the level of permissions required for users to stop the server. The default level of permissions requir ed is you set it to production environment, Sybase strongly recommends that only the DBA be allowed to stop the database server.

Interactively , of course, anyone at the machine where the server was started can click Shutdown (NT only) or type

utility_db database), or if

DBA, but you can also set the value to ALL or NONE. If

NONE, even the DBA cannot execute STOP ENGINE. In a

q on the server window.

Shutting down operating system sessions

Always stop the database server explicitly before closing the operating system session.

If you close an operating system session where a database server is running, or if you use an operating system command to stop the datab ase server (other than the UNIX command shown in the previous sectio n), the server shuts down, bu t not cleanly . Next time the database is load ed, recov ery hap pens automatically. For information on system recovery, see Adaptive Server IQ Troubleshooting and Error Messages Guide.

Starting and stopping databases

Examples of commands that do not stop a server cleanly include:

• Stopping the process in the Windows NT Task Manager Processes window.

• Using a UNIX

kill command.

Starting and stopping databases

Y ou can start databases when you start the server, or after the server is ru nning. T o start a database when you start the server , see “Starting the database server” on page 21 for details.

A database server can have more than one database in use at a time. However, it is more common to run one database per server, especially in a production environment.

Starting a database on a running server

There are several ways to start a database on a running server.

• To start a database from DBISQL or Embedded SQL, use the

DATABASE

statement. For a description, see the chapter “SQL

Statements” in the Adaptive Server IQ Reference Manual.

• T o start and con nect to a database from DBISQL or Sybase Central, use a data source that specifies the database file. See “W orking with ODBC data sources”.

• T o start and connect to a database when you start DBISQL from a system command prompt, include the parameter parameters. See “Connecting to a database from DBISQL”.

“DBF=

db-file

” in the connection

START

Page size limitations

• To start a database from Sybase Central, see Chapter 4, “Managing Databases with Sybase Central” in Introduct io n to Adap ti ve Server IQ

• To start an embedded database, while connected to a server, connect to a database using a DBF parameter. This parameter specifies a database file for a new connection. The database file is loaded onto the current server.

The server holds databa se information in memory usi ng pa ges o f a fi xed s ize. Once a server has been started, you cannot load a database that has a larger Catalog page size or IQ page size than the server. For this reason, you should always set the Catalog page size to its maximum value, 4096 by tes, with the

-gp switch.

CHAPTER 2 Running Adaptive Server IQ

Permission limitations

The -gd server command-line option determines the permission level required to start databases. By default, this option is set to database administrator privileges can start IQ databases. However, you can

Stopping a database

also set this option to database.

NONE means that no users, including the DBA, can start a database.

You can stop a database in the following ways:

ALL or NONE. ALL means that all users can start a

• Disconnect from a database started by a connection string. The database

stops automatically when the last user disconnects from it, unless you explicitly set the

AUTOSTOP connection parameter to NO.

• From DBISQL or Embedded SQL, use the For information, see the Adaptive Server IQ Reference Manual.

Starting the asiqdemo database

You can start the server and the asiqdemo database easily, using the configuration file that Adaptive Server IQ provides. This config uration file, called asiqdemo.cfg, contains all the parameters necessary to start the sample database.

❖ To start the server and asiqdemo database on UNIX operating systems:

• From a command line, type the following command:

DBA, so that only users with

STOP DATABASE statement.

Behind the scenes

%cd $ASDIR/demo %start_asiq @asiqdemo.cfg asiqdemo

These commands use the configuration file asiqdemo.cfg that is created automatically at installation. Y ou can edit this file to change the parameters you use to start the sample database. For example, the server name in this file is hostname_asiqdemo. You can rename the server to a unique name of your choice, like

❖ To start the server and asiqdemo database on a Windows NT system:

janed_server.

• Click Start on the Taskbar, and select Programs→Adaptive Server IQ

12.0→ Sample Database.

The command that executes when you perform these steps is:

path\win32\asiqsrv12 @demo\asiqdemo.cfg demo\asiqdemo.db

Starting and stopping Sybase Central

where path is your Adaptive Server IQ installation directory, demo\asiqdemo.cfg specifies the configuration file, and demo\asiqdemo.db is

the sample database file. The

asiqsrv12 command starts the server in a

dedicated window . You can start database servers by entering this command at a system command prompt, as described elsewhere in this chapter.

Starting and stopping Sybase Central

If your system supports a graphical user interface, you will use Sybase Central to perform many administrative tasks. This guide gives summa ry instructions for using Sybase Central. For more information, see the Introduction to Adaptive Server IQ, or use the online help available within Sybase Central.

Starting Sybase Central on UNIX Systems

Starting Sybase Central on Windows NT Systems

To start Sybase Central, change directory to $SYBASE/sybcentral and type:

% scjview

If you have added $SYBASE/asiq12/bin or $SYBASE/bin to your path, as instructed at the end of the installation, you can issue the

scjview command

from any directory. To start Sybase Central, select Start→Programs→Adaptive Server IQ

12→Sybase Cent ral Java Edition. The main Sybase Central window appears.

CHAPTER 2 Running Adaptive Server IQ

Figure 2-1: The Sybase Central Hierarchy

Plug-ins for Sybase Central, such as the Adaptive Server IQ database management system, occupy the first level in the Sybase Central hierarchy after the root level. A plug-in is a graphical tool for managing a particular product. When you install the product, you can also install its Sybase Central plug-in. When you next start Sybase Central, the new product automatically

“plugs in” to Sybase Central and appears in the main window. The right panel displays the contents of the container that has been selected in

the left panel.

Connecting a plug-in

If you do not see the plug-in for Adaptive Server IQ in the main Sybase Central window, you can connect to it manually.

❖ Connecting to a plug-in

1 Select Tools → Adaptive Server IQ 12. 2 If you do not see Adaptive Server IQ on the Connect Menu, select Tools

→ Plug-ins.

Introduction to connections

3 If Adaptive Server IQ (ASIQ) is listed, select Register. If not, select Load.

Use the Browse button to find and select the file ASIQPlugin.jar. Click OK.

Stopping Sybase Central

To stop Sybase Central, select File → Exit.

Introduction to connections

The remainder of this chapter describes how client applications connect to databases. It contains info rmati on abou t connecti ng t o datab ases from ODB C applications and application development systems, as well as from Embedded SQL applications.

Any client application that uses a database must establish a connection to that database before any work can be done. The connection forms a channel through which all activity from the client application takes place. For example,

your user ID determi nes permissi ons to carry ou t actions on the database —and the database server has your user ID because it is part of the request to establish a connection.

This sounds simple, bu t so me cl ient t ools may not clearl y in di cate con nect i on status, and a failed command is your first indication that the connection does not exist. For a novice user, a quick way to confirm the connection is by a simple

select db_name().

The syntax is:

select db_name()

to display the current database, or

select db_name([

to display any database you specify.

database_id

])

How connections are established

To establish a connection, the client application calls fun ctions in one of the supported interfaces. Adaptive Server IQ supports the following interfaces:

• ODBC — ODBC connections are discussed in this chapter.

• Embedded SQL — Embedded SQL connections are discussed in this

chapter.

• Sybase Open Client — Open Client connections are not discussed in this

chapter. For information on connecting to IQ from Open Client applications, see Chapter 14, “Adaptive Server IQ as a Data Server”

• JDBC — JDBC connections are not discussed in this chapter. For

information on connecting via JDBC, see Chapter 4, “Managing Databases with Sybase Central” in Introduction to Adaptive Server IQ. To create JDBC data sources, see the chapter entitled “Data Access Using JDBC” in the Adaptive Server Anywhere User’s Guide.

Note JDBC provides the link between the execution of Java objects and

database ope rations. For a description of Java support in Ad aptive Server IQ, see “Enabling Java in the database” on page 113.

CHAPTER 2 Running Adaptive Server IQ

Learning about connections

The interface uses connection information included in the call from the client application, perhaps together with informati on held on disk in a file data source, to locate and connect to a server running the required database. The following figure is a simplified representation of the pieces involved.

If you want ... Consider reading ...

Some examples to get started quick ly “Simple connection examples”

Introduction to connections

If you want ... Consider reading ...

A conceptual overview “Connection parameters specify

To create data sources “Working with ODBC data sources” To see an in-depth description of how

connections are establi shed To add users and grant them permissions “How Adaptive Server IQ makes

To diagnose networ k-specific conne ction issues

To learn about character set issues affecting connections

Connection parameters specify connections

When an application connects to a database, it uses a set of connection parameters to define the connection. Connection parameters include

information such as the server name, the database name, and a user ID.

connections”

“Working with ODBC data sources”

connections” “Troubleshooting network

communications” in the Adaptive Se rv er

IQ Troubleshooting and Error Messages Guide

“Connection strings and character sets” on page 338

A keyword-value pair , of the form parameter=value, specifies each co nnection parameter. For example, you specify the password connection parameter for the default password as follows:

Password=sql

Connection parameters are passed as connection strings

Connection parameters are assembled into connection strings. In a connection string, a semicolon separates each connection parameter, as follows:

ServerName=host_asiqdemo;DatabaseName=asiqdemo

Representing connection strings

This chapter has many examples of connection strings, represented in the following form:

parameter1=value1 parameter2

...

value2

This is equivalent to the following con nection string:

parameter1=value1;parameter2=value2

CHAPTER 2 Running Adaptive Server IQ

Y ou must enter a connection string on a single line, with the parameter settings separated by semicolons.

Connection parameters are passed as connection strings

Connection parameters are passed to the interface library as a connection string. This string consists of a set of parameters, separated by semicolons.

In general, the connection string built up by an application and passed to the interface library does not correspond directly to the way a user enters the information. Instead, a user may fi ll in a dialog box, or the application may read connection information from an initialization file.

Certain Adaptive Server IQ utilities accept a connection string as the command-line option and pass the connection string on to the interface library without change. For example, the following is a typical Collation utility (

dbcollat) command line for Windows NT systems. It should be entered all on

one line.

dbcollat -c "uid=DBA;pwd=SQL;dbn=asiqdemo" c:\temp\asiqdemo.col

Note DBISQL processes the connection string internally. It does not simply

pass on the connection parameters to the interface library. Do not use Interactive SQL to test command strings from a command prompt.

Simple connection examples

Although the connection model for Adaptive Server IQ is configurable, and can become complex, in many cases connecting to a database is very simple.

This section describes some simple cases of applications connecting to an Adaptive Server IQ database. When you are getting started, this section may be all you need, for example, if you are running the a local server and are not connected to a network. However, in most IQ environments, in order to ensure that y ou can connect and disconnect p roperly , a very complete set of connection parameters is essential.

-c

asiqdemo sample database on

Simple connection examples

For steps in connecting to a database us ing Sybase Central, see the Intr oduction to Adaptive Server IQ. For more detailed information on available conn ection

parameters and their use, see “Connection parameters” on page 73.

Connecting to a database from DBISQL

Many examples and exercises throughout the documentation start by connecting to the sample database from Interactive SQL, also called DBISQL. Here is how to carry out this step.

Note T o avoid ambiguity, specify connection parameters for DBISQL instead

of relying on defaults. You can specify connection parameters in a command line or an initialization file such as .odbc.ini or odbc.ini. For a complete list, see Chapter 3, “Connection and Communication Parameters” in Adaptive Server IQ Reference Manual.

If more than one database is started on a server, for example, you should specify the database name. In a network with subnets, specify the parameter with protocol options including the host number.

In the .odbc.ini file, you must use the long form of each parameter. For example, use DatabaseFile instead of DBF.

CommLinks

If your parameters are incomplete or incorrect, you may see an error such as

Database name required to start engine

❖ To connect from a UNIX system:

1 Make sure that your PATH and other environment variables are correctly

set, as described in Chapter 1, “File Locations and Installation Settings” in the Adaptive Server IQ Reference Manual.

2 To ensure that the sample database is loaded on a running server, at the

UNIX prompt enter:

ps -eaf | grep asiqdemo

If you need to start the sample database, enter:

cd $ASDIR/demo start_asiq @asiqdemo.cfg asiqdemo

3 If you have not already don e so, change to you r home directory ($HOME)

and issue the following command to copy the terminfo extension file default.tix into it:

CHAPTER 2 Running Adaptive Server IQ

% cp $SYBASE/asiq12/tix/default.tix

This file controls key sequences for DBISQL and im proves the comm and

window display. For more information, see Chapter 6, “Getting Started with DBISQL” in Introduction to Adaptive Server IQ.

4 Start DBISQL by entering

dbisql -c "uid=DBA;pwd=SQL;eng=

servername

;links=tcpip"

at the command line. Make sure that the value supplied for the servername is the same server name that was supplied in the

start_asiq command to

start the server. The

–c parameter specifies connection parameters. You can also specify

these parameters in a data source, as described later in this chapter.

Note links=tcpip (or CommLinks=tcpip) is only required if you use

TCP/IP to connect to the database. If you use the shared memory port to connect to a local database you can omit the

links parameter; however,

it is always safer—and required on some platfo rms—to inclu de complete network parameters.

To connect to a database on a foreign host, you must add the host. For example:

dbisql -c "uid=DBA;pwd=SQL;eng=SERV1_asiqdemo; links=tcpip(host=

SERV2

If the host was started with a non-default port num ber (not 2638) then the port number must be added also. For example:

dbisql -c "uid=DBA;pwd=SQL;eng=SERV1_asiqdemo; links=tcpip(host=SERV2;port=1234)"

If you prefer , use this alternate form of the links clause, which has the sam e result:

links=tcpip(host=SERV2:1234)"

❖ To connect from a Windows NT system

1 Select Start → Προγραµσ → Sybase Adaptive Server IQ 12 →

Interactive SQL, or at the NT command prompt enter

dbisql

Simple connection examples

2 Enter the user ID

3 Click the Database tab and type the server name (for example,

4 If you use TCP/IP to connect to databases, on the Network tab, click on

You can include the -c parameter to specify connection parameters in the

dbisql command, as described in the procedure above for connecting to

UNIX. If you omit these parameters, the DBISQL log on window appears.

DBA

and the password

SQL

This is the default user ID and password for Adaptive Server IQ databases when they are created.

“hostname_asiqdemo” for the asiqdemo database).

TCPIP. (If you use the shared memory port for connecting, skip this step.) If your database is on a remote machine, you must add hos t informati on in

the space beside TCPIP by typing “host=servername:nnnn” where servername is the name of your system and n nnn is your port numb er. (The default port number is 2638, but if the host was s tarted with a different number, use that instead.)

5 Click OK to connect to the database. 6 After you connect to the database, the DBISQL window appears. The

DBISQL window displays the database name, user ID, and server name for the connection on its title bar. The words “Connected to database” appear in the Statistics window along with a message displaying the collation sequence used by the database.

You can connect to any database server that is already running in the same manner. You can also specify a non-default character set and language.

For more information on using DBISQL, see the chapter “Getting Started with DBISQL” in the Introduction to Adaptive Server IQ.

Connecting to other databases from DBISQL

The following procedure shows how to connect to a running database from DBISQL.

CHAPTER 2 Running Adaptive Server IQ

❖ To connect to a database from DBISQL on UNIX:

1 Start the server and t h e dat abas e by ty pi ng at a sy stem command prompt:

start_asiq

dbname

2 Start DBISQL by typing at a system command prompt:

dbisql -c "uid=userID;pwd=password;eng=

dbname

;links=tcpip"

For example, to connect to the sample database you would enter:

dbisql -c "uid=DBA;pwd=SQL;eng=asiqdemo;links=tcpip"

The –c parameter specifies connection parameters. See “Connection parameters” for more about connection parameters.

To connect to a system on a foreign host, you must add the host:

dbisql -c "uid=DBA;pwd=SQL;eng= links=tcpip(host=

hostname

dbname

;

If the host was started with a non-default port num ber (not 2638) then the port number must be added as well:

dbisql -c "uid=DBA;pwd=SQL;eng=anotherdb; links=tcpip(host=

hostname

;port=

nnnn

Connecting to an embedded database

An embedded database, designed for use by a single application, runs on the same machine as the application and is largely hidden from the application user.

When an application uses an embedded database, the database server is generally not running when the application connects. In this case, you can start the database using the conn ection stri ng, and by sp ecifying th e database fi le in the DatabaseFile (DBF) parameter of the connection string.

Using the DBF parameter

The DBF parameter specifies which database file to use. The database file automatically loads onto the default server , or starts a server if no ne is running.

The database unloads when there are no more connections to the database (generally when the application that started the connection disconnects). If the connection started the server, it stops once the database unloads.

Simple connection examples

The following connection parameters show how to load the sample database as an embedded database:

where path is the name of your Adaptive Server IQ installation directory.

dbf=path\asiqdemo.db uid=dba pwd=sql

Using the Start parameter

Example: connecting from DBSQL

The following connection parameters show how you can customize the startup of the sample database as an embedded database. This is useful if you wish to use command-line options, such as the cache size:

Start=asiqsrv12 -gm 10 -gp 4096 -c 8M dbf=path\asiqdemo.db uid=dba pwd=sql

Extra cache needed for Java

If you are using Jav a in an embe dded data base, you s hould u se th e start lin e to provide more than the default cache size. For development purposes, a cache size of 8 MB is sufficient.

In this example, the sample database is an embedded database within DBSQL.

❖ To connect to an embedded database from DBSQL in Windows NT:

1 Start DBISQL with no databases running. You can use either of the

following ways:

• From the W indows NT Start men u, choose Sybase→Adaptive Server

Anywhere →Interactive SQL.

•Type

dbisql at a system command prompt.

When DBISQL starts, it is not connected to any database.

2 Type CONNECT in the command window, and press F9 to execute the

command. The connection dialog appears.

3 If you have an ODBC data source for your database, select that data

source.

4Enter

DBA as the user ID and SQL as the password. Then click the Database

tab. Enter the full path of the sample database in the Database File field. For example, if your installat ion directo ry is c:\sybase\asiq12 you shoul d enter the following:

c:\sybase\asiq12\asiqdemo.db

5 Leave all other fields blank, and click OK. Adaptive Server IQ starts up

and loads the sample database, and DBISQL connects to the database.

Connecting using a data source

You can save sets of conn ection par amete rs in a data sou rce. Data sour ces can be used by ODBC and Embedded SQL applications like DBISQL. You can create data sources from the ODBC Administrator; see “Creating and editing

ODBC data sources” for details. The following procedure shows how to connect to the sample database from

DBISQL using a data source.

❖ To connect using a data source:

1 Start DBISQL with no databases runn ing.

CHAPTER 2 Running Adaptive Server IQ

The asiqdemo data source

• On UNIX, type

dbisql at a system command prompt.

• On Windows NT, from the Start menu choose Programs→Sybase → Adaptive Server IQ 12.0 → Interactive SQL.

2Enter

DBA as the user ID and SQL as the password.

3 Specify the data source. On Windows NT, you can select from the drop-

down list of ODBC data sources; for the sample database, select ASIQ12 Sample. On UNIX you must enter it in the ODBC Data Source field.

4 For the sample database, leave all other fields blank, and click OK.

Adaptive Server IQ starts up and loads the sample database, and Interactive SQL connects to the database. For other databases you may need to provide additional information, depending on your data source.

Note You can also specify the data source name by including the dsn

connection parameter in the dbisql command, as follows:

dbisql -c "dsn=ASIQ12 Sample"

The asiqdemo data source holds a set of connection parameters, including the database file and a Start parameter to start the sample database. The server name in this data source is “hostname_asiqdemo” where hostname represents your system name.

Simple connection examples

Connecting to a server on a network

T o connect to a database running on a network server somewhere on a local or wide area network, the client software must be able to locate the database server. Adaptive Server IQ provides a network library (a DLL or shared library) that handles this task.

Network connections occur over a network protocol. Several protocols are supported, including TCP/IP, IPX, and NetBIOS.

Specifying the server

Specifying the protocol

Adaptive Server IQ server names are unique on a local domain for a given network protocol. The following connection parameters provide a simple example for connecting to a server running elsewhere on a network:

eng=

svr_name

dbn=

db_name

uid=

user_id

pwd=

password

CommLinks=all

The client library first looks for a local server of the given name, and then looks on the network for a server of the specified name.

The above example finds any server started using the default port number. However, you can start servers using other port numbers by providing more information in the CommLinks parameter. For information on this parameter,

see Chapter 3, “Connection and Communication Parameters” in Adaptive Server IQ Reference Manual.

If several protocols are available, you can tell the network library which ones to use. The following parameters use only the TCP/IP protocol:

eng=

svr_name

dbn=

db_name

uid=

user_id

pwd=

password

CommLinks=tcpip

CHAPTER 2 Running Adaptive Server IQ

The network library searches for a server by broadcasting over the network, which can be a time-consuming process. Once the network library locates a server, the client library stores its name and network address in a file. Users should never need to use this file directly. Adaptive Server IQ reuses this entry for subsequent connection attempts, which can be many times faster than a connection that is achieved by broadcast.

Many other connection parameters are available to assist Adaptive Server IQ in locating a server efficiently over a network. For more information see

“Network communications parameters” in the Adaptive Server IQ Reference Manual.

To see how you can include connection parameters in an ODBC data source, see “Creating and editing ODBC data sources”.

Note In a subnetted network environment, it is possible to have multiple

servers with the same name and port number running on different nodes in different subnets. This is true because in most situations, routers are not programmed to pass broadcast messages between subnets. If you are running in a subnetted environment, it is always safest to use specific host, port numbers, and server names to guarantee that you are connecting to the proper server and database. This is particularly true when using de fault connection parameters, and is required on AIX platforms.

Using default connection parameters

You can leave many connection parameters unspecified, and instead use the default behavior to make a connection.

Note Be extremely cautious about relying on default behavior in production

environments, especially if you distribute your application to customers who may install other Adaptive Server IQ or Adaptive Server Anywhere applications on their machine.

Default database server

If you are connecting to a database on your local server, and more than one database has been started on that server, you need to specify the database you wish to connect to, but you can leave the server as a default:

dbn=

db_name

uid=

user_id

Simple connection examples

Note Do not use these parameters if more than one local server is running, or

you may connect to the wrong server.

pwd=

password

Default database

If more than one server is runnin g, you need to sp ecify whic h one you wish to connect to. If onl y one database ha s been started on that server , you do n ot need to specify the database name. The following connection string connects to a named server, using the default database:

eng=

server_name

uid=

user_id

pwd=

password

No defaults

The following connection string connects to a named server, using a named database:

eng=

server_name

dbn=

db_name

uid=

user_id

pwd=

password

For more information about default behavior, see “How Adaptive Server IQ makes connections”.

Connecting from Adaptive Server IQ utilities

Adaptive Server IQ database utilities that communicate with the server (rather than acting directly on database files) do so using Embedded SQL. They follow the procedure outlined in “How Adaptive Server IQ makes connections” when connecting to a database.

How database utilities obtain connection parameter values

Many of the administration utilities obtain the connection parameter values by: 1 Using values specified on the command line (if there are any). For

example, the following command starts the collation utility on the sample database on the default server, using the user ID

DBA and the password SQL

and the asiqdemo.col collation file:

dbcollat -c "uid=DBA;pwd=SQL;dbn=asiqdemo" c:\temp\asiqdemo.col

CHAPTER 2 Running Adaptive Server IQ

2 Using the SQLCONNECT environment variable settings if any command

line values are missing. Adaptive Server IQ database utilities do not set this variable automatically. For a description of the SQLCONNECT

environment variable, see Chapter 1, “Environment Variables and Registry Entries,” in Adaptive Server IQ Reference Manual.

3 Prompting you for a user ID and password to connect to the default

database on the default server, if parameters are not set in the command line or the SQLCONNECT environment variable.

For a description of command-line switches for each database utility, see Chapter 4, “Database Administration Utilities” in the Adaptive Server IQ Reference Manual.

Working with ODBC data sources

You can store a set of Adaptive Server IQ connection parameters as a data source. Data sources are required to use applications that connect using the Open Database Connectivity (ODBC) interface.

Embedded SQL can use data sources

Microsoft Corporation defines the ODBC interface, which is a standard interface for connecting client applications to database management systems in the Windows and Windows NT environments. Many client applications, including application development systems, use the ODBC interface to access a wide range of databas e systems.

Although data sources are especially designed for W indows and W indows NT, Adaptive Server IQ allows you to create and use them on UNIX servers as well. This allows ODBC–based client applications to connect to databases on UNIX servers.

When you connect to a database using ODBC , you use an ODBC data sou rce. The data source contains a set of connection parameters. You need an ODBC data source on the client computer for each database you will connect to.

If you have a data source, your connection string can simply name the data source to use.

Embedded SQL applications such as Interactive SQL and the other Adaptive Server IQ database administration utilities can als o use ODBC data sources, even though they are not ODBC applications.

Working with ODBC data sources

DSNs and FILEDSNs

You specify a data source either as a DSN (data source name) or as a FileDSN (file data source name).

You can reference a data source in the Windows NT registry using the DSN connection parameter:

DSN=

You can reference a data source held in a file using the FileDSN connection parameter:

FileDSN=

DSNs and FileDSNs differ only in how they are stored, and how you create them. With the exception of encrypted passwords, you can put identical connection informat ion in them. You can use both DSNs and FileDSNs on any platform.

my data source

mysource

.dsn

Where DSNs and FileDSNs are stored

File data sources can be distributed

How you create DSNs and FILEDSNs

A DSN, or Data Source Name, is stored in the file odbc.ini and in the registry on Windows NT systems. On UNIX platforms it is stored in the odbc.ini file only.

A FileDSN, or File Data Source Name, is always stored on a file on all platforms.

File data sources can easily be distributed to end users, so that connection information does not have to be recon struc ted on each machin e. It can be sent via email, for example, but is not stored automatically in any public place. If the file is placed in the default location for file data sources, it is picked up automatically by ODBC. In this way, managing connections for many users can be made simpler.

Note Because DSNs are stored in the NT registry , they are public information.

For this reason you shou ld not put a pas sword in a DSN, unless you encr ypt it. If you want to store your password in your data source, use a File DSN.

To create DSNs on NT systems, use the ODBC Administrator; do not edit

odbc.ini directly. See “Creating and editing ODBC data sources” for details. To create File DSNs on Windows NT systems, use the ODBC Administrator.

See “Creating and editing ODBC data sources”.

CHAPTER 2 Running Adaptive Server IQ

To create or edit DSNs or File DSNs on UNIX systems, use a text editor. For DSNs you can edit the .odbc.ini file directly. For File DSNs, create a file with the name you choose, using the file extension .dsn.

Note Sybase recommends that, to avoid ambiguity, you be as specific as

possible in creating ODBC and other data sources, whether you create them using the ODBC Administrator, or by editing odbc.ini, .odbc.ini, or .dsn files directly. If more than one database is started on a server, for example, you should specify the database name, and in a network with subnets, specify the

CommLinks parameter including the host number when editing files; include

the host number in the network protocol options on the Network tab in the ODBC Administr a t or.

If connection parameters are incomplete or incorrect, you may see an error such as

Database name required to start engine

For a complete list of connection parameters, see Chapter 3, “Connection and Communication Parameters” in Adaptive Server IQ Reference Manual.

Examples of connection strings using data sources

The following connection string specifies an ODBC Data Source Name and a user ID.

DSN=ASIQ sample;uid=DBA

The following connection string specifies a File Data Source Name, with a user ID and passw ord.

FILEDSN=ASIQ on UNIX;uid=DBA;pwd =SQL

Creating and editing ODBC data sources

You need an ODBC data source on the client computer for each database you wish to connect to. Y ou prob ably already have an odbc.ini file on your system.

On Windows NT, the ODBC Administrator provides a central place for managing ODBC data sources. The following procedure uses the ODBC Administrator to add a new data source to your existing odbc.ini, or creates a new file if necessary.

T o create ODBC data sources on UNIX systems, see also “Using ODBC data sources on UNIX”.

Creating and editing ODBC data sources

❖ To create an ODBC User Data Source:

1 Select Settings → Control Panel → ODBC or Select Programs → Sybase

→ Adaptive Server IQ → ODBC Administrator

2 In the ODBC Data Source Administrator, click Add on the User DSN tab. 3 Select the Adaptive Server IQ 12 from the list of drivers and click Finish. 4 In the Adaptive Server IQ ODBC Configurat ion box, type the Data Source

Name.

5 Now click the Login tab. Type the User ID and Password for your

database. For example, use “DBA” and “SQL”.

6 Click the Database tab. If the data source is on your local machine, type a

Start line and Database file, including the path.

7 If the data source is on a remote system, click the Network tab. Click the

box for the appropriate protocol and specify the options beside the box. For example, to connect to a server on system PUSHKIN using TCP/IP protocol and port 1870, you would click TCP/IP and type:

host=pushkin:1870

You could also use the host network address. For example:

host=157.133.66.75:1870

8 Click OK when you have finished defining your data source.

The ODBC Data Source Administrator returns you to the User DSN tab.

For details of the ODBC configuration box and its tabs, see “Configuring ODBC data sources” on page 67

Note When specifying network connections, you need a different

systemname:port# combination for each database server. The port number must match the one you started the server wit h.

❖ To test an ODBC Data Source

To test your data source, you must first start the database. 1 Start the database. For example, to start the Sample Database, select Start

→ Programs → Sybase → Adaptive Server IQ 12 → Start ASIQ Demo Database.

2 In the ODBC Data Source Administrator , select your new data source from

the list of User Data Sources.

3 Click Configure. 4 On the ODBC Configuration dialog box, cli ck Test Connectio n.

If you cannot access the Data Source, check that you have filled out the various tabs with correct file and pathnames.

To edit a data source, select one from the list in the ODBC administrator window and click Configure.

If you need to access Windows NT across a network in order to create an ODBC data source, see the Adaptive S erver IQ Instal lation an d Configu ration Guide.

Configuring ODBC data sources

This section describes the meaning of each of the options on the ODBC configuration dialog box, organized by tab.

ODBC tab

Data source name The Data Source Name is used to identify the ODBC

data source. You can use any descriptive name for the data source (spaces are allowed) but it is recommended that you keep the name short, as you may need to enter it in connection strings.

CHAPTER 2 Running Adaptive Server IQ

For more information, see the DataSourceName connection parameter in the Adaptive Server IQ Reference Manual.

Description You can enter an optional longer description of the Data

Source.

Translator Choose Adaptive Server IQ 12.0 Translator if your database

uses an OEM code page. If your database uses an ANSI code page, which is the default, leave this unchecked.

Isolation level The isolation level for an IQ data source is always

effectively 3. However, the default Catalog Store isolation level is 0. For more information, see “Isolation levels” on page 302.

Creating and editing ODBC data sources

Microsoft applications (keys in SQL Statistics) Check this box if you

wish foreign keys to be returned by SQL statistics. The ODBC specifications states that primary and foreign keys should not be returned by SQL statistics, however, some Microsoft applications (such as Visual Basic and Access) assume that primary and foreign keys are returned by SQL statistics.

Delphi applications Check this box to improve performance for Borland

Delphi applications. When this option is checked, one bookmark value is assigned to each row, instead of the two that are otherwise assigned (one for fetching forwards and a different one for fetching backwards).

Delphi cannot handle multiple bookmark va lues for a row. If the option is unchecked, scrollable cursor performance can suffer since scrolling must always take place from the beginning of the cursor to the row requested in order to get the correct bookmark value.

Prevent Driver Not Capable errors The Adaptive Server Anywhere

ODBC driver returns a Driver not capable error code because it does not suppo rt qualifiers. Some ODBC applications do not handle this error properly. Check this box to disable this error code, allowing such applications to work.

Delay AutoCommit until statement close Check this box if you wish the

Adaptive Server Anywhere ODBC driver to delay the commit op eration until a statement has been closed.

Describe cursor behavior S e le ct how oft en you wis h a cursor to be re-

described when a procedure is executed or resumed.

Test Connection Te sts if the information provided will result in a proper

connection. In order for the test to work a user ID and password must have been specified.

Database tab

CHAPTER 2 Running Adaptive Server IQ

Use integrated login Connects using an integrated login. The User ID and

password do not need to be specified. To use this type of login users must have been granted integrated login permission. The database being connected to must also be set up to accept integrated logins. Only users with DBA access can administer integrated login permissions.

For more information, see “Using integrated logins” on page 87.

User ID Provide a place for you to enter the User ID for the connection. Password Provides a place for you to enter the password for the

connection.

Encrypt password Check this box if you wish the password to be stored

in encrypted form in the profile. For more information, on User ID, Password, and Encrypt password, see the

chapter “Connection and Communication Parameters” in the Adaptive Server IQ Reference Manual.

Server name Provides a place for you to enter the name of the IQ server. Start line Enter the server that should be started. Only provide a Start Line

parameter if a database server is being connected to that is not currently running. For example:

C:\Program Files\Sybase\ASIQ12\win32\asiqsrv12.exe

-gm 10 -gp 4096 -c 8M

dbf=path\asiqdemo.db uid=DBA pwd=SQL

Database name

Provides a place for you to enter the name of the Adaptive

Server IQ database that you wish to connect to.

Database file Provides a place for you to enter the full path and name of

the Adaptive Server IQ database file on the server machine. You can also click Browse to locate the file. For example:

C:\Program Files\Sybase\ASIQ12\demo\asiqdemo.db

Automatically shut down database after last disconnect

Selecting this will cause the automatic shutdown of the server after the last user has disconnected.

Creating and editing ODBC data sources

For more information on the parameters in the Database tab, see the EngineName, StartLine, DatabaseName, DatabaseFile, and AutoStop

connection parameters in the chapter “Connection and Communication Parameters” in the Adaptive Server IQ Reference Manual.

Network tab

Select the network protocol and specify any protocol specific options where necessary The TCP/IP, IPX, and NetBIOS check boxes specifies

what protocol or protocols the ODBC DSN will use to access a network database server. In the adjacent boxes, you may enter communication parameters that establish and tune connections from your client application to a database.

For a TCP/IP example, see “To create an ODBC User Data Source:” on page

66. For more information see the CommLinks connection parameter, and Network communications parameters, in the chapter “Connection and Communication Parameters” in the Adaptive Server IQ Reference Manual.

Encrypt all network packets Enables encryption of packets transmitted

from the client machine over the network. By default, network encryption packets is set to OFF.

Advanced tab

Liveness timeout A liveness packet is sent across a client/server to

confirm that a connection is intact. If the client runs for the liveness timeout period without detecting a liveness packet, the communication will be severed. This parameter works only with network server and TCP/ IP or IPX communications protocols. The default is 120 seconds.

Buffer size Set the maximum size of communication packets, in bytes. Buffer space Indicates the amount of space to allocate on startup for

network buffers, in kilobytes. For more information on the Encryption, LivenessTimeout, CommBufferSize

and CommBufferSpace connection parameters, see the Adaptive Server IQ Reference Manual.

Connection name The name of the connection that is being created. Character set The name of the character set. Allow multiple record fetching Enables multiple records to be retrieved

at one time instead of individually. By default, multiple record fetching is allowed.

Display debugging information in a log file The name of the file in

which the debugging information is to be saved.

Additional connection parameters Enter any additional switches here.

Parameters set throughout the remainder of this dialog take precedence over parameters typed here.

Creating a File Data Source

Data sources are stored in the system registry. File data sources are an alternative, which are stored as files. File data sources typically have the extension .dsn. They consist of sections, each section starting with a name enclosed in square brackets. DSN files are very similar in layout to initialization files.

CHAPTER 2 Running Adaptive Server IQ

Creating a file data source from the ODBC Administrator

Creating a file data source using a text editor

Example of a file data source

On Windows NT systems, you can create a file data sou rce using the following procedure.

❖ To create an ODBC file data source:

1 Select Settings→Control Panel, and then click the ODBC icon to start the

ODBC Administr a t or. 2 From the File DSN tab, click Add. 3 Select Adaptive Server IQ 12 from the list of drivers, and click Next. 4 Follow the instructions to create the data source. A file data source is a text file, so it can be edited using any text editor. On

UNIX systems you must use a text editor to create file data sources. One limitation to using a text editor is that you cannot store encrypted passwords in the file.

[Sample File Data Source]

ENG = asiqdemo

DBA = DBA

PWD = SQL

Using ODBC data sources on UNIX

On UNIX operating systems, ODBC data sources are held in a file named .odbc.ini. When creating a .odbc.ini file on any UNIX system, you must use the long form of each identifier, for example:

[My Data Source] EngineName=myserver CommLinks=tcpip UserID=DBA Password=SQL

Network communications parameters are added as part of the CommLinks parameter. For a complete list, see “Connection parameters” on page 73.

References to ODBC functions are resolved at run time. The database server looks for the .odbc.ini file in:

1 The directory specified by the ODBCHOME environment variable 2 The directory specified by the HOME environment variables 3The path The database server ignores the ODBC_HOME, ODBC_INI and ODBCINI

environment variables.

Note On UNIX systems, Adaptive Server IQ installation installs only the

ODBC driver, and not th e driver manager. The name of the driver file includes an operating system-specific extension, for example, so for Solaris systems. For example, on a Sun Solaris system, if you are using an ODBC application that uses libodbc.so (libodbc.so.1) or libodb cinst .so (libodbcinst.so.1), simply create symbolic links for these that point to $SYBASE/asiq12 /lib/dbodbc6. so.1. If you are creating a custom ODBC application, you can link directly to dbodbc6.so.

If Adaptive Server IQ does not detect the presence of an ODBC driver manager, i t wi ll u se ~/.o dbc. ini for data source information. Otherwise, it will query the driver manager for data source information.

Connection parameters

Adaptive Server IQ connection parameters are listed in the following table. For a full description of each of these connection parameters, see Chapter 3,

“Connection and Communication Parameters” in the Adaptive Server IQ Reference Manual.

Parameter

AutoPreCommit AutoPreC

AutoStop Astop Yes/No Prevent a database from

CommAutoStop CAstop Yes/No Unload network

CommBufferSize CBSize Integer Set the maximum size of

CommBufferSpace CBSpace Integer Specify the amount of

CommLinks Links String Speci fy network

ConnectionName * CON String Name a connection to

DatabaseFile DBF String Identify a database file

DatabaseName DBN String Identify a loaded

CHAPTER 2 Running Adaptive Server IQ

Short form Argument Description

Yes/No Force each statement to

ommit

commit before execution

being unloaded as soon as there are no more open connections. (Embedded databa s e s )

communications ports as soon as there are no more open connections from the client machine.

communica tion pack ets, in bytes.

space to allocate on startup for netw ork buffers, in kilobytes.

communications links.

make switching to it easier in multiconnection applications.

to load and connect to (for embedded databases).

database to which a connection needs to be made.

Connection parameters

Short

Parameter

DatabaseSwitches DBS St ri n g Provide database-

form Argument Description

specific switches when starting a database.

DataSourceName ** DSN String Tell the ODBC driver

manager where to look in odbc.ini to find ODBC data source information.

Debug DBG Boolean P rov ide diagnostic

information on communications links on startup.

DisableMultiRowFetch DMRF Boolean Turn off multi-record

fetches across the network.

EngineName ENG String Identify server to

connect to

EncryptedPassword ENP Encrypted

string

Provide a password, and store it in an encrypted fashion in a data source.

Encryption ENC Boolean Encrypt packets

transmitted from the client machine over the network.

EngineName / ServerName ENG String Name of the database

server.

FileDataSourceName FILEDSN String Provide a file data

source name for the connection.

Integrated INT Yes/No Enable integrated

logins. For a client application to use an integrated login, server must be running with

LOGIN_MODE database

option set to Mixed or Integrated.

LivenessTimeout LTO Integer Control the termination

of connections when they are no longer intact.

CHAPTER 2 Running Adaptive Server IQ

Short

Parameter

Logfile LOG String Send client error

Password ** PWD String Provide a password for

ServerName ENG String Specify server to

StartLine Start String Start a database server

Unconditional UNC Yes/No Stop a server even if

Userid ** UID String User ID with which you

form Argument Description

messages and debugging messa ge s to a file.

the connection

connect to

running from an application (for embedded databases).

connections are active

log on to the database

* Not supported in ODBC connections ** Verbose form of keyword not supported in DSN and FI LEDSN connec tion

parameters

Notes

• Boolean (true or false) arguments ar e either YES, ON, 1, or TRUE if true,

or NO, OFF, 0, or FALSE if false.

• Connection parameters and their values are case insensitive.

• The connection parameters used by the interface library can be obtained

from the following places (in order of precedence):

• Connection string

• SQLCONNECT environment variable

• Data sources

• The server name must be composed of characters in the r ange 1 to 127 of

the ASCII character set. There is no such limitation on other parameters.

For more informatio n on the character set iss ues, see “Connection strings

and character sets”.

• The following rules govern the priority of parameters:

Connection parameters

• The entries in a connection string are read left to right. If the same

parameter is specified more than once, the last one in the string

applies.

• If a string contains a DS N or FILEDSN entr y , the profi le is read from

the configuration file, and the entries fro m the file are used if they are

not already set. For example, if a connection string contains a data

source name and sets some of the parameters contained in the data

source explicitly, then in case of conflict the explicit parameters are

used.

Connection parameter priorities

Connection parameters often provide more than one way of accomplishing a given task. This is particularly the case with embedded databases, where a database server is started by the connection string.

For example, if your connection starts a database, you can specify the database name using the DBN connection par ameter or using the DBS parameter.

Here are some recommendations and notes for situations where connection parameters conflict:

• Specify database files using DBF You can specify a database file on

the Start parameter or using the DBF parameter. DBF is recommended.

• Specify database names using DBN You can specify a database

name on the Start parameter, the DBS parameter, or using the DBN parameter. DBN is recommended.

• Use the Start parameter to specify cache size Even though you

use the DBF connection parameter to specify a database file, you may still want to tune the way in which it starts. You can use the Start parameter to do this.

For example, if you are using the Java features of Adaptive Server IQ, you should provide additional cache memory for the Catalog Store on the S tart parameter. The following sample set of embedded database connection parameters describes a connection that may use Java features:

DBF=path\asademo.db

DBN=Sample

ENG=Sample Server

UID=DBA

PWD=SQL

Start=asiqsrv12 -c 8M

CHAPTER 2 Running Adaptive Server IQ

How Adaptive Server IQ makes connections

This section describes how the interface libraries establish connections.

Who needs to read this section?

In many cases, establishing a connection to a database is straightforward using the information presented in the preceding sections of this chapter . However , if you are having problems establishi ng connections to a server , you may need to understand how Adaptive S erver IQ establis hes connections in order to resolv e your problems.

Note If you have no problem establishing connections to your database, you

do not need to read this section.

The software follows exactly the same procedure for each of the following types of client application:

• Any ODBC application using the

SQLDriverConnect function, which is the

common method of connectio n for ODBC applicatio ns. Many applicatio n

development systems, such as Sybase PowerBuilder and Power++, belong

to this class of application.

• Any client applicatio n using Embed ded SQL and us ing the recom mended

function for connecting to a database (

db_string_connect).

The SQL CONNECT statement is available for Embedded SQL

applications and in Interactive SQL. It has two forms:

CONNECT USING... . All the database administration tools, including

utilities and Interactive SQL, use

db_string_connect.

CONNECT AS... and

Steps in establishing a connection

T o establish a connection to Adaptiv e Server IQ, the client ap plication carr ies out the following steps:

1 Locate the interface library. The client application must locate the ODBC

driver or Embedded SQL interface library. 2 Assemble a list of connection parameters. Connection parameters may b e

provided in several places, such as data sources, a connection string

assembled by the application, and an environment variable. The ODBC

driver or Embedded SQL interface library assembles the parameters into a

single list.

How Adaptive Server IQ makes connections

3 Locate a server. Using the connection parameters, the ODBC driver or

Embedded SQL interface library must locate a database server on your machine or over a network.

4 Locate the database. Once it locates the server, the ODBC driver or

Embedded SQL interface library must locate the database you are connecting to.

The following sections describe each of these steps in detail.

Locating the interface library

The client application makes a call to one of the Adaptive Server IQ interface libraries. In general, the location of this DLL or shared library is transparent to the user. Here we describe how the library is located, in case of problems.

ODBC driver location

Embedded SQL interface library location

When the library is located

For ODBC, the interface library is also called an ODBC driver. An ODBC client application calls the ODBC driver manager, and the driver manager locates Adaptive Server IQ’s driver.

The ODBC driver manager looks in the supplied data source in the odbc.ini file or registry to locate the driver . When you create a data source using the ODBC Administrator, Adaptive Server IQ fills in the current location for your ODBC driver.

Embedded SQL applications call the interface library by name. The name of the Adaptive Server IQ Embedded SQL interface library is as follows:

• Windows NT: dblib6t.dll

• UNIX: dblib6 with an operating system-specific extension. The locations that are searched depend on the operating system:

• On Windows NT, the client application looks for files in the current directory, in the system path, and in the Windows and Windows\system directories.

• On UNIX, the client application looks for files in the system path and the user path.

Once it locates the interface library, the client application passes a connection string to it. The interface library uses the connection string to assemble a list of connection parameters, which it uses to establish a connection to a server.

CHAPTER 2 Running Adaptive Server IQ

Assembling a list of connection parameters

The following figure illustrates how the interface libraries assemble the list of connection parameters they will use to establish a connection.

Notes

Key points from the figure are as follows:

• Precedence — Parameters held in more than one place are subject to the

following order of precedence:

Connection string > SQLCONNECT > profile

That is, if a parameter is supplied both in a data source and in a connection string, the connection string value overrides the data source value.

• Failure — Failure at this stage occurs only if you specify in the connection

string or in SQLCONNECT a data source that does not exist in the client connection file.

• Common param eters — Depending on other connections already in use,

some connection parameters may be ignored. These include the following:

•

AutoStop Ignored if the database is already loaded.

•

CommLinks The specificati ons for a netw ork protocol are ignored if

another connection has already set parameters for that protocol.

•

CommBufferSize Ignored if another connection has already set this

parameter.

How Adaptive Server IQ makes connections

• CommBufferSpace Ignored if another connection h as already set this

parameter.

•

Unconditional Ignored if the database is already loaded or if the server is already running.

The interface library uses the completed list of connection parameters to attempt to connect.

Sybase 12.4.2 User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual