Give Web Service Calls a Rest with Newest Db2 Update

 One might imply from the title that I’m advocating that developers stop using SQL to invoke web services from their IBM i applications. That is not my intent at all! In fact, the Db2 enhancements associated with the newest IBM i Technology Refresh for the IBM i 7.3 and 7.4 releases make it even more compelling to use SQL to invoke web services from IBM i applications.

The reason that SQL invocation of services is more compelling with the latest updates is that Db2 for i now supports the following set of new HTTP functions in QSYS2. These HTTP functions allow SQL to use RESTful services without the overhead of creating a Java Virtual Machine (JVM) like the existing HTTP functions found in SYSTOOLS.

HTTP_GET	HTTP_GET_VERBOSE
HTTP_POST	HTTP_POST_VERBOSE
HTTP_PUT	HTTP_PUT_VERBOSE
HTTP_DELETE	HTTP_DELETE_VERBOSE

The elimination of the JVM requirement means that web service invocations will run faster and more efficiently on your IBM i server. IBM i applications can scale to support more users and more transactions with these new HTTP functions requiring less usage of CPU and heap temporary storage. More details from my IBM colleagues on these HTTP functions can be found here.

The latest Database Group PTFs also include a new Db2 for i service, ACTIVE_QUERY_INFO, that can be helpful in narrowing down your analysis and investigation of query performance issues.

While not delivered with the Database Group PTF, the newest IBM i Access Client Solutions (ACS) version also includes some nice add-ons for Db2 and SQL. These ACS updates include Run SQL Scripts supporting tighter transaction control as well as improved error feedback.

To demonstrate the improved Run SQL Scripts error feedback, let’s use a simple query that divides two integer columns: SELECT Col1/Col2 FROM Tab1. The following figure shows the output from the latest version of ACS Run SQL Scripts. In this example, the second row in table Tab1 has a Null value for Col2 and the fourth row in table Tab1 has a value of 0 for Col2. Those Col2 values explain why the result set returned by this simple query does not contain a numeric division result in the second and fourth rows. 

The “-“ value indicates a Null value was returned for the division result which makes sense since the divisor value in the second row was Null.  The “++++++++++++” value in the last row indicates an error occurred due to the divisor value being 0.  Prior to this latest enhancement, Run SQL Scripts would have returned the same “-“ value for both the second and fourth rows in the result set making it difficult for the user to detect that a divide by zero error had occurred during the execution of their query.  Disclaimer – this improved error feedback was actually part of the prior ACS update, but I forgot to highlight this enhancement in my coverage of the prior Db2 for i update.

These great enhancements are a good reminder about the importance of keeping both your Database Group PTF level and ACS versions current. 

Speed Dating Your Legacy Dates

Given that I’ve been happily married for over 30 years, I’ll have to admit that I have no experience with speed dating. However, I do have lots of experience seeing the many ways in which clients store date values in legacy formats in their Db2 for i databases.  These legacy date formats range from storing the entire date value in a single character or decimal column to storing the components of a date value (Year, Month, Day, Century) in separate columns.  This blog topic focuses on the intersection of performance (i.e., speed) and legacy dates because our team has also seen many customer SQL requests that consume a fair amount of system resources while transforming these legacy date values into SQL date values.

IBM certainly contributed to this use of legacy date formats since Db2 for i has not always supported a Date data type. The Date data type was not available to use until the V2R1.1 release of OS/400. While our Expert Labs team certainly works with many clients now using the Date data type, there are a significant number of clients still using Db2 for i tables that were designed and created prior to that release thanks to the strong backward compatibility of our great platform.

The large number of legacy date transformations on SQL requests are not a surprise due to the continued rise in SQL adoption by IBM i developers and SQL’s rich support for date processing. This support includes many SQL functions that make it easy to convert numeric and character date values into real SQL date values.

Here’s a sampling of some of the legacy date transformations that have shown up on IBM Expert Labs client engagements. Other clients have created their own user-defined functions(UDFs) to perform the legacy date conversions. 

   ==> TIMESTAMP_FORMAT(CHAR(DateDec8) , 'YYYYMMDD')
   ==> DATE((DTYEAR+2000) || '-' || DTMNTH || '-' || DTDAY)
   ==> ((YRVAL*10000) + (MTHVAL*100) + (DAYVAL))

