Fujitsu J2X0-2273-01EN Bedienungsanleitung

J2X0-2273-01E N SymfoWARE(R)Server RDB User's Guide: Database Definition[...]

Preface Purpose This manual is a user's guide for S ymfoW ARE Server RDB. The manual explains ho w to create and define databases. Intended reader This manual is for users who design and define SymfoWARE/RDB databases. Readers should have the follo wing skills and knowledge: · A general understanding of SymfoW ARE/RDB functions and databases [...]

Reading this manual The purpose of this manual is to give r eaders a basic introduction to databases and their creation to make it easier to use SymfoW ARE/RDB. Unless otherwise noted, appl ication programs and SQL st atement in this manual are written in C. Title Not ation of Related Manual The table below lists the manuals related to this manual [...]

Besides the preceding manu als, SymfoW A RE provides an online manual. Command synt ax UNIX The man command is used to displa y the syntax of RDB commands. For details on the man command, refer to Ans w erBook2 of the Reference Manual Collection. The copyright of the online manual is the propert y of UNIX System Laboratories, Inc. and Fujitsu. Foll[...]

Wind o w s NT/2000/XP Action in re s p o n se to dis p l a y e d messa g e s is incl u d e d in the W ind o w s N T /2000 / XP o n l i ne help. Rel a ted m a nuals The related manuals are as fol lows: · Refer e nce M a nual C o l l ecti o n of An s w er B o o k 2 · Fujitsu COBOL User's Guide f o r Wind o w s · COBOL85 User's Guide · F[...]

July 2002 Microsoft, MS, MS-DOS, Windows, and Windows NT are trademarks or registered trademarks of Microsoft Corporation in the United St ates and other countries. v[...]

vi UNIX is a registered trademark in t he United St ates and other countries, licensed exc lusively thr ough X/Open Company Limited. Solaris is a trademark of Sun Micros ystems, Inc. in the United St ates. Lotus is a registered trademar k of Lotus Development Corporatio n. SymfoW AR E is a registered tr ademark of Fujitsu Limited. Other company and[...]

Chapter 1 SymfoW ARE/RDB Overview SymfoW AR E/RDB provides functions for creating a databa se, managing a database, an d manipulating database data. Before creating a database, the user must design the database structur e and d efine the database based on this database structure design s pecifications. Then, the user must generate the database base[...]

[Figure: SymfoW ARE/RDB functions configur ation] Functions for defining t able format s (database definition) T o create a database, first define the tabl e formats. 2.2 "Designing a Database," explains the kinds of formats used for tables. RDB commands are used to execute database d efiniti ons. For information about how to use actual R[...]

Functions for manipulating t a bles (t able manipulation) Data manipulation SQL st atements are used to insert, alter , delete, and refere nce data in tables. These SQL statements are used within application programs. For informa tion abo ut how to develop application pr ograms that use data manipulation SQL statements, refer to the "RDB User&[...]

[Figure: Relationship of logical, storage, and phy sical structures within databa ses] 1.2.1 Physical structure The physical structure consists of database spaces. Dat abase sp ace Under UNIX, a database space is defined on a ra w device created on a magnetic disk; un der Windows NT/2000/ XP , a database space is defined in a local file created on [...]

[Figure: Example of correspondenc e between schemas and dat abase sp aces] Base t able A base table consists of columns and ro ws. Figure: Base table format e xample is a base table format example. In this figure, one row consists of the data for one product. T he data of a single row consists of several columns. A column corresponds to a data item[...]

updated. Column constraint: A column constraint defines a constr aint 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 hav e the same value in the relevant column." T able constraint A table constraint enables the[...]

index for each column of a table. Multiple columns also can be combined and spec ified as a single index. Searching a column for which no index h as been created is less efficient than searching a column ha ving an index. Thus, an inde x must be created for a column used as a data search key . Ho wever , whenever an inde x is created, additional da[...]

1.2.3 Storage structure The storage structure consists of DSOs and DSIs. DSO A DSO defines the storage stru cture of the data for a base table. T he two types of DSOs are as follows: · T able · Index Ta b l e D S O A table DSO defines the type of stora g e structure for storing data, and, if data is subdivided for storage, the subdivision method.[...]

[Figure: Example in which DSOs and DSIs are associated in a 1:n corr espondence] As Figure: S torage structure c omponents shows, the four types of storage structures are SEQ UENTIAL, 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 structur[...]

[Figure: Example in which a dat abase sp ace is allocated to each component] 10[...]

[Figure: Example of allocating mult iple dat abase sp aces to increase the size of each compon ent] 1.3 Overview of Dat abase Creation T asks A SymfoWARE/RDB database can be created in one of the following two ways: · Using RDB commands · Creating a SQL-embedded program 11[...]

Using RDB commands The user can create databases by e xecuting RDB co mmands at the command p rompt of UNIX or Windo ws NT/2000/XP . The user can define databases b y specif ying the file containing vario us SQLs for definin g databases and using the rdbddlex command. The user can also create databases b y using the rdbsloader comm and. T his metho[...]

Chapter 2 Dat abase Creation This chapter covers proced ures ranging from the desi gn and creation of a SymfoWARE/RDB dat abase to database operation. 2.1 Overview of T asks From Dat abase Design T o Operation 2.2 Designing a Database 2.3 Creating a Database 2.4 Entering a Database Name 2.5 Creating a Database S pace 2.6 Defining a Logical Structur[...]

12. In preparation for using the database, save the database data. 13. Operate the database. Figure: Procedure from database design to operation shows the flow of tasks from database design to operation. 14[...]

[Figure: Procedure from dat abase design to operation] 15[...]

2.2 Designing a Dat abase T o create a database, first design t he database. When designing the database, be sure to careful ly analyze the business applications, t ypes and amounts of data to be processed, and data processing methods. The database design procedure incl udes steps for designing ta bles, attributes, simplified storage structures, an[...]

