unisys

imagine it. done.

OS 2200

Relational Data Management System (RDMS

2200) and IPF SQL Interface
End Use Guide
ClearPath OS 2200 12.1 Release

June 2010 7831 0778–003

NO WARRANTIES OF ANY NATURE ARE EXTENDED BY THIS DOCUMENT. Any product or related information
described herein is only furnished pursuant and subject to the terms and conditions of a duly executed agreement to
purchase or lease equipment or to license software. The only warranties made by Unisys, if any, with respect to the
products described in this document are set forth in such agreement. Unisys cannot accept any financial or other
responsibility that may be the result of your use of the information in this document or software material, including
direct, special, or consequential damages.

You should be very careful to ensure that the use of this information and/or software material complies with the laws,
rules, and regulations of the jurisdictions with respect to which it is used.

The information contained herein is subject to change without notice. Revisions may be issued to advise of such
changes and/or additions.

Notice to U.S. Government End Users: This is commercial computer software or hardware documentation developed at
private expense. Use, reproduction, or disclosure by the Government is subject to the terms of Unisys standard
commercial license for the products, and where applicable, the restricted/limited rights provisions of the contract data
rights clauses.

Unisys and ClearPath are registered trademarks of Unisys Corporation in the United States and other countries.
All other brands and products referenced in this document are acknowledged to be the trademarks or registered
trademarks of their respective holders.
Contents
Section 1. About This Guide

1.1. Documentation Updates .......................................................... 1–2

1.2. Prerequisites ............................................................................ 1–2

Section 2. Introduction

2.1. Database Management in the OS 2200 Environment ............. 2–1

2.2. Basic End-User Tasks .............................................................. 2–2
2.3. Structured Query Language (SQL) ........................................... 2–3

Section 3. Relational Concepts

3.1. Relational Data Model .............................................................. 3–1

3.2. Characteristics of Relational Tables ......................................... 3–3
3.3. Tables and Views ..................................................................... 3–3

Section 4. Threads

4.1. Managing Threads, Steps, and Recovery ................................ 4–1

4.2. Starting a Session .................................................................... 4–2
4.3. Committing Changes ............................................................... 4–4
4.4. Canceling Changes .................................................................. 4–4
4.5. Ending a Session ...................................................................... 4–5

Section 5. Querying the Database

5.1. Query Specifications ................................................................ 5–1

5.1.1. Specifying Columns: Select List...................................... 5–2
5.1.2. Specifying Tables: FROM Clause .................................... 5–4
5.1.3. Selecting Rows: WHERE Clause .................................... 5–6
5.1.4. Retrieving Information on Groups of Rows:
GROUP BY Clause ...................................................... 5–7
5.1.5. Restricting Groups: HAVING Clause ............................... 5–8
5.2. Joining Tables .......................................................................... 5–9
5.3. Using Cursors ........................................................................ 5–13
5.3.1. Declaring a Cursor (DECLARE CURSOR) ...................... 5–13
5.3.2. Opening a Cursor (OPEN) ............................................. 5–14
5.3.3. Closing a Cursor (CLOSE) ............................................. 5–15

7831 0778–003 iii

Contents

Section 6. Changing Data

6.1. Deleting Rows from a Table (DELETE) ..................................... 6–1

6.2. Inserting Data in a Table (INSERT)............................................ 6–2
6.3. Changing Values in a Table (UPDATE) ...................................... 6–3

Section 7. Views

7.1. Creating a View (CREATE VIEW) .............................................. 7–1

7.1.1. Qualifier Name and View Name ...................................... 7–2
7.1.2. Column Names ................................................................ 7–3
7.1.3. View Query Specification ................................................. 7–4
7.1.4. Updating Views ................................................................ 7–5
7.1.5. View WITH CHECK OPTION Clause ................................ 7–6
7.2. Dropping a View (DROP VIEW) ................................................ 7–7

Section 8. Restricting Access to Data

8.1. Using Locks .............................................................................. 8–1

8.2. Locking Tables (LOCK) ............................................................. 8–2
8.3. Removing Locks (UNLOCK) ..................................................... 8–4

Section 9. RDMS Interfaces

Section 10. IPF SQL Interface

10.1. Using SQL Statements with IPF.............................................10–1

10.2. IPF SQL Interface Sessions ....................................................10–3
10.2.1. Calling the IPF Processor (@IPF) ....................................10–3
10.2.2. Specifying a Data Manager ............................................10–4
10.2.3. Specifying an IPF Escape Character ..............................10–4
10.2.4. Opening an RDMS Database (BEGIN THREAD) ............10–5
10.2.5. Selecting Recovery Options...........................................10–6
10.2.6. Getting Help with SQL Statements ...............................10–6
10.2.7. Automatically Committing Changes to the
Database ....................................................................10–9
10.2.8. Saving Changes to a Database (COMMIT) ..................10–10
10.2.9. Discarding Changes to a Database (ROLLBACK) ........10–10
10.2.10. Closing a Database (END THREAD) .............................10–11
10.2.11. Exiting IPF (LOGOFF) ...................................................10–11
10.3. Saving SQL Statements in a File ..........................................10–12
10.4. Query Result Output ............................................................10–14
10.4.1. Saving Query Results in a File .....................................10–14
10.4.2. Using SQL Full-Screen Mode ......................................10–15

Section 11. SQL Operations and Variables

11.1. Cursor Operations...................................................................11–1

11.1.1. Declaring a Cursor (SQL DECLARE CURSOR) ..............11–2

iv 7831 0778–003
 Contents

11.1.2. Dropping a Cursor (SQL DROP CURSOR) .................... 11–2

11.1.3. Closing a Cursor (SQL CLOSE) ..................................... 11–2
11.1.4. Retrieving a Row from a Cursor (SQL FETCH) ............. 11–3
11.2. IPF Variables .......................................................................... 11–4
11.2.1. Status Variables ............................................................. 11–5
11.2.2. Indicator Variables ......................................................... 11–7
11.3. IPF Procedures and Variables ................................................ 11–9

Appendix A. Qualifier and Version Names

A.1. Purpose and Use ...................................................................... A–1

A.2. Using Qualifiers ........................................................................ A–2
A.3. Using Versions ......................................................................... A–2
A.4. Default Qualifier and Version Names ....................................... A–2
A.5. Establishing a New Default Qualifier (USE DEFAULT
QUALIFIER) ......................................................................... A–3
A.6. Establishing a Default Version (USE DEFAULT
VERSION) ............................................................................ A–3

Glossary ............................................................................................. 1

Index ............................................................................................. 1

7831 0778–003 v
Contents

vi 7831 0778–003
Tables

3–1. Table CUSTOMERS ........................................................................................... 3–2

3–2. Table HOUSES ................................................................................................... 3–2

5–1. GROUP BY Result Table .................................................................................... 5–7

5–2. CUSTOMERS/HOUSES Cross-Product Table .................................................. 5–10
5–3. CUSTOMERS/HOUSES Cross-Product Table with WHERE Clause ................ 5–11
5–4. CUSTOMERS/HOUSES Cross-Product Table with Query Specification ......... 5–12

11–1. Error Codes and Auxiliary Status Information .................................................. 11–6

7831 0778–003 vii

Tables

viii 7831 0778–003

Section 1
About This Guide

Purpose
RDMS is one of the Universal Data System (UDS) suite of software products. UDS
products work together to manage and control the flow of data on a system. RDMS is a
part of the standard product (the UDS kernel).

RDMS is based on the relational data model. The relational data model provides an
uncomplicated view of data to all users by representing data in two-dimensional tables of
rows and columns. These tables are called relational tables. A relational database is a
collection of relational tables. RDMS is the data manager for relational databases.

RDMS users use Structured Query Language (SQL), the industry-standard relational
database management language, and some RDMS extensions to SQL to interact with
RDMS databases.

Interactive Processing Facility (IPF) users can use the IPF SQL Interface to manage
RDMS databases using SQL.

This guide serves two purposes. First, it is a procedural guide to common RDMS
end-user tasks performed in SQL. These tasks include retrieving, updating, inserting, and
deleting rows from RDMS tables; thread control; and concurrency control. This guide is
not intended as a text for learning SQL. It is meant to serve the occasional user as well
as the SQL user unfamiliar with RDMS.

The guide’s second purpose is to document the IPF SQL Interface. The IPF SQL
Interface enables IPF users to use RDMS databases.

Scope
The end-user tasks covered in this guide include managing threads; querying and
updating tables; and using views, variables, and locks.

The IPF SQL Interface sections of this guide cover

• Logging on to and off from the IPF SQL Interface

• Specifying a data manager
• Beginning and ending threads
• Getting online help
• Saving SQL statements and IPF commands in a file for later execution

7831 0778–003 1–1

About This Guide

• Using variables with the IPF SQL Interface

• Using the IPF SQL Interface as an application prototype and development platform

This guide explains the necessary concepts and terminology for each task. It also
provides examples of how to perform each task.

Audience
The primary audience for this guide is all RDMS end users, which includes

• Any RDMS end user who needs assistance with SQL or other end-user tasks
• IPF SQL Interface users
• Programmers developing or maintaining applications written in third-generation
programming languages such as UCS COBOL, UCS FORTRAN, UCS C, UCS Pascal,
ASCII COBOL or ASCII FORTRAN

1.1. Documentation Updates

This document contains all the information that was available at the time of publication.
Changes identified after release of this document are included in problem list entry (PLE)
18726157. To obtain a copy of the PLE, contact your Unisys service representative or
access the current PLE from the Unisys Product Support Web site:

[Link]

Note: If you are not logged into the Product Support site, you will be asked to do so.

1.2. Prerequisites
This guide assumes that users have a working knowledge of the Exec. It also assumes
that users can work effectively with the interface of their choice. This means, for
example, that they know how to write programs in the third-generation programming
language they are using, or, if they are IPF SQL Interface users, that they know how to
use IPF.

As for relational database competence, this guide assumes that most users have had
some training and have at least a basic understanding of relational concepts and
terminology. However, the guide contains a brief refresher of elementary concepts for
the benefit of new, out-of-practice, or infrequent users.

1–2 7831 0778–003

Section 2
Introduction

2.1. Database Management in the OS 2200

Environment
Unisys supports database management systems for OS 2200 systems through the
Universal Data System (UDS). UDS supports network databases through Data
Management System (DMS), flat file databases through Shared File System (SFS), and
relational databases through RDMS. UDS has two components that support all
databases, regardless of type:

• UDS Control
UDS Control handles all actual input/output operations on the database. It also
provides recovery and locking services for you.
• Unisys Repository Manager (UREP)
UREP handles repository tasks, such as storing table and view definitions.

UDS Control and UREP work together in many ways to support database activities.

RDMS is a relational database manager based on SQL that provides relational database
users with many services:

• Parsing SQL or other relational data management instructions from users

• Issuing diagnostic messages to users when it finds errors in their instructions $
Creating instruction packets from user instructions and executing them
• Providing data type conversion services
• Passing diagnostic and other messages back to users
• Providing interfaces for programming languages, application generators, interactive
tools, report generators, and other relational tools developed by Unisys and other
vendors

7831 0778–003 2–1

Introduction

2.2. Basic End-User Tasks

The basic tasks end users perform in working with a relational database include

• Retrieving data
You use retrieval statements to specify which rows and columns you want to
retrieve for examination or further processing. In a real estate database application
for example, you might want to see a list of all the customers who said the
maximum price they would pay for a home was $100,000 or more.
• Updating data
You use update statements to change the values in one or more columns of one or
more rows. For example, a family could change their minds and decide they prefer to
buy in New London instead of Woodbury. You must then update the preferred
location column for their row to indicate their correct preference.
• Inserting data
As you attract more customers and list more houses, you need to add rows to those
tables to keep them current.
• Deleting data
As customers buy houses, you need to delete the customers and the purchased
houses from tables.

These are the main tasks users perform regularly. Other tasks, like creating and dropping
tables, controlling access to tables, and managing recovery, are the responsibility of the
database administrator. For more information on these and other administrative tasks,
see the RDMS Administration Guide.

2–2 7831 0778–003

 Introduction

2.3. Structured Query Language (SQL)

RDMS users use SQL statements to create and access RDMS databases.

SQL, an acronym that stands for Structured Query Language, is a relational data
management language. The American National Standards Institute uses SQL as the
official standard for relational database languages. SQL is pronounced “ess-cue-ell” or
“sequel.”

SQL divorces the application program or end user from the physical and location
attributes of the data. Data manipulation commands are expressed in logical terms. A
SQL query declares an action to be applied to the data of interest. It makes no difference
where the data is located or how it is arranged. The data of interest is identified only in
logical terms, which includes

• Identifying the table or tables involved in the action (retrieval or update). Optionally,
the names of the tables can be qualified by a schema name. A query can involve one
or more tables, and the tables specified generally can involve multiple schemas.
• Identifying the rows to be selected from the tables. Rows are selected through
specifications that identify the rows of interest. The specifications include column
names whose associated value in the row can be compared or included in an
expression that is compared to other like expressions. These statements of “truth”
or “falseness” identify whether or not the row is to be included as the “set of
interest.” Optionally, columns can be qualified by a table and schema.
• Identifying the columns on which the action (retrieval or update) is specified. The
specifications include column names whose associated value is to be presented or
included in an expression of a column or columns for presentation from the rows in
the tables selected. For update actions, column names are associated with
corresponding values for assignment to the columns of the selected row set. In all
cases, the column names can be qualified by a table and schema name.

You use SQL to control a database and its data. SQL statements enable you to perform
all the basic tasks and most of the administrative tasks required for working with
relational databases.

RDMS supports entry-level SQL 92 and several extension to it. Some of these
extensions take advantage of the unique capabilities of RDMS, the Integrated Recovery
Utility (IRU), or the Exec. The thread control operations are examples of this kind of
extension to the standard. Refer to Section 4, “Threads,” for more information. Some
extensions of this kind are presented in this guide. RDMS also extends the capability of
some standard SQL statements and supports embedded SQL (ESQL), a method by
which you can incorporate SQL statement into third-generation programming languages.

For details on RDMS extensions to standard SQL and more information about ESQL, see
the RDMS SQL Programming Reference Manual.

7831 0778–003 2–3

Introduction

2–4 7831 0778–003

Section 3
Relational Concepts

3.1. Relational Data Model

The relational data model represents information, or data, in an organized way: as a table
or a group of tables. A table (also called a relation) is a collection of rows, each containing
values for a fixed number of columns. (Rows are also called tuples, and columns are also
called attributes.)

Relational tables are just like the tables you see every day in reports or timetables. Each
row in the table contains information about one item. Each column in a row reveals
something about the item the row describes. Each column has a set of permissible
values called its domain.

Tables look like and are similar to conventional sequential files. Like a file, each table has
a name. The rows in a table are like the records in a file and are, in fact, sometimes
referred to as “records.” The columns in a row are like the data fields in a record. Like
data fields, columns have names and data types associated with them.

RDMS stores the structural definition of each table in the repository. From the table
definition, RDMS can determine how many columns a given table has, the names of the
columns, and the data types.

Table 3-1 and Table 3-2 are examples of relational tables a real estate company might
use. These tables and their data are used in examples throughout this guide.

7831 0778–003 3–1

Relational Concepts

Table CUSTOMERS contains a customer identifier (CNO), the customer’s name

(CNAME), the maximum price the customer can pay for a house (MAXPRICE), and the
location desired by each customer (DESIREDLOC).

Table 3–1. Table CUSTOMERS

CNO CNAME MAXPRICE DESIREDLOC

C101 Thompson 150000 Woodbury

C102 Owen 70000 New London
C103 Johnson 90000 Woodbury
C104 Jones 100000 Greenville
C105 Smith 120000 Westchester

Table HOUSES contains a house identifier (HNO), the location of the house (LOCATION),
the asking price (PRICE), and a brief description of each house on the market
(DESCRIPTION).

Table 3–2. Table HOUSES

HNO LOCATION PRICE DESCRIPTION

H101 Woodbury 130000 2-bedroom

condominium
H102 Woodbury 40000 Handyman’s special
H103 Westchester 90000 Duplex
H104 Parkway Heights 85000 3-bedroom town
house
H105 Turtle Creek 110000 4-bedroom house

3–2 7831 0778–003

 Relational Concepts

3.2. Characteristics of Relational Tables

Relational tables have the following characteristics:

• Each table has a specific number of columns. You specify how many columns the
table is to have when you create it using the CREATE TABLE statement. You can
change the number of columns later by using the ALTER TABLE statement.
• Each relational table contains just one type of row (that is, each row has the same
format). This means that every row in a given table has the same +columns in the
same order.
• The intersection of a column and a row is called a data item. An example of a data
item in the HOUSES table is the intersection of the column Price with the row
describing house number H102. The specific value of a given data item is called a
data value. For example the data value of Price for house H102 is 40000.
• Each row in a table contains information about one item. For example, each row in
the HOUSES table contains information about one particular house.
• If a primary key is defined, part of each row must be unique. This means that for
each row, the value of one column (or a group of columns considered together) must
be unique. This column or set of columns that have a unique value for every row is
called the primary key. The group of columns that have a unique value when
considered together can include all columns in the table. No more than one row can
have a given primary key value.
• Each column in a table has a distinct name. Rows do not have names.

3.3. Tables and Views

A base table has physical data associated with it in permanent files that reside on a
computer system’s mass storage. You can work directly on tables by using SQL
statements to retrieve, change, add, or delete rows from tables in the database.

A view, also called a virtual table, is another way to look at the data that is physically
stored in tables in the database. A view has no physical data associated with it; instead,
a view is a filter through which you “view” the data in one or more tables. The definition
of a view is stored in the repository, just like the definition of a table. But the computer
mass storage system that holds the data for a view contains no permanent files.

Views and tables are so similar in most ways that you can use the terms “table” and
“view” interchangeably in this guide unless otherwise noted.

7831 0778–003 3–3

Relational Concepts

3–4 7831 0778–003

Section 4
Threads

4.1. Managing Threads, Steps, and Recovery

A thread is a work session with the databases in a particular application group. (An
application group is a collection of files and programs that make up a working
environment. A host computer system can have up to nine application groups.) A thread
is composed of steps. Steps are portions of the work session that begin and end with
thread control operations.

You can use the following thread control operations:

• BEGIN THREAD
• COMMIT
• ROLLBACK
• END THREAD

Any changes you make to the database are not made permanent until you execute a
COMMIT or END THREAD statement. Until that time, UDS Control keeps both the
updated and original copies of the data. If an error or system stop occurs before the end
of the step, UDS Control “rolls backs” the database to the state that existed at the start
of the step. This operation is called “recovery.” Recovery assures you of a consistent
database.

UDS Control and RDMS also lock updated data until the end of the step to prevent other
users from accessing uncommitted data.

7831 0778–003 4–1

Threads

4.2. Starting a Session

To establish communication with RDMS and to specify which database application group
you want to open, use the BEGIN THREAD statement. You must specify the BEGIN
THREAD statement before any other SQL statement.

While a thread is open, you can display the information in the database and make
changes to it. A thread remains open until you enter an END THREAD statement or log
off the system.

Only one application group can be open at a time. The application group that is currently
open must therefore be closed with the END THREAD statement before a new database
can be opened.

The BEGIN THREAD statement registers a thread with UDS and sets up the thread
working storage and environment.

To use the BEGIN THREAD statement, you specify

• The name of the thread

This is optional. If you omit the thread name, RDMS uses the program run-id.
• The application group name
Application group names are twelve or fewer alphanumeric characters (letters,
numbers, and underscores only), begin with a letter, and do not end with an
underscore. The default application group name is UDSSRC.
• Whether or not you plan to update the database
− If you specify READ or RETRIEVE on the FOR clause of the BEGIN THREAD
statement, you can retrieve data from the tables on which you have retrieval
privileges, but you cannot add, delete, or update rows, nor can you create, drop,
or modify tables. Attempts to do other than data retrieval result in an error.
− If you specify UPDATE in the FOR clause of the BEGIN THREAD statement, you
can retrieve, add, delete, and update data in tables for which you have the
corresponding privileges. You can also create, modify, or drop tables.
− If you do not specify whether or not you plan to update the database, RDMS
uses UPDATE(DEFERRED). This means that you can update the database and
that you are using the DEFERRED recovery option.

4–2 7831 0778–003

 Threads