While the built-in functions and UDFs make it easy to transform the legacy date formats, these transformations are CPU intensive.  If these transformations are performed on thousands of rows in your table, then the CPU costs start to add up. Especially, when you consider that it’s the same legacy date values being converted over and over again. And that’s exactly where speed dating of your legacy date values can help.

Speed dating of legacy date values involves pre-converting your legacy date values and storing them in a Date dimension or lookup table. The same SQL built-in and user-defined functions can be used to populate the table.  Once this lookup table has been populated, your SQL queries can simply join to the table whenever you want to use the equivalent SQL date value associated with your legacy date value.  Joining to a dimension table is a low-cost alternative to converting your existing Db2 tables to use the Date data type.

The following graphic visually depicts the join to a Dates lookup table from a legacy Orders table which has date values stored in a legacy character column.

The ORDERDATE column is storing a date value in an 8-byte character field.  The Dates lookup table has that legacy date value stored in the DC_MDYY_CHAR column and the equivalent SQL date value in the DC_DATE column. When an SQL query wants to display or process the ORDERDATE column as an SQL date value, it can just join to the Dates dimension table on the ORDERDATE and DC_MDYY_CHAR columns and then reference the DC_DATE column.  The SQL date value is accessible with a simple lookup instead of a CPU intensive transformation.  Assuming that the proper indexes are covering the join columns, a join can be processed by Db2 very efficiently and quickly. Even if you have 50 years of dates stored in the lookup table, the table is only going to have just over 18,000 rows – a small table when it comes to join performance.

Notice that in this example that the Dates dimension table also is being used to store contextual information such as Day of Week number and Day Name.  These contextual date values were also pre-calculated so they are also ready to be used by your SQL request without any dynamic calculation.  You could easily add other contextual date values such as business fiscal year or quarter to the lookup table. This Db2 Web Query Redbook has more details on creating and using a Date dimension table. Even better, Db2 Web Query also ships with a utility to create your own dimension table.

So how much performance savings can be realized with speed dating? As with any performance question the answer is “It Depends”.  One client recently made the switch to using a Date lookup table because they had an SQL query which performed many legacy date transformations using this pattern: DATE((DTYEAR+2000) ||'-'||DTMNTH||'-'||DTDAY). Obviously, your mileage may vary depending on the query complexity and the number of rows being processed.  

Feel free to reach out if you need assistance applying this speed dating technique to the legacy dates stored in your Db2 for i databases.

Tale of the Tape - SYSDUMMY1 vs Tableless SQL Performance

 I thought about titling this entry “Tableless SQL for Dummies” to play off the old Dummies book series and the focus of this entry: the Db2 dummy table, SYSDUMMY1.  However, I thought that might be a little too abrasive for some readers.  Plus, that catchy title doesn’t reflect that SQL performance can be improved by simply replacing SYSDUMMY1 queries with a tableless SQL request. This Tale of the Tape comparison will help you better understand this performance difference.  

You may or may not know that Db2 provides a dummy table, SYSDUMMY1, in the SYSIBM schema.  The SYSDUMMY1 table contains a single row and column which as you can see from the following example makes it really easy for a developer to invoke an SQL function or retrieve the value of an SQL special register. 

SELECT UPPER(:hv), CURRENT TIMESTAMP
  INTO :hv1, :hv2
FROM sysibm.sysdummy1

Back when I was working with software vendors, the SYSDUMMY1 table was also quite handy when porting Oracle applications to the IBM i platform because some of those applications relied on using the Oracle dummy table - DUAL.  Database vendors often included a dummy table because all SQL DML (Data Manipulation Language) statements required a table reference. However, that’s no longer case because the SQL standard has been enhanced to support tableless queries.

The VALUES statement was the new addition to the SQL standard which enables you to run tableless queries.  Taking the previous example, you can see how it is implemented with the tableless support:

VALUES (UPPER(:hv), CURRENT TIMESTAMP)
  INTO :hv1, :hv2

