Db2 Practice 1

The correct answer is "ADD COLUMN" because when using the ALTER TABLE statement to update a table definition with a new column, the ADD COLUMN clause is used. This clause allows the user to add a new column to the existing table.

A trigger is a type of database object that is designed to perform actions in response to specific events, such as an INSERT, UPDATE, or DELETE operation on a table. It is not designed to return specific types of values, nor does it contain executable code. Instead, a trigger is used to capture details about an activity, such as a join operation against multiple tables. Therefore, the correct statement about triggers is that they perform actions in response to an event on a table.

The correct answer is the second option:
SELECT empno, hiredate, salary
FROM employee
WHERE hiredate < '1998-01-01' AND salary < 35000

This statement uses the WHERE clause to filter the results based on two conditions: hiredate being before 1998 and salary being less than $35,000. It will retrieve the employee number, hire date, and salary for each employee who meets these criteria from the EMPLOYEE table.

TRUNCATE command is used to delete all rows from a table without generating log records. It is a faster and more efficient method compared to the DELETE command because it does not generate individual log records for each deleted row. Instead, it deallocates the data pages and records the deallocation in the log file as a single operation. This makes TRUNCATE a preferred choice when the goal is to quickly remove all data from a table.

IBM Data Studio is the correct answer because it is a comprehensive tool that allows users to connect to a DB2 database using a wizard. It provides a user-friendly interface with step-by-step instructions to establish a connection to the database. This tool offers various features such as database administration, SQL development, and performance tuning, making it a suitable choice for connecting to a DB2 database. Control Center, Universal Connection Expert, and DB2 Connection Pool Manager do not specifically provide a wizard for connecting to a DB2 database.

The page size of the database created with the command "CREATE DATABASE test" is 4 kilobytes. This means that each page in the database will have a size of 4 kilobytes, which is a common default page size for many database systems. A larger page size can improve performance for certain types of workloads, but it also requires more memory and storage space.

A schema in DB2 is used to provide a logical grouping of other database objects. It acts as a container for organizing and categorizing related tables, views, indexes, procedures, and other database objects. By using schemas, it becomes easier to manage and maintain the database as it allows for better organization and separation of objects based on their purpose or functionality.

The SELECT operation in a database normally does not require an exclusive lock. An exclusive lock is used to prevent other transactions from accessing or modifying the data while a transaction is in progress. However, a SELECT operation only reads the data and does not modify it, so it does not need to acquire an exclusive lock. This allows multiple SELECT operations to be executed concurrently without blocking each other. On the other hand, operations like GRANT, DROP, and BIND may modify the database and therefore require an exclusive lock.

Lock escalation is a process in database management systems where a lock on a lower-level object is escalated to a higher-level object, reducing the overall number of locks held. The number of locks is a factor that influences lock escalation. As the number of locks increases, the system may escalate the locks to a higher level to improve performance and reduce overhead. This helps to minimize the resources required to manage locks and improves concurrency in the system. Therefore, the number of locks is an important factor in determining when lock escalation occurs.

A primary key is used in conjunction with a foreign key to define a relationship between two tables. The primary key is a unique identifier for each record in a table, and it is used as a reference in the foreign key of another table to establish a connection between the two. This relationship ensures data integrity and allows for efficient data retrieval and manipulation through join operations.

When the data type of a sequence needs to be changed, it cannot be modified directly using the ALTER SEQUENCE statement. This is because the data type of a sequence is determined when it is created and cannot be altered afterwards. Therefore, in order to change the data type of a sequence, it needs to be dropped and recreated with the desired data type.

When an application using a temporary table terminates or disconnects from the database unexpectedly, the data stored in the temporary table is deleted and the table itself is implicitly dropped. This means that both the table structure and the data it contained are completely removed from the database.

The correct answer is "SELECT job, MIN(salary), MAX(salary)
FROM employee
GROUP BY job;"

