Unit 8: Database Maintenance, Monitoring, and Problem Determination

Task 1: Load a Test Table and Create a Set of Explain Tables for Query Analysis

1. Logon to the Linux system using the user id inst23, with a password of ibm2blue.

2. Right-click the empty Linux desktop and select Open in Terminal.

3. Issue the following series of commands in the Linux terminal session:

cd $HOME/ddl
db2 connect to musicdb
db2 -tvf explain.ddl
db2 -tvf create_testhist.ddl

The SQL statements show a sum from the QTY column for a set of rows before and after the UPDATE processing.

Task 2: Use the Explain SQL Capability of Data Server Manager to Review the Access Plan and Estimated Costs for Processing a SQL Statement

1. Start DSM if not already started and select the MUSICDB database from the database list at the top. On the left, click Run SQL.

2. Clear any SQL statements from the editor that may remain from previous steps.

3. Click Script > Open from Client. Use the Browse button for the Open SQL Script window to locate and select the file inst23/ddl/query_history.sql.

4. Click Open.

5. Click OK to complete loading of the SQL text into the SQL editor.

6. Click Run > Explain to generate the access plan report. The Visual Explain graph will appear in a new Web Browser tab.

7. Select the TBSCAN operation and look at the cost statistics that appear next to the TBSCAN octagon. You may want to click on the (+) magnifier icon on the left to increase the size of the access plan objects.

Estimated Cardinality	162
Cumulative Total Cost	3,229
Cumulative I/O Cost	3,575

Task 3: Use the db2advis Command to Recommend Additional Table Indexes that Could Reduce Processing Costs for the SQL Query

1. Issue the following series of commands using the Linux terminal session:

cd $HOME/ddl
db2advis -d musicdb -i query_history.sql | more

The output will include results similar to the following:

execution started at timestamp 2017-07-25-11.07.01.240879
found [1] SQL statements from the input file
Recommending indexes...
total disk space needed for initial set [ 10.981] MB
total disk space constrained to [ 29.976] MB
Trying variations of the solution set.
1 indexes in current solution
[3234.0000] timerons (without recommendations)
[ 22.0000] timerons (with current solution)
[99.32%] improvement
--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- index[1], 10.981MB
CREATE INDEX "INST23 "."IDX1707251507050" ON "TEST "."HISTORY"
("BRANCH_ID" ASC, "TELLER_ID" ASC, "ACCTNAME" ASC,
"BALANCE" ASC, "ACCT_ID" ASC) ALLOW REVERSE SCANS COLLECT SAMPLED
DETAILED STATISTICS;
COMMIT WORK ;

The suggested new index should look similar to the following:

CREATE INDEX "INST23 "."IDX1608151408110" ON "TEST "."HISTORY"
("BRANCH_ID" ASC, "TELLER_ID" ASC, "ACCTNAME" ASC,
"BALANCE" ASC, "ACCT_ID" ASC) ALLOW REVERSE SCANS COLLECT SAMPLED
DETAILED STATISTICS;

This index may be very efficient for processing this one SQL statement, but you will create a simple two column index, on BRANCH_ID and TELLER_ID, that will require less disk space.

2. Using DSM, on the left, click Run SQL.

3. Clear any SQL statements from the editor that may remain from previous steps.

4. Click Script > Open from Client. Use the Browse button for the Open SQL Script window to locate and select the file inst23/ddl/create_testhist_ix.ddl.

5. Click Open.

6. Click OK to complete loading the SQL text into the SQL editor.

7. Review the CREATE INDEX statement and the ADMIN_CMD procedure call that is used to invoke the RUNSTATS processing.

8. Click Run > Run All and wait for the SQL statements to be processed.

Task 4: Use the Explain SQL Capability of DSM to Check the Access Plan and Estimated Costs with the New Index in Place

1. Click Run SQL from the options on the left. Clear any SQL statements from the editor that may remain from previous steps.

2. Click Script > Open from Client. Use the Browse button for the Open SQL Script window to locate and select the file inst23/ddl/query_history.sql.

3. Click Open.

4. Click OK to complete loading of the SQL text into the SQL editor.

5. Click Run > Explain to generate the access plan report. The Visual Explain graph will appear in a new Web Browser tab.

6. Select the IXSCAN operation and look at the cost statistics that appear next to the IXSCAN octagon. You may want to click on the (+) magnifier icon on the left to increase the size of the access plan objects.

Estimated Cardinality	162
Cumulative Total Cost	699
Cumulative I/O Cost	103

Your results may be slightly different, but a test result produced the estimated cardinality of 162 rows, and estimated I/O cost at 103. These estimated costs are much lower than those required for the table scan in the previous access plan. The relatively high I/O count, over 100, in order to read the estimated 162 rows suggests that the data you need spans many pages.

Task 5: Reorganize a Table Using a REORG Utility

1. Click Administer > Tables on the left side of the DSM application. The current table objects are listed.

2. Locate and select the table TEST.HISTORY. You may need to resize the schema and table columns. (You can enter '%HISTORY' in the filter to make it easier to locate the table.)

3. Select Reorganize (which may be listed under the More Actions list).

4. Select these options:

• Reorganization Method: Classic
• Indexes, Table Spaces, and Dictionary: Reorganize by using an existing index (the only index, TEST.HISTIX, is listed)
• Temporally store a copy of the reorganized table in a temporary table space
• Leave other options with default values

5. The DSM tool generates the REORG TABLE command with a procedure call to ADMIN_CMD. Click on the (+) next to Command to view the command text. The command text should be similar to the following:

CALL SYSPROC.ADMIN_CMD ('REORG TABLE TEST.HISTORY INDEX TEST.HISTIX
USE TEMPSPACE1');

6. Click Run. The result should show that the command processing succeeded.

7. Locate and select the table TEST.HISTORY and click on the check box. You need to collect new table and index statistics, so that the Db2 Optimizer can accurately plan access to the newly reorganized table.

8. Select Collect Statistics (which may be listed under the More Actions list).

9. Click on the right pointing Arrow at the bottom.

10. Leave all of the options with default values, and then Click on the (+) next to Command to view the command text.

11. Select Run. The result should show that the command processing succeeded.

Task 6: Use the Explain SQL Capability of DSM to Recheck the Access Plan and Estimated Costs After Table Reorganization

1. Click Run SQL from the options on the left. Clear any SQL statements from the editor that may remain from previous steps.

2. Click Script > Open from Client. Use the Browse button for the Open SQL Script window to locate and select the file inst23/ddl/query_history.sql.

3. Click Run > Explain to generate the access plan report. The Visual Explain graph will appear in a new Web Browser tab. The access plan utilizes the index with an IXSCAN, or index scan operation, followed by the FETCH to retrieve data rows using the index pointers. The new estimated costs are much lower with a reorganized table.

4. Select the FETCH operation and look at the cost statistics that appear.

Estimated Cardinality	162
Cumulative Total Cost	33
Cumulative I/O Cost	4.9

Your results may be slightly different, but a test result produced the estimated cardinality of 162 rows with an estimated I/O cost at 4.9. These estimated costs are much lower than those required for the index scan in the previous access plan before the table was reorganized. The relatively small count, about 5, shows that Db2 expects to find the rows in a small number of pages, which drastically reduces the estimated costs.