In this question, you will execute a few concurrent transactions against a banking table. The accounts table is shown in Table 1. Create the accounts table in DB2. You may assume username is of type char(10), name is char(20), and balance is of type decimal(10,2). Write the appropriate INSERT INTO statements to load the five records shown into your accounts table.

Setup You will simulate concurrent database queries, using two different database connections, against the accounts table. You will open two terminal sessions, and execute the following commands to prepare your environments. We will call the two sessions, A and B. In Session A 1. Invoke db2 using db2 +c. This command turns off the AUTO COMMIT feature in DB2. You will now be at the DB2 command line. 2. Verify AUTO COMMIT is turned OFF by running the command list command options. (You should see Auto-Commit OFF in the second entry of the table.) 3. Change the transaction isolation level to Read Stability by running the command change isolation to rs. Transaction isolation levels define the degree of access and interaction among a set of concurrent transactions which operate against the same data. Please see the DB2 reference for more information on isolation levels in DB2. 4. Connect to your database. In Session B 1. Invoke DB2 by typing db2 (do not turn AUTO COMMIT off). 2. Run change isolation to rs 3. Connect to your database. Transactions Run the following commands (in the given order) and provide your answers to the stated questions. 1. In Session A, insert the record (snoke, First Order, 20000.00), followed by a select * from accounts. 2. In Session B, run select * from accounts. Is the output you get the same or different than in (1)? Why did this occur? What is a possible solution? 3. Return to Session A, and implement your solution from step #2. 3 4. In Session B, do the SELECT * query again (to list all records). Provide your output. 5. We will update accounts from two different transactions. In Session A, change the isolation level to cursor stability (CS). In Session B, change the isolation level to uncommitted read (UR). (You will have to disconnect and reconnect to the database.) 6. In Session A, transfer $5000 from Kylos account into Reys account. 7. Then, in Session B, list all accounts and their balances. Then issue a $10000 transfer from Snokes account to Finns account. What happens and why? Explain the interaction between the two sessions. 8. Commit the transaction in Session A. Report the latest balances. 9. Change the transaction isolation level in Session A to Uncommitted Read. 10. Now, in Session A, transfer 50% of Kylos account balance to Leias account. Commit the transaction. 11. In Session A, transfer all of Snokes funds to Lukes account. In session B, list all data records. What is Lukes balance? Does it reflect the latest transfer from Snoke? Based on your transaction in step (7) and this step, what can you say about the allowed actions in CS and UR isolation levels? How do these compare with the RS isolation level? 12. Abort the Snoke to Luke transfer transaction in Session A, by executing the command rollback. 13. In session A and B, list all data records. What are the final balances?