These two examples are almost identical except for the FROM clause. Not only does the VALUES statement save you from having to type the FROM clause, it also will return the results faster because there is no FROM clause! Let’s dig into why the FROM clause causes a performance difference. 

 

Any time there is a table referenced on the FROM clause on an SQL request, the Db2 for i engine has to performance serialization actions to support concurrent access of the specified table and its rows. Even though the SYSDUMMY1 table is small and contains a single row, the required serialization steps makes it slower to process than the equivalent tableless query. 

 

To measure this performance difference, I created separate SQL procedures with each one of them running these two equivalent SQL statements 50,000 times. 

SELECT CURRENT TIMESTAMP INTO :hv1 FROM sysibm.sysdummy1

VALUES CURRENT TIMESTAMP INTO :hv1

The average run time for the SELECT statement procedure call was 1,430 milliseconds while the average run time for the VALUES statement call was 340 milliseconds – a 4x times difference in performance!  While you might be thinking that the average execution time of each method is very small when you consider each statement was executed fifty thousand times, remember the old proverb that “Little by little, a little becomes a lot”.

You might find that this type of SYSDUMMY1 request is being run over and over again on your system.  In fact, I recently reviewed a customer’s SQL Plan Cache Snapshot data and they had a SYSDUMMY1 query that has been run over 360 million times on their system.  So even if the tableless query approach was just 100 microseconds faster, the multiplicative impact of that small improvement will add up to make a difference on their system. 

The performance advantages that tableless SQL requests hold over SYSDUMMY1 queries should make it clear that tableless queries are the clear winner in this comparison.

Performance Toolbox for IBM i Services

 In March, I wrote about the importance of thinking about performance as part of using IBM i Services with SQL.  One reader suggested a follow-up article highlighting which tools would be helpful for analyzing the performance of an IBM i Service, so that’s what I aim to do here. 

Since I’m a Db2 guy, we’ll start with the database performance tools first which means the IBM i Access Client Solutions (ACS) toolset. Visual Explain is THEE tool for understanding the implementation details of an SQL request. Visual Explain can be used from many different perspectives – interactively from Run SQL Scripts as you run your SQL or post-SQL execution from the Plan Cache or Plan Cache Snapshots.

 Visual Explain graphically lays out the blueprint of the query optimizer’s implementation for a query.  This graphical representation shows all of the Db2 objects used in the query implementation including tables, indexes (some might still refer to these as access paths), and user-defined functions.  The number of objects represented in a query plan can give you an indication in how much work you’re asking the system to do.  For example, joining together ten tables is usually going to require consume more system resources than joining together two tables.

Below I’ve included a portion of Visual Explain output from a customer’s report that was joining to the Group_Profile_Entries catalog view/service.   At face value, this view seems relatively simple in that it returns a row for each user profile that belongs to the group profile.  As you can see from the Visual Explain representation, the processing required to return data from this view is anything but simple. This view ends up joining together the result data from 5 different IBM i Service calls in order to return the user profile entries that belong to a specific group.  Instead of simply reading data from underlying table(s) like many of the Db2 for i catalog view, the Group_Profile_Entries view has to dynamically generate the data using user-defined table function calls (ie, external program calls).  As you might guess, program calls are more expensive than reading a row from a table. 

Based on this Visual Explain output, you should be getting a sense that SQL referencing the Group_Profile_Entries may consume a fair amount of system resources.  This perspective may influence how often you run the SQL request and when you run the SQL request.  In addition, you’ll want to look at the query to see if more filtering can be specified with the Group_Profile_Entries reference to limit the amount of data that must be dynamically generated. 

System Performance tools can also be used to analyze the performance signature of the IBM i Services that you’re trying to use. These tools are great at providing insights into the system resource utilization that are used by a service.  Performance tools that you might want to use to analyze the system impact of IBM i services queries are Collection Services and IBM iDoctor.

Here’s a relatively simple query using the Output_Queue_Entries service to count the number of output queue entries for each job on the system. 

SELECT job_name AS job, COUNT(*) AS sum_splf
  FROM qsys2.output_queue_entries
  GROUP BY job_name ORDER BY COUNT(*) DESC

 

