Instruction/ maintenance manual of the product J2X0-2273-01EN Fujitsu
Go to page of 225
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.
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.
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.
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:.
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.
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.
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.
[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.
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.
[Figure: Relationship of logical, storage, and phy sical structures within databa ses] 1.2.1 Physical structure The physical structure consists of database spaces.
[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.
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.
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 .
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.
[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 foll.
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.
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.
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 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.
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 .
[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.
[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.
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.
[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.
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.
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.
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.
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.
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.
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.
· 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).
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.
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.
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.
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 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.
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.
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.
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.
- 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.
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 .
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.
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.
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 .
· 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.
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.
[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.
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 .
· 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.
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.
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.
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 .
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.
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.
· 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.
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 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.
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.
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.
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.
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.
[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 .
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.
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.
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.
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.
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.
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.
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 si.
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.
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.
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.
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.
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.
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.
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.
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 .
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.
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:.
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.
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.
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.
· 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 belo.
· Routine definition date and time · Routine comment definition · Privilege information (only if -p is specifie d) · Parameter information (parameter names, parameter types, modes) · Related tabl.
· 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 (DS.
[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 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.
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.
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 .
[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.
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) .
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.
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.
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.
[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.
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.
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.
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.
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.
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.
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.
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.
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.
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 .
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.
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 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: 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 Databas.
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.
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.
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.
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.
[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.
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.
[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 .
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.
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.
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.
[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 .
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.
[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.
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.
[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.
· 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.
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.
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.
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).
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.
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.
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: 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.
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).
[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 statem.
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: · Messa.
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.
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 .
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.
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.
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.
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.
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.
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 SQ.
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 .
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.
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.
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.
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.
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/.
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.
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.
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).
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.
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.
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.
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.
An important point after buying a device Fujitsu J2X0-2273-01EN (or even before the purchase) is to read its user manual. We should do this for several simple reasons:
If you have not bought Fujitsu J2X0-2273-01EN yet, this is a good time to familiarize yourself with the basic data on the product. First of all view first pages of the manual, you can find above. You should find there the most important technical data Fujitsu J2X0-2273-01EN - thus you can check whether the hardware meets your expectations. When delving into next pages of the user manual, Fujitsu J2X0-2273-01EN you will learn all the available features of the product, as well as information on its operation. The information that you get Fujitsu J2X0-2273-01EN will certainly help you make a decision on the purchase.
If you already are a holder of Fujitsu J2X0-2273-01EN, but have not read the manual yet, you should do it for the reasons described above. You will learn then if you properly used the available features, and whether you have not made any mistakes, which can shorten the lifetime Fujitsu J2X0-2273-01EN.
However, one of the most important roles played by the user manual is to help in solving problems with Fujitsu J2X0-2273-01EN. Almost always you will find there Troubleshooting, which are the most frequently occurring failures and malfunctions of the device Fujitsu J2X0-2273-01EN along with tips on how to solve them. Even if you fail to solve the problem, the manual will show you a further procedure – contact to the customer service center or the nearest service center
