Basic Concepts of Mimer SQL

This chapter provides a general introduction to the basic concepts of Mimer SQL databases and Mimer SQL objects.

Mimer SQL is a relational database system. This means that the information in the database is presented to the user in the form of tables. The tables represent a logical description of the contents of the database which is independent of, and insulates the user from, the physical storage format of the data.

The Mimer SQL database includes the data dictionary which is a set of tables describing the organization of the database and is used primarily by the database management system itself.

The database, although located on a single physical platform, may be accessed from many distinct platforms, even at remote geographical locations, linked over a network through client/server support.

Commands are available for managing the connections to different databases, so the actual database being accessed may change during the course of an SQL session.

At any one time, however, the database may be regarded as one single organized collection of information.

Tables

Data in a relational database is logically organized in tables, which consist of horizontal rows and vertical columns.

Columns are identified by a column-name. Each row in a table contains data pertaining to a specific entry in the database. Each field, defined by the intersection of a row and a column, contains a single item of data.

For example, a table containing information about currencies may have columns for the currency code, name and exchange rate:

CREATE TABLE currencies (

     code CHARACTER(3) PRIMARY KEY,

     currency CHARACTER(32) NOT NULL,

     exchange_rate DECIMAL(12, 4));

 

CURRENCIES

Each row in a table must have the same set of data items (one for each column in the table), but not all the items need to be filled in.

A column can have a default value defined (either as part of the column specification itself or by using a domain with a default value) and this is stored if an explicit value has not been specified.

If no default value has been defined for a column, the null value is stored when no data value is supplied (the way the null value is displayed depends on the application – in Mimer BSQL the minus sign is used).

A relational database is built up of several inter-dependent tables which can be joined together. Tables are joined by using related values that appear in one or more columns in each of the tables. Part of the flexibility of a relational database structure is the ability to add more tables to an existing database. A new table can relate to an existing database structure by having columns with data that relates to the data in columns of the existing tables. No alterations to the existing data structure are required.

All the fields in any one column contain the same data type with the same maximum length. See the Mimer SQL Reference Manual, Data Types in SQL Statements, for a detailed description of data types supported by Mimer SQL.

Base Tables and Views

The logical representation of data in a Mimer SQL database is stored in tables (this is what the user sees, as distinct from the physical storage format which is transparent to the user).

The tables which store the data are referred to as base tables. Users can directly examine data in the base tables.

In addition, data may be presented using views, which are created from specific parts of one or more base tables or views. To the user, views may look the same as tables, but operations on views are actually performed on the underlying base tables.

Access privileges on views and their underlying base tables are completely independent of each other, so views provide a mechanism for setting up specific access to tables.

The essential difference between a table and a view is underlined by the action of the DROP command, which removes objects from the database. If a table is dropped, all data in the table is lost from the database and can only be recovered by redefining the table and re-entering the data. If a view is dropped, however, the table or tables on which the view is defined remain in the database, and no data is lost.

Data may, however, become inaccessible to a user who was allowed to access the view but who is not permitted to access the underlying base table(s).

Note:Since views are logical representations of tables, all operations requested on a view are actually performed on the underlying base table, so care must be taken when granting access privileges on views.
Such privileges may include the right to insert, update and delete information. As an example, deleting a row from a view will remove the entire row from the underlying base table and this may include table columns the user of the view had no privilege to access.

Restriction Views

Views may be created to simplify presentation of data to the user by including only some of the base table columns in the view or only by including selected rows from the base table. Views of this kind are called restriction views.

For example, a view may be created on the COUNTRIES table to include only the COUNTRY and CURRENCY_CODE columns:

CREATE TABLE countries (

     code CHARACTER(2) PRIMARY KEY,

     country VARCHAR(48) NOT NULL,

     currency_code CHARACTER(3) NOT NULL);

 

CREATE VIEW countries_view

  AS SELECT country, currency_code

  FROM countries;

 

COUNTRIES_VIEW

Similarly, a view may be created to include only the rows in COUNTRIES where US dollars are used (CURRENCY_CODE = 'USD'):

