Fujitsu 5.0L10 - Manual

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...

[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. F...

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...

[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 sp...

[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 c...

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." Tabl...

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 subdivide...

[Figure: Example of allocating multiple database spaces to increase the size of each component] 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

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 Data...

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, at...

[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 ...

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 th...

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 ...

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 e...

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 devic...

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 ...

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 environmen...

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 ta...

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...

[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 (un...

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 defi...

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 viola...

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 Tab...

- 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...

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, re...

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 p...

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 de...

· 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 exec...

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 F...

[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 defini...

· 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 nam...

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, ...

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 voi...

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 Specif...

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 de...

· 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 ...

[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 storag...

[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 impro...

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. Cauti...

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...

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 speci...

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 ...

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...

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...

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 ta...

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 wit...

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. ...

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 ...

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 GR...

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...

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...

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 P...

· 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) - Informat...

[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 specificat...

Sample database output object specification and print results [Figure: Sample database output object specification and print results] 95

Definition information output format for a DB specification [Figure: Definition information output format for a DB specification] 96

Definition information output form at for a TABLE specification [Figure: Definition information output format for a TABLE specification] 98

Definition information output format for a DSO specification [Figure: Definition information output format for a DSO specification] 102

Definition information output format for a DSI specification [Figure: Definition information output format for a DSI specification] 104

Definition information output format for a SCOPE specification [Figure: Definition information output format for a SCOPE specification] 109

Definition information output format of a USER specification [Figure: Definition information output format of a USER specification] 111

Definition information output format of a ROLE specification [Figure: Definition information output format of a ROLE specification] 112

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. ...

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 PRO...

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 ex...

[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 d...

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...

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...

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....

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 DS...

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 ...

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

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...

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...

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

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 ...

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 D...

Deleting a database To delete the database name, use the DROP DATABASE statement. Example: Delete STOCKMN_DB. 138

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 repres...

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 definiti...

[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 exp...

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 ca...

[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 st...

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 v...

· 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 th...

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

Appendix A Quantitative Restrictions Table: Quantitative limitations shows quantitative restrictions on SymfoWARE/RDB. [Table: Quantitative limitations] 169

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: App...

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 f...

Standard shell procedure Table: Standard shell procedures lists the standard shell procedures used under UNIX. 185

[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 lett...

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 containi...

Appendix F Handling SymfoWARE/RDB Messa ges 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 s...

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

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 pro...

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...