• If you do plan to update the database, specify the type of recovery you want. Put the
recovery option in parentheses immediately following the UPDATE keyword. You or
the database administrator should choose one of these recovery options:
− DEFERRED
Use DEFERRED if you plan to make few updates (fewer than the
MAX-AOR-LIMIT configuration parameter, which is 10 by default) between
thread control operations, or if you plan to roll back changes. If you specify
UPDATE but you do not specify a recovery option, RDMS uses DEFERRED.
− QUICKLOOKS
Use QUICKLOOKS if you plan to make many changes to the database between
thread control operations (more than about 100). If you plan to make between 10
and 100 updates per step, it does not make much difference whether you
choose DEFERRED or QUICKLOOKS.
− COMMANDLOOKS
Use COMMANDLOOKS, the most expensive type of recovery, to provide
recovery to the beginning of a statement if the statement encounters errors. It is
recommended for the IPF SQL Interface and other interactive sessions.
(Interactive users are more likely to use erroneous statements than debugged
programs.)
• How RDMS should handle error messages
The UDSMESSAGE option instructs RDMS to print error messages immediately
instead of waiting for you to retrieve them. IPF SQL Interface users cannot use this
option because the IPF SQL Interface already has a mechanism for retrieving error
messages.

Examples
The following example registers thread T1 with RDMS in application group UDSSRC.
This thread cannot update the database.

BEGIN THREAD t1 FOR udssrc READ

The next example registers thread PUBS in application group UDSSRC. This thread can
update the database; any updates use the DEFERRED recovery option.

BEGIN THREAD pubs FOR udsscr UPDATE(DEFERRED)

7831 0778–003 4–3

Threads

4.3. Committing Changes

Use the COMMIT statement to preserve any changes you make to the [Link]
system takes the changes you made since the most recent COMMIT or BEGIN THREAD
statement and applies them to the database, making them permanent. Until you commit
the changes, the system has only changed a working copy of the database.

Use the COMMIT statement only if a thread is open. When you close a database with
the END THREAD statement, the system automatically commits the changes for you.

Committing a thread releases all locks except those set by LOCK statements.

Example
The following example writes all updates for the current step (since the last thread
control operation) to the database:

COMMIT

4.4. Canceling Changes

The ROLLBACK statement discards any updates made to the working copy of the
database since the last BEGIN THREAD, ROLLBACK, or COMMIT statement; thus, the
changes do not become permanent. This statement is useful if you make a mistake and
do not want to manually undo the changes.

The ROLLBACK statement may apply to application programs using relational

statements in two circumstances:

• You are making several updates to the database, get an unexpected error, and want
to commit the changes only if you can apply them all. If any one of the updates is
unsuccessful, you can use the ROLLBACK statement to discard all updates made by
the step.
• You are making temporary updates to generate a “what if” report. In this case, you
can discard all changes to the database with a ROLLBACK statement when you have
all the information you need.

Rolling back a thread releases all locks except those set by LOCK statements.

Example
The following example ends the current recovery step and discards any updates to the
database made since the last thread control operation:

ROLLBACK

4–4 7831 0778–003

 Threads

4.5. Ending a Session

When you finish working with a database, end the session using the END THREAD
statement. This statement closes the thread that is currently open and ends
communication with RDMS. It also writes any updates made to the database during the
last step. Since only one application group can be accessed by a thread, you do not need
to specify which thread or application group to close.

Example
The following example ends the current thread:

END THREAD

7831 0778–003 4–5

Threads

4–6 7831 0778–003

Section 5
Querying the Database

5.1. Query Specifications

SQL makes it easy for you to query a database to get answers to business questions.
The basic format for query specifications is SELECT. . . FROM. . . WHERE (although
other clauses can be added).

A query specification can be used alone or as a clause in some other SQL statements.
Used alone, it is the SELECT statement. Other SQL statements that use query
specifications as clauses include

• DECLARE CURSOR
In SQL, you can describe the conditions for a group of rows you want to retrieve and
process one at a time. This description is called a cursor.
• CREATE VIEW
Refer to Section 7, “Threads.”
• UNLOAD
The UNLOAD statement is not covered in this guide. For information on the
UNLOAD statement, see the RDMS SQL Programming Reference Manual.

The query specification allows you to retrieve information from a database. You can
retrieve zero or more rows from a table. Only those rows with data values that satisfy
the selection criteria you specify are retrieved. Use several clauses with the query
specification to specify this criteria:

• Which columns you want to see

This can be a list of column names or an asterisk ( *) if you want to see all the
columns in the table. This part of the query specification is called the select list.
• Which table or tables are those columns in (FROM clause)
This is a list of table names. You must name every table from which you are
retrieving columns.

7831 0778–003 5–1

Querying the Database

You can also use these optional clauses to further refine a query:

• WHERE clause
The WHERE clause lets you specify conditions for the rows you want to see. If a
row meets the condition, the columns you specified are retrieved. Otherwise, the
row is not retrieved.
• GROUP BY clause
The GROUP BY clause lets you obtain summarized information. For example, you
can summarize a sales report and use the GROUP BY clause to find the total
month’s sales for each salesperson.
• HAVING clause
The HAVING clause lets you specify conditions each group specified in the GROUP
BY clause must meet. For example, you can use the HAVING clause to eliminate all
salespersons who have not met their bonus quotas for a sales contest.

5.1.1. Specifying Columns: Select List

To select all of the columns from the table specified in the FROM clause, use an asterisk
( *) after the keyword SELECT.

Examples
The following example selects all columns from table CUSTOMERS:
SELECT *
FROM customers

You can also select all columns from two or more tables as in the following query
specification, which specifies all columns from tables CUSTOMERS and HOUSES:

SELECT *
FROM customers, houses

To select all columns from one particular table when the FROM clause specifies several
tables, place the table name followed by a period in front of the asterisk. The following
example illustrates how to select all columns from table CUSTOMERS but only column
HNO from table HOUSES when joining the two tables:

SELECT customers.*, [Link]

FROM customers, houses

5–2 7831 0778–003

 Querying the Database

If you do not want all the columns from a table, you can specify the desired columns
with a list of column names.

You do not have to specify the table containing the column name in a select list. RDMS
matches the column with one of the table names specified in the FROM clause. It scans
the list of tables in the FROM clause (from left to right) until it matches a column with
the table that contains that column. RDMS then qualifies the column name implicitly
with the matching table name.

Assume, for example, that you have the following tables:

• Table R1 (with columns A, B, and C)

• Table R2 (with columns B, D, E, and F)

The results of the following two query specifications are the same:

SELECT A, D, B, C
FROM r1, r2
WHERE B = r2.B
SELECT r1.A, r2.D, r1.B, r1.C
FROM r1, r2
WHERE r1.B = r2.B

An entry in the select list can also be an arithmetic expression. For example, if you do
not want to retrieve the price of a house, but want to know the agent’s 6 percent
commission, enter

SELECT hno, price * .06

FROM houses

You get house numbers and sales commissions; you do not get the price of houses.

You can also include numeric constants or string literals in the select list:

SELECT 'THE HOUSE NUMBER IS', hno

FROM houses

In this case, each row retrieved has two data values: one character string with the value
THE HOUSE NUMBER IS and a second character string that contains a house
identification number.

You can also use the DISTINCT keyword to specify no duplicate rows. DISTINCT
requires RDMS to sort and compare rows to eliminate duplicates, except in special
cases such as the projection of all columns in a primary key or unique index. This means
that selecting data with DISTINCT takes longer than selecting data without it.

7831 0778–003 5–3

Querying the Database

If you use the GROUP BY clause, all column names used in the SELECT list must also be
in the GROUP BY column specification list.

You cannot include both column names and functions (AVG, COUNT, MAX, MIN, and
SUM) in the select list unless a GROUP BY clause exists. In that case, you can mix
columns in the GROUP BY column specification list with functions that affect columns
not included in the column specification list.

5.1.2. Specifying Tables: FROM Clause

In the FROM clause, provide the names of the tables and views from which you want to
select data. Use commas to separate table specifications in the list.

The table list can include views, but a view containing DISTINCT, GROUP BY, or built-in
functions cannot be joined to any other table or view. If you list such a view in the FROM
clause, the FROM clause can contain no other tables or views.

If you have more than one table name in the FROM clause, RDMS performs a join of the
tables. If you join tables and one of the tables is empty, nothing is selected (see 5.2).

You might want to use the same table more than once in a query specification. You can
do this with a correlation name. A correlation name is an alias for a table.

If you use a table or view specification followed by a correlation name, any column name
referenced in the select list can be qualified with that correlation name. This means that
column names qualified in this manner are assumed to be in the table associated with
the correlation name.

Examples
To list identically priced pairs of houses, you can specify X and Y as variable names for
the rows in the single table HOUSES, as in the following example:

SELECT [Link], [Link]

FROM houses x,houses y
WHERE [Link] = [Link]
AND NOT ([Link] = [Link])

The WHERE clause in this example uses a Boolean expression. The second part of the
Boolean expression, namely NOT ([Link] = [Link]), ensures that no house is paired
with itself. (Boolean expressions decide true or false kinds of conditions. For example,
the expression A = B is either true or false. A complete discussion of how RDMS uses
Boolean expressions is beyond the scope of this guide. See the RDMS SQL
Programming Reference Manual.)

5–4 7831 0778–003

 Querying the Database

Any table identified in a FROM clause is subject to a scoping rule that governs the range
within the overall query in which the table’s columns can be referenced. The scope of a
table includes the query specification whose FROM clause identifies the table as well as
all subqueries nested within the query specification, no matter how deep.

The reference to column C2 of table T1 in the following example is legal since it falls
within the scope of T1, which consists of the entire query:

SELECT * FROM t1
WHERE t1.c1 < (SELECT min(t2.d1)
FROM t2
WHERE t1.c2 = t2.d2)

The reference to column D3 of table T2 in the next example is not legal, however,
because it is referenced outside the scope of T2, which consists solely of the subquery:

SELECT * FROM t1
WHERE t1.c1 < (SELECT min(t2.d1)
FROM t2
WHERE t1.c2 = t2.d2)
AND t2.d3 IS NOT NULL

7831 0778–003 5–5

Querying the Database

5.1.3. Selecting Rows: WHERE Clause

The WHERE clause restricts which rows are selected. It first combines rows from all
tables specified in the FROM clause and then determines if each of these rows satisfies
the conditions in a Boolean expression. The WHERE clause selects data from only those
rows that meet the specified conditions. (Rows for which the Boolean expression is not
TRUE are not selected.)

Examples
Since CNO is the primary key of table CUSTOMERS and each row in a table has a unique
primary key, the following example selects no more than one row of the table:

WHERE [Link] = 'C101'

To continue, you may want to select all houses with an asking price of over $100,000.
The WHERE clause that restricts the houses to this price range is

WHERE [Link] > 100000

You can further restrict the selected rows by using an AND operand in a Boolean
expression. For example, you can select only the houses that cost over $100,000 and are
located in Woodbury:

WHERE [Link] > 100000

AND [Link] = 'Woodbury'

You can also restrict the selected rows by using nested SELECT statements or
subqueries. You can use the following WHERE clause, for example, to select all houses
that every customer can afford:

WHERE [Link] <= (SELECT min([Link])

FROM customers)

If you leave out the WHERE clause in a query specification, RDMS selects all rows from
the specified table or tables.

5–6 7831 0778–003

 Querying the Database

5.1.4. Retrieving Information on Groups of Rows: GROUP BY

Clause
You use the GROUP BY clause to direct RDMS how to divide rows into separate groups.
All rows in which the set of columns in the column specification list have the same set
of values form one group. Any row containing a null value in any grouping column (those
in the column specification list) is considered a distinct group.

The GROUP BY clause contains the instructions to RDMS to construct a conceptual

table (called a “grouped table”), which contains one row for each group. Each row in the
grouped table contains summary information about one group. This information may be
the sum of values of a column in that group or the largest value in a column in that
group.

Example
To get a list of towns (locations) where houses are listed and the average price of
houses in each location, you can use the following query specification:

SELECT location, AVG(Price)

FROM houses
GROUP BY location

Table 5-1 illustrates the data selected from table HOUSES.

Table 5–1. GROUP BY Result Table

LOCATION AVG(PRICE)

Woodbury 85000
Westchester 90000
Parkway Heights 85000
Turtle Creek 110000

7831 0778–003 5–7

Querying the Database

5.1.5. Restricting Groups: HAVING Clause

Just as the WHERE clause imposes search conditions on the table formed by the FROM
clause, the HAVING clause imposes search conditions on groups formed by the GROUP
BY clause.

RDMS forms the groups first and then applies the HAVING clause. One grouped table
row is formed for each group that satisfies the HAVING clause. The Boolean-expression
in the HAVING clause can refer to any column in the GROUP BY column specification
list. You can use any column not in that list as a parameter to a built-in function only.

If you have a HAVING clause but no GROUP BY clause, all rows in the FROM and
WHERE clauses comprise a single group, and the resulting grouped table consists of
zero or one row.

Example
To find the locations where the average price of a house exceeds $100,000, use the
following query specification:

SELECT location, AVG(price)

FROM houses
GROUP BY location
HAVING AVG(price) > 100000

If this example is applied to table HOUSES, one row is selected:

Turtle Creek 110000

5–8 7831 0778–003

 Querying the Database

5.2. Joining Tables

When RDMS joins tables, it first determines the cross product of the tables listed in the
FROM clause. Suppose the FROM clause lists these tables:

• Table T1 with columns A, B, C

• Table T2 with columns D, E, F, G
• Table T3 with columns H, I, J

The cross-product table is a conceptual table (that is, it does not physically exist in the
database). This table contains all the columns from all three tables: A, B, C, D, E, F, G, H,
I, and J.

The number of rows in the cross-product table is the product of the number of rows in
Tables T1, T2, and T3. For example, if T1 contains 10 rows, T2 contains 5 rows, and T3
contains 20 rows, the cross-product table contains 10X5X20 = 1000 rows.

Therefore, if you include an empty table (that is, with no rows in it), the resulting
cross-product table is also empty.

Second, RDMS applies the conditions in the WHERE clause to every row in the
cross-product table. If a row satisfies the conditions in the WHERE clause, it goes into a
reduced cross-product table. This table contains the same number of columns as the
original cross-product table. That is, if the Boolean expression is true for a given row, that
row goes into the reduced cross-product table. The number of rows in the reduced
product table is probably reduced as a result of conditions in the WHERE clause.

Third, RDMS now applies the query specification clause to the reduced cross-product
table. The only columns selected for the SELECT clause are those that meet any
conditions in the SELECT clause.

Example
The following example illustrates a query specification clause to join tables CUSTOMERS
and HOUSES.

SELECT cno, cname, maxprice, hno, location, price

FROM customers, houses
WHERE [Link] <= [Link]

In three steps, RDMS performs the following steps:

1. RDMS determines the cross-product table.

2. RDMS determines the reduced cross-product table by applying the WHERE clause.
3. RDMS determines the final result by applying the SELECT clause.

7831 0778–003 5–9

Querying the Database

Table 5-2 illustrates the joined tables CUSTOMERS and HOUSES in a cross-product
table.

Table 5–2. CUSTOMERS/HOUSES Cross-Product Table

CNO CNAME MAXPRICE DESIREDLOC HNO LOCATION PRICE DESCRIP

C101 Thompson 150000 Woodbury H101 Woodbury 130000 2-bedroom

C101 Thompson 150000 Woodbury H102 Woodbury 40000 Handyman
C101 Thompson 150000 Woodbury H103 Westchester 90000 Duplex
C101 Thompson 150000 Woodbury H104 Parkway Hts 85000 3-bedroom
C101 Thompson 150000 Woodbury H105 Turtle Creek 110000 4-bedroom
C102 Owen 70000 New London H101 Woodbury 130000 2-bedroom
C102 Owen 70000 New London H102 Woodbury 40000 Handyman
C102 Owen 70000 New London H103 Westchester 90000 Duplex
C102 Owen 70000 New London H104 Parkway Hts 85000 3-bedroom
C102 Owen 70000 New London H105 Turtle Creek 110000 4-bedroom
C103 Johnson 90000 Woodbury H101 Woodbury 130000 2-bedroom
C103 Johnson 90000 Woodbury H102 Woodbury 40000 Handyman
C103 Johnson 90000 Woodbury H103 Westchester 90000 Duplex
C103 Johnson 90000 Woodbury H104 Parkway Hts 85000 3-bedroom
C103 Johnson 90000 Woodbury H105 Turtle Creek 110000 4-bedroom
C104 Jones 100000 Greenville H101 Woodbury 130000 2-bedroom
C104 Jones 100000 Greenville H102 Woodbury 40000 Handyman
C104 Jones 100000 Greenville H103 Westchester 90000 Duplex
C104 Jones 100000 Greenville H104 Parkway Hts 85000 3-bedroom
C104 Jones 100000 Greenville H105 Turtle Creek 110000 4-bedroom
C105 Smith 120000 Westchester H101 Woodbury 130000 2-bedroom
C105 Smith 120000 Westchester H102 Woodbury 40000 Handyman
C105 Smith 120000 Westchester H103 Westchester 90000 Duplex
C105 Smith 120000 Westchester H104 Parkway Hts 85000 3-bedroom
C105 Smith 120000 Westchester H105 Turtle Creek 110000 4-bedroom

5–10 7831 0778–003

 Querying the Database

Table 5-3 illustrates the reduced cross-product table after the WHERE clause has been
applied.

Table 5–3. CUSTOMERS/HOUSES Cross-Product Table with WHERE Clause

CNO CNAME MAXPRICE DESIREDLOC HNO LOCATION PRICE DESCRIP

C101 Thompson 150000 Woodbury H101 Woodbury 130000 2-bedroom

C101 Thompson 150000 Woodbury H102 Woodbury 40000 Handyman
C101 Thompson 150000 Woodbury H103 Westchester 90000 Duplex
C101 Thompson 150000 Woodbury H104 Parkway Hts 85000 3-bedroom
C101 Thompson 150000 Woodbury H105 Turtle Creek 110000 4-bedroom
C102 Owen 70000 New London H102 Woodbury 40000 Handyman
C103 Johnson 90000 Woodbury H102 Woodbury 40000 Handyman
C103 Johnson 90000 Woodbury H103 Westchester 90000 Duplex
C103 Johnson 90000 Woodbury H104 Parkway Hts 85000 3-bedroom
C104 Jones 100000 Greenville H102 Woodbury 40000 Handyman
C104 Jones 100000 Greenville H103 Westchester 90000 Duplex
C104 Jones 100000 Greenville H104 Parkway Hts 90000 3-bedroom
C105 Smith 120000 Westchester H102 Woodbury 40000 Handyman
C105 Smith 120000 Westchester H103 Westchester 90000 Duplex
C105 Smith 120000 Westchester H104 Parkway Hts 85000 3-bedroom
C105 Smith 120000 Westchester H105 Turtle Creek 110000 4-bedroom

7831 0778–003 5–11

Querying the Database

Table 5-4 illustrates the final cross-product table after the SELECT clause has been
applied.

Table 5–4. CUSTOMERS/HOUSES Cross-Product Table with Query

Specification

CNO CNAME MAXPRICE HNO LOCATION PRICE

C101 Thompson 150000 H101 Woodbury 130000

C101 Thompson 150000 H101 Woodbury 130000
C101 Thompson 150000 H102 Woodbury 40000
C101 Thompson 150000 H103 Westchester 90000
C101 Thompson 150000 H104 Parkway Heights 85000
C101 Thompson 150000 H105 Turtle Creek 110000
C102 Owen 70000 H102 Woodbury 40000
C102 Owen 70000 H103 Westchester 90000
C102 Owen 70000 H104 Parkway Heights 85000
C103 Johnson 90000 H102 Woodbury 40000
C104 Jones 100000 H102 Woodbury 40000
C104 Jones 100000 H103 Westchester 90000
C104 Jones 100000 H104 Parkway Heights 85000
C105 Smith 120000 H102 Woodbury 40000
C105 Smith 120000 H103 Westchester 90000
C105 Smith 120000 H104 Parkway Heights 85000
C105 Smith 120000 H105 Turtle Creek 110000

In some cases when you do not select a column or columns from each table in the
FROM clause, RDMS is able to optimize retrieval by not returning all duplicate rows.

5–12 7831 0778–003

 Querying the Database

5.3. Using Cursors

You can use three SQL statements to declare cursors and make them available or
unavailable:

• DECLARE CURSOR
Use the DECLARE CURSOR statement to define the rows you want to retrieve.
• OPEN
Use the OPEN statement to instruct RDMS to prepare the rows you defined in the
DECLARE CURSOR statement.
• CLOSE
Use the CLOSE statement to instruct RDMS to release the rows it retrieved when it
opened the cursor.

