75 ways to demystify DB2: #13 : Expert Advice : 'STMTID' - The key for long term performance tracking of an SQL

Body

One scenario you may have !!

You attend a monthly DB operation meeting.
Each DB administrator team from Oracle, DB2 and Sybase presents monthly events and various monitoring results. 

On first turn, an Oracle guy indicates a performance issue on a specific day and says a SQL has triggered the issue.

And.......he shows the historical performance data for the SQL by keeping on track of the change day by day.
Actually the hash key value 'SQL_ID' of Oracle enables this kind of analysis.

Now it's your turn DB2 !

  Your boss or clients are asking if you can make this kind of report from DB2 and how you can do that.
  What would you say ?

Basically what we need is identifying SQL queries with unique key value which is ALWAYS SAME regardless of any time, any situation such as recycling DB2.
I had seen some effort from DB2 mates such as making their own hash function or leaving a special tag on SQL comment head line.

Now, you don't need to do such things any more.

In DB2, the answer is 'STMTID' of MON_GET_PKG_CACHE_STMT. This is available from DB2 V10.5 Fixpack 3.
Nowadays, 'MON_GET_PKG_CACHE_STMT table function' is getting more popular than before even though many people still stick to dynamic SQL snapshot.

The 'STMTID' is the hash key value that identifies normalized statement text. So you can use this as the same purpose as the above scenario in DB2.

Somebody can say 'What about 'EXECUTABLE_ID' ?
One thing you could bear in mind is 'EXECUTABLE_ID' is not appropriate for long term performance trend analysis.
This can only be used for real time or half days depending on the workload of the system.

The executable ID also indicates a unique section resulting from a specific compilation.
 
For static SQL, this is created at bind time and for dynamic SQL, a unique value is created every time that the statement is compiled.
The package cache uses an LRU algorithm to evict entries when space for a new entry is needed.
When a same exact SQL is compiled and the sections are created on package cache again, this executable ID will be changed.
That means we can't use 'EXECUTABLE_ID' as the consistent key of an SQL.

As for another internal value 'STMT_PKG_CACHE_ID' of MON_GET_PKG_CACHE_STMT, it's consistent while DB is up and running.
However, once DB2 is recycled, this value also changes for the same SQL.
That means, this value is not useful when you restart DB once a week or a month for maintenance purpose.

In conclusion, just gather 'MON_GET_PKG_CACHE_STMT' periodically to keep SQL performance historic data.
Then, use the 'STMTID' when you need to see any performance changes of a specific interested SQL.