Informix

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

ISBN: 9780130809063 | 0130809063
Cover: Paperback
Copyright: 10/1/1998

Rent

(Recommended)

$44.15

Term

Due

Price

Quarter

Aug 13

$44.15

*This item is part of an exclusive publisher rental program and requires an additional convenience fee. This fee will be reflected in the shopping cart.
Buy New

Currently Available, Usually Ships in 24-48 Hours

$49.44

This is the most complete, convenient and up-to-date reference for all Informix database professionals. It presents an overview of the entire Informix database product line, and introduces the overall Informix database architecture, covering shared memory, internal multi-threading, scalability and other common elements, as well as architectural elements unique to Online Dynamic Server, Extended Parallel Server, and Informix Universal Server. The book walks through the installation of each Informix database, and includes detailed, practical coverage of Informix database administration, monitoring and tuning -- using both GUI-based and command-line based tools. Three convenient reference sections present Informix-SQL syntax, character-based and GUI tools commands.

Preface

xxiii

Chapter 1 Informix Product Overview

(16)

History of Informix Products

(10)

C-ISAM

(1)

The Relational Database and Informix-RDS

(1)

Informix-SQL Engine (SE)

(2)

Informix-Turbo

(2)

Informix-Online Dynamic Server

(1)

Informix IDS/UD

(1)

Data Type Extensibility

(1)

Inheritance

(1)

User-Defined Functions

(1)

Informix IDS/XP

(1)

Informix Tools

(1)

Informix Monitoring Tools

(1)

Informix Language Tools

(1)

Informix Internet Development Tools

(1)

Informix Product Architecture

(2)

Disk I/O

(1)

Memory (Buffer)

(1)

Thread (Process)

(1)

Communications

(1)

Parallel Database Operations

(1)

Chapter Summary

(1)

Next Chapter

(2)

Chapter 2 Informix Basic Concepts and Server Architecture

(50)

IDS Basic Concepts

(9)

Shared Memory Buffer

(1)

Multithreading and Virtual Processors

(2)

Thread Operation

(2)

Informix Database Logging

(1)

Physical Log

(2)

Logical Logs

(1)

Checkpoints

(1)

Chunks and dbspace

(1)

Structure of Data Storage

(1)

Informix Data Types

(9)

Smallint

(1)

Integer

(1)

Smallfloat

(1)

Decimal

(2)

Money

(1)

Float

(1)

Date

(1)

Datetime

(1)

Interval

(2)

Character

(1)

Varchar

(1)

Special Data Types

(1)

Mirroring

(1)

Data Replication

(1)

System Reserved Pages

(3)

PAGE_PZERO

(1)

PAGE_CONFIG

(1)

PAGE_CKPT

(1)

PAGE_DBSP

(1)

PAGE_PCHUNK

(1)

PAGE_MCHUNK

(1)

PAGE_ARCH

(2)

Root dbspace

(1)

System Catalog

(1)

The sysmaster Database

(2)

Informix Server Architecture

(15)

The IDS Architecture: Disk I/O

(1)

The IDS Architecture: Database Memory

(1)

Resident Portion of Shared Memory

(2)

Shared Memory Buffer Pool

(1)

Logical Log Buffers

(1)

Physical Log Buffers

(1)

Data Replication Buffer

(1)

LRU management

(2)

Virtual Portion of Shared Memory

(2)

IDS Architecture: Thread (Process)

(1)

IDS Architecture: Communication

(1)

Tracing a Query Through the Engine

(3)

Processing the Query Results

(1)

Parallel Data Query and PDQ Priority

(1)

IDS Options and Extensions

(5)

Architecture of IDS/XP

(1)

Query Processing in IDS/XP

(1)

Architecture of IDS/UD

(1)

Inheritance in IDS/UD

(1)

Chapter Summary

(1)

Next Chapter

(2)

Chapter 3 Installation and Configuration of the Informix Dynamic Server

(44)

IDS Configuration

(3)

Installation Preparation

(1)

IDS Installation

(2)

Running the Installation Program

