Case Studies on DB2

Case Studies on DB2

Case 1 : Create tables and load Data
1. Create a database and few tablespaces
2. Create below tables
TableA
AC1 integer
AC2 char(5)
AC3 varchar(10)
AC4 date
AC5 timestamp
AC6 integer
Uniq index – AC1
Secondary index – AC2 and AC4

TableB
BC1 integer
BC2 char(5)
BC3 BLOB
BC4 CLOB

Uniq clustered index – BC1
Secondary index – BC2

3. Insert 10 records each
4. Export data and import it back
5. Create a file of 2-3 K records for TableA and load replace the data

Case 2 : Referential integrity
1. Create RI between TABLEA and TABLEB
2. Illustrate the ON delete cascade/restrict /set NULL options.
3. Load replace the child table and check the table status
4. DROP RI
Case 3 : Triggers and Stored procedure
1. Create a trigger which will insert a row in TABLEC (New table) when a row is deleted from TABLEA
2. Create a SP which will insert a record to TableA and then read all the rows from TableA and display the result set
Select * from TableA where AC6 =<some value>

3. Do a explain of the package and run advise
4. Create an index on TableA / AC6
5. Runstats on TableA
6. Rebind the package and check explain again
Case 4 : Range Partitioned table and Views
1. Create a TableC like tableA but a Range partitioned table based on AC1 values and load data
2. Create a view to select AC1,AC2 and BC1,BC2 from the tables using a join
3. Create a MDC table TableD
Case 5 : Reorg
1. Run online index reorg for TableA
2. Run offline reorg and tableA and monitor
3. Run online reorg for tableA and monitor
Case 6 : Offline Backup and restore
1. Take a offline backup
2. Restore the database
Case 7 : Online Backup and restore
1. Take an online backup
2. Restore the database and roll forward to end of logs
3. Take an online backup
4. Restore the database and roll forward to point in time
Case 8 : Tablespace level Backup and Restore
1. Initiate a online tablespace backup
2. Restore the tablespace and rollforward till Point in time.
Case 9 : Database Redirected restore
1. Take a offline backup
2. Restore the database to a new database in a new path
Case 10 : Automatic Storage
Check the current status of your database
Check the Current Status of Your Table Spaces
Enable Automatic Storage for Database
Create tablespaces with automatic storage disabled
Enable Automatic Storage for Table Spaces Online
Enable Automatic Storage for Table Spaces via Redirect Restore

Document the difference between the above mentioned methods

Case 11 : Event monitor
Create,monitor and read the output for event monitor by restricting the max files generated to be limited to 10.
Create a table and load data into it
Create event monitor for that table
Check if the event monitor is enabled and is functioning fine
Perform DML operations on that table after enabling event monitor
Get the results of the logs generated by the event monitor.
Turn off the event monitor once the job is done.
Repeat the same steps by creating event monitor for statements and transactions.
Case 12 : Enabling and disabling rotating diagnostic logging.
Case 13 : Perform schema copy using ADMIN_COPY_SCHEMA procedure and db2move utility for schema of your choice
Create a table with a user created schema and load data into it
Copy the present source schema to a different target schema using both the above utilities. It needs to be done on both same DB as well as different DB
Case 14 : Create a non unicode DB and then convert to unicode DB (code set UTF-8)
Case 15 : Identity columns
1. Create a table with identity column
2. Insert few records.
3. Reset the identity column
Case 16 : Script
1. Write a script which will create a file and insert 100 records
2. Write a script which will load the file created by step 1 to a table
3. Write a script which will do reorg/runstats on a table and take a backup