Db2

 View Only

Statement History for IIAS (the IBM Integrated Analytics System)

By Peter KOHLMANN posted Fri July 19, 2019 12:10 PM

  
One of the most frequent requests I hear is for how to create a history of all the SQL statements that run in the IBM Integrated Analytics System (IIAS) console. The good news is that it is available today.

By default, the IIAS console collects and saves up to 10,000 entries of the most recent query executions and prunes the collected statements after 7 days. You can view the historical query executions in MONITOR-Workloads-Query Executions panel by switching the Time mode from Realtime to History.

If your IIAS machine is at 1.0.17 or later, you can now also adjust the number of statements retained and for and how long they are retained. There is a new technote that details how to adjust the historical query execution retention period. You can change the retention rules either from the appliance command line or by calling a RESTful service API. The API can be called through a CURL command from a remote machine.

If you want to find out more about the different options for SQL statement monitoring have a read through the article (ibm.biz/DSMStatementHistory) I wrote that outlines how we collect and store the data. Through DSM and the database consoles there are three different ways to monitor statements:
  • In Flight Statements
  • Package Cache History
  • Individual Statement History
Db2 Warehouse and IIAS both use all the capabilities found in Data Server Manager to monitor the history of the SQL statements. The main difference is that these physical and virtual appliance offerings are preconfigured.

There are advantages and disadvantages to each method. Increasing the Individual Statement History retention will provide the most detailed history but it can use a lot of storage. You can also be very precise on how to use Workload definitions to just collect history from the applications, users or connections you want to target.

I also explain how to directly mine the data by accessing the tables that store the detailed history in the IIAS database. So if you don't want to use the IIAS console you can always write your own queries to access the information.
#Db2
0 comments
15 views

Permalink