Manual Informix 10

DB2 IBM Informix

Version 10.0/8.5
IBM Informix DB-Access User’s Guide
G251-2277-00
DB2 IBM Informix
®

Version 10.0/8.5
IBM Informix DB-Access User’s Guide
G251-2277-00
Note!
Before using this information and the product it supports, read the information in “Notices” on page D-1.
First Edition (December 2004)

This document contains proprietary information of IBM. It is provided under a license agreement and is protected by
copyright law. The information contained in this publication does not include any product warranties, and any
statements provided in this manual should not be interpreted as such.
When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any
way it believes appropriate without incurring any obligation to you.
© Copyright International Business Machines Corporation 1996, 2004. All rights reserved.
US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract
with IBM Corp.
Contents
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii
About This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii
Types of Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . viii
Software Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . viii
Assumptions About Your Locale . . . . . . . . . . . . . . . . . . . . . . . viii
New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix
Documentation Conventions . . . . . . . . . . . . . . . . . . . . . . . . . ix
Typographical Conventions . . . . . . . . . . . . . . . . . . . . . . . . . ix
Feature, Product, and Platform . . . . . . . . . . . . . . . . . . . . . . . . x
Syntax Diagrams . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
Example Code Conventions . . . . . . . . . . . . . . . . . . . . . . . . xiv
Additional Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Installation Guides . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Online Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Informix Error Messages . . . . . . . . . . . . . . . . . . . . . . . . . xvii
Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviii
Online Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviii
Accessibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviii
IBM Informix Dynamic Server Version 10.0 and CSDK Version 2.90 Documentation Set . . . . . xviii
Compliance with Industry Standards . . . . . . . . . . . . . . . . . . . . . . xxi
IBM Welcomes Your Comments . . . . . . . . . . . . . . . . . . . . . . . . xxii
Chapter 1. Getting Started with DB-Access . . . . . . . . . . . . . . . . . . . 1-1

What Is DB-Access? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-2
Using DB-Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-2
Setting Up DB-Access . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-4
Pre-DB-Access Installation . . . . . . . . . . . . . . . . . . . . . . . . . 1-4
Environment Variables . . . . . . . . . . . . . . . . . . . . . . . . . . 1-4
Creating and Working with the Demonstration Databases . . . . . . . . . . . . . . . 1-6
Demonstration Databases . . . . . . . . . . . . . . . . . . . . . . . . . 1-6
Demonstration Installation . . . . . . . . . . . . . . . . . . . . . . . . . 1-7
Command-Line Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . 1-8
Privileges for the Demonstration Database . . . . . . . . . . . . . . . . . . . . 1-10
Permissions for the SQL Command Files. . . . . . . . . . . . . . . . . . . . . 1-10
Invoking DB-Access . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-10
Starting the DB-Access Menu Interface . . . . . . . . . . . . . . . . . . . . 1-11
Executing a Command File . . . . . . . . . . . . . . . . . . . . . . . . 1-17
Viewing the Information Schema . . . . . . . . . . . . . . . . . . . . . . 1-17
Checking for ANSI Compliance . . . . . . . . . . . . . . . . . . . . . . . 1-18
Checking the Scale of DECIMAL(p) Values . . . . . . . . . . . . . . . . . . . 1-18
Activating the XLUF Feature for Nonprintable Characters . . . . . . . . . . . . . . 1-19
Using DB-Access Interactively in Non-Menu Mode . . . . . . . . . . . . . . . . 1-19
Related Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-23
Chapter 2. Using the Full-Screen Menu Interface . . . . . . . . . . . . . . . . . 2-1
© Copyright IBM Corp. 1996, 2004 iii

Using the DB-Access User Interface . . . . . . . . . . . . . . . . . . . . . . . 2-1
Using the Keyboard with DB-Access . . . . . . . . . . . . . . . . . . . . . 2-2
Using Menus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-3
Using Response Screens. . . . . . . . . . . . . . . . . . . . . . . . . . 2-4
Using the HELP Screen . . . . . . . . . . . . . . . . . . . . . . . . . . 2-5
Using an Editor Screen . . . . . . . . . . . . . . . . . . . . . . . . . . 2-6
Alternative Approaches . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-6
Database-Level Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . 2-6
Table-Level Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-8
Related Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-10
Chapter 3. The Query-language Option . . . . . . . . . . . . . . . . . . . . . 3-1

Overview of the SQL Menu . . . . . . . . . . . . . . . . . . . . . . . . .3-2
Using a System Editor . . . . . . . . . . . . . . . . . . . . . . . . . . .3-2
The Query-language Option . . . . . . . . . . . . . . . . . . . . . . . . .3-3
Entering a New SQL Statement . . . . . . . . . . . . . . . . . . . . . . . .3-4
Using the Editing Keys . . . . . . . . . . . . . . . . . . . . . . . . . .3-5
Editing Restrictions . . . . . . . . . . . . . . . . . . . . . . . . . . .3-6
Running an SQL Statement . . . . . . . . . . . . . . . . . . . . . . . . .3-7
Statements That the Run Option Supports . . . . . . . . . . . . . . . . . . .3-7
Viewing Successful Results . . . . . . . . . . . . . . . . . . . . . . . . 3-11
What Happens When Errors Occur . . . . . . . . . . . . . . . . . . . . . 3-12
Modifying an SQL Statement . . . . . . . . . . . . . . . . . . . . . . . . 3-12
Redirecting Query Results . . . . . . . . . . . . . . . . . . . . . . . . . 3-14
Sending Output to a Printer . . . . . . . . . . . . . . . . . . . . . . . . 3-15
Sending Output to a File . . . . . . . . . . . . . . . . . . . . . . . . . 3-15
Sending Output to a Pipe . . . . . . . . . . . . . . . . . . . . . . . . . 3-16
Choosing an Existing SQL Statement . . . . . . . . . . . . . . . . . . . . . . 3-16
Saving the Current SQL Statement . . . . . . . . . . . . . . . . . . . . . . . 3-18
Displaying Table Information . . . . . . . . . . . . . . . . . . . . . . . . 3-19
Dropping an SQL Statement . . . . . . . . . . . . . . . . . . . . . . . . . 3-20
Support for SPL Routines . . . . . . . . . . . . . . . . . . . . . . . . . . 3-21
Related Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-24
Chapter 4. The Database Option . . . . . . . . . . . . . . . . . . . . . . . 4-1

Selecting a Database Menu Option . . . . . . . . . . . . . . . . . . . . . . . 4-2
Selecting a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-2
List of Available Databases. . . . . . . . . . . . . . . . . . . . . . . . . 4-3
Specifying a Database . . . . . . . . . . . . . . . . . . . . . . . . . . 4-3
Creating a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-4
Specifying a Dbspace . . . . . . . . . . . . . . . . . . . . . . . . . . 4-5
Specifying Logging . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-5
Exiting the CREATE DATABASE Menu . . . . . . . . . . . . . . . . . . . . 4-5
Displaying Database Information . . . . . . . . . . . . . . . . . . . . . . . 4-6
Retrieving Information on Dbspaces . . . . . . . . . . . . . . . . . . . . . 4-7
Retrieving Nondefault Locale Information . . . . . . . . . . . . . . . . . . . 4-8
Retrieving Information on Routines . . . . . . . . . . . . . . . . . . . . . . 4-9
Selecting a Different Database . . . . . . . . . . . . . . . . . . . . . . . 4-10
Deleting a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-10
The DROP DATABASE Screen . . . . . . . . . . . . . . . . . . . . . . . 4-11
iv IBM Informix DB-Access User’s Guide

Confirming Your Decision to Delete a Database . . . . . . . . . . . . . . . . . 4-11
Closing a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-12
Related Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-13
Chapter 5. The Table Option . . . . . . . . . . . . . . . . . . . . . . . . 5-1

The TABLE Menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-2
Creating or Altering a Table . . . . . . . . . . . . . . . . . . . . . . . . . 5-3
The CREATE TABLE Screen . . . . . . . . . . . . . . . . . . . . . . . . 5-3
The ALTER TABLE Screen . . . . . . . . . . . . . . . . . . . . . . . . . 5-4
Using the Schema Editor . . . . . . . . . . . . . . . . . . . . . . . . . 5-5
Adding Columns to a Table (Add Option) . . . . . . . . . . . . . . . . . . . 5-6
Building the Table (Exit Option) . . . . . . . . . . . . . . . . . . . . . . 5-11
Modifying Columns (Modify Option) . . . . . . . . . . . . . . . . . . . . . 5-12
Deleting Columns (Drop Option) . . . . . . . . . . . . . . . . . . . . . . 5-12
Arranging Storage and Locking (Table_options) . . . . . . . . . . . . . . . . . 5-13
Defining Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . 5-25
Displaying Column Information . . . . . . . . . . . . . . . . . . . . . . 5-36
Displaying Index Information . . . . . . . . . . . . . . . . . . . . . . . 5-39
Displaying Table-Level Privileges . . . . . . . . . . . . . . . . . . . . . . 5-40
Displaying References Privileges . . . . . . . . . . . . . . . . . . . . . . 5-40
Displaying Column Constraints and Defaults . . . . . . . . . . . . . . . . . . 5-41
Displaying Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-43
Displaying Fragmentation Information . . . . . . . . . . . . . . . . . . . . 5-44
Dropping a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-45
Moving a Table (XPS) . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-46
Related Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-48
Chapter 6. The Connection and Session Options . . . . . . . . . . . . . . . . . 6-1

Choosing the Connection Option . . . . . . . . . . . . . . . . . . . . . . . 6-1
Connecting to a Database Environment . . . . . . . . . . . . . . . . . . . . 6-2
Disconnecting from a Database Environment . . . . . . . . . . . . . . . . . . 6-5
Transaction Processing . . . . . . . . . . . . . . . . . . . . . . . . . . 6-5
Choosing the Session Option . . . . . . . . . . . . . . . . . . . . . . . . . 6-6
Appendix A. How to Read Online Help for SQL Statements . . . . . . . . . . . . . A-1
Appendix B. Demonstration SQL . . . . . . . . . . . . . . . . . . . . . . . B-1
Appendix C. Accessibility . . . . . . . . . . . . . . . . . . . . . . . . . C-1
Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . D-1
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . X-1
Contents v
vi IBM Informix DB-Access User’s Guide
Introduction
About This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii
Types of Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . viii
Software Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . viii
Assumptions About Your Locale . . . . . . . . . . . . . . . . . . . . . . . viii
New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix
Documentation Conventions . . . . . . . . . . . . . . . . . . . . . . . . . ix
Typographical Conventions . . . . . . . . . . . . . . . . . . . . . . . . . ix
Feature, Product, and Platform . . . . . . . . . . . . . . . . . . . . . . . . x
Syntax Diagrams . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
How to Read a Command-Line Syntax Diagram . . . . . . . . . . . . . . . . xii
Keywords and Punctuation . . . . . . . . . . . . . . . . . . . . . . . xiii
Identifiers and Names . . . . . . . . . . . . . . . . . . . . . . . . . xiv
Example Code Conventions . . . . . . . . . . . . . . . . . . . . . . . . xiv
Additional Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Installation Guides . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Online Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Locating Online Notes . . . . . . . . . . . . . . . . . . . . . . . . . xvi
Online Notes Filenames . . . . . . . . . . . . . . . . . . . . . . . . xvii
Informix Error Messages . . . . . . . . . . . . . . . . . . . . . . . . . xvii
Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviii
Online Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . xviii
Printed Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . xviii
Online Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviii
Accessibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviii
IBM Informix Dynamic Server Version 10.0 and CSDK Version 2.90 Documentation Set . . . . . xviii
Compliance with Industry Standards . . . . . . . . . . . . . . . . . . . . . . xxi
IBM Welcomes Your Comments . . . . . . . . . . . . . . . . . . . . . . . . xxii
In This Introduction
This introduction provides an overview of the information in this manual and
describes the conventions it uses.
About This Manual

This manual describes how to use the DB–Access utility to access, modify, and
retrieve information from Informix database servers. Chapter 1, “Getting
Started with DB-Access,” on page 1-1 explains how to create and work with
the demonstration databases provided with your Informix database server.
Important: Use DB–Access with the current version of an Informix database

server. If you use DB–Access with a database server from a
© Copyright IBM Corp. 1996, 2004 vii

different version, you might obtain inconsistent results, such as
when you use a version that does not support long identifiers
with a version that does.
Types of Users
This manual is written for the following users:
v Database users
v Database administrators
v Database-application programmers
This manual assumes that you have the following background:

v A working knowledge of your computer, your operating system, and the
utilities that your operating system provides
v Some experience working with relational databases or exposure to database
concepts
v Some experience with computer programming
If you have limited experience with relational databases, SQL, or your

operating system, see the following IBM Informix manuals:
v IBM Informix: Getting Started Guide
v IBM Informix: GLS User's Guide
v IBM Informix: Guide to SQL Reference
v IBM Informix: Guide to SQL Syntax
v IBM Informix: Guide to SQL Tutorial
Software Dependencies
This manual assumes that you are using one of the following database
servers:
v IBM Informix Extended Parallel Server, Version 8.50
v IBM Informix Dynamic Server, Version 10.0
Assumptions About Your Locale
IBM Informix products can support many languages, cultures, and code sets.
All culture-specific information is brought together in a single environment,
called a Global Language Support (GLS) locale.
This manual assumes that you use the U.S. 8859-1 English locale as the
default locale. The default is en_us.8859-1 (ISO 8859-1) on UNIX platforms or
en_us.1252 (Microsoft 1252) for Windows environments. This locale supports
U.S. English format conventions for dates, times, and currency, and also
supports the ISO 8859-1 or Microsoft 1252 code set, which includes the ASCII
code set plus many 8-bit characters such as é, è, and ñ.
viii IBM Informix DB-Access User’s Guide

If you plan to use nondefault characters in your data or your SQL identifiers,
or if you want to conform to the nondefault collation rules of character data,
you need to specify the appropriate nondefault locale.
For instructions on how to specify a nondefault locale, additional syntax, and

other considerations related to GLS locales, see the IBM Informix: GLS User's
Guide.
New Features
For a comprehensive list of new features for your database server, see the
IBM Informix: Getting Started Guide.
Documentation Conventions
This section describes the conventions that this manual uses. These
conventions make it easier to gather information from this and other volumes
in the documentation set.
The following conventions are discussed:

v Typographical conventions
v Other conventions
v Syntax diagrams
v Command-line conventions
v Example code conventions
Typographical Conventions
This manual uses the following conventions to introduce new terms, illustrate
screen displays, describe command syntax, and so forth.
Convention Meaning
KEYWORD All primary elements in a programming language statement
(keywords) appear in uppercase letters in a serif font.
italics Within text, new terms and emphasized words appear in italics.
italics Within syntax and code examples, variable values that you are to
italics specify appear in italics.
boldface Names of program entities (such as classes, events, and tables),
boldface environment variables, file and pathnames, and interface elements
(such as icons, menu items, and buttons) appear in boldface.
monospace Information that the product displays and information that you
monospace enter appear in a monospace typeface.
KEYSTROKE Keys that you are to press appear in uppercase letters in a sans serif
font.
Introduction ix
Convention Meaning
> This symbol indicates a menu item. For example, “Choose Tools >
Options” means choose the Options item from the Tools menu.
Tip: When you are instructed to “enter” characters or to “execute” a

command, immediately press RETURN after the entry. When you are
instructed to “type” the text or to “press” other keys, no RETURN is
required.
Feature, Product, and Platform
Feature, product, and platform markup identifies paragraphs that contain
feature-specific, product-specific, or platform-specific information. Some
examples of this markup follow:
Dynamic Server
Identifies information that is specific to IBM Informix Dynamic Server

End of Dynamic Server
Extended Parallel Server
Identifies information that is specific to IBM Informix Extended Parallel Server

End of Extended Parallel Server
UNIX Only
Identifies information that is specific to UNIX platforms

End of UNIX Only
Windows Only
Identifies information that is specific to the Windows environment

End of Windows Only
This markup can apply to one or more paragraphs within a section. When an
entire section applies to a particular product or platform, this is noted as part
of the heading text, for example:
Table Sorting (Linux Only)
x IBM Informix DB-Access User’s Guide

Syntax Diagrams
This guide uses syntax diagrams built with the following components to
describe the syntax for statements and all commands other than system-level
commands.
Note: Starting in 2004, syntax diagrams have been reformatted to conform to

the IBM standard.
Syntax diagrams depicting SQL and command-line statements have changed

in the following ways:
v The symbols at the beginning and end of statements are now double arrows
instead of a vertical line at the end.
v The symbols at the beginning and end of syntax segment diagrams are now
vertical lines instead of arrows.
v How many times a loop can be repeated is now explained in a diagram
footnote instead of a number in a gate symbol.
v Syntax statements that are longer than one line now continue on the next
line instead of looping down with a continuous line.
v Product or condition-specific paths are now explained in diagram footnotes
instead of icons.
The following table describes syntax diagram components.
Component represented in PDF Component represented in HTML Meaning

>>---------------------- Statement begins.
-----------------------> Statement continues on

next line.
>----------------------- Statement continues from

previous line.
----------------------->< Statement ends.
--------SELECT---------- Required item.
--+-----------------+--- Optional item.

’------LOCAL------’
---+-----ALL-------+--- Required item with choice.

+--DISTINCT-----+ One and only one item
’---UNIQUE------’ must be present.
Introduction xi
Component represented in PDF Component represented in HTML Meaning
---+------------------+--- Optional items with choice
+--FOR UPDATE-----+ are shown below the main
’--FOR READ ONLY--’ line, one of which you
might specify.
.---NEXT---------. The values below the
----+----------------+--- main line are optional, one
+---PRIOR--------+ of which you might
’---PREVIOUS-----’ specify. If you do not
specify an item, the value
above the line will be used
as the default.
.-------,-----------. Optional items. Several
V | items are allowed; a
---+-----------------+--- comma must precede each
+---index_name---+ repetition.
’---table_name---’
>>-| Table Reference |->< Reference to a syntax

segment.
Table Reference Syntax segment.
|--+-----view--------+--|
+------table------+
’----synonym------’
How to Read a Command-Line Syntax Diagram

The following command-line syntax diagram uses some of the elements listed
in the table in the previous section.
Creating a No-Conversion Job

onpladm create job job -n -d device -D database
-p project
-t table
(1)
Setting the Run Mode
-S server -T target
xii IBM Informix DB-Access User’s Guide

Notes:
1 See page 17-4
The second line in this diagram has a segment named “Setting the Run
Mode,” which according to the diagram footnote, is on page 17-4. This
segment is shown in the following segment diagram (the diagram uses
segment start and end components).
Setting the Run Mode:

l
c
-f
d u n N
p
a
To construct a command correctly, start at the top left with the command.
Follow the diagram to the right, including the elements that you want. The
elements in the diagram are case sensitive.
The Creating a No-Conversion Job diagram illustrates the following steps:

1. Type onpladm create job and then the name of the job.
2. Optionally, type -p and then the name of the project.
3. Type the following required elements:
v -n
v -d and the name of the device
v -D and the name of the database
v -t and the name of the table
4. Optionally, you can choose one or more of the following elements and
repeat them an arbitrary number of times:
v -S and the server name
v -T and the target server name
v The run mode. To set the run mode, follow the Setting the Run Mode
segment diagram to type -f, optionally type d, p, or a, and then
optionally type l or u.
5. Follow the diagram to the terminator.
Your diagram is complete.
Keywords and Punctuation

Keywords are words reserved for statements and all commands except
system-level commands. When a keyword appears in a syntax diagram, it is
Introduction xiii
shown in uppercase letters. When you use a keyword in a command, you can
write it in uppercase or lowercase letters, but you must spell the keyword
exactly as it appears in the syntax diagram.
You must also use any punctuation in your statements and commands exactly
as shown in the syntax diagrams.
Identifiers and Names

Variables serve as placeholders for identifiers and names in the syntax
diagrams and examples. You can replace a variable with an arbitrary name,
identifier, or literal, depending on the context. Variables are also used to
represent complex syntax elements that are expanded in additional syntax
diagrams. When a variable appears in a syntax diagram, an example, or text,
it is shown in lowercase italic.
The following syntax diagram uses variables to illustrate the general form of a
simple SELECT statement.
SELECT column_name FROM table_name
When you write a SELECT statement of this form, you replace the variables
column_name and table_name with the name of a specific column and table.
Example Code Conventions
Examples of SQL code occur throughout this manual. Except as noted, the
code is not specific to any single IBM Informix application development tool.
If only SQL statements are listed in the example, they are not delimited by
semicolons. For instance, you might see the code in the following example:
CONNECT TO stores_demo
...
DELETE FROM customer

WHERE customer_num = 121
...
COMMIT WORK
DISCONNECT CURRENT
To use this SQL code for a specific product, you must apply the syntax rules
for that product. For example, if you are using DB–Access, you must delimit
multiple statements with semicolons. If you are using an SQL API, you must
use EXEC SQL at the start of each statement and a semicolon (or other
appropriate delimiter) at the end of the statement.
Tip: Ellipsis points in a code example indicate that more code would be
added in a full application, but it is not necessary to show it to describe
the concept being discussed.
xiv IBM Informix DB-Access User’s Guide

For detailed directions on using SQL statements for a particular application
development tool or SQL API, see the manual for your product.
Additional Documentation
For additional information, refer to the following types of documentation:
v Installation guides
v Online notes
v Informix error messages
v Manuals
v Online help
Installation Guides
Installation guides are located in the /doc directory of the product CD or in
the /doc directory of the product‘s compressed file if you downloaded it from
the IBM Web site. Alternatively, you can obtain installation guides from the
IBM Informix Online Documentation site at
http://www.ibm.com/software/data/informix/pubs/library/.
Online Notes
The following sections describe the online files that supplement the
information in this manual. Please examine these files before you begin using
your IBM Informix product. They contain vital information about application
and performance issues.
Introduction xv
Online File Description Format
TOC Notes The TOC (Table of Contents) notes file HTML
provides a comprehensive directory of
hyperlinks to the release notes, the fixed and
known defects file, and all the documentation
notes files for individual manual titles.
Documentation Notes The documentation notes file for each manual HTML, text
contains important information and
corrections that supplement the information
in the manual or information that was
modified since publication.
Release Notes The release notes file describes feature HTML, text
differences from earlier versions of IBM
Informix products and how these differences
might affect current products. For some
products, this file also contains information
about any known problems and their
workarounds.
Machine Notes (Non-Windows platforms only) The machine text
notes file describes any platform-specific
actions that you must take to configure and
use IBM Informix products on your
computer.
Fixed and Known This text file lists issues that have been text
Defects File identified with the current version. It also lists
customer-reported defects that have been
fixed in both the current version and in
previous versions.
Locating Online Notes

Online notes are available from the IBM Informix Online Documentation site
at http://www.ibm.com/software/data/informix/pubs/library/. Additionally
you can locate these files before or after installation as described below.
Before Installation
All online notes are located in the /doc directory of the product CD. The
easiest way to access the documentation notes, the release notes, and the fixed
and known defects file is through the hyperlinks from the TOC notes file.
The machine notes file and the fixed and known defects file are only provided
in text format.
After Installation
xvi IBM Informix DB-Access User’s Guide

On UNIX platforms in the default locale, the documentation notes, release
notes, and machine notes files appear under the
$INFORMIXDIR/release/en_us/0333 directory.
Dynamic Server
On Windows the documentation and release notes files appear in the

Informix folder. To display this folder, choose Start > Programs > IBM
Informix Dynamic Server version > Documentation Notes or Release Notes
from the taskbar.
Machine notes do not apply to Windows platforms.

Online Notes Filenames

Online notes have the following file formats:
Online File File Format Examples

TOC Notes prod_os_tocnotes_version.html ids_win_tocnotes_10.0.html
Documentation Notes prod_bookname_docnotes_version.html/txt ids_hpl_docnotes_10.0.html
Release Notes prod_os_relnotes_version.html/txt ids_unix_relnotes_10.0.txt
Machine Notes prod_machine_notes_version.txt ids_machine_notes_10.0.txt
Fixed and Known prod_defects_version.txt ids_defects_10.0.txt
Defects File client_defects_2.90.txt
ids_win_fixed_and_known ids_win_fixed_and_known
_defects_version.txt _defects_10.0.txt
Informix Error Messages

This file is a comprehensive index of error messages and their corrective
actions for the Informix products and version numbers.
On UNIX platforms, use the finderr command to read the error messages and
their corrective actions.
Dynamic Server
On Windows, use the Informix Error Messages utility to read error messages
and their corrective actions. To display this utility, choose Start > Programs >
IBM Informix Dynamic Server version > Informix Error Messages from the
taskbar.
Introduction xvii
You can also access these files from the IBM Informix Online Documentation
site at http://www.ibm.com/software/data/informix/pubs/library/.
Manuals
Online Manuals
A CD that contains your manuals in electronic format is provided with your
IBM Informix products. You can install the documentation or access it directly
from the CD. For information about how to install, read, and print online
manuals, see the installation insert that accompanies your CD. You can also
obtain the same online manuals from the IBM Informix Online Documentation
site at http://www.ibm.com/software/data/informix/pubs/library/.
Printed Manuals
To order hardcopy manuals, contact your sales representative or visit the IBM
Publications Center Web site at
http://www.ibm.com/software/howtobuy/data.html.
Online Help
IBM Informix online help, provided with each graphical user interface (GUI),
displays information about those interfaces and the functions that they
perform. Use the help facilities that each GUI provides to display the online
help.
Accessibility
IBM is committed to making our documentation accessible to persons with
disabilities. Our books are available in HTML format so that they can be
accessed with assistive technology such as screen reader software. The syntax
diagrams in our manuals are available in dotted decimal format, which is an
accessible format that is available only if you are using a screen reader. For
more information about the dotted decimal format, see the Accessibility
appendix.
IBM Informix Dynamic Server Version 10.0 and CSDK Version 2.90
Documentation Set
The following tables list the manuals that are part of the IBM Informix
Dynamic Server, Version 10.0 and the CSDK Version 2.90, documentation set.
PDF and HTML versions of these manuals are available at
http://www.ibm.com/software/data/informix/pubs/library/. You can order
hardcopy versions of these manuals from the IBM Publications Center at
http://www.ibm.com/software/howtobuy/data.html.
xviii IBM Informix DB-Access User’s Guide

Table 1. Database Server Manuals
Manual Subject
Administrator’s Guide Understanding, configuring, and administering your database server.
Administrator’s Reference Reference material for Informix Dynamic Server, such as the syntax of
database server utilities onmode and onstat, and descriptions of
configuration parameters, the sysmasters tables, and logical-log records.
Backup and Restore Guide The concepts and methods you need to understand when you use the
ON-Bar and ontape utilities to back up and restore data.
DB-Access User’s Guide Using the DB-Access utility to access, modify, and retrieve data from
Informix databases.
DataBlade API The DataBlade API functions and the subset of ESQL/C functions that
Function Reference the DataBlade API supports. You can use the DataBlade API to develop
client LIBMI applications and C user-defined routines that access data in
Informix databases.
DataBlade API The DataBlade API, which is the C-language application-programming
Programmer’s Guide interface provided with Dynamic Server. You use the DataBlade API to
develop client and server applications that access data stored in Informix
databases.
Database Design and Designing, implementing, and managing your Informix databases.
Implementation Guide
Enterprise Replication How to design, implement, and manage an Enterprise Replication system
Guide to replicate data between multiple database servers.
Error Messages file Causes and solutions for numbered error messages you might receive
when you work with IBM Informix products.
Getting Started Guide Describes the products bundled with IBM Informix Dynamic Server and
interoperability with other IBM products. Summarizes important features
of Dynamic Server and the new features for each version.
Guide to SQL: Reference Information about Informix databases, data types, system catalog tables,
environment variables, and the stores_demo demonstration database.
Guide to SQL: Syntax Detailed descriptions of the syntax for all Informix SQL and SPL
statements.
Guide to SQL: Tutorial A tutorial on SQL, as implemented by Informix products, that describes
the basic ideas and terms that are used when you work with a relational
database.
High-Performance Loader Accessing and using the High-Performance Loader (HPL), to load and
User’s Guide unload large quantities of data to and from Informix databases.
Installation Guide for Instructions for installing IBM Informix Dynamic Server on Windows.
Microsoft Windows
Installation Guide for Instructions for installing IBM Informix Dynamic Server on UNIX and
UNIX and Linux Linux.
Introduction xix
Table 1. Database Server Manuals (continued)
Manual Subject
J/Foundation Developer’s Writing user-defined routines (UDRs) in the Java programming language
Guide for Informix Dynamic Server with J/Foundation.
Large Object Locator Using the Large Object Locator, a foundation DataBlade module that can
DataBlade Module User’s be used by other modules that create or store large-object data. The Large
Guide Object Locator enables you to create a single consistent interface to large
objects and extends the concept of large objects to include data stored
outside the database.
Migration Guide Conversion to and reversion from the latest versions of Informix
database servers. Migration between different Informix database servers.
Optical Subsystem Guide The Optical Subsystem, a utility that supports the storage of BYTE and
TEXT data on optical disk.
Performance Guide Configuring and operating IBM Informix Dynamic Server to achieve
optimum performance.
R-Tree Index User’s Guide Creating R-tree indexes on appropriate data types, creating new operator
classes that use the R-tree access method, and managing databases that
use the R-tree secondary access method.
SNMP Subagent Guide The IBM Informix subagent that allows a Simple Network Management
Protocol (SNMP) network manager to monitor the status of Informix
servers.
Storage Manager Informix Storage Manager (ISM), which manages storage devices and
Administrator’s Guide media for your Informix database server.
Trusted Facility Guide The secure-auditing capabilities of Dynamic Server, including the creation
and maintenance of audit logs.
User-Defined Routines and How to define new data types and enable user-defined routines (UDRs)
Data Types Developer’s to extend IBM Informix Dynamic Server.
Guide
Virtual-Index Interface Creating a secondary access method (index) with the Virtual-Index
Programmer’s Guide Interface (VII) to extend the built-in indexing schemes of IBM Informix
Dynamic Server. Typically used with a DataBlade module.
Virtual-Table Interface Creating a primary access method with the Virtual-Table Interface (VTI)
Programmer’s Guide so that users have a single SQL interface to Informix tables and to data
that does not conform to the storage scheme of Informix Dynamic Server.
Table 2. Client/Connectivity Manuals

Manual Subject
Client Products Installation Installing IBM Informix Client Software Developer’s Kit (Client SDK) and
Guide IBM Informix Connect on computers that use UNIX, Linux, and
Windows.
Embedded SQLJ User’s Using IBM Informix Embedded SQLJ to embed SQL statements in Java
Guide programs.
xx IBM Informix DB-Access User’s Guide

Table 2. Client/Connectivity Manuals (continued)
Manual Subject
ESQL/C Programmer’s The IBM Informix implementation of embedded SQL for C.
Manual
GLS User’s Guide The Global Language Support (GLS) feature, which allows IBM Informix
APIs and database servers to handle different languages, cultural
conventions, and code sets.
JDBC Driver Programmer’s Installing and using Informix JDBC Driver to connect to an Informix
Guide database from within a Java application or applet.
.NET Provider Reference Using Informix .NET Provider to enable .NET client applications to
Guide access and manipulate data in Informix databases.
ODBC Driver Programmer’s Using the Informix ODBC Driver API to access an Informix database and
Manual interact with the Informix database server.
OLE DB Provider Installing and configuring Informix OLE DB Provider to enable client
Programmer’s Guide applications, such as ActiveX Data Object (ADO) applications and Web
pages, to access data on an Informix server.
Object Interface for C++ The architecture of the C++ object interface and a complete class
Programmer’s Guide reference.
Table 3. DataBlade Developer’s Kit Manuals

Manual Subject
DataBlade Developer’s Kit Developing and packaging DataBlade modules using BladeSmith and
User’s Guide BladePack.
DataBlade Module Basic orientation for developing DataBlade modules. Includes an
Development Overview example illustrating the development of a DataBlade module.
DataBlade Module Installing DataBlade modules and using BladeManager to manage
Installation and Registration DataBlade modules in Informix databases.
Guide
Compliance with Industry Standards

The American National Standards Institute (ANSI) and the International
Organization of Standardization (ISO) have jointly established a set of
industry standards for the Structured Query Language (SQL). IBM Informix
SQL-based products are fully compliant with SQL-92 Entry Level (published
as ANSI X3.135-1992), which is identical to ISO 9075:1992. In addition, many
features of IBM Informix database servers comply with the SQL-92
Intermediate and Full Level and X/Open SQL Common Applications
Environment (CAE) standards.
Introduction xxi
IBM Welcomes Your Comments
We want to know about any corrections or clarifications that you would find
useful in our manuals, which will help us improve future versions. Include
the following information:
v The name and version of the manual that you are using
v Section and page number
v Your suggestions about the manual
Send your comments to us at the following email address:
[email protected]
This email address is reserved for reporting errors and omissions in our
documentation. For immediate help with a technical problem, contact IBM
Technical Support.
We appreciate your suggestions.
xxii IBM Informix DB-Access User’s Guide

