Assignment MANDATORY PDF

Preview

Summary

ASSIGMENT...

Description

1) How would you use RUNSTATS, REORGCHK and REORG utilities for maintaining database efficiency? AnsThe RUNSTATS command can be used to collect new table and index statistics. If a table is growing in size or a new index is added, it is important to update the statistics to reflect these changes.



RUNSTATS ON TABLE employee AND SAMPLED DETAILED INDEXES ALL The REORGCHK command can be used to check a series of indicators and recommend which tables or indexes would benefit from reorganization.



db2 reorgchk on schema auto The REORG utility can be used to reorganize tables and indexes to improve the efficiency of the storage and to reduce access costs.



2) Describe the methods that can be used for monitoring database and application activity including db2pd commands, Event Monitors and using SQL statements to access statistics. Ans  -

Database monitoring is a vital activity for the maintenance of the performance and health of your database management system. DB2 collects information so you can perform the following tasks: Forecast hardware requirements based on database usage patterns. Analyze the performance of individual applications or SQL queries. Track the usage of indexes and tables. Pinpoint the cause of poor system performance. Assess the impact of optimization activities

The db2pd command provides a way to database monitoring and problem determination tasks. It gathers information about current activity in a way that does not interfere with the database workload and allows diagnostic information to be collected. The Event Monitor query operational status over time for a specific activity. It is created by the SQL statement "CREATE EVENT MONITOR ..". Event monitors let you analyze resource usage by recording the state of the database at the time specific events occur. It can also be used to collect static and dynamic SQL statements. Monitoring activities refers to the execution of the section for a SOL statement.

3) Describe the function of EXPLAIN to use this facility to assist basic analysis. Ans   

Explain Tables store detailed information about how DB2 will access the data to satisfy a query request. It contains information for both static and dynamic SQL statements. Visual Explain tools allow for the analysis of access plan and optimizer information from the EXPLAIN tables through a graphical interface. The db2exfmt command produces detailed explain report based on data in explain tables. db2expln is a command line tool to analyze the access plan.

4) How would you use the db2advis command to analyze a workload for potential performance improvements? AnsThe db2advis command can be used to suggest changes, like adding new table indexes that could reduce processing costs for an SQL workload. cd c: \inst####\ dal db2advis -d musicdb -i query history.sql | more db2 -tuf create testhist.ddl

5) How would you use the db2fodc command to collect diagnostic data for a system hang? Ans 



The db2fodc utility captures symptom-based data about the DB2 instance to help in problem determination situations. The db2fodc command can be used for manual first occurrence data collection (FODC) on problems that cannot trigger automatic FODC, such as hangs or sever performance problems. - The db2fodc tool captures data, to be included in the FODC package, and places it inside an FODC package directory - Either in the default diagnostic path or in an FODC directory path you specify using the -fodcpath parameter To collect data from sample database: db2fodc -db sample -hang A new directory prefixed with FODC_HANG_ is created under the current diagnostic path.



To collect data during a performance issue from a specific database using the full collection script.

db2fode -db sample -perf full...