Manuals / Brands / Computer Equipment / Server / Fujitsu / Computer Equipment / Server

Fujitsu 5.0L10, J2X0-2273-01EN 2.3 Creating a Database

1 225

Download 225 pages, 12.56 Mb

[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

Contents

Main Preface Purpose Intended reader Organization Page Page Commen t s on this manual Page Page Chapter 1 SymfoWARE/RDB Overview 1.1 Overview of SymfoWARE/RDB Functions [Figure: SymfoWARE/RDB functions configuration] Functions for defining table formats (database definition) Functions for maintaining and managing databases (database management) Functions for manipulating tables (table manipulation) 1.2 Overview of the SymfoWARE/RDB Database Configurat ion [Figure: SymfoWARE/RDB database configuration] [Figure: Relationship of logical, storage, and physical structures within databases] 1.2.1 Physical structure Database space 1.2.2 Logical structure Schema [Figure: Example of correspondence between schemas and database spaces] Base table Table name Column Table constraint [Figure: Base table format example] View [Figure: Concept of a view] Index [Figure: Concept of an index] 1.2.3 Storage structure DSO Table DSO Index DSO DSI [Figure: Example in which DSOs and DSIs are associated in a 1:n correspondence] [Figure: Storage structure components] Page 1.3 Overview of Database Creation Tasks Using RDB commands Creating a SQL-embedded program [Figure: Overview of tasks involved in database creation] Chapter 2 Database Creation 2.1 Overview of Tasks From Database Design To Operatio n Database creation and operation Page Page 2.2 Designing a Database Designing tables Designing attributes Designing the storage structure Determining the storage structure STOCK table ORDER table COMPANY table Page Page Column attributes of each table of the inventory management database [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] 2.3 Creating a Database 2.3.1 Defining a database by using the rdbddlex command Input file format of rdbddlex command Format 1 Format 2 (Format for defining a procedure routine) [Figure: Sample creation of a database from a definition file] Page Page Page 2.3.3 - Omitted - 2.4 Entering a Database Name [Figure: Configuration of a database] CREATE DAT ABASE statement Database name 2.5 Creating a Database Space 2.5.1 Creating a database space on a raw device CREATE DBSPACE statement Database space name Raw device name Notes on operating multi-RDB 2.5.2 Creating a database space on a local file CREATE DBSPACE statement Database space name File name 2.5.3 - Omitted - 2.5.4 Operation of a scalable log 2.6 Defining a Logical Structure [Figure: Logical structure definition procedure] Schema definition Sequence definition Table definition View definition Trigger definition Schema name Schema comment definition 2.6.2 Sequence definition Sequence name 2.6.3 Table definition [Figure: CREATE TABLE statement that defines the STOCK t able] Table name Column definition Column name Column data type Page Page Page [Table: Time interval specifications] Default value Column constraint Column comment definition Unique constraint UNIQUE [Figure: Sample data that violates the unique constraint] PRIMARY KEY Table comment definition 2.6.4 Table definition for multimedia data storage 2.6.5 View definition Table name (view name) View column list Column name Column comment definition Query specification Table (view) comment definition 2.6.6 Trigger definition Simple trigger Update-and-add trigger Trigger for calling a procedure routine Page Page Trigger operation Notes on defining a trigger 2.6.7 Procedure routine definition Procedure comment definition 2.6.8 Function routine definition 2.7 Defining a Storage Structure [Figure: Storage structure definition procedure] DSO definition Table DSO definition Index DSO definition DSI definition Table DSI definition 2.7.1 Table DSO definition DSO name Table name Data structure SEQUENTIAL RANDOM Split condition [Table: Data types of columns that can be specified for CLUSTER and column name list] 2.7.2 Table DSO definition for multimedia data storage 2.7.3 Index DSO definition DSO name Key specification Table name Column name list [Table: Data types of columns that can be specified for the column name list of an index] Base representation 2.7.4 Table DSI definition DSI name DSO name Split values [Table: Specification formats of constants that can be specified for split values] [Table: Specification formats of time interval types that can be specified for split key values] Space allocation Database space name Allocation size Page Page 2.7.5 Index DSI definition Page [Figure: Overview of DSI initialization] 2.7.7 Scope definition Scope name DSI name list Caution: 2.8 Applying a Storage Structure Definition 2.8.1 Scope definition application Scope name Privilege identifier 2.9 Simplifying a Storage Structure Definition 2.9.1 Table definition Storage area specification Table DSO and DSI names Table storage structure 2.9.2 Table definition for multimedia data storage Page 2.9.3 Index definition Index name Key specification Storage area specification Index DSO name and DSI name Index storage structure Index comment definition 2.10 Defining a Temporary Table Extracting data necessary for a user from a database and creating a table containing the dat a Page Page Defining a temporary table Table name Table elements Row deletion specification Comment definition Defining an index Index name Key specification Index DSO name and DSI name 2.11 Privilege Information Definition Defining privileges using a role 1) Define a role. 2) Specify the privileges to be granted in the role. 3) Grant the role privileges to users. Specifying a default role 2.12 Defining Optimization Information What is optimization information? Optimization information definition opportunity When optimization information is defined with values assumed in advance: Defining optimization information Page Definition for each index DSO Output of optimization information Page Page Page 2.13 Generating a Database Database generation methods [Figure: Database generation methods] Generating a database by using the INSERT statement Page 2.14 Referencing Database Definition Information 2.14.1 Information printed by the rdbprt command Items printed in definition information DB specification SCHEMA specification TABLE specification TRIGGER specification Routine specification DSO specification DSI specification SCOPE specification DBSPACE specification SEQUENCE specification 2.14.2 rdbprt command specification method Sample database list print specification [Figure: Sample database list print specification] Sample user name list print specification [Figure: Sample user name list print specification] Sample role name list print specification [Figure: Sample role name list print specification] Sample definition information print specification 2.14.3 rdbprt command print format Sample database list print results [Figure: Sample database list print results] Sample user name list print results [Figure: Sample user name list print results] 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] Page Page Page Page Page Page Page Page Page Page Page Page Page Page Page Page Page Page 2.14.4 Printing privilege information Page Chapter 3 Database Definition Alteration and Deletion 3.1 Altering a Database Definition Altering the logical structure definition: Altering the storage structure definition: Defining optimization information for added definition Altering privileges 3.1.1 Altering a logical structure defining 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 column definition [Figure: Adding a column to a table] Deleting a column definition [Figure: Deleting a column from a table] 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 defini tion (DROP FUNCTION statement) Changing a comment definition Swapping a table (SWAP TABLE statement) Deleting a table DSO definition (DROP DSO statement) Adding an index DSO definition (CREATE DSO statement) Deleting an index DSO definition (DROP DSO statement) Adding a table DSI definiti on (CREATE DSI statement) Deleting a table DSI definition (DROP DSI statement) Adding an index DSI definiti on (CREATE DSI statement) Changing a split key value of a DSI definition (ALTER DSI statement) Integrating DSIs Changing the DSI storage range Adding a scope definition (CREATE SCOPE statement) Applying a scope definition (APPLY SCOPE statement) Releasing a scope definition (RELEASE SCOPE statement) Deleting a user scope (DROP SCOPE statement) 3.1.3 Defining optimization information for added definitions (SET STATISTICS statement) Precaution when altering a database definition 3.1.4 Altering privilege information Adding privilege information (GRANT statement) Deleting privilege information (REVOKE statement) Adding a role definition (CREATE ROLE statement) Altering role privilege information (GRANT statement) Deleting a role definition (DROP ROLE statement) Deleting/removing role privileges (REVOKE statement) 3.2 Deleting a Database [Figure: Database deletion procedure] Deleting temporary tables Deleting temporary table indexes Deleting scopes Deleting index storage structures Deleting table storage structures Deleting procedure routines Deleting function routine definitions Deleting triggers Deleting views Deleting tables Deleting sequences Deleting schemas Deleting database spaces Page Chapter 4 Storage Structure 4.1 Features of Table Storage Structures 4.1.1 SEQUENTIAL structure [Figure: Overview of SEQUENTIAL structure] SEQUENTIAL structure features for data processing patterns SEQUENTIAL structure page size specification When an index must be added to a SEQUENTIAL structure 4.1.2 RANDOM structure [Figure: Overview of RANDOM structure] RANDOM structure features for data processing patterns RANDOM structure page size specification When an index must be added to a RANDOM structure RANDOM structure data storage position specification [Figure: Overview of data storage method] Page Page [Figure: Overview of OBJECT structure] OBJECT structure page size specification 4.2 Features of the Index Storage Structure 4.2.1 BTREE structure Page Page BTREE structure features for data processing patterns BTREE structure page size specification Considerations when adding indexes 4.3 Allocating Space 4.3.1 Formats for associating storage structures and database spaces Format 1 [Figure: Database space sharing among components] Format 2 [Figure: Multi-database space components] Format 3 [Figure: Mixed database space configuration] 4.3.2 Considerations when allocating space 4.4 Estimating the Required Amount of Database Space Page Page Page Page Page Page Page Page Key compression rate criteria Example of estimating required space for each storage structure Page Page Page Page Page Page Page Page Page Page Page Page Page Page Page Page Page Page Page Appendix D Environment Variables Environment variables specified at compilation and link-editing of application programs Environment variables specified at execution of application programs Operating environment tuning priority Page Page Page Page [Table: Standard shell procedures] Notes on using RDB commands Handling of uppercase and lowercase letters Handling characters that have special meaning in the shell Handling of reserved words in SQL Page Appendix F Handling SymfoWARE/RDB Messages When executing an RDB command When compiling an application program (C language) When compiling an application program (COBOL) When executing an application program Page Page Appendix G Exclusive Control between Application Programs and RDB Commands [Table: Locked resources and locked strength for each SymfoWARE/RDB function] Page Page Page Page Page Page Page Glossary Access Application program Base table BTREE structure Client Client/server model Cluster key Column Column attribute Connection Cursor Cursor SQL statement Data manipulation SQL Data part Database space Deadlock DEFAULT clause DELETE statement DSO Esql Excel Exception condition Exclusive control External routine Hash structure Host variable Index Index definition Index part Log group Logical structure Logical structure definition Lotus 1-2-3 M host Non-cursor SQL statement NOT NULL constraint NULL Number of key values that differ OBJECT structure Optimization information Overflow part Overflow pointer Page Page split Physical structure definition Pointer variable Primary key Prime part Private sort work area Raw device RDB configuration parameter RDB configuration parameter file RDB dictionary RDB library Relational database Remote database Remote database access - service (RDA-SV) Role Rollback Row Row identifier Scalable log operation Schema Schema definition Server Shared buffer pool Shared buffers Shared memory Shared sort work area Split condition Split key Split value Split table operation SQL Storage data Storage structure Storage structure definition Structure host variable System table Table declaration Table name Temporary table Transaction Trigger definition UPDATE statement Upgrade View definition WHERE clause Work table