IBM DB400 Online Test

Consider the following DB2 database scenario. You are managing a stock trading platform that uses a DB2 database to track user transactions and market prices. The database has two tables, Users and Stocks.

A user transaction to buy a stock is a two-step process:

1. Decrease the user's balance in the Users table.
2. Increase the quantity of the stock owned by the user in the Stocks table.

Both these steps need to be performed atomically to maintain data consistency.

Recently, with the increase in the volume of transactions, you are experiencing performance issues. Additionally, you have observed that during peak trading hours, a large number of user transactions are getting blocked, leading to user dissatisfaction.

Which of the following strategies would BEST address the performance and blocking issues, while ensuring data consistency?

A: Implement an optimistic concurrency control mechanism.
B: Reduce the transaction isolation level to READ UNCOMMITTED.
C: Break the transaction into two separate transactions
D: Increase the database's buffer pool size.
E: Implement row-level locking.

Consider two users executing the following transactions concurrently on DB2:

Transaction 1 (User A):

Transaction 2 (User B):

Table1 has thousands of records, and the value 'Value1' for Column1 is not unique in Table1. Both transactions are executed almost simultaneously with Transaction 2 executed a bit earlier. Considering the isolation levels set for the transactions, which of the following is the potential data inconsistency issue that might arise?

A: A deadlock situation may occur.
B: Transaction 1 will wait indefinitely for Transaction 2 to commit.
C: Both transactions might update the same record leading to a lost update.
D: Transaction 1 can experience a dirty read due to the changes made by Transaction 2.

You are managing a DB2 database which includes a table named `ORDERS`. The `ORDERS` table has a large volume of data and contains the following columns: `OrderID`, `CustomerID`, `OrderDate`, `ProductID`, `Quantity`. 

One of the frequently executed queries on the `ORDERS` table is:

You are asked to optimize this query without changing its structure or the table schema. Which of the following strategies would MOST likely improve the performance of this query?

A: Create a HASH index on `CustomerID` and `OrderDate`.
B: Create a CLUSTERED index on `CustomerID` and `OrderDate`.
C: Create a NONCLUSTERED index on `CustomerID` and `OrderDate`.
D: Create a CLUSTERED index on `OrderID`.
E: Create a NONCLUSTERED index on `OrderID`.

Consider the following pseudocode in an IBM DB2 environment:

This stored procedure is used to update the inventory of a particular item. If the `NewQuantity` input parameter is negative, the procedure signals an exception. There's also a handler declared for exceptions, which logs an error message.

Given this scenario, if an error occurs during the UPDATE statement, which of the following will happen?

A: The update operation will fail, an error message will be logged, and the quantity in the inventory will remain unchanged.
B: The update operation will succeed, no error message will be logged, and the quantity in the inventory will be updated.
C: The update operation will fail, an error message will be logged, but the quantity in the inventory will still be updated.
D: The update operation will fail, no error message will be logged, and the quantity in the inventory will remain unchanged.
E: The update operation will succeed, an error message will be logged, but the quantity in the inventory will still be updated.