(1)

IDS Environment Variables

(10)

ONCONFIG

(2)

INFORMIXSERVER

(1)

TERMINFO

(1)

PATH

(1)

DBANSIWARN

(1)

DBCENTURY

(1)

DBDELIMITER

(1)

DBEDIT

(1)

DBLANG

(1)

DBFLTMASK

(1)

DBMONEY

(1)

DBPATH

(1)

DBPRINT

(1)

DBSPACETEMP

(2)

DBTEMP

(1)

DBTIME

(1)

FET_BUF_SIZE

(1)

INFORMIXSHMBASE

(1)

INFORMIXSQLHOSTS

(1)

INFORMIXSTACKSIZE

(1)

INFORMIXTERM

(1)

PDQPRIORITY

(1)

PSORT_DBTEMP

(1)

PSORT_NPROCS

(1)

TERM

(1)

TERMCAP

(1)

The onconfig File

(27)

ROOTNAME

(3)

ROOTPATH

(1)

ROOTOFFSET

(1)

ROOTSIZE

(1)

MIRROR

(1)

MIRRORPATH

(1)

MIRROROFFSET

(1)

PHYSDBS

(1)

PHYSFILE

(1)

LOGFILES

(1)

LOGSIZE

(1)

MSGPATH

(1)

CONSOLE

(1)

ALARMPROGRAM

(1)

TAPEDEV

(1)

TAPEBLK

(1)

TAPESIZE

(1)

LTAPEDEV

(1)

LTAPEBLK

(1)

LTAPESIZE

(1)

STAGEBLOB

(1)

SERVERNUM

(1)

DBSERVERNAME

(1)

DBSERVERALIASES

(1)

NETTYPE

(1)

DEADLOCK_TIMEOUT

(1)

RESIDENT

(1)

MULTIPROCESSOR

(1)

NUMCPUVPS

(1)

SINGLE_CPU_VP

(1)

NOAGE

(1)

AFF_SPROC

(1)

AFF_NPROC

(1)

LOCKS

(1)

BUFFERS

(1)

NUMAIOVPS

(1)

PHYSBUFF

(1)

LOGBUFF

(1)

LOGSMAX

(1)

CLEANERS

(1)

SHMBASE

(1)

SHMVIRTSIZE

(1)

SHMADD

(1)

SHMTOTAL

(1)

CKPTINTVL

(1)

LRUS

(1)

LRU_MAX_DIRTY

(1)

LRU_MIN_DIRTY

(1)

LTXHWM

(1)

LTXEHWM

(1)

TXTIMEOUT

(1)

STACKSIZE

(1)

OFF_RECVRY_THREADS

(1)

ON_RECVRY_THREADS

100

(1)

DRAUTO

100

(1)

DRINTERVAL

101

(1)

DRTIMEOUT

101

(1)

DRLOSTANDFOUND

101

(1)

CDR_LOGBUFFERS

101

(1)

CDR_EVALTHREADS

101

(1)

CDR_DSLOCKWAIT

102

(1)

CDR_QUEMEM

102

(1)

BAR_ACT_LOG

102

(1)

BAR_MAX_BACKUP

102

(1)

BAR_RETRY

102

(1)

BAR_NB_XPORT_COUNT

103

(1)

BAR_XFER_BUF_SIZE

103

(1)

BAR_BSALIB_PATH

103

(1)

RA_PAGES

103

(1)

RA_THRESHOLD

103

(1)

DBSPACETEMP

104

(1)

DUMPDIR

104

(1)

DUMPSHMEM

104

(1)

DUMPGCORE

104

(1)

DUMPCORE

104

(1)

DUMPCNT

105

(1)

FILLFACTOR

105

(1)

USEOSTIME

105

(1)

MAX_PDQPRIORITY

105

(1)

DS_MAX_QUERIES

106

(1)

DS_TOTAL_MEMORY

106

(1)

DS_MAX_SCANS

106

(1)

DATASKIP

107

(1)

OPTCOMPIND

107

(1)

ONDBSPACEDOWN

