Home | Previous Page | Next Page

Contents

In This Introduction

About This Manual

Software Dependencies

Assumptions About Your Locale

Demonstration Database

New Features in Dynamic Server, Version 10.0

Documentation Conventions

Typographical Conventions

Feature, Product, and Platform

Syntax Diagrams

Example Code Conventions

Additional Documentation

Installation Guides

Informix Error Messages

IBM Informix Dynamic Server Version 10.0 and CSDK Version 2.90 Documentation Set

Compliance with Industry Standards

IBM Welcomes Your Comments

Database Concepts

In This Chapter

Illustration of a Data Model

Concurrent Use and Security

Controlling Database Use

Centralized Management

Important Database Terms

The Relational Database Model

Operations on Tables

The Object-Relational Model (IDS)

Structured Query Language

Informix SQL and ANSI SQL

Interactive SQL

General Programming

ANSI-Compliant Databases

Global Language Support (GLS)

Composing SELECT Statements

In This Chapter

Introducing the SELECT Statement

Output from SELECT Statements

Some Basic Concepts

Single-Table SELECT Statements

Using the Asterisk Symbol (*)

Using the ORDER BY Clause to Sort the Rows

Selecting Specific Columns

Using the WHERE Clause

Creating a Comparison Condition

Using a FIRST Clause to Select Specific Rows

Expressions and Derived Values

Using Rowid Values In SELECT Statements

Multiple-Table SELECT Statements

Creating a Cartesian Product

Creating a Join

Some Query Shortcuts

Selecting Data from Complex Types (IDS)

In This Chapter

Selecting Row-Type Data

Selecting Columns of a Typed Table

Selecting Columns That Contain Row-Type Data

Selecting from a Collection

Selecting Nested Collections

Using the IN Keyword to Search for Elements in a Collection

Selecting Rows Within a Table Hierarchy

Selecting Rows of the Supertable without the ONLY Keyword

Selecting Rows from a Supertable with the ONLY Keyword

Using an Alias for a Supertable

Using Functions in SELECT Statements

In This Chapter

Using Functions in SELECT Statements

Aggregate Functions

Date-Conversion Functions (IDS)

Cardinality Function (IDS)

Smart-Large-Object Functions (IDS)

String-Manipulation Functions (IDS)

Other Functions

Using SPL Routines in SELECT Statements

Using Data Encryption Functions (IDS)

Composing Advanced SELECT Statements

In This Chapter

Using the GROUP BY and HAVING Clauses

Using the GROUP BY Clause

Using the HAVING Clause

Creating Advanced Joins

Subqueries in SELECT Statements

Correlated Subqueries

Subqueries in SELECT Statements

Subqueries in a Projection Clause

Subqueries in WHERE Clauses

Handling Collections in SELECT Statements (IDS)

Collection Subqueries

Collection-Derived Tables

In This Chapter

Modifying Your Database

Deleting All Rows of a Table

Deleting All Rows using TRUNCATE TABLE

Deleting Specified Rows

Deleting Selected Rows

Deleting Rows That Contain Row Types (IDS)

Deleting Rows That Contain Collection Types (IDS)

Deleting Rows from a Supertable (IDS)

Complicated Delete Conditions

Using a Delete Join (XPS)

Inserting Rows into Typed Tables (IDS)

Inserting into Row-Type Columns (IDS)

Inserting Rows into Supertables (IDS)

Inserting Collection Values into Columns (IDS)

Inserting Smart Large Objects (IDS)

Multiple Rows and Expressions

Restrictions on the Insert Selection

Selecting Rows to Update

Updating with Uniform Values

Restrictions on Updates

Updating with Selected Values

Updating Row Types (IDS)

Updating Collection Types (IDS)

Updating Rows of a Supertable (IDS)

Using a CASE Expression to Update a Column

Using SQL Functions to Update Smart Large Objects (IDS)

Using a Join to Update a Column

Privileges on a Database and on its Objects

Database-Level Privileges

Table-Level Privileges

Displaying Table Privileges

Granting Privileges to Roles

Entity Integrity

Semantic Integrity

Referential Integrity

Object Modes and Violation Detection

Interrupted Modifications

Transaction Logging

Specifying Transactions

Backups and Logs with Informix Database Servers

Concurrency and Locks

IBM Informix Data Replication (IDS)

Accessing and Modifying Data in an External Database

In This Chapter

Accessing Other Database Servers

Accessing ANSI Databases

Creating Joins Between External Database Servers

Accessing External Routines (IDS)

Restrictions for Remote Database Access

SQL Statements and Logging Modes