STOCK table: Contains information about products handle d and quantities of those products in stock ORDER table: Contains information related to products, quantitie s ordered, and purc hase prices for each customer . COMP ANY t ab le: Contains information about company names, te lephone n umbers, and addresses for each customer The usage examples u[...]

[Figure: Content s of the inventory management dat abase] 18[...]

19[...]

Column attributes of each t able of the inventory management database T able: Column attributes of each tabl e of the inventory manag ement database shows the column attributes of each table. [T able: Colu mn attributes of each t able of the inventory management dat abase] Relationship s among the STOCK t able, ORDER t able, and COMP ANY t able Fig[...]

[Figure: Relationship s among the STOCK t a ble, ORDER tab le, and COMP ANY t able] 2.3 Creating a Dat abase This section contains the following topics to explain ho w to create databases: · Defining a database by using the rdb ddlex command · Defining a database from an application prog ram 2.3.1 Defining a dat abase by using the rdbddlex comman[...]

Figure: Sample creation of a database from a definitio n file is a sample of database creation from a definition file . Figure: Sample definition file is a sample d efinition file. These figures are exampl es for Solaris. For Windows NT , c hange the input file specificati on in the rdbddle x command and the database space definition in the input f[...]

[Figure: Sample definition file] 23[...]

2.3.2 Defining the dat abase fr om an application program This section shows how to use d ynamic SQL statements to create a database. Logic al and storage structures can be defined from an application program. Reg i ster a database name and create a database space in advance by using the rdbddlex command becaus e these tasks cannot be ex ecuted fro[...]

CMDAREA3 are set up as SQL statement variables. T he programming language used is C. Figure: Sampl e application program definition is a sample of definition by an applicatio n program. [Figure: Sample application program definition] If a storage structure is simplified for database definition from an application program, one program co vers the st[...]

2.3.3 - Omitted - 2.4 Entering a Dat abase Name All logical structure definition s and st orage structure d efinitions belong to a given database environment. Log ical structure definitions are the schemas and tables to be creat ed. Such stor age structure definitio ns are the DSOs and DSIs. The user must enter the database name before defining the[...]

At the creation of a database spac e, a log environment can be allocate d for each database space. This section explains the relati onships between database space and magnetic disk, and the correspond ence between the database space and the log environment. 2.5.1 Creating a dat abase sp ace on a r aw device Under UNIX, a partition on a magnetic dis[...]

Raw device name S pecif y 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 s pecified raw device m ay be shared by another Sy mfoW ARE/RD B environment. After a required raw device is created, use the chown and chmod com mands to configure the access rights so that onl[...]

Dat abase sp ace name For the database name, specify up to 36 alphan umeric c haracters beginning with an alphabetic character . File name S pecif y 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 lo g environment can be associated with i t. The method o[...]

Schema definition Schemas are managed according to sch ema names. Multiple schemas can be cre ated for a single database. Define a schema using a CREA TE SCHEMA statement. A schema definit ion includes definitions of the base tables and views, the elements that form the schema. Sequence definition A sequence ca n be defined to automatically generat[...]

· Vie w t ab l es · T riggers · Procedure routines · Function routines Define the schema name using the schem a definit ion statement (CREA TE SCHEMA statement). A sample schema definition for the inventor y man agement database follows. The schema named STOCKS and the tables that belong to it, such as the STOCK t able, are defined for STOCKMN_[...]

index definition. Schema comment definition A comment consisting of a ch aracter stri ng can be specified for the schem a. A character string of up to 256 bytes can be specified. If no comment is necessary , omit the specification. An example follo ws. Example: S pecif y a comment for the STOCKS schema. 2.6.2 Sequence definition Define a sequence u[...]

statement that defines the STOCK t able. [Figure: CREA TE T ABLE st atement that defines the ST OCK t able] T able name S pecif y 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: V alid 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 colum n data type must be specified in a column definition. T he other items can be specified as requ[...]

Example 3: Let the data type of the PRODUCT column of the STOCK table be a 10-character variable lengt h character string. Example 4: Same definition as example 3 Example 5: Let the data type of the STOCKQTY column of the STOCK t able be a 10-digit exter nal decimal number with two digits to the right of the decimal point. Example 6: Let the data t[...]

Example 9: Let the data type of the STOCKQ TY column of the STOCK table be a double-precision appr oximate numeric value. 36[...]

[T able: Colu mn dat a types] 37[...]

[T able: T ime interval specifications] Default value A value can be specified as a default value for a column. S pecify a value to be set in the column if no value is specified w hen a row is inserted in the t able. The default s can b e specified w ith a cons tant, log in name (u nder UNIX) or logon name (under Windo ws NT/2000/XP), NULL, the cur[...]

NOT NULL constraint: S pecif y this constraint when NULL is not permitted as column data. S pecif y NOT NULL. Unique constraint: S pecif y this constraint when duplicate values are not permitted as column data. S pecify UNIQUE or PRIMARY KEY . The unique constraint is detailed lat er on. A sample column definition for the IT MNO column of the STOCK[...]

Example: Sample table constraint specification for a gr oup of columns The STOCK table for which the unique constraint of this exam ple has been specified c annot have rows such as [3] and [4] in Figure: Sample data that violates the uni que constr aint. Rows [3] and [4] in Figur e: Sample data that violates the unique constraint violat e the uniq [...]

T able comment definition A comment consisting of a character stri ng can be specified for a table. A char acter string of up to 256 bytes can be specified. If no comment is necessary , omit the specification. An exam ple follows. Example: S pecif y a character string comment for the STOCK table. 2.6.4 T able definition for multimedia dat a storage[...]

- Column name - Column comment definition · Query specification · T able (view) comment definition A sample view definiti on for the inventory management d atabase follows. This view definition defines a view consisting of the rows of the ITMNO and STOCKQTY columns of the ST OCK table for which ST OCKQTY is at least 50. Example: CREA TE VIEW stat[...]

