This document covers the following topics:
In spite of SQL standardization by SQL-89 and SQL-92, there are still considerable differences in the extent of the SQL language between the SQL systems available on the market. For the portability of an application program, essential parts (e.g., the most important return messages to be dealt with by the application program) have not yet been sufficiently taken into account by the standardizations currently available.
To provide more options to our users, Adabas D supports the most important SQL dialects by selecting an SQLMODE:
The SQLMODE determines the extent of the SQL language, the structure and significance of SQLCA and SQLDA, data types supported, as well as the numbering of the most important return messages.
When using one of the SQLMODEs, application systems which were written for another SQL system can be ported to Adabas D with a small effort.
The SQLMODE can be set for each session.
This is the most attractive SQLMODE because it is the most powerful. Facilities exceeding the standard and performance spectrum of other SQL systems are only available in this SQLMODE. For the user, this means maximum productivity combined with optimal performance.
For users who want to achieve the highest possible portability of their applications, the restriction to the entry level of SQL 92 can be selected with this SQLMODE. Adabas D will then only accept SQL statements which conform to the ANSI standard.
The fulfillment of the ANSI standard by Adabas D was assured by the United States National Institute of Standards and Technology (NIST) test suite.
This option is particularly interesting for software companies which have to make their products available on various relational database systems and for whom the standardized facilities suffice.
Adabas D supports the following data types:
For every SERVERDB, a default code, ASCII or EBCDIC, is specified which does not have to agree with the machine code. This default can then be overridden in each table column, as required. It is thus possible, e.g., to store all data on an ASCII computer in an EBCDIC coding. This may be desirable to avoid differing sorting sequences in a client-server configuration for CHAR values with upper and lower cases.
For transparent storage, there is, apart from ASCII and EBCDIC, the CHAR BYTE. In client-server configurations, contents from CHAR (N) BYTE columns are not converted implicitly into the code of the client.
The internal storage of CHAR values is performed up to a length of 30 in a fixed format. Larger CHAR values are stored implicitly internally in a representation of varying length. By explicitly using the data type VARCHAR, even shorter CHAR values can be represented internally using varying length.
Often only the states "available / not available" or "true / not true" have to be distinguished for attributes. This can be done by CHAR(1) columns which have been set to appropriate values. But the embedding in a programming language such as C/C++ or Cobol is simplified by providing a Boolean data type. It is easier to formulate WHERE qualifications with Boolean columns.
Here, an internal format is stored as YYYYMMDD. The external representation can be configured according to the usual conventions in Europe, North America, or in the Pacific region.
TIME values are kept internally in the form HHHHMMSS. As in the case of DATE values, it is possible to configure for different external representation. DATE and TIME values are special CHAR values. This means that, apart from the date and time functions, all CHAR functions can be applied to them.
TIMESTAMP values are kept internally in the form
They are a combination of a DATE and TIME value extended by the specification of milliseconds and microseconds. In addition to their usage as a timestamp, it is also convenient to use them for time estimating, because then overflows in the day's portion need not be handled by the user. TIMESTAMP values are special CHAR values. This means that, apart from the date and time functions, all CHAR functions can be applied to them.
For numeric values, a decimal fixed point representation with a maximum of 18 digits is provided.
The data type SERIAL is provided as an extension of the data type FIXED (N). Starting with a start value to be defined ascending positive integer numbers are inserted.
Apart from the fixed point representation by FIXED, a decimal floating point representation is available with a maximum precision of 18 places and a value range of 10
For storing non-formatted data (BLOB), Adabas D provides the data type LONG which can accept data of up to 2.1 GB per column. As with CHAR columns, the variants ASCII, EBCDIC, and BYTE are supported. Adabas D is thus in a position to manage extensive text, image, and voice information in an appropriate way.
The additional data types occurring in other SQLMODEs are understood by Adabas D and mapped onto those described above.
To support application programming with non-formatted data sets (texts, graphics, voice, images), Adabas D provides the data type LONG.
LONG columns are specified in the usual way when defining a table (CREATE TABLE). They are completely subject to the transaction concept, the authorization, and the dynamic memory management of Adabas D. For each table, several LONG columns can be created.
Within the SQL statements INSERT, UPDATE, DELETE, and SELECT or FETCH, LONG columns can only be operated as containers. This means that in the application program, a host variable that is sufficiently long must be defined in which the entire content of the LONG column can be read in or out.
LONG columns can grow dynamically and be created, updated, and deleted during database operation. Saving and restoring LONG columns is done with the usual Adabas D backup and recovery utilities.
The integrity of data is supported in Adabas D by declarations using powerful DDL statements and options.
Integrity rules for tables:
is the primary key table. Adabas D ensures the uniqueness of the primary key.
is a set of columns whose values uniquely identify a row in a table.
is a mandatory column. Adabas D ensures that this column is always set to values.
assigns a default value specific to a column.
The CHECK definition checks the row of the table affected by INSERT and UPDATE. In a CHECK rule, a WHERE condition can be formulated for this row which has to be satisfied.
Referential integrity constraints between tables are expressed by specifying the foreign key (inclusive ON DELETE).
In addition to value lists or value ranges, the CHECK rule allows complex conditions to be formulated which can also refer to other columns in the same row.
To be able to formulate extensive business data models with Adabas D, domain definitions are supported as well as table definitions. Domains are user-defined data types which can be used in CREATE TABLE statements instead of the predefined data types (CHAR, FIXED, FLOAT, ...). By using domains, integrity rules can be defined on the level of data elements and their use in the definition of tables helps in modelling the data uniformly and consistently.
CREATE DOMAIN itemno CHAR(12) CHECK LIKE '????.???.???' CREATE TABLE item( itno itemno, itname CHAR(30) NOT NULL entrydate DATE NOT NULL, purchaseprice FIXED(6,2) NOT NULL CHECK purchaseprice > 0, sellprice FIXED(8,2) NOT NULL CHECK sellprice > purchaseprice, PRIMARY KEY (itno))
Data integrity in Adabas D can be ensured in most cases by means of declarative integrity rules. Only in exceptional cases is it necessary to formulate procedural triggers to monitor highly complex integrity conditions. In general, the use of declarative rules has advantages over a procedural formulation via triggers because Adabas D ensures, when an integrity rule is modified, that the existing data set corresponds to the new integrity rule. Such a check cannot be carried out for procedurally formulated rules.
In a Adabas D application with a modular structure, the SQL statements are typically not distributed over the entire application but are concentrated in a single access layer. This access layer has a procedural interface with the rest of the application at which the typical operations for application objects are made available.
For example, insert, update, delete, and select operations are realized on the application object CUSTOMER where the rest of the application does not need to know the number of tables that represent this application object and which integrity checks are performed when modifying customer information.
___________________________ | | | | | Application | Client | | | | |_________________________| | | | SQL access layer | |_________________________| | | _____________|_____________ | | | | | | | Adabas D kernel | Server | | | | |_________________________|
In client-server configurations, there is an interaction between client and server when executing any SQL statement in the access layer.
The number of these interactions can be drastically reduced when the SQL access layer is no longer run in the client but in the server. Adabas D provides a language for this purpose which allows an SQL access layer to be formulated on the server side.
___________________________ | | | | | Application | Client | | | | |_________________________| | | _____________|_____________ | | | SQL access layer | |_________________________| | | | | | | | Adabas D kernel | Server | | | | |_________________________|
This has three important advantages:
The number of interactions between client server is reduced by several factors. Client-server communication is only required for each operation on the object layer, no longer for each SQL statement. This enhances the performance of client-server configurations considerably.
The second advantage has to do with software engineering. The SQL access layer contains the procedurally formulated integrity and business rules. The concentration of these rules on the server side and their elimination from applications means that these rules can be updated centrally so that they become immediately effective in all applications. In this way, these integrity and decision rules also become part of the database catalog.
An SQL access layer relocated to the server side also allows for creating client-specific database functionality and is thus an important customizing tool.
All suppliers of SQL DBMS are moving technically in the direction of supporting DB procedures and triggers. But with respect to the individual offerings a very close look must be taken at the procedural completeness of the DB procedure language (goal: full programming language) and of the testability of new DB procedures (goal: test environment independent of the DB kernel, preferably 4GL level). In Adabas D, as an additional feature, the code of a DB procedure may run optionally on the client or on the server. This simplifies the development and testability decisively, and characterizes the DB procedure basically as centralized application code.
With the exception of the data type LONG, data types supported by Adabas D can be used in DB procedures for input/output parameters. It is also possible to pass the name of a cursor (i.e., of a result table) as input and output parameters. This allows a result to be selected in the form of a table within a DB procedure and it allows the result rows to be retrieved by a sequence of FETCH statements outside the DB procedure.
The author or owner of a DB procedure can authorize other Adabas D users to employ this procedure (EXECUTE privilege). This leads to an implicit privilege extension of the other users; i.e., they can execute operations on database objects with a DB procedure for which they are not privileged. In this way, it can be ensured that the manipulation of certain database objects is only possible with DB procedures and no longer with the SQL language.
Triggers are special DB procedures. They possess the same procedural power but are not called explicitly; they run implicitly as a consequence of an INSERT, UPDATE or DELETE operation in a table. This provides a general user-exit mechanism which allows the user to formulate further arbitrary actions on a database in addition to the normal statement semantics. Among other things, triggers permit the formulation of complex integrity rules, the checking of complicated access protection conditions, and the execution of implicit database modifications.
In Adabas D, triggers are generally performed after the execution of the DML statement. Within the trigger, access is given to the before and after image of the table row by qualifying OLD or NEW for the corresponding column. This is an essential prerequisite for the creation of consistency or integrity rules which concern modifications of such a column.
In case of mass INSERT, UPDATE, or DELETE statements, one SQL statement modifies more than one table row. When doing so, it must be decided whether a trigger has to be activated for each table row concerned or only once for the SQL statement. Adabas D supports both options; statement-oriented triggers are formulated by a specific trigger locking.
Apart from a development environment and the testability of DB procedures and triggers, the SQL extensions described in the following in the form of temporary tables and subtransactions are required for their practical application.
Adabas D permits the user to define temporary tables which exist up to the end of the session. Temporary tables are implicitly deleted at the end of a database session.
CREATE TABLE TEMP.delivery (seq_no FIXED(5), customer CHAR(20), product CHAR(20), address CHAR(30), PRIMARY KEY(seq_no)) IGNORE ROLLBACK
Temporary tables are particularly suitable as auxiliary tables which are only required for the duration of an application execution. These tables have no overhead for catalog administration because they are user-specific. They can be operated optionally with or without logging (IGNORE ROLLBACK).
Especially in DB procedures, temporary tables can be used to create large intermediate results which can be accessed via SELECT and FETCH.
The usual transaction concept (COMMIT, ROLLBACK) ensures that related modifications of several data objects are performed completely or not at all.
When DB procedures are called from an application, the user would like to have the same behavior as for SQL statements. If it is successful, the DB procedure should perform all the database actions. If it is not successful, it should not leave any effect in the database. This can only be realized with the help of subtransactions. A DB procedure must not contain any COMMIT or ROLLBACK because otherwise it would intervene unexpectedly in the transaction control of the application program. To be able to reset the effects of a DB procedure (and only these effects) if there is an error, the DB procedure must be formulated as a subtransaction. For this purpose, Adabas D provides the commands SUBTRANS BEGIN, SUBTRANS END and SUBTRANS ROLLBACK. If it is successful, a DB procedure is concluded with SUBTRANS END. If error situations arise which require a resetting of the DB procedure's effects, this can be achieved by means of SUBTRANS ROLLBACK without this influencing the top-level transaction control of the application program. Since DB procedures can be called in a nested way, the nesting of subtransactions within subtransactions is also supported.
Apart from the use of subtransactions for programming DB procedures and triggers, they are also necessary to program library functions containing SQL statements with a proper error handling; that is, without influencing the transaction control of the main program which invoked the library function.
Apart from DB procedures and triggers, Adabas D also supports arrays as host variables to enhance performance in client-server configurations in the statements INSERT, UPDATE, DELETE, SELECT, and FETCH. Thus it is possible to process not only one row of a table but a large number of rows with one SQL statement. This reduces the interaction between client and server.
In the case of FETCH statements, the attempt is always made to transfer more than one row of the table into the application program. No special programming is required.
Adabas D provides various locking levels with respect to read consistency, specifically for each session: so-called isolation levels.
|NATIVE||ANSI||Kind of Lock|
|1, 10||1||Read committed|
|2, 20||2||Repeatable Read|
Isolation level 0 makes so-called "dirty reads" possible, i.e., the access to database objects that are just being modified by another user in an uncommitted transaction. Since no share locks are set implicitly, isolation level 0 allows the highest degree of parallel operation in OLTP operation. In order to compensate for the possible effects of a "dirty read", a check read should be done or the optimistic locks described below should be used.
For the isolation level 1 or 10, Adabas D implicitly sets a share lock on every table row currently being read, thus preventing access to modifications of transactions that have not yet been completed. This share lock is kept until the next read command for the same table is issued; i.e., for each table a share lock is set to one row at the most.
With respect to the handling of locks during the processing of mass commands, isolation level 15 is an extension of the isolation level 1 or 10. The tables addressed are locked in share mode for the duration of command processing. In this way, the tables involved cannot be modified while the commands are being processed.
For the isolation level 2 or 20, mass operations are first secured by a table share lock as in isolation level 15. In addition, all the rows read locked in share mode. These locks are only released at the end of the transaction.
By implicitly setting table share locks on all tables processed up to the end of the transaction, the isolation level 3 or 30 ensures that database users only see those modifications they themselves have made.
By means of options in a SELECT statement, it is possible to work in a specific isolation level but to achieve a higher or lower read consistency with respect to multiuser effects.
In all isolation levels, updates of table rows lead to exclusive locks for these rows up to the end of the transaction.
For simplified programming of OLTP applications, Adabas D provides optimistic locks. They make it possible to write an application for operation in isolation level 0 without the usual repetitive check reading being necessary.
For this purpose, an optimistic lock is demanded when accessing a database object. When updating the same database object, the application program receives a message if, in the meantime, other users have made modifications to this database object. Otherwise, the update is executed without the application having to verify the database object with a check reading.
Optimistic locks do not preclude share locks or exclusive locks.
The opposing requirements of consistency and maximum concurrency must be harmonized for the processing of master-detail structures in OLTP applications.
If, for example, all items ordered by a particular customer are locked during the processing of an order operation, all the other order operations will be blocked. If the customer row is released after each order item, it can happen that the input of order items that are still open is blocked by the simultaneous processing of other orders made by the same customer.
For this reason, Adabas D provides a transaction chaining via the KEEP LOCK option in the COMMIT statement. Such a transaction chaining allows a user to perform a COMMIT at the end of a transaction and to extend simultaneously some of the locks (typically those on the master row) to the next transaction.
In SQL systems, a view table that exists only virtually and whose contents are defined by a SELECT statement referring to permanent tables (base tables) or other views.
If at least two base tables are used in the FROM clause of the SELECT statement of a view definition, it is called a join view.
Whereas SQL systems usually do not permit modifying operations (INSERT, UPDATE, DELETE) in join views, these can be done with Adabas D. In this way, the view concept is extended considerably and enables, for example, attribute migration between tables without this having effects on existing application programs. Even complex application objects consisting of several tables can be made available with renewable join views.
The following constructs are not permitted in renewable join views:
GROUP BY or HAVING
UNION, INTERSECT, or EXCEPT
The view must be defined WITH CHECK OPTION and contain the primary key columns of all tables involved. For master-detail (1:N) structures, a FOREIGN KEY must be defined between the tables involved.
A join view which satisfies these rules is renewable.
With the usual join of two tables, the result table contains only those rows for which the join condition is satisfied. The outer join also permits rows to be included in the result table for which there are no corresponding rows in the second table. These kinds of rows are supplemented in the outer join by NULL values in the columns which, properly speaking, should come from the other table. When joining two tables, the NULL value can be added for the "left" or the "right" table or also for both.
To be able to execute outer joins over more than two tables with a clearly defined semantics, Adabas D offers the possibility of formulating a further SELECT instead of a table name in the FROM part of the SELECT statement. In this way, in the case of outer joins, the processing sequence is laid down implicitly.
In cursor processing, Adabas D supports more than the usual FETCH logic which runs sequentially through the SELECT result once. In addition to FETCH NEXT as default, FETCH PREV, FETCH FIRST, FETCH LAST, FETCH POS, and FETCH SAME are also supported. It is thus possible to run through the same SELECT result several times and, above all, to formulate easily and directly a backward scrolling in results. Anyone who has tried to program this backward scrolling with the usual means available in other SQL systems will be thankful for this facility.
There are positioned accesses not only within SELECT results. Also when accessing individual rows of a table with a primary key definition, Adabas D supports the SELECT variants SELECT NEXT, SELECT PREV, SELECT FIRST, SELECT LAST, and SELECT DIRECT. The specification of direction refers to the primary key order. In this way, application programming based on the primary key order is supported more directly and more efficiently than would be possible using the usual SQL constructs (ORDER BY).
Adabas D provides an extensive range of built-in functions, most of which can be employed in the SELECT statements for qualification or data formatting.
+, -, *, /, DIV, MOD
COUNT, MAX, MIN, SUM, AVG, STDDEV, VARIANCE
TRUNC, ROUND, FIXED, CEIL, FLOOR, SIGN, ABS,
POWER, SQRT, LENGTH, INDEX, COS, SIN, TAN, COT,
COSH, SINH, TANH, ACOS, ASIN, ATAN, ATAN2,
RADIANS, DEGREES, EXP, LN, LOG, PI
SUBSTR, || (concatenation), & (concatenation),
LFILL, RFILL, TRIM, LTRIM, RTRIM, EXPAND, UPPER,
LOWER , LPAD, RPAD, MAPCHAR, INITCAP, REPLACE,
TRANSLATE , ALPHA, ASCII, EBCDIC, SOUNDEX
ADDDATE, SUBDATE, DATEDIFF, DAYOFWEEK, DAYOFMONTH,
WEEKOFYEAR , DAYOFYEAR, MAKEDATE, YEAR, MONTH, DAY,
TIMESTAMP , DAYNAME, MONTHNAME
ADDTIME, SUBTIME, TIMEDIFF, MAKETIME, HOUR,
MINUTE, SECOND, TIME, MICROSECOND
VALUE, GREATEST, DECODE, LEAST
NUM, CHR, HEX, CHAR
With these built-in functions, extensive and complex calculations or evaluations can be performed very easily on the Adabas D database. This saves a major part of conventional application programming.
Adabas D allows a user to extend the effects of the SQL statements with user-specific DB functions. In addition to the above-mentioned built-in functions, it is possible to define own user-specific functions for use in SELECT statements or WHERE qualifications. This allows more application logic to be shifted to the database server.
DB functions are developed with the same powerful programming language as DB procedures and triggers.
As a European SQL system, Adabas D puts special emphasis on supporting the various special characters in the individual European alphabets (e.g., the German umlauts).
These special characters present two sorts of problems: their representation on the terminal and their internal sorting.
For presenting these special characters, conversions are necessary because not all hardware manufacturers use the ISO-ASCII character set. Adabas D therefore provides configurable conversion tables (TERMCHAR SET) which can be activated specifically for each session depending on the type of terminal. The correct input and output and the correct internal storage can thus be achieved even in a heterogeneous hardware, terminal, and PC environment.
To be able to sort these special characters according to the user's conventions and not according to the often arbitrary internal codes, Adabas D provides configurable sorting tables. In these, it can be specified, for example, that for the German "ü" a sorting according to "ue" is desired. With the built-in function MAPCHAR, a virtual column can be generated from the stored data which sorts the data set in the desired manner.
Adabas D provides the user with a comprehensive authorization concept that supports four functional user classes and column-oriented access rights.
It is thus possible to generate an individual partial view on the data set for each user and to protect the data from unauthorized access and modifications.
Adabas D distinguishes between four classes of user:
In addition to the rights of a DBA, the SYSDBA has the right to create users with the status DBA on his SERVERDB.
Users with DBA status can create users and usergroups with RESOURCE and STANDARD status, create private data and pass on privileges to other users. The user status DBA includes all rights which a user with the RESOURCE status has.
RESOURCE users can define their own tables, views, and synonyms and pass on privileges for these objects.
STANDARD users may define views and synonyms but otherwise may only execute operations on data for which they have been privileged.
Several RESOURCE or STANDARD users can be grouped together by their DBA into a user class. This makes the administration of privileges easier because all members of a usergroup obtain the same rights with respect to SQL authorization.
Privileges are granted to users or usergroups with the GRANT statement and are withdrawn with REVOKE. Privileges relate to tables, views, columns, and DB procedures. With views, it is also possible to formulate privileges which depend on the database contents (value-dependent privileges).
The following privileges relating to database objects can be granted:
SELECT (column list)
UPDATE (column list)
SELUPD (column list)
The privileges SELECT, INSERT, DELETE, UPDATE, and SELUPD relate to the corresponding SQL statements. INDEX allows the use of CREATE INDEX; ALTER the use of ALTER TABLE, and REFERENCES the reference to a table in the REFERENCES clause of a table definition. With EXECUTE, the right to call a DB procedure can be passed on to other users. Adabas D supports the WITH GRANT option with which the recipient of a privilege is able to pass on this privilege.
DBAs can restrict the use of resources by database users for whom they are responsible. With PERMLIMIT and TEMPLIMIT, the allocatable disk space can be restricted. COSTWARNING and COSTLIMIT prevent expensive SQL queries. With the CONTROL function ACCOUNTING, the use of resources per user can be recorded and accounted precisely.
Adabas D provides extensive possibilities for obtaining information about the static and dynamic aspects of the database objects.
This is done using system tables which are provided in the form of views and which build the SQL catalog. These system tables can be accessed via SELECT statements. Descriptions of all currently defined database objects and information about their space requirements can be retrieved. Further system tables are offered, in addition, containing data obtained by monitoring the current database operation.