CREATE VIEW usd_countries_view

   AS SELECT country

   FROM countries

    WHERE currency_code = 'USD';

Join Views

Views may also be created to combine information from several tables – join views.

Join views can be used to present data in more natural or useful combinations than the base tables themselves provide (the optimal design of the base tables will have been governed by rules of relational database modeling).

Join views may also contain restriction conditions.

For example, the join view below presents the countries that use some kind of dollars. The CURRENCY_CODE in COUNTRIES_VIEW is linked with the CODE column in the CURRENCIES table, and a restriction of CURRENCY includes 'dollar' is applied:

CREATE VIEW dollar_countries

  AS SELECT country, currency

  FROM countries_view JOIN currencies

     ON countries_view.currency_code = currencies.code

   WHERE lower(currency) like '%dollar%';

 

dollar_countries

Primary Keys and Indexes

Rows in a base table are uniquely identified by the value of the primary key defined for the table. The primary key for a table is composed of the values of one or more columns.

Primary keys are automatically indexed to facilitate effective information retrieval. The primary key index is the most effective access path for the table.

Table columns that are in the primary key, a unique constraint or used in a foreign key reference are automatically indexed (in the order in which they are defined in the key). Therefore, explicitly creating an index on these columns will not improve performance at all.

Other columns or combinations of columns may be defined as a secondary index to improve performance in data retrieval. Secondary indexes are defined on a table after it has been created (using the CREATE INDEX statement).

An example of when a secondary index may be useful is when a search is regularly performed on a non-keyed column in a table with many rows, defining an index on the column may speed up the search. The search result is not affected by the index but the speed of the search is optimized.

It should be noted, however, that indexes create an overhead for update, delete and insert operations because the index must also be updated.

An index will be used if the internal query optimization process determines it will improve the efficiency of a search.

An index can be used in select statements as an ordinary table, but explicit write operations on indexes are not allowed.

SQL queries are automatically optimized when they are internally prepared for execution. The optimization process determines the most effective way to execute each query, which may or may not involve using an applicable index.

WORD_SEARCH Index Algorithm

The WORD_SEARCH index algorithm improves performance for “begins word” searches and “match word” searches, when using the builtin.begins_word() and builtin.match_word() functions.

create table documents (id integer primary key, title varchar(50),

    content nvarchar(500) collate english_1);

create index dcont_ws on documents (content for word_search);

select * from documents where builtin.word_match(content, 'Mimer');

Data Integrity

A vital aspect of a Mimer SQL database is data integrity. Data integrity means that the data in the database is complete and consistent both at its creation and at all times during use.

Mimer SQL has built-in facilities that ensure the data integrity in the database:

•Domains

•Unique constraints and primary keys

•Foreign keys (also referred to as referential integrity)

•Check constraints in table definitions

•Check options in view definitions

•Default values

•Triggers

•Transactions

These features should be used whenever possible to protect the integrity of the database, guaranteeing that incorrect or inconsistent data is not entered into it. By applying data integrity constraints through the database management system, the responsibility of ensuring the data integrity of the database is moved from the users of the database to the database designer.

Domains

Each column in a table holds data of a single data type and length, specified when the column is created or altered. The data type may be specified explicitly (e.g. CHARACTER(20) or INTEGER) or through the use of domains.

A domain definition consists of a data type, optional check conditions and an optional default value. Data which falls outside the constraints defined by the check conditions will not be accepted in a column which is defined as belonging to the domain.

A column belonging to a domain for which a default value is defined (unless there is an explicit default value for the column) will automatically receive that value if row data is entered without a value being explicitly specified for the column.

Unique Constraints and Primary Keys

Rows in a base table are uniquely identified by the value of the primary key defined for the table. The primary key for a table is composed of the values of one or more columns. A table cannot contain two rows with the same primary key value. If the primary key contains more than one column, the key value is the combined value of all the columns in the key. Individual columns in the key may contain duplicate values as long as the whole key value is unique.