107

(1)

LBU_PRESERVE

108

(1)

OPCACHEMAX

108

(1)

Summary

108

(1)

Next Chapter

109

(2)

Chapter 4 Managing the IDS Disk

111

(38)

The IDS View of Disk

111

(8)

Defining Physical Disk Layout

112

(1)

Logical Disk Layout

113

(1)

Maintaining dbspaces

114

(1)

Creating and Dropping a dbspace

114

(2)

Adding Chunks to a dbspace

116

(1)

Using dbspace Mirroring

117

(1)

Dropping a Chunk

118

(1)

Using Soft Links to Assign Chunks

119

(1)

Creating Database Tables

119

(14)

Locking Granularity for a Table

124

(1)

Fragmentation

125

(2)

Developing a Fragmentation Strategy

127

(1)

Fragmentation for Archiving

128

(1)

Fragmentation for Performance

128

(4)

Fragmentation Strategies for Archive/Recovery

132

(1)

Other Fragmentation Considerations

133

(1)

Using Data Distributions

133

(8)

Using Distribution Information to Determine Fragmentation Strategies

138

(2)

Data Distributions that Vary with Time

140

(1)

Monitoring Disk Usage

141

(5)

Summary

146

(1)

Next Chapter

147

(2)

Chapter 5 Monitoring and Tuning the IDS Engine

149

(64)

Tuning Strategies for IDS

149

(2)

Monitoring IDS Using the onstat Utility

151

(4)

Using the onstat Utility to Identify Resource Bottlenecks

155

(4)

Monitoring CPU Usage with onstat -g glo

155

(3)

Interpreting onstat VP Output

158

(1)

Using Operating System Resource Monitors

159

(1)

Addressing CPU Resource Problems

160

(1)

Monitoring IDS Memory Usage

161

(3)

Repeating onstat Options to Monitor IDS

163

(1)

Addressing Memory Problems

163

(1)

Monitoring Disk Activity

164

(3)

Addressing Disk Problems

166

(1)

Log Activity in the IDS Engine

167

(2)

Addressing Log Performance Problems

168

(1)

Evaluating and Tuning Physical Log Activity

169

(2)

The Physical Log Location

169

(1)

The Physical Log Size

170

(1)

Evaluating and the LRU Queue

171

(8)

Evaluating LRU Performance

171

(3)

Tuning LRU Management

174

(3)

LRU Parameter Settings for a Heavy Update Load

177

(1)

LRU Parameter Settings for a Light Update Load

177

(1)

LRU Settings for a Decision Support System

178

(1)

Iterative Benchmarking for LRU Tuning

178

(1)

Evaluating and Tuning Checkpoints

179

(1)

Tuning Queries and Applications

180

(7)

Tracking the Query Session with `onstat -g ses'

181

(1)

Viewing Session Activity with the `onstat -g ses' Command

182

(2)

The `onstat -g sql' Command

184

(1)

Monitoring Active Threads with `onstat -g act'

185

(1)

Monitoring Asynchronous I/O with `onstat -g iof'

186

(1)

Addressing System Resource Problems

187

(2)

Adding CPU VPs

188

(1)

Tuning a Query

189

(11)

Fragmenting to the Same Disk

191

(1)

Filters and Fragment Elimination

192

(1)

The PDQPRIORITY Setting

193

(3)

Setting and Controlling PDQPRIORITY

196

(2)

Impacting Optimization with the OPTCOMPIND Parameter

198

(1)

Index Read Versus Sequential Read

198

(1)

The Read-Ahead Option

198

(2)

The Query and the Query Plan

200

(3)

The Access Plan

200

(1)

The Join Plan

201

(1)

Hash Join

201

(1)

Evaluation Order

202

(1)

The Cost-Based Optimizer and the `set optimization' Command

202

(1)

Tuning the Query

203

(3)

Identifying and Addressing Full Table Scans

203

(1)

When a Full Table Scan is Acceptable

204

(2)

Using Table Fragmentation for Query Tuning

206

(5)