S pecif y a comment for the NO column in the MASS_STOCK view . Query specification The query specification ind icates which po rtion of the base table forms the view . Example: Define the view named MASS_STOCK. Let the ITMNO and STOCKQTY columns of the STOCK t able be the NO and QTY columns of the view , respectively . T able (view) comment definit[...]

Up date-and-add trigger For a row added to a database using the I NSERT statement or updated using the UP DA TE statement, the user can use a trigger to automatically arrang e data of the row into columns. T hat is, a table upda ted by an SQL statement that causes the start of a trigger can be updated in processing of the triggered SQL st atement. [...]

Example 2: Up date-and-add trigger This trigger sets the differential value, variable date and time, and executor name if a stock quantity in the STOCK table decreases by 10 or more. Sample trigger definition Operation 45[...]

If this trigger is defined and an SQL statement that updates t he STOCK table is execut ed, the update operation specified in the triggered SQ L statem ent is automaticall y ex ecuted. T o define an updat e-and-add trigger , specify ROW_ID in the WHERE clause in the triggered SQL statement. Example 3: T rigger for calling a procedure routine When a[...]

Operation As described above, if a procedure rout ine a nd a trigger are defined when an SQ L statement that updates the table is executed, the procedure routi ne specified in the trigger ed SQ L statement is automatically executed to suppr ess any updating that may impair data consistency . The user can define a trigger t hat checks table for ever[...]

· Chained triggers cannot be executed by updating the triggered SQL statem ent specified by an update-and-add trigger . · If the trigger defined for the table updat ed by the tr iggered SQL st atement is a trigger other than an update-and-add trigger , a chain of triggers is executed. If a trigger is execut ed again as an extension of its own exe[...]

Procedure comment definition A comment consisting of a c haracter stri ng can be specified for a procedure rout ine. A character strin g 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 usin g the CREA TE FUNCTION statement. For details of th e func[...]

[Figure: Sto rage structure definition procedure] DSO definition The DSO definition specifies the type of storag e structure for storing data and rules such as whether to apply split table operation. The two types of DSO definitions are tabl e DSO definitions and index DSO definitions. T able DSO definition A table DSO definition specifies the type[...]

2.7.1 T able DSO definition Use the CREA TE DSO statement to specify a table DSO definition. Sample table DSO definitions follow . Example: T able DSO definitions · When split table operation is not app lie d (data structure: SEQUENTIAL) · When split table operation is not app lied (data structure: RANDOM) 51[...]

· When split table operation is appl ie d (data structure: SEQUENTIAL) · When split table operation is appl ied (data structure: RANDOM) DSO name For the DSO name, specify up to 36 alphanumeric char ac ters beginning wit h an alphabetic character . The DSO name must be unique within the database. T able name S pecif y the name of the base table c[...]

RANDOM When RANDOM is specified, added data is st ored in a random order . For RANDOM, use CLUSTER to specify a key for determining the data storage position. If CLUST ER is omitted, the data is stored on the basis of the arrangemen t of PRIMARY KEY in the table definition. For P AGESIZE1 and P AGESIZE2, specify the page sizes of the data structure[...]

Example 2: T able DSO definition with multiple co lumns as the split key 2.7.2 T able DSO definition fo r multimedia dat a storage This section explains ho w to specify a table DSO definiti on for storin g image or audio dat a. T his data is stored in a BLOB-type column. For storing data such as image and voice, the record leng th in a table may ex[...]

2.7.3 Index DSO definition Use the CREA TE DSO statement to specify an index DSO def inition. If a column is used for a cond itional search, define an index for the column used in the s earch condition to improve the search efficiency . If PRIMARY KEY or UNIQUE is specified in a table defin ition, an index DSO definition with the same column config[...]

DSO name For the DSO name, specify up to 36 alphan umeric char acters beginning with an alphab etic character . The DSO name must be unique within the database. Key specification S pecif y the table name for which the index is to be cr eated and the list of column names forming the i ndex. T able name S pecif y the name of the base table for which [...]

BTREE For P AGESIZE1, specify the page size of the data p art. For P AGESIZE2, specify the page size of the index part. For details, refer to "4 .2.1 BTREE S tructure." Base represent ation S pecif y the way the ind ex and base are ass ociated. If this specification is omitted, SEQUENTIAL or OBJECT default to ADDRESS , and RANDOM defaults[...]

· When split table operation is appl ied (data structure: RANDOM) · When the table data structure is object DSI name For the DSI name, specify up to 36 alphanumeric c haracters begi nning with an alphabetic character . The DSI name must be unique within the database. DSO name S pecif y the table DSO name given in a CREA TE DSO statement. With no [...]