Apart from a primary key constraint its also possible to add one or more unique constraints. The primary key constraint and the unique constraint are similar, but treat NULLs in different ways. However, the definition of the primary key is also a definition of the most effective access path for the table.

Foreign Keys – Referential Integrity

A foreign key is one or more columns in a table defined as cross-referencing the primary key or a unique key of table. Data entered into the foreign key must either exist in the key that it cross-references or be null. This maintains referential integrity in the database, ensuring that a table can only contain data that already exists in the selected key of the referenced table.

As a consequence of this, a key value that is cross-referenced by a foreign key of another table must not be removed from the table to which it belongs by an update or delete operation.

The DELETE and UPDATE rules defined for the referential constraint provide a mechanism for adjusting the values in a foreign key in a way that may permit a cross-referenced key value to effectively be removed.

Note:The referential integrity constraints are effectively checked at the end of an INSERT, DELETE or UPDATE statement, or at COMMIT depending on whether the constraint is declared as IMMEDIATE or DEFERRED.

The following example illustrates the column CURRENCY_CODE in the table COUNTRIES as a foreign key referencing the primary key of the table CURRENCIES.

CREATE TABLE countries (

     code CHARACTER(2) PRIMARY KEY,

     country VARCHAR(48) NOT NULL,

     currency_code CHARACTER(3) NOT NULL,

     FOREIGN KEY (currency_code) REFERENCES currencies(code));

 

COUNTRIES

CURRENCIES

 

In this example, the referential constraint means there cannot be a currency in the COUNTRIES table that does not exist, and a currency cannot be deleted if it is assigned to a country.

Foreign key relationships are defined when a table is created using the CREATE TABLE statement and can be added to an existing table by using the ALTER TABLE statement.

The cross-referenced table must exist prior to the declaration of foreign keys on that table, unless the cross-referenced and referencing tables are the same.

The exception to this rule is when foreign key relationships are defined for tables in a CREATE SCHEMA statement. Object references in a CREATE SCHEMA statement are not verified until the end of the statement, when all the objects have been created. Therefore, it is possible to reference a table that will not be created until later in the CREATE SCHEMA statement.

Check Conditions

Check conditions may be specified in table and domain definitions to make sure that the values in a column conform to certain conditions.

Check conditions are discussed in detail in Check Constraints.

Check Options in View Definitions

You can maintain view integrity by including a check option in the view definition. This causes data entered through the view to be checked against the view definition. If the data conflicts with the conditions in the view definition, it is rejected.

For example, the restriction view USD_COUNTRIES is created with the following SQL statement:

CREATE VIEW usd_countries

  AS SELECT code, country, currency_code

  FROM countries

   WHERE currency_code = 'USD'

  WITH CHECK OPTION;

 

This means that the view USD_COUNTRIES contains CODE, COUNTRY and CURRENCY_CODE columns from the COUNTRIES table on the condition that the value in the CURRENCY_CODE column is USD.

Any attempt to change contents of the CURRENCY_CODE column in the view or to insert data in the view where CURRENCY_CODE does not contain USD is rejected.

If check option is not used, a user could update a row in the view that is not returned by the view.

Sequences

A sequence is a database object that provides a series of integer values.

A sequence has an initial value, an increment value, a minimum value and a maximum value defined when it is created, either implicitly or explicitly (by using the CREATE SEQUENCE statement, see Mimer SQL Reference Manual, CREATE SEQUENCE).

A sequence can be defined with CYCLE or NO CYCLE option. A sequence with CYCLE option may re-use values when the maximum value has been reached. A sequence with NO CYCLE option never generates the same value twice.

A sequence definition may contain a data type which determines the limits for which values that can be generated by using the sequence. The allowed data types are SMALLINT, INTEGER and BIGINT.

A sequence generates a series of values by starting at the initial value and proceeding in increment steps. If all values in a sequence with cycle option has been exhausted, the sequence will start over again with the min value if the increment is positive, and with the max value if the increment is negative.

It is possible to generate the next value in the value series of a sequence by using the NEXT VALUE FOR sequence-name construct. This is used for the first time after the sequence has been created to establish the initial value defined for the sequence. Subsequent uses will add the increment step value to the value of the sequence and the result will be established as the current value of the sequence.