This statement is correct because it uses the GROUP BY clause to group the salaries by job code, allowing us to calculate the minimum and maximum salaries for each job. The MIN(salary) function returns the minimum salary for each job, while the MAX(salary) function returns the maximum salary for each job. This statement will give us the desired result of selecting the minimum and maximum salaries by job code from the EMPLOYEE table.

The primary purpose of a view is to combine data from multiple tables. Views allow users to retrieve and manipulate data from multiple tables as if it were a single table. This simplifies the querying process and provides a more organized and efficient way to access data. By creating a view, users can avoid the need to write complex join queries every time they need to retrieve data from multiple tables. Views also provide an added layer of security by allowing users to access only the data they need without exposing the underlying tables.

The purpose of the Query Tuner is to provide recommendations and analysis for tuning a single query. This tool helps optimize the performance of a specific query by suggesting improvements and providing analysis on its execution plan. It helps identify potential bottlenecks, suggests index changes, and provides insights on how to improve the query's efficiency.

The ROLLBACK TO SAVEPOINT command is used to undo a subset of database changes that have been made within a unit of work. It allows you to revert back to a specific savepoint within a transaction, discarding any changes made after that savepoint. This is useful in situations where you want to undo certain changes without rolling back the entire transaction. The other options, COMMIT TO SAVEPOINT, ROLLBACK, and COMMIT, do not provide the functionality to selectively undo changes within a unit of work.

A stored procedure is a set of SQL statements that are stored in a database and can be executed later. These procedures are typically organized and grouped into logical units called schemas. A schema is a container that holds database objects such as tables, views, and stored procedures. Therefore, a stored procedure is defined within a schema, allowing for better organization and management of database objects.

The metadata for an index is stored in the system catalog. The system catalog is a collection of database objects that contain information about the database, such as tables, indexes, and views. It is used by the database management system to keep track of the structure and properties of the database objects. Storing the index metadata in the system catalog allows for easy access and management of the index information by the database management system.

To invoke a user-defined function, the privilege required is EXECUTE. This privilege allows the user to execute or run the function. The EXECUTE privilege is necessary to access and utilize the functionality provided by the user-defined function. It grants the user the authority to execute the code within the function and retrieve the desired results. Without the EXECUTE privilege, the user would not be able to invoke the user-defined function and utilize its functionality.

Repeatable Read (RR) isolation level offers the greatest protection of data but provides the least amount of concurrency. In this isolation level, a transaction locks all the rows it accesses until the transaction is completed, preventing other transactions from modifying or deleting those rows. This ensures that the data remains consistent throughout the transaction, as any changes made by other transactions are not visible within the current transaction. However, this locking mechanism reduces concurrency, as it restricts other transactions from accessing the locked rows, potentially leading to increased waiting times for accessing the data.

JDBC (Java Database Connectivity) would be the appropriate interface to use in this scenario. JDBC is a Java API that allows developers to interact with various databases, including DB2. It provides a consistent and platform-independent way to connect to and query databases, making it suitable for writing applications that can run on different platforms without requiring significant changes. By using JDBC, the application can easily connect to both DB2 10 and DB2 9 databases and perform the necessary queries.

In a trusted context environment, if a switch request is made with an authorization ID that is not allowed on a trusted connection, the connection is placed in an "Unconnected" state. This means that the connection is not established or active, and further actions are required to establish a valid connection.

If a user has been granted SYSCTRL authority, they can perform the activities of creating and dropping databases. This means they have the ability to create new databases and remove existing ones as needed. This authority allows them to have control over the database structure and organization.

To complete the definition of an application-period temporal table, a BUSINESS_TIME period must be specified in a CREATE or ALTER of the table. This means that a time period needs to be defined for the table, indicating when the data in the table is valid or active. This allows for tracking and managing the temporal aspect of the data, such as when it was valid or active in the past or will be in the future. By specifying the BUSINESS_TIME period, the table becomes an application-period temporal table, enabling temporal data management.

