Db2 Basics, Part 3

Declared temporary tables are not persistent, meaning that they are not stored permanently in the database. They are only used by the application that created them and exist only for the duration of that application's execution. Additionally, multiple applications can create a table with the same name, as the temporary tables are scoped within the application that created them. The descriptions and constraints of these tables are stored in the system catalog tables of the database.

Unlike the key, the secondary columns are not used to enforce data uniqueness.

A clustering index determines the physical order of data in a table. It is used to arrange the rows in the table based on the values of one or more columns. Since the physical order of data can only be determined by one index, only one index in a table can be a clustering index. Therefore, the statement that only one index in a table can be a clustering index is true.

A trigger is a database object that defines a set of actions to be executed whenever an insert, update, or delete operation is performed against a table or updatable view. Triggers are used to enforce business rules, maintain data integrity, and automate tasks in response to database changes. When a specified operation occurs, the trigger is activated and the defined actions are executed.

A trigger is a database object that is associated with a table or view and is automatically executed or fired when a specified event occurs. The components of a trigger include the subject table or view (the table or view on which the trigger is defined), the trigger event (the specific event that will cause the trigger to be fired), the trigger activation time (whether the trigger is fired before or after the event), the set of affected rows (the rows that are affected by the trigger), the trigger granularity (whether the trigger is fired for each affected row or for the entire set of affected rows), and the triggered action (the action or set of actions that the trigger performs when fired).

The term "trigger granularity" refers to the level at which trigger actions will be executed. It determines whether the trigger actions will be performed once for the entire insert, update, or delete operation (trigger event), or once for every row affected by the operation. In other words, it specifies whether the trigger actions should be applied at the operation level or at the individual row level.

In the given CREATE TABLE statement, the column "activityno" is defined as a SMALLINT column with the GENERATED BY DEFAULT AS IDENTITY clause. This means that the values for this column will be automatically generated by the system, starting with 10 and incrementing by 10 for each new row. The columns "actkwd" and "actdesc" are defined as NOT NULL, which means that values must be provided for these columns when inserting data into the table. The UNIQUE constraint is applied to the "activityno" column, which means that each value in this column must be unique. The term "stock" in "stock.activity" identifies the schema to which the table belongs.

The triggered action refers to the set of SQL statements that are executed when the trigger is activated. It is an optional search condition that determines when the trigger should be triggered and specifies the actions to be performed when the condition is met.

An identity column is a unique identifier in a table that automatically generates a sequential number for each new record. It is used to uniquely identify each individual record in the table.

The given answer lists the possible triggered actions that can be performed in an AFTER trigger. These actions include signaling SQL states, retrieving data, and performing operations such as inserting, updating, and deleting records. Additionally, it is also possible to set transition variables in an AFTER trigger.

The keyword "LIKE" is used in the CREATE TABLE statement to create a new table with the same definition as an existing table. By using the "LIKE" keyword followed by the name of the source table, the new table will have the same column names, data types, constraints, and indexes as the source table. This allows for easy duplication of table structures without having to manually specify each column and its properties.

A transition variable is a qualified column name that indicates whether the reference is to the original value (before an insert, update, or delete) or to the new value. This variable helps to differentiate between the original and new values during database operations such as inserts, updates, or deletes. It is used to track the changes made to a column and is particularly useful in trigger functions where the original and new values need to be compared or manipulated.

A transition table is a feature that uses subject table names and allows the complete set of affected rows to be treated as a table. This table is specifically used in AFTER triggers. It provides a way to access and manipulate the rows that have been affected by the trigger action. This allows for further processing or analysis of the affected data within the trigger logic.

Base tables are the foundational tables in a database schema. They must have a unique name within a schema to avoid conflicts. Additionally, the descriptions and constraints of the base tables are stored in the system catalog tables of the database, which provide metadata about the tables. This allows for better organization and management of the tables. Other characteristics mentioned in the question, such as not being persistent and being used only for the life of the application, are not specific to base tables and can apply to other types of tables as well.

