Sybase Server 1242 User Manual

Administration and Performance Guide

Adaptive Server® IQ

12.4.2

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 and performance tuning

recommendations for Sybase Adaptive Server IQ, a high-performance

decision support server designed specifically for data warehouses and data

marts.

Audience

This guide is for system and database administrators or for anyone who

needs to set up or manage Adaptive Server IQ or understand performance

issues. Familiarity with relational database systems and introductory user-

level experience with Adaptive Server IQ is assumed.

How to use this book

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

xvii

Related documents

C H A P T E R

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 optimized 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 many

data sources

Adaptive Server IQ can integrate data from diverse sources—not just IQ

databases, but other databases in the Adaptive Server family, as well as non-

Sybase 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 warehousing and

Adaptive Server IQ

Data warehouses are collections of data designed to allow business analysts

to analyze information. They are typically distinct from production databases,

to avoid interrupting daily operations. Data warehouses are often used as data

stores on which to build decision support systems (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 DSS

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 database 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 manage an Adaptive Server 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

Typically, 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...

Install and configure Adaptive Server Adaptive Server IQ Installation and

IQ for your platform Configuration Guide

Start and stop the database server, and Chapter 2, “Running Adaptive Server

set up user connections IQ”

Look in...

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

Objects”

Determine appropriate indexes for

your users' queries

Chapter 4, “Adaptive Server IQ

Indexes”

Load data into your database

Chapter 5, “Moving Data In and Out

of Databases”

Add users and assign them privileges Chapter 10, “Managing User IDs and

Permissions”

Ensure the integrity of data in your

tables

Chapter 7, “Ensuring Data Integrity”

Understand how transactions impact

concurrency

Chapter 8, “Transactions and

Versioning”

Set up your database for the language Chapter 9, “International Languages

you work in

and Character Sets”

Back up and restore databases

Chapter 11, “Backup and Data

Recovery”

Tune Adaptive Server IQ for

maximum performance

Chapter 12, “Managing System

Resources”; see also performance

tuning hints for specific features in all

chapters

Monitor IQ performance

Chapter 13, “Monitoring and Tuning

Performance”

Set up and manage a multiplex

configuration

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

operations on that database. This guide is addressed primarily to the DBA,

who typically carries out most administrative tasks.

•

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

perform any operation on that object.

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

considered a public user for objects owned by other users.

Granting permissions

Except for the DBA, who can perform any task, users must be granted the

authority to perform specific tasks. For example, you need the proper authority

to:

•

Connect to a database.

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

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

To help you manage your database, Adaptive Server IQ provides two primary

tools:

CHAPTER 1 Overview of Adaptive Server IQ System Administration

•

Sybase Central is an application for managing 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 monitoring database performance. 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. DBISQL

has a window-like user interface on all platforms.

The Introduction to Adaptive Server IQ explains how to use Sybase 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 Store is the set of Adaptive Server IQ tables. You can have one or more

permanent IQ Stores, each in a separate database. Each IQ Store includes a set

of tables that organize your data. The table data is stored 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 tables and 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 along with your IQ tables, or you may have a separate Adaptive

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 database, 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 system. Stored

procedures give you information about your database and users, and carry out

various operations 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 execute it.

For example, if you run the stored procedure sp_addlogin in the asiqdemo

database, it adds a user to 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 that 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_iqestjoin, sp_iqestdbspaces,

sp_iqestspace.

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

sp_iqindex

Estimates the amount of space needed

to create a database, based on the

number of rows in the underlying

database tables.

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

sp_iqtable

Displays miscellaneous status

information about the database

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 Command→Options and entering a new Limit Display Columns

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

sp_addgroup

Description

Adds a group to a database

Adds a new user account to a database

sp_addlogin

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

Adds a new user to a database

sp_adduser

sp_changegroup

Changes a user’s group or adds a user to a

group

sp_dboption

Displays or changes database options

Drops a group from a database

Drops a user from a database

Drops user-defined messages

Drops a user-defined data type

Drops a user from a database

sp_dropgroup

sp_droplogin

sp_dropmessage

sp_droptype

sp_dropuser

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 sp_column_privileges procedure. The

implemented catalog procedures are described in the following table.

The following list describes the supported Adaptive Server Enterprise catalog

procedures.

Catalog procedure

sp_column_privileges

sp_columns

Description

Unsupported

Returns the data types of the specified column

sp_fkeys

Returns foreign key information about the

specified table

sp_pkeys

Returns primary key information for a single

table

sp_special_columns

sp_sproc_columns

sp_stored_procedures

sp_tables

Returns the optimal set of columns that

uniquely identify a row in a table

Returns information about a stored procedure’s

input and return parameters

Returns information about one or more stored

procedures

Returns a list of objects that can appear in a

FROM clause

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

data types

sp_remote_tables

List tables on a remote server

System tables and views

Procedure name

Purpose

sp_servercaps

Display information about a remote

server’s capabilities

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 tables is:

•

Database characteristics

Table characteristics, including table definitions and information about the

size and location of each table

•

Information about indexes

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

SYSCOLLATION

Describes columns in each article in a

SQL Remote publication

Contains the complete collation

sequences available to Adaptive

Server IQ

SYSCOLLATIONMAPPINGS

SYSCOLUMN

Lists the collation sequences available

in Adaptive Server IQ and their GPG

and JDK mappings

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 database

SYSFKCOL

Associates each foreign key column

with a primary key column

SYSFOREIGNKEY

SYSGROUP

Contains general information about

each foreign key

Describes a many-to-many

relationship between groups and

members

SYSINDEX

Describes indexes in the database

Describes database characteristics

Lists backups and restores

SYSINFO

SYSIQBACKUP

SYSIQCOLUMN

Lists information on columns in every

table or view in the IQ Store

SYSIQFILE

SYSIQINDEX

SYSIQINFO

Lists information on operating system

files for the database

Lists internal information on indexes

in the database

Lists additional database

characteristics

SYSIQJINDEX

Describes join indexes in the database

SYSIQJOINIXCOLUMN

Describes columns that participate in

join indexes

SYSIQJOINIXTABLE

SYSIQTABLE

Lists the tables that participate in each

join index in the database

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

SYSJAVACLASS

Describes each jar component

associated with the database

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

SYSPROCPARM

Describes each procedure in the

database

Describes each parameter to every

procedure in the database

SYSPROCPERM

Lists each user granted permission to

call each procedure in the database

SYSPUBLICATION

SYSREMOTETYPE

SYSREMOTEUSER

Describes each SQL Remote

publication

Contains information about SQL

Remote

Describes user IDs with REMOTE

permissions and the status of their

SQL Remote messages

SYSSQLSERVERTYPE

Contains information relating to

compatibility with Adaptive Server

Enterprise

SYSSUBSCRIPTION

SYSTABLE

Relates each user ID with REMOTE

permissions to a publication

Describes one table or view in the

database

SYSTABLEPERM

SYSSQLSERVERTYPE

SYSUSERMESSAGES

SYSUSERPERM

Describes permissions granted on

each table in the database

Contains information on compatibility

with Adaptive Server Enterprise

Lists user-defined error messages and

their creators

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

SYSCOLUMNS

SYSFOREIGNKEYS

SYSGROUPS

Presents column update permission

information from SYSCOLPERM

Presents a readable version of the table

SYSCOLUMN

Presents foreign key information from

SYSFOREIGNKEY and SYSFKCOL

Presents group information from

SYSGROUP

SYSINDEXES

Presents index information from

SYSINDEX and SYSIXCOL

SYSOPTIONS

Displays option settings contained in

the table SYSOPTION

SYSPROCPARMS

SYSREMOTEUSERS

SYSTABAUTH

SYSUSERAUTH

Lists all the procedure parameters

from SYSPROCPARM

Lists the information in

SYSREMOTEUSER

Presents table permission information

in SYSTABLEPERM

Displays all the information in the

table SYSUSERPERM except foruser

numbers. Because it contains

passwords, this system view does not

have PUBLIC select permission

SYSUSERLIST

Presents all information in

SYSUSERAUTH except for

passwords

SYSUSEROPTIONS

SYSUSERPERMS

Display effective permanent option

settings for each user

Contains exactly the same information

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 administrative tasks. For complete details of supported 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 definitions. Examples of DDL

statements include CREATE TABLE, CREATE INDEX, ALTER TABLE, and

DROP.

•

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 IF, CALL, and

ROLLBACK.

Functions

Functions return information from the database. They are allowed anywhere an

expression is allowed. Adaptive Server IQ provides functions that:

•

Aggregate data (for example, AVG, COUNT, MAX, MIN, SUM)

Manipulate numeric data (for example, ABS, CEILING, SQRT,

TRUNCATE)

•

Manipulate string data (for example, LENGTH, SOUNDEX, UCASE)

CHAPTER 1 Overview of Adaptive Server IQ System Administration

•

Manipulate date and time data (for example, TODAY, DATEDIFF,

DATEPART, MINUTES)

Convert retrieved data from one format to another (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-MM-DD hh:mm:sswhere

YYYY

4-digit year

2-digit month number (0-12)

2-digit day of month number (0-31)

2-digit numbmer of complete hours that have passed since

midnight (00-23)

2-digit number of complete minutes that have passed since

the start of the hour (00-59)

2-digit number of complete seconds that have passed since

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.iqmsg file before 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 utility database automatically.

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

•

Start the database server using the START ENGINE command with no

database specified

•

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

Start the database server or connect to a database when any other databases

you have are either corrupt or unavailable due to media failure

•

Restore a database

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

can change these to other values during installation, or later by editing the

connection parameters in the util_db.ini file in your executable directory.

CHAPTER 1 Overview of Adaptive Server IQ System Administration

For more information on the utility database, see Chapter 3, “Configuring

Client/Server Communications” in your Adaptive Server IQ Installation and

Configuration Guide.

Compatibility with earlier versions

Version 12 of Adaptive Server IQ differs markedly from earlier versions 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 knowledge 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 pre-

version 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

C H A P T E R

About this chapter

Running Adaptive Server IQ

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 performing these three

steps. This chapter explains various options for accomplishing each of

these steps, and gives suggestions for which to 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 database 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, start_asiq, that runs

the server as a UNIX background process. See “Starting the server on

UNIX” on page 23.

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 additional 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 [ server-switches ] [ database-file

[ database-switches ], ...]

The elements of this command line are as follows:

•

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).

CHAPTER 2 Running Adaptive Server IQ

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 one 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:

•

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 scripts should change directory 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:

Change to a writable directory.

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

command is:

start_asiq servername [ database ]

You can also include server switches or database switches, as discussed in the

next section.

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 displays the 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 your Adaptive Server IQ Installation and Configuration

Guide for a list of parameter settings for your platform.

CHAPTER 2 Running Adaptive Server IQ

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:

asiqsrv12 [ server-switches ] [ database-file [

database-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.

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 specifying

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, it does not run in the

background, and messages do not automatically go to the server log. However,

if your include the -o filename server switch, messages are sent to the named

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 scripts should change 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 Task bar, and select Programs → Sybase → Adaptive Server

IQ 12.

From here, you can start the sample database, Sybase Central, 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 [ server-switches ] [ path\database-file [

database-switches ]]

You must either enter the database-file or include the servername as one of the

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

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 locate your database file.

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

start asiqsrv12 [ server-switches ] database [

database-switches ]

Running the server outside the current session

When you log on to a computer using a user ID and a password, you establish

a session. When you start a database server, or any other application, it runs

within that session. When you log off the computer, all applications 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 running. 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 -udcommand-line option, enabling the database server to run in

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

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 -udcommand-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 NT-

specific 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

-gd

-gk

-gm

-gn

“Checkpoint interval”

“Controlling permissions from the command line”

“Concurrent users”

“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”

“Recovery time”

-gr

-gu

“Controlling permissions from the command line”

“Concurrent queries”

-iqgovern

-iqmc

-iqtc

“Buffer caches and physical memory”

-iqsmem

“Unwired memory” (AIX, Compaq Tru64 UNIX, and

HP UNIX only)

-iqwmem

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

and Sun UNIX only)

-n

-p

-ti

-tl

-x

-z

“Naming the server and databases”

“Other performance-related switches”

“Setting the default client timeout”

“Setting the default network timeout”

“Selecting communications protocols”

“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, and for a complete list of command-line switches and full reference

information on them, see the Adaptive Server IQ Reference Manual.

Displaying command-

line options

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 /?

Case sensitivity

Command-line parameters are case sensitive.

Using configuration

files

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.

You 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.

-n Elora

-c 16M

-x tcpip(port=2367

-gm 10

-gp 4096

path\mydb.db

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

follows:

asiqsrv12 @mydb.cfg

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.

Required command-

line 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 servername -c 16M -gc 6000 -gd all -gm 10

-gp 4096 -gr 6000 -ti 4400 -tl 300 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 -x

’tcpip(port=nnnn)’ set, then the server uses the default TCP/IP port number 2638.

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

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

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 Adaptive Server IQ gives an example

of the sample database configuration file. This file is found in

demo/asiqdemo.cfg in your installation directory.

A note about defaults

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.

Naming restrictions

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 bar 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 mydb, the second database

is sample, and the server is named mydb.

Using command-line switches

asiqsrv12 mydb.db sample.db

Naming databases

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 server named Cambridge_sample

and the sample database on that server:

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

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:

asiqsrv12 -n Galt -gm 10 -gp 4096

Note Although you can start a server by relying on the default server name, it

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 running server, see “Starting and

stopping databases”.

Case sensitivity and

naming conventions

Server names and database names are case insensitive on Windows NT, and

case sensitive on UNIX.

CHAPTER 2 Running Adaptive Server IQ

You should adopt a set of naming conventions for your servers and databases,

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 Store only, see the Adaptive Server IQ Reference Manual for

more information.

Performance tuning suggestions are given throughout this guide. See Chapter

12, “Managing System Resources” for a full discussion of how Adaptive

Server IQ uses memory, disk, and processors, the effect of user connections on

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

objects

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 SET OPTION command to set the Main_Cache_Memory_MB and

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.

Using command-line switches

To override these settings for the current server session, specify the server

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

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.

Concurrent users

Your license sets the absolute number of concurrent users. However, 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 limiting the number of user connections to fewer than your license

allows. Alternatively, you may want to use the -iqgovern switch to control

query use; see “Concurrent queries.”

Concurrent queries

Wired memory

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 license. By specifying the -iqgovern switch, you can help IQ

optimize paging of buffer data out to disk, and avoid overcommitting memory.

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

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

The -iqsmem switch creates a memory pool to increase total available memory.

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 than 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

Number of processing

threads

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.

Catalog Store cache

size

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 applications, 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 asnK 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 the -gc switch to set the

maximum desired length of time (in minutes) that the database server will run

without doing a checkpoint. The default value is 60 minutes.

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 recovery 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 option 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.

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 cannot connect

to databases that are not already started, either at the same time as the server,

or since then by the DBA.

Creating and deleting

databases

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 -gp option to set the Catalog page size explicitly. By setting -gp to

the maximum size, 4096, you maximize the number of columns per table 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 -gp option overrides this default. Once the

server starts, you cannot load a database with a larger Catalog page size than

the server. Unless you specify -gp , an attempt to load a database file with a

Catalog page size larger than the databases started on the command line will

fail.

If you use larger page sizes, remember to increase your 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 option and the page sizes listed here apply to the Catalog Store

only. You set the page size for the IQ Store in the IQ PAGE SIZE parameter of

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

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 protocols for communications across networks and for same-

machine communications.

The database server supports the following protocols:

•

Shared memory is used for same-machine communications, and is loaded

by default (unless the -hs parameter is specified on startup).

•

TCP/IP is supported on all platforms.

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 on Windows NT only. Named Pipes is provided

for same machine communications to and from Windows 3.x client

applications using ODBC or Embedded SQL.

Specifying protocols

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 using 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.

CHAPTER 2 Running Adaptive Server IQ

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 lines 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 –x switch, see “Network communications parameters” in

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.

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 section. 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 -ti switch. By

disconnecting inactive connections, 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. To 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 START ENGINE command to start a named

server from DBISQL.

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.

Example

The following DBISQL command, entered on one 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 environment, it would be unusual to have more than one server

running on the same system. In a development environment, 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 dedicated to each server. Make sure that each

client uses the same temporary directory as its server by setting the

ASTMP environment variable explicitly on both 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

troubleshooting connection 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

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

- 17:36

janed 28932 38122 0 11:39:24

asiqsrv12 -c 16m -gc 6000 -gr 6000

-gm 10 -gp 4096 -ti 4400

- 2:10

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

-iqmt 256 -x tcpip(port=1872)

•

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 more Adaptive Server

IQ icons. Place the cursor over each icon and read the server name.

Windows server log

file

On Windows systems, use the -o parameter 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

CHAPTER 2 Running Adaptive Server IQ

Stopping the database server

The preferred ways to stop the database server are:

•

In UNIX, use the stop_asiq utility. For details, see “Example — Stop a

server with stop_asiq”.

Note that when stop_asiq is used, the following message appears:

"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."

•

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 uncommitted

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’

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

1: janed 2838 15:11:37 0:07

Owner

PID Started CPU_Time

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.

To leave the server running, type N (no). You return to the system prompt and

IQ does not shut down the server.

CHAPTER 2 Running Adaptive Server IQ

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’

Example —Stop a

server from DBISQL

The following example stops a server from DBISQL:

STOP ENGINE Ottawa UNCONDITIONALLY

The optional keyword UNCONDITIONALLY specifies that the database server

will be stopped even if there are connections to it.

Note You can stop a server from DBISQL if you are connected as DBA to one

of the databases running on that server (including the utility_db database), or if

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 required is DBA, but you can also set the value to ALL or NONE. If

you set it to NONE, even the DBA cannot execute STOP ENGINE. In a

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 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 database server (other than

the UNIX command shown in the previous section), the server shuts down, but

not cleanly. Next time the database is loaded, recovery happens 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

You can start databases when you start the server, or after the server is running.

To 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 START

DATABASE statement. For a description, see the chapter “SQL

Statements” in the Adaptive Server IQ Reference Manual.

To start and connect to a database from DBISQL or Sybase Central, use a

data source that specifies the database file. See “Working with ODBC data

sources”.

To start and connect to a database when you start DBISQL from a system

command prompt, include the parameter “DBF=db-file” in the connection

parameters. See “Connecting to a database from DBISQL”.

•

To start a database from Sybase Central, see Chapter 4, “Managing

Databases with Sybase Central” in Introduction to Adaptive 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.

Page size limitations

The server holds database information in memory using pages of a fixed size.

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 bytes, with the

-gp switch.

CHAPTER 2 Running Adaptive Server IQ

Permission limitations

Stopping a database

The -gd server command-line option determines the permission level required

to start databases. By default, this option is set to DBA, so that only users with

database administrator privileges can start IQ databases. However, you can

also set this option to ALL or NONE. ALL means that all users can start a

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

You can stop a database in the following ways:

•

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 STOP DATABASE statement.

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 configuration 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:

%cd $ASDIR/demo

%start_asiq @asiqdemo.cfg asiqdemo

These commands use the configuration file asiqdemo.cfg that is created

automatically at installation. You 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 janed_server.

❖

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

•

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

12.0→ Sample Database.

Behind the scenes

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 summary 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

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.

Starting Sybase

Central on Windows

NT Systems

To start Sybase Central, select Start→Programs→Adaptive Server IQ

12→Sybase Central 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

Select Tools → Adaptive Server IQ 12.

If you do not see Adaptive Server IQ on the Connect Menu, select Tools

→ Plug-ins.

Introduction to connections

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 information about connecting to databases from ODBC

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 determines permissions to carry out 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, but some client tools may not clearly indicate connection

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([ database_id ])

to display any database you specify.

CHAPTER 2 Running Adaptive Server IQ

How connections are established

To establish a connection, the client application calls functions 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 operations. For a description of Java support in Adaptive Server IQ,

see “Enabling Java in the database” on page 113.

The interface uses connection information included in the call from the client

application, perhaps together with information 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.

Learning about

connections

If you want ...

Consider reading ...

Some examples to get started quickly

“Simple connection examples”

Introduction to connections

If you want ...

Consider reading ...

A conceptual overview

“Connection parameters specify

connections”

To create data sources

“Working with ODBC data sources”

To see an in-depth description of how

connections are established

To add users and grant them permissions “How Adaptive Server IQ makes

connections”

Todiagnosenetwork-specific connection “Troubleshooting network

issues

communications” in the Adaptive Server

IQ Troubleshooting and Error Messages

Guide

To learn about character set issues

affecting connections

“Connection strings and character sets”

on page 338

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.

A keyword-value pair, of the form parameter=value, specifies each connection

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 connection string:

parameter1=value1;parameter2=value2

CHAPTER 2 Running Adaptive Server IQ

You 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 fill 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 -c

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 asiqdemo sample database on

a local server and are not connected to a network. However, in most IQ

environments, in order to ensure that you can connect and disconnect properly,

a very complete set of connection parameters is essential.

Simple connection examples

For steps in connecting to a database using Sybase Central, see the Introduction

to Adaptive Server IQ. For more detailed information on available connection

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 To 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 CommLinks

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.

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:

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.

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

If you have not already done so, change to your 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 improves the command

window display. For more information, see Chapter 6, “Getting Started

with DBISQL” in Introduction to Adaptive Server IQ.

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 platforms—to include 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 number (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 same

result:

links=tcpip(host=SERV2:1234)"

❖

To connect from a Windows NT system

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

Interactive SQL, or at the NT command prompt enter

dbisql

Simple connection examples

You can include the -cparameter 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 logon window appears.

Enter the user ID

DBA

and the password

SQL

This is the default user ID and password for Adaptive Server IQ databases

when they are created.

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

“hostname_asiqdemo” for the asiqdemo database).

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

TCPIP. (If you use the shared memory port for connecting, skip this step.)

If your database is on a remote machine, you must add host information in

the space beside TCPIP by typing “host=servername:nnnn” where

servername is the name of your system and nnnn is your port number. (The

default port number is 2638, but if the host was started with a different

number, use that instead.)

Click OK to connect to the database.

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:

Start the server and the database by typing at a system command prompt:

start_asiq dbname

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=dbname;

links=tcpip(host=hostname"

If the host was started with a non-default port number (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 connection string, and by specifying the database file 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 none 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:

dbf=path\asiqdemo.db

uid=dba

pwd=sql

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

Using the Start

parameter

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 Java in an embedded database, you should use the start line to

provide more than the default cache size. For development purposes, a cache

size of 8 MB is sufficient.

Example: connecting

from DBSQL

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

❖

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

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

following ways:

•

From the Windows NT Start menu, choose Sybase→Adaptive Server

Anywhere →Interactive SQL.

•

Type dbisqlat a system command prompt.

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

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

command. The connection dialog appears.

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

source.

Enter DBAas the user ID and SQLas the password. Then click the Database

tab. Enter the full path of the sample database in the Database File field.

For example, if your installation directory is c:\sybase\asiq12 you should

enter the following:

c:\sybase\asiq12\asiqdemo.db

CHAPTER 2 Running Adaptive Server IQ

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 connection parameters in a data source. Data sources 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:

Start DBISQL with no databases running.

•

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.

Enter DBA as the user ID and SQL as the password.

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.

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

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

To 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

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.

Specifying the

protocol

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 default 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

pwd=password

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

you may connect to the wrong server.

Default database

If more than one server is running, you need to specify which one you wish to

connect to. If only one database has been started on that server, you do not 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:

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

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.

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.

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 database systems.

Although data sources are especially designed for Windows and Windows 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 source.

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 can

use data sources

Embedded SQL applications such as Interactive SQL and the other Adaptive

Server IQ database administration utilities can also 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=my data source

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

parameter:

FileDSN=mysource.dsn

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 information in them. You can use both DSNs and FileDSNs on any

platform.

Where DSNs and

FileDSNs are stored

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

be distributed

File data sources can easily be distributed to end users, so that connection

information does not have to be reconstructed on each machine. 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 should not put a password in a DSN, unless you encrypt it.

If you want to store your password in your data source, use a File DSN.

How you create DSNs

and FILEDSNs

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 Administrator.

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 password.

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. You probably 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.

To 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:

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

→ Adaptive Server IQ → ODBC Administrator

In the ODBC Data Source Administrator, click Add on the User DSN tab.

Select the Adaptive Server IQ 12 from the list of drivers and click Finish.

In the Adaptive Server IQ ODBC Configuration box, type the Data Source

Name.

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

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

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

Start line and Database file, including the path.

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

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 with.

❖

To test an ODBC Data Source

To test your data source, you must first start the database.

Start the database. For example, to start the Sample Database, select Start

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

Database.

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

the list of User Data Sources.

CHAPTER 2 Running Adaptive Server IQ

Click Configure.

On the ODBC Configuration dialog box, click Test Connection.

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 Server IQ Installation and Configuration

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.

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 values 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

ODBC driver returns a

The Adaptive Server Anywhere

Driver not capable

error code because it does not support 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 operation until

a statement has been closed.

Describe cursor behavior

Select how often you wish a cursor to be re-

described when a procedure is executed or resumed.

Test Connection

Tests 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.

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.

Provides a place for you to enter the password for the

Password

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.

Database tab

Server name

Start line

Provides a place for you to enter the name of the IQ server.

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.

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.

Indicates the amount of space to allocate on startup for

Buffer space

network buffers, in kilobytes.

For more information on the Encryption, LivenessTimeout, CommBufferSize

and CommBufferSpace connection parameters, see the Adaptive Server IQ

Reference Manual.

Advanced tab

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.

CHAPTER 2 Running Adaptive Server IQ

Display debugging information in a log file

which the debugging information is to be saved.

The name of the file in

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.

Creating a file data

source from the

ODBC Administrator

On Windows NT systems, you can create a file data source using the following

procedure.

❖

To create an ODBC file data source:

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

ODBC Administrator.

From the File DSN tab, click Add.

Select Adaptive Server IQ 12 from the list of drivers, and click Next.

Follow the instructions to create the data source.

Creating a file data

source using a text

editor

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.

Example of a file data

source

[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:

The directory specified by the ODBCHOME environment variable

The directory specified by the HOME environment variables

The 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 the 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 libodbcinst.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, it will use ~/.odbc.ini for data source information. Otherwise, it will

query the driver manager for data source information.

CHAPTER 2 Running Adaptive Server IQ

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.

Short

Parameter

form

Argument

Description

AutoPreCommit

AutoPreC Yes/No

ommit

Force each statement to

commit before

execution

AutoStop

Astop

Yes/No

Integer

Prevent a database from

being unloaded as soon

as there are no more

open connections.

(Embedded databases)

CommAutoStop

CAstop

CBSize

Unload network

communications ports

as soon as there are no

more open connections

from the client machine.

CommBufferSize

Set the maximum size of

communication packets,

in bytes.

CommBufferSpace

CBSpace Integer

Specify the amount of

space to allocate on

startup for network

buffers, in kilobytes.

CommLinks

Links

CON

String

Specify network

communications links.

ConnectionName *

Name a connection to

make switching to it

easier in multi-

connection applications.

DatabaseFile

DBF

DBN

String

Identify a database file

to load and connect to

(for embedded

databases).

DatabaseName

Identify a loaded

database to which a

connection needs to be

made.

Connection parameters

Short

form

Parameter

Argument

Description

DatabaseSwitches

DBS

String

Provide database-

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

Provide diagnostic

information on

communications links

on startup.

DisableMultiRowFetch

DMRF

Boolean

String

Turn off multi-record

fetches across the

network.

EngineName

ENG

ENP

Identify server to

connect to

EncryptedPassword

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

Integrated

FILEDSN String

Provide a file data

source name for the

connection.

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

form

Argument

Description

Logfile

LOG

String

Send client error

messages and

debugging messages to

a file.

Password **

ServerName

StartLine

PWD

ENG

Start

String

Provide a password for

the connection

Specify server to

connect to

Start a database server

running from an

application (for

embedded databases).

Unconditional

Userid **

UNC

UID

Yes/No

String

Stop a server even if

connections are active

User ID with which you

log on to the database

* Not supported in ODBC connections

** Verbose form of keyword not supported in DSN and FILEDSN connection

parameters

Notes

•

Boolean (true or false) arguments are 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 range 1 to 127 of

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

For more information on the character set issues, 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 DSN or FILEDSN entry, the profile is read from

the configuration file, and the entries from 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 parameter 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 Start

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 establishing connections to a server, you may need to

understand how Adaptive Server IQ establishes connections in order to resolve

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 connection for ODBC applications. Many application

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

to this class of application.

•

Any client application using Embedded SQL and using the recommended

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 AS... and

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

utilities and Interactive SQL, use db_string_connect.

Steps in establishing a connection

To establish a connection to Adaptive Server IQ, the client application carries

out the following steps:

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

driver or Embedded SQL interface library.

Assemble a list of connection parameters. Connection parameters may be

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

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.

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

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

interface library

location

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.

When the library is

located

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 parameters — 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 specifications for a network 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 has 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.