It is possible to get the value of a sequence by using the CURRENT VALUE FOR sequence_name construct. This construct cannot be used until the initial value has been established for the sequence (i.e. using it immediately after the sequence has been created will raise an error). For each new database connection, NEXT VALUE must be used before CURRENT VALUE can be used.

When the current value of a sequence with NO CYCLE option is equal to the last value in the series it defines, NEXT VALUE OF sequence-name will raise an error and the value of the sequence will remain unaltered.

If the sequence has CYCLE option, NEXT VALUE FOR sequence-name will always succeed.

The value of CURRENT VALUE FOR sequence-name and NEXT VALUE FOR sequence-name can be used where a value-expression would normally be used. The value may also be used after the DEFAULT clause in the CREATE DOMAIN, CREATE TABLE and ALTER TABLE statements.

An ident must hold USAGE privilege on the sequence in order to use it.

If a sequence is dropped, with the CASCADE option in effect, all object referencing the sequence will also be dropped.

Examples:

A sequence with CYCLE option with start value 1, increment 3 and maximum 10 will generate the following series of values: 1, 4, 7, 10, 1, 4, 7, 10, 1, 4...

A sequence with NO CYCLE option, start value 1, increment 3, minvalue 1 and maxvalue 10 will generate the following series of values: 1, 4, 7, 10.

Note:It is possible that not every value in the series defined by the sequence will be generated. If a server failure occurs it is possible that some of the values in the series might be skipped.

Synonyms

A synonym is an alternative name for a table, view or another synonym. Synonyms can be created or dropped at any time.

A synonym cannot be created for a function, procedure or a module.

Using synonyms can be a convenient way to address tables that are contained in another schema.

For example, if a view called customer_details is contained in the schema called mimer_store, the full name of the view is mimer_store.customer_details.

This view may be referenced from the schema called mimer_store_book by its fully qualified name as given above.

Alternatively, a synonym may be created for the view in schema mimer_store_book, e.g. cust_details. Then the name cust_details can simply be used to refer to the view mimer_store.customer_details.

Note:The name cust_details is contained in schema mimer_store_book and can only be used in that context.

Databanks

A databank is the physical file where a tables and sequences are stored. A Mimer SQL database may include any number of databanks. There are two types of databanks - system databanks and user databanks.

System Databanks

System databanks contain system information used by the database manager. These databanks are defined when the system is created.

The system databanks are:

•SYSDB containing the data dictionary tables

•TRANSDB used for transaction handling

•LOGDB used for transaction logging

•SQLDB used in transaction handling and for temporary storage of internal work tables.

User Databanks

User databanks contain the user tables and sequences. These databanks are defined by the user(s) responsible for setting up the database. See the Mimer SQL Reference Manual, Specifying the Location of User Databanks, for details concerning path names.

The division of tables between different user databanks is a physical file storage issue and does not affect the way the database contents are presented to the user. Except in special situations (such as when creating tables), databanks are completely invisible to the user.

Note:In Mimer SQL, backup and restore can be performed on a per-databank basis rather than on the entire database file base, see the System Management Manual, Backing-up and Restoring Data for more information.

Shadows

Mimer SQL Shadowing can be used to create and maintain one or more copies of a databank on different disks. Shadowing provides extra protection from the consequences of disk crashes, etc.

Read more in the System Management Manual, Mimer SQL Shadowing.

Mimer SQL Character Sets

For character data, Mimer SQL uses the character set ISO 8859-1, also known as the Latin1 character set. By default, character data is sorted in the numerical order of its code according to the ISO8BIT collation.

For national character data, Mimer SQL uses the Unicode character set, which is a universal character set, see https://www.unicode.org for more information. National character data is sorted according to the UCS_BASIC collation. UCS_BASIC is a collation in which the ordering is determined entirely by the Unicode scalar values of the characters in the strings being sorted.

See the Mimer SQL Reference Manual, Character Sets for more information.