Chapter 1. Getting Started with DB-Access
What Is DB-Access? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-2
Using DB-Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-2
Setting Up DB-Access . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-4
Pre-DB-Access Installation . . . . . . . . . . . . . . . . . . . . . . . . . 1-4
Environment Variables . . . . . . . . . . . . . . . . . . . . . . . . . . 1-4
Creating and Working with the Demonstration Databases . . . . . . . . . . . . . . . 1-6
Demonstration Databases . . . . . . . . . . . . . . . . . . . . . . . . . 1-6
Demonstration Installation . . . . . . . . . . . . . . . . . . . . . . . . . 1-7
Command-Line Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . 1-8
Privileges for the Demonstration Database . . . . . . . . . . . . . . . . . . . . 1-10
Permissions for the SQL Command Files. . . . . . . . . . . . . . . . . . . . . 1-10
Invoking DB-Access . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-10
Starting the DB-Access Menu Interface . . . . . . . . . . . . . . . . . . . . 1-11
Displaying the Main Menu . . . . . . . . . . . . . . . . . . . . . . . 1-11
Displaying Other Menus or Options . . . . . . . . . . . . . . . . . . . . 1-12
Menu Suboptions . . . . . . . . . . . . . . . . . . . . . . . . . . 1-14
Examples of Command-Line Syntax . . . . . . . . . . . . . . . . . . . . 1-16
Executing a Command File . . . . . . . . . . . . . . . . . . . . . . . . 1-17
Viewing the Information Schema . . . . . . . . . . . . . . . . . . . . . . 1-17
Checking for ANSI Compliance . . . . . . . . . . . . . . . . . . . . . . . 1-18
Checking the Scale of DECIMAL(p) Values . . . . . . . . . . . . . . . . . . . 1-18
Activating the XLUF Feature for Nonprintable Characters . . . . . . . . . . . . . . 1-19
Using DB-Access Interactively in Non-Menu Mode . . . . . . . . . . . . . . . . 1-19
Reading from the Keyboard or Standard Input Device . . . . . . . . . . . . . . 1-19
Connecting to a Database Environment in Non-Menu Mode . . . . . . . . . . . . 1-21
Using the DB-Access History in Command Mode (XPS) . . . . . . . . . . . . . 1-22
Related Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-23
In This Chapter
This chapter introduces a new user to DB–Access. It provides information
about how to set up your DB–Access environment and the demonstration
database.
This chapter discusses the following topics:

v What is DB–Access?
v Setting up DB–Access
v Creating and working with the demonstration databases
v Setting the DB–Access permissions
v Invoking DB–Access
© Copyright IBM Corp. 1996, 2004 1-1

What Is DB-Access?
DB–Access provides a user interface for entering, executing, and debugging
Structured Query Language (SQL) statements and Stored Procedure Language
(SPL) routines.
SQL and SPL allow you to perform data-definition tasks, such as specifying
the number and type of data columns in a table, and data-management tasks,
such as storing, viewing, and changing table data. The DB–Access interface
allows you to apply powerful Informix extensions to SQL and SPL.
You can use DB–Access for the following aspects of database processing:
v Using ad hoc queries that you execute infrequently
v Connecting to one or more databases, transferring data between the
database and external text files, and displaying information about a
database
v Displaying system catalog tables and the Information Schema, which are
explained in the IBM Informix: Guide to SQL Reference
v Practicing the statements and examples provided in the IBM Informix: Guide
to SQL Tutorial or the IBM Informix: Database Design and Implementation
Guide
v Testing applications that you intend to store for use in a production
environment
Important: DB–Access is not intended as an application-development

environment. You branch conditionally or loop through SQL
statements when you run them within DB–Access.
Using DB-Access
You can use the DB–Access user interface to:
v Run statements interactively, discarding them after you achieve the desired
results, or saving them in a file for repetitive execution.
v Type statements directly in the DB–Access text-entry screen or your
preferred text editor.
v Start DB–Access in menu mode and select options from the menus.
Figure 1-1 on page 1-3 illustrates the top two levels of the DB–Access menu
hierarchy. The view of available options summarizes the types of database
tasks that you can accomplish with DB–Access.
1-2 IBM Informix DB-Access User’s Guide

Figure 1-1. The DB-Access Menu Hierarchy
For detailed submenu instructions, see the following chapters.

Option Instructions
Chapter 1. Getting Started with DB-Access 1-3

Query-language Chapter 3, “The Query-language Option,” on
page 3-1
Database Chapter 4, “The Database Option,” on page
4-1
Table Chapter 5, “The Table Option,” on page 5-1
Session Chapter 6, “The Connection and Session
Options,” on page 6-1
Connection Chapter 6, “The Connection and Session
Options,” on page 6-1
Setting Up DB-Access
This section reviews the procedure for setting up the DB-Access environment.
Pre-DB-Access Installation
Before you set up the DB–Access environment, you must perform the
following preparatory steps:
1. Install the database server and set environment variables.
Global Language Support
2. Set up the Global Language Support (GLS) locale, where language

localization requires it.
End of Global Language Support
3. Start the database server.
You can then run the initialization script for the demonstration database
(optional) and invoke the DB–Access program, as described in “Invoking
DB-Access” on page 1-10.
Environment Variables
As part of the installation and setup process, the system or database
administrator sets certain environment variables that enable IBM Informix
products to work within a particular operating-system environment. This
section lists the environment variables that affect your ability to use
DB–Access.
Important: You must have $INFORMIXDIR/bin in your path if you use

DB–Access on a UNIX platform or %INFORMIXDIR%\bin in
your path if you use DB–Access on a Windows platform. Your
operating system uses the path to locate the initialization script

and the dbaccess executable.
UNIX Only
In a UNIX environment, the database server must have the appropriate

terminal set up from among those listed in the INFORMIXTERM
environment variable.
End of UNIX Only
You can set the following optional environment variables:
Dynamic Server
v DBACCNOIGN rolls back an incomplete transaction if you execute the

LOAD command in menu mode.
v DBACCNOIGN rolls back an incomplete transaction if an error occurs

while you are in Non-Menu mode.
v DBCENTURY lets you choose the appropriate expansion for DATE and
DATETIME values that have only a two-digit year, such as 04/15/98.
v DBDATE specifies the end-user formats of DATE values. See IBM Informix:
Migration Guide for more information on this variable.
v DBEDIT sets the default DB–Access text editor without changing the
default text editor associated with the operating-system shell.
For more information on how DB–Access uses the text editor that you
specify as default, see “Entering a New SQL Statement” on page 3-4.
v DBFLTMASK sets the default floating-point values of data types FLOAT,
SMALLFLOAT, and DECIMAL within a 14-character buffer.
The effect of this variable is limited to the DB–Access display size for
numbers.
v DELIMIDENT causes the database server to interpret double quoted (“)
text as identifiers rather than strings.

v IFMX_HISTORY_SIZE determines the number of SQL commands logged
in the command history. The default value is 50. The maximum value is
500.
Note: In the menu mode, multiple SQL commands in a dbaccess editor can
execute all at once and will consume only one dbaccess history entry.
Dynamic Server
v IFX_LONGIDdetermines whether a client application can handle long

identifiers.
Note: If you use the IFX_LONGID environment variable to support SQL

identifiers with up to 128 bytes, some error, warning, or other
messages of DB-Access might truncate database object names that
include more than 18 characters in their identifiers. You can avoid
this truncation by not declaring names that have more than 18
characters.
For more information on environment variables, see the IBM Informix: Guide
to SQL Reference.
Creating and Working with the Demonstration Databases

You can practice using DB–Access with a demonstration database or with a
database that you create. If you use an Informix demonstration database, you
can add, delete, or change the provided data and scripts, then re-initialize the
database to its original condition. This manual provides examples of
statements run against the demonstration data, with illustrations that show
the results of these statements.
Demonstration Databases
The DB–Access utility, which is provided with your IBM Informix database
server products, includes one or more of the following demonstration
databases:
v The stores_demo database illustrates a relational schema with information
about a fictitious wholesale sporting-goods distributor. Many examples in
IBM Informix manuals are based on the stores_demo database.

v The sales_demo database illustrates a dimensional schema for
data-warehousing applications. For conceptual information about
dimensional data modeling, see the IBM Informix: Database Design and
Implementation Guide.
Dynamic Server
v The superstores_demo database illustrates an object-relational schema. The

superstores_demo database contains examples of extended data types, type
and table inheritance, and user-defined routines.
For descriptions of the databases and their contents, see the IBM Informix:
Guide to SQL Reference.
The scripts that you use to install the demonstration databases reside in the
$INFORMIXDIR/bin directory on UNIX platforms and in the
%INFORMIXDIR%\bin directory in Windows environments.
Table 1-1 lists the databases available for each database server. To set up or
reinitialize the demonstration database, run the corresponding initialization
script that Table 1-1 shows.
Table 1-1. Demonstration Databases
Server Type Database Name Model Initialization Script
All Informix database servers stores_demo Relational database dbaccessdemo
IBM Informix Extended Parallel sales_demo Dimensional data dbaccessdemo -dw
Server warehouse
IBM Informix Dynamic Server superstores_demo Object-relational dbaccessdemo_ud
database
Demonstration Installation
When you run the installation script for a demonstration database, the script
asks you if you would like to copy sample SQL command files. Command
files that the demo includes have a .sql extension and contain sample SQL
statements that you can use.
Always initialize or run DB–Access from the directory in which you want to
store SQL command files for the following reasons:

v Each time you create or reinitialize the demonstration database, the
installation script prompts you to ask if you want a copy of the sample
command files provided with the demonstration database saved in the
current directory.
v DB–Access lists only the files that end in the extension .sql in the current
directory.
Command files are described in Appendix B, “Demonstration SQL,” on page

B-1.
To prepare a demonstration database:

1. Create a new directory.
UNIX Only
You must have UNIX read and execute permissions for each directory in
the pathname that you create.
End of UNIX Only

2. Change directories to the new directory and run the initialization script.
on page 1-7 describes the various database models available for each
database server version. For options that you can use with the
initialization script, see “Command-Line Syntax” on page 1-8.
3. The initialization script displays a series of messages on the screen as the
database is created. The final message of the script prompts you to make a
choice. If you want to copy the command files into the directory that you
created, press Y.
Tip: If you want to discard changes that you made to your database or to the
command files, rerun the demonstration initialization script. When the
script prompt message displays, press Y to replace the command files
with the original versions.
Command-Line Syntax
The illustrations in this section show the syntax of the initialization scripts.
For assistance in reading the diagrams, see “Syntax Diagrams” on page xi in
the introduction to this manual.
To create stores_demo:
dbaccessdemo
-log dbname -dbspace dbspace_name

To create sales_demo:
dbaccessdemo -dw
Dynamic Server
To create superstores_demo:
dbaccessdemo_ud
-log dbname -dbspace dbspace_name

-dw creates the sales_demo database.
-log requests transaction logging for the demonstration database.
dbname substitutes for the default database name. For object-name
guidelines, see the IBM Informix: Guide to SQL Syntax.
-dbspace requests a particular dbspace location for the demonstration
database.
dbspace_name houses the demonstration database. If you do not specify a
dbspace name, by default, the data for the database is put in
the root dbspace. To create a dbspace, use the onspaces utility
as described in your IBM Informix: Administrator's Guide.
Examples:
v The following command creates a database named stores_demo:
dbaccessdemo
v The following example creates an instance of the stores_demo database
named demo_db:
dbaccessdemo demo_db
v The following command initializes the stores_demo database and also
initiates log transactions:
dbaccessdemo -log
v The following command creates an instance of the stores_demo database
named demo_db in dbspace_2:
dbaccessdemo demo_db -dbspace dbspace_2

Privileges for the Demonstration Database
When you run the initialization script to create an instance of the
demonstration database under your login, the database server recognizes your
login as the owner and database administrator (DBA) of that database. As
DBA, you automatically acquire some exclusive privileges over objects in your
database. You can extend those privileges to others with the GRANT
statement, which is described in the IBM Informix: Guide to SQL Syntax.
Permissions for the SQL Command Files

Some operating systems require that you have execute permissions to run
SQL command files, read permissions to display these files or their contents in
DB–Access, or write permissions to save modified or new files.
UNIX Only
Use the UNIX chmod command to enable execution of the SQL files that the
initialization script installed.
End of UNIX Only
Invoking DB-Access
For more information on how to invoke DB-Access, see the following
references:
v To display all the menus, start DB–Access at its main menu. See
“Displaying the Main Menu” on page 1-11.
v To start and exit from a specific DB–Access menu or screen, see “Displaying
Other Menus or Options” on page 1-12.
v To execute a file that contains SQL statements without displaying the
DB–Access menus, see “Executing a Command File” on page 1-17.
v To type DB–Access options at the command line, without the full-screen
menu interface, see “Using DB-Access Interactively in Non-Menu Mode” on
page 1-19.
v To check the DB–Access version or transfer nonprintable characters in
hexadecimal form, see “Activating the XLUF Feature for Nonprintable
Characters” on page 1-19.
Windows Only
You can set up the DB–Access program icon to perform any of the commands
that this chapter shows.

UNIX Only
If the TERM, TERMCAP, or TERMINFO environment variables do not

enable DB–Access to recognize the type of terminal you use, the main menu
does not appear. Instead, a message similar to the following text appears:
Unknown terminal type.
End of UNIX Only
Windows Only
If you use a Windows terminal to run DB–Access on a UNIX database server,

the terminal-emulation window must emulate a terminal type that DB–Access
can recognize, or the database server displays an unknown terminal-type
message in the terminal-emulation window.
For more information about INFORMIXTERM and the appropriate terminal

setup, see your IBM Informix: Installation Guide or the IBM Informix: Guide to
SQL Reference.
End of Windows Only
Tip: If your operating system cannot find dbaccess, place the full path before
the program name, as follows:
$INFORMIXDIR/bin/dbaccess
Starting the DB-Access Menu Interface

For information on how to read the diagrams in this section, see “Syntax
Diagrams” on page xi in the introduction to this manual. To familiarize
yourself with the DB–Access menu structure, see Figure 1-1 on page 1-3..
Displaying the Main Menu

Typically, you start DB–Access with the main menu as the top-level menu
from which you reach submenus and their options.
UNIX Only
If you use a window interface, such as OpenWindows, on a UNIX terminal,

issue the dbaccess command from a nonscrolling console window.
End of UNIX Only

If your path includes $INFORMIXDIR/bin, the following command shows
the simplest way to start DB–Access:
dbaccess
Without arguments, the single word dbaccess starts the main menu with no
database selected and no options activated. You can then select submenus
from the main menu.
Displaying Other Menus or Options

You can specify the options shown in the following diagram to access menus
directly.
dbaccess database
-ansi -nohistory -q
query_language _menu_option
filename
table
-t
table_menu_option
table
-d database_menu_option
-c connect_menu_option
-s
-e database filename
-
-v
-V
-X
-help
Dynamic Server

dbaccess database
-ansi -q
query_language _menu_option
filename
table
-t
table_menu_option
table
-d database_menu_option
-c connect_menu_option
-s
-e database filename
-
-m database filename
-
-v
-V
-X

-ansi causes DB–Access to generate a warning whenever it
encounters an Informix extension to ANSI-compliant syntax.
For more information, see “Checking for ANSI Compliance”
on page 1-18.
-nohistory suppresses the history feature. For more information, see
“Using the DB-Access History in Command Mode (XPS)” on
page 1-22. (XPS)
-c starts with the CONNECTION menu as the top-level menu.
-d starts with the DATABASE menu as the top-level menu.
-e echoes each line from a command file designated by filename.
-m displays all error messages generated by multiple levels of the
server that pertain to an SQL statement in command files.
(IDS)
-q starts at the query-language menu (SQL-menu) as the
top-level menu.
-s connects you to the main DB-Access menu and displays
information about the current session.
This information includes database server name, database
server type, the host computer, server capabilities, and other
settings.
-t starts at the TABLE menu as the top-level menu.
-v or -V displays the version number and serial number for DB–Access
without launching the application. You cannot use any other
options with -v or -V.
-X activates the hexadecimal format for LOAD and UNLOAD

statements. The -X option can be used after the -ansi option
and before any of the other options.
-help displays the dbaccess command line options (XPS).
-filename indicates that you are not specifying a database in the
command line because one is specified in a DATABASE
statement in the command file.
database name of the database that you want DB-Access to connect to
at the startup of your current session.
filename names a command file to load with the SQL menu.
table specifies a table in the database.
connect_menu_option
option or suboption on the connect menu. See
“CONNECTION Menu Options” on page 1-14.
database_menu_option
option or suboption on the database menu. See “DATABASE
Menu Options” on page 1-14.
query_language_menu_option
option or suboption on the query-language menu. See
“QUERY-LANGUAGE Menu Options” on page 1-15.
table_menu_option
option or suboption on the table menu. See “TABLE Menu
Options” on page 1-16.
If you exit from a submenu or option that you specified from the command
line, you will exit directly to the operating-system command line.
Menu Suboptions
The following menu suboptions allow you to access submenus directly.
CONNECTION Menu Options:

-cc chooses the Connect option on the CONNECTION menu.
-cd chooses the Disconnect option on the CONNECTION menu.
DATABASE Menu Options:

-dc chooses the Create option on the DATABASE menu.
-dcl takes you to the LOG option on the CREATE
DATABASE menu
-dd chooses the Drop option on the DATABASE menu.

-di chooses the Info option on the DATABASE menu. With this
option, you can add another letter as follows to go to the next
menu level and view:
-dib dbspaces information for the current database (OnLine
only)
-din NLS information for the current database
-dip stored procedures in the current database
If you do not include a database name before any -di option,
you must choose a current database from the SELECT
DATABASE screen.
-dl chooses the cLose option on the DATABASE menu.
-ds chooses the Select option on the DATABASE menu.
QUERY-LANGUAGE Menu Options:

-qc chooses the Choose option on the SQL menu.
-qd chooses the Drop option on the SQL menu.
-qi chooses the Info option on the SQL menu. With this option,
you can add another letter as shown in the following list (and
specify a table) to go to the next menu level and view:
-qic columns in the table
-qif information on fragmentation strategy for the table
-qig information on triggers in the table
-qii indexes on the table
-qio table constraints
-qip access privileges on the table
-qir table-level references privilege on the table
-qis table status information
If you do not include a table name with the -qi option, you
must choose one from the INFO FOR TABLE screen.
-qm chooses the Modify option on the SQL menu.
-qn chooses the New option on the SQL menu.
-qs chooses the Save option on the SQL menu.
-qu chooses the Use-editor option on the SQL menu.

If you do not include a database name before a -q option, you
must choose a current database from the SELECT DATABASE
screen.
When you select the Modify option on the QUERY-LANGUAGE menu, you
must first select a command file to modify from the CHOOSE menu. The
MODIFY screen then appears and displays the text.
Tip: You cannot go directly to the Run or Output option on the SQL menu.
Trying to do so results in an error message.
TABLE Menu Options:

-ta chooses the Alter option on the TABLE menu.
-tc chooses the Create option on the TABLE menu.
-td chooses the Drop option on the TABLE menu.
-ti chooses the Info option on the TABLE menu. With this option,
you can add another letter as shown in the following list (and
specify a table) to go to the next menu level and view:
-tic columns in the table
-tif information on fragmentation strategy for the table
-tig information on triggers in the table
-tii indexes on the table
-tio table constraints
-tip access privileges on the table
-tir table-level references privilege on the table
-tis table status information
If you do not include a table name with the -ti option, you
must choose one from the INFO FOR TABLE screen.
If you do not include a database name before a -t option, you
must choose a current database from the SELECT DATABASE
screen.
Examples of Command-Line Syntax

Assume that the database server that you have online contains a database
named mystores. To make the mystores database the current database, start
DB–Access with the following command:
dbaccess mystores

You can specify a database on a database server that is not online. For
example, either of the following commands selects the newstores database on
the xyz database server:
dbaccess newstores@xyz
dbaccess //xyz/newstores
When DB–Access starts, the database and database server name that you
specify appear on the dashed line, as Figure 1-2 shows.
DB-Access: Query-language Connection Database Table Session Exit
---------------- newstores@xyz ---------------------Press CTRL-W for Help ---
Figure 1-2. The DB-Access Main Menu with Database and Database Server Name
Executing a Command File

When you invoke DB–Access from the command line, you can specify a
database as current, execute a file that contains one or more SQL statements,
and display multilevel error messages.
The following sample command executes the SQL statements in a file named
sel_stock.sql on the mystores database:
dbaccess mystores sel_stock
The following sample command executes the SQL statements in the sel_all.sql
file on the database that file specifies:
dbaccess - sel_all.sql
Viewing the Information Schema

Use DB–Access to execute the xpg4_is.sql file in the $INFORMIXDIR/etc
directory. This SQL file creates the Information Schema and installs the views
for a specified database. The following command creates the Information
Schema for database mystores:
dbaccess mystores $INFORMIXDIR/etc/xpg4_is.sql
The Information Schema adds to the database four information-only views

that conform to X/Open XPG4 with Informix extensions. After you run
xpg4_is.sql, use DB–Access to retrieve information about the tables and
columns that you have access to in the specified database. For more
information on Information Schema views, see the IBM Informix: Guide to SQL
Reference.

Tip: It is not recommended that you install these XPG4-compliant views on
an ANSI database, because their format differs considerably from that of
the ANSI-compliant Information Schema views that the SQL standards
committee has defined.
Checking for ANSI Compliance
To check your SQL statements for compliance with ANSI standards, include
the -ansi option or set the DBANSIWARN environment variable. Use the
-ansi option with other dbaccess options such as -dc (to create a database), -tc
or -ta (to create or alter a table), or -qc filename (to choose a command file).
The following command checks for ANSI compliance while DB–Access creates
the database research:
dbaccess -ansi -dc research
You do not need to specify the -ansi option on the command line if the
DBANSIWARN environment variable is set.
DB–Access displays the SQLSTATE value with the warning under the
following circumstances:
v You include the -ansi option or set the DBANSIWARN environment
variable.
v You access or create an ANSI database.
v You run DB–Access in line mode or specify a .sql input file.
v Execution of a SQL statement generates a warning rather than an error.
For more information on ANSI-compliant databases, see the IBM Informix:

Guide to SQL Reference and the IBM Informix: Guide to SQL Tutorial. For more
information on SQLSTATE values, see the GET DIAGNOSTICS statement in
the IBM Informix: Guide to SQL Syntax. The IBM Informix: Guide to SQL Syntax
also provides information about ANSI compliance in Informix SQL syntax.
Checking the Scale of DECIMAL(p) Values
In an ANSI-compliant database, columns declared as a DECIMAL(p) data
type have a precision of p and a scale of zero, so that only integer values are
stored. In a database that is not ANSI-compliant, DECIMAL(p) is a
floating-point data type whose scale is large enough to store the exponential
notation for a value.
For example, the following calculation shows how many bytes a DECIMAL(5)
column requires in the default locale (where the decimal point occupies a
single byte):

1 byte for the sign of the data value
1 byte for the 1st digit
1 byte for the decimal point
4 bytes for the rest of the digits (precision of 5 - 1)
1 byte for the e symbol
1 byte for the sign of the exponent
3 bytes for the exponent
-------------------------------------------------
12 bytes total
Thus, ″12345″ in a DECIMAL(5) column is displayed as ″12345.00000″ (that is,

with a scale of 6) in a database that is not ANSI-compliant.
Activating the XLUF Feature for Nonprintable Characters
You can use the -X option to activate the hexadecimal load and unload format
(XLUF) feature in DB–Access at the command line. When you invoke
DB–Access with the -X option, the LOAD and UNLOAD SQL statements can
format nonprintable ASCII signs in hexadecimal format. The -X option can be
used after the -ansi option and before any of the other options. A .unl file that
the UNLOAD statement produces contains the hexadecimal format changes.
Important: The .unl files that contain data in a hexadecimal format are not
compatible with Informix database servers prior to Version 6.0.
However, .unl files generated without the XLUF functionality are
fully compatible with Version 6.0 or later database servers.
For more information, see the descriptions of the LOAD and UNLOAD
statements in the IBM Informix: Guide to SQL Syntax. Also see the discussion
of the various SQL utilities in the IBM Informix: Migration Guide and the data
types information in the IBM Informix: Guide to SQL Reference.
Using DB-Access Interactively in Non-Menu Mode
If you do not want to use the menus and do not have a prepared SQL file,
use your keyboard or standard input device to enter SQL statements.
Reading from the Keyboard or Standard Input Device

When you invoke DB–Access without a menu argument (such as -q) and with
a hyphen as the final argument, DB–Access processes commands from the
standard input device (on UNIX platforms) or the keyboard (on Windows
platforms). DB–Access reads what you type until you indicate that the input
is complete. Then DB–Access processes your input and writes the results to
the standard output device (on UNIX platforms), or the command window
(on Windows).

Interactive Input: DB–Access reads and executes SQL statements from the
terminal keyboard interactively. While DB–Access runs interactively, the
greater than (>) prompt marks the line where you type your next SQL
statement.
When you type a semicolon (;) to end a single SQL statement, DB–Access
processes that statement. When you press CTRL-D to end the interactive
session, DB–Access stops running. The following example shows user input
and results in an interactive session:
dbaccess - -
>database stores_demo;
Database selected.
>select count(*) from systables;
(count(*))
21
1 row(s) retrieved.
>^D
dbaccess - -
>database stores_demo;
Database selected.
>select count(*) from systables;
(count(*))
21
1 row(s) retrieved.
>^D
Batch Command Input on UNIX Platforms: You can use an in-line shell
script to supply one or more SQL statements. For example, you can use the
UNIX C, Bourne, or Korn shell with in-line standard input files:
dbaccess mystores- <<EOT!
select avg(customer_num) from customer
where fname matches ’[A-G]*’;
EOT!
You can use a pipe to supply SQL statements:

echo ’select count(*) from systables’ | dbaccess mystores

DB–Access interprets any line that begins with an exclamation mark (!) as a
shell command. You can mix shell escape lines with SQL statements and put
them in SQL statements, as follows:
dbaccess mystores -
>select
!echo hello
>hello
count(*) from systables;
>
(count(*))
21
1 row(s) retrieved.
>
Connecting to a Database Environment in Non-Menu Mode

You can use the CONNECT ... USER syntax in SQL statements that you issue
in interactive mode. However, DB–Access does not support the USER clause
of the CONNECT statement when you connect to a default database server.
Connecting in Interactive Non-Menu Mode: When you include the USER

‘user identifier’ clause in a CONNECT statement in interactive mode,
DB–Access prompts you to enter a password.
The following two command examples show how to connect to a database

server in interactive mode. The first example uses the CONNECT statement
without specifying a user identifier.
dbaccess -nohistory- -
> connect to ’@starfish’;
Connected.
If you include the USER clause in a CONNECT statement, as the second

example shows, DB–Access uses echo suppression to prompt you for a
password:
> connect to ’@starfish’ user ’marae’;
ENTER PASSWORD:
Connected.
Important: For security reasons, do not enter the password on the screen
where it can be seen. Also, do not include the USING password
clause in a CONNECT statement when you use DB–Access
interactively. If you are in interactive mode and attempt to enter a
password before the prompt, an error message appears.

Connecting with a File or Shell File in Background Mode: You can execute
the USER clause of a CONNECT statement in a DB–Access file that includes
the USER clause. The following example uses a command file that contains a
CONNECT statement with a USING clause to connect to a database server:
dbaccess - connfile.sql
Important: An SQL command file that contains the statement

CONNECT USER user_id USING password
should be protected from access by anyone other than the user_id

that the USER clause identifies.
UNIX Only
The following example uses a shell file to connect to a database server.

DB–Access prompts you for a password.
dbaccess - - <<\!
connect to ’@starfish’ user ’marae’;
!
ENTER PASSWORD:
End of UNIX Only
Using the DB-Access History in Command Mode (XPS)

DB-Access provides command history logging which makes it easier to
re-execute commands. The following example shows how the command
history function is used.
% dbaccess - -
1> create database sales_demo;
Database created.
2> create table customer (

2> customer_code integer,
2> customer_name char(31),
2> company_name char(20));
Table created.
3> insert into customer values (102, "Carole Sadler", "Sports Spot");
1 row(s) inserted.
4> history;
1 create database sales_demo

2 create table customer (

customer_code integer,
customer_name char(31),
company_name char(20))
3 insert into customer values (102, "Carole Sadler", "Sports Spot")
4> run 3;
1 row(s) inserted.
5> history;
1 create database sales_demo

2 create table customer (
company_name char(20))
5>
The history command lists the SQL statements used in the current session.
The number of commands that are logged is determined by the
IFMX_HISTORY_SIZE environment variable. For more information see
“Environment Variables” on page 1-4.
The run statement_number command executes the statement corresponding to

the number in the command history. The run command logs the original
command in the history. The history and run commands are not logged.
Related Manuals
For information on how to install the database server, set environment
variables, and put the database server online, see the manuals for your
database server listed in Table 1-2.

Table 1-2. Related Manuals
Task or Feature Topic System Manual
Installing the database server All IBM Informix: Installation Guide
Setting environment variables .cshrc or .login UNIX IBM Informix: Installation Guide
IBM Informix: Guide to SQL
Reference
Setting environment variables Registry Windows IBM Informix: Installation Guide
IBM Informix: Guide to SQL
Reference
Setting UNIX permissions chmod UNIX UNIX documentation
Putting database server online oninit All IBM Informix: Administrator's
Guide
Creating database spaces onspaces All IBM Informix: Administrator's
Guide
Granting permissions on GRANT All IBM Informix: Guide to SQL
database objects Reference

Chapter 2. Using the Full-Screen Menu Interface
Using the DB-Access User Interface . . . . . . . . . . . . . . . . . . . . . . . 2-1
Using the Keyboard with DB-Access . . . . . . . . . . . . . . . . . . . . . 2-2
Using Menus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-3
Choosing a Menu Option . . . . . . . . . . . . . . . . . . . . . . . . 2-3
Leaving a Menu Screen . . . . . . . . . . . . . . . . . . . . . . . . . 2-4
Using Response Screens. . . . . . . . . . . . . . . . . . . . . . . . . . 2-4
Reading the Screen Header . . . . . . . . . . . . . . . . . . . . . . . 2-4
Selecting or Entering Values on the Screen . . . . . . . . . . . . . . . . . . 2-4
Leaving a Response Screen . . . . . . . . . . . . . . . . . . . . . . . 2-5
Using the HELP Screen . . . . . . . . . . . . . . . . . . . . . . . . . . 2-5
Using an Editor Screen . . . . . . . . . . . . . . . . . . . . . . . . . . 2-6
Alternative Approaches . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-6
Database-Level Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . 2-6
Table-Level Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-8
Related Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-10
In This Chapter
This chapter provides introductory information about the following topics:
v Using the DB–Access user interface
v Alternative approaches
v Related manuals
If you are familiar with DB–Access, you might prefer to skip this introductory
chapter.
Using the DB-Access User Interface

The DB–Access user interface combines the following features:
v A hierarchy of menus
v Screens that prompt you for brief responses and choices from selection lists
v Contextual HELP screens
v The interactive Schema Editor, which helps you structure tables
v An SQL programmer environment, which includes the following features:
– The built-in SQL editor where you enter and modify SQL and SPL
statements
– An option to use another editor of your choice
– The database server syntax checker and runtime debugger
– Storage, retrieval, and execution of SQL and SPL routines

v A choice of output for database queries and reports
Nonprogrammers build their databases with the interactive Schema Editor

described in Chapter 5, “The Table Option,” on page 5-1 Programmers use the
SQL programmer environment described in Chapter 3, “The Query-language
Option,” on page 3-1.
The remainder of this chapter summarizes how to navigate through menus

and screens.
Using the Keyboard with DB-Access
The terminal keyboard has special keys that you use to instruct DB–Access.
Before you begin to use DB–Access, locate the following keys:
Arrows The arrow keys are usually found at the lower right of your
keyboard.
The up arrow [ ↑ ] key moves the cursor up one line. If your
terminal does not have a [ ↑ ] key, type CTRL-K.
The down arrow [ ↓ ] key moves the cursor down one line. If
your terminal does not have a [ ↓ ] key, type CTRL-J.
The left arrow [ ← ] key moves the cursor back one position on
the screen. If your terminal does not have a [ ← ] key, type
CTRL-H.
The right arrow [ → ] key moves the cursor forward one
position on the screen. If your terminal does not have a [ → ]
key, use the CTRL-L key.
BACKSPACE The BACKSPACE key is usually located at the top right of the
keyboard and might be marked with a left arrow.
CONTROL The CONTROL key is often labeled CTRL or CNTRL and is usually
located on the left side of the keyboard. This manual refers to
the CONTROL key as CTRL. On some systems, type CTRL-C to
cancel or abort a program or to leave a current menu and
return to the menu one level above. This manual refers to
CTRL-C as the Interrupt key.
DELETE The DELETE key is sometimes labeled RUBOUT, CANCEL, or DEL.
On most systems, use the DELETE key to cancel or abort a
program or to leave a current menu and return to the menu
one level above. This manual refers to the DELETE key as the
DEL key or the Interrupt key.
ESCAPE The ESCAPE key is usually located on the upper left corner of
your keyboard and might be labeled ESC.

RETURN The RETURN key is located on the right side of the keyboard. It
might be labeled RETURN or NEWLINE, or it might be indicated
with a bent left arrow.
SPACEBAR The SPACEBAR is generally unlabeled.
Interrupt Issue the command stty -a on the command line and check
the “intr” setting to see if your terminal recognizes CTRL-C or
DELETE (described earlier) or another key as the Interrupt key.
Using Menus
Menus appear at the top of each screen. Generally, the DB–Access main menu,
as Figure 2-1 shows, is the top level of the menu hierarchy. You can select a
submenu as the top level if you invoke DB–Access with a submenu option,
such as those described in “Executing a Command File” on page 1-17.
DBACCESS: Query-language Connection Database Table Session Exit

Use SQL Query Language.
------------------------------------------------Press CTRL-W for Help ---
Figure 2-1. The DB-Access Main Menu
A message below the option menu briefly describes the function of the
highlighted option. If you highlight another option, the description changes.
To find out what an option does, simply highlight it and read the description.
The dashed line at the bottom of the header displays the name of the current
database, if one is selected, and a reminder to press CTRL-W for Help.
Choosing a Menu Option

In Figure 2-1, the box, or highlight, indicates that Query-language is the
current option. Press RETURN to choose the highlighted option or choose
another option as follows:
v Use the SPACEBAR or the left and right arrow keys to move the highlight.
Options are arranged in a ring. If you move the highlight to the right, past
the end of the list, the first option becomes current again.
Press RETURN to choose the highlighted option.
v Type the shortcut letter shown in uppercase in the name of the option that
you want to select. (You can type the shortcut letter in either uppercase or
lowercase.)
Because some menus have multiple options that start with the same letter,
the shortcut key is not always the first letter of an option name. For
example, in Figure 2-2, the DATABASE menu, both Create and cLose start
with the letter “c.” As indicated by the uppercase shortcut keys, press the C
Chapter 2. Using the Full-Screen Menu Interface 2-3

