Recently, a DB2 for z/OS professional asked me about something he'd occasionally seen in a DB2 monitor display: a non-zero value for CONDITIONAL GETPAGE FAILURES for a buffer pool. Now, his DB2 subsystem appeared to be running just fine, but we are rather conditioned (so to speak) to associate the word FAILURE with the meaning NOT GOOD, and he wanted to know if these FAILURES indicated something with which he should be concerned. The short answer to this question is, "No -- that's just an FYI number." In the remainder of this entry I'll provide some background on conditional GETPAGEs and explain why associated "failures" are nothing to lose sleep over.
I expect that you all know what a DB2 GETPAGE is: a request by DB2 for z/OS to access a particular page in a table or an index. For a "regular" GETPAGE (the vast majority are of this type), a "miss" (i.e., a situation in which the requested page is not found in the DB2 buffer pool) will result in DB2 issuing a synchronous read I/O to get the page into the buffer pool from disk storage. In essence, a DB2 synchronous read I/O is the result of a "regular" GETPAGE "failure." Are you concerned about those "failures?" Of course not -- that's just DB2 business as usual: "I (DB2) need to access page 123 of tablespace XYZ. If it's not in the buffer pool, suspend the application process on behalf of which I'm requesting this page, read the page into memory from disk right away, and resume the application process when the read I/O is completed."
So it is with conditional GETPAGE "failures" -- no worries (as the Aussies say -- I love that phrase). What's different about a conditional GETPAGE versus a "regular" GETPAGE: for the former, a "miss" (reported by a DB2 monitor as a "failure") will result in DB2 driving an asynchronous read I/O to get the page into memory and subsequently issuing a "regular" GETPAGE for the page. "Huh?" you might be thinking, "An asynchronous I/O for one page? I thought that asynchronous I/Os (generally associated with prefetch reads) were issued for multiple pages (typically, up to 32 at a time)." This all has to do with WHY DB2 issues conditional GETPAGE requests: it's for parallel I/O operations under a single task. Before DB2 10, conditional GETPAGEs were issued when query I/O parallelism was used by DB2 to access table or index pages (since DB2 for z/OS V4, most folks equate "query parallelism" with query CPU parallelism -- I/O parallelism is what we had before that). Since there's one task, you can have one synchronous I/O going. If DB2 needs a page and it's not in the buffer pool, it can issue an asynchronous I/O (performed under a DB2 task, versus the application process's task) and move on to the next page that it wants (from another tablespace partition, for example). When it's ready to work on that page for which the asynchronous I/O was driven, DB2 will issue a regular GETPAGE for it, and there's a good chance that it'll be in the buffer pool.
So, as I mentioned, I/O parallelism hasn't exactly been front and center in the minds of mainframe DB2 people since query CPU parallelism made the scene with DB2 V4. With DB2 10, there's something new: index I/O parallelism for inserts into a table in a non-segmented tablespace on which multiple indexes are defined. When there's an insert into a such a table (generally, one having three or more indexes), DB2 will of course issue "regular" GETPAGEs (for root, non-root, and leaf pages) for the table's clustering index, because that's how it identifies the target page in the table for the insert. When that's done and the row is inserted (into the target page of the table, or into a page near that one, if the target page is full or locked and space is available in a nearby page), it's time to update the table's indexes with the row's physical location. The clustering index leaf page needing updating is already in memory. DB2 goes to the next index and issues a conditional GETPAGE for the page it needs to access. If there's a "miss" (aka a "failure"), it moves on and issues a conditional GETPAGE for the next index page it needs. Thus you can have the inserting task stay busy while asynchronous read I/O requests are processed in the background. DB2 will issue "regular" GETPAGEs for the "last" of the table's indexes that it's updating, as it did for the clustering index, but again the objective is for DB2 to work in the background to bring pages from the other indexes on the table into memory asynchronously. When the inserting task is ready to update those index pages, the hope is that those pages will already be in memory, thanks to the asynchronous I/Os. Index I/O parallelism for inserts can result in significant reductions in elapsed time for insert operations into tables in non-segmented tablespaces in a DB2 10 environment.
Besides boosting performance, DB2 10 index I/O parallelism for inserts can result in some conditional GETPAGE "failures," as these are what trigger the asynchronous read I/Os for pages not found in the buffer pool. That, of course, is no sweat whatsoever, whether we're talking about DB2 10 or a prior release (in which case, as noted, conditional GETPAGEs are associated with query I/O parallelism). These "failures" are just buffer pool misses, and those happen all the time in a DB2 environment (unless you have a really big buffer pool configuration and a really small database). So when you're looking at a DB2 monitor display of activity for a buffer pool, and you see a non-zero value for conditional GETPAGE failures, think, "OK," and move on to other fields in the display. Hang loose, dude.
Sunday, June 26, 2011
Monday, June 13, 2011
Application Programming Tip: Let DB2 do the Work for You
Back in the 1980s, when DB2 was relatively new, you had, of course, a lot of application developers who were learning how to program using SQL (IBM invented the relational database and Structured Query Language). Though SQL is pretty intuitive, mastery takes time and so it was understandable that some programmers early on did in their code things that would more effectively be done by DB2. Examples of what I'm talking about include joining tables in application code (open a cursor on table A, fetch a qualifying row, and look for a matching row in table A), retrieving unordered result sets and sorting the rows programmatically, and failing to leverage the set-oriented nature of SQL (too often retrieving result set rows via a series of singleton SELECTs versus DECLARE CURSOR / OPEN CURSOR / FETCH, FETCH, FETCH, FETCH...).
Interestingly, we're here now at 27 years after DB2's introduction, and there are STILL programmers who are doing in their applications what DB2 should be doing by way of properly coded SQL statements. In this blog entry I'll try to make the case for allowing DB2 to do what it can do in terms of retrieving and updating data. There are several motivating factors when it comes to exploiting the power of SQL as fully as possible. The ones that are most important to me include the following:
Enhance CPU efficiency. When your program issues an SQL statement, obviously that statement has to get from your program to DB2, and after statement execution has completed control has to return to your application code. This program-to-DB2 round trip is not free. The CPU cost of DB2-and-back is (usually) readily grasped in a client-server setting, what with network send and receive processing and all, but there is overhead associated with getting to and from DB2 even when the SQL-issuing program is running on the same server as DB2 (as is often the case in a mainframe system, where CICS and batch programs access a local DB2 database). Get what you want with fewer SQL statements, and you'll reduce the cumulative CPU cost of trips across the application program-DB2 boundary. The effect of getting the job done with fewer trips to DB2 can be quite significant, and here it's important to focus not on a single unit of work but on an overall workload. A developer might run a comparison test between a more-SQL-statements and a fewer-SQL-statements approach to retrieving a particular result set or accomplishing a data change operation, and he might conclude that it's a wash because the elapsed time is about the same either way. Elapsed time is not the issue here -- CPU time is. That's the money metric. And a seemingly small difference in CPU time per transaction (something that a DB2 monitor would show) can end up being a big deal when hundreds of transactions execute per second, or when a batch job has to work through hundreds of thousands or even millions of records in an input file.
Lest you think that this is just a matter of having DB2 and not your application code join tables, I'll tell you: it goes beyond that. To really get things tight from a CPU efficiency perspective, you need to keep up with, and be prepared to take advantage of, new DB2 features and functions that can enable you to reduce "chattiness" between your program and DB2. The MERGE statement (sometimes referred to as "upsert"), with which you can make changes to a table based on a set of input records, updating target table data when there is a match with an input record, and inserting a new row into the table when there isn't a match? That's a CPU saver compared to the old methodology of doing a SELECT against the target table to see if there is a row that matches an input record and then driving an UPDATE (if there is a match) or an INSERT (if there's no match). Multi-row FETCH and multi-row INSERT (sometimes called block FETCH and block INSERT), means of, respectively, getting several rows from DB2 with one FETCH and placing several new rows in a table with one INSERT? Those are CPU savers versus the one-at-a-time way of doing things. Look for ways to do more with less SQL, and you'll reduce the load on your DB2 server.
Application performance consistency. OK, back to joining tables. I like this example because when you do this in application code YOU'RE making the access path decision. Opening a cursor on table A, fetching a qualifying row, and looking for a match in table B is, in essence, a nested loop join. How do you know that nested loop is the right way to go? What about a merge join or a hybrid join (DB2 for z/OS) or a hash join (DB2 for Linux/UNIX/Windows)? When DB2 performs the table join, the SQL optimizer determines the low-cost means of accomplishing the join, based on statistical information stored in DB2 catalog tables. The optimizer is very good at what it does, having been continually enhanced for upwards of thirty years (in addition to inventing the relational database and SQL, IBM invented cost-based SQL statement optimization). Do you really think that you'd do a better job of choosing the most efficient join method for a query? And where does my point about consistency come in? Well, suppose that you do a programmatic join and choose a nested loop method to get the job done. That may in fact be the right choice early on in the life of the application, when target tables -- and the query result set -- are relatively small. What about later, when the database has grown substantially and the query result set is perhaps much larger than before? Would a merge join be the better choice under those circumstances? If it is, and if you let DB2 do the join, the optimizer will take care of that access path change automatically (in the case of dynamic SQL) or with a simple REBIND PACKAGE command (for static SQL) -- no program code changes are needed. Do the join in application code, and you either live with deteriorating performance (if the join method you chose is no longer appropriate), or you change the join logic in your program.
And what about the effect of physical database changes? Suppose that the nested loop join that you accomplish programmatically depends for good performance on the existence of a certain index on a target table? That same table may be over-indexed to the point that insert and delete operations are costing too much. It may be that if the index your programmatic join needs is removed, DB2 -- were it handling the join -- could switch to a merge join and deliver acceptable performance for your query. You can't switch from nested loop to merge join in your program (not without re-write effort), and so your programmatic join may stand in the way of DBAs making a physical database change that could reduce run times for critical data-change operations.
Oh, and things get way more complex when the number of tables joined in a given SELECT statement increases. In what order should the tables be joined? Should the same join method be used all the way through, or would it be best to join A and B via nested loop, and then join that composite table to C via a merge join? And what about tables D, and E, and so on? Do you really want to sort this out yourself?
Bottom line: when you let DB2 do as much as it can, you leave the access path selection process up to DB2. That's a very good move on your part.
Application program simplification. As with reducing program-to-DB2 "chattiness" for improved CPU efficiency, leveraging SQL for application program simplification goes beyond obvious things like having DB2 do things like table joins and result set row grouping and aggregation -- you do best when you learn about and exploit new features and functions that are delivered with every release of DB2. Do you need to assign a number -- perhaps a ranking number -- to each row in a result set based on some criterion of your choice? You could do that with your own program logic, or you could let DB2 do the work via its built-in RANK, DENSE_RANK, or ROW-NUMBER functions. Want to concatenate one character string value to another? Figure out the day of the week for a given date value? Get the number of seconds between midnight and a timestamp value? Overlay some portion of a character string with another string? Find a character string value with a pronunciation that is the same, or close to, that of another string? Serialize an XML value into a character string? All these capabilities, and MANY MORE, are built into DB2 (on all platforms -- mainframe and LUW). Have you checked out the built-in DB2 functions (scalar and aggregate) lately? If not, give 'em a look. Your best source for DB2 function information is the SQL Reference. You can find that manual, for DB2 for z/OS and for DB2 for LUW, at IBM's Web site (the built-in functions are documented in Volume 1 of the DB2 for LUW SQL Reference).
Beyond the built-in functions, you can move work into DB2 (and out of your application code) by way of CASE expressions, which provide for on-the-fly transformation of values returned by, or otherwise processed by, an SQL statements (e.g., "return X when the value in column COL1 is Y"), and with CAST specifications, which tell DB2 to change the data type of column values (e.g., from decimal to integer). Again, the SQL Reference is the place to go for more information.
I haven't even mentioned things like the INTERSECT and EXCEPT result-set-comparison operators, date/time arithmetic, and moving aggregates. DB2 SQL is very powerful and is becoming more so as time goes by. You can leverage that power to remove complexity that would otherwise be in your application program code.
Code re-use. This is a hot topic these days, and with good reason: to the extent that application logic can be encapsulated in a readily reusable form, programmer productivity increases (less reinvent-the-wheel work) and so does organizational agility (application creation and extension is accelerated). DB2 can be a big help here. There are all kinds of ways to push logic into the DB2 level of an application, and when that's done said logic is made available to any kind of program that accesses the database. A simple example of logic-in-DB2 is DB2-defined referential integrity. Why burden programmers with the task of ensuring that data values in a column of a "child" table always have a corresponding value in a "parent" table column, when DB2 can do that? And, when that's implemented at the DB2 level then it's there for the benefit of ALL DB2-accessing programs. Ditto table check constraints, which can ensure, among other things, that values to be inserted into a column must exist within a certain range of values.
Triggers are another way to push logic into the DB2 level of an application infrastructure. These can be used to enforce business rules for insert operations (rules that might be too complex to implement with table check constraints), to automatically maintain values in denormalized database tables ("if column X in table A is updated by a program, perform the same update for column Y in the matching row of table B"), to make otherwise read-only views updateable (this with INSTEAD OF triggers), and more. User-defined functions (UDFs) make certain routines (e.g., to perform certain data transformation operations) available to any program that can issue an SQL statement, and the same is true of DB2 stored procedures (these can be more sophisticated than user-defined functions, and they are invoked via the SQL statement CALL versus being referenced in SELECT statements as are UDFs).
The more data-centric logic is implemented in the DB2 database, the more you as a programmer can do what delivers the greatest value to your employer: writing code that directly addresses the business functionality needs of the organization. If you have some data-access logic that you think could be broadly applicable in your enterprise, talk to a DB2 DBA and see about getting that deployed in the database layer of the application system. A lot of your colleagues could end up benefiting from that move, as their programs will also be able to utilize the DB2-implemented capability.
Now, you've probably heard it said that there's an exception to every rule, and in closing I'll mention one such exception to my "let DB2 do what it can" rule. This exception has to do with sorting result set rows. Sometimes, in a decision support application environment, a user will want the capability to sort data rows returned from DB2 by whatever field he chooses. Having DB2 do that initial sort is the right move, but if the user wants to sort the same result set by a different field, you might consider doing that at the user workstation level (or maybe at the application server level). This might be good for efficiency if the result set is not particularly large (maybe a few hundred rows or less) but a lot of host resources are needed to generate the result set (sometimes a lot of data may be scanned and aggregated to build a small result set). In that case, you might not want to send the query back to DB2 with just a different ORDER BY specification -- maybe you just want to re-sort the 20 or 50 or 100 (or whatever) rows at the client end of things. The real work here was generating the result set. A simple re-sort might best be done locally with respect to the end user (on the other hand, if the query in question is very fast-running and the result set is rather small, having DB2 re-order the rows at the user's request is probably no big deal).
So, with the occasional exception here and there, you really are doing the right thing by letting DB2 do all that it can do with SQL (and remember that some of this logic- and functionality-implementing SQL -- examples include the creation of triggers and UDFs, and the altering of tables to include check constraints or referential integrity rules -- is in a DBA's domain, so get help there when you need it). Your programs are likely to be more efficient, performance will probably be more consistent over time, the code you have to write will be simplified, and your organization overall will benefit from the accessibility and re-usability of capabilities built into your DB2 database. A lot to like, there.
Interestingly, we're here now at 27 years after DB2's introduction, and there are STILL programmers who are doing in their applications what DB2 should be doing by way of properly coded SQL statements. In this blog entry I'll try to make the case for allowing DB2 to do what it can do in terms of retrieving and updating data. There are several motivating factors when it comes to exploiting the power of SQL as fully as possible. The ones that are most important to me include the following:
Enhance CPU efficiency. When your program issues an SQL statement, obviously that statement has to get from your program to DB2, and after statement execution has completed control has to return to your application code. This program-to-DB2 round trip is not free. The CPU cost of DB2-and-back is (usually) readily grasped in a client-server setting, what with network send and receive processing and all, but there is overhead associated with getting to and from DB2 even when the SQL-issuing program is running on the same server as DB2 (as is often the case in a mainframe system, where CICS and batch programs access a local DB2 database). Get what you want with fewer SQL statements, and you'll reduce the cumulative CPU cost of trips across the application program-DB2 boundary. The effect of getting the job done with fewer trips to DB2 can be quite significant, and here it's important to focus not on a single unit of work but on an overall workload. A developer might run a comparison test between a more-SQL-statements and a fewer-SQL-statements approach to retrieving a particular result set or accomplishing a data change operation, and he might conclude that it's a wash because the elapsed time is about the same either way. Elapsed time is not the issue here -- CPU time is. That's the money metric. And a seemingly small difference in CPU time per transaction (something that a DB2 monitor would show) can end up being a big deal when hundreds of transactions execute per second, or when a batch job has to work through hundreds of thousands or even millions of records in an input file.
Lest you think that this is just a matter of having DB2 and not your application code join tables, I'll tell you: it goes beyond that. To really get things tight from a CPU efficiency perspective, you need to keep up with, and be prepared to take advantage of, new DB2 features and functions that can enable you to reduce "chattiness" between your program and DB2. The MERGE statement (sometimes referred to as "upsert"), with which you can make changes to a table based on a set of input records, updating target table data when there is a match with an input record, and inserting a new row into the table when there isn't a match? That's a CPU saver compared to the old methodology of doing a SELECT against the target table to see if there is a row that matches an input record and then driving an UPDATE (if there is a match) or an INSERT (if there's no match). Multi-row FETCH and multi-row INSERT (sometimes called block FETCH and block INSERT), means of, respectively, getting several rows from DB2 with one FETCH and placing several new rows in a table with one INSERT? Those are CPU savers versus the one-at-a-time way of doing things. Look for ways to do more with less SQL, and you'll reduce the load on your DB2 server.
Application performance consistency. OK, back to joining tables. I like this example because when you do this in application code YOU'RE making the access path decision. Opening a cursor on table A, fetching a qualifying row, and looking for a match in table B is, in essence, a nested loop join. How do you know that nested loop is the right way to go? What about a merge join or a hybrid join (DB2 for z/OS) or a hash join (DB2 for Linux/UNIX/Windows)? When DB2 performs the table join, the SQL optimizer determines the low-cost means of accomplishing the join, based on statistical information stored in DB2 catalog tables. The optimizer is very good at what it does, having been continually enhanced for upwards of thirty years (in addition to inventing the relational database and SQL, IBM invented cost-based SQL statement optimization). Do you really think that you'd do a better job of choosing the most efficient join method for a query? And where does my point about consistency come in? Well, suppose that you do a programmatic join and choose a nested loop method to get the job done. That may in fact be the right choice early on in the life of the application, when target tables -- and the query result set -- are relatively small. What about later, when the database has grown substantially and the query result set is perhaps much larger than before? Would a merge join be the better choice under those circumstances? If it is, and if you let DB2 do the join, the optimizer will take care of that access path change automatically (in the case of dynamic SQL) or with a simple REBIND PACKAGE command (for static SQL) -- no program code changes are needed. Do the join in application code, and you either live with deteriorating performance (if the join method you chose is no longer appropriate), or you change the join logic in your program.
And what about the effect of physical database changes? Suppose that the nested loop join that you accomplish programmatically depends for good performance on the existence of a certain index on a target table? That same table may be over-indexed to the point that insert and delete operations are costing too much. It may be that if the index your programmatic join needs is removed, DB2 -- were it handling the join -- could switch to a merge join and deliver acceptable performance for your query. You can't switch from nested loop to merge join in your program (not without re-write effort), and so your programmatic join may stand in the way of DBAs making a physical database change that could reduce run times for critical data-change operations.
Oh, and things get way more complex when the number of tables joined in a given SELECT statement increases. In what order should the tables be joined? Should the same join method be used all the way through, or would it be best to join A and B via nested loop, and then join that composite table to C via a merge join? And what about tables D, and E, and so on? Do you really want to sort this out yourself?
Bottom line: when you let DB2 do as much as it can, you leave the access path selection process up to DB2. That's a very good move on your part.
Application program simplification. As with reducing program-to-DB2 "chattiness" for improved CPU efficiency, leveraging SQL for application program simplification goes beyond obvious things like having DB2 do things like table joins and result set row grouping and aggregation -- you do best when you learn about and exploit new features and functions that are delivered with every release of DB2. Do you need to assign a number -- perhaps a ranking number -- to each row in a result set based on some criterion of your choice? You could do that with your own program logic, or you could let DB2 do the work via its built-in RANK, DENSE_RANK, or ROW-NUMBER functions. Want to concatenate one character string value to another? Figure out the day of the week for a given date value? Get the number of seconds between midnight and a timestamp value? Overlay some portion of a character string with another string? Find a character string value with a pronunciation that is the same, or close to, that of another string? Serialize an XML value into a character string? All these capabilities, and MANY MORE, are built into DB2 (on all platforms -- mainframe and LUW). Have you checked out the built-in DB2 functions (scalar and aggregate) lately? If not, give 'em a look. Your best source for DB2 function information is the SQL Reference. You can find that manual, for DB2 for z/OS and for DB2 for LUW, at IBM's Web site (the built-in functions are documented in Volume 1 of the DB2 for LUW SQL Reference).
Beyond the built-in functions, you can move work into DB2 (and out of your application code) by way of CASE expressions, which provide for on-the-fly transformation of values returned by, or otherwise processed by, an SQL statements (e.g., "return X when the value in column COL1 is Y"), and with CAST specifications, which tell DB2 to change the data type of column values (e.g., from decimal to integer). Again, the SQL Reference is the place to go for more information.
I haven't even mentioned things like the INTERSECT and EXCEPT result-set-comparison operators, date/time arithmetic, and moving aggregates. DB2 SQL is very powerful and is becoming more so as time goes by. You can leverage that power to remove complexity that would otherwise be in your application program code.
Code re-use. This is a hot topic these days, and with good reason: to the extent that application logic can be encapsulated in a readily reusable form, programmer productivity increases (less reinvent-the-wheel work) and so does organizational agility (application creation and extension is accelerated). DB2 can be a big help here. There are all kinds of ways to push logic into the DB2 level of an application, and when that's done said logic is made available to any kind of program that accesses the database. A simple example of logic-in-DB2 is DB2-defined referential integrity. Why burden programmers with the task of ensuring that data values in a column of a "child" table always have a corresponding value in a "parent" table column, when DB2 can do that? And, when that's implemented at the DB2 level then it's there for the benefit of ALL DB2-accessing programs. Ditto table check constraints, which can ensure, among other things, that values to be inserted into a column must exist within a certain range of values.
Triggers are another way to push logic into the DB2 level of an application infrastructure. These can be used to enforce business rules for insert operations (rules that might be too complex to implement with table check constraints), to automatically maintain values in denormalized database tables ("if column X in table A is updated by a program, perform the same update for column Y in the matching row of table B"), to make otherwise read-only views updateable (this with INSTEAD OF triggers), and more. User-defined functions (UDFs) make certain routines (e.g., to perform certain data transformation operations) available to any program that can issue an SQL statement, and the same is true of DB2 stored procedures (these can be more sophisticated than user-defined functions, and they are invoked via the SQL statement CALL versus being referenced in SELECT statements as are UDFs).
The more data-centric logic is implemented in the DB2 database, the more you as a programmer can do what delivers the greatest value to your employer: writing code that directly addresses the business functionality needs of the organization. If you have some data-access logic that you think could be broadly applicable in your enterprise, talk to a DB2 DBA and see about getting that deployed in the database layer of the application system. A lot of your colleagues could end up benefiting from that move, as their programs will also be able to utilize the DB2-implemented capability.
Now, you've probably heard it said that there's an exception to every rule, and in closing I'll mention one such exception to my "let DB2 do what it can" rule. This exception has to do with sorting result set rows. Sometimes, in a decision support application environment, a user will want the capability to sort data rows returned from DB2 by whatever field he chooses. Having DB2 do that initial sort is the right move, but if the user wants to sort the same result set by a different field, you might consider doing that at the user workstation level (or maybe at the application server level). This might be good for efficiency if the result set is not particularly large (maybe a few hundred rows or less) but a lot of host resources are needed to generate the result set (sometimes a lot of data may be scanned and aggregated to build a small result set). In that case, you might not want to send the query back to DB2 with just a different ORDER BY specification -- maybe you just want to re-sort the 20 or 50 or 100 (or whatever) rows at the client end of things. The real work here was generating the result set. A simple re-sort might best be done locally with respect to the end user (on the other hand, if the query in question is very fast-running and the result set is rather small, having DB2 re-order the rows at the user's request is probably no big deal).
So, with the occasional exception here and there, you really are doing the right thing by letting DB2 do all that it can do with SQL (and remember that some of this logic- and functionality-implementing SQL -- examples include the creation of triggers and UDFs, and the altering of tables to include check constraints or referential integrity rules -- is in a DBA's domain, so get help there when you need it). Your programs are likely to be more efficient, performance will probably be more consistent over time, the code you have to write will be simplified, and your organization overall will benefit from the accessibility and re-usability of capabilities built into your DB2 database. A lot to like, there.
Tuesday, May 24, 2011
Some Nuggets from IDUG in the OC, Part 2
Following up on the entry I posted last week, here are some more items of information picked up in sessions I attended during the IDUG 2011 North American DB2 Tech Conference, put on by the International DB2 Users Group earlier this month in Anaheim, California (in Orange County, aka "the OC"):
IBM Distinguished Engineer John Campbell delivered a presentation on DB2 10 for z/OS migration and early user experiences that was (as usual where John's concerned) full of useful, actionable content. One of the first points made during the session had to do with hash access, a new (with DB2 10) way of organizing rows in a table that can provide, under the right circumstances, super-efficient access to data. John told attendees that the "sweet spot" for hash-organization of data -- the set of data access scenarios in which hash organization would be the right choice for a table -- is smaller than he'd originally anticipated. I was pleased to hear that note of caution, as I feel that some folks have gotten a little carried away with the notion of accessing data rows via a hash key. It's something best used in a targeted way, versus generally. The hash route could be good for tables for which reads well outnumber inserts, and for which read access is dominated by single-row retrieval using a unique search argument (that would be the table's hash key column, or column group).
John also talked about the increased use of real storage (i.e., server memory) that should be expected in a DB2 10 environment relative to prior-release DB2 systems. He mentioned that DB2 10 could require 10-30% more real storage "to stand still," and more than that if people get aggressive with memory-using features such as high-performance DBATs (about which I blogged in an entry posted last month). In a lot of environments, this shouldn't be a concern, as I've seen plenty of production z/OS systems with tens of gigabytes of memory and demand paging rates in the low single digits per second; however, if you're running with DB2 V8 or DB2 9 and you're kind of tight on memory in your production z/OS LPAR (and I'd say that you are if the demand paging rate is near or more than 10 per second), consider adding memory to that LPAR prior to migrating to DB2 10 (and John pointed out that the cost of memory was substantially reduced when IBM rolled out the z196 mainframes).
John mentioned that while performance improvements (referring to CPU time) in the range of 5-10% should be expected for applications' in-DB2 processing in a DB2 10 environment (assuming that packages are rebound on the DB2 10 system), some "skinny packages" (packages with very short-running SQL statements and/or that issue very frequent commits) bound with RELEASE(COMMIT) were seen to have somewhat worse CPU efficiency in a DB2 10 environment as compared to prior-release systems. John pointed to a recently available fix (the one for APAR PM31614) that addresses this situation.
DB2 10's support of much larger numbers of concurrently active threads got prominent mention in John's presentation. Some threads, he mentioned, use more virtual storage than others, but John indicated that he's pretty confident that DB2 10 sites should be able to support at least 2500 to 3000 concurrently active threads per subsystem.
DB2 users should expect that exploitation of new features will increasingly require the use of universal table spaces, which were introduced with DB2 9. John pointed out that universal table spaces are a prerequisite for inline LOBs (a potentially major performance boost for applications that read and/or insert mostly small LOBs), the "currently committed" locking behavior (whereby data readers don't wait for the release of X-locks held for inserts or deletes of qualifying rows), and the aforementioned hash organization of tables. Fortunately, DB2 10 provides a means of migrating existing simple, segmented, and "classic" partitioned table spaces to universal table spaces without the need for an unload/drop/re-create/re-load sequence (this is done by way of ALTER TABLESPACE followed by an online REORG).
John brought up a change in the behavior of the CHAR function in a DB2 10 environment, when input to the function is decimal data (among other things, values returned by the function are not padded to the left with zeros). He then informed attendees that the fix for APAR PM29124 will restore pre-DB2 10 behavior for the CHAR function operating on decimal data.
Near the end of his presentation, John talked about preparing for DB2 10 in a DB2 Connect sense. He mentioned that DB2 10 requires that DB2 clients (referring to DB2 Connect or the IBM Data Server Drivers) be at least at the V9.1 fix pack 1 level. Several new DB2 10 functions require that DB2 clients be at the V9.7 fix pack 3A level or higher.
IBM's Beth Hamel delivered a session on data warehousing and business intelligence on the mainframe DB2 platform. She pointed out that two of the trends that are driving growth in data warehousing on System z are consolidation (mainframe systems are very highly scalable) and the rise of "transactional analytics" (these high-volume, quick-running queries can run concurrently with more complex, longer-running BI tasks in a mainframe DB2 system thanks to z/OS's advanced Workload Manager).
Beth also noted that a very large amount of the source data that goes into data warehouses comes from mainframe systems, and she said that organizations are looking to get closer to this source data by locating their data warehouses on mainframes. Also boosting System z activity in the BI space is the fact that data warehouses are increasingly seen by businesses as being mission critical. That leads to a greater emphasis on availability -- long a bedrock strength of the mainframe platform (and that high availability story gets even better when several DB2 for z/OS systems function as a data sharing group on a Parallel Sysplex shared-data mainframe cluster).
In addition to delivering new BI-enabling technology for the mainframe platform, IBM has made moves in the areas of product pricing and packaging that can help organizations to get up and running with DB2-based data warehouses on System z in less time and at a lower cost versus a piece-by-piece implementation. Beth pointed to the InfoSphere Warehouse on System z offering, which provides cubing services to accelerate OLAP applications, SQL-based ETL functionality, and more, all managed by way of an easy-to-use interface. Beth told attendees that the cost of InfoSphere Warehouse on System z is way below that of the offering's individual components, were these to be acquired separately.
In wrapping up her presentation, Beth talked about Version 2 of the IBM Smart Analytics Optimizer, a query accelerator that attaches to a mainframe DB2 system and can deliver eye-popping performance for formerly long-running data retrieval tasks. ISAO V2 will take advantage of Netezza technology (Netezza was acquired by IBM last year) to expand the range of queries that can be processed by the ISAO and to significantly boost the system's data capacity. Beth said that she expects the beta program for ISAO V2 to begin in the third quarter of this year.
That's it for this collection of IDUG nuggets. I'll wrap up this series of posts with a Part 3 entry in a few days.
IBM Distinguished Engineer John Campbell delivered a presentation on DB2 10 for z/OS migration and early user experiences that was (as usual where John's concerned) full of useful, actionable content. One of the first points made during the session had to do with hash access, a new (with DB2 10) way of organizing rows in a table that can provide, under the right circumstances, super-efficient access to data. John told attendees that the "sweet spot" for hash-organization of data -- the set of data access scenarios in which hash organization would be the right choice for a table -- is smaller than he'd originally anticipated. I was pleased to hear that note of caution, as I feel that some folks have gotten a little carried away with the notion of accessing data rows via a hash key. It's something best used in a targeted way, versus generally. The hash route could be good for tables for which reads well outnumber inserts, and for which read access is dominated by single-row retrieval using a unique search argument (that would be the table's hash key column, or column group).
John also talked about the increased use of real storage (i.e., server memory) that should be expected in a DB2 10 environment relative to prior-release DB2 systems. He mentioned that DB2 10 could require 10-30% more real storage "to stand still," and more than that if people get aggressive with memory-using features such as high-performance DBATs (about which I blogged in an entry posted last month). In a lot of environments, this shouldn't be a concern, as I've seen plenty of production z/OS systems with tens of gigabytes of memory and demand paging rates in the low single digits per second; however, if you're running with DB2 V8 or DB2 9 and you're kind of tight on memory in your production z/OS LPAR (and I'd say that you are if the demand paging rate is near or more than 10 per second), consider adding memory to that LPAR prior to migrating to DB2 10 (and John pointed out that the cost of memory was substantially reduced when IBM rolled out the z196 mainframes).
John mentioned that while performance improvements (referring to CPU time) in the range of 5-10% should be expected for applications' in-DB2 processing in a DB2 10 environment (assuming that packages are rebound on the DB2 10 system), some "skinny packages" (packages with very short-running SQL statements and/or that issue very frequent commits) bound with RELEASE(COMMIT) were seen to have somewhat worse CPU efficiency in a DB2 10 environment as compared to prior-release systems. John pointed to a recently available fix (the one for APAR PM31614) that addresses this situation.
DB2 10's support of much larger numbers of concurrently active threads got prominent mention in John's presentation. Some threads, he mentioned, use more virtual storage than others, but John indicated that he's pretty confident that DB2 10 sites should be able to support at least 2500 to 3000 concurrently active threads per subsystem.
DB2 users should expect that exploitation of new features will increasingly require the use of universal table spaces, which were introduced with DB2 9. John pointed out that universal table spaces are a prerequisite for inline LOBs (a potentially major performance boost for applications that read and/or insert mostly small LOBs), the "currently committed" locking behavior (whereby data readers don't wait for the release of X-locks held for inserts or deletes of qualifying rows), and the aforementioned hash organization of tables. Fortunately, DB2 10 provides a means of migrating existing simple, segmented, and "classic" partitioned table spaces to universal table spaces without the need for an unload/drop/re-create/re-load sequence (this is done by way of ALTER TABLESPACE followed by an online REORG).
John brought up a change in the behavior of the CHAR function in a DB2 10 environment, when input to the function is decimal data (among other things, values returned by the function are not padded to the left with zeros). He then informed attendees that the fix for APAR PM29124 will restore pre-DB2 10 behavior for the CHAR function operating on decimal data.
Near the end of his presentation, John talked about preparing for DB2 10 in a DB2 Connect sense. He mentioned that DB2 10 requires that DB2 clients (referring to DB2 Connect or the IBM Data Server Drivers) be at least at the V9.1 fix pack 1 level. Several new DB2 10 functions require that DB2 clients be at the V9.7 fix pack 3A level or higher.
IBM's Beth Hamel delivered a session on data warehousing and business intelligence on the mainframe DB2 platform. She pointed out that two of the trends that are driving growth in data warehousing on System z are consolidation (mainframe systems are very highly scalable) and the rise of "transactional analytics" (these high-volume, quick-running queries can run concurrently with more complex, longer-running BI tasks in a mainframe DB2 system thanks to z/OS's advanced Workload Manager).
Beth also noted that a very large amount of the source data that goes into data warehouses comes from mainframe systems, and she said that organizations are looking to get closer to this source data by locating their data warehouses on mainframes. Also boosting System z activity in the BI space is the fact that data warehouses are increasingly seen by businesses as being mission critical. That leads to a greater emphasis on availability -- long a bedrock strength of the mainframe platform (and that high availability story gets even better when several DB2 for z/OS systems function as a data sharing group on a Parallel Sysplex shared-data mainframe cluster).
In addition to delivering new BI-enabling technology for the mainframe platform, IBM has made moves in the areas of product pricing and packaging that can help organizations to get up and running with DB2-based data warehouses on System z in less time and at a lower cost versus a piece-by-piece implementation. Beth pointed to the InfoSphere Warehouse on System z offering, which provides cubing services to accelerate OLAP applications, SQL-based ETL functionality, and more, all managed by way of an easy-to-use interface. Beth told attendees that the cost of InfoSphere Warehouse on System z is way below that of the offering's individual components, were these to be acquired separately.
In wrapping up her presentation, Beth talked about Version 2 of the IBM Smart Analytics Optimizer, a query accelerator that attaches to a mainframe DB2 system and can deliver eye-popping performance for formerly long-running data retrieval tasks. ISAO V2 will take advantage of Netezza technology (Netezza was acquired by IBM last year) to expand the range of queries that can be processed by the ISAO and to significantly boost the system's data capacity. Beth said that she expects the beta program for ISAO V2 to begin in the third quarter of this year.
That's it for this collection of IDUG nuggets. I'll wrap up this series of posts with a Part 3 entry in a few days.
Monday, May 16, 2011
Some Nuggets from IDUG in the OC, Part 1
That's OC as in Orange County, California, where you'll find Anaheim, site of the 2011 International DB2 Users Group North American Tech Conference. The conference wrapped up on May 6, and herein I have items of information gleaned from some of the sessions I attended (posting delayed by a crazy-busy week following the IDUG event). In a few days I'll provide more of these nuggets in a Part 2 entry.
Gerald Hodge of HLS Technologies delivered a presentation on the plan management feature introduced with DB2 9 for z/OS. This capability enables you to rebind a package whilst retaining the previous version (and, optionally, the "original" version), making it easy to switch to a saved version, in case of performance regression, by issuing a REBIND PACKAGE command with the SWITCH(PREVIOUS) option (or SWITCH(ORIGINAL), to restore the original version of the package). The purpose of the plan management feature is, as some have said, to "take the fear out of rebinding." Gerald pointed out that rebinding all packages is getting to be a more and more important part of the DB2 for z/OS migration process, as performance gains -- and this is particularly true of DB2 10 for z/OS -- depend increasingly on regeneration of packages. He stressed that realizing performance gains in a DB2 10 system (especially in the area of CPU efficiency) is NOT just a matter of getting a different access path. In fact, performance improvements are expected for most packages when rebound in a DB2 10 environment (and we're talking about Conversion Mode here) even when access paths for the packages' SQL statements don't change. The packages generated in a DB2 10 system via REBIND will feature internal enhancements (some pertaining to column handling, others to predicate evaluation) that should result in DB2 doing the same thing better (if access paths are as they were in the previous release). Package regeneration is also required to get the bulk of thread-related virtual storage up above the 2 GB "bar" in the DB2 database services address space (aka DBM1), and THAT is the key to DB2 10's ability to support 5 to 10 times the number of concurrently active threads versus previous releases of the product.
Gerald reminded session attendees that packages last bound prior to DB2 for z/OS Version 6 will be automatically rebound when you get to DB2 10 (that's if the ABIND parameter in ZPARM is set to the default value of YES or to COEXIST; otherwise, an attempt to execute such a package will result in a -908 SQL error code), and he recommended getting such old packages rebound in the "come from" DB2 environment (be that DB2 V8 or DB2 9) BEFORE going to DB2 10. Want to see if you have packages that were last bound prior to DB2 V6? Run the DB2 pre-migration "check-out" job on your system. This job, DSNTIJPM, ships with DB2 10. You can also get the DB2 V8 and DB2 9 versions of the job (DSNTIJP8 and DSNTIJP9, respectively) by way of the fix for APAR PM04968.
Bryan Paulsen of John Deere talked about his company's experience with DB2 10. Bryan told session attendees that Deere's DB2 10 migration and fallback testing went "flawlessly." He also spoke of old DB2 functionality that is going away with DB2 10 -- in particular, the private protocol used for mainframe-DB2-to-mainframe-DB2 client-server communication. DRDA has long been the preferred protocol for DB2 distributed database processing (for all platforms on which DB2 runs -- not just the mainframe), but at some shops there are still packages that utilize private protocol. Bryan pointed people to an APAR, PK64045, that provides, for DB2 V8 and V9 users, several tools that can facilitate the identification of private protocol-using packages and the conversion of these to use the DRDA protocol. He also mentioned APAR PK92339, which introduces a new ZPARM that can be used to disable private protocol at the DB2 subsystem level. Bryan said that Deere found this private protocol disablement capability to be a very useful means of "smoking out" private-protocol-using programs prior to the migration to DB2 10.
Bryan noted that DBRMs bound into plans (versus packages) is another old piece of functionality that is gone in a DB2 10 environment. If DB2 10 encounters a DBRM that is bound directly into a plan, it will create a corresponding package, but Bryan suggested that people do these conversions themselves before going to DB2 10. He briefly described APAR PK62876, which delivers a new REBIND PLAN option that can be used to convert DBRMs bound directly into a plan into packages, and then to convert the plan to use a PKLIST that will include the collection into which the new packages (corresponding to the DBRMs) were bound.
Moving on, Bryan said that Deere had successfully tested with 3000 concurrently active threads on a DB2 subsystem. He noted that Deere normally runs with 450 concurrently active threads for a subsystem, so this test result is in keeping with the expectation that DB2 10 will support 5 to 10 times more concurrently active threads versus a DB2 V8 or V9 system.
Bryan mentioned that he likes the new DB2 10 catalog table SYSPACKCOPY, which makes it easier to track the status of previous copies of a package that are maintained by way of the previously mentioned plan management functionality of DB2 (which allows retention of, and an easy "switch to," the immediate previous and -- optionally -- the "original" copy of a given package).
DB2 10 online schema enhancements -- a further expansion of changes that can be effected for a database object without the need to drop and recreate that object -- were another of the new release capabilities successfully tested by Deere. Bryan said that Deere changed the DSSIZE value for a partition-by-growth universal table space, and changed page sizes for table spaces and indexes, using the new ALTER-then-REORG process introduced with DB2 10.
Sometimes, people will see a DB2 online REORG job fail in the switch phase (the last phase before clean-up) because a thread holds a read claim on the table space or partition being REORGed. DB2 10 introduced a new ZPARM parameter, LRDRTHLD, that can help in identifying processes that hold read claims for extended periods of time (read claims are released at commit time, but sometimes read-only applications do not issue commits in a timely manner). Bryan said that Deere successfully tested this new functionality, which will cause DB2 to write a trace record when the threshold is hit, using the default LRDRTHLD value of 10 minutes.
Bryan concluded his presentation with brief descriptions of some relatively new DB2 10 APARs, including PM27811, which allows for inlining of LOB values in the skeleton package table (SPT01) in the DB2 10 directory (as part of the DB2 10 enable new function mode process, SPT01 is converted to a partition-by-growth universal table space, with package information stored as LOB values). LOB inlining should improve SPT01 access performance and reduce disk space requirements for the tablespace (the latter because a LOB tablespace cannot be compressed, but the LOB values inlined in a base table can be compressed when compression is used for the corresponding table space). [Note: APAR PM27073 enables one to change the LOB inline length used for SPT01.]
Terry Berman of DST Systems also discussed DB2 10 features from a user's perspective. He started out with a positive review of the catalog restructuring accomplished as part of the DB2 10 enable new function mode (ENFM) process. In particular, SYSDBASE undergoes big changes: each of the 14 tables formerly in that table space goes into a partition-by-growth table space (a PBG table space, being a universal table space, contains one and only one table). Tests run at DST showed that the catalog structure changes greatly improved concurrency for DDL and BIND operations (Terry said that they successfully tested 20 concurrent BINDs).
One of the nice features delivered with DB2 9 for z/OS was the LASTUSED column of the SYSINDEXSPACESTATS catalog table -- a BIG help when it comes to identifying indexes that are not helping performance and are candidates for dropping (fewer indexes means better performance for INSERT and DELETE operations, and for UPDATEs of indexed columns, as well as savings with respect to disk space consumption). Terry gave a thumbs up to the introduction, with DB2 10, of LASTUSED in the SYSPACKAGE and SYSPLAN catalog tables, saying that this information facilitates identification of obsolete plans and packages. The new LASTUSED column values are maintained in DB2 10 conversion mode and are updated once per day.
Also on the topic of new DB2 10 catalog columns, Terry said that he was pleased to see read-activity metrics introduced to the real-time statistics tables in the catalog. He specifically mentioned the usefulness of two new SYSTABLESPACESTATS columns: REORGSCANACCESS, which records data accesses for a table space since the last REORG or LOAD REPLACE of the object (or since the object was created, if it hasn't been subsequently REORGed or LOAD REPLACEd), and REORGCLUSTERSENS, which shows the number of times that data in a table space was read by SQL statements that are sensitive to the clustering sequence of data in the table space.
Terry told session attendees that their DB2 EXPLAIN really ought to be in Unicode format in the DB2 10 environment (APAR PK85068 can help with the conversion of EBCDIC EXPLAIN tables to Unicode). Terry also pointed out that the number of PLAN_TABLE columns continues to grow: the DB2 10-format PLAN_TABLE has 64 columns -- up from 59 columns for the DB2 9 format and 58 for the DB2 V8 format (PK85068 also helps in getting EXPLAIN tables into your current release format).
Terry talked up the access path repository introduced with DB2 10, pointing out that it can be used to (among other things) set various optimization options, such as REOPT, at an individual SQL statement level, versus the package-level granularity of previous DB2 releases.
Terry concluded his presentation with information related to DB2 instrumentation. He noted that compression of SMF trace records works very well: DST saw 75.8% compression with CPU overhead that did not exceed 1% (APAR PM27872 provides a sample SMF decompression program). Terry also said that he really likes the inclusion of statement ID information in DB2 10 messages, which -- thanks to the new STMT_ID columns in the SYSPACKSTMT catalog table and the DSN_STATEMENT_CACHE_TABLE, makes it much easier to tie error situations to SQL statements in a DB2 10 system. Also getting mention was the separation (Terry: "Finally") of lock and latch times in DB2 accounting trace data, the new IFCID 359 trace record (index page split activity), IFCID 361 (auditing the DB2 "superusers" in the system), and IFCID 401, which provides statement-level metrics with a lower CPU overhead versus previous DB2 releases (Terry pointed out that getting the IFCID 401 information requires that packages be bound or rebound in a DB2 10 new function mode environment).
That's all for now. As I mentioned up top, more to come in a few days.
Gerald Hodge of HLS Technologies delivered a presentation on the plan management feature introduced with DB2 9 for z/OS. This capability enables you to rebind a package whilst retaining the previous version (and, optionally, the "original" version), making it easy to switch to a saved version, in case of performance regression, by issuing a REBIND PACKAGE command with the SWITCH(PREVIOUS) option (or SWITCH(ORIGINAL), to restore the original version of the package). The purpose of the plan management feature is, as some have said, to "take the fear out of rebinding." Gerald pointed out that rebinding all packages is getting to be a more and more important part of the DB2 for z/OS migration process, as performance gains -- and this is particularly true of DB2 10 for z/OS -- depend increasingly on regeneration of packages. He stressed that realizing performance gains in a DB2 10 system (especially in the area of CPU efficiency) is NOT just a matter of getting a different access path. In fact, performance improvements are expected for most packages when rebound in a DB2 10 environment (and we're talking about Conversion Mode here) even when access paths for the packages' SQL statements don't change. The packages generated in a DB2 10 system via REBIND will feature internal enhancements (some pertaining to column handling, others to predicate evaluation) that should result in DB2 doing the same thing better (if access paths are as they were in the previous release). Package regeneration is also required to get the bulk of thread-related virtual storage up above the 2 GB "bar" in the DB2 database services address space (aka DBM1), and THAT is the key to DB2 10's ability to support 5 to 10 times the number of concurrently active threads versus previous releases of the product.
Gerald reminded session attendees that packages last bound prior to DB2 for z/OS Version 6 will be automatically rebound when you get to DB2 10 (that's if the ABIND parameter in ZPARM is set to the default value of YES or to COEXIST; otherwise, an attempt to execute such a package will result in a -908 SQL error code), and he recommended getting such old packages rebound in the "come from" DB2 environment (be that DB2 V8 or DB2 9) BEFORE going to DB2 10. Want to see if you have packages that were last bound prior to DB2 V6? Run the DB2 pre-migration "check-out" job on your system. This job, DSNTIJPM, ships with DB2 10. You can also get the DB2 V8 and DB2 9 versions of the job (DSNTIJP8 and DSNTIJP9, respectively) by way of the fix for APAR PM04968.
Bryan Paulsen of John Deere talked about his company's experience with DB2 10. Bryan told session attendees that Deere's DB2 10 migration and fallback testing went "flawlessly." He also spoke of old DB2 functionality that is going away with DB2 10 -- in particular, the private protocol used for mainframe-DB2-to-mainframe-DB2 client-server communication. DRDA has long been the preferred protocol for DB2 distributed database processing (for all platforms on which DB2 runs -- not just the mainframe), but at some shops there are still packages that utilize private protocol. Bryan pointed people to an APAR, PK64045, that provides, for DB2 V8 and V9 users, several tools that can facilitate the identification of private protocol-using packages and the conversion of these to use the DRDA protocol. He also mentioned APAR PK92339, which introduces a new ZPARM that can be used to disable private protocol at the DB2 subsystem level. Bryan said that Deere found this private protocol disablement capability to be a very useful means of "smoking out" private-protocol-using programs prior to the migration to DB2 10.
Bryan noted that DBRMs bound into plans (versus packages) is another old piece of functionality that is gone in a DB2 10 environment. If DB2 10 encounters a DBRM that is bound directly into a plan, it will create a corresponding package, but Bryan suggested that people do these conversions themselves before going to DB2 10. He briefly described APAR PK62876, which delivers a new REBIND PLAN option that can be used to convert DBRMs bound directly into a plan into packages, and then to convert the plan to use a PKLIST that will include the collection into which the new packages (corresponding to the DBRMs) were bound.
Moving on, Bryan said that Deere had successfully tested with 3000 concurrently active threads on a DB2 subsystem. He noted that Deere normally runs with 450 concurrently active threads for a subsystem, so this test result is in keeping with the expectation that DB2 10 will support 5 to 10 times more concurrently active threads versus a DB2 V8 or V9 system.
Bryan mentioned that he likes the new DB2 10 catalog table SYSPACKCOPY, which makes it easier to track the status of previous copies of a package that are maintained by way of the previously mentioned plan management functionality of DB2 (which allows retention of, and an easy "switch to," the immediate previous and -- optionally -- the "original" copy of a given package).
DB2 10 online schema enhancements -- a further expansion of changes that can be effected for a database object without the need to drop and recreate that object -- were another of the new release capabilities successfully tested by Deere. Bryan said that Deere changed the DSSIZE value for a partition-by-growth universal table space, and changed page sizes for table spaces and indexes, using the new ALTER-then-REORG process introduced with DB2 10.
Sometimes, people will see a DB2 online REORG job fail in the switch phase (the last phase before clean-up) because a thread holds a read claim on the table space or partition being REORGed. DB2 10 introduced a new ZPARM parameter, LRDRTHLD, that can help in identifying processes that hold read claims for extended periods of time (read claims are released at commit time, but sometimes read-only applications do not issue commits in a timely manner). Bryan said that Deere successfully tested this new functionality, which will cause DB2 to write a trace record when the threshold is hit, using the default LRDRTHLD value of 10 minutes.
Bryan concluded his presentation with brief descriptions of some relatively new DB2 10 APARs, including PM27811, which allows for inlining of LOB values in the skeleton package table (SPT01) in the DB2 10 directory (as part of the DB2 10 enable new function mode process, SPT01 is converted to a partition-by-growth universal table space, with package information stored as LOB values). LOB inlining should improve SPT01 access performance and reduce disk space requirements for the tablespace (the latter because a LOB tablespace cannot be compressed, but the LOB values inlined in a base table can be compressed when compression is used for the corresponding table space). [Note: APAR PM27073 enables one to change the LOB inline length used for SPT01.]
Terry Berman of DST Systems also discussed DB2 10 features from a user's perspective. He started out with a positive review of the catalog restructuring accomplished as part of the DB2 10 enable new function mode (ENFM) process. In particular, SYSDBASE undergoes big changes: each of the 14 tables formerly in that table space goes into a partition-by-growth table space (a PBG table space, being a universal table space, contains one and only one table). Tests run at DST showed that the catalog structure changes greatly improved concurrency for DDL and BIND operations (Terry said that they successfully tested 20 concurrent BINDs).
One of the nice features delivered with DB2 9 for z/OS was the LASTUSED column of the SYSINDEXSPACESTATS catalog table -- a BIG help when it comes to identifying indexes that are not helping performance and are candidates for dropping (fewer indexes means better performance for INSERT and DELETE operations, and for UPDATEs of indexed columns, as well as savings with respect to disk space consumption). Terry gave a thumbs up to the introduction, with DB2 10, of LASTUSED in the SYSPACKAGE and SYSPLAN catalog tables, saying that this information facilitates identification of obsolete plans and packages. The new LASTUSED column values are maintained in DB2 10 conversion mode and are updated once per day.
Also on the topic of new DB2 10 catalog columns, Terry said that he was pleased to see read-activity metrics introduced to the real-time statistics tables in the catalog. He specifically mentioned the usefulness of two new SYSTABLESPACESTATS columns: REORGSCANACCESS, which records data accesses for a table space since the last REORG or LOAD REPLACE of the object (or since the object was created, if it hasn't been subsequently REORGed or LOAD REPLACEd), and REORGCLUSTERSENS, which shows the number of times that data in a table space was read by SQL statements that are sensitive to the clustering sequence of data in the table space.
Terry told session attendees that their DB2 EXPLAIN really ought to be in Unicode format in the DB2 10 environment (APAR PK85068 can help with the conversion of EBCDIC EXPLAIN tables to Unicode). Terry also pointed out that the number of PLAN_TABLE columns continues to grow: the DB2 10-format PLAN_TABLE has 64 columns -- up from 59 columns for the DB2 9 format and 58 for the DB2 V8 format (PK85068 also helps in getting EXPLAIN tables into your current release format).
Terry talked up the access path repository introduced with DB2 10, pointing out that it can be used to (among other things) set various optimization options, such as REOPT, at an individual SQL statement level, versus the package-level granularity of previous DB2 releases.
Terry concluded his presentation with information related to DB2 instrumentation. He noted that compression of SMF trace records works very well: DST saw 75.8% compression with CPU overhead that did not exceed 1% (APAR PM27872 provides a sample SMF decompression program). Terry also said that he really likes the inclusion of statement ID information in DB2 10 messages, which -- thanks to the new STMT_ID columns in the SYSPACKSTMT catalog table and the DSN_STATEMENT_CACHE_TABLE, makes it much easier to tie error situations to SQL statements in a DB2 10 system. Also getting mention was the separation (Terry: "Finally") of lock and latch times in DB2 accounting trace data, the new IFCID 359 trace record (index page split activity), IFCID 361 (auditing the DB2 "superusers" in the system), and IFCID 401, which provides statement-level metrics with a lower CPU overhead versus previous DB2 releases (Terry pointed out that getting the IFCID 401 information requires that packages be bound or rebound in a DB2 10 new function mode environment).
That's all for now. As I mentioned up top, more to come in a few days.
Friday, April 22, 2011
You DO Let DB2 for z/OS Allocate Utility Sort Work Data Sets, Don't You?
Some of my blog posts are about DB2 things that are very new (my previous entry described the high-performance DBATs introduced with DB2 10 for z/OS), and some cover DB2 stuff that's been out there for a while. This entry is an example of the latter. Dynamic allocation of the sort work data sets used in the execution of mainframe DB2 utilities is something that goes back at least to DB2 Version 8, and a lot if DBAs have taken advantage of this feature to improve the reliability and performance of their utility jobs. At the same time, it seems that there are a number of DB2 for z/OS people who are kind of confused about dynamic allocation of utility sort work data sets, and are uncertain as to how they can leverage this product capability. Seeing as how dynamic allocation of the data sets in question (and not just dynamic allocation, but DB2-directed allocation -- more on that distinction momentarily) is highly recommended by IBM, my aim today is to clear things up for folks who could use some clarification. By the way, I want to give props to Christian Michel, one of the utilities developers at the IBM lab in Boeblingen, Germany, who helped me get my arms around the topic.
So, a little background: a number of DB2 utilities -- including LOAD, REORG, REBUILD INDEX, and RUNSTATS -- use DFSORT (an IBM product that's a feature of z/OS) to handle required sorting of data records (typically index keys). DFSORT of course needs some disk space to do this work, and some time ago you had to allocate those data sets via DD statements in the JCL of your utility jobs. SORTWKnn is the DD name that generally comes to mind first when one thinks of these data sets, but there are others: SW01WKnn, DATAWKnn, DA01WKnn, etc.). The problem with the JCL-directed allocation of these data sets was that you could have a utility job fail if the space available in the DFSORT temporary data sets were insufficient, or the job might not perform optimally if the wrong number of data sets were allocated (this could impede parallelization of sort operations).
To address these challenges, IBM provided new keywords for DFSORT-using utilities (again, I'm not sure if this was introduced with DB2 V8 or a prior release) that would enable DFSORT to dynamically allocate the temporary data sets that it needed to do the sorting required by a utility job. The new keywords were SORTDEVT, which indicates the device type to be used for data sets dynamically allocated by DFSORT, and SORTNUM, which can be used to specify the number of temporary data sets to be allocated by DFSORT for each sort operation performed in the execution of a DB2 utility (the value of SORTDEVT is usually set to a so-called "esoteric," which is a z/OS installation-defined group of devices, and the common specification is SYSDA, because that means magnetic disk storage and it's an esoteric that is almost always defined on a z/OS system).
Dynamic allocation of utility sort work data sets by DFSORT was a step in the right direction, but still the situation was less than ideal (SORTNUM might be set to a sub-optimal value, potentially impeding utility sort parallelization, or the estimate of space needed for sort work data sets might be substantially off due to inaccurate statistics in the DB2 catalog). To address these challenges, DB2 utilities were enhanced via APARs PK45916 (for DB2 Version 8) and PK41899 (for DB2 9) to enable them to dynamically allocate sort work data sets before invoking DFSORT (the enhancements delivered via these APARs are part of the base functionality of DB2 10). In doing this, the utilities would optimize the number of sort work data sets dynamically allocated for a job (thereby removing the need for a user-specified SORTNUM value). On top of that, sort work space requirements would be more accurately estimated utilizing information in the real-time statistics tables.
That's all good (REAL good), but some people were (and still are) uncertain as to what had to be done to realize the benefits of these important DB2 utility enhancements. That, as much as anything, is what I want to address in this entry. Here's the deal:
Finally: some people who have been accustomed to allocating sort work data sets via JCL DD statements might be concerned about loss of control over placement of these data sets in the disk subsystem if they go the dynamic allocation route. After all, SYSDA (again, the typical SORTDEVT specification) is usually very generic ("allocate these on magnetic disk devices"). What do people do to direct dynamically allocated sort work data sets to a particular set of disk volumes? My understanding is that the primary means to this end is the utilization of DFSMS Automatic Class Selection (ACS) routines. These routines would look for the DD names of the sort work data sets being dynamically allocated (as mentioned previously, these contain the string WKnn) and would direct those data sets to an SMS storage group that would be comprised of the volumes you want to be used for the "sort pool." Another option would be to define an esoteric that would include the sort pool volumes and then to use the name of this esoteric instead of SYSDA as the value of the SORTDEVT utility control card option.
OK, so all of this is old news to people who have been taking advantage of DB2-directed dynamic allocation of sort work data sets since the functionality was introduced back in 2008. I'm interested in reaching people who are still allocating DB2 utility sort work data sets the old way. If you're in that group, I'm telling you: the new way (let DB2 allocate the data sets) is the better way -- better for performance, and better for reliability. Give it a shot, if you haven't already.
So, a little background: a number of DB2 utilities -- including LOAD, REORG, REBUILD INDEX, and RUNSTATS -- use DFSORT (an IBM product that's a feature of z/OS) to handle required sorting of data records (typically index keys). DFSORT of course needs some disk space to do this work, and some time ago you had to allocate those data sets via DD statements in the JCL of your utility jobs. SORTWKnn is the DD name that generally comes to mind first when one thinks of these data sets, but there are others: SW01WKnn, DATAWKnn, DA01WKnn, etc.). The problem with the JCL-directed allocation of these data sets was that you could have a utility job fail if the space available in the DFSORT temporary data sets were insufficient, or the job might not perform optimally if the wrong number of data sets were allocated (this could impede parallelization of sort operations).
To address these challenges, IBM provided new keywords for DFSORT-using utilities (again, I'm not sure if this was introduced with DB2 V8 or a prior release) that would enable DFSORT to dynamically allocate the temporary data sets that it needed to do the sorting required by a utility job. The new keywords were SORTDEVT, which indicates the device type to be used for data sets dynamically allocated by DFSORT, and SORTNUM, which can be used to specify the number of temporary data sets to be allocated by DFSORT for each sort operation performed in the execution of a DB2 utility (the value of SORTDEVT is usually set to a so-called "esoteric," which is a z/OS installation-defined group of devices, and the common specification is SYSDA, because that means magnetic disk storage and it's an esoteric that is almost always defined on a z/OS system).
Dynamic allocation of utility sort work data sets by DFSORT was a step in the right direction, but still the situation was less than ideal (SORTNUM might be set to a sub-optimal value, potentially impeding utility sort parallelization, or the estimate of space needed for sort work data sets might be substantially off due to inaccurate statistics in the DB2 catalog). To address these challenges, DB2 utilities were enhanced via APARs PK45916 (for DB2 Version 8) and PK41899 (for DB2 9) to enable them to dynamically allocate sort work data sets before invoking DFSORT (the enhancements delivered via these APARs are part of the base functionality of DB2 10). In doing this, the utilities would optimize the number of sort work data sets dynamically allocated for a job (thereby removing the need for a user-specified SORTNUM value). On top of that, sort work space requirements would be more accurately estimated utilizing information in the real-time statistics tables.
That's all good (REAL good), but some people were (and still are) uncertain as to what had to be done to realize the benefits of these important DB2 utility enhancements. That, as much as anything, is what I want to address in this entry. Here's the deal:
- Make sure that the value of the DB2 ZPARM parameter UTSORTAL is set to YES (note that YES is the default setting for UTSORTAL in a DB2 10 system).
- Remove SORTNUM specifications from your utility control statements, or set the DB2 ZPARM parameter IGNSORTN to YES to have DB2 ignore any SORTNUM specifications found in utility control statements.
- Remove ALL of the sort-related DD statements from the JCL of your DB2 utility jobs -- that's anything with a WKnn string in it (SORTWKnn, SW01WKnn, DATAWKnn, DA01WKnn, STATWKnn, and ST01WKnn), along with UTPRINnn and DTPRINnn (exception: keep the UTPRINT and SORTDIAG DD statements in your JCL).
- Ensure that real-time statistics information is available to the DB2 utilities. If you are still on DB2 V8 or using DB2 9 in Conversion Mode, you need to create the real-time statistics tables if they have not already been defined on your system (starting with DB2 9 in New Function Mode, the real-time statistics tables are part of the DB2 catalog, and you don't have to be concerned with creating them). If you need to set up the real-time statistics tables in your DB2 environment, refer to Appendix I in the DB2 Version 8 Administration Guide (available in PDF form at this url: https://www-304.ibm.com/support/docview.wss?uid=swg27011659).
Finally: some people who have been accustomed to allocating sort work data sets via JCL DD statements might be concerned about loss of control over placement of these data sets in the disk subsystem if they go the dynamic allocation route. After all, SYSDA (again, the typical SORTDEVT specification) is usually very generic ("allocate these on magnetic disk devices"). What do people do to direct dynamically allocated sort work data sets to a particular set of disk volumes? My understanding is that the primary means to this end is the utilization of DFSMS Automatic Class Selection (ACS) routines. These routines would look for the DD names of the sort work data sets being dynamically allocated (as mentioned previously, these contain the string WKnn) and would direct those data sets to an SMS storage group that would be comprised of the volumes you want to be used for the "sort pool." Another option would be to define an esoteric that would include the sort pool volumes and then to use the name of this esoteric instead of SYSDA as the value of the SORTDEVT utility control card option.
OK, so all of this is old news to people who have been taking advantage of DB2-directed dynamic allocation of sort work data sets since the functionality was introduced back in 2008. I'm interested in reaching people who are still allocating DB2 utility sort work data sets the old way. If you're in that group, I'm telling you: the new way (let DB2 allocate the data sets) is the better way -- better for performance, and better for reliability. Give it a shot, if you haven't already.
Friday, April 1, 2011
DB2 10 for z/OS: What do You Know About High-Performance DBATs?
DB2 10 for z/OS, which became generally available this past October, is loaded with features and functions that can reduce the CPU cost of applications. One of my favorites is high-performance DBATs (DBATs, or database access threads, are associated with SQL statements that are issued by remote requesters using the DRDA protocol and processed via DB2's Distributed Data Facility). I'm big on high-performance DBATs because I'm big on mainframe DB2's use as a super-scalable, super-available, super-secure data server for applications that run on Java, .NET, and other application servers. For DB2 for z/OS to realize its full value-delivery potential in client-server computing environments, it has to provide to DBAs and to systems administrators the same performance management and tuning options that are available for local-to-DB2 applications (such as those that run in CICS regions). This has been happening for a period that stretches back a good 20 years or so. First we got static SQL for client-server applications (back when the DRDA protocol and package bind were introduced with DB2 Version 2 Release 3). With DB2 Version 4 we got stored procedures, a critical enhancement with respect to driving up the scalability and manageability of DB2-based client-server computing (stored procedures themselves have been substantially enhanced since their introduction, with features such as WLM-managed stored procedure address spaces and, more recently, native SQL procedures). Management of DB2 DDF transaction priority through WLM service classes provided a means of setting performance objectives and report classes that could be very granular, with transaction classification possible by way of all kinds of attributes including collection, package, stored procedure, client application name, and user ID.
All this is great, and plenty of organizations are running very high-volume OLTP workloads in mainframe DB2 client-server environments today. That said, there was one thing annoyingly missing from the DDF transaction-tuning tool kit: the ability to leverage the CPU efficiency benefits of packages bound with RELEASE(DEALLOCATE), used in combination with threads that persist through multiple transaction executions. That gap was filled very nicely by the introduction, with DB2 10 for z/OS, of high-performance DBATs.
Before getting into the particulars of high-performance DBATs, I want to do a little illustration by comparison. High-performance DBATs are conceptually similar to CICS-DB2 protected entry threads (with some important differences, which I'll cover momentarily). Like CICS-DB2 protected entry threads, high-performance DBATs persist through multiple transaction executions (typically, a non-protected CICS-DB2 thread will terminate when the transaction using that thread completes). This persistence, in and of itself, is not such a big deal in the DB2 DDF world. For one thing, thread creation and termination doesn't consume a whole lot of resource (the DB2 systems services address space, aka MSTR, handles this function, and you can see in a DB2 monitor statistics report that MSTR consumes relatively little CPU). More importantly, if you have the DSNZPARM parameter CMTSTAT set to INACTIVE (the default), DB2 DDF connections are disassociated from DB2 DDF threads, and the latter can be pooled and used to support a very large number of connections, so that DBAT re-use will typically be very high and the incidence of DBAT creation will typically be very low.
So, if the re-usability of high-performance DBATs is not the big news, what is? The big news is that high-performance DBATs allow you to realize the CPU efficiency benefit of the RELEASE(DEALLOCATE) option of BIND PACKAGE -- a benefit previously available only for packages associated with local-to-DB2 programs. Back to the CICS-DB2 protected entry thread analogy. The real bang for the buck there is the use of such threads for the execution of packages bound with RELEASE(DEALLOCATE). Why? Because, for a package so bound, certain resources, such as table space locks and EDM pool elements, will be retained until the thread used in the execution of the package is deallocated -- this as opposed to being released and then reacquired, over and over again, as a high-volume CICS-DB2 transaction is executed (and retention of table space locks is generally not a big deal, as these are usually intent locks, and intent locks do not conflict with each other). Think about it: suppose a CICS-DB2 transaction accesses data in four table spaces, and the package has several sections that are stored in the EDM pool (the place where packages -- the run-time structures created from binding programs with embedded static SQL -- are cached in memory). If the transaction program's package is bound with RELEASE(COMMIT), when the transaction executes it will get locks (again, almost certainly intent -- versus exclusive -- locks) on the four table spaces and space in the EDM pool for the required package sections, and when it completes (driving a DB2 commit) those tablespace locks and EDM pool elements will be released. If the same transaction is executed right after that first one, those same tablespace locks and EDM pool elements will be reacquired and then released again at transaction completion. All that acquisition and release of the same stuff adds to the CPU cost of the transaction.
If the CICS-DB2 transaction program's package is bound with RELEASE(DEALLOCATE), and IF the thread used to execute the package can persist through executions of the program (that's where CICS-DB2 protected entry threads come in), the tablespace locks and EDM pool elements acquired for the program will be retained until the thread is deallocated, and that will likely not happen until the transaction has been executed a LOT of times; so, RELEASE(DEALLOCATE) plus protected entry threads together deliver a nice CPU efficiency benefit for high-volume CICS-DB2 transactions.
What kept that benefit from being available for transactions that execute in a client-server DB2 environment? It wasn't thread persistence -- as mentioned, DBATs, once created, can be reused many, many times. The problem was the RELEASE(DEALLOCATE) part of the equation. See, for a long time (since DB2 V6, I believe), although you could bind a package executed by remote DRDA requesters with RELEASE(DEALLOCATE), DB2 would not honor that bind specification in a DDF setting -- if an execution of the package were associated with a DBAT (versus a local-to-DB2 program's thread), DB2 would handle the package as though it had been bound with RELEASE(COMMIT). Why? Because, as noted, DBATs can stick around for a long time, and having tablespace locks -- even intent locks -- held by such long-duration threads could interfere with certain database maintenance operations (utilities, in particular). Now that high-performance DBATs have made the scene, RELEASE(DEALLOCATE) can be honored by DB2 even for packages executed on behalf of remote requesters via DBATs.
I mentioned that there are some important differences between CICS-DB2 protected entry threads and high-performance DBATs. One of those key differences concerns the actions required to bring the two different kinds of threads into existence. To have CICS-DB2 protected entry threads, one has to use CICS resource definition online functionality (aka RDO) to define a DB2ENTRY resource with a PROTECTNUM (and a THREADLIMIT) value that is greater than zero. A high-performance DBAT, on the other hand, is instantiated when it is used in the execution of a package bound with RELEASE(DEALLOCATE) -- there is no set-up, per se, required in order to have high-performance DBATs in your DB2 system (aside from the need to set the MAXDBAT and CONDBAT DSNZPARM parameters to values that are greater than zero, and to have CMTSTAT in DSNZPARM set to INACTIVE -- but that's what you've probably done anyway if you do any DDF processing on your system). Let's say that a remote requester issues an SQL statement that causes a DB2 package bound with RELEASE(DEALLOCATE) to be executed. Here's what will happen:
Once the high-performance DBAT has been instantiated, it will be used to process 200 units of work and will then be terminated (if it is not terminated before as a result of hitting the idle thread timeout threshold). Auto-termination after 200 units of work is done to periodically release resources held by the high-performance DBAT. Even with that periodic break in the action, there will be times when you want resources acquired in the execution of packages to be released at commit, no matter how the packages are bound (perhaps during a period of time during which you want to run a number of utilities involving tablespaces targeted by SQL statements in the client-server packages). That can be easily accomplished thanks to a new command, -MODIFY DDF, that was introduced with DB2 10. If you issue -MODIFY DDF PKGREL(COMMIT), packages executed via DBATs will release resources at commit, no matter what RELEASE option was specified when the package was created (again, we're talking about tablespace locks and EDM pool elements -- retention of page and row locks is not affected by the RELEASE option of BIND PACKAGE). When this period of utility (for example processing is done, you can issue -MODIFY DDF PKGREL(BNDOPT) to have DB2 once again honor the RELEASE(DEALLOCATE) specification for packages so bound that are executed via DBATs.
Now, I implied previously that a high-performance DBAT, once instantiated, will be dedicated to the connection through which the request invoking the RELEASE(DEALLOCATE) package was issued. This is in fact the case. A high-performance DBAT does not go into the pool when it is freed up, to be usable by other connections to the DB2 subsystem. Instead, if the RELEASE(DEALLOCATE) package invocation came from a connection established by application server XYZ, the high-performance DBAT instantiated as a result of that request will persist to process 199 more units of work from application server XYZ; furthermore, there is no requirement that all -- or any -- of those 199 additional units of work be associated with packages bound with RELEASE(DEALLOCATE) -- a high-performance DBAT can be used for the execution of packages bound with RELEASE(COMMIT), too.
If you're using DB2 10 now, or you're planning your migration to this new DB2 release, I encourage you to think about taking advantage of high-performance DBATs to improve the CPU efficiency of your DDF workload. In other words, I want you to think about binding packages executed frequently via DBATs with RELEASE(DEALLOCATE); and, don't limit your consideration to packages associated with frequently executed programs that issue static SQL statements (though such packages, which could be related to DB2 stored procedures, can be excellent candidates for binding with RELEASE(DEALLOCATE)). You should also consider binding with RELEASE(DEALLOCATE) packages used by remote programs that issue dynamic SQL statements. Here, I have particularly in mind the packages used by the IBM Data Server Driver for JDBC and SQLJ. You can read more about binding these packages with RELEASE(DEALLOCATE) in the IBM "red book" titled "DB2 10 for z/OS Technical Overview" (see section 9.5).
The high-performance DBAT feature of DB2 10 is one of the latest in a long line of enhancements that have made DB2 for z/OS more and more valuable as a foundation for high-volume, transactional client-server applications. Go and rev up your DDF workload.
All this is great, and plenty of organizations are running very high-volume OLTP workloads in mainframe DB2 client-server environments today. That said, there was one thing annoyingly missing from the DDF transaction-tuning tool kit: the ability to leverage the CPU efficiency benefits of packages bound with RELEASE(DEALLOCATE), used in combination with threads that persist through multiple transaction executions. That gap was filled very nicely by the introduction, with DB2 10 for z/OS, of high-performance DBATs.
Before getting into the particulars of high-performance DBATs, I want to do a little illustration by comparison. High-performance DBATs are conceptually similar to CICS-DB2 protected entry threads (with some important differences, which I'll cover momentarily). Like CICS-DB2 protected entry threads, high-performance DBATs persist through multiple transaction executions (typically, a non-protected CICS-DB2 thread will terminate when the transaction using that thread completes). This persistence, in and of itself, is not such a big deal in the DB2 DDF world. For one thing, thread creation and termination doesn't consume a whole lot of resource (the DB2 systems services address space, aka MSTR, handles this function, and you can see in a DB2 monitor statistics report that MSTR consumes relatively little CPU). More importantly, if you have the DSNZPARM parameter CMTSTAT set to INACTIVE (the default), DB2 DDF connections are disassociated from DB2 DDF threads, and the latter can be pooled and used to support a very large number of connections, so that DBAT re-use will typically be very high and the incidence of DBAT creation will typically be very low.
So, if the re-usability of high-performance DBATs is not the big news, what is? The big news is that high-performance DBATs allow you to realize the CPU efficiency benefit of the RELEASE(DEALLOCATE) option of BIND PACKAGE -- a benefit previously available only for packages associated with local-to-DB2 programs. Back to the CICS-DB2 protected entry thread analogy. The real bang for the buck there is the use of such threads for the execution of packages bound with RELEASE(DEALLOCATE). Why? Because, for a package so bound, certain resources, such as table space locks and EDM pool elements, will be retained until the thread used in the execution of the package is deallocated -- this as opposed to being released and then reacquired, over and over again, as a high-volume CICS-DB2 transaction is executed (and retention of table space locks is generally not a big deal, as these are usually intent locks, and intent locks do not conflict with each other). Think about it: suppose a CICS-DB2 transaction accesses data in four table spaces, and the package has several sections that are stored in the EDM pool (the place where packages -- the run-time structures created from binding programs with embedded static SQL -- are cached in memory). If the transaction program's package is bound with RELEASE(COMMIT), when the transaction executes it will get locks (again, almost certainly intent -- versus exclusive -- locks) on the four table spaces and space in the EDM pool for the required package sections, and when it completes (driving a DB2 commit) those tablespace locks and EDM pool elements will be released. If the same transaction is executed right after that first one, those same tablespace locks and EDM pool elements will be reacquired and then released again at transaction completion. All that acquisition and release of the same stuff adds to the CPU cost of the transaction.
If the CICS-DB2 transaction program's package is bound with RELEASE(DEALLOCATE), and IF the thread used to execute the package can persist through executions of the program (that's where CICS-DB2 protected entry threads come in), the tablespace locks and EDM pool elements acquired for the program will be retained until the thread is deallocated, and that will likely not happen until the transaction has been executed a LOT of times; so, RELEASE(DEALLOCATE) plus protected entry threads together deliver a nice CPU efficiency benefit for high-volume CICS-DB2 transactions.
What kept that benefit from being available for transactions that execute in a client-server DB2 environment? It wasn't thread persistence -- as mentioned, DBATs, once created, can be reused many, many times. The problem was the RELEASE(DEALLOCATE) part of the equation. See, for a long time (since DB2 V6, I believe), although you could bind a package executed by remote DRDA requesters with RELEASE(DEALLOCATE), DB2 would not honor that bind specification in a DDF setting -- if an execution of the package were associated with a DBAT (versus a local-to-DB2 program's thread), DB2 would handle the package as though it had been bound with RELEASE(COMMIT). Why? Because, as noted, DBATs can stick around for a long time, and having tablespace locks -- even intent locks -- held by such long-duration threads could interfere with certain database maintenance operations (utilities, in particular). Now that high-performance DBATs have made the scene, RELEASE(DEALLOCATE) can be honored by DB2 even for packages executed on behalf of remote requesters via DBATs.
I mentioned that there are some important differences between CICS-DB2 protected entry threads and high-performance DBATs. One of those key differences concerns the actions required to bring the two different kinds of threads into existence. To have CICS-DB2 protected entry threads, one has to use CICS resource definition online functionality (aka RDO) to define a DB2ENTRY resource with a PROTECTNUM (and a THREADLIMIT) value that is greater than zero. A high-performance DBAT, on the other hand, is instantiated when it is used in the execution of a package bound with RELEASE(DEALLOCATE) -- there is no set-up, per se, required in order to have high-performance DBATs in your DB2 system (aside from the need to set the MAXDBAT and CONDBAT DSNZPARM parameters to values that are greater than zero, and to have CMTSTAT in DSNZPARM set to INACTIVE -- but that's what you've probably done anyway if you do any DDF processing on your system). Let's say that a remote requester issues an SQL statement that causes a DB2 package bound with RELEASE(DEALLOCATE) to be executed. Here's what will happen:
- If there is already a high-performance DBAT in existence and associated with this particular connection (more on that in a moment), and it's available, it will be used in the execution of the package.
- If there is not already a high-performance DBAT in existence for this connection, or if there is and it is tied up in the execution of another package, a "regular" DBAT in the pool will become a high-performance DBAT and will be used to execute the package (or a new DBAT will be created and will be a high-performance DBAT, if there are no available "regular" pooled DBATs).
Once the high-performance DBAT has been instantiated, it will be used to process 200 units of work and will then be terminated (if it is not terminated before as a result of hitting the idle thread timeout threshold). Auto-termination after 200 units of work is done to periodically release resources held by the high-performance DBAT. Even with that periodic break in the action, there will be times when you want resources acquired in the execution of packages to be released at commit, no matter how the packages are bound (perhaps during a period of time during which you want to run a number of utilities involving tablespaces targeted by SQL statements in the client-server packages). That can be easily accomplished thanks to a new command, -MODIFY DDF, that was introduced with DB2 10. If you issue -MODIFY DDF PKGREL(COMMIT), packages executed via DBATs will release resources at commit, no matter what RELEASE option was specified when the package was created (again, we're talking about tablespace locks and EDM pool elements -- retention of page and row locks is not affected by the RELEASE option of BIND PACKAGE). When this period of utility (for example processing is done, you can issue -MODIFY DDF PKGREL(BNDOPT) to have DB2 once again honor the RELEASE(DEALLOCATE) specification for packages so bound that are executed via DBATs.
Now, I implied previously that a high-performance DBAT, once instantiated, will be dedicated to the connection through which the request invoking the RELEASE(DEALLOCATE) package was issued. This is in fact the case. A high-performance DBAT does not go into the pool when it is freed up, to be usable by other connections to the DB2 subsystem. Instead, if the RELEASE(DEALLOCATE) package invocation came from a connection established by application server XYZ, the high-performance DBAT instantiated as a result of that request will persist to process 199 more units of work from application server XYZ; furthermore, there is no requirement that all -- or any -- of those 199 additional units of work be associated with packages bound with RELEASE(DEALLOCATE) -- a high-performance DBAT can be used for the execution of packages bound with RELEASE(COMMIT), too.
If you're using DB2 10 now, or you're planning your migration to this new DB2 release, I encourage you to think about taking advantage of high-performance DBATs to improve the CPU efficiency of your DDF workload. In other words, I want you to think about binding packages executed frequently via DBATs with RELEASE(DEALLOCATE); and, don't limit your consideration to packages associated with frequently executed programs that issue static SQL statements (though such packages, which could be related to DB2 stored procedures, can be excellent candidates for binding with RELEASE(DEALLOCATE)). You should also consider binding with RELEASE(DEALLOCATE) packages used by remote programs that issue dynamic SQL statements. Here, I have particularly in mind the packages used by the IBM Data Server Driver for JDBC and SQLJ. You can read more about binding these packages with RELEASE(DEALLOCATE) in the IBM "red book" titled "DB2 10 for z/OS Technical Overview" (see section 9.5).
The high-performance DBAT feature of DB2 10 is one of the latest in a long line of enhancements that have made DB2 for z/OS more and more valuable as a foundation for high-volume, transactional client-server applications. Go and rev up your DDF workload.
Sunday, March 13, 2011
Monitoring DB2 for z/OS: What's in YOUR Subsystem?
First of all, if your organization uses DB2 for z/OS, I hope that you have a DB2 monitor on the system. While several of the DB2 DISPLAY commands provide information that's useful for monitoring activity on a DB2 subsystem (a favorite of mine is -DISPLAY BUFFERPOOL(ACTIVE) DETAIL), for an in-depth view into what's going on you really want the capabilities of a monitor tool at your disposal. Multiple vendors provide these products -- IBM's offering is Tivoli OMEGAMON XE for DB2 Performance Monitor on z/OS (as that's a bit of a mouthful, I'll hereafter refer to it in this blog post as OMEGAMON for DB2).
I'll tell you something interesting that I've observed regarding the use of DB2 monitors out in the real world: LOTS of folks use only the online monitoring capability of whatever tool they have on their system. Online monitoring is definitely useful when it comes to checking out what's happening right now in a DB2 subsystem -- something you may need to do, and quickly, if a problem pops up. That said, when it comes to deep-dive analysis of the performance of a DB2 subsystem, nothing beats the REPORTS that a DB2 monitor can provide. Plenty of DB2 professionals have NEVER used the report-generation capabilities of their monitor product. That's a bummer, because there's so much good stuff in those reports, and they are fantastic for trend analysis. If you have never used your DB2 monitor to produce reports, do yourself a favor and figure out how to do that. It's not hard: check your monitor's report command reference (or batch reporting users guide -- different monitoring tools have differently-titled manuals), and you'll see JCL requirements (a key: point to the SMF data set that will provide the desired input to the monitor) and examples of SYSIN control statements (these specify report type, reporting time frame, and report interval, among other things). Run some of these reports, and check out the wealth of information therein.
I find that the two most useful DB2 monitor report types are the Accounting Report - Long and the Statistics Report - Long (that's what OMEGAMON for DB2 calls 'em -- for some other monitoring products these reports are referred to as Accounting Detail and Statistics Detail). Input to these reports are records generated by the standard DB2 accounting and statistics trace classes that most folks have running all the time (e.g., accounting trace classes 1, 2, and 3, plus classes 7 and 8 if you're interested in package-level accounting). I'll tell you about my favorite flavor of the Accounting Report - Long in just a moment, but first I want to mention a practice implemented at my shop when I worked in the IT department of a DB2-using organization: every day, an Accounting Report - Long and a Statistics Report - Long (each covering the previous days' 24 hours of activity) were "printed" to a data set on disk (more specifically, a GDG, which made it easy to keep a rolling X days of reports). 60 days of these reports were kept online, available for browsing via TSO/ISPF. Having these around was super for a couple of reasons: 1) if a problem situation started to crop up, we could look back over several weeks of data to see how things had been trending, and what might have changed; and 2) with the reports on-hand, we didn't have to keep 60 days of SMF records online (the reports summarize and greatly reduce these records).
Now, the Statistics Report - Long is great, and I may write more about that report in a future post, but for now I want to talk a little about the Accounting Report - Long (statistics reports show activity from the DB2 subsystem perspective, while accounting reports provide information from an application or workload point of view). An important specification when generating an Accounting Report - Long is the desired grouping of the accounting data. For an OMEGAMON for DB2 report, this data grouping is determined via the ORDER subcommand (this would be part of the SYSIN input to a report generation job). The default grouping for OMEGAMON for DB2 (and for other monitors I've seen) is primary authorization ID within plan name. I generally don't want data in the report grouped that way. What I most often like to see is a report generated with ORDER(CONNTYPE) (using OMEGAMON for DB2 lingo -- the terminology might be slightly different for other monitors). With ORDER(CONNTYPE), your Accounting Report - Long will have several sections, with one report component for each DB2 connection type. In other words, one part of the report will show all the CICS-DB2 activity on the subsystem, another part will have all the DRDA activity (that which comes through the DB2 DDF address space), another will show activity for all programs linked with the Call Attach Facility (typically, these are batch jobs), and so on.
If you do generate an Accounting Report - Long with data grouped by connection type, do a little exercise for me -- it could be something of an eye-opener for you. Take, for each part of the report (i.e., the CICS-DB2 part, the DRDA part, the CAF part, etc.), two fields (maybe three, as I'll explain), and get the product of these. The fields of interest are (and again, I'm using OMEGAMON for DB2 terminology -- this might vary somewhat from product to product) #OCCURRENCES in the HIGHLIGHTS section, and CP CPU TIME in the AVERAGE section (the latter under the "class 2" column in that report section, so called because it shows information from DB2 accounting trace class 2 records). Before getting the product of those two fields, check to see if the SE CPU TIME filed in the "class 2" column in the AVERAGE section contains a non-zero value (and note that depending on your monitor and your release of DB2, "SE CPU TIME" may be labeled "IIP CPU TIME). If it does, that's CPU time on a zIIP engine (an SE, or "specialty engine"), and it's NOT included in CP CPU TIME (which is just CPU time on general-purpose engines, or central processors -- CPs for short). Still with me? OK, so take the average class 2 CPU time per occurrence (that is, per accounting trace record), which is general-purpose CP CPU time plus zIIP, or SE, CPU time, and multiply that by the number of occurrences. What this gives you: the total application-chargeable CPU time consumed in SQL statement execution for the connection type. [I say "application chargeable" because some SQL execution-related CPU time is consumed by things such as prefetch reads and database writes, which are charged to DB2's address spaces and not to so-called allied address spaces -- but the "application chargeable" CPU time is almost always the large majority of total SQL statement-related CPU consumption.]
[If you have a DB2 data sharing group, you'll want to sum the SQL statement CPU consumption figures for each member of the group. In other words, add total CICS-DB2 class 2 CPU time for member DB2A to total CICS class 2 CPU time for member DB2B to the total for DB2C, and so on, for each connection type.]
To help clarify things, here is an excerpt from an OMEGAMON for DB2 Accounting Report - Long, with the fields I've mentioned highlighted:
SUBSYSTEM: DB2A ORDER: CONNTYPE INTERVAL FROM: 12/01/10 09:00:00.00
DB2 VERSION: V9 SCOPE: MEMBER TO: 12/01/10 11:00:00.00
CONNTYPE: DRDA
AVERAGE APPL(CL.1) DB2 (CL.2) ... HIGHLIGHTS
------------ ---------- ---------- ... --------------------------
ELAPSED TIME 0.031811 0.015938 ... #OCCURRENCES : 2813092
NONNESTED 0.031361 0.015523 ... #ALLIEDS : 7790
STORED PROC 0.000313 0.000277 ... #ALLIEDS DISTRIB: 0
UDF 0.000000 0.000000 ... #DBATS : 2805272
TRIGGER 0.000138 0.000138 ... #DBATS DISTRIB. : 30
... #NO PROGRAM DATA: 0
CP CPU TIME 0.004754 0.004685 ... #NORMAL TERMINAT: 71402
AGENT 0.004754 0.004685 ... #DDFRRSAF ROLLUP: 276108
NONNESTED 0.004614 0.004551 ... #ABNORMAL TERMIN: 0
STORED PRC 0.000120 0.000114 ... #CP/X PARALLEL. : 0
UDF 0.000000 0.000000 ... #IO PARALLELISM : 0
TRIGGER 0.000020 0.000020 ... #INCREMENT. BIND: 1299
PAR.TASKS 0.000000 0.000000 ... #COMMITS : 2823659
... #ROLLBACKS : 74630
SECP CPU 0.000682 N/A ... #SVPT REQUESTS : 0
... #SVPT RELEASE : 0
SE CPU TIME 0.003992 0.004067 ... #SVPT ROLLBACK : 0
I'll tell you why this information is of such interest to me: it shows the in-DB2 CPU cost (i.e., the CPU cost of SQL statement execution) of the DB2 workload by component, and that can be news to people. I could ask a DB2 person, "What's the largest component of your DB2 workload?" and that person might say, "Batch," or "CICS," because he or she thinks that's the case. Then we generate and take a look at a DB2 monitor Accounting Report - Long with data grouped by connection type, and we do the numbers in the aforementioned way (average class 2 CPU CPU time per occurrence -- ensuring that class 2 zIIP CPU time, if any, is added in -- times number of occurrences, for each connection type). People are sometimes surprised by what they see. Maybe the batch DB2 workload isn't king of the hill, after all. What often makes the biggest impression is the relative size of the DRDA workload (which I often refer to as the client-server DB2 workload). It's not unusual for this to be the fastest-growing part of an organization's overall DB2 for z/OS workload, and sometimes it's the largest component of the overall workload. One factor here: at more and more sites, the bulk of new DB2 for z/OS-related application development work involves applications running on off-mainframe application servers, directing SQL statements to a DB2 for z/OS database via DRDA and the DB2 DDF (these statements may take the form of JDBC or ODBC calls, and more and more frequently they include calls -- maybe LOTS of calls to DB2 stored procedures).
Another client-server DB2 workload growth factor: a growing number of organizations are providing users with query and reporting tools and allowing them to use these to access data in production, operational DB2 databases. That's right -- BI (business intelligence) work targeting production DB2 for z/OS tables. Yes, this can be done while OLTP and batch programs go against the same data. I've seen it, and it works (it can work particularly well when DB2 is running in data sharing mode on a Parallel Sysplex, and one or two members of the group are dedicated to decision support applications). The DDF connection? The query and reporting tools typically send SQL statements to DB2 using the DRDA protocol (done through DB2 Connect or via one of the IBM Data Server Driver packages).
Whatever the breakdown of your workload, knowing it can help you to see where the demand for SQL statement execution capacity is coming from, and that can help you to deliver support where its needed most (suggestion: try tracking the workload breakdown over time, perhaps presenting the trends graphically in a line chart, with different colored lines for the different components of the overall DB2 workload, or a series of pie charts, the latter with slices for the different workload components).
I'll try to post more entries in the future on other uses of the information that can be found in a DB2 monitor Accounting Report - Long and Statistics Report - Long. For now, look these reports over at your shop. Don't run 'em yet? Get started. You'll be glad you did.
I'll tell you something interesting that I've observed regarding the use of DB2 monitors out in the real world: LOTS of folks use only the online monitoring capability of whatever tool they have on their system. Online monitoring is definitely useful when it comes to checking out what's happening right now in a DB2 subsystem -- something you may need to do, and quickly, if a problem pops up. That said, when it comes to deep-dive analysis of the performance of a DB2 subsystem, nothing beats the REPORTS that a DB2 monitor can provide. Plenty of DB2 professionals have NEVER used the report-generation capabilities of their monitor product. That's a bummer, because there's so much good stuff in those reports, and they are fantastic for trend analysis. If you have never used your DB2 monitor to produce reports, do yourself a favor and figure out how to do that. It's not hard: check your monitor's report command reference (or batch reporting users guide -- different monitoring tools have differently-titled manuals), and you'll see JCL requirements (a key: point to the SMF data set that will provide the desired input to the monitor) and examples of SYSIN control statements (these specify report type, reporting time frame, and report interval, among other things). Run some of these reports, and check out the wealth of information therein.
I find that the two most useful DB2 monitor report types are the Accounting Report - Long and the Statistics Report - Long (that's what OMEGAMON for DB2 calls 'em -- for some other monitoring products these reports are referred to as Accounting Detail and Statistics Detail). Input to these reports are records generated by the standard DB2 accounting and statistics trace classes that most folks have running all the time (e.g., accounting trace classes 1, 2, and 3, plus classes 7 and 8 if you're interested in package-level accounting). I'll tell you about my favorite flavor of the Accounting Report - Long in just a moment, but first I want to mention a practice implemented at my shop when I worked in the IT department of a DB2-using organization: every day, an Accounting Report - Long and a Statistics Report - Long (each covering the previous days' 24 hours of activity) were "printed" to a data set on disk (more specifically, a GDG, which made it easy to keep a rolling X days of reports). 60 days of these reports were kept online, available for browsing via TSO/ISPF. Having these around was super for a couple of reasons: 1) if a problem situation started to crop up, we could look back over several weeks of data to see how things had been trending, and what might have changed; and 2) with the reports on-hand, we didn't have to keep 60 days of SMF records online (the reports summarize and greatly reduce these records).
Now, the Statistics Report - Long is great, and I may write more about that report in a future post, but for now I want to talk a little about the Accounting Report - Long (statistics reports show activity from the DB2 subsystem perspective, while accounting reports provide information from an application or workload point of view). An important specification when generating an Accounting Report - Long is the desired grouping of the accounting data. For an OMEGAMON for DB2 report, this data grouping is determined via the ORDER subcommand (this would be part of the SYSIN input to a report generation job). The default grouping for OMEGAMON for DB2 (and for other monitors I've seen) is primary authorization ID within plan name. I generally don't want data in the report grouped that way. What I most often like to see is a report generated with ORDER(CONNTYPE) (using OMEGAMON for DB2 lingo -- the terminology might be slightly different for other monitors). With ORDER(CONNTYPE), your Accounting Report - Long will have several sections, with one report component for each DB2 connection type. In other words, one part of the report will show all the CICS-DB2 activity on the subsystem, another part will have all the DRDA activity (that which comes through the DB2 DDF address space), another will show activity for all programs linked with the Call Attach Facility (typically, these are batch jobs), and so on.
If you do generate an Accounting Report - Long with data grouped by connection type, do a little exercise for me -- it could be something of an eye-opener for you. Take, for each part of the report (i.e., the CICS-DB2 part, the DRDA part, the CAF part, etc.), two fields (maybe three, as I'll explain), and get the product of these. The fields of interest are (and again, I'm using OMEGAMON for DB2 terminology -- this might vary somewhat from product to product) #OCCURRENCES in the HIGHLIGHTS section, and CP CPU TIME in the AVERAGE section (the latter under the "class 2" column in that report section, so called because it shows information from DB2 accounting trace class 2 records). Before getting the product of those two fields, check to see if the SE CPU TIME filed in the "class 2" column in the AVERAGE section contains a non-zero value (and note that depending on your monitor and your release of DB2, "SE CPU TIME" may be labeled "IIP CPU TIME). If it does, that's CPU time on a zIIP engine (an SE, or "specialty engine"), and it's NOT included in CP CPU TIME (which is just CPU time on general-purpose engines, or central processors -- CPs for short). Still with me? OK, so take the average class 2 CPU time per occurrence (that is, per accounting trace record), which is general-purpose CP CPU time plus zIIP, or SE, CPU time, and multiply that by the number of occurrences. What this gives you: the total application-chargeable CPU time consumed in SQL statement execution for the connection type. [I say "application chargeable" because some SQL execution-related CPU time is consumed by things such as prefetch reads and database writes, which are charged to DB2's address spaces and not to so-called allied address spaces -- but the "application chargeable" CPU time is almost always the large majority of total SQL statement-related CPU consumption.]
[If you have a DB2 data sharing group, you'll want to sum the SQL statement CPU consumption figures for each member of the group. In other words, add total CICS-DB2 class 2 CPU time for member DB2A to total CICS class 2 CPU time for member DB2B to the total for DB2C, and so on, for each connection type.]
To help clarify things, here is an excerpt from an OMEGAMON for DB2 Accounting Report - Long, with the fields I've mentioned highlighted:
SUBSYSTEM: DB2A ORDER: CONNTYPE INTERVAL FROM: 12/01/10 09:00:00.00
DB2 VERSION: V9 SCOPE: MEMBER TO: 12/01/10 11:00:00.00
CONNTYPE: DRDA
AVERAGE APPL(CL.1) DB2 (CL.2) ... HIGHLIGHTS
------------ ---------- ---------- ... --------------------------
ELAPSED TIME 0.031811 0.015938 ... #OCCURRENCES : 2813092
NONNESTED 0.031361 0.015523 ... #ALLIEDS : 7790
STORED PROC 0.000313 0.000277 ... #ALLIEDS DISTRIB: 0
UDF 0.000000 0.000000 ... #DBATS : 2805272
TRIGGER 0.000138 0.000138 ... #DBATS DISTRIB. : 30
... #NO PROGRAM DATA: 0
CP CPU TIME 0.004754 0.004685 ... #NORMAL TERMINAT: 71402
AGENT 0.004754 0.004685 ... #DDFRRSAF ROLLUP: 276108
NONNESTED 0.004614 0.004551 ... #ABNORMAL TERMIN: 0
STORED PRC 0.000120 0.000114 ... #CP/X PARALLEL. : 0
UDF 0.000000 0.000000 ... #IO PARALLELISM : 0
TRIGGER 0.000020 0.000020 ... #INCREMENT. BIND: 1299
PAR.TASKS 0.000000 0.000000 ... #COMMITS : 2823659
... #ROLLBACKS : 74630
SECP CPU 0.000682 N/A ... #SVPT REQUESTS : 0
... #SVPT RELEASE : 0
SE CPU TIME 0.003992 0.004067 ... #SVPT ROLLBACK : 0
I'll tell you why this information is of such interest to me: it shows the in-DB2 CPU cost (i.e., the CPU cost of SQL statement execution) of the DB2 workload by component, and that can be news to people. I could ask a DB2 person, "What's the largest component of your DB2 workload?" and that person might say, "Batch," or "CICS," because he or she thinks that's the case. Then we generate and take a look at a DB2 monitor Accounting Report - Long with data grouped by connection type, and we do the numbers in the aforementioned way (average class 2 CPU CPU time per occurrence -- ensuring that class 2 zIIP CPU time, if any, is added in -- times number of occurrences, for each connection type). People are sometimes surprised by what they see. Maybe the batch DB2 workload isn't king of the hill, after all. What often makes the biggest impression is the relative size of the DRDA workload (which I often refer to as the client-server DB2 workload). It's not unusual for this to be the fastest-growing part of an organization's overall DB2 for z/OS workload, and sometimes it's the largest component of the overall workload. One factor here: at more and more sites, the bulk of new DB2 for z/OS-related application development work involves applications running on off-mainframe application servers, directing SQL statements to a DB2 for z/OS database via DRDA and the DB2 DDF (these statements may take the form of JDBC or ODBC calls, and more and more frequently they include calls -- maybe LOTS of calls to DB2 stored procedures).
Another client-server DB2 workload growth factor: a growing number of organizations are providing users with query and reporting tools and allowing them to use these to access data in production, operational DB2 databases. That's right -- BI (business intelligence) work targeting production DB2 for z/OS tables. Yes, this can be done while OLTP and batch programs go against the same data. I've seen it, and it works (it can work particularly well when DB2 is running in data sharing mode on a Parallel Sysplex, and one or two members of the group are dedicated to decision support applications). The DDF connection? The query and reporting tools typically send SQL statements to DB2 using the DRDA protocol (done through DB2 Connect or via one of the IBM Data Server Driver packages).
Whatever the breakdown of your workload, knowing it can help you to see where the demand for SQL statement execution capacity is coming from, and that can help you to deliver support where its needed most (suggestion: try tracking the workload breakdown over time, perhaps presenting the trends graphically in a line chart, with different colored lines for the different components of the overall DB2 workload, or a series of pie charts, the latter with slices for the different workload components).
I'll try to post more entries in the future on other uses of the information that can be found in a DB2 monitor Accounting Report - Long and Statistics Report - Long. For now, look these reports over at your shop. Don't run 'em yet? Get started. You'll be glad you did.
Subscribe to:
Posts (Atom)