This chapter covers topics of general relevance to using the Empress Query Language:
In a relational database it is possible to extract data from several tables at once. In most cases, this kind of operation, called a join, is done with tables that are logically related to each other through common attributes. For example, the tables personnel and loans are logically related through their name attributes. This means that given a name, a search of the loans and personnel tables using this attribute can show both the personnel number of an employee and what loans are currently outstanding.
It is possible to join tables that are logically unrelated. For instance, selecting from two tables without using a WHERE clause joins every record of the first table with every record of the second. The interpretation of the results is up to you. This section discusses joining tables using related attributes.
When joining tables on related attributes a join condition is formed by comparing the attributes with the equality (=) operator. When equating attributes in a join, the attributes must be of the same data type. It is not necessary, however, that the attributes have the same name. The attribute name in personnel could have been called employees, without affecting its logical connection to name in loans. Similarly, the fact that two attributes have the same name does not necessarily mean that a join can be performed on them (e.g., name in personnel and name in customers).
If you are not careful, join operations can be quite time-consuming, as the speed of a join operation depends on the way the selection is specified. This usually does not matter, particularly for day-to-day use, but can be important when designing joins to be used in applications.
The WHERE clause in a join will generally restrict the records chosen from each of the tables and link the records from tables. For the repairs database, a typical join could be:
SELECT name, credit_limit, amount FROM personnel, loans
WHERE number = 10
AND personnel.name = loans.name;
In this case, the condition number = 10 limits the set of records selected from personnel while the condition personnel.name = loans.name links records of the two tables.
Approach of Empress' to a join operation can be visualized as follows:
Note that for each record of the first table, the selection of a record from the second table is done as if each clause mentioning an attribute of the first were in fact a comparison with a constant. Similarly, selection from the third table occurs as if each mention of attributes from the first and second were constants.
In our example, when a selection has been made from personnel according to the personnel number, the selection from loans will be made using the condition that loans.name be equal to the personnel.name values taken from the personnel record. Since these have already been narrowed down to a single value (Kilroy, whose personnel number is 10), this severely limits the number of loans records that must be considered in the join.
In the previous example, had loans occurred before personnel where the two tables are listed in the first line of the command, no conditions would have been specified in the first selection. The result of this would have been that each of the records from loans would have been tested against every record in personnel to find the ones in both tables, and then this set of records checked through to find the ones with the right personnel number. With personnel specified before loans, the records of the loans table are only tested against the record already selected from personnel.
A join is most efficient if the tables are ordered so that the largest number of records are filtered out as early in the join process as possible. In the example above, specifying personnel first filters out all but one record of personnel.
In the relational database management system, an index on an attribute is a mechanism to speed up data retrieval. When you are looking for a particular record, an index can serve as a logical pointer to the data's physical location. Just like an index in a book, indices helps you to quickly find the information you want to read. However, you have to create the index in order for Empress to use it. Once the index is created, it is up to Empress to decide whether or not to use the index for each query you submit. For example, to create an index on the attribute name of the personnel table:
CREATE INDEX personnel_name ON personnel(name);
where personnel_name is an index name. With Empress, a SELECT command will not always use the indices on the attributes of the table from which records are selected. First, an indexed attribute must be referenced in the WHERE clause for its index to be used. Second, the attribute must be appropriately situated in the WHERE clause. For example:
SELECT * FROM personnel WHERE credit_limit > 500;
The above query will not use the index on the personnel.name. The Empress system variable MSQUERYPLAN (when set) provides an analysis of the query with regard to the indices used and to how joins were performed.
To drop the index on personnel.name, type in:
DROP INDEX personnel_name;
In Empress, you can create a virtual table based on one or more physical tables. This virtual table is called a view. A view is like a movable window, through which you can see data. It is a virtual table because it does not exist as a real table in the database. It exists as a definition of the SELECT statement in the data dictionary. For example, to query information on the loans table as well as the credit limit information of the person (in the personnel table), you need to type:
SELECT name, date, amount, credit_limit
FROM personnel, loan
WHERE personnel.name = loans.name;
The query involves a lot of typing, but if you create a view called loansinfo:
CREATE VIEW loansinfo
AS SELECT name, date, amount, credit_limit
FROM personnel, loans
WHERE personnel.name = loans.name;
And do:
SELECT * FROM loansinfo;
this will achieve the same result. Empress will create a virtual table called loansinfo and the information will be retrieved based on the information in the personnel and loans tables as you query the loansinfo. To drop the definition of the view, use:
DROP VIEW loansinfo;
In this example, the real tables personnel and loans underlying a view are called base tables. A complex view is a view with more than one base table. Empress allows you to operate on a view as if it is a real table; however, some update operations on a complex view can violate the integrity of the base tables. In the following sections, we will discuss conditions under which Empress will permit update type operations (delete, update or insert).
This is a discussion of the conditions under which Empress will permit update type operations on complex views. A view is a virtual table. It is a structure that appears as a table, though no data is stored in it. A view is defined through a SELECT command applied to one or more tables; selecting from the view retrieves the same data from the underlying tables as the SELECT command that defines the view. These underlying tables may themselves be virtual, that is, views on other tables. We shall call the real tables underlying a view its base tables. A complex view is a view with more than one base table. By update type operation, we mean a delete, update or insert operation.
The conditions allowing update type operations on views fall into three general categories:
In general, a view must satisfy certain conditions in relation to its base tables in order for any update type operations to be allowable on it. To see why, consider a view V1 created from a join of tables T1 with attributes a and b, and T2 with attributes c and d.
T1(a,b) T2(c,d) V1(a,b,c,d) = SELECT FROM T1,T
WHERE T1.a = T2.c
0 0 0 2 0 0 0 2
1 1 0 3 0 0 0 3
If the record V1(0 0 0 2) were deleted from V1, records T1(0,0) and T2(0,2) should be deleted from the base tables, with the following results:
T1(a,b) T2(c,d) V1(a,b,c,d) = SELECT FROM T1,T
WHERE T1.a = T2.c
1 1 0 3
Deleting one record from V1 would result in removing two records from the view.
Note that this outcome is data dependent, in this case the inclusion of record T1(0,0) in two records of the view. In general, when a view includes more than one record containing data from a given record in a base table, deleting any of those records will remove all of them from the view. This effect is peculiar to views and does not occur with tables. To prevent views from having this characteristic to distinguish them from tables, Empress will not allow update type operations on views that do not satisfy the following Basic Condition:
| Basic Condition | To allow update type operations on a view, the view should satisfy the condition that any record from a base table can, in principle, appear at most once in the view. |
Three rules to determine whether a view satisfies the basic condition are given below. These rules concern the join conditions that determine the structure of the view, as expressed in the WHERE clause used to define the view, and the attributes involved in the join.
The WHERE clause used to define the view must be limited to AND conjunctions of a limited set of conditions. The set of conditions is limited to the following:
This rule means that if you wish to do update type operations on a view, the WHERE clause that defines the view cannot contain:
This rule assures that the next rule is not compromised.
There must be sufficient limiting conditions in the WHERE clause of the view to prevent a record of any base table from occurring more than once in the view. In effect, this means that there must be sufficient conditions of equality between attributes of the base tables.
A technique for determining whether the rule is satisfied is to represent each base table as a point, and a condition of equality joining two tables as a line between the respective points. If it is possible to move from any point to any other point by traveling along lines (crossing intermediate points if necessary), then the rule is satisfied.
To illustrate the rule, consider the tables T1(a,b,c), T2(d,e), T3(f,g,h) and T4(i,j). The following joins satisfy the rule. Using the line and dot representation, it is possible to move among all points along lines. Note that it is not important which attribute from each table is involved in each condition.
SELECT .... FROM T1, T2, T3, T4
WHERE T1.a = T2.d AND T2.e = T3.h AND T3.g = T4.i
SELECT .... FROM T1, T2, T3, T4
WHERE T1.a = T2.d AND T2.e = T3.h AND T2.d = T4.i
SELECT .... FROM T1, T2, T3, T4
WHERE T1.a = T2.d AND T1.b = T3.h AND T1.c = T4.i
The following joins do not satisfy the rule. Using the line and dot
representation, it is not possible to move among all points along lines.
SELECT .... FROM T1, T2, T3, T4
WHERE T1.a = T2.d
SELECT .... FROM T1, T2, T3, T4
WHERE T1.a = T2.d AND T2.e = T4.i
SELECT .... FROM T1, T2, T3, T4
WHERE T1.a = T2.d AND T3.h = T4.i
An adjunct to this rule is that a table cannot be selected twice in the same
view. That is, if a table is joined to itself to form a view, no update type
operations will be permitted on the view.
For every permutation of base table pairs, the set of attributes from the first table that are involved in equality comparisons with attributes of the second table must include a key (unique index). That is, there must either be a unique index on one of the attributes or a unique composite index on a subset of them. (In the case of a composite index, it cannot involve attributes not part of the WHERE clause of the join.) This rule is also necessary to prevent two records in a table being combined with the same record in another. Refer back to the first example:
T1(a,b) T2(c,d) V1(a,b,c,d) = SELECT FROM T1,T2
WHERE T1.a = T2.c
0 0 0 2 0 0 0 2
1 1 0 3 0 0 0 3
This rule demands that unique indices exist on T1.a and T2.c. Table T2 cannot satisfy the rule; if T2 did not have the record (0 3) it could satisfy the rule, and consequently the view would not contain the duplicate contribution from T1(0,0).
A technique for determining whether this rule is satisfied is to pair each table with every table to which it is joined, and for each pair list the attributes of the first table involved in equality comparisons with the second table. Either one of the attributes in each list must have a unique index or several of the attributes must have a unique composite index.
For instance, consider the tables T1(a,b,c), T2(d,e) and T3(f,g,h) and the following three joins:
SELECT .... FROM T1, T2, T3
WHERE T1.a = T2.d and T2.d = T3.f
We can compile the lists:
T1 to T2: T1.a T2 to T1: T2.d T2 to T3: T2.d T3 to T2: T3.f
Unique indices must exist for T1.a, T2.d and T3.f.
SELECT .... FROM T1, T2, T3
WHERE T1.a = T2.d AND T2.e = T3.f
We can compile the lists:
T1 to T2: T1.a T2 to T1: T2.d T2 to T3: T2.e T3 to T2: T3.f
Unique indices must exist for T1.a, T2.d, T2.e and T3.f.
SELECT .... FROM T1, T2, T3
WHERE T1.a = T3.f AND T2.e = T3.g
AND T2.d = T3.f
We can compile the lists:
T1 to T3: T1.a T2 to T3: T2.e, T2.d T3 to T1: T3.f T3 to T2: T3.g, T3.f
Unique indices must exist for T1.a and T3.f. Either a unique index must exist for T2.d or T2.e, or there must be a composite unique index on T2.d and T2.e.
Empress permits records to be deleted from views that satisfy the basic condition as expressed in the three basic rules. To permit update and insert operations, additional conditions must be satisfied.
Consider again tables T1(a,b) and T2(c,d), with a view V1 defined as:
CREATE VIEW V1 AS SELECT FROM T1, T2
WHERE T1.a = T2.c;
Suppose also that there are unique indices on T1.a and T2.c, satisfying Rule Three. The view V1 then satisfies all the basic rules given above.
T1(a,b) T2(c,d) V1(a,b,c,d) = SELECT FROM T1,T2
WHERE T1.a = T2.c
0 0 0 2 0 0 0 2
1 1
Suppose you wished to update the record in V1 to (0, 0, 3, 2). If this were permitted the record would no longer be included in the view, as it would violate the view definition. In effect, an update operation would appear to delete a record. This effect is caused by the inclusion in the view of two attributes that are equated in the WHERE clause.
Again to prevent views from having a characteristic distinguishing them from tables, Empress does not allow an update operation on such views.
When joining tables to form a view, equating pairs of attributes from base tables can result in sets of attributes from two or more tables specified to be equal.
The First Additional Condition states that:
| First Additional Condition | For every set of attributes in the base tables that must be equal, only one attribute from each set may appear in the view. |
When the view is updated, every attribute in such a set is assigned the value specified for the attribute that does appear.
Views that violate this condition can give rise to a further problem.
Consider again tables T1(a,b) and T2(c,d), with unique indices on T1.a and T2.c, and a view V1 defined as:
CREATE VIEW V1 AS SELECT b, c, d FROM T1, T2
WHERE T1.a = T1.b AND T1.a = T2.c;
With data:
T1(a,b) T2(c,d) V1(b,c,d) = SELECT b, c, d FROM T1,T2
WHERE T1.a = T1.b AND T1.a = T2.c
0 0 0 2 0 0 2
1 1
An attempt to update the record in V1 to (0, 1, 2) gives rise to the ambiguity: the WHERE clause defining the view equates attribute T1.a to both T1.b and T2.c; when these two attributes are assigned different values, which value should be assigned to T1.a? The condition that restricts the view to containing only one of a set of attributes specified to be equal prevents this ambiguity from occurring.
Empress permits records to be updated in views that satisfy the first additional condition as well as the basic condition.
To specify attributes as equal in the WHERE clause of a view they must be of the same data type. The attributes may, however, be defined with different data type parameters, and thus be capable of storing numbers of different sizes or different types of character data. Data that is input to a view will not be truncated to fit any attribute. If the data cannot be inserted into any of the attributes the operation will not proceed and an error message will be printed.
If conversion errors are generated for any of the attributes in the base tables, the operation will not proceed and an error message will be printed.
The second additional condition concerns attributes that are defined not null or that have unique indices on them. Since such attributes may not have null values inserted for them, they must either appear in the view or be equal to an attribute that does appear in the view if insert operations are to be permitted on the view. This is because any attribute of a base table that does not appear in the view will have a null value inserted for it if a record is inserted into the view.
The Second Additional Condition states that:
| Second Additional Condition | Any NOT NULL attributes in any base tables must be in the view or equal to an attribute in the view. |
Empress permits records to be inserted into views that satisfy the first and second additional conditions as well as the basic condition.
If data to be updated or inserted into a view conflict with a unique index constraint on a base table, and the record in the base table is identical to the record that would be inserted into the table, the existing record is taken to satisfy the operation.
Consider the view V1 defined as:
CREATE VIEW V1 AS SELECT a, b, d FROM T1, T2
WHERE T1.a = T2.c;
With data:
T1(a,b) T2(c,d) V1(a,b,d) = SELECT a, b, d FROM T1, T2
WHERE T1.a = T2.c
0 0 0 0 0 0 0
1 1
There are unique indices on T1.a and T2.c, so records can be inserted in the view. If the record (1, 1, 1) is inserted into the view, the result is:
T1(a,b) T2(c,d) V1(a,b,d) = SELECT a, b, d FROM T1, T2
WHERE T1.a = T2.c
0 0 0 0 0 0 0
1 1 1 1 1 1 1
The record (1, 1) to be inserted into T2 is identical with the second record already in T2. Since there is a unique index on T2.c a second (1, 1) record cannot be inserted into the table. The existing (1, 1) is taken to satisfy the insert operation on T2. The insertion into T1 proceeds normally.
Views can be defined to give access to data residing in several databases which may reside on different file systems or nodes. If the views are properly defined to allow update type operations, they are functionally indistinguishable from tables in the default database. This section presents a simple example of a view that illustrates how to access distributed data.
First create two databases:
empmkdb /usr/db1 empmkdb /usr2/db2
Invoke empsql on one of the databases:
empsql /usr/db1
Now create a table in each database:
CREATE tab1 (a1, b1); CREATE "/usr2/db2":tab2 (a2, b2);
You can now create a view of db2:tab2 in database db1:
CREATE VIEW tab2 AS SELECT FROM "/usr2/db2":tab2;
Note that in Windows NT environment, Backslash (\) convention also works for Windows NT (i.e., using \usr\db1 instead of /usr/db1)when addressing the path.
The view db1:tab2 is functionally indistinguishable from the table db2:tab2 insofar as selects, inserts, updates and deletes are concerned provided they have the same user access privileges associated with them.
You can now create a view tab12 that gives access to both
tab1 and tab2.
Access to tab2 can be through either the table
db2:tab2 or the view db1:tab2. In
this example we will use the view
CREATE VIEW tab12 AS SELECT tab1.a1, tab1.b1,tab2.b2
FROM tab1, tab2 WHERE tab1.a1 = tab2.a2;
This view is a join of tab1 and tab2 with sufficient join conditions to allow update type operations on the view. However, to allow insert operations, the attributes of the join condition must have unique indices:
CREATE UNIQUE INDEX ON tab1.a1; CREATE UNIQUE INDEX ON "/usr2/db2":tab2.a2;
Note that the index has to be placed on the table tab2, not the view (indices cannot be placed on views).
The view tab12 can now be manipulated like an ordinary table:
INSERT tab12 VALUES (1,2,3,4,5,6,7,8,9); SELECT FROM tab12; a1 b1 b2 1 2 3 4 5 6 7 8 9 SELECT FROM tab1; a1 b1 1 2 4 5 7 8 SELECT FROM tab2; a2 b2 1 3 4 6 7 9 DELETE FROM tab12 WHERE a1 = 1; SELECT FROM tab12; a1 b1 b2 4 5 6 7 8 9
A subquery is a SELECT statement that is nested inside the WHERE clause of another statement. In a WHERE clause, a subquery may appear on the right side of a comparison. A subquery is:
| [ | |ANY ||ALL |
|] (select_command) | |
A simple form of WHERE clause using this would be:
| WHERE attr [NOT] | | = |!= | < |<= | > |>= |IN |
| [ | | | | | | |
|ANY |ALL |
|] (select_command) | |
If ANY is used, the boolean expression is true if the comparison is true for at least one value returned by the nested select. The boolean expression is false if the comparison is false for every value returned by the nested select, or if the nested select returns no values.
If ALL is used, the boolean expression is true if the comparison is true for every value returned by the nested select, or if the nested select returns no values. The result is false if the comparison is false for at least one value returned by the nested select.
A subquery may be used to produce a list of values to be compared with, as in:
SELECT ... WHERE attr [NOT] IN (SELECT attr...);
In addition, a boolean expression in a WHERE clause may consist of:
EXISTS subquery_command
When EXISTS subquery is used, the WHERE clause is true if one or more records are selected in the SELECT command.
Subqueries can be used in WHERE clauses in UPDATE and DELETE statements as well as SELECT statements.
The following WHERE clause finds all loans less than the average loan amount over all loans, by using a nested select statement to find the average.
SELECT FROM loans
WHERE amount < (SELECT AVG (amount) FROM loans);
It produces:
name date amount Jones 7 February 1992 $33.95 Wladislaw 27 February 1992 $55.00 Jones 3 April 1992 $25.00 Wladislaw 12 May 1992 $25.00 Peterson 6 June 1992 $50.00 Wladislaw 25 June 1992 $75.00
The next command retrieves loans to people listed in the personnel table, illustrating the use of ANY.
SELECT FROM loans WHERE name = ANY (SELECT name FROM personnel);
It produces:
name date amount Mosca 2 February 1992 $150.00 Jones 7 February 1992 $33.95 Kilroy 16 February 1992 $250.00 Wladislaw 27 February 1992 $55.00 Jones 3 April 1992 $25.00 Mosca 4 May 1992 $200.00 Wladislaw 12 May 1992 $25.00 Peterson 6 June 1992 $50.00 Wladislaw 25 June 1992 $75.00 Jones 12 August 1992 $300.00 Scarlatti 9 September 1992 $150.00 Scarlatti 14 October 1992 $125.00
An equivalent command, illustrating the use of IN is:
SELECT FROM loans WHERE name IN (SELECT name FROM personnel);
It produces:
name date amount Mosca 2 February 1992 $150.00 Jones 7 February 1992 $33.95 Kilroy 16 February 1992 $250.00 Wladislaw 27 February 1992 $55.00 Jones 3 April 1992 $25.00 Mosca 4 May 1992 $200.00 Wladislaw 12 May 1992 $25.00 Peterson 6 June 1992 $50.00 Wladislaw 25 June 1992 $75.00 Jones 12 August 1992 $300.00 Scarlatti 9 September 1992 $150.00 Scarlatti 14 October 1992 $125.00
To find those loans that exceed everyone's credit limits:
SELECT FROM loans WHERE amount >
ALL (SELECT credit_limit FROM personnel);
It produces:
name date amount
There are no loans that large.
DATE and TIME attributes may have arithmetic performed on them, or have their year, month, day, etc. extracted from them. The following keywords may be used in expressions involving dates or times, or simply in expressions by themselves, in which case their results are converted to integers:
year years yearof month months monthof week weeks dayof day days hourof hour hours minuteof minute minutes secondof second seconds weekof
The yearof, monthof and dayof operators produce integers; years are 0 or greater, months range from 1 to 12, weeks from 1 to 52, and days from 1 to the number of days in the month. hourof, minuteof, and secondof also produce integers; hours range from 0 to 23, minutes and seconds from 0 to 59.
Note that trying to find hourof, minuteof, or secondof a DATE rather than a TIME attribute will give zero as the result.
Note also that when supplying a date to an expression, it must either be in universal date format (yyyymmdd) or correspond to the day, month, year order specified by the date picture variable MSDATEPIC, in order to be recognized (for more information on the MSDATEPIC variable, refer to the Empress: Database Administrator's Guide).
The following examples illustrate date arithmetic.
SELECT date + 2 months CONVERT TO DATE (1) FROM loans;
It produces:
convert (date + (2 months)) to date
2 April 1992
7 April 1992
16 April 1992
27 April 1992
3 June 1992
4 July 1992
12 July 1992
6 August 1992
25 August 1992
12 October 1992
9 November 1992
14 December 1992
The following uses the dayof operator:
SELECT dayof date FROM loans;
It produces:
(dayof date)
2
7
16
27
3
4
12
6
25
12
9
14
Adding two complete dates does not make sense, but subtracting them does. The result is the interval in days between them. To find how old each of the loans is, taking the current date as Jan 1, 1992, and assuming MSDATEPIC has its default value of "dd aaaaaaaaa yyyy", the command is:
SELECT name, "31 Dec 1992" - date FROM loans;
Empress displays:
name ("31 Dec 1992" - date)
Mosca 333
Jones 328
Kilroy 319
Wladislaw 308
Jones 272
Mosca 241
Wladislaw 233
Peterson 208
Wladislaw 189
Jones 141
Scarlatti 113
Scarlatti 78
The value before years, months, days, etc. may be an expression, as long as the expression evaluates to an integer.
The following will generate a heading "outstanding" for the expression.
SELECT name, '31 Dec 1992' - date PRINT "outstanding" FROM loans;
Empress displays:
name outstanding Mosca 333 Jones 328 Kilroy 319 Wladislaw 308 Jones 272 Mosca 241 Wladislaw 233 Peterson 208 Wladislaw 189 Jones 141 Scarlatti 113 Scarlatti 78
Values resulting from expressions are implicitly converted to the most appropriate generic data type in a Query Language SELECT command. A generic data type is a data type without explicit parameter definitions (size, etc.). When this type is not what you need, you may also explicitly convert the data to a specific type. In fact, this is generally the most convenient way to produce nicely formatted output.
The general form for explicitly converting an expression to a data type is either one of the following:
CONVERT expr [TO] data_type expr CONVERT TO data_type
where data_type is one of the standard data types (e.g., CHAR (40,1), DATE(3), SHORTINTEGER, DOLLAR (4,2), etc.), or the keyword GENERIC followed by CHAR, INTEGER, DECIMAL, FLOAT, DATE, or EXTERNAL. GENERIC EXTERNAL prints values as though they were all character strings.
Note that conversions may be nested.
To give some examples, the following command:
SELECT amount, amount * 1.05 CONVERT TO DOLLAR(4,1)
PRINT newamount FROM loans;
produces:
amount newamount $150.00 $157.50 $33.95 $35.64 $250.00 $262.50 $55.00 $57.75 $25.00 $26.25 $200.00 $210.00 $25.00 $26.25 $50.00 $52.50 $75.00 $78.75 $300.00 $315.00 $150.00 $157.50 $125.00 $131.25
The command:
SELECT date, date + 6 months
CONVERT TO DATE(1)
PRINT Õdue dateÕ WIDTH 20
FROM loans WHERE name = 'Jones';
produces:
date due date
7 February 1992 7 August 1992
3 April 1992 3 October 1992
12 August 1992 12 February 1993
The next example calculates simple interest at 24% per annum on all loans more than three months old on August 1, 1992. This requires finding the interval in days between the date the loan was made and the August 1 date. The application must then check whether it is more than three months: if it is, the number of days is multiplied by 0.24/365 to find the interest, then this is converted to a dollar amount. With the date picture variable MSDATEPIC set to its default value of "dd aaaaaaaaa yyyy", the command is:
SELECT date, amount,
(amount * ("1 August 1992" - date) * 0.24 / 365)
CONVERT TO DOLLAR(4,1)
PRINT interest FROM loans
WHERE "1 August 1992" > (date + 3 months);
and produces:
date amount interest
2 February 1992 $150.00 $17.85
7 February 1992 $33.95 $3.92
16 February 1992 $250.00 $27.45
27 February 1992 $55.00 $5.64
3 April 1992 $25.00 $1.97
The person creating a database is the Database Administrator. The Database Administrator (as DBA) has the power to grant any privilege on any table in the database. When the database is created, Empress also creates a data dictionary which containing system tables. By default, the Database Administrator is given USER privileges (delete, display, insert, select, update) on the system tables in the data dictionary, with grant authorization. This means that the Database Administrator can exercise these privileges on the tables in the data dictionary, and can grant these privileges to others. Note that the Database Administrator can always grant ALL privileges on the system tables to anyone including himself or herself at any time; however, the Database Administrator should never need to exercise the ALTER, DROP, or EMPTY privileges on the data dictionary tables.
In order to create new tables, a user must have insert privilege in the sys_tables table in the data dictionary. Since only the Database Administrator has this privilege by default (it is one of the USER privileges), initially only the Database Administrator can create tables in the database. To allow someone else to create tables, the Database Administrator must grant insert privilege on sys_tables to the other person, preferably as DBA (the grantor). Granting privileges as the Database Administrator rather than as a user means that if the Database Administrator changes, the new Database Administrator can easily identify (and revoke, if necessary) privileges previously granted.
When someone creates a table they become the Creator of the table. The Creator (as CREATOR) has the power to grant any privilege on that table. The Creator of each table is named in the sys_tables table in the data dictionary. The Database Administrator or anyone with sufficient privileges can always change the value of CREATOR of any table.
When a table is created, certain privileges are granted on that table to the Creator. These are defaults specified by the administrative variable MSDBPRIVS in tabzero. The default specification is to grant the CREATOR the DBA type privileges and the USER type privileges to the the user name (ie. the system User ID as USER) who created the table. Note that if no privileges were granted by default, the DBA of the database and CREATOR of the table could still grant themselves or anyone else any privilege on the table.
If the user who created the table wants to allow others to access the table, the user (who creates the table) must grant them or everybody (as the PUBLIC) the appropriate privileges. Where privileges are granted with the GRANT OPTION, the privileges may also be granted to other users by the grantee; however the DBA and CREATOR retain ultimate control over privileges on the table since they may revoke any privileges granted; in this way they may revoke any privilege on the table as any grantor who granted the privilege. This aspect of the Database Administrator's control extends to every table in the database.
Empress uses the file permissions that are set by the underlying operating system of each database. In order to access a table in a Empress database, a user must have both the appropriate operating system file permissions and the appropriate database privileges.
A transaction is a set of Query Language commands that are treated as a unit. When a transaction is in progress, all operation on all databases accessed during the transaction are treated as part of the transaction. Transactions are useful for two reasons:
Dividing work-sessions into transactions gives greater flexibility and security in manipulating databases. If commands in a transaction produce unsatisfactory results, they may be undone and tried again. Thus, a major user error, such as emptying the wrong table, is reduced to the minor annoyance of re-running a couple of commands.
A transaction is:
Also:
Table 3-1 Transaction Commands
| Command | Description |
| START WORK; | Initiates a transaction. |
| SAVEPOINT savepoint; | Defines a save point. |
| ROLLBACK WORK TO savepoint; | Rolls back to a save point. |
| DISPLAY WORK; | Displays status of current transaction. |
| ROLLBACK WORK; | Rolls back and end the transaction. |
| COMMIT WORK; | Commits and end the transaction. |
For detail syntax information of the above transaction commands, please refer to the Empress SQL: Reference manual.
Transactions can be nested by marking save points to which you have the option of rolling back. Once in a transaction, you can set a save point in the transaction with the SAVEPOINT command. This starts a nested transaction. In the nested transaction you can issue a ROLLBACK WORK command naming the save point. This rolls back all commands after the save point, leaving the original transaction in the state it was in when you started the nested transaction.
There is no practical limit to the length of transactions or to how deeply transactions can be nested. Within nested transactions, rollbacks can occur to any previously named save point, or the entire transaction can be rolled back.
Transactions cannot be committed in stages. The COMMIT WORK command will always commit all commands in the transaction.
It is possible to have Empress treat each Query Language command as a transaction, committing it only when the next command is successfully entered. With this feature, called automatic transactions, your last command may be canceled by issuing a ROLLBACK WORK command before you issue another command. Invalid commands or any of the executive commands CHANGE, EDIT, HELP, HOLD, RECALL, SET, STORE, or UNSET do not commit the previous command or start a new transaction. A RUN command may or may not commit the transaction, depending on what the previous command was. If it was a command from the above list, the transaction is not committed. If it was anything else, the transaction is committed and a new transaction started.
To enable automatic per-command transactions, set the Empress system variable MSQLTRANSACTION:
SET MSQLTRANSACTION TO X;
To disable automatic transactions, unset MSQLTRANSACTION and then issue a COMMIT WORK or ROLLBACK WORK command to commit or cancel your last command:
UNSET MSQLTRANSACTION; COMMIT WORK;
or the following:
UNSET MSQLTRANSACTION; ROLLBACK WORK;
It is advisable to have locking set at the table level (see the LOCK LEVEL command in the Empress: SQL Reference) during a transaction. If the Empress system variable MSTRANSTABLELOCK is set, and some level of locking has been set on the target table with the LOCK LEVEL command, then locking is automatically set to table level for any tables involved in a transaction for the duration of the transaction. This ensures that successive reads of a given table will show the same records in the same order.
This variable can be set by:
SET MSTRANSTABLELOCK TO X;
and unset by:
UNSET MSTRANSTABLELOCK;
Table locking for the duration of a transaction is effective during automatic transactions as well. This means that a table is locked from the time you type in a command to the time you type in the next command.
The command LOCK TABLE is also available for locking tables explicitly when in a transaction. Again, some level of locking has to be defined for the table for the LOCK TABLE command to have effect.
The Empress system variable MSTRANSCOMMENT can be used to place a comment on transactions. The value of MSTRANSCOMMENT will be displayed as the comment on the transaction by the DISPLAY WORK command.
Comments are useful when several people are running transactions on a database. If comments are used to identify the person running a transaction, This information will be available to the DISPLAY WORK command.
The Query Language DISPLAY WORK command displays the status of transactions. This section discusses the general use of the command. The full syntax for the command is presented in the Empress SQL: Reference.
There are three forms of the DISPLAY WORK command:
This command displays status of current transaction. It can only be used while in a transaction.
This command is used to identify transactions in progress and determine their status in the database. (Summary information is listed without the ALL option, full information is listed with the ALL option.) Typed after a system failure, it will list the identification code of all unresolved transactions affecting the database.
This command prints full status information on the transaction with the identification code id. The identification code of the transaction, optionally preceded by database:. If no database: prefix is specified, the current default database is assumed. In any case the command is a query on the status of the transaction on the database. (If the transaction does not affect the database, the transaction with the transaction code id will not be recognized, even if the identification code is valid.)
This form of the DISPLAY WORK command can be used to list full details of the transaction status, including status information on the coordinator and each participant, as well as the overall status of the transaction.
A transaction can affect tables in several databases. Although a transaction affecting several databases can normally be treated conceptually and operationally as a single transaction, Empress internally manages and coordinates a separate transaction on each database involved. To secure data integrity in this situation, transaction processing is coordinated using a two-phase commit protocol.
Empress recognizes transactions that span multiple databases and enforces the two-phase commit protocol to ensure consistent recovery over all the affected databases. The transaction you run is controlled as a unit. The DISPLAY WORK and warm restart facilities treat the transaction over all databases as a unit so that data integrity is preserved. Under normal circumstances you need not be aware of or concerned with the individual databases affected in a transaction.
If a transaction spans several databases, the first database accessed is identified as the coordinator for that transaction. The coordinator's significance is that it is the final arbiter as to whether the transaction can be committed or rolled back. The coordinator maintains status information on the progress of the transaction including any actions taken on the transaction after the system failure both for itself and for the other databases involved. The other databases involved in the transaction are known as participants.
The coordinator is fully informed of the status of the transaction at all times. If a warm restart is necessary, the coordinator can always determine whether the transaction is to be committed or rolled back. Most of the time a participant also has information on the status of the transaction, and can determine independently of the coordinator whether the transaction is to be committed or rolled back. However, there are moments in a transaction at which a participant will require access to the coordinator to determine the correct action.
When the warm restart utility is run on a database it may encounter several transactions involving the database that were in progress at the time of the system failure. For each transaction it encounters it must determine the correct course of action to be taken to recover the transaction on the database. For those transactions for which the database is the coordinator, the status of the transaction will be determined and any appropriate action taken. For those transactions for which the database is a participant, an attempt will be made to determine the status of the transaction but no action will be taken.
Warm restart facilities are provided to guarantee the security of your data if a transaction is interrupted due to any kind of system failure. In the event of failures such as a failure of the executing task, a failure of the operating system, or a disk failure, the warm restart facilities allow you to recover your databases to a consistent state, provided that database operations were carried out within transactions at the time of failure.
To be able to recover databases, the warm restart facilities require that no disk corruption has occurred. If physical data has been lost from a disk, use the emprecov utility to recover from an archived database.
When you run a transaction in Empress, you retain the option of ultimately committing all the operations in the transaction permanently to the database(s), or rolling them back. Should you be interrupted in the midst of a transaction, the transaction is unresolved and the database(s) may contain inconsistent or corrupted data. The warm restart facilities can be used to restart the affected databases and resolve the unresolved transactions. When a database is restarted, an interrupted transaction involving that database can either be completed or rolled back.
The automatic warm restart utility empwarm is described in the Empress: Database Administrator's Guide.
Manual overrides are provided for use in either of two circumstances:
If a transaction is valuable to you, because of time invested or effort expended, for example, you may wish to set save points in the transaction, so that in the event the transaction is interrupted you will have the alternative of recovering the transaction to a save point. In this case you can examine the status of the transaction, and if it would be rolled back by a warm restart, you can recover the transaction to a save point instead. See Recovering to a Save Point below.
If you need to resolve a transaction on a participant and communication cannot be established with the coordinator, you can force a resolution on the participant. In most cases the DISPLAY WORK command at the participant will tell you the correct resolution (commit or rollback). However, it can happen that a transaction spanning several databases is interrupted leaving a participant unable to determine whether to commit or roll the transaction back. This will be the case if the participant cannot independently determine the appropriate course of action, and if it also cannot access the coordinator to learn the appropriate course of action (recall that the coordinator can always determine whether the transaction should be committed or rolled back). In this case, you should either wait until communication is re-established or contact someone at the coordinator's site and have them use the DISPLAY WORK command to determine the correct course of action.
When you have determined the correct action to take to resolve the transaction, use either the COMMIT WORK (forced) or ROLLBACK WORK (forced) command described below to commit or rollback the transaction at the participant.
If you force a transaction to be committed or rolled back at a participant the status information for the participant is removed from the participant database. Normally the participant will inform the coordinator of the forced action. However, if the coordinator is not accessible it is not possible to inform it of the final status of the participant. Moreover, the coordinator will not be able to obtain it when communication is re-established. The coordinator will assess this situation as one in which disk corruption may have occurred, and the automatic warm restart utility will not take any action. It will therefore be necessary for you to use manual overrides to force the resolution of the transaction at the coordinator and all other participants. It will also be your responsibility to ensure that manual overrides are used in a consistent manner on all databases involved.
Manual overrides are effected using Query Language commands. These options should be used with caution and only in concert with all sites involved in the transaction. Careless or irresponsible use of manual overrides can result in inconsistent or incorrect data residing in your databases, since a forced commit or rollback at a participant can conflict with recovery procedures at the coordinator or other participants.
The following commands are used to identify transactions, display their status, and recover or resolve them. They can only be used when not currently in a transaction.
Table 3-2: Transaction Recovery Commands
| Command | Description |
| DISPLAY WORK ON ... | Displays all transactions in database; progress (that is, not completed) that involves the database. |
| DISPLAY WORK id; | Displays status of the transaction. |
| START WORK id FROM ... | Recovers the transaction by rolling savepoint; back to the save point. |
| ROLLBACK WORK id; | Force a rollback of the transaction. |
| COMMIT WORK id; | Force a commit of the transaction. |
If an unprotected transaction is interrupted before a commit command is issued and logged, the automatic warm restart utility will roll the transaction back. To prevent the loss of entire transactions, a means of recovering partially completed transactions using save points within the transaction is available. By setting save points in the transaction, you can recover the transaction to any save point after an interrupt. Recovering to a save point resolves the transaction; when a transaction has been recovered to a save point it will not be affected by subsequent execution of the warm restart utility.
If a transaction is unprotected, the recovery to a save point must occur before any execution of the warm restart utility. If a transaction is protected, the warm restart utility will ignore the transaction if it can be recovered to a save point (and the -p option to override protection is not used). The recovery to a save point can therefore occur after the warm restart utility is run.
A save point is set in a transaction with the command:
SAVEPOINT savepoint;
where savepoint is the name of a save point. It must be a string starting with a letter. Save points are also used to start a nested transaction within the current transaction.
A transaction is recovered to a save point with the command:
START WORK [database:]id [FROM savepoint];
where database is the name of a database. If no database is specified the current default database is assumed. Each transaction has an ID that can be determined using the DISPLAY WORK command described below. If no savepoint is specified, the transaction will be recovered to the last defined save point. A transaction with no save points cannot be recovered with this command.
You can use this command when you are not in a transaction. It restarts the transaction at the appropriate save point, and you can then continue the transaction.
Transactions can be protected from the automatic warm restart utility. The system variable MSTRANSWARMPROTECT is a flag that controls whether transactions will be acted on (resolved) by the automatic warm restart utility empwarm. If this variable is set to any value when a transaction is started, the transaction is protected from the warm restart utility, that is, the utility empwarm will ignore the transaction if it can be recovered to a save point. This feature is useful if the automatic warm restart is included as part of the system re-boot process. Since an unprotected transaction will certainly be rolled back unless a commit command has been issued, unprotected long transactions that are interrupted will tend to be rolled back. Protecting a transaction allows you to recover a transaction to a save point with the START WORK FROM savepoint command after the warm restart utility has been run.
The variable is not set by default, so the warm restart utility will resolve all interrupted transactions by default. To set the variable, do:
SET MSTRANSWARMPROTECT TO TRUE;
Locking prevents access conflicts and preserves data integrity by controlling concurrent accesses to a table. Every time Empress receives a command involving a table it attempts to determine the lock status of the table, which is stored either in live shared memory or in a file on disk. No other Empress process may access this lock manager while the lock level is being checked.
If an update is in progress, the lock manager will let no other operation proceed. If a read is in progress, another read can proceed. If Empress can proceed with the command, it marks the file with the kind of operation it is, and continues.
If Empress attempts to access a table and fails because the table is already locked, it will try again.
The number of attempts made and the interval between attempts, are controlled by the Empress system variables MSEXCLRETRY, MSIAEXCLSLEEP, MSLOCKRETRY, MSLOCKSLEEP, MSIAEXCLRETRY, MSIAEXCLSLEEP, MSIALOCKRETRY and MSIALOCKSLEEP. These variables provide sufficient control to avoid instances of a deadlock "hung'' system due to repeated attempts to access an inaccessible table.
MSEXCLRETRY determines the number of attempts Empress makes to read the lock. The default is 30 attempts. MSEXCLSLEEP determines the number of seconds between each attempt, and its default is one second. If you enter the Query Language command:
SET MSEXCLRETRY TO 5; SET MSEXCLSLEEP TO 2;
Then try to access a table whose lock is in use, Empress will make five attempts to read the lock at two-second intervals, for a total of eight seconds, before giving up.
MSLOCKRETRY determines the number of attempts made to write the lock once access to the lock has been achieved. For example, the table may be locked in update mode, which prevents other commands from writing the lock. The default is 30 retries.
MSLOCKSLEEP sets the number of seconds between attempts to write the lock, and its default is one second. If you enter the command:
SET MSLOCKRETRY TO 5; SET MSLOCKSLEEP TO 2;
Then try to write the lock for a table, Empress will make five attempts to do so at two-second intervals, for a total of eight seconds, before giving up.
MSIAEXCLRETRY determines the number of attempts Empress makes to read the lock when using the Interactive Interface. It will override the setting of MSIAEXCLRETRY. The default is two attempts. MSIAEXCLSLEEP determines the number of seconds between each attempt, and its default is one second. If you enter the Query Language command:
SET MSIAEXCLRETRY TO 3; SET MSIAEXCLSLEEP TO 3;
Then try to access a table using the Interactive Interface, Empress will make three attempts to read the lock at three-second intervals, for a total of six seconds, before giving up.
MSIALOCKRETRY determines the number of attempts made to write a lock for a table when using the Interactive Interface. It will override the setting of MSLOCKRETRY. The default is two attempts, since response should be fast in the Interactive Interface.
MSIALOCKSLEEP sets the number of seconds between attempts to write a lock in the Interactive Interface, and its default is one second. If you enter the command:
SET MSIALOCKRETRY TO 3; SET MSIALOCKSLEEP TO 3;
Then try to write a lock for a table, Empress will make three attempts to do so at three-second intervals, for a total of six seconds, before requesting further instructions.
During an interactive UPDATE, if the table cannot be accessed or Empress cannot start to find the necessary records, you may either retry or quit. Once the update has started, if a given record is inaccessible you may either retry, skip the record, or quit. During an interactive INSERT, you may either retry or quit.