Collations and Linguistic Sorting

As stated in the previous section, character and national character data is sorted according to specific collations.

A collation, also known as a collating sequence, is a database object containing a set of rules that determines how character strings are compared, searched and alphabetically sorted. The rules in the collation determine whether one character string is less than, equal to or greater than another. A collation also determines how case-sensitivity and accents are handled.

You can specify a different collation for ordering characters when you create or alter a table or create a domain.

If you have specified a collation for a column, the collation is used implicitly in SQL statements.

You only need to explicitly use a collate clause in SQL statements if you want to override the default collation or the collation you specified when creating or altering the column or creating the domain.

For more information, see Collations.

Since Unicode is a universal character set the Unicode sorting order can be employed on any arbitrary character set that is a subset of Unicode.

The default Unicode sorting order is provided in https://www.unicode.org/reports/tr10/allkeys.txt (Unicode 3.1.1 mapping). This table (the Default Unicode Collation Element Table) provides a mapping from characters to collation elements for all the explicitly weighted characters.

Stored Procedures

In Mimer SQL you can define functions and procedures, collectively known as stored procedures.

Mimer SQL stored procedures enable you to define and use powerful functionality through the creation and execution of routines. By using stored procedures, you can move application logic from the client to the server, thereby reducing network traffic. This will also allow the logic to be shared between different applications.

Stored procedures are stored in the data dictionary and you can invoke them when needed.

For a complete and detailed discussion of stored procedures, see Mimer SQL Reference Manual, Functions and the Mimer SQL Programmer's Manual, Mimer SQL Stored Procedures.

Stored procedures execute their statements using the user context of the creator of the stored procedure, independent of the actual current user.

Routines – Functions and Procedures

The term routine is a collective term for functions and procedures. Functions are distinguished from procedures in that they return a single value and the parameters of a function are used for input only. A function is invoked by using it where a value expression would normally be used.

Mimer SQL supports standard procedures and also result set procedures, which are procedures capable of returning the row value(s) of a result set.

Standard procedures are invoked directly by using the CALL statement and can pass values back to the calling environment through the procedure parameters.

A result set procedure is invoked by using the CALL statement, and the result set values are presented in the same way as for a SELECT statement.

In Embedded SQL, ODBC and JDBC, result set procedures are invoked by declaring a cursor which includes the procedure call specification and by then using the FETCH statement to execute the procedure and return the row(s) of the result set.

The creator of a routine must hold the appropriate access rights on any database objects referenced from within the routine. These access rights must be held for the life of the routine.

Routine names, like those of other private objects in the database, are qualified with the name of the schema to which they belong.

Modules

A module is simply a collection of routines. All the routines in a module are created when the module is created and belong to the same schema.

If a module is dropped, all the routines contained in the module are dropped.

Under certain circumstances a routine may be dropped because of the cascade effect of dropping some other database object. If such a routine is contained in a module, it is implicitly removed from the module and dropped. The other routines contained in the module remain unaffected.

In general, care should be taken when using DROP or REVOKE in connection with routines, modules or objects referenced from within routines because the cascade effects can often affect many other objects. See Dropping Objects from the Database and Recursive Effects of Revoking Privileges for details.

Triggers

A trigger defines a number of procedural SQL statements that are executed whenever a specified data manipulation statement is executed on the table or view on which the trigger has been created.

The trigger can be set up to execute AFTER, BEFORE or INSTEAD OF the data manipulation statement. Trigger execution can also be made conditional on a search condition specified as part of the trigger.

Triggers are described in detail in the Mimer SQL Programmer's Manual, Triggers.

Idents

An ident is an authorization-id used to identify users, programs and groups. There are three types of idents in a Mimer SQL database: USER, PROGRAM and GROUP idents.

USER Idents

USER idents identify individual users who can connect to a Mimer SQL database. USER idents are generally associated with specific physical individuals who are authorized to use the system.

A USER’s access to the database objects is restricted by the specific privileges granted to the ident.

A USER ident is usually protected by a password. For a USER ident it is also possible to add one or several OS_USER logins which allows the user currently logged in to the operating system to access the Mimer SQL database without providing a password.

