J2X0-2273-01EN
SymfoWARE(R)Server
RDB User's Guide: Database Definition
Download from Www.Somanuals.com. All Manuals Search And Download.
Reading this manual
The purpose of this manual is to give readers a basic introduction to databases and their creation to make it easier to
use SymfoWARE/RDB.
Unless otherwise noted, application programs and SQL statement in this manual are written in C.
Title Notation of Related Manual
The table below lists the manuals related to this manual and their title notation in this manual.
Position of this manual
Manual system and position of this manual in the system
ii
Download from Www.Somanuals.com. All Manuals Search And Download.
Besides the preceding manuals, SymfoWARE provides an online manual.
Command syntax
UNIX
The man command is used to display the syntax of RDB commands.
For details on the man command, refer to AnswerBook2 of the Reference Manual Collection.
The copyright of the online manual is the property of UNIX System Laboratories, Inc. and Fujitsu. Follow the items in
the written contract to use the product properly.
Windows NT/2000/XP
The command syntax is included in the Windows NT/2000/XP online help.
Action in response to displayed messages
UNIX
The rdbprtmsg command (RDB command) gives the meaning and user response for each displayed message.
iii
Download from Www.Somanuals.com. All Manuals Search And Download.
Windows NT/2000/XP
Action in response to displayed messages is included in the Windows NT/2000/XP online help.
Related manuals
The related manuals are as follows:
· Reference Manual Collection of AnswerBook 2
· Fujitsu COBOL User's Guide for Windows
· COBOL85 User's Guide
· Fujitsu COBOL Language Reference
Comments on this manual
Products covered by this manual
UNIX
· SymfoWARE Server Enterprise Edition 5.0 or later
· SymfoWARE Server Hot Standby Option 5.0 or later
Windows NT/2000/XP
· SymfoWARE Server Enterprise Edition V5.0L10 or later
Operating systems supporting SymfoWARE/RDB
· Solaris
· Microsoft(R) Windows NT(R) Server, Enterprise Edition
· Microsoft(R) Windows NT(R) Server network operating system
· Microsoft(R) Windows NT(R) Workstation operating system
· Microsoft(R) Windows NT(R) Server, Terminal Server Edition
· Microsoft(R) Windows(R) 2000 Professional operating system
· Microsoft(R) Windows(R) 2000 Server operating system
· Microsoft(R) Windows(R) 2000 Advanced Server operating system
· Microsoft(R) Windows(R) XP Professional
UNIX release version
This system conforms to UNIX System Rel 4.2MP.
About the drawings
The drawings covering SymfoWARE/RDB printing in this manual are intended to give the reader only a rough idea of
how the printing process works.
About the explanatory models
The sample databases in this manual are modeled mainly from inventory control databases of retailers. The database
designs and data contents are fictitious and not based on facts.
Abbreviated names
This manual uses the following abbreviated names:
iv
Download from Www.Somanuals.com. All Manuals Search And Download.
July 2002
Microsoft, MS, MS-DOS, Windows, and Windows NT are trademarks or registered trademarks of Microsoft
Corporation in the United States and other countries.
v
Download from Www.Somanuals.com. All Manuals Search And Download.
UNIX is a registered trademark in the United States and other countries, licensed exclusively through X/Open
Company Limited.
Solaris is a trademark of Sun Microsystems, Inc. in the United States.
Lotus is a registered trademark of Lotus Development Corporation.
SymfoWARE is a registered trademark of Fujitsu Limited.
Other company and product names used in this manual are trademarks or registered trademarks of their respective
owners.
The symbols of (R) and TM are omitted in this manual.
All Rights Reserved, Copyright (C) FUJITSU LIMITED 2002
vi
Download from Www.Somanuals.com. All Manuals Search And Download.
Chapter 1 SymfoWARE/RDB Overview
SymfoWARE/RDB provides functions for creating a database, managing a database, and manipulating database data.
Before creating a database, the user must design the database structure and define the database based on this
database structure design specifications. Then, the user must generate the database based on this database
definition. Database management is required for checking database usage conditions and handling database damage.
Structured query language (SQL) statements are used to access data within the database.
SymfoWARE/RDB provides functions for building a flexible client server system that includes the networks between
systems.
This chapter covers the following topics:
1.1 Overview of SymfoWARE/RDB Functions
1.2 Overview of the SymfoWARE/RDB Database Configuration
1.3 Overview of Database Creation Tasks
1.1 Overview of SymfoWARE/RDB Functions
SymfoWARE/RDB is a relational database processing system that represents data in table format and processes
those tables. The functions of SymfoWARE/RDB can be broadly divided into functions for:
· Defining table formats (database definition)
· Maintaining and managing databases (database management)
· Manipulating tables (table manipulation)
Figure: SymfoWARE/RDB functions configuration shows the configuration of SymfoWARE/RDB functions.
1
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: SymfoWARE/RDB functions configuration]
Functions for defining table formats (database definition)
To create a database, first define the table formats. 2.2 "Designing a Database," explains the kinds of formats used for
tables. RDB commands are used to execute database definitions. For information about how to use actual RDB
commands to define a database, see Chapter 2 "Database Creation."
Functions for maintaining and managing databases (database management)
SymfoWARE/RDB has functions for creating, saving, restoring, maintaining, and managing a database. These
functions are invoked by executing specific RDB commands.
For descriptions of the database maintenance and management functions and information about how to execute the
functions, refer to the "RDB Operations Guide."
2
Download from Www.Somanuals.com. All Manuals Search And Download.
Functions for manipulating tables (table manipulation)
Data manipulation SQL statements are used to insert, alter, delete, and reference data in tables. These SQL
statements are used within application programs. For information about how to develop application programs that use
data manipulation SQL statements, refer to the "RDB User's Guide: Application Program Development."
For information about how to use data manipulation SQL statements, refer to the "SQL Beginner's Guide."
1.2 Overview of the SymfoWARE/RDB Database Configurat
ion
As Figure: SymfoWARE/RDB database configuration shows, a SymfoWARE/RDB database consists of multiple
databases and an RDB dictionary and RDB directory file for managing them. The database logical structure, storage
structure, and physical structure definition information is stored in the RDB dictionary. Base tables, which are the
database data, and indexes are stored in database spaces.
[Figure: SymfoWARE/RDB database configuration]
In addition, as Figure: Relationship of logical, storage, and physical structures within databases shows, each database
consists of schemas, base tables, data structure organizations (DSOs), data structure instances (DSIs), and
databases spaces. These items are the basic elements of a three-tier hierarchy composed of the logical structure,
storage structure, and physical structure.
3
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Relationship of logical, storage, and physical structures within databases]
1.2.1 Physical structure
The physical structure consists of database spaces.
Database space
Under UNIX, a database space is defined on a raw device created on a magnetic disk; under Windows NT/2000/XP, a
database space is defined in a local file created on a magnetic disk. A SymfoWARE/RDB system enables multiple
database spaces to be defined so that the hard disk I-O load balance can be adjusted. In addition, the base table data
or index data of a single schema can be divided and stored in multiple database spaces.
In SymfoWARE/RDB, database spaces become storage structures that enable resources to be managed. Careful
consideration is given to processing efficiency, storage efficiency, and operation. The following two functions can be
used primarily:
Multi-database space:
A large-scale database can be built by allocating one table or index in multiple database spaces.
Split table operation:
When a single table is split into multiple parts based on specific rules, each subdivision unit can operate
independently. This function enables independent creation, update, backup, and recovery in parallel for each
subdivision unit of a large-scale database.
1.2.2 Logical structure
The logical structure consists of schemas and base tables, the elements of the schema.
Schema
A schema consists of table data and table definition data. The user must carefully consider the following aspects when
determining the kinds of base tables that are to form a schema. Consider the applications that are to use the database,
the contents of the data to be processed, and the data processing methods. The schema configuration is defined by
schema definition statements. These schema definition statements define the following items:
· Schema name
· Schema components
- Base table name and format
Schema definition data is entered in the RDB dictionary. Base table data is stored in a database space.
Multiple schema definitions can be entered in an RDB dictionary. In addition, base table data belonging
to a single schema can be stored in a single database space. Alternatively, base table data can be
divided in terms of individual base tables and stored in multiple database spaces.
Figure: Example of correspondence between schemas and database spaces is an example showing
multiple schemas being stored in multiple database spaces.
4
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Example of correspondence between schemas and database spaces]
Base table
A base table consists of columns and rows. Figure: Base table format example is a base table format example. In this
figure, one row consists of the data for one product. The data of a single row consists of several columns. A column
corresponds to a data item. The data for one product (one row) consists of the four data items (columns): ITMNO,
PRODUCT, STOCKQTY, and WHCODE.
The base table configuration is defined by schema definition statements. Table definitions in a schema definition
define the data items that form each base table. A table definition defines the following items:
· Table name
· Column
· Table constraint
Table name
A table name is the name assigned to each table.
The table name is used to specify the table to be the object of a data manipulation. The table name is also used when
adding or deleting a table definition. This table definition specifies the table definition information subject to deletion or
addition processing.
Column
A column definition contains the following definitions for a column that forms a base table.
Column name:
A column name is the name assigned to each column. The column name is used to specify the column to be
processed by a data manipulation. The column name is also used when altering a schema definition to
indicate the column to be altered.
Column data type:
The data of each column has a type, such as character, numeric or data-and-time type.
Column default value:
The column default value defines the value to be set if the column data is omitted when data is inserted or
5
Download from Www.Somanuals.com. All Manuals Search And Download.
updated.
Column constraint:
A column constraint defines a constraint condition on table creation. One such condition may be "each row
must have a value stored in the relevant column." Another condition may be "more than one row cannot have
the same value in the relevant column."
Table constraint
A table constraint enables the user to define whether or not a constraint is to be applied to a table. Such a constraint
may be "more than one row cannot have the same values in one or more columns." This constraint is called a unique
constraint.
[Figure: Base table format example]
View
A view is a virtual table for manipulating data. The view does not actually contain any data. A view table is equivalent
to the subtable of a base table as shown in Figure: Concept of a view. A view is defined by a view definition. A view
definition defines the following items:
Table name:
Defines the name of the view.
Column names:
Defines the names of the view columns.
View column and row definitions:
Defines which portions of a base table or view are to form a view.
[Figure: Concept of a view]
Index
An index increases the efficiency of search processing for the database data. A data manipulation is usually
accompanied by a data search using column data of a table as the search key. Thus, data search efficiency is an
important factor in judging the efficiency of a data manipulation. The user can specify whether or not to create an
6
Download from Www.Somanuals.com. All Manuals Search And Download.
index for each column of a table. Multiple columns also can be combined and specified as a single index. Searching a
column for which no index has been created is less efficient than searching a column having an index. Thus, an index
must be created for a column used as a data search key. However, whenever an index is created, additional database
capacity is required for the storage. Carefully consider the space required for each index when determining the size of
a database space.
Although an index affects the database capacity and data manipulation efficiency, it does not affect the data
manipulation. The user need not be concerned with indexes when developing application programs that use SQL
statements to manipulate data.
An index is defined by a storage structure definition statement.
An index is defined for a column of a base table. An index cannot be defined for a view.
An index is defined after the schema is defined and before data is stored in the database. However, an index may also
be defined after data is stored in the database by database generation or data manipulation. An index is created in a
database space following database generation after an index has been defined and when data manipulation (update)
is performed.
Figure: Concept of an index shows an index created for the STOCKQTY column of the STOCK table. This figure
portrays the concept of an index; it does not accurately represent the database format.
[Figure: Concept of an index]
The STOCKQTY index consists of STOCKQTY values and pointers indicating the position of the corresponding row in
the STOCK table, sorted by STOCKQTY value order. For example, say the user specifies a search for the row of the
STOCK table for which the STOCKQTY value is 60. In this case, first the STOCKQTY index is searched. Since the
index is arranged by STOCKQTY value order, the search can be performed very quickly. Once the search of the index
is completed, the position of the row in the STOCK table is revealed by the corresponding pointer. The specified row
can thus be obtained.
7
Download from Www.Somanuals.com. All Manuals Search And Download.
1.2.3 Storage structure
The storage structure consists of DSOs and DSIs.
DSO
A DSO defines the storage structure of the data for a base table. The two types of DSOs are as follows:
· Table
· Index
Table DSO
A table DSO defines the type of storage structure for storing data, and, if data is subdivided for storage, the
subdivision method.
Index DSO
An index DSO defines how the index is created for the table.
DSI
A DSI defines an area for storing base table data so that it can be allocated in a database space. The two types of
DSIs are as follows:
· Table
· Index
Table DSI
A table DSI defines an area for storing data so that it can be allocated in a database space.
Index DSI
An index DSI defines an area for storing index data added to a table so that it can be allocated in a database space.
A DSI associates a table or index with a database space.
DSOs and DSIs can be related in either a 1:1 or 1:n correspondence. If a 1:n correspondence exists, table data is
subdivided for storage, and rules for splitting the data are defined in the DSO. Figure: Example in which DSOs and
DSIs are associated in a 1:1 correspondence is an example in which table data is stored without being split. Figure:
Example in which DSOs and DSIs are associated in a 1:n correspondenceis an example in which table data is
subdivided for storage. An index DSI is defined for a table DSI. If table data is subdivided for storage, an index DSI
must be defined for each table DSI.
[Figure: Example in which DSOs and DSIs are associated in a 1:1 correspondence]
8
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Example in which DSOs and DSIs are associated in a 1:n correspondence]
As Figure: Storage structure components shows, the four types of storage structures are SEQUENTIAL, RANDOM,
OBJECT, and BTREE. The SEQUENTIAL, RANDOM, and OBJECT structures are used as storage structures for
tables. The BTREE structure is used as a storage structure for indexes. Each of these storage structures consists of
one or more components as shown in Figure: Storage structure components.
[Figure: Storage structure components]
A DSI can consist of multiple database spaces. The configuration of the multiple database spaces is divided into two
types. In one configuration, a database space is allocated for each component in the storage structure shown in
Figure: Storage structure components. In the other configuration, a database space is allocated to increase the size of
each component. Figure: Example in which a database space is allocated to each component is an example in which
a database space is allocated to each component. An example of allocating multiple database spaces to increase the
size of each component is shown in Figure: Example of allocating multiple database spaces to increase the size of
each component.
9
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Example in which a database space is allocated to each component]
10
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Example of allocating multiple database spaces to increase the size of each compon
ent]
1.3 Overview of Database Creation Tasks
A SymfoWARE/RDB database can be created in one of the following two ways:
· Using RDB commands
· Creating a SQL-embedded program
11
Download from Www.Somanuals.com. All Manuals Search And Download.
Using RDB commands
The user can create databases by executing RDB commands at the command prompt of UNIX or Windows
NT/2000/XP.
The user can define databases by specifying the file containing various SQLs for defining databases and using the
rdbddlex command. The user can also create databases by using the rdbsloader command. This method is suitable
for operation in which database logical and storage structures are defined in detail.
For details of how to create databases by using the RDB commands, see Chapter 2 "Database Creation."
Creating a SQL-embedded program
Create a SQL-embedded program that uses dynamic SQL to define databases. Create a SQL-embedded program
that uses the INSERT statement to create databases.
Details on using a SQL-embedded program to create databases are given in Chapter 2 "Database Creation."
Figure: Overview of tasks involved in database creation provides an overview of the database creation tasks.
[Figure: Overview of tasks involved in database creation]
12
Download from Www.Somanuals.com. All Manuals Search And Download.
Chapter 2 Database Creation
This chapter covers procedures ranging from the design and creation of a SymfoWARE/RDB database to database
operation.
2.1 Overview of Tasks From Database Design To Operation
2.2 Designing a Database
2.3 Creating a Database
2.4 Entering a Database Name
2.5 Creating a Database Space
2.6 Defining a Logical Structure
2.7 Defining a Storage Structure
2.8 Applying a Storage Structure Definition
2.9 Simplifying a Storage Structure Definition
2.10 Defining a Temporary Table
2.11 Defining Privilege Information
2.12 Defining Optimization Information
2.13 Generating a Database
2.14 Referencing Database Definition Information
2.1 Overview of Tasks From Database Design To Operatio
n
Database creation tasks are performed after the SymfoWARE/RDB system environment has been created and the
SymfoWARE/RDB system has been started. For information about creating the SymfoWARE/RDB system
environment and starting the SymfoWARE/RDB system, refer to the "RDB Operations Guide."
Refer to the following manuals for more information about the syntax of the SQL statements shown in
this manual:
· SQL Reference Guide
· SQL Beginner's Guide
The required procedure for creating and operating a database is as follows.
Database creation and operation
1. Design the database.
2. Enter the database name.
3. Create the database space.
4. Define the logical structures such as schema and table:
When a logical structure is defined, a simple storage structure can be defined.
5. Define the storage structure (DSO and DSI for table and index).
To facilitate data retrieval and operation, the storage structure can be defined as follows:
- Divide a storage table to localize the retrieval range, thus improving retrieval.
- Divide a storage table to maintain and operate a database without having to stop regular operations
during backup and restore if a database failure occurs.
6. Define a temporary table.
7. Define privilege information.
8. Define optimization information.
9. Initialize the database (DSI). If the rdbsloader command was used to create the database, the DSI need not be
initialized.
10. To maintain database definition information, save the RDB dictionary data.
11. Generate the database by entering data from external data or an application program.
13
Download from Www.Somanuals.com. All Manuals Search And Download.
12. In preparation for using the database, save the database data.
13. Operate the database.
Figure: Procedure from database design to operation shows the flow of tasks from database design to operation.
14
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Procedure from database design to operation]
15
Download from Www.Somanuals.com. All Manuals Search And Download.
2.2 Designing a Database
To create a database, first design the database. When designing the database, be sure to carefully analyze the
business applications, types and amounts of data to be processed, and data processing methods.
The database design procedure includes steps for designing tables, attributes, simplified storage structures, and
storage structures.
Designing tables
Design tables that meet the needs of the business application.
Designing attributes
Determine appropriate data types for designed items, as well as possible column constraints and default values. For
details about the attributes that can be used, refer to the "SQL Reference Guide."
Designing the storage structure
Determining the storage structure
From an application program viewpoint, the database is represented in table format. The application program
performs data operations according to structured query language (SQL) statements as if it were manipulating rows
and columns of data.
The structure for storing the data represented in table format on physical pages is called the storage structure. An
appropriate database storage structure is designed according to the analysis data of the access path. To design such
a database storage structure, obtain the size of the table from the amount of data and determine the required amount
of disk space. The use of a split table is determined by estimating the amount of data to be added and the operation
time acceptable for database reorganization.
For details about storage structures, see Chapter 4 "Storage Structure."
· SEQUENTIAL structure
· RANDOM structure
· OBJECT structure
· BTREE structure
Estimating the amount of database space required for each storage structure
In this step, allocate database space. Carefully consider the amount of data to be processed and the area access
patterns, then allocate database space for each component of the storage structure.
For details about estimating the amount of database space required for each storage structure, see 4.4 "Estimating
the Required Amount of Database Space."
Designing a simplified storage structure
To simplify the process of defining the storage structure, specify the database space for storing data by defining the
table or index. SymfoWARE/RDB then automatically defines the storage structure. In this case, the SEQUENTIAL
structure is used as the table storage structure. For a table of multimedia data, the SEQUENTIAL or OBJECT
structure can be selected as the table storage structure. This process does not allow the use of split storage.
If the storage structure is simplified defined, the DSO and DSI names of the table are automatically assigned from the
names generated by the table or index definitions. The data length and allocation are automatically determined at this
time.
For simplified definition of the storage structure, the capacity of DSI is dynamically expanded.
The naming prefix, data length, allocation amount, and DSI capacity expansion setting can all be changed by the use
of operating environment file parameters. For a table of multimedia data, the storage structure can also be selected.
For information about the operating environment file, refer to the "RDB User's Guide: Application Program
Development."
Examples of designing the database
Figure: Contents of the inventory management database shows an inventory management database for a retail store.
This inventory management database is used as an example for explaining database design in this section. This
database is used to implement the inventory management applications of the retail store. The inventory management
database consists of three tables, as follows.
16
Download from Www.Somanuals.com. All Manuals Search And Download.
STOCK table:
Contains information about products handled and quantities of those products in stock
ORDER table:
Contains information related to products, quantities ordered, and purchase prices for each customer.
COMPANY table:
Contains information about company names, telephone numbers, and addresses for each customer
The usage examples use the inventory management database mainly to explain database creation and data
manipulations. The table and column names above are used in usage examples throughout the remainder of the
section. Refer to them as necessary.
STOCK table
Figure: Contents of the inventory management database a) shows the contents of the STOCK table, which consists of
these four columns:
ITMNO:
Column of code number data assigned to products
PRODUCT:
Column of product type data
STOCKQTY:
Column of data indicating the quantity of the product in stock
WHCODE:
Column of number data for warehouses where the products are stored
ORDER table
Figure: Contents of the inventory management database b) shows the contents of the ORDER table, which consists of
these four columns:
CUSTOMER:
Column of company number data for customers
PRODNO:
Column of code numbers assigned to products (corresponds to the ITMNO column of the STOCK table)
PRICE:
Column of data indicating product purchase prices
ORDERQTY:
Column of data indicating quantities of products ordered
COMPANY table
Figure: Contents of the inventory management database c) shows the contents of the COMPANY table, which
consists of these four columns:
COMPNO:
Column of code number data assigned to companies (corresponds to the CUSTOMER column of the ORDER
table)
COMPANY:
Column of company name data
PHONE:
Column of company telephone number data
ADDRESS:
Column of company address data
17
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Contents of the inventory management database]
18
Download from Www.Somanuals.com. All Manuals Search And Download.
19
Download from Www.Somanuals.com. All Manuals Search And Download.
Column attributes of each table of the inventory management database
Table: Column attributes of each table of the inventory management database shows the column attributes of each
table.
[Table: Column attributes of each table of the inventory management database]
Relationships among the STOCK table, ORDER table, and COMPANY table
Figure: Relationships among the STOCK table, ORDER table, and COMPANY table shows the relationships among
the three tables. The STOCK table and ORDER table are related according to ITMNO and PRODNO. In addition, the
ORDER table and COMPANY table are related according to CUSTOMER and COMPNO. For example, the product
having ITMNO 123 in the STOCK table is the PRODUCT named REFRIGERATOR. Further, the STOCKQTY is 60,
and the number of the warehouse in which this product is stored is 1. From the row of the ORDER table in which
PRODNO is 123, the PRICE and ORDERQTY of this product are 48000 and 60, respectively. Moreover, since the
company number of the CUSTOMER is 61, the following data can be gleaned from the COMPANY table row in which
COMPNO is 61. Users can learn the company name, telephone number, and address of the CUSTOMER of that
product.
20
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Relationships among the STOCK table, ORDER table, and COMPANY table]
2.3 Creating a Database
This section contains the following topics to explain how to create databases:
· Defining a database by using the rdbddlex command
· Defining a database from an application program
2.3.1 Defining a database by using the rdbddlex command
This section shows how to create databases from a definition file.
Physical, logical, and storage structures can be defined using the rdbddlex command.
The first step is to create an input file to be used by the rdbddlex command. The next step is to execute the rdbddlex
command.
The d option of the rdbddlex command can only be omitted if the first SQL statement of the definition file is the
CREATE DATABASE statement.
Input file format of rdbddlex command
The syntax of for describing an input file of the rdbddlex command has the following general formats:
Format 1
Specify a semicolon (;) to terminate each SQL statement.
Format 2 (Format for defining a procedure routine)
To define a procedure routine, prefix the input file with "EXEC SQL" and suffix it with "END-EXEC;". The data between
"EXEC SQL" and "END-EXEC;" is assumed to be an SQL statement. This format is only valid if the x option has been
specified in the rdbddlex command.
21
Download from Www.Somanuals.com. All Manuals Search And Download.
Figure: Sample creation of a database from a definition file is a sample of database creation from a definition file.
Figure: Sample definition file is a sample definition file.
These figures are examples for Solaris. For Windows NT, change the input file specification in the rdbddlex command
and the database space definition in the input file as shown below.
· Windows NT/2000/XP
- Input file specification: C:¥USERS¥DEFAULT¥DDL.DAT
- Database
space
definition:
CREATE
DBSPACE
DBSPACE1
ALLOCATE
FILE
C:¥SFWD¥RDB¥USR¥DBSP¥DATABASE_SPACE ATTRIBUTE SPACE(2M)
Use two consecutive hyphens (--) to specify comments. Everything on the line after the two hyphens is treated as a
comment.
[Figure: Sample creation of a database from a definition file]
22
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Sample definition file]
23
Download from Www.Somanuals.com. All Manuals Search And Download.
2.3.2 Defining the database from an application program
This section shows how to use dynamic SQL statements to create a database. Logical and storage structures can be
defined from an application program. Register a database name and create a database space in advance by using the
rdbddlex command because these tasks cannot be executed from an application program. CMDAREA1 to
24
Download from Www.Somanuals.com. All Manuals Search And Download.
CMDAREA3 are set up as SQL statement variables. The programming language used is C. Figure: Sample
application program definition is a sample of definition by an application program.
[Figure: Sample application program definition]
If a storage structure is simplified for database definition from an application program, one program covers the steps
from definition to creation. However, a COMMIT statement should be specified before data manipulation.
[Figure: Sample definition to creation from an application program]
25
Download from Www.Somanuals.com. All Manuals Search And Download.
2.3.3 - Omitted -
2.4 Entering a Database Name
All logical structure definitions and storage structure definitions belong to a given database environment. Logical
structure definitions are the schemas and tables to be created. Such storage structure definitions are the DSOs and
DSIs. The user must enter the database name before defining the logical and storage structures.
When a database name is entered, that information is stored in the RDB dictionary.
Figure: Configuration of a database shows the configuration of a database.
[Figure: Configuration of a database]
CREATE DATABASE statement
Enter a database name using the CREATE DATABASE statement. Specify the database name to be entered in this
SQL statement. The specified database name is entered in the RDB dictionary.
Example 1:
Enter MASTER_DB as a database.
Example 2:
Enter STOCKMN_DB as a database.
Database name
For the database name, specify up to 36 alphanumeric characters beginning with an alphabetic character.
2.5 Creating a Database Space
Allocate database space as an area for processing a database. The database space can be reserved on a raw device
or as a local file on a magnetic disk. The raw device is used for a database space under UNIX. The local file is used
for a database space under Windows NT/2000/XP.
26
Download from Www.Somanuals.com. All Manuals Search And Download.
At the creation of a database space, a log environment can be allocated for each database space.
This section explains the relationships between database space and magnetic disk, and the correspondence between
the database space and the log environment.
2.5.1 Creating a database space on a raw device
Under UNIX, a partition on a magnetic disk is allocated as a database area; therefore, an actual raw device must be
acquired before a database space can be created.
To create a database space, use the CREATE DBSPACE statement.
Executing the CREATE DBSPACE statement associates the database space and an actual raw device as well as the
database space and a log environment. The CREATE DBSPACE statement also registers information about the
database space in the RDB dictionary.
CREATE DBSPACE statement
In the CREATE DBSPATE statement, specify the database space name and the name of the raw device in which the
database space is to be created.
The following example shows the execution of a CREATE DBSPACE statement to define a database space for a stock
management database.
Example:
Create database spaces DBSP_1, DBSP_2, and DBSP_3. Then allocate the following raw devices to
their respective database spaces. These raw devices must have been defined in advance.
· DBSP_1.../dev/rdsk/c1t0d1s1
· DBSP_2.../dev/rdsk/c2t0d2s3
· DBSP_3.../dev/rdsk/c3t0d3s3
Database space name
For the database space name, specify up to 36 alphanumeric characters beginning with an alphabetic character.
27
Download from Www.Somanuals.com. All Manuals Search And Download.
Raw device name
Specify the name of the raw device to be allocated for the database space.
Notes on operating multi-RDB
For operation of a multi-RDB, the specified raw device may be shared by another SymfoWARE/RDB environment.
After a required raw device is created, use the chown and chmod commands to configure the access rights so that
only the activation user of each system can access the raw device. For details on the chown and chmod commands,
refer to the commands reference manual of the relevant operating system.
2.5.2 Creating a database space on a local file
Under Windows NT/2000/XP, an NTFS file is allocated to a database area.
Create the database space by using the CREATE DBSPACE statement. When the CREATE DBSPACE statement is
executed, the database space is associated with an actual local file. In addition, the database space is associated with
a log environment. Information related to the database space is entered in the RDB dictionary.
CREATE DBSPACE statement
In the CREATE DBSPACE statement, specify the names of the database space and the local file for creating the
database space.
Sample CREATE DBSPACE statements for executing database space definitions for STOCKMN_DB follow.
Example:
Create database spaces DBSP_1, DBSP_2, and DBSP_3.
The following database-dedicated NTFS files are allocated to these database spaces:
DBSP_1 :
C:¥SFWD¥RDB¥USR¥DBSP1¥DB_SP1
DBSP_2 :
C:¥SFWD¥RDB¥USR¥DBSP1¥DB_SP2
DBSP_3 :
E:¥SFWD¥RDB¥USR¥DBSP1¥DB_SP3
28
Download from Www.Somanuals.com. All Manuals Search And Download.
Database space name
For the database name, specify up to 36 alphanumeric characters beginning with an alphabetic character.
File name
Specify the name of file to be allocated to the database space.
2.5.3 - Omitted -
2.5.4 Operation of a scalable log
When a database space is created, a log environment can be associated with it. The method of specifying this
scalable log is as follows.
Example:
Associate log group group1 with database spaces DBSP_1 and DBSP_3, and log group group2 with
database space DBSP_2.
Log group name
For the log group name, specify up to 18 characters consisting of alphanumeric characters and underbar (_).
If "system" is specified for the log group name or no log group name is specified, the log group of the system is
assumed to have been specified.
For details on scalable log operation, refer to the RDB Operations Guide.
2.6 Defining a Logical Structure
After a database space has been created, define the logical structure. When the logical structure is defined, the
definition information is stored in the RDB dictionary.
The logical structure definitions include schema definitions, sequence definitions, table definitions, view definitions,
trigger definitions, procedure routine definitions, and function routine definitions.
Figure: Logical structure definition procedure shows the logical structure definition procedure.
[Figure: Logical structure definition procedure]
29
Download from Www.Somanuals.com. All Manuals Search And Download.
Schema definition
Schemas are managed according to schema names. Multiple schemas can be created for a single database.
Define a schema using a CREATE SCHEMA statement. A schema definition includes definitions of the base tables
and views, the elements that form the schema.
Sequence definition
A sequence can be defined to automatically generate values within the sequence. The user can use a sequence to
create primary key values.
Define a sequence using the CREATE SEQUENCE statement. The CREATE SEQUENCE statement can also be
used to add a sequence to a previously defined schema.
Table definition
A table definition defines a base table name and the columns that form the base table. Multiple base tables can be
created for a single schema. Define a base table using the CREATE TABLE statement.
The CREATE TABLE statement can also be used to add a base table to a previously defined schema.
View definition
A view definition defines a view name and the columns that form the view. Multiple views can be created for a single
schema. Define a view using the CREATE VIEW statement.
The CREATE VIEW statement can also be used to add a view to a previously defined schema.
Trigger definition
If a trigger definition is made, data can be automatically inserted into another table when an application program
updates a table.
Use the CREATE TRIGGER statement to make a trigger definition. The CREATE TRIGGER statement can also be
used to add a trigger definition to a previously defined schema.
Procedure routine definition
The database operation tasks include tasks that always process data according to a fixed pattern. These fixed-pattern
processes can be defined in a schema as a processing procedure called a procedure routine. In the procedure routine
definition, specify a procedure routine that belongs to the schema. Multiple procedure routines can be created for one
schema. Define a procedure routine using the CREATE PROCEDURE statement.
The CREATE PROCEDURE statement can also be used to add a procedure routine definition for a defined schema.
Function routine definition
A function routine definition defines a user-created application program written in C as a function. A function routine
can be defined to specify a function in an SQL statement and process it. Define a function routine using the CREATE
FUNCTION statement.
The CREATE FUNCTION statement can also be used to add a function routine to a previously defined schema.
2.6.1 Schema definition
A schema definition defines a schema name, a schema comment definition, and the following elements that form the
schema:
· Sequences
· Base tables
30
Download from Www.Somanuals.com. All Manuals Search And Download.
· View tables
· Triggers
· Procedure routines
· Function routines
Define the schema name using the schema definition statement (CREATE SCHEMA statement).
A sample schema definition for the inventory management database follows. The schema named STOCKS and the
tables that belong to it, such as the STOCK table, are defined for STOCKMN_DB.
Example:
Define a schema for STOCKMN_DB.
Schema name
For the schema name, specify up to 36 alphanumeric characters beginning with an alphabetic character. The schema
name must be unique within the database.
When sequences, base tables, and view tables are specified in SQL statements, the schema name is used to qualify
the sequence and table names. The schema name is also used to qualify the table names if an index is specified in an
31
Download from Www.Somanuals.com. All Manuals Search And Download.
index definition.
Schema comment definition
A comment consisting of a character string can be specified for the schema. A character string of up to 256 bytes can
be specified. If no comment is necessary, omit the specification. An example follows.
Example:
Specify a comment for the STOCKS schema.
2.6.2 Sequence definition
Define a sequence using the CREATE SEQUENCE statement.
A sequence can be defined to automatically generate unique names within the sequence. The user can use a
sequence to create primary key values.
A sample sequence definition for a stock management database follows. This sequence definition defines a sequence
that belongs to a schema named STOCKS.
Example:
Sample sequence definition
Sequence name
Specify a name to be assigned to a sequence. Specify up to 36 alphanumeric characters for a sequence name, whose
first character must be an alphabetic character. Each sequence name within a schema must be unique. A sequence
with the same sequence name may be defined in another schema.
2.6.3 Table definition
Define a base table using the CREATE TABLE statement.
The table definition defines the following items:
· Table name
· Columns that form the base table
- Column name
- Column data type (such as character, integer or date-and-time type)
- Column constraint (such as a unique constraint or allowing or disallowing NULL values)
- Column comment definition
· Table constraint for the base table
· Table comment definition
A sample base table definition for the inventory management database follows. This table definition defines the
STOCK table that belongs to the schema STOCKS.
Example:
Figure: CREATE TABLE statement that defines the STOCK table shows the CREATE TABLE
32
Download from Www.Somanuals.com. All Manuals Search And Download.
statement that defines the STOCK table.
[Figure: CREATE TABLE statement that defines the STOCK table]
Table name
Specify a name to be assigned to the base table using up to 36 alphanumeric characters beginning with an alphabetic
character.
The table name must be unique within the schema. The same table name can be defined in other schemas.
When a table definition is specified as a schema definition element and the table name is modified by a schema name,
the table name must be same as the schema name specified in the schema definition. When the table name is not
modified by the schema name, the table name is considered to be modified by the schema name specified in the
schema definition.
The table name is used to specify the table to be manipulated by a data manipulation SQL statement.
Example 1:
Sample table names
Example 2:
Invalid table name specification
33
Download from Www.Somanuals.com. All Manuals Search And Download.
Example 3:
Valid table name specifications
Column definition
Define the following items for each column that forms the table:
· Column name
· Column data type
· Default value
· Column constraint
· Column comment definition
The column name and column data type must be specified in a column definition. The other items can be specified as
required.
Column name
Specify a name to be assigned to the column. For the column name, specify up to 36 alphanumeric characters
beginning with an alphabetic character. The column name must be unique within a table.
Example:
Sample column names
Column data type
Specify the data type of the column. Table: Column data types shows the types that can be specified. The data type is
determined by the type of data to be stored and the data size (length).
Example 1:
Let the data type of the PRODUCT column of the STOCK table be a 10-character fixed length
character string.
Example 2:
Same definition as example 1
34
Download from Www.Somanuals.com. All Manuals Search And Download.
Example 3:
Let the data type of the PRODUCT column of the STOCK table be a 10-character variable length
character string.
Example 4:
Same definition as example 3
Example 5:
Let the data type of the STOCKQTY column of the STOCK table be a 10-digit external decimal number
with two digits to the right of the decimal point.
Example 6:
Let the data type of the STOCKQTY column of the STOCK table be a 10-digit internal decimal number
with two digits to the right of the decimal point.
Example 7:
The data type of the STOCKQTY column of the STOCK table is as follows:
Example 8:
Let the data type of the STOCKQTY column of the STOCK table be an approximate numeric value with
precision 22.
35
Download from Www.Somanuals.com. All Manuals Search And Download.
Example 9:
Let the data type of the STOCKQTY column of the STOCK table be a double-precision approximate
numeric value.
36
Download from Www.Somanuals.com. All Manuals Search And Download.
[Table: Column data types]
37
Download from Www.Somanuals.com. All Manuals Search And Download.
[Table: Time interval specifications]
Default value
A value can be specified as a default value for a column. Specify a value to be set in the column if no value is
specified when a row is inserted in the table. The defaults can be specified with a constant, login name (under UNIX)
or logon name (under Windows NT/2000/XP), NULL, the current date, the current time, and the current timestamp.
Example 1:
Sample column definition for the ITMNO column of the STOCK table
Example 2:
Sample column definition for using a sequence for the ITMNO column of the STOCK table
Column constraint
A constraint on the data to be stored can be specified for a column. Specify a constraint after the data type
specification. The following two kinds of column constraints can be specified:
38
Download from Www.Somanuals.com. All Manuals Search And Download.
NOT NULL constraint:
Specify this constraint when NULL is not permitted as column data. Specify NOT NULL.
Unique constraint:
Specify this constraint when duplicate values are not permitted as column data. Specify UNIQUE or PRIMARY
KEY.
The unique constraint is detailed later on.
A sample column definition for the ITMNO column of the STOCK table follows. The following conditions are assumed
for ITMNO:
· ITMNO is an integer having up to eight digits.
· ITMNO is unique for each product, and a row of the STOCK table is uniquely identified by ITMNO.
· A row cannot be inserted unless an ITMNO value is entered.
Example:
Sample column definition for the ITMNO column of the STOCK table
Column comment definition
A comment consisting of a character string can be specified for a column. A character string of up to 256 bytes can be
specified. If no comment is necessary, omit the specification. An example follows.
Example:
Specify a comment for the ITMNO column in the STOCK table.
Unique constraint
The unique constraint can be specified as a constraint for a group of several columns within a table. This type of
specification is called a table constraint. A unique constraint can also be specified as a column constraint for a single
column of a table.
With a unique constraint specification, the specified column or group of columns cannot have the same value or group
of values in more than one row. The value of the specified column or values of the group of columns are determined
uniquely within the table. The unique constraint is specified by UNIQUE or PRIMARY KEY.
UNIQUE
Specify UNIQUE in the following situation. The table is not permitted to have more than one row with the same value
or values for the specified column or group of columns. The specification format is as follows.
NOT NULL must already be specified in the column definition for any column for which UNIQUE is specified.
The next example defines the STOCK table with the constraint that two or more rows cannot have identical values in
both the ITMNO and PRODUCT columns.
39
Download from Www.Somanuals.com. All Manuals Search And Download.
Example:
Sample table constraint specification for a group of columns
The STOCK table for which the unique constraint of this example has been specified cannot have rows such as [3]
and [4] in Figure: Sample data that violates the unique constraint. Rows [3] and [4] in Figure: Sample data that violates
the unique constraint violate the unique constraint because they both have 123 as the ITMNO and they both have
REFRIGERATOR as the PRODUCT. Rows [1] and [2] do not violate the unique constraint because the ITMNO values
differ even though the PRODUCT value is the same. Similarly, rows [5] and [6] and rows [7] and [8] do not violate the
unique constraint. If the unique constraint were specified only for the ITMNO column, then rows [3] and [4] and rows
[5] and [6] in Figure: Sample data that violates the unique constraint would violate the unique constraint.
[Figure: Sample data that violates the unique constraint]
PRIMARY KEY
One or a combination of columns used for determining that a row in a table is unique is called a primary key. The
value specified for primary keys must be unique for each row in a table. The specification format is as follows.
NOT NULL must already be specified in the column definition for any column for which PRIMARY KEY is specified.
PRIMARY KEY can only be specified once within a table definition.
The next example specifies the unique constraint related to the ITMNO column of the STOCK table as a table
constraint.
Example:
Sample unique constraint specification for the ITMNO column as a table constraint
40
Download from Www.Somanuals.com. All Manuals Search And Download.
Table comment definition
A comment consisting of a character string can be specified for a table. A character string of up to 256 bytes can be
specified. If no comment is necessary, omit the specification. An example follows.
Example:
Specify a character string comment for the STOCK table.
2.6.4 Table definition for multimedia data storage
This section explains how to define a table that stores data types such as image and voice. This type of data is stored
in a BLOB-type column.
To define a BLOB-type column of 31 kilobytes or more, specify SEQUENTIAL or OBJECT as the table storage
structure.
If OBJECT is used as the data storage structure, the following conditions are added to the definition of a table for
storing numeric values and characters.
1. Only one BLOB-type column for data exceeding 31 kilobytes can be specified, and the column must be
specified as the last column in the table.
2. The NOT NULL constraint must be specified for the column described in item 1.
3. The data type for columns other than the column described in item 1. must be fixed length.
4. An ALTER TABLE statement for changing a table definition cannot be described in item 1.
For more information on storage structure, refer to "2.7 Definition of Storage Structure."
Example:
The following is an example in which the PRODPHOT table is defined in schema S1 when
SEQUENTIAL is used as the data storage structute. In this sample, ITMNO is defined as a column for
non-BLOB-type data. Next, PRODPHOTO is defined as a column for one-megabyte BLOB-type data.
2.6.5 View definition
Define a view using the CREATE VIEW statement. Views are used to simplify data manipulations by application
programs and to join multiple tables and process them as a single table. Views are also used to increase the
independence of application programs and data.
A view definition defines the following items:
· Table name (view name)
· View column list
41
Download from Www.Somanuals.com. All Manuals Search And Download.
- Column name
- Column comment definition
· Query specification
· Table (view) comment definition
A sample view definition for the inventory management database follows. This view definition defines a view consisting
of the rows of the ITMNO and STOCKQTY columns of the STOCK table for which STOCKQTY is at least 50.
Example:
CREATE VIEW statement that defines the MASS_STOCK view
Table name (view name)
Specify a name to be assigned to the view. For the table name, specify up to 36 alphanumeric characters beginning
with an alphabetic character.
A view name is unique in a schema.
Example:
Sample view name specification
View column list
Specify column names for the columns that form the view.
Column name
Specify names for each of the columns that form the view. For the column name, specify up to 36 alphanumeric
characters beginning with an alphabetic character.
A column name is unique in a view.
Example:
Define a view having column names NO and QTY.
Column comment definition
A comment consisting of a character string can be specified for each column in the view. A character string of up to
256 bytes can be specified. If no comment is necessary, omit the specification. An example follows.
Example:
42
Download from Www.Somanuals.com. All Manuals Search And Download.
Specify a comment for the NO column in the MASS_STOCK view.
Query specification
The query specification indicates which portion of the base table forms the view.
Example:
Define the view named MASS_STOCK. Let the ITMNO and STOCKQTY columns of the STOCK table
be the NO and QTY columns of the view, respectively.
Table (view) comment definition
A comment consisting of a character string can be specified for a view. A character string of up to 256 bytes can be
specified. If no comment is necessary, omit the specification. An example follows.
Example:
Specify a character string comment for the MASS_STOCK view.
2.6.6 Trigger definition
Define a trigger using the CREATE TRIGGER statement. In a trigger definition, specify a trigger event and a
procedure to be started. As the trigger event, specify a table data update method that starts the trigger. As the
procedure to be started, specify a procedure to be processed by a triggered SQL statement.
Triggers are classified into the following groups according to the purpose of the trigger:
· Simple trigger
· Update-and-add trigger
· Trigger for calling a procedure routine
Simple trigger
The user can define a simple operation, such as insertion of data into another table, deletion of data from another
table, or updating of data in another table, so that the operation is automatically executed when a table is updated.
Specify a simple INSERT, UPDATE, or DELETE statement as the triggered SQL statement.
The use can only define simple operations. However, the definition is easy because it is not required to define a
procedure routine.
The table updated using the SQL statement that is the source of the trigger cannot be updated using the triggered
SQL statement.
43
Download from Www.Somanuals.com. All Manuals Search And Download.
Update-and-add trigger
For a row added to a database using the INSERT statement or updated using the UPDATE statement, the user can
use a trigger to automatically arrange data of the row into columns. That is, a table updated by an SQL statement that
causes the start of a trigger can be updated in processing of the triggered SQL statement.
For example, the timestamp indicating the time when a column in a row has been updated can be placed in another
column, and the name of the user who updated the row can be placed in another column too. This type of trigger is
called an update-and-add trigger.
Trigger for calling a procedure routine
The user can call a procedure routine by specifying the CALL statement in the triggered SQL statement.
By using the procedure routine, the user can define a procedure that consists of multiple SQL statements as a
triggered SQL statement. In addition, the user can use a trigger to define data integrity and consistency that is
customized according to the user's desired application requirements.
Using triggers can simplify application programs and construct a highly reliable system because the system can
automatically set information in tables and databases as described above.
Sample trigger definitions follow.
Example 1: Simple trigger
If the order price in a row added to the ORDER table exceeds five million, the ORDER_TRIGGER
trigger adds the customer name, product price, and order quantity to the EXPENSIVE_ORDER table.
Sample trigger definition
Operation
44
Download from Www.Somanuals.com. All Manuals Search And Download.
Example 2: Update-and-add trigger
This trigger sets the differential value, variable date and time, and executor name if a stock quantity in
the STOCK table decreases by 10 or more.
Sample trigger definition
Operation
45
Download from Www.Somanuals.com. All Manuals Search And Download.
If this trigger is defined and an SQL statement that updates the STOCK table is executed, the update operation
specified in the triggered SQL statement is automatically executed. To define an update-and-add trigger, specify
ROW_ID in the WHERE clause in the triggered SQL statement.
Example 3: Trigger for calling a procedure routine
When a line is entered to the ORDER table, the trigger "ORDER trigger 2" calls a procedure routine
"ORDER routine." The ORDER routine checks the consistency of the entered data. Then, it changes a
stock quantity in the STOCK table according to the ordered quantity.
Sample procedure routine definition
Sample trigger definition
46
Download from Www.Somanuals.com. All Manuals Search And Download.
Operation
As described above, if a procedure routine and a trigger are defined when an SQL statement that updates the table is
executed, the procedure routine specified in the triggered SQL statement is automatically executed to suppress any
updating that may impair data consistency. The user can define a trigger that checks table for every linked row of the
rows that reference one another in the procedure routine sot that data integrity between the tables can be ensured.
The user can also define a trigger that automatically deletes corresponding data in child tables when a row in the
parent table is deleted.
However, do not define any complex transaction logic with a trigger. A trigger operates as an extension of table
updates that start the trigger. No transaction can be controlled in any trigger. Create complex transaction logic with
stored procedures, and specify that application program are to call procedure routine directly.
Trigger operation
A trigger operates as follows.
· Triggers are executed in the following sequence. Each chained trigger is executed in units of rows, following
this same rule.
1. Executes an SQL statement that updates the table for which a trigger is defined.
2. Loops the row affected by the SQL statement.
3. Moves to the row to be updated or deleted by the SQL statement.
4. Executes the defined BEFORE trigger.
5. Updates, deletes, or inserts the row according to the SQL statement in step 1).
6. Executes the defined AFTER trigger.
· If a trigger event is defined more than once for the same table, triggers are executed in no particular sequence.
Consider the execution sequence to not have an effect on triggers specified with the same trigger operation
point.
· When data load (rdbsloader command) or database reinitialization (rdbfmt command) is executed using the
utility function, any trigger defined for the execution target table does not operate.
· To update the table updated by an SQL statement that starts a trigger in the triggered SQL statement, use an
update-and-add trigger. To define such an update-and-add trigger, specify the UPDATE statement in which
ROW_ID is specified in the WHERE condition of the triggered SQL statement. For this trigger, specify AFTER
as the trigger operation point and INSERT or UPDATE as the trigger event. With another type of trigger, the
table updated by an SQL statement that starts the trigger cannot be updated in processing of the triggered
SQL statement.
47
Download from Www.Somanuals.com. All Manuals Search And Download.
· Chained triggers cannot be executed by updating the triggered SQL statement specified by an update-and-add
trigger.
· If the trigger defined for the table updated by the triggered SQL statement is a trigger other than an
update-and-add trigger, a chain of triggers is executed. If a trigger is executed again as an extension of its own
execution processing, an error occurs.
· A triggered SQL statement operates as the transaction that executes the SQL statement starting the trigger. If
LOCK_MODE is specified in the SQL statement that starts a trigger, the triggered SQL statement operates,
assuming that the same LOCK_MODE is implicitly specified in each SQL data manipulation statement
executed by the triggered SQL statement.
· When a database is updated by a triggered SQL statement, the unique and NOT NULL constraints defined for
an updated table are checked in each SQL statement. If the CALL statement is specified, the constraints are
checked in each SQL statement defined in the CALL statement.
· If an error occurs in a triggered SQL statement, execution of the SQL statement that has started the trigger is
canceled with an error. If the CALL statement is specified as the triggered SQL statement, an error may occur
during execution of an SQL statement in the called procedure routine. In this event, execution of SQL
statements can continue in accordance with the error handling method specified in the procedure routine.
Alternatively, execution of the CALL statement can be assumed to cause the error, and execution of the SQL
statement that starts the trigger can be canceled with an error.
· If the CALL statement is specified as a triggered SQL statement, a transaction rollback exception may occur in
the called procedure routine (SQLSTATE exception code: 40). In this event, the transaction containing the SQL
statement that starts the trigger is automatically rolled back.
Notes on defining a trigger
Note the following points about defining a trigger.
· If a subquery is specified in an SQL statement that starts a trigger, the table specified by the subquery cannot
be updated by the triggered SQL statement.
· If a row with a unique constraint in the table has been updated using the search routine of the UPDATE
statement, using an SQL statement for updating multiple rows may temporarily cause some data to not be
unique. When data is not unique, the row whose updating causes the start of the trigger cannot be referenced
temporarily in the extension of the CALL statement specified as the triggered SQL statement of the executed
trigger. For this reason, if the procedure routine called by the CALL statement must reference the row whose
update causes the start of the trigger, specify the appropriate arguments of the CALL statement to pass the
necessary row values.
· Privileges required when a trigger is defined
The user who wants to define a trigger must have the CREATE privilege for the schema for which the trigger is
defined, TRIGGER privilege for the table for which the trigger is defined, and privilege corresponding to the
SQL operation specified in the triggered SQL statement.
- To specify the INSERT statement: User must have the INSERT privilege for the table specified in the
triggered SQL statement.
- To specify the DELETE statement: User must have the DELETE privilege for the table specified in the
triggered SQL statement.
- To specify the UPDATE statement: User must have the UPDATE privilege for the table specified in the
triggered SQL statement.
- To specify the CALL statement: User must have the execution privilege for the procedure routine
specified in the triggered SQL statement.
· Privilege check when a triggered SQL statement is executed
When an SQL statement starts a trigger and the triggered SQL statement is executed, no privilege check is
performed for the triggered SQL statement. The user who executes the application program does not need the
privilege for the table specified in the triggered operation.
2.6.7 Procedure routine definition
Define a procedure routine using the CREATE PROCEDURE statement. For details about the procedure function,
refer to the "RDB User's Guide: Application Program Development." A sample definition that sets procedure PROC001
in the STOCKMN_DB database follows.
Example:
Define PROC001.
48
Download from Www.Somanuals.com. All Manuals Search And Download.
Procedure comment definition
A comment consisting of a character string can be specified for a procedure routine. A character string of up to 256
bytes can be specified. If no comment is necessary, omit the specification.
2.6.8 Function routine definition
Define a function routine using the CREATE FUNCTION statement. For details of the function routine function, refer to
the "RDB User's Guide: Application Program Development." A sample definition for user-created function routine
USER001 follows.
Example:
Sample definition for user-created function routine USER001
2.7 Defining a Storage Structure
After the logical structure has been defined, define the storage structure. The storage structure definition is then
stored in the RDB dictionary.
The two kinds of storage structure definitions are DSO definitions and DSI definitions. A table DSO and a table DSI
are defined for a table created by a logical structure definition. In addition, if an index is to be defined for a table, an
index DSO and an index DSI are defined.
A storage structure is defined by executing a DSO definition statement and a DSI definition statement.
For details about storage structures, see Chapter 4 "Storage Structure."
Figure: Storage structure definition procedure shows the storage structure definition procedure.
49
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Storage structure definition procedure]
DSO definition
The DSO definition specifies the type of storage structure for storing data and rules such as whether to apply split
table operation.
The two types of DSO definitions are table DSO definitions and index DSO definitions.
Table DSO definition
A table DSO definition specifies the type of storage structure for storing data and rules such as whether to apply split
table operation for a base table. Use the CREATE DSO statement to specify a table DSO definition using.
The storage structures for base tables can be categorized according to the size of the stored data. The storage
structures for handling character and numeric data are different from the storage structures for handling multimedia
data. For information about handling multimedia data, see 2.7.2 "Table DSO definition for multimedia data storage."
Index DSO definition
An index DSO definition defines the columns that form the index and information such as the type of storage structure
for storing index data. Use the CREATE DSO statement to specify an index DSO definition.
DSI definition
A DSI definition specifies an association with the database space where the data is actually stored and information
such as split key values for performing split table operation.
The two types of DSI definitions are table DSI definitions and index DSI definitions.
Table DSI definition
A table DSI definition specifies an association with a database space according to a table DSO definition. The table
DSI definition also specifies information such as split key values for performing split table operation. Use the CREATE
DSI statement to specify a table DSI definition.
Index DSI definition
An index DSI definition specifies an association with a database space according to an index DSO definition. Use the
CREATE DSI statement to specify an index DSI definition.
Storage structure definition statements consist of table and index DSO definition statements and DSI definition
statements. Details about these definition statements are explained in 2.7.1 "Table DSO definition," 2.7.3 "Index DSO
definition," 2.7.4 "Table DSI definition," and 2.7.5 "Index DSI definition."
Scope definition
Scope defines the range of table data that is to be manipulated. The user can specify a range in advance so that only
data within that range is processed. Application and release of the scope can be specified for each user.
A sample of using scope follows.
Example:
Define scope TOKYO, apply it to user SUZUKI, and retrieve a limited table from an application
program.
50
Download from Www.Somanuals.com. All Manuals Search And Download.
2.7.1 Table DSO definition
Use the CREATE DSO statement to specify a table DSO definition.
Sample table DSO definitions follow.
Example:
Table DSO definitions
· When split table operation is not applied (data structure: SEQUENTIAL)
· When split table operation is not applied (data structure: RANDOM)
51
Download from Www.Somanuals.com. All Manuals Search And Download.
· When split table operation is applied (data structure: SEQUENTIAL)
· When split table operation is applied (data structure: RANDOM)
DSO name
For the DSO name, specify up to 36 alphanumeric characters beginning with an alphabetic character. The DSO name
must be unique within the database.
Table name
Specify the name of the base table corresponding to the structure definition. The table name must be qualified by the
schema name.
Data structure
Specify information about the data structure to be used when storing table data. SEQUENTIAL or RANDOM can be
specified when a table storage structure is defined by a DSO definition.
SEQUENTIAL
When SEQUENTIAL is specified, added data is stored in the order of addition.
Specify the page length at PAGESIZE. Specify ORDER(0) not to reuse a deleted area or ORDER(1) to reuse it. For
more information, see "4.1.1 SEQUENTIAL structure."
52
Download from Www.Somanuals.com. All Manuals Search And Download.
RANDOM
When RANDOM is specified, added data is stored in a random order.
For RANDOM, use CLUSTER to specify a key for determining the data storage position. If CLUSTER is omitted, the
data is stored on the basis of the arrangement of PRIMARY KEY in the table definition.
For PAGESIZE1 and PAGESIZE2, specify the page sizes of the data structure elements (PRIME and OVERFLOW for
a RANDOM structure).
A RANDOM structure allows a data storage position to be determined by specifying RULE. For more information, see
"4.1.1 RANDOM structure."
Split condition
When split table operation is to be applied, specify the split condition. The DSI definition statement specifies the actual
subdivision units. Here, specify only the condition used for splitting.
The rows stored in each subdivision unit is determined as follows. The values set in each column specified in the
column name list determine these rows. In addition, the result of assigning the following values determines the rows.
These values are the values specified by the DSI definition statement for the question marks (?) in the dummy value
list.
If a split condition is specified in a DSO definition statement and if the data structure is RANDOM, the column name
list specified in the split condition must be included in the column name list specified in CLUSTER.
Up to 64 columns can be specified in the column name list. The number of column names in the column name list
must be the same as the number of question marks (?) in each dummy value list.
Table: Data types of columns that can be specified for CLUSTER and column name list shows the data types of the
columns that can be specified for CLUSTER and the column name list.
[Table: Data types of columns that can be specified for CLUSTER and column name list]
NOT NULL must be specified for the CLUSTER columns and the column name list. The column name list specified by
the split condition is called the split key.
A sample DSO definition for the ORDER table with CUSTOMER and PRODNO of the ORDER table as a composite
split key follows.
Example 1:
Relationship between the number of column names in the column name list and the number of
question marks (?) in the dummy value lists
53
Download from Www.Somanuals.com. All Manuals Search And Download.
Example 2:
Table DSO definition with multiple columns as the split key
2.7.2 Table DSO definition for multimedia data storage
This section explains how to specify a table DSO definition for storing image or audio data. This data is stored in a
BLOB-type column.
For storing data such as image and voice, the record length in a table may exceed the page length. In this case,
specify SEQUENTIAL or OBJECT as the data structure. If OBJECT is specified, assign 32 to PAGESIZE. For the
OBJECT type, split operation is not available, and the associated table definition is conditional. For more information
on table definition conditions, refer to "2.6.4 Table Definition for Multimedia Data Storage." A sample definition follows.
Example:
Table DSO definition for storing multimedia data (SEQUENTIAL)
Example:
Table DSO definition for storing multimedia data (OBJECT)
54
Download from Www.Somanuals.com. All Manuals Search And Download.
2.7.3 Index DSO definition
Use the CREATE DSO statement to specify an index DSO definition. If a column is used for a conditional search,
define an index for the column used in the search condition to improve the search efficiency.
If PRIMARY KEY or UNIQUE is specified in a table definition, an index DSO definition with the same column
configuration is required. (The order is the same.) If RANDOM is specified for the table data structure, either
PRIMARY KEY or UNIQUE can be associated with CLUSTER KEY. In this case, an index DSO definition is
unnecessary.
In addition, a new index DSO definition can be added for a table in which data has already been stored.
Sample index DSO definitions follow.
Example:
Index DSO definitions
· When the table data structure is SEQUENTIAL
· When the table data structure is RANDOM
· When the table data structure is OBJECT
55
Download from Www.Somanuals.com. All Manuals Search And Download.
DSO name
For the DSO name, specify up to 36 alphanumeric characters beginning with an alphabetic character.
The DSO name must be unique within the database.
Key specification
Specify the table name for which the index is to be created and the list of column names forming the index.
Table name
Specify the name of the base table for which the storage format is to be defined. The table name must be qualified by
the schema name.
Column name list
Specify the column names for which the index is to be created. Table: Data types of columns that can be specified for
the column name list of an index shows the data types of the columns that can be specified for the column name list of
an index.
[Table: Data types of columns that can be specified for the column name list of an index]
Data structure
Specify information about the data structure to be used when index data is stored. Only BTREE can be specified.
56
Download from Www.Somanuals.com. All Manuals Search And Download.
BTREE
For PAGESIZE1, specify the page size of the data part.
For PAGESIZE2, specify the page size of the index part.
For details, refer to "4.2.1 BTREE Structure."
Base representation
Specify the way the index and base are associated. If this specification is omitted, SEQUENTIAL or OBJECT default
to ADDRESS , and RANDOM defaults to KEY.
ADDRESS:
The index and table records are associated according to the storage addresses of the table records. Specify
ADDRESS when the table data structure is SEQUENTIAL or OBJECT.
KEY:
The index and table records are associated according to the cluster key of the table records. Specify KEY
when the table data structure is RANDOM.
2.7.4 Table DSI definition
Use the CREATE DSI statement to specify a table DSI definition. The table DSI definition allocates database space
according to the table DSO definition.
Sample table DSI definitions follow.
Example:
Table DSI definitions
· When split table operation is not applied (data structure: SEQUENTIAL)
· When split table operation is not applied (data structure: RANDOM)
· When split table operation is applied (data structure: SEQUENTIAL)
57
Download from Www.Somanuals.com. All Manuals Search And Download.
· When split table operation is applied (data structure: RANDOM)
· When the table data structure is object
DSI name
For the DSI name, specify up to 36 alphanumeric characters beginning with an alphabetic character.
The DSI name must be unique within the database.
DSO name
Specify the table DSO name given in a CREATE DSO statement.
With no split table operation, define only one DSI for one DSO definition. However, if split table operation is to be
applied, define multiple DSIs. These DSIs specify split key values. The DSI definitions for split table operation must
include all existing data.
Split values
Specify the values for the split condition when split table operation is to be applied.
Specify the constant value for the question marks (?) in the dummy value list. The dummy value list is specified in the
split condition of the table DSO definition. If the split condition includes multiple question marks (?), use commas to
delimit the constant values in order of occurrence. The number of constant values must be the same as the number of
58
Download from Www.Somanuals.com. All Manuals Search And Download.
question marks (?) specified in the split condition.
The user cannot specify split values to have the storage destination of a given row (data) include multiple DSIs. Table:
Specification formats of constants that can be specified for split values shows the specification formats of constants
that can be specified for split values.
[Table: Specification formats of constants that can be specified for split values]
59
Download from Www.Somanuals.com. All Manuals Search And Download.
[Table: Specification formats of time interval types that can be specified for split key values]
Space allocation
Specify the physical space to be allocated to the table DSI.
Specify the name of the database space where the data is to be physically stored. In addition, specify the size of the
storage area to be acquired for this DSI within the database space. The acquired storage area is accessed according
to the page size specified in the DSO definition.
Database space name
Specify the name of the physical database space where the data is actually stored.
To store data in multiple database spaces for scalable log operation, specify their names in the same log group.
Allocation size
Specify an unsigned integer combined with a unit symbol (K or M) to denote the size of the storage area acquired in
the database space. The unit symbol K indicates kilobytes, and the unit symbol M indicates megabytes.
The units to be used for storage in the database space according to the table DSI are determined as follows. The split
condition in the table DSO definition and the split key value specification determine these storage units. Some
examples follow.
Example 1:
Relationship between the number of question marks (?) specified in the split condition and the number
of constants in the split value specification
60
Download from Www.Somanuals.com. All Manuals Search And Download.
Create a television DSI by splitting and storing STOCK table data according to PRODUCT and WHCODE values.
Example 2:
Create a television DSI for the STOCK table.
Create a JAPAN DSI by splitting and storing ORDER table data according to CUSTOMER region. The ORDER table
CUSTOMER numbers are divided by region with the range 71 and 72 representing companies in JAPAN.
Example 3:
Create JAPAN DSI for the ORDER table.
61
Download from Www.Somanuals.com. All Manuals Search And Download.
Create a television and refrigerator DSI by splitting and storing STOCK table data according to PRODUCT and
WHCODE values. Set PRODUCT and WHCODE for the split condition, and specify multiple split values.
Example 4:
Create a DSI for products TELEVISION and REFRIGERATOR for the STOCK table.
Create a sales amount DSI by splitting and storing SALES table data according to FISCAL YEAR and MONTH.
Specify multiple split values.
Example 5:
Create a DSI for sales amount of the second half of fiscal years 1999 and 2000 for the sales table.
62
Download from Www.Somanuals.com. All Manuals Search And Download.
2.7.5 Index DSI definition
Use the CREATE DSI statement to specify an index DSI definition. The index DSI definition allocates database space
according to the index DSO definition.
A new index DSI definition can be added for a table DSI in which data has already been stored.
Example:
Create an index DSI definition.
63
Download from Www.Somanuals.com. All Manuals Search And Download.
DSI name
For the database name, specify up to 36 alphanumeric characters beginning with an alphabetic character. The DSI
name must be unique within the database.
DSO name
Specify the index DSO name given in a CREATE DSO statement.
Table DSI name
Specify a DSI name indicating the corresponding base table. When split table operation is to be applied, create an
index for each table DSI. When split table operation is not to be applied, can not specify the table DSI name.
Space allocation
Specify the physical space to be allocated to the index DSI.
Specify the name of the database space where the data is to be physically stored. In addition, specify the size of the
storage area to be acquired for this DSI within the database space.
Database space name
Specify the name of the physical database space where the index data is actually stored.
To store data in multiple database spaces for scalable log operation, specify their names in the same log group.
Allocation size
Specify an unsigned integer combined with a unit symbol (K or M) to denote the size of the storage area acquired in
the database space. The unit symbol K indicates kilobytes, and the unit symbol M indicates megabytes.
2.7.6 DSI initialization
A DSI must be initialized before data is stored. This section explains DSI initialization. Figure: Overview of DSI
initialization provides an overview of DSI initialization.
64
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Overview of DSI initialization]
A DSI must be initialized before data is stored. The rdbfmt command performs DSI initialization. However, when the
rdbsloader command is used to store data, DSI initialization is unnecessary.
In addition, if the DSI is associated with a shared buffer to improve performance, the rdbconbf command must be
executed before the rdbfmt command. For more information on the rdbconbf and rdbfmt commands, refer to the man
command (under UNIX) or the SymfoWARE/RDB online manual (under Windows NT).
An example of DSI initialization follows.
Example:
Initialize created DSIs.
2.7.7 Scope definition
A scope is defined by using the CREATE SCOPE statement.
The CREATE SCOPE must be used by the person who defined table DSI.
Example:
Define scope TOKYO.
Scope name
Specify the name of a scope that limits the range of DSI names using up to 36 alphanumeric characters beginning
65
Download from Www.Somanuals.com. All Manuals Search And Download.
with an alphabetic character.
A scope name is unique in a database.
DSI name list
Specify the DSI names of tables whose access range is limited as DSI name lists. Each DSI name must be unique in
the database. A DSI that is the same as that of the scope definition statement cannot be specified.
Caution:
Limiting a data manipulation range with the scope function is only effective for SQL statements of an application
program or rdbupt command. It is not effective for RDB commands such as rdbsloader.
2.8 Applying a Storage Structure Definition
This section explains how to apply a storage structure definition. Application of storage structure definition involves
application of scope definition.
2.8.1 Scope definition application
A scope definition is applied by using the APPLY SCOPE statement. A scope definition can be applied to a user to limit
the range of items in a table that the user is allowed to manipulate.
The executor of the APPLY SCOPE statement must be the user who defined the scope.
A sample scope application follows.
Example:
Apply scope TOKYO to user SUZUKI.
Scope name
Specify the name of the scope applied to the table user with up to 36 alphanumeric characters beginning with an
alphabetic character.
A scope name must be defined by the CREATE SCOPE statement.
A scope name is unique in a database.
Privilege identifier
Specify the user identifier of the scope user.
2.9 Simplifying a Storage Structure Definition
This section explains how to simplify a storage structure definition.
When defining a storage structure in a simplified form, use table or index definition. In a table or index definition,
specify a database space to store data. SymfoWARE/RDB automatically defines a storage structure according to the
specification. In this case, the table storage structure is set to SEQUENTIAL and the index storage structure is set to
BTREE. For a multimedia data storage table, SEQUENTIAL or OBJECT can be selected as the table storage
structure; however, split storage is not available.
If a storage structure is defined in a simplified form, DSO and DSI of the table are automatically named in accordance
with the table or index definition. The data length and allocation size are then also automatically determined.
66
Download from Www.Somanuals.com. All Manuals Search And Download.
A simplified storage structure definition dynamically extends the DSI capacity.
The prefix for naming, the data length, the allocation size, and the DSI capacity extension can be changed by using
parameters in the operating environment file. For a multimedia data storage table, the storage structure can be
selected. For information about the operating environment file, refer to the "RDB User's Guide: Application Program
Development."
2.9.1 Table definition
In a table definition, specify a data space as a storage area for table data. When a storage area is specified, the table
DSO and DSI are automatically defined. In addition, DSI is automatically initialized.
Storage area specification
Specify a database space as a storage area for actual table data. If a storage area is specified, the table and schema
names should be specified using up to 8 alphanumeric characters beginning with an alphabetic character.
If DEFAULT_DSI_NAME=CODE is specified in the system operating environment file, however, the table and schema
names may be specified using up to 36 alphanumeric characters beginning with an alphabetic character.
The example below defines a base table for an stock management database with a storage structure. Define an
STOCK table belonging to schema STOCKS.
Example:
CREATE TABLE statement defining an STOCK table (with storage area specification)
The above specification has the following definitions:
Table DSO and DSI names
DSO and DSI of a table are named by combining schema and table names in a table definition. The DSO and DSI
names are the same.
67
Download from Www.Somanuals.com. All Manuals Search And Download.
If DEFAULT_DSI_NAME=CODE is specified in the system operating environment file, however, table DSO and DSI
are named with a 10-diit figure determined by the system.
Examples are given below.
Example 1:
When the schema and table names are character strings
Example 2:
If DEFAULT_DSI_NAME=CODE is specified in the system operating environment file
Table storage structure
The table storage structure becomes as follows: Note that these are automatically defined by SymfoWARE/RDB.
Storage structure:
SEQUENTIAL structure
Data part page size:
4 kilobytes
Data part allocation size:
256 kilobytes
Area reuse specification:
ORDER(1)
For information about the storage structure, see "2.7 Storage structure definition."
2.9.2 Table definition for multimedia data storage
For a multimedia data storage table, specification of the storage area, and naming a table DSO and DSI are the same
as that for a table of characters and numeric values. The following example is a storage area specification for a table
that is to storage multimedia data and its table storage structure. For information about specifying the table DSO and
table DSI names, see 2.6.2 "Table definition."
Example:
Storage area specification for a table that is to storage multimedia data
68
Download from Www.Somanuals.com. All Manuals Search And Download.
If DEFAULT_DSI_TYPE is specified for the operating environment file, this specification has the same meaning as of
the definition below.
· At DEFAULT_DSI_TYPE = SEQUENTIAL
· At DEFAULT_DSI_TYPE = OBJECT
The table storage structure is defined as follows.
Storage structure:
If the table format does not satisfy the conditions listed below, specify SEQUENTIAL as the storage structure.
If the table format satisfies all the conditions listed below, specify OBJECT as the storage structure. If
necessary, the OBJECT structure can be changed to SEQUENTIAL structure by specifying
DEFAULT_DSI_TYPE for the operating environment file. If this parameter is omitted, OBJECT is assumed to
have been specified.
· Only one BLOB-type column is specified at the end of a table with a size of 32 kilobytes or more.
· Columns other than BLOB-type columns have the fixed-length attribute.
· The NOT NULL restriction is specified for the BLOB-type column.
Page size of data part:
Specify the page size defined in DEFAULT_TABLE_SIZE of the operating environment file in the
SEQUENTIAL structure. The page size should be 32 kilobytes.
In the OBJECT structure, specify a page size of 32 kilobytes.
Allocated size of data part:
For the SEQUENTIAL structure, specify the allocated size of the table data storage area defined in
DEFAULT_TABLE_SIZE of the operating environment file. Reserve a sufficient value, taking into account the
amount of data to be handled.
For the OBJECT structure, specify the allocated size of the table data storage area defined in
69
Download from Www.Somanuals.com. All Manuals Search And Download.
DEFAULT_OBJECT_TABLE_SIZE of the operating environment file. Reserve a sufficient value, taking into
account the amount of data to be handled.
For information about the storage structure, see 2.7 "Defining a Storage Structure."
2.9.3 Index definition
Use the CREATE INDEX statement to define an index.
In the index definition, specify the columns that form the index key and the database space for holding the table data.
SymfoWARE/RDB automatically generates the index DSO and DSI definitions.
Index name
Define the name (index name) to be assigned to the index, using up to 8 alphanumeric characters beginning with an
alphabetic character.
Note that the index name can be specified with character strings of up to 36 alphanumeric characters beginning with
an alphabetic character when DEFAULT_DSI_NAME=CODE is specified in the system operating environment file.
To delete the index definition, specify the index definition to be deleted by index name.
Key specification
Specify the column name of the column that is to be the index key. Multiple columns can be combined to form a single
index.
Storage area specification
After the keyword ON, specify the database space name of the database space for storing the index.
The following example defines the storage structure of an index formed by combining the ITMNO and PRODUCT
columns of the stock management database. The index name is IDX1, and the index is stored in the database space
named DBSPACE1.
Example:
CREATE INDEX statements for defining the STOCK table
The preceding specification has the same meaning as the following definition.
70
Download from Www.Somanuals.com. All Manuals Search And Download.
Index DSO name and DSI name
The index DSO and index DSI names are generated by combining the schema name and index name from the index
definition. The DSO and DSI names are the same.
If DEFAULT_DSI_NAME=CODE is specified in the system operating environment file, the system assigns 10-digit
names for the index DSO and DSI names.
An example follows.
Example 1:
When the schema name and index name are character strings
Example 2:
When DEFAULT_DSI_NAME=CODE is specified in the system operating environment file
71
Download from Www.Somanuals.com. All Manuals Search And Download.
Index storage structure
The index storage structure is as follows:
Storage structure:
BTREE structure
Data part page size:
2 kilobytes
Index part page size:
2 kilobytes
Data part allocation size:
168 kilobytes
Degeneration specification:
Present
Index part allocation size:
32 kilobytes
Index comment definition
A comment consisting of a character string can be specified for an index if the index is defined with an abbreviated
storage structure definition. A character string of up to 256 bytes can be specified. If no comment is necessary, omit
the specification. An example follows.
Example:
Specifying a comment for an index that has an abbreviated storage structure definition.
2.10 Defining a Temporary Table
Before using a temporary table, define the temporary table and an index.
A temporary table is created specifically for a user of an application program. Multiple users can use temporary tables
with the same table name. Uses of temporary tables are listed below.
· Extracting data necessary for a user from a database and creating a table containing the data
· Incorporating processing results of a procedure routine into an application program using a temporary table
As described above, a temporary table can be used as a table independent of other application programs to facilitate
development of an application program.
Extracting data necessary for a user from a database and creating a table containing the dat
a
When data manipulated by a user can be limited or if specific data is frequently referenced, creating in advance a
compact table containing necessary data enables smooth processing that is not affected by update operations of
another user.
Temporary tables can be used for such processing. To create a compact table, data can be extracted from the master
table (STOCK table) according to the conditions set for a user and then stored in a temporary table (T1).
72
Download from Www.Somanuals.com. All Manuals Search And Download.
A user can execute the following INSERT statement to create a temporary table (T1) that contains necessary data:
Incorporating processing results of a procedure routine into an application program using a
temporary table
Because a temporary table is created for one user, data can be passed between procedure routines that are executed
consecutively ((1) and (2)) and between an application program and procedure routine ((3) and (4)).
73
Download from Www.Somanuals.com. All Manuals Search And Download.
Processing results of a procedure routine for multiple transactions that outputs processing results in the same format
can be stored in a temporary table (5) in order to pass the results to an application program (6).
For more specific examples of use, refer to the "RDB User's Guide: Application Program Development."
When multiple connections are established between an application program and servers, a temporary table is created
for each connection. These temporary tables are independent, and the connections cannot share the temporary
tables.
74
Download from Www.Somanuals.com. All Manuals Search And Download.
The user can specify whether to use a temporary table within a session of the application program or within a
transaction. Stored data is erased after the session or transaction terminates.
The storage structure of a temporary table is SEQUENTIAL. The storage structure of the index of a temporary table is
BTREE. The storage structures are automatically defined.
Defining a temporary table
Use the CREATE TABLE statement to define a temporary table.
A sample temporary table definition follows.
This sample definition defines the STOCK_TEMPORARY table for database spaces DBSP_1 and DBSP_2.
Example:
CREATE TABLE statement for defining the STOCK_TEMPORARY table
Table name
Specify a name to be assigned to a temporary table. Specify up to 36 alphanumeric characters for a table name,
whose first character must be an alphabetic character. The table name must be qualified by a schema name.
Table elements
Specify the name, data type, default value, and constraint of each column in a temporary table. Specify up to 36
75
Download from Www.Somanuals.com. All Manuals Search And Download.
alphanumeric characters for a column name, whose first character must be an alphabetic character. Each column
name within the table must be unique.
Row deletion specification
Specify when to delete a row in a temporary table. If this argument is omitted, the temporary table is assumed to be
used within a transaction, and data stored in the temporary table is deleted when the transaction terminates.
DELETE ROWS:
The temporary table is assumed to be used within a transaction, and data stored in the temporary table is
deleted when the transaction terminates.
PRESERVE ROWS:
The temporary table is assumed to be used in a session, and data stored in the temporary table is deleted
when the session terminates.
Comment definition
For a temporary table, a comment can be specified with a character string or national character string. A character
string of up to 256 bytes can be specified. Both a character string and national character string can be specified as a
comment. If no comment is necessary, omit the specification.
Database space name
Specify the name of a physical database space in which to store data.
Number of users
Specify the number of temporary tables to create in a database space. Specify the number of users so that the total
number of database space users indicates the multiplicity of the application program that uses the temporary tables.
DSO name and DSI name of temporary table
The DSO name and DSI name of a temporary table begin with _TEMP.
Defining an index
Use the CREATE INDEX statement to define an index. In the index definition, specify the columns that form the index
key.
A sample index definition for a temporary table follows.
This sample definition defines an index for the STOCK_TEMPORARY table.
Example:
CREATE INDEX statement for defining an index
Remarks: KEY (ITMNO, STOCKQTY) is a key specification.
Index name
Define a name (index name) to be assigned to an index. Specify up to 36 alphanumeric characters for the index name,
whose first character must be an alphabetic character.
Key specification
Specify the name of a column to be used as the index key. Multiple columns can be combined to form a single index.
76
Download from Www.Somanuals.com. All Manuals Search And Download.
The index is created in the database space specified in the temporary table definition and paired with a temporary
table.
Index DSO name and DSI name
The DSO name and DSI name of an index begin with TEMP.
2.11 Privilege Information Definition
Use the GRANT statement to define privilege information. If a resource is defined, only the user who defined the
resource retains all privileges for the resource. In order for another user to access the resource, privilege information
must be defined. The GRANT statement can be used to specify which privileges to grant to specific users for a
resource. A sample definition that specifies privilege information in the stock management database follows. The
definition defines privilege information for the STOCK table belonging to schema STOCKS.
Example:
Grants the reference, storage, and update privileges for the STOCK table.
When defining privilege information, the user may want to specify at one time the privileges necessary for a specific
transaction. In this situation, use of roles is convenient for granting privileges.
Defining privileges using a role
A role is a group of privileges necessary for a transaction. Define a role to specify the privileges necessary for a
transaction. For efficient privilege management, a role can be defined to grant the role privileges to all users who
perform the transaction.
The procedure for granting privileges using the role function is given below.
1. Define a role.
2. Specify the privileges to be granted in the role.
3. Grant the role privileges to users.
The role function is outlined below.
77
Download from Www.Somanuals.com. All Manuals Search And Download.
1) Define a role.
Use the CREATE ROLE statement to define a role.
A sample role definition for defining role STOCKS_A2 follows.
Example:
2) Specify the privileges to be granted in the role.
Use the GRANT statement to specify the privileges to be granted in the role. In the defined role, specify the privileges
granted for accessing a table in a database.
The following table lists the privileges that can be defined in a role by using the GRANT statement.
An example of specifying privileges granted for individual tables in role STOCKS_A2 follows.
Example:
78
Download from Www.Somanuals.com. All Manuals Search And Download.
3) Grant the role privileges to users.
Grant the role privileges to users.
Use the GRANT statement to grant the role privileges to users.
An example of granting the privileges granted in role STOCKS_A2 to users SATO, SUZUKI, and TANAKA follows.
Example:
To enable the privileges specified with the GRANT statement in the defined role, execute the SET ROLE statement in
an application program. For details on how to execute the SET ROLE statement in an application program, refer to the
"RDB User's Guide: Application Program Development."
Specifying a default role
After a role is created for defining privilege information, a default role can be specified. A default role is a role that is
effective prior to execution of the SET ROLE statement in the application program specified at environment
configuration.
Use the ALTER USER statement to specify a default role.
An example of specifying default role STOCKS_A2 for users SATO, SUZUKI, and TANAKA follows.
Example:
2.12 Defining Optimization Information
For more efficient database access, define optimization information. Determine the optimization information by
considering the number of records stored in the database and the changes in the index key caused after the definition
of the storage structure is completed.
After defining the optimization information, fetch and analyze an access plan so the database can be tuned
appropriately. Once the database is tuned by an access plan, optimization information need not be redefined and
updated even if the data status is changed. For details on how to fetch and analyze the access plan, refer to the
SQLTOOL User's Guide.
If it is impossible to estimate the number of records to be stored in a database and variations of the index key,
optimization information can be defined after the data is actually stored.
What is optimization information?
Optimization information is used to efficiently process data according to data status. It is defined for base table and
index DSIs, and is used to execute application programs. Optimization information contains values that depend on the
79
Download from Www.Somanuals.com. All Manuals Search And Download.
status of the data in a database, for example, number of data items, and base table and index DSI space
requirements.
Optimization information definition opportunity
Optimization information must be determined in an ordinary way, considering the number of records stored in a
database and variations of the index key that will occur after the definition of the storage structure is completed.
Additional opportunities for optimization information definition are listed below.
When optimization information is defined with values assumed in advance:
· When a database is defined
· When a database is reconfigured
- When a table DSI is added at split storage
- When index is added
- When a large amount of data is added or updated
Defining optimization information
Use the rdbddlex command to specify the SET STATISTICS statement to define optimization information with the
values assumed in advance. For more information on the SET STATISTICS statement, refer to the SQL Reference
Guide.
Optimization information must be defined for each table or index DSI.
Optimization information can be defined for each table or index DSO; however, the system converts the specified
value for each DSO into a value for each DSI.
Optimization information should therefore be defined for each DSI.
· Definition for each table DSI (data structure: SEQUENTIAL)
· Definition for each index DSI (data structure: BTREE)
The items to be specified depend on the definition method. Details of the optimization information to be defined are
listed below.
80
Download from Www.Somanuals.com. All Manuals Search And Download.
Definition for each DSI
Definition for each table
81
Download from Www.Somanuals.com. All Manuals Search And Download.
Definition for each index DSO
Output of optimization information
To output the defined optimization information, use the rdbddlex command to specify the PRINT STATISTICS
statement. For more information on the PRINT STATISTICS statement, refer to the SQL Reference Guide.
Examples of specification and output given below.
Example 1:
Output example of optimization information defined for each table DSI (data structure: SEQUENTIAL or
OBJECT)
Example 2:
Output example of optimization information defined for each table DSI (data structure: RANDOM)
82
Download from Www.Somanuals.com. All Manuals Search And Download.
Example 3:
Output example of optimization information defined for each index DSI (data structure: BTREE)
Example 4:
Output example of optimization information defined for each table (data structure: SEQUENTIAL or
OBJECT)
83
Download from Www.Somanuals.com. All Manuals Search And Download.
Example 5:
Output example of optimization information defined for each table (data structure: RANDOM)
84
Download from Www.Somanuals.com. All Manuals Search And Download.
Example 6:
Output example of optimization information defined for each index DSO
85
Download from Www.Somanuals.com. All Manuals Search And Download.
2.13 Generating a Database
A database is generated by the entry of data in the base tables. Data is entered after the storage destination database
spaces are created, and the definition of logical structures and storage structures for base tables and indexes is
completed.
Database generation methods
A database can be generated in the following two ways:
· By using a data file and the rdbsloader command (DSI initialization unnecessary)
· By creating an application program for database generation and using the INSERT statement (DSI initialization
required)
Figure: Database generation methods provides an overview of the two database generation methods: using the
rdbsloader command and using the INSERT statement of an application program.
[Figure: Database generation methods]
Generating a database by using the INSERT statement
An example of generating a database by using the INSERT statement follows.
For details on the INSERT statement, refer to the "SQL Beginner's Guide."
86
Download from Www.Somanuals.com. All Manuals Search And Download.
Generating a database by using the rdbsloader command
A sample for generating a database by using the rdbsloader command follows.
UNIX
Windows NT/2000/XP
For information about the rdbsloader command, refer to "RDB Operations Guide."
87
Download from Www.Somanuals.com. All Manuals Search And Download.
2.14 Referencing Database Definition Information
After the database is defined, verify the database definition information. This section explains how to print database
definition information.
The rdbprt command prints the database name list and definition information. The rdbddlex command with the PRINT
STATISTICS statement or the rdbups command prints the optimization information. These informations can be used to
perform database management tasks such as confirming the range of database usage.
For details on the PRINT STATISTICS statement, refer to the SQL Reference Guide. For details on the rdbups and
rdbprt commands, refer to the RDB Operations Guide, or the man command (under UNIX) or the SymfoWARE/RDB
online manual (under Windows NT).
This section explains the information printed by the rdbprt command, the command specification method, and the print
format.
2.14.1 Information printed by the rdbprt command
The rdbprt command prints the following information:
· Database name list information
Database name list information lists the names of all databases under the target SymfoWARE/RDB.
· User name list information
User name list information is a list of the names of all users under the target SymfoWARE/RDB.
· Role name list information
Role name list information is a list of the names of all roles under the target SymfoWARE/RDB.
· Definition information
Definition information includes information about a database, schema, base table, view table, temporary table,
trigger, routine, DSO, DSI, scope, database space, sequence, and user, and role definitions.
· User parameter information
User parameter information includes user parameter information used by the target SymfoWARE/RDB.
To print database name list information, specify DB in the m option of the rdbprt command. To print definition
information, specify DEF in the m option of the rdbprt command. The definition information print specifications are:
· DB
· SCHEMA
· TABLE
· TRIGGER
· ROUTINE
· DSO
· DSI
· SCOPE
· DBSPACE
· SEQUENCE
· USER
· ROLE
Items printed in definition information
The information generated for each print specification type is given next.
DB specification
· Database name
· Database creator
· Database definition date and time
· Names of schemas belonging to the database
· Names of database spaces belonging to the database
· Log group name of log environment used for database space belonging to the database
· Names of scope belonging to the database
SCHEMA specification
· Schema name
· Name of database to which the schema belongs
· Schema creator
· Schema definition date and time
88
Download from Www.Somanuals.com. All Manuals Search And Download.
· Schema comment definition
· Privilege information (only when -p is specified)
· Names of tables belonging to the schema
· Table type (base, view, or temporary table)
· Names of routines belonging to a schema
· Types of routines belonging to a schema
· Names of triggers belonging to a schema
· Names of sequences belonging to a schema
TABLE specification
· Table, view table, or temporary table name
· Name of database to which the table belongs
· Name of schema to which the table belongs
· Type (base, view, or temporary table)
· Base, view, or temporary table creator
· Table definition date and time and update date and time
· Table comment definition
· Privilege information (only when -p is specified)
· Column information (column name, existence of NOT NULL specification, byte length, data type, DEFAULT
value)
· Table record length
· Table constraint information (PRIMARY KEY information and UNIQUE information)
· PRIMARY KEY information (component column names)
· UNIQUE information (component column names)
· Table DSO name
· Index DSO name
· Routine name
· Routine type
· View information (reference view information, configuration view information, query expression, indication of
whether WITH CHECK OPTION is specified, updatability)
· Trigger name
· Name of the database space to which a temporary table is allocated
· Number of users of the database space to which a temporary table is allocated
TRIGGER specification
· Trigger name
· Name of database to which the trigger belongs
· Name of schema to which the trigger belongs
· Trigger creator
· Trigger definition date and time
· Name of schema to which the trigger-target table belongs
· Name of trigger-target table
· Type of trigger-target table
· Trigger operation point
· Trigger event
· Name of column to be updated in the trigger-target table
· Correlation name of old and new values in the trigger-target table.
· Execution unit of triggered operation
· Execution condition of triggered operation
· Table name included in the SQL statement of triggered operation
· Name of schema to which the table included in the SQL statement of the triggered operation belongs
· Type of table included in the SQL statement of triggered operation
· SQL statement of triggered operation
· Routine name included in the SQL statement of a triggered operation
· Name of the schema to which the routine included in the SQL statement of a triggered operation belongs
· Type of routine included in the SQL statement of a triggered operation
· Sequence name included in the SQL statement of a triggered operation
· Name of the schema to which a sequence included in the SQL statement of a triggered operation belongs
Routine specification
· Routine name
· Name of the database to which a routine belongs
· Name of the schema to which a routine belongs
· Routine creator
89
Download from Www.Somanuals.com. All Manuals Search And Download.
· Routine definition date and time
· Routine comment definition
· Privilege information (only if -p is specified)
· Parameter information (parameter names, parameter types, modes)
· Related table information (schema name, table name, type)
· Name of a routine called by this routine, name of the schema to which the called routine belongs, and type of
routine
· Name of a routine that calls this routine, name of the schema to which the called routine belongs, and type of
routine
· Procedure routine definition statement
· Symbol name of the execution module that processes the function routine
· Path name of the execution module that processes the function routine
DSO specification
· DSO name
· Type (table DSO or index DSO)
· Name of database to which DSO belongs
· Related table information (schema name and table name)
· DSO creator
· DSO definition date and time
· Data structure type (SEQUENTIAL, RANDOM, OBJECT, or BTREE, page size for each part, information
[column names] about columns forming the cluster key, existence of NOT UNIQUE specification, storage order
assurance level, page reuse point, and contents of RULE specification)
· Base representation (BY ADDRESS or BY KEY)
· Split condition for subdividing into DSIs
· Index degeneration specification
· Information about columns making up the index (column name, data type)
· DSI information (DSI name)
DSI specification
· DSI name
· Type (table DSI or index DSI)
· Name of database to which DSI belongs
· Related table information (schema name and table name)
· Related DSO information (DSO name)
· Related table DSI name
· Related index DSI name
· DSI creator
· DSI definition date and time
· Split key value
· Page reuse point
· Index degeneration specification
· Database space allocation information (allocation part, database space name, allocation size)
· Alarm point and expansion specification information (expansion point, allocation size, allocation candidate
database space name)
· Name of the scope that limits DSI
SCOPE specification
· Scope name
· Name of database to which the scope belongs
· User ID of the user who defined the scope
· Scope definition date and time
· Name of DSI limited by scope
· User ID of the user to which the scope is applied
DBSPACE specification
· Database space name
· Name of database to be allocated
· Raw device name (for UNIX)
· File name
· Device type
· Database space creator
· Database space definition date and time
· Log group name of log environment used for database space
90
Download from Www.Somanuals.com. All Manuals Search And Download.
· Privilege information (only if -p is specified)
Note:
If a DSI exists for which database space has been allocated, the following information is also printed:
- Information on the allocated DSI (DSI name and type)
- Information on the table related to the DSI (schema name and table name)
- Information on the DSO related to the DSI (DSO name)
- Allocation size
SEQUENCE specification
· Sequence name
· Name of the database to which a sequence belongs
· Name of the schema to which a sequence belongs
· Sequence creator
· Sequence definition date and time
· Sequence number increment interval
· Initial sequence number
· Maximum sequence number
· Minimum sequence number
· Whether to use sequence numbers cyclically
· Number of memory spaces allocated to sequence numbers and number of memory spaces retained with
sequence numbers
· Whether to ensure a sequence between clusters
USER specification
· User name
· User type
· User management type
· Password status
· User creator
· User definition date and time
· Last date and time when the user information was altered (using the ALTER USER statement)
· Last date and time when the user was connected to a database
· Number of times the user failed to connect to a database
· Name and value of each parameter set for the user
ROLE specification
· Role name
· User who defined the role
· Role definition date and time
· Role update date and time
· Resource type
· Database name
· Schema name
· Table name (output if the resource type is TABLE)
· Procedure routine name (output if the resource type is PROCEDURE)
· Function routine name (output if the resource type is FUNCTION)
· Sequence name (output if the resource type is SEQUENCE)
· Trigger name (output if the resource type is TRIGGER)
· Schema name (output if the resource type is SCHEMA)
· Database space name (output if the resource type is DBSPACE)
· Privilege type
· Grantor name
· Grantee names
· Whether the user has the privilege to grant a role
2.14.2 rdbprt command specification method
Figure: Sample database list print specification and Figure: Sample definition information print specification show
sample specifications of the rdbprt command. For more information on specifying the rdbprt command, refer to the
man command (under UNIX) or the SymfoWARE/RDB online manual (under Windows NT).
Sample database list print specification
This example prints a list of all databases in a target SymfoWARE/RDB system.
91
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Sample database list print specification]
Sample user name list print specification
This example specifies printing a list of names of all users under the target SymfoWARE/RDB.
[Figure: Sample user name list print specification]
Sample role name list print specification
This example specifies printing a list of names of all roles under the target SymfoWARE/RDB.
[Figure: Sample role name list print specification]
Sample definition information print specification
If TABLE is specified for the output object and the f option is specified in the rdbprt command, this example prints not
only the table information but also the related DSO and DSI information.
[Figure: Sample definition information print specification]
Sample user parameter information print specification
This example specifies printing a list of user parameter information defined in the target SymfoWARE/RDB.
92
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Sample user parameter information print specification]
2.14.3 rdbprt command print format
Figure: Sample database list print results shows a sample printout for the rdbprt command specified in Figure: Sample
database list print specification. Figure: Sample database output object specification and print results shows a sample
printout for the rdbprt command specified in Figure: Sample definition information print specification. Figure: Definition
information output format for a DB specification to Figure: Definition information output format for a SCOPE
specification show the rdbprt command definition information output formats.
The following figure enable readers to see a print image. However, these figures are not complete figures.
Sample database list print results
[Figure: Sample database list print results]
Sample user name list print results
[Figure: Sample user name list print results]
93
Download from Www.Somanuals.com. All Manuals Search And Download.
Sample role name list print results
[Figure: Sample role name list print results]
Sample user parameter information print results
[Figure: Sample user parameter information print results]
Remarks: DEFAULT_ROLE is not printed in user parameter information.
It is printed in USER specification in definition information.
94
Download from Www.Somanuals.com. All Manuals Search And Download.
Sample database output object specification and print results
[Figure: Sample database output object specification and print results]
95
Download from Www.Somanuals.com. All Manuals Search And Download.
Definition information output format for a DB specification
[Figure: Definition information output format for a DB specification]
96
Download from Www.Somanuals.com. All Manuals Search And Download.
Definition information output format for a SCHEMA specification
[Figure: Definition information output format for a SCHEMA specification]
97
Download from Www.Somanuals.com. All Manuals Search And Download.
Definition information output form at for a TABLE specification
[Figure: Definition information output format for a TABLE specification]
98
Download from Www.Somanuals.com. All Manuals Search And Download.
99
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: View information output format]
100
Download from Www.Somanuals.com. All Manuals Search And Download.
Definition information output format for a ROUTINE specification
[Figure: Definition information output format for a ROUTINE specification]
101
Download from Www.Somanuals.com. All Manuals Search And Download.
Definition information output format for a DSO specification
[Figure: Definition information output format for a DSO specification]
102
Download from Www.Somanuals.com. All Manuals Search And Download.
103
Download from Www.Somanuals.com. All Manuals Search And Download.
Definition information output format for a DSI specification
[Figure: Definition information output format for a DSI specification]
104
Download from Www.Somanuals.com. All Manuals Search And Download.
105
Download from Www.Somanuals.com. All Manuals Search And Download.
Definition information output format for a DBSPACE specification
[Figure: Definition information output format for a DBSPACE specification]
106
Download from Www.Somanuals.com. All Manuals Search And Download.
Definition information output format for a TRIGGER specification
[Figure: Definition information output format for a TRIGGER specification]
107
Download from Www.Somanuals.com. All Manuals Search And Download.
108
Download from Www.Somanuals.com. All Manuals Search And Download.
Definition information output format for a SCOPE specification
[Figure: Definition information output format for a SCOPE specification]
109
Download from Www.Somanuals.com. All Manuals Search And Download.
Definition information output format of a SEQUENCE specification
[Figure: Definition information output format of a SEQUENCE specification]
110
Download from Www.Somanuals.com. All Manuals Search And Download.
Definition information output format of a USER specification
[Figure: Definition information output format of a USER specification]
111
Download from Www.Somanuals.com. All Manuals Search And Download.
Definition information output format of a ROLE specification
[Figure: Definition information output format of a ROLE specification]
112
Download from Www.Somanuals.com. All Manuals Search And Download.
2.14.4 Printing privilege information
To print privilege information, specify -p in the rdbprt command. Privilege information is printed only for the following
specifications:
· SCHEMA
· TABLE
· ROUTINE
· DBSPACE
· TRIGGER
· SEQUENCE
Figure: Sample privilege information printout shows a sample command specification and a sample printout (SCHEMA
specification). For more information on how to specify the rdbprt command, refer to the man command (under UNIX)
or the SymfoWARE/RDB online manual (under Windows NT).
113
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Sample privilege information printout]
114
Download from Www.Somanuals.com. All Manuals Search And Download.
Chapter 3 Database Definition Alteration and
Deletion
A database can be used after it has been created. To use the database, create an application program. For
information about how to use an application program to process a database, refer to the RDB User's Guide:
Application Programs Development.
After a database has been created, the user may need to add data items to the designed database. Alternatively, data
items may become unnecessary.
This chapter explains how to alter and delete a database definition. The explanations are given in the following order:
3.1 Altering a Database Definition
3.2 Deleting a Database
3.1 Altering a Database Definition
The user alters a database definition by performing the following operations.
For more information about the SQL statements described in this chapter, refer to the following
manuals:
· SQL Reference Guide
· SQL Beginner's Guide
Altering the logical structure definition:
· Adding a schema definition (CREATE SCHEMA statement).
· Deleting a schema definition (DROP SCHEMA statement).
· Adding a sequence definition (CREATE SEQUENCE statement)
· Deleting a sequence definition (DROP SEQUENCE statement)
· Adding a table definition (CREATE TABLE statement).
· Deleting a table definition (DROP TABLE statement).
· Altering a table definition (ALTER TABLE statement).
· Adding a view definition (CREATE VIEW statement).
· Deleting a view definition (DROP VIEW statement).
· Adding a trigger definition (CREATE TRIGGER statement).
· Deleting a trigger definition (DROP TRIGGER statement).
· Adding a procedure routine definition (CREATE PROCEDURE statement).
· Deleting a procedure routine definition (DROP PROCEDURE statement).
· Adding a function routine definition (CREATE FUNCTION statement)
· Deleting a function routine definition (DROP FUNCTION statement)
· Altering a comment definition.
· Swapping a table (SWAP TABLE statement).
Altering the storage structure definition:
· Adding a table data structure organization (DSO) definition (CREATE DSO statement).
· Deleting a table DSO definition (DROP DSO statement).
· Adding a table data structure instance (DSI) definition (CREATE DSI statement).
· Deleting a table DSI definition (DROP DSI statement).
· Adding an index DSO definition (CREATE DSO statement).
· Deleting an index DSO definition (DROP DSO statement).
· Altering the split value of a DSI definition (ALTER DSI statement).
· Addinging a scope definition (CREATE SCOPE statement).
· Applying a scope definition (APPLY SCOPE statement).
· Releasing a scope definition (RELEASE SCOPE statement).
· Deleting a scope definition (DROP SCOPE statement).
115
Download from Www.Somanuals.com. All Manuals Search And Download.
Defining optimization information for added definition
If a table and index is added, define the optimization information for them.
· Defining optimization information (SET STATISTICS statement)
Altering privileges
· Adding privilege information (GRANT statement)
· Deleting privilege information (REVOKE statement)
· Adding a role definition (CREATE ROLE statement)
- Adding a privilege to a role (GRANT statement)
- Granting the role privileges to a user (GRANT statement)
· Altering role privilege information (GRANT statement)
· Deleting a role definition (DROP ROLE statement)
· Removing a role privilege (REVOKE statement)
- Deleting a privilege for a table from a role
- Removing the role privileges from a user
Note that when a database definition is deleted or altered, the definition information is deleted or altered. Moreover,
the data itself (table or index) is deleted or altered at the same time. If the deletion or alteration of a database
definition is specified by mistake, important data may also be deleted. Always use care when deleting or altering a
database definition.
The following database is used in the examples in this section:
Database:
STOCKMN_DB
Schema:
STOCKS
Sequence:
SEQUENCE1
Tables:
STOCK and ORDER
View:
MASS_STOCK
Table DSOs:
STOCK_DSO and ORDER_DSO
Table DSIs:
STOCK_DSI and WEST_ORDER_DSI
Index DSO:
PRODUCT_IXDSO
Index DSI:
PRODUCT_IXDSI
Because of dependencies between definitions, the user must follow an alteration sequence that conforms to those
dependencies when altering a database definition. For information about basic alteration sequences, see Appendix B
"Sequential Relationships Among Definition Changes."
3.1.1 Altering a logical structure defining
Adding a schema definition (CREATE SCHEMA statement)
A new schema definition can be added to an existing database definition. To add a schema definition, specify the
CREATE SCHEMA statement in a similar manner as when defining a schema. For details about how to specify the
CREATE SCHEMA statement, see 2.6 "Defining a Logical Structure."
Example:
Adding a schema belonging to STOCKMN_DB.
116
Download from Www.Somanuals.com. All Manuals Search And Download.
Deleting a schema definition (DROP SCHEMA statement)
To delete a schema definition, use the DROP SCHEMA statement. A specification example follows.
Example:
Delete a schema belonging to STOCKMN_DB.
When an attempt is made to delete a schema definition, if any of the following definitions subordinate to that schema
exist, the schema cannot be deleted. The user must delete these definitions before deleting the schema definition:
· Sequence
· Table
· View
· Temporary table
· Trigger
· Index
· Storage structure
- Table DSO
- Index DSO
- Table DSI
- Index DSI
· Procedure
· Function routine
However, if the user specifies CASCADE, then even if the preceding definitions exist, all related definitions are
unconditionally deleted.
Adding a sequence definition (CREATE SEQUENCE statement)
To add a sequence definition to a schema, use the CREATE SEQUENCE statement. A specification example follows.
For details on how to specify the CREATE SEQUENCE statement, see 2.6 "Defining a Logical Structure."
Example:
Adds a definition of SEQUENCE2 to schema STOCKS
Deleting a sequence definition (DROP SEQUENCE statement)
To delete a sequence definition, use the DROP SEQUENCE statement. If a base table, view table, procedure routine,
function routine, or trigger references a sequence, the sequence definition cannot be deleted. Delete the definition of
the base table, view table, procedure routine, function routine, or trigger that references the sequence before deleting
the sequence definition. A specification example follows.
Example:
Deletes SEQUENCE1.
117
Download from Www.Somanuals.com. All Manuals Search And Download.
Adding a table definition (CREATE TABLE statement)
To add a table definition to a schema, use the CREATE TABLE statement. A specification example follows. For details
about how to specify the CREATE TABLE statement, see 2.6 "Defining a Logical Structure."
Example:
Add a definition of the PRODUCT table to the schema named STOCKS.
Deleting a table definition (DROP TABLE statement)
To delete a table definition, use the DROP TABLE statement. If a view table, procedure routine, function routine, or
trigger references a base table, the table definition cannot be deleted. Delete the definition of the view table,
procedure routine, function routine, or trigger that references the base table before deleting the table definition. To
delete the view, procedure routine, function routine, or trigger definition that references a base table together with the
table definition, specify CASCADE. Similarly, the CASCADE specification is required to delete any storage structure
definition of a base table together with the table definition.
When a table definition is deleted by the DROP TABLE statement, the base table database data is also deleted at the
same time. If another table name is specified by mistake in the table definition, the definition information will be lost.
Moreover, the important data will be lost as well. Be especially careful when using the DROP TABLE statement.
An example of deleting a table definition follows.
Example:
Delete the STOCK table definition information and STOCK table.
Altering a table definition (ALTER TABLE statement)
To alter a table definition, use the ALTER TABLE statement. The ALTER TABLE statement can be used to make the
following changes:
· Add a column definition.
· Delete a column definition.
Adding a column definition
To add a column to a base table, specify addition of a column definition in the ALTER TABLE statement. Only one
column can be added at the end of the existing columns per table definition alter statement. For the SEQUENTIAL
118
Download from Www.Somanuals.com. All Manuals Search And Download.
structure, data corresponding to one row in a table may exceed one page after a BLOB-type column is added.
Only NOT NULL can be specified as a constraint for the column to be added. If NOT NULL is specified, the DEFAULT
clause must be specified in the column definition.
DEFAULT values are set for existing data.
A specification example for adding a column definition follows.
Example:
Add a PRICE_SOLD column to the ORDER table. Figure: Adding a column to a table shows the result.
[Figure: Adding a column to a table]
Deleting a column definition
To delete a column from a base table, specify the deletion of a column definition in the ALTER TABLE statement. Only
one column can be deleted by one table alteration statement. A column cannot be deleted if it is referenced by a view
definition. If an index DSO definition for the column exists, the column cannot be deleted. The index DSO definition
must be deleted first. However, if a unique constraint for the column exists, the column cannot be deleted even if the
index DSO definition is deleted. The number of columns in a table cannot be reduced to zero by deleting a column
definition. To delete all columns, use the DROP TABLE definition. A specification example for deleting a column
definition follows.
Example:
Delete the PRICE column from the ORDER table. Figure: Deleting a column from a table shows the
result.
119
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Deleting a column from a table]
Adding a view definition (CREATE VIEW statement)
To add a view definition to a previously defined schema, use a CREATE VIEW statement. A view definition cannot be
altered. To change a view definition, first delete the view definition and then add a new view definition. An example of
adding a view definition follows. For details about how to specify the CREATE VIEW statement, see "2.6 Defining a
Local Structure."
Example:
Add a PHONE_LIST view to the schema named STOCKS.
Deleting a view definition (DROP VIEW statement)
To delete a view definition from a previously defined schema, use the DROP VIEW statement. If a view table to be
deleted is referenced in another view table, a procedure routine, a function routine, or a trigger definition, the view
definition cannot be deleted. Delete the view table, procedure routine, function routine, or trigger definition that
references the view table to be deleted before deleting the view definition. To delete the view table, procedure routine,
function routine, or trigger definition that references a view table together with the view table definition, specify
CASCADE.
An example of deleting a view definition follows.
Example:
Delete the definition of the view named STOCK_VIEW.
Adding a trigger definition (CREATE TRIGGER statement)
To add a trigger definition, use the CREATE TRIGGER statement. For more information on how to specify the
CREATE TRIGGER statement, see 2.6 "Defining a Logical Structure."
Example:
Define the trigger ORDER_TRIGGER.
120
Download from Www.Somanuals.com. All Manuals Search And Download.
Deleting a trigger definition (DROP TRIGGER statement)
To delete a trigger definition, use the DROP TRIGGER statement.
Example:
Delete trigger ORDER_TRIGGER.
Adding a procedure routine definition (CREATE PROCEDURE statement)
To add a procedure routine, use the CREATE PROCEDURE statement. For details about how to specify the CREATE
PROCEDURE statement, see 2.6 "Defining a Logical Structure."
Example:
Add PROC002 to the STOCKS schema.
Deleting a procedure routine definition (DROP PROCEDURE statement)
To delete a procedure routine, use the DROP PROCEDURE statement. If the schema has another procedure routine
that calls the procedure routine to be deleted, the procedure routine cannot be deleted. To delete all related procedure
routines, specify CASCADE. For details about how to specify the DROP PROCEDURE statement, see 2.6 "Defining a
Logical Structure."
Example:
Delete PROC002 from the STOCKS schema.
Adding a function routine definition (CREATE FUNCTION statement)
To add a function routine, use a function routine definition statement. For details on how to specify the function routine
definition statement, see 2.6 "Defining a Logical Structure."
121
Download from Www.Somanuals.com. All Manuals Search And Download.
Example:
Defines function routine USER002 to schema STOCKS.
Deleting a function routine definition (DROP FUNCTION statement)
To delete a function routine, use a function routine deletion statement. If a function routine to be deleted is specified in
an SQL statement related to a procedure routine or trigger, the function routine cannot be deleted. However,
specifying CASCADE deletes all related definitions.
Example:
Deletes USER001 from schema STOCKS.
Changing a comment definition
Comment definitions for tables and columns can be changed with the ALTER TABLE statement. An example follows.
Example:
Change the table comment definition.
Example:
Change the column comment definition.
122
Download from Www.Somanuals.com. All Manuals Search And Download.
Swapping a table (SWAP TABLE statement)
The table name exchange in table swapping exchanges the table's relationship with DSO. Use the SWAP TABLE
statement for table swapping. This produces the following effects:
· Data can be transfered in a short time.
· An application program need not be changed.
· Previous views and routines can be used without changing their definitions.
An example of table swapping follows.
Example:
This example shows table swapping when the data for each day is shifted to the day before in a
system managing data by days. For a new day, data is initialized.
3.1.2 Changing a definition of a storage structure
Adding a table DSO definition (CREATE DSO statement)
To add a table DSO definition after adding the table definition, use the CREATE DSO statement. An example of adding
a table DSO definition follows. For details about how to specify the CREATE DSO statement, see 2.7 "Defining a
Storage Structure."
Example:
Add STOCK_DSO as the DSO of the STOCK table.
123
Download from Www.Somanuals.com. All Manuals Search And Download.
Deleting a table DSO definition (DROP DSO statement)
To delete a table DSO definition from a base table, use the DROP DSO statement. If an index DSO or a table DSI has
been defined for the DSO to be deleted, the relevant DSO definition cannot be deleted. All related index DSO
definitions or table DSI definitions must be deleted in advance.
However, if the user specifies CASCADE, all DSIs related to the DSO to be deleted are deleted.
An example of deleting a table DSO definition follows.
Example:
Delete all table DSOs belonging to STOCKMN_DB.
Adding an index DSO definition (CREATE DSO statement)
To add an index DSO definition for a base table for which the table DSO has already been defined, use the CREATE
DSO statement. An example of adding an index DSO definition follows. For details about how to specify the CREATE
DSO statement, see 2.7 "Defining a Storage Structure."
Example:
Add STOCKNO_IXDSO as the index DSO for the WHCODE column of the STOCK table.
Deleting an index DSO definition (DROP DSO statement)
To delete an index DSO definition from a base table, use the DROP DSO statement.
If an index DSI has been defined, the relevant index DSO definition cannot be deleted. All related index DSI definitions
must be deleted in advance.
However, when CASCADE is specified, if the DSO to be deleted is for a base table, all related index DSOs and
related DSIs are deleted.
An example of deleting an index DSO definition follows.
Example:
Delete all index DSOs belonging to STOCKMN_DB.
124
Download from Www.Somanuals.com. All Manuals Search And Download.
Adding a table DSI definition (CREATE DSI statement)
To add a table DSI definition for a base table for which the DSO has been defined, use the CREATE DSI statement.
Specify each item of the CREATE DSI statement in a similar manner as when defining the storage structure.
An example of adding a table DSI definition follows. For details about how to specify the CREATE DSI statement, see
2.7 "Defining a Storage Structure".
Example:
Add EAST_ORDER_DSI to STOCKMN_DB. EAST_ORDER_DSI is subordinate to ORDER_DSO and
is defined on the database space named DBSP_3.
Deleting a table DSI definition (DROP DSI statement)
To delete a DSI definition from a base table, use the DROP DSI statement. If the DSI to be deleted is a table DSI, and
an index DSI related to it exists, the relevant DSI definition cannot be deleted. All DSI definitions of related index DSIs
must be deleted in advance.
However, when CASCADE is specified, if the DSI to be deleted is a table DSI, all related index DSIs are deleted.
An example of deleting a DSI definition follows.
Example:
Delete all DSIs belonging to STOCKMN_DB.
125
Download from Www.Somanuals.com. All Manuals Search And Download.
Adding an index DSI definition (CREATE DSI statement)
To add an index DSI definition, use the CREATE DSI statement.
Changing a split key value of a DSI definition (ALTER DSI statement)
To change a split key value of a table DSI definition, use the ALTER DSI statement. If the table storage structure is
SEQUENTIAL or RANDOM, the split key value can be changed. An example of changing a split key value follows.
Integrating DSIs
An example of integrating Hyogo data into Osaka data follows.
126
Download from Www.Somanuals.com. All Manuals Search And Download.
Changing the DSI storage range
An example follows in which the number of years of storage is changed for sales data having a three-year storage
period.
127
Download from Www.Somanuals.com. All Manuals Search And Download.
Adding a scope definition (CREATE SCOPE statement)
To add a scope definition, use the CREATE SCOPE statement. An example of applying a scope definition follows. For
more information on how to specify the CREATE SCOPE statement, see 2.7.7 "Scope definition."
Example:
Add scope OSAKA_SCOPE.
Applying a scope definition (APPLY SCOPE statement)
To apply an added scope definition, use the APPLY SCOPE statement. An example of applying a scope definition
follows. For more information on how to specify the APPLY SCOPE statement, see 2.8.1 "Scope definition
application."
Example:
Apply scope OSAKA_SCOPE to user SUZUKI.
128
Download from Www.Somanuals.com. All Manuals Search And Download.
Releasing a scope definition (RELEASE SCOPE statement)
To release a scope definition, use the RELEASE SCOPE statement. This statement releases a scope definition that
was applied to a user by the APPLY SCOPE statement.
The statements "RELEASE SCOPE" and "APPLY SCOPE" must be executed by the same user.
An example of using the RELEASE SCOPE statement follows.
Example:
Release the scope "OSAKA_SCOPE" that is currently applied to user "SUZUKI."
Deleting a user scope (DROP SCOPE statement)
To delete a scope definition, use the DROP SCOPE statement. This statement deletes a scope definition that was
applied to a user by the APPLY SCOPE statement.
The statements "DROP SCOPE" and "CREATE SCOPE" must be executed by the same user.
An example of using the DROP SCOPE statement follows.
Example:
Delete scope TOKYO_SCOPE.
3.1.3 Defining optimization information for added definitions (SET STATISTICS
statement)
If a table and index are added, use the SET STATISTSICS statement to define optimization information for them. An
example of the SET STATISTICS statement follows.
Example:
Define optimization information about an added TOKAI_ORDER _DSI.
129
Download from Www.Somanuals.com. All Manuals Search And Download.
Precaution when altering a database definition
After a database definition is altered, use the rdbprdic command to confirm the RDB dictionary utilization rate and
estimate the expansion point. This precaution ensures sufficient RDB dictionary space.
For information about estimating the RDB dictionary expansion point, refer to "RDB Operations Guide."
3.1.4 Altering privilege information
Adding privilege information (GRANT statement)
To add privilege information, use the GRANT statement.
Example:
Adds the deletion privilege.
Deleting privilege information (REVOKE statement)
To delete privilege information, use the REVOKE statement.
Example:
Deletes deletion privilege.
If a privilege is deleted with CASCADE specified, the base table, view table, temporary table, procedure routine, and
trigger defined by the grantees using the privilege are deleted. The following example assumes that SUZUKI created
the PHONE view table using the COMPANY table.
When the SELECT privilege granted to SUZUKI for the COMPANY table is removed, the PHONE view table created
by SUZUKI is deleted.
Adding a role definition (CREATE ROLE statement)
To add a role definition, use the CREATE ROLE statement. To specify a privilege to be granted for a table in a role and
grant this role privilege to a user, use the GRANT statement.
To add a role, follow the procedure given below.
1. Define a role using the CREATE ROLE statement.
2. Specify the privileges to be granted in the role by using the GRANT statement.
3. Grant the role privileges to a user by using the GRANT statement.
130
Download from Www.Somanuals.com. All Manuals Search And Download.
Example:
Adds role STOCKS_A2.
Specify the privileges to be granted in role STOCKS_A2.
Grant the privileges of role STOCKS_A2 to users.
Altering role privilege information (GRANT statement)
To alter the role privileges for a table, use the GRANT statement.
Example:
Adds privileges for the STOCK table to role STOCKS_A2.
Deleting a role definition (DROP ROLE statement)
To delete a role definition, use the DROP ROLE statement. When a role is deleted, the privileges to be granted as
defined with the GRANT statement in the role are also deleted, and the role privileges granted to users are removed.
Example:
Deletes role STOCKS_A2.
Deleting/removing role privileges (REVOKE statement)
To delete a privilege from a role or remove the role privileges from a user, use the REVOKE statement.
Example 1:
Deletes the SELECT privilege for the STOCK table from role STOCKS_A2.
131
Download from Www.Somanuals.com. All Manuals Search And Download.
Example 2:
Removes the role privileges from user TANAKA.
3.2 Deleting a Database
The user deletes a database definition by performing a sequence of operations. Figure: Database deletion procedure
shows this procedure.
132
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Database deletion procedure]
The following database is used in the examples in this section:
Database:
STOCKMN_DB
Schema:
STOCKS
Sequence:
SEQUENCE1
133
Download from Www.Somanuals.com. All Manuals Search And Download.
Tables:
STOCK, ORDER, and STOCK_TEMPORARY TABLE
Table DSO:
ORDER_DSO
Table DSIs:
STOCK_DSI and WEST_ORDER_DSI
Index:
STOCK_TEMPORARY_IX
Index DSO:
PRODUCT_IXDSO
Index DSI:
PRODUCT_IXDSI
Database spaces:
DBSPACE_1, DBSPACE_2
Procedure routine:
PROC002
Function routine:
USER001
Trigger:
ORDER_TRIGGER
Scope:
TOKYO_SCOPE
Deleting temporary tables
To delete a temporary table, use the DROP TABLE statement.
Example:
Deletes temporary table STOCK_TEMPORARY.
Deleting temporary table indexes
To delete the index of a temporary table, use the DROP INDEX statement.
Example:
Deletes temporary table index STOCK_TEMPORARY_IX.
Deleting scopes
To delete a scope, use the DROP SCOPE statement.
Example:
Delete scope TOKYO_SCOPE.
134
Download from Www.Somanuals.com. All Manuals Search And Download.
Deleting index storage structures
To delete index storage structures, use the DROP DSI and DROP DSO statements.
Example:
Delete the index DSI and index DSO belonging the STOCKMN_DB.
When the DSI specified in the DROP DSI statement is deleted, all data stored in the database space allocated to that
DSI becomes invalid.
When the index storage structure is defined by an index definition, use the following method to delete it.
Example:
Delete the index belonging to STOCKMN_DB.
The index specified in the DROP INDEX statement is deleted. At this time, the index DSI and index DSO are deleted.
Deleting table storage structures
To delete table storage structures, use the DROP DSI and DROP DSO statements.
Example:
Delete the table DSIs and table DSO belonging to STOCKMN_DB.
The table DSIs specified in the DROP DSI statements and the table DSO specified in the DROP DSO statement are
deleted.
135
Download from Www.Somanuals.com. All Manuals Search And Download.
Deleting procedure routines
To delete a procedure routine, use the DROP PROCEDURE statement. When a procedure routine is deleted, the
privilege information for the procedure is also deleted.
Example:
Delete PROC002 from the STOCKS schema.
Deleting function routine definitions
To delete a function routine, use the DROP FUNCTION statement. When a function routine is deleted, privilege
information of the function routine is also deleted.
Example:
Deletes USER001 from schema STOCKS.
Deleting triggers
To delete a trigger, use the DROP TRIGGER statement.
Example:
Delete the trigger belonging to the STOCKMN_DB.
Deleting views
To delete a view, use the DROP VIEW statement.
Example:
Delete the view belonging to STOCKMN_DB.
The view specified in the DROP VIEW statement is deleted.
Deleting tables
To delete a table, use the DROP TABLE statement. When a table is deleted, the privilege information for the table is
also deleted.
136
Download from Www.Somanuals.com. All Manuals Search And Download.
Example:
Delete the tables belonging to STOCKMN_DB.
The tables specified in the DROP TABLE statements are deleted.
Deleting sequences
To delete a sequence, use the DROP SEQUENCE statement. When a sequence is deleted, privilege information of
the sequence is also deleted.
Example:
Deletes a sequence belonging to the stock management database.
Deleting schemas
To delete a schema, use the DROP SCHEMA statement. When a schema is deleted, the privilege information for the
schema is also deleted.
Example:
Delete the schema belonging to STOCKMN_DB.
The schema specified in the DROP SCHEMA statement is deleted.
Deleting database spaces
To delete a database space, use the DROP DBSPACE statement. When a database space is deleted, the privilege
information for the database space is also deleted.
Example:
Delete the database spaces belonging to STOCKMN_DB.
137
Download from Www.Somanuals.com. All Manuals Search And Download.
Deleting a database
To delete the database name, use the DROP DATABASE statement.
Example:
Delete STOCKMN_DB.
138
Download from Www.Somanuals.com. All Manuals Search And Download.
Chapter 4 Storage Structure
From the application program viewpoint, database data is represented in table format. The application program
manipulates data as if it were manipulating rows and columns of a table by using structured query language (SQL)
statements.
The structure for storing data represented in table format on physical pages is called the storage structure. The
storage structure cannot be directly seen from the application program. Regardless of the storage structure used, from
the application program viewpoint, the table rows and columns appear as if they are being manipulated according to
SQL statements.
However, since the physical data is stored according to the storage structure, the storage structure is an important
factor in determining processing efficiency.
Ignoring the interrelationships between transactions, such as exclusion, and focusing on storage structure, one can
see the following elements affecting the data processing efficiency of an application program:
· Addition of an index
Add an index for a table.
· Allocation of database space
Carefully consider the amount of data to be processed and area usage patterns. Allocate database space for
each component of the storage structure.
· Association with the shared buffer pool
Select a page size and shared buffer pool appropriate to the data processing.
This chapter explains the features of storage structures. The explanation covers the following topics:
4.1 Features of Table Storage Structures
4.2 Features of the Index Storage Structure
4.3 Allocating Space
4.4 Estimating the Required Amount of Database Space
4.1 Features of Table Storage Structures
Specify the storage structure according to a data structure organization (DSO) definition. The three types of storage
structures are SEQUENTIAL, RANDOM, OBJECT, and BTREE. The SEQUENTIAL, RANDOM, and OBJECT
structures are used as storage structures for tables. The BTREE structure is used as the storage structure for indexes.
This section explains the features of the table storage structures and the data processing appropriate to those
structures.
4.1.1 SEQUENTIAL structure
Data is stored in a SEQUENTIAL structure in the order that the data is inserted.
Figure: Overview of SEQUENTIAL structure shows an overview of the SEQUENTIAL structure, using the STOCK
table as an example.
139
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Overview of SEQUENTIAL structure]
SEQUENTIAL structure features for data processing patterns
The factor having the greatest effect on data processing efficiency is the I-O frequency. The SEQUENTIAL structure
has the following features:
· All data is referenced for data processing unless an index exists. Thus, the I-O frequency depends greatly on
the data volume. When data manipulations specify a column, the user must add an index corresponding to that
column.
For information about items to carefully consider when adding an index, see 4.2.1 "BTREE structure."
SEQUENTIAL structure page size specification
In a SEQUENTIAL structure, the page size is specified by the PAGESIZE option of the DSO definition.
140
Download from Www.Somanuals.com. All Manuals Search And Download.
When specifying the page size, carefully consider the following point:
· If the table does not contain a BLOB-type column, a row of data in the table must fit within in one page.
· If a table including a BLOB-type column is defined or a BLOB-type column is added during the change of a
table definition, a row of data in the table may exceed one page. However, the total size of the data of the
columns other than the BLOB-type column must not exceed one page.
When an index must be added to a SEQUENTIAL structure
The user must add an index for a column of the corresponding table that has a unique constraint. If no index is added
for such a column, the table cannot be accessed.
4.1.2 RANDOM structure
In a RANDOM structure, collections of storage pages (called buckets) are calculated from the values of the group of
columns defined as a key for the data. The data is stored in pages within those buckets. If the data cannot fit in a
bucket, the SymfoWARE/RDB system automatically creates an overflow part bucket and stores the data in that bucket.
The collection of pages that belong to the original bucket are called the prime part for the overflow part.
A group of columns that determines the page for storing data is called a cluster key. A cluster key is determined by the
CLUSTER option of the table DSO definition. If the CLUSTER option is omitted, the cluster key becomes the primary
key of the corresponding table definition. In a RANDOM structure, data having an equal cluster key is stored in the
same packet.
The hash function is used in calculations to determine a bucket from the cluster key value. From the hash function,
SymfoWARE/RDB automatically determines a bucket for storing data. If RULE is specified in the storage option of the
table DSO definition statement, the data storing bucket is determined from the result of calculating the formula
specified at RULE.
Figure: Overview of RANDOM structure shows an overview of the RANDOM structure, using the STOCK table as an
example.
141
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Overview of RANDOM structure]
RANDOM structure features for data processing patterns
The factor having the greatest effect on data processing efficiency is the I-O frequency. The RANDOM structure has
the following features:
· If all values of the cluster key are specified in conditional expressions and a unique constraint is set as the
cluster key in data processing, the I/O frequency hardly depends on the data volume.
· In other cases, since all data is referenced, the I-O frequency depends greatly on the data volume. In these
142
Download from Www.Somanuals.com. All Manuals Search And Download.
cases, the entire cluster key is not specified in the data processing.
RANDOM structure page size specification
In a RANDOM structure, a prime part bucket is associated with a single page. An overflow part bucket is also
independently associated with a single page. The size of each of these pages can be independently specified. The
prime part page size is specified by the PAGESIZE1 option of the DSO definition. The overflow part page size is
specified by the PAGESIZE2 option.
When specifying the page size, carefully consider the following points:
· Data corresponding to a single row of the table must fit in one page.
· When setting the prime part page size, carefully consider the average I-O frequency (which depends on the
number of data entries within the page).
When an index must be added to a RANDOM structure
The user must add an index for a column of the corresponding table that has a unique constraint but is not specified
as the cluster key. If no index is added for such a column, the table cannot be accessed.
RANDOM structure data storage position specification
A rule of determining a data storage packet can be specified by setting RULE to the storage option of the table DSO
definition statement. When specifying RULE, note the following:
· A RULE formula resulting in a negative value cannot be specified.
· A RULE formula should be designed by considering the cluster key value to be stored in the table and the
database I/O count and storage efficiency from the point of view of work.
A data-storing packet is determined from the remainder of dividing the RULE formula calculation result by the number
of packets. Figure: Overview of data storage method shows an outline of the data storage method.
[Figure: Overview of data storage method]
The example below shows the table DSO definition when RULE is specified.
Example:
When cluster keys are generated in ascending order, this definition enhances the storage efficiency
143
Download from Www.Somanuals.com. All Manuals Search And Download.
and prevents data storage in the overflow part. The record size of the PROD_INF table is set to 200
bytes. By considering the page size (PAGESIZE1) of the prime part, design the RULE formula to store
four data items on each page.
144
Download from Www.Somanuals.com. All Manuals Search And Download.
4.1.3 OBJECT structure
An OBJECT structure stores BLOB-type data such as photograph in the order that the data is inserted.
Figure: Overview of OBJECT structure shows an overview of the OBJECT structure, using the PRODPHOT table as
an example.
145
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Overview of OBJECT structure]
OBJECT structure page size specification
In an OBJECT structure, the page size is specified by the PAGESIZE option of the DSO definition. The specified page
size must be 32.
4.2 Features of the Index Storage Structure
The index storage structure is the BTREE structure only. This section explains the features of the index storage
structure and the data processing appropriate to that structure.
4.2.1 BTREE structure
BTREE structure is the storage structure used for indexes. Internally, the BTREE structure consists of a tree structure
index part and a data part. The index part, which consists of groups of correspondence information values for index
146
Download from Www.Somanuals.com. All Manuals Search And Download.
keys and base table data, manages the pages where data is stored. The portion consisting of pages in which data is
stored is called the data part for the index part.
If ADDRESS is specified as the base expression for the DSO definition, the table record storage address is used as
information corresponding to the base table data. If KEY is specified, the cluster key is used.
If data cannot fit in a given page of the data part, the SymfoWARE/RDB system automatically creates a new page.
The SymfoWARE/RDB system then divides the data between the new page and the original page for storage (called
page splitting). Index part page splitting may be performed together with data part page splitting.
If the index key is not a unique key, multiple base table data correspondence information values may exist for a single
index key value. This multiplicity (number of base table data correspondence information values), which also depends
on the index key values, is not fixed. Therefore, the BTREE structure groups index keys and base table data
correspondence information for management. Ascending order is guaranteed for these key groups. Also, to improve
storage efficiency, the system performs front compression for the index key portion.
Figure: Overview of BTREE structure shows an overview of the BTREE structure, using the STOCK table as an
example. In this example, an index is assumed to be defined for the WHCODE column, and the table storage
structure is assumed to be a SEQUENTIAL structure.
147
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Overview of BTREE structure]
148
Download from Www.Somanuals.com. All Manuals Search And Download.
BTREE structure features for data processing patterns
The factor having the greatest effect on data processing efficiency is the I-O frequency. The BTREE structure has the
following features:
· In the following cases, the I-O frequency depends on the depth of the index part. The range of index key
values is specified, or only the values of the leftmost column of an index key consisting of a group of columns
is specified in the data processing.
· Otherwise, the relevant index is not used.
BTREE structure page size specification
In a BTREE structure, the page size can be independently specified for the index part and the data part. The data part
page size is specified by the PAGESIZE1 option of the DSO definition. The index part page size is specified by the
PAGESIZE2 option.
When specifying the page size, carefully consider the following points:
· The group of correspondence information values for index keys and base table data must fit as two records.
These records must be in a single page of the index part and data part.
· If the amount of data that fits in the index part and data part is small, the page utilization rate (data storage
rate) decreases. To increase the page utilization rate, specify a page size having a surplus area.
· To fit a maximum number of data entries in a single page of the data part, carefully consider and set the page
splitting points. Frequent page splitting decreases the processing efficiency.
Considerations when adding indexes
The user can add multiple indexes to a single base table. By adding indexes, the user increases the processing
efficiency for searches. However, in the following cases, since processing for upgrading all related indexes is
performed, overall processing efficiency may decrease:
· Application program processing updates values of columns for which indexes have been added or base table
data correspondence information.
· Application program processing primarily performs insertions and deletions.
4.3 Allocating Space
This section gives details on the following topics related to the allocation of database space for DSIs:
· Formats for associating storage structures and database spaces
· Considerations when allocating space
4.3.1 Formats for associating storage structures and database spaces
Each type of storage structure consists of several components. When database space is allocated to storage
149
Download from Www.Somanuals.com. All Manuals Search And Download.
structures, each of these components is associated with a database space. The user can use one of the following
formats to make these associations:
Format 1
This format associates multiple components with the same database space, as shown in Figure: Database space
sharing among components. This format reduces the number of database spaces to be used. However, multiple DSIs
can be associated with the database space. When the DSIs are used at the same time, accesses to the database
space can be concentrated.
[Figure: Database space sharing among components]
Format 2
This format associates one component with the multi-database space, as shown in Figure: Multi-database space
components. This format enables a massive database to be built. However, the number of database spaces to be
used increases.
150
Download from Www.Somanuals.com. All Manuals Search And Download.
[Figure: Multi-database space components]
Format 3
This format combines the two previous formats, as shown in Figure: Mixed database space configuration. In this case,
infrequently used overflow parts are concentrated in a single database space.
[Figure: Mixed database space configuration]
4.3.2 Considerations when allocating space
In a storage structure where one DSO is divided into multiple DSIs, the user can maintain individual DSIs in parallel by
allocating database spaces on different hard disks for individual DSIs or DSI groups.
Also, if the number of hard disk units and the capacity permits, consider the following points. Take into account the
transitions required for reaccessing storage structure components:
151
Download from Www.Somanuals.com. All Manuals Search And Download.
· For a RANDOM structure
Allocate the prime and overflow parts in database spaces on separate hard disks.
· For a BTREE structure
Allocate the index and data parts in database spaces on separate hard disks.
4.4 Estimating the Required Amount of Database Space
This section explains how to estimate the required amount of database space for each type of storage structure.
Criteria are presented later for determining values used in the formulas, such as utilization rates. However, since
these values differ depending on the data, ranges of values are given. The user should make space estimates with
some surplus built in. (That is, multiply the estimates by a safety factor.)
152
Download from Www.Somanuals.com. All Manuals Search And Download.
SEQUENTIAL structure
153
Download from Www.Somanuals.com. All Manuals Search And Download.
RANDOM structure (when a unique constraint is set for the cluster key)
154
Download from Www.Somanuals.com. All Manuals Search And Download.
RANDOM structure (when a unique constraint is not set for the cluster key)
155
Download from Www.Somanuals.com. All Manuals Search And Download.
OBJECT structure
156
Download from Www.Somanuals.com. All Manuals Search And Download.
BTREE structure data part
157
Download from Www.Somanuals.com. All Manuals Search And Download.
158
Download from Www.Somanuals.com. All Manuals Search And Download.
BTREE structure index part
159
Download from Www.Somanuals.com. All Manuals Search And Download.
Utilization rate criteria
The utilization rates (average utilization rate of each page) of the prime and overflow parts of a RANDOM structure
vary depending on the following factors. These factors are the data key values and the order of data additions and
160
Download from Www.Somanuals.com. All Manuals Search And Download.
deletions. Similarly, the utilization rates vary depending on these factors for the data and index parts of a BTREE
structure and the overflow rate of a RANDOM structure. (The overflow rate of a RANDOM structure is the rate of
overflow to the overflow part relative to the total number of records.) When estimating the amounts of space needed,
use the following kinds of criteria for the various values in the formulas.
If the number of records in a page is small, the various utilization rates are lower, and the overflow rate is higher.
Key compression rate criteria
The key is compressed and stored in the BTREE structure data part and index part. Use the following kinds of criteria
for the key compression rates in the formulas:
Example of estimating required space for each storage structure
Example 1:
Estimate of the required amount of space for a SEQUENTIAL structure
Calculate the required amount of space for the ORDER table (total number of records: 30,000) having
this kind of structure:
Storage structure:
SEQUENTIAL structure
Page size:
32 kilobytes
Safety factor:
1.3
161
Download from Www.Somanuals.com. All Manuals Search And Download.
Example 2:
Estimate of the required amount of space for a RANDOM structure
Calculate the required amount of space for the ORDER table (total number of records: 30,000) having
this kind of structure:
The storage structure is designed as follows. The cluster key is the PRIMARY KEY (a unique constraint
is set). Thus, the formulas for a RANDOM structure (when a unique constraint is set for the cluster key)
are used.
Storage structure:
RANDOM structure with CUSTOMER and PRODNO as the cluster key
Page size:
4 kilobytes for both the prime part and the overflow part
Prime part utilization rate:
0.5
Overflow part utilization rate:
0.2
Overflow rate:
0.2
Safety factor:
1.3
162
Download from Www.Somanuals.com. All Manuals Search And Download.
Example 3:
An example of a space size estimate for an OBJECT structure.
Calculate the required space size for the PRODPHOT table (total number of records: 3000), which is
the following structure:
Storage structure:
OBJECT structure
Page size:
32 kilobytes
Safety factor:
1.3
163
Download from Www.Somanuals.com. All Manuals Search And Download.
Example 4:
Estimate of the required amount of space for a BTREE structure
Calculate the required amount of space when an index is added for the ORDER table having the
following kind of structure. PRICE and ORDERQTY is used as the secondary key for the index. The
ORDER table has a SEQUENTIAL structure, with a total of 30,000 records. The table structure is as
follows:
Storage structure:
BTREE structure with PRICE and ORDERQTY as a secondary key
Page size (data part):
16 kilobytes
Page size (index part):
2 kilobytes
Utilization rate (data part):
0.5
Utilization rate (index part):
0.5
Compression rate (data part):
0.3
Compression rate (index part):
0.5
164
Download from Www.Somanuals.com. All Manuals Search And Download.
165
Download from Www.Somanuals.com. All Manuals Search And Download.
166
Download from Www.Somanuals.com. All Manuals Search And Download.
Estimating column length
167
Download from Www.Somanuals.com. All Manuals Search And Download.
168
Download from Www.Somanuals.com. All Manuals Search And Download.
Appendix A Quantitative Restrictions
Table: Quantitative limitations shows quantitative restrictions on SymfoWARE/RDB.
[Table: Quantitative limitations]
169
Download from Www.Somanuals.com. All Manuals Search And Download.
170
Download from Www.Somanuals.com. All Manuals Search And Download.
171
Download from Www.Somanuals.com. All Manuals Search And Download.
172
Download from Www.Somanuals.com. All Manuals Search And Download.
Appendix B Sequential Relationships among
Definition Changes
Definition changes must follow an order determined by definition dependencies. Table: Basic sequential relationships
among addition-type definition changes (1/2) shows basic sequential relationships among addition-type changes.
Table: Basic sequential relationships among addition-type definition changes (2/2) shows basic sequential
relationships among deletion-type changes.
173
Download from Www.Somanuals.com. All Manuals Search And Download.
[Table: Basic sequential relationships among addition-type definition changes (1/2)]
174
Download from Www.Somanuals.com. All Manuals Search And Download.
[Table: Basic sequential relationships among addition-type definition changes (2/2)]
175
Download from Www.Somanuals.com. All Manuals Search And Download.
176
Download from Www.Somanuals.com. All Manuals Search And Download.
Appendix C Operating Environment File
Parameters
This appendix lists parameters that can be specified in operating environment files and the files in which the
parameters can be specified. Table: Operating environment file parameters shows the operating environment file
parameters.
[Table: Operating environment file parameters]
177
Download from Www.Somanuals.com. All Manuals Search And Download.
178
Download from Www.Somanuals.com. All Manuals Search And Download.
179
Download from Www.Somanuals.com. All Manuals Search And Download.
180
Download from Www.Somanuals.com. All Manuals Search And Download.
Appendix D Environment Variables
At compilation, and link-editing, and execution of application programs, the user can specify tuning the operating
environment with environment variables.
For the specification formats and meanings of the environment variables, refer to the "RDB User's Guide: Application
Program Development."
Environment variables specified at compilation and link-editing of application
programs
· LANG (UNIX only)
· RDBDB
· SQLPC
· SQLPCOB
· INCDIR (UNIX only)
· INCLUDE (Windows NT/2000/XP only)
Environment variables specified at execution of application programs
· LD_LIBRARY_PATH_64
· LD_LIBRARY_PATH
· LD_PRELOAD
· SHLIB_PATH
· RDBNAME
· SQLRTENV
In addition, there are environment variables related to the execution parameters of the operating environment file for
application programs. The operating environment file is used to tune the operating environment of application
programs; however, a part of environment can be specified with environment variables. The parameters specified in
those environment variables are also valid in the rdbupt command.
Operating environment tuning priority
If a specification resulting from an environment variable duplicates a specification resulting from an operating
environment file, the former specification takes precedence.
Correspondence between environment variables and execution parameters of
the operating environment file
Table: Environment variables specified upon execution of the application program and execution parameters of the
operating environment file shows the correspondence between environment variables and execution parameters of
the operating environment file.
181
Download from Www.Somanuals.com. All Manuals Search And Download.
[Table: Environment variables specified upon execution of the application program and execu
tion parameters of the operating environment file]
182
Download from Www.Somanuals.com. All Manuals Search And Download.
Appendix E RDB Command Summary
This appendix gives an overview of RDB commands and functions. For more information about the syntax of the RDB
commands, refer to the man command (under UNIX) or the SymfoWARE/RDB online manual (under Windows NT).
RDB Command Summary
A list of the RDB commands is as follows:
183
Download from Www.Somanuals.com. All Manuals Search And Download.
[Table: RDB command list]
184
Download from Www.Somanuals.com. All Manuals Search And Download.
Standard shell procedure
Table: Standard shell procedures lists the standard shell procedures used under UNIX.
185
Download from Www.Somanuals.com. All Manuals Search And Download.
[Table: Standard shell procedures]
Notes on using RDB commands
Handling of uppercase and lowercase letters
In RDB command specifications, lowercase letters are distinguished from uppercase letters. In SQL, an ordinary
identifier (not enclosed in double quotes) is handled by converting lowercase letters to the corresponding uppercase
letters. Therefore, when an identifier defined using lowercase letters in ordinary identifier format is specified in an RDB
command, it must be specified using uppercase letters. In addition, lowercase letters in a delimited identifier (enclosed
in double quotes) ate handled unchanged in SQL. Therefore, when an identifier defined using lowercase letters in
delimited identifier format is specified in an RDB command, it must be specified using lowercase letters.
Example:
Example of specification using uppercase letters (specifying ordinary identifier as DSI name in SQL
statement)
Example:
Example of specification using lowercase letters (specifying delimited identifier as DSI name in SQL
statement)
Handling characters that have special meaning in the shell
In UNIX system SQL, identifiers may contain #, ¥, and @. In the shell, these characters are treated as symbols having
special meanings. # is the beginning of a comment in a shell script. ¥ is treated as an escape character or line
continuation symbol. @ is used in the line deletion function. Therefore, when an identifier defined using these
characters is specified in an RDB command, the following condition applies. The special meanings of the characters
according to shell rules must be canceled. To cancel the special meaning of a character, enclose the character string
186
Download from Www.Somanuals.com. All Manuals Search And Download.
in quotes ('), or specify an escape character (¥) just before the character.
Example:
Example of enclosing character string in quotes (') (specifying DSI name containing # in SQL
statement)
Example:
Example of specifying escape character (¥) just before character string (specifying DSI name
containing @ in SQL statement)
Some characters that have special meanings can be changed by shell functions. The results must be considered in
the user environment setup if those changes are being made.
Handling of reserved words in SQL
When a reserved word is used in an identifier in SQL, it must be specified in a delimited identifier (enclosed in double
quotes). However, even if an identifier in an RDB command is an SQL reserved word, it can be specified without
changing the format.
Example:
Example of specifying without changing format (specifying reserved word as DSI name in SQL
statement)
187
Download from Www.Somanuals.com. All Manuals Search And Download.
188
Download from Www.Somanuals.com. All Manuals Search And Download.
Appendix F Handling SymfoWARE/RDB
Messages
Refer to the online manual "SymfoWARE/RDB Message Reference" for the user handling of the following
SymfoWARE/RDB output messages:
· Message issued when an RDB command is executed
· Message issued when an application program is compiled
· Message set in the message variable (SQLMSG) when an application program is compiled
· Message output when SymfoWARE/RDB terminates abnormally on detecting an internal inconsistency
When executing an RDB command
Example 1:
Display a description of the qdg03110u message issued by executing an RDB command.
(SymfoWARE Server Enterprise Edition)
When compiling an application program (C language)
Example 2:
Display a description of message 11005 issued by the C precompiler.
189
Download from Www.Somanuals.com. All Manuals Search And Download.
When compiling an application program (COBOL)
Example 3:
Display a description of message 11011 issued by the COBOL precompiler.
When executing an application program
Example 4:
Display a description of message JYP2031E set in the message variable (SQLMSG) when executing
an application program.
190
Download from Www.Somanuals.com. All Manuals Search And Download.
When SymfoWARE/RDB has detected an internal inconsistency
Example 5:
Display a description of message qdg12695u issued when SymfoWARE/RDB terminates abnormally
on detecting an internal inconsistency. (Reference code: 16.1.7.769)
191
Download from Www.Somanuals.com. All Manuals Search And Download.
192
Download from Www.Somanuals.com. All Manuals Search And Download.
Appendix G Exclusive Control between
Application Programs and RDB Commands
When an application program and RDB command operate simultaneously on the same database resource, the
SymfoWARE/RDB system performs exclusive control. In this way, each process can be executed without
contradiction.
Each function performs processing by applying exclusive control of the resource specified by the option or input data.
The period for which the resource is exclusively controlled is as follows:
Application program:
From the time the first SQL statement is issued from the application program until the application program
terminates
RDB command:
From the time the command is entered until the response message is generated
Table: Locked resources and locked strength for each SymfoWARE/RDB function shows resources for which
lock is performed by each function and the strength of the lock. Table: Relationships among lock strengths
shows the relationships among locked strengths.
[Table: Locked resources and locked strength for each SymfoWARE/RDB function]
193
Download from Www.Somanuals.com. All Manuals Search And Download.
194
Download from Www.Somanuals.com. All Manuals Search And Download.
195
Download from Www.Somanuals.com. All Manuals Search And Download.
196
Download from Www.Somanuals.com. All Manuals Search And Download.
197
Download from Www.Somanuals.com. All Manuals Search And Download.
198
Download from Www.Somanuals.com. All Manuals Search And Download.
199
Download from Www.Somanuals.com. All Manuals Search And Download.
[Table: Relationships among lock strengths]
200
Download from Www.Somanuals.com. All Manuals Search And Download.
Glossary
Access
The operations of reading data from a storage device and writing data to a storage device. In this manual, reading
data from a database and writing data to a database is called access.
Application program
Generally, programs used by users for their work are defined as application programs. In this manual, programs that
read data from, or write data to, databases in response to business processing performed by users are known as
application programs.
Base table
A table defined as a base table in logical structure definition. The data body is stored in a database space.
BTREE structure
The storage structure for an index. Consists of an index part and a data part. The index part folds the values of
information about the correspondence between groups of columns that are index keys and base table data, and
manages the pages of the data part. The data part holds data consisting of values of information about the
correspondence between groups of columns that are index keys and base table data.
Related terms:
Index part, storage structure, data part
Bucket
A structure unique to RANDOM structures. A RANDOM structure uses a hash function as follows. The function
specifies the collection of pages that sore data from the values in the group of columns that form the data key. The
collection of pages is called a bucket.
Related term:
RANDOM structure
B-tree structure
An index system using an effective retrieval technique for speedily finding table data satisfying the retrieval condition.
SymfoWARE/RDB uses this effective data retrieval technique to record data storage location in the root-trunk-leaf
format and retrieve data satisfying the retrieval condition in high speed. An index using this technique is calld "B-tree
structure." SymfoWARE/RDB adopts the B-tree structure for the index that is a table storage structure.
Related term:
Hash structure
201
Download from Www.Somanuals.com. All Manuals Search And Download.
Client
he operational unit that requests data processing in the client/server model. When a database is used according to the
client/server model, the application program requesting access to the server database operates in the client.
Client/server model
A model used in data processing in which the operational unit (client) that requests data processing and the
operational unit (server) that executes data processing are established separately.
Cluster key
A grouping of columns that become a key for determining the page that stores data. Data for which cluster key values
are equal is stored in the same bucket or overflow part bucket. Cluster keys are specified in the CLUSTER option of a
table DSO definition. If cluster keys are omitted, the primary key of the corresponding table becomes the cluster key.
Column
A constituent element of a table. A relational database represents data using two-dimensional tables consisting of rows
and columns.
Column attribute
Column data types and column constraints
Column constraint
Column constraints include the NOT NULL constraint (NOT NULL) and unique constraints (UNIQUE, PRIMARY KEY).
Related terms:
Unique constraint, NOT NULL constraint
Column name (item name)
The name attached to a column as defined in the schema. The column name is used to specify a column that is the
subject of an operation in an SQL statement that manipulates data.
Commit
Makes the data manipulation of a transaction being processed take effect. The data manipulation in the transaction is
physically reflected in the database. An application program deliberately controls a commit by issuing a COMMIT
statement.
Related term:
Rollback
202
Download from Www.Somanuals.com. All Manuals Search And Download.
Connection
Connection refers to the relationship that connects a client to a server. A connection is made by specifying a
CONNECT statement in an application program. Conversely, a DISCONNECT statement cuts off the connection.
Cursor
A cursor is an indicator that indicates a row to be manipulated. A cursor is defined by a cursor declaration statement.
The OPEN and CLOSE statements start and stop cursor operations, respectively. The FETCH statement moves the
cursor.
Cursor SQL statement
A cursor SQL statement is a data manipulation SQL statements that uses a cursor to specify rows to be manipulated.
Data manipulation SQL
A data manipulation SQL is an SQL statement used to reference, add, delete, or update a database.
Data part
A constituent element of a storage structure. The data part contains storage data corresponding to the table data
(SEQUENTIAL structure or OBJECT structure) or data made up of index keys and table cluster keys (BTREE
structure).
Related term:
Index part
Data structure instance (DSI)
Expresses the storage structure for a table (base table). In addition to the information expressed in a DSO, a DSI
expresses a mapping to a database space. The relationship between a DSO and a DSI can be one-to-one or
one-to-many. A one-to-many relationship occurs only when a split table operation is applied.
Related terms:
Storage structure, DSO
Database generation
Storing the initial data in empty base tables immediately after database definition. SymfoWARE/RDB utilities are used.
Database name
Many database can be created as units of administration and design on one server system. To identify each database
uniquely, each is assigned a unique name (database name) on the server system.
203
Download from Www.Somanuals.com. All Manuals Search And Download.
Database space
An area that stores base tables and indexes. Database space is created by physical structure definition in
SymfoWARE/RDB.
Deadlock
A stopped status that occurs when several transactions share use of a database. A deadlock is to a loop that occurs
when several transactions wait for the same resource. Each transaction waits for another transaction to release the
resource, and all transactions in the loop end up in stopped status.
Related terms:
Transaction, exclusive control
DEFAULT clause
An element of the definition of a column in a table. If the following condition applies, the value defined in the DEFAULT
clause is inserted. The data to be inserted in a column is not specified when a row is inserted in a table using the
INSERT statement. If a DEFAULT clause is not defined for a column, a NULL value is inserted.
DELETE statement
The DELETE statement is data manipulation SQL statement used to delete rows from tables.
DSO
Expresses the storage structure for a table (base table). DSOs include table DSOs and index DSOs.
Related terms:
Storage structure, DSI
Dynamic SQL
Dynamic SQL is a function for generating and executing SQL statements when executing an application program. In
general, this function is used by general-purpose package programs.
Embedded exception declaration
An embedded exception declaration specifies the processing to be performed if an exception condition is issued when
and SQL statement is executed in an application program. The WHENEVER statement is used to specify an
embedded exception declaration.
Embedded SQL
SQL statements can be used as embedded SQL statements. Use embedded SQL statements to manipulate data in
application programs written in high-level languages such as C language or COBOL. For example, when COBOL is
used, specify an SQL statement embedded between EXEC SQL and END-EXEC.
204
Download from Www.Somanuals.com. All Manuals Search And Download.
Esql
A generic name of the compiler function for embedded SQL C programs or embedded SQL COBOL programs.
Embedded SQL C programs and embedded SQL COBOL programs are called as shown below:
Embedded SQL C program:
Esql-c
Embedded SQL COBOL program:
Esql-COBOL
Excel
Developed by Microsoft Corporation, Excel is spreadsheet software that runs on a workstation.
Exception condition
When an SQL statement is executed in an application program, data to be processed may not be able to be found or
an error may occur. Such a condition is known as an exception condition or exception. if an exception condition occurs
while an application program is running, the status code corresponding to be exception condition is set in SQLSTATE.
Exclusive control
Control used when multiple users use a database. While one user is updating the database, other users cannot
reference the data being updated or cause data conflicts by updating the data being updated. This function is
generally called locking.
External routine
A user-created program (written in a language such as C) registered to a server as a dynamic link library called from
an SQL statement. External routines have an advantage because they use C and other languages to enable complex
processing such as formatting of character string data to be easily accomplished. Such processing with SQL functions
has limitations.
With SymfoWARE/RDB, C can be used for creating external routines.
Function routine
A function that defines a user-created C program as a function in an SQL statement and processes it.
Users can create functions that they want and that are not provided by SymfoWARE/RDB, and they can use them in
SQL statements in the same way as for numeric, data string, and date-and-time functions.
Hash function
The function used to specify the collection of pages that store data from the value of the group of columns that form
the data key. Unique to RANDOM structures.
Related term:
RANDOM structure
205
Download from Www.Somanuals.com. All Manuals Search And Download.
Hash structure
An indexing method used to find quickly the data of a base table that matches a search condition. A hash structure is a
storage structure that makes fast data manipulation possible as follows. A hash structure uses a has function to
determine the storage location of data using the value of a data key. SymfoWARE/RDB uses this mechanism in
RANDOM structures that are the storage structures for base tables.
Related terms:
B-tree structure, RANDOM structure
Host variable
A variable for passing data between an application program and a database in SQL statements that manipulate data.
Index
Key data for increasing the efficiency of retrieving table data. If efficient data retrieval is not possible in
SymfoWARE/RDB using only the primary key specified in the table definition, a supplemental positional key data can
be created. This key data is called an index. An index can be created for each column of a table or for several
combined columns. An index is established in a storage structure definition.
Index definition
A definition that indicates the columns of a table for which the index is created. To increase data manipulation
efficiency, an index is required for frequently searched columns.
Index part
A constituent element of a storage structure. The index part is the portion that stores the data of an index for retrieving
data stored in the data part. The index part is a constituent element of a BTREE structure.
Related terms:
Data part
Indicator variable
In high-level languages such as C language and COBOL, the indicator variable is a variable specified to be paired
with an SQL data variable. Indicator variables are used when SQL statements are used to fetch data from, and update
a data base. When SQL statements are used to update a data base, the indicator variable indicates whether or not
data stored in the SQL data variable contains a null value. When SQL statements are used to reference a data base,
the indicator variable indicates whether or not the execution result of the SQL statement has a null value stored in the
SQL data variable. The indicator variable also shows the number of characters in character-string data stored in the
SQL data variable.
INSERT statement
The INSERT statement is an SQL data manipulation statement used to add rows to a table.
206
Download from Www.Somanuals.com. All Manuals Search And Download.
Log group
A log environment split unit is called a log group. Each log file consists of a log management file, temporary log file,
and archive log file. There are two types of log groups: system log groups unique in the default RDB system and
multiple user log groups to be added and defined.
Logical structure
One of the structures of a database along with the storage structure and physical structure. The data structure that
includes the schema, table, and column configuration, and column data types is called the logical structure.
Constraints such as unique constraints, privileges, procedure routines, and triggers are also elements of the logical
structure.
Related terms:
storage structure, physical structure
Logical structure definition
An element of SymfoWARE/RDB database definition (also called schema definition). Table and view table definitions
apply to logical structure definition.
Lotus 1-2-3
Developed by Lotus Development Corporation, Lotus 1-2-3 is spreadsheet software.
M host
The information processing system built into the M series general-purpose computer is called "M host"
Multi-database space
A base table or index DSI (data structure instance) allocated to multiple database spaces. A multi-database space can
correspond to a large capacity DSI that exceeds the absolute capacity of a disk volume.
Multi-RDB
Multi-RDB means to activate multiple SymfoWARE/RDB systems with different RDB dictionaries in a single system
configuration. This configuration allows linkage to each SymfoWARE/RDB environment for data access.
NTFS (Windows NT(R) file system)
A file system for use in the Windows NT(R) operating system. This file system supports a file recovery function, mass
storage media, long file names, and strict access privilege control.
207
Download from Www.Somanuals.com. All Manuals Search And Download.
Non-cursor SQL statement
An SQL statement used for data manipulation, the non-cursor SQL statement does not use a cursor to specify rows to
be manipulated. Instead, the rows to be processed are specified in the search condition specified in the SQL
statement.
NOT NULL constraint
A constraint on a column of a table. This constraint prohibits rows in the table for which the value in the column is
NULL.
NULL
The value of the data in a specified column of a row in a table that is undefined.
Number of key values that differ
Optimization information that refers to the number of key values of storage data in a database that differ from one
another. For example, if all the storage data key values in a database are different, the number of different key values
matches the storage data count. Conversely, if all the storage data key values are the same, the number of different
key values is one.
OBJECT structure
A storage structure of SymfoWARE/RDB database base tables. This structure is applied to base tables for handling
image, voice, and other types of multimedia data.
Related terms:
SEQUENTIAL structure, RANDOM structure
Open Systems Interconnection/Remote Database Access (OSI/RDA)
OSI is an international standard for interconnecting different types of computers. This standard is being developed by
the International Organization for Standardization (ISO) and the International Telegraph and Telephone Consultative
Committee (CCITT). RDA is an OSI application layer standard for interoperation of databases between different types
of systems. RDA makes it possible to perform processes such as retrieval and update using the SQL database
language in databases on different types of systems.
Operating environment file
A file for defining the operating environment used when an application program is executed. Operating environment
files include client operating environment files and server operating environment files. A server operating environment
file is used for tuning the system-provided files for the application program.
Optimization
Determining the most efficient processing procedures for the search conditions by investigating tables bound to SQL
statement instructions.
208
Download from Www.Somanuals.com. All Manuals Search And Download.
Optimization information
Information that is the basis for optimization in SymfoWARE/RDB. Optimization information includes the amount of
base table data (number of rows), the number of levels of indexes, and the number of different key values.
SymfoWARE/RDB integrates and evaluates SQL statements and optimization information and determines the most
efficient data manipulation processing procedures. Optimization information is collected using the rdbups command.
Overflow part
A constituent element of a storage structure unique to a RANDOM structure. When storage data can not be collected
in prime part pages because of excess data, the overflow part provides reserved pages for storing the excess data.
The overflows part consists of these reserved pages.
Related terms:
Prime part
Overflow pointer
When storage data is not collected in the prime part, the storage data is collected in the pages of the overflow part.
(This condition does not apply to a page split.) When this condition occurs, an overflow pointer connects the relevant
page of the prime part to the relevant page of the overflow part. The overflow pointer is unique to RANDOM structures.
Related terms:
Overflow part, prime part
Page
The smallest unit of I-O for a database. The size of a page is determined by the number of rows to store in the page.
Page split
A self-adjustment function unique to B-tree structures. When the amount of data stored in a given page exceeds a
fixed value, this function relocates storage data between separate pages (including empty pages). This relocation
evens the amount of data stored in pages to maintain a balance in processing efficiency.
Related terms:
B-tree structure
Parallel query
To raise the information processing efficiency in handling a large volume of data, a database is divided into several
DSI units. The parallel processing of DSI units is called parallel query processing.
Physical structure
One of the database structures along with the logical structure and storage structure. The database space located on
a magnetic disk volume is called the physical structure. A database space is an aggregate of fixed-size blocks.
Related terms:
Storage structure, logical structure
209
Download from Www.Somanuals.com. All Manuals Search And Download.
Physical structure definition
An element of a SymfoWARE/RDB database definition for creating database spaces.
Pointer variable
A host variable declared as a pointer in an embedded C program. An area dynamically obtained using the malloc
function or obtained outside an embedded SQL declare section can be specified in an SQL statement.
Primary key
A column or group of columns that can uniquely specify a row of a table. One of the major elements for designing
logical structure. The primary key is specified using PRIMARY KEY in the unique constraint of the table definition.
Prime part
A constituent element of a storage structure that is unique to RANDOM structures. Storage data corresponding to
table data is obtained from a page (bucket) of this part first.
Related terms:
Overflow part
Private sort work area
One of the sort work areas of the SymfoWARE/RDB system. A private sort work area is prepared by the user. The
directory is specified in the operating environment file specific to an application program.
Related terms:
Shared sort work area, sort work area
Procedure routine
The definition of a database processing procedure by SQL is called a procedure routine.
Procedure routine definition
Defining a procedure routine with a logical structure is called procedure routine definition.
Related term:
Procedure routine
RANDOM structure
A storage structure of SymfoWARE/RDB database base tables. A storage structure in which hash function indexing is
used in the data storage method is called a RANDOM structure.
Related term:
OBJECT structure, SEQUENTIAL structure
210
Download from Www.Somanuals.com. All Manuals Search And Download.
Raw device
A disk area that can be accessed regardless of the UNIX file system is called a raw device. The raw device is a
character-type special device created using the UNIX utility. It is ordinarily indicated by a node name created under
/dev/rdsk/.
RDB configuration parameter
Information, such as where the RDB directory file is located, that defines the operating environment of a
SymfoWARE/RDB system. Operating environment setup is performed in accordance with the description of the RDB
configuration parameter during when the RDB system is activated.
Related terms:
RDB configuration parameter file
RDB configuration parameter file
A file containing RDB configuration parameters is called an RDB configuration parameter file. The RDB configuration
parameters are used to define SymforWARE/RDB operating environments such as an RDB directory file allocation
destination.
RDB dictionary
A file in which user database definition information is stored. An RDB dictionary is represented in a table like those the
user defines. This table is called a system table.
Related terms:
System table
RDB library
When an application program that uses SQL statements is executed, shared objects provided by the
SymfoWARE/RDB system are called to perform database processing. The shared objects also call shared objects
provided by Windows NT(R) system. The load module of an application program that uses SQL statements must be
dynamically linked to these shared objects. In this manual, these shared objects are called the RDB library.
Read-only cursor
In an SQL data manipulation statement, a cursor that cannot be used to update and delete is called a read-only cursor.
The cursor declaration specifies whether a cursor is read-only. For example, if two or more tables are specified in the
FROM clause of a query specification, the cursor is a read-only cursor.
Related terms:
Updatable cursor
Reference mode
A mode denoting strength of exclusion. Also called shared mode, the reference mode represents the strength of data
locks in data manipulation. Data that is locked in reference mode can only be referenced by other transactions and
cannot be updated. In general, the execution of other transactions waits until a commit is performed on the transaction
211
Download from Www.Somanuals.com. All Manuals Search And Download.
that obtained the data lock.
Related terms:
procedure routine, function routine
Relational database
Database used in SymfoWARE/RDB that represents data using two-dimensional tables consisting of rows and
columns. Database operations are performed using the SQL database language.
Remote database
When a database is distributed into multiple servers, the processing mechanism to access the database from one
application program is called "remote database"
Remote database access - service (RDA-SV)
A software product provided by Fujitsu Limited that implements distributed database functions to be used by PC
spreadsheet software and application programs on a server system
Role
A group of privileges required for one transaction. To specify at one time the privileges required for one transaction,
define a role. For efficiency of privilege management, a role can be defined to grant the role privileges to all users who
perform the relevant transaction.
Rollback
Nullifying the data manipulation of a transaction that is being processed. The two kinds of rollback can be performed.
Rollback in data manipulating SQL statement units can be the occurrence of an exception condition in a data
operation. Rollback in transaction units can be the deliberate execution of a ROLLBACK statement by an application
program.
Related terms:
Commit
Routine
Procedure routines and function routines are generally called routines. Related terms:
procedure routine, function routine
Routine name
The name of a procedure routine or function routine is called a routine name.
212
Download from Www.Somanuals.com. All Manuals Search And Download.
Row
A row is one of the components of a table. In relational data bases, data is expressed in terms of two-dimensional
tables containing rows and columns.
Row identifier
The rows of a database table are uniquely identified. A user can manipulate a row using the row identifier fetched by
the single row SELECT statement or by using the FETCH statement.
Scalable log operation
Splits log environments into multiple log groups in the RDB system for definition.
Schema
A constituent element of a database. SymfoWARE/RDB performs data analysis using information analysis system
AA/BRMODELLING to create tables or view tables.
Schema definition
Logical structure definition that includes defining the base tables and view tables that constitute a database, the
column configuration of each table, and the attributes of each column. In addition, privileges, a procedure routine, and
triggers can be defined. This is called "schema definition"
Scope
When a table is accessed by use of a SQL statement for data manipulation, the access range can be limited. The
access range of is called the scope. The scope function limits the data manipulation range for each user by applying
or canceling the scope for the user who accesses the table.
Security
SymfoWARE/RDB assures security for resources such as schemas, tables, procedure routines, and database spaces.
Sequence
A function that generates a value unique within an entire system. A sequence can be specified in an SQL statement to
use the generated values primarily for creating primary key values in a table.
SEQUENTIAL structure
A storage structure of base tables in a SymfoWARE/RDB database. This storage structure is applied to base tables for
adding rows (records) in the order of data generation, as in a historical journal.
213
Download from Www.Somanuals.com. All Manuals Search And Download.
Related terms:
RANDOM structure
Server
The operational unit that executes data processing in the client/server model. When a database is used according to
the client/server model, the database operates in the server.
Shared buffer pool
A buffer for accessing a database (also called a shared buffer). Because data can be shared by multiple application
programs, a shared buffer pool can minimize the number of inputs and outputs of data application programs accessing
in common.
Shared buffers
A buffer for accessing a database (also called a shared buffer pool). Because data can be shared by multiple
application programs, a shared buffer can minimize the number of inputs and outputs of data application programs
accessing in common.
Related term:
Shared memory
Shared memory
A memory area that can be mutually referenced by more than one process. In a SymfoWARE/RDB system, shared
buffers and the log collection area are placed in shared memory.
Related terms:
Shared buffers
Shared sort work area
One of the sort work areas in the SymfoWARE/RDB system. The shared sort work area is provided by the user and is
specified in the common application environment file of the directory system.
Related terms:
Private sort work area, sort work area
Sort work area
A work area the SymfoWARE/RDB system uses on magnetic disk. The SymfoWARE/RDB system saves data in a
work table when it needs to save an intermediate result while manipulating data. If sorting is required, the
SymfoWARE/RDB system uses sort work. A work table or sort work of up to a specified fixed amount uses virtual
memory. When a work table or sort work exceeds the fixed amount, it uses an area on magnetic disk. This area is
called a sort work area. A shared sort work area is shared by the entire SymfoWARE/RDB system; own sort work
areas are used privately by each application program or command.
Related terms:
Shared sort work area, private sort work area, working sort area, work table
214
Download from Www.Somanuals.com. All Manuals Search And Download.
Split condition
When a split table operation is applied, the rule for dividing data into split units is called the split condition. The split
condition specifies a list of column names representing split keys and a list of dummy values in which "?" is specified.
A split condition is specified in table DSO definition.
Related terms:
Split key, split key value
Split key
he key used to locate data by dividing it into split units when applying split table operation. This key corresponds to a
specific column (or multiple columns) of a table.
Related terms:
Split value, split condition
Split value
The value for a "?" specified in a split condition in a table DSO definition that is specified using a constant when the
table DSI is defined. This value applies to split table operation. Data stored in the defined DSI is a row in which
evaluation of the condition is true when the "?" in the split condition is replaced by the split value.
Related term:
Split key, split condition
Split table operation
SymfoWARE/RDB allows splitting a single logical base table into physically separate tables. Splitting tables speeds up
the access to a large database and ensures practicality from an operations standpoint. For example, a sales table for
all stores can be split by store. An application program can apply split table operation without giving it special
consideration. Moreover, database tuning tasks and database save operations can be performed independently and
concurrently in the split units.
SQL
SQL is a standard database language for performing database definition and data manipulation. SymfoWARE/RDB
basically conforms to the protocols of the international standards IS 9075, JIS X3005, and ANSI X3.135.
SQL embedded host program
In an application program that manipulates a data base, the parts of the program that perform database processing
are written using SQL statements. The parts of the program that perform other kinds of processing are written using a
programming language such as C language or COBOL. Application program in which SQL statements are embedded
are called SQL embedded host programs. The following terms are used for application programs that contain
embedded SQL statements:
a. SQL embedded C program
b. SQL embedded COBOL program
SQL embedded host program is the generic term for a) and b).
Status variable
The status variable, SQLSTATE, reports the processing result of an SQL statement to an application program. When
215
Download from Www.Somanuals.com. All Manuals Search And Download.
an SQL statement is executed, the status code for the execution result is stored in the status variable.
Storage data
Data that is stored in a database space. The rows and columns of tables represent the logical aspect of data, and
stored data represents the physical aspect of data.
Storage structure
A database structure along with logical structure and physical structure. Storage structure physically locates data
logically expressed as rows and columns of tables in a database as storage data. A storage structure is expressed
using DSO and DSI. The storage structures for base tables are RANDOM structures, SEQUENTIAL structures, and
OBJECT structures; BTREE structures are the storage structures for indexes.
Related terms:
Physical structure, logical structure
Storage structure definition
The definition of mapping between tables and database spaces. The two kinds of storage structure definition are data
structure organization (DSO) definition and data structure instance (DSI) definition.
Structure host variable
A host variable declared as the structure type is called a structure host variable. When a structure host variable is
declared, each member is handled to correspond to individual columns in a database so that multiple-column data can
be manipulated in row units. Multiple rows can also be inserted at one time. A structure host variable can be used in
combination with a pointer variable to simplify the application program and improve maintainability.
System table
A table that manages definition information for, for example, databases and schemas defined by a SymfoWARE/RDB
user. The system table is also called the RDB dictionary.
Table
In a relational database, data is represented using two-dimensional tables consisting of rows and columns. The two
kinds of tables are base tables and view tables.
Table constraint
Constraints on tables are unique constraints (UNIQUE or PRIMARY KEY)
Related terms:
Unique constraint
216
Download from Www.Somanuals.com. All Manuals Search And Download.
Table declaration
A table declaration declares the schema in which a table is located. If a table declaration is specified, the schema
name need not be specified for a table in data manipulation statements. Using a table declaration simplifies the
specification of table names. Using a table declaration also helps to make an application program independent of a
data base.
Table name
A name attached to table. Table names are set in schema definitions. Table names are used to specify the tables to be
made the subjects of operations in SQL statements that manipulate data.
Temporary table
A table created specifically for a user of an application program. Multiple users can use temporary tables with the
same table name. To temporarily save data being processed by an application program, a temporary table can be
used independently of other application programs.
Transaction
The unit that guarantees consistency of sequential data operations. A database can be accessed and updated serially
or updated by arranging a number of SQL statements. If a problem such as an unexpected system crash occurs
during sequential data operations, database recovery status can be based on units of transactions.
Trigger definition
Trigger definition defines table data manipulation (insert) in conjunction with other table data manipulations (insert,
delete, update).
Unique constraint
A constraint on a table or column. This constraint requires that a table cannot have multiple rows having the same
value in a column or combination of columns.
Updatable cursor
In an SQL data manipulation statement, a cursor that can be used to update and delete is called an updatable cursor.
The cursor declaration specifies whether a cursor is updatable. For example, if a table specified in the FROM clause
of a query specification satisfies just one condition, the cursor is an updatable cursor.
Related terms:
Read-only cursor
Update mode
A mode denoting strength of exclusion. Also called nonshared mode, the update mode represents the strength of data
locks in data manipulation. Data that is locked in update mode cannot be manipulated by other transactions. In
general, the execution of other transactions waits until a commit is performed on the transaction that obtained the data
lock.
217
Download from Www.Somanuals.com. All Manuals Search And Download.
Related term:
Reference mode
UPDATE statement
The UPDATE statement is a SQL data manipulation statement used to update data in table rows.
Upgrade
Index update processing. In conjunction with data update, insertion, and deletion processing in a table, this process
updates indexes attached to that table to reflect the latest status. This processing is called upgrading indexes.
View definition
The definition of a view in a logical structure definition. The view definition defines the portion of the base table that
forms the view, the name of the view, and the name of each column.
WHERE clause
In an SQL data manipulation statement, the WHERE clause is a search condition that specifies rows to be
manipulated.
Work table
A temporary table the SymfoWARE/RDB system uses for manipulating data. Intermediate results often must be saved
when the SymfoWARE/RDB system executes operations on data. A table that saves intermediate results is called a
work table. A work table of up to a specified fixed amount uses virtual memory. When a work table exceeds the fixed
amount, it uses a sort work area on magnetic disk. The amount of virtual memory to use can be specified in the
application environment file.
Related terms:
Sort work area
Working sort area
A temporary area used for sorting by the SymfoWARE/RDB system. If the SymfoWARE/RDB system has to sort data
during data manipulation, loading, or unloading, it sorts this data in a work area for sorting. This work area for sorting
is called the working sort area. Up to a fixed amount of virtual storage is used for a working sort area. However, when
this amount is exceeded, a sort work area on magnetic disk is used. The amount of virtual storage to be used can be
specified in the operating environment file.
Related terms:
Sort work area
218
Download from Www.Somanuals.com. All Manuals Search And Download.
|
Electro Voice Portable Speaker Force i25 User Manual
Everpure Water System Quad XC User Manual
Extron electronic Network Card CTL208CM User Manual
Focal Car Speaker 130 CVX User Manual
Frigidaire Ventilation Hood HV2730B User Manual
Fujitsu Laptop ST5111 User Manual
Garmin Laptop Mobile PC User Manual
GE Freezer ZIFP360NX User Manual
Gemini Turntable SA 600 User Manual
GE Refrigerator 25 and 27 User Manual