DB2 dynamic SQL and Stored Procedure SQL view operations

Source: Internet
Author: User

The following articles mainly describe the actual operation steps for viewing DB2 dynamic SQL and Stored Procedure SQL, the following describes the actual operation steps for viewing DB2 dynamic SQL and Stored Procedure SQL. We hope this will help you in your future studies.

We all know that the dynamic SQL statements of the DB2 database can be viewed using snapshot, and the execution of SQL statements in the stored procedure can be viewed using event monitor.

Dig a hole first and add it later)

Supplement 1: event monitor)

Run event monitor

Unlike snapshot, event monitor has a significant impact on DB2 performance. This is caused by the amount of information written for each event object. Statement monitor has a great impact on performance, because the database engine must execute all the extra work for each query: not only can execute the query, the DB2 engine must also write all the features and runtime information of this query. This information must be written to a text file, which further reduces performance.

First, you must create an event monitor and run the monitor to collect the data to be analyzed. Rkmon is the name of the event monitor.

Open a new DB2 command line processor session and execute the following DB2 UDB command:

  1. db2 => connect to dbname  
  2. db2 => update monitor switches using statement on  
  3. db2 => create event monitor rkmon for statements write to file '/db2'  
  4. db2 => set event monitor rkmon state=1 

Keep this session open until these database activities are completed. Make sure that the/db2 Directory has enough space to save the trace file. The/db2 Directory is selected here because all users can access this directory, but other directories can also be selected. Make sure that DB2 has access permissions for this directory ). The directory size depends on the number of SQL statements you want to capture.

In the beginning, it is best to set it to 500 MB. Execute normal database activities until the time period you want to monitor ends. This monitoring phase can be a problem generation period or a common database activity process. In the/db2 Directory, you should see a group of files with the extension ". evt. Return to the session opened in step 1 and issue the following statement:

  1. db2 => set event monitor rkmon state=0 
  2. db2 => terminate 

Run the following command at a normal Command Prompt:

  1. $ Db2evmon-path/db2> sqltrace.txt
  2. Delete rkmon
  3. Drop event monitor rkmon

The above content is an introduction to DB2 dynamic SQL and Stored Procedure SQL viewing. I hope you will get some benefits.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.