Accessing External Database Objects

Programming with SQL

In This Chapter

SQL in Programs

SQL in SQL APIs

SQL in Application Languages

Static Embedding

Dynamic Statements

Program Variables and Host Variables

Calling the Database Server

SQL Communications Area

SQLERRM Character String

Retrieving Single Rows

Data Type Conversion

Working with NULL Data

Dealing with Errors

Retrieving Multiple Rows

Declaring a Cursor

Opening a Cursor

Cursor Input Modes

Active Set of a Cursor

Using a Cursor: A Parts Explosion

Preparing a Statement

Executing Prepared SQL

Dynamic Host Variables

Freeing Prepared Statements

Quick Execution

Embedding Data-Definition Statements

Granting and Revoking Privileges in Applications

Assigning Roles

Modifying Data Through SQL Programs

In This Chapter

Direct Deletions

Deleting with a Cursor

Using an Insert Cursor

Rows of Constants

An Insert Example

Using an Update Cursor

Cleaning Up a Table

Programming for a Multiuser Environment

In This Chapter

Concurrency and Performance

Locking and Integrity

Locking and Performance

Concurrency Issues

Duration of a Lock

Locks While Modifying

Locking with the SELECT Statement

Setting the Isolation Level

Retaining Update Locks

Locks Placed with INSERT, UPDATE, and DELETE

Understanding the Behavior of the Lock Types

Controlling Data Modification with Access Modes

Setting the Lock Mode

Waiting for Locks

Not Waiting for Locks

Waiting a Limited Time

Handling a Deadlock

Handling External Deadlock

Simple Concurrency

Using the SQL Statement Cache

Creating and Using SPL Routines

In This Chapter

Introduction to SPL Routines

What You Can Do with SPL Routines

SPL Routine Behavior for Extended Parallel Server

Writing SPL Routines

Using the CREATE PROCEDURE or CREATE FUNCTION Statement

Example of a Complete Routine

Creating an SPL Routine in a Program

Routines in Distributed Operation

Defining and Using Variables

Declaring Local Variables

Declaring Global Variables

Assigning Values to Variables

Expressions in SPL Routines

Writing the Statement Block

Implicit and Explicit Statement Blocks

Using the FOREACH Loop to Define Cursors

Using an IF - ELIF - ELSE Structure

Adding WHILE and FOR Loops

Returning Values from an SPL Function

Returning a Single Value

Returning Multiple Values

Handling Row-Type Data (IDS)

Precedence of Dot Notation

Updating a Row-Type Expression

Handling Collections (IDS)

Using Collection Data Types

Preparing for Collection Data Types (IDS)

Inserting Elements into a Collection Variable

Selecting Elements from a Collection

Deleting a Collection Element

Updating a Collection Element

Updating the Entire Collection

Inserting into a Collection

Executing Routines

Using the EXECUTE Statements

Using the CALL Statement

Executing Routines in Expressions

Executing an External Function with the RETURN Statement

Executing Cursor Functions from an SPL Routine

Dynamic Routine-Name Specification

Privileges on Routines

Privileges for Registering a Routine

Privileges for Executing a Routine

Privileges on Objects Associated with a Routine

DBA Privileges for Executing a Routine

Finding Errors in an SPL Routine

Looking at Compile-Time Warnings

Generating the Text of the Routine

Debugging an SPL Routine

Exception Handling

Trapping an Error and Recovering

Scope of Control of an ON EXCEPTION Statement

User-Generated Exceptions

Checking the Number of Rows Processed in an SPL Routine

Creating and Using Triggers

In This Chapter

When to Use Triggers

How to Create a Trigger

Assigning a Trigger Name

Specifying the Trigger Event

Defining the Triggered Actions

A Complete CREATE TRIGGER Statement

Using Triggered Actions

Using BEFORE and AFTER Triggered Actions

Using FOR EACH ROW Triggered Actions

Using SPL Routines as Triggered Actions

Triggers in a Table Hierarchy (IDS)

Using Select Triggers (IDS)

SELECT Statements That Execute Triggered Actions

Restrictions on Execution of Select Triggers

Select Triggers on Tables in a Table Hierarchy

Re-Entrant Triggers

INSTEAD OF Triggers on Views (IDS)

Using an INSTEAD OF Trigger to Update on a View

Tracing Triggered Actions

Example of TRACE Statements in an SPL Routine

Example of TRACE Output

Generating Error Messages

Applying a Fixed Error Message

Generating a Variable Error Message

Appendix. Accessibility

Dotted Decimal Syntax Diagrams

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]