Fragmentation Schemes

207

(1)

Fragment by inv_date

208

(1)

Fragment by inv_flag

209

(1)

Fragment by ship_to_code

210

(1)

Fragmentation and RAID

210

(1)

Chapter Summary

211

(1)

Next Chapter

212

(1)

Chapter 6 Database Administration in the Informix Dynamic Server

213

(32)

Starting and Stopping the Database

213

(3)

Engine Operating Modes

214

(1)

Startup Problems

215

(1)

Online Engine Modifications

216

(10)

Bringing the Engine to Offline Mode

216

(2)

Bringing the Engine to Single User Mode

218

(1)

Bringing the Engine to Online (Multi-User) Mode

219

(1)

Adding and Removing CPU Resources

220

(1)

DSS Parameter Changes

220

(2)

Using onmode to Alter Shared Memory Parameters

222

(1)

Adding Virtual Shared Memory with the `onmode -a' Command

222

(1)

Setting the Shared Memory Buffer to Resident Status with `onmode -r'

223

(1)

Miscellaneous onmode Commands

224

(1)

The `onmode -l' Command

225

(1)

The `onmode -Z' [transaction id] Command

225

(1)

The `onmode -z' [pid] Command

225

(1)

The 'onmode -R' Command

225

(1)

The `onmode -d' Command

225

(1)

IDS Database Security

226

(5)

Database-Level Permissions

227

(1)

Database connect Privileges

227

(1)

Database resource Privileges

227

(1)

Database dba Privileges

227

(1)

Create and Using User Roles

228

(1)

Table Permissions

229

(1)

The `with grant option' Statement

230

(1)

Removing Permissions with the `revoke' Command

231

(1)

Managing IDS Logs

231

(7)

Monitoring Logical Logs

232

(1)

Backing Up Log Files

233

(1)

Changing the Size and Location of the Physical Log

234

(2)

Changing the onconfig File

236

(1)

Using the onparams Command

236

(1)

Adding Logical Logs

236

(1)

Dropping Logical Logs

237

(1)

Archiving and Recovery

238

(4)

Archive Levels

238

(1)

Performing and Archive

239

(1)

Restoring Data

240

(1)

Cold Restores and Warm Restores

241

(1)

Changing the Database Logging Status

241

(1)

Chapter Summary

242

(1)

Next Chapter

243

(2)

Chapter 7 The Relational Database and Informix-SQL

245

(88)

History of the Relational Database

246

(1)

Relational Database Concepts

246

(2)

Entities, Attributes, and Relationships Normalization

247

(1)

Normalization

248

(1)

The Structured Query Language in IDS

248

(2)

SQL Statement Types

249

(1)

Extended SQL Statements

250

(1)

Creating Data Stroage--The DDL Statements

250

(13)

Create Database

250

(1)

The Create Table Statement

251

(1)

Table Name, Column Names and Data Types

252

(1)

IDS Fragmentation Definition

253

(2)

Creating Database Views

255

(1)

Enforcing Security with Views

256

(1)

Using Keywords in Views

257

(1)

Referencing Complete Queries

258

(1)

Dropping Database Tables

258

(1)

Dropping Database Views

259

(1)

Dropping a Database

259

(1)

The `Alter table' Statement

260

(2)

The rename column Statement

262

(1)

The Rename table Statement

262

(1)

Column Integrity and Referential Constraints

263

(4)

Primary Key/Foreign Key Definition at the Table Level

264

(2)

Creating a Unique Column

266

(1)

Naming a Constraint

266

(1)

Accessing Data--DML Statements

267

(17)

The Select Statement

268

(1)

The Select List

268

(1)

The from Clause

268

(1)

The Where Clause

269

(1)

Assigning Display Labels

270

(1)

Identifying a Sort Order

270

(1)

Specifying a Maximum Number of Rows Returned

271

(1)

Grouping Records

272

(1)

Table Aliasing

273

(1)

Using Subqueris

274

(2)

Recursive Queries

276

(2)

Using Outer Joins

278

(1)

SQL Unions

278

(1)

Segmenting Values with the union Clause

279

(2)

The Insert Statement

281

(1)

The update Statement

282

(2)

The delete Statement

284

(1)

Miscellaneous SQL

284

(4)

SQL Copy Operations

285

(1)

The load and Unload Statements

285

(2)

Using Temporary Tables

287

(1)

Informix Stored Procedure Language

288

(20)

The if Statement

289

(3)

The return Statement and the returning Clause

292

(1)

The foreach Statement

293

(1)

The Call statement

294

(1)

The while Loop

294

(1)

The for Statement

295

(1)

The Begin/end Statement Block

296

(2)

The continue Statement

298

(1)

The Raise Exception Statement

299

(1)

The let Statement

300

(1)

The system Statement

301

(1)

The `on exception' Statement