You can also use a DROP CURSOR statement with RDMS, but it is not part of standard
SQL.

Working with cursors often involves the use of variables to pass values from an
application to RDMS and back. Variable usage depends on the interface used. For more
information about variables, see the documentation for the interface you are using. See
the RDMS SQL Programming Reference Manual also.

5.3.1. Declaring a Cursor (DECLARE CURSOR)

The DECLARE CURSOR statement is intended for application programs that need to
specify many rows and retrieve them one at a time for processing. To use the DECLARE
CURSOR statement, specify a name for the cursor and follow it with a query
specification. (Remember, a query specification is a SELECT statement used as a clause
in another statement.)

Examples
The following example defines a set of rows by joining selected columns from tables
CUSTOMERS and HOUSES. It restricts the cursor to rows where the asking price of the
house does not exceed the maximum price the customer is willing to pay.

DECLARE matchprice CURSOR

FOR SELECT cno, maxprice, hno, price
FROM customers, houses
WHERE [Link] <= [Link]

7831 0778–003 5–13

Querying the Database

The next example illustrates how to use an arithmetic expression with a column to give
you a calculated result instead of just the data in the table. It returns the sales agent’s
commission (6%) instead of the house price. It also sorts the retrieved rows by the
amount of the commission and the location of the house. (Since the commission is not
an actual column value but an expression, you use an unsigned integer to designate its
position in the list of items selected.)

DECLARE mycut CURSOR

FOR SELECT hno, price * 0.06, location
FROM houses
ORDER BY 2, location

The more work you give a cursor to do, the longer it takes to do it. Since the cursor
MYCUT has to perform both arithmetic calculations and sort the retrieved rows, it takes
longer to execute than an unsorted retrieval without calculations.

The next example illustrates how to use the UNION set operator with columns in two
tables. It returns all cities in ascending order that have either a customer or a house. The
UNION operator requires two query specifications.

DECLARE cities CURSOR

FOR SELECT desiredloc
FROM customers
UNION
SELECT location
FROM houses
ORDER BY 1

5.3.2. Opening a Cursor (OPEN)

You use the OPEN statement to make a cursor available for use after you have declared
it.

If you are working through an application program, you also use the OPEN statement to
associate new values with the cursor declaration.

Example
The following example opens the cursor MATCHPRICE that was declared in the example
in 4.3.1:

OPEN matchprice

5–14 7831 0778–003

 Querying the Database

5.3.3. Closing a Cursor (CLOSE)

When you open a cursor, RDMS does preparatory work to be able to efficiently retrieve
rows on subsequent statements. RDMS also maintains internal tables to keep track of
the cursor. These tables and retrieved data occupy main and mass storage space;
depending on the amount of data retrieved, considerable system resources may be
dedicated to a cursor.

When you close a cursor, you release most of the system resources used by the cursor.
It is a good idea to close cursors you no longer need. Since opening cursors can require
considerable processing time, however, do not close cursors prematurely.

To close a cursor, use the CLOSE statement with the cursor name.

Example
The following example closes cursor MATCHPRICE, which was opened in the example
in 4.3.2:

CLOSE matchprice

7831 0778–003 5–15

Querying the Database

5–16 7831 0778–003

Section 6
Changing Data

6.1. Deleting Rows from a Table (DELETE)

The DELETE statement deletes zero or more rows from a table in the database. To use
the DELETE statement, specify the name of the table and add a WHERE clause (unless
you want to delete every row in the table.)

Note: If you do not use the WHERE clause, RDMS deletes every row from the table.
Use a WHERE clause if you do not want to delete all the rows.

Examples
The following example deletes all rows from table HOUSES:

DELETE houses

The next example deletes all rows from table CUSTOMERS:

DELETE customers

You can also delete rows that meet certain conditions. In the next two examples, you
are eliminating from the database all customers who are unable to spend more than
$40,000 for a house:

DELETE customers
WHERE maxprice <= 40000
DELETE customers
WHERE maxprice - 40000 <= 0

The next example deletes only those rows where the value of the PRICE column is
greater than $100,000:

DELETE houses
WHERE price > 100000

The last example removes a specific row from a table:

DELETE houses
WHERE hno = 'H102'

7831 0778–003 6–1

Changing Data

6.2. Inserting Data in a Table (INSERT)

You use the INSERT statement to add a row to an existing table. The table may or may
not already contain rows of data. It must, however, already be created with the CREATE
TABLE statement. If you want to create a table, see the database administrator. For a
description of the process, see the RDMS Administration Guide.

To use the INSERT statement, you name the table into which you want to insert a row.
You must also specify the values you are inserting in the new row (the VALUES clause).
If you do not place the values into the columns in the same order as the columns were
defined, you must list the column names in front of the VALUES clause.

Instead of specifying a VALUES clause, you can use a query specification to select
values from other tables to form rows to be inserted.

Use commas to separate the data items in the values list. The order of the data items
must match the order of the columns. All columns must have a data item, and each data
item must be compatible with the column data type.

Examples
Here are some examples of INSERT statements:

INSERT INTO houses

VALUES ('H106', 'Anoka', 95000, 'Duplex')
INSERT INTO houses
(hno, price, location, description)
VALUES ('H106', 95000, 'Anoka', 'Duplex')

To insert a row with null values, use the keyword NULL:

INSERT INTO houses

(hno, price, location, description)
VALUES('H107', NULL, 'Hibbing', NULL)

The next two examples assume another table, HOUSES2, exists with three columns
defined as HNUM, LOC, and PRICE. An insertion from table HOUSES2 into table
HOUSES results in default values for DESCRIPTION:

INSERT INTO houses SELECT * FROM houses2

The last example inserts default values for PRICE and DESCRIPTION:

INSERT INTO houses (hno, location) SELECT hnum, loc FROM houses2

6–2 7831 0778–003

 Changing Data

6.3. Changing Values in a Table (UPDATE)

The UPDATE statement modifies rows in a table. Use this statement to change

• All data items in all rows of a table

• All data items in one row of a table
• Some data items in some rows of a table

Standard SQL has two versions of the UPDATE statement. Both are easy to use; they
differ in how you specify which row or rows to change. In one form, you use a WHERE
clause (see 5.1.3) to restrict the changes to the desired row or rows. In the other form,
you substitute WHERE CURRENT OF cursor name (use the name of the cursor) for the
WHERE clause. This restricts the update to the current row (that is, to the row you have
just retrieved using the cursor).

Note: If you do not put a WHERE clause on the UPDATE statement, the change
applies to all rows.

In both versions of the UPDATE statement, you first name the table in which you want
to change values. Next, you list the changes you want to make. Each change is a column
name followed by an equal sign ( =) and the new value that column is to take.

For the special case in which you wish to update a value and then immediately retrieve it
(for example, if a bank adds $10 to an account balance and then reports the new
balance), use the RETRIEVE INTO clause. The RETRIEVE INTO clause on the UPDATE
statement is available only in the embedded SQL (ESQL) interface to RDMS from
COBOL.

Note: When a table’s primary key is updated, it is treated as a DELETE statement

followed by an INSERT statement. If a duplicate key is encountered, RDMS rolls back to
the last commit point unless the COMMANDLOOKS recovery option is invoked. For
more information about the DELETE and INSERT statements, see 6.1 and 6.2.

Examples
The following example changes the asking price for house number H101 in table
HOUSES to $150,000:

UPDATE houses
SET(price = 150000)
WHERE hno = 'H101'

With one UPDATE statement, the next example changes the asking price for two
houses:

UPDATE houses
SET(price = 150000)
WHERE hno = 'H101' OR hno = 'H102'

7831 0778–003 6–3

Changing Data

6–4 7831 0778–003

Section 7
Views

7.1. Creating a View (CREATE VIEW)

You use the CREATE VIEW statement so that you can access data in a different way. A
view is a virtual table that can be defined in terms of one or more underlying tables or
views, or tables and views. Like tables, views can be created and dropped at any time. A
view is, in effect, a window into the underlying table or tables.

To use the CREATE VIEW statement, specify the following:

• A view name
• Column names
If you do not list column names, the default column names for the view are the
same as the column names resulting from the select list. Column names must be
unique within the view. If you do list column names, you must use as many as are
required to satisfy the select list.
• A query specification to retrieve data from the underlying tables and views to
populate the view you are creating
• An optional WITH CHECK OPTION clause
RDMS ensures that UPDATE and INSERT operations against the view satisfy the
WHERE clause of the definition of the view.
• An optional ACCESS CONTROL clause for security

The user who creates a view is the owner of that view. When a view is created within
the context of the CREATE SCHEMA statement, the owner is the authorization-id of the
CREATE SCHEMA statement. The owner is the only person who can grant and revoke
privileges for that view.

The owner of a view inherits access privileges from the base table or tables on which
the view depends. For example, if only one base table exists and the view owner has
SELECT and INSERT access privileges to that table, the owner has those privileges to
the view as well. The one exception to this rule is that if the view cannot be updated, the
only privilege that can be granted is SELECT.

You cannot specify the ORDER BY clause with the CREATE VIEW statement.

7831 0778–003 7–1

Views

Example
The following example defines a view using table HOUSES:

CREATE VIEW s1.v1_houses

(v1_hno, v1_loc, v1_price)
AS SELECT hno, location, price FROM [Link]
WHERE price < 150000
WITH CHECK OPTION

Views can be made up of views themselves or any combination of views and tables.
Every level of table or view join, select list, and WHERE clause is another level of
filtration to produce the data picture you want. This enables you to nest query
specifications to retrieve precisely the information you want.

7.1.1. Qualifier Name and View Name

You can use an explicit qualifier name to associate a view with a schema, or you can
make the association by default.

If the CREATE VIEW statement is made within the context of a CREATE SCHEMA
statement, the view name is implicitly associated with the name of the schema.

If the view is not in the CREATE SCHEMA statement and you use a USE DEFAULT
QUALIFIER statement, the default qualifier you specify qualifies the view.

Barring any of these conditions, the schema name is determined by the system default
qualifier. That is, it is either RDMS or the current user-id, depending on the setting of the
RDMS-DEFAULT-QUALIFIER configuration attribute (a UREP dynamic system
reconfiguration parameter).

Views are defined on logical, not physical, tables and views. Therefore, any attempt to
specify a version with the name of the view being defined or any views or tables
referenced within its definition are rejected.

Views are associated with a particular data set at run time by either explicitly specifying a
version for the view or by using the default version whenever the view is referenced in a
manipulative statement. Upon resolution of the view, the underlying tables use the
version currently associated with the view.

7–2 7831 0778–003

 Views

7.1.2. Column Names

You can provide a list of the column names you want to use in the view definition. Here
are some of the rules to keep in mind:

• Names must be valid names and unique within the view column list.
• The number of columns specified must be equal to the number of columns
appearing in the select list of the SELECT clause.
• The order of the columns in the view column list must parallel columns derived from
the select list of the SELECT clause.
• Columns can be derived from a built-in function, an arithmetic expression, or a
constant.
• The view column list is mandatory when the select list contains an arithmetic
expression, a function, a constant, or duplicate column names.

Examples
The following example associates columns HNO1 and LOC1 with HNO and LOC from
table [Link], and HNO2 and LOC2 with HNO and LOC from table [Link].
Since the tables in the FROM clause have identical column names, a view column list
must be included; otherwise, RDMS returns an error.

CREATE VIEW v1 (hno1,loc1,hno2,loc2)

AS SELECT [Link],[Link],[Link],[Link]
FROM [Link] a, [Link] b

In the next example, no view column list is given, and all columns are specified in the
SELECT statement. The columns for V2 have the same names as the columns in
[Link].

CREATE VIEW v2
AS SELECT * FROM [Link]

7831 0778–003 7–3

Views

7.1.3. View Query Specification

The query specification in the CREATE VIEW statement provides an additional resource
for focusing on the underlying tables and views. Section 4 gives you details about
formulating a query specification, but be aware of the following restrictions when you
write the query specification in the CREATE VIEW statement.

• All columns specified in the SELECT clause, Boolean expression, and GROUP BY or
HAVING clause must belong to one of the tables or views in the FROM clause.
• Tables and views referenced must already exist.
• The user of the view being defined must at least have select privileges on all the
tables referenced in the statement.
• The UNION[ALL] set operator is not allowed.

Also, you cannot put an aggregate operation in the query specification if you specify a
view in the FROM clause that has an aggregate operation in its query specification.
Aggregate operations are

• DISTINCT keyword
• GROUP BY clause
• HAVING clause
• Built-in functions
− SUM
− AVG
− MAX
− MIN
− COUNT

7–4 7831 0778–003

 Views

7.1.4. Updating Views

You can use some views in an UPDATE, INSERT, or DELETE statement to alter the
contents of the underlying table. A view must meet the following conditions to be used
in an UPDATE statement:

• The view must be derived from a single underlying base table. The FROM clause of
the view must list no more than one table or view.
• The select list cannot contain arithmetic expressions, constants, or functions; it can
contain only column names or an asterisk.
• The view cannot contain a DISTINCT specification in the SELECT clause. $ The view
cannot contain a GROUP BY clause.
• The view’s list of column names can rename any one column in the base table no
more than once.
• Any view listed in the view’s FROM clause must adhere to all of these rules.
• The view cannot contain a subquery or the UNION[ALL] set operator.

A view must meet the following conditions to be used in an INSERT or DELETE

statement:

• The view must meet all the aforementioned conditions for the UPDATE statement.
• The view must select all the columns in the underlying base table. In other words,
there must be an exact, one-to-one correspondence between the columns in the
view and the columns in the underlying base table.

Note: If certain aspects of a view’s underlying table are altered, that view and any
other views dependent on that table are dropped. Keep this in mind particularly when
using the ALTER TABLE statement.

7831 0778–003 7–5

Views

7.1.5. View WITH CHECK OPTION Clause

The WITH CHECK OPTION clause on the view definition directs RDMS to verify that any
updated or inserted values satisfy the view WHERE clause. This enables you to enforce
domain constraints that logically exist on columns in a base table. With the WITH CHECK
OPTION clause, the WHERE clause is checked by any view that builds on this view.

Examples
Suppose that table ORDERS contains the column MONTH that is defined to contain a
2-digit decimal value in the range 1 through 12. Column MONTH is declared as a 2-digit
decimal number with no digits after the decimal point. You can enforce this constraint by
building the following view:

CREATE VIEW verified_orders AS SELECT * FROM orders

WHERE month BETWEEN 1 AND 12
WITH CHECK OPTION

By using view VERIFIED_ORDERS instead of table ORDERS, all values of the column
MONTH are in the range 1 to 12.

Suppose table ORDERS contained another column, PRICE, that is defined to contain a
7-digit decimal value with 2 digits after the decimal point. Creating the following view
illustrates how CHECK OPTION works:

CREATE VIEW verified_orders1

AS SELECT * FROM verified_orders
WHERE price > 1000.00

The view VERIFIED_ORDERS1 is built on top of view VERIFIED_ORDERS, on which

WITH CHECK OPTION is specified. If PRICE has a value smaller than 1000.00, view
VERIFIED_ORDERS1 does not reject it because this view has no WITH CHECK OPTION
clause specified. However, if MONTH has a value not between 1 and 12, view
VERIFIED_ORDERS1 rejects the row.

7–6 7831 0778–003

 Views

7.2. Dropping a View (DROP VIEW)

You use the DROP VIEW statement to remove a view definition from a database.

To drop a view, specify it in the DROP VIEW statement.

Note: When you drop a view, RDMS automatically drops any other views that refer to
the dropped view in their query specifications.

Example
The following example drops definitions for views V1 and V2, which exist under qualifier
S1, and view VX under qualifier S2:

USE DEFAULT QUALIFIER s1

DROP VIEW v1, v2, [Link]

7831 0778–003 7–7

Views

7–8 7831 0778–003

Section 8
Restricting Access to Data

8.1. Using Locks

You might occasionally need to restrict access to a database so that you can

• Ensure that no one else changes a row while you are making changes
• Keep other users from accessing a table while you make major changes to its
contents or structure

You can use the LOCK statement to place a lock on a table while you are using it. There
are several kinds of locks, some more restrictive than others. Locks are released either
by the UNLOCK statement or by an END THREAD, ROLLBACK, or COMMIT statement;
in any case, all locks you place on a table are released when the session ends. Locks are,
therefore, a temporary restriction on data.

Locks are primarily an administrative concern and are covered in detail in the RDMS
Administration Guide and RDMS SQL Programming Reference Manual. This section
explains how to use the LOCK and UNLOCK statements so you can execute them with
the clauses and options specified by the database administrator specifically for you
based on the kind of work you do.

Use these statements only as directed by the database administrator. Careless use can
keep other users from successfully completing their work.

7831 0778–003 8–1

Restricting Access to Data

8.2. Locking Tables (LOCK)

With a relational database, more than one user can retrieve or update data from the
same relational table at the same time. Since most databases have more than one user,
multiple users must be able to concurrently access data. With concurrent access,
however, the risk of losing data integrity increases.

Ensuring data integrity means that you and other users never get conflicting data from
the same table at the same time. Use the LOCK statement to control the maintenance
and reliability of data by RDMS. For a more detailed discussion of when you need
locking, how you anticipate locking conflicts, and how you resolve conflicts, see the
RDMS Administration Guide.

In addition to ensuring data integrity, locking tables allow you to access data more
efficiently.

The LOCK statement specifies a lock for one or more tables during a step. This lock
remains in effect until you execute an UNLOCK statement for that table and a COMMIT,
ROLLBACK, or END THREAD statement.

During a thread, you cannot use two LOCK statements for the same table without an
intervening UNLOCK statement.

RDMS places implicit locks on tables or rows for you during data retrieval or table
update. Implicit locks are a transparent but necessary protection feature to ensure data
integrity.

The configuration attribute RDMS-SERIALIZABLE affects implicit locking. If

RDMS-SERIALIZABLE is set to YES, RDMS implicitly locks every table you reference.
Other users cannot update any table you reference until you execute a COMMIT,
ROLLBACK, or END THREAD statement. For more information, see the RDMS
Administration Guide.

Use the LOCK statement to place explicit locks on tables. You cannot place locks on
individual rows; only RDMS can do row-locking. You cannot lock views. If you need
locking protection while working with a view, you must use LOCK statements for each
underlying base table.

8–2 7831 0778–003

 Restricting Access to Data

To use the LOCK statement, specify

• Table name list

If you lock more than one table with a single LOCK statement, separate the names
of tables with commas
• IN clause
This specifies the lock mode and usage
− Mode
Use one of the following:
ο SHARE or SHARED (synonymous)
ο PROTECTED
ο EXCLUSIVE
− Usage
Use one of the following:
ο RETRIEVAL (this is the default)
ο UPDATE
• Conflict option
This instructs RDMS on what to do if one run has a locking conflict with another run.
Use one of the following:
− RETURN
− QUEUE (this is the default)

Use the values specified by the database administrator on the LOCK statement.

Examples
The following example locks table HOUSES for exclusive update. If a locking conflict
occurs, the LOCK statement is not executed and control is returned to you.

LOCK houses
IN EXCLUSIVE UPDATE MODE
ON CONFLICT RETURN

The next example locks table HOUSES for shared retrieval. If a locking conflict occurs,
the run waits for it to clear.

LOCK houses
IN SHARED RETRIEVAL MODE
ON CONFLICT QUEUE

7831 0778–003 8–3

Restricting Access to Data

8.3. Removing Locks (UNLOCK)

The UNLOCK statement releases the locking protocol for one or more tables. If the
locking protocol is in UPDATE mode, the unlock takes effect at the next commit or
rollback point.

To use the UNLOCK statement, specify the name of the table or tables you are
unlocking. If you unlock more than one table with a single UNLOCK statement, separate
the table names with commas.

Example
The following example unlocks table HOUSES:

UNLOCK houses

If you unlock more than one table with a single UNLOCK statement and the statement
returns an RDMS error (error status in the 6000 range), some of the tables may already
have been unlocked successfully before RDMS detected the error.

An unlock request can also become queued after some of the tables are unlocked. To
avoid this potentially confusing situation, use a separate UNLOCK statement for each
table.

8–4 7831 0778–003

Section 9
RDMS Interfaces

RDMS provides a number of interfaces to other products manufactured by Unisys and

other vendors. All RDMS interfaces support all basic relational operations including the
retrieval, insertion, deletion, and updating of data from within application programs. For
details about which SQL statement can be used with each interface, see the RDMS SQL
Programming Reference Manual.