question marks (?) specified in the split cond ition. The user cannot specify spl it values to have the storage destination of a g iven row (data) include multiple DSIs. T able: S pecification f ormats of constants that can be specified for split values sh ows the specification formats of constants that can be specified for split values. [T able: S[...]

[T able: S pecification formats of tim e interval types that can be specifi ed for split key values] Sp ace allocation S pecif y the physical space to be allocated to the table DSI. S pecif y the name of the database space wher e the data is to be ph ysically stored. In ad dition, specify the size of the storage area to be acquired fo r this DSI wi[...]

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 JAP AN DSI by splitting and storing ORDER table data according to CUSTOMER region. The ORDER table CUSTOMER numbers are divided by region with the ra nge 71 and 72 repr esenting c[...]

Create a television and refrigerator DSI by splitting a n d storing STOCK table dat a according to PRODUCT and WHCODE values. Set PRODUCT and WHCODE for the split condition, and specify multiple spl it values. Example 4: Create a DSI for products TELEVISION and REFRIGERA TOR for the STOCK table. Create a sales amount DSI by splitting and storin g S[...]

2.7.5 Index DSI definition Use the CREA TE DSI statement to specify a n index DSI definition. T he index DSI definition a llocates database space according to the index DSO definition. A new index DSI definitio n can be added for a table DSI in which data has already been stored. Example: Create an index DSI definition. 63[...]

DSI name For the database name, specify up to 36 alphanumer ic characters beginning with an alphabetic character . The DSI name must be unique within t he database. DSO name S pecif y the index DSO name given in a CREA T E DSO statement. T able DSI name S pecif y a DSI name indicating the corresponding base table. When split table operation is to b[...]

[Figure: Overview of DSI initialization] A DSI must be initialize d before data is stored. The rdbf mt command performs DSI initialization. Ho wever , when the rdbsloader command is used to store data, DSI initialization is unnecessa ry . In addition, if the DSI is associated with a shared buffer to improve perf o rmance, the rdbconbf command m ust[...]

with an alphabetic character . A scope name is unique i n a database. DSI name list S pecif y the DSI names of tables whose access range is lim ited 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 t[...]

A simplified storage structur e definition dynamically extends the DSI capacity . The prefix for naming, the dat a length, the allocation size, and the DSI ca pacity extension can be changed by using parameters in the operating environment file. For a mult imedia data storage table, the storage structure can be selected. For information about the o[...]

If DEF AUL T_DSI_NAME=CODE is specified in the system operati ng environment file, ho wever , table DSO and DSI are named with a 10-diit figur e determined by the system. Examples are given belo w . Example 1: When the schema and table names are character strings Example 2: If DEF AUL T_DSI_NAME=CODE is specified in the system operating envir onmen[...]

If DEF AUL T_DSI_TYPE is specified for the operating environme nt file, this specification has the same meaning as of the definition belo w . · At DEF AUL T_DSI_TYPE = SEQUENTIAL · At DEF AUL T_DSI_TYPE = OBJECT The table storage structure is defined as follo ws. S torage structur e: If the table format does not satisfy the conditions listed bel [...]

DEF AUL T_OBJECT _T ABLE_SIZE of the operating environm ent file. Reserve a sufficient value, taking into account the amount of data to be handled. For information about the storage structure, see 2.7 "D efining a S torage S tructure." 2.9.3 Index definition Use the CREA TE INDEX statem ent to define an inde x. In the index definition, sp[...]

Index DSO name and DSI name The index DSO and index D SI names are generated by co mbining the schema nam e and index name from the index definition. The DSO and DSI names are the same. If DEF AUL T_DSI_NAME=CODE is specified in the s ystem oper ating environm ent file, the system assigns 10-digit names for the index DSO and DSI names. An example f[...]

Index storage structure The index storage structure is as follo ws: S torage structur e: BTREE structure Data par t pag e siz e: 2 kilobytes Index pa rt page size: 2 kilobytes Data p art allocation size: 168 kilobytes Degeneration specificati on: Present Index part allocation size: 32 kilobytes Index comment definition A comment consisting of a cha[...]

A user can execute the follo wing INSERT statement to create a temporary table (T1) that contains necessary data: Incorporating processing resu lt s of a procedure routine in to an application program using a temporary t able Because a temporary table is created for o ne user , data ca n be passed between procedure r outines that are executed conse[...]

Processing results of a procedure routine for multipl e trans actions that outputs processing results in the same format can be stored in a temporary table (5) in ord er to pass the results to an application program (6). For more specific examples of use, refer to the " RDB User's Guide: Application Program Developme nt." When multip[...]

The user can specify wheth er to use a temporary table wi thin a s ession of the application progr am or within a transaction. S tored d ata is erased after the session or transaction terminates. The storage structure of a tem porary table is SEQUENT IAL. T he storage structure of the index of a temp orary table is BTREE. The storage structures are[...]

alphanumeric characters for a colum n name, whose first character must be an alphabetic character . Each column name within the table must be unique. Row deletion specification S pecif y when to delete a row in a temporar y table. If this argument is omitted, the tem porary table is assumed to be used within a transaction, and data stored in the te[...]

The index is created in the d atabase sp ace 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 inde x begin with TEMP . 2.1 1 Privilege Information Definition Use the GRANT statement to define privilege information. If a resourc e is defined, only the user who [...]

1) Define a role. Use the CREA TE ROLE stat ement to define a role. A sample role definition for defi ning role STOC KS_A2 follows. Example: 2) Specify the privileges to be granted in the role. Use the GRANT statement to specify the pr ivileges to be granted in the rol e. In t he defined role, specify the privi leges granted for accessing a table i[...]

3) Grant the role priv ileges 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 SA TO, SUZUKI, and T ANAKA follows. Example: T o enable the privileges specified with t he GRANT stateme nt in the defined role , execute [...]

status of the data in a database, for example, number of data item s, and base table and index DSI space requirements. Optimization information definition opportunity Optimization information must be determined in an ordin ary way , considering the number of records stored in a database and variations of the index ke y that will occur after the def[...]

Definition for each DSI Definition for each t able 81[...]

Definition for each index DSO Output of optimization information T o output the defined optimization info rmation, use the rdbddlex command to specify the PRINT ST A TIST ICS statement. For more information on the PRINT ST A T ISTICS statement, refer to the SQL Reference Guide. Examples of specificati on and output given below . Example 1: Output e[...]

Example 3: Output example of optim ization information define d 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 defi ned for each table (data structure: RANDOM) 84[...]

Example 6: Output example of optim ization information d efined for each index DSO 85[...]

2.13 Generating a Dat abase A database is generated by the ent r y of data in the base tables. Dat a is entered after the storage destination database spaces are created, and the def inition of logical structur es and storage structures fo r base tables and indexes is completed. Dat abase generation methods A database can be gener ated in the follo[...]

Generating a dat abase by using the rdbsloader command A sample for gener ating a database by using the rdbsloader command fol lows. UNIX Windows NT/2000/XP For information about the rdbsload er command, refer to "RDB Operations Guide." 87[...]

