This glossary contains definitions of the most important terms used in the SESAM/SQL manuals.
Terms which appear in italics in the explanatory text are explained elsewhere in the glossary.
The “Synonym(s)” line refers to terms with similar or identical meanings that are used in other documentation, but not in SESAM/SQL manuals.
The word “See” accompanying a term refers the user to the term that is actually used in the SESAM/SQL manuals.
In documentation on relational databases, different terms are often used to denote the same thing. Relation, for instance, is also referred to as table, tuple as row, and attribute as column.
In the SESAM/SQL manuals, the terms “table”, “row” and “column” are used. The only exception being the “CALL DML Applications” manual, which uses “attribute” instead of “column”. (This term was used prior to SESAM/SQL V1.1.)
abstract table
A table whose individual rows are not stored persistently. Abstract tables always output the current values at present as determined by SESAM/SQL based on values from other tables. In SESAM/SQL, abstract tables form the basis for views in the information schema.
access authorization
See privileges
access handle
This term occurs in the context of SESAM CLI calls. An access handle is required when reading or writing a BLOB value sequentially. In SESAM/SQL, this sequential processing involves repeatedly issuing the appropriate SESAM CLI calls. During the process, the access handle manages internal information on which BLOB value is currently being processed, and up to which segment of the BLOB value processing has progressed. An access handle is generated using the SESAM CLI call SQL_BLOB_VAL_OPEN and closed using SQL_BLOB_VAL_CLOSE.
access mode of transaction
This is declared in the SQL statement SET TRANSACTION and specifies whether rows can be read (READ ONLY) or updated (READ WRITE) within a transaction.
accounting
User-related accounting feature that covers all the services incurred in the course of a session, such as the number of logical and physical file accesses.
activity
Function of the utility monitor used to create or change a contiguous collection of database objects (catalog, schema, table).
additional mirror unit
Additional mirror disk in a Symmetrix disk system which can be split off for other purposes (backups, test operations etc.) without impairing current operation.
Synonym: Business Continuance Volume (BCV). In other publications also referred to as: BCV data volume.
administration command
Type of command used by the system administrator to monitor and control the database handler (DBH) and the SESDCN distribution component.
Unlike administration statements, administration commands are entered using the BS2000 command INFORM-PROGRAM or by including a CALL-DML statement in a CALL DML program. Otherwise, administration commands basically function in the same way as administration statements.
administration statement
Type of statement used by the system administrator to monitor and control the database handler (DBH) and the SESDCN distribution component.
Administration statements are entered using the SESADM administration program, which features an interactive menu interface.
AES (Advanced Encrpytion Standard)
A standard for encryption, originating from the USA, that has been adopted worldwide. It is a block cipher where fixed length blocks of plain text are encrypted into blocks of cipher text of the same length with a fixed algorithm. AES is a symmetric cipher which uses the same secret key for both encryption and decrpyption verwendet. In SESAM/SQL, both the block length and the length of the secret key is 16 bytes (128 bits, AES-128).
after image
Block after an update.
aggregate
Collection of atomic values. Within a row, an aggregate represents the range of a multiple column in part or in its entirety. The INSERT and UPDATE statements can be used to assign aggregates to multiple columns.
annotation
Special SQL comment. Provides information for executing an SQL or utility statement. Depending on its position an annotation only has an effect of one particular operation in the statement.
application
Realization of a task in one or more application programs working with SESAM/SQL.
asynchronous conversation
UTM conversation executing independently of the user who started it.
Asynchronous requests are appropriate for tasks and messages where the user does not require return messages for the next dialog step.
atomic column
Column that can only contain a single value, as opposed to a multiple column .
attribute
See column
attribute value
See column
authorization identifier (of an SQL user)
An authorization identifier is assigned privileges which are used to determine which operations (e.g. SELECT or UPDATE) a given user may carry out on the database. The authorization identifier is created by means of the SQL statement CREATE USER and assigned to a system user identification by means of CREATE SYSTEM_USER.
autonomous transaction
Autonomously executing transaction with its own thread and its own transaction context within a surrounding transaction.
base table
Table created using the CREATE TABLE statement. A base table is permanently stored in the database. Base tables are also generated as the result of migration.
Base tables can have different table styles.
Base tables can be partitioned ( partitioned tables).
The number and data type of the columns as well as any integrity constraints can be defined using the SQL statement CREATE TABLE. ALTER TABLE can be used to modify them. The number of rows is not part of the table definition.
batch mode
Mode of operation where a user's request is specified in its entirety and can be handled at a different time from when it was specified. This mode should be distinguished from interactive mode.
before image
Block before an update.
Big Endian
A sequence of bytes in memory in a particular coding. In the case of a big endian the most significant byte is stored at the lowest storage address. Big endians are used by SESAM/SQL for the UTF-16 code units (each with 2 bytes).
Antonym: little endian.
BLOB (Binary Large Object)
A data type used in the storage of multimedia data content in databases, e.g. graphics, video, or sound.
BLOB object
In SESAM/SQL, BLOBs are assigned not only a value, but also numerous attributes.
BLOB table
Special base table used exclusively for storing BLOBs. This table is created using the SQL statement CREATE TABLE ... OF BLOB.
BLOB value
Actual value of a BLOB object. It consists of a sequence of bytes of variable length, up to a maximum of 231-1 bytes.
block
Physical data unit of 4096 bytes, which serves as the unit of access under SESAM/SQL.
Synonym: data block
block mode
Mode which can be activated for a cursor by the PREFETCH pragma. When block mode is activated, the first FETCH-NEXT statement causes the DBH to read several rows of the cursor table into a buffer ( prefetch buffer). The first FETCH statement only returns the first transferred row to the user. Each further FETCH statement returns the next row from the buffer until the buffer is empty, in which case a further FETCH statement causes further rows to be transferred. Block mode can accelerate processing of a cursor considerably.
block utilization
See free space reservation
BS2000 system user ID
See system user identification
buffer
Memory area for buffering blocks , recovery information, and statement-specific data. The purpose of buffering is to minimize disk I/O operations.
The buffer management of the DBH controls the displacement of blocks in the case of a buffer overflow.
buffer management
See buffer
byte order mark
The Unicode character “zero-width no break space”, NX'FEFF'. It is sometimes used as the first character to show whether a string is available in little endian or big endian format in the encoding form UTF-16.
CALL DML
Collection of statements from a special type of data manipulation language (DML) whose functions are activated by a subroutine call included in the application program. CALL DML can be used in transactions.
CALL DML mode
The application program is in CALL DML mode when it processes CALL DML statements.
CALL DML table
See table style
CALL DML table catalog list
Contains an entry for each table of the CALL DML table style that is processed in the course of a DBH session. Among other things, the DBH is informed of which CALL DML table is assigned to which database. There must be an entry in the SQL database catalog for each database to which a CALL DML table has been assigned.
The system administrator creates the CALL DML table catalog using the DBH start statement. Entries can be added or removed using the appropriate administration statements.
Cartesian product
The Cartesian product of two tables results in a new table containing all possible concatenations of rows from the first table with rows from the second. The number of rows in the resulting table is the product of the number of rows in the two underlying tables.The Cartesian product can also be applied to more than two tables in the same way.
CAT logging
Recovery feature which ensures that all changes to the catalog space are recorded in the CAT-LOG file. Combined with DA logging , CAT logging is capable of recovering a defective database or space (i.e. media recovery ).
catalog
Named collection of schemas of a database . In addition to the user-defined schemas, the database’s catalog space always contains the information schemas . The database’s metadata can be queried using the information schemas.
catalog ID (CATID)
identifies the pubset which contains the BS2000 or SESAM/SQL files ( spaces, database-specific files, DBH-specific files). It precedes the database or file name in the form :catid.
catalog recovery file (CAT-REC file)
Database-specific file required for media recovery. It contains entries on backup data for the catalog space. These entries are created using the COPY utility statement.
In the catalog recovery file, each set of backups is numbered sequentially. If a backup is required for recovery using the utility statement RECOVER, it is identified by its sequence number.
A CAT-REC copy is created when COPY is used or when CHANGE-CATLOG is used for administration ( catalog.CAT-REC.COPY). If a replication is created, a CAT-REC file of the replication is also created ( replication.CAT-REC.REPL).
catalog space
The catalog space contains the metadata for all schemas of a database , i.e. the information schemas, user-defined schemas and other types of internal administration information. In addition to the catalog space, there are also user spaces .
CATID
See catalog ID
CATID list
List of CATIDs specified by the user, which can be used to restrict a file search. This list can be assigned to the DBH and the utility monitor on startup using the link name SESAMCID.
CAT-LOG file
Database-specific file. Changes to the catalog space are recorded in the CAT-LOG files (CAT logging). The media table defines the media used for the CAT-LOG files.
check constraint
Integrity constraint used to limit the number of data values that satisfy a search condition for one or more than one column of a table. If a check constraint has been defined, rows that do not meet any search conditions specified in INSERT or UPDATE statements are not inserted or updated.
CLI (Call Level Interface)
See SESAM CLI
client/server architecture
In this type of architecture, components called “clients” request services from components called “servers”. This role distribution is reflected more or less strongly in its various possible implementations: remote presentation, remote data management, distributed applications or distributed databases.A client/server architecture requires appropriate means of communication between the connected systems. Distributed transaction processing requires global transaction management.
code point (Unicode Code Point)
In Unicode, each character is assigned a number, the so-called code point. In SQL a Unicode code point can be specified in the format U&'\xxxx' or U&'\+xxxxxx', where x is a hexadecimal digit. The code points lie in the range U&'\0000' through U&'\10FFFF'.
code unit
A code unit is the unit of Unicode coding. For example, a code unit in UTFE is 1 byte (NX'nn') long and in UTF-16 2 bytes (NX'nnnn') long, where n is a hexadecimal digit.
coded character set (CCS)
Rules which define the unambiguous assignment of characters in a character set with their representation in bits. A coded character set is identified by its name ( coded character set name, CCSN).
coded character set name (CCSN)
In a SESAM/SQL database, the CCSN (CODE_TABLE parameter in the SQL statements CREATE CATALOG and ALTER CATALOG) specifies the EBCDIC character set with which values stored in columns of the data type [VAR]CHAR are to be interpreted. The CCSN of a SESAM/SQL application (CCSN parameter of the configuration file) specifies the EBCDIC character set with which the application interprets character strings. A terminal’s CCSN (CODED-CHARACTER-SET parameter in the BS2000 command /MODIFY-TERMINAL-OPTIONS) specifies the EBCDIC character set with which characters are displayed on the terminal. The CCSN of a file (CODED-CHARACTER-SET parameter in the BS2000 command /MODIFY-FILE-ATTRIBUTES) specifies the character set with which the characters in the file are to be interpreted.
collation
Sorting sequence depending on the encoding of the characters. In the Unicode standard the sequence in which the Unicode characters are collated is defined with the help of the Unicode Default Collation Table (DUCET). This table contains a cardinality of the character at various levels. In SESAM/SQL collation according to the Unicode Default Collation Table is offered via the SQL function COLLATE().
collation element
Collation element of the Unicode Default Collation Table (DUCET). In SESAM/SQL the result of the SQL function COLLATE().
CO-LOG file
DBH-specific file that logs the requests.
The system administrator can activate, and specify a volume for, the CO-LOG file using an administration statement.
column
Part of a table . Each column is assigned a name and a data type and contains column values of this data type. Columns may be atomic columns or multiple columns .
The “CALL DML Applications” manual uses the term “attribute” instead of “column”.
Synonym: attribute
column constraint
Integrity constraint enforcing the property of a column at table definition. The integrity constraint is specified in the definition of the relevant column.
column value
In the case of Dan atomic column, the column value is the atomic value of the column. In the case of a multiple value, the column value is an aggregate of values of the data type of the column.
The “CALL DML Applications” manual uses the term “attribute value” instead of “column value”.
Synonym: attribute
communication name
See DBH name
compound index
See index
compound key
See compound primary key
compound primary key
Primary key consisting of a more than one column.
Synonym: compound key
COMPOUND statement
A COMPOUND statement contains procedure statements which are executed in a common context. Common local procedure variables, common local cursors and common local error routines which are all declared in the COMPOUND statement apply for these procedure statements. A COMPOUND statement may not contain another COMPOUND statement. COMPOUND statements cannot be nested.
concurrent access
Attempt by two or more application programs to access the same row (record) at the same time. SESAM/SQL coordinates concurrent access by means of concurrent transactions.
concurrent transactions
See concurrent access
configuration
An identifiable group of DBHs, distribution components (see SESAM/SQL-DCN) and application programs residing on the same system, independent of other DBHs, distribution components and application programs. The utility monitor is assigned to the configuration as an application program.
A configuration must have a unique configuration name within the system. Global (inter-network) uniqueness is recommended, however.
SESAM/SQL applications can run concurrently and independent of each other if they belong to configurations of different names.
configuration file
BS2000 file created by the user. It contains either the start parameters for the DBH or the control statements for SESDCN or the start parameters for the connection module (DBCON) for an application program and /or the utility monitor.
A configuration file for the DBH contains exclusively DBH start statements and options.
A configuration file for SESDCN contains exclusively DCN control statements and options.
The configuration file for DBCON or the utility monitor can contain both start parameters for DBCON and for the utility monitor.
configuration name
See configuration
connection module
Component which is used to connect the DBH to the application program. The connection module must be linked to each application program.
consistency
A state in which the data in the database is free of errors. See also consistency check.
consistency check
The various components of SESAM/SQL carry out consistency checks within the field of their relevant activities. Any inconsistencies the components find are output to the console or the data display terminal (central error messages) and to SYSLST.
consistency level
replaced by the isolation level. The consistency level is supported only for reasons of compatibility to SESAM/SQL < V2.0.
consistency point
Point in time at which the database is in a consistent state. In a DBH session using transaction management , the DBH ensures that the database is in a consistent state at defined times. These consistency points are used by transaction management as rollback points for restart.
constant
See literal
constraint
See integrity constraint
continuation form
Form which is available in certain functions of the utility monitor. It enables branching and further processing.
conversation
Related sequence of transactions. For the time of a conversation, resources of the SQL session are available, e.g. stored cursors. These resources are not subject to any security measures, i.e. they are dropped at the end of the DBH session.
A DB conversation contains 0 through n DB transactions.
A UTM conversation contains 1 through n UTM transactions.
A DB conversation can be assigned to precisely one openUTM conversation or it can extend over several UTM conversations.
An SQL conversation corresponds to a DB conversation (if it is a pure SQL conversation) or to a DB sub-conversation (if it is part of a mixed conversation).
correlation name
Additional name that can be defined for a table or derived table using an SQL statement.Synonym: synonym
DO string is used in the FOR statement. It may not be specified as a correlation name. However, you can use the special name "DO".cross join
Join operation for which the derived table corresponds to the Cartesian product of
the tables involved.
CSV file
Standardized format for the platform-independent exchange of tabular data (CSV: Comma Separated Values). Such files can be generated using a large number of software products (e.g. using SESAM/SQL or Microsoft EXCEL).
cursor
Pointer within a special type of derived table, the cursor table, that allows rows to be retrieved one at a time. A cursor name is defined when the cursor is declared. The cursor description also includes an indication whether the cursor table is updatable or subject to a particular order criterion.
cursor buffer
Memory area reserved for the intermediate results of retrieval statements. It is managed by the DBH. In the case of a buffer overflow, the DBH relocates the data in it to one or more internal cursor files.
cursor file
DBH-specific file that exists in two variations:
The internal cursor file is used by the DBH to store the intermediate results of retrieval statements.
The user cursor file is used by the user to store the intermediate results of CALL DML searches. The user cursor file is identified by a file identifier.
DA logging
Recovery method that uses the DA-LOG files as recovery media to log all changes effected via DML (see data manipulation language). DA logging can be applied either to the entire database or to individual spaces. Combined with CAT logging, DA logging is capable of recovering a defective database or space (i.e. media recovery).
DA-LOG file
database-specific file. Used to log all DML (see data manipulation language) changes made to a database or a space ( DA logging).
The media required for the DA-LOG file are managed with the media table.
data block
See block
data definition language (DDL)
Generic term frequently used in database languages to refer to the statements used for the definition of schemas, tables, privileges, and integrity constraints. In the SQL standard, the statements used for the definition and management of schemas are referred to as “SQL schema statements”.
data manipulation language (DML)
Generic term frequently used in database languages to refer to the statements used for the retrieval and modification of data. The SQL standard does not use the term “data manipulation language”. Instead, it refers to “ SQL data statements” for the retrieval and modification of data in the narrow sense. It also classifies SQL statements into “SQL transaction statements” (for transaction management), “SQL session statements” (for session control), “SQL dynamic data statements” (for dynamic SQL), and the WHENEVER statement for ESQL error handling.
data protection
Protection against unauthorized access.
On the one hand, data protection refers to the protection against unrestricted capture, storage, processing and distribution of the personal data of individuals (also called “privacy”). A number of legal measures (along with commissioners assuring their implementation) have been instituted to deal with this aspect of data protection. On the other hand, data protection includes the measures required to implement it.
Under SESAM/SQL, protection against unauthorized access to databases is implemented using a system entry. The system entry is made up of the authorization identifier and the system user identification. In SQL, different users can be granted different types of access to the various objects of a database by means of views and privileges.
For tables of the CALL DML table style, access control is implemented using a password. On BS2000 system level, the files of the database system can be protected by a BS2000 password.
data type
The data type defines the permissible values for database objects (e.g. columns ). In SQL, the CREATE TABLE or ALTER TABLE statements are used to define the data type of a column. SESAM/SQL supports numeric data types (SMALLINT, INTEGER, NUMERIC, DECIMAL, REAL, FLOAT, DOUBLE PRECISION), character data types (CHARACTER, CHARACTER VARYING), and date/time data types (DATE, TIME, TIMESTAMP) as well as data types for vectors . Each data type can have the null value .
database
Related collection of data that is processed, manipulated and administered by the database system.
In SESAM/SQL, a database consists of the metadata in the catalog space and the user data in the associated user spaces . A database is identified by its database name.
database administration
Database administration covers the following functions:
generating a database
loading and unloading data
importing and exporting tables
securing and recovering a database or parts of it; controlling backups
reorganizing and checking spaces
The individual functions can be activated either:
by including utility statements in the application program, or
using the menus of the utility monitor.
database administrator
Person or group of persons responsible for database administration. Synonym: database administrator
database catalog
See CALL DML table catalog list, SQL database catalog list
database file
BS2000 file containing a space. Database files can have the following names:
catalog.CATALOG for the catalog space
catalog.space (for any other spaces)
where “catalog” stands for the database name specified in the CREATE CATALOG statement, and “space” for the name of the space created using CREATE SPACE.
database handler (DBH)
SESAM/SQL component that analyzes, executes and coordinates all the database accesses of a DBH session.The database handler (DBH) is available in two variants:
independent DBH
This type of database handler is an independent program system that supports multi-user operation. The independent DBH runs independently of the user program. The DBH can consist of several DBH and service tasks; the first DBH task is called the start task, and the others are called followup tasks.linked-in DBH
This type of database handler is linked into, and exclusively processes the requests of, a single application program. The linked-in DBH executes under the same task as this application program.
Synonym: SESAM/SQL DBH, DBH
database management system
See database systemAbbreviation: DBMS
database system
Software system which supports all tasks in conjunction with managing and controlling large data sets. The processes contained in the database system lead to stable, redundancy-free, and expandable data organization. The database system enables concurrent access by multiple users to the same databases and ensures that the data resources remain in a consistent state. Synonym: database management system
database-specific file
File containing database-specific information maintained for each database. Information on database-specific files is stored in the media table.The following are database-specific files: DA-LOG file CAT-LOG file, catalog recovery file and PBI file.
DB user ID
The BS2000 user ID, is not the same as the DBH user ID, but is the user ID under which the database, i.e. the catalog space, its CAT-REC file and the user spaces are stored. The database can be stored under a DB user ID or under the DBH user ID.
DB/DC system
Database system (DB) which is combined with a data communication system (DC) to permit the common use of processes and concurrent accesses. Global and synchronized security measures guarantee that the databases and files in the entire DB/DC system are in a consistent state at all times. SESAM/SQL features a fully synchronized DB/DC system under the control of the transaction monitor openUTM.
DBH
See database handler
DBH name
It uniquely identifies a DBH among the other DBHs in a configuration. Some DBH-specific files are also identified by the DBH name.
The system administrator defines the DBH name using a DBH option. Default name: '?'
Synonym: communication name
DBH option
DBH options are parameters for the DBH, defined by the system administrator at startup time. DBH options affect the limits, resources and execution rules for the current DBH session.
DBH session
Period of time between starting and terminating a DBH.
DBH start statement
DBH start statements initiate reading of the DBH options and the insertion of entries in the SQL database catalog list or the CALL DML table catalog list.
The system administrator enters the DBH start statements when starting the DBH.
DBH task
BS2000 task under which the DBH basic functions are run. In multitasking the DBH can be loaded with multiple DBH tasks. Some partial functions of SESAM/SQL can also be run under service tasks.
DBH user ID
BS2000 user ID under which SESAM/SQL DBH was started as database server.
DBH-specific file
File that is created by the database handler (DBH) for the duration of a session.
The following files are DBH-specific: cursor file , TA-LOG files , WA-LOG file , CO- LOG file and the file for logging the DBH message buffer.
Information on the storage devices for the cursor file, the temporary files, the TA-LOG and WA-LOG files is held in the media catalog .
DCN
See SESAM/SQL DCN (distribution component)
DCN option
DCN options are start parameters for the SESAM/SQL-DCN distribution component.
DDL TA LOG file
A DDL-TA-LOG file is used to protect long-running DDL and SSL statements that execute in a service task. The DDL-TA-LOG file takes some of the load off the TA-LOG files. This file is assigned to the relevant space and is deleted when the transaction terminates.
deadlock
State in which two or more transactions mutually lock each other.
decryption
The conversion of cipher text into plain text using an encryption algorithm with a key. In SESAM SQL, this is performed with the SQL function DECRYPT().
default value
See SQL default value
default value character
Character that has to be individually defined for each column not contained in the primary key in tables of the CALL DML/SQL table style. Each column of this type is assigned a non-significant attribute value by applying the appropriate default value character.
derived table
Table that is the result of a query expression.
descriptor area (SQL)
Data structure for the description of the input and output values of dynamic SQL statements. Each value and its type are represented in the item descriptor area. In the case of multiple columns, there are several entries for one value ( vector).
diacritical mark
A mark linked to a basic character or symbol, e.g. accent, tilde.
direct update (CALL DML)
Update performed using the CALL DML interface.
dirty read
Phenomenon that can occur if a particular isolation level has been specified.
A transaction updates or adds a row. A second transaction ignores the record lock and reads the row before the first transaction has committed it. So the second transaction has read a row that can still be changed by the first transaction, i.e. that was not in its final state.
distributed data management
See distributed databases
distributed database handler
The entirety of database handlers taking part in distributed processing.
distributed databases
Type of data management which implies that the associated databases are distributed over several processors (distributed management) and are subject to distributed processing. Distributed databases enable application programs to use one database locally on the processor on which data is most often needed. This data can also be queried and changed within a transaction by application programs from other processors.
distributed processing
The distributed processing component of SESAM/SQL is SESAM/SQL-DCN.
Distribution processing enables an application program to process more than one DBH in one session in a transaction.
Intra-configuration or inter-configuration as well as intra-processor or interprocessor processing is possible. The application program does not know which DBH it is using at a particular point in time. The distribution rules control the assignment of DBHs to a database as well as the distribution of databases over the network.
Distributed processing is a major asset if the processed databases reside on different processors ( distributed databases). A openUTM application can be distributed over several processors using UTM-D regardless of whether any databases are distributed under SESAM/SQL-DCN.
distributed transaction
See global transaction
distribution rule
For distributed processing, the distribution of the databases to the individual processors must be known to SESAM/SQL-DCN. Within each processor, each database must be assigned to a configuration.
In the distribution rules, the system administrator specifies which database resides on which processor, which DBH is to be used to access the database and which distribution component passes on database accesses by application programs from other configurations.
dyadic operator
Operator comprising two operands, e.g. the basic arithmetic operators +, -, * and /.
dynamic SQL
Dynamic SQL offers preparable SQL statements that can be compiled and executed while the SQL application is running, as well as statements for setting and getting SQL descriptor areas. Dynamic SQL can be applied in interactive query or update programs that use variables, for example.
ECM (Electronic Codebook Mode)
A method for encrpyting plain text greater than a block in length with a block cipher, such as the AES cipher used in SESAM SQL. In ECM, long plain text is divided into several blocks, and each of these blocks is encrypted separately, with the same key. The last of these blocks may have to be padded. In SESAM SQL, this method is used to encrypt values longer than 16 bytes.
If the plain text contains a repetition of some byte sequence such that two blocks of plain text are exactly identical, the corresponding blocks with cipher text will also be the same. Such repetitions can therefore be detected in the cipher text without knowledge of the key.
embedded SQL statement
SQL statement embedded in a host language (e.g. COBOL) program. It is started by means of EXEC SQL and ended by means of END-EXEC. This allows SQL statements to be clearly distinguished from host language statements and precompiled.
encryption
The conversion of plain text into cipher text, using an encryption algorithm with a key. In SESAM/SQL, this is performed with the SQL function ENCRYPT().
escape character
The escape character is defined in the ESCAPE clause of a LIKE predicate. It must immediately precede the character “%”, “_” or another escape character in the comparison value. The escape character disables the placeholder or escape character functions of the “%” or “_” character, or another escape character. As a result, these characters are interpreted as normal characters.
exception file
File which the user
can specify when changing the data types of one or more columns of a base table. If conversion errors occur, SESAM/SQL writes the original column values together with the related error message to the specified exception file without alerting the user.
can specify when loading a base table. It contains information about corrupted records in the used input file and the cause of the error.
File created if necessary by SESAM/SQL for CHECK FORMAL, LOAD, or UNLOAD. This file contains information about corrupted records and the cause of the error. SESAM/SQL creates a separate exception file for each of these three utility statements, which is then updated with each new error that occurs.
export file
BS2000 file in which the metadata and user data of a base table are stored when the table is exported. Using the appropriate SQL statements, the user can then decide which user data is to be transferred to a new table. The export file cannot be edited manually, however, and is used exclusively for importing a table.
external restart
Restart following a system crash. It is performed by the DBH as soon as it loaded after an abort session.
file identifier (CALL-DML)
Two-digit identifier for a logical file. It is assigned when a logical file is opened using the CALL DML open statement. It can also be used to identify a cursor file.
follow-up update (CALL DML)
Update that is formulated using a previously entered direct update, but has different attribute values.
foreign copy
A copy that was created by some BS2000 means and not by the SESAM statement COPY. A foreign copy can be used as the basis for carrying out a recovery or creating a replication.
foreign key
Column in a table referencing a particular column that is a PRIMARY KEY (see primary key constraint ) or UNIQUE (see uniqueness constraint ) in another table. A foreign key is defined by means of the referential constraint that establishes the connection between the foreign key and the referenced column. The table containing the UNIQUE or PRIMARY KEY column is the referenced column. The table to which the referential constraint is applied is the referencing table. Referential constraints can also be applied to combinations of columns.
form
A screen form used for input and output of data. Forms are used for the user interface of the utility monitor , the SESADM user interface, and the SESMON user interface.
form name
name of a form. It is displayed in the status section of the screen when using the utility monitor.
form short name
Three-digit mnemonic name of a form. It is displayed in the status section of the screen when using the utility monitor.
free space reservation
The free space reservation specifies what percentage of each block is to be left free following execution of a LOAD or REORG ( space) utility statement. Free space reservation is defined using the PCTFREE parameter of the CREATE CATALOG or CREATE SPACE statement. The block utilization specifies the maximum percentage of a block that may be occupied following a LOAD or REORG operation.
function identifier (CALL DML)
The function identifier specifies the functions available for the time of validity of the current file identifier.
global configuration file
The global configuration file combines the configuration data for several compo- nents in a single file (see configuration file).
global transaction
A transaction which is distributed by SESAM/SQL-DCN over multiple database handlers.
group commit
Collective commit of a number of “end transaction” calls.
host variable
Variable in a host language (e.g. COBOL) referred to in an embedded SQL statement. Host variables are prefixed with a colon. They are declared in the DECLARE section.
independent DBH
See database handler
index
Tree-like access structure referencing the rows of a table assigned to a column or column combination. Using an inverted list , an index assigns rows containing these values or value combinations in the corresponding columns to each value
or value combination of the underlying column(s). The primary purpose of indexes is to speed up data retrieval.
An index is called a one-column index if it refers to only one column. It is called a compound index if it refers to a number of columns. An index has a unique name within the schema in which it is defined and is stored in a space . An index is created using the SQL CREATE INDEX statement.
SESAM/SQL requires an index for each column (combination) for which a UNIQUE constraint has been defined. If an index has already been defined for the corresponding column(s), it is also used for the UNIQUE constraint. Otherwise, the required index is created implicitly. The name of an index that has been created implicitly starts with UI, followed by a 16-digit number.
An index to which the columns of the primary key constraint apply is also called a primary index or primary key index. An index which is not a primary index is called a secondary index.
index browsing (CALL-DML)
Function of CALL DML used to determine the frequency of attribute values (see column).
index space
User space in which at least one index is stored. A space is defined as an index space of a specific base table when at least one index of that base table is stored in it.
indicator variable
Special type of host variable of the numeric data type SMALLINT, which is assigned to another host variable. The indicator variable indicates whether the other host variable contains the null value or whether data was lost during the transfer of character-string values.
Information schema
See schema
INFORMATION_SCHEMA
See schema
inner join
Join operation which returns all rows that satisfy the join condition as the Cartesian product in the derived table.
Integrity constraint
Rule that applies constraints to the value range of a column or a number of columns as determined by their data type. An integrity constraint limits the number of rows in a base table. It can be defined for a column ( column constraint) or a table ( table constraint). The following constraints are available in SESAM/SQL: CHECK, UNIQUE, primary key, referential and NOT NULL constraints. The database system enforces these constraints.
interactive mode
Mode in which a user request is the result of a sequence of steps executed inter- actively at the terminal. The interactive mode should be distinguished from batch mode.
internal restart
Restart following minor internal errors. The internal restart is performed by the DBH without interrupting the current session.
internal statement format
Optimized format of a CALL DML statement, generated internally from the relevant user statement by the DBH. Internal statement formats can be reused in follow-up statements.
inverted list
Reference list assigned to a column of a table. For each column value, it contains a reference to the rows in the table containing the corresponding values. There are also inverted lists for column combinations. Inverted lists are required for secondary indexes (see index).
Isolation level
The isolation level specifies the degree to which consistency can be affected by concurrent (updating) accesses of another transaction when reading rows in a transaction.
The following isolation levels can be specified: “READ UNCOMMITTED”, “READ COMMITTED”, “REPEATABLE READ” and “SERIALIZABLE”. By using the appropriate isolation level, it is possible to prevent the following phenomena: “ dirty read”, “ non-repeatable read and “ phantoms”.
join
For SQL : the join is effected over two or more tables using the values in the join column(s) . SESAM/SQL knows four types of join then cross join , the inner join , the outer join and the union join .
For CALL-DML : the join is realized by means of the search with join realisiert.
join attribute
See join column
join column
Column of a table whose values are compared with a column of the same type in the same or another table two at a time. If the comparison results in the truth value “true”, the row returned by the join is included in the derived table.
join expression
Query expression containing the keyword JOIN. It specifies the tables to be joined, the type of join ( cross , inner , outer or union join ) to be performed and the join condition.
key
A sequence of bits used in an algorithm for encryption or decryption . The AES cipher used by SESAM/SQL employs the same key for both operations (symmetric cipher). Thus the key has to be kept secret.
The key used by SESAM/SQL is an alphanumeric character string with16 bytes (128 bits).
There are ciphers with different keys for encryption and decryption in which only one of the keys has to be kept secret (public key cryptography).
linked-in DBH
See database handler
literal
Character string that represents a fixed value. SESAM/SQL supports alphanu meric and special literals, numeric literals and date/time literals.
Little Endian
A sequence of bytes in memory in a particular coding. In the case of little endian the least significant byte is stored at the lowest storage address. Antonym: big endian.
load option
See DBH option, DCN option
local cursor
With the definition of local cursors, cursors are defined which can be addressed in the COMPOUND statement.
local error handling routine
The definition of local error routines determines what response is made when, during processing of a procedure statement in the context of the COMPOUND statement, an SQLSTATE ≠ '00000' is reported.
local procedure variable
Local procedure variables are variables which can only be addressed in the COMPOUND statement. A data type and, if required, a default value is defined for them. They have no indicator variable.
lock sequence
Lock sequences play an important role in the administration of the DBH. A lock sequence can be specified in the form of a time period. During this time period all locks on database catalog lists and spaces requested by the user are activated. The lock sequence is initiated using the DBH administration statement BEGIN-LOCK-SEQUENCE and concluded using END-LOCK-SEQUENCE. This closing statement also implicitly releases all locks.
locking concept
See transaction concurrency
locking granularity
Unit of data to be locked, e.g. rows, records, tables.
logical after image
Record or part of a record within a block after it has been updated. Logical afterimages (LAIs) are logged to DA-LOG files and CAT-LOG files (as part of media recovery).
logical before image
Record or part of a record within a block before it has been updated. Moreover, the logical before-image (LBI) contains system-internal rollback information. LBIs are used by transaction managementto roll back open transactions.
logical data saving
See DA logging
logical database name
Name used by the application program to access a SESAM/SQL database. The logical database name may be the name of an existing database. In this case, the logical database name is identical to the physical database name. f no SESAM/SQL database with the logical database name exists, the logical database name is assigned to an existing database using the physical database name in the SQL database catalog list.
logical file (CALL DML)
A portion of a CALL DML table defined by the user. It is the user-specific view of a CALL DML table.
A logical file is identified by a file identifier.
longlock
Status where a transaction has been inactive for a long period of time (lock time) and occupies resources. The DBH automatically releases longlocks by rolling back transactions that exceed the lock time value. The default lock time value is 4 minutes. This can be changed either by means of a DBH option, or during operation by means of an administration statement.
main function
Function which allows processing of a number of utility monitor forms as one unit.
media catalog
The media catalog contains storage information for certain DBH-specific files (cursor file, temporary file, TA-LOG file, WA-LOG file).
It indicates the volume, catalog ID and storage assignments for the files to be created. The media catalog is created by the system administrator using DBH options. The media catalog is not held in a file; it resides in the DBH for the course of a DBH session.
media recovery
Recovery measure designed to protect data against loss, e.g. due to a hardware error. Media recovery includes data backup and DA logging as well as recovering data to a previous or the current state.
Recovering data to a previous state conveys data to the state of consistency it was in at the time the backup copy was taken. Recovering data to the current state means that all the changes effected in the database or a space since the last copy was taken are applied. This conveys the database to the state it was in when the failure occurred.
In SESAM/SQL, the smallest recovery unit is the space.
media table
The media table contains the characteristics of and the storage devices for database-specific files. The media table is held in the catalog space. The user can query the contents of the media table using the views of the information schemas (see schema). When creating a database-specific file, the DBH refers to the information kept in the media table. The media table can be processed using the utility statements CREATE/ALTER/DROP MEDIA DESCRIPTION FOR.
metadata
Type of data required by the database system to manage the user data, e.g. to describe its structure. Metadata is held in the catalog space.
migration
Refers to the conversion of databases (base tables) created under SESAM/SQL V1.x into base tables of a SESAM/SQL database of the current version. Migration is effected using the utility statement MIGRATE. Depending on the characteristics of the database to be migrated, migration results in tables of different table styles.
mirror disk
Disk set consisting of at least two disks having identical contents.
mixed mode
Operating mode enabling SESAM/SQL databases to be processed both using the SQL and the CALL DMLinterface.
monadic operator
Operator with just one operand. The signs + and - are examples of monadic operators.
multi-db operation
Operating mode enabling each user to work with a maximum of 254 databases at the same time.
multiple attribute
See multiple column
multiple column
Column which can contain more than one value of the same data type for each row. Each of these values is called an occurrence.
Synonyms: multiple attribute, multiple field
multiple field
See multiple column
multitasking
The multitasking architecture can be used to load the DBH with multiple tasks in conjunction with high performance requirements. This allows the DBH load to be distributed to several processors in multi-processor systems.
multithreading
Method enabling the DBH to utilize the CPU more efficiently.
Multi-threading allows the DBH to process multiple requests at the same time by means of threads. Each thread contains information on the current status of a particular request. If a request has to wait for an I/O operation to be completed, the DBH uses the CPU to process another request.
The number of threads and thus the number of parallel requests can be defined by the system administrator using a DBH option.
multi-user operation
Operating mode enabling multiple users to work with one DBH at the same time.
noncharacter
Noncharacters are Unicode characters which are permanently reserved for internal purposes and may not occur in SQL data. They comprise the 66 Unicode characters U&'\FDDx', U&'\FDEx', U&'\+0xFFFE', U&'\+0xFFFF', U&'\+10FFFE' and U&'\+10FFFF', where x is a hexadecimal digit. In SQL, their use in literals or host variables results in an error.
non-repeatable read
Phenomenon that can occur if a particular isolation level has been specified.
A transaction reads a row without locking it against the access of other transactions. A second transaction updates or deletes this row and commits it (COMMIT WORK, end transaction) while the first transaction is still open. If the first transaction reads the row again, it either finds the values to be changed or it fails.
non-significant attribute value
For tables of the CALL DML table style, an attribute value containing exclusively default value characters. This value is not stored in the database. This type of default value character must not be confused with the SQL default value.
normalize
Conversion of a Unicode string in which characters and strings which can be represented in various ways in Unicode are represented uniformly. In Normalization Form D, characters are dismantled as far as possible; in Normalization Form C, compound characters (e.g. “Ä”) are also used. In SQL, Unicode strings should be stored in Normalization Form C.Normalization is offered by SESAM/SQL in the SQL function NORMALIZE().
NOT NULL constraint
Integrity constraint that requires a column to contain a value other than the null value. If a NOT NULL constraint has been defined for a particular column, SESAM/SQL prevents the null value from being entered in this column of a particular table row.
NULL value
Special value that indicates that the contents of a column or the result of an expression is undefined or unknown.
occurrence (of a multiple column)
See multiple column
outer join
Join operation that results in the derived table containing not only the result of an inner join but also each row in the join column of one table for which no matching value could be found in the join column of the other table. The missing values are represented by NULL values in the derived table. The keywords LEFT, RIGHT and FULL can be used to define from which of the two tables the additional rows are to be fetched.
partial replication
A replication that does not contain all the spaces of the original database.
partition
Area ( rows ) of a partitioned table which is saved on a space. The partition boundaries are determined via intervals of the primary key value.
partitioned table
Base table with a special physical structure, the partitions. Up to 16 partitions can be defined for a partitioned table.
password (CALL DML; SEPA)
Only applies to tables of the CALL-DML table style. Three-character string which enables the CALL DML user to access protected CALL DML tables.
A password can allow access to individual records (rows) or attributes of a CALL DML table, or a particular access mode, i.e. read, write, read/write. The passwords and information on the access authorization are kept in the password catalog. The password catalog is created using the SEPA utility.
password catalog (CALL DML)
The password catalog only applies to tables of the CALL DML table style . It is only activated if the CALL-DML interface is used. The password catalog is created and maintained using the SEPA utility. It contains the list of passwords and information on access authorization required for access control.
A CALL DML table that is subject to SEPA access control is assigned its own password catalog on the same space .
PBI file
database-specific file . PBI files are required by SESAM/SQL to ensure the consistency of SESAM backup copies (as disk copies or with ARCHIVE) created online using the COPY utility statement. SESAM/SQL logs the physical before-images (PBIs) of all blocks which are updated by SQL or CALL DML statements in the course of a copy operation.
persistent data
Data is called persistent if it exists until it is deleted by the user. This is not true of data which is deleted automatically at the end of a transaction or session, or an ESQL-COBOL program.
phantoms
Phenomenon that can occur if a particular isolation level has been specified.With a query (e.g. SELECT expression, query specification), a transaction selects rows from a table that satisfy certain conditions. While this transaction is still active, another transaction adds rows to the table that also satisfy the condition. If the first transaction repeats the query, the result also contains the added rows.
physical after image
Block after an update. The physical after-image (PAI) is required to ensure physical consistency in the event of a restart.
physical before-image
Block before an update. Physical before-images (PBIs) are required by SESAM/SQL to ensure the consistency of backup copies of the databasecreated online using the COPY utility statement. PBIs are also needed to restore physical consistency after a restart.
physical database name
Name of an existing SESAM/SQL database. A physical database name may differ from its corresponding logical database name, which is used to access a SESAM/SQL database.
plan buffer
Memory area maintained by the DBH to buffer SQL access plans .
pragma
Special SQL comment. Provides information for executing an SQL or utility statement . A pragma affects the entire statement, including the views used. The PREFETCH pragma even affects all operations with a cursor .
predicates
Operation contained in a search condition. Predicates can return: true, false, or unknown.
prefetch buffer
Area of memory in which the rows read in by a FETCH NEXT statement are buffered in block mode.
prefetch cursor
Cursor for which block modewas activated by the PREFETCH pragma.
preparable SQL statement
SQL statement that is not compiled until the program in the host language (e.g. COBOL) is executed. Thus, database queries which were not known when the program was generated can be formulated dynamically.
primary index
See index Synonym: primary key index
primary key
One or more columns that uniquely identify a row in a table. A primary key is defined using a primary key constraint. Only one primary key may be defined for each table.
primary key constraint
Integrity constraint for a column or combination of columns of a table. The primary key constraint requires that the UNIQUE constraint be fulfilled and that the associated column (combination) not contain the null value. Only one primary key constraint may be defined for each table.
primary key index
See index
privileges
Privileges define the SQL statements the user is permitted to execute on a table or column. SESAM/SQL distinguishes between SELECT, INSERT, UPDATE, DELETE, EXECUTE and REFERENCES privileges. In addition to these, there are a number of special privileges.
Synonym: access authorization
procedure
A procedure is used to store sequences of SQL statements in the database which can be executed later with a single call. A procedure is comparable to a subroutine which runs entirely in the DBH, in other words without exchanging data with the application program.Procedures are called using the SQL statement CALL. They have input and output parameters.
Synonym: Stored Procedure
procedure parameter
Call parameter of a procedure. A name, a data type, and the type of procedure parameter (IN, OUT or INOUT) must be specified for each procedure parameter.
procedure statement
A procedure statement is an SQL statement which may be used in a procedure. Every procedure contains precisely one procedure statement. SESAM/SQL recognizes the following procedure statements:
SQL statements without cursor: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL
SQL statements with cursor: OPEN, FETCH, UPDATE, DELETE, CLOSE
SQLstatements for procedure control: COMPOUND, IF, LOOP, LEAVE, SET
projection
Selected set of columns from one or more tables that is included in a derived table.
pubset
Pubsets are sets of shared (public) disks which provide a file storage location in BS2000. One of the most attractive features of public volume sets is the fact that, as well as the files themselves, they also contain all the metadata required for file management purposes (file catalog, user catalog, etc.). As well as public volume sets, BS2000 also supports private volumes.
qualified name
A qualified name is used to identify an SQLobject by adding a higher-level SQL object. A qualified name is followed by a dot in front of the lower-level SQL object. By adding the schema name, it is thus possible to distinguish tableswith identical names in different schemas.
query expression
Element of an SQL statement that is used to define a new table (called a derived table) on the basis of base tables and views. A query expression can be a SELECT expression, a join expression, or a combination of SELECT expressions and join expressions linked by the keyword UNION.
record lock
A record lock is applied by a transaction to prevent other transactions from accessing this record (or row) (see transaction concurrency ).
recovery
Generic term for all backup and recovery methods supported by database systems. On the one hand, a recovery process allows you to quickly restart the database system when inconsistencies are found. On the other hand, it protects against loss of data due to hardware errors or system failures.
There are three principal concepts:
transaction recovery
restart (external/internal) of the DB or DB/DC systems (also called system recovery)
media recovery
REF value
Each BLOB is assigned a unique REF value which references the associated object. SESAM/SQL uses this REF value to access the object in question.
referential constraint
Integrity constraint defined by the foreign key.
In the case of single-column foreign keys, the referential constraint requires that each non- null value of the foreign key of a table (called the referencing table) which satisfies the UNIQUE constraint be contained in a particular column of another table (called the referenced table).
In the case of a multiple-column foreign key, the referential constraint ensures that each non-null value combination is contained in a particular column combination of the referencing table. This combination of columns must fulfil a UNIQUE constraint.
regular expression
Regular expressions are precisely defined search patterns. They are a powerful means of searching large data sets for complex search conditions . They have long been used, for example, in the Perl programming language. In SESAM/ SQL they can be used in the predicate LIKE_REGEX.
relation
See table
remote access
With distributed processing with SESAM/SQL-DCN: access of an application program to a database in another configuration.
reorganization
The purpose of reorganization is to physically cluster logically associated blocks. It takes into account free space reservation, however. Reorganization is also applied to spaces that are physically relocated when they have been assigned to a new storage group by the database administrator. Both reorganization and relocation are started using the REORG utility statement.
replication
Copy of a database which can only be used for recovery in DML operation. This is a defined version of an original database which can be updated and restored to a defined status by means of the DA-LOG file. Replications can also be used to repair or reset an original database or to create a new original database.
requesting user
In timesharing mode, a requesting user is an interactive or batch program. In transaction mode, it is a terminal (UTM terminal in the case of UTM) or a combination of a (UTM) terminal and a (UTM) user ID.
restart
Also called system recovery. Recovery method for restarting operation following an error.
Transactions which are open at the time of the failure are rolled back to the most recent valid consistency point by the DBH , i.e. all the participating databases are in a consistent state. Depending on whether the current DBH session is interrupted or not, the DBH executes an external or internal restart. Restart is only possible if the DBH is running under transaction management .
retrieval
Reading the data in a database .
routine
Generic term for procedure and User Defined Function (UDF) in SESAM/SQL. Procedures and UDFs have an almost identical scope of functions, but they differ in how they are called and in their return information.
row
Ordered sequence of values arranged horizontally in an SQL table .
Synonym: Tupel
Scaliger's Julian day number
Integer value which specifies the number of days which have passed since a certain start date. For historical reasons, the starting date is 24th November 4712 B.C. (in accordance with the Gregorian calendar). This date has the Julian day number 0. The dates permitted by SESAM/SQL, 0001-01-01 to 9999-1231, correspond to Julian day numbers from 1721426 to 5373484.
schema
Schemas are held in the catalog space of a database .
They are subdivided into two categories: user-defined schemas and information schemas.
User-defined schemas contain the metadata for the base tables and views in the database. Moreover, user-defined schemas contain information on privileges . A user-defined schema is assigned a name and an owner who can access it using his/her authorization identifier . User-defined schemas are created using the SQL statement CREATE SCHEMA and can be modified using various other statements (e.g. CREATE TABLE).
There must be two information schemas for each database: INFORMA TION_SCHEMA and SYS_INFO_SCHEMA. They enable the user to access the metadata contained in the user-defined schemas, such as base tables , views , integrity constraints , privileges , etc.
The INFORMATION_SCHEMA can be accessed by every user using SQL. The SYS_INFO_SCHEMA contains system-specific data and can only be accessed by the universal user .
search (CALL-DML)
Central CALL DML statement for the retrieval of data.
search condition
SQL language resource which returns a truth value of true, false or unknown. It comprises predicates which can be linked by the logical operators AND, OR and NOT. The search conditionis uses to restrict the quantity of rows in a derived table (e.g. if the WHERE clause of a query expression is used).
If the search condition is used in a check constraint, it restricts the values permitted for the column(s) concerned.
search with join (CALL-DML)
Feature that joins two logical files. The values of the join attribute (see join column) in one logical file are compared with the values of the join attributes in the other logical file two at a time. If the values match, the corresponding records (or rows) are joined and considered in the returned result.The two logical files can belong to the same base table or two different base tables.
secondary index
See index
SELECT expression
Query expression starting with the keyword SELECT. Its syntax is restricted.
select list
The select list defines the columns to be included in the derived table. It contains the names of columns from one or more tables as well as any other type of value expression.
selection
Set of Rows retrieved from one or more tables . The rows must satisfy defined conditions.
server
See client/server architecture
service task
One of various possible BS2000 tasks to which SESAM/SQL relocates CPU- intensive activities. For example, service tasks can be used for database administration functions and for sorting intermediate results.
SESAM backup copy
Copy created with the SESAM statement COPY.
SESAM CLI (Call Level Interface)
Procedural interface for complex operations of SESAM/SQL which are either cumbersome or impossible to execute with SQL statements.
SESAM/SQL DBH
See database handler
SESAM/SQL-DCN
Add-on product for processing distributed databases with SESAM/SQL.
SESAM/SQL-specific statements
Type of statements in SQL syntax that is not part of the SQL standard, e.g. utility statements.
SESDCN control statement
SESDCN control statements include the DCN options and the statements for defining the distribution rule.
The system administrator enters SESDCN control statements when starting the SESDCN distribution component.
SESDCN distribution component
Main component of the SESAM/SQL-DCN product. The principal tasks of SESDCN are the generation and maintenance of the distribution rule as well as the receiving and passing on of remote accesses . Furthermore, SESDCN has administrative and monitoring functions.
session
See DBH session, SQL session
set function
Function used to calculate a value from a set of column values (AVG, MAX, MIN, SUM, COUNT) or rows (COUNT*).
single primary key
Primary key that consists of only one column.
single system image
The SESAM/SQL DBH task family presents itself to the system administrator as a unit. The individual tasks are not relevant for administration purposes (see multitasking ).
space
Named memory area which is held in a BS2000 file and assigned to a storage group. There is one catalog space and one or more user spaces for each database . Spaces are the organizational units for reorganization and recovery .
space list
Recovery unit required for media recovery, which consists of a number of user spaces. For the purposes of recovery, it is possible for users to create a space list themselves from several user spaces with an identical time stamp. Spaces with a common time stamp are created by a shared copy statement. The time stamp indicates the time the copy was taken and must be identical for all user spacesin the space list.
space set
Recovery unit required for media recovery, which consists of a number of user spaces. A space set is created by a shared COPY statement involving several user spaces, and is identified by its time stamp. The time stamp indicates the time the copy was taken and must be identical for all user spaces in the space set.
special privileges
Special privileges are used to describe which definition and administration statements an SQL user can execute. SESAM/SQL distinguishes between the following special privileges: CREATE USER, CREATE SCHEMA, CREATE STOGROUP, UTILITY and USAGE ON STOGROUP.
SQL
The language most commonly used for processing relational databases. In contrast to the procedural languages of non-relational database systems, SQL is descriptive, i.e. the user describes, in set-oriented form, the result of a database operation rather than the steps required to get there. SQL offers extensive language resources for data definition, data manipulation, transaction management, access control, and so on. Embedded SQL (ESQL) makes it possible to access a database using embedded SQL statements from host language programs (e.g. in COBOL). SQL was first standardized by the International Organization for Standard- ization in 1987. SESAM/SQL is based on the up-to-date standard, referred to as SQL standard . SESAM/SQL supports the language core defined in SQL (referred to as Core SQL), optional language constructs of SQL, and supple- mentary functions which are not provided in SQL ( utility statements ).
SQL access plan
Evaluation rule which the DBH creates internally for every SQL statement. An SQL access plan comprises at least one subarea, usually more, referred to as SQL scans. The optimized format of a scan forms the internal statement format.
SQL database catalog list
Contains an entry for every database to be processed in the course of a DBH session. The DBH uses the SQL database catalog list to obtain information on which database is assigned to which BS2000 user ID.
The system administratorcreates the SQL catalog list using a DBH start statement.
The system administrator can add entries to, or delete entries from, the SQL database catalog list using administration statements.
SQL default value
Value specified as the default value for a column in the DEFAULT clause of the CREATE TABLE statement. It is automatically assigned to this column. The SQL default value must not be confused with the non-significant attribute value.
SQL mode
Operating mode of an application program that starts with the execution of an SQL statement within a transaction and terminates when the transaction is terminated.
SQL return code
See SQL status code
SQL scan
Part of an SQL access plan referring to exactly one base table. The SESAM/SQL DBH generates an SQL access plan for each SQL statement.
Synonym: scan
SQL session
Sequence of SQL statements. A session starts when SESAM/SQL is initiated and ends when the connection is cleared.
SQL standard
Short name used in the SESAM/SQL manuals for the up-to-date international SQL standard.
SQL status code
The SQL status code (SQLSTATE) contains information on whether an SQL statement has executed with or without error.
Execution without error results in one of the following status codes: “successful completion”, “warning” or “no-data”.
If the execution terminates with error, the SQL status code contains information on the type of the error.
The SQL return codes (SQLCODEs) are no longer contained in the SQL standard and are only supported for reasons of compatibility to earlier SESAM/SQL versions. They have the same function as SQL status codes, but are not as detailed.
SQL table
See table style
SQLCODE
See SQL status code
SQLSTATE
See SQL status code
storage group
A named set of disks under the same catalog ID. All the disks must be of the same device type. Storage groups allow the user to control where the spaces (BS2000 files) are created. Every space is assigned to a storage group. The space is then created on the disks of this storage group.
storage structure language (SSL)
Statements used to administer the storage structure; SSL is used to create and process storage groups, spaces and indexes. Storage Structure Language is not a component part of the SQL-Norm.
suborders
When SQL statements are processed, suborders are identical with SQL scans, i.e. subareas of an SQL access plan. In the case of CALL DML, suborders are logical files for CALL DML requests.
subquery
Query expression enclosed in parentheses returning a simple column value, a row comprising a number of column values, a column or a table.
surrogate pairs
In UTF-16, a sequence of two code units which represent a Unicode character with a code point greater than U&'\FFFF'. NX'D800' through NX'DBFF': Leading Surrogate; NX'DC00' through NX'DFFF': Trailing Surrogate.
A leading and a trailing surrogate pair map the code points from U&'\+010000' through U&'\+10FFFF'.
symbolic attribute name (CALL DML)
Three-character name of an attribute, which identifies that attribute in CALL DML statements.
synonym
See correlation name
SYS_INFO_SCHEMA
See schema
system administration
Area of responsibility covering the start and termination of the DBH and administration of the current DBH session.
system administrator
Person or group of people responsible for system administration.
Synonym: SESAM/SQL system administrator
system data buffer
Area in main memory which the DBH reserves and manages for system data.
system entry
A pair, comprising the system user identification and the authorization identifier (for an SQL user). The system entry permits a user to access a SESAM/SQL database. A system entry is created using the SQL statement CREATE SYSTEM_USER. This does not apply to the universal user.
system user identification
Identifies a TIAM or openUTM user in BS2000.
A TIAM user is identified by the (symbolic) computer name and the BS2000 ID.
A UTM user is identified by the (symbolic) computer name, the name of the UTM application and the KDCSIGN or LSES name.
To work with a SESAM/SQL database, a TIAM or UTM user must be assigned an authorization identifier (see system entry ).
table
A table is a two-dimensional arrangement of data elements comprising rows (horizontal) and columns (vertical).
A distinction is made between base tables , views , derived tables , and abstract tables .
Synonym: relation
table constraints
Integrity constraint, defined as a property of a base table with CREATE TABLE or ALTER TABLE. It can be a UNIQUE constraint, a referential constraint or a check constraint.
table space
User space in which at least one base table is stored. The table space of a specific base table is the space where that base table is stored.
table style
This specifies whether a base tablecan be processed via the SQL interface and/or the CALL DML interface. The table style is of particular importance in the context of migration. The following different table styles are distinguished:
tables which can be processed with CALL DML only (CALL DML only tables)
tables which can be processed with CALL DML and (with certain restric- tions) with SQL (CALL DML/SQL tables)
tables which can be processed with SQL only (SQL tables).
tables which are used exclusively for storing BLOBs (BLOB tables)
CALL DML only tables and CALL DML/SQL tables are subsumed in the CALL DML table style. A non-significant attribute value must be declared for every attribute in a CALL DML table.
TA-LOG buffer
Area in main memory in which the DBH buffers the backup information for transaction management. If the buffer overflows or a consistency point is inserted, the DBH transfers the contents of the TA-LOG buffer to the TA-LOG files.
TA-LOG files
Backup files containing the information for transaction management , transaction recovery and for internal and external restarts . The contents of the TA-LOG files include before-images and after-images .
Synonym: transaction log files
thread
See multithreading
timesharing mode
In timesharing mode, a number of users work independently in batch or interactive mode, each with a separate application program.
transaction
Sequence of related statements which move a database from one consistent state to another consistent state. A transaction is performed either in its entirety or not at all.
Special statements are available for transaction processing:
CALL DML has one statement each for the following operations: opening, closing (committing) and rolling back a CALL DML transaction.
SQL has special statements for ending and rolling back a transaction only. In openUTM applications, the corresponding UTM calls must be used for this purpose. There is no special SQL statement for defining the start of a transaction: the first transaction-initiating statement after the previous transaction has been committed or rolled back or after the program has been started is taken by SESAM/SQL to be the start of the transaction.
transaction concurrency
Simultaneous access by different transactions to the same data resources.The locking concept in SESAM/SQL means that the consistency of the data processed is ensured, even during concurrent access. Normally, every trans- action locks the rows it is accessing against access by other transactions. The lock is only lifted when the transaction has been completed. It is, however, possible for the user to change the locking behavior to suit requirements by means of the isolation level and thus to increase the level of transaction concurrency and consequently the throughput. In SQL applications the isolation level is used for this purpose.
transaction log file
See TA-LOG file
transaction management
Transaction management provides mechanisms for handling error situations automatically and, in the event of an error, uses rollback mechanisms to restore the consistency of the data resources. This means that transaction management forms the basis for the recovery mechanisms transaction recovery and restart (internal/external).
transaction mode
In transaction mode, several users use the same application program simulta- neously to access one or more databases. The openUTM transaction monitor or the DCAM data communication system is responsible for coordinating the application users.
transaction processing
This ensures that a database can only be accessed within a transaction. This means that the consistency of a database is ensured at all times.
transaction recovery
Recovery mechanism for resetting an individual transactionafter a user error.
transaction status
See access mode of transaction
transaction-initiating SQL statement
Transaction-initiating SQL statements include all statements other than DECLARE CURSOR, SET TRANSACTION, SET CATALOG, SET SCHEMA, SET SESSION AUTHORIZATION, PERMIT, WHENEVER, ALTER TABLE with pragma UTILITY MODE=ON and the utility statements.
transcoding
Conversion of a national string from the UTFE character set to the UTF-16 character set. In SQL a transcoding is performed explicitly with the SQL function TRANSLATE(). See also: transliteration.
transfer container
Area of main memory which the DBH reserves and manages for SQL scans and for the query and response areas of logical files.
transliteration
Conversion of an alphanumeric string into a national string and vice versa. In SQL a transliteration is performed explicitly with the SQL function TRANSLATE(). If required, implicit transliterations are executed with the SQL statement ALTER TABLE ALTER COLUMN and with the utility statements LOAD
and UNLOAD. See also transcoding .
tuning
Optimization of the performance (response behavior, use of resources, throughput) of the database system .
tuple
See row
two-phase commit
Terminates a transaction in two phases during distributed processing:
The “provisional end of the transaction” request (PREPARE TO COMMIT) first initiates the end of the transaction. All the components involved in the transaction now try to achieve a state in which the transaction can be both rolled back and committed. If one or more of the DBHs involved in the distributed processing cannot achieve this state, the entire transaction is rolled back. Otherwise, a second phase issues the final request “end of transaction”.
uncorrelated function call
Function call of a UDF with constant input values. Constant input values do not refer to the SQL statement which contains the function call.
union join
Join operation whose derived table contains rows from the table on the left of the UNION operator and from the table on the right of the UNION operator, each supplemented by the columns in the other table which are set to NULL values.
UNIQUE constraint
Integrity constraint for a column or combination of columns of a table. Integrity constraint applied to a column or column combination to ensure that no two rows of a table have the same value or value combination for the specified column (combination). If the UNIQUE constraint has been defined, SESAM/SQL prevents this from happening.
UNIQUE index
See index
Universal Character Set 2 (UCS-2)
In UTF-16 all Unicode characters from NX'0000' through NX'FFFF' are encoded in 2 bytes. The characters in this range are also referred to as Universal Character Set 2 (UCS-2).
universal user
SQL user with comprehensive privileges defined for every database when the database is generated with the utility statement CREATE CATALOG. The universal user is described by the first system entry.
The SQL statement CREATE SYSTEM USER allows the universal user to create additional system entries with the privileges of the universal user. This is done by assigning the universal user system entry to other system user identifications.
updatable
A View or cursor is called “updatable” if it can be used to update the underlying base table(s). The corresponding privileges for the base table(s) are required regardless of this capability.
user data
Data in the user spaces of the database. The user data is distinguished from the metadata in the database's catalog space.
user data buffer
Area in main memory which the DBH reserves and manages for user data.
User Defined Function (UDF)
A UDF is used to store sequences of SQL statements in the database which can be executed later with a single call. A UDF is comparable to a subroutine which runs entirely in the DBH, in other words without exchanging data with the application program.
UDFs can be used in almost all expressions by means of their function call. They have input parameters and supply a return value.
user space
Space containing the user's tables and indexes as opposed to the catalog space.
user view
see View
UTF-16
Unicode encoding defined by the Unicode Consortium. In the case of UTF-16, all Unicode characters from NX'0000' through NX'FFFF' are encoded in 2 bytes.
All characters above NX'FFFF' are represented by 4 bytes, so-called surrogate pairs. SESAM/SQL stores values internally in UTF-16 format in columns of the data type N[VAR]CHAR and also uses UTF16 for national values in host variables.
UTF-8
Unicode encoding defined by the Unicode Consortium. UTF-8 uses a variable number of bytes (one to four bytes) for encoding the Unicode characters. The byte represenatation of the ASCII characters remains unchanged. In the case of a character which is encoded in several bytes, none of the individual bytes represents a valid character.
UTF-EBCDIC (UTFE)
Unicode encoding for machines which use the EBCDIC character set.Here the Unicode characters from NX'0000' through NX'009F' are represented by the corresponding EBCDIC characters, and all other Unicode characters by strings of 2 to 5 bytes.
In SESAM/SQL, Unicode with UTF-EBCDIC coding can be used in the case of the UNLOAD and LOAD utility functions with DELIMITER_FORMAT.
utility mode
Mode which the user can set when adding, changing, or deleting one or more columns of a base table using the pragma UTILITY MODE. When the utility mode is set, the associated SQL statement cannot initiate a transaction and transaction loggingis switched off.
utility monitor
Database administration tool with the following functions:
execution of SQL and utility statements
evaluation of INFORMATION_SCHEMA and SYS_INFO_SCHEMA for the catalog space
reading and updating of the metadata for the catalog recovery file and for the spaces
The utility monitor also allows you to call the administration program SESADM as a subroutine.
You can choose to use the utility monitor in interactive mode using a menu- driven interface or to have it read its input from a statement file.
utility statement
Utility statements are statements which use SQL syntax and which make available utility functions for database administration. The utility statements include statements for generating, loading, unloading, copying, reorganizing and migrating databases.
The utility statements cannot start transactions and do not form part of the SQL standard.
value expression
The result of a value expression can be a numeric, character-string, or date/ time value. It stands for a column of a table, a constant , a set function , a host variable , or a subquery . Any of these elements combined by operators can also be a value expression.
vector
Data object comprising a certain number of values of the same data type.
vector variable
Host variable for a vector used for storing the different occurrences of a multiple column .
view
Named virtual table which is derived from one or more tables . A view is defined in a query expression using the SQL statement CREATE VIEW. The derived table produced by the query expression is generated anew every time the view is referenced in an SQL statement. The derived table thus always contains the most current data from the database.
Synonym: user view
WA-LOG file
Information on control and backup of the DBH session and on the DBH restart is stored in the WA-LOG file. Each SESAM/SQL DBH has its own WA-LOG file. Among other things, the WA-LOG file contains the following:
the DBH options which the DBH uses for the restart
the physical before images
information that describes the progress of the restart process
information on synchronization with openUTM.
work container
Memory area maintained by the DBH for buffering internal statement formats.