In my DB2 for z/OS-related work over the past 25 years, I've done a lot in the way of performance analysis, looking at individual queries and at DB2-accessing application programs (both transactional and batch in nature). Time and time again, sometimes in reviewing trend data and sometimes in examining DB2 monitor output to gauge the impact of performance tuning actions, I've seen that the CPU time consumed in SQL statement execution rises or falls with the associated level of GETPAGE activity (a GETPAGE is essentially a request by DB2 to examine a page from a table or an index). The correlation I observed between CPU cost and GETPAGEs for SQL statements was so high that I came to see GETPAGE activity as the number one determinant of a query's CPU consumption in a mainframe DB2 system (and I declared as much in a blog entry that I wrote a few years ago while working as an independent DB2 consultant).
Well, a lot of rules have exceptions, and that turned out to be the case for this rule of thumb. I got some information from a DB2 for z/OS DBA that turned my notion of the relationship between GETPAGEs and CPU cost for SQL statements on its head. The data I saw seemed at first to be very strange, but as I dug deeper it started to make sense. I still see GETPAGEs as being, more often than not, the most important factor with regard to a query's CPU cost, but it's not the only factor, and sometimes other aspects of SQL statement execution exert a greater influence on CPU consumption. In this blog entry I'll show you why GETPAGEs are not always the strongest flavor in the mix of determinants that influence CPU consumption for DB2 for z/OS-accessing queries.
Interestingly, the case about which I'm writing involved data compression. That's a DB2 for z/OS feature that's been around a LONG time (around 20 years or so), and which is VERY widely used. Data compression is super-popular at mainframe DB2 sites because it saves a ton of disk space and it's virtually free, overhead-wise. Why is DB2 for z/OS data compression so CPU-efficient? Two reasons: first, it gets a hardware assist from System z servers (big difference versus a software-only implementations of data compression). The second reason is GETPAGE-related: because compression often allows you to get three to four times as many rows in a page versus a non-compressed table space, the number of GETPAGEs required to access data frequently drops considerably when a table space is compressed. Yes, even hardware-assisted data compression has a cost, but many DB2 for z/OS-using organizations have found that the reduction in GETPAGE activity that goes along with compression largely offsets the CPU cost of compressing and decompressing data rows; thus, the near-zero overhead effect of DB2 compression observed in many systems.
Imagine my surprise, then, when the aforementioned DBA presented me with DB2 monitor data showing the effect of data compression on a DB2 for z/OS-accessing query. I saw that the number of GETPAGEs associated with execution of the query dropped by 65% after the target table space was compressed. No surprise there. I also saw that the query's elapsed time dropped by a whopping 92%. Not a huge surprise there, either. The lion's share of elapsed time for many a query is wait-for-I/O time, and with many fewer pages having to be read from disk to obtain the query's result set, a much-reduced run time would be expected. Then came the not-expected result: the query's CPU time went up by 31% following compression of the target table space.
What?!? How could that be? How could you have a 65% reduction in GETPAGEs and and a 31% increase in CPU time? Sure, I wouldn't expect CPU time to go down by the percentage of GETPAGE reduction, because some CPU cost does get added when COMPRESS YES is put into effect for a table space. Still, though, I would have expected either a modest decrease in the query's CPU cost, or at worst a break-even situation, with the lower level of GETPAGE activity cancelling out the CPU expense of decompressing result set rows. This just didn't make sense to me.
My confusion started to dissipate as I learned more about the query in question, and thought more about what DB2 was doing in executing the query. The query targeted a table with about 3 million rows, and 600,000 of those rows had to be examined in order to generate the result set. The large number of rows needing evaluation was due to the fact that the primary search argument -- the one that really filtered out a lot of rows -- was a non-indexable LIKE predicate used to find a particular character string that could occur anywhere within a certain column of the table (the % in front of the LIKE made the predicate non-indexable; an index on the column could have been used in executing the query had there been only a % after the LIKE, versus the %LIKE% that enabled location of the character string anywhere in the referenced column). Another predicate in the query -- an indexable one -- filtered out 80% of the table's rows, but the remaining 600,000 rows had to be individually examined for the existence of the required character string in the referenced column, as noted.
Here's where the compression effect kicked in. See, when rows in a compressed page are evaluated against a query search argument, they are individually decompressed, one at a time. Sometimes (very often, in fact), an index can be used to identify the small number of qualifying rows that must be examined in a given table page (or simply retrieved from the page, if result set row qualification can be fully determined at the index level), and decompressing those few rows in the page will be a relatively small component of the SQL statement's overall CPU cost. Other times (and this was the case for the query that the DBA discussed with me), an index can be used to reduce the number of table pages requiring access in query execution, but in a given page there might be 30 or 40 or more rows that have to be decompressed and evaluated (and maybe retrieved). When decompression cost is driven by the expansion of 30 or 40 rows in a page, versus 3 or 4 rows, that cost can rise to the point that it exceeds the cost reduction associated with diminished GETPAGE activity for a compressed table space. That's exactly what happened in the situation I've described. Was the compression performance result reported by the DBA good or bad? That depends on whether the performance objective for the query was improved response time (in which case the big reduction seen for the query's elapsed time with the compressed table space would be a positive outcome) or lower CPU consumption (this particular query consumed less CPU time when the target table space was not compressed).
It all goes to show that rules of thumb are best thought of as guidelines, as opposed to absolutes. You still have to use your brain in analyzing query performance, and you have to be ready to dig below the surface of a situation. GETPAGE activity is indeed a very important factor in determining a query's CPU cost in a DB2 for z/OS system, and tuning actions that reduce GETPAGEs are generally beneficial with regard to SQL statement CPU efficiency. What you have to remember is that GETPAGE activity is one of several determinants of query performance. When DB2 has to do something to a lot of rows in each page accessed in the execution of a query -- things like decompression, value transformation via scalar functions, casting of one data type to another, arithmetic operations, etc. -- the CPU cost of GETPAGEs can become a relatively smaller part of the query performance big picture. Consider what DB2 has to do with the rows on a page -- and with how many of the rows on that page -- once it gets the page. There will be times when improving the CPU efficiency of a query is is not just a matter of reducing the query's GETPAGE count. Also, have a good understanding of your query tuning objective: is it reduced elapsed time, or reduced CPU consumption, or both?
Rules of thumb can be useful in guiding your query tuning efforts. Don't let them become blinders that prevent you from thinking outside of the box.
Friday, August 30, 2013
Friday, August 9, 2013
DB2 for z/OS: Get Your DDF-Related Dispatching Priorities Right
The DB2 for z/OS Distributed Data Facility (DDF), the component of DB2 that provides data services for network-attached client applications, is not a new kid on the block -- it's been around for more than two decades. In recent years, however, DDF has gained in importance for many organizations as client-server activity has grown as a percentage of the overall DB2 workload (in a blog entry posted a couple of years ago, I wrote about a way in which a DB2 monitor accounting report can be used to determine the relative size of a DDF workload in a DB2 system). At numerous sites, DDF-related activity is the fastest growing component of the overall DB2 for z/OS workload, and in more and more cases it is already the largest DB2 workload component on a mainframe system (a DBA recently told me that more than 95% of the SQL processing on his company's production DB2 for z/OS system is DDF-related).
As a DDF workload grows, it becomes more important that the associated execution environment be set up for optimal performance and throughput. A key aspect of this effort is the proper assignment of dispatching priorities (by way of a z/OS WLM policy) to DDF-related tasks in the system, and by "tasks" I'm referring both to the "main tasks" of address spaces (the DDF address space and, if applicable, stored procedure address spaces) and to tasks associated with application processes sending SQL requests to DB2 via DDF. Lots of organizations have done this in a way that I'd consider to be spot-on. That said, I've seen that some companies have their DDF-related dispatching priorities set up in a less-than-ideal way. In this blog entry I'll give you my recommendations on assigning priorities to DDF-related tasks in your system.
First, let's consider the DDF address space itself (it's also known as the DIST address space). Too often, I see that people have assigned a too-low priority to DDF. The fact of the matter is that the DDF address space should have the same priority as the other DB2 address spaces (those being the database services and system services address spaces, also known as DBM1 and MSTR, respectively). The DB2 DBM1 and MSTR address spaces are typically assigned to a service class with a name like STCHIGH or STCHI, and that is where the DIST address space should be, too (the IRLM address space should be assigned to the SYSSTC service class). Why is DDF often assigned to a service class with a priority below that of the service class used for DBM1 and MSTR? I believe that it has to do with two things: 1) a belief that SQL getting to DB2 via DDF will execute at the priority of the DDF address space, and 2) a misunderstanding of DDF CPU utilization in general. With regard to that first factor (DDF-related SQL executing at DDF's priority), this is something that was once true but stopped being true a LONG time ago. For years and years its been the case that SQL coming through DDF executes under tasks called enclave SRBs (more specifically, preemptible SRBs), and the priority of these tasks is determined according to the service class to which network-attached application processes have been mapped in the active WLM policy (more on this momentarily) -- NOT by the priority of the DDF address space.
As for the general misunderstanding of DDF CPU utilization on the part of some folks, that's a topic on which I blogged last year. There are people who see a high level of DDF CPU utilization in an environment with a large amount of DB2 client-server activity, and don't know what to make of that. Were they to do a little investigation using DB2 monitor data (as I described in the aforementioned blog entry), they'd see that only a very small portion of that DDF CPU time is associated with DDF "system" tasks (those that run at the priority of the DDF address space). The vast majority of DDF CPU time simply reflects the cost of executing the SQL statements that get to DB2 via DDF (just as the CPU cost of SQL statements issued by CICS-DB2 transactions is charged to the associated CICS region), and those statements, as noted in the preceding paragraph, execute at a priority dictated by the service class (or classes) to which network-attached application requesters have been mapped in the system's WLM policy. The DDF "system" tasks use very little CPU, but when those tasks need CPU time they need it RIGHT AWAY, and if they can't be dispatched in a very timely manner then the throughput of the DDF application workload could be negatively impacted in a busy system.
So, do the right thing and assign the DDF address space to the same high-priority service class that you use for the DB2 DBM1 and MSTR address spaces; and, while we're on the subject of address spaces, I'll tell you that your WLM-managed stored procedure address spaces (if you use external DB2 stored procedures) should also have the same priority as DBM1 and MSTR, and for the same reason that I make this recommendation for DDF: the "main tasks" of these stored procedure address spaces use very little CPU time, and they need to be very quickly dispatched when they have work to do if you want optimal stored procedure performance. A stored procedure that executes in one of these WLM-managed address spaces inherits the dispatching priority of the application task that called it -- it does NOT run at the priority of the stored procedure address space (a native SQL procedure, which runs in the DB2 DBM1 address space, also inherits the priority of the task of the application process that called it).
Now, about the DDF application tasks (versus the DDF "system" tasks): these, as I mentioned previously, are preemptible enclave SRBs in the DDF address space. We've established that the priority of these tasks is NOT tied to the priority of the DDF address space; rather, they are determined according to the service class (or classes) to which network-attached application processes are assigned in the system's WLM policy. But what if your WLM policy does no such mapping? What then? Well, in that case, your network-attached application processes will, by default, be mapped to a service class called SYSOTHER. That service class has a priority called "discretionary." That's a nice way of saying "bottom feeder." It is a LOW priority -- almost certainly NOT the one you want for your DDF transactions (unless you like providing DDF users with poor performance). At the very least, you should have a "catch-all" service class for DDF-connected applications that has at least a modestly high dispatching priority. Of course, if you have a large and varied DDF workload (some business intelligence queries, some high-volume transactions associated with a vendor-supplied ERP application, some processes related to a master data management application, etc.), you can -- and probably should -- map different parts of the DDF workload to different service classes. There are multiple attributes that you can use to tie various network-attached application processes to different service classes, including collection name, procedure name, and primary DB2 authorization ID; and, for a given service class you can use period aging to progressively lower a task's priority as its execution time increases (particularly useful for workloads characterized by long-running queries). In all cases, I recommend keeping the priority of DDF application tasks at least a few notches below the priority of the DB2 address spaces (DBM1, MSTR, DIST, and -- if applicable -- stored procedure address spaces). You don't want application code running at a priority higher than that of system code. Really useful information on setting up a WLM policy for a DB2 DDF workload can be found in section 3.3 ("Workload Manager setup") of the IBM redbook titled "DB2 9 for z/OS: Distributed Functions" (very much applicable to a DB2 10 system).
There you have it. Make sure that you understand how dispatching priorities are assigned to DDF system and application tasks, and make sure that your WLM policy is set up to help deliver the kind of DB2 data-serving performance that your client-server application users want.
As a DDF workload grows, it becomes more important that the associated execution environment be set up for optimal performance and throughput. A key aspect of this effort is the proper assignment of dispatching priorities (by way of a z/OS WLM policy) to DDF-related tasks in the system, and by "tasks" I'm referring both to the "main tasks" of address spaces (the DDF address space and, if applicable, stored procedure address spaces) and to tasks associated with application processes sending SQL requests to DB2 via DDF. Lots of organizations have done this in a way that I'd consider to be spot-on. That said, I've seen that some companies have their DDF-related dispatching priorities set up in a less-than-ideal way. In this blog entry I'll give you my recommendations on assigning priorities to DDF-related tasks in your system.
First, let's consider the DDF address space itself (it's also known as the DIST address space). Too often, I see that people have assigned a too-low priority to DDF. The fact of the matter is that the DDF address space should have the same priority as the other DB2 address spaces (those being the database services and system services address spaces, also known as DBM1 and MSTR, respectively). The DB2 DBM1 and MSTR address spaces are typically assigned to a service class with a name like STCHIGH or STCHI, and that is where the DIST address space should be, too (the IRLM address space should be assigned to the SYSSTC service class). Why is DDF often assigned to a service class with a priority below that of the service class used for DBM1 and MSTR? I believe that it has to do with two things: 1) a belief that SQL getting to DB2 via DDF will execute at the priority of the DDF address space, and 2) a misunderstanding of DDF CPU utilization in general. With regard to that first factor (DDF-related SQL executing at DDF's priority), this is something that was once true but stopped being true a LONG time ago. For years and years its been the case that SQL coming through DDF executes under tasks called enclave SRBs (more specifically, preemptible SRBs), and the priority of these tasks is determined according to the service class to which network-attached application processes have been mapped in the active WLM policy (more on this momentarily) -- NOT by the priority of the DDF address space.
As for the general misunderstanding of DDF CPU utilization on the part of some folks, that's a topic on which I blogged last year. There are people who see a high level of DDF CPU utilization in an environment with a large amount of DB2 client-server activity, and don't know what to make of that. Were they to do a little investigation using DB2 monitor data (as I described in the aforementioned blog entry), they'd see that only a very small portion of that DDF CPU time is associated with DDF "system" tasks (those that run at the priority of the DDF address space). The vast majority of DDF CPU time simply reflects the cost of executing the SQL statements that get to DB2 via DDF (just as the CPU cost of SQL statements issued by CICS-DB2 transactions is charged to the associated CICS region), and those statements, as noted in the preceding paragraph, execute at a priority dictated by the service class (or classes) to which network-attached application requesters have been mapped in the system's WLM policy. The DDF "system" tasks use very little CPU, but when those tasks need CPU time they need it RIGHT AWAY, and if they can't be dispatched in a very timely manner then the throughput of the DDF application workload could be negatively impacted in a busy system.
So, do the right thing and assign the DDF address space to the same high-priority service class that you use for the DB2 DBM1 and MSTR address spaces; and, while we're on the subject of address spaces, I'll tell you that your WLM-managed stored procedure address spaces (if you use external DB2 stored procedures) should also have the same priority as DBM1 and MSTR, and for the same reason that I make this recommendation for DDF: the "main tasks" of these stored procedure address spaces use very little CPU time, and they need to be very quickly dispatched when they have work to do if you want optimal stored procedure performance. A stored procedure that executes in one of these WLM-managed address spaces inherits the dispatching priority of the application task that called it -- it does NOT run at the priority of the stored procedure address space (a native SQL procedure, which runs in the DB2 DBM1 address space, also inherits the priority of the task of the application process that called it).
Now, about the DDF application tasks (versus the DDF "system" tasks): these, as I mentioned previously, are preemptible enclave SRBs in the DDF address space. We've established that the priority of these tasks is NOT tied to the priority of the DDF address space; rather, they are determined according to the service class (or classes) to which network-attached application processes are assigned in the system's WLM policy. But what if your WLM policy does no such mapping? What then? Well, in that case, your network-attached application processes will, by default, be mapped to a service class called SYSOTHER. That service class has a priority called "discretionary." That's a nice way of saying "bottom feeder." It is a LOW priority -- almost certainly NOT the one you want for your DDF transactions (unless you like providing DDF users with poor performance). At the very least, you should have a "catch-all" service class for DDF-connected applications that has at least a modestly high dispatching priority. Of course, if you have a large and varied DDF workload (some business intelligence queries, some high-volume transactions associated with a vendor-supplied ERP application, some processes related to a master data management application, etc.), you can -- and probably should -- map different parts of the DDF workload to different service classes. There are multiple attributes that you can use to tie various network-attached application processes to different service classes, including collection name, procedure name, and primary DB2 authorization ID; and, for a given service class you can use period aging to progressively lower a task's priority as its execution time increases (particularly useful for workloads characterized by long-running queries). In all cases, I recommend keeping the priority of DDF application tasks at least a few notches below the priority of the DB2 address spaces (DBM1, MSTR, DIST, and -- if applicable -- stored procedure address spaces). You don't want application code running at a priority higher than that of system code. Really useful information on setting up a WLM policy for a DB2 DDF workload can be found in section 3.3 ("Workload Manager setup") of the IBM redbook titled "DB2 9 for z/OS: Distributed Functions" (very much applicable to a DB2 10 system).
There you have it. Make sure that you understand how dispatching priorities are assigned to DDF system and application tasks, and make sure that your WLM policy is set up to help deliver the kind of DB2 data-serving performance that your client-server application users want.
Thursday, July 25, 2013
DB2 for z/OS: Clearing Up Some Matters Pertaining to LOB Inlining
Plenty of folks know that LOB inlining is one of the really great enhancements delivered with DB2 10 for z/OS (it's something about which I blogged last year). I've noticed lately, however, that a fair number of people have some misconceptions regarding two important aspects of LOB inlining, namely the relationship between inlining and LOB table spaces, and scenarios in which LOB inlining is and is not a good idea from an application performance perspective. In this entry I'll aim to clear up these misunderstandings.
LOB table spaces: You need them. Period (but DB2 can help)
Some DBAs have this idea that LOB inlining can eliminate the requirement that there be an auxiliary table (and associated table space and index) for every LOB column in a table definition (and that's actually one auxiliary table per LOB column per partition, if the base table space is partitioned). This line of thinking is understandable: If the largest (length-wise) value that will go into a LOB column is smaller than the inline length specified for that column, so that all of the column's values can be 100% inlined, there's no need for an auxiliary table for that column, right?
WRONG. You MUST have an auxiliary table (and associated LOB table space and index) if you are going to have a LOB column in a base table, even if the values in the LOB column will always be 100% inlined. If these objects do not exist, you will not be able to insert any data into the table because the definition of the table will be considered by DB2 to be incomplete. So, avoiding the need for an auxiliary table and a LOB table space and an index is NOT a reason to go with LOB inlining, because LOB inlining does nothing to change the requirement that these objects exist before the table with the LOB column can be used.
Now, creating one measly auxiliary table, and one LOB table space to hold that table, and one index on the auxiliary table (used to quickly access a LOB value associated with a particular base table row) is not exactly a tall order, but recall that you need one set of these objects per partition and per column if the base table is partitioned and has multiple LOB columns. Do the math, and the DDL work can start to look kind of intimidating. What if a base table has 1000 partitions and two LOB columns? Ready to create 2000 auxiliary tables, and the same number of LOB table spaces and indexes?
Before you get all wound up about such a prospect, consider that DB2 can automatically create required LOB-related objects for you when you create a table with one or more LOB columns. DB2 will do that if EITHER of the following is true:
Performance: When LOB inlining helps, and when it doesn't
As I see it, the primary ways in which LOB inlining delivers benefits in terms of application performance and resource-efficiency are as follows:
Clearly, LOB inlining can be used very advantageously in some cases. In other cases, LOB inlining could negatively impact application performance. Here are some potential disadvantages of LOB inlining:
Here's the bottom line: If LOB data will be stored in a table but only rarely retrieved, inlining probably isn't a good idea unless it is very important to improve INSERT (or LOAD) performance (and that won't happen unless most LOB values can be 100% inlined in the base table). If you go with inlining for this reason, consider enlarging the buffer pool to which the base table space is assigned so the buffer pool hit ratio won't be negatively impacted. LOB inlining can have a very positive impact on the performance of queries that retrieve LOB data, so if that is important to you then inlining can be a very good move when most of the LOB values can be 100% inlined in the base table. Again, consider enlarging the base table space's buffer pool so that queries that do not retrieve LOB data won't be negatively impacted by a reduced buffer pool hit ratio.
DB2 10 is by far the best release of DB2 in terms of LOB data management capabilities, and LOB inlining is an important part of that story. The important thing to keep in mind is that LOB inlining is not a universally applicable DB2 feature. Figure out first if inlining makes sense for your particular situation.
LOB table spaces: You need them. Period (but DB2 can help)
Some DBAs have this idea that LOB inlining can eliminate the requirement that there be an auxiliary table (and associated table space and index) for every LOB column in a table definition (and that's actually one auxiliary table per LOB column per partition, if the base table space is partitioned). This line of thinking is understandable: If the largest (length-wise) value that will go into a LOB column is smaller than the inline length specified for that column, so that all of the column's values can be 100% inlined, there's no need for an auxiliary table for that column, right?
WRONG. You MUST have an auxiliary table (and associated LOB table space and index) if you are going to have a LOB column in a base table, even if the values in the LOB column will always be 100% inlined. If these objects do not exist, you will not be able to insert any data into the table because the definition of the table will be considered by DB2 to be incomplete. So, avoiding the need for an auxiliary table and a LOB table space and an index is NOT a reason to go with LOB inlining, because LOB inlining does nothing to change the requirement that these objects exist before the table with the LOB column can be used.
Now, creating one measly auxiliary table, and one LOB table space to hold that table, and one index on the auxiliary table (used to quickly access a LOB value associated with a particular base table row) is not exactly a tall order, but recall that you need one set of these objects per partition and per column if the base table is partitioned and has multiple LOB columns. Do the math, and the DDL work can start to look kind of intimidating. What if a base table has 1000 partitions and two LOB columns? Ready to create 2000 auxiliary tables, and the same number of LOB table spaces and indexes?
Before you get all wound up about such a prospect, consider that DB2 can automatically create required LOB-related objects for you when you create a table with one or more LOB columns. DB2 will do that if EITHER of the following is true:
- The CREATE TABLE statement for the base table (the table with the LOB column(s)) does NOT include an "in database-name.table-space-name" clause. In that case, DB2 will implicitly create the database for the base table space, the base table space itself, and all other objects needed to make the base table usable (e.g., a unique index on the table's primary key if the CREATE TABLE statement designated a primary key, and all objects needed for LOB data if the CREATE TABLE statement included one or more LOB columns).
- The CREATE TABLE statement for the base table DOES include an "in database-name.table-space-name" clause, and the value of the DB2 special register CURRENT RULES is 'STD' at the time of the execution of the CREATE TABLE statement.
Performance: When LOB inlining helps, and when it doesn't
As I see it, the primary ways in which LOB inlining delivers benefits in terms of application performance and resource-efficiency are as follows:
- Disk space savings, if a high percentage of a table's LOB values can be 100% inlined in the base table. In such a situation, the disk space requirement for LOB data is reduced in two ways: 1) Compression. Data in a LOB table space cannot be compressed by DB2; however, inlined LOB data values will be compressed by DB2, along with non-LOB data in the base table space. 2) More efficient use of data page space. In a LOB table space, one particular page can hold data associated with one LOB value. If, for example, a LOB table space has 8 KB-sized pages, and a particular LOB value is 9 KB in length, the first 8 KB of the LOB value will be in one page and the last 1 KB will be in a second page in the LOB table space. The rest of that second page in the LOB table space (7 KB of space) will remain empty because it cannot be used to hold data for any other LOB value. In a base table space, of course, there is no such rule, so inlined LOB data can lead to more efficient use of space in data pages.
- Improved performance of INSERT and SELECT operations (for SELECTs retrieving LOB data), when most LOB values can be 100% inlined in the base table. The performance gains here can be quite significant versus the non-inlining case.
- Ability to create an index on an expression on the inlined portion of a LOB column. Such an index would be created using an expression based on the SUBSTR function. This could be very useful if, for example, you store a type of document as a CLOB and a value of interest (maybe a department number) always appears in characters 10 through 14 of the document. You could build an index on a SUBSTR expression on the inlined portion of the LOB, and therefore be able to very quickly zero in on rows containing documents pertaining to department 'AB123' (I posted an entry about DB2's index-on-expression capability -- introduced with DB2 9 for z/OS -- to the blog I maintained while I was working as an independent DB2 consultant prior to re-joining IBM).
Clearly, LOB inlining can be used very advantageously in some cases. In other cases, LOB inlining could negatively impact application performance. Here are some potential disadvantages of LOB inlining:
- Performance degradation for INSERT and SELECT operations (for SELECTs retrieving LOB data) when most LOB values cannot be 100% inlined in the base table. Performance would be negatively impacted because DB2 would have to go to both the base table and the auxiliary table for most inserts and retrievals of LOB data.
- Performance degradation for SELECTs that DO NOT retrieve LOB data. When you inline LOB data, you make the base table rows longer (sometimes much longer). As a result, you'll have fewer base table rows per page, and because of that you'll get a lower buffer pool hit ratio for the base table. That means more disk I/Os, and that will impact elapsed time.
Here's the bottom line: If LOB data will be stored in a table but only rarely retrieved, inlining probably isn't a good idea unless it is very important to improve INSERT (or LOAD) performance (and that won't happen unless most LOB values can be 100% inlined in the base table). If you go with inlining for this reason, consider enlarging the buffer pool to which the base table space is assigned so the buffer pool hit ratio won't be negatively impacted. LOB inlining can have a very positive impact on the performance of queries that retrieve LOB data, so if that is important to you then inlining can be a very good move when most of the LOB values can be 100% inlined in the base table. Again, consider enlarging the base table space's buffer pool so that queries that do not retrieve LOB data won't be negatively impacted by a reduced buffer pool hit ratio.
DB2 10 is by far the best release of DB2 in terms of LOB data management capabilities, and LOB inlining is an important part of that story. The important thing to keep in mind is that LOB inlining is not a universally applicable DB2 feature. Figure out first if inlining makes sense for your particular situation.
Sunday, July 21, 2013
DB2 for z/OS Data Sharing: The Evolution of a GBP Sizing Formula
Recently I had an e-mail exchange with my friend, colleague, and fellow DB2 blogger Willie Favero, on the subject of DB2 for z/OS group buffer pool sizing. One of my messages was a little wordy (imagine that), and Willie responded with, "[This] reads like it could be your next blog post." And so it is. In this entry I'll share with you what I think is an interesting story, along with some information that I hope will help you with group buffer pool monitoring and tuning.
For those of you who aren't familiar with the term "group buffer pool," a bit of introduction: DB2 data sharing is technology that enables multiple DB2 for z/OS subsystems to share concurrent read/write access to a single database. The DB2 subsystems are members of a data sharing group that runs on a cluster of z/OS systems called a Parallel Sysplex. Within the Parallel Sysplex are resources known as coupling facilities. These are essentially shared memory devices. Within a coupling facility one would find several structures, and among these would be the DB2 group buffer pools, or GBPs. Basically, group buffer pools are used for two things:
OK, now for the story: back in the mid-1990s, when data sharing was introduced with DB2 Version 4 for z/OS, an organization that was one of the early adopters of the technology wanted to know how large a GBP ought to be. I was working at the time in IBM's DB2 for z/OS national technical support group, and I fielded this question and thought that "as large as possible" was an unacceptably imprecise answer; so, I set about trying to determine the right size for a GBP in a quantitative way. I focused on GBPs with 4K-sized data entries because buffer pools that hold 4K-sized pages were (and to a somewhat lesser extent still are) dominant versus buffer pools that hold 8K, 16K, or 32K-sized pages. Further, I assumed that the default ratio of five GBP directory entries for every one data entry would be in effect. I also knew that a GBP directory entry occupied about 200 bytes of space (that was then -- I'll get to the current size of a GBP directory entry in a bit).
The last piece of the puzzle was a GBP sizing objective. A GBP sized right would be beneficial because... what? That "what," I decided, should be 1) avoidance of GBP write failures due to lack of storage (something that can occur if a GBP is so small that casting out of changed pages to disk -- required to make GBP data entries stealable -- cannot keep up with the volume of writes of changed pages to the GBP) and 2) avoidance of GBP directory entry reclaims (if a page must be registered in a GBP but all of that GBP's directory entries are in use, a directory entry has to be reclaimed, and when that happens the copies of the page cached in local buffer pools of member DB2 subsystems have to be preemptively marked invalid). Knowing that GBP write failures due to lack of storage are most likely to occur for a way-undersized GBP, I decided to concentrate on determining a GBP size that would virtually eliminate the possibility of directory entry reclaims, my thinking being that a GBP so sized would also be large enough to avoid GBP write failures due to lack of storage (and that has proven to be the case, in my experience).
How, then, to avoid GBP directory entry reclaims? I realized that the key to accomplishing that goal was having at least as many GBP directory entries as there were "slots" into which different pages of table spaces and indexes could be cached. These slots would be the buffers of local pools, PLUS the data entries in the corresponding GBP. If, for example, a data sharing group had two member DB2 subsystems, and if BP1 on each member had 30,000 buffers, you'd have 60,000 page slots right there. 60,000 directory entries in GBP1 would cover that base, right? Yeah, but with the standard 5:1 ratio of GBP directory entries to data entries, those 60,000 directory entries would bring along 12,000 data entries in GBP1. That's 12,000 additional slots in which different pages could be cached, so you'd need 12,000 more directory entries to eliminate the possibility of directory entry reclaims. Well, those 12,000 GBP directory entries would bring along another 2400 GBP data entries (with the aforementioned 5:1 ratio in effect). Providing 2400 more directory entries to account for those page slots would give you 480 more GBP data entries, so you'd need 480 additional directory entries, and so on. I came up with a formula into which I plugged numerical values (200 bytes for a directory entry, 4K for a data entry, 5 directory entries for every 1 data entry), and saw that I had a converging sequence. That sequence converged to approximately 0.3125, meaning that combining the size (in megabytes) of local BPn buffer pools and multiplying that figure by 0.3125 would give you a size for GBPn that would virtually ensure zeroes for directory entry reclaims and for GBP write failures due to lack of storage (something that could be verified using the output of the command -DISPLAY GROUPBUFFERPOOL(GBPn) GDETAIL(*)).
Nice, but I was concerned that 0.3125 was a number that people would not be able to readily bring to mind, and I really wanted a GBP sizing formula that a person could carry around in his or her head; so, I nudged 0.3125 up to 0.33, and thus arrived at the GBP sizing rule of thumb that came to be known as "add 'em up and divide by 3" (example: given a 4-way DB2 data sharing group, with a BP3 sized at 30,000 buffers on each member, and the default 5:1 ratio of directory entries to data entries in the GBP, a good initial size for GBP3 would be one-third of the combined size of the BP3 pools, or (30,000 X 4KB X 4) / 3, which is 160 MB). That formula was widely and successfully used at DB2 data sharing sites, and it continued to work well even as the size of a GBP directory entry increased (with newer versions of coupling facility control code). Now that the size of a directory entry is about 400 bytes, "add 'em up and divide by 3" has become "add 'em up and multiply by 0.375." So, given that prior example of a 4-way data sharing group with BP3 sized at 30,000 buffers on each member, a GBP3 size that would virtually eliminate the possibility of directory entry reclaims (because it would give you, with the default 5:1 directory-to-data entry ratio, a number of directory entries about equal to the number of "slots" into which different pages could be cached in the local BP3 buffer pools and in GBP3) would be:
(30,000 X 4KB X 4) X 0.375 = 180 MB
Now, I've mentioned multiple times that the formula I derived was based on two assumptions: a local buffer pool with 4K-sized buffers, and a GBP with five directory entries for every one data entry. What about buffer pools with larger buffer sizes (e.g., 8K or 16K)? What about GBP directory-to-data-entry ratios (which can be changed via the -ALTER GROUPBUFFERPOOL command) other than the default 5:1? Regardless of the size of the buffers and the directory-to-data-entry ratio in effect for a GBP, the basic goals remain the same: avoid directory entry reclaims and avoid write failures due to lack of storage. Because accomplishing the first of these goals is likely to result in achievement of the second objective as well, you focus on sizing a GBP to eliminate directory entry reclaims. Directory entries won't be reclaimed if there is at least one directory entry for every "slot" in which a different page could be cached (i.e., you need to have a number of directory entries that is at least as great as the number of buffers in the local buffer pools PLUS the number of data entries in the GBP).
To see how this GBP sizing approach can be used, suppose that you have a 2-way DB2 data sharing group, with a BP16K1 that has 10,000 buffers on each member. Suppose further that you have reason to want to use a 7:1 directory-to-data-entry ratio for GBP16K1 instead of the default 5:1 ratio. In that case, how should GBP16K1 be sized so as to prevent directory entry reclaims? Well, to begin with, you know that you'll need a directory entry in GBP16K1 for every local BP16K1 buffer. That's 20,000 directory entries. Given the 7:1 directory-to-data-entry ratio in effect for GBP16K1, along with the 20,000 GBP directory entries you'll have 2858 data entries (20,000 divided by 7, and rounded up to the nearest integer value). To cover those additional 2858 page "slots," you'll need another 2858 directory entries in the GBP. Again with the 7:1 directory-to-data-entry ratio in mind, the 2858 additional directory entries will come with another 409 data entries (2858 divided by 7, rounded up). You'll need another 409 directory entries to cover those 409 data entries, but that means an additional 59 data entries. The 59 directory entries needed to cover those data entries will bring along 9 more data entries, and the 9 directory entries needed to cover those data entries will mean 2 more data entries, for which you'll need 2 more directory entries. Add one more data entry to go along with those last 2 directory entries (2 divided by 7, rounded up), and you've taken the sequence far enough.
Now, just add up the directory entries you got with each iteration of the sequence, starting with the initial 20,000:
20,000 + 2858 + 409 + 59 + 9 + 2 = 23,337
Divide that figure by 7 (and round up to the nearest integer) to get a number of data entries for the GBP:
23,337 / 7 = 3333.86, or 3334 when rounded up
Now you can size GBP16K1 by multiplying the number of directory entries by 400 bytes and the number of data entries by 16KB:
(23,337 X 400 bytes) + (3334 X 16KB) = 62.7 MB
I'd round that up to 63 MB for good measure. And there you have it: a GBP16K1 sized at 63 MB (given our use of a 7:1 directory-to-data-entry ratio for this GBP) should result in your seeing zero directory entry reclaims for the GBP (and zero write failures due to lack of storage, to boot). As mentioned previously, you can check directory entry and write failure values using the output of the DB2 command (using GBP16K1 in this case) -DISPLAY GROUPBUFFERPOOL(GBP16K1) GDETAIL(*). Dealing with a different buffer size? A different GBP directory-to-data-entry ratio? Doesn't matter. You can use the approach laid out above for any buffer size and any GBP directory-to-data-entry ratio.
I hope that this information will be useful to you in right-sizing your group buffer pools, if you're running DB2 for z/OS in data sharing mode (if you're not, consider the use of this technology at your site -- you can't beat it for availability and scalability).
For those of you who aren't familiar with the term "group buffer pool," a bit of introduction: DB2 data sharing is technology that enables multiple DB2 for z/OS subsystems to share concurrent read/write access to a single database. The DB2 subsystems are members of a data sharing group that runs on a cluster of z/OS systems called a Parallel Sysplex. Within the Parallel Sysplex are resources known as coupling facilities. These are essentially shared memory devices. Within a coupling facility one would find several structures, and among these would be the DB2 group buffer pools, or GBPs. Basically, group buffer pools are used for two things:
- Page registration -- When a DB2 member subsystem reads into a local buffer pool a page belonging to a GBP-dependent data set (i.e., a table space or index -- or a partition of a partitioned table space or index -- in which there is inter-DB2 read/write interest), it registers that page in a directory entry of the corresponding GBP (so, a page read into buffer pool BP1 would be registered in GBP1). DB2 member X registers a locally cached page of a GBP-dependent data set so that it can be informed if that page is changed by a process running on DB2 member Y. Such a change effected on another member of the data sharing group would cause the copy of the page cached locally in a buffer pool of DB2 member X to be marked invalid. On re-referencing the page, DB2 member X would see that its copy of the page is invalid, and would then look for the current version of the page in the associated GBP (and would retrieve that current version from disk if it were not found in the GBP).
- Caching of changed pages -- If a process running on DB2 member Y of the data sharing group changes a page belonging to a GBP-dependent data set, that member will write the changed page to the associated GBP in a coupling facility LPAR (usually at commit time, but sometimes before a commit). The changed page will remain in the GBP -- from whence it can be retrieved by a member DB2 subsystem in a few microseconds -- for some time following the update. Eventually the page will be overwritten by another changed page, but before that happens the GBP data entry occupied by the page will be made stealable via a process called castout, through which changed pages written to a GBP are externalized to disk.
OK, now for the story: back in the mid-1990s, when data sharing was introduced with DB2 Version 4 for z/OS, an organization that was one of the early adopters of the technology wanted to know how large a GBP ought to be. I was working at the time in IBM's DB2 for z/OS national technical support group, and I fielded this question and thought that "as large as possible" was an unacceptably imprecise answer; so, I set about trying to determine the right size for a GBP in a quantitative way. I focused on GBPs with 4K-sized data entries because buffer pools that hold 4K-sized pages were (and to a somewhat lesser extent still are) dominant versus buffer pools that hold 8K, 16K, or 32K-sized pages. Further, I assumed that the default ratio of five GBP directory entries for every one data entry would be in effect. I also knew that a GBP directory entry occupied about 200 bytes of space (that was then -- I'll get to the current size of a GBP directory entry in a bit).
The last piece of the puzzle was a GBP sizing objective. A GBP sized right would be beneficial because... what? That "what," I decided, should be 1) avoidance of GBP write failures due to lack of storage (something that can occur if a GBP is so small that casting out of changed pages to disk -- required to make GBP data entries stealable -- cannot keep up with the volume of writes of changed pages to the GBP) and 2) avoidance of GBP directory entry reclaims (if a page must be registered in a GBP but all of that GBP's directory entries are in use, a directory entry has to be reclaimed, and when that happens the copies of the page cached in local buffer pools of member DB2 subsystems have to be preemptively marked invalid). Knowing that GBP write failures due to lack of storage are most likely to occur for a way-undersized GBP, I decided to concentrate on determining a GBP size that would virtually eliminate the possibility of directory entry reclaims, my thinking being that a GBP so sized would also be large enough to avoid GBP write failures due to lack of storage (and that has proven to be the case, in my experience).
How, then, to avoid GBP directory entry reclaims? I realized that the key to accomplishing that goal was having at least as many GBP directory entries as there were "slots" into which different pages of table spaces and indexes could be cached. These slots would be the buffers of local pools, PLUS the data entries in the corresponding GBP. If, for example, a data sharing group had two member DB2 subsystems, and if BP1 on each member had 30,000 buffers, you'd have 60,000 page slots right there. 60,000 directory entries in GBP1 would cover that base, right? Yeah, but with the standard 5:1 ratio of GBP directory entries to data entries, those 60,000 directory entries would bring along 12,000 data entries in GBP1. That's 12,000 additional slots in which different pages could be cached, so you'd need 12,000 more directory entries to eliminate the possibility of directory entry reclaims. Well, those 12,000 GBP directory entries would bring along another 2400 GBP data entries (with the aforementioned 5:1 ratio in effect). Providing 2400 more directory entries to account for those page slots would give you 480 more GBP data entries, so you'd need 480 additional directory entries, and so on. I came up with a formula into which I plugged numerical values (200 bytes for a directory entry, 4K for a data entry, 5 directory entries for every 1 data entry), and saw that I had a converging sequence. That sequence converged to approximately 0.3125, meaning that combining the size (in megabytes) of local BPn buffer pools and multiplying that figure by 0.3125 would give you a size for GBPn that would virtually ensure zeroes for directory entry reclaims and for GBP write failures due to lack of storage (something that could be verified using the output of the command -DISPLAY GROUPBUFFERPOOL(GBPn) GDETAIL(*)).
Nice, but I was concerned that 0.3125 was a number that people would not be able to readily bring to mind, and I really wanted a GBP sizing formula that a person could carry around in his or her head; so, I nudged 0.3125 up to 0.33, and thus arrived at the GBP sizing rule of thumb that came to be known as "add 'em up and divide by 3" (example: given a 4-way DB2 data sharing group, with a BP3 sized at 30,000 buffers on each member, and the default 5:1 ratio of directory entries to data entries in the GBP, a good initial size for GBP3 would be one-third of the combined size of the BP3 pools, or (30,000 X 4KB X 4) / 3, which is 160 MB). That formula was widely and successfully used at DB2 data sharing sites, and it continued to work well even as the size of a GBP directory entry increased (with newer versions of coupling facility control code). Now that the size of a directory entry is about 400 bytes, "add 'em up and divide by 3" has become "add 'em up and multiply by 0.375." So, given that prior example of a 4-way data sharing group with BP3 sized at 30,000 buffers on each member, a GBP3 size that would virtually eliminate the possibility of directory entry reclaims (because it would give you, with the default 5:1 directory-to-data entry ratio, a number of directory entries about equal to the number of "slots" into which different pages could be cached in the local BP3 buffer pools and in GBP3) would be:
(30,000 X 4KB X 4) X 0.375 = 180 MB
Now, I've mentioned multiple times that the formula I derived was based on two assumptions: a local buffer pool with 4K-sized buffers, and a GBP with five directory entries for every one data entry. What about buffer pools with larger buffer sizes (e.g., 8K or 16K)? What about GBP directory-to-data-entry ratios (which can be changed via the -ALTER GROUPBUFFERPOOL command) other than the default 5:1? Regardless of the size of the buffers and the directory-to-data-entry ratio in effect for a GBP, the basic goals remain the same: avoid directory entry reclaims and avoid write failures due to lack of storage. Because accomplishing the first of these goals is likely to result in achievement of the second objective as well, you focus on sizing a GBP to eliminate directory entry reclaims. Directory entries won't be reclaimed if there is at least one directory entry for every "slot" in which a different page could be cached (i.e., you need to have a number of directory entries that is at least as great as the number of buffers in the local buffer pools PLUS the number of data entries in the GBP).
To see how this GBP sizing approach can be used, suppose that you have a 2-way DB2 data sharing group, with a BP16K1 that has 10,000 buffers on each member. Suppose further that you have reason to want to use a 7:1 directory-to-data-entry ratio for GBP16K1 instead of the default 5:1 ratio. In that case, how should GBP16K1 be sized so as to prevent directory entry reclaims? Well, to begin with, you know that you'll need a directory entry in GBP16K1 for every local BP16K1 buffer. That's 20,000 directory entries. Given the 7:1 directory-to-data-entry ratio in effect for GBP16K1, along with the 20,000 GBP directory entries you'll have 2858 data entries (20,000 divided by 7, and rounded up to the nearest integer value). To cover those additional 2858 page "slots," you'll need another 2858 directory entries in the GBP. Again with the 7:1 directory-to-data-entry ratio in mind, the 2858 additional directory entries will come with another 409 data entries (2858 divided by 7, rounded up). You'll need another 409 directory entries to cover those 409 data entries, but that means an additional 59 data entries. The 59 directory entries needed to cover those data entries will bring along 9 more data entries, and the 9 directory entries needed to cover those data entries will mean 2 more data entries, for which you'll need 2 more directory entries. Add one more data entry to go along with those last 2 directory entries (2 divided by 7, rounded up), and you've taken the sequence far enough.
Now, just add up the directory entries you got with each iteration of the sequence, starting with the initial 20,000:
20,000 + 2858 + 409 + 59 + 9 + 2 = 23,337
Divide that figure by 7 (and round up to the nearest integer) to get a number of data entries for the GBP:
23,337 / 7 = 3333.86, or 3334 when rounded up
Now you can size GBP16K1 by multiplying the number of directory entries by 400 bytes and the number of data entries by 16KB:
(23,337 X 400 bytes) + (3334 X 16KB) = 62.7 MB
I'd round that up to 63 MB for good measure. And there you have it: a GBP16K1 sized at 63 MB (given our use of a 7:1 directory-to-data-entry ratio for this GBP) should result in your seeing zero directory entry reclaims for the GBP (and zero write failures due to lack of storage, to boot). As mentioned previously, you can check directory entry and write failure values using the output of the DB2 command (using GBP16K1 in this case) -DISPLAY GROUPBUFFERPOOL(GBP16K1) GDETAIL(*). Dealing with a different buffer size? A different GBP directory-to-data-entry ratio? Doesn't matter. You can use the approach laid out above for any buffer size and any GBP directory-to-data-entry ratio.
I hope that this information will be useful to you in right-sizing your group buffer pools, if you're running DB2 for z/OS in data sharing mode (if you're not, consider the use of this technology at your site -- you can't beat it for availability and scalability).
Monday, June 24, 2013
DB2 for z/OS Business Analytics: Moving the Queries to the Data
In the world of business, data has become the coin of the realm. Organizations that effectively leverage their informational assets tend to outperform peers and rivals. Given the value of data in today's marketplace, it's no wonder that companies are keenly focused on data security and availability, and on data delivery mechanisms that will keep pace with business growth. For 30 years, firms evaluating data-serving platforms in light of the aforementioned criteria -- security, availability, and scalability -- have chosen DB2 for z/OS and IBM System z servers. A big percentage of the data generated, and otherwise owned, by large enterprises around the world is stored in DB2 for z/OS databases.
Now, having data is one thing, but to get maximum value from this resource, you have to use it -- to identify new market opportunities, to optimize product mix, to manage risk, to better understand customer needs and buying behaviors. Thus the importance of business analytics, which can be thought of as the means whereby information is made to yield insight. For quite a few years, the conventional wisdom held that business analytics was best done on distributed systems platforms; so, organizations wishing to analyze, for decision support purposes, data stored in DB2 for z/OS databases, routinely copied that data to databases running under Linux, UNIX, or Windows on x86- or RISC-based servers. The copied data would then be accessed by various business intelligence tools.
The argument for performing business analytics in this fashion was largely cost-based. People acknowledged that mainframe systems were great as data-of-record repositories -- the super-sized, super-reliable data vaults on which run-the-business applications were built -- but they assumed that System z was overkill, cost-wise, as a platform for query and reporting applications. Interestingly, as LInux-, UNIX- and Windows-based analytics systems grew and proliferated within enterprises, a funny thing happened: the "inexpensive" alternative started to become notably more expensive, as costs mounted in several areas:
Faced with the escalating costs of a "move the data to the query systems" approach, a growing number of companies have opted to go a different route: they are moving the queries to the data. In other words, enterprise data is analyzed where it lives: on the mainframe.
"Moving the queries to the data" is a beautifully simple concept that is grounded in advanced technology and open standards. It starts with the fact that the leading query and reporting tools -- from IBM and from other vendors -- absolutely can be used with DB2 for z/OS. If your preferred business analytics software utilizes JDBC or ODBC to access relational databases (very often the case), you're in good shape, as IBM provides DB2 drivers for those interfaces (and others, as well).
If you query mainframe data on the mainframe (versus copying it to some other platform for analytics purposes), you might still decide to extract data from a source operational database into a query and reporting database that also resides on a System z server. Why? There are several potential reasons:
Whatever the reason for copying operational DB2 for z/OS data into query and reporting tables, that task is made simpler and more efficient when the source and target tables are both managed by DB2 for z/OS.
In some cases, organizations point query and reporting tools directly at DB2 for z/OS operational tables -- in other words, data copying is not part of the picture. Is that really feasible? Yes, it is, thanks largely to the highly advanced workload management capabilities of z/OS, an operating system that for decades has handled highly differentiated workloads (e.g., transactional and batch) concurrently in a single system image. Having analytics users query the same tables as operational applications can be a particularly attractive option when DB2 for z/OS is running in data sharing mode on a Parallel Sysplex cluster configuration. In that setting, a location alias could be used to restrict business analytics users to (for example) two members of a six-member data sharing group, with the operational applications running on the other four members of the group (also by way of a location alias for DRDA requesters, and via the new subgroup attach feature of DB2 10 for batch jobs, and through the placement of CICS regions for a CICS-DB2 workload). With this type of set-up, the operational and analytics applications could access the same DB2 tables and there would be no contention for memory or CPU resources (assuming that the DB2 members used for the query and reporting workload were running on LPARs in the Parallel Sysplex separate from the LPARs on which the DB2 members handling the operational applications run).
Here's another interesting configuration option for mainframe business analytics: when BI queries involve particularly large scans of data and/or are particularly complex, dramatic reductions in query elapsed times can be achieved through the use of an IBM DB2 Analytics Accelerator. These servers extend the capabilities of a DB2 for z/OS system running a business intelligence workload, delivering "shockingly fast" performance for formerly long-running queries (borrowing a phrase used by an IT manager friend of mine at a site that recently started using DB2 Analytics Accelerator technology), while requiring nothing in the way of changes on the client side of the BI application (users continue to direct their queries to the same DB2 location, and the DB2 optimizer determines whether a given query should be processed locally (i.e., on the DB2 for z/OS front-end) or routed to the Analytics Accelerator. This is way cool technology about which I'll write more soon in a subsequent blog entry.
Topping it all off are the functionality enhancements, delivered in each succeeding release of DB2 for z/OS, that are especially useful in a business analytics context. Just in the last couple of releases (Versions 9 and 10), we've gotten OLAP specifications such as rank, dense rank, and moving aggregates; index-on-expression, which can make predicates with column functions indexable; temporal data support, which enables one to add a time dimension to data in a table; the access path repository, a set of new DB2 10 catalog tables including SYSQUERYOPTS, that enables, among other things, DB2 server-side control of parallelization for dynamic queries at the statement level; a slew of new built-in functions (among them LPAD, MONTHS_BETWEEN, OVERLAY, and TRIM); new data types such as BIGINT, DECFLOAT, and XML; and real-time scoring, which allows for evaluation of newly inserted or updated database values against an SPSS predictive model as part of a transaction's flow, with virtually no impact on transaction elapsed time. And, there's more of this to come with DB2 11 for z/OS, now in beta test.
Is DB2 for z/OS the source DBMS for much of your company's "of record" data? Are you copying this data out to various other platforms for query and reporting purposes? Have you stopped to consider whether moving the queries to the data -- i.e., running your business analytics queries on the source DB2 for z/OS system -- might be a less complicated, more efficient, more secure, and more cost-effective approach? Leveraging DB2 for z/OS and System z as a platform for business analytics has been a winning strategy for many organizations. It might be for yours, as well.
Now, having data is one thing, but to get maximum value from this resource, you have to use it -- to identify new market opportunities, to optimize product mix, to manage risk, to better understand customer needs and buying behaviors. Thus the importance of business analytics, which can be thought of as the means whereby information is made to yield insight. For quite a few years, the conventional wisdom held that business analytics was best done on distributed systems platforms; so, organizations wishing to analyze, for decision support purposes, data stored in DB2 for z/OS databases, routinely copied that data to databases running under Linux, UNIX, or Windows on x86- or RISC-based servers. The copied data would then be accessed by various business intelligence tools.
The argument for performing business analytics in this fashion was largely cost-based. People acknowledged that mainframe systems were great as data-of-record repositories -- the super-sized, super-reliable data vaults on which run-the-business applications were built -- but they assumed that System z was overkill, cost-wise, as a platform for query and reporting applications. Interestingly, as LInux-, UNIX- and Windows-based analytics systems grew and proliferated within enterprises, a funny thing happened: the "inexpensive" alternative started to become notably more expensive, as costs mounted in several areas:
- Source data extract and change replication. Aside from the costs associated with extract and load processing to build an initial query/reporting database on a distributed systems platform, there is the cost of keeping the copied data up to date, as demanded by BI application users. At some sites, growth in the number of query/reporting databases resulted in a change data replication infrastructure that looked something like a spider web when viewed diagrammatically. This infrastructure could end up being quite resource intensive from a support perspective.
- Environmental factors. Floor space, electricity, and data center cooling costs mount as x86- and RISC-based analytics servers multiply.
- Data security and integrity. As more and more copies of data sourced from a DB2 for z/OS database pop up in the enterprise, control and management of the data becomes increasingly problematic. Data copies can become out-of-sync with respect to the source, and data security safeguards present on the mainframe system may be lacking on some query and reporting platforms.
Faced with the escalating costs of a "move the data to the query systems" approach, a growing number of companies have opted to go a different route: they are moving the queries to the data. In other words, enterprise data is analyzed where it lives: on the mainframe.
"Moving the queries to the data" is a beautifully simple concept that is grounded in advanced technology and open standards. It starts with the fact that the leading query and reporting tools -- from IBM and from other vendors -- absolutely can be used with DB2 for z/OS. If your preferred business analytics software utilizes JDBC or ODBC to access relational databases (very often the case), you're in good shape, as IBM provides DB2 drivers for those interfaces (and others, as well).
If you query mainframe data on the mainframe (versus copying it to some other platform for analytics purposes), you might still decide to extract data from a source operational database into a query and reporting database that also resides on a System z server. Why? There are several potential reasons:
- "Data stability" during daytime query hours. Sometimes, users want to get the same result for a given query whether it is executed at 9 AM or at 4 PM on a given day -- in other words, they don't want the data "changing underneath them." In such cases, updates of data in the query and reporting database are processed on a nightly basis.
- Database design differences. Whereas the data in the source operational tables is likely organized in traditional third normal form, data to be queried via business intelligence tools might need to be arranged in a star schema manner.
- Data transformation requirements. Data values might need to be transformed in some ways before being loaded or inserted into query and reporting tables.
- Workload isolation. In particular in a non-data sharing DB2 for z/OS environment, directing analytics users to a set of tables separate from the production operational tables might be done to increase the degree of physical separation between the business intelligence workload and the "run the business" transactional and batch workload. Separate tables could be assigned to separate buffer pools to reduce contention for server memory resources. They could be in a different DB2 subsystem on another z/OS LPAR, perhaps on the same mainframe server, to add CPU isolation to the aforementioned memory isolation which can be achieved by using different DB2 buffer pools (different processors on a System z server can be dedicated to different z/OS LPARs on the server).
Whatever the reason for copying operational DB2 for z/OS data into query and reporting tables, that task is made simpler and more efficient when the source and target tables are both managed by DB2 for z/OS.
In some cases, organizations point query and reporting tools directly at DB2 for z/OS operational tables -- in other words, data copying is not part of the picture. Is that really feasible? Yes, it is, thanks largely to the highly advanced workload management capabilities of z/OS, an operating system that for decades has handled highly differentiated workloads (e.g., transactional and batch) concurrently in a single system image. Having analytics users query the same tables as operational applications can be a particularly attractive option when DB2 for z/OS is running in data sharing mode on a Parallel Sysplex cluster configuration. In that setting, a location alias could be used to restrict business analytics users to (for example) two members of a six-member data sharing group, with the operational applications running on the other four members of the group (also by way of a location alias for DRDA requesters, and via the new subgroup attach feature of DB2 10 for batch jobs, and through the placement of CICS regions for a CICS-DB2 workload). With this type of set-up, the operational and analytics applications could access the same DB2 tables and there would be no contention for memory or CPU resources (assuming that the DB2 members used for the query and reporting workload were running on LPARs in the Parallel Sysplex separate from the LPARs on which the DB2 members handling the operational applications run).
Here's another interesting configuration option for mainframe business analytics: when BI queries involve particularly large scans of data and/or are particularly complex, dramatic reductions in query elapsed times can be achieved through the use of an IBM DB2 Analytics Accelerator. These servers extend the capabilities of a DB2 for z/OS system running a business intelligence workload, delivering "shockingly fast" performance for formerly long-running queries (borrowing a phrase used by an IT manager friend of mine at a site that recently started using DB2 Analytics Accelerator technology), while requiring nothing in the way of changes on the client side of the BI application (users continue to direct their queries to the same DB2 location, and the DB2 optimizer determines whether a given query should be processed locally (i.e., on the DB2 for z/OS front-end) or routed to the Analytics Accelerator. This is way cool technology about which I'll write more soon in a subsequent blog entry.
Topping it all off are the functionality enhancements, delivered in each succeeding release of DB2 for z/OS, that are especially useful in a business analytics context. Just in the last couple of releases (Versions 9 and 10), we've gotten OLAP specifications such as rank, dense rank, and moving aggregates; index-on-expression, which can make predicates with column functions indexable; temporal data support, which enables one to add a time dimension to data in a table; the access path repository, a set of new DB2 10 catalog tables including SYSQUERYOPTS, that enables, among other things, DB2 server-side control of parallelization for dynamic queries at the statement level; a slew of new built-in functions (among them LPAD, MONTHS_BETWEEN, OVERLAY, and TRIM); new data types such as BIGINT, DECFLOAT, and XML; and real-time scoring, which allows for evaluation of newly inserted or updated database values against an SPSS predictive model as part of a transaction's flow, with virtually no impact on transaction elapsed time. And, there's more of this to come with DB2 11 for z/OS, now in beta test.
Is DB2 for z/OS the source DBMS for much of your company's "of record" data? Are you copying this data out to various other platforms for query and reporting purposes? Have you stopped to consider whether moving the queries to the data -- i.e., running your business analytics queries on the source DB2 for z/OS system -- might be a less complicated, more efficient, more secure, and more cost-effective approach? Leveraging DB2 for z/OS and System z as a platform for business analytics has been a winning strategy for many organizations. It might be for yours, as well.
Thursday, June 6, 2013
DB2 10 for z/OS and Enterprise Identity Mapping
You've probably seen this type of commercial many times on television: a frazzled individual vents his or her frustration over the difficulty of accomplishing some task, and finally cries out, "There must be a better way!" At which point the announcer chimes in, with a smooth and confident voice, "There is!" And then you get the product pitch (and it all concludes with the smiling, nodding approval of the formerly rattled, now happy-in-accomplishment user of the promoted product or service).
OK, now imagine you're a security administrator. You've got end users who log into an application server using their network IDs. An application running in that server accesses DB2 for z/OS data via TCP/IP connections (from a DB2 perspective, it is a DRDA requester). In connecting to DB2, the application utilizes a particular ID and password. You had a DBA create a DB2 role and a trusted context to prevent misuse of the application's security credentials, but still you were confronted with a seemingly vexing challenge: for audit purposes, and for purposes of granting DB2 privileges in a more fine-grained fashion versus having all of the application's users exercise the privileges granted to the application's DB2 authorization ID, you want to use RACF IDs to represent application users on the mainframe system. For several reasons, however, the RACF IDs that you use are different in form from the network IDs that the end users utilize for client-side authentication. The users have already done their authentication duty, and you don't want them to have to be concerned with the RACF IDs with which you want to associate their DB2 data access activities; furthermore, you don't want to have to define a different RACF ID for each and every individual application user -- you want the flexibility to assign a particular subset of the users to a single RACF ID.
What to do? Define a different RACF ID for each of the application's users? Ask each user to keep track of, and to use as appropriate, a RACF ID (and password) in addition to his or her network ID? ISN'T THERE A BETTER WAY?!?
Yes, there is. It's called enterprise identity mapping, and it's a capability that you can use with DB2 10 for z/OS. Truth be told, enterprise identity mapping (EIM) could be used in a DB2 9 system, but getting it set up in that environment was a pretty complex undertaking. With DB2 10 (and with some operating system enhancements initially delivered with z/OS 1.11), exploitation of EIM functionality got much easier. In this blog entry, I'll provide a high-level overview of the steps associated with EIM implementation in a DB2 10 setting. More detailed information can be found in the IBM "redbook" titled DB2 10 for z/OS Technical Overview (see section 10.6.2, "z/OS Security Server identity propagation"), and in the DB2 10 for z/OS Managing Security manual (see the information under the heading, "Implementing DB2 support for distributed identity filters"). Basically, there are three parts to this whole.
The RACF part
The key step here is use of the RACMAP command to associate a distributed user identity with a RACF ID. Mike Kearney, a colleague of mine who's a z/OS security expert, leads workshops that cover, among other things, enterprise identity mapping. Mike uses a command like the one following to illustrate the use of RACMAP for EIM (I've changed some of the resource names around):
RACMAP ID(USER1) MAP USERDIDFILTER(name('cn=rsmith,ou=users,o=DSC'))
REGISTRY(name('dg27.dallas.ibm.com:437'))
In this example, 'USER1' is a RACF ID to which you want a distributed user identity to map, 'rsmith' is the distributed user identity of interest, and 'dg27.dallas.ibm.com:437' identifies the registry in which the distributed user name is defined. Note that I could have used a wild card character (an asterisk, or '*') instead of the specific distributed user identity 'rsmith' in the "name" part of the USERDIDFILTER portion of the command, and that's where the flexibility of this approach comes in: had I done that, the RACMAP command above would have enabled RACF to map any distributed user identity defined in the registry 'dg27.dallas.ibm.com:437' to RACF ID 'USER1'; so, I can do 1-to-1 mapping of distributed user identities to RACF IDs, or I can do a many-to-1 mapping, with all distributed user identities defined in a particular registry mapping to a single RACF ID. Use 1-to-1 or many-to-1 mapping, or both, depending on your particular needs.
The DB2 part
As it pertains to DB2 for z/OS, RACF distributed user identity mapping is supported in relation to a DB2 trusted context. That trusted context would define the circumstances in which the privileges granted to a role could be exercised by a user. So, suppose I want a user (or a set of users, referring to the many-to-1 mapping mentioned above) to have the privileges granted to a role, called APPL1_ROLE, when accessing the DB2 database via an application that connects to the DB2 subsystem using the authorization ID 'APPL1SYS', from an application server at IP address 1.2.3.4. Further, I want the end user ID of the person using the application (i.e., the network ID used by that person to authenticate on the client side) to map to RACF user ID 'USER1' (referring to the example in "The RACF part" of this blog entry, above). In that case, my CREATE TRUSTED CONTEXT statement would look something like this:
CREATE TRUSTED CONTEXT APPL1_TRUST
BASED UPON CONNECTION USING SYSTEM AUTHID APPL1SYS
ATTRIBUTES (ADDRESS ‘1.2.3.4’)
ENABLE
WITH USE FOR USER1 ROLE APPL1_ROLE;
The application part
For a client application to do its part in EIM, it needs to 1) get a trusted context-based connection to the DB2 for z/OS server, and 2) request an authorization ID switch for that trusted context-based connection (in doing that, the application passes to DB2 the distributed user identity and the name of the associated registry). These actions can be accomplished by way of the IBM WebSphere Application Server, if the application executes in that environment. If some other application server that does not provide this functionality is being used, the required actions can still be accomplished by way of APIs for Java, CLI/ODBC, and NET. These APIs support establishing a trusted connection and switching a distributed user identity for such a connection. Here is a link to information about the JDBC APIs for trusted context-based connection creation and distributed user identity switching, in the DB2 10 for z/OS Information Center on the Web:
.
http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.java/src/tpc/imjcc_c0023797.htm
And here is a link to information on the CLI/ODBC function used to establish a trusted connection:
http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0000575.html
And a link to the CLI/ODBC function used to switch user identities on a trusted connection:
http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.qb.dbconn.doc/doc/t0023258.html
Flexibility without complicating things for end-users
Put it all together, and you have a flexible and powerful means of mapping distributed user identities to RACF IDs, with no requirement for application end users to do anything different than what they're doing today. That's a better way.
OK, now imagine you're a security administrator. You've got end users who log into an application server using their network IDs. An application running in that server accesses DB2 for z/OS data via TCP/IP connections (from a DB2 perspective, it is a DRDA requester). In connecting to DB2, the application utilizes a particular ID and password. You had a DBA create a DB2 role and a trusted context to prevent misuse of the application's security credentials, but still you were confronted with a seemingly vexing challenge: for audit purposes, and for purposes of granting DB2 privileges in a more fine-grained fashion versus having all of the application's users exercise the privileges granted to the application's DB2 authorization ID, you want to use RACF IDs to represent application users on the mainframe system. For several reasons, however, the RACF IDs that you use are different in form from the network IDs that the end users utilize for client-side authentication. The users have already done their authentication duty, and you don't want them to have to be concerned with the RACF IDs with which you want to associate their DB2 data access activities; furthermore, you don't want to have to define a different RACF ID for each and every individual application user -- you want the flexibility to assign a particular subset of the users to a single RACF ID.
What to do? Define a different RACF ID for each of the application's users? Ask each user to keep track of, and to use as appropriate, a RACF ID (and password) in addition to his or her network ID? ISN'T THERE A BETTER WAY?!?
Yes, there is. It's called enterprise identity mapping, and it's a capability that you can use with DB2 10 for z/OS. Truth be told, enterprise identity mapping (EIM) could be used in a DB2 9 system, but getting it set up in that environment was a pretty complex undertaking. With DB2 10 (and with some operating system enhancements initially delivered with z/OS 1.11), exploitation of EIM functionality got much easier. In this blog entry, I'll provide a high-level overview of the steps associated with EIM implementation in a DB2 10 setting. More detailed information can be found in the IBM "redbook" titled DB2 10 for z/OS Technical Overview (see section 10.6.2, "z/OS Security Server identity propagation"), and in the DB2 10 for z/OS Managing Security manual (see the information under the heading, "Implementing DB2 support for distributed identity filters"). Basically, there are three parts to this whole.
The RACF part
The key step here is use of the RACMAP command to associate a distributed user identity with a RACF ID. Mike Kearney, a colleague of mine who's a z/OS security expert, leads workshops that cover, among other things, enterprise identity mapping. Mike uses a command like the one following to illustrate the use of RACMAP for EIM (I've changed some of the resource names around):
RACMAP ID(USER1) MAP USERDIDFILTER(name('cn=rsmith,ou=users,o=DSC'))
REGISTRY(name('dg27.dallas.ibm.com:437'))
In this example, 'USER1' is a RACF ID to which you want a distributed user identity to map, 'rsmith' is the distributed user identity of interest, and 'dg27.dallas.ibm.com:437' identifies the registry in which the distributed user name is defined. Note that I could have used a wild card character (an asterisk, or '*') instead of the specific distributed user identity 'rsmith' in the "name" part of the USERDIDFILTER portion of the command, and that's where the flexibility of this approach comes in: had I done that, the RACMAP command above would have enabled RACF to map any distributed user identity defined in the registry 'dg27.dallas.ibm.com:437' to RACF ID 'USER1'; so, I can do 1-to-1 mapping of distributed user identities to RACF IDs, or I can do a many-to-1 mapping, with all distributed user identities defined in a particular registry mapping to a single RACF ID. Use 1-to-1 or many-to-1 mapping, or both, depending on your particular needs.
The DB2 part
As it pertains to DB2 for z/OS, RACF distributed user identity mapping is supported in relation to a DB2 trusted context. That trusted context would define the circumstances in which the privileges granted to a role could be exercised by a user. So, suppose I want a user (or a set of users, referring to the many-to-1 mapping mentioned above) to have the privileges granted to a role, called APPL1_ROLE, when accessing the DB2 database via an application that connects to the DB2 subsystem using the authorization ID 'APPL1SYS', from an application server at IP address 1.2.3.4. Further, I want the end user ID of the person using the application (i.e., the network ID used by that person to authenticate on the client side) to map to RACF user ID 'USER1' (referring to the example in "The RACF part" of this blog entry, above). In that case, my CREATE TRUSTED CONTEXT statement would look something like this:
CREATE TRUSTED CONTEXT APPL1_TRUST
BASED UPON CONNECTION USING SYSTEM AUTHID APPL1SYS
ATTRIBUTES (ADDRESS ‘1.2.3.4’)
ENABLE
WITH USE FOR USER1 ROLE APPL1_ROLE;
The application part
For a client application to do its part in EIM, it needs to 1) get a trusted context-based connection to the DB2 for z/OS server, and 2) request an authorization ID switch for that trusted context-based connection (in doing that, the application passes to DB2 the distributed user identity and the name of the associated registry). These actions can be accomplished by way of the IBM WebSphere Application Server, if the application executes in that environment. If some other application server that does not provide this functionality is being used, the required actions can still be accomplished by way of APIs for Java, CLI/ODBC, and NET. These APIs support establishing a trusted connection and switching a distributed user identity for such a connection. Here is a link to information about the JDBC APIs for trusted context-based connection creation and distributed user identity switching, in the DB2 10 for z/OS Information Center on the Web:
.
http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.java/src/tpc/imjcc_c0023797.htm
And here is a link to information on the CLI/ODBC function used to establish a trusted connection:
http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0000575.html
And a link to the CLI/ODBC function used to switch user identities on a trusted connection:
http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.qb.dbconn.doc/doc/t0023258.html
Flexibility without complicating things for end-users
Put it all together, and you have a flexible and powerful means of mapping distributed user identities to RACF IDs, with no requirement for application end users to do anything different than what they're doing today. That's a better way.
Wednesday, May 15, 2013
DB2 for z/OS Performance Tuning: the Rising Tide and the Home Run
I've been working with DB2 for z/OS for about 25 years. During most of that time, I've been actively engaged in performance tuning work -- specifically, performance tuning as it pertains to the CPU and elapsed time associated with application access to data in DB2-managed databases. I've learned that the best results, in terms of efficiency and throughput gains, are achieved via a two-pronged approach that I conceptualize as a rising tide and home runs. In this blog entry, I'll explain these concepts and try to provide you with a road map for achieving your own DB2 for z/OS performance tuning successes.
The rising tide
Here in the USA (and perhaps in some other countries), there's an old saying: "A rising tide lifts all boats." The DB2 tuning actions that I think of as being in the "rising tide" category are those that positively affect the performance of all SQL statements (or at least many statements) that execute in the target DB2 subsystem. Examples of these actions include the following (and I'm providing here a few links to blog posts and articles of mine that provide additional information):
"Rising tide" DB2 performance tuning actions are attractive for several reasons. Chief among these:
All good; and yet, there is a pretty significant "yeah, but" associated with most "rising tide" DB2 performance tuning actions (as in, "Yeah, that's nice, but..."). The big "yeah, but" to which I'm referring is the typically modest magnitude of the performance gain that usually results from implementation of a "rising tide" tuning step. Very often, what you'd expect to see is a reduction in CPU consumption of maybe a few percentage points for SQL statements impacted by a "rising tide" change. Basically, you're improving a lot of DB2-access operations by a little bit each. Saving a little bit of CPU for a lot of SQL statements is a good thing, of course, but along with the "lots of little" improvements, you want some big scores. That's where "home run" tuning comes into play.
The home run: narrow focus, big payback
Concentrate your tuning efforts on a single SQL statement, with all performance-improving options on the table, and you might see the CPU time (and, quite possibly, the elapsed time) of that statement go down by 90% or more. I've seen that happen time and time again over the past couple of decades, with query elapsed and CPU times slashed as a result of statement-focused tuning actions. Examples of such actions include:
In these and other cases, I've observed that chances of success are improved when:
One other thing: working to reduce CPU and elapsed time for one particular query might in fact improve the performance of other similar queries. For example, an index on an expression created to cut the response time of one query might similarly shorten run times for other queries that also have predicates with the column expression in question. Think of that -- getting better performance for several queries as a result of an action taken to drive down the CPU cost of a single query -- as hitting a home run with men on base (sticking with the baseball analogy).
It's not an either/or thing
By all means, you should avoid getting into arguments over the relative value of the "rising tide" versus the "home run" approach to DB2 for z/OS performance tuning. One approach produces low-cost (particularly in terms of required effort) wins that have wide-ranging -- if generally modest -- cost-reducing effects on query execution. The other approach is typically more labor-intensive and can require more time for implementation, but often delivers big performance improvement results for queries singled out for tuning. What you want is to use both approaches in an ongoing, systematic way: make your query execution environment as conducive to good performance as possible, and look for ways to make your most-costly queries much less costly.
Pursuing better DB2 application performance on these two fronts brings to my mind one more baseball-related image. The San Francisco Giants play their home games in a baseball stadium that's right on the water. Hitters who can drive the ball to right field with some power can land a ball in San Francisco Bay. That's where the rising tide meets the home runs, and that's where you want to be with respect to tuning the performance of your mainframe DB2 system.
The rising tide
Here in the USA (and perhaps in some other countries), there's an old saying: "A rising tide lifts all boats." The DB2 tuning actions that I think of as being in the "rising tide" category are those that positively affect the performance of all SQL statements (or at least many statements) that execute in the target DB2 subsystem. Examples of these actions include the following (and I'm providing here a few links to blog posts and articles of mine that provide additional information):
- Adjusting buffer pool configuration settings to optimize performance
- Enlarging DB2 buffer pools to reduce read I/O activity (and associated CPU consumption)
- Page-fixing DB2 buffer pools to reduce the CPU cost of DB2 database I/O operations (and, in a DB2 10 environment, to take advantage of 1 MB real storage page frames for further CPU savings)
- Making DB2 virtual storage pools such as the dynamic statement cache, the RID pool, and the sort pool larger -- all examples of using more memory so as to use fewer MIPS
- Binding packages that are executed frequently (and via persistent threads) with RELEASE(DEALLOCATE) -- something that can improve the performance of batch jobs, CICS-DB2 transactions, and DRDA-using client-server applications (the latter thanks to the high-performance DBAT feature of DB2 10)
- Driving CICS-DB2 thread reuse
- Eliminating useless indexes, to improve the performance of insert and delete operations
- Getting older, rarely referenced records out of the database and into an archive
- Migrating to DB2 10 for z/OS, which delivered for many organizations the biggest release-related performance improvement seen since DB2 Version 2 Release 3
- Upgrading to a faster, more powerful mainframe server
"Rising tide" DB2 performance tuning actions are attractive for several reasons. Chief among these:
- They tend to have a wide-ranging impact on the performance of DB2 data access operations. That's the "lifts all boats" effect to which I alluded above. In taking one or more of these actions, you can expect to see improved performance (particularly in terms of CPU efficiency) for lots of the SQL statements that execute in your DB2 subsystem -- not just one or two statements.
- They're often pretty easy to implement. Want to page-fix buffer pool BP2 (for example)? OK -- issue an -ALTER BUFFERPOOL(BP2) PGFIX(YES) command (the change will take effect when the pool is next deallocated and reallocated -- usually as a result of "bouncing" the DB2 subsystem). Want to increase the size of your DB2 dynamic statement cache? Change the value of the EDMSTMTC parameter in your ZPARM module. Want to boost CICS-DB2 thread reuse? Change PROTECTNUM to a non-zero value in the DB2ENTRY resources associated with your most frequently executed CICS transactions. In these and other cases, no database design changes are needed, and no application code changes are required.
- They can be implemented quickly. This is directly related to item 2, above. When a tuning action requires a change to an SQL statement issued by a program, that change is not going to go into production overnight. Even if it's a pretty simple change, a change request has to be generated and approved, the change has to be assigned to a programmer, it has to be tested, etc. All this could take weeks. Because "rising tide" tuning actions generally don't necessitate modification of application code, they can often be put into effect in relatively short order.
All good; and yet, there is a pretty significant "yeah, but" associated with most "rising tide" DB2 performance tuning actions (as in, "Yeah, that's nice, but..."). The big "yeah, but" to which I'm referring is the typically modest magnitude of the performance gain that usually results from implementation of a "rising tide" tuning step. Very often, what you'd expect to see is a reduction in CPU consumption of maybe a few percentage points for SQL statements impacted by a "rising tide" change. Basically, you're improving a lot of DB2-access operations by a little bit each. Saving a little bit of CPU for a lot of SQL statements is a good thing, of course, but along with the "lots of little" improvements, you want some big scores. That's where "home run" tuning comes into play.
The home run: narrow focus, big payback
Concentrate your tuning efforts on a single SQL statement, with all performance-improving options on the table, and you might see the CPU time (and, quite possibly, the elapsed time) of that statement go down by 90% or more. I've seen that happen time and time again over the past couple of decades, with query elapsed and CPU times slashed as a result of statement-focused tuning actions. Examples of such actions include:
- Defining a new index on a table to provide a better-performing access path for an expensive query.
- Using Data Studio Statistics Advisor recommendations to update DB2 catalog statistics via RUNSTATS to reflect the non-uniform distribution of duplicate values in a column of a table, thereby enabling DB2 to choose a much better access path for a query.
- Adding a non-result-set-altering predicate to a query to provide DB2 with more information about the characteristics of qualifying rows, enabling selection of a much superior access path for the query.
- Changing a "between" predicate in a query to an in-list predicate, and changing another predicate in the same query to be indexable (by removing an arithmetic column expression in the predicate), resulting in an increase in the number of index key columns on which DB2 can match in processing the predicates, and a much better-performing access path.
- Changing a join query with OR'ed predicates referencing columns in two different tables (WHERE TABLE_A.COL1 = 'XYZ' OR TABLE_B.COL2 = 123) to a union of two selects without such OR'ed predicates, leading to vastly improved pre-join row filtering.
- Creating an index on a column expression to make a formerly non-indexable predicate indexable and stage 1.
In these and other cases, I've observed that chances of success are improved when:
- You do a good job of target selection. Because statement-level tuning work might improve performance only for the one query on which you're focused, choose your target query wisely. Make sure that the query you've selected for tuning is one for which a big performance improvement would be truly valuable for your organization.
- You don't develop tunnel vision at the outset of a statement-level tuning effort. By this I mean that you don't want to settle prematurely on a particular technique for reducing the query's CPU and/or elapsed time, as doing so could blind you to other means of accomplishing your goal. Consider all available tuning options. What worked for one query may or may not work for another.
- You get the right people involved. Not only DBAs and application developers, but business-knowledgeable people and data domain experts, as well. Different people will view a query that's up for tuning in different ways, and that can spur creative and effective thinking in terms of identifying actions that boost query performance.
One other thing: working to reduce CPU and elapsed time for one particular query might in fact improve the performance of other similar queries. For example, an index on an expression created to cut the response time of one query might similarly shorten run times for other queries that also have predicates with the column expression in question. Think of that -- getting better performance for several queries as a result of an action taken to drive down the CPU cost of a single query -- as hitting a home run with men on base (sticking with the baseball analogy).
It's not an either/or thing
By all means, you should avoid getting into arguments over the relative value of the "rising tide" versus the "home run" approach to DB2 for z/OS performance tuning. One approach produces low-cost (particularly in terms of required effort) wins that have wide-ranging -- if generally modest -- cost-reducing effects on query execution. The other approach is typically more labor-intensive and can require more time for implementation, but often delivers big performance improvement results for queries singled out for tuning. What you want is to use both approaches in an ongoing, systematic way: make your query execution environment as conducive to good performance as possible, and look for ways to make your most-costly queries much less costly.
Pursuing better DB2 application performance on these two fronts brings to my mind one more baseball-related image. The San Francisco Giants play their home games in a baseball stadium that's right on the water. Hitters who can drive the ball to right field with some power can land a ball in San Francisco Bay. That's where the rising tide meets the home runs, and that's where you want to be with respect to tuning the performance of your mainframe DB2 system.
Subscribe to:
Posts (Atom)