The following interfaces are available:

• Interpreter interface
This interface is used with most third-generation programming languages, including
ASCII COBOL, ASCII FORTRAN, UCS C, UCS COBOL, UCS FORTRAN, and UCS
Pascal.
This interface enables you to place SQL statements marked by special syntax in the
source code of a program. This syntax, which varies from compiler to compiler,
instructs the compiler not to parse the SQL statements. Instead, the compiler inserts
instructions to hand control over to RDMS when the SQL statements are
encountered at run time. The program is compiled and linked or collected as usual.
RDMS is then called at run time to interpret and execute the SQL statements.
This interface offers great power and flexibility. However, since syntactical analysis
must occur every time a SQL statement is encountered, the resulting overhead can
slow execution.
Note: Since the SQL statements are interpreted as they are encountered during
execution, the order in which they appear in the program source code may have no
relation to the order in which they are interpreted. For example, you may be able to
use a cursor at line 5,000 that you do not declare until line 10,000, as long as the
program executes the cursor declaration before first using the cursor.
• Embedded SQL interface
The embedded SQL (ESQL) interface is available through UCS COBOL and UCS C.
You can use ESQL in either a static or dynamic mode. Static ESQL enables you to
include SQL statements that are fully compiled by RDMS at compile time. As a
result, execution of SQL statements may be significantly faster than when using the
interpreter interface to do the same tasks. This is particularly noticeable in tight
loops.

7831 0778–003 9–1

RDMS Interfaces

Dynamic ESQL resembles the interpreter interface. It provides you with a way to
include or programmatically create statements that are not compiled until run time.
Static and dynamic ESQL statements and interpreter interface calls may exist in the
same object module. This enables you to retain the flexibility of the interpreter
interface where necessary, but exploit the speed of static ESQL where possible.
Note: Static ESQL is compiled in the order in which it appears in a program’s
source code. This is different from the interpreter interface. For example, if you
declare a cursor at line 5,000 of the source listing, the system returns a fatal error at
compile time if you have any reference to that cursor on any line before line 5,000.
You may have to rearrange source listings to compile without errors.
As with the interpreter interface, dynamic ESQL is parsed and verified at run time.
This means that statements like USE DEFAULT QUALIFIER may follow the first use
of the default qualifier in question in the source listing as long as the USE DEFAULT
QUALIFIER statement is executed before the default qualifier is first used.
• IPF SQL Interface
The IPF SQL Interface couples the interpreter interface to IPF. When you use the IPF
SQL Interface, you retain all the features of IPF and you gain the ability to
interactively work with an RDMS database. IPF offers online help, procedures, easy
access to the Exec, and a powerful editor.
The IPF SQL Interface provides an ideal environment for prototyping and debugging
applications that handle relational databases. It is also a superb environment for the
casual or infrequent database user. Refer to Section 10, “IPF SQL Interface,” for
more information.
• Module language interface
The module language interface, available for UCS FORTRAN and UCS Ada, allows
SQL statements to be used in a module separate from the host program and to be
invoked through procedure calls in the host program.
• Oracle Transparent Gateway
The Oracle Transparent Gateway allows Oracle applications to access RDMS. For
more information, see the Oracle Transparent Gateway for RDMS Installation,
Administration, and Operations Guide.

For information about other interfaces to RDMS (such as Query Language Processor
[QLP], TransIT Open/OLTP, UniAccess Transaction Client/Server, and UniAccess ODBC
Server), see the RDMS Administration Guide.

9–2 7831 0778–003

Section 10
IPF SQL Interface

10.1. Using SQL Statements with IPF

IPF offers an integrated interface from which you can perform virtually all tasks on OS
2200 systems. The IPF SQL Interface is software that enables IPF users to access
RDMS databases and execute SQL statements interactively. SQL statements can also
be placed in files (or elements) for later execution, and used in IPF procedures. The IPF
SQL interface also features on-line help screens for both IPF commands and SQL
statements.

The IPF SQL Interface is easy to learn and use, so it makes a good platform for
occasional RDMS users. Being able to place SQL statements in files and procedures also
makes it an excellent platform for interactive applications or for prototyping applications
being developed in third-generation languages. You can develop and debug the SQL
statements quickly and easily without the cost and time needed for repetitive compiling,
linking, and executing a test program.

SQL statements can be used with IPF commands in an IPF session.

You can perform the following tasks:

• Alternate between SQL statements and IPF commands when working interactively
at a terminal
• Use SQL statements with IPF commands in IPF procedures
• Store SQL statements in a file with IPF commands and execute them all at once
with the IPF command IN

7831 0778–003 10–1

IPF SQL Interface

Some differences exist between the SQL statements and IPF commands:

• With most IPF commands, you use keyword parameters to direct IPF how to use the
command. You cannot use keywords with SQL statements. Instead, you use
clauses, some of which are optional. You must provide the clauses in a fixed order.
• The pattern-matching operators provided by the IPF SQL Interface conform to those
used by standard SQL. That is, an underscore ( _) character matches any single
character, and the percent sign ( %) matches any string of zero or more characters.
IPF 1100 EDIT 1100, however, uses the question mark ( ?) and the asterisk ( *) to
provide the same functions in editing sessions. If you have both the IPF SQL
Interface and IPF 1100 EDIT 1100 installed, be sure to use the correct operators for
the processor you are using.
• You cannot use abbreviations for SQL statements and keywords as you can with IPF
commands, with the exception that you can abbreviate
− ASCENDING as ASC
− CHARACTER as CHAR
− DESCENDING as DESC
− DECIMAL as DEC
• All SQL statements must be preceded by the letters SQL to inform IPF that it is a
SQL statement. IPF then passes the statement to RDMS for execution.

You must observe the following rules when you use SQL statements with the IPF SQL
Interface:

• Format
Precede each SQL statement with the letters SQL, as in the following example:
SQL SELECT * FROM HOUSES

• Do not enter multiple statements following a single SQL prefix. You must repeat the
letters SQL before entering each statement.
• Continuing a SQL statement
You can continue a SQL statement to the next line by using the IPF continuation
character. The default continuation character is the ampersand ( & ), unless you
change it to something else. You can change the continuation character by using the
system variable $CONTCHAR. You cannot continue a line in the middle of a name,
number, or character string. For more information, see the IPF Command Language
User’s Guide.
• Terminating a SQL statement
You can terminate a SQL statement by using a semicolon (;). If you do not end a
statement with a semicolon, the IPF SQL interface terminates the statement.
However, do not terminate a CREATE TRIGGER, FUNCTION, or PROCEDURE
statement with a semicolon.

10–2 7831 0778–003

 IPF SQL Interface

• SQL Comments
You may not include an SQL comment within an SQL statement submitted from IPF
SQL.

10.2. IPF SQL Interface Sessions

This subsection describes the operations for calling IPF 100, accessing RDMS, and
exiting the system. It explains how to

• Call the IPF processor (@IPF)

• Specify RDMS as the data manager
• Open a database (BEGIN THREAD)
• Get help with SQL statements
• Save changes to a database (COMMIT)
• Discard changes to a database (ROLLBACK)
• Close a database (END THREAD)
• Exit IPF (LOGOFF)

10.2.1. Calling the IPF Processor (@IPF)

You must log on to IPF before you can use IPF SQL Interface commands.

Format
@IPF

Example
í@IPF

System Response
IPF 1100 7R1A 12/08/97 [Link]
í~C~í

The first line of the response displays the level of IPF you are using, today’s date, and
the current time. The second line is the IPF prompt for input.

7831 0778–003 10–3

IPF SQL Interface

10.2.2. Specifying a Data Manager

Before you enter a SQL statement, you must specify the data manager you want to use.

Format
$DATAMANAGER:=data-manager

where data-manager is the name of the data manager you plan to use.

Example
The following example specifies RDMS as the data manager:

í~C~í$DATAMANAGER:=RDMS

You can abbreviate $DATAMANAGER to $DATA.

You must specify a data manager before you can get help on SQL statements.

10.2.3. Specifying an IPF Escape Character

Before you enter a SQL statement, set $SQLESCAPE to the escape character you want
to use. An escape character before $ or % indicates that this is not an IPF variable.

Format
$SQLESCAPE:="escape-character"

where escape-character is the escape character you plan to use, enclosed in quotation
marks.

Example
The following example specifies ! as the escape character:

í~C~í$SQLESCAPE:="!"

You cannot abbreviate $SQLESCAPE.

The default value for $SQLESCAPE at logon time is a backslash ( \ ).

10–4 7831 0778–003

 IPF SQL Interface

10.2.4. Opening an RDMS Database (BEGIN THREAD)

To establish communication with RDMS and to specify what application group you want
to open, use the BEGIN THREAD statement. You must specify the BEGIN THREAD
statement before any other SQL statement. Remember to precede the BEGIN THREAD
statement with SQL.

A thread remains open until you enter an END THREAD statement or log off from IPF. If
you log off from IPF without an END THREAD statement, the system implicitly issues a
COMMIT statement followed by an END THREAD statement.

You can have only one application group open at a time. You must therefore close the
application group that is currently open with the END THREAD statement before you can
open a new application group. You must also log off from IPF and log back on before you
can open a different application group.

The BEGIN THREAD statement registers a thread with UDS and sets up the thread
working storage and environment. The BEGIN THREAD statement also specifies

• The application group you want to use

• Whether or not you plan to update the database
• If you do plan to update the database, the type of recovery you want

Note: The IPF SQL Interface does not require a UDS message option since it always
displays available UDS messages.

For the BEGIN THREAD statement syntax, see the RDMS SQL Programming Reference
Manual.

Examples
The following example registers thread T1 with RDMS in application group UDSSRC.
Thread T1 cannot update the database.

SQL BEGIN THREAD t1 FOR udssrc READ

The next example registers thread PUBS in application group UDSSRC. Thread PUBS
can update the database; any updates use the COMMANDLOOKS recovery option.

SQL BEGIN THREAD pubs FOR udssrc UPDATE(COMMANDLOOKS)

7831 0778–003 10–5

IPF SQL Interface

10.2.5. Selecting Recovery Options

Interactive users, like IPF SQL Interface users, should normally use the
COMMANDLOOKS recovery option. However, if you are using debugged stored IPF
procedures that contain SQL statements, you can gain even greater efficiency with one
of the other recovery options. The COMMANDLOOKS recovery option is the slowest—
and most expensive in terms of computer resources—of all recovery options.

Without the COMMANDLOOKS recovery option, most errors cause a rollback to the
beginning of the current step. The current step includes everything you have done since
the last time you executed a BEGIN THREAD, ROLLBACK, or COMMIT statement, or
since the system automatically rolled back the thread because of an error.

If you have a large number of updates to the database, you should use QUICKLOOKS.

If you have a small number of updates, the DEFERRED recovery option is very efficient.

The NONE recovery option means that no recovery is performed in the thread. Use it
only when you are updating tables located in storage areas defined as nonrecoverable.
Use of the NONE recovery option is not recommended.

Note: Since all RDMS internal files (RDT$FILE, FDT$FILE, AUTH$FILE, and
VIEWDEP$FILE) are recoverable, you cannot create, alter, or drop tables or views, or
grant or revoke privileges from a thread using the NONE recovery option.

Ask the database administrator about which recovery option to use.

10.2.6. Getting Help with SQL Statements

You can get online help from the IPF SQL Interface about SQL statements. However,
you must specify RDMS as the data manager.

Format
The most general help screen is available by entering the following any time after
specifying RDMS as the data manager:

SQL ?

10–6 7831 0778–003

 IPF SQL Interface

System Response

**SQL203200 IPF 1100 recognizes SQL as a set of statements used to access

í relational tables. Each SQL statement begins with the letters SQL,
í followed by a space, followed by the action to be performed by the data
í manager.
í
í To get help with any of the following SQL commands, enter the command
í followed by a question mark:
í
í SQL ALTER SQL BEGIN THREAD
í SQL CALL SQL CLOSE
í SQL COMMIT SQL CREATE INDEX
í SQL CREATE SCHEMA SQL CREATE TABLE
í SQL DEBUG SQL CREATE VIEW
í SQL DELETE SQL DROP CURSOR
í SQL DROP FUNCTION SQL DROP INDEX
í SQL DROP PROCEDURE SQL DROP TABLE
í SQL DROP VIEW SQL END THREAD
í SQL EXPLAIN SQL FETCH
í SQL FILE SQL FUNCTION
í (Enter "?" to see more SQL commands.)

The IPF SQL Interface does not pass SQL statements to RDMS for execution until a
BEGIN THREAD statement has executed. Until a SQL BEGIN THREAD statement is
entered, therefore, the only SQL statements for which help is available are SQL BEGIN
THREAD and SQL FILE (a file of stored commands can contain a SQL BEGIN THREAD
statement).

After you have executed a SQL BEGIN THREAD statement, you can get help on any SQL
statement by entering the statement as illustrated in the preceding screen followed by a
question mark ( ? ).

Example
The following example is requesting help on the CREATE TABLE statement:

SQL CREATE TABLE ?

7831 0778–003 10–7

IPF SQL Interface

System Response