Constraints are used to enforce business rules within the database. They define the rules and restrictions that the data must adhere to, such as the uniqueness of values in a column, the range of values allowed, or the relationships between tables. By placing these rules in the database, rather than in applications, it ensures that the data remains consistent and accurate, regardless of how it is accessed or modified.

The GENERATED ... AS IDENTITY clause can be used when creating a declared temporary table. This clause allows for the automatic generation of unique values for a specified column in the temporary table. Therefore, the statement is true.

True, if the ON COMMIT PRESERVE ROWS clause is used.

An index is a data structure that contains pointers to the rows in a base table. It provides a quick way to locate specific rows based on the values of one or more columns. By using an index, the database system can efficiently retrieve the desired rows without having to scan the entire table. The pointers in the index refer to the physical locations of the rows, allowing for fast access and retrieval of data.

An index is based on one or more columns, known as the key, in the base table. In other words, the key columns are the columns that the index is created on. These key columns are used to organize and sort the data in the index, making it easier and faster to retrieve specific records from the table.

An index may also be created implicitly; e.g., to support a table's primary key.

When using the LIKE clause to create a table with the same definition as an existing table, the default constraints are copied unless EXCLUDING COLUMN DEFAULTS is specified. Both tables will have the same number of columns, and the columns in the new table will have the same names, data types, and nullability features. However, unique constraints, referential constraints, triggers, and indexes are not automatically copied.

The UNIQUE keyword must be specified; i.e., CREATE UNIQUE INDEX [IndexName] ...

The CLUSTER option of the CREATE INDEX statement will cause these effects: the DB2 Database Manager will attempt to store records on a page that already contains other records with similar index key values. If there is no space available on that page, it will try to store the record on a nearby page. This technique increases performance by reducing the amount of I/O required to access data, as it ensures that related records are physically stored close to each other.

The subject table in this trigger is "activity_history". The trigger event is "DELETE" which means the trigger will be activated when a delete operation is performed on the activity_history table. The trigger activation time is "BEFORE" which means the trigger will be executed before the delete operation takes place.

The line "FOR EACH ROW" is an example of trigger granularity as it specifies that the trigger should be executed for each row that is affected by the DELETE statement. The SIGNAL and SET statements are examples of triggered actions as they are the actions that are performed when the trigger is executed, in this case, signaling an SQLSTATE and setting the message text.

The table will be created in the default table space, which is USERSPACE1. Values must be provided for the fields projno and projname.

The three trigger activation time keywords listed in alphabetical order are AFTER, BEFORE, and INSTEAD OF. These keywords are commonly used in programming or database management systems to specify the timing of trigger actions. The AFTER keyword is used to specify that the trigger action should occur after the triggering event, the BEFORE keyword is used to specify that the trigger action should occur before the triggering event, and the INSTEAD OF keyword is used to specify that the trigger action should replace the triggering event.

The column idcol2 must have a matching value in column idcol1, due to the foreign key constraint fkconst. In this case, idcol1 is the parent key and idcol2 is the foreign key. Every value of column idcol1 must be unique, which is why an index was created for column idcol1, due to the PRIMARY KEY constraint. The table self_reference will be created in table space USERSPACE1.

The line "FOR EACH ROW" is an example of trigger granularity, as it specifies that the trigger should be executed for each row that is updated in the "employees" table. The SET statement is an example of a triggered action, as it defines the action that should be performed when the trigger is activated. "new.salary" is an example of a transition variable, as it refers to the new value of the "salary" column for the updated row.

Multidimensional clustering (MDC) provides a way to cluster data along multiple dimensions automatically. It is a technique used in database management systems to organize data in a multidimensional space, allowing for efficient retrieval and analysis of data based on multiple criteria. MDC index refers to the index structure used to support multidimensional clustering. Therefore, both MDC and MDC index are correct answers for this question.