While this query is simple, the following IBM iDoctor output shows that this service resulted in the job waiting on a large number of page faults while it was off processing each output queue entry on the system. This page fault wait time signature is probably an indicator that you should consider tightening the focus of your request by limiting the number of jobs that have output queue entries checked. 

If you’re looking for help becoming more proficient with these tools, IBM Expert Labs can help.  The Database Engineer (DBE) Enablement offering features hands-on training with Visual Explain and the full suite of ACS SQL performance tools.  For assistance getting started with the system performance tools, check out the IPAWs training or the IBM iDoctor workshop.

Simplify your SQL development by staying current

Since I returned to the IBM i world last Fall, I’ve spent time reviewing all of the Db2 for i and SQL enhancements that have been delivered during the 5+ years that I was off working on Watson.  The Rochester development team has definitely been busy cranking out some great functionality while I was away. It’s been nice to see improvements to the SQL standards that deliver real efficiency for developers.

One of those SQL standard additions that caught my eye is the LISTAGG aggregate function.  The documentation states that this function aggregates a set of string elements into one string by concatenating the strings.  I think a simpler real-world benefit description is that it makes it easy to combine values from multiple rows into a single row. For example, you’re asked to produce a report of the shipping companies that your business uses by region and the report format needs to be the following:

NORTH	FEDEX, GARZA SHIPPING, SPEEDEE DELIVERY
SOUTH	FEDEX, TYLER TRUCKING, UPS, USPS

 

This is easily done with the LISTAGG function with this simple, but powerful SQL statement:

SELECT region, 

       LISTAGG(DISTINCT carrier, ', ') WITHIN GROUP(ORDER BY carrier)
   FROM shipping_method GROUP BY region

I highlight the simplicity of this solution because you could do this in SQL before with recursive SQL syntax, but those types of SQL statements are longer and difficult to understand. In this article, I provide more details on LISTAGG and contrast it with the recursive SQL solutions.

I believe a feature like this is also a good reminder of the benefits of staying current on your IBM i release level and Database Group PTFs.  The more current your systems are, the more Db2 & SQL features there are available to simplify life for your developers.

Latest IBM i TR adds Query Supervisor to Db2 & Your Team!

The latest IBM i Technology Refresh was announced this week which means there are new Db2 for i features and functions available to you. This latest announcement includes Db2 Mirror for i enhancements, SQL advancements, and new & updated IBM i services – however, the Query Supervisor is by far the most interesting new bell and whistle.

I like to remind IBM i clients that when they start using SQL they’re actually extending the size of their development team. Your team grows in size because the Db2 for i SQL engine features a Query Optimizer whose job it is to determine the fastest way to access and retrieve the data. Your development team is able to focus on the coding of business logic and leave the performance logic to the Query Optimizer. This latest announcement adds another member to your team with the introduction of the Query Supervisor. 

The new Query Supervisor allows you to take query governance and monitoring to another level. If you’ve been around the platform for a while, you might be thinking that Db2 for i already supports query governance and you would be right.  The Db2 for i support for a Query Governor goes way back to V3R1.

The Query Governor is known as a predictive query governor because it allows action to be taken before a query starts running. The governor allows you to prevent a query from wasting system resources when the query’s runtime implementation is predicted to exceed the time and/or temporary storage thresholds that you’ve defined for your server. The predictive aspect means that the Db2 for i Query Optimizer had to estimate the amount of time and resources that it takes to run the requested query.

As you might imagine, generating an accurate estimate is a tall task. One of the biggest hurdles is that the optimizer has no idea how busy the system will be when the query starts running. A CPU intensive workload could start running milliseconds after the current query starts and there’s no easy to way to predict this. Thus, the optimizer estimates are not going to be one hundred percent accurate. The optimizer’s estimates are relatively accurate meaning that long-running queries will have larger estimates than small-running queries. 

