Using the DBA Cockpit for Monitoring Advanced Log Space Management

[SabineReich]

Do you know the DBA Cockpit? It’s SAP’s tool for monitoring SAP systems on IBM Db2 for Linux, UNIX, and Windows. It comes to you for free as part of every ABAP-based SAP system. If you already know it, read on, and you’ll learn more about some of the latest features that have been built in for monitoring Advance Log Space Management (ALSM). If you haven’t heard of the DBA Cockpit yet, try it out using transaction code DBACOCKPIT, or have a look at this video.

Monitoring Advanced Log Space Management

As of IBM Db2 11.5 MP4 FP0SAP, Advanced Log Space Management (ALSM) is available for productive use in SAP systems.  ALSM can help avoid transaction log full situations where applications rarely commit and hold the oldest log file very long, which prevents Db2 from releasing closed log files.  In addition, ALSM can also help speed up rollback processing for applications that span a large log volume because the extracted rollback information is stored in dedicated files. (Check the blog post by my colleague Frank-Martin Haas for more details about ALSM.)

Now, what you probably should monitor when using ALSM is the following:

• Is log extraction enabled and active? If not, you might want to check whether errors are keeping it from working properly.


• Is log extraction throttled, and if so, what is the reason? Log extraction throttling might occur for various reasons, and you don’t always need to be concerned about it. For example, log extraction only starts when the available log volume is utilized above the threshold of 80%. Until then, log extraction is throttled.


• You might want to know the data throughput of the log extraction and the average lookup time from the extraction log for rollback and currently committed.

To support such monitoring, as of IBM Db2 11.5 MP5 FP0, IBM has enhanced its Db2 monitoring table function SYSPROC.MON_GET_TRANSACTION_LOG with the following metrics:

• LOG_EXTRACTION_STATUS


• LOG_EXTRACTION_THROTTLE_REASON

See also the IBM documentation: MON_GET_TRANSACTION_LOG table function - Get log information.

For example, you can now check the status of your log files as follows:

SELECT first_active_log, 

       last_active_log, current_active_log, 

       method1_next_log_to_archive,

       case archive_method1_status

         when 1 then 'Good' else 'Failure' end as archive_method1_status,

       log_extraction_last_extracted_log AS last_extracted_log,

       case log_extraction_status

         when 1 then 'Error' when 2 then 'Active' else 'Other' end AS log_extraction_status,

       log_extraction_throttle_reason

FROM TABLE(MON_GET_TRANSACTION_LOG(-1)) as t

A result could look as follows, for example:

FIRST_ACTIVE_LOG     LAST_ACTIVE_LOG      CURRENT_ACTIVE_LOG   EASON

-------------------- -------------------- -------------------- -------

                  95                  153                  153

   



METHOD1_NEXT_LOG_TO_ARCHIVE ARCHIVE_METHOD1_STATUS LAST_EXTRACTED_LOG   LOG_EXTRACTION_STATUS

--------------------------- ---------------------- -------------------- ---------------------

                        153 Good                                    145 Active      





LOG_EXTRACTION_THROTTLE_REASON

------------------------------

DB_LOG_SPACE_USED

Make Your Life Easier: Monitoring ALSM Using the DBA Cockpit

You can get a more comfortable view on your log file status and ALSM using the DBA Cockpit as follows:

1. In your SAP system, call up the DBA Cockpit using transaction code DBACOCKPIT.


2. In the navigation pane, choose Performance -> Transaction Log.


3. Here you can find the following status information and metrics:

• 
  
  
  
  • Log Extraction Status
  
  
  • Reason for Throttling Log Extraction
  
  
  • Log Extraction Analysis Throughput
  
  
  • Log Extraction Write Throughput
  
  
  • Average Extraction Log Lookup Time for Rollback
  
  
  • Average Extraction Log Lookup Time for Currently Committed
  
  
  
  

Advanced Log Space Management: Metrics in the DBA Cockpit

How do you know whether your DBA Cockpit has the latest metrics for ALSM? Check the following SAP Note to see whether your SAP system is on a support package level that’s sufficient:

3020581 - DB6: DBA Cockpit: New metrics for monitoring Advanced Log Space Management (ALSM) as of Db...

Summary

Advanced Log Space Management (ALSM) is a new feature for SAP systems running on IBM Db2 for Linux, UNIX, and Windows. I hope I have convinced you that using the DBA Cockpit with its latest features for monitoring ALSM makes the life of the Db2 administrator a bit easier. If you want to learn more about the DBA Cockpit, also check out the documentation that is available on SAP Help Portal:

Database Administration Using the DBA Cockpit