The application-period temporal table can be used to store only time-sensitive data. This type of table allows for tracking changes to data over time and is commonly used in applications where it is important to keep a record of when certain data was valid or active. This table type is specifically designed to store time-sensitive information and is different from other temporal table types such as bitemporal, time-period, and system-period tables.

To revoke SECADM authority from user USER1 and assign it to someone else, you must have SECADM authority yourself. This authority allows you to manage security administration tasks, including granting or revoking SECADM authority to other users. DBADM authority, on the other hand, is related to database administration activities and does not grant the ability to manage security administration tasks. Therefore, having DBADM authority is not sufficient for revoking SECADM authority. Similarly, DBCTRL and DBMAINT authorities are also unrelated to security administration tasks and do not provide the necessary privileges to revoke SECADM authority.

When a COMMIT statement is executed, it signifies the end of the current transaction and the beginning of a new transaction boundary. This means that any changes made within the current transaction are permanently saved and become visible to other transactions. Additionally, any locks held on the database are automatically released, allowing other transactions to access the affected data. However, the COMMIT statement does not have any impact on open cursors defined with the WITH HOLD option, as they remain open and retain their data. Similarly, the COMMIT statement does not automatically delete data stored in global temporary tables.

The workload manager is used to customize execution environments in order to control system resources. It allows for the allocation and prioritization of resources based on department or service class, preventing any one department or service class from overwhelming the system.

A unique constraint can be used to ensure that an INTEGER column in a table will never be assigned more than one record that contains a NULL value. This constraint ensures that all values in the column are unique and does not allow duplicate values, including NULL. Therefore, by applying a unique constraint to the INTEGER column, it guarantees that only one record can have a NULL value in that column.

The data for a column defined with an XML data type is stored in an XML storage object that is separate from the table. This means that the XML data is stored in a separate location, allowing for efficient storage and retrieval of XML data. Storing the XML data separately also helps to maintain the integrity and performance of the table by keeping the XML data separate from the rest of the data.

A Materialized Query Table (MQT) can be used to improve the execution performance of qualified SELECT statements in DB2. An MQT is a table that stores the result of a query and is updated periodically to reflect changes in the underlying data. This allows for faster retrieval of data as the query result is already pre-calculated and stored in the table. By using an MQT, the database can avoid executing the query every time it is requested, resulting in improved performance for SELECT statements.

A locator is a type of mechanism that is used to refer to a much larger object, such as a large object (LOB). It is a simple token value that acts as a reference to the larger object, allowing for efficient retrieval and manipulation of the object's data. This mechanism is commonly used in database systems to manage and access large data objects.

A Share (S) lock allows the lock owner and all concurrent applications to read the locked data but not update it. This means that multiple users can access the data simultaneously for reading purposes, but none of them can make changes to it. The Share lock ensures that the data remains consistent and prevents any conflicts that may arise from concurrent updates.

If application APP_B wants to retrieve all rows from table TAB1 without waiting for application APP_A to finish making updates, it must use the Uncommitted Read (UR) isolation level. This isolation level allows APP_B to read uncommitted data, meaning it can access rows that have been modified but not yet committed by APP_A. This ensures that APP_B can retrieve the most up-to-date data without waiting for any ongoing updates.

The correct answer is the fourth option. This SQL statement will produce the desired list of all employees and their associated revenue, even if no revenue exists. It uses a RIGHT OUTER JOIN to join the SALES table with the EMPLOYEES table based on the emp_id column. This ensures that all rows from the EMPLOYEES table are included in the result set, even if there is no matching revenue in the SALES table. The SELECT statement then selects the employees' names and the associated revenue from the SALES table.

The given statement will delete the last 5 rows in the table named MY_TAB.

The functionality that allows users to perform a UNION operation between a DB2 table and an Oracle view is called Distributed request. This functionality enables users to access and combine data from different databases, in this case, a DB2 table and an Oracle view, using a single query or request. It facilitates data integration and retrieval from multiple sources, providing a unified view of the data.