í**SQL203200 You use the SQL CREATE TABLE command to add a new relational
í table to your database. The format of the SQL CREATE TABLE command is:
í
í { CREATE TABLE table-specification
í ( column definition
í [ , { column-definition |
í table-constraint-specification |
í storage-area-specification |
í index-specification } ] ... )
í Note that (1) you must specify at least one column definition immediately
í after the table-specification, and (2) if you include index-specification
í in a SQL CREATE TABLE command, it must be the last element of the
í command.
í Except for these restrictions, you may specify the elements of the SQL
í CREATE TABLE command in any order.
í
í
í
í
í
í

On the screen, CREATE and all words in uppercase letters are keywords; table-name and
all words in lowercase letters are variables (you supply a value). You select one of the
words enclosed in braces ( { } ); words enclosed in brackets ( [ ] )are optional. Ellipses
(. . .) mean repeat if desired.

You can get additional help by entering a single question mark. You can continue to enter
single question marks until you get this message:

This completes the message series. Enter a ? to repeat the series.

10–8 7831 0778–003

 IPF SQL Interface

10.2.7. Automatically Committing Changes to the Database

The following IPF commands automatically commit changes to the database if a thread
is open:

Describe

Inventory

Library

Pack

Position Run

XQT

The following IPF commands may automatically commit changes to the database if a
thread is open, depending on the operation or procedure:

“Call”

Condition Copy

Execute

For more information about IPF commands, see the IPF Command Language User’s
Guide.

7831 0778–003 10–9

IPF SQL Interface

10.2.8. Saving Changes to a Database (COMMIT)

You use the COMMIT statement to preserve any changes you make to a database.
RDMS applies the changes you made since the most recent COMMIT, ROLLBACK, or
BEGIN THREAD statement to the database, making them permanent. Until you commit
the changes, the system has changed only a working copy of the database. The
uncommitted changes are available only to you.

Use the COMMIT statement only if a thread is open. When you close a database with
the END THREAD statement, the system automatically commits the changes for you.

Committing a thread releases all implicit locks and all explicit locks for which an UNLOCK
statement has successfully executed.

Example
The following example writes all updates for the current step since the last thread
control operation, or COMMIT or ROLLBACK statement, to the database:

SQL COMMIT

10.2.9. Discarding Changes to a Database (ROLLBACK)

The ROLLBACK statement discards any updates made to the working copy of the
database since the last BEGIN THREAD, ROLLBACK, or COMMIT statement; thus, the
changes do not become permanent. This statement is useful if you make a mistake or
do not want to manually undo the changes. Generally, you use the ROLLBACK
statement in two situations:

• You make several updates to the database and want to commit the changes only if
you can apply them all. If any one of the updates is unsuccessful and causes an
error, you can use the ROLLBACK statement to discard all updates made by the
step.
• You make temporary updates to generate a “what if” report. In this case, you can
discard all changes to the database with a ROLLBACK statement when you have all
the information you need.

Rolling back a thread releases all implicit locks and all explicit locks for which an
UNLOCK statement has successfully executed.

Example
The following example ends the current recovery step and discards any updates to the
database made since the last BEGIN THREAD, COMMIT, or ROLLBACK statement:

SQL ROLLBACK

10–10 7831 0778–003

 IPF SQL Interface

10.2.10. Closing a Database (END THREAD)

When you finish working with an application group, end the session with the END
THREAD statement. This statement closes the application group that is currently open
and ends communication with RDMS. It also commits any updates made to the
database since the last thread control operation. Since only one application group can be
open at a time, you do not need to specify which application group to close.

Examples
The following example ends the current thread:

SQL END THREAD

The next example, with its TERMINATE recovery step counter option, ends both the
current thread and the current recovery step counter:

SQL END THREAD TERMINATE

10.2.11. Exiting IPF (LOGOFF)

Format
To exit IPF, enter

LOGOFF

Example
í~C~íLOGOFF

System Response
íEND IPF
í

The last, unaccompanied SOE character (í) indicates you are no longer in IPF.

Note: If you log off without entering the END THREAD statement, all pending updates
are automatically committed to the database. If you do not wish to make the updates
permanent, you must roll them back with a SQL ROLLBACK statement before you log
off from the IPF SQL Interface.

7831 0778–003 10–11

IPF SQL Interface

10.3. Saving SQL Statements in a File

You can use the FILE command to execute one or more SQL statements that are saved
in a file.

You can use the FILE command to enter a series of SQL statements that exceeds the
number of characters you can enter directly on the terminal.

Format
SQL FILE filename

where filename is either a data file or an element in a directory.

Examples
SQL FILE abc*infile.
SQL FILE abc*[Link]
SQL FILE abc*[Link]/ver1
SQL FILE inelt

If you enter the element name without a directory, the IPF working directory is searched
for the element.

When using the FILE command

• The file you name on the FILE command can be in a directory or in a data file. If you
name a directory, you must specify an element.
• The file must contain one or more SQL statements. Each statement must begin with
SQL. Each line of a statement, except the last line, must end with a continuation
character. The continuation character must match the value of the IPF system
variable $CONTCHAR. The default is the ampersand ( & ).
• You cannot use the FILE command as one of the statements in the file.
• If a thread is not open when you use a FILE command, the first statement in the file
must be BEGIN THREAD.
• If an incorrect read/write key is entered, the Exec aborts. For more information, see
the ER Programming Reference Manual.
• If the contents of the file specified on the FILE command is blank or empty, the SOE
character (í) is returned. No error message is returned. Blank lines do not stop
processing.

10–12 7831 0778–003

 IPF SQL Interface

The following example illustrates the contents of a file (INELT) in the IPF home directory
that contains SQL statements:

í~C~íold inelt
í~C~íprint all
íSQL SELECT * FROM &
í CUSTOMERS WHERE &
í Desiredloc='Woodbury'
íSQL INSERT INTO HOUSES &
í (Hno,Price,Location,Description) &
í VALUES ('H102',80000,'Plymouth','Bi-level')
í~C~í

To execute these SQL statements, enter

í~C~íSQL FILE inelt

When you enter a FILE command, the IPF SQL Interface accesses the specified file, and
then reads, displays (see below), and executes each SQL statement. The IPF SQL
Interface executes all SQL statements in the file, even if it detects an error in the
execution of a statement.

You can determine whether or not you want the FILE command to display the SQL
statements by setting the $SQLFILEDISPLAY system variable to a value or TRUE or
FALSE:

• When $SQLFILEDISPLAY is set to TRUE, the FILE command displays all SQL
statements in the file.
• When $SQLFILEDISPLAY is set to FALSE, the FILE command displays no SQL
statements at all. It does, however, read and execute the statements, and it displays
any error messages resulting from their execution.

The $SQLFILEDISPLAY system variable is set to TRUE at the beginning of a session.

7831 0778–003 10–13

IPF SQL Interface

10.4. Query Result Output

Some queries you make produce small amounts of output easily viewed on the screen in
line mode. However, queries can result in very large data output, especially if the
database is large or if one or more joins are required.

You can handle large output from the IPF SQL Interface two ways: You can direct the
output to a file or you can use full-screen mode.

10.4.1. Saving Query Results in a File

The IPF OUT command is useful when you expect very large output or when you want
to save a copy of the query results. The OUT command diverts output directed to the
screen to an existing file. You can then print the file or use IPF 1100 EDIT 1100 to
examine it.

Example
The following example illustrates the use of the OUT command to save query results in a
file:

í~C~íCREATE FILE=outfile.

This creates a file named OUTFILE.

í~C~íOUT FILE=outfile.

This informs IPF that the destination file for the OUT command is OUTFILE.

í~C~íSQL SELECT * FROM CUSTOMERS

This SELECT statement instructs the IPF SQL Interface to display the contents of the
CUSTOMERS table.

í~C~íOUT FILE=TERMINAL

This informs IPF that the destination file for the OUT command is once again the
terminal.

í~C~íOLD FILE=outfile.

The OLD command copies the contents of the file OUTFILE into the IPF workspace.

You can now examine the data on the screen, or you can copy the file to a printer to
obtain a hard copy.

Note: If you are in IPF full-screen mode and you set $SQLSCREEN := TRUE, IPF
ignores the OUT command. For more information about full-screen displays of SQL
queries, see 10.4.2.

10–14 7831 0778–003

 IPF SQL Interface

10.4.2. Using SQL Full-Screen Mode

In IPF line mode, some SQL queries produce too much information for you to use. For
example

• Results can scroll off the screen faster than you can read.
• Results might be too wide to fit on a single line of the screen, making them hard to
understand.
• Sometimes you may need to abort the output from a query because the amount of
information it produces is just too large. For example, if you have a large database,
you can have thousands of rows of data coming from a query.

In these cases, you can direct the output to a file, as described in 9.4.1. But if all you
need is to slow the query results and make the output easier to read, you may find the
SQL full-screen mode helpful.

SQL full-screen mode is not the same as IPF screen mode. IPF screen mode does keep
output from scrolling off the screen so fast that you cannot read it, and it lets you abort
long output listings. But if the output from a query produces rows too wide to fit on one
line on the screen, IPF screen mode does nothing to make them more readable.

SQL full-screen mode, an enhancement to the IPF screen mode, provides for SQL query
output that is too wide and too lengthy for line mode or IPF screen mode.

Format
To use SQL full-screen mode, first enter IPF screen mode by entering

MODE SCREEN

For more information on using IPF in full-screen mode, see the IPF Command Language
User’s Guide.

Note: You can use the IPF OUT command to redirect output to a file in IPF screen
mode if $SQLSCREEN is FALSE. If $SQLSCREEN is TRUE, IPF ignores the OUT
command.

After entering MODE SCREEN, you are in IPF screen mode. Now enter the following on
the command line to use SQL full-screen mode:

$SQLSCREEN:=TRUE

7831 0778–003 10–15

IPF SQL Interface

The screen looks the same as before; however, SQL queries now receive special
handling as discussed in the rest of this subsection.

Notes:
1. The default value of $SQLSCREEN is FALSE. To use the SQL full-screen mode
feature, you must set $SQLSCREEN to TRUE.
2. For SQL queries to receive special handling, both $SQLSCREEN must be TRUE and
you must execute a MODE SCREEN command.
3. When you have both MODE SCREEN set and $SQLSCREEN is TRUE, only SQL
queries are affected. All other IPF operations function as they normally do in IPF
screen mode.
4. When $SQLSCREEN is set to TRUE, the maximum character size of a retrieved row
is 2800. When $SQLSCREEN is set to FALSE, the maximum character size of a
retrieved row is 12000.

In SQL full-screen mode, you see one screen at a time. You control when the next
screen is presented. And if the output is too wide for one line, you can shift the output to
the left and back to the right as needed. Use the fields at the bottom of the screen to
specify whether you want the next screen of output or a shift to the right or left. You can
specify the number of characters to shift using the right or left fields, or you can let the
IPF SQL Interface shift a screen width automatically. You can also put the number of
lines to scroll forward in the go field. You cannot scroll backwards.

When you have seen all the output, or if you want to discard unwanted output, transmit
from the done field.

10–16 7831 0778–003

 IPF SQL Interface

Example
In this example, which includes eight screens, the SQL statement was intentionally
designed to produce too much output for line mode to handle conveniently.

STOREN at VINTNU
UM---- -------name-------- ------address------- -----city------e- -zip- M----
1 trumpetvine wines trumpetvine court berkeley ca 94704 1
1 trumpetvine wines trumpetvine court berkeley ca 94704 2
1 trumpetvine wines trumpetvine court berkeley ca 94704 3
1 trumpetvine wines trumpetvine court berkeley ca 94704 4
1 trumpetvine wines trumpetvine court berkeley ca 94704 5
1 trumpetvine wines trumpetvine court berkeley ca 94704 6
1 trumpetvine wines trumpetvine court berkeley ca 94704 7
1 trumpetvine wines trumpetvine court berkeley ca 94704 8
1 trumpetvine wines trumpetvine court berkeley ca 94704 9
2 freds market 1940 university ave. berkeley ca 94704 1
2 freds market 1940 university ave. berkeley ca 94704 2
2 freds market 1940 university ave. berkeley ca 94704 3
2 freds market 1940 university ave. berkeley ca 94704 4
2 freds market 1940 university ave. berkeley ca 94704 5
2 freds market 1940 university ave. berkeley ca 94704 6
2 freds market 1940 university ave. berkeley ca 94704 7
2 freds market 1940 university ave. berkeley ca 94704 8
2 freds market 1940 university ave. berkeley ca 94704 9
3 college ave. liquor 1300 college ave. oakland ca 94577 1
3 college ave. liquor 1300 college ave. oakland ca 94577 2
go ____ left ____ right ____ done __

To shift the screen a screen width to the right, transmit from the right field:

----------NAME----------- --------LOCATION--------
joseph phelps vineyards st. helena ca
long vineyards st. helena ca
almaden vineyard san jose ca
beringer vineyards st. helena ca
geyser peak vineyards geyserville ca
fetzer vineyards redwood city ca
beaulieu vineyard rutherford ca
grgich hills cellar rutherford ca
inglenook vineyards san francisco ca
joseph phelps vineyards st. helena ca
long vineyards st. helena ca
almaden vineyard san jose ca
beringer vineyards st. helena ca
geyser peak vineyards geyserville ca
fetzer vineyards redwood city ca
beaulieu vineyard rutherford ca
grgich hills cellar rutherford ca
inglenook vineyards san francisco ca
joseph phelps vineyards st. helena ca
long vineyards st. helena ca
go ____ left ____ right ____ done __

7831 0778–003 10–17

IPF SQL Interface

Transmit from the left field to get back to column 1, and then transmit from the go field
to get the next screen:

STOREN at VINTNU
UM---- -------name-------- ------address------- -----city------ e- -zip- M---
3 college ave. liquor 1300 college ave. oakland ca 94577 3
3 college ave. liquor 1300 college ave. oakland ca 94577 4
3 college ave. liquor 1300 college ave. oakland ca 94577 5
3 college ave. liquor 1300 college ave. oakland ca 94577 6
3 college ave. liquor 1300 college ave. oakland ca 94577 7
3 college ave. liquor 1300 college ave. oakland ca 94577 8
3 college ave. liquor 1300 college ave. oakland ca 94577 9
4 the wine shoppe 105 university walk berkeley ca 94704 1
4 the wine shoppe 105 university walk berkeley ca 94704 2
4 the wine shoppe 105 university walk berkeley ca 94704 3
4 the wine shoppe 105 university walk berkeley ca 94704 4
4 the wine shoppe 105 university walk berkeley ca 94704 5
4 the wine shoppe 105 university walk berkeley ca 94704 6
4 the wine shoppe 105 university walk berkeley ca 94704 7
4 the wine shoppe 105 university walk berkeley ca 94704 8
4 the wine shoppe 105 university walk berkeley ca 94704 9
5 epstein wine vine 1000 liquor lane richmond ca 94777 1
5 epstein wine vine 1000 liquor lane richmond ca 94777 2
5 epstein wine vine 1000 liquor lane richmond ca 94777 3
5 epstein wine vine 1000 liquor lane richmond ca 94777 4
go ____ left ____ right ____ done __

To shift the screen a screen width to the right, transmit from the right field:

----------NAME----------- --------LOCATION--------
almaden vineyard san jose ca
beringer vineyards st. helena ca
geyser peak vineyards geyserville ca
fetzer vineyards redwood city ca
beaulieu vineyard rutherford ca
grgich hills cellar rutherford ca
inglenook vineyards san francisco ca
joseph phelps vineyards st. helena ca
long vineyards st. helena ca
almaden vineyard san jose ca
beringer vineyards st. helena ca
geyser peak vineyards geyserville ca
fetzer vineyards redwood city ca
beaulieu vineyard rutherford ca
grgich hills cellar rutherford ca
inglenook vineyards san francisco ca
joseph phelps vineyards st. helena ca
long vineyards st. helena ca
almaden vineyard san jose ca
beringer vineyards st. helena ca
go ____ left ____ right ____ done __

10–18 7831 0778–003

 IPF SQL Interface

Transmit from the left field to get back to column 1, and then transmit from the go field
to get the next screen:

STOREN at VINTNU
UM---- -------name-------- ------address------- -----city------ e- -zip- M---
5 epstein wine vine 1000 liquor lane richmond ca 94777 5
5 epstein wine vine 1000 liquor lane richmond ca 94777 6
5 epstein wine vine 1000 liquor lane richmond ca 94777 7
5 epstein wine vine 1000 liquor lane richmond ca 94777 8
5 epstein wine vine 1000 liquor lane richmond ca 94777 9
6 northside wines 130 euclid ave. berkeley ca 94711 1
6 northside wines 130 euclid ave. berkeley ca 94711 2
6 northside wines 130 euclid ave. berkeley ca 94711 3
6 northside wines 130 euclid ave. berkeley ca 94711 4
6 northside wines 130 euclid ave. berkeley ca 94711 5
6 northside wines 130 euclid ave. berkeley ca 94711 6
6 northside wines 130 euclid ave. berkeley ca 94711 7
6 northside wines 130 euclid ave. berkeley ca 94711 8
6 northside wines 130 euclid ave. berkeley ca 94711 9
7 j.v. liquor 1614 university ave. berkeley ca 94706 1
7 j.v. liquor 1614 university ave. berkeley ca 94706 2
7 j.v. liquor 1614 university ave. berkeley ca 94706 3
7 j.v. liquor 1614 university ave. berkeley ca 94706 4
7 j.v. liquor 1614 university ave. berkeley ca 94706 5
7 j.v. liquor 1614 university ave. berkeley ca 94706 6
go ____ left ____ right ____ done __

7831 0778–003 10–19

IPF SQL Interface

To shift the screen a screen width to the right, transmit from the right field:

----------NAME----------- --------LOCATION--------
geyser peak vineyards geyserville ca
fetzer vineyards redwood city ca
beaulieu vineyard rutherford ca
grgich hills cellar rutherford ca
inglenook vineyards san francisco ca
joseph phelps vineyards st. helena ca
long vineyards st. helena ca
almaden vineyard san jose ca
beringer vineyards st. helena ca
geyser peak vineyards geyserville ca
fetzer vineyards redwood city ca
beaulieu vineyard rutherford ca
grgich hills cellar rutherford ca
inglenook vineyards san francisco ca
joseph phelps vineyards st. helena ca
long vineyards st. helena ca
almaden vineyard san jose ca
beringer vineyards st. helena ca
geyser peak vineyards geyserville ca
fetzer vineyards redwood city ca
go ____ left ____ right ____ done __

10–20 7831 0778–003

 IPF SQL Interface

Transmit from the left field to get back to column 1, and then transmit from the go field
to get the next screen:

STOREN at VINTNU
UM---- -------name-------- ------address------- -----city------ e- -zip- M---
7 j.v. liquor 1614 university [Link] ca 94706 7
7 j.v. liquor 1614 university [Link] ca 94706 8
7 j.v. liquor 1614 university [Link] ca 94706 9
63 records selected

go ____ left ____ right 23__ done __ 3

7831 0778–003 10–21

IPF SQL Interface

Note the 23 entered in the right field on the preceding screen. Entering 23 and
transmitting from that position causes the IPF SQL Interface to shift the next screen only
23 columns to the right instead of the default screen width and results in the following
screen:

st
at VINTNU
--- ------address------- -----city------ e- -zip- M----- ----------NAME--------
1614 university ave. berkeley ca 94706 7 beaulieu vineyard
1614 university ave. berkeley ca 94706 8 grgich hills cellar
1614 university ave. berkeley ca 94706 9 inglenook vineyards

go ____ left ____ right ____ done __

Now transmit from the done field to return to the IPF command line.

10–22 7831 0778–003

Section 11
SQL Operations and Variables

11.1. Cursor Operations

By combining cursors with IPF procedures and variables, you can use the IPF SQL
Interface as a platform for interactive database applications or as a prototyping tool for
applications being developed in third-generation languages.

You can place SQL statements in IPF files and procedures to test the SQL statements
you plan to have in an application without having to repeatedly compile and execute the
application.

You can use SQL cursor operations to manipulate the data retrieved by a query one row
at a time. For example, if a real estate sales agent retired, you might want to retrieve all
the houses that agent had listed and assign them to other agents.

Using cursors, you can easily specify all the houses the retired agent listed with a single
query. Then you can update each one of them, assigning each to the agent best qualified
to sell the house.

The IPF SQL Interface supports most SQL cursor operations, including $ DECLARE
CURSOR

• DROP CURSOR
• CLOSE
• FETCH

The IPF SQL Interface does not support the following SQL cursor operations:

• OPEN
• LOCATE

These further considerations also apply:

• The IPF SQL Interface does not support the use of placeholder variables on the
DECLARE CURSOR statement
• You cannot specify an ORDER BY or WITH DESCRIPTION clause on the DECLARE
CURSOR statement
• The IPF SQL Interface supports only sequential access cursors; random or direct
access cursors are not supported

7831 0778–003 11–1

SQL Operations and Variables

• The syntax for the FETCH statement is slightly different for the IPF SQL Interface
than for other RDMS interfaces
• The IPF SQL Interface supports a maximum of five cursors to be declared at any one
time

11.1.1. Declaring a Cursor (SQL DECLARE CURSOR)

You use the DECLARE CURSOR statement to define a set of rows (sometimes called an
active set) that meet certain criteria.

Example
The following example selects all houses with an asking price less than or equal to the
buyer’s maximum price:

SQL DECLARE matchprice CURSOR &

SELECT cno, maxprice, hno, price &
FROM customers, houses &
WHERE [Link] <= [Link]

11.1.2. Dropping a Cursor (SQL DROP CURSOR)

You use the DROP CURSOR statement to discard an existing cursor declaration. This
releases the storage space used by the dropped cursor, which can then be used by a
new cursor declaration. Since the IPF SQL Interface allows no more than five cursors to
be declared at one time, you may have to drop one cursor to be able to declare another.

Once a cursor has been dropped, you must declare it again before you can use it.

Example
The following example drops cursor MATCHPRICE, which was created in the last
example:

SQL DROP CURSOR matchprice

11.1.3. Closing a Cursor (SQL CLOSE)

A cursor is a temporary table consisting of selected rows. These temporary tables are
kept in main storage. Depending on the number and kind of columns and the number of
rows selected, these temporary tables can become large enough to affect system
performance. When you close a cursor, you release the storage space back to the
system. Closing a cursor always resets the cursor pointer to the first row of the cursor.

All thread control operations close all open cursors, so closing is unnecessary just before
or just after a thread control operation.

11–2 7831 0778–003

 SQL Operations and Variables

Example
The following example closes cursor MATCHPRICE:

SQL CLOSE matchprice

11.1.4. Retrieving a Row from a Cursor (SQL FETCH)

After you have declared a cursor with the DECLARE CURSOR statement, use the
FETCH statement to retrieve one row at a time from the cursor.

Format
SQL FETCH [option] cursor-name INTO a1 [b1] [, a2 [b2]]. . .

where SQL, FETCH, and INTO are keywords and must be entered.

option
is optional and must be one of the following:

FIRST retrieves the first row of the cursor.

PRIOR moves the cursor pointer to the row immediately preceding its
current position and retrieves that row. If you execute a FETCH
PRIOR statement immediately after opening a cursor, you retrieve
the same row you retrieve when you execute a FETCH LAST
statement.
NEXT moves the cursor pointer to the next row and retrieves that row.
LAST retrieves the last row in a cursor.

cursor-name
is the name of the cursor from which the row is being fetched.

a1
is an IPF variable into which RDMS places the value of a column. You need a
separate variable for each column specified in the query specification of the cursor
declaration. Separate variables with commas.

b1
(optional) is an IPF indicator variable. You need an indicator variable for each column
specified in the query specification of the cursor declaration that can contain a null
value (see 11.2.2).

Example
The following example is retrieving a row of four columns from cursor C1:

SQL FETCH NEXT c1 INTO %v1 %v2, %v3 %v4, &

%v5 %v6, %v7

7831 0778–003 11–3

SQL Operations and Variables

11.2. IPF Variables

IPF variables can appear anywhere in a SQL statement after the SQL prefix. The IPF SQL
Interface scans each statement and replaces all IPF variable names (except those in the
INTO clause of a FETCH statement) with the contents of the variable. (The variables of
the INTO clause of a FETCH statement indicate where the results of the FETCH
statement are to be placed.)

The IPF SQL Interface uses the following rules for variable substitution:

• Begin the IPF variable name with a special character followed by 1 to 32 letters,
numbers, and underscores:
− The special character is % for IPF user variables and $ for IPF system variables.
− The first character following the special character must be a letter.
− You cannot have two underscore characters in a row.
• Use an escape character, which is the value of the IPF variable $SQLESCAPE whose
default value is the backslash ( \ ), to remove any special meaning from a special
character.
• Use a tilde ( ~ ) to mark the end of a variable name when the character that
immediately follows could be part of the variable name.

For example, suppose you make the following assignments:

set %v1:="variable1"
set %v2:="variable2"
set %v3:="variable3"

The following table illustrates the results of various combinations before and after
substitution:

Before After Substitution

%v1 %v2 variable1 variable2

%v1\%v2 variable1%v2
%v1\\%v2 variable1\variable2
%v1\%v2%v3 variable1 %v2variable3
%v1~abc variable1abc
%v1\~abc variable1~abc

Concatenation is achieved by juxtaposing the two variable names. Unlike other IPF
components, no concatenation operator is available.

11–4 7831 0778–003

 SQL Operations and Variables

11.2.1. Status Variables

The IPF SQL Interface places status information about the last SQL statement processed
in the following IPF system variables:

• $SQLERROR contains the last error number reported to you by the IPF SQL
Interface.
This error number is the number that appears in the prefix of an IPF SQL Interface
error message.
• $SQLCODE contains the SQL standard error code number:
− 0 means that the SQL command executed normally.
− +100 means that either no data or no further data exists. This status is returned
whenever the interpreter interface returns ‘6001’ or if a DELETE, INSERT, or
UPDATE statement does not affect a row.
− n, where n is a negative number, is the error code that the interpreter interface
would have returned ($SQLRETURNCODE).
• $SQLRETURNCODE contains the status code returned from RDMS.
• $SQLAUXILIARY contains the auxiliary status information returned by RDMS.
Table 11-1 explains how to interpret the value in $SQLAUXILIARY.

7831 0778–003 11–5

SQL Operations and Variables

Table 11–1. Error Codes and Auxiliary Status Information

$SQLRETURNCODE Contents of $SQLAUXILIARY

0000 For statement

UPDATE, the number of updated rows.
INSERT, the number of inserted rows.
DELETE, the number of deleted rows.
UNLOAD, the number of unloaded rows.
REVOKE, the number of internal rows deleted, indicating that
privileges were revoked.
1 to 1999 The column number where the syntax error was encountered in the
statement.
6000 to 6999 The number
0 means that RDMS did not roll back the thread. Unless
$SQLRETURNCODE indicates an end-of-cursor condition (6001), the
statement was not executed.
1 means that the statement was not executed to completion, and
RDMS rolled back any changes made by the statement before
execution was interrupted.
2 means that RDMS rolled back the thread. The statement was not
executed. Any changes made to the database since the last rollback
or commit are discarded. All cursors are closed.

8000 to 8999 Not applicable.

All other values The column number indicating how much of the statement was
parsed before the error was found. Note that a column number is not
always relevant.

Note: The IPF system variable $SQLSCREEN controls how IPF SQL Interface query
results are displayed. It is used in conjunction with IPF screen mode. $SQLSCREEN can
be set to either TRUE or FALSE, with FALSE as the default. In IPF line mode,
$SQLSCREEN is ignored. In IPF screen mode, $SQLSCREEN controls the query result
display. For more information on $SQLSCREEN, see 10.4.2.

11–6 7831 0778–003

 SQL Operations and Variables

11.2.2. Indicator Variables

Indicator variables can be used on SQL CALL, FETCH, and SET statements. An indicator
variable is required by columns that can contain a null value; it is optional for columns
that do not have null values. If a column in a row contains a null value, the indicator
variable for that column has a negative value.

Examples
The following example illustrates a SQL FETCH statement using indicator variables (IPF
indicator variables are prefaced with IND):

SQL FETCH c1 INTO %col1 %ind1, %col2 %ind2

The next example illustrates a sequence of statements that

• Creates a table
• Populates the table
• Fetches rows from the table using indicator variables
í~C~í$data:=rdms

**** Start a session with RDMS

í~C~ísql begin thread for udssrc update(deferred)

**** Create a table with 2 character columns, the first column does not
**** allow NULLS and the second column does.
í~C~ísql create table example in s1.c1 columns are col1 char(10) NOT NULL, &
í~C~ícol2 char(15) primary key k1 is col1
The CREATE TABLE statement completed successfully.

**** Insert some records into this created table.

í~C~ísql insert into example values('abc',null)
1 records were inserted.
í~C~ísql insert into example values('def','pqrstuvwxyz')
1 records were inserted.

**** Declare a cursor against this table, so that we may fetch against it.
í~C~ísql declare c1 cursor select col1, col2 from example
The DECLARE CURSOR statement has completed successfully.

**** Fetch from the declared cursor. In the first row in this table, COL2
**** has a NULL, so an indicator variable must be used. The indicator
**** variable returns a negative value. (Since COL1 does not allow nulls,
**** an indicator variable is optional.)
í~C~ísql fetch c1 into %col1 %ind1, %col2 %ind2
The FETCH statement has completed successfully.
í~C~ídisplay %col1
abc
í~C~ídisplay %ind1

7831 0778–003 11–7

SQL Operations and Variables

00
í~C~ídisplay %col2

í~C~ídisplay %ind2
-1

**** Fetch the next record. This record does not have any nulls, therefore
**** indicator variables are optional.
í~C~ísql fetch c1 into %col1 %ind1, %col2 %ind2
>The FETCH statement has completed successfully.
í~C~ídisplay %col1
def
í~C~ídisplay %ind1
00
í~C~ídisplay %col2
pqrstuvwxyz
í~C~ídisplay %ind2
00

**** Close the thread and log off from IPF.

í~C~ísql end thread
í~C~ílogoff

11–8 7831 0778–003

 SQL Operations and Variables

11.3. IPF Procedures and Variables

You can use SQL statements in IPF procedures to debug or prototype applications, to
specify different paths to take depending on the values of certain variables (flow of
control), and to repeat a group of statements. IPF procedures enable you to perform
many of the same tasks that are provided by a programming language.

IPF procedures use IPF variables to check, set, or change the values used by the
procedure. The variables are checked in flow of control instances, and a decision is made
based on the values of the variables.

To get error messages returned from RDMS when using IPF procedures, you must set
the IPF system variable $PROCDEBUG:=ON.

Example
This procedure

• Creates two tables (the files were cataloged and the UREP work done before this
IPF SQL Interface session)
• Populates the first table
• Uses the FETCH statement to retrieve data from the first table
This data is used to populate the second table using the INSERT statement.

**** Create a file in which to place the procedure

í~C~ínew file=sample

**** Turn on all completion and error messages so they are printed
í~C~í100 $procdebug:=on

**** Start the IPF SQL session

í~C~í200 $data:=rdms
í~C~í300 sql begin thread for udssrc update(deferred)

**** Create the two tables used in this procedure

í~C~í400 sql create table table1 in [Link] columns are col1 &
í~C~í500 decimal(4,0), col2 decimal(4,0) primary key k1 is col1
í~C~í600 sql create table duplicate in [Link] columns are col1 &
í~C~í700 decimal(4,0), col2 decimal(4,0) primary key k1 is col1

**** Populate table TABLE1

í~C~í800 sql insert into table1 values(1,NULL)
í~C~í900 sql insert into table1 values(2,2)
í~C~í1000 sql insert into table1 values(3,3)

**** Examine table TABLE1 to be sure it has rows in it

í~C~í1100 display "TABLE1"
í~C~í1200 sql select * from table1

7831 0778–003 11–9

SQL Operations and Variables

**** Examine table DUPLICATE to be sure it is empty

í~C~í1300 display "DUPLICATE"
í~C~í1400 sql select * from duplicate

**** Declare a cursor on TABLE1

í~C~í1500 sql declare c1 cursor select co11, col2 from table1

**** Fetch the first row using IPF variables and indicator variables
í~C~í1600 sql fetch c1 into %col1 %ind1, %col2 %ind2

**** Loop while there are no errors and rows remain

í~C~í1700 while $sqlreturncode == "0000"

**** Display the retrieved row

í~C~í1800 display %col1
í~C~í1900 display %ind1
í~C~í2000 display %col2
í~C~í2100 display %ind2

**** If COL2 is null, write out the word NULL to insert a null value into
**** table DUPLICATE. Otherwise, use the retrieved variables to insert the
**** value into table DUPLICATE.
í~C~í2200 if %ind2 < 0
í~C~í2300 sql insert into duplicate values( %col1, NULL)
í~C~í2400 else
í~C~í2500 sql insert into duplicate values( %col1, %col2)
í~C~í2600 endif

**** Fetch the next record

í~C~í2700 sql fetch c1 into %col1 %ind1, %col2 %ind2

**** End loop

í~C~í2800 endwhile

**** Display both tables. They are now the same.

í~C~í2900 display "TABLE1"
í~C~í3000 sql select * from table1
í~C~í3100 display "DUPLICATE"
í~C~í3200 sql select * from duplicate

**** Undo everything done in the thread and end the thread.
í~C~í3300 sql rollback
í~C~í3400 sql end thread

**** Make the file an IPF file type so it can run as a procedure
í~C~ítype ipf

**** Save the file

í~C~ísave sample

11–10 7831 0778–003

 SQL Operations and Variables

**** Execute the procedure

í~C~ísample

**** Here is the output from the procedure

The CREATE TABLE statement completed successfully.
The CREATE TABLE statement completed successfully.
1 records were inserted.
1 records were inserted.
1 records were inserted.
TABLE1
COL1- col2
1
2 2
3 3
3 records were selected.

DUPLICATE
COL1- col2
0 records were selected.
The DECLARE CURSOR statement has completed successfully.
The FETCH statement has completed successfully.
1
00

-1
1 records were inserted.
The FETCH statement has completed successfully.
2
00
2
00
1 records were inserted.
The FETCH statement has completed successfully.
3
00
3
00
1 records were inserted.
**SQL200860 An end of cursor condition has been detected by RDMS.
TABLE1
COL1- col2
1
2 2
3 3
3 records were selected.
DUPLICATE
COL1- col2
1
2 2
3 3
3 records were selected.
í~C~í

7831 0778–003 11–11

SQL Operations and Variables

11–12 7831 0778–003

Appendix A
Qualifier and Version Names

A.1. Purpose and Use

In addition to the name of the table itself, a table also has a qualifier and version name.
When you use the qualifier, table name, and version name all together, it is called a table
specification. You can use the qualifier and table name together without the version
name. You can also use the table and version name together without the qualifier.

“Qualifier” in an RDMS table specification is not the same as a qualifier in the Exec file
system. In this context, it is synonymous with a UREP “schema.” For more information
about schemas, see the UREP Administration Guide.

When you create a table, you state in which storage area the table is to be stored. You
can have many versions of a table. This means that you can have a single table definition
with several different sets of data that fit that definition. For example, a real estate
agency might have one table definition for houses, but have a set of data for Minnesota,
Wisconsin, Illinois, and so on. Each such set of data is called a version of the table.

Each version of a table requires a storage area version. In turn, each storage area version
requires its own Exec file. You create Exec files with the ECL command @CAT. You use
UREP to associate Exec files with storage area versions. For more information about
storage areas and versions, see the UREP Administration Guide.

The storage area version name is the same as the table version name. The only time the
storage area version name is not the same as the table version name is when the table
version name is PRODUCTION. The table version name PRODUCTION is associated
with the blank storage area name.

7831 0778–003 A–1

Qualifier and Version Names

A.2. Using Qualifiers

When you use a qualifier with the table name, put a period (.) between the qualifier and
the table name. Do not include any spaces.

Example
Assuming that the qualifier for a table is RDMS and the table name is HOUSES, you
enter the following to refer to the table:

[Link]

A.3. Using Versions

When you use a version with a table name, put a colon (:) between the table name and
the version name. Do not include any spaces.

Example
Assuming that the table name is HOUSES and the version is PRODUCTION, you enter
the following to refer to the table:

houses: production

Version names require some care in their use with the DECLARE CURSOR, OPEN (not
supported by IPF SQL), GRANT, and REVOKE statements. For more information on
these statements and version names, see the RDMS SQL Programming Reference
Manual.

A.4. Default Qualifier and Version Names

If you do not specify a qualifier, RDMS uses RDMS as the qualifier by default. If you do
not specify a version, RDMS uses the default version name, PRODUCTION. You can
change the default qualifier and the default version with the USE DEFAULT statement.

The USE DEFAULT statement establishes a new default qualifier or a new default
version name. The USE DEFAULT statement works like the ECL statement @QUAL. The
default qualifier and version name remain in effect for the life of a thread, or until you
establish new default names.

For data definition operations (CREATE TABLE, CREATE VIEW, GRANT) within a
CREATE SCHEMA statement, RDMS uses the schema name specified in the CREATE
SCHEMA statement as the qualifier.

A–2 7831 0778–003

 Qualifier and Version Names

A.5. Establishing a New Default Qualifier

(USE DEFAULT QUALIFIER)
To specify a default qualifier, use the USE DEFAULT QUALIFIER statement with the
qualifier name. The qualifier you supply must be a name. A qualifier can have up to 30
characters.

Example
The following example uses the qualifier DRUID:

USE DEFAULT QUALIFIER druid

Once you enter this statement, you can still use any other qualifier as required for a
given table name. But if you omit the qualifier for any table, RDMS uses the qualifier you
specified in the USE DEFAULT QUALIFIER statement.

The USE DEFAULT QUALIFIER statement remains in effect until the end of the session
or until a new USE DEFAULT QUALIFIER statement is used.

A.6. Establishing a Default Version

(USE DEFAULT VERSION)
To specify a default version, use the USE DEFAULT VERSION statement with the
version name. The version must be a name of up to 30 characters.

Example
The following example uses the version name JACQUES:

USE DEFAULT VERSION jacques

Once you enter this statement, you can still use any other version name required for a
given table name. But if you omit the version for any table, RDMS uses the version
name you specified in the USE DEFAULT VERSION statement.

The USE DEFAULT VERSION statement remains in effect until the end of the session or
until a new USE DEFAULT VERSION statement is used.

7831 0778–003 A–3

Qualifier and Version Names

A–4 7831 0778–003

Glossary

A
abnormal program termination (APT)
An abrupt end to a program that can be caused by an @@X keyin or another operator
keyin.

access control
An RDMS security mechanism for preventing unauthorized users from accessing a table
or altering its definition. See also data access control; definition access control.

ACOB
The ASCII COBOL compiler call.

ad hoc search
Synonym for nonindexed search.

Ada
A sophisticated high-level programming language.

ADT
See application definition table.

aggregate function
One of the RDMS built-in functions (AVG, MAX, MIN, SUM, or COUNT) that results in
one value from multiple values in a column. See also function.

allocation page
An RDMS page that contains information about available data pages and thereby
allocates unused data and index pages in a B-tree.

anomaly
An undesirable consequence, such as loss of data or inconsistent updates, which is
eliminated when a database is normalized.

application definition table (ADT)

The table that contains information about all application groups in use on the system. The
ADT describes applications created by DSR when a configuration is processed. The ADT
links UDS Control with a particular system and verifies that it is accessing the specified
application group.

7831 0778–003 Glossary–1

Glossary

APT
See abnormal program termination.

arithmetic expression
An expression that results in a numeric value, which can include arithmetic operators
(+,-, *, and / ) and numeric literals, column references, or variables.

arithmetic operation
Any of the basic operations of arithmetic such as addition, subtraction, multiplication, and
division.

ASCII COBOL
The Unisys implementation of the COBOL programming language for basic mode.

ASCII FORTRAN
The Unisys implementation of the FORTRAN programming language for basic mode.

assignable
The characteristic of a value or data type that permits the value, or the values of the data
type, to be assigned to data objects of a specified data type.

B
B-tree
The balanced data structure used by RDMS to store data. B-trees consist of a root page,
index pages, and data pages. Each RDMS table is stored as one or more B-trees. RDMS
creates a B-tree for the primary key of a table and each secondary index.

base dump
Synonym for database dump.

base relation
Synonym for relational table.

base table
Synonym for relational table.

bind
(1) To resolve a reference to a cursor. (2) To link an application program to the proper
application group.

Boolean existential function

See function.

Boolean expression
An expression that results in a value of TRUE, FALSE or UNKNOWN.

Boolean operator
The word AND, OR, or NOT, which can be included in an SQL statement to connect or
qualify the conditions specified.

Glossary–2 7831 0778–003

 Glossary

bound
Resolved or linked. See also bind.

brute force search

Synonym for nonindexed search.

built-in function
A predefined function that returns a value.

C
cache/disk recovery
The IRU process that recovers database updates present in cache memory after a power
outage or cache/disk subsystem failure.

cache/disk subsystem
A storage control unit of cache memory and its associated disk units.

cache hit rate

The rate at which UDS Control finds the data already in cache memory.

CCS
See character set.

CD flag bit
A flag bit used by RDMS when a deleted record contains an item value compressed in
one or more undeleted records on the same page.

character repertoire
The term used by the SQL standard to refer to a character set.

character set
A group of alphabetical, numeric, and special characters used to construct the words and
other elementary units of a spoken or written language. The difference between a
character set and a coded character set (CCS) is that in a character set, each character is
an abstract concept, whereas, in a CCS, each character is associated with a specific
binary value. ISO 8859-1 is a CCS. In Unisys user documentation, the term “character
set” is used for both character set and coded character set.

character string
A sequence or group of contiguous characters connected by code or keywords.

check constraint
See constraint.

child table
A relational table defined by a foreign key. The contents of one or more columns in the
child table are restricted to the primary key values or unique index values in the parent
table.

7831 0778–003 Glossary–3

Glossary

clause
An ordered sequence of words and characters (including names and special characters)
that form part or all of a command or statement.

coded character set (CCS)

See character set.

collation
Essentially, the sorting of characters. More specifically, the collating sequence is a set of
rules established by the cultural convention set and locale that determines how character
strings are compared in a particular character set.

column
A set of values, all of the same data type, in a set ordinal position in a relational table.

column constraint
See constraint.

column name
The name of a column in a relational table, which can also be used as an operand in an
arithmetic or Boolean expression.

column specification
A list of specific columns from a relational table. Users can select, retrieve, insert, delete,
or update data from columns in a column specification.

command
A set of syntactically correct words and clauses that instruct the system to perform a
task. A command can be divided into clauses that can be divided into phrases and
subclauses. In keeping with SQL standards in RDMS, specific SQL “commands” are
referred to as “SQL statements.”

command rollback
The recovery process that reverses the effect of a command without rolling back the
entire step.

comparable
The characteristic of two data objects that permits the value of one object to be
compared with the value of the other object.

comparison operator
Any of the various operators that compare two items of data (operands) including < (less
than), > (greater than), <= (less than or equal to), >= (greater than or equal to), = (equal
to), and f (not equal to), LIKE, NOT LIKE, IS NULL, IS NOT NULL, IN and NOT IN.
Synonymous with relational operator.

compile
To make source code executable by verifying that it is syntactically correct and
translating it into object code.

composite key
A primary key or secondary index that includes more than one column.

Glossary–4 7831 0778–003

 Glossary

compressed deleted flag bit

See CD flag bit.

conceptual table
Synonym for cursor.

concurrency
The extent to which multiple users can access the same database at the same time
without conflict.

constant
A value that remains unchanged during the execution of a program. Contrast with
variable.

constraint
A restriction placed on columns or rows in a relational table, the validity of which RDMS
is responsible for maintaining. A unique or primary key constraint ensures that no rows
have the same values in a specified column or columns. A check constraint establishes a
condition that every data item or row must satisfy. A referential or foreign key constraint
restricts the values of columns in one table to the primary key values in another table. A
NOT NULL constraint ensures that a value exists for the associated column in each row
of the table.

correlated query
An outer query referenced by one or more subqueries using outer references. Contrast
with uncorrelated query.

correlation name
An alias for an RDMS table within a query specification, the most common use of which
is to join a table with itself. Formerly called “tuple variable,” which is an obsolete term.

currency
The position of a pointer in a cursor.

cursor
A named set of data extracted from one or more RDMS tables with a pointer to the
current row. Synonymous with conceptual table.

D
D-bank
A bank that contains data belonging to a program or a UDS common bank.

data access control

An RDMS security mechanism for designating the owner of a table and establishing
control over access to data in the table.

data bank
See D-bank.

7831 0778–003 Glossary–5

Glossary

Data Definition Facility (DDF)

The UREP component for defining FDTs and RDTs.

data integrity
Data accuracy and consistency.

data item
The intersection of a column and a row in a table. The part of a row that contains one
category or entity of data such as a last name, a person’s age, or a social security
number. Items that fall into the same category in a table are listed in the same column.
See also data value.

Data Management System (DMS)

The Unisys data management software product that conforms to the CODASYL
(network) data model and enables data definition, manipulation, and maintenance in
mass storage database files.

data model
A means of organizing and representing information. See also relational data model.

data page
A portion of a table that a contains a number of data records, arranged to facilitate I/O
operations to and from the storage area and to serve as the basis for recovery of the
storage area. See also page.

data page number

A number assigned by RDMS to each data page it creates in a B-tree to uniquely identify
the page.

data storage definition (DSD)

The process that defines data storage areas and associates each storage area with an
Exec or TIP file.

data type
A description of the characteristics of the data values allowed such as numeric, date,
time, and timestamp.

data value
The specific value of a data item.

database dump
A copy of the database files as they exist at a given point during processing.
Synonymous with base dump.

DDF
See Data Definition Facility.

deadlock
A condition in which a run cannot proceed because of an unresolvable conflict by another
thread in the same application group for a system resource or facility.

decryption
The process of unscrambling encrypted data. Contrast with encryption.

Glossary–6 7831 0778–003

 Glossary

deferred update recovery

Synonym for medium recovery.

definition access control

An RDMS security mechanism for controlling access by users to tables.

definition-level lock
A lock on the FDT or RDT.

degree
The number of columns in a relational table, view, query, or subquery.

DELETE privilege
A privilege that owners of RDMS tables grant to other users to enable them to delete
rows. See also data access control.

descriptive text
The text that describes a data file (similar to comment lines in a program).

diacritical mark
A mark such as a circumflex ( ^ ) or tilde ( ~ ) added to a character to indicate a phonetic
value different from the base character. See also ISO 646:1983.

dieresis
Also spelled diaeresis. Two dots over the second of two adjacent vowels in a word to
indicate two separate sounds. In German, the two dots (Umlaut) occur over a single
vowel and indicate a change in the vowel sound caused by partial assimilation to a vowel
or semivowel in the following syllable. See also diacritical mark.

DMS
See Data Management System.

DSD
See data storage definition.

DSR
See dynamic system reconfiguration.

dump
See database dump.

dynamic ESQL
See embedded SQL.

dynamic system reconfiguration (DSR)

The UREP process by which users can dynamically change UDS Control configuration
parameters and reconfigure an application group.

7831 0778–003 Glossary–7

Glossary

E
embedded SQL (ESQL)
SQL statements placed in a UCS COBOL program with the special markers or delimiters,
EXEC SQL and END-EXEC. ESQL statements are compiled instead of interpreted and
take two forms: static and dynamic. Static ESQL is processed more extensively at
compile time than dynamic ESQL and thus executes faster. Dynamic ESQL receives
more extensive run-time processing than static ESQL but provides greater flexibility.

encryption
The process of scrambling user-defined data in RDMS database files to protect the
information in the files from users who are able but not authorized to access the files or
otherwise obtain some form of the data in the files. Contrast with decryption.

encryption user routine (EUR)

A program written by a system programmer with extensive knowledge of Unisys
operating systems to perform the encryption and decryption of RDMS database files.

entity
An object in the repository that defines a piece of information to UREP, such as qualified
entity names, entity types, schemas, and tables.

equi-join
A join operation in which the comparison operator is an equal sign.

ESQL
See embedded SQL.

EUR
See encryption user routine.

exclusive lock
A lock that prevents other threads from accessing a file or page until the lock is released.

Exec
See Executive.

Executive
The Unisys core software that controls the system’s operating environment, usually
referred to as the “Exec.”

EXISTS function
See function.

explicit lock
A user-specified lock to protect data in the RDMS database.

expression
A valid combination of constants, variables, operators, and functions that perform a
computation.

Glossary–8 7831 0778–003

 Glossary

Extended Processing Complex (XPC)

A systemwide accelerator for data on mass storage device types attached to Unisys
operating systems.

Extended Transaction Capacity (XTC)

A group of software components that enables increased processing capacity for TIP
database environments as well as for UDS/TIP and UDS/Exec database files running on
operating system hardware. The XTC group includes MHTIP and MHFS.

F
FCP
See file control page.

FDT
See file description table.

FETCH privilege
Synonym for SELECT privilege.

file control page (FCP)

The first page in a relational file that contains general storage information about the file
and a list of the root page numbers of the B-trees stored in the file.

file control page B-tree list

A set of two-word entries that contain the internal relation code for each B-tree stored in
the file and the page number of the root page for each B-tree stored in the file.

file description table (FDT)

The run-time definition for a storage area that corresponds to the characteristics
described in the repository storage area definition. FDTs reside in the FDT$ file for each
application group.

first normal form

A level of normalization in which no repeating groups occur.

flush
To remove a page from memory so that subsequent references to that page must read it
from mass storage.

foreign key
A column or columns that establish a relationship between the primary key of one table
(parent table) and a column or columns in a second table (child table). One table can be
both the parent and child table. Foreign keys limit the acceptable values for a column in
one table to the primary key or unique index values in another table.

foreign key constraint

See constraint.

FTN
The ASCII FORTRAN compiler call.

7831 0778–003 Glossary–9

Glossary

function
(1) In general terms, a task, operation, special purpose, or action. (2) An operation that
optionally uses one or more input parameters but always returns a single output value.
RDMS uses two types of built-in functions. An aggregate function retrieves a specific
attribute about groups of rows, such as the minimum value of a column. A system
function retrieves a dynamic value, such as the current time or user. The Boolean
existential function, [NOT] EXISTS, checks for the existence of a row and returns one of
the “truth values” (TRUE or FALSE). See also stored function.

H
High-Volume Transaction Processing (HVTIP)
A high-performance transaction processing environment.

hit record
A record that satisfies the condition of the WHERE clause in a query.

HVTIP
See High-Volume Transaction Processing.

hybrid hash join

One of the methods used by RDMS to perform a join operation. RDMS uses the hybrid
hash join to equally join the two outermost tables when both tables equally join on
nonindexed columns or on nonleading index columns.

I
I-bank
A bank that contains instructions in the form of program code.

I18N
See internationalization.

ICR
See intercept and connect routine.

implicit lock
A lock automatically created by RDMS to protect data in the database.

implicit primary key (IPK)

The internally defined primary key created by RDMS when the SQL standard table
definition does not specify a primary key.

inconsistency
A discrepancy in a database that occurs when the same data has different values.
Inconsistencies are removed once the database is normalized.

index
See secondary index.

Glossary–10 7831 0778–003

 Glossary

index level number

A two-digit number from 01 to 49 recorded inside each data and index page. The index
level indicates whether the page is a data page or an index page.

index page
A page with records that provide a logical access path to data pages. See also root page.

index page number

A page number assigned by RDMS to each index page created in a B-tree to uniquely
identify the page.

index record
A group of facts or fields used to store information about a relational table. Index records
contain a primary key value and a page number pointing to a data or index page. Index
pages contain index records.

INSERT privilege
A privilege that owners of RDMS tables grant to other users to enable them to add rows.
See also data access control.

instruction bank
See I-bank.

Integrated Recovery Utility (IRU)

The Unisys command-driven software product that maintains the integrity and availability
of files, databases, and transaction messages. IRU performs tasks such as dumping,
reloading, and moving files; displaying audit trail information; and allocating, reallocating,
and releasing mass storage. IRU also performs alternate recovery procedures to recover
messages, queue items, and database updates if the built-in recovery processes of Exec,
TIP file control, UDS, and MCB fail.

interactive driver
A program that enables the use of SQL statements without the need to write a program
or use IPF.

Interactive Processing Facility (IPF)

A suite of Unisys software products that provides an integrated environment for system
users. IPF includes modules for online help and editing, and supports user-written
procedures. See also IPF SQL Interface.

intercept and connect routine (ICR)

A group of UDS common banks that provide a standard interface to user programs,
applications, and application groups.

intercept function
The portion of the ICR that directs program control from users to the appropriate control
component.

International Standards Organization (ISO)

The organization that sets standards for computer processing.

7831 0778–003 Glossary–11

Glossary

internationalization (I18N)
The inclusion of the capability in a computer program of making itself adaptable to the
requirements of different native languages, local customs, and coded character sets,
thus allowing for localization.

interpreted command
A command or statement that is analyzed and executed at run time.

interpreter interface
An interface between RDMS and UCS COBOL, UCS FORTRAN, UCS C, ASCII COBOL,
ASCII FORTRAN, IPF SQL, or MRI in which each SQL statement is passed to RDMS and
interpreted each time it is encountered during program execution.

IPF SQL Interface

An IPF product that provides online access to RDMS data through the use of relational
commands. See also Interactive Processing Facility.

IPF
See Interactive Processing Facility.

IPK
See implicit primary key.

ISO
Abbreviation for International Standards Organization, which sets standards for computer
processing.

ISO 646:1983
A 7-bit coded character set for information interchange. The reference version of the
character set in the standard includes the US ASCII alphanumeric characters and many of
the special characters. The standard provides for 12 character positions that can be
adapted for regional needs. Several nationalized versions of the standard are used by
other countries to represent characters with diacritical marks found in most European
languages.

ISO 8859-1:1987
An 8-bit single-byte coded graphic character set. This standard character set comprises
191 graphic characters that cover the requirements of most West European languages.
Synonymous with Latin Alphabet No. 1 and LATIN1.

IRU
See Integrated Recovery Utility.

J
join
A relational operation that combines rows from two or more tables. See also equi-join;
hybrid hash join; merge join; nested loop join; semi-join.

Glossary–12 7831 0778–003

 Glossary

K
kanji data
Japanese characters represented in a 16-bit code.

key
See composite key; foreign key; primary key.

key compression
The RDMS process of condensing or squeezing together data and index records when a
record contains leading key values that are the same as the leading key values of the
logically preceding record.

key item
A special item or set of items within a row that uniquely identifies the row. Key items for
each row within a table must be unique.

keyword
A word that must occur in an SQL statement to make it syntactically correct. See also
reserved word.

L
LATIN1
Synonym for Latin Alphabet No. 1. See ISO 8859-1:1987.

LETS-J
A Unisys proprietary coded character set that defines a 2-byte encoding for kanji
characters.

literal
An alphanumeric character string that provides the value for the data item it represents.
Synonym for constant.

LINC
The Unisys software product that defines a general methodology to handle business
applications. LINC provides solutions based on the nature of the problem and does not
demand an understanding of the specifics of a data model.

load factor
The highest percentage of space used for loading each index and data page in an RDMS
file or each data page in a DMS area.

loading
The process of moving large amounts of data into a database. See also RDMS Utility
processor.

locale
The environment (or, more precisely, a set of rules) under which the user is operating,
which determines the character set, language, and collating sequence used as well as
the conventions for displaying date, time, and currency.

7831 0778–003 Glossary–13

Glossary

localization
Establishing the capability on a system of enabling users to work in their native language
using a familiar character set and conventions. See also internationalization.

lock
A security mechanism for controlling access to data in tables and files, as well as to
definitions in the repository. Locking ensures data integrity and controls concurrent
access to data and resources.

locking conflict
A condition that occurs when more than one user tries to access or lock a table or page
at the same time.

locking mode
The scope of a lock on a table. Users can define the mode—exclusive, protected, or
shared—explicitly (for example, with a LOCK statement in RDMS) or the LDM can define
the mode implicitly.

locking usage
The attribute—retrieval or update—that specifies the type of lock designated explicitly by
a user or implicitly by the LDM.

logical I/O
A request for data from an LDM to UDS Control. Contrast with physical I/O.

logical operation
A mathematical action performed on the Boolean operands AND, OR, and NOT.

long recovery
The IRU process that restores an inconsistent database to a consistent state by
recovering database updates, messages, and queue items for all steps that were
committed before the failure occurred. Long recovery also restores updates for steps in
the ready state at the recovery end point to a UDS retention file (when necessary). Users
can elect to recover just the database, just the messages, just the queue items, or any
combination of these.

long selective recovery

The IRU process that restores a selected portion of the database instead of the entire
database.

M
MAPPER Relational Interface (MRI)
The Unisys software product that establishes an online interface between the MAPPER
system and an RDMS database.

mass storage address

See sector address.

mass storage data file

Synonym for mass storage file.

Glossary–14 7831 0778–003

 Glossary

mass storage file

An Exec or TIP data file maintained on a directly addressable external storage device,
such as a disk. Each LDM storage area is associated with exactly one file. Synonymous
with mass storage data file; physical file.

MCB
See Message Control Bank.

medium recovery
The IRU process that uses data from the audit trail and UDS retention files to recover
deferred update program steps that did not complete update actions when a host or
component failed and to roll back updates for the quick-look program steps that were
active when the host or component failed. Synonymous with deferred update recovery.

merge join
One of the methods used by RDMS to perform a join operation. RDMS uses the merge
join to equally join the two outermost tables when the outer table has more than an
RDMS internally defined number of hit records and the inner table uses an index search
on the join columns.

meta-database
A database that contains information about other databases.

Message Control Bank (MCB)

The Unisys software product that interfaces with Communications Management System
(CMS) and transaction programs to provide message recovery and transaction
scheduling.

MHFS
See Multi-Host File Sharing.

MHTIP
See Multi-Host Transaction Processing

module language program

A separate program that contains embedded SQL statements. Each SQL statement is
encapsulated as a procedure call, which in turn is executed by UCS Ada or UCS
FORTRAN programs.

MRI
See MAPPER Relational Interface.

Multi-Host File Sharing (MHFS)

An Exec component that enables the sharing of files among multiple hosts. MHFS
associates the files with a standard (local) or shared MFD and handles the
communication necessary for file sharing within a host and across multiple host
systems.

Multi-Host Transaction Processing (MHTIP)

An XTC component that enables concurrent (shared) access to TIP database files from
multiple hosts, thus increasing system capacity. MHFS software provides I/O access
from each host to the shared database. See also Record Lock Processor.

7831 0778–003 Glossary–15

Glossary

N
negative zero
A condition in earlier releases of RDMS in which a negative sign is automatically placed
in front of a zero. RDMS automatically converts the negative sign to positive.

nested loop join

One of the methods used by RDMS to perform a join operation. RDMS uses the nested
loop join for all joins after the first join in a query, and for the first join if other methods do
not apply.

nonindexed search
A sequential search through the data pages of a B-tree to find the specified data. Usually
a slow method of accessing data, nonindexed searches are used when the primary key
or secondary index columns to locate desired data are not specified. Synonymous with
ad hoc search; brute force search.

nonrecoverable step
A step within a thread or run unit that cannot be recovered if a system error occurs.

normalization
The process of dividing large tables that refer to many objects into smaller tables that
refer to one object. See also first normal form; second normal form; third normal form.

null value
An undefined value.

numeric constant
The representation of a unique numeric value that cannot be changed.

O
operand
A piece of data upon which an arithmetic or Boolean operation is performed (for
example, ADD X or AND X, where X is the operand).

operation
(1) In general terms, the action or process of performing the action specified by a
command or program, including but not limited to, arithmetic operations. (2) In arithmetic
expressions, a combination of at least one operator and one operand (for example, ADD
X, where ADD is the operator and X is the operand).

operator
(1) The action portion of an arithmetic or logical operation (for example, ADD X, where
ADD is the operator). (2) The person who performs basic system operations such as
mounting tapes and responding to console messages by manipulating computer
controls, usually from a system console.

ordering key
A binary sortable key created from a character string using a culturally sensitive set of
transformation rules.

Glossary–16 7831 0778–003

 Glossary

outer query
An RDMS SELECT statement with one or more SELECT statements embedded within it.
See also subquery.

outer reference
A reference in an RDMS subquery to a column that belongs to a table defined or
specified in an outer query or subquery.

owned schema
A schema created by the CREATE SCHEMA statement, in which the creator of the
schema is implicitly the owner of all tables and views created within the schema.

owner
For owned schemas and their underlying tables and views, the creator of the schema;
for tables in unowned schemas, the user specified in the OWNER IS clause of the
CREATE TABLE or ALTER TABLE statement; for views in unowned schemas, the creator
of the view. Owners of tables and views determine who can and cannot access the data.

P
page
A physical memory block that constitutes a subdivision of and measurement for areas.
Page size can vary from area to area. Each page contains header information, zero or
more records, and a page slot table. A DMS page may also contain CALC chain headers.
I/O (that is, the exchange of data between a storage device and main storage) occurs at
the page level. See also data page; index page.

page header
The part of the file control page that contains general information about the file.

page-level locking
The process that locks out a page of a file or record so that other programs or
applications cannot access the data on the page until the lock is released. Page-level
locking is faster than record-level locking.

page number
A unique identifying integer assigned to each data page or index page in a B-tree.

parent table
The table referenced by the child table in a foreign key. The parent table’s primary key
values determine the acceptable values for a column or columns in the child table.

physical file
Synonym for mass storage file.

physical I/O
The actual transfer of data from a file to main storage. Contrast with logical I/O.

placeholder variable
A place to hold values in an interpreted SQL statement. Each placeholder variable is
associated with a host program variable.

7831 0778–003 Glossary–17

Glossary

population
The number of records in a B-tree.

positive zero
A condition in RDMS in which a positive sign is automatically attached to a zero. If a
negative sign is attached to a zero, RDMS automatically converts it to a positive sign.

predicate
A simple Boolean expression that contains at least one value expression (for example, a
column, a literal, an arithmetic expression, or a built-in function) that evaluates to one of
the truth values, TRUE, FALSE, or UNKNOWN. A predicate in its most common form
consists of two value expressions separated by a comparison operator. Predicates can
be connected by Boolean operators to create more complex Boolean expressions.

primary key
A column or group of columns that uniquely identifies each row in a table. A primary key
that includes more than one column is a composite key.

primary key constraint

See constraint.

primary key value

The specific value of a primary key that uniquely identifies a row in a table.

privilege
A security mechanism that enables owners of tables to grant other users access to the
contents of a table. Privileges include retrieving, updating, inserting, deleting, and
referencing data. See also data access control.

program control table

An Exec table that contains control information for a particular run.

projection
An operation that selects some or all columns from an existing table.

protected lock
A lock that prevents other threads from changing a file or page until the lock is released.

Q
QLP
See Query Language Processor.

qualifier
The first segment of a table or view specification, which is prefixed to a table name to
reduce ambiguity. A name used to prefix another name to ensure uniqueness.

query
A request for information from a database that does not update the database.
Specifically, an SQL statement that requests information.

Glossary–18 7831 0778–003

 Glossary

Query Language Processor (QLP)

The Unisys software product with reporting functions that provides online access to
DMS and RDMS databases, and to data files accessible by PCIOS.

query specification
A specification of the information to be selected: the columns and the table or tables in
which the columns reside, as well as any conditions to be imposed on the rows that
contain the specified columns and any calculations or other manipulations of the
information used to identify it.

quick-look recovery
The process that copies a data page before it is updated so that the copy can be used
later for recovery.

R
random access cursor
An RDMS cursor that enables the use of the LOCATE statement to position the pointer
in a cursor.

RDI
A MAPPER system run used to access an RDMS database. RDI stands for “Relational
Database Interface.”

RDM
See relational data manager.

RDMCA
See relational data management communications area.

RDMSIZE
See RDMS sizing utility.

RDMS sizing utility (RDMSIZE)

An RDMS component for sizing database files. RDMSIZE helps in setting up the physical
attributes of an RDMS database file with the goal of meeting application size and
performance requirements.

RDMS table
See relational table.

RDMS Utility processor (RDMUTL)

A processor included with RDMS software that enables users to load data from an SDF
file to tables in an RDMS database.

RDMUTL
See RDMS Utility processor.

RDT
See relation description table.

7831 0778–003 Glossary–19

Glossary

RDT$FILE
A file that contains all RDMS table descriptions. See also relation description table.

record
Synonym for row.

record-level locking
The process that locks out a record so that other programs or applications cannot access
the data in the record until the lock is released. See also page-level locking.

Record Lock Processor (RLP)

A hardware component required for an XTC system. The central complex device
connected to all hosts in an XTC configuration, the RLP synchronizes and retains locks
for XTC hosts that concurrently access shared mass storage TIP and UDS databases.

recovery
Any process that restores a database or other files to a consistent state following a user
error or a hardware or software failure. See also long recovery; medium recovery; short
recovery.

redundancy
An incidence of repeated data in the database. Redundancies are removed once a
database is normalized.

reference resolution
The process by which computer software scans a symbolic name and translates it into
an actual address in storage.

referential constraint
See constraint.

relation
See relational table; relational view.

relation code
A code that uniquely identifies a B-tree within a storage area, assigned by UREP for each
B-tree of a table whenever an RDMS table is defined.

relation description table (RDT)

The encoded, machine-readable form of a relational table definition that corresponds to
the information stored in symbolic form in the repository for user reports.

relational command
A command used to manipulate a relational database. See also Structured Query
Language.

relational data management communications area (RDMCA)

A buffer through which the RDM passes information (but not actual data) from the
database to user programs.

relational data manager (RDM)

The management routine for RDMS.

Glossary–20 7831 0778–003

 Glossary

relational data model

A data model that represents data in the simple form of two-dimensional tables called
relational tables.

relational database
A database organized into one or more relational tables.

Relational File Analyzer (RFA)

A support processor that checks the structure of RDMS files for corruption.

relational operator
Synonym for comparison operator.

relational storage manager (RSM)

The RDMS software component that manages the relational database.

Relational Syntax Analyzer (RSA)

The RDMS software component that parses SQL statements, handles variables passed
in or with SQL statements, and returns error information to the calling host program.

relational table
A two-dimensional arrangement of data. The columns of the table are also known as
items, fields, or attributes. The rows of the table are also known as records or tuples.
Tables are actual collections of data physically stored in the computer. Synonymous with
base relation; base table; table.

relational table definition

An entry in the repository and RDT$ file that describes the characteristics of one or more
relational tables.

relational view
A conceptual table that represents a restriction, projection, or join of one or more tables.
Synonymous with view.

relational view definition

An entry in the repository and RDT$ file that describes the characteristics of a view.

REPORT command
A UREP command that enables users to retrieve and print listings of repository
information associated with an individual entity (for example, a storage area or
relationship).

repository
The database that contains UREP information, such as symbolic table and storage area
definitions for an RDMS database. See also Unisys Repository Manager.

repository command
A UREP command that performs operations on the repository and sends instructions to
other UDS components.

repository system definition

A symbolic form of a table definition or a storage area definition that describes
characteristics (for example, a table specification or storage area attribute).

7831 0778–003 Glossary–21

Glossary

reserved word
A word reserved by Unisys or a Unisys product for special use. For RDMS specifically, a
keyword that cannot be used as an identifier or name.

restriction
An operation that constructs a conceptual table (cursor) by selecting all or some rows
from existing tables.

retrieval lock
A user- or system-specified lock on a file or page intended to allow only the retrieval of
data from the file or page and coinstantaneously restrict other threads from accessing
the affected data.

retrieve
To extract selected data from a database.

RFA
See Relational File Analyzer.

rollback
The recovery process that reverses (that is, rolls back) all uncommitted updates and
restores the database to the state that existed at the beginning of the recoverable step.

RLP
See Record Lock Processor.

root page
The index page at the top of a B-tree.

row
A set of relational information treated as a unit. In a table, each row of information is a
record. In a cursor, each occurrence of the specified data is a record. A record includes
one or more columns. Synonymous with tuple.

row-level lock
A lock on an individual row or an entire page within a table.

RSA
See Relational Syntax Analyzer.

RSM
See relational storage manager.

S
schema
A description of the database as it exists on mass storage that describes units of data
such as storage areas, tables, record types, and relations

Glossary–22 7831 0778–003

 Glossary

schema name
The name that uniquely identifies the units of data associated with a particular schema.
See also qualifier.

scratch bank
An application-level bank used only by RDMS for working storage.

SDF
See system data format.

second normal form

A level of normalization where every nonprimary key attribute is functionally dependent
on the whole primary key.

secondary index
One or more columns of a relational table. Secondary index values can be specified to
find particular data values in an RDMS table more efficiently. RDMS uses secondary
indexes to choose the best access strategy for finding the data desired. See also primary
key.

sector address
The mass storage address of a page in a file.

security
The protection of a database from unauthorized access. See also data access control;
definition access control.

select list
The list of columns specified in a query specification.

SELECT privilege
A privilege granted by owners of RDMS tables to other users to enable them to retrieve
data. Synonymous with FETCH privilege. See also data access control.

self-referencing foreign key

A foreign key constraint where the referenced table is both the parent and child.

semi-join
A join operation in which the last table in the join has no columns projected and a global
DISTINCT is specified in the select list.

sequential access cursor

A normal cursor that enables access to the records in a file in the order in which they
appear, as opposed to at random.

serializability
An optional, configurable locking strategy that can be implicitly employed by RDMS to
ensure that if concurrent users are updating or reading a database, the results of their
activities appear as though they happened in order instead of at the same time.

SFS
See Shared File System.

7831 0778–003 Glossary–23

Glossary

Shared File System (SFS)

The Unisys data management software that enables shared access to “flat” data files.

shared lock
A lock that guarantees that records read by one user are not changed by another user for
the duration of a step.

short recovery
The process that recovers an inconsistent database to a consistent state if the database
is in an inconsistent state because program steps did not complete update actions
because of a host or component failure. Short recovery is also used to restart an
application group after medium or long recovery.

snapshot
A collection of sorted or grouped records.

SQL
See Structured Query Language.

SQLCODE
The standard SQL 89 variable used to check for the successful completion of an SQL
statement. SQLCODE is an integer.

SQL routine
A stored procedure or stored function.

SQLSTATE
The standard SQL 92 variable used to check for the successful completion of an SQL
statement. SQLSTATE is 5 characters long and consists of a 2-character class and a
3-character subclass.

static ESQL
See embedded SQL.

step
A recoverable or nonrecoverable unit of work, such as an application program or part of a
program. Steps are the smallest recoverable unit of a thread. Each step begins and ends
with a thread control command or rollback caused by an error. Users can recover
recoverable steps to restore the database to a consistent state. See also step control.

step control
An Exec integrated recovery component that uses queues and queue items to record
and update the current state of all steps accessing the database within a recoverable
application group. Step control provides central control information for the other
integrated recovery components.

storage area
A UDS database file—actually stored in an Exec or TIP file (for RDMS, the data stored in
one or more relational tables; for DMS, an area of records; for SFS, a file of PCIOS or
SFS data).

Glossary–24 7831 0778–003

 Glossary

storage area definition

Information stored in the repository that describes the characteristics of a storage area
(for example, the storage area name, the Exec file name or TIP file number, and other
storage area attributes). See also file description table.

stored function
A stored procedure that returns a value to the caller.

stored procedure
An executable database object (usually, a number of SQL statements) stored in the
database that can be called from a client or host application.

string literal
A character or characters enclosed in apostrophes (sometimes referred to as “single
quotation marks”) to indicate a literal value.

Structured Query Language (SQL)

An industry-standard set of relational commands (called SQL statements) that enables
users to create, retrieve, and update data in a relational database and to access
information in the repository.

subquery
A query specification nested within a Boolean expression of another query expression.
The Boolean expression that references the subquery can be part of either the WHERE
or HAVING clause.

syntax
The format and rules for writing commands or statements.

system data format (SDF)

A data file format that has a sequential series of symbolic images, each of which is
preceded by control information that describes the image. RDMS data is on SDF files.

system function
One of the RDMS built-in datetime functions, CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP, and the keyword USER. See also function.

T
table
Synonym for relational table.

table constraint
See constraint.

table control system (TCS)

A UDS Control component that operates in conjunction with UREP to create and
maintain its own internal tables, which it then uses to help the data management
software products execute their commands.

7831 0778–003 Glossary–25

Glossary

table definition
See relational table definition.

table-level lock
A lock on an entire table.

table-level security
The level of security that enables the owner of a table to control who has access to all
versions of the table and which commands can access those versions.

table owner
See owner.

table version
See version.

TCS
See table control system.

third normal form

A level of normalization in which no nonprimary key attribute is functionally dependent on
an attribute key that is not part of the primary key.

thread
A sequence of commands from one user that constitutes a session of work centered
around one application group. The user determines when the thread begins and ends.
Threads are often referred to as “user programs” or “run units” and can be partitioned
into steps. All commands submitted to RDMS must be part of a thread. A COMMIT or
ROLLBACK statement indicates the end of a step within the thread.

thread rollback
The recovery process that reverses the effect of a thread on the database since the last
BEGIN THREAD or COMMIT statement executed.

TIP
See Transaction Processing.

trace control (TRCCTL)

A UDS Control facility for monitoring performance and performing diagnostics.

Transaction Processing (TIP)

The Unisys real-time system for processing transactions under Exec control where users
enter a transaction code requesting a function for which a prewritten, preregistered
program is loaded into memory. The program then processes input data and returns the
results to the initiator.

TRCCTL
See trace control.

tuple
Synonym for row.

Glossary–26 7831 0778–003

 Glossary

tuple variable
An obsolete term. See correlation name.

U
UCS COBOL
The extended mode compiler version of COBOL.

UCS FORTRAN
The extended mode compiler version of FORTRAN.

UDS
See Universal Data System.

UDS Control
See Universal Data System Control.

uncorrelated query
An outer query whose subquery or subqueries contain no outer references. Contrast
with correlated query.

UNION operator
A logical set operator used in RDMS to derive one table from two other tables. The result
of a UNION operation is a set of all selected rows from both tables not including
duplicates. The result of a UNION ALL operation is a set of all selected rows from both
tables including duplicates.

unique constraint
See constraint.

Unisys Repository Manager (UREP)

The Unisys data management software that provides data dictionary functions by
keeping track of information resources. It also is the tool for creating and maintaining
data objects in a repository and reporting on them.

Universal Data System (UDS)

The Unisys expandable, modular suite of software products for data management, data
processing, and database application development. Together with other data
management software products and components, UDS provides an integrated
environment for control, maintenance, and recovery of user databases.

Universal Data System Control (UDS Control)

The UDS online data and file manager that directs and coordinates the operations of UDS
products.

universal quantifier
One of the keywords ANY, ALL, or SOME when used with a comparison operator.

unloading
The moving of data from an RDMS database to an SDF file.

7831 0778–003 Glossary–27

Glossary

unowned schema
A schema created through UREP. Contrast with owned schema. See also owner.

updatable view
A relational view that can be used to alter the contents of the underlying tables. Such a
view must meet certain conditions to be updatable.

update lock
A user- or system-specified lock on a file or page intended to allow only the changing of
data in the file or page and coinstantaneously restrict other threads from accessing the
affected data.

UPDATE privilege
A privilege that owners of RDMS tables grant to other users to enable them to change
information. See also data access control.

UREP
See Unisys Repository Manager.

V
value
A numeric, alphabetic, or alphanumeric character string stored for an entity (such as an
attribute) at the address allocated for that entity. See also data value.

value expression
A subcomponent of a predicate that results in either a single numeric value or a character
string value. A value expression in its simplest form can be a literal, a column, or a
program variable. In a more complex form, it can be an arithmetic expression or an
aggregate function.

variable
A symbolic name used to identify a single storage sequence. Its name remains fixed
during the execution of a program; its value can change during execution. Contrast with
constant.

VDT
See view definition table.

version
Another copy of a table, which can contain different data. Many different versions of a
table can exist. Each version of the table is associated with the same storage area name
but has a different version name attached to it within that storage area definition. All
versions of a table have identical column names, primary keys, and secondary indexes
(because they share the same definition). Each version, however, can contain different
data. Data associated with each version is stored in its own file.

version-level security
The level of security that enables the owner of a table to control who has access to a
specific version of the table and which statements can access that version.

Glossary–28 7831 0778–003

 Glossary

view
Synonym for relational view.

view definition
See relational view definition.

view definition table (VDT)

The encoded, machine-readable form of a view definition.

view owner
See owner.

W
work
The SQL name for a thread.

X
XPC
See Extended Processing Complex.

XRLOAD
The Unisys stand-alone utility processor that implements fast initial offline loading of
large amounts of data into an RDMS database. XRLOAD does not access UDS Control or
RDMS.

XTC
See Extended Transaction Capacity.

7831 0778–003 Glossary–29

Glossary

Glossary–30 7831 0778–003

Index

A columns
introduction to, 3-1
names, listing for views, 7-3
aggregate operations, 7-4
specifying (select list), 5-2
aliases in table lists, using, 5-4
COMMANDLOOKS recovery option, 10-6
ALTER TABLE statement, effect on
COMMANDSLOOKS recovery option, 4-3
views, 7-5
COMMIT statement (example), 4-4, 10-10
application group, 4-1
conceptual table, 5-7
attribute (See columns)
Condition command, IPF, 10-9
authorization-id, as owner of view, 7-1
configuration attributes
AVG function, 7-4
RDMS-DEFAULT-QUALIFIER, 7-2
RDMS-SERIALIZABLE, 8-2
Copy command, IPF, 10-9
B correlation name, 5-4
COUNT function, 7-4
backslash escape character, 11-4 CREATE SCHEMA statement
BEGIN THREAD statement authorization-id as owner of view, 7-1
examples, 4-3 schema name as default qualifier, A-2
starting a session, 4-2 view name, 7-2
BEGIN THREAD statement examples, 10-5 CREATE VIEW statement
Boolean expressions, 5-4 access privileges, 7-1
built-in functions, 7-4 examples, 7-2, 7-3
query specifications, 7-4
use, 7-1
C cross-product tables, 5-9
cursor operations, support of, 11-1
CALL statement, 11-7 cursors
canceling changes to database, 4-4 closing, 5-15
CAT command, ECL, A-1 declaring, 5-13
clauses opening, 5-14
ACCESS CONTROL, 7-1 using, 5-13
FOR, 4-2 with IPF SQL Interface, 11-1
FROM, 5-1 CUSTOMERS table, 3-2
GROUP BY, 5-2
HAVING, 5-2
IN, 8-3 D
ORDER BY, 7-1
RETRIEVE INTO, 6-3 data
VALUES, 6-2 accessing efficiently, 8-2
WHERE, 5-2 deleting, 2-2
WHERE CURRENT OF, 6-3 inserting, 2-2
WITH CHECK OPTION, 7-1, 7-6 restricting access to, 8-2
CLOSE statement (example), 5-15, 11-3 retrieving, 2-2
column names, listing for views, 7-3 updating, 2-2

7831 0778–003 Index–1

Index

data item, 3-3 module language, 9-2

data manager, specifying (example), 10-4 static, 9-1
data value, 3-3 EXCLUSIVE lock mode, 8-3
database Execute command, IPF, 10-9
changes exiting IPF, 10-11
canceling, 4-4
database management, 2-1
databases F
canceling changes to, 4-4
cancelling changes to, 10-10 FETCH statement, format/example, 11-3
changes FILE command, 10-12
canceling, 10-10 FOR clause, 4-2
discarding, 10-10 FROM clause
saving, 10-10 introduction to, 5-1
closing, 10-11 specifying tables, 5-4
opening, 10-5 full-screen mode, SQL, 10-15
querying, 5-1
retrieving information from, 5-1
support of by UDS Control and UREP, 2-1 G
DECLARE CURSOR statement
examples, 5-13
GRANT statement, A-2
introduction to, 5-1
GROUP BY clause
IPF SQL Interface support of, 11-1
as aggregate operation, 7-4
SQL (example), 11-2
introduction to, 5-2
DEFERRED recovery option, 4-3, 10-6
retrieving information on rows, 5-7
DELETE statement
grouped table, 5-7
description of, 6-1
examples, 6-1
views, rules for updating with, 7-5
deleting data, 2-2
H
Describe command, IPF, 10-9
discarding changes to database, 4-4 HAVING clause
DISTINCT keyword, 5-3, 7-4 as aggregate operation, 7-4
DROP CURSOR statement (example), 11-2 introduction to, 5-2
DROP VIEW statement, 7-7 restricting groups, 5-8
dynamic ESQL, 9-1 help, online, 10-6
HOUSES table, 3-2

E
I
embedded SQL (See ESQL)
END THREAD statement indicator variables, using with SQL, 11-7
ending a session, 4-5 INSERT statement
example, 4-5, 10-11 description of, 6-2
error messages, retrieving, 4-3 examples, 6-2
escape character, specifying (example), 10-4 views, rules for updating with, 7-5
ESQL inserting data, 2-2
description of, 9-1 interfaces
dynamic, 9-1 description of, 9-1
interpreter/ third-generation language, 9-1 ESQL, 9-1
introduction to, 2-3 interpreter/third-generation language, 9-1
IPF SQL, 9-2 IPF SQL, 9-2
module language, 9-2

Index–2 7831 0778–003

 Index

Inventory command, IPF, 10-9 M

IPF commands/entries
"Call", 10-9
MAX function, 7-4
committing changes to database, 10-9
MIN function, 7-4
Condition, 10-9
MODE SCREEN entry, 10-15
Copy, 10-9
Describe, 10-9
Execute, 10-9
Inventory, 10-9
N
Library, 10-9
LOGOFF, 10-11 NONE recovery option, 10-6
MODE SCREEN, 10-15
OLD, 10-14
OUT, 10-14 O
Pack, 10-9
Position, 10-9 OLD command, IPF, 10-14
Run, 10-9 online help, 10-6
XQT, 10-9 OPEN statement, A-2
IPF processor OPEN statement (example), 5-14
calling, 10-3 Oracle Transparent Gateway reference, 9-2
exiting, 10-11 OUT command, IPF, 10-14
IPF screen mode, compared with SQL
full-screen mode, 10-15
IPF SQL Interface session, managing, 10-3 P
IPF variables, using with SQL, 11-4, 11-5
Pack command, IPF, 10-9
Position command, IPF, 10-9
J PROTECTED lock mode, 8-3

joining tables, 5-9

joining tables (example), 5-9 Q
qualifiers
L default, 7-2, A-2
using (example), A-2
Library command, IPF, 10-9 QUERY lock conflict option, 8-3
LOCK statement query results, handling/saving, 10-14
examples, 8-3 query specifications
introduction to, 8-2 aliases for tables, 5-4
locks clauses available, 5-1
conflict options, 8-3 CREATE VIEW statement, 7-4
errors with UNLOCK statement, joining tables (example), 5-9
avoiding, 8-3 QUEUE lock conflict option, 8-3
implicit, 8-2 QUICKLOOKS recovery option, 4-3, 10-6
introduction to, 8-1
modes, 8-3
releasing, 4-4 R
removing, 8-4
LOGOFF command, IPF, 10-9 recovery
COMMANDLOOKS option, 4-3
DEFERRED option, 4-3
description of, 4-1

7831 0778–003 Index–3

Index

options, 4-3 BEGIN THREAD, 4-2, 4-3

options, specifying from IPF, 10-6 CLOSE, 5-13, 5-15
QUICKLOOKS option, 4-3, 10-6 COMMIT, 4-4
relation (See tables) CREATE SCHEMA, 7-1
relational data model, 3-1 CREATE VIEW, 7-1
relational database DECLARE CURSOR, 5-1, 5-13
end user tasks, 2-2 DELETE, 6-1
SQL-based manager, 2-1 DROP CURSOR, 5-13
relational tables (See tables) DROP VIEW, 7-7
RETRIEVAL lock mode usage, 8-3 END THREAD, 4-5
RETRIEVE INTO clause, use with UPDATE GRANT, A-2
statement, 6-3 INSERT, 6-2
retrieving data, 2-2 introduction to, 2-3
RETURN lock conflict option, 8-3 lock, 4-4
REVOKE statement, A-2 LOCK, 8-2
ROLLBACK statement OPEN, 5-14, A-2
canceling changes to database, 4-4 REVOKE, A-2
example, 4-4, 10-10 ROLLBACK, 4-4
rows SELECT, 5-1, 5-2, 5-3, 5-4, 5-7, 5-8, 5-9
deleting, 6-1 UNLOCK, 8-4
in cross-product table, 5-9 UPDATE, 6-3
inserting, 6-2 USE DEFAULT QUALIFIER, 7-2, 9-2, A-2
introduction to, 3-1 USE DEFAULT VERSION, A-2
joining tables, effect on by WHERE static ESQL, 9-1
clause, 5-9 status variables,contents, 11-5
multiple, specifying and retrieving, 5-13 stored procedures and functions, 10-12,
retrieving information on groups of, 5-7 10-22
selecting, 5-6 Structured Query Language (SQL)
updating, 6-3 cursor operations, 11-1
Run command, IPF, 10-10 description of, 2-3
full-screen mode, using, 10-15
WHERE clause versions, 6-3
S subquery (example), 5-5, 5-6
SUM function, 7-4
select list, specifying columns, 5-2
SELECT statement, 5-1, 5-2, 5-3, 5-4, 5-7, 5-8,
5-9 T
SET statement, 11-7
SHARED lock mode, 8-3 tables
SQL (See Structured Query Language) access privileges on CREATE VIEW
SQL full-screen mode statement, 7-1
$SQLSCREEN, setting for, 10-16 aliases in query specifications, 5-4
compared with IPF screen mode, 10-15 ALTER TABLE statement effect on
example, 10-17, 10-18, 10-19, 10-21 views, 7-4
statements, SQL (See also IPF characteristics of, 3-3
commands/entries) conceptual, 5-7
ALTER TABLE, 7-5 CREATE VIEW statement, access
and IPF privileges, 7-1
rules, 10-2 cross-product, 5-9
saving, 10-12 CUSTOMERS, 3-2
and IPF commands deleting rows from, 6-1
introduction to, 10-1 grouped, 5-7

Index–4 7831 0778–003

 Index

HOUSES, 3-2 views

inserting rows in, 6-2 ALTER TABLE statement effect on, 7-4
introduction to, 3-1 description of, 7-4
joining, effect of WHERE clause, 5-9 dropping, 7-6
locking, 8-2 in table list, 5-4
query specifications to, 7-3 introduction to, 3-3
querying, 5-1 locking through table, 8-2
specifying, 5-4 naming, 7-1
updating rows in, 6-3 query specifications to, 7-4
virtual (or view), 3-3, 7-1 updating, 7-5
thread control operations, 4-1 virtual table, 7-1
threads WITH CHECK OPTION clause, 7-5
description of, 4-1 virtual table, 7-1
ending session, 4-5
opening (starting session), 4-2
tuple (See rows) W
WHERE clause
U introduction to, 5-2
joining tables, effect on rows, 5-9
UDS Control, support by, 2-1 selecting rows, 5-6
UNION operator (example), 5-14 UPDATE statement, effect on, 6-3
UNLOCK statement, 8-4 WHERE CURRENT OF clause, 6-3
UPDATE lock mode usage, 8-3 WITH CHECK OPTION clause
UPDATE statement description of, 7-1
description of, 6-3 example, 7-6
effect on by WHERE clause, 6-3
examples, 6-3
RETRIEVE INTO clause use, 6-3 X
views, rules for updating with, 7-4
updating data, 2-2 XQT command, IPF, 10-9
UREP, support by, 2-1
USE DEFAULT QUALIFIER statement, 9-2,
A-3 Special Character
USE DEFAULT VERSION statement, A-3
"CALL" command, IPF, 10-9
$SQLCODE status variable, contents, 11-5
V $SQLERROR status variable, contents, 11-5
$SQLESCAPE default value, 10-4
VALUES clause, 6-2 $SQLRETURNCODE status variable,
variables, using with SQL contents, 11-5
indicator (example), 11-7 $SQLSCREEN system variable,
IPF, 11-4 setting, 10-14, 11-5
procedures (example), 11-9 $SQLSCREEN:=TRUE entry, 10-15
status, 11-5
versions
default
changing, A-2
establishing new (example), A-3
introduction to, A-1
using (example), A-1

7831 0778–003 Index–5

Index

Index–6 7831 0778–003

.
© 2010 Unisys Corporation.
All rights reserved.

*78310778-003*
7831 0778–003