2.14 Referencing Dat abase Definition Information After the database is defined, verify t he database definition information. T his section explains ho w to print database definition information. The rdbprt command prints the database name list and definit ion information. The rdbddle x command with the PRINT ST A TISTICS statement or the rdbup s c[...]

· Schema comment definition · Privilege information (only when -p is specified) · Names of tables belonging to the schema · T able type (base, vie w , or temporary table) · Names of routines belonging to a schema · T ypes of routines belong ing to a schema · Names of triggers belonging to a schema · Names of sequences belongin g to a schema[...]

· Routine definition date and time · Routine comment definition · Privilege information (only if -p is specifie d) · 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 schem a to which the called routine belongs, an[...]

· Privilege information (only if -p is specifie d) 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) - A[...]

[Figure: Sample dat abase list print specification] Sample user name list print specification This example specifies printing a list of names of all users under the target SymfoW ARE/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 t[...]

[Figure: Sample user p arameter information print specification] 2.14.3 rdbprt command print format Figure: Sample database list print results shows a sample pr in tout for the rdbprt command specified in Fig ure: Sample database list print specification. Figure: Sample database output object specification and print results shows a sample printout [...]

Sample role name list print result s [Figure: Sample role name list print result s] Sample user p arameter information print result s [Figure: Sample user p arameter information print result s] Remarks: DEF AUL T_ROLE is not prin ted in user para mete r info rmation. It is printed in USER specificat ion in definiti on information. 94[...]

Sample dat abase output object specification and print result s [Figure: Sample dat abase output object specifi cation and print result s] 95[...]

Definition information output format for a DB specification [Figure: Definition information out put forma t for a DB specification] 96[...]

Definition information output fo rmat for a SCHEMA specification [Figure: Definition information outpu t format f or a SCHEMA specificat ion] 97[...]

Definition information output fo rm at for a T ABLE specification [Figure: Definition information outpu t format for a T ABLE specification] 98[...]

99[...]

[Figure: Vi ew information output format] 100[...]

Definition information output fo rmat for a ROUTINE specification [Figure: Definition information outpu t format for a ROUTINE specification] 101[...]

Definition information output format for a DSO specification [Figure: Definition information out put form at for a DSO specification] 102[...]

103[...]

Definition information output fo rmat for a DSI specification [Figure: Definition information out put forma t for a DSI specification] 104[...]

105[...]

Definition information output forma t for a DBSP ACE specification [Figure: Definition information outpu t format for a DBSP ACE specification] 106[...]

Definition information output fo rmat for a TRIGGER specification [Figure: Definition information outpu t format for a TRIGGER speci fication] 107[...]

108[...]

Definition information output fo rmat for a SCOPE specification [Figure: Definition information outpu t format for a SCOPE specification] 109[...]

Definition information output forma t of a SEQUENCE specification [Figure: Definition information outpu t format of a SEQUENCE specification] 1 10[...]

Definition information output fo rmat of a USER specification [Figure: Definition information outpu t format of a USER specification] 111[...]

Definition information output fo rmat of a ROLE specification [Figure: Definition information outpu t format of a ROLE specification] 1 12[...]

2.14.4 Printing privilege information T o print privilege information, specif y -p in the rdbprt command. Privilege information is printed onl y for the following specifications: · SCHEMA · T ABLE · ROUTINE · DBSP ACE · TRIGGER · SEQUENCE Figure: Sample privilege inform ation printout shows a sample command specificatio n and a sample printou[...]

[Figure: Sample privilege information printout] 1 14[...]

Chapter 3 Dat abase Definition Alterati on and Deletion A database can be used after it has been created. T o 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 Develop ment. After a database has been created, the us[...]

Defining optimization information for added definition If a table and index is added, define t he optim ization information for them. · Defining optimization information (SET ST A TISTICS statement) Altering privileges · Adding privilege informati on (GRANT statement) · Deleting privilege information (REVOKE statement) · Adding a role definitio[...]

Deleting a schema definition (DROP SCHEMA statement) T o delete a schema definition, us e the DROP SCHEMA statement. A specification example follo ws. Example: Delete a schema belonging t o STOCKMN_DB. When an attempt is made to delete a schema definiti on, if any of the follo wing definitions subordinate to that schema exist, the schema cannot be [...]

Adding a t able definition (CREA TE T ABLE statement) T o add a table definition to a schema, use the CREA T E T ABLE statement. A specification ex ample follo ws. For details about how to specify the CREA T E T ABLE statement, see 2.6 "Defining a Logical Structure." Example: Add a definition of the PRODUCT table to the schema named STOCK[...]

structure, data corresponding to one row in a table ma y exceed one page after a BLOB-type column is add ed. Only NOT NULL can be spec ified as a constraint for the col umn to be added. If NOT NULL is sp ecified, the DEF AUL T clause must be specified in the colum n definition. DEF AUL T values are set for existing data. A specification example for[...]

[Figure: Deleting a column from a t able] Adding a view definition (CREA TE VIEW statement) T o add a view definiti on to a previousl y defined schema, u se a CREA TE VIEW statement. A vie w definition cannot be altered. T o change a vie w definition, first delete the view definition and then add a new view definition. An example of adding a view d[...]

Deleting a trigger definition (DROP TRIGGER st atement) T o delete a trigger definition, use the DROP TRIGGER statement. Example: Delete trigger ORDER_TRIGGER. Adding a procedure routine defini tion (CREA TE PROCEDURE statement) T o add a procedure r outine, use the CREA TE PROCEDURE st atement. For details abo ut how to specify th e CREA TE PROCED[...]

Example: Defines function routine USER002 to schem a STOCKS. Deleting a function routine defini tion (DROP FUNCTION st atement) T o 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 procedur e routine or trigger , the function r outine cann ot be d[...]