Constraints are rules that govern how data values can be added to a table and how data values in a table can be modified or changed.

Constraints are typically defined during table creation, specifying the rules and limitations for the data stored in the table. However, constraints can also be added to existing tables using the ALTER TABLE statement. This allows for the modification of the table structure and the addition of constraints after the initial creation of the table.

Values must be provided for the fields po_number and amt. An index was provided for the field po_number. The default for the date field is the system date. The sales table will be created in the schema central and table space my_space.

The BLOB (Binary Large Object) data type is used to store large binary data such as images, audio, and video. It allows for efficient storage and retrieval of these types of data in a database. BLOBs are commonly used in applications that require the storage and management of multimedia files.

A trigger event in SQL is an operation (INSERT, UPDATE, or DELETE) that, when executed on the subject table or view, activates the trigger. Triggers are database objects that are designed to automatically execute a set of actions or procedures in response to specific events or changes in the database. In this case, the trigger event is the specific operation (INSERT, UPDATE, or DELETE) that triggers the execution of the trigger.

The column "empid" is an identity column. Values must be supplied for fields "firstname" and "lastname". Constraints are not named when they are part of the column definition, as for field "edlevel". Every value entered in field "empid" must be unique, due to its constraint. An index was created for field "empid", due to its constraint. Every value entered in field "deptid" must have a matching value in the "deptno" field of table "department", due to referential constraint "emp_dept_fk".

A UNIQUE constraint is a database constraint that ensures that the values in a column or a group of columns are unique. It does not allow NULL values, meaning that the column(s) must have a value for every row. It enforces uniqueness, meaning that each value in the column(s) must be unique and not duplicated. Additionally, a UNIQUE constraint can be referenced in a referential constraint, which means it can be used in relationships between tables to maintain data integrity.

The subject table in this trigger is "employees" as specified in the CREATE TRIGGER statement. The trigger event is "UPDATE" as indicated by the BEFORE UPDATE clause. "BEFORE" is the trigger activation time, meaning the trigger will be executed before the update operation takes place on the "employees" table.

A unique index generally cannot be used in a referential constraint because it is not designed to maintain relationships between tables. It enforces uniqueness by not allowing duplicate values in the indexed column(s). Additionally, it allows NULL values, meaning that the indexed column(s) can have NULL values as long as they are not duplicated. However, it cannot be directly referenced in a referential constraint to establish relationships between tables.

Triggers are used in database management systems to enforce data integrity and business rules. They can be used to invoke functions, such as issuing errors or alerts, when certain conditions are met. Triggers can also generate or transform values for inserted or updated rows, and update other tables. These capabilities make triggers a powerful tool for maintaining the integrity and consistency of data within a database.

For table 'employees', values must be provided for the empid column. Every value provided for empid must be unique. An index was provided for the empid column.

For table 'paychecks', pay_amt has a precision of 6 and a scale of 2. Every value in paychecks.empid must have a matching value in employees.empid, due to CONSTRAINT fkconst. Whenever a row is deleted from table employees, all rows in table paychecks that have the same value will also be deleted.

The subject table in this trigger is "sales" because the trigger is defined on the "sales" table. "INSERT" is the trigger event because the trigger is activated after an insert operation on the "sales" table. The trigger activation time is "AFTER" because the trigger is fired after the insert operation. "FOR EACH ROW" indicates that the trigger is executed for each row affected by the insert operation. The triggered action is an "UPDATE" statement because the trigger updates the "sale_date" column. "n.invoice" is an example of a transition variable because it references the new value of the "invoice" column in the inserted row.

The possible triggered actions that can occur before a trigger are: signaling SQL states, retrieving data, inserting, updating, and deleting records, and setting transition variables. These actions can be performed before the trigger is executed and can help in preparing the environment or gathering necessary information for the trigger to function properly.

Multibyte character set is distinguished from double-byte character set (DBCS), which is processed by the DBCLOB data type.