The overhead that a monitor can place on disk and I/O subsystems is no longer the problem it used to be.
By Kent Milligan
Starting a Database Monitor on your server is quite simple, whether you're using the STRDBMON CL command or the System i Navigator graphical interface. The challenging part of a Database Monitor is minimizing the overhead that a monitor can place on disk and I/O subsystems while it's writing the collected data.
To minimize this overhead, filters were introduced in V5R4 to reduce the amount of data that DB2 for i collects with a Database Monitor. Less data being collected means less data being written to disk by DB2. The filters that are available on V5R4 using the graphical SQL Performance Monitor interface in iSeries Navigator are shown in Figure 1. Let's examine a couple of the filters to make sure the concept is clear. The job name filter provides the ability to have monitor data collected only for a specified job (e.g., QZDASOINIT). The "Queries that access these tables" filter results in the monitor only collecting data for SQL statements that reference a specific object (e.g., MYLIB/BIGTABLE).
Figure 1: In V6R1, System i Navigator offers special additional filters. (Click images to enlarge.)
Additional filters for TCP/IP port number and query governor limits were also added in V6R1 to both the STRDBMON CL command and the System i Navigator interface.
IBM has also recently delivered PTFs to provide additional filter capabilities to systems still running at i5/OS V5R4. These additional filters can be loaded on your server by applying PTF SI32059 or by loading level #17 of the V5R4 Database Group PTF. Since these new filters were delivered outside of a normal IBM release, the filters are available only through the STRDBMON CL command interface. In addition, the off-release delivery vehicle prevented IBM from adding new command parameters; instead, all of the new filters are passed in on the COMMENT parameter.
Two of the new filters delivered with the recent V5R4 PTFs enable customers to more easily determine if they are getting maximum performance benefit from the SQL Query Engine (SQE). IBM delivered the first phase of the SQE way back in V5R2 to improve performance of complex SQL statements and queries (see the Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS Redbook for more details). With each new release, IBM increases the amount of SQL requests that can be processed by SQE. More SQL requests being processed by SQE usually means better performance for applications and end users. Those requests that cannot be routed to SQE are processed by the Classic Query Engine (CQE). Since CQE has a smaller number of performance algorithms and techniques in its toolbox, there's a greater opportunity for slow-running queries, especially for complex SQL requests.
As a result, many customers who have installed a new IBM i release want to know if they have any SQL requests that are still being executed by CQE. That's where the new filters come into play. The Database Monitor output table has always included a field that flags whether an SQL statement was processed by SQE or CQE. Performing this SQE usage analysis, however, required that monitor data be collected for all SQL statements in a workload. The WANT_CQE_ONLY filter simplifies this process by collecting monitor data only for those statements processed by CQE.
As an example, let's take an application that executes 1,000 SQL statements and 950 of the 1,000 SQL statements are processed by the SQL Query Engine. An unfiltered database monitor collection would contain monitor records for all 1,000 SQL statements that are executed by the application. An analyst would then have to sift through the monitor records to find only those statements processed by CQE. In contrast, the WANT_CQE_ONLY filter results in monitor data being collected for only the 50 SQL statements processed by CQE. A database monitor collection will have much lower overhead and disk space requirements when monitor data is being collected for 50 SQL statements instead of 1,000 statements.
Here's an example of how easy it is to specify the new WANT_CQE_ONLY filter:
STRDBMON OUTFILE(MYLIB/APP1) COMMENT('WANT_CQE_ONLY')
As mentioned earlier, there's no CQE command parameter. Instead, the WANT_CQE_ONLY filter is specified as a string value on the COMMENT parameter. If you want the database monitor collection limited to SQL processed by SQE, the filter name is WANT_SQE_ONLY. With the CQE filter specified in this example, the monitor output table (MYLIB/APP1) will contain only SQL statements processed by CQE. The following query can be quickly run against the monitor output to identify the statements being processed by CQE.
SELECT qq1000 "Statement Text" FROM mylib/app1 WHERE qqrid=1000
For my testing, the monitor analysis resulted in the SQL statements being processed by CQE as shown in Figure 2.
Figure 2: In this example, the monitor analysis resulted in CQE processing two SQL statements.
The first SELECT statement is processed by CQE because the FROM clause references a logical file object. The second SELECT statement is processed by CQE because of the reference to the Upper function. With DB2 for i 6.1, SQE supports both the Upper and Lower function; however, SQE still does not support logical file references on the FROM clause.
You must be aware that if a database monitor is not started at the beginning of a job or connection, the collected monitor data may not include the data necessary to determine if the statement was processed by CQE or SQE. As a result, a database monitor collection that is supposed to include all those statements processed by CQE may not include statements that were executed by CQE.
Hopefully, you can see how these new filters can reduce the overhead of the Database Monitor running on V5R4 systems. IBM will eventually formalize these filters as command parameters and options on the graphical System i Navigator interface in future IBM i releases. For more information on the Database Monitor tool and analysis, please reference the IBM Redbook OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4.
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online