J2X0-2273-01EN SymfoWARE(R)Server RDB User's Guide: Database Definition
Preface Purpose This manual is a user's guide for SymfoWARE Server RDB. The manual explains how to create and define databases. Intended reader This manual is for users who design and define SymfoWARE/RDB databases.
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.
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.
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.
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.
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.
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.
[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 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.
[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.
[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.
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.
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.
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.
[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: Example in which a database space is allocated to each component] 10
[Figure: Example of allocating multiple database spaces to increase the size of each compon ent] 1.
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.
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.
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.
[Figure: Procedure from database design to operation] 15
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.
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.
[Figure: Contents of the inventory management database] 18
19
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.
[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.
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.
[Figure: Sample definition file] 23
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.
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.
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.
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.
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.
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.
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.
· 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.
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.
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.
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.
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 9: Let the data type of the STOCKQTY column of the STOCK table be a double-precision approximate numeric value.
[Table: Column data types] 37
[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.
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.
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.
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.
- 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.
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.
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.
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.
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.
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.
· 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.
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.
[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.
2.7.1 Table DSO definition Use the CREATE DSO statement to specify a table DSO definition. Sample table DSO definitions follow.
· 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.
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.
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.
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.
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.
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.
· 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.
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 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.
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.
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.
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.
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.
[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.
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.
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.
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.
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.
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 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.
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 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)).
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.
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.
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.
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.
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.
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.
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.
Definition for each DSI Definition for each table 81
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 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
Example 5: Output example of optimization information defined for each table (data structure: RANDOM) 84
Example 6: Output example of optimization information defined for each index DSO 85
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.
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.
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.
· · · · · · · · 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
· · · · · · · · · · 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 defini
· 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
[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 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.
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.
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 format for a SCHEMA specification [Figure: Definition information output format for a SCHEMA specification] 97
Definition information output form at for a TABLE specification [Figure: Definition information output format for a TABLE specification] 98
99
[Figure: View information output format] 100
Definition information output format for a ROUTINE specification [Figure: Definition information output format for a ROUTINE specification] 101
Definition information output format for a DSO specification [Figure: Definition information output format for a DSO specification] 102
103
Definition information output format for a DSI specification [Figure: Definition information output format for a DSI specification] 104
105
Definition information output format for a DBSPACE specification [Figure: Definition information output format for a DBSPACE specification] 106
Definition information output format for a TRIGGER specification [Figure: Definition information output format for a TRIGGER specification] 107
108
Definition information output format for a SCOPE specification [Figure: Definition information output format for a SCOPE specification] 109
Definition information output format of a SEQUENCE specification [Figure: Definition information output format of a SEQUENCE specification] 110
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
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).
[Figure: Sample privilege information printout] 114
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.
Defining optimization information for added definition If a table and index is added, define the optimization information for them.
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.
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.
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: 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 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.
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.
[Figure: Database deletion procedure] The following database is used in the examples in this section: Database: STOCKMN_DB Schema: STOCKS Sequence: SEQUENCE1 133
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 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.
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.
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.
Deleting a database To delete the database name, use the DROP DATABASE statement. Example: Delete STOCKMN_DB.
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.
[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.
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.
[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.
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.
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.
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.
[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.
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.
[Figure: Overview of BTREE structure] 148
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.
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: 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.
· 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.
SEQUENTIAL structure 153
RANDOM structure (when a unique constraint is set for the cluster key) 154
RANDOM structure (when a unique constraint is not set for the cluster key) 155
OBJECT structure 156
BTREE structure data part 157
158
BTREE structure index part 159
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.
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.
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.
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.
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.
165
166
Estimating column length 167
168
Appendix A Quantitative Restrictions Table: Quantitative limitations shows quantitative restrictions on SymfoWARE/RDB.
170
171
172
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.
[Table: Basic sequential relationships among addition-type definition changes (1/2)] 174
[Table: Basic sequential relationships among addition-type definition changes (2/2)] 175
176
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.
178
179
180
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.
[Table: Environment variables specified upon execution of the application program and execu tion parameters of the operating environment file] 182
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).
[Table: RDB command list] 184
Standard shell procedure Table: Standard shell procedures lists the standard shell procedures used under UNIX.
[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.
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.
188
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: Displ
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.
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.
192
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.
194
195
196
197
198
199
[Table: Relationships among lock strengths] 200
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
