1ST_CONN
If the keyword 1ST_CONN is defined for the tm_database parameter, the first database to which the application connects in the transaction will be used as the transaction manager database.
Care must be taken when using 1ST_CONN.
You should only use this configuration if it is easy to ensure that all databases involved are cataloged correctly. For example, use it in the following situations:
Cataloged Database
Before a client application can access a remote database, the database must be cataloged on the server node and on any client nodes that will connect to it.
When you create a database, it is automatically cataloged on the server with the Database alias (Database_alias) and adopts the Database name (Database_name) in the process.
The information in the database directory, along with the information in the node directory, is used on the client to establish a connection to the remote database.
A database management system (DBMS), or database manager, manages data by providing centralized control and independence of data. As well as defining the physical storage of data (in databases), a database manager provides for efficient access to, as well as integrity, recovery, concurrency control, privacy, and security of data.
Database name
A database name must be specified when a database is created. All databases managed by the same database manager instance must have unique alias names.
DB2 database names are displayed as fields in the system database directory, the local database directory, and the database connection services directory.
A database name consists of 1 - 8 characters from the following list:
Also, a database name:
| Note: | To avoid potential problems, do not use the special characters @, #, and $ in a database name if you intend to use the database in a communications environment. Also, because the characters @, #, and $ are not common to all keyboards, do not use them if you plan to use the database in another country. |
A database object is anything that can be created or manipulated with SQL.
To see the definition for any of the following database objects, double-click on the term:
Connection partition
Database partition
Node
Nodegroup
Table partition
Table space partition
A dependent table is a table that contains one or more foreign keys. A dependent table can also be a parent table.
Examples
TABLE1 has a foreign key that references the primary key in TABLE2. TABLE1 is a dependent table; it is dependent on TABLE2.
A table is a descendent of table T if it is a dependent of T or a dependent of a descendent of T.
Examples
TABLE1 has a foreign key that references the primary key in TABLE2, and TABLE2 has foreign keys that reference the primary keys in TABLE3 and TABLE4. TABLE2 is a dependent of TABLE3 and TABLE4. TABLE1 is a dependent of TABLE2. Therefore, TABLE1 is a descendent of TABLE3 and TABLE4.
Exception tables are user-created tables that match the definition of the tables that are being checked for constraints. They are used to store copies of the rows that violate constraints.
Federated database system (federated system)
A DB2 federated system consists of a DB2 server (called a federated server), a DB2 database, and a set of diverse data sources that DB2 sends queries to. You can configure any number of DB2 instances to function as federated servers. In a federated system, each data source consists of an instance of a relational database management system (RDBMS), plus the database or databases that the instance supports. The particulars of the instance and supported databases vary with each RDBMS type. For example, a data source consists of an instance of the database manager and one or more databases.
A DB2 federated system provides location transparency for database objects. If information is moved, references to that information can be updated without any changes to applications that request the information. Also a DB2 federated server provides compensation for data sources that do not support all of the DB2 SQL dialect or certain optimization capabilities. For example, if a data source does not support recursive SQL, DB2 can run recursive SQL against the data sources data.
The federated database contains catalog entries identifying data sources and access methods. These catalog entries contain information about federated database objects: what they are called, information they contain, and conditions under which they can be used. Applications connect to the federated database just like any other DB2 database.
Because this catalog stores information about objects in many DBMSs, it is called a global catalog. Object attributes are stored in the catalog: the actual DBMSs being referenced, database access modules that will be used, and DBMS data objects (such as tables) that will be accessed are outside the database.
Required federated database objects are:
Depending on your specific needs, you might create additional objects:
A file system is a collection of files and file management structures on a logical volume.
A logical volume is an allocation of space on one or more physical storage devices.
Frequency
The frequency of an SQL statement represents the number of times it is run. The Create Index wizard uses this number to judge the importance of the statement.
| Note: | If an SQL statement is more important or more critical than some of the other SQL statements in the workload, even though it is run less frequently, then specify a frequency that is higher than that of the other statements in the workload. |
A database manager instance is a logical database manager environment that is an image of the actual database manager environment. There can be several instances of a database manager on the same workstation.
In a partitioned database environment, the instance is made up of a
collection of nodes that are defined in the
db2nodes.cfg file.
The isolation level determines how data is locked (isolated) from other processes while it is being accessed.
DB2 supports the following isolation levels:
For more information, see the section on concurrency in the Administration Guide .
Each workstation directory path that has one or more databases residing on it has a local database directory. Each database entry that is created on a drive has an associated entry in a local database directory.
A nickname is an identifier that references a data source table or view.
When a client application submits a distributed request to the federated database, DB2 parcels out the request to the appropriate data sources. This request does not need to specify where the tables or views reside. Such an approach provides no location transparency or independence to the client application.
To provide location transparency, DB2 creates nicknames that map to identifiers that contain the names and locations of data source tables and views. Nicknames are not alternate names for tables and views in the same way that aliases are; rather, they are pointers by which a federated system references these objects. You can supply metadata that the federated server's optimizer can use to expedite access to table or view data. For example, you can indicate which columns, if any, contain numeric strings only.
A null indicator is a column (by byte position) in a nondelimited ASCII file that contains the null indicator flag for the data being loaded into a table column. The null indicator can be any valid positive integer. A null indicator of zero (0) indicates that the table column cannot be NULL.
When the load process looks at each data row, the character in the null indicator column indicates whether or not the data in the column defined by the start and end positions is NULL. Unless otherwise specified, a 'Y' is used to denote that the data column is NULL. Anything else indicates that the data column is not NULL. For example, when loading the following ASC file into a table with columns COL1, COL2, and COL3:
3 4 5 6 0.........0.........0.........0 Smith,B. 4973 N15.46 Jones,S. 12345 Y Davis,S. 452121 N93.78
For rows 1 and 3, an N is in position 49, indicating that the data in COL3 for those rows is not NULL. For row 2, a Y is in position 49, indicating that the data in COL3 for row 2 is NULL.
The null indicator flag is a one-byte character that is contained in a null indicator column of a nondelimited ASCII file. When the load process looks at each data row, the null indicator flag indicates whether or not the data in the column defined by the start and end positions is NULL. Unless another flag is specified (using the NULLINDCHAR option), a 'Y' is used to denote that the data column is NULL. Anything else indicates that the data column is not NULL.
The null indicator flag for a given table column can be anywhere in the data row, but its position must be specified by the load or import process and a character must be in that position.
For an example of null indicator flags used in an ASC file, see null indicator.
O
Ordinary and delimited identifiers (name)
There are two types of SQL identifiers: ordinary identifiers and delimited identifiers.
An ordinary identifier:
If you type a lowercase letter as part of an ordinary identifier in a field, the lowercase letter is stored as an uppercase letter.
Attention: Validation of identifiers occurs when you submit an action for processing (for example, when you click OK in the Create Table notebook). At that time, you will receive an error message if the identifier you specified is invalid.
A delimited identifier:
Leading spaces are stored as part of a delimited identifier, but trailing spaces are ignored.
Attention: Validation of identifiers occurs when you submit an action for processing (for example, when you click on OK on the Create Table notebook). At that time, you will receive an error message if the identifier you specified is invalid.
Examples
To name a table WKLYSAL, you can type: WKLYSAL or wklysal or Wklysal in the appropriate field. The lowercase letters are changed to uppercase, and the table name is stored as WKLYSAL. If you want the name of the table to be Wkly Sal, type: "Wkly Sal" in the appropriate field. The quotation marks are recognized as the delimiters of the identifier, and the table name is stored as Wkly Sal.
If you want the name of a column to be "Nickname", type: """Nickname""" in the appropriate field. The outermost quotation marks are recognized as the delimiters of the identifier, and each pair of consecutive quotation marks represents one quotation mark. The column name is stored as "Nickname".
Database Connection Services parameters are passed to the application requester when it is invoked.
The following parameters are optional, but they must be used in the order given.
For more information, refer to the DB2 Connect User's Guide guide.
A parent table is a table that contains a primary key that is related to at least one foreign key in a table. A parent table can also be a dependent table.
Examples
TABLE1 has a foreign key that references the primary key in TABLE2. TABLE2 is a parent table; it is a parent of TABLE1.
TABLE2 has two foreign keys: one references the primary key in TABLE3 and another references the primary key in TABLE4. TABLE3 and TABLE4 are parents of TABLE2.
In DB2 Universal Database Enterprise - Extended Edition a partitioning key is an ordered set of one or more columns in a given table. For each row in the table, the values in the partitioning key columns are used to determine on which database partition the row belongs.
No LOB, LONG VARCHAR, or LONG VARGRAPHIC columns may be used as part of a partitioning key.
If you do not identify a partitioning key when you create a table that resides in a multinode nodegroup, one of three actions will occur by default:
If you do not identify a partitioning key when you create a table that resides in a single-node nodegroup, the table is created without a partitioning key. You can add or alter the partitioning key after the table is created.
For more information on choosing columns for the partitioning key, see Data Partitioning Across Multiple Partitions in the SQL Reference.
Prefetching
Prefetching reads the data needed by a query before it is referenced by the query, so that the query does not have to wait for I/O to be performed.
When you reorganize a table, the table data is rearranged into a physical sequence, usually according to a specified index. As a result, SQL statements on that data can be processed more efficiently. In addition, the reorganization process removes unused, empty space from the table, and the reorganized table is stored more compactly.
During a roll-forward operation, the database manager uses the information stored in the archive and active log files to reconstruct the transactions performed on the database since its last backup.
The roll-forward utility can be executed only by a user with SYSADM, SYSCTRL, or SYSMAINT authority.
A database is marked as being in the roll-forward pending state when it is successfully restored from a full database backup and requires roll-forward recovery. (The rollfwd-pending configuration parameter contains the roll-forward status for the database.) When a database is in the roll-forward pending state, its data is not accessible.
A table space is put into the roll-forward pending state when:
To remove the table space from the roll-forward pending state, roll-forward recovery for the table space must complete successfully.
When a table space is in the roll-forward pending state, its data is not accessible.
Schema
A schema is a collection of named objects. It provides a logical classification of objects in the database and may contain objects such as aliases, tables, views, indexes, triggers, distinct types, functions, and packages.
You can explicitly create a schema using the Create Schema window. A schema can be implicitly created when an object (such as an alias, table, view, index, trigger, or package) is created. The schema exists in the database as an object.
When creating an object, you can assign it to a schema by specifying the schema name. The schema name is used as the first part of the two-part object name. If a schema name is not specified, the authorization name of the creator of the object is used as the default.
Server (in a federated system)
A server (short for server definition) is a unit of reference that defines a data source to a DB2 federated database. To access a data source, the applications must reference the data source by name. You supply this name; you also provide information pertaining to the data source: for example, the type and version of the RDBMS that it is an instance of, and an identifier for this instance. The federated database stores the name and information in its system catalog.
A service name provides a symbolic method of specifying the port number to be used at a remote node. Its value is contained in the svcename configuration parameter.
There are two pieces of information required when making a TCP/IP connection:
The service name must match the name in the corresponding entry of the \etc\services file. It is case-sensitive, and can consist of 1 to 14 characters.
A logical name representing a computer running DB2. This name may be arbitrary and is usually assigned at DB2 install time. The system name is stored in the DB2SYSTEM environment variable. For more information on DB2SYSTEM, see the section about registry values and environment variables in the Administration Guide.
The system name is used to group resources in the DB2 catalogs. DB2 resources are grouped by system name in the object tree of the Control Center.
The system database directory contains entries for each database that can be accessed using the database manager. It is created when the first database is created or cataloged on the system.
This directory is located in the path where the database manager is installed and it must contain an entry for every database that the database manager can access.
Table space
A table space is a storage model that provides a level of indirection between a database and the tables stored within that database. Table spaces allow you to assign the location of database and table data directly onto containers. (A container can be a directory name, a device name, or a file name.) This can provide improved performance, more flexible configuration, and better data integrity.
A single table space can span several containers. For improved performance, each container can use a different disk.
In DB2 Universal Database Enterprise - Extended Edition a table partition is the portion of a table that is local to a database partition.
In DB2 Universal Database Enterprise - Extended Edition a table space partition is the portion of a table space that is local to a database partition.
Temporary tables are work areas that are used by the database manager to execute operations such as join or sort.
Timeron
A timeron is an abstract unit of measure. It does not directly equate to any actual elapsed time, but gives a rough relative estimate of the resources (cost) required by the database manager to execute an access plan.
Triggers, trigger events, and triggered actions
A trigger is an object that initiates an action when an UPDATE, DELETE, or INSERT operation is run against a table. The action is called a triggered action and the operation is called a trigger event.
Triggered actions can perform such tasks as:
For example, suppose that each row in table EMPLOYEE contains information about an employee of a company, and that a column in table COMPANY_STATS contains a value denoting the number of employees in the company. You can specify a triggered action that increases this value by 1 whenever a row of information about a new employee is inserted into EMPLOYEE.
The following links provide additional information on triggers:
When triggered actions occur
Triggered actions can occur before or after trigger events. Specifically, a triggered action can occur:
Example: A DELETE operation is scheduled to delete ten rows of Table X. If any of these rows contains a value under 5 in Column Y, the value must be copied to another table before the row is deleted. You can code a triggered action to look for a value under 5 in Column Y and, if the value is there, to copy it into the other table. The action will then be invoked against each row before the row is deleted.
In the Create Trigger notebook, you specify when this action occurs by selecting the Before radio button under Time to trigger action on the Trigger page. When you do so, the Row radio button under For each on the Triggered Action page is automatically selected for you.
Example: The number of rows in Table A is denoted by a value in Table B. You can code a triggered action to increase this value by 1 whenever an INSERT operation adds a row to Table A.
In the Create Trigger notebook, you specify when this action occurs by selecting:
Example: An UPDATE operation is scheduled to update ten rows in Table X. After the updates are made, a report summarizing them needs to be created. You can code a triggered action to create this report.
In the Create Trigger notebook, you specify when this action occurs by selecting:
SQL for triggered actions: summary
The SQL for a triggered action can start with a WHEN clause that specifies the condition under which the action is to occur.
If you want the action to occur before a trigger event, the code must include one of the following statements:
If you want the action to occur after a trigger event, the code must include one of the following statements:
Example: A trigger called REORDER ensures that whenever a parts record is updated, the following check and (if necessary) action is taken:
If the on-hand quantity is less than 10% of the maximum stocked quantity, then issue a shipping request ordering the number of items for the affected part to be equal to the maximum stocked quantity minus the on-hand quantity.
In the following definition of REORDER:
CREATE TRIGGER reorder AFTER UPDATE OF on_hand, max_stocked ON parts REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN (n.on_hand < 0.10 * n.max_stocked) BEGIN ATOMIC VALUES(issue_ship_request(n.max_stocked - n.on_hand, n.partno)); END
For more examples of SQL for triggered actions, see online help for correlation names and temporary tables. See also "CREATE TRIGGER" in the SQL Reference.
Transition variables and correlation names
If a triggered action needs to read or manipulate a value, you can reference this value with a variable in the form of R.C, where R and C identify the row and column where the value is located:
A variable that references a value in the SQL for a triggered action is called a transition variable. The row name in such a variable is called a correlation name.
Example: You want a triggered action to invoke an error message if a dollar amount in the SALARY column of table EMPLOYEE is updated to a figure that's over 110% of the amount. The action needs to reference two values: the original dollar amount and the figure to which it will be updated.
To reference the first value, you can specify a variable called OLDROW.SALARY, where OLDROW refers to the row that contains the value.
To reference the second value, you can specify a variable called NEWROW.SALARY, where NEWROW refers to the row that contains the value.
OLDROW and NEWROW are names for the same row. The name OLDROW signifies that the row contains the original amount; the name NEWROW signifies that the row contains an updated value.
You code the triggered action in a trigger that you call RAISE_LIMIT. Here is the definition of RAISE_LIMIT:
CREATE TRIGGER raise_limit
AFTER UPDATE OF salary ON employee
REFERENCING OLD AS oldrow NEW AS newrow
FOR EACH ROW MODE DB2SQL
WHEN (newrow.salary > 1.1 * oldrow.salary)
SIGNAL SQLSTATE '75000' ('Salary increase > 10%')
For more examples of SQL containing correlation names, see "CREATE TRIGGER" in the SQL Reference.
Temporary tables
In the code for a triggered action, you can reference the rows that a trigger event acts on. You do this by treating the rows as a temporary table, naming the table, and then referring to the table by name in the code.
Example:The rows of table EMPLOYEE are periodically updated with the new salaries of employees who have received raises. If, after any such update, the total increment in salaries exceeds 5 percent, you need to notify management.
You can code a triggered action to generate the notification. In the code, you use temporary tables to refer to the updated rows as they are at two points in time--before the update and after the update. The temporary table for the rows before the update is called OLDTABLE, and the temporary table for the rows after the update is called NEWTABLE.
You code the triggered action in a trigger that you call OVERALL_RAISE. Here is the definition of OVERALL_RAISE:
CREATE TRIGGER overall_raise
AFTER UPDATE OF salary ON employee
REFERENCING OLD_TABLE AS oldtable NEW_TABLE AS newtable
FOR EACH STATEMENT MODE DB2SQL
WHEN ((SELECT SUM(salary) FROM newtable) * 100.0000 /
(SELECT SUM(salary) FROM oldtable) > 105)
SIGNAL SQLSTATE '75001' ('Overall salary increase > 5%')