For example: if there is a USER ident ALBERT defined in Mimer SQL that has an OS_USER login ALBERT, then the operating system user ALBERT may start Mimer BSQL (for example) and connect directly to Mimer SQL simply by pressing <return> at the Username: prompt.

However, if the USER ident ALBERT defined in Mimer SQL has an OS_USER login HERBERT, then the operating system user HERBERT may start Mimer BSQL and connect directly to Mimer SQL by entering HERBERT at the Username: prompt and simply pressing <return> at the PASSWORD: prompt.

PROGRAM Idents

PROGRAM idents can be used for effective administration of access rights and authorization control.

PROGRAM idents do not strictly connect to Mimer SQL, but they may be entered by an ident by using the ENTER statement. (The ENTER statement may only be used by an ident who is already connected to a Mimer SQL database.)

An ident is granted the privilege to enter a PROGRAM ident. A PROGRAM ident is set up to have certain privileges and these apply after the ENTER statement has been used. PROGRAM idents are generally associated with specific functions within the system, rather than with physical individuals.

When a PROGRAM ident is entered, any privileges granted to that ident become current and privileges belonging to the previous ident (i.e. the ident issuing the ENTER statement) are suspended.

PROGRAM idents are disconnected with the LEAVE statement.

GROUP Idents

GROUP idents are collective identities used to define groups of user and/or program idents.

Any privileges granted to or revoked from a GROUP ident automatically apply to all members of the group. Any ident can be a member of as many groups as required, and a group can include any number of members.

GROUP idents provide a facility for organizing the privilege structure in the database system. All idents are automatically members of the group PUBLIC. When a privilege is granted to PUBLIC, all users receive the privilege.

Schemas

A schema defines a local environment within which private database objects can be created. The ident creating the schema has the right to create objects in it and to drop objects from it.

When a USER or PROGRAM ident is created, a schema with the same name usually is created and the created ident becomes the creator of the schema. This happens by default unless WITHOUT SCHEMA is specified in the CREATE IDENT statement. A user without a schema is not allowed to create any database objects at all.

When a private database object is created, the name for it can be specified in a fully qualified form which identifies the schema in which it is to be created. The names of objects must be unique within the schema to which they belong, according to the rules for the particular object-type.

If an unqualified name is specified for a private database object, a schema name equivalent to the name of the connected ident is assumed.

Access Rights and Privileges

Privileges control how users may access database objects and the operations they can perform in the database.

USER and PROGRAM idents are protected by a password, which must be given together with the correct ident name in order for a user to gain access to the database or to enter a PROGRAM ident. Passwords are stored in encrypted form in the data dictionary and cannot be read by any ident, including the system administrator. An ident’s password may only be changed by the ident or by the creator of the ident.

A set of privileges define the operations each ident is permitted to perform. There are three classes of privileges in a Mimer SQL database: system, object and access.

System Privileges

System privileges, which control the right to perform backup and restore operations, the right to execute the UPDATE STATISTICS statement as well as the right to create new databanks, idents, schemas and to manage shadows.

System privileges are granted to the system administrator when the system is installed and may be granted by the administrator to other idents in the database. As a general rule, system privileges should be granted to a restricted group of users.

Note:An ident who is given the privilege to create new idents is also able to create new schemas.

Object Privileges

Object privileges, which control membership in GROUP idents, the right to invoke functions and procedures, the right to enter PROGRAM idents, the right to create new tables in a specified databank and the right to use a domain or sequence.

The creator of an object is automatically granted full privileges on that object.

Thus the creator of:

•a group is automatically a member of the group

•a function or procedure may execute it

•a pre-compiled statement may execute it

•a PROGRAM ident may enter it

•a schema may create objects in and drop objects from it

•a databank may create tables and sequences in the databank

•a table or view holds all privileges on it

•a domain may use it

•a sequence may use that sequence.

The creator of an object generally has the right to grant any of these privileges to other users, in the case of views, functions and procedures this actually depends on the creator’s privileges on objects referenced from within.