302

(2)

The define Statement

304

(1)

Using Global Scope Variabless in SPL

305

(1)

The Exit Statement

305

(1)

Producing Debug Output with Trace Statements

306

(1)

Using the dbinfo Function in Procedures

306

(2)

IDS Database Triggers

309

(11)

The Create Trigger Statement

309

(1)

Insert Triggers

310

(1)

Delete Triggers

311

(2)

Update Triggers

313

(1)

The Utility of Triggers

314

(1)

Executing Stored Procedures within Triggers

315

(1)

Using the When Clauses in Triggers

316

(1)

Using Triggers and Stored Procedures for Custom Replication

316

(3)

IDS String Manipulation Functions

319

(4)

The Trim Function

320

(1)

The Substring Function

321

(1)

The Substr Function

321

(1)

The Replace Function

322

(1)

The lpad Function

322

(1)

The rpad Function

322

(1)

The Case Conversion Functions

323

(1)

New Version 7.3 Features

323

(7)

The Case Statement

324

(1)

The Decode Function

325

(1)

The nvl Function

326

(1)

Creating Memory Resident Tables and Indexes

327

(1)

Using Optimizer Directives

328

(2)

Chapter Summary

330

(1)

Next Chapter

330

(3)

Chapter 8 Introduction to INFORMIX-4GL

333

(52)

The INFORMIX-4GL Language

334

(3)

Statements and Operators

334

(1)

Program Blocks

335

(1)

Language Operators

335

(1)

Memory Control

336

(1)

Data Typing

336

(1)

Database Interaction

336

(1)

Parts of a 4GL Application

337

(2)

User Interaction with Informix-4GL

338

(1)

Reporting and Data Filtering

338

(1)

Modular Programming with INFORMIX-4GL

339

(1)

Informix Data Types and Operators

339

(10)

Informix-4GL DataTypes

340

(1)

Smallint

340

(1)

Integer

340

(1)

Smallfloat

341

(1)

Decimal

341

(1)

Money

342

(21)

Float

343

(1)

Date

343

(1)

Datetime

343

(1)

Interval

344

(1)

Character

345

(1)

Complex Data Types

346

(1)

Arrays

346

(1)

Records

346

(1)

Database Binding

347

(1)

Null Data Values

348

(1)

Data Conversion in Assignments

349

(5)

Assignment and Conversion in Expressions

350

(2)

Character Conversions

352

(1)

Arithmetic Operators and Numeric Conversion Rules

352

(1)

Initialization

353

(1)

Language Constants

354

(1)

Flow of Control Statements in INFORMIX-4GL

355

(1)

The for Statement

355

(1)

Conditional Expressions in INFORMIX-4GL

356

(2)

The Concatenation Operator

358

(1)

Using SQL in INFORMIX-4GL Applications

359

(23)

Type Conversion with SQL Statements

360

(1)

Database Binding in INFORMIX-4GL

361

(2)

Schema 1

363

(1)

Schema 2

363

(1)

prepare Statement

363

(1)

Optimization with the prepare Statement

364

(1)

SQL Processing: Front-End vs. Back-End Activity

365

(1)

Optimization with the prepare and declare Statements

366

(3)

Cursors in INFORMIX-4GL

369

(1)