key to select the Create option and press the L key to select the cLose
option.
DATABASE: Select Create Info Drop cLose Exit

Select a database to work with.
------------------------------------------------ Press CTRL-W for Help ---
Figure 2-2. A Sample Menu Screen
DB–Access displays the screen for the menu option that you select.
Leaving a Menu Screen

If the menu has an Exit option, press the E key to exit the menu. If no Exit
option exists, use the Interrupt key (usually DEL or CTRL-C) to exit the menu.
You then return to the menu, screen, or command line from which you
selected the exit menu.
Using Response Screens
Some screens, such as the SELECT DATABASE screen in Figure 2-3, prompt
you for a name or value.
SELECT DATABASE >>

Select a database with the Arrow keys, or enter a name, then press Return.
-------------------------------------------- Press CTRL-W for Help -----
mystores@dbserver1
sysmaster@dbserver1
Figure 2-3. A Sample Response Screen
If Global Language Support (GLS) is enabled, the listed items are sorted
according to the code-set collation order of the current locale.
Reading the Screen Header

The top line of a response screen displays a prompt as the screen name
followed by double angle brackets (>>) and the cursor. A message below the
prompt gives brief instructions, such as Press CTRL-W for Help. The dashed
line identifies the database that you select as current.
Selecting or Entering Values on the Screen

Where you enter your response depends on the operation, as the following
two choices show:

v You might type a value in the header of a entry screen, after the double
angle brackets (>>) at the top of the screen. For example, you could type a
valid database name after SELECT DATABASE>>, as Figure 2-3 shows.
Press RETURN when you finish typing, and DB–Access displays the next
screen or takes other appropriate action.
v Some screens have a list on the lower part of the screen from which you
can choose an item instead of typing your selection in the header. For
example, the SELECT DATABASE screen in Figure 2-3 lists the databases
available on the current database server.
Use the arrow keys to highlight the item that you want and then press
RETURN. DB–Access displays the next screen or takes other appropriate
action.
Leaving a Response Screen

Without a menu, a screen cannot have an Exit option. Press the Interrupt key
(usually DEL or CTRL-C) to return to the previous menu or screen.
Using the HELP Screen
Press CTRL-W to display a HELP screen that provides information about the
current menu option or screen function. Figure 2-4 shows some of the text that
appears when you press CTRL-W for information about the Run option on the
SQL menu.
HELP: Screen Resume

Displays the next page of Help text.
---------------------------------------------------------------------------
The Run option runs the current SQL statements and displays the
output on your terminal.
If there are errors:
If there are errors, an error message will appear on the

bottom of the screen and the Modify option will be highlighted.
Figure 2-4. A Partial HELP Screen with Text
If the Help text is longer than one page, the Screen option is highlighted.
Press RETURN to view the next screen. To select the Resume option, highlight it
with the SPACEBAR or the right arrow key and then press RETURN or the R key.
If the Help text is only one page, the Resume option is highlighted, and you
need only press RETURN.
For an illustration of how to read the syntax diagrams that appear when you
request online Help for creating, modifying, or editing an SQL statement, see
Appendix A, “How to Read Online Help for SQL Statements.”

Using an Editor Screen
You need to use a text editor to prepare and modify SQL statements and
command files when you select the Query-language option on the main
menu. DB–Access provides two ways to edit the text of SQL statements and
command files before you run them: the SQL editor and a system editor.
Because you only use these screens with the Query-language option, refer to
Chapter 3, “The Query-language Option,” on page 3-1 for detailed
instructions.
Alternative Approaches
This section illustrates some common database tasks and lists alternative
procedures to use them. This section can help you determine your preferred
method of using the DB–Access interface.
For each task listed in this section, the number in the last column indicates
which chapter to consult for detailed instructions. You also need the
companion documents listed in “Related Manuals” on page 2-10.
Database-Level Tasks
The database you use is called the current database. To select an existing
database as current, you can:
v Use the following command line syntax:
dbaccess databasename
For more information about using DB–Access from the command line, see
“Invoking DB-Access” on page 1-10.
v Use any method from within DB–Access that is described in the following
list.

Option or SQL
Menu Screen Statement Action Chapter
CONNECTION Connect Prompts for database server, 6
username, password, and
database name. Connects you
according to the choices you
make.
SQL CONNECT Connects to a database. If you 3
use a fully-qualified database
name, you simultaneously
connect to the database server.
DATABASE Select Chooses a current database 4
from a list of databases
available on the current
database server.
SQL DATABASE Chooses a database as current. 3
To create your own database, use one of the following methods.
Option or SQL
DATABASE Create Prompts for a database name, 4
dbspace, and log options and
then creates the new database.
SQL CREATE DATABASE Creates the database you 3
name in the statement with
the appropriate setup for the
keywords you use.
To drop a database, use one of the following methods.
Option or SQL
DATABASE Drop Lists available databases and 4
drops the database you
choose from the list.
SQL DROP DATABASE Drops the database named in 3
the command.

To close a database, use one of the following methods.
Option or SQL
DATABASE cLose Closes the current database. 4
SQL CLOSE DATABASE Closes the current database. 3
CONNECTION Disconnect Closes the current database 6
and disconnect from a
database server.
SQL DISCONNECT Closes the current database 3
CURRENT and disconnect from the
current database server.
To display information about a database, such as the dbspaces that contain it,
choose the Info option on the DATABASE menu.
Table-Level Tasks
To create a table, allocate storage, or apply fragmentation, use one of the
following methods.
Option or SQL
TABLE Create Uses the Schema Editor. 5
SQL CREATE TABLE Uses the SQL editor or system 3
editor and SQL statements.

To make changes to the structure of a table, use one of the following methods.
Option or SQL
TABLE Alter Guides you, with menus, 5
through the available choices
for changing an existing table.
CREATE TABLE Modify Enables you to change a 5
schema before you build the
table.
SQL ALTER TABLE Changes an existing table 3
according to the keywords
you include with the
statement.
SQL ALTER FRAGMENT v Changes an existing 3
fragmentation strategy (for
a table or index).
v Creates the table fragments.
To drop a table from a database, use one of the following methods.
Option or SQL
TABLE Drop Drops the current table. 5
SQL DROP TABLE Drops the table named in the 3
command.
To move a table from current database to another database, use one of the
following methods.
Option or SQL
TABLE Move Guides you, with menus, 5
through available choices for
moving a table from the
current database to another
database.
SQL Move Table Moves the table named in the 3
command.
To display information about the structure and characteristics of a table, use

one of the following methods.

Option or SQL
TABLE Info Provides a menu of available 5
table information categories.
SQL Info (option) Prompts you to select from a 3
list of available tables and
then provides the same menu
as the Info option on the
TABLE screen.
SQL INFO (SQL statement) Provides the information 3
specified in the keywords you
include with the INFO
statement.
To display the data stored in a table, run a SELECT statement from the SQL
editor. Use the procedures described in Chapter 3, “The Query-language
Option,” on page 3-1.
Tip: Several command files are included with DB–Access that contain sample
practice SELECT statements. Appendix B lists the demonstration files that
are supplied with the stores_demo database.
Related Manuals
As you work with any of the following IBM Informix companion documents,
you might find reasons to use DB–Access:
v The IBM Informix: Database Design and Implementation Guide introduces
database-definition fundamentals for a variety of objectives, strategies, and
Informix database servers.
v The IBM Informix: Guide to SQL Tutorial introduces data-manipulation
fundamentals and explains how to design SQL and other language
applications to select, combine, report, and alter data.
v The IBM Informix Client Software Developer's Kit contains programmer’s
guides for developers whose data resides on an Informix database server.
For more information on topics related to DB–Access see:

v The IBM Informix: Guide to SQL Syntax, which describes the syntax for all
SQL and SPL statements available through DB–Access.
v The IBM Informix: Guide to SQL Reference, which contains information about:
– The Informix system catalog and Information Schema views
– The structure and contents of the demonstration database
– Environment variables

v The IBM Informix: GLS User's Guide, which provides information on GLS
features.

Chapter 3. The Query-language Option
Overview of the SQL Menu . . . . . . . . . . . . . . . . . . . . . . . . .3-2
Using a System Editor . . . . . . . . . . . . . . . . . . . . . . . . . . .3-2
The Query-language Option . . . . . . . . . . . . . . . . . . . . . . . . .3-3
Entering a New SQL Statement . . . . . . . . . . . . . . . . . . . . . . . .3-4
Using the Editing Keys . . . . . . . . . . . . . . . . . . . . . . . . . .3-5
Editing Restrictions . . . . . . . . . . . . . . . . . . . . . . . . . . .3-6
Running an SQL Statement . . . . . . . . . . . . . . . . . . . . . . . . .3-7
Statements That the Run Option Supports . . . . . . . . . . . . . . . . . . .3-7
Viewing Successful Results . . . . . . . . . . . . . . . . . . . . . . . . 3-11
What Happens When Errors Occur . . . . . . . . . . . . . . . . . . . . . 3-12
Modifying an SQL Statement . . . . . . . . . . . . . . . . . . . . . . . . 3-12
Redirecting Query Results . . . . . . . . . . . . . . . . . . . . . . . . . 3-14
Sending Output to a Printer . . . . . . . . . . . . . . . . . . . . . . . . 3-15
Sending Output to a File . . . . . . . . . . . . . . . . . . . . . . . . . 3-15
The New-file Option . . . . . . . . . . . . . . . . . . . . . . . . . 3-15
The Append-file Option . . . . . . . . . . . . . . . . . . . . . . . . 3-15
Sending Output to a Pipe . . . . . . . . . . . . . . . . . . . . . . . . . 3-16
Choosing an Existing SQL Statement . . . . . . . . . . . . . . . . . . . . . . 3-16
Saving the Current SQL Statement . . . . . . . . . . . . . . . . . . . . . . . 3-18
Dropping an SQL Statement . . . . . . . . . . . . . . . . . . . . . . . . . 3-20
Support for SPL Routines . . . . . . . . . . . . . . . . . . . . . . . . . . 3-21
Related Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-24
In This Chapter
This chapter describes how to use the Query-language option on the
DB–Access main menu. When you select the Query-language option,
DB–Access displays the SQL menu. Use the various SQL menu options to
enter, modify, save, retrieve, and run SQL statements.
Use the Query-language option to:

v Learn SQL and SPL.
For example, use the Query-language option to practice the examples in the
IBM Informix: Guide to SQL Tutorial.
v Create and alter table structures as an alternative to the DB–Access Schema
Editor.
v Select, display, add, update, and delete data.
DB–Access has no menu options for data manipulation.
This chapter also describes support for SPL routines.

Overview of the SQL Menu
As you use the various SQL menu options, DB–Access retains the statements,
if any, in the editor. These statements are called the current statements.
Use the following steps to create and test SQL:

v To enter a new SQL statement or to enter multiple statements separated by
semicolons, use the New option.
If you prefer to enter or modify a query with an editor of your choice, use
the Use-editor option. This option provides an alternative to the New and
Modify options.
v To execute your statements, use the Run option.
When you choose the Run option, a message appears or the data retrieved
by a query appears with the number of rows retrieved.
To send the query results to an output file or printer instead of your
terminal, use the Output option.
v If a syntax error occurs when you run the query, or if you want to change
the query, use the Modify or Use-editor option to revise the text of the
query.
To reexecute the query, use the Run option.
v To save the text of the query in a command file, use the Save option.
To run or modify a query that you saved in a command file, use the
Choose option to select the file.
v To delete a command file in which you saved a query, use the Drop option.
Using a System Editor

When you want to enter or modify a long SQL statement or series of
statements, you might prefer the flexibility and familiarity of a system editor
to the SQL editor. Select the Use-editor option from the SQL menu to use the
system editor.
If you have not set the DBEDIT environment variable, you must select a text
editor to use for the session. If you select Use-editor, DB–Access prompts you
to accept or override the default system editor once each session, as Figure 3-1
shows.
USE-EDITOR >>vi
Enter editor name. (RETURN only for default editor)
--------------- mystores@dbserver1 ------------ Press CTRL-W for Help -----
Figure 3-1. Sample System Editor Screen for Entering and Modifying SQL Statements

The default editor that DB–Access displays depends on the preference that
you establish for your operating system:
UNIX Only
v Common UNIX system editors are vi and ex.

End of UNIX Only
Windows Only
v If you use a text editor as the system default, you must save the .sql files as
text.
End of Windows Only
Press RETURN to select the default editor you named after the USE-EDITOR
prompt. To use a different editor, type the name of that editor and press
RETURN.
The Query-language Option

To select SQL, choose the Query-language option from the main menu. Press
the Q key or highlight the Query-language option and press RETURN. The
Query-language option displays the SQL menu, as Figure 3-2 shows.
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Enter new SQL statements using the SQL editor.
------------------------------------------------Press CTRL-W for Help -----
Figure 3-2. The SQL Menu
If you select the Query-language option on the main menu and have not
selected a database, the SELECT DATABASE screen appears. Specify a
database at the prompt or press the Interrupt key to display the SQL menu.
From the SQL menu, create or choose and run an SQL statement to specify the
current database.
Important: In the SELECT DATABASE screen, the names of databases are

limited to 18 characters. If a database name is longer than 18
characters, you will see the first 17 characters of the name
followed by a ‘+’ sign. Enter a ‘+’ sign to display the complete
long name in vi. To exit from vi, press ESC ZZ.
Chapter 3. The Query-language Option 3-3

The SQL menu has the following options.
Option Purpose Instructions

New Clears current statements and “Entering a New SQL
positions cursor in SQL editor. Statement” on page 3-4
Run Executes current SQL statements. “Running an SQL Statement”
on page 3-7
Modify Allows you to modify current SQL “Modifying an SQL Statement”
statements in SQL editor. on page 3-12
Use-editor Invokes a system editor so that you “Using a System Editor” on
can modify current statements or page 3-2
create new statements. Use-editor is
interchangeable with New and
Modify.
Output Redirects Run-option output to a file, “Redirecting Query Results” on
printer, or system pipe. page 3-14
Choose Lists SQL command files so that you “Choosing an Existing SQL
can choose a file to execute or modify. Statement” on page 3-16
Save Saves current SQL statements in a file “Saving the Current SQL
for later use. Statement” on page 3-18
Info Displays table information, such as “Displaying Table Information”
columns, indexes, privileges, on page 3-19
constraints, triggers, status, and
fragmentation strategy.
Drop Deletes a specified SQL command file. “Dropping an SQL Statement”
on page 3-20
Exit Returns to main menu. none
Entering a New SQL Statement

To enter an SQL statement, perform one of the following actions:
v Select the New option on the SQL menu. The NEW screen appears and
indicates that you are using the SQL editor, as Figure 3-3 shows.
NEW: ESC = Done editing CTRL-A = Typeover/Insert CTRL-R = Redraw

CTRL-X = Delete character CTRL-D = Delete rest of line
-------------- mystores@dbserver1 ----------- Press CTRL-W for Help -----

Figure 3-3. The NEW Screen for Entering New SQL Statements

v If you prefer to work with a different editor from the one that is installed
on your system, select the Use-editor option. This option is described in
“Using a System Editor” on page 3-2.
As Figure 3-3 shows, the NEW screen starts with the cursor positioned below
the header, which indicates where you enter text on the screen. Use the editor
to enter statements and edit them before you run them. To string several SQL
statements together, separate them with a semicolon.
On Extended Parallel Server, the DB-Access menu appears slightly different. It

includes the CTRL-B and CTRL-F options which are used to scroll through the
command history.
Using the Editing Keys

The editing keys listed at the top of the SQL editor screen perform the
following special functions when you enter SQL statements:
CTRL-A switches between insert mode and typeover mode. You are
automatically in typeover mode when you begin to use the
SQL editor.
v In insert mode, the text beneath the cursor shifts to the
right as you enter new characters.
v In typeover mode, characters you enter replace the text
beneath the cursor.
CTRL-D deletes characters from the current cursor position through the
end of the line.
CTRL-R redraws the current screen. Use it when you receive an
electronic message or some other interference that makes it
difficult to read the SQL statement text that you enter.
CTRL-X deletes a character beneath the cursor.
ESC returns you to the SQL menu when you finish entering or
editing the SQL statement. You can then run or modify the
statement or select the system editor for more extensive
editing.
CTRL-F scrolls forward through the command history list. (XPS)
CTRL-B scrolls backward through the command history list. (XPS)
For more information on arrow and cursor-positioning keys, see “Using the
Keyboard with DB-Access” on page 2-2.

Editing Restrictions
The SQL editor does not display more than 80 characters on a line and does
not wrap lines, as the following two such restrictions show:
v If you choose an existing command file in which the characters in a line
extend beyond the 80th column, DB–Access displays a percent sign (%) in
the 80th column to indicate an overflow. You cannot see all the characters
beyond the percent sign, but the statement should run correctly.
v If you type characters in a new command file so that a line extends beyond
the 80th column, DB–Access overwrites all the characters in the 80th
column. You cannot see the overflow, and the statement does not run
correctly.
To make the full text appear on the screen, press RETURN at a logical place in
the first 80 characters of each line. If you need to type a quoted character
string that exceeds 80 characters, such as an insert into a long CHAR column,
use a system editor instead of the SQL editor.
If you want to include comments in the text:

v Use double minus signs for ANSI-compliant databases.
v Preface each comment line with a double minus sign (--) comment
indicator. The comment indicator spans the entire line.
v Use braces ({ }) for databases that are not ANSI-compliant. Enclose the
entire comment indicator between the braces.
When you use the SQL editor, you can type as many lines of text as you need.
You are not limited by the size of the screen, although you might be limited
by the memory constraints of your system or the maximum SQL statement
size of 64 kilobytes.
If you insert more lines than one screen can hold, the SQL editor scrolls down
the page with the additional text. The beginning and ending line numbers of
the current page are displayed on the fourth line of the text-entry screen, as
Figure 3-4 shows.
NEW: ESC = Done editing CTRL-A = Typeover/Insert CTRL-R = Redraw

CTRL-X = Delete character CTRL-D = Delete rest of line
-- 3 to 20 of 20 ---- mystores@dbserver1 ------- Press CTRL-W for Help ----
Figure 3-4. SQL Statement Text-Entry Screen with Scrolling
When you finish entering a new SQL statement or statements, press ESC to
return to the SQL menu.

Warning: If you do not use the Save option to save your typed statements,
they will be deleted the next time you select an option that clears
the SQL editor (such as New or Choose).
Running an SQL Statement

After you exit the editor screen, the SQL menu reappears with the Run option
highlighted and the statement text displayed in the bottom of the screen, as
Figure 3-5 shows.
Run the current SQL statements.
--------------- mystores@dbserver1 ----------- Press CTRL-W for Help -----
CREATE TABLE mystock

(
stock_num SMALLINT,
manu_code CHAR(3),
description CHAR(15),
unit_price MONEY(6),
unit CHAR(4),
unit_descr CHAR(15),
PRIMARY KEY (stock_num, manu_code) CONSTRAINT stock_man_primary,
FOREIGN KEY (manu_code) REFERENCES manufact
)
Figure 3-5. The SQL Menu with SQL Statement Text Ready to Run
Press RETURN or the R key to select the Run option. DB–Access first checks
each statement to ensure that it conforms to the SQL syntax and usage rules.
If your statements contain no syntax mistakes, DB–Access processes them.
Tip: You can check your SQL statements for ANSI compatibility if you set the
DBANSIWARN environment variable or invoke DB–Access with the
-ansi option.
Statements That the Run Option Supports
Table 3-1 lists the statements that you can execute with the Run option.
Table 3-1. SQL Statements for Run Option
Options
SQL Statement XPS IDS
ALTER ACCESS_METHOD x
ALTER FRAGMENT x x
ALTER FUNCTION x
ALTER INDEX x
ALTER PROCEDURE x
ALTER ROUTINE x

Table 3-1. SQL Statements for Run Option (continued)
Options
ALTER SEQUENCE x
ALTER TABLE x x
BEGIN WORK x x
CLOSE DATABASE x x
COMMIT WORK x x
CONNECT x x
CREATE ACCESS_METHOD x
CREATE AGGREGATE x
CREATE CAST x
CREATE DATABASE x x
CREATE DISTINCT TYPE x
CREATE FRAGMENT x x
CREATE INDEX x x
CREATE OPAQUE TYPE x
CREATE OPCLASS x
CREATE FUNCTION x
CREATE PROCEDURE x x
CREATE ROLE x x
CREATE ROW TYPE x
CREATE SCHEMA x x
CREATE SEQUENCE x
CREATE SYNONYM x x
CREATE TABLE x x
CREATE TRIGGER x
CREATE VIEW x x
DATABASE x x
DELETE x x
DISCONNECT x x
DROP AGGREGATE x
DROP CAST x
DROP DATABASE x x

Options
DROP DISTINCT TYPE x
DROP INDEX x x
DROP OPAQUE TYPE x
DROP OPCLASS x
DROP FUNCTION x
DROP PROCEDURE x x
DROP ROLE x x
DROP ROW TYPE x
DROP SEQUENCE x
DROP SYNONYM x x
DROP TABLE x x
DROP TRIGGER x
DROP VIEW x x
EXECUTE PROCEDURE x x
EXECUTE FUNCTION x
GRANT x x
GRANT FRAGMENT x
INFO x x
INSERT x x
LOAD x x
LOCK TABLE x x
MERGE x
MOVE TABLE x
OUTPUT x x
RENAME COLUMN x x
RENAME DATABASE x x
RENAME SEQUENCE x
RENAME TABLE x x
REVOKE x x
REVOKE FRAGMENT x
ROLLBACK WORK x x

Options
SAVE EXTERNAL DIRECTIVES x
SELECT x x
SET x x
SET COLLATION x
SET DATASKIP x x
SET DEBUG FILE TO x x
SET ENCRYPTION PASSWORD x
SET ENVIRONMENT x x
SET EXPLAIN x x
SET ISOLATION x x
SET LOCK MODE x x
SET LOG x x
SET OPTIMIZATION x x
SET PDQPRIORITY x x
SET OPTIMIZATION x x
SET ROLE x x
SET SESSION AUTHORIZATION x x
SET STATEMENT CACHE x
SET TRANSACTION x x
START VIOLATIONS TABLE x x
STOP VIOLATIONS TABLE x x
UNLOAD x x
UNLOCK TABLE x x
UPDATE x x
UPDATE STATISTICS x x
For information about additional statements for Optical Subsystem, see the
IBM Informix: Optical Subsystem Guide.
Tip: To execute statements that are not listed, use the SQL menu options New
(or Use-editor) and Save to enter and save them, and then run the saved
file from the command line.

Viewing Successful Results
If a statement other than a query completes successfully, a message appears at
the bottom of the screen. For the statement that Figure 3-5 on page 3-7 shows,
the following message appears:
Table created.
If you use the Run option with a SELECT statement and that SELECT
statement runs correctly, DB–Access displays the requested results below the
header. If your query retrieves more rows than can fit on a single screen, the
results screen has a menu at the top, as Figure 3-6 shows.
RUN: Next Restart Exit

Display the next page of query results.
------------------ mystores@dbserver1 -------------- Press CTRL-W for Help ------
customer_num 106
call_dtime 1997-06-12 08:20
user_id maryj
call_code D
call_descr Order was received, but two of the cans of ANZ tennis balls within
the case were empty
res_dtime 1997-06-12 08:25
res_descr Authorized credit for two cans to customer, issued apology. Called
ANZ buyer to report the QA problem.
customer_num 110
call_dtime 1997-07-07 10:24
user_id richc
call_code L
call_descr Order placed one month ago (6/7) not received.
res_dtime 1997-07-07 10:30
res_descr Checked with shipping (Ed Smith). Order sent yesterday- we were
waiting for goods from ANZ. Next time will call with delay if
necessary.
Figure 3-6. The Menu for Displaying Additional Query Results
Note: When running a query that returns more than one screen of data, it is
important to note that a cursor is still open and its corresponding locks
are still held until all data is returned.
Advance through the output as follows:

v With the Next option highlighted, press RETURN to advance the display
through the selected rows. The last screen of data has a message at the
bottom that indicates the number of rows that the query returned.
v Select Restart to return to the first screen of query output.
v Select Exit to exit the output display and return to the SQL menu. If you
exit before you reach the last output screen, a message at the bottom of the
screen indicates that you interrupted the query.

What Happens When Errors Occur
If you make any syntax or typing mistakes in an SQL statement, DB–Access
does not process the statement. Instead, it continues to display the text of the
statement with a message that describes the error. For example, if a syntax
error occurs, the following message appears at the bottom of the screen:
201: A syntax error has occurred.
If an execution or runtime error occurs, DB–Access continues to process the

statement and returns an error message. For example, if you try to create a
table that was already created, the following message appears at the bottom of
the screen:
310: Table (mavis.mystock) already exists in database.
If you try to execute a statement that contains more than one SQL statement,
you might not see the error message immediately. If, for example, the first
statement is a SELECT statement that runs correctly and the next statement
contains a typing error, the data that the first statement retrieved appears on
the screen before the error message appears for the second statement.
When DB–Access detects an error, it gives you an opportunity to edit the

statement that caused the error. Processing stops when the Modify option on
the SQL menu is highlighted. Select one of the following methods to correct
the statement:
v Press RETURN to choose Modify, which returns you to the SQL editor.
v Select the Use-editor option to use the default editor of your choice.
Modifying an SQL Statement

When DB–Access finds an error in an SQL statement that you are trying to
run, the Modify option is highlighted on the SQL menu, and the current
statement text and error message are displayed, as Figure 3-7 shows.

Modify the current SQL statements using the SQL editor.
------------------ mystores@dbserver1 -------------- Press CTRL-W for Help ------

(
stock_num SMALLINT,
manu_code CHAR(3),
description CHAR(15)
unit CHAR(4),
)
201: A syntax error has occurred.
Figure 3-7. The SQL Menu with SQL Statement Text to Be Modified
If you press RETURN, DB–Access calls the SQL editor and positions the cursor
on the line with the first error. You can correct the error with the SQL editor,
or you can press ESC to exit to the SQL menu and select the Use-editor option
to edit the statement with your system editor. To exit, perform one of the
following steps:
v If you use the SQL editor to make changes, press ESC when you finish
editing the statement.
v If you use the system editor to make changes, exit the file according to the
convention for that editor.
You then return to the SQL menu, where you can press RETURN to run the
statement again.
If the SQL statement is new, the screen is blank. If you want to change or call
up this statement with the Choose option, the text of the SQL statement
appears on the screen.
If an error occurs while you run an SQL statement, the edit screen contains
the error message with a pointer to the likely cause. Figure 3-8 shows how an
editor screen might look after a syntax error. The editor used in this example
displays the name of the temporary file assigned to the SQL statement.

(
stock_num SMALLINT,
manu_code CHAR(3),
description CHAR(15)
^
#
#201:A syntax error has occurred
#
unit CHAR(4),
)
“tmp/rsqa02775.err” 11 lines, 132 characters
Figure 3-8. A Temporary Text-Editing File with SQL Statement Text to Be Corrected
Make your corrections to the text. When you finish entering or modifying
your SQL statement or statements, exit the editor as you normally do. The
SQL menu reappears with the Run option highlighted. The statement text
appears in the bottom half of the screen.
Press RETURN to run the statement or select another menu option to save the
statement in a command file or direct its output.
Redirecting Query Results

The output from a SELECT statement normally appears on the screen. You
can, instead, use the Output option on the SQL menu to route query results to
the printer, store them in a system file, or pipe them to a program. This
option has the same purpose as the SQL statement OUTPUT.
The SELECT statement must be on the screen as the current statement. Then
you can select the Output option from the SQL menu, which displays the
OUTPUT menu, as Figure 3-9 shows.
OUTPUT: Printer New-file Append-file To-pipe Exit

Send query results to a printer.
---------------- mystores@dbserver1 ---------- Press CTRL-W for Help -----
SELECT * FROM customer
Figure 3-9. The OUTPUT Menu for Redirecting Query Results
To return to the SQL menu without redirecting query results from an

OUTPUT screen, press the Interrupt key.

Sending Output to a Printer
To send your query results directly to a printer, select the Printer option from
the OUTPUT menu. DB–Access sends the results to your default printer and
displays a message on the bottom of the screen that indicates how many rows
were retrieved. The query results do not appear on the screen. You can set the
DBPRINT environment variable to specify a default printer.
Sending Output to a File
You can write query results to a new file or append the results to an existing
file. If you do not specify a path when DB–Access prompts you for a
filename, the file will be stored in the directory that you were in when you
invoked DB–Access.
The New-file Option

To create a new file to store query results, select the New-file option on the
OUTPUT menu. The OUTPUT NEW-FILE screen appears, as Figure 3-10
shows.
OUTPUT NEW-FILE >>

Enter the name you want to assign to the new file, then press Return.
---------------- mystores@dbserver1 ----------- Press CTRL-W for Help ------
Figure 3-10. The OUTPUT NEW-FILE Screen
Type a name for the file and press RETURN. DB–Access forwards the results of
the query to that file and displays a message that indicates how many rows
were retrieved. The query results do not appear on the screen.
Warning: If you enter the name of an existing file, this procedure overwrites
the existing file with the query results.
The Append-file Option

To add your query results to the end of an existing file without replacing its
contents, select the Append-file option on the OUTPUT menu. The OUTPUT
APPEND-FILE screen appears, as Figure 3-11 shows.
OUTPUT APPEND-FILE >>

Enter the name of the file you want to append results to, then press Return.
---------------- mystores@dbserver1 ------------- Press CTRL-W for Help ------
Figure 3-11. The OUTPUT APPEND-FILE Screen

Type the name of an existing file where you want to append the query results
and press RETURN. DB–Access appends the query results to the end of that file
and displays a message that indicates how many rows were retrieved. The
query results do not appear on the screen.
Sending Output to a Pipe
If you want to send query results to a pipe, select the To-pipe option on the
OUTPUT menu. The OUTPUT TO-PIPE screen appears, as Figure 3-12 shows.
OUTPUT TO-PIPE >>

Enter the name of the pipe you want to send results to, then press Return.
----------------- mystores@dbserver1 ------------- Press CTRL-W for Help -----
Figure 3-12. The OUTPUT TO-PIPE Screen
Specify a target program, such as more, through which to pipe output.

DB–Access sends the results to that pipe.
UNIX Only
On a UNIX operating system, you must have permission to run the target
program.
End of UNIX Only
Choosing an Existing SQL Statement

When you save SQL statements in a command file, as described in “Saving
the Current SQL Statement” on page 3-18, you can retrieve the command file
and run or edit the SQL statements at any time.
Select the Choose option on the SQL menu to display the CHOOSE screen
with a list of the command files that you can access. These files have the
extension .sql, although the extension is not shown. For example, Figure 3-13
on page 3-17 lists the command files included in the demonstration database.

CHOOSE >>
Choose a command file with the Arrow Keys, or enter a name, then press Return.
---------------- mystores@dbserver1 ----------- Press CTRL-W for Help -------
alt_cat c_state d_trig sel_ojoin1
c_calls c_stock d_view sel_ojoin2
c_cat c_stores del_stock sel_ojoin3
c_custom c_table ins_table sel_ojoin4
c_index c_trig opt_disk sel_order
c_items c_type sel_agg sel_sub
c_manuf c_view1 sel_all sel_union
c_orders c_view2 sel_group upd_table
c_proc d_proc sel_join
Figure 3-13. The CHOOSE Screen Listing Current .sql Files
If no current database exists, the list includes all the command files located in
the current directory and in any directories that the DBPATH environment
variable specifies.
Important: This list includes only those filenames that have the .sql
extension. If you create a new SQL file outside of DB–Access and
save it without the .sql extension, it will not appear in the list of
files to choose. Add the .sql extension to the filename and then
select Choose again.
Note: DB–Access can only recognize files that are stored in the directory from
which you started DB–Access. If the Choose command results in an
empty list, and you know you have command files, exit DB–Access,
change directories to the directory that contains your .sql files, and
restart DB–Access.
To select a command file, use the arrow keys to highlight its name or enter
the name of the file at the prompt.
When the SQL menu reappears, it displays the command file statements on
the screen as the current statements. To modify, run, edit, or output these
statements, choose the appropriate menu option.
To leave the CHOOSE screen without selecting a command file, press the
Interrupt key, which returns you to the SQL menu.

Saving the Current SQL Statement
You can save SQL statements in a file for later use, such as to invoke the
statements from the command line (see “Executing a Command File” on page
1-17) or retrieve the saved statements with the Choose option on the SQL
menu.
To save the current SQL statement or statements in a file, select the Save
option on the SQL menu. The SAVE screen appears and prompts you to enter
a name for the command file, as Figure 3-14 shows.
SAVE >>
Enter the name you want to assign to the command file.
-------------------mystores@dbserver1 --------- Press CTRL-W for Help -----
select max (ship_charge), min (ship_charge)

from orders;
Figure 3-14. The SAVE Screen for Saving Statements in a File
You assign the left portion of the filename. Use 1 to 10 characters. Start with a
letter, then use any combination of letters, numbers, and underscores (_). Press
RETURN to save the file.
UNIX Only
You can use uppercase and lowercase letters in the name. However, remember
that UNIX operating systems are case sensitive. The file orders is not the same
as Orders or ORDERS.
End of UNIX Only
DB–Access appends the extension .sql to the name that you assign when it
stores the statements in a file. For example, if you name your file cust1,
DB–Access stores the file with the name cust1.sql. The CHOOSE screen still
lists cust1, but the operating system identifies the same file as cust1.sql if you
list the directory files from the command line.
To leave the SAVE screen without assigning a name to a command file, press
the Interrupt key, and you return to the SQL menu.

Displaying Table Information
Use the Info option on the SQL menu to display information about the
columns, indexes, access privileges, reference privileges, constraints
(referential, primary-key, check, unique, and defaults), triggers, status, and
fragmentation strategy of a table. The Info option has the same purpose as the
SQL statement INFO.
The INFO menu displays the following options.

