HP 3000 Manuals HP 3000 Manuals
GLOSSARY Ad Hoc Query Type of query that is issued for the needs of a particular moment. It is usually not stored for later use or built into an application. Ad hoc queries are important in the use of relational databases for decision support. Archive Logging Logging method that uses log files to roll back incomplete transactions after a system failure and to roll forward from an earlier DBEnvironment backup. Uses a relatively large file or set of files to record all activity that modifies databases from the point at which you do a backup of the entire DBEvironment. If the logs are intact following a hard crash, you can recover a DBEnvironment from an earlier saved version. Attribute A characteristic of a data element considered during database design. As you organize your data, you arrange it into categories that possess similar attributes. The categories are known as entities. Audit Log Records Log records used by ALLBASE/Replicate to transfer the transaction information. audit_name Specified in the START DBE NEW or START DBE NEWLOG statements and uniquely identifies the DBEnvironment that generates the audit log record. Each audit name must be unique throughout the network where replication is taking place. Authority Permission to access specific objects for specific purposes within an ALLBASE/SQL DBEnvironment. Three major types are SPECIAL authority, TABLE authority, and RUN authority. Authorization Group See Group. Base Table Table upon which a view is based. Check Constraint An integrity constraint that enforces a condition that must not be false for the columns of a table. Any value you attempt to insert into a column that has a check constraint defined on it must either satisfy the condition or be NULL. Class Special category of ALLBASE/SQL owner that is neither a particular DBEUserID nor a group. You do not explicitly create a class; you create it implicitly by creating objects owned by it. A class does not have members like a group. Objects owned by classes can be dropped or modified only by a DBA. A class does not have a password associated with it. Clustering Index An index which attempts to locate new rows in physical proximity to other rows with similar key values. Valuable when a large number of inserts follows a similar large number of delete operations. Column Vertical division within a database table. Analogous to a field in a file. Column Authorization Permission to update a specific column within a table. Column List One or more columns specified as part of a query result. Concurrency The ability of multiple users to access the same database files simultaneously. Concurrency is regulated by locking, which controls the degree of concurrent access permitted--from exclusive read or write access to shared read with concurrent updates. Constraint A condition placed upon a column or table that requires values in the column or table to meet certain conditions before a row can be inserted or deleted. Two types supported by ALLBASE/SQL are unique constraints and referential constraints. Cursor Stability (CS) An isolation level that guarantees that any data on the page you are currently accessing cannot be updated by other users until you move off that page. This offers a greater degree of concurrency than Repeatable Read, which is the default isolation level. Data Analysis Study of raw data before building a database. Concerns the kind of data that is to be stored and how the data is to be used. Database A structured arrangement of data elements designed for the easy selection of information. In ALLBASE/SQL, a database is a collection of tables, views, and indexes having the same ownership in a DBEnvironment. A DBEnvironment may contain several databases. Database Administrator (DBA) The individual with DBA authority who creates and maintains objects in a DBEnvironment. DBA authority permits the use of certain restricted SQL and SQLUtil commands or options, and also confers co-ownership of all the objects in a DBEnvironment. Database Design The creation of a specific arrangement of data in tables or data sets with an appropriate security structure. Data Control Language The set of SQL commands that control access to data. This includes the ADD, REMOVE, GRANT, and REVOKE commands, as well as the commands to create, manage, and drop authorization groups. Also known as DCL. Data Definition The process of creating and dropping database objects. Data Definition Language The set of SQL commands that create and drop database objects. This includes the commands to create and remove DBEFileSets, DBEFiles, tables, views, and indexes. Also known as DDL. Data Manipulation The process of access data within a database. Data Manipulation Language The set of SQL commands that access data. This includes the actions of selecting data, inserting rows, updating columns, and deleting rows. Also known as DML. Data Type A kind of data that can be stored in database tables. Valid types are CHARACTER, VARCHAR, INTEGER, DECIMAL, FLOAT, DATE, TIME, DATETIME, INTERVAL, BINARY, and VARBINARY. LONG varieties of BINARY and VARBINARY are also available. DBA Authority The most powerful authority within an ALLBASE/SQL DBEnvironment. Includes the authority to create new objects, drop all existing objects, and grant or revoke all authorities for other users. DBA authority implies co-ownership of all objects within the DBEnvironment. DBCore A central component of ALLBASE/SQL that performs physical file access and logging. DBCore also provides concurrency control through the use of isolation levels and locking. DBECon File DBEnvironment Configuration File. This contains startup parameters for the DBEnvironment. The contents of this file are initially determined at the time you issue the START DBE NEW command. You can modify some of these parameters using SQLUtil, and you can override some of them with the START DBE command. DBECreator The individual who issues the START DBE NEW command. Some maintenance operations require you to be the DBECreator. DBEFile File containing data or indexes or both. A DBEFile of type TABLE can only contain table data; a DBEFile of type INDEX can only contain index data; a DBEFile of type MIXED can contain both table and index data. DBEFiles are operating system files and are named according to the conventions of the operating system. DBEFileSet Logical grouping of DBEFiles. You associate newly created DBEFiles with a DBEFileSet, and you specify a DBEFileSet when you create a table. DBEnvironment A collection of files containing one or more databases. Files include the DBECon file (which holds startup parameters and log file names); DBEFile0, which contains the system catalog; and log files. A DBEnvironment may also contain additional DBEFiles for table and index data. The DBEnvironment is the maximum scope of a transaction within ALLBASE/SQL. DBEUserID In HP-UX, a login name. In MPE/iX, a login name and account name joined with the character '@'. One type of owner of database objects. Direct Update When a table receives an update through a user application, the ISQL interface, or a third-party application, it is a direct update. Embedded SQL Program An application program incorporating SQL statements for programmatic access to ALLBASE/SQL databases. Each embedded SQL statement begins with the keywords EXEC SQL. Embedded SQL programs are preprocessed, then compiled before execution. For most SQL commands, the preprocessor stores a section, or runtime version of the command, in the DBEnvironment. Entity Basic subdivision of data elements in database design. Each entity is a thing or event about which information is kept in the database. For each entity, there is at least one attribute that uniquely identifies a data element as belonging to the entity. Explicit Locking Locking of tables in transactions by the use of the LOCK TABLE command. Expression Specifies a value. The most common sources of values are columns in a table or host variables in an application program. Expressions are used to identify columns or rows or to define new values for columns. Foreign Key A column or columns in a table which have a relationship to a primary column or columns in a different table such that the value must exist in the primary key column before it can be inserted into the foreign key column, and it must be deleted from all foreign key columns before it can be deleted from the primary key column. global_commit_id Shows the unique identification number assigned by the master DBEnvironment to the most recently committed transaction on that partition for the SCR of a master partition. On a slave, the global_commit_id in the SCR of a partition being replicated shows the global_commit_id assigned by the master DBEnvironment to the transaction most recently committed on the slave partition. Group Authorization group. Membership in a group is used to confer common ownership or common authorization for other objects in the DBE. You create a group explicitly, using the CREATE GROUP command, then you add users to it. You can then grant authorizations to the group or revoke authorizations from the group. You can also use the group name for the ownership of database objects. Hard Resynchronization Hard resynchronization is the process of transferring a consistent complete image of the master partitions being replicated to the slave DBEnvironment. Hash Structure ALLBASE/SQL table containing rows that are stored in such a way as to permit fast access to specific tuples by means of a hash function. A hash structure provides a method for quickly finding a row by calculating its location based on the value of the hash key, which you specify when you create the table. Host Variable A variable in an application program that receives data from an ALLBASE/SQL database (output host variable) or passes data to the database from the program (input host variable). Implicit Locking Locking of tables in transactions according to table type and isolation level. For example, PRIVATE tables are locked exclusively for all access; PUBLIC tables are locked exclusively only for write operations. Index A data structure that potentially speeds access to table data through the use of an index scan. The four types of index are: unique, clustering, unique and clustering, and non-unique and non-clustering. An index is created for one or more key columns in the table. Index Scan A method of looking up each row in an index to find its location in the data file, then accessing the row in the table. This kind of access requires the existence of a B-tree index, which you must create. You do not explicitly request an index scan. Instead, SQLCore makes this choice if the query optimizer decides that the use of an index is the best way to access the data. Indirect Update When a table receives an update through the use of ALLBASE/REPLICATE, it is an indirect update. Integrity Constraint A constraint placed on the columns of a table to ensure that a database contains only valid data. Two types are the referential constraint and the unique constraint. ISQL The interactive interface to ALLBASE/SQL. ISQL is the tool you use for ad hoc queries as well as for loading and unloading data and other database administration tasks. Isolation Level The degree of separation enforced between the transactions of different users. There are four levels: Repeatable Read (RR), Cursor Stability (CS), Read Committed (RC), and Read Uncommitted (RU). You specify an isolation level in the BEGIN WORK command. Join A query that accesses data from two or more ALLBASE/SQL tables at a time. A join column is a column that occurs in both tables of a join (often it is a key column) and contains similar values in both tables. Key One or more columns on which an index, hash structure, or integrity constraint are based. Key Column A column which is indexed, or a column which participates in integrity constraints as all or part of a PRIMARY or FOREIGN key. Key Value The value contained in the columns of a key. Key values are stored in index pages along with pointers to the location of rows in data pages. local_commit_id Is used to compare the slave with the master. The local_commit_id is only for local use and is not used to compare the slave and the master. Locking A technique for concurrency control through which ALLBASE/SQL restricts access to data by one individual when the data is being used by another. Locks are of three types: shared, exclusive, or shared with intent to become exclusive. Lock type is determined by the type of table being accessed and by the kind of operation the user is performing. Locks are released when a transaction ends with a COMMIT WORK command. log_file_timestamp On the master, reflects the time the last START DBE NEW or START DBE NEWLOG was done on the DBEnvironment. This value, along with the audit_name and the global_commit_id, uniquely identify a transaction. Logging The use of log files to record operations that modify database files. Logging is of two kinds: nonarchive logging, and archive logging. Both kinds permit you to roll back incomplete transactions following a system failure. This maintains data integrity by backing out changes to the database that were not committed. Only archive logging allows you to roll forward from an earlier version of a DBEnvironment by reapplying all committed transactions up to a specific recovery time. Master DBEnvironment The source DBEnvironment is called the master DBEnvironment, or the primary DBEnvironment. Message Catalog A file containing ALLBASE/SQL error and warning messages. When a message is displayed, its text comes from this file. You can look up the meaning of the message in the ALLBASE/SQL Message Manual. Message File An error and warning file known as SQLMSG generated by a preprocessor session. It contains any errors generated during the preprocessing of an embedded SQL program. Mirror Image A near-real-time or exact copy of the tables on the master. Mixed Mode When some tables act as the master in one DBEnvironment and as a slave in some other DBEnvironment, it is referred to as a mixed mode. Modified Source File The file that results from using the preprocessor on an embedded SQL source file. The modified source file can then be compiled into an executable program. Module A group of sections stored in the DBEnvironment when an embedded SQL program is preprocessed or when you use the PREPARE command in ISQL. The sections are activated when the program is run or when the EXECUTE command is issued in ISQL. Native Language The language of the DBEnvironment or of specific CHAR and VARCHAR columns in a table. You specify the DBEnvironment's language in the START DBE NEW command using the LANG= clause. In table creation, you can use the LANG= clause as part of a character column description. The default language is known in HP-UX as n-computer; in MPE/iX, it is NATIVE-3000. Nonarchive Logging The default logging method. Uses log files to roll back (that is, undo) incomplete transactions that were not committed at the time of a system failure. Normalization A formal process of adjusting table design in relational databases by examining and adjusting the relationships among columns. Object A structure created and stored in an ALLBASE/SQL DBEnvironment. The most common objects are tables, views, indexes, and groups. Optimizer Component of SQLCore which chooses the access path in processing a query. In optimization, ALLBASE/SQL chooses whether to use serial access to the data, or whether to use an index or hash structure if they exist. If there is a choice among indexes, the optimizer calculates the best access path. Owner A DBEUserID, a group name, or a class name. Ownership applies to database objects such as tables, views, indexes, and authorization groups. The owner may drop the object or transfer it to some other owner. Partitions The set of tables in the master DBEnvironment are divided into subsets called partitions. A partition may include every table in the master DBEnvironment, or just a subset of the DBEnvironment containing one or more tables. partition_id Identifies the particular partition with which the specific SCR is associated. The same master partition_id value may be used in several different DBEnvironments. PartsDBE The sample DBEnvironment is called PartsDBE. Most of the examples in this manual are based on the tables, views, and other objects in this sample DBEnvironment. For complete information about PartsDBE, refer to appendix C of the ALLBASE/SQL Reference Manual. Predicate Part of query syntax that specifies a subset of rows to be returned in the query result. Predicates are introduced by the keyword WHERE, so they are sometimes called WHERE clauses. Predicates let you specify a range of values. The comparison predicate lets you compare a column value with a constant or host variable ; the LIKE predicate lets you compare a column value with a portion of a character string; the BETWEEN predicate specifies a range of values for a comparison. Special predicates of various kinds let you search for rows in more complex ways, including the use of subqueries. Preprocessor A component of ALLBASE/SQL that converts an embedded SQL program into a modified source file for input to a compiler in one of several programming languages: C, COBOL, FORTRAN, and Pascal. Primary DBEnvironment The source DBEnvironment, or master DBEnvironment, is the primary DBEnvironment. Primary Key A column in a table defined so as to permit reference by foreign keys in other tables. A primary key also enforces uniqueness within the column. Procedure A sequence of SQL statements that are stored in a DBEnvironment and applied as a group either through rules or through execution by specific users. Together with rules, procedures let you define generalized constraints within a database to implement the relationships in the database design. Projection Relational operation that extracts a subset of columns from a table. Query Request for information from database tables. A typical example is a SELECT statement. Query Language A set of operators, expressions, and commands that let you manipulate a database. The query language of ALLBASE/SQL is SQL. Query Result The rows retrieved by a SELECT statement. Query results are also known as result tables. Read Committed (RC) An isolation level that guarantees only that data you read in a transaction has been committed by some earlier transaction; that is, it is not currently in the process of update by some other transaction at the time you are reading it. In practical terms, this means that another transaction can update or delete the same row before your transaction is over. However, concurrency is greatly improved. Read Uncommitted (RU) An isolation level that enforces no separation between your transaction and those of others, because no locks are obtained for reads. This level permits dirty reads, that is, reading data from the data buffers that has not and may never be written to the database at all. Referential Constraint An integrity constraint that enforces a relationship between the rows of two tables. Any value you attempt to insert into a table that has a referential constraint must either be NULL or be the same as a value in the referenced table. Relation See Table. Relational Operations Ways of extracting data from relational tables. The three primary relational operations are selection, projection, and joining. Relationship The meaningful interaction of entities in database design. Relationships may be one-to-one, one-to-many, or many-to-many. Repeatable Read (RR) An isolation level that enforces the highest level of separation between the transactions of different users. This level guarantees that when you re-read any data you have read previously in the same transaction, the value seen in the second read will be the same as the value seen in the first read. In practical terms, this means that other users may not update any data you have read at this isolation level until you COMMIT WORK. Replicated When committed transactions for tables on the master are duplicated on the slave, they are replicated. REPMAST The ALLBASE/REPLICATE application that runs on the master is called REPMAST. REPSLAVE The ALLBASE/REPLICATE application that runs on the slave is called REPSLAVE. Result Table See Query Result. Resynchronization The transfer and duplication activities that bring about replication are together referred to as resynchronization. There are two kinds of resynchronization: soft resynchronization and hard resynchronization. Synchronization Checkpoint Record (SCR) Each element of the SCR array. Synchronization Checkpoint Record Array (SCR Array) The data structure used to record, on both master and slave, the transaction identifier for the most recently committed transaction in each partition. This information is used to insure that all transactions committed on the master are sequentially committed on the slave, without losing any transactions in the process. Rollback Recovery A process by which ALLBASE/SQL backs out of incomplete transactions using a log file. If a DBEnvironment stops while some transactions are still in progress, they must be undone the next time the DBEnvironment starts up. Rollforward Recovery A process by which ALLBASE/SQL reapplies transactions to a DBEnvironment from a log file. Rollforward recovery requires the use of archive logging. Row Horizontal division within a database table. Analogous to a record in a file. Rule A database object that ties the execution of a procedure to specific kinds of data manipulation performed on a database table. Together with procedures, rules let you define generalized constraints within a database to implement the relationships in the database design. Run Authority Permission to execute stored sections that perform ALLBASE/SQL queries or other operations from an application program. Required in addition to permission at the operating system level to execute the application itself. Schema A complete SQL database definition as coded in a CREATE SCHEMA statement. Also, an ISQL command file containing commands to create a DBEnvironment and the objects within it, such as DBEFileSets, DBEFiles, tables, views, indexes, and authorities. You can create a schema file with an editor, or you can generate one from an existing DBEnvironment by using SQLGEN. Also, a TurboIMAGE database definition which is the input to the TurboIMAGE DBSCHEMA program. Secondary DBEnvironment The secondary DBEnvironment is the target or slave DBEnvironment. Section An SQL command stored in the DBEnvironment for use at run time by an application program. When sections are valid, they can be executed immediately by ALLBASE/SQL. When they are invalid, they must be revalidated at run time before execution. Serial Scan A method of reading sequentially from the start of a table until the row is found. Also called table or relation scan. This is the default scan method used to access rows in a table when indexes do not exist. If indexes do exist on a table, the optimizer chooses whether to perform an index scan or a serial scan. Slave DBEnvironment The target DBEnvironment is usually referred to as the slave DBEnvironment. Soft Resynchronization Soft resynchronization is the process carried out by the ALLBASE/Replicate applications that run on the master and the slave. SQL Database A logical entity consisting of all tables, views, and other SQL objects in a DBEnvironment having the same owner. Special Authority Permission to use the DBEnvironment in particular ways. CONNECT authority lets you establish a user session. RESOURCE authority lets you create and drop objects such as tables, views, DBEFiles, etc. DBA authority gives you permission to perform all SQL and SQLUtil commands, and it grants co-ownership of all objects in a DBEnvironment. SQL See Structured Query Language. SQLCore A central component of ALLBASE/SQL. SQLCore checks the syntax of commands and prepares them for processing. SQLCore also optimizes queries, that is, chooses the best access path to the data. SQLGEN A utility program for database administrators that generates the SQL commands necessary to re-create all or part of a DBEnvironment. The output from SQLGEN is a command file (sometimes called a schema) that can be used as input to ISQL in re-creating database objects. SQLMigrate A utility program for database administrators that assists in migrating a DBEnvironment from one version of ALLBASE/SQL to another without unloading and reloading data. SQLUtil A utility program for database administrators that assists with DBEnvironment maintenance, backup, and recovery. SQLUtil also lets you modify the startup parameters for a DBEnvironment. Structured Query Language A standard query language syntax defined by ANSI standards in the United States and X/OPEN standards in Europe. The relational database query language used by ALLBASE/SQL. Subquery A query within another query. An example is a subquery embedded in the predicate of another query. The result of the inner query is used to evaluate the outer query. SYSTEM A DBEFileSet created by ALLBASE/SQL when you issue the START DBE NEW command. The DBEFile known as DBEFile0 is associated with SYSTEM, which is the DBEFileSet containing the system catalog. You can add DBEFiles to SYSTEM as you would to any other DBEFileSet. Also, a special user associated with the system views in the system catalog. System Catalog A system-maintained database of tables and views owned by the special user SYSTEM and containing information about all the objects in the DBEnvironment. Contains data about all the objects created in the DBEnvironment. Differs from the DBECon file, which contains startup parameters, not object definitions. System Table See System View. System View A component view within the system catalog. You can issue queries on the views in the system catalog just as you would on ordinary database tables to display information about the DBEnvironment. Table Basic unit of data storage in a relational database. Also known as a relation. Tables consist of rows and columns. A result table is a query result displayed in tabular form. Table Authority Permission to use specific SQL commands on particular tables. There are several kinds of TABLE authority: SELECT, INSERT, DELETE, UPDATE, and INDEX. SELECT, INSERT, and DELETE let you operate on rows or sets of rows in a table; UPDATE lets you modify specific rows or columns in a table; and INDEX lets you create indexes on a table. Transaction A unit of work in ALLBASE/SQL. Also, a unit of DBEnvironment logging and recovery. A transaction is started with a BEGIN WORK command and is ended by a COMMIT WORK command. The BEGIN WORK statement may be implicitly issued by ALLBASE/SQL if no other transaction is current. Transaction Identifier A data structure containing the global_commit_id, log file timestamp, and the audit_name essential for uniquely identifying a transaction. Unique Constraint An integrity constraint which requires that no two rows in a table have the same values in a specified column or columns. Unique Index An index which requires that no two rows in a table have the same key value. Validation The process by which ALLBASE/SQL marks a section valid in the system catalog. A section is marked valid if all the objects it refers to exist, and if it has been optimized. A valid section can be executed immediately at run time with no further preparation. View A table derived by placing a "window" over one or more tables. The derivation of a view is a SELECT command. View names are governed by the same rules as table names. Wrapper DBEnvironment A dummy DBEnvironment that is created using the SQLUtil WRAPDBE command to wrap around the accessible log files to rescue log file information.