This “relative” accuracy of the optimizer’s estimates made it difficult to use the predictive query governor function system-wide. If you set a query time limit of 5 minutes on the system, a query that runs closer to two minutes might be prevented from running because the optimizer’s estimate was 6 minutes. A query that was estimated to run in 3 minutes might actually run for 7 minutes due to the system being busier than predicted. As a result of these challenges, clients have asked IBM for additional query workload controls and IBM has delivered on that request with the new Query Supervisor feature.

The Query Supervisor provides a solution for taking action on an active query whose actual resource usage exceeds a user-defined threshold. When the Db2 engine detects that your threshold has been exceeded, it will call a user-defined exit program to take action. That action might be cancelling the query or just logging it and notifying an administrator about the offending query; the possibilities are endless. If the exit program chooses to cancel the query, then one could argue that system resources were wasted by a query that didn’t complete. However, the upside is that you were able to limit the amount of time that the query caused overhead on your system.  Like the Query Governor, the Query Supervisor supports SQL and non-SQL queries (OPNQRYF, etc.).

The new Query Supervisor support allows you to set the following threshold types with a simple call to an SQL procedure - QSYS2.ADD_QUERY_THRESHOLD.

• ELAPSED TIME
• TEMPORARY STORAGE
• CPU TIME
• TOTAL IO COUNT

The threshold value is dependent on the type of threshold. The threshold value for Elapsed Time and CPU Time would be specified in seconds while the value specified for a Temporary Storage threshold would be given in megabytes. The Total IO Count threshold value is just a count because it specifies a limit on the number I/O operations that a query can perform.

With the Query Supervisor, you can also specify filters to narrow the focus of the supervisor’s threshold monitoring. The threshold filters that can be applied are subsystem names, job names, and user profile names to include or exclude. The values for each of these thresholds can be up to 100 names and can include generic names (eg, ‘RPTUSER*’). 

Here’s an example of a threshold definition to prevent QZDASOINIT jobs from having queries that run longer than 5 minutes, unless the queries were submitted by a manager. Notice in this example that you’re able to specify multiple threshold filters on the definition.

 CALL QSYS2.ADD_QUERY_THRESHOLD (
  THRESHOLD_NAME => 'QZDASOINIT Time Limit',
  THRESHOLD_TYPE => 'ELAPSED TIME',
  THRESHOLD_VALUE=> 300,
  JOB_NAMES      => 'QZDASOINIT',
  EXCLUDE_USERS  => 'MGRID*',
  SUBSYSTEMS     => '*ALL'  )

Thresholds can be easily removed by specifying the THRESHOLD_NAME on the QSYS2.REMOVE_QUERY_THRESHOLD procedure

The user-defined exit program needs to be registered to the new QIBM_QQQ_QRY_SUPER exit point. As mentioned earlier, the exit program allows you take a wide variety of actions. That flexibility is enhanced even further with the input values that are passed to the exit program. Here’s some of the more interesting input values to consider utilizing in your program logic:

–        Threshold name, type & value

–        User, Subsystem, and Job Info

–        SQL Statement text, Plan Identifier & Host Variable values

–        Client Register Values

To try out your new Query Supervisor team member, you just need to load the Database Group PTFs onto your IBM i 7.3 or 7.4 systems when they are released next month. 

IBM i Services are Great, but they're NOT Magic

 The development team in Rochester has been quite busy over the last several years making it easy to use SQL to access data and services related to the IBM i operating system. These IBM i Services can be used with SQL to perform a variety of requests; these requests range from retrieving data on active jobs & database connections to placing entries on data queues to checking object authorities to writing data to IFS files. I’m using the "IBM i Services" term here generically to refer to services that you call and to queries against system catalog views because many of the newer views utilize service calls to return data.   

The good news is that we’re seeing a lot of IBM i developers leverage these services to solve a number of problems. The bad news is we’re finding many developers using these services without any thought given to performance. Little consideration is given to how much data is being requested and how often the request is being made - it’s almost like they think there’s something magical about the performance of these services just because they are from IBM. While the IBM development team creating these services is really good, performance analysis and testing should be part of the rollout of any new functions using the IBM i Services. The same performance rigor that you would apply to the deployment of new business processes on your production systems needs to be applied to the deployment of code using the IBM i Services.