Columns Lists the columns in the specified “Displaying Column
table, shows the data type, and shows Information” on page 5-36
whether null values are allowed
Indexes Lists the name, owner, and type “Displaying Index
(unique or duplicate) of each index Information” on page 5-39
for a specified table. (The display also
shows if the index is clustered, the
access method used, and the names of
the columns that are indexed.)
Privileges Lists the users who have table-level “Displaying Table-Level
Select, Update, Insert, Delete, Index, Privileges” on page 5-40
and Alter privileges
References Lists the users who have the “Displaying References
table-level References privilege for the Privileges” on page 5-40
specified table and which columns
they can reference
Status Lists the table name, the table owner, none
the size of the row (in number of
bytes), the number of rows in the
table (as of the last UPDATE
STATISTICS statement), the number
of columns in a row, and the date the
table was created
cOnstraints Displays referential, primary-key, “Displaying Column
check, and unique constraints and Constraints and Defaults” on
default values for the columns in the page 5-41
specified table
triGgers Displays header and body “Displaying Triggers” on
information for a specified trigger page 5-43
Table Redisplays the INFO FOR TABLE none
menu so that you can select a new
table to request information on the
INFO menu

Fragments Displays fragmentation strategy for “Dropping a Table” on page
the selected table 5-45
Exit Returns to the SQL menu none
The Table option on the main menu displays a TABLE menu, which in turn
has an Info option. The Info option screens are the same for both the SQL
menu and TABLE menu. For more information on the Info option, see
“Displaying Table Information” on page 5-34.
To leave the INFO FOR TABLE screen without requesting table information,
press the Interrupt key.
Dropping an SQL Statement

Your current database directory stores files that contain SQL statements. You
might have installed some of these files with the demonstration database. You
might have created other files and put them in the directory with the Save
option on the SQL menu. Additional files might have become available when
you installed a language supplement with DB–Access.
To remove command files from the current database directory, select the Drop
option on the SQL menu. The DROP COMMAND FILE screen appears with
an alphabetical list of command files in the current database, as Figure 3-15
shows.
DROP COMMAND FILE >>

Enter the name of the sql command file you wish to drop from the database.
------------- stores_demo@dbserver1 ----------- Press CTRL-W for Help -----
Figure 3-15. The DROP COMMAND FILE Screen with Sample Files

If GLS is enabled, the order in which DB–Access lists the names of command
files might vary, depending on the locale you use.
Type the name of the command file that you want to drop or highlight it with
the arrow keys and press RETURN. A special menu appears that asks for
confirmation before it drops the command file as Figure 3-16 shows.
CONFIRM: No Yes

No, I do not want to drop it.
----------------- stores_demo@dbserver1 ----------- Press CTRL-W for Help -----
Figure 3-16. The DROP COMMAND FILE Confirmation Menu
The default is No to help prevent you from deleting a command file by

mistake. To drop the highlighted command file, press the Y key or use the
right arrow key to highlight Yes and press RETURN. DB–Access drops the
command file and returns you to the SQL menu.
To leave the DROP COMMAND FILE screen without dropping a command

file, press the N key, RETURN, or the Interrupt key. You then return to the SQL
menu.
Support for SPL Routines

You can create and execute routines written in SPL from the SQL menu.
You can store the SPL routine in a separate command file and then call it from
an application or execute it as a stand-alone program. After you create the
SPL routine, you can execute it within DB–Access with the appropriate SQL
statement. The following example details the steps.

To create and execute an SQL routine:
1. To create the text of the routine, type directly in the NEW screen or the
Use-editor screen. Enter the SPL and SQL statements for your routine in
the statement block of a CREATE PROCEDURE statement.
Dynamic Server
If you use Dynamic Server, use the CREATE FUNCTION statement if the
routine returns values.
For more information on the CREATE FUNCTION statement, see the
IBM Informix: Guide to SQL Syntax.
2. Use the Run option to create the routine and register it in the
sysprocedures system catalog table.
3. Use the NEW screen to enter an EXECUTE PROCEDURE statement that
names the routine that you want to run.
Dynamic Server
If you use Dynamic Server and created your routine with the CREATE
FUNCTION statement, enter an EXECUTE FUNCTION statement to run
the function.
4. Use the Run option to execute the routine and display the results.
Figure 3-17 shows the text of the routine in the c_proc.sql command file,
which is supplied with the demonstration database. To try this routine, use
the Choose option and then select c_proc.
Dynamic Server
If you use Dynamic Server, change the word procedure in c_proc.sql to function
because the routine returns a value.
To register the routine in the database, select the Run option, as Figure 3-17
shows.

----------------------- mydata@mynewdb ------- Press CTRL-W for Help --------
create procedure read_address (lastname char(15))
returning char(15), char(15), char(20), char(15),char(2), char(5);

define p_fname, p_city char(15);
define p_add char(20);
define p_state char(2);
define p_zip char(5);
select fname, address1, city, state, zipcode
into p_fname, p_add, p_city, p_state, p_zip
from customer
where lname = lastname;
return p_fname, lastname, p_add, p_city, p_state, p_zip;
end procedure;
Figure 3-17. Displaying the Text of an SPL Routine on the SQL Menu
DB–Access displays a message to indicate that the database server created the
routine. To execute the routine, select New from the SQL menu and then enter
the appropriate EXECUTE statement. In the following example, the user
requests the address of a customer whose last name is Pauli:
EXECUTE PROCEDURE read_address ("Pauli")
Dynamic Server
If you use Dynamic Server, enter:

EXECUTE FUNCTION read_address("Pauli")
After you enter the EXECUTE PROCEDURE or EXECUTE FUNCTION

statement on the NEW screen, press Esc to return to the SQL menu. Select
Run from the SQL menu to execute the routine. Figure 3-18 shows the result
of executing the routine.

----------------------- mydata@mynewdb ------- Press CTRL-W for Help --------
Ludwig
Pauli
213 Erstwild Court
Sunnyvale
CA
94086
Figure 3-18. Result of Executing an SPL Routine on the SQL Menu
Tip: SPL routines are stored in the system catalog tables in executable format.
Use the Routines option on the DATABASE INFO menu to display a list
of the routines in the current database or to display the text of a
specified routine.
Related Manuals
The following companion manuals provide details for the Informix
implementation of SQL statements and SPL programming logic:
v For tutorial information on SQL statements and SPL routines, see the
IBM Informix: Guide to SQL Tutorial.
v For reference information on the syntax and usage of SQL and SPL
statements, see the IBM Informix: Guide to SQL Syntax.
v For reference information on SQL system catalog tables, data types, and
environment variables, see the IBM Informix: Guide to SQL Reference.
Dynamic Server
v For additional information on extended data types and user-defined

routines, see IBM Informix: User-Defined Routines and Data Types Developer's
Guide.

Chapter 4. The Database Option
Selecting a Database Menu Option . . . . . . . . . . . . . . . . . . . . . . . 4-2
Selecting a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-2
List of Available Databases. . . . . . . . . . . . . . . . . . . . . . . . . 4-3
Specifying a Database . . . . . . . . . . . . . . . . . . . . . . . . . . 4-3
Creating a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-4
Specifying a Dbspace . . . . . . . . . . . . . . . . . . . . . . . . . . 4-5
Specifying Logging . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-5
Exiting the CREATE DATABASE Menu . . . . . . . . . . . . . . . . . . . . 4-5
Displaying Database Information . . . . . . . . . . . . . . . . . . . . . . . 4-6
Retrieving Information on Dbspaces . . . . . . . . . . . . . . . . . . . . . 4-7
Retrieving Nondefault Locale Information . . . . . . . . . . . . . . . . . . . 4-8
Retrieving Information on Routines . . . . . . . . . . . . . . . . . . . . . . 4-9
Selecting a Different Database . . . . . . . . . . . . . . . . . . . . . . . 4-10
Deleting a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-10
The DROP DATABASE Screen . . . . . . . . . . . . . . . . . . . . . . . 4-11
Confirming Your Decision to Delete a Database . . . . . . . . . . . . . . . . . 4-11
Closing a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-12
Related Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-13
In This Chapter
This chapter describes how to use the Database option. To perform any of the
following actions, select the Database option from the main menu.
v Create a database or select a database.
The database you work with is called the current database.
v Retrieve and display information about a database, such as available
dbspaces and the text of routines.
v Delete an existing database or close the current database.
v Commit or rollback transactions.
You can only access databases that reside on the current database server. To
select a database server as current, you can specify a database server when
you invoke DB–Access, you can use the Connection menu, or you can run a
CONNECT statement from the SQL menu. If you do not explicitly select a
database server, DB–Access uses the default database server that the
$INFORMIXSERVER environment variable specifies as the current database.

Selecting a Database Menu Option
Figure 4-1 shows the DATABASE menu. To reach the DATABASE menu from
the main menu, press the D key or highlight the Database option and press
RETURN.
DATABASE: Select Create Info Drop cLose Exit

Select a database to work with.
------------------------------------------------- Press CTRL-W for Help -----
Figure 4-1. The DATABASE Menu
The DATABASE menu displays the following options.

Select Makes a database the current database Figure 4-5 on page 4-5
Create Builds a new database and makes that “Creating a Database” on page 4-4
database the current database
Info Displays information about the “Displaying Database Information”
current database on page 4-6
Drop Removes a database from the system “Deleting a Database” on page
4-10
cLose Closes the current database “Closing a Database” on page 4-12
Exit Exits the DATABASE menu and none
returns you to the main menu
If you select or create a database when another database is already open,

DB–Access closes that database before it makes your selection the current or
new database. Figure 4-17 on page 4-12 shows the TRANSACTION menu that
appears if you attempt to open a new database without first terminating a
transaction.
If you enter the name of a nonexistent database or a database that DB–Access

cannot locate, DB–Access displays an error message.
Selecting a Database
To work with an existing database, choose the Select option from the
DATABASE menu. The SELECT DATABASE screen appears, as Figure 4-2
shows.

SELECT DATABASE >>
Select a database with the Arrow Keys, or enter a name, then press Return.
-------------------------------------------- Press CTRL-W for Help --------

demodb@dbserver1
mydata@dbserver1
productn@factory
Figure 4-2. The SELECT DATABASE Screen
The SELECT DATABASE screen also appears whenever you need to specify a
database, such as when you choose the Table or Query-language option
without specifying a database on the DB–Access command line.

followed by a ‘+’ sign. Enter a ‘+’ sign to display the complete
To leave the SELECT DATABASE screen and return to the DATABASE menu
without selecting a database, press the Interrupt key.
List of Available Databases
When the SELECT DATABASE screen appears, the first database in the list of
available databases is highlighted, accompanied by the names of database
servers. The list is organized alphabetically by database server and then by
database for each database server. You can display a maximum of 512
database names on the SELECT DATABASE screen.
The list of available databases that is displayed depends on the settings of

certain environment variables. For example:
v DB–Access displays the names of all databases on the current database
server and in your DBPATH setting.
v If you use multiple database servers, the ONCONFIG environment variable
determines the current database server.
The list of available databases displayed also depends on the current

connection. For example:
v If no current connection exists or the current connection is an implicit
default connection, all databases in the DBPATH appear.
v If a current explicit connection exists, all databases in the DBPATH setting
that pertain to the current database server appear.
Specifying a Database
You can select a database name on the SELECT DATABASE screen in any of
the following ways:
Chapter 4. The Database Option 4-3

v To select the first database on the list, which is already highlighted, press
RETURN.
v Use the arrow keys to highlight the name of the database and press RETURN.
v Type the database name and press RETURN.
For example, to select the demodb database on the current database server,
type demodb or highlight demodb@dbserver1 and press RETURN.
To specify a database on another database server, include the database server

with the database name. For example, to select the productn database on the
factory database server, type the name or highlight productn@factory and
press RETURN.
The name of the database that you select appears on the dashed line below
the screen header.
Creating a Database
To create a new database instead of selecting an existing one, select the Create
option from the DATABASE menu. The CREATE DATABASE screen appears,
as Figure 4-3 shows.
CREATE DATABASE >>

Enter the name you want to assign to the new database, then press Return.
------------------------------------------- Press CTRL-W for Help --------
Figure 4-3. The CREATE DATABASE Screen
Enter a name for the database that you want to create and press RETURN.
Note: You can assign any name to your database, as long as you follow the
syntax guidelines described in the IBM Informix: Guide to SQL Syntax.
To create a database on another database server, specify the server name with
the database name. Follow the syntax guidelines described in the
After you name the new database, the CREATE DATABASE menu appears as
Figure 4-4 shows.
CREATE DATABASE : Dbspace Log Exit

Select a dbspace for storing the database’s data.
------------------------------------------- Press CTRL-W for Help -------
Figure 4-4. The CREATE DATABASE Menu

You can specify that a database be stored in a nonroot dbspace or create a
database with or without buffered transaction logging or create an
ANSI-compliant database.
Specifying a Dbspace
Select the Dbspace option and the SELECT DBSPACE screen appears, as
Figure 4-5 shows.
SELECT DBSPACE >>

Select a dbspace with the Arrow Keys, or enter a name, then press Return.
--------------------------------------------- Press CTRL-W for Help -------

rootdbs
pers_dbs
empl_dbs
Figure 4-5. The SELECT DBSPACE Screen
From the list of dbspaces, select an available dbspace in which to store

database data. The default is rootdbs or a dbspace that you create as the
default. You then return to the CREATE DATABASE menu.
Specifying Logging
To specify the type of transaction logging, select the Log option. The LOG
menu appears, as Figure 4-6 shows.
LOG : None Log Buffered_log log_Mode_ansi Exit

Do not activate transaction logging.
-------------------------------------------- Press CTRL-W for Help -------
Figure 4-6. The LOG Menu
The LOG menu displays the following options.

Option Purpose
None Default, does not activate transaction logging.
Log Specifies unbuffered transaction logging.
Buffered_log Specifies buffered transaction logging.
log_Mode_ansi Creates an ANSI-compliant database with
unbuffered transaction logging.
Exit Exits the LOG menu and returns you to the
CREATE DATABASE menu.
Exiting the CREATE DATABASE Menu
When you exit the CREATE DATABASE menu, you must confirm or discard
the new database, as Figure 4-7 shows.

EXIT : Create-new-database Discard-new-database
Create new database
------------------------------------------------- Press CTRL-W for Help -------
Figure 4-7. The CREATE DATABASE Confirmation Screen
The default is Create-new-database. Press RETURN to create a database with the

specified parameters, make it the current database, and return to the
DATABASE menu. If you do not want to create the new database, press the D
key or use the right arrow key to move the cursor to the Discard-new-
database option and press RETURN. DB–Access does not create a new database.
If you exit without specifying a value for dbspace or logging, the defaults
apply to the database.
Displaying Database Information

Use the Info option on the DATABASE menu to display information about
dbspaces and nondefault locale settings and to read the body of routines in
the current database.
Tip: This menu option provides information about the database that the SQL
statement INFO does not display.
When you select the Info option on the DATABASE menu, the SELECT
DATABASE screen appears, as Figure 4-2 shows.
After you select a database, the DATABASE INFO menu appears, with the
database you selected identified in the dashed line.
Figure 4-8 shows the DATABASE INFO menu for IBM Informix Dynamic
Server.
DATABASE INFO: dBspace Nls Routine Databases Exit

Display DBSPACE information for a database.
------------ demodb@dbserver1 ------- Press CTRL-W for Help -----
Figure 4-8. The DATABASE INFO Menu for IBM Informix Dynamic Server

The DATABASE INFO menu displays the following options.

dBspace Retrieves information about the “Retrieving Information on
dbspaces in the current database. Dbspaces” on page 4-7
NlS Displays Native Language Support “Retrieving Nondefault Locale
(NLS) settings for a database that Information” on page 4-8
supports NLS. This option is
provided for compatibility with
legacy database versions that
support NLS.
Procedures Lists the procedures in the current “Retrieving Information on
(XPS only) database. Select a procedure name Routines” on page 4-9
to see the statements in the body of
that procedure.
Routine In Dynamic Server, the routine “Retrieving Information on
(IDS only) option lists the procedures and Routines” on page 4-9
functions in the current database.
Select a routine name to see the
body of that routine as text.
Databases Selects a different database about “Selecting a Different Database”
which to retrieve information. on page 4-10
Exit Leaves the DATABASE INFO menu none
and returns you to the DATABASE
menu.
To leave the DATABASE INFO menu without displaying information about

the current database, press the Interrupt key to return to the DATABASE
menu.
Retrieving Information on Dbspaces
To retrieve information about the dbspaces in the current database, select the
dBspace option from the DATABASE INFO menu, as Figure 4-9 shows.

Display DBSPACE information for a database.
------------- demodb@dbserver1 --------- Press CTRL-W for Help ------
Number of When
Id Name Chunks Created Mirror
3 dbspace2 1 04/28/94 N
Figure 4-9. The DATABASE INFO Menu with Dbspaces Information Displayed

Retrieving Nondefault Locale Information
Global Language Support (GLS) and Native Language Support (NLS) affect
the order in which lists appear in DB–Access. GLS enables the display and
appropriate ordering of non-English data. Legacy database server versions
used NLS for this purpose.
Native Language Support
If the current database supports NLS, you can select the Nls option on the
DATABASE INFO menu to display information about collating sequence and
C CType (character classification type), as Figure 4-10 shows.

Display NLS information for a database.
-------------- - stores_demo ------------ Press CTRL-W for Help --------
fr_fr.8859-1 Collating Sequence

CType
Figure 4-10. The DATABASE INFO Menu with NLS Information Displayed
An error message displays if the database does not support NLS or the
environment variables for NLS are not properly set.
End of Native Language Support
DB–Access does not provide an option on the DATABASE INFO menu for
displaying the GLS collating sequence and character classification type. To
obtain information about the GLS locale enabled for your database server,
enter the following query with the SQL editor that is described in Chapter 3:
SELECT tabname, site FROM systables
WHERE tabid = 90 OR tabid = 91
The row with tabid 90 stores the COLLATION category of the database locale.
The row with tabid 91 stores the CTYPE category of the database locale.
Figure 4-11 shows the result of the preceding query for the default U.S.
English locale.

------- mydata@mynewdb ------ Press CTRL-W for Help ---
tabname GL_COLLATE
site en_US.819
tabname GL_CTYPE
site en_US.819
2 row(s) retrieved
Figure 4-11. Retrieving GLS Information
For further information on the COLLATION and CTYPE categories in a GLS

locale file, see the IBM Informix: GLS User's Guide.
Retrieving Information on Routines

Depending on the database server product that you use, select either the
Procedures or Routine option on the DATABASE INFO menu to display the
text from a selected routine. The SELECT PROCEDURE or SELECT ROUTINE
screen appears and shows a list of SPL routines in the current database.
Tip: Routine and Procedures options do not display system-created routines.

They only display user-defined routines and SPL routines.
Dynamic Server
Figure 4-12 shows the SELECT ROUTINE screen that appears if you use
Dynamic Server. The SELECT PROCEDURE screen looks the same although
the prompt uses different wording for an SPL routine.
SELECT ROUTINE >>

Select a routine with the Arrow Keys, or enter a name, then press Return.
--------------- demodb@dbserver1 ------------ Press CTRL-W for Help --------
read_address
Figure 4-12. The SELECT ROUTINE Screen
If the routine exists in the system catalog and it fits on the DATABASE INFO
menu, the text appears on the screen, as Figure 4-13 shows.

DATABASE INFO: read_address: dBspace Nls Routine Databases Exit
Display routine text for a selected routine.
--------------- demodb@dbserver1 ------------- Press CTRL-W for Help --------

returning char(15), char(15), char(20), char(15), char(2), char(5);
select fname, address1, city, state, zipcode
into p_fname, p_add, p_city, p_state, p_zip
from customer
where lname = lastname;
return p_fname, lastname, p_add, p_city, p_state, p_zip;
end procedure
Figure 4-13. The DATABASE INFO Menu with Text of Selected Routine Displayed
If the routine text does not fit on one screen, the DISPLAY menu appears with
partial text, as Figure 4-14 shows.
DISPLAY: Next Restart Exit

Display the next page of results.
----------------- demodb@dbserver1 ---------- Press CTRL-W for Help -------

returning char(15), char(15), char(20), char(15),char(2), char(5);
Figure 4-14. The DISPLAY Menu with Partial Routine Text Displayed
To display the next page of text, select the Next option. To display text from
the beginning, select the Restart option.
Selecting a Different Database
To display information about a different database, select the Database option
on the DATABASE INFO menu. The SELECT DATABASE screen appears and
you can select a database, as described in “Selecting a Database” on page 4-2.
You can then use the other options of the DATABASE INFO menu, as this
chapter describes.
Deleting a Database
To delete an existing database on a specified database server, select the Drop
option from the DATABASE menu. The DROP DATABASE screen appears, as
Figure 4-15 shows.

DROP DATABASE >>
Enter the name of the database you wish to drop.
-------------- demodb@dbserver1--------------- Press CTRL-W for Help --------
mydata@dbserver1
demodb@dbserver1
personnel@mynewdb
Figure 4-15. The DROP DATABASE Screen
You cannot delete the current database. The current database is the database
whose name appears in the dashed line below the header of the display.
The DROP DATABASE Screen
To delete a database, use the DROP DATABASE screen in either of the
following ways:
v Use the arrow keys to highlight the name of the database that you want to
delete and press RETURN.
For example, to delete the mydata database, type mydata or highlight

mydata@dbserver1 with an arrow key and press RETURN.
To leave the DROP DATABASE screen without deleting a database, press the
Interrupt key. You return to the DATABASE menu.
Confirming Your Decision to Delete a Database
When you delete a database, DB–Access displays a special menu that asks for
confirmation before it deletes the database, as Figure 4-16 shows.
CONFIRM: No Yes

--------------- demodb@dbserver1 ------------- Press CTRL-W for Help --------
mydata@dbserver1
demodb@dbserver1
personnel@mynewdb
Figure 4-16. The DROP DATABASE Confirmation Menu
The default is No, which helps prevent deleting a database by mistake. If you
want to delete the highlighted database, press the Y key or use the right arrow
key to highlight Yes, and press RETURN. DB–Access deletes the database and all
data that it contains. Be absolutely sure that you choose the correct database
to delete.

Closing a Database
To close the current database, choose the cLose option from the DATABASE
menu and press RETURN. The message Database Closed appears at the bottom
of the screen, which indicates that the current database is closed. The database
name disappears from the Help line, but the database server name remains.
If you select the cLose option when no database name is on the Help line,
DB–Access displays an error message.
If you begin a transaction but do not commit it or roll it back, and then try to
close a database with transactions, the TRANSACTION menu appears, as
Figure 4-17 shows.
TRANSACTION: Commit Rollback

Commit the current transaction.
------------- demodb@dbserver1 --------------- Press CTRL-W for Help ------
Figure 4-17. The TRANSACTION Menu for Databases with Transactions
The TRANSACTION menu ensures that you either commit or roll back an
active transaction before you close the current database. The following list
shows the two menu options:
v The default option is Commit.
Press the Y key or RETURN and DB–Access commits the transactions and
closes the database.
v If you want to roll back the transactions, use an arrow key to move the
highlight to the Rollback option.
Press RETURN, and DB–Access rolls back the transactions and closes the
database.
Warning: Select an option carefully. You might commit transactions you do

not want if you select Commit, and you will lose any new
transactions if you select Rollback.
If you press the Interrupt key, DB–Access displays the DATABASE menu
without committing or rolling back the transactions.
The TRANSACTION menu also appears whenever you attempt to open a

new database or try to leave the DB–Access menu system without first
terminating a transaction. These instances are the only times when you can
access the TRANSACTION menu from DB–Access.
Important: If you begin a transaction in an ANSI-compliant database but do

not issue a COMMIT statement or ROLLBACK statement, then try

to close the database using a non-menu mode, DB–Access will
commit the transaction for you. If you do not want to commit the
transaction, issue both a ROLLBACK statement and a CLOSE
DATABASE statement from the command line.
Related Manuals
The following manuals contain information pertinent to database connection
and creation:
v For syntax information, see the IBM Informix: Guide to SQL Syntax.
v For information about environment variables, see the IBM Informix: Guide to
SQL Reference.
v For information about GLS considerations that apply to opening or

connecting to a database on a different database server, see the
IBM Informix: GLS User's Guide.
The database server examines the client locale information that a client
(such as DB–Access) passes, verifies the database locale, and determines the
server-processing locale for transferring data between the client and the
database.

Chapter 5. The Table Option
The TABLE Menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-2
Creating or Altering a Table . . . . . . . . . . . . . . . . . . . . . . . . . 5-3
The CREATE TABLE Screen . . . . . . . . . . . . . . . . . . . . . . . . 5-3
The ALTER TABLE Screen . . . . . . . . . . . . . . . . . . . . . . . . . 5-4
Using the Schema Editor . . . . . . . . . . . . . . . . . . . . . . . . . 5-5
Adding Columns to a Table (Add Option) . . . . . . . . . . . . . . . . . . . 5-6
Column Name . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-6
Column Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . 5-7
Data Length or Range . . . . . . . . . . . . . . . . . . . . . . . . . 5-9
Column Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-9
Column Index Fill Factor . . . . . . . . . . . . . . . . . . . . . . . . 5-10
Null Value Permission . . . . . . . . . . . . . . . . . . . . . . . . . 5-11
Building the Table (Exit Option) . . . . . . . . . . . . . . . . . . . . . . 5-11
Modifying Columns (Modify Option) . . . . . . . . . . . . . . . . . . . . . 5-12
Deleting Columns (Drop Option) . . . . . . . . . . . . . . . . . . . . . . 5-12
Arranging Storage and Locking (Table_options) . . . . . . . . . . . . . . . . . 5-13
Selecting Dbspaces . . . . . . . . . . . . . . . . . . . . . . . . . . 5-14
Fragmenting a New Table . . . . . . . . . . . . . . . . . . . . . . . 5-14
Altering Fragmentation for an Existing Table . . . . . . . . . . . . . . . . . 5-18
Attaching a Dbspace . . . . . . . . . . . . . . . . . . . . . . . . . 5-20
Detaching a Dbspace . . . . . . . . . . . . . . . . . . . . . . . . . 5-21
Fragmenting an Existing Table . . . . . . . . . . . . . . . . . . . . . . 5-22
Setting the Extent Size . . . . . . . . . . . . . . . . . . . . . . . . . 5-23
Determining the Lock Mode . . . . . . . . . . . . . . . . . . . . . . . 5-24
Adding or Dropping Rowids . . . . . . . . . . . . . . . . . . . . . . 5-24
Defining Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . 5-25
Defining Primary-Key Constraints . . . . . . . . . . . . . . . . . . . . . 5-26
Defining Foreign-Key Constraints . . . . . . . . . . . . . . . . . . . . . 5-27
Defining Check Constraints . . . . . . . . . . . . . . . . . . . . . . . 5-28
Defining Unique Constraints. . . . . . . . . . . . . . . . . . . . . . . 5-30
Defining Default Values . . . . . . . . . . . . . . . . . . . . . . . . 5-31
Displaying Column Information . . . . . . . . . . . . . . . . . . . . . . 5-36
DB-Access Data Types . . . . . . . . . . . . . . . . . . . . . . . . . 5-36
Extended Data Types in Dynamic Server. . . . . . . . . . . . . . . . . . . 5-38
Displaying Index Information . . . . . . . . . . . . . . . . . . . . . . . 5-39
Displaying Table-Level Privileges . . . . . . . . . . . . . . . . . . . . . . 5-40
Displaying References Privileges . . . . . . . . . . . . . . . . . . . . . . 5-40
Displaying Column Constraints and Defaults . . . . . . . . . . . . . . . . . . 5-41
Displaying Referential Constraints . . . . . . . . . . . . . . . . . . . . . 5-41
Sample Primary and Check Constraint Displays . . . . . . . . . . . . . . . . 5-42
Displaying Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-43
Displaying Fragmentation Information . . . . . . . . . . . . . . . . . . . . 5-44
Dropping a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-45

Moving a Table (XPS) . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-46
Related Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-48
In This Chapter
This chapter describes how to use the features of the Table option on the main
menu. Select this option if you want to perform any of the following table
management tasks without SQL programming:
v Create a new table
v Define fragmentation strategy for a new or existing table
v Alter, delete, or display information about an existing table
The TABLE Menu

When you press the T key or select the Table option from the main menu, the
TABLE menu appears, as Figure 5-1 shows.
TABLE: Create Alter Info Drop Move Exit

Create a new table.
-------------mydata@mydbserv--------------------Press CTRL-W for Help -----
Figure 5-1. The TABLE Menu
If no current database exists when you select the Table option, the SELECT
DATABASE screen appears. Select from a list of databases defined for the
current database server or press the Interrupt key to display the main menu.

followed by a + sign. Enter the + sign to display the complete

Use the TABLE menu options as the following table shows.

Create Enables you to define the structure of a “Creating or Altering a Table”
new table on page 5-3
Alter Enables you to alter the structure of an “Creating or Altering a Table”
existing table on page 5-3
Info Displays information about the structure “Displaying Table Information”
of a table on page 5-34
Drop Deletes a table from the database “Dropping a Table” on page
5-45
Move Moves a table from the current database “Moving a Table (XPS)” on
to another database. page 5-46
Exit Returns to the DB-Access main menu none
Creating or Altering a Table

The Create option on the TABLE menu provides a menu that guides you
through the functions of a CREATE TABLE statement. The Alter option
provides menus that guide you through the functions of an ALTER TABLE or
ALTER FRAGMENT statement.
The CREATE TABLE Screen
When you select the Create option on the TABLE menu, the CREATE TABLE
screen appears, as Figure 5-2 shows.
CREATE TABLE >>

Enter the table name you wish to create with the schema editor.
---------------- mydata@mydbserv ------------- Press CTRL-W for Help ------
Figure 5-2. The CREATE TABLE Screen
At the prompt, type the name of the new table and press RETURN. You can
assign any name to the table, as long as you follow the syntax guidelines for
naming database objects described in the IBM Informix: Guide to SQL Syntax.
After you enter the new table name, the CREATE TABLE menu appears, as
Figure 5-3 shows.
Chapter 5. The Table Option 5-3

CREATE TABLE clients: Add Modify Drop Screen Table_options Constraints Exit
Adds columns to the table above the line with the highlight.
---- Page 1 of 1 ---- mydata@mydbserv ------------ Press CTRL-W for Help -----
Column Name Type Length Index Nulls

Figure 5-3. The CREATE TABLE Menu
The ALTER TABLE Screen

When you select the Alter option on the TABLE menu, the ALTER TABLE
prompt and a list of the tables defined in the current database appear, as
Figure 5-4 shows.
ALTER TABLE >>

Enter the table name you wish to alter with the schema editor.
---------------- mydata@mydbserv ------------- Press CTRL-W for Help ------
customer
items
orders
Figure 5-4. The ALTER TABLE Prompt
Enter the name of the table that you want to alter after the prompt or use the
arrow keys to highlight the table name in the list. After you press RETURN, the
ALTER TABLE menu and the table schema appear, as Figure 5-5 shows.
ALTER TABLE clients: Add Modify Drop Screen Table_options Constraints Exit
Adds columns to the table above the line with the highlight.
--- Page 1 of 1 --- mydata@mydbserv ----------- Press CTRL-W for Help -----
customer_num Serial 101 Unique No

fname Char 15 Yes
lname Char 15 Yes
company Char 20 Yes
Figure 5-5. The ALTER TABLE Menu
Important: You must have the Alter privilege to successfully alter a table.
Without the privilege, you can use the menus from the ALTER
TABLE screen, but an error results when you attempt to select
Build-new-table from the EXIT screen. For references explaining
the Alter privilege and other table-level privileges, see page 5-2.
To use the LOAD statement to insert data into a table, you must
have both Insert and Select privileges for the table. You need the

Select privilege because DB–Access must read SELECT
information about the columns before inserting data into the table.
If you do not have the Select privilege, the LOAD command fails
and you get error message -272, “No SELECT permission.” If you
have the Select but not the Insert privilege, you get error -275,
“No INSERT permission.”
Using the Schema Editor
Both the CREATE TABLE and ALTER TABLE menus have the same options,
which are described in the following list.

Add Displays the Schema Editor, from “Adding Columns to a Table
which you can add a new column (Add Option)” on page 5-6
to the table
Modify Displays the columns that you “Modifying Columns (Modify
defined with the Add option so Option)” on page 5-12
that you can modify the column
structure before building the table
Drop Drops an existing column from the “Deleting Columns (Drop
table Option)” on page 5-12
Screen Displays the next screen of column none
definitions in the Schema Editor
Table_options Displays and enables you to select “Arranging Storage and Locking
storage spaces for a new table (Table_options)” on page 5-13
Displays choices from which you
set a fragmentation strategy for a
new table Enables you to set extent
sizes and lock mode for a new
table Adds or deletes rowids for an
existing fragmented table
Constraints Enables you to define primary-key, “Defining Constraints” on page
foreign-key, check, and unique 5-25 “Defining Default Values”
constraints Enables you to set on page 5-31
default column values
Exit Builds, rebuilds, or discards the “Building the Table (Exit
schema and structure that you Option)” on page 5-11
described with the other options
and then returns to the TABLE
menu
Important: You must use the SPACEBAR to move between menu options
because the arrow keys control cursor movement in the Schema
Editor.

To leave the CREATE TABLE menu or ALTER TABLE menu and return to the
TABLE menu without building or altering a table, press the Interrupt key.
Adding Columns to a Table (Add Option)
To add a new column to a table, you define a new line in the Schema Editor
that appears below the dashed line. When you create or alter the columns of a
table, the Schema Editor issues prompts to assist you.
Important: Before you use the Add option from the ALTER TABLE menu,
you must position the highlight in the Schema Editor to indicate
where you want to insert the new column or columns. To move
the highlight within the displayed columns, use the up and down
arrow keys. To scroll more of the column list onto the screen, use
the Screen option on the menu. When you select the Add option,
the highlighted line moves down to make an empty line for the
new column.
The Schema Editor progresses from left to right, completing one horizontal
line of description for each column, with the name of the column at the left.
Use the right arrow key to move the highlight to each field. To accept the
default entry for each field, press RETURN or an arrow key.
As you finish one column, the cursor moves to the next line down, so that
you can type another column name. Thus, the columns that make up the table
are listed vertically.
You can change or bypass any field entry in a line before you move to the
next line in either of the following ways:
v Use the left arrow key to move back to a field that you passed on the
current line.
v Press the Interrupt key to cancel a prompt without inserting a value into
the current (highlighted) field.
After you move the cursor to another line, you must use the Modify option
on the CREATE TABLE menu to change your entry, as “Modifying Columns
(Modify Option)” on page 5-12 describes.
Column Name
The Add option on the CREATE TABLE (or ALTER TABLE) menu places the
cursor on an empty line and displays the ADD COLUMN NAME prompt.
Type the name of the column after the ADD COLUMN NAME prompt and
press RETURN. You can assign any name, as long as you follow the identifier
syntax guidelines described in the IBM Informix: Guide to SQL Syntax.