Swapping a t able (SW AP T ABLE statement) The table name exchange in table swapping exc hanges the table's relationship with DSO. Use the SW AP T ABLE statement for table swapping. This produces the following effects: · Data can be transfered in a short time. · An application program nee d not be changed. · Previous views and routines can [...]

Deleting a t able DSO definition (DROP DSO st atement) T o delete a table DSO definition from a base table, use the DROP DSO statement. If an index DSO or a t able DSI has been defined for the DSO to be deleted, the relevant DS O definition cannot be del et ed. All related inde x DSO definitions or table DSI definitions must be deleted in advan ce.[...]

Adding a t able DSI definiti on (CREA TE DSI statement) T o add a table DSI definition for a base table for which the DSO has been defined, us e the CREA TE DSI statement. S pecif y each item of the CREA TE DSI statement in a similar manner as when defining the st orage structure. An example of adding a table DSI definition follo ws. For det ails a[...]

Adding an index DSI definiti on (CREA TE DSI statement) T o add an index DSI definitio n, use the CREA TE DSI statement. Changing a split key value of a DS I definition (AL TER DSI statement) T o change a split key val ue of a table DS I definition, use the AL TER DSI statem ent. If the table storage structure is SEQUENTIAL or RANDOM, the split key[...]

Changing the DSI storage range An example follows in which the numbe r of years of storage is changed for sales data having a three -year storage period. 127[...]