Our Db2 team in IBM Expert Labs team analyzes the Top 25 Most Time Consuming SQL statements as part of the SQL Performance Assessment service that we perform for clients.  In the last two years, we’re often finding SQL statements using IBM i Services in the list of most time consuming SQL. These time consuming SQL statements often have a negative impact on overall system performance because of their resource usage. A recent conversation with colleagues in IBM Support revealed they are seeing similar trends with clients reporting system performance problems, only to find a suboptimal services call as the root cause.

Here are some performance considerations when using IBM i Services with SQL.

First, not all services are designed to be called repeatedly on the system and/or to be called during daytime hours when system usage is heavy. Some services consume more system resources than others – that’s why performance testing is key to get a feel for how long the service takes to run and how many resources will be consumed by the service. The IBM Support team shared one story of a client complaining about system performance only to determine the client was calling the DISPLAY_JOURNAL service with very few input filters every two minutes on their production system. Repeated calls to other services like DATA_QUEUE_ENTRIES may be perfectly fine and that can be easily determined with performance testing and analysis. Performance analysis may also lead you to determine that the service needs to be called and run overnight when system usage is low.

Second, while the creation of indexes is often a solution to make SQL queries run faster & more efficiently  – this technique does not help with SQL statements using IBM i Services.  Indexes are not applicable because most IBM i Services rely on functions to dynamically generate the result data, so there’s no data to index. The function  logic is accessing & processing IBM i objects with system API calls. The operating system data being processed is not stored in tables and rows which are normally the target of an SQL request.

The lack of help from indexing leads to the last and probably most important performance consideration – ensure your IBM i Service calls are tightly focused.  A tight focus means specifying as many search parameters as possible on your calls to minimize the amount of data that has to be touched in order to return the result. For example, one could use the following query against the SYSPROGRAMSTAT catalog view to retrieve release details about an SQL program (MYSQLPGM)

SELECT target_release, earliest_possible_release     
  FROM qsys2.sysprogramstat
  WHERE program_name='MYSQLPGM'

While this query works, the scope is not focused – the request directs Db2 to search through every library containing an SQL program instead of scoping the search to the library containing the program.  The focus has been tightened on this new version of the SELECT statement resulting in the same result, but with better performance since the scope was narrowed to a single library:

SELECT target_release, earliest_possible_release     
  FROM qsys2.sysprogramstat
  WHERE program_name='MYSQLPGM' AND program_schema='MYPGMLIB'

Including search predicates on a WHERE clause is one way to narrow the focus of an IBM i Service.  The other method for narrowing focus is passing input parameters on the service call itself. Let’s use the OBJECT_STATISTICS service as an example. An audit requires you to provide a list of all the program & service program objects on your system. The following SELECT statement generates the required listing, but the scope of the service request is too broad requesting all libraries on the system be processed by the table function call.

SELECT objname, objlib, objtype
  FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALL', 'PGM SRVPGM'))
  WHERE objlib NOT LIKE 'Q%'

While the IBM libraries are filtered out with the WHERE clause – that filtering is performed after the data is returned by the table function.  The amount of data processed by this call would be greatly reduced by changing the first input parameter from *ALL to *ALLUSR so that only user libraries are processed by the service.  Thus, eliminating the need for the WHERE clause.

SELECT objname, objlib, objtype
  FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALLUSR', 'PGM SRVPGM'))
  WHERE objlib NOT LIKE 'Q%'

Some IBM i services have options that reduce the amount of detailed data is collected and returned in order to speed up the performance.  The OBJECT_STATISTICS service has such an option, so this request can be made even more efficient by adding a third parameter with a value of *ALLSIMPLE. 

SELECT objname, objlib, objtype
  FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALLUSR', 'PGM SRVPGM', '*ALLSIMPLE'))
  WHERE objlib NOT LIKE 'Q%'

With these guidelines in tow, you should be able to use IBM i Services in production without negatively impacting system performance.  You can also find a ton of good examples of using IBM i Services on  Scott Forstie’s SQL examples site:  ibm.biz/Db2foriSQLTutor  Let me know if your company needs assistance tuning your usage of IBM i Services.