DB–Access enters the specified name under Column Name, as Figure 5-6
shows.
ADD COLUMN NAME >>

Enter column name. RETURN adds it. INTERRUPT returns to CREATE/ALTER menu.
---- Page 1 of 1 ---- mydata@mydbserv ---------- Press CTRL-W for Help ----
customer_num
Figure 5-6. The ADD COLUMN NAME Screen with Column Name Entered
Column Data Type

The ADD TYPE menu displays various data types, as Figure 5-7 shows.
ADD TYPE clients : Char Numeric Serial Date Money date-Time ...
Permits any combination of letters, numbers, and punctuation.
---- Page 1 of 1 ----- mydata@mydbserv ---------- Press CTRL-W for Help ----
customer_num
Figure 5-7. The ADD TYPE Menu for Defining Column Data Types
To select the data type for the column, type the first capitalized letter of the
data type, using either uppercase or lowercase letters or the SPACEBAR to
highlight it and then press RETURN.
Important: Use the spacebar to move to your choice. Use the arrow keys to
control cursor movement in the lower part of the screen.
Dynamic Server
The CREATE TABLE menu provides options for built-in data types. To define
a column with one of the extended data types, such as smart large objects,
user-defined (opaque) data types, or a collection data type, use the SQL menu
to enter and run a CREATE TABLE statement.
If you select one of the following data type categories from the ADD TYPE
menu, DB–Access displays one or two submenus for that category.

ADD TYPE Category Data Type Submenu Additional Submenu
Numeric Integer
Smallint
Decimal
Float Smallfloat or Float
Char Char (press C to select)
Nchar (press N to select)
Variable-length Varchar Varchar (press V to select)
Nvarchar (press N to select)
Text or Byte Table
Blobspace
Tip: Although some data types described above are not included in the menu
mode, you can use any data types in interactive, non-menu mode.
Locale Character Data: If you use character data in a default locale, select
Char for fixed-length data or Varchar if the table will have varying-length
entries in that column.
If you use a nondefault locale, select Nchar for fixed length or Nvarchar for
varying length.
Large Object Storage Location: If you select VARIABLE-LENGTH TEXT or

BYTE data type, perform one of the following actions to indicate where that
large-object data should reside:
v Select Table to store the full data directly in the column.

Always select Table for databases that reside on Extended Parallel Server.
v Select Blobspace to store the actual TEXT or BYTE data in a large-object
space (blobspace). The table column then holds the blobspace location.
DB–Access displays the SELECT BLOBSPACE screen, as Figure 5-8 shows.
Use the arrow keys to choose a blobspace from the alphabetical list or type
the blobspace name at the top of the screen.

SELECT BLOBSPACE >>
Select a blobspace with the Arrow Keys, or enter a name, then press Return
----- Page 1 of 1 ------ mydata@mydbserv ------- Press CTRL-W for Help ------
cust_blob
Figure 5-8. The SELECT BLOBSPACE Screen for Storing Variable-Length Data
Data Length or Range

If you select any of the following data types for the column, a new ADD
screen appears. Enter the appropriate information in the Length field.
Data Type Length or Range
Char Enter length (the default is 20).
Nchar Enter length (the default is 20).
Numeric For the fixed-point form of the DECIMAL type, enter the
precision and scale (the default is 16, 2). For the floating-point
form of the DECIMAL type, enter the precision only.
Serial Enter the starting number (the default is 1).
Money Specify a length (the default is 16, 2).
Datetime Specify first to last datetime qualifiers.
Interval Specify first to last interval qualifiers.
Varchar Specify a maximum length (from 1 to 255 bytes) and a
minimum space (from 0 to 255 bytes).
Nvarchar Specify a maximum length (from 1 to 255 bytes) and a
minimum space (from 0 to 255 bytes).
Column Index
DB–Access can construct only a nonclustered, ascending B-tree column index.
Select the Yes option to create this type of index with the ADD INDEX menu,

ADD INDEX clients : Yes No
Specifies that this column will NOT have an index.
----- Page 1 of 1 ----- mydata@mydbserv ----------- Press CTRL-W for Help ----
customer_num Serial 101

Figure 5-9. The ADD INDEX Menu
DB–Access displays an ADD DUPLICATES screen. Press RETURN or the Y key

to allow duplicate values, and the word Dups appears in the Index field.
Press the N key to prevent duplicate values. The word Unique appears in the
Index field.
DB–Access displays the ADD FILL FACTOR PERCENTAGE screen, as

Figure 5-10 shows.
If you do not want to index the values in this column or if you want any
other type of index, such as an R-tree index, select the No option. You must
create an R-tree index directly with SQL.
Column Index Fill Factor

Use the ADD FILL FACTOR PERCENTAGE screen, as Figure 5-10 shows, to
set the fill-factor percentage when you create an index on a single column.
The index column has fill factor and Unique or Dups abbreviated to U or D.
ADD FILL FACTOR PERCENTAGE >>

Enter the fill factor percentage. RETURN adds it.
----- Page 1 of 1 -------personnel ------------- Press CTRL-W for Help -----
empl_num Integer U
70% No
last_name Char 20 D 90% No
insurance Integer Dups Yes
ss_num Integer Unique No
Figure 5-10. The ADD FILL FACTOR PERCENTAGE Screen
Important: You can only set a fill-factor value when you create a new index.
You can modify the fill factor through the Modify option on the
CREATE TABLE menu. However, you cannot alter it through the
ALTER TABLE menu after the table for the index is created.
Enter any positive value to a maximum of 100. A value less than 1 or greater
than 100 results in an error.

If you press RETURN without entering a value, the index will have the fill-factor
percentage set in the database server ONCONFIG file. If ONCONFIG has no
fill-factor setting, the index will have the default fill-factor value of 90 percent.
Null Value Permission

Specify whether the column allows null values on the ADD NULLS menu, as
Figure 5-11 shows.
ADD NULLS clients : Yes No

Permits null values in this column.
---- Page 1 of 1 ----- mydata@mydbserv --------- Press CTRL-W for Help ----
customer_num Serial 101 Unique
Figure 5-11. The ADD NULLS Menu
Select Yes to allow null values in the column or No to force the column to
always have a non-null value.
To add another column definition to the table or return to the CREATE

TABLE menu, press Interrupt, an arrow key, or RETURN.
Building the Table (Exit Option)
When you complete the schema for the new (or modified) table, select the Exit
option on the CREATE TABLE (or ALTER TABLE) menu. DB–Access displays
the menu, as Figure 5-12 shows.
CREATE TABLE clients: Build-new-table Discard-new-table

Builds a new table and returns to the Table Menu.
----- Page 1 of 1 ---- mydata@mydbserv --------- Press CTRL-W for Help ----
customer_num Serial 101 Unique No

fname Char 15 Yes
lname Char 15 Yes
company Char 20 Yes
address1 Char 20 Yes
address2 Char 20 Yes
city Char 15 Yes
state Char 2 Yes
zipcode Char 5 Dups Yes
phone Char 18 Yes

Figure 5-12. The CREATE TABLE Menu
To create the table that contains the displayed columns and return to the
TABLE menu, select Build-new-table. To return to the TABLE menu without
saving the new or modified table definition, select Discard-new-table.

Modifying Columns (Modify Option)
To modify an existing column, perform the following steps:
1. Use the arrow keys to highlight the column definition that you want to
modify.
2. If necessary, select Screen from the CREATE TABLE menu to display the
next screen of column definitions in the Schema Editor.
3. Select the Modify option on the CREATE TABLE (or ALTER TABLE) menu
and press RETURN.
4. Move the highlight to the field you want to modify.
DB–Access prompts appropriately for the field where the highlight is
located. Each of these prompt screens works like the corresponding ADD
screen.
For instructions on specific prompts, see the following table.
Prompt Instructions
MODIFY COLUMN NAME “Column Name” on page 5-6
MODIFY TYPE “Column Data Type” on page 5-7
MODIFY LENGTH “Data Length or Range” on page 5-9
MODIFY INDEX “Column Index” on page 5-9
MODIFY NULLS “Null Value Permission” on page 5-11
5. Move the highlight to the next field and repeat the process.
6. Select Exit to leave the screen after you have elected to build the table or
discard the schema, as Figure 5-12 on page 5-11 shows.
To leave a Modify screen or menu without making any changes, press the
Interrupt key at any time.
Deleting Columns (Drop Option)
To delete a column from a table schema, perform the following steps:
1. Position the highlight anywhere on the column that you want to delete.
2. Select the Drop option on the CREATE TABLE (or ALTER TABLE) menu.
The column line is then partially or completely highlighted on the screen.
DB–Access displays the DROP menu that prompts you to verify your
decision, as Figure 5-13 shows.

DROP clients : Yes No
Deletes the highlighted column from the table.
--- Page 1 of 1 ---- mydata@mydbserv --------- Press CONTROL-W for Help ---
customer_num Serial 101

Unique No
fname Char 15 Yes
lname Char 15 Yes
company Char 20 Yes
Figure 5-13. The DROP Menu
Select Yes from the DROP menu to delete the line currently highlighted in the
Schema Editor; select No to keep the line.
Arranging Storage and Locking (Table_options)
To display the TABLE_OPTIONS menu, as Figure 5-14 shows, select
Table_options from the CREATE TABLE menu (or ALTER TABLE menu). You
can then specify storage-management parameters, such as location and
distribution of data on the storage media.
TABLE_OPTIONS clients: Storage eXtent_size Next_size Lock_mode Exit

Define dbspace or fragmentation strategy for table storage.
---- Page 1 of 1 ---- mydata@mydbserv ---------- Press CTRL-W for Help ----

Figure 5-14. The TABLE_OPTIONS Menu
The TABLE_OPTIONS menu contains the following options.

Storage Displays dbspaces and enables you “Selecting Dbspaces” on page
to assign a dbspace to the current 5-14 “Fragmenting a New Table”
table Enables you to define a on page 5-14
fragmentation strategy for the
current table
eXtent_size Enables you to specify the initial “Setting the Extent Size” on page
extent size of the table 5-23
Next_size Enables you to specify the next “Setting the Extent Size” on page
extent size 5-23
Lock_mode Enables you to select either Page or “Determining the Lock Mode” on
Row as the lock mode page 5-24
Exit Returns to the previous menu none

For assistance in setting Table_options values such as dbspaces, fragmentation
strategy, extent sizes, and lock mode, see your IBM Informix: Administrator's
Guide and IBM Informix: Performance Guide.
Selecting Dbspaces
To display the STORAGE menu, as Figure 5-15 shows, select the Storage
option from the TABLE_OPTIONS menu.
STORAGE new_acct: Dbspace Fragment Exit

Select a dbspace in which to store the table.
----- Page 1 of 1 ----- mydata@mydbserv -------- Press CTRL-W for Help ----
Figure 5-15. The STORAGE Menu for Storing Table Data and Defining Fragmentation Strategy
To display the SELECT DBSPACE screen, as Figure 5-16 shows, select Dbspace
from the STORAGE menu. Use the arrow keys to highlight a dbspace from
the list of dbspaces in the current database and then press RETURN.
SELECT DBSPACE >>

----- Page 1 of 1 ---- mydata@mydbserv --------- Press CTRL-W for Help ----
rootdbs
pers_dbs
empl_dbs
Figure 5-16. The SELECT DBSPACE Screen for Specifying Table Storage
Select Fragment to set up fragmentation strategy in a series of additional

menus. For instructions, see “Fragmenting a New Table” on page 5-14 or
“Altering Fragmentation for an Existing Table” on page 5-18.
Fragmenting a New Table

To arrange fragmentation for a new table, display the FRAGMENT menu, as
Figure 5-17 shows. You reach this menu through the following steps:
1. On the main menu, select Table.
2. On the TABLE menu, select Create.
3. On the CREATE TABLE, select Table_options.
4. On the TABLE_OPTIONS menu, select Storage.
5. On the STORAGE menu, select Fragment.

FRAGMENT new_acct: Round_robin eXpression rOwids Exit
Select and define a round robin fragmentation strategy.
Figure 5-17. The FRAGMENT Menu for Defining Fragmentation Strategy
Select the strategy that you want from the FRAGMENT menu, as the
following table shows.

Round_robin Selects a round-robin strategy for Figure 5-30 on page 5-22
fragmentation
eXpression Selects an expression strategy for “Expression Strategy Setup” on
fragmentation page 5-16
rOwids Adds a column that contains Press the o key to explicitly
rowids to a fragmented table enable access by rowid
(The database server does not
automatically assign rowids
when you insert rows in a
fragmented table.)
Exit Exits the FRAGMENT menu and Figure 5-15 on page 5-14
returns to the STORAGE menu
DB–Access has no FRAGMENT menu option for hash or hybrid

fragmentation. If you want this type of strategy, use the SQL menu to enter
and run the CREATE TABLE or ALTER TABLE statement.
Extended Parallel Server does not support rowids for fragmented tables.
Round-Robin Setup: To display the ROUND_ROBIN menu, as Figure 5-18

shows, select the Round_robin option on the FRAGMENT menu.
ROUND_ROBIN new_acct: Add Modify Drop Screen Exit

Add a dbspace to the fragmentation strategy above the line with the highlight.
Dbspace Name
dbspace1
dbspace2
Figure 5-18. The ROUND_ROBIN Menu for Selecting Fragment Storage Spaces

The ROUND_ROBIN menu has the following options.
Option Purpose
Add Displays dbspaces so that you can add a new dbspace to the
round-robin fragment space assigned to the current table
Modify Enables you to redefine the fragmentation strategy for the
highlighted dbspace
Drop Deletes the highlighted dbspace from the existing strategy, but
does not delete the dbspace from the database server
Screen Scrolls the screen to display more of the available dbspaces
Exit Returns to the FRAGMENT menu
Use the arrow keys to highlight a dbspace from the list on the SELECT
DBSPACE screen, as Figure 5-16 on page 5-14 shows, and press RETURN. If you
try to add a dbspace that is already part of another strategy, an error message
appears.
When you return to the ROUND_ROBIN menu, the screen displays all
dbspaces currently chosen for the strategy.
Expression Strategy Setup: To display the EXPRESSION menu, as

Figure 5-19 shows, select the eXpression option on the FRAGMENT menu.
EXPRESSION new_acct: Add Modify Drop Screen Exit

Add a strategy definition.
Dbspace Name Expression
dbspace1 field1 <100

dbspace2 field1 >=100 and field1 <200
dbspace3 remainder
Figure 5-19. The EXPRESSION Menu for Defining Expression Fragmentation Strategy
The EXPRESSION menu has the following options.

Option Purpose
Add Adds a new dbspace to those that will contain fragments of
this table, according to the expression fragmentation strategy
Modify Modifies the dbspace or expression associated with that
dbspace
Drop Deletes the highlighted dbspace and expression from the
existing strategy, but does not delete the dbspace from the
database server

Screen Scrolls the screen to display more of the available dbspaces
Exit Returns to the FRAGMENT menu
Important: DB–Access does not perform data validation on the expression of

the strategy.
The Add option on the EXPRESSION menu displays the SELECT DBSPACE
screen, as Figure 5-16 on page 5-14 shows. Use the arrow keys to highlight a
dbspace from the list and press RETURN. If you try to add a dbspace that is
already part of another strategy, an error message appears.
After you select a dbspace, the EDIT EXPRESSION menu appears, as

Figure 5-20 shows. From this menu you can enter an expression that
determines whether a particular record belongs in the highlighted dbspace.
EDIT EXPRESSION new_acct: New Modify Use-editor Exit

Enter a new expression which will determine where a record will be stored.
----- Page 1 of 1 ----- mydata@mydbserv --------- Press CTRL-W for Help ----
dbspace1 field1<100

dbspace2 field1>=100 and field1<200
dbspace3 remainder
Figure 5-20. The EDIT EXPRESSION Menu for Defining and Editing Expressions
The EDIT EXPRESSION menu has the following options.

Option Purpose
New Displays the blank SQL editor screen so that you can enter a
new expression
Modify Displays the current expression on the SQL editor screen so
that you can modify the expression
Use-editor Displays the current expression in the system editor so that
you can modify the expression
Exit Returns to the EXPRESSION menu
After you exit the editor, DB–Access displays the CONFIRM CHANGES
menu, as Figure 5-21 shows.

CONFIRM CHANGES dbspace1: SAVE DISCARD
Save changes.
----------------mydata@mydbserv----------------- Press CTRL-W for Help ------
field1<100
Figure 5-21. The CONFIRM CHANGES Menu
To save the edits to the expression, press RETURN. To discard the edits to the
expression, select DISCARD. You return to the EXPRESSION menu.
Altering Fragmentation for an Existing Table

If you are altering a table, you arrange or remove fragmentation with the
ALTER FRAGMENT menu. To reach the ALTER FRAGMENT menu:
v On the main menu, select Table.
v On the TABLE menu, select Alter.
v Select the Table_options option from the ALTER TABLE menu.
v Select the Storage option from the TABLE_OPTIONS menu.
v Select the Fragment option from the STORAGE menu.
Figure 5-22 shows how the ALTER FRAGMENT menu displays a table with a
round-robin fragmentation strategy. Figure 5-23 shows how the ALTER
FRAGMENT menu displays a table with an expression-based fragmentation
strategy.
ALTER FRAGMENT - new_acct: Add Drop Screen Init aTtach detaCh Exit
Add one new dbspace to the end of the list.
----- Page 1 of 1 ---- newstores@mydbserv ------- Press CTRL-W for Help ---
Dbspace Name
dbspace1
dbspace2
dbspace3

Figure 5-22. The ALTER FRAGMENT Menu for Round-Robin Fragmentation Strategy

ALTER FRAGMENT - new_acct: Add Modify Drop Screen Init aTtach detaCh Exit
Add an expression to the fragmentation strategy above the line with the highlight.
----- Page 1 of 1 ----- newstores@mydbserv --------- Press CTRL-W for Help ----
dbspace1 field1 <100
dbspace2 field1 >=100 and field1 <200
dbspace3 remainder
Figure 5-23. The ALTER FRAGMENT Menu for Expression Fragmentation Strategy
The ALTER FRAGMENT menu has the following options.

Add Adds a dbspace to the round-robin “Round-Robin Setup” on page 5-15
scheme or “Expression Strategy Setup” on
page 5-16, depending on the
Drop Drops one dbspace from those used fragmentation type of the current
for fragments of the current table table
Screen Scrolls more dbspaces onto the none
screen
Init Describes the fragmentation scheme “Fragmenting an Existing Table” on
(if any) of an existing table and page 5-22
enables you to change the strategy,
attach fragments, detach fragments,
or remove fragmentation
aTtach Attaches one or more tables to the “Attaching a Dbspace” on page 5-20
current table, modifies or deletes a
selected attachment
detaCh Detaches records from the current “Detaching a Dbspace” on page 5-21
table from a specific dbspace and
creates a new table with those
records
Exit Returns to the TABLE_OPTIONS none
menu
Important: You can execute only one menu option in an ALTER FRAGMENT
menu, and it can be applied to the current strategy only once. For
example, you can add only one dbspace to a round-robin strategy,
and you cannot delete a dbspace during the same ALTER TABLE
session.

Attaching a Dbspace
Select the aTtach option on the ALTER FRAGMENT expression strategy
menu, and the ATTACH TABLES menu appears. Figure 5-24 shows the
ATTACH TABLES menu for a table that has expression fragmentation. The
round-robin version of this screen shows only the table name for each
fragment.
ATTACH TABLES new_acct: Add Modify Drop Screen Exit

Add one new table to the fragmentation strategy.
---- Page 1 of 1 ---- newstores@mydbserv -------- Press CTRL-W for Help ----
Table Name Expression Position Dbspace
acct field1 <100 BEFORE dbspace1
cur_acct field1 >=100
Figure 5-24. The ATTACH TABLES Menu for Expression Fragmentation Strategy
Select Add on the ATTACH TABLES menu to begin attaching a fragment. On

the SELECT ATTACHING TABLE screen, highlight or type in the table name
you want and press RETURN, as Figure 5-25 shows.
SELECT ATTACHING TABLE >>

Select a table with the Arrow Keys, or enter the name, then press Return.
acct
cur_acct
myacct
Figure 5-25. The SELECT ATTACHING TABLE Screen
If the table uses round-robin strategy, the resulting new fragment or fragments
will be positioned at the end of the fragmentation strategy and the ALTER
FRAGMENT menu returns.
If the table uses expression strategy, complete the following steps:

1. After you select a table, the EDIT EXPRESSION menu appears. See
Figure 5-20 on page 5-17.
2. When you exit the EDIT EXPRESSION menu, the ADD DEFINE ATTACH
POSITION menu appears, as Figure 5-26 shows.

ADD DEFINE ATTACH POSITION acct: Before After None
Define a position for an attaching fragment before a dbspace.
----- Page 1 of 1 ---- newstores@mydbserv -------- Press CTRL-W for Help ----
Figure 5-26. The ADD DEFINE ATTACH POSITION Menu
v Select Before to attach the new fragment before a dbspace that you select
in the next step.
v Select After to attach the new fragment after the dbspace that you select
in the next step.
v Select None to attach the new fragment in the default position.
3. If you select Before or After as the attach position, the SELECT DBSPACE
screen appears, as Figure 5-27 shows, listing the dbspaces that the strategy
encompasses prior to attaching the new one.
SELECT DBSPACE >>

----- Page 1 of 1 ---- newstores@mydbserv -------- Press CTRL-W for Help ---
dbspace1
dbspace2
dbspace3
Figure 5-27. The SELECT DBSPACE Screen Listing Fragmented Dbspaces
Select the dbspace before or after which you want to attach the added
fragment.
The ATTACH TABLES menu reappears, as Figure 5-24 on page 5-20 shows,
and shows values for the Position and Dbspace fields.
Detaching a Dbspace
The detaCh option from the ALTER FRAGMENT menu displays the DETACH
DBSPACE screen, as Figure 5-28 shows.
DETACH DBSPACE >>

----- Page 1 of 1 ---- newstores@mydbserv ------- Press CTRL-W for Help ----
dbspace1
dbspace2
dbspace3
Figure 5-28. The DETACH DBSPACE Screen for Removing Fragmentation

Select the dbspace from which you want the records copied into a new,
unfragmented table. You can select a dbspace from the list or type in a
dbspace name. If you enter an invalid dbspace name, an error message
appears.
After you correctly enter a dbspace on the DETACH DBSPACE screen, the
NEW TABLE screen appears, as Figure 5-29 shows.
NEW TABLE >>

Enter the name you want assigned to the new table, then press Return.
Figure 5-29. The NEW TABLE Screen for Naming a Detached dbspace
Enter the name you want to assign to the new, unfragmented table. This table
stores the records from the dbspace you previously selected through the
DETACH DBSPACE screen. The display returns to the ALTER FRAGMENT
menu.
Fragmenting an Existing Table

If a table has no fragmentation strategy when you select the Fragment option
on the STORAGE menu, the ALTER FRAGMENT menu appears, as
Figure 5-30 shows.
ALTER FRAGMENT - new_acct: Init Attach Exit

Define a fragmentation strategy.
----- Page 1 of 1 ---- newstores@mydbserv ------- Press CTRL-W for Help ----
Figure 5-30. The ALTER FRAGMENT Menu for No Fragmentation Strategy

The ALTER FRAGMENT menu has the following options.

Init Provides the following options: The ALTER FRAGMENT-INIT
v fragments a previously unfragmented menu has the same options as
table the FRAGMENT menu under
CREATE TABLE. For
v removes fragmentation from a table
instructions, see “Fragmenting a
v changes the fragmentation strategy for New Table” on page 5-14.
a table
Attach Enables you to define a fragmentation The ALTER
strategy and select the tables to fragment FRAGMENT-ATTACH menu
with the new strategy. This option has offers round-robin and
the same effect as the following expression fragmentation. For
statement: ALTER FRAGMENT ON TABLE instructions on both types of
table1... ATTACH table1, table2 strategy, see “Fragmenting a
New Table” on page 5-14.
Exit Returns to the TABLE_OPTIONS menu none
Important: You can perform only one operation during an ALTER

FRAGMENT session.
Setting the Extent Size

When you create a table, you can specify how much initial disk space, or
initial extent size, to reserve for the table. You can also specify the size of
additional extents, or next extent spaces, the database server adds if the initial
extent becomes full.
To specify an initial extent size, select the eXtent_size option on the

TABLE_OPTIONS menu. DB–Access displays the Extent Size screen, as
Figure 5-31 shows. After you set initial extent size, select the Next_size option
from the TABLE_OPTIONS menu to display the Next Size screen, as
Figure 5-32 shows.
Extent Size >>

Specify initial extent size in kilobytes.

Figure 5-31. The (Initial) Extent Size Screen

Next Size >>
Specify next extent size in kilobytes.
----- Page 1 of 1 ----- mydata@mydbserv ---------- Press CTRL-W for Help ----

Figure 5-32. The Next (Extent) Size Screen
To select extent size on either screen, perform one of the following actions:
v Press RETURN to accept the default size of 8 kilobytes.
v Type a number (representing kilobyte units) and press RETURN.
The minimum extent size is 4 kilobytes.
Determining the Lock Mode

When you select the Lock_mode option on the TABLE_OPTIONS menu,
DB–Access displays the LOCK_MODE menu, as Figure 5-33 shows.
LOCK_MODE clients: Page Row Exit

Locking is at page level. This is the default.
----- Page 1 of 1 ----- mydata@mydbserv ---------- Press CTRL-W for Help ----

Figure 5-33. The LOCK_MODE Menu
The LOCK_MODE menu lets you choose the mode to use when the database
locks the rows in a table. The LOCK_MODE menu has the following options.
Option Purpose
Page Locks the entire page on which a row resides
Row Locks a selected row individually
Exit Exits to the TABLE_OPTIONS menu
One row of a table is the smallest object that you can lock. A disk page
contains one or more rows of a table. To determine if you will enhance
performance by locking a disk page rather than individual rows on the page,
see your IBM Informix: Performance Guide. Unless you specify row-level
locking before you exit, DB–Access uses the default (Page).
Adding or Dropping Rowids

You can add or delete rowids only when you alter an existing table. To reach
the ALTER ROWID menu:
v Select Table_options from the ALTER TABLE menu.
v Select Rowids from the TABLE OPTIONS menu.

The ALTER ROWID menu has the following options.
Option Purpose
Add Adds a column with rowids to the fragmented table
Drop Discards the rowid column previously added
None Cancels the selection you made on this screen so that you can
exit without altering the table
Exit Exits to the TABLE_OPTIONS menu
If you select Add or Drop, another menu prompts you to verify your
selection. Select Yes to execute the Add or Drop; select No to cancel the Add
or Drop.
Defining Constraints
You can use the DB–Access Schema Editor to define constraints for columns
in a specified table. You can define primary-key, foreign-key, column-level and
table-level check, and unique constraints, as well as add and modify column
default values.
If you select the Constraints option from the CREATE TABLE menu, the
CONSTRAINTS menu appears, as Figure 5-34 shows.
CONSTRAINTS - mytab: Primary Foreign Check Unique Defaults Exit

Define a primary key constraint.
-----------------mydata@mydbserv------a-------- Press CTRL-W for Help --------
Figure 5-34. The CONSTRAINTS Menu
The CONSTRAINTS menu has the following options.

Option Purpose
Primary Lists the columns that make up the table so that you can
choose the column or columns that make up the primary key
Foreign Asserts a foreign-key relationship for a column
Check Enables you to specify valid values for a column and forces
the validation of data entry in that column
Unique Declares that a column must contain a unique value
Defaults Enables you to set a default value for a column
Exit Returns you to the CREATE TABLE menu

Defining Primary-Key Constraints
To add, modify, or delete primary-key constraints for the current table, select
the Primary option on the CONSTRAINTS menu. The PRIMARY KEY menu
appears, as Figure 5-35 on page 5-26 shows.
PRIMARY KEY mytab: Add Modify Drop Screen Exit

Add a constraint name or column name.
-------Page 1 of 1 --------mydata@mydbserv------- Press CTRL-W for Help --------
Constraint Name Column Name
constraint1 column1
Figure 5-35. The PRIMARY KEY Menu
When you enter a primary constraint, DB–Access validates your entry by

verifying the following information:
v The column name is not repeated.
v No more than 16 column names appear in one constraint.
v The column is not assigned the BYTE or TEXT data type.
Use the menu options as follows:

v Use the Screen option to scroll to any additional constraint descriptions so
that you can select one to modify or delete.
v If you select the Add option to add a primary-key referential constraint, the
Schema Editor inserts and highlights a new line at the top of the constraint
list. If you move your cursor to an existing constraint line and select the
Modify option, you can change the contents of the line.
1. At the ADD (or MODIFY) CONSTRAINT NAME prompt, enter a
constraint name.
If you press RETURN in this field without typing a constraint name, the
database server assigns a temporary constraint name, such as
unassigned1, unassigned2, and so on. This temporary constraint name
exists until you modify it or the table is built or discarded. The database
server assigns a permanent constraint name at the time that you create
the table.
2. At the ADD (or MODIFY) COLUMN NAME prompt, enter a column
name.
v Select Drop to eliminate the constraint that the highlight indicates. If the
highlighted field is Constraint Name, then all columns associated with that
constraint are deleted. The lines are removed and any gaps are closed in the
display.

Defining Foreign-Key Constraints
To create, modify, or delete foreign-key constraints for the current table, select
the Foreign option on the CONSTRAINTS menu. The FOREIGN KEY menu
appears, as Figure 5-36 shows.
FOREIGN KEY mytab: Add Modify Drop Screen Exit

Add a constraint name or referencing/referenced column pair.
------Page 1 of 1 -------mydata@mydbserv------ Press CTRL-W for Help --------
Constraint Referencing Column Referenced Table Referenced Column CD
unassigned0 column1 table2 column1 Y
column2 column2
column3 column3
Figure 5-36. The FOREIGN KEY Menu
Use the menu options as follows:

v Use the Screen option to scroll any additional constraint descriptions onto
the screen so that you can select one to modify or delete.
v Select the Add option to specify the constraint name, referenced table, and
cascading deletes, or to add one or more referencing and referenced column
pairs to the constraint.
1. At the ADD CONSTRAINT NAME prompt, enter a constraint name. If
you press RETURN in this field without typing a constraint name,
DB–Access assigns a temporary constraint name. The database server
assigns a permanent name to the constraint when the constraint is
created.
2. At the ADD COLUMN NAME prompt, enter the name of the referencing
column.
3. At the ADD REFERENCED TABLE prompt, enter the name of the
referenced table.
4. At the second ADD COLUMN NAME prompt, enter the name of the
referenced column.
5. When the CD (Cascading Deletes) field is the current field, the ADD
ENABLE CASCADING DELETES menu appears, as Figure 5-37 on page
5-28 shows.

ADD ENABLE CASCADING DELETES mytab3: No Yes
No, I do not want cascading deletes.
-------- Page 1 of 1 --------mydata@mydbserv------ Press CTRL-W for Help -----
Constraint Referencing Column Referenced Table Referenced Column CD
cons1 col1 yourtab col6 N
Figure 5-37. The ADD ENABLE CASCADING DELETES Menu
Select Yes to enable cascading deletes. When you delete a referenced

(parent) record, you also delete all corresponding referencing (child)
records. (Option Yes is equivalent to the ON DELETE CASCADE option
of the REFERENCES clause in the CREATE TABLE statement.)
Select No to prevent cascading deletes. A referenced (parent) column
cannot be deleted if referencing (child) records exist.
For a detailed description of referential integrity and cascading deletes,
see the CREATE TABLE statement in the IBM Informix: Guide to SQL
Syntax.
When you complete the Cascading Deletes entry, the cursor returns to the
Constraint field. Enter another constraint or press the Interrupt key to
return to the top line of the FOREIGN KEY menu.
v Move your cursor to an existing constraint line and select the Modify
option to change the contents of the line. You can modify Constraint,
Referencing Column, and Referenced Table information.
Change the entry for the field and press RETURN to modify it, or press the
Interrupt key if you do not want to modify a foreign constraint.
v The Drop option displays a list of foreign-key constraints and prompts you
to select one to delete.
If the current (highlighted) field is Constraint, then the entire constraint is
deleted. If any other field is highlighted, then only that referenced and
referencing pair is deleted.
The default is Yes. Press RETURN to delete the highlighted constraint. Move
the cursor to highlight No if you do not want to delete that constraint. You
return to the FOREIGN KEY menu.
Defining Check Constraints

The CHECK CONSTRAINTS menu lets you add, modify, or delete a check
constraint for the current table. Select the Check option on the
CONSTRAINTS menu to access the CHECK CONSTRAINTS menu, as
Figure 5-38 shows.