Adding a scope definition (CREA TE SCOPE statement) T o add a scope definition, use the CRE A TE SCOPE statement. An example of applying a scope definition follo ws. For more information on how to specify the CRE A TE SCOPE statement, see 2.7.7 "Scope definition." Example: Add scope OSAKA_SCOPE. Applying a scope definiti on (APPL Y SCOPE [...]

Releasing a scope definition (RELEASE SCOPE st atement) T o release a scope definition, use the RELEASE SCOPE stat ement. T his statement releases a scope definition that was applied to a user by the APPL Y SCOPE statement. The statements "RELEASE SCOPE" and "APPL Y SCOPE" must be executed by the same user . An example of using [...]

Precaution when altering a database definition After a database definition is altered, use the rd bprdic co mmand to conf irm the RDB dictionary utilization r ate and estimate the expansion point. This precautio n ensures sufficient RDB dictionary space. For information about estimating the RDB dic tionary expansion point, refer to "RDB Operat[...]

Example: Adds role STOCKS_A2. S pecif y the privileges to be granted in role STOCKS_A2. Grant the privileges of role STOCKS_A2 to users. Altering role privilege information (GRANT st atement) T o 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[...]

Example 2: Removes the role privileges from user T ANAKA. 3.2 Deleting a Dat abase The user deletes a database definition by pe rforming a seq uence of opera tions. Figure: Database deletion proc edure shows this procedure. 132[...]

[Figure: Dat abase deletion procedure] The following database is used in the exampl es in this section: Database: STOCKMN_DB Schema: STOCKS Sequence: SEQUENCE1 133[...]

T ables: STOCK, ORDER, and STOCK_TEMPORAR Y T ABLE T able DSO: ORDER_DSO T able DSIs: STOCK_DSI and WEST_ORDER_DSI Index: STOCK_TEMPORAR Y_I X Index DSO: PRODUCT_IXDSO Index DSI: PRODUCT_IXDSI Database spaces: DBSP ACE_1, DBSP ACE_2 Procedure routine: PROC002 Function routine: USER001 T rigger: ORDER_TRIGGER Scope: TOKYO_SCOPE Deleting temporary t [...]

Deleting index storage structures T o delete index storage structures, use the DROP DSI and DROP DSO statement s. Example: Delete the index DSI and index DSO belongi ng the STOCKMN_DB. When the DSI specified in the DROP DSI st atement is deleted, all data stored in the database sp ace allocated to that DSI becomes invalid. When the index storage st[...]

Deleting procedure routines T o delete a procedure rout ine, use the DROP PROCEDURE statement. When a procedure routine is delet ed, the privilege information for the proced ure is also deleted. Example: Delete PROC002 from the STOCKS schema. Deleting function routine definitions T o delete a function routin e, use the DROP FUNCTION stat ement. Whe[...]

Example: Delete the tables belonging to STOCKMN_DB. The tables specified in the DROP T ABLE statements are deleted. Deleting sequences T o delete a sequence, use the DROP SEQ UENCE statement. When a sequence is deleted, privileg e information of the sequence is also deleted. Example: Deletes a sequence belonging to the stock management database. De[...]

Deleting a dat abase T o delete the database name, use the DROP DA T ABASE statement. Example: Delete STOCKMN_DB. 138[...]

Chapter 4 S torage Structure From the application progr am viewpoint, database data is represented in table for mat. The application pr ogram manipula tes data a s if it were ma nipulating row s and co lumns of a table by using structured query la nguage (SQL) statements. The structure for storing data represented in table format on physical pages [...]

[Figure: Overvie w of SEQUENTIAL structur e] SEQUENTIAL structure features for dat a 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 i ndex exists. Thus, the I-O frequency depend[...]

When specifyin g 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 p age. · If a table including a BLOB-type column is defin ed or a BLOB-t ype column is added during the change of a table definition, a row of data in the table ma y exceed o[...]

[Figure: Overview of RANDOM s tructure] RANDOM structure features fo r dat a processing p atterns The factor having the greatest effect on data processi ng efficiency is the I- O frequency . The RAN DOM structure has the following features: · If all values of the cluster key are specified in condit ional expressions and a u nique constraint is set[...]

cases, the entire cluster key is not specifie d in the data processing. RANDOM structure p age size specification In a RANDOM structure, a prime part bucket is associated with a single page. An overflo w pa rt bucket is also independently associat ed with a single page. T he size of each of these pages can be i ndependently specified. The prime par[...]

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 (P AGESIZ E1) of the prime part, design the RULE formula to store four data items on each page. 144[...]

4.1.3 OBJECT structure An OBJECT structure stores BLOB-type data such as photograph in the order t hat the data is inserted. Figure: Overview of OBJECT structure shows an overview of the OBJECT structure, us ing the PRODPHOT t able as an example. 145[...]

[Figure: Overvie w of OBJECT struct ure] OBJECT structure p age size specification In an OBJECT structure, the page size is specified by the P AGESIZE option of the DSO definitio n. The specified page size must be 32. 4.2 Features of the Index Storage S tructure The index storage structure is the BT REE structure only . This section explains the fe[...]

keys and base table data, manages the pages where data is stored. The portio n consisting of pages in which data is stored is called the data part for the index p art. If ADDRESS is specified as the base expr ession for the DSO definition, the table record storage address is used as information corresponding to the bas e table data. If KEY is speci[...]

[Figure: Overview of BTREE s tructure ] 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 follo wing cases, the I-O frequency depends on the depth of the index par t. The range of index ke y values is specified, or only the values of the[...]

structures, each of these components is associate d with a database space. The user can us e one of the follo wing formats to make these associations: Format 1 This format associates multiple compone nts with the sa me database space, as shown in Figure: Database space sharing among components. This format reduces the number of database spaces to b[...]

[Figure: Multi-dat abase space component s] Format 3 This format combines the two previous formats, as shown in Figure: Mixed database space c onfigurati on. In this case, infrequently used overflo w p arts are c oncentrated in a single database space. [Figure: Mixed dat abase sp ace configuration] 4.3.2 Considerations when allocating space In a st[...]

· For a RANDOM structure Allocate the prime and overflo w part s 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 Dat abase Sp ace This section explains ho w to estimate the required am ount of database space for ea[...]

SEQUENTIAL structure 153[...]

RANDOM structure (when a unique constr aint is set for the cluster key) 154[...]

RANDOM structure (when a unique constrai nt is not set for the cluster key) 155[...]

OBJECT structure 156[...]

BTREE structure data p art 157[...]

158[...]

BTREE structure index p art 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. T hese factors are the data key values and the order of data additions and 160[...]

deletions. Similarly , the utilization rat es vary dependi ng o n these factors for the data and index parts of a BTREE structure and the overflo w rate of a RA NDOM structure. (T he overflow rate of a RANDOM structure is the r ate of overflow to the overflow part relative to the tot al numbe r of records.) When estimating the amounts of space need[...]

Example 2: Estimate of the required amount of space for a RANDOM structure Calculate the required am ount of sp ace for the ORDER table (total num ber of records: 30,000) having this kind of structure: The storage structure is designed as fol lows. The clus ter key is the PRIMA RY KEY (a unique constraint is set). Thus, the formulas for a RANDOM st[...]

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: S torage structur e: OBJECT structure Page size: 32 kilobytes Safety factor: 1.3 163[...]

Example 4: Estimate of the required amount of space for a BTREE structure Calculate the required amou nt of space when an index is added for the ORDER table having the following kind of structure. PRICE and ORDERQTY is used as the secondar y key for the index. T he ORDER table has a SEQUENTIAL struct ure, with a total of 30,000 reco rds. The table [...]

165[...]

166[...]

Estimating column length 167[...]

168[...]

Appendix A Quantit ative Restrictions T able: Quantitative limitations shows q uantitative restrictions on SymfoW ARE/RDB. [T able: Qua ntitativ e limitations] 169[...]

170[...]

171[...]

172[...]

Appendix B Sequential Relationshi p s among D efinition Changes Definition changes must follo w an order determine d by defin ition d ependencies. T able: Basic sequential relationships among addition-t ype definition changes (1/2) sho ws basic sequential r elationships among addition-t ype changes. T able: Basic sequential relations hips among add[...]

[T able: Basi c sequential relationship s am ong addition-type definition changes (1/2)] 174[...]

[T able: Basi c sequential relationship s am ong addition-type definition changes (2/2)] 175[...]

176[...]

Append i x C Operating Environment F i le Param eters This appendix lists parameters that can be specified in operating environm ent files and the files in which the parameters can be specified. T able: Opera ting environm ent file parameters sho ws the operating environment file parameters. [T able: Ope rating environment file p arameters] 177[...]

178[...]

179[...]

180[...]

Appendix D Environment V ariables At compilation, and link-editin g, and execution of applicat ion programs, the user can s pecify tuning the op erating environment with environment variabl es. For the specification formats and meanings of the environ ment variables, refer to the "RDB User's Guide: Ap plication Program Development." [...]

[T able: Environment variables specified upon execution of the application program and execu tion p arameters of the operating e nvironment file] 182[...]

Appendix E RDB Command Summary This appendix gives an overvi ew of RDB commands and functi ons. For more information about the syntax of the RDB commands, refer to the man command (under UNIX) or t he S ymfoW ARE/RDB online ma nual (under Windows NT). RDB Command Summar y A list of the RDB commands is as follo ws: 183[...]

[T able: RDB command list] 184[...]

St andard shell procedure T able: S tandard shell procedures lists t he standard shell procedures used und er UNIX. 185[...]

[T able: S tandard shell procedures ] Notes on using RDB commands Handling of uppercase and lowercase letters In RDB command specifications, lowercase letters are dist inguished from uppercase letters. In SQL, an ordinar y identifier (not enclosed in double quotes) is handled by converting lo wercase letters to the corresponding uppercas e letters.[...]

in quotes ('), or specify an escape character (¥) just before t he character . Example: Example of enclosing chara cter string in quotes (') (specifying DSI name containi ng # in SQL statement) Example: Example of specifyin g escape character (¥) ju st before character st ring (specifying DSI name containing @ in SQL statement) Some cha[...]

188[...]

Appendix F Handling Symfo W ARE/RDB Messa ges Refer to the online manu al "SymfoWARE/RDB Message Reference" for the user han dling of the follo wing SymfoW AR E/RDB output messages: · Message issued when an RDB command is executed · Message issued when an application progr am is compiled · Message set in the message variable (SQLMS G) [...]

When compiling an application program (COBOL) Example 3: Display a description of message 1 101 1 issu ed by the COBOL precompiler . When executing an application program Example 4: Display a description of mess age JYP2031E set in the me ssage variable (SQLMSG) when executi ng an application program. 190[...]

When SymfoW ARE/RDB has detected an internal inconsistency Example 5: Display a description of mess age qdg12695u i ssued when SymfoW ARE/RDB terminates abnormally on detecting an internal inconsistency . (Reference code: 16 .1.7.769) 191[...]

192[...]

Appendix G Exclusive Control between Applic ation Prog r ams and RDB Commands When an application program and RDB command o perate simultaneously on the same database resource, the SymfoW AR E/RDB system performs exclusive control. In this way , each process can be executed without contradiction. Each function performs processing by applying e xclu[...]

194[...]

195[...]

196[...]

197[...]

198[...]

199[...]

[T able: Relationship s among lock strengths] 200[...]

Glossary Access The operations of read ing data from a st orage device and writing data to a storage device. In this ma nual, reading data from a database and writing data to a database is called access. Application program Generally , programs used by users for th eir work are defin ed as application programs. I n this manual, programs that read d[...]

Client he operational unit that requests data proc essing in the client/server model. W hen a database is used according to the client/server model, the application pr ogram requesting acce ss to the server database operates in the client. Client/server model A model used in data processing in which the operatio nal unit (client) that req uests dat[...]

Connection Connection refers to the relationship that connects a c lient to a server . A connection is made by specifying a CONNECT statement in an application program. Converse ly , a DISCONNECT statement cuts of f the connecti on. Cursor A cursor is an indicat or that indicates a row to be manipulat ed. A cursor is defined b y a cursor declaratio[...]

Dat abase space An area that stores base tables an d indexes. Database space is created by physical structure definition in SymfoW ARE/RDB. Deadlock A stopped status that occurs when severa l transactions sh are use of a database. A deadlock is to a loop that occurs when several transactions wait for the same resource. Eac h transaction waits for a[...]

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 CO BOL 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 sp reads heet software that runs on a wo[...]

Hash structure An indexing method used to find quickl y the data of a base tabl e that matches a search condi tion. A has h structure is a storage structure that makes fast data manipulation possible as fo llows. A hash structure uses a has function to determine the storage location of data using the val ue of a data key . SymfoW ARE/RDB uses this [...]

Log group A log environm ent split unit is called a log gr oup. Each log f ile cons ists of a log managem ent file, temporar y log file, and archive log file. T here are two types of log groups: sy stem log groups unique in the default RD B system and multiple user log groups to be added and defined. Logical structure One of the structures of a dat[...]

Non-cursor SQL statement An SQL statement used for data manipulati on, the non-curso r SQL statement does not use a cursor to specif y rows to be manipulated. Instead, th e rows to be processed are s pecified in the se arch condition specifie d in the SQL statement. NOT NULL cons traint A constraint on a column of a table. Th is constraint prohibit[...]

Optimization information Information that is the basis for optimiz ation in SymfoWARE/RDB. Optimization information includes th e amount of base table data (number of rows), t he number of levels of inde xes, and the number of different key values. SymfoW AR E/RDB integrates and evaluat es SQL statement s and optimization in formation and determine[...]

Physical structure definition An element of a SymfoW ARE/RDB database definition for creating database spaces. Pointer variable A host variable declar ed as a pointer in an embedded C pr ogram. An area dynamical ly obtained using the malloc function or obtained outside an embedded SQL declare section can be specified in an SQL statement. Primary ke[...]

Raw device A disk area that can be acc essed regardless of the UNI X fi le system is called a r aw device. The ra w device is a character-type special device created us ing the UNIX utilit y . It is ordinarily indic ated by a node nam e created under /dev/rdsk/. RDB configuration p arameter Information, such as where the RDB director y file is loca[...]

that obtained the data lock. Related terms: procedure routine, function routine Relational dat abase Database used in SymfoWARE/RDB that represents data using two-dimensional tables consisting of rows and columns. Database operations are perfo rmed using the SQL dat abase language. Remote dat abase When a database is distributed into multiple serve[...]

Row A row is one of the compone nts 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 uniquel y identified. A user can manipulate a row using the row identifier fetched by the single row SELECT statement or by using the FETCH statem[...]

Related terms: RANDOM structure Server The operational unit that exec utes data processing in the client/server mo del. When a database is used according to the client/server model, the dat abase operates in the server . Shared buffer pool A buffer for accessing a database (also called a shared b uffe r). Because data can be shared by multiple appl[...]

Split condition When a split table operation is applied, the rule for dividin g data into split units is called the split condition. The split condition specifies a list of column names representing split ke ys and a list of dummy values in which "?" is specified. A split condition is specified i n table DSO definition. Related terms: S p[...]

an SQL statement is executed , the st atus code for the execution result is stored in the status variable. Storage dat a Data that is stored in a database space. The rows and columns of tables repr esent the logica l aspect of data, and stored data represents the ph ysical aspect of data. Storage stru cture A database structure along with lo gical [...]

T able declaration A table declaration declar es the schema in which a table is located. If a table declaration is specified, the schema name need not be sp ecified for a table in data manipulat ion statements. Using a table declaration simplifies the specification of table names. Using a t able declaration also helps to make an applicatio n progra[...]

Related term: Reference mode UPDA TE st atement The UPDA TE statement is a SQL data manipulation statement used to update data in table rows. Upgrade Index update processin g. In conjunction with data update, insertion, and deletion process ing in a table, this process updates indexes attached to that table to reflect the late st st atus. This proc[...]