Access Privileges

Access privileges, which define access to the contents of the database, i.e. the rights to retrieve data from tables or views, delete data, insert new rows, update data and to refer to table columns as foreign key references.

About Privileges

Granted privileges can be regarded as instances of grantor/privilege stored for an ident. An ident will hold more than one instance of a privilege if different grantors grant it.

A privilege will be held as long as at least one instance of that privilege is stored for the ident. All privileges may be granted with the WITH GRANT OPTION which means that the receiver has, in turn, the right to grant the privilege to other idents. An ident will hold a privilege with the WITH GRANT OPTION as long as at least one of the instances stored for the ident was granted with this option.

If the same grantor grants a privilege to an ident more than once, this will not result in more than one instance of the privilege being recorded for the ident. If a particular grantor grants a privilege without WITH GRANT OPTION and subsequently grants the privilege again with WITH GRANT OPTION, then WITH GRANT OPTION will be added to the existing instance of the privilege.

Each instance of a privilege held by an ident is revoked separately by the appropriate grantor. It is possible to revoke WITH GRANT OPTION without revoking the associated privilege completely. Revoking Privileges describes revoking privileges in more detail.

The Data Dictionary

The data dictionary contains information on all the database objects (e.g. tables, views and idents) stored in a Mimer SQL database and how they relate to one another, and access rights and privileges.

The data dictionary views (INFORMATION_SCHEMA) are described in Mimer SQL Reference Manual, Data Dictionary Views.

Mimer SQL Statements

Mimer SQL is a language made up of a number of different statements, which may be divided into the following basic categories:

•Data definition statements

•Access Control Statements

•Data manipulation statements

•Connection statements

•Transaction control statements

•Database administration statements

The SQL statements are described in detail in subsequent chapters of this manual and in the Mimer SQL Reference Manual, SQL Statements.

In addition, there is a set of commands specific to the BSQL environment, for managing output formatting and so on, see Mimer BSQL.

Note:In Mimer BSQL, statements are terminated by a semicolon (;). This is not part of the SQL statement syntax, but is included in the examples in this manual.

Data Definition Statements

Data definition statements are used to maintain objects in a database. For example:

•CREATE, creates objects

•ALTER, modifies objects

•DROP, drops objects

•COMMENT, documents objects.

Access Control Statements

Access Control Statements are used to manage privileges. For example:

•GRANT grants privileges

•REVOKE revokes privileges.

Data Manipulation Statements

Data manipulation statements are used to examine and change data in the database. For example:

•SELECT retrieves data

•INSERT adds new rows to tables

•UPDATE changes data in existing rows

•DELETE deletes data

•CALL executes procedures

•SET value assignment.

Connection Statements

Connection statements are used to connect and disconnect user and program idents to or from the database. For example:

•CONNECT connects a user ident to the database

•DISCONNECT disconnects a user ident from the database

•SET CONNECTION changes the active database connection

•ENTER enters a PROGRAM ident

•LEAVE leaves a PROGRAM ident.

Transaction Control Statements

Transaction control statements are used to control when database transactions begin and end, and when updates take effect. For example:

•SET TRANSACTION sets transaction options for subsequent transactions

•SET SESSION sets the default transaction options for the session

•START starts a transaction build-up

•COMMIT commits the current transaction

•ROLLBACK abandons the current transaction.

Database Administration Statements

Database administration statements are used to manage backup/restore operations and the statistical information used to optimize queries. For example:

•CREATE BACKUP creates a backup copy of a databank, with an optional incremental backup. Incremental backups may also be taken on their own with the statement CREATE INCREMENTAL BACKUP

•ALTER DATABANK, the RESTORE variant of this statement recovers a databank from incremental backup information

•SET DATABASE sets the database ONLINE or OFFLINE

•SET DATABANK sets a databank ONLINE or OFFLINE

•SET SHADOW sets one or more shadows ONLINE or OFFLINE

•UPDATE STATISTICS updates the statistical information used for query optimization. DELETE STATISTICS deletes the statistical information.