CHECK CONSTRAINTS mytab: Add Modify Drop Screen Exit
Add a check constraint.
-----------------mydata@mydbserv------------- Press CTRL-W for Help ---------
Constraint Name Value
cons2 (column1 > (c ...
cons3 column2 < col ...
cons4 column3 > 100
Figure 5-38. The CHECK CONSTRAINTS Menu
The CHECK CONSTRAINTS menu displays any previously added check

constraints. The first 36 characters of the check value appear on the CHECK
CONSTRAINTS menu. Use the CHECK CONSTRAINTS menu options as
follows:
v The Drop option lets you delete the current check constraint.
The default is Yes. Press RETURN to delete the highlighted constraint. Move
the cursor to highlight No and then press RETURN if you do not want to
delete that constraint. You return to the CHECK CONSTRAINTS menu.
v Select the Add option to add a check constraint for the table and enter the
name and value of the constraint.
1. At the ADD (or MODIFY) CONSTRAINT NAME prompt, enter a name.
If you press RETURN in this field without specifying a value, a
temporary constraint name is assigned, and it exists until the table is
built or discarded.
2. From the ADD (or MODIFY) CHECK VALUE menu, as Figure 5-39 on
page 5-29 shows, select the SQL editor or specify another editor to enter
the check-constraint value.
ADD CHECK VALUE cons99: New Modify Use-editor Exit

Enter a new check value using the SQL editor.
----------------mydata@mydbserv------------- Press CTRL-W for Help --------
Figure 5-39. The ADD CHECK VALUE Menu
The ADD CHECK VALUE menu has the following options.

Option Purpose
new check value
Modify Displays the current check value on the SQL editor screen so
that you can modify the value
Use-editor Displays the current check value in the system editor so that
you can modify the value

Exit Returns to the CHECK CONSTRAINTS menu
If you break from the ADD CHECK VALUE menu or exit without defining
the check value, you return to the CHECK CONSTRAINTS menu. If you
defined the check value, you remain in add mode, a new line is inserted, the
Constraint Name is the current field, and the ADD CONSTRAINT NAME
prompt appears.
Defining Unique Constraints

The UNIQUE CONSTRAINTS menu lets you add, modify, or delete a unique
constraint for the current table. To access the UNIQUE CONSTRAINTS menu,
select the Unique option on the CONSTRAINTS menu, as Figure 5-40 shows.
UNIQUE CONSTRAINTS mytab: Add Modify Drop Screen Exit

Add a unique constraint.
------------------mydata@mydbserv------------ Press CTRL-W for Help ---------
cons2 column1
column2
column3
cons3 column4
Figure 5-40. The UNIQUE CONSTRAINTS Menu
Use the UNIQUE CONSTRAINTS menu options as follows:

v Select Screen to display the next screen of unique constraints.
v If you select the Add option, the Schema Editor adds a new line.
1. At the ADD CONSTRAINT NAME prompt, enter a constraint name. If
you press RETURN in this field without typing a name, a temporary
constraint name is assigned, which exists until the table is built or
discarded.
2. At the ADD COLUMN NAME prompt, enter the name of the column
that should have a unique value. A new line is added, and the Column
Name is still the current field.
v The Modify option lets you modify either the Constraint Name or Column
Name field. If you modify the Constraint Name field, the MODIFY
CONSTRAINT NAME prompt appears. If you modify the Column Name
field, the MODIFY COLUMN NAME prompt appears.
v Select the Drop option to delete the constraint or column name where the
highlight is located.
If you delete a constraint name, all column names associated with that
constraint name are also deleted.
Important: You cannot modify unique constraints after you create them. To
identify the unique constraints listed on the UNIQUE

CONSTRAINTS menu, use an asterisk (*) before the constraint
name. If you try to modify a unique constraint using the Modify
option in the UNIQUE CONSTRAINTS menu, an error message
appears.
Data Validation: When you enter a unique constraint, DB–Access validates

your entry by verifying the following information:
v The column name exists.
v The column name is not repeated.
v No more than 16 column names are present in one constraint.
v The column is not assigned the BYTE or TEXT data type.
Defining Default Values

Use the DEFAULTS menu to define default values for columns in a table, as
Figure 5-41 shows. To access the DEFAULTS menu, select the Defaults option
on the CONSTRAINTS menu.
DEFAULTS mytab: Add Modify Drop Screen Exit

Add a column default.
-------Page 1 of 1 --------mydata@mydbserv----- Press CTRL-W for Help -------
Column Name Type Value
column1 User
column3 Null
column5 Today
column6 Current (Fraction to Fraction (5))
column7 Literal
column8 Literal 1200
Figure 5-41. The DEFAULTS Menu
The DEFAULTS menu has the following options.

Option Purpose
Add Adds a column default value of the appropriate data type
Modify Lists an existing default name, data type, and value so that
you can change the default attributes
Drop Deletes a column default
Screen Displays the next screen of defaults
Exit Returns to the CONSTRAINTS menu
You can see the first 28 characters of the value.

To add a column default:
1. Select the Add option.
The Schema Editor inserts a new line at the top of the list and makes the
Column Name the current field.
2. At the ADD COLUMN NAME prompt, enter a value for the column
name.
3. From the ADD DEFAULT TYPE menu that Figure 5-42 shows, define the
default value of a column in the current table.
ADD DEFAULT TYPE mytab: Literal User Current Null Today Db-server-name Site-name
Assign a literal value using either the SQL editor or a system editor.
---------------mydata@mydbserv---------------- Press CTRL-W for Help ---------
Figure 5-42. The ADD DEFAULT TYPE Menu
The ADD DEFAULT TYPE menu has the following options that let you assign
default values to the column.
Option Default Value Assigned
Literal A literal default value entered either in the
SQL editor or a user-specified system editor
User The login name of the current user
Current The current system clock time of day
Null Null
Today The current system date
Db-server-name The current database server name
Site-name The current site name
v If you select User, Null, Today, Db-server-name, or Site-name, the system
assigns that value to the default type and returns you to the DEFAULTS
menu in add mode.
v If you select Current as the default value, the qualifier is taken from the
column definition.
v If you enter a default value and the type is DATETIME or INTERVAL, enter
only the value. The qualifier comes from the column definition.
v If you select Literal as the default type, the ADD DEFAULT VALUE menu
appears, which lets you assign a literal as the default value.
The ADD DEFAULT VALUE menu lets you add or modify the default value
for a column in the current table with either the SQL editor or a system editor,

ADD DEFAULT VALUE column7: New Modify Use-editor Exit
Enter a new default value using the SQL editor.
-----------------mydata@mydbserv------------- Press CTRL-W for Help ----------
Figure 5-43. The ADD DEFAULT VALUE Menu
The ADD DEFAULT VALUE menu displays the following options.

Option Purpose
new value
Modify Displays the current default on the SQL editor screen so that
you can modify the default value
Use-editor Displays the current default in the system editor so that you
can modify the current value
Exit Returns to the ADD DEFAULT VALUE menu
To modify the column name, type, or value field:

1. Select the Modify option to modify the Column Name, Type, or Value field
where the highlight is located.
2. If you highlight a value, the MODIFY DEFAULT VALUE menu prompts
you to invoke the SQL editor or the system editor so that you can type
over or modify the existing default value.
Data Validation: When you enter a default value, DB–Access validates your
entry. The database server validates the literal value and checks the following
information:
v The column name must exist.
v The column type cannot be SERIAL.
v If the column does not allow nulls, you cannot specify the default type as
Null.
v You can use the default type Current only with a DATETIME column type.
v You can use the default type Db-server-name only with a column type of
CHAR, NCHAR, VARCHAR, or NVARCHAR, which has a minimum
length of 18 characters.
v You can use the default type Site-name only with a column type of CHAR,
NCHAR, VARCHAR, or NVARCHAR, which has a minimum length of 18
characters.
v You can use the default type Today only with a column type of DATE.
v You can use the default type User only with a column type of CHAR,
NCHAR, VARCHAR, or NVARCHAR, which has a minimum length of 18
characters.

Displaying Table Information
Use the Info option on the TABLE menu to display information about
columns, indexes, access privileges, reference privileges, constraints
(referential, check, or unique), column default values, triggers, status, and
fragmentation strategy of a table. No options exist to display table owners or
information on views.
When you select the Info option on the TABLE menu, the INFO FOR TABLE
screen appears, as Figure 5-44 shows.
INFO FOR TABLE >>

Choose a table with the Arrow Keys, or enter a name, then press Return.
------------------ mydata@mydbserv ------------ Press CTRL-W for Help -------
clients
customer
orders
Figure 5-44. The INFO FOR TABLE Screen
This screen lists the names of tables that exist in the current database. Note
the following items:
v If you are not the table owner, the table name is prefixed by the owner
name, as in june.clients.
v If the list of tables does not fit on one screen, the last entry is an ellipsis
(...). Use the arrow keys to highlight the ellipsis, and the next page of table
names appears.
v If Global Language Support is enabled, the list of table names is sorted

according to the database collation rules defined when the database was
created. Thus, different users using different collating sequences for
DB–Access see the table names in the database listed in the same order.
To request information about tables on a different database server, use the

format database@server:table or database@server:owner.table at the prompt. The
following example requests information on the customer table that dba
created in the accounts database on the database server topend:
INFO FOR TABLE >> accounts@topend:dba.customer
To leave the INFO FOR TABLE screen without requesting table information,
press the Interrupt key. You return to the TABLE menu.

You can select a table in one of the following ways:
v Type the table name and press RETURN.
You must use this method and include the full pathname if you want
information on a table that is not in the current database.
v Use the arrow keys to highlight the table name that you want and press
RETURN.
For example, for the customer table, type customer or use the arrow keys to
highlight it and press RETURN. The INFO menu appears, with customer in the
top line, as Figure 5-45 and Figure 5-46 on page 5-35 show.
INFO - customer: Columns Indexes Privileges References Status ...

Display column names and data types for a table.
----------------- mydata@mydbserv ------------- Press CTRL-W for Help -------
Figure 5-45. The INFO Menu for Displaying Table Information (First Screen)
INFO - customer: ... cOnstraints triGgers Table Fragments Exit

Reference menu and primary, unique, check and defaults options.
------------------ mydata@mydbserv ------------- Press CTRL-W for Help ------
Figure 5-46. The INFO Menu for Displaying Table Information (Second Screen)
The INFO menu has the following options.

Columns Lists data type by column name and “Displaying Column
indicates which columns can contain Information” on page 5-36
a null value
Indexes Describes each index defined for a “Displaying Index Information”
specified table on page 5-39
Privileges Lists the users who have Select, “Displaying Table-Level
Update, Insert, Delete, Index, or Privileges” on page 5-40
Alter privileges for the specified
table
References Lists the users who have the “Displaying References
table-level References privilege for Privileges” on page 5-40
the specified table and the names of
the columns they can reference
Status Lists the table name, owner, row none
size, number of rows and columns,
and creation date of the current
table

cOnstraints Displays the referential, primary, “Displaying Column Constraints
unique, and check constraints, as and Defaults” on page 5-41
well as the default values for the
columns in the specified table
triGgers Displays header and body “Displaying Triggers” on page
information for a specified trigger 5-43
Table Redisplays the INFO FOR TABLE none
menu so that you can select a
different table for examination
Fragments Lists fragmented dbspaces assigned “Displaying Fragmentation
to the table and, for Information” on page 5-44
expression-based fragmentation,
displays the expression assigned to
each dbspace
Exit Returns to the TABLE menu none
Tip: From the CREATE TABLE menu, use Table-options to view extent and
lock mode information, or issue a SELECT statement to list the table
description in the systables system catalog table.
Displaying Column Information
Use the Columns option on the INFO menu to display the following
information for each column of the specified table: the name of the column,
the data type of the column, and whether null values are allowed in the
column.
Figure 5-47 shows the kind of information that you see when you select the
Columns option for the cust_calls table.
Column name Type Nulls
customer_num INTEGER no
call_dtime DATETIME YEAR TO MINUTE yes
user_id CHAR(32) yes
call_code CHAR(1) yes
call_descr CHAR(240) yes
res_dtime DATETIME YEAR TO MINUTE yes
res_descr CHAR(240) yes
Figure 5-47. Displaying Column Information for the cust_calls Table
DB-Access Data Types

The columns in Figure 5-47 show that the cust_calls table consists of all
built-in (standard) data types. The data types available through the CREATE
TABLE menu hierarchy are built-in types.

The built-in types that the Columns option can display are BOOLEAN, BYTE,
CHAR, CHARACTER VARYING, DATE, DATETIME, DECIMAL, FLOAT,
INT8, INTEGER, INTERVAL, MONEY, NCHAR, NVARCHAR, SERIAL,
SERIAL8, SMALLFLOAT, SMALLINT, TEXT, VARCHAR, and user-defined
types.
For descriptions of these data types, see the IBM Informix: Guide to SQL
Reference.
Figure 5-48 shows the display of column information for a table that has
BOOLEAN, INT8, and SERIAL8 columns as well as other built-in data types.
id_num integer yes

yes_or_no boolean yes
int8col int8 yes
serial8col serial8 yes
text_descr char(20) yes
Figure 5-48. Displaying Column Information for a Table with Several Built-in Types
Large Objects: Large objects are built-in data types that store a large amount
of data in a single column. Within a table, large-object data type columns
actually contain pointers to the physical storage spaces where the database
server places the large data objects.
DB–Access can display any of the following large-object data types:

v TEXT, BYTE
Dynamic Server
v CLOB, BLOB
The Columns option displays the specific data type for any column that
contains pointers to large objects. Figure 5-49 shows the display of column
information for a table that has a BYTE column.
id integer yes
binary_col byte yes
Figure 5-49. Displaying Column Information for a BLOB Column

Extended Data Types in Dynamic Server
This section shows how DB–Access displays user-defined and complex data
types for Dynamic Server.
Opaque Data Types: An opaque data type characterizes data that cannot be
represented by any of the built-in types that belong to the database server.
DB–Access can identify and display opaque data types.
For example, suppose you assign an opaque data type called circle_t to a
column named circle_col. The Columns option displays the opaque data type
name in the Type column, as Figure 5-50 shows.
id integer yes
circle_col circle_t yes
Figure 5-50. Displaying Information for a Column with an Opaque Data Type
Collection Types: A collection type contains zero or more elements and is

more specifically defined with one of the following data type names.
Data Type Characteristics
SET An unordered collection of elements in which duplicates are
not allowed
MULTISET An unordered collection of elements in which duplicates are
allowed
LIST An ordered collection of elements in which duplicates are
allowed
DB–Access displays the specific kind of collection type in the Type column.
For example, Figure 5-51 shows the display of a SET data type column named
siblings.
id integer yes
siblings set yes
Figure 5-51. Displaying Information for a Column with a Collection Data Type
Row Types: The Columns option for a table that includes a column with a
row type displays the string Row in the Type column. DB–Access displays
this string whether the column has a named or unnamed row type. Assume
you define row type rectangle_t and assign it to column rect. Figure 5-52

shows that the Columns display for rect returns row rather than the specific
row-type name rectangle_t as the data type.
id integer yes
rect row yes
Figure 5-52. Column Information for a Row Data Type
Displaying Index Information

Indexes option for the cust_calls table. The c_num_dt_ix index is a B-tree
index defined on two columns of the cust_calls table. The c_num_cus_ix
index is a B-tree index defined on a single column of the cust_calls table.
Index name Owner Type/Clstr Access_Method Columns
c_num_dt_ix velma unique/No B-Tree customer_num

call_dtime
c_num_cus_ix velma dupls/No B-Tree customer_num
Figure 5-53. Display of Index Information
The following table shows the meaning of each column in the display.
Display Column Description
Index Name The name of the index
Owner The owner of the index
Type The index type (unique or duplicate)
Clstr Indicates whether the index is clustered. (A
clustered index causes the table to be
physically reordered in the same sequence as
the index.)
Access Method The index access method (such as B-tree or
functional)
Columns The column or columns on which the index is
defined
For further information about the types of indexes available on your database
server, see your IBM Informix: Performance Guide.
Dynamic Server
You can display information for non-B-tree indexes, including indexes based
on user-defined secondary access methods that Dynamic Server permits. For

example, the index shown in Figure 5-54 is based on a Fulltext access method
that a DataBlade module provides.
Index name Owner Type/Clstr Access_Method Columns

text_idx wilma dupls/No Fulltext zone_descr
Figure 5-54. Display of Information for a DataBlade Module Index
For information about user-defined access methods, see your DataBlade

documentation or the documentation for the access method.
Displaying Table-Level Privileges

Figure 5-55 shows the kind of table-level access-privileges information that
you see when you select the Privileges option for the cust_calls table.
User Select Update Insert Delete Index Alter
public All All Yes Yes Yes No
Figure 5-55. Display of Privileges Information
Unless your login is listed separately, you have the privileges given for public
(a general category for all users).
If you want information about database-level privileges, use a SELECT

statement to access the sysusers system catalog table.
Displaying References Privileges
References option for a table that has referential integrity.
User Column References
betty col1
col2
col3
wilma All
public None
Figure 5-56. Display of References Information
This display indicates the following table-level references privileges:

v The user betty can reference columns 1, 2, and 3 of the specified table.
v The user wilma can reference all the columns in the table.
v Users who only have privileges granted to public cannot access any
columns in the table.

Displaying Column Constraints and Defaults
When you select the cOnstraints option from the INFO menu, the
CONSTRAINTS menu appears, as Figure 5-57 shows.
CONSTRAINTS - mytab: Reference Primary Check Unique Defaults Exit

Referenced and referencing options.
------------------mydata@mydbserv-------------- Press CTRL-W for Help -------
Figure 5-57. The CONSTRAINTS Menu
You can select from the following options on the CONSTRAINTS menu.

Reference Shows referential (foreign-key) “Displaying Referential
constraints associated with the Constraints” on page 5-41
specified table
Primary Shows primary-key columns in the Figure 5-61
specified table
Check Shows check constraints on columns Figure 5-62
in the specified table
Unique Lists columns that must contain none
unique data
Defaults Lists columns for which default none
values are defined and the default
value
Exit Returns to the INFO menu none
Displaying Referential Constraints

When you select the Reference option from the CONSTRAINTS menu, the
REFERENCE menu appears, as Figure 5-58 shows.
REFERENCE - mytab: Referencing referenceD Exit

Display foreign key constraints.
-----------------mydata@mydbserv-------------- Press CTRL-W for Help --------
Figure 5-58. The REFERENCE Menu
The REFERENCE menu lets you display the following information:

v Foreign-key (referencing) constraints
v Columns of the current table
v Cascading deletes enabling
v Referenced columns (columns referenced as foreign keys by columns from
another table)

Select the Referencing option on the REFERENCE menu to display the
foreign-key constraints in the current table, as Figure 5-59 shows.
REFERENCE - myaccts: Referenc referenceD Exit

Display referential constraints.
-----------------sub_accounts -----------Press CTRL-W for Help ----------------
Constraint Name Referencing Column Referenced Table Referenced Column CD

r107_13 ref_num sub_accounts acc_num Y
ref_type acc_type
r107_13 ref_num accounts acc_num Y

ref_type acc_type
Figure 5-59. The REFERENCE Menu with Referencing Information
Select the referenceD option on the REFERENCE menu to display other tables
and columns that reference your current columns as foreign keys, as
Figure 5-60 shows.
REFERENCE - myaccts: Referenc referenceD Exit

Display columns which have foreign keys which reference this table.
------------------accounts ------------Press CTRL-W for Help ------------------
Constraint Name Referenced Column Referencing Table Referencing Column CD

r107_13 acc_num sub_accounts ref_acc Y
acc_type ref_type
Figure 5-60. The REFERENCE Menu with Referenced Information
Select Exit to return to the CONSTRAINTS menu.
Sample Primary and Check Constraint Displays

Figure 5-61 shows the columns that form a primary key. This display results
from selecting Primary from the CONSTRAINTS menu.
CONSTRAINTS - mytab: References Primary Check Unique Defaults Exit

Primary key constraints
-----------------mydata@mydbserv------------------ Press CTRL-W for Help ------
constraint1 assembly
partnum
Figure 5-61. The CONSTRAINTS Menu with Primary-Key Constraint Information
Select the Check option on the CONSTRAINTS menu to display the check
constraints placed on columns of the current table, as Figure 5-62 shows.

CONSTRAINTS - mytab: Reference Primary Check Unique Defaults Exit
Display check constraints.
--------------------mydata@mydbserv------------- Press CTRL-W for Help -------
Constraint name Value
cons2 (column1 > (column2 * 100 - 1000 + column1 / 2 -

column2/20 + 40 * 3 - 55 * column2 + 77 * column1))
cons3 column2 > column3
cons4 column3 > 100
Figure 5-62. The CONSTRAINTS Menu with Check-Constraint Information
Displaying Triggers
When you select the triGgers option from the INFO menu, the INFO FOR
TRIGGER screen appears, as Figure 5-63 shows.
INFO FOR TRIGGER>>

Choose a trigger with the Arrow Keys, or enter a name, then press Return.
------------------mydata@mydbserv--------------- Press CTRL-W for Help ------
updrec_t
Figure 5-63. The INFO FOR TRIGGER Screen
Select a trigger from the list of trigger names in the current table. If you do
not want to select a trigger, press the Interrupt key, and you return to the
TABLE INFO menu.
If the header and body information for the selected trigger fit on one screen,
the INFO menu reappears, displaying the trigger information, as Figure 5-64
shows.
INFO - updrec_t: .... triGgers Status Table Exit

Display header and body information for a trigger.
----------------- mystores@dbserver1 ----------- Press CTRL-W for Help ------
create trigger updrec_t

unit_price on stock
referencing old as pre_upd
new as post_upd
(insert into log_record values (stock_num, CURRENT,

pre_upd.unit_price, post_upd.unit_price)) for each row;
Figure 5-64. Display of Trigger Information on the INFO Menu
Figure 5-64 shows the header information for a trigger, which consists of the
CREATE TRIGGER statement and trigger name, the SQL statement that

triggers an event, and the referencing clause. The body of a trigger is the
triggered action. In Figure 5-64, the triggered action is the INSERT statement.
If the trigger does not fit on a single INFO menu screen, use the menu at the
top of the screen as follows:
v Press N, or if the Next option is highlighted, press RETURN to advance to the
next screen of trigger information. Continue to press N or RETURN as needed
to page through the information.
v Select Restart at any time to display the trigger information from the
beginning.
v Select Exit to return to the TABLE menu.
Displaying Fragmentation Information
Fragments option for an indexed table created with a round-robin
fragmentation strategy.
Idx/Tbl Name Dbspace Type Expression
cust dbspace1 T
cust dbspace2 T
cust rootdbs T
cust dbspace1 I
cust dbspace2 I
cust rootdbs I
Figure 5-65. Fragmentation Information where Round-Robin Strategy Applies to Both Table and
Index
Idx/Tbl Name shows the object that was explicitly fragmented.
Dynamic Server
If you use Dynamic Server, the display includes a Type column to indicate
whether the fragment on the line is part of an index or the table data. In
Figure 5-65, the cuts table was created with round-robin strategy, but the
index was created without specifying a strategy. In this case, the indexes are
located in default dbspaces.
Suppose, when creating an index, you use the following statement to apply a
fragmentation strategy:
create index idx on cust(custnum) fragment by expression
custnum < 200 in dbspace1,
custnum > 200 in dbspace2,
remainder in rootdbs;

In this case, the fragmentation display looks like Figure 5-66.
Idx/Tbl Name Dbspace Type Expression
cust dbspace1 T
cust dbspace2 T
cust rootdbs T
idx dbspace1 I (custnum < 200)
idx dbspace2 I (custnum > 200)
idx rootdbs I remainder
Figure 5-66. Fragmentation Information Where Table and Index Have Different Strategies
Idx/Tbl Name shows the index name because the fragmentation was explicitly
applied to the index.
Dropping a Table
Use the Drop option on the TABLE menu to delete an existing table schema
from the database. Press the D key, or highlight Drop and press RETURN. The
DROP TABLE screen appears, as Figure 5-67 shows.
DROP TABLE >>

Enter the table name you wish to drop from the database.
----------------- mydata@mydbserv --------------- Press CTRL-W for Help -----
clients
customer
orders
Figure 5-67. The DROP TABLE Screen
This screen lists the names of tables that exist in the current database. You can
delete a table in one of the following ways:
v Type the table name and press RETURN. You must use this method and
include the full pathname if you want to delete a table that is not in the
current database.
v Use the arrow keys to highlight the name of the table that you want to
delete from the database and press RETURN.
To leave the DROP TABLE screen without deleting a table, press the Interrupt
key. You return to the TABLE menu.
Warning: When you delete a table, you delete both the table and all the data
it contains.

After you select a table to delete, DB–Access displays the CONFIRM menu,
which asks for confirmation before it deletes the table, as Figure 5-68 shows.
CONFIRM: No Yes

-----------------mydata@mydbserv------------------ Press CTRL-W for Help ------
clients
customer
orders
Figure 5-68. The CONFIRM Menu
The default is No to prevent you from deleting a table. You must explicitly
delete a table. Thus, if you want to delete the highlighted table, press the Y
key or use the right arrow key to highlight Yes and press RETURN. DB–Access
deletes the table.
Moving a Table (XPS)

Use the Move option on the TABLE menu to move an existing table from the
current database to another database. Press the M key, or highlight Move and
press RETURN. The MOVE TABLE screen appears, as Figure 5-69 shows
MOVE TABLE >>

Select or enter the table to move.
clients
customer
orders
Figure 5-69. The MOVE TABLE Screen
This screen lists the names of tables that exist in the current database. You can
move a table in one of the following ways:
v Type the table name and press RETURN.
v Use the arrow keys to highlight the name of the table that you want to
move from the database and press RETURN.
To leave the MOVE TABLE screen without moving a table, you can press the
Interrupt key to return to the TABLE menu.
Note: You cannot use the Move option on the TABLE menu to move a table
that has dependency objects such as views or referential constraints
associated with it. To move such a table use the SQL Command MOVE

with the CASCADE mode, which lets you drop those dependent objects
and then the Move table operation can succeed.
After you select a table to move, DB-Access displays the ″SELECT

DATABASE″ screen, which appears as Figure 5-70 shows.
SELECT DATABASE >>

Select or enter a database to move the table to.
mydata@mydbserv
demodb@mydbserv
personnel@mynewdb
This screen lists the names of databases that are available in the current
instance. You can only move a table to a database that is in the same instance
as the database you are moving the table from. You can select the database in
one of these ways:
v Use the arrow keys to highlight the name of the database in which you
want to move a table and press RETURN.
After you select a database to move the table to, DB-Access displays the
″RENAME″ screen, you can use this to rename the table. The ″RENAME″
screen appears as Figure 5-71 shows.
RENAME >>
Enter a new name for the table or press enter to keep the same name.
Figure 5-71. The RENAME Screen
To keep the original table and owner name, press RETURN at the prompt
without typing anything. To rename the table, type the name at the prompt
and press RETURN.
You can also change the owner of the table by including the name of the
owner in the new table name. For example to change the table name to
neworders and make the user john the owner you would type this:
john.neworders

After you press RETURN, DB-Access displays the CONFIRM Menu, which
asks for confirmation before it moves the table, as Figure 5-72 shows.
CONFIRM: No Yes >>

No, I do not want to move it.
Figure 5-72. The CONFIRMATION Screen
The default is No to prevent you from accidentally moving a table. To move

the highlighted table, press the Y key or use the right arrow key to highlight
Yes and press RETURN. DB–Access then moves the table.
Related Manuals
Have the following manuals available while you create or alter a table schema
or structure:
v For information on how to name a table or a column, see the IBM Informix:
Guide to SQL Syntax.
v For information about data types, see the IBM Informix: Guide to SQL
Reference.
v For information about fragmentation and storage space allocation, see your
IBM Informix: Administrator's Guide.
v For recommendations concerning fragmentation, indexes, and extent size,
see your IBM Informix: Performance Guide.
v For information about nondefault locale names and character data types
(CHAR, VARCHAR, NCHAR, NVARCHAR, and TEXT), see the
IBM Informix: GLS User's Guide.
v For information on how to design, implement, and manage your database,
see the IBM Informix: Database Design and Implementation Guide.

Chapter 6. The Connection and Session Options
Choosing the Connection Option . . . . . . . . . . . . . . . . . . . . . . . 6-1
Connecting to a Database Environment . . . . . . . . . . . . . . . . . . . . 6-2
Permissions Needed . . . . . . . . . . . . . . . . . . . . . . . . . . 6-3
Implicit Closures . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-5
Disconnecting from a Database Environment . . . . . . . . . . . . . . . . . . 6-5
Transaction Processing . . . . . . . . . . . . . . . . . . . . . . . . . . 6-5
Choosing the Session Option . . . . . . . . . . . . . . . . . . . . . . . . . 6-6
In This Chapter
This chapter describes the Connection and Session options on the main menu.
Use the Connection option if you want to connect to a specific database server
and database or explicitly disconnect from the current database environment.
Use the Session option to display information about the current DB–Access
session.
For the GLS considerations that apply to establishing a connection between a

client application, such as DB–Access, and a database, see the IBM Informix:
GLS User's Guide. The database server examines the client locale information
passed by the client, verifies the database locale, and determines the
server-processing locale for transferring data between the client and the
database.
Choosing the Connection Option

From the main menu, press the C key (or highlight the Connection option and
press RETURN) to display the CONNECTION menu, as Figure 6-1 shows.
CONNECTION: Connect Disconnect Exit

Connect to a database environment.
Figure 6-1. The CONNECTION Menu

The CONNECTION menu displays the following options.

Connect Connects to a database environment “Connecting to a Database
Environment” on page 6-2
Disconnect Disconnects from the current “Disconnecting from a Database
database environment Environment” on page 6-5
Exit Returns to the DB-Access main none
menu
Connecting to a Database Environment

To connect to an existing database server, choose the Connect option from the
CONNECTION menu. DB–Access displays a list of available database servers
and prompts you to make a selection. Select a database server and DB–Access
prompts you to enter a user name, as Figure 6-2 shows.
USER NAME >>

Enter the login name you want to use for this connection.
coral
cowry
seahorse
starfish
Figure 6-2. The USER NAME Prompt Screen
If you do not specify a user identifier on the USER NAME screen and press
RETURN, you see the standard SELECT DATABASE screen listing databases on
the chosen database server.
If you enter the login name that you want DB–Access to use when connecting
to the target database server, DB–Access displays the PASSWORD screen, as
Figure 6-3 shows.

PASSWORD >>
Enter the password associated with the user identifier.
-------------------------------------------------- Press CTRL-W for Help -----
coral
cowry
seahorse
starfish
Figure 6-3. The PASSWORD Screen
From the PASSWORD screen, enter a password associated with the user
identifier or press RETURN if you do not want to enter a password. For security
reasons, the password that you enter on the screen is not displayed.
Tip: If you press CRTL-C on the USER NAME screen, DB–Access might try to
connect to the specified database server rather than interrupt the session.
This situation occurs because pressing CRTL-C on this screen is the
equivalent of using the current user name.
If the user identifier and password combination are valid, you connect to the
target database server. You can then select a database from that database
server. The SELECT DATABASE SERVER screen appears, as Figure 6-4 shows.
SELECT DATABASE SERVER>>

Select a server with the Arrow Keys, or enter a name, then press Return.
--------------------------------------------- Press CTRL-W for Help --------
coral
cowry
seahorse
starfish
Figure 6-4. The SELECT DATABASE SERVER Screen
The SELECT DATABASE SERVER screen shows an alphabetical list of

database servers from the $INFORMIXDIR/etc/sqlhosts file on UNIX or the
sqlhosts entry in the registry on Windows NT. The first entry is highlighted.
Press RETURN to select that database server or select a different database server.
If entries span more than one page, use the arrow keys to move the cursor to
highlight the ellipses (...), and the next page of database servers appears.
Permissions Needed
To access a specific database, you must have permission. If you do not have
permission to connect to the specified database server, an error message
Chapter 6. The Connection and Session Options 6-3

appears. Select a different database server for which you have permission or
ask your database server administrator for permission to connect to the
database server.
If you have the correct permissions for the specified database server, you are
prompted to specify a database to use on that database server.
When the SELECT DATABASE screen appears, the name of the specified
database server is highlighted, as Figure 6-5 shows.
SELECT DATABASE >>

Select a database with the Arrow Keys, or enter a name, then press Return.
---------------------- @coral ----------------- Press CTRL-W for Help --------
borabora@coral
huahine@coral
moorea@corala
The SELECT DATABASE screen alphabetically lists all available databases on

the specified database server. The database list on the SELECT DATABASE
screen depends on the current connection. For example:
v If no current connection exists or the current connection is an implicit
default connection, all the databases listed in the DBPATH environment
variable setting are displayed.
v If a current explicit connection exists, all the databases in the DBPATH that
pertain to the current server are displayed.
For information on setting the DBPATH environment variable, see the

IBM Informix: Guide to SQL Reference.
To select a database, type the database name or use the arrow keys to
highlight the name of a database, then press RETURN.
If you enter the CONNECT menu with a current connection, and the new
connection succeeds, DB–Access disconnects from the previous environment
and closes any databases that belong to that environment. For more
information, see “Implicit Closures” on page 6-5.
If you enter the name of a nonexistent database or a database that DB–Access

cannot locate, an error message appears.
To leave the SELECT DATABASE screen without selecting a database, press

the Interrupt key. You return to the CONNECTION menu, but your database
server connection is not severed.

Implicit Closures
DB–Access closes any open connections or databases when you connect to a
new environment, in the following situations:
v When you connect to a new database environment without explicitly
disconnecting from the current one, DB–Access performs an implicit
disconnect and the database closes.
v When you connect to a database@server and then close the database, the
database server remains connected.
v When you connect to a database server, open a database, and then close the
database, the database server remains connected.
v If you open a database and then try to connect to a database server,
DB–Access performs an implicit disconnect and closes the database.
Only one connection is allowed. You must disconnect from the database
server associated with the open database or close the database before you
can connect to another database server.
If DB–Access needs to close a database that still has outstanding transactions,

it prompts you to commit or rollback those transactions, as described in
“Transaction Processing” on page 6-5.
Disconnecting from a Database Environment
To disconnect from the current database server and close the current database,
choose the Disconnect option from the CONNECTION menu. The
DISCONNECT confirmation menu appears, as Figure 6-6 shows.
DISCONNECT: Yes No
Disconnect from the current database environment.
------------------- moorea@coral --------------- Press CTRL-W for Help -------
Figure 6-6. The DISCONNECT Confirmation Menu
When you select the Disconnect option from the CONNECTION menu, you
must confirm your decision on the DISCONNECT confirmation screen. The
following two options are available:
v To confirm that you want to disconnect, press RETURN with the default Yes
option highlighted. DB–Access disconnects from the database server and
closes the database.
v If you do not want to disconnect, press the N key or use the right arrow key
to highlight No, and press RETURN. DB–Access returns to the CONNECTION
menu.
Transaction Processing
A database that has transaction logging prompts you to confirm or roll back
any transactions when you explicitly disconnect from the current database
environment or when you connect to another environment, which forces

DB–Access to close an open database. The TRANSACTION menu appears, as
Figure 6-7 shows.
TRANSACTION: Commit Rollback

Commit the current transaction.
---------------- moorea@coral ---------------- Press CTRL-W for Help --------
Figure 6-7. The TRANSACTION Menu for Databases with Transactions
The TRANSACTION menu ensures that you either commit or roll back an
active transaction before you close the current database. You have the
following menu options:
v The default is Commit. Press RETURN, and DB–Access commits the
transaction and closes the database.
v If you want to roll back the transaction, use an arrow key to move the
highlight to the Rollback option. Press RETURN and DB–Access rolls back the
transaction and closes the database.
Warning: Select an option carefully. You might commit transactions that you
do not want if you select Commit. You will lose any new
transactions if you select Rollback.
If you press the Interrupt key, DB–Access displays the DATABASE menu
without committing or rolling back the transaction.
Choosing the Session Option

From the main menu, press the S key or highlight the Session option and
press RETURN. The DB–Access main menu remains on the screen and
information about the current DB–Access session appears. For example,
Figure 6-8 shows the following information:
v The name of the database server for this session is coral.
v The database server type is Dynamic Server. This server type refers to all
Informix database servers that are based on dynamic scalable architecture.
For example, you would see Dynamic Server if you use Dynamic Server.
v The server is connected to a host named carrots.
v The server is a multithreaded server.

DBACCESS: Query-language Connection Database Table Session Exit
Retrieve information about the current DB-Access session.
-------------------- @coral --------------- Press CTRL-W for Help --------
Server
coral
OnLine
Connected to host carrots
Multi-threaded
Figure 6-8. Main Menu with Sample Session Information for a Dynamic Server Instance
Native Language Support
If you are running a legacy database that supports Native Language Support,
the Session option shows the collating sequence and character type.
End of Native Language Support
The Session option does not display Global Language Support attributes, but
you can use the method shown in “Retrieving Nondefault Locale
Information” on page 4-8 to obtain these settings.
To exit from the Session information screen, select another option on the main
menu.

Appendix A. How to Read Online Help for SQL Statements
This appendix shows the conventions that are used to represent the syntax of
SQL statements in DB–Access online help screens. You can request online help
for SQL statements in either of the following ways:
v Highlight the New, Modify, or Use-editor options on the SQL menu and
press CTRL-W.
v Press CTRL-W while you are on the NEW or MODIFY screens of the SQL
menu.
The form of the syntax diagrams that appears when you request online Help
for SQL statements in DB–Access is different from the syntax diagrams in the
The conventions and rules governing SQL statement syntax in DB–Access

online help screens are described in the following list.
ABC Any term in an SQL statement displayed in uppercase letters
is a keyword. Type keywords exactly, disregarding case, as
shown in the following example:
CREATE SYNONYM synonym-name
This syntax indicates you must type the keywords CREATE

SYNONYM or create synonym without adding or deleting
spaces or letters.
abc Substitute a value for any term that appears in lowercase
letters. In the previous example, you should substitute a value
for synonym-name.
() Type any parentheses as shown. They are part of the syntax of
an SQL statement and are not special symbols.
[] Do not type brackets as part of a statement. They surround
any part of a statement that is optional. For example:
CREATE [TEMP] TABLE
This syntax indicates that you can type either CREATE TABLE
or CREATE TEMP TABLE.
| The vertical bar indicates a choice among several options. For
example:
[VANILLA | CHOCOLATE [MINT] | STRAWBERRY]
© Copyright IBM Corp. 1996, 2004 A-1

This syntax indicates that you can enter either VANILLA,
CHOCOLATE, or STRAWBERRY and that, if you enter
CHOCOLATE, you can also enter MINT.
{} When you must choose only one of several options, the
options are enclosed in braces and are separated by vertical
bars. For example:
{GUAVA | MANGO | PASSIONFRUIT}
This syntax indicates that you must enter either GUAVA,

MANGO, or PASSIONFRUIT, but you cannot enter more than
one choice.
... An ellipsis indicates that you can enter an indefinite number
of additional items, such as the one immediately preceding
the ellipsis. For example:
old-column-name
...
This syntax indicates that you can enter a series of existing

column names after the first one.
The IBM Informix: Guide to SQL Syntax contains more detailed syntax
diagrams, as well as instructions for interpreting the diagram format used in
that book.
For a general explanation of how to use online Help in DB–Access, see “Using
the HELP Screen” on page 2-5.
A-2 IBM Informix DB-Access User’s Guide

Appendix B. Demonstration SQL
This appendix shows the contents of the various command files that are
available with DB–Access. These command files all have the extension .sql
when displayed from the command line but appear without the extension on
the SQL CHOOSE menu.
Keywords in these command files are shown in uppercase letters to make the
SQL statements easier to read. Keywords in the actual command files are
lowercase.
Important: Although the command files are listed alphabetically in this

appendix, you cannot execute the command files that create tables
in that order without causing errors. The order in which the tables
are created is very important because of the referential constraints
that link those tables.
When you select the Choose option on the SQL menu, the CHOOSE screen
appears. It displays a list of the command files that you can access, similar to
the display that Figure B-1 shows. These files are included with the
stores_demo database. Other .sql files are discussed later in this appendix.
CHOOSE >>
Choose a command file with the Arrow Keys, or enter a name, then press Return.
----------------- stores_demo @dbserver1 -------------- Press CTRL-W for Help ------
c_cat c_stores_demo del_stock sel_ojoin3
Figure B-1. Command Files Listed on the CHOOSE Screen
If you do not see the command files included with your demonstration
database, check the following:
© Copyright IBM Corp. 1996, 2004 B-1

1. Did you copy the demonstration SQL command files to your current
directory when you ran the demonstration database initialization script? If
not, you can rerun the initialization script to copy them.
2. Did you start DB–Access from the directory in which you installed the
demonstration SQL command files? If not, exit DB–Access, change to the
appropriate directory, and start DB–Access again.
For instructions on running the initialization script, copying command files,

and starting DB–Access, refer to Chapter 1, “Getting Started with DB-Access,”
on page 1-1.
Use these command files with DB–Access for practice with SQL and the
demonstration database. You can rerun the demonstration database
initialization script whenever you want to refresh the database tables and SQL
files.
SQL Files for the Relational Database Model

The SQL files described in this section are included with the stores_demo
demonstration database and conform to the stores_demo schema. For more
information about the stores_demo schema, refer to the IBM Informix: Guide to
SQL Reference.
alt_cat.sql
The following command file alters the catalog table. It drops the existing
constraint aa on the catalog table and adds a new constraint, ab, which
specifies cascading deletes. You can use this command file and then the
del_stock.sql command file for practice with cascading deletes on a database
with logging.
ALTER TABLE catalog DROP CONSTRAINT aa;
ALTER TABLE catalog ADD CONSTRAINT

(FOREIGN KEY (stock_num, manu_code) REFERENCES stock
ON DELETE CASCADE CONSTRAINT ab);
c_calls.sql
The following command file creates the cust_calls table:
CREATE TABLE cust_calls
(
customer_num INTEGER,
call_dtime DATETIME YEAR TO MINUTE,
user_id CHAR(18) DEFAULT USER,
call_code CHAR(1),
call_descr CHAR(240),
res_dtime DATETIME YEAR TO MINUTE,
res_descr CHAR(240),
PRIMARY KEY (customer_num, call_dtime),
FOREIGN KEY (customer_num) REFERENCES customer (customer_num),
FOREIGN KEY (call_code) REFERENCES call_type (call_code)
);
B-2 IBM Informix DB-Access User’s Guide

c_cat.sql
The following command file creates the catalog table. It contains a constraint,
aa, which allows you to practice with cascading deletes by running the SQL
statements in the alt_cat.sql and del_stock.sql command files on a database
with logging.
CREATE TABLE catalog
(
catalog_num SERIAL(10001),
stock_num SMALLINT NOT NULL,
manu_code CHAR(3) NOT NULL,
cat_descr TEXT,
cat_picture BYTE,
cat_advert VARCHAR(255, 65),
PRIMARY KEY (catalog_num),
FOREIGN KEY (stock_num, manu_code) REFERENCES stock
CONSTRAINT aa
);
c_custom.sql
The following command file creates the customer table:
CREATE TABLE customer
(
customer_num SERIAL(101),
fname CHAR(15),
lname CHAR(15),
company CHAR(20),
address1 CHAR(20),
address2 CHAR(20),
city CHAR(15),
state CHAR(2),
zipcode CHAR(5),
phone CHAR(18),
PRIMARY KEY (customer_num)
);
c_index.sql
The following command file creates an index on the zipcode column of the
customer table:
CREATE INDEX zip_ix ON customer (zipcode);
c_items.sql
The following command file creates the items table:
CREATE TABLE items
(
item_num SMALLINT,
order_num INTEGER,
stock_num SMALLINT NOT NULL,
manu_code CHAR(3) NOT NULL,
quantity SMALLINT CHECK (quantity >= 1),
total_price MONEY(8),
PRIMARY KEY (item_num, order_num),
Appendix B. Demonstration SQL B-3

FOREIGN KEY (order_num) REFERENCES orders (order_num),
FOREIGN KEY (stock_num, manu_code) REFERENCES stock
(stock_num, manu_code)
);
c_manuf.sql
The following command file creates the manufact table:
CREATE TABLE manufact
(
manu_code CHAR(3),
manu_name CHAR(15),
lead_time INTERVAL DAY(3) TO DAY,
PRIMARY KEY (manu_code)
);
c_orders.sql
The following command file creates the orders table:
CREATE TABLE orders
(
order_num SERIAL(1001),
order_date DATE,
customer_num INTEGER NOT NULL,
ship_instruct CHAR(40),
backlog CHAR(1),
po_num CHAR(10),
ship_date DATE,
ship_weight DECIMAL(8,2),
ship_charge MONEY(6),
paid_date DATE,
PRIMARY KEY (order_num),
FOREIGN KEY (customer_num) REFERENCES customer (customer_num)
);
c_proc.sql
The following command file creates an SPL routine. It reads the full name and
address of a customer and takes a last name as its only argument.
This routine shows the legacy use of CREATE PROCEDURE.
Dynamic Server
To conform with the SQL standard preferred with Dynamic Server, define a
function if you want to return values from a routine.
CREATE PROCEDURE read_address (lastname CHAR(15))
RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15), CHAR(2), CHAR(5);
DEFINE p_fname, p_city CHAR(15);
DEFINE p_add CHAR(20);
DEFINE p_state CHAR(2);
DEFINE p_zip CHAR(5);
SELECT fname, address1, city, state, zipcode
INTO p_fname, p_add, p_city, p_state, p_zip
FROM customer

WHERE lname = lastname;
RETURN p_fname, lastname, p_add, p_city, p_state, p_zip;
END PROCEDURE;
c_state
The following command file creates the state table:
CREATE TABLE state
(
code CHAR(2),
sname CHAR(15),
PRIMARY KEY (code)
);
c_stock.sql
The following command file creates the stock table:
CREATE TABLE stock
(
stock_num SMALLINT,
manu_code CHAR(3),
description CHAR(15),
unit CHAR(4),
PRIMARY KEY (stock_num, manu_code),
);
c_stores.sql
The following command file creates the stores_demo database:
CREATE DATABASE stores_demo;
c_table.sql
The following command file creates a database named restock and then
creates a custom table named sports in that database:
CREATE DATABASE restock;
CREATE TABLE sports

(
catalog_no SERIAL UNIQUE,
stock_no SMALLINT,
mfg_code CHAR(5),
mfg_name CHAR(20),
phone CHAR(18),
descript VARCHAR(255)
);
c_trig.sql
The following command file creates a table named log_record and then
creates a trigger named upqty_i, which updates it:

CREATE TABLE log_record
(item_num SMALLINT,
ord_num INTEGER,
username CHARACTER(8),
update_time DATETIME YEAR TO MINUTE,
old_qty SMALLINT,
new_qty SMALLINT);
CREATE TRIGGER upqty_i

UPDATE OF quantity ON items
REFERENCING OLD AS pre_upd
NEW AS post_upd
FOR EACH ROW(INSERT INTO log_record
VALUES (pre_upd.item_num, pre_upd.order_num, USER, CURRENT,
pre_upd.quantity, post_upd.quantity));
Extended Parallel Server does not support triggers.

c_type.sql
The following command file creates the call_type table:
CREATE TABLE call_type
(
call_code CHAR(1),
code_descr CHAR(30),
PRIMARY KEY (call_code)
);
c_view1.sql
The following command file creates a view called custview on a single table
and grants privileges on the view to public. It includes the WITH CHECK
OPTION keywords to verify that any changes made to underlying tables
through the view do not violate the definition of the view.
CREATE VIEW custview (firstname, lastname, company, city) AS
SELECT fname, lname, company, city
FROM customer
WHERE city = ’Redwood City’
WITH CHECK OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE

ON custview
TO public;
c_view2.sql
The following command file creates a view on the orders and items tables:
CREATE VIEW someorders (custnum,ocustnum,newprice) AS
SELECT orders.order_num, items.order_num,
items.total_price*1.5
FROM orders, items
WHERE orders.order_num = items.order_num
AND items.total_price > 100.00;

d_proc.sql
The following command file drops the SPL routine that the c_proc.sql
command file created:
DROP PROCEDURE read_address;
d_trig.sql
The following command file drops the trigger that the c_trig.sql command file
created:
DROP TRIGGER upqty_i;
d_view.sql
The following command file drops the view named custview that the
c_view1.sql command file created:
DROP VIEW custview;
del_stock.sql
The following command file deletes rows from the stock table where the stock
number is 102. This delete will cascade to the catalog table (although the
related manufacturer codes will remain in the manufact table). The
del_stock.sql command file can be used following the alt_cat.sql command
file for practice with cascading deletes on a database with logging.
DELETE FROM stock WHERE stock_num = 102;
After running the SQL statements in the alt_cat.sql and del_stock.sql

command files, issue the following query on the catalog table to verify that
the rows were deleted:
SELECT * FROM catalog WHERE stock_num = 102;
The stores_demo database has been changed. You might want to rerun the
dbaccessdemo script to rebuild the original database.
ins_table.sql
The following command file inserts one row into the sports table that the
c_table.sql command file created:
INSERT INTO sports
VALUES (0,18,’PARKR’, ’Parker Products’, ’503-555-1212’,
’Heavy-weight cotton canvas gi, designed for aikido or
judo but suitable for karate. Quilted top with side ties,
drawstring waist on pants. White with white belt.
Pre-washed for minimum shrinkage. Sizes 3-6.’);
opt_disk.sql
The following command file provides an example of a SELECT statement on
an optical-disc subsystem. It includes the read-only family() and volume()
operators that support optical storage. (This is available only with the Optical
Subsystem.)

The query generates a list of the volumes that contain pictures of bicycle
helmets. One row of output (family, volume) is generated for each data row
that contains a picture of a bicycle helmet. The family() operator returns the
name of the optical family where an optical blob column is stored, and
volume() returns the number of the volume where an optical blob column is
stored. These functions are valid only for data stored on optical media.
SELECT family(cat_picture), volume(cat_picture)
FROM catalog
WHERE stock_num = 110;
sel_agg.sql
The SELECT statement in the following command file queries on table data
using aggregate functions. It combines the aggregate functions MAX and MIN
in a single statement.
SELECT MAX (ship_charge), MIN (ship_charge)
FROM orders;
sel_all.sql
The following example command file contains all seven SELECT statement
clauses that you can use in the Informix implementation of interactive SQL.
This SELECT statement joins the orders and items tables. It also uses display
labels, table aliases, and integers as column indicators; groups and orders the
data; and puts the results into a temporary table.
SELECT o.order_num, SUM (i.total_price) price,
paid_date - order_date span
FROM orders o, items i
WHERE o.order_date > ’01/01/90’
AND o.customer_num > 110
AND o.order_num = i.order_num
GROUP BY 1, 3
HAVING COUNT (*) < 5
ORDER BY 3
INTO TEMP temptab1;
sel_group.sql
The following example command file includes the GROUP BY and HAVING
clauses. The HAVING clause usually complements a GROUP BY clause by
applying one or more qualifying conditions to groups after they are formed,
which is similar to the way the WHERE clause qualifies individual rows. (One
advantage to using a HAVING clause is that you can include aggregates in
the search condition; you cannot include aggregates in the search condition of
a WHERE clause.)
Each HAVING clause compares one column or aggregate expression of the

group with another aggregate expression of the group or with a constant. You
can use the HAVING clause to place conditions on both column values and
aggregate values in the group list.

SELECT order_num, COUNT(*) number, AVG (total_price) average
FROM items
GROUP BY order_num
HAVING COUNT(*) > 2;
sel_join.sql
The following example command file uses a simple join on the customer and
cust_calls tables. This query returns only those rows that show the customer
has made a call to customer service.
SELECT c.customer_num, c.lname, c.company,
c.phone, u.call_dtime, u.call_descr
FROM customer c, cust_calls u
WHERE c.customer_num = u.customer_num;
sel_ojoin1.sql
The following example command file uses a simple outer join on two tables.
The use of the keyword OUTER in front of the cust_calls table makes it the
subservient table. An outer join causes the query to return information on all
customers, even if they do not make calls to customer service. All rows from
the dominant customer table are retrieved, and null values are assigned to
corresponding rows from the subservient cust_calls table.
SELECT c.customer_num, c.lname, c.company,
c.phone, u.call_dtime, u.call_descr
FROM customer c, OUTER cust_calls u
WHERE c.customer_num = u.customer_num;
sel_ojoin2.sql
The following example command file creates an outer join, which is the result
of a simple join to a third table. This second type of outer join is called a
nested simple join.
This query first performs a simple join on the orders and items tables,
retrieving information on all orders for items with a manu_code of KAR or
SHM. It then performs an outer join, which combines this information with
data from the dominant customer table. An optional ORDER BY clause
reorganizes the data.
SELECT c.customer_num, c.lname, o.order_num,
i.stock_num, i.manu_code, i.quantity
FROM customer c, OUTER (orders o, items i)
WHERE c.customer_num = o.customer_num
AND manu_code IN (’KAR’, ’SHM’)
ORDER BY lname;
sel_ojoin3.sql
The following example SELECT statement is the third type of outer join,
known as a nested outer join. It queries on table data by creating an outer join,
which is the result of an outer join to a third table.

This query first performs an outer join on the orders and items tables,
retrieving information on all orders for items with a manu_code of KAR or
SHM. It then performs an outer join, which combines this information with
data from the dominant customer table. This query preserves order numbers
that the previous example eliminated, returning rows for orders that do not
contain items with either manufacturer code. An optional ORDER BY clause
reorganizes the data.
SELECT c.customer_num, lname, o.order_num,
stock_num, manu_code, quantity
FROM customer c, OUTER (orders o, OUTER items i)
AND manu_code IN (’KAR’, ’SHM’)
ORDER BY lname;
sel_ojoin4.sql
The following example queries on table data using the fourth type of outer
join. This query shows an outer join, which is the result of an outer join of
each of two tables to a third table. In this type of outer join, join relationships
are possible only between the dominant table and subservient tables.
This query individually joins the subservient tables orders and cust_calls to
the dominant customer table but does not join the two subservient tables. (An
INTO TEMP clause selects the results into a temporary table.)
SELECT c.customer_num, lname, o.order_num,
order_date, call_dtime
FROM customer c, OUTER orders o, OUTER cust_calls x
AND c.customer_num = x.customer_num
INTO temp service;
sel_order.sql
The following example uses the ORDER BY and WHERE clauses to query. In
this SELECT statement, the comparison ’bicycle%’ (LIKE condition, or
’bicycle*’ for a MATCHES condition) specifies the letters bicycle followed by
any sequence of zero or more characters. It narrows the search further by
adding another comparison condition that excludes a manu_code of PRC.
SELECT * FROM stock
WHERE description LIKE ’bicycle%’
AND manu_code NOT LIKE ’PRC’
ORDER BY description, manu_code;
sel_sub.sql
The following example uses a subquery to query. This self-join uses a
correlated subquery to retrieve and list the 10 highest-priced items ordered.
SELECT order_num, total_price
FROM items a
WHERE 10 >

(SELECT COUNT (*)
FROM items b
WHERE b.total_price < a.total_price)
ORDER BY total_price;
sel_union.sql
The following example uses the UNION clause to query on data in two tables.
The compound query performs a union on the stock_num and manu_code
columns in the stock and items tables. The statement selects items that have a
unit price of less than $25.00 or that have been ordered in quantities greater
than three, and it lists their stock_num and manu_code.
SELECT DISTINCT stock_num, manu_code
FROM stock
WHERE unit_price < 25.00
UNION
SELECT stock_num, manu_code

FROM items
WHERE quantity > 3;
upd_table.sql
The following example updates the sports table that the c_table.sql command
file created:
UPDATE sports
SET phone = ’808-555-1212’
WHERE mfg_code = ’PARKR’;
SQL Files for the Dimensional Database Model

The sales_demo database illustrates a dimensional schema for
data-warehousing applications. This database model alters the stores_demo
schema and data. The success of the files in this section requires two
prerequisites:
v You must first create a stores_demo database with the following command:
dbaccessdemo -log
v The createdw.sql and loaddw.sql files must be in the same directory as the
files with extension .unl that the loaddw.sql uses.
createdw.sql
This file creates the new sales_demo database with logging and then creates
tables within that database. It contains the following statements:
create database sales_demo with log;
create table product (

product_code integer,
product_name char(31),
vendor_code char(3),
vendor_name char(15),

product_line_code smallint,
product_line_name char(15));
create table customer (

company_name char(20));
create table sales (

district_code smallint,
time_code integer,
product_code integer,
units_sold smallint,
revenue money (8,2),
cost money (8,2),
net_profit money(8,2));
create table time

(
time_code int,
order_date date,
month_code smallint,
month_name char(10),
quarter_code smallint,
quarter_name char(10),
year integer
);
create table geography (

district_code serial,
district_name char(15),
state_code char(2),
state_name char(18),
region smallint);
loaddw.sql
This file contains the commands necessary to load data from two sources:
v The files with the extension .unl in your demonstration directory
v Data selected from the stores_demo database
These SQL statements in loaddw.sql accomplish these actions:

connect to "stores_demo ";
load from "add_orders.unl"
insert into stores_demo :orders;
load from ’add_items.unl’
insert into stores_demo :items;
connect to "sales_demo";
load from ’costs.unl’
insert into cost;
load from ’time.unl’
insert into time;

insert into geography(district_name, state_code, state_name)
select distinct c.city, s.code, s.sname
from stores_demo :customer c, stores_demo :state s
where c.state = s.code;
update geography -- converts state_code values to region values
set region = 1
where state_code = "CA";
update geography
set region = 2
where state_code <> "CA";
insert into customer (customer_code, customer_name, company_name)

select c.customer_num, trim(c.fname) || " " || c.lname, c.company
from stores_demo :customer c;
insert into product (product_code, product_name, vendor_code,

vendor_name, product_line_code, product_line_name)
select a.catalog_num,
trim(m.manu_name) || " "|| s.description,
m.manu_code, m.manu_name, s.stock_num, s.description
from stores_demo :catalog a, stores_demo :manufact m,
stores_demo :stock s
where a.stock_num = s.stock_num and
a.manu_code = s.manu_code and
s.manu_code = m.manu_code;
insert into sales (customer_code, district_code,
time_code, product_code,
units_sold, revenue, cost, net_profit)
select c.customer_num, g.district_code, t.time_code, p.product_code,
SUM(i.quantity), SUM(i.total_price),
SUM(i.quantity * x.cost),
SUM(i.total_price) - SUM(i.quantity * x.cost)
from stores_demo :customer c, geography g, time t,
product p,
stores_demo :items i, stores_demo :orders o, cost x
where c.customer_num = o.customer_num and
o.order_num = i.order_num and
p.product_line_code = i.stock_num and
p.vendor_code = i.manu_code and
t.order_date = o.order_date and
p.product_code = x.product_code and
c.city = g.district_name
GROUP BY 1,2,3,4;
connect to "stores_demo ";

load from ’add_orders.unl’
insert into stores_demo :orders;
load from ’add_items.unl’
insert into stores_demo :items;
connect to "sales_demo";
load from ’costs.unl’
insert into cost;
load from ’time.unl’
insert into time;
insert into geography(district_name, state_code, state_name)

select distinct c.city, s.code, s.sname
from stores_demo :customer c, stores_demo :state s
where c.state = s.code;
update geography -- converts state_code values to region values
set region = 1

where state_code = "CA";
update geography
set region = 2
where state_code <> "CA";
insert into customer (customer_code, customer_name, company_name)

select c.customer_num, trim(c.fname) || " " || c.lname, c.company
from stores_demo :customer c;
insert into product (product_code, product_name, vendor_code,

vendor_name, product_line_code, product_line_name)
select a.catalog_num,
trim(m.manu_name) || " " || s.description,
m.manu_code, m.manu_name, s.stock_num, s.description
from stores_demo :catalog a, stores_demo :manufact m,
stores_demo :stock s
where a.stock_num = s.stock_num and
a.manu_code = s.manu_code and
s.manu_code = m.manu_code;
insert into sales (customer_code, district_code,

time_code, product_code,
units_sold, revenue, cost, net_profit)
select c.customer_num, g.district_code, t.time_code, p.product_code,
SUM(i.quantity), SUM(i.total_price),
SUM(i.quantity * x.cost),
SUM(i.total_price) - SUM(i.quantity * x.cost)
from stores_demo :customer c, geography g, time t, product p,
stores_demo :items i, stores_demo :orders o, cost x
where c.customer_num = o.customer_num and
o.order_num = i.order_num and
p.product_line_code = i.stock_num and
p.vendor_code = i.manu_code and
t.order_date = o.order_date and
p.product_code = x.product_code and
c.city = g.district_name
GROUP BY 1,2,3,4;
User-Defined Routines for the Object-Relational Database Model

The superstores_demo database does not replace the stores_demo database.
Both databases are available. The superstores_demo database schema is not
backward compatible with stores_demo. In many cases, you cannot use test
queries developed for stores_demo against the tables of superstores_demo
because the tables differ.
No SQL command files are associated specifically with superstores_demo.

However, there are user-defined routines that you can use with the screens
described in Chapter 3, “The Query-language Option,” on page 3-1.
The superstores_demo database includes examples of the following new

features:
v Collection types: SET, LIST
v Named row types: location_t, loc_us_t, loc_non_us_t
v Unnamed row types

v Type and table inheritance
v Built-in data types: BOOLEAN, SERIAL8, INT8
v Distinct data type: percent
v Smart large objects: BLOB and CLOB
The superstores_demo database has row types and tables to support the
following table-inheritance hierarchies:
v customer/retail_customer
v customer/whlsale_customer
v location/location_us
v location/location_non_us
For a complete description of the superstores_demo tables and inheritance

hierarchies, see the IBM Informix: Guide to SQL Reference. For more
information on user-defined routines, see IBM Informix: User-Defined Routines
and Data Types Developer's Guide.

Appendix C. Accessibility
The syntax diagrams in the HTML version of this manual are available in
dotted decimal syntax format, which is an accessible format that is available
only if you are using a screen reader.
Dotted Decimal Syntax Diagrams

In dotted decimal format, each syntax element is written on a separate line. If
two or more syntax elements are always present together (or always absent
together), the elements can appear on the same line, because they can be
considered as a single compound syntax element.
Each line starts with a dotted decimal number; for example, 3 or 3.1 or 3.1.1.
To hear these numbers correctly, make sure that your screen reader is set to
read punctuation. All syntax elements that have the same dotted decimal
number (for example, all syntax elements that have the number 3.1) are
mutually exclusive alternatives. If you hear the lines 3.1 USERID and 3.1
SYSTEMID, your syntax can include either USERID or SYSTEMID, but not both.
The dotted decimal numbering level denotes the level of nesting. For example,
if a syntax element with dotted decimal number 3 is followed by a series of
syntax elements with dotted decimal number 3.1, all the syntax elements
numbered 3.1 are subordinate to the syntax element numbered 3.
Certain words and symbols are used next to the dotted decimal numbers to
add information about the syntax elements. Occasionally, these words and
symbols might occur at the beginning of the element itself. For ease of
identification, if the word or symbol is a part of the syntax element, the word
or symbol is preceded by the backslash (\) character. The * symbol can be
used next to a dotted decimal number to indicate that the syntax element
repeats. For example, syntax element *FILE with dotted decimal number 3 is
read as 3 \* FILE. Format 3* FILE indicates that syntax element FILE repeats.
Format 3* \* FILE indicates that syntax element * FILE repeats.
Characters such as commas, which are used to separate a string of syntax

elements, are shown in the syntax just before the items they separate. These
characters can appear on the same line as each item, or on a separate line
with the same dotted decimal number as the relevant items. The line can also
show another symbol that provides information about the syntax elements.
For example, the lines 5.1*, 5.1 LASTRUN, and 5.1 DELETE mean that if you
use more than one of the LASTRUN and DELETE syntax elements, the elements
© Copyright IBM Corp. 1996, 2004 C-1

must be separated by a comma. If no separator is given, assume that you use
a blank to separate each syntax element.
If a syntax element is preceded by the % symbol, this identifies a reference that

is defined elsewhere. The string following the % symbol is the name of a
syntax fragment rather than a literal. For example, the line 2.1 %OP1 means
that you should refer to a separate syntax fragment OP1.
The following words and symbols are used next to the dotted decimal
numbers:
? Specifies an optional syntax element. A dotted decimal number
followed by the ? symbol indicates that all the syntax elements with a
corresponding dotted decimal number, and any subordinate syntax
elements, are optional. If there is only one syntax element with a
dotted decimal number, the ? symbol is displayed on the same line as
the syntax element (for example, 5? NOTIFY). If there is more than one
syntax element with a dotted decimal number, the ? symbol is
displayed on a line by itself, followed by the syntax elements that are
optional. For example, if you hear the lines 5 ?, 5 NOTIFY, and 5
UPDATE, you know that syntax elements NOTIFY and UPDATE are
optional; that is, you can choose one or none of them. The ? symbol is
equivalent to a bypass line in a railroad diagram.
! Specifies a default syntax element. A dotted decimal number followed
by the ! symbol and a syntax element indicates that the syntax
element is the default option for all syntax elements that share the
same dotted decimal number. Only one of the syntax elements that
share the same dotted decimal number can specify a ! symbol. For
example, if you hear the lines 2? FILE, 2.1! (KEEP), and 2.1
(DELETE), you know that (KEEP) is the default option for the FILE
keyword. In this example, if you include the FILE keyword but do not
specify an option, default option KEEP is applied. A default option also
applies to the next higher dotted decimal number. In this example, if
the FILE keyword is omitted, default FILE(KEEP) is used. However, if
you hear the lines 2? FILE, 2.1, 2.1.1! (KEEP), and 2.1.1 (DELETE),
the default option KEEP only applies to the next higher dotted
decimal number, 2.1 (which does not have an associated keyword),
and does not apply to 2? FILE. Nothing is used if the keyword FILE is
omitted.
* Specifies a syntax element that can be repeated zero or more times. A
dotted decimal number followed by the * symbol indicates that this
syntax element can be used zero or more times; that is, it is optional
and can be repeated. For example, if you hear the line 5.1*
data-area, you know that you can include more than one data area or
C-2 IBM Informix DB-Access User’s Guide

you can include none. If you hear the lines 3*, 3 HOST, and 3 STATE,
you know that you can include HOST, STATE, both together, or nothing.
Notes:
1. If a dotted decimal number has an asterisk (*) next to it and there
is only one item with that dotted decimal number, you can repeat
that same item more than once.
2. If a dotted decimal number has an asterisk next to it and several
items have that dotted decimal number, you can use more than
one item from the list, but you cannot use the items more than
once each. In the previous example, you could write HOST STATE,
but you could not write HOST HOST.
3. The * symbol is equivalent to a loop-back line in a railroad syntax
diagram.
+ Specifies a syntax element that must be included one or more times. A
dotted decimal number followed by the + symbol indicates that this
syntax element must be included one or more times. For example, if
you hear the line 6.1+ data-area, you must include at least one data
area. If you hear the lines 2+, 2 HOST, and 2 STATE, you know that you
must include HOST, STATE, or both. As for the * symbol, you can only
repeat a particular item if it is the only item with that dotted decimal
number. The + symbol, like the * symbol, is equivalent to a loop-back
line in a railroad syntax diagram.
Appendix C. Accessibility C-3

C-4 IBM Informix DB-Access User’s Guide
Notices
IBM may not offer the products, services, or features discussed in this
document in all countries. Consult your local IBM representative for
information on the products and services currently available in your area. Any
reference to an IBM product, program, or service is not intended to state or
imply that only that IBM product, program, or service may be used. Any
functionally equivalent product, program, or service that does not infringe
any IBM intellectual property right may be used instead. However, it is the
user’s responsibility to evaluate and verify the operation of any non-IBM
product, program, or service.
IBM may have patents or pending patent applications covering subject matter
described in this document. The furnishing of this document does not give
you any license to these patents. You can send license inquiries, in writing, to:
IBM Director of Licensing
IBM Corporation
North Castle Drive
Armonk, NY 10504-1785
U.S.A.
For license inquiries regarding double-byte (DBCS) information, contact the

IBM Intellectual Property Department in your country or send inquiries, in
writing, to:
IBM World Trade Asia Corporation Licensing
2-31 Roppongi 3-chome, Minato-ku
Tokyo 106-0032, Japan
The following paragraph does not apply to the United Kingdom or any
other country where such provisions are inconsistent with local law:
INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS
PUBLICATION “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER
EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY
OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow
disclaimer of express or implied warranties in certain transactions, therefore,
this statement may not apply to you.
This information could include technical inaccuracies or typographical errors.

Changes are periodically made to the information herein; these changes will
be incorporated in new editions of the publication. IBM may make
© Copyright IBM Corp. 1996, 2004 D-1

improvements and/or changes in the product(s) and/or the program(s)
described in this publication at any time without notice.
Any references in this information to non-IBM Web sites are provided for
convenience only and do not in any manner serve as an endorsement of those
Web sites. The materials at those Web sites are not part of the materials for
this IBM product and use of those Web sites is at your own risk.
IBM may use or distribute any of the information you supply in any way it
believes appropriate without incurring any obligation to you.
Licensees of this program who wish to have information about it for the
purpose of enabling: (i) the exchange of information between independently
created programs and other programs (including this one) and (ii) the mutual
use of the information which has been exchanged, should contact:
IBM Corporation
J46A/G4
555 Bailey Avenue
San Jose, CA 95141-1003
U.S.A.
Such information may be available, subject to appropriate terms and

conditions, including in some cases, payment of a fee.
The licensed program described in this information and all licensed material
available for it are provided by IBM under terms of the IBM Customer
Agreement, IBM International Program License Agreement, or any equivalent
agreement between us.
Any performance data contained herein was determined in a controlled

environment. Therefore, the results obtained in other operating environments
may vary significantly. Some measurements may have been made on
development-level systems and there is no guarantee that these measurements
will be the same on generally available systems. Furthermore, some
measurements may have been estimated through extrapolation. Actual results
may vary. Users of this document should verify the applicable data for their
specific environment.
Information concerning non-IBM products was obtained from the suppliers of

those products, their published announcements or other publicly available
sources. IBM has not tested those products and cannot confirm the accuracy
of performance, compatibility or any other claims related to non-IBM
products. Questions on the capabilities of non-IBM products should be
addressed to the suppliers of those products.
D-2 IBM Informix DB-Access User’s Guide

All statements regarding IBM’s future direction or intent are subject to change
or withdrawal without notice, and represent goals and objectives only.
All IBM prices shown are IBM’s suggested retail prices, are current and are
subject to change without notice. Dealer prices may vary.
This information contains examples of data and reports used in daily business
operations. To illustrate them as completely as possible, the examples include
the names of individuals, companies, brands, and products. All of these
names are fictitious and any similarity to the names and addresses used by an
actual business enterprise is entirely coincidental.
COPYRIGHT LICENSE:
This information contains sample application programs in source language,

which illustrate programming techniques on various operating platforms. You
may copy, modify, and distribute these sample programs in any form without
payment to IBM, for the purposes of developing, using, marketing or
distributing application programs conforming to the application programming
interface for the operating platform for which the sample programs are
written. These examples have not been thoroughly tested under all conditions.
IBM, therefore, cannot guarantee or imply reliability, serviceability, or function
of these programs. You may copy, modify, and distribute these sample
programs in any form without payment to IBM for the purposes of
developing, using, marketing, or distributing application programs
conforming to IBM’s application programming interfaces.
Each copy or any portion of these sample programs or any derivative work,
must include a copyright notice as follows:
© (your company name) (year). Portions of this code are derived from IBM
Corp. Sample Programs. © Copyright IBM Corp. (enter the year or years).
All rights reserved.
If you are viewing this information softcopy, the photographs and color
illustrations may not appear.
Notices D-3
Trademarks
AIX; DB2; DB2 Universal Database; Distributed Relational Database
Architecture; NUMA-Q; OS/2, OS/390, and OS/400; IBM Informix®;
C-ISAM®; Foundation.2000™; IBM Informix ® 4GL; IBM
Informix®DataBlade®Module; Client SDK™; Cloudscape™; Cloudsync™; IBM
Informix®Connect; IBM Informix®Driver for JDBC; Dynamic Connect™; IBM
Informix®Dynamic Scalable Architecture™(DSA); IBM Informix®Dynamic
Server™; IBM Informix®Enterprise Gateway Manager (Enterprise Gateway
Manager); IBM Informix®Extended Parallel Server™; i.Financial Services™;
J/Foundation™; MaxConnect™; Object Translator™; Red Brick™; IBM
Informix® SE; IBM Informix® SQL; InformiXML™; RedBack®; SystemBuilder™;
U2™; UniData®; UniVerse®; wintegrate®are trademarks or registered
trademarks of International Business Machines Corporation.
Java and all Java-based trademarks and logos are trademarks or registered
trademarks of Sun Microsystems, Inc. in the United States and other countries.
Windows, Windows NT, and Excel are either registered trademarks or

trademarks of Microsoft Corporation in the United States and/or other
countries.
UNIX is a registered trademark in the United States and other countries

licensed exclusively through X/Open Company Limited.
Other company, product, and service names used in this publication may be
trademarks or service marks of others.
D-4 IBM Informix DB-Access User’s Guide

Index
CHOOSE screen 3-17
Special characters CLOB data type 5-37
$INFORMIXDIR/bin 1-4
CLOSE DATABASE statement 4-13
%INFORMIXDIR%\bin 1-5
Closing a database
A from a menu 4-12
with active transactions 6-6
Access method, index 5-39
Accessibility xviii with the Disconnect option 6-5
dotted decimal format of syntax diagrams C-1 Code set, ISO 8859-1 viii
syntax diagrams, reading in a screen reader C-1 Code, sample, conventions for xiv
ADD CHECK VALUE menu 5-29 Collection data type 5-38
ADD DEFAULT TYPE menu 5-32 LIST 5-38
ADD DEFAULT VALUE menu 5-33 MULTISET 5-38
ADD NULLS menu 5-11 SET 5-38
ADD TYPE menu 5-7 Column
ALTER FRAGMENT menu 5-18 allowing null values with the schema editor 5-11
no strategy 5-22 constraints for 5-25
data types
round-robin 5-18
ALTER ROWID menu 5-24 adding 5-7
ALTER TABLE screen 5-4 description 4-13, 5-48
ANSI compliance selection 5-7
data validation of default values 5-33
checking SQL statements for 1-18
ANSI database 4-12 default literal value 5-32
ANSI-compliant database default type, null 5-32
and SQLSTATE value 1-18 defining 5-6
specifying 4-5 displaying data type and nulls with the Info
option 5-36
transaction logging 4-5
displaying information with the Columns
ANSI, checking SQL statements for compliance 1-18
option 5-36
Arrow keys 2-2
dropping from a table with the schema editor 5-12
B indexing 5-9
BACKSPACE key 2-2 modifying 5-12
BLOB data type 5-37 null default value 5-32
Blobspace removing from the schema 5-13
adding column for 5-8 Column Name 5-6
data types in Columns display 5-37 Command files
specifying for TEXT or BYTE data 5-9 choosing (CHOOSE screen) 3-16
Boldface type ix executing from the command line 1-17
BOOLEAN data type 5-37 modifying with an editor 2-6
Buffered logging 4-5 rules for naming 3-18
Built-in data type 5-36 saving 3-18
BYTE data type 5-37 supplied SQL command files B-1
Command line
C additional features 1-19
Cascading deletes, enabling 5-28 interactive input through standard input 1-20
CHAR data type 5-37 reading from standard input 1-19
CHARACTER VARYING data type 5-37 syntax for invoking DB-Access 1-12
CHECK CONSTRAINTS menu 5-29 using the -ansi flag 3-7
chmod command 1-10 Command options.
Choose option (SQL menu) 3-16 See dbaccess command options.
© Copyright IBM Corp. 1996, 2004 X-1

Command window should not have scroll bars 1-11 CREATE TABLE menu (continued)
Command-line conventions building schema on exit 5-11
how to read xii exiting 5-11
sample diagram xii Modify option, changing a column 5-12
Command-line options, displaying the main Creating a database
menu 1-12 from a menu 4-4
COMMIT 4-12 restrictions on naming 4-4
COMMIT statement 4-12 Creating a table
Committing transactions with the TRANSACTION fragmented (EXPRESSION menu) 5-16
menu 4-12, 6-6 fragmented (ROUND_ROBIN menu) 5-15
Compliance with the schema editor 5-3
with industry standards xxi CTRL-A 3-5
Confirmation screen CTRL-D 1-20, 3-5
creating a database 4-6 CTRL-R 3-5
dropping a command file 3-21 CTRL-W 2-5, A-1
dropping a database 4-11 CTRL-X 3-5
CONNECT statement 1-21 Current statement, definition of 3-2
Connecting to a database environment 6-1 Cursor
Connecting to database environment in background moving on the terminal screen 2-2
mode 1-22 position when using SQL editor 3-5
CONNECTION menu 6-1, 6-2 where displayed on text-entry screens 2-4
options 1-14
PASSWORD prompt screen 6-3 D
USER NAME prompt screen 6-2 Data types
Constraints ADD TYPE menu options for 5-7
adding with CREATE TABLE 5-25 adding column 5-7
check, defining 5-28 BLOB 5-37, B-15
default values, defining 5-31 BOOLEAN 5-37, B-15
defining 5-25 built-in 5-36
displaying 5-41 BYTE 5-37
enabling cascading deletes 5-28 CHAR 5-37
foreign key, defining 5-27 CHARACTER VARYING 5-37
foreign key, information on 5-41 CLOB 5-37, B-15
primary key, defining 5-26 collection 5-38, B-14
primary key, information on 5-42 DATE 5-37
reference, information on 5-41 DATETIME 5-37
unique, defining 5-30 DECIMAL 5-37
CONSTRAINTS menu defining for a column with the schema editor 5-7
options on 5-41 described 4-13, 5-48
Primary option 5-42 distinct B-15
Reference option 5-41 FLOAT 5-37
shown 5-25, 5-41 INT8 5-37
Contact information xxii INTEGER 5-37
CONTROL key 2-2 INTERVAL 5-37
Conventions LIST 5-38, B-14
command-line xii MONEY 5-37
documentation ix MULTISET 5-38
online Help A-1 NCHAR 5-37
sample-code xiv NVARCHAR 5-37
syntax diagrams xi opaque 5-38
syntax notation xi row 5-38, B-14
typographical ix SERIAL 5-37
CREATE DATABASE menu 4-4 SERIAL8 5-37, B-15
Create option, TABLE menu 5-3 SET 5-38, B-14
CREATE TABLE menu 5-5 SMALLFLOAT 5-37
X-2 IBM Informix DB-Access User’s Guide

Data types (continued) DB-Access
SMALLINT 5-37 environment variables affecting 1-4
TEXT 5-37 History command 1-22
VARCHAR 5-37 menu hierarchy 1-3
Data validation USER NAME prompt screen 6-2
default column values 5-33 what it is 1-2
unique constraints 5-31 DB-Access utility 1-6
Data, viewing the next page on the screen 3-11 dbaccess command options
Database -ansi 1-13
cLose option 4-12 -c 1-13
closing 4-12 -cc 1-14
DISCONNECT menu 6-5 -cd 1-14
with active transactions 6-6 -d 1-13
closing implicitly 6-5 -dc 1-14
confirming your decision to drop 4-11 -dd 1-14
creating 4-4 -di 1-15
creating ANSI-compliant 4-5 -dl 1-15
current 4-1 -ds 1-15
displaying information for 4-6 -e 1-13
dropping 4-11 -help option (XPS) 1-14
naming 4-4 -m 1-13
selecting 2-6 -nohistory option (XPS) 1-13
selecting from a menu 4-2 -q 1-13
storing in a dbspace 4-5 -qc 1-15
Database administrator 1-10 -qd 1-15
DATABASE INFO menu -qi 1-15
dBspaces option 4-7 -qm 1-15
how to exit 4-7 -qn 1-15
options 4-7 -qs 1-15
Routines option 4-9 -qu 1-15
Database information -s 1-13
dbspaces 4-7 -t 1-13
routines 4-9 -ta 1-16
DATABASE menu -tc 1-16
available options 4-2 -td 1-16
cLose option 4-12 -ti 1-16
CREATE DATABASE screen 4-4 -v or -V 1-13
DROP DATABASE screen 4-10 -X 1-13
Drop option 4-10 connect_menu_option 1-14
Info option 4-6 database 1-14
options 1-14 database_menu_option 1-14
SELECT DATABASE screen 4-2 filename 1-14
Select option 4-2 query_menu_option 1-14
selecting options from the command line 1-14 table 1-14
the SELECT DATABASE screen 4-3 table_menu_option 1-14
Database server dbaccess, invoking 1-10
connecting to 6-2 DBACCNOIGN environment variable 1-5
disconnecting from 6-5 DBEDIT environment variable 1-5
disconnecting implicitly 6-5 DBFLTMASK environment variable 1-5
name, saving in column 5-32 DBPATH environment variable 6-4
SELECT DATABASE SERVER screen 6-2 dbspace
selecting from a menu 6-2 ALTER FRAGMENT menu 5-18
DATE data type 5-37 defining expression for 5-17
DATETIME data type 5-37 deleting from existing strategy 5-16
modifying current 5-16
Index X-3
dbspace (continued) Demonstration database (continued)
Round-robin fragmentation with 5-16 superstores_demo 1-7
SELECT DBSPACE screen 4-5 superstores_demo setup 1-9
specifying for table storage 5-14 working directory required for 1-8
storing a database 4-5 Dependencies, software viii
DECIMAL data type 5-37 Disabilities, visual
DECIMAL(p) values reading syntax diagrams C-1
checking, scale of 1-18 Disconnecting from a database environment 6-5
Default Disk space, managing with Extent Size screen 5-23
ADD DEFAULT TYPE menu 5-32 Distinct data type B-15
ADD DEFAULT VALUE menu 5-33 Distributed databases, requesting table information on
column type, null 5-32 another server 5-34
column values 5-32 Documentation conventions ix
column values, data validation 5-33 Documentation Notes xvi
column values, defining 5-31 Documentation set of all manuals xviii
column values, displaying 3-19 Documentation, types of xv
column, adding 5-32 machine notes xvi
column, data validation 5-33 online manuals xviii
database server, selecting 6-3 printed manuals xviii
dbspace for database data 4-5 Dotted decimal format of syntax diagrams C-1
defining values for columns 5-31 DROP DATABASE screen
dropping a check constraint 5-29 selecting from the DATABASE menu 4-10
dropping a foreign key 5-28 two ways to drop a database 4-10
for ADD DUPLICATES screen 5-10 Drop option
for ADD NAME screen 5-6 confirmation screen for dropping command
for CREATE DATABASE confirmation screen 4-5 files 3-21
for DISCONNECT confirmation screen 6-5 SQL menu 3-20
for DROP COMMAND FILE confirmation Dropping a command file
screen 3-21 confirming your decision 3-21
for DROP TABLE confirmation screen 5-46 from a menu 3-20
for exiting CREATE DATABASE menu 4-6 Dropping a database, from a menu 4-10
for LOCK MODE menu 5-24 Dropping a table
for LOG menu 4-5 confirming your decision 5-46
initial extent size 5-24 from a menu 5-45
length and scale, for number type 5-9 Dropping an SQL statement
length, for CHAR data type 5-9 confirming your decision 3-21
length, for MONEY data type 5-9 from a menu 3-20
length, for NCHAR data type 5-9
number, for SERIAL data type 5-9 E
operating system editor 3-3 Editor
printer, sending output to 3-15 creating new SQL statements 3-5
table storage location 5-14 entering literal values 5-32
Default locale viii modifying SQL statements 3-13
DEFAULTS menu 5-31 restrictions 3-6
DELETE key 2-2 text 2-6
Deletes, enabling cascading 5-28 en_us.8859-1 locale viii
DELIMIDENT environment variable 1-5, 1-6 ENABLE CASCADING DELETES menu 5-28
Demonstration database 1-6 Environment variables ix
installing 1-8 DBACCNOIGN 1-5
models 1-7 DBANSIWARN 1-18, 3-7
reinitializing 1-7 DBEDIT 1-5
sales_demo 1-9 DBFLTMASK 1-5
SQL command files B-1 DBPATH 3-17, 4-3
stores_demo setup 1-8 DELIMIDENT 1-5, 1-6
stores_demo, SQL command files B-2 IFMX_HISTORY_SIZE 1-6

Environment variables (continued) Foreign-key constraints
LC_COLLATE 3-17 defining 5-27
ONCONFIG 4-3 enabling cascading deletes 5-28
setting for default editor 3-2 information on 5-41
Error message Fragment
terminal setup 1-11 ALTER FRAGMENT menu in DB-Access 5-18
Error messages xvii EXPRESSION menu in DB-Access 5-16
Errors FRAGMENT menu in DB-Access 5-15
connecting to a server after opening a database 6-5 option on DB-Access STORAGE menu 5-15
correcting with the SQL editor 3-13 ROUND_ROBIN menu in DB-Access 5-16
editing in SQL statements 3-14 FRAGMENT menu, reaching 5-15
entering a fill factor 5-10 Fragmentation strategy
executing command files B-1 no strategy menu in DB-Access 5-22
modifying a constraint 5-31 round-robin in DB-Access 5-18
no CONNECT permission 6-4
running SQL statements 3-12 G
using command-line options 1-16 Global Language Support (GLS) viii
ESCAPE key 2-2, 3-5 displaying information on 4-8
Exit option GRANT statement 1-10
CREATE DATABASE menu 4-6
CREATE TABLE menu 5-11 H
DATABASE INFO menu 4-7 Help xviii
DATABASE menu 4-2 calling with CTRL-W A-1
LOCK MODE menu 5-24, 5-25 exiting online help screen 2-5
LOG menu 4-5 how to read syntax diagrams A-1
RUN screen 3-11, 5-44 online syntax information for SQL statements A-1
SQL menu 3-4 paging through online information screens 2-5
TABLE menu 5-3, 5-5 HELP menu 2-5
Expression strategy History command 1-22
ATTACH TABLES menu 5-20 How to use your terminal 2-2
DEFINE ATTACH POSITION menu 5-21
EDIT EXPRESSION menu 5-17 I
EXPRESSION menu 5-16 IFMX_HISTORY_SIZE environment variable 1-6
FRAGMENT menu 5-15 Index
NEW TABLE screen 5-22 access method 5-39
SELECT ATTACHING TABLE screen 5-20 allowing duplicate values 5-10
extended data types 5-7 creating with the ADD INDEX screen 5-10
Extent size 5-23 from DataBlade 5-40
specifying a fill factor 5-10
F user-defined 5-40
Features of this product, new ix Indexing a column, the ADD INDEX screen 5-9
File Industry standards, compliance with xxi
.sql extension for command files 3-18, B-1 INFO FOR TABLE screen 5-34
appending query results to 3-15 INFO menu
command, selecting 3-16 available options 5-35
command, shown B-2 Columns option 5-36
creating to store query results in 3-15 cOnstraints option 5-41
reading from standard input 1-20 displaying column information 5-35
saving current SQL statement in 3-18 displaying constraint information 5-41
saving SQL statements in 3-16 displaying table index information 5-39
storing query results in 3-14 displaying table privileges information 5-40
Fill factor, specifying 5-10 exiting 3-20, 5-36
Fixed and Known Defects File xvi Fragmentation option 5-44
FLOAT data type 5-37 listing tables 5-36
option on the TABLE menu 5-34
Privileges option 5-40
Index X-5
INFO menu (continued) Keys (continued)
References option 5-40 RETURN 2-3
triGgers option 5-43 SPACEBAR 2-3
with SQL 5-35 used with SQL editor 3-5
Info option Keywords
Columns option 5-36 in syntax diagrams xiii
cOnstraints option 5-41
DATABASE menu 4-6 L
Exit option 5-36 Large objects
Indexes option 5-39 defined 5-37
Privileges option 5-40 simple 5-37
References option 5-40 smart 5-37
SQL menu 3-19 LIST data type 5-38
TABLE menu 5-3 Literal, assigning default value 5-32
Table option 5-36 Locale viii
triGgers option 5-43, 5-44 default viii
Information en_us.8859-1 viii
displaying for current database 4-6 LOCK MODE menu 5-24
displaying for current session 6-6 Lock mode, specifying page or row 5-24
displaying for tables 3-19, 5-34 LOG menu 4-5
Information Schema 1-17 Logging
Informix Dynamic Server documentation set xviii creating a database with 4-5
INFORMIXDIR/bin directory 1-7 specifying buffered or unbuffered 4-5
Input
interactive 1-20 M
reading from standard 1-19 Machine notes xvi
Installation Guides xv Main menu
INT8 data type 5-37 displaying from the command line 1-12
INTEGER data type 5-37 Query-language option 3-3
Interactive input, through standard input 1-20 selecting the Connection option 6-1
Interrupt key 2-2 selecting the Session option 6-6
INTERVAL data type 5-37 selecting the Table option 5-2
Invoking DB-Access Menu options
checking for ANSI compliance 1-18 CONNECTION menu 6-2
command-line options 1-10 DATABASE menu 4-2
DATABASE menu options 1-14 SQL menu 3-4
displaying the main menu 1-12 TABLE menu 5-3
executing a command file 1-17 Menus
SQL menu options 1-15 ADD CHECK VALUE 5-29
TABLE menu options 1-16 ADD DEFAULT TYPE 5-32
ISO 8859-1 code set viii ADD DEFAULT VALUE 5-33
ADD NULLS 5-11
K ADD TYPE 5-7
Keys chart of 1-3
arrow 2-2 CHECK CONSTRAINTS 5-29
BACKSPACE 2-2 CONNECTION 6-1
CONTROL 2-2 CONSTRAINTS 5-25, 5-41
CTRL-A 3-5 DEFAULTS 5-31
CTRL-D 1-20, 3-5 ENABLE CASCADING DELETES 5-28
CTRL-R 3-5 example 2-4
CTRL-W 2-5, A-1 HELP 2-5
CTRL-X 3-5 how to exit 2-4
DELETE 2-2 how to select an option 2-3
ESCAPE 2-2, 3-5 INFO 5-35
Interrupt 2-2 LOCK MODE 5-24

Menus (continued) Pipe (continued)
REFERENCE 5-41 sending query results to 3-16
TABLE OPTIONS 5-13 Primary-key constraints
TRANSACTION 4-12, 6-6 defining 5-26
UNIQUE CONSTRAINTS 5-30 information on 5-42
Mode Printed manuals xviii
insert 3-5 Printing the results of a query 3-15
locking 5-24
typeover 3-5 Q
Modify option (SQL menu) 3-13 Query
MONEY data type 5-37 appending results to an existing file 3-15
MULTISET data type 5-38 sending results to a file 3-15
sending results to a pipe 3-16
N sending results to a printer 3-15
Native Language Support storing results in a new file 3-15
command files list order 3-17 QUERY-LANGUAGE menu, options 1-15
displaying information on 4-8 Query-language option
NCHAR data type 5-37 how to use 3-4
New features of this product ix on the main menu 3-3
Null values
default for column 5-32 R
specifying with the ADD NULLS menu 5-11 Reading from standard input 1-19
NVARCHAR data type 5-37 REFERENCE menu
options 5-41
O with referenced information 5-42
Online help xviii with referencing information 5-42
Online manuals xviii Release Notes xvi
Online notes xv, xvi Restrictions, for SQL editor 3-6
Opaque data type 5-38 RETURN key 2-3
Options ROLLBACK 4-12
for CONNECTION menu 6-2 Rolling back transactions 4-12, 6-6
for DATABASE menu 4-2 Rolling back transactions, with the TRANSACTIONS
for SQL menu 3-4 menu 6-6
for TABLE menu 5-3 Round-robin strategy
how to get Help for 2-5 FRAGMENT menu 5-15
how to select on a menu 2-3 ROUND_ROBIN menu 5-16
Options. Routines
See dbaccess command options. creating and running 3-21
Output option demonstration command file 3-22
Append-file option 3-15 displaying list of 4-9
New-file option 3-15 displaying text of 4-9
Printer option 3-15 SELECT ROUTINE screen 4-9
SQL menu 3-14 stored 3-24
To-pipe option 3-16 sysprocedures system catalog table 3-22
Row data type, defined 5-38
P Row locking 5-24
Page option (LOCK MODE menu) 5-24 Row option (LOCK MODE menu) 5-24
PASSWORD prompt screen 6-3 Rowids, adding or dropping 5-24
Password, prompt in DB-Access interactive mode 1-21 Run option
PATH Exit option 3-11, 5-44
DB-Access requirements 1-4 Next option for viewing data 3-11
demonstration database and 1-8 Restart option to redisplay results 3-11
Permissions, UNIX 1-8 SQL menu 3-7
Pipe Running SQL statements
reading from 1-20 when there are errors 3-12
redirecting query results to a program 3-14
Index X-7
Running SQL statements (continued) Shell (continued)
when there are no errors 3-7 Korn 1-20
Sitename, saving in a column 5-32
S Size
sales_demo 1-7 setting initial extent 5-23
Sample-code conventions xiv setting next extent 5-23
Save option SMALLFLOAT data type 5-37
rules for naming saved files 3-18 SMALLINT data type 5-37
SQL menu 3-18 Software dependencies viii
Saving command files 3-18 SPACEBAR 2-3
Schema editor SPACEBAR key 2-3
creating a table 5-6 SPL Routines 3-21
defining a column 5-6 SQL
dropping a column from a table 5-12 entering statements 3-4
EXIT menu 5-11 how to read syntax in online Help screens A-1
Schema, building table with 5-11 INFO statement equivalent 3-19
Screen reader using from a menu 3-10
reading syntax diagrams C-1 SQL code xiv
Screens SQL command files
ADD FILL FACTOR PERCENTAGE 5-10 must be in current directory 1-7
CHOOSE 3-17 requirements for listing with Choose
CREATE DATABASE confirmation 4-6 command 3-17
DISCONNECT confirmation 6-5 sales_demo B-11
example 2-4 SQL editor
for DATABASE menu 4-1, 4-11 assigning default data type for column 5-32
for SQL menu 3-3, 3-21 CTRL-A editing key 3-5
for TABLE menu 5-2 CTRL-D editing key 3-5
for text entry 2-4 CTRL-R editing key 3-5
how to enter text 2-4 CTRL-X editing key 3-5
how to exit 2-5 editing keys 3-5
INFO FOR TABLE 5-34 editing restrictions 3-6
SELECT BLOBSPACE 5-9 ESC key 3-5
SELECT DATABASE 6-4 how to use 3-5
SELECT DATABASE SERVER 6-3 modifying SQL statements 3-13
SELECT DBSPACE 5-14 new SQL statements 3-5
SELECT PROCEDURE 4-9 SQL menu
SELECT ROUTINE 4-9 available options 3-4
structure of header 2-4 Choose option 3-16
SELECT BLOBSPACE screen 5-9 CHOOSE screen 3-16
SELECT DATABASE screen 6-4 Drop option 3-20
exit without selecting a database 6-4 Info option 3-19
selecting from the DATABASE menu 4-2 Modify option 3-13
two ways to select a database 4-3 OUTPUT menu 3-14
SELECT DATABASE SERVER screen 6-2, 6-3 Output option 3-14
SELECT DBSPACE screen 5-14 Run option 3-7
SELECT ROUTINE screen 4-9 running new SQL statements 3-7
Selecting a database server 6-2 Save option 3-18
Selecting a database, from a menu 4-2 SAVE screen 3-18
SERIAL data type 5-37 selecting options from the command line 1-15
SERIAL8 data type 5-37 SQL statements
Session information 6-6 appending query results to an existing file 3-15
SET data type 5-38 choosing a command file 3-16
Shell current, defined 3-2
Bourne 1-20 dropping 3-20
C 1-20 editing 3-2

SQL statements (continued) Table (continued)
editing with the system editor 3-2 primary-key constraint information 5-42
entering new ones with the SQL editor 3-5 privileges display 5-40
executing from standard input 1-21 privileges information 5-40
interactive input on terminal 1-20 references display 5-40
modifying 3-12 removing a column from the schema 5-13
reading from standard input 1-19 SELECT ATTACHING TABLE screen (expression
redirecting query results 3-14 fragmentation) 5-20
saving to a command file 3-18 setting the extent sizes 5-13
selecting the SAVE screen 3-18 setting the lock mode 5-13
sending output to a file 3-15 storage location for 5-14
sending output to a printer 3-15 trigger information 5-43, 5-44
sending query results to a pipe 3-16 triggers display 5-44
syntax conventions in online Help A-1 TABLE menu
what happens when there are errors 3-12 available options 5-3
sqlhosts, display connectivity information in 6-3 guidelines for using 5-2
SQLSTATE value displayed 1-18 Info option 5-34
stdin, for interactive input 1-20 options 1-16
Stored procedures selecting options from the command line 1-16
SELECT PROCEDURE screen 4-9 selecting the CREATE TABLE screen 5-3
stores_demo 1-6, 1-7 selecting the Drop option 5-45
superstores_demo 1-7 TABLE OPTIONS menu 5-13
Syntax diagrams Table_options option (CREATE TABLE menu) 5-5
conventions for xi Terminal
keywords in xiii as standard input 1-20
reading in a screen reader C-1 how to use the keys 2-2
variables in xiv Text
Syntax segment xiii editing with the system editor 3-2
System date, saving in a column 5-32 how to enter on a screen 2-4
System requirements TEXT data type 5-37
database viii Text editor
software viii how to use 2-6
internal editor 3-6
T SQL editor 3-6
Table TOC Notes xvi
allowing null values in a column 5-11 TRANSACTION menu 4-12, 6-6
building schema 5-11 Transactions, committing or rolling back 4-12
column constraints display 5-41 Trouble shooting
column information 5-36 Choose command does not list your SQL command
constraint information 5-41 files 3-17
dbspace for storing 5-14 dbaccess window must not have scroll bars 1-11
displaying from another server 5-34 Trouble shooting, terminal type unknown 1-11
displaying information on the screen 3-19, 5-34 Typographical conventions ix
displaying information with the triGgers
option 5-43 U
dropping 5-45 Unbuffered logging
dropping a column (schema editor) 5-12 how to specify 4-5
extent sizes for 5-23 Unique constraints
foreign-key constraint information 5-41 data validation 5-31
index information 5-39 defining 5-30
indexing a column (schema editor) 5-9 UNIQUE CONSTRAINTS menu 5-30
inheritance B-15 UNIX
lock mode for 5-24 case sensitivity and filenames 3-18
modifying a column (schema editor) 5-12 chmod command 1-10
modifying before building 5-12 permissions 1-8
Index X-9
UNIX (continued)
system editors 3-3
UNIX operating system
default locale for viii
USER clause of CONNECT statement
in DB-Access interactive mode 1-21
User id, saving in a column 5-32
User name
CONNECT statement with 1-21
specifying when connecting in background
mode 1-22
USER NAME prompt screen 6-2
user-defined data types 5-7
Users, types of viii
V
VARCHAR data type 5-37
Variables, in syntax diagrams xiv
Visual disabilities
reading syntax diagrams C-1
W
Windows NT
default locale for viii
Working directory 1-8
Y
Year values, two and four digit 1-5

Printed in USA
G251-2277-00
Spine information:
IBM DB2 IBM Informix Version 10.0/8.5 IBM Informix DB-Access User’s Guide

Manual Informix 10

Uploaded by

Copyright:

Available Formats

Manual Informix 10

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Manual Informix 10

Uploaded by

Copyright:

Available Formats

DB2 IBM Informix

IBM Informix DB-Access User’s Guide

IBM Informix DB-Access User’s Guide

First Edition (December 2004)

Chapter 1. Getting Started with DB-Access . . . . . . . . . . . . . . . . . . . 1-1

Chapter 2. Using the Full-Screen Menu Interface . . . . . . . . . . . . . . . . . 2-1

© Copyright IBM Corp. 1996, 2004 iii

Chapter 3. The Query-language Option . . . . . . . . . . . . . . . . . . . . . 3-1

Chapter 4. The Database Option . . . . . . . . . . . . . . . . . . . . . . . 4-1

iv IBM Informix DB-Access User’s Guide

Chapter 5. The Table Option . . . . . . . . . . . . . . . . . . . . . . . . 5-1

Chapter 6. The Connection and Session Options . . . . . . . . . . . . . . . . . 6-1

Appendix A. How to Read Online Help for SQL Statements . . . . . . . . . . . . . A-1

Appendix B. Demonstration SQL . . . . . . . . . . . . . . . . . . . . . . . B-1

Appendix C. Accessibility . . . . . . . . . . . . . . . . . . . . . . . . . C-1

About This Manual

Important: Use DB–Access with the current version of an Informix database

© Copyright IBM Corp. 1996, 2004 vii

This manual assumes that you have the following background:

If you have limited experience with relational databases, SQL, or your

viii IBM Informix DB-Access User’s Guide

For instructions on how to specify a nondefault locale, additional syntax, and

The following conventions are discussed:

Tip: When you are instructed to “enter” characters or to “execute” a

Identifies information that is specific to IBM Informix Dynamic Server

Extended Parallel Server

Identifies information that is specific to IBM Informix Extended Parallel Server

Identifies information that is specific to UNIX platforms

Identifies information that is specific to the Windows environment

x IBM Informix DB-Access User’s Guide

Note: Starting in 2004, syntax diagrams have been reformatted to conform to

Syntax diagrams depicting SQL and command-line statements have changed

The following table describes syntax diagram components.

Component represented in PDF Component represented in HTML Meaning

-----------------------> Statement continues on

>----------------------- Statement continues from

--------SELECT---------- Required item.

--+-----------------+--- Optional item.

---+-----ALL-------+--- Required item with choice.

>>-| Table Reference |->< Reference to a syntax

How to Read a Command-Line Syntax Diagram

Creating a No-Conversion Job

xii IBM Informix DB-Access User’s Guide

Setting the Run Mode:

The Creating a No-Conversion Job diagram illustrates the following steps:

Your diagram is complete.

Keywords and Punctuation

Identifiers and Names

DELETE FROM customer

xiv IBM Informix DB-Access User’s Guide

Locating Online Notes

xvi IBM Informix DB-Access User’s Guide

On Windows the documentation and release notes files appear in the

Machine notes do not apply to Windows platforms.

Online Notes Filenames

Online File File Format Examples

Informix Error Messages

xviii IBM Informix DB-Access User’s Guide

Table 2. Client/Connectivity Manuals

xx IBM Informix DB-Access User’s Guide