The foreach Statement

369

(1)

Data Variables Used in SQL Statements

370

(1)

Cursors in INFORMIX-4GL

371

(1)

Types of Cursors

372

(1)

Scroll Cursor

372

(2)

Problems with Scroll Cursors

374

(2)

Update Cursors

376

(1)

Insert Cursor

377

(1)

Closing the Cursor

378

(1)

Views

379

(2)

Modularity and SQL

381

(1)

Chapter Summary

382

(1)

Next Chapter

383

(2)

Chapter 9 Screen Forms and Reports

385

(66)

Managing Screen Size

386

(1)

Screen Form Programming

386

(1)

Database Binding in Screen Forms

386

(1)

formonly Screens

387

(1)

Manipulating the Screen Form in INFORMIX-4GL

388

(1)

Screen Form Identifiers

389

(1)

Modularity and Screen Form Interaction

389

(1)

Managing Multiple Windows

390

(5)

The Current Window

390

(5)

display Statement

395

(4)

The display Statement and Screen Form Interaction

397

(2)

input Statement

399

(4)

Moving Past an Input Field

400

(2)

Highlighting the Current Field

402

(1)

Termacp Issues

403

(4)

Redefining Function Keys

403

(1)

Turning Off the Insert Key and Delete Key in input array

404

(1)

Adding Additional Function Keys in the Termcap File

404

(1)

Trapping the Interrupt Key

405

(2)

INFORMIX-4GL Reports

407

(27)

Report Statements

408

(1)

Declaring a Report

409

(1)

Generating a Default Report

410

(1)

The output Section

411

(1)

The order by Section

412

(1)

The format Section

413

(1)

First Page Header

413

(1)

Creating Dynamic Column Positions in Reports

414

(1)

The format Section--before group of/after group of Clauses

415

(2)

Batch Processing in Reports

417

(2)

print Statement

419

(1)

Managing Text Fields in Reports

420

(1)

Page Breaks

421

(2)

Modular Dynamic Reports

423

(2)

Report Issues

425

(3)

Modularity in Reports

428

(2)

Synchronous Reports

430

(1)

Dynamic Reports Driven by the construct Statement

430

(2)

The qry_acct Function

432

(1)

The rpt_drv Function

432

(2)

The acct_totals Report

434

(4)

Table-Driven Dynamic Report

438

(11)

The main Program Block and the rpt_drv Function

440

(2)

The proc_line Function

442

(1)

The sql_rpt Report

443

(3)

The on every row Report Block

446

(3)

Chapter Summary

449

(1)

Next Chapter

449

(2)

Chapter 10 Functions and Error Handling in INFORMIX-4GL

451

(32)

Function Parameters

452

(1)

Implementation

452

(2)

Functions as Expressions

454

(1)

Use of Variables Within a Function

455

(1)

Passing a Variable Number of Parameters to Functions

456

(3)

Recursion in INFORMIX-4GL

459

(2)

An Example of Recursion--The Parts Explosion Problem

461

(5)

Error Handling and Help Messages in INFORMIX-4GL

466

(15)

whenever error Statement

467

(2)

Error Logging with INFORMIX-4GL

469

(2)

Trapping Forms and Expression Errors with whenever any error

471

(2)

Error Messages

473

(1)

Generalized Error-Handling Routines

474

(1)

An Error Prompt Function

475

(1)

Help Messages in INFORMIX-4GL

476

(2)

The sqlca Record

478

(1)

The sqlerrd Array

479

(1)

The sqlwarn String

480

(1)

Chapter Summary

481

(2)

Index

483

Please wait while the item is added to your bag...

FREE SHIPPING

Rent More, Save More! Use code: BBRENTAL

5% off 1 book, 7% off 2 books, 10% off 3+ books

Informix

Summary

Table of Contents

Supplemental Materials

FREE SHIPPING

Rent More, Save More! Use code: BBRENTAL

5% off 1 book, 7% off 2 books, 10% off 3+ books

Informix

Summary

Table of Contents

Supplemental Materials

Digital License