Inline LOBs are used to store LOB data together with the formatted rows on data pages, instead of in a separate LOB storage object. This means that the LOB data is stored directly within the table's data pages, making it more efficient and reducing the need for additional storage objects.

The correct answer is SELECT lastname, firstnme, salary, job
FROM employee
ORDER BY 3 DESC
FETCH FIRST 5 ROWS ONLY. This statement will retrieve the last name, first name, salary, and job columns from the employee table, order the result set by the third column (salary) in descending order, and fetch only the first 5 rows. This will give the desired result set.

The statement "LOCK TABLE tab1 IN SHARE MODE" will prevent concurrent application processes from performing anything other than read-only operations against the table named TAB1. This mode allows multiple sessions to read from the table simultaneously, but it prevents any session from modifying the data.

The correct answer is DATAACCESS authority. This authority allows the user to retrieve information from the database. It grants the necessary privileges to access and read data from the tables in the database.

An INTENT EXCLUSIVE (IX) lock is required when a transaction intends to read or change data. This type of lock indicates that the transaction has the intention to modify the data, and it is used to prevent conflicts with other transactions. By acquiring an IX lock, the transaction signals its intention to modify the data, and other transactions can be aware of this intent and acquire appropriate locks to avoid conflicts. Therefore, an IX lock is necessary when a transaction intends to read or change data.

The correct answer is "SELECT conv_temp(avg_temp) FROM climate_info;". This is because the conv_temp function is being used to convert the average temperature (avg_temp) data stored in the CLIMATE_INFO table. The SELECT statement is used to retrieve the converted temperature values from the table.

The correct answer is "Repeatable read (RR)". This isolation level prevents dirty reads, nonrepeatable reads, and phantoms. Dirty reads occur when a transaction reads uncommitted data from another transaction. Nonrepeatable reads happen when a transaction reads the same data multiple times but gets different results due to other transactions modifying the data. Phantoms refer to new rows that appear in subsequent reads within a transaction due to other transactions inserting new data. Repeatable read ensures that once a transaction reads a row, it will see the same data throughout the transaction, preventing these issues.

When a user-defined function (UDF) is created without specifying a schema, the UDF is stored in a schema that has the same name as the user ID of the UDF definer. This means that the UDF is associated with the user who created it and can be accessed within their schema. This allows for better organization and management of UDFs within the database system.

When performing an INSERT operation, the values being inserted must adhere to any check constraints that have been defined on the table. This means that the values must meet the specified conditions set by the constraints. If the values do not satisfy these conditions, the INSERT statement will fail. This ensures data integrity and prevents the insertion of invalid or inconsistent data into the table.

An index used to support a UNIQUE constraint must be defined with the UNIQUE attribute because this attribute ensures that the index enforces uniqueness on the column or columns it is defined on. Without the UNIQUE attribute, the index would not be able to enforce the constraint and duplicate values would be allowed in the column or columns.

In the body of an SQL scalar user-defined function, two operations that are allowed are CALL statements and SQL control statements. CALL statements are used to invoke other functions or procedures within the function's body. SQL control statements, such as IF, CASE, and LOOP, are used to control the flow of execution within the function. External file access and use of a scratch pad are not allowed in the body of an SQL scalar user-defined function. COMMIT statements are used to save changes to the database and are not typically used within a function.

The XMLTABLE function can be used to obtain values from XML documents that are to be inserted into one or more tables. This function allows for the extraction of data from XML documents and the transformation of that data into relational rows and columns, making it ideal for inserting XML data into database tables.

The correct answer is the first option: SELECT * FROM employee
WHERE (hiredate < '1999-01-01' AND salary < 40000)
OR (education = 'University')

This statement uses the OR operator to retrieve records that satisfy at least one of the given criteria. The first condition checks if the hire date is before 1999 and the salary is less than $40,000. The second condition checks if the employee has attended university. By using the OR operator, the statement will return records that meet either of these conditions.