tag:blogger.com,1999:blog-68066543304367222442024-02-08T08:08:31.750-08:00Catterall ConsultingRobert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.comBlogger121125tag:blogger.com,1999:blog-6806654330436722244.post-58032882048878806292010-08-05T19:04:00.000-07:002010-08-05T19:14:06.357-07:00Back at IBM<span style="font-family: arial;">It's been longer than usual since my last post to this blog. I've been busier than usual, largely due to a career change. On August 2, I rejoined IBM (I previously worked for the Company from 1982 to 2000). I'm still doing technical DB2 work, and I'll continue to blog about DB2, but new entries will be posted to my new blog, called, simply, "Robert's DB2 blog." It can be viewed at http://www.robertsdb2blog.blogspot.com/.<br /><br />Though I will no longer be posting to this blog, I'll continue to respond to comments left by readers of entries in this blog.<br /><br />To those who've been regular readers, I thank you for your time. I hope that you'll visit my new blog.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0tag:blogger.com,1999:blog-6806654330436722244.post-6495411449886810322010-07-12T22:08:00.000-07:002010-07-12T22:25:28.732-07:00EXPLAINing DB2 for z/OS: Don't Overlook MATCHCOLS<span style="font-family: arial;">Do you use EXPLAIN output when you're analyzing the performance of a query in a DB2 for z/OS environment? I hope so. If you do, you might be one of those who likes to retrieve access plan information by querying the PLAN_TABLE into which DB2 inserts rows when a SQL statement is EXPLAINed (either dynamically via the EXPLAIN statement or, for embedded static SQL, as part of the program bind process when EXPLAIN(YES) is included in the BIND command); or, you might prefer to view EXPLAIN data in the form of an access plan graph generated by a tool such as IBM's Optimization Service Center for DB2 for z/OS (free), Data Studio (also free), or Optimization Expert (have to pay for that one, but you get additional functionality). In any case, there are probably a few things that you really focus in on. These might include tablespace scans (indicated by an "R" in the ACCESSYPE column of PLAN_TABLE -- something that you generally don't want to see), index-only access (that would be a "Y" in the INDEXONLY column of PLAN_TABLE -- often desirable, but you can go overboard in your pursuit of it), and sort activity (a "Y" in any of the SORTC or SORTN columns of PLAN_TABLE -- you might try to get DB2 to use an index in lieu of doing a sort).<br /><br />What about MATCHCOLS, the column of PLAN_TABLE that indicates the number of predicates that match on columns of the key of an index used to access data for a query (in a visual EXPLAIN access plan graph, this value would be referred to as "matching columns" for an index scan)? Do you scope that out? If so, do you just look for a non-zero value and move on? Here's a tip: pay attention to MATCHCOLS. Making that value larger is one of the very best things that you can do to reduce the run time and CPU cost of a SELECT statement. The performance difference between MATCHCOLS = 1 and MATCHCOLS = 2 or 3 can be huge. The reason? It's all about cardinality. You could have an index key based on three of a table's columns, and if the table has 10 million rows and the cardinality (i.e., the number of distinct values) of the first key column is 5, MATCHCOLS = 1 is an indication that you're going to get very little in the way of result set filtering at the index level (expect lots of GETPAGEs, a relatively high CPU cost, and extended run time). If the cardinality of the first two columns of the index key is 500,000, MATCHCOLS = 2 should mean much quicker execution, and if the full-key cardinality of the index is 5 million, a SELECT statement with MATCHCOLS = 3 should really fly. A larger MATCHCOLS value generally means more index-level filtering, and that is typically very good for performance.<br /><br />Step one in boosting the value of MATCHCOLS for a query is to determine how high the value <span style="font-style: italic;">could</span> be. Obviously, if a SELECT statement has only one predicate then any effort to make MATCHCOLS greater than 1 will be a waste of time. So, the upper bound on the value of MATCHCOLS in a given situation is the number of predicates present in a query (or, more precisely, in a subselect, if the statement contains multiple SELECTs -- think UNIONs, nested table expressions, subquery predicates, etc.); but, that's an oversimplification, because not all predicates can match on a column of an index key -- COL <> 2 is an example of a non-indexable predicate (my favorite source of information on predicate indexability is the <a href="http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.perf/db2z_summarypredicateprocessing.htm">"Summary of predicate processing"</a> section of the DB2 9 for z/OS <span style="font-style: italic;">Performance Monitoring and Tuning Guide</span>). Even that's not the whole story, however, as the position of columns in an index key -- and not just the number of indexable predicates in a query -- has an impact on the MATCHCOLS value.<br /><br />This last point bears some explaining. Suppose you have a query with these predicates:<br /><br />COL_X = 'ABC'<br />COL_Y = 5<br />COL_Z > 10<br /><br />All three of the predicates are indexable. If the three columns referenced in the predicates are part of an index key, will the MATCHCOLS value for the query be 3? Maybe, maybe not. If the index key is COL_X | COL_Y | COL_Z, MATCHCOLS will indeed be 3 (assuming the index provides the low-cost path to the data retrieved by the query). If, on the other hand, the index key is COL_Z | COL_Y | COL_X, MATCHCOLS will be 1. Why? Because once a key column is matched for a range predicate, other columns that follow in the index key won't be matches for other predicates in the query (the basic rule, which I'll amend momentarily, is that index column matching, which proceeds from the highest- to the lowest-order column of an index key, stops following the first match for a predicate that is not of the "equals" variety). If the index is on COL_X | COL_Y | COL_H | COL_Z, MATCHCOLS will be 2, because index key column matching stops if you have to "skip over" a non-predicate-referenced column (in this case, COL_H). So, one of the things that you can do to get a higher versus a lower MATCHCOLS value for a query is to arrange columns in an index key so as to maximize matching (that's fine if you're defining a new index on a table, but be careful about replacing an index on a set of columns in one order with an index on the columns in a different order -- you could improve the performance of one query while causing others to run more slowly).<br /><br />Now, about the aforementioned amendment to the basic rule that states that index key column predicate matching stops after the first match for something other than an "equals" predicate: there is at least one special case of a predicate that is not strictly "equals" in nature but which is treated that way from an index key column matching perspective. I'm talking about an "in-list" predicate, such as COL_D IN ('A01', 'B02', 'C03'). Thus, if a query contained that predicate and two others, COL_E = 'Y' AND COL_F > 8 (assuming that the three columns belong to one table), and if the target table had an index defined on COL_D | COL_E | COL_F, the expected MATCHCOLS value for the query would be 3.<br /><br />Something else about indexes and MATCHCOLS: I mentioned earlier that cardinality is a big part of the story, but for cardinality to mean anything to DB2, DB2 has to <span style="font-style: italic;">know</span> about it. DB2 gets index key cardinality information from the catalog, so it pays to ensure that catalog statistics are accurate through regular execution of the RUNSTATS utility; but, there's more: to provide DB2 with really complete information on index key cardinality, specify the KEYCARD option on the RUNSTATS utility control statement. When the utility is run sans KEYCARD, cardinality statistics will be generated for the first column of an index key and for the full index key, but not for anything (if anything) in-between. In other words, with an index on COL_A | COL_B | COL_C | COL_D, an execution of RUNSTATS for the index without the KEYCARD option will cause the catalog to be updated with cardinality statistics for COL_A (the first of the key columns) and for the entire four-column key, but not for COL_A | COL_B and not for COL_A | COL_B | COL_C. With KEYCARD specified, the utility will get cardinality statistics for these intermediate partial-key combinations.<br /><br />A final note on this topic: it can make sense to include a predicate-referenced column in an index key, even if the predicate in question is non-indexable (meaning that the presence of the column in the key cannot help to make the MATCHCOLS number larger). This is so because the column could still be used by DB2 for something called index screening. So, if you have a non-indexable predicate such as one of the COL_J <> 'M' form, it might be a good idea to include COL_J in an index key after some predicate-matching columns, especially if COL_J is relatively short and the underlying table is large. DB2 can use the COL_J information in the index to filter results, just not as efficiently as it could if COL_J were referenced by an indexable predicate.<br /><br />Bottom line: MATCHCOLS matters. Tune on!<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0tag:blogger.com,1999:blog-6806654330436722244.post-89794572373730034722010-06-29T20:57:00.000-07:002010-06-29T21:31:07.499-07:00Using DB2 for z/OS Real-Time Statistics for Smarter Database Management<span style="font-family: arial;">Unless you are somehow beyond the reach of advertising, you're probably familiar with IBM's "smarter planet" campaign. It's all about leveraging analytics -- the purposeful analysis of timely, relevant information -- to improve decision-making outcomes. If you administer a mainframe DB2 database, you can work smarter by taking advantage of a DB2 feature that, while familiar to many DBAs in an "I've heard of that" sense, is under-exploited to a surprising degree. I'm talking about real-time statistics (aka RTS). Understand real-time stats -- what they are, where you find them, and how you can use them -- and you're on your way to enhancing the efficiency and effectiveness of your DB2 database administration efforts.<br /><br />I'm sure that most everyone who reads this blog entry is familiar with the database statistics found in the DB2 catalog. These statistics are updated by way of the RUNSTATS utility, and they can be useful for things like identifying tablespaces and indexes in need of reorganization. Still, from a "work smarter" perspective, they are less than ideal. For one thing, they are only updated when you run the RUNSTATS utility (or when you gather and update statistics as part of a REORG or a LOAD utility operation -- more on that in a moment). How often do you do that? Maybe not too frequently, if you have a whole lot of tablespaces in your database. Suppose you run RUNSTATS, on average, once a month for a given tablespace. Could that tablespace end up getting pretty disorganized in the middle of one of those one-month periods between RUNSTATS jobs? Yes, and in that case you wouldn't be aware of the disorganization situation for a couple of weeks after the fact -- not so good.<br /><br />As for updating catalog stats via REORG and/or LOAD, that's all well and good, but consider this: when you do that, the stats gathered will reflect perfectly organized objects (assuming, for LOAD, that the rows in the input file are in clustering-key sequence). They won't show you how the organization of a tablespace and its indexes may be deteriorating over time.<br /><br />Then there's the matter of dynamic cache invalidation. ANY time you run the RUNSTATS utility -- no matter what options are specified -- you invalidate SQL statements in the dynamic statement cache. For a while thereafter, you can expect some extra CPU consumption as the statement cache gets repopulated through the full-prepare of dynamic queries that otherwise might have resulted in cache hits.<br /><br />So, there's goodness in getting frequently updated catalog statistics to help you determine when objects need to be reorganized, but running RUNSTATS frequently will cost you CPU time, both directly (the cost of RUNSTATS execution) and indirectly (the CPU cost of repopulating the dynamic statement cache following a RUNSTATS job). You could avoid these CPU costs by not using catalog stats to guide your REORG actions, relying instead on a time-based strategy (e.g., REORG every tablespace and associated indexes at least once every four weeks), but that might lead to REORG operations that are needlessly frequent for some tablespaces that remain well-organized for long stretches of time, and too-infrequent REORGs for objects that relatively quickly lose clusteredness. And I haven't even talked about tablespace backups. Getting a full image copy of every tablespace at least once a week, with daily incremental copies in-between, is a solid approach to recovery preparedness, but what if you're daily running incremental image copy jobs for objects that haven't changed since the last copy? How could you get smarter about that? And what about RUNSTATS itself? How can you get stats to help you make better decisions about updating catalog statistics?<br /><br />Enter real-time statistics. This is the name of an item of functionality that was introduced with DB2 for OS/390 Version 7. That was almost 10 years ago, and while the feature has been effectively leveraged by some DBAs for years, it's remains on the edge of many other DBAs' radar screens, largely for two reasons:<br /></span><ol><li><span style="font-family: arial;"><span style="font-style: italic;">You (used to) have to create the real-time statistics objects yourself</span>. I'm referring to the real-time statistics database (DSNRTSDB), the real-time stats tablespace (DSNRTSTS), two tables (SYSIBM.TABLESPACESTATS and SYSIBM.INDEXSPACESTATS), and a unique index on each of the tables. Instructions for creating these objects were provided in the DB2 Administration Guide, but some folks just didn't have the time or the inclination to bother with this. Happily, with DB2 9 for z/OS the real-time statistics objects became part of the DB2 catalog -- they are there for you like all the other catalog tables (if your DB2 subsystem is at the Version 8 level and the real-time statistics objects have already been created, when you migrate to DB2 9 any records in the user-created RTS tables will be automatically copied to the RTS tables in the catalog).</span><span style="font-family: arial;"></span></li><li><span style="font-family: arial;"><span style="font-style: italic;">People had this idea that real-time statistics drive up CPU overhead in a DB2 environment</span>. They really don't. You see, DB2 is always updating the real-time statistics counters anyway, whether or not you make any use of them. What we know as real-time statistics involves the periodic externalization of these counters, and that's a pretty low-cost operation (</span><span style="font-family: arial;">the default RTS externalization interval is 30 minutes, and you can adjust that by way of the STATSINT parameter of ZPARM)</span><span style="font-family: arial;">.</span></li></ol><span style="font-family: arial;">So, if you are already on DB2 9, take a few minutes and check out the data in the SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS catalog tables (in a pre-9 DB2 environment, the names of the user-defined RTS tables are -- as previously mentioned -- SYSIBM.TABLESPACESTATS and SYSIBM.INDEXSPACESTATS). You'll see that the column names are pretty intuitive (Hmmm, wonder what you'll find in the EXTENTS column of SYSTABLESPACESTATS? Or how about TOTALENTRIES in SYSINDEXSPACESTATS?). The theme is "news you can use," and a primary aim is to help you get to a needs-based strategy with regard to the execution of utilities such as REORG, RUNSTATS, and COPY, versus running these using only time-based criteria. To this end, RTS provides valuable information such as the total number of rows added to a tablespace since it was last reorganized (REORGINSERTS), the number of rows inserted out of clustering sequence since the last REORG (REORGUNCLUSTINS), the number of updates since the last RUNSTATS execution for a tablespace (STATSUPDATES), the number of data-change operations since a tablespace was last image-copied (COPYCHANGES), and the number of index leaf pages that are far from where they should be due to page splits that have occurred since the last time the index was reorganized or rebuilt (REORGLEAFFAR). Note, too, that in addition to the utility-related numbers, RTS provides, in a DB2 9 system, a column, called LASTUSED (in SYSINDEXSPACESTATS), that can help you identify indexes that are just taking up space (i.e., that aren't being used to speed up queries or searched updates or deletes, or to enforce referential integrity constraints).<br /><br />How will you leverage RTS? You have several options. You can process them using a DB2-supplied stored procedure (DSNACCOR for DB2 Version 8, and the enhanced DSNACCOX delivered with DB2 9). You might find that DB2 tools installed on your system -- from IBM and from other companies -- can take advantage of real-time statistics data (check with your tools vendors). DBAs who know a thing or two about the REXX programming language have found that they can write their own utility-automation routines thanks to RTS. And of course you can write queries that access the RTS tables and return actionable information. I encourage you to be creative here, but to get the juices flowing, here's an RTS query that I've used to find highly disorganized nonpartitioned tablespaces (this particular query was run in a DB2 Version 8 system -- it should work fine in a DB2 9 subsystem if you change TABLESPACESTATS to SYSTABLESPACESTATS):<br /><br />SELECT A.NAME,<br /> A.DBNAME,<br /> CAST(REORGLASTTIME AS DATE) AS REORGDATE,<br /> CAST(FLOOR(TOTALROWS) AS INTEGER) AS TOTALROWS,<br /> REORGINSERTS,<br /> CAST((DEC(REORGUNCLUSTINS,11,2) / DEC(REORGINSERTS,11,2)) * 100<br /> AS INTEGER) AS PCT_UNCL_INS,<br /> REORGDELETES,<br /> B.PCTFREE,<br /> B.FREEPAGE<br /> FROM SYSIBM.TABLESPACESTATS A, SYSIBM.SYSTABLEPART B<br /> WHERE A.NAME = B.TSNAME<br /> AND A.DBNAME = B.DBNAME <br /> AND TOTALROWS > 10000<br /> AND REORGUNCLUSTINS > 1000<br /> AND (DEC(REORGUNCLUSTINS,11,2) / DEC(REORGINSERTS,11,2)) * 100 > 50<br /> AND A.PARTITION = 0<br /> ORDER BY 6 DESC <br /> WITH UR;<br /><br />Real-time stats are going mainstream, folks. Be a part of that. Work smart.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0tag:blogger.com,1999:blog-6806654330436722244.post-47565914722490305732010-06-09T10:08:00.000-07:002010-06-09T10:39:55.384-07:00Nuggets from DB2 by the Bay, Part 4<span style="font-family:arial;">The last of my posts with items of information from the 2010 International DB2 Users Group North American Conference, held last month in Tampa (as in Tampa Bay), Florida.<br /><br /><span style="font-weight: bold;">Good DB2 9 for z/OS migration information from David Simpson.</span> David, a senior DB2 instructor with Themis Training, described some things of which people migrating to DB2 9 should be aware. Among these are the following:<br /></span><ul><li><span style="font-family:arial;"><span style="font-style: italic;">The pureXML functionality delivered in DB2 9 is quite comprehensive and opens up a lot of possibilities. </span>One of David's colleagues at Themis figured out how to create views that make data stored in XML columns of DB2 tables look like standard relational data.</span><span style="font-family:arial;"></span></li></ul><ul><li><span style="font-family:arial;"><span style="font-style: italic;">Do you have a handle on your simple tablespace situation?</span> David reminded session attendees that simple tablespaces cannot be created in a DB2 9 environment. This being the case, it would be a good idea to move data from the simple tablespaces that you have to other tablespace types (segmented tablespaces, most likely). Sure, you can still read from, and update, a simple tablespace in a DB2 9 system, but the inability to create such a tablespace could leave you in a tough spot if you were to try to recover a simple tablespace that had been accidentally dropped (David suggested that people create a few empty simple tablespaces before migrating to DB2 9, so you'll have some available just in case you need a new one). You might think that you don't have any simple tablespaces in your DB2 Version 8 system, but you could be wrong there -- David pointed out that simple tablespaces are the default up through DB2 Version 8 (so, an implicitly-created tablespace in a pre-DB2 9 environment will be a simple tablespace).</span><br /></li></ul><span style="font-family:arial;"></span><ul><li><span style="font-family:arial;"><span style="font-style: italic;">New RRF developments.</span> That's RRF as in reordered row format, a change (introduced with DB2 9) in the way that columns are physically ordered in a table. In the old set-up (now referred to as BRF, or basic row format), varying-length columns in a table (such as those with a VARCHAR data type) are physically stored, relative to other columns, in the order in which they appear in the CREATE TABLE statement. With RRF in effect, varying-length columns are grouped at the end of a table's rows, and that group of varying-length columns is preceded by a set of offset indicators -- one for each varying-length column -- that enable DB2 to very efficiently go right to the start of a given varying-length column. David told attendees that RRF does NOT affect what programs "see", as the logical order of a table's columns does not change with a change to RRF. RRF is a good thing with respect to varying-length-data access performance, but it may cause some issues when tablespaces are compressed (RRF rows sometimes don't compress quite as much as equivalent BRF rows), when data changes related to tables in compressed tablespaces are propagated via "log-scraping" replication tools (you just need to make sure that your replication tool can deal with the new compression dictionary that is created when a tablespace goes from BRF to RRF), and when tablespaces are operated on by the DB2 DSN1COPY utility (this utility doesn't use the SQL interface, so it is sensitive to changes in the physical order of columns even when this has no effect on the columns' logical order in a table).<br /><br />Early on with DB2 9, the change from BRF to RRF was automatic with the first REORG in a DB2 9 environment of a tablespace created in a pre-9 DB2 system. Various DB2 users asked for more control over the row-format change, and IBM responded with APARs like <a href="http://www-01.ibm.com/support/docview.wss?uid=swg1PK85881">PK85881</a> and <a href="http://www-01.ibm.com/support/docview.wss?uid=swg1PK87348">PK87348</a>. You definitely want to get to RRF at some point. With the fixes provided by these APARs, you can decide if you want BRF-to-RRF conversion to occur automatically with some utility operations (REORG and LOAD REPLACE), or if you want to explicitly request format conversion on a tablespace-by-tablespace basis. You can also determine whether or not you want tablespaces created in a DB2 9 environment to have BRF or RRF rows initially.</span><br /></li></ul> <ul><li><span style="font-family:arial;"><span style="font-style: italic;">Time to move on from Visual Explain.</span> David mentioned that VE is not supported in the DB2 9 environment -- it doesn't work with new DB2 9 data types (such as XML), and it can produce "indeterminate results" if a DB2 9 access plan is not possible in a DB2 Version 8 system. If you want a visual depiction of the access plan for a query accessing a DB2 9 database, you can use the free and downloadable <a href="http://www-01.ibm.com/support/docview.wss?rs=64&uid=swg27017059">IBM Optimization Service Center for DB2 for z/OS</a>, or <a href="http://www-01.ibm.com/software/data/optim/data-studio/features.html?S_CMP=rnav">IBM Data Studio</a>.</span></li></ul><ul><li><span style="font-family:arial;"><span style="font-style: italic;">Bye-bye, BUILD2.</span> David explained that partition-level online REORG in a DB2 9 system does not have a BUILD2 phase (in prior releases of DB2, this is the REORG utility phase during which row IDs in non-partitioned indexes are updated to reflect the new position of rows in a reorganized table partition). That's good, because data in a partition is essentially unavailable during the BUILD2 phase, and BUILD2 can run for quite some time if the partition holds a large number of rows. There's a catch, though: BUILD2 is eliminated because DB2 9 partition-level online REORG reorganizes non-partitioned indexes in their entirety, using shadow data sets. That means more disk space and more CPU time for partition-level REORG in a DB2 9 system. It also means that you can't run multiple online REORG jobs for different partitions of the same partitioned tablespace in parallel. You can get parallelism within one partition-level online REORG job if you're reorganizing a range of partitions (e.g., partitions 5 through 10). Note that in a DB2 10 environment, you can get this kind of intra-job parallelism for an online REORG even if the multiple partitions being reorganized are not contiguous (e.g., partitions 3, 7, 10, and 15).</span></li></ul><span style="font-family:arial;"><span style="font-weight: bold;">DB2 for z/OS and application programming.</span> Dave Churn, a database architect at DST Systems in Kansas City, delivered a session on application development in a DB2 context. David commented on a number of application-oriented DB2 features and functions, including these:<br /></span><ul><li><span style="font-family:arial;"><span style="font-style: italic;">Fetching and inserting chunks of rows.</span> DST has made some use of the multi-row FETCH and INSERT capabilities introduced with DB2 for z/OS Version 8. Dave said that performance benefits had been seen for programs that FETCH rows in blocks of 5-10 rows each, and for programs that INSERT rows in blocks of 20 rows each. The other side of that coin is increased programming complexity (Dave noted that with multi-row FETCH, you're "buffering in your program"). In DST's case, multi-row FETCH is not being used to a great extent, because the increased time required for programmers to write code to deal with multi-row FETCH (versus using traditional single-row FETCH functionality) is generally seen as outweighing the potential performance gain (and that gain will often not be very significant in an overall sense -- as Dave said, "How often is FETCH processing your primary performance pain point?").<br /><br />Use of multi-row INSERT, on the other hand, has been found to be more advantageous in the DST environment, particularly with respect to the Company's very high-volume, time-critical, and INSERT-heavy overnight batch workload. As with multi-row FETCH, there is an increase in programming complexity associated with the use of multi-row INSERT (among other things, to-be-inserted values have to be placed in host variable arrays declared by the inserting program), but the performance and throughput benefits often made the additional coding effort worthwhile. Interestingly, others in the audience indicated that they'd seen the same pattern in their shops: multi-row INSERT was found to be of greater use than multi-row FETCH. Dave mentioned that at DST, programs using multi-row INSERT were generally doing so with the NOT ATOMIC CONTINUE ON SQLEXCEPTION option, which causes DB2 to NOT undo successful inserts of rows in a block if an error is encountered in attempting to insert one or more rows in the same block. The programs use the GET DIAGNOSTICS statement to identify any rows in a block that were not successfully inserted. These rows are written to a file for later analysis and action.</span><br /></li></ul><span style="font-family:arial;"></span><ul><li><span style="font-family:arial;"><span style="font-style: italic;">The new BINARY data type in DB2 9 can be great for some client-server applications.</span> When DB2 for z/OS is used for the storage of data that is inserted by, and retrieved by, programs running on Linux/UNIX/Windows application servers, the BINARY data type can be a good choice: if the data will not be accessed by programs running on the mainframe, why do character conversion? Use of the BINARY data type ensures that character conversion will not even be attempted when the data is sent to or read from the DB2 database.</span><br /></li></ul><span style="font-family:arial;"></span><span style="font-family:arial;"></span><ul><li><span style="font-family:arial;"><span style="font-style: italic;">DB2 9 FETCH WITH CONTINUE is useful for really big LOBs.</span> In some cases, a LOB value might be larger than what a COBOL program can handle (which is about 128 MB). The FETCH WITH CONTINUE functionality introduced with DB2 9 enables a COBOL program to retrieve a very large LOB is parts.</span><br /></li></ul><span style="font-family:arial;"></span><ul><li><span style="font-family:arial;"><span style="font-style: italic;">MERGE musings.</span> The MERGE statement, new with DB2 9 and sometimes referred to as "UPSERT", is very handy when a set of input records is to be either inserted into a DB2 table or used to update rows already in the table, depending on whether or not an input record matches an existing table row. Dave mentioned that the matching condition (specified in the MERGE statement) will ideally be based on a unique key, so as to limit the scope of the UPDATE that occurs when a "no match" situation exists. DST likes MERGE because it improves application efficiency (it reduces the number of program calls to DB2 versus the previously-required INSERT-ELSE-UPDATE construct) and programmer productivity (same reason -- fewer SQL statements to code). Dave said that DST has used MERGE with both the ATOMIC and NOT ATOMIC CONTINUE ON SQLEXCEPTION options (when the latter is used for MERGE with a multi-record input block, GET DIAGNOSTICS is used to determine what, if any, input records were not successfully processed -- just as is done for multi-row INSERT).</span><br /></li></ul><span style="font-family:arial;"></span><ul><li><span style="font-family:arial;"><span style="font-style: italic;">SELECT FROM UPDATE/INSERT/DELETE/MERGE is great for efficiently obtaining DB2-generated or DB2-modified values.</span> DST has used the SELECT FROM <span style="font-style: italic;">data-changing-statement</span> syntax (introduced for INSERT with DB2 Version 8, and expanded to other data-changing statements with DB2 9) to obtain values generated by BEFORE triggers on DB2 tables (as an aside, Dave mentioned that DST has used triggers to, among other things, dynamically change a program's commit frequency). DST has also found it useful to execute SELECT FROM MERGE statements with the INCLUDE option (enabling return of values not stored in a target table) to determine whether rows in a MERGE input block were inserted or used to update the target table.</span><br /></li></ul><span style="font-family:arial;"></span><ul><li><span style="font-family:arial;"><span style="font-style: italic;">When will you use the new DB2 9 XML data type?</span> You'll use it, Dave said, when "your clients want to exchange information with you in the form of XML documents." In other words, you're likely to use it when your company's clients make XML data exchange a requirement for doing business. DST is using DB2 9 pureXML now. You might want to get ready to use it, just in case you'll need to. Being prepared could make exploitation of the technology an easier process (and it is pretty amazing whet DB2 can do with XML documents, in terms of indexability, schema validation, and search and retrieval using XQUERY expressions embedded in SQL statements).</span><br /></li></ul><span style="font-family:arial;">That's a wrap for this multi-part post. I hope that part 4 has provided you with some useful information, and I invite you to check out parts <a href="http://catterallconsulting.blogspot.com/2010/05/nuggets-from-db2-by-bay-part-1.html">1</a>, <a href="http://catterallconsulting.blogspot.com/2010/05/nuggets-from-db2-by-bay-part-2.html">2</a>, and <a href="http://catterallconsulting.blogspot.com/2010/06/nuggets-from-db2-by-bay-part-3.html">3</a>, if you haven't already done so. The <a href="http://www.idug.org/db2-north-american-conference/idug-2011-north-america.html">IDUG 2011 North American Conference</a> will be held in Anaheim, California next May. I'm planning on being there, and I hope that many of you will be there as well. It's always a great source of DB2 "news you can use."<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0tag:blogger.com,1999:blog-6806654330436722244.post-24298844965639811622010-06-01T21:49:00.000-07:002010-06-01T22:09:43.120-07:00Nuggets from DB2 by the Bay, Part 3<span style="font-family: arial;">Still more items of information from the 2010 International DB2 Users Group North American Conference held last month in Tampa, Florida.<br /><br /><span style="font-weight: bold;">A new chapter in the history of the DB2 optimizer. </span>Terry Purcell, uber-optimizer-guy on IBM's DB2 for z/OS development team, delivered an excellent session on new query optimizer features in the DB2 10 environment. These goodies include:<br /></span><ul><li><span style="font-family: arial;"><span style="font-style: italic;">An new option allows you to get dynamic statement cache matching for SQL statements that have different literal values but are otherwise identical.</span> Prior to DB2 10, matching was possible only for statements that were identical on a byte-for-byte basis: such statements would either contain parameter markers or identical literal values. The CPU efficiency attained through statement matching with different literals won't be quite what you get when statements containing parameter markers are matched, but it should be pretty close.</span><br /></li></ul><ul><li><span style="font-family: arial;"><span style="font-style: italic;">How about PERSISTENT caching of prepared dynamic SQL statements?</span> Dynamic statement caching is great, but when a prepared statement gets flushed out of the cache, it's gone, right? Not any more, folks. DB2 10 (in New Function Mode) will provide an access path repository in the catalog that will enable you to stabilize -- in a long-term way -- access paths for dynamic SQL statements (a "game-changer," Terry called it). When this option is in effect, DB2 will look first to match an incoming dynamic statement with a statement in the repository, then (if not found in the repository) in the dynamic statement cache. If neither of these matching attempts is successful, DB2 will dynamically prepare the statement. Want to change a path in the repository? You'll be able to do so by rebinding at the query level. By the way, the repository will also enable a more-robust implementation of DB2's access path hint functionality: it will be possible to put a hint into the repository, so you'll no longer have to provide a query number value in order to use a hint.</span><br /></li></ul><span style="font-family: arial;"></span><ul><li><span style="font-family: arial;"><span style="font-style: italic;">Mass rebind? No problem.</span> A new REBIND option, APREUSE(YES), will instruct DB2 to generate a new control structure for a package (to take advantage of a service fix, for example) while retaining the existing access path, if possible. If the package's old access path can't be reused for some reason, a new one will be generated. And, speaking of new and different access paths, another DB2 10-delivered REBIND option, APCOMPARE(ERROR), can be used to tell DB2 to issue an error message if a rebind operation changes an access path (you can optionally have DB2 issue a warning instead of an error). Going forward, when you want to do a mass rebind of packages as part of a version-to-version DB2 migration, you may well want to do your rebinds with APREUSE(YES) and APCOMPARE(ERROR).</span><br /></li></ul><span style="font-family: arial;"></span><ul><li><span style="font-family: arial;"><span style="font-style: italic;">More user-friendly access plan stability.</span> Lots of people like the access plan stability capability that was delivered with DB2 9 for z/OS via the new PLANMGMT option of the REBIND command. Nice as that is, it could be a hassle trying to get information about a version of a package's access plan other than the one currently in use. DB2 10 will address that problem with a new catalog table, SYSPACKCOPY, that will provide metadata for previous and original copies of access plans.</span><br /></li></ul><span style="font-family: arial;"></span><ul><li><span style="font-family: arial;"><span style="font-style: italic;">Playing it safe when choosing access paths.</span> DB2 has always gone for lowest cost when choosing an access path for a query. Sometimes, that can be a problem for a statement with one or more host variables in its predicates, as the path identified as lowest-cost might result in really bad performance for certain variable values. The DB2 10 optimizer, older and wiser, will consider risk (i.e., the chance of getting poor performance for certain statement variable values) as well as cost in determining the optimal path for a SQL statement.</span><br /></li></ul><span style="font-family: arial;"></span><ul><li><span style="font-family: arial;"><span style="font-style: italic;">Staying with the RID option.</span> Some folks cringe when DB2 starts down the path of using a RID list in executing a query (perhaps for multi-index access), and then switches to a tablespace scan because a RID limit was reached. DB2 10 can overflow a big RID list to a workfile and keep on trucking. Will you need more workfile space as a result? Perhaps, but note that the spillover effect is mitigated by a new larger default size for the RID pool in a DB2 10 environment.</span><br /></li></ul><span style="font-family: arial;"></span><ul><li><span style="font-family: arial;"><span style="font-style: italic;">In-list advances.</span> DB2 10 extends its predicate transitive closure capability (the ability to determine a relationship between A and C based on A-B and B-C relationships) to in-list predicates. DB2 10 can also use matching index access for multiple in-list predicates in a query (prior to DB2 10, if a query had several in-list predicates, only one of these could be used for a matching index scan). And one more thing: DB2 10 can take several OR-connected predicates that match on one index and convert them to a single in-list predicate to generate a result set (that's more efficient than using "index ORing" for the predicates, as is done in a pre-10 DB2 system).</span><br /></li></ul><span style="font-family: arial;"></span><ul><li><span style="font-family: arial;"><span style="font-style: italic;">Query parallelism enhancements.</span> With DB2 10, you can get parallelism for multi-row FETCH operations (though not for an ambiguous cursor). DB2 10 also enables parallel query tasks to share workfiles. And, in DB2 10, something called "dynamic record range partitioning" can be used to cause data in table A to be split into partitions that "line up" with the partitions of table B, the result being improved parallel table-join processing. This does introduce a data sort, but the expectation is that the technique will be used when table A is on the small side, so the sort shouldn't be a big deal.=</span><br /></li></ul><span style="font-family: arial;"></span><ul><li><span style="font-family: arial;"><span style="font-style: italic;">A RUNSTATS efficiency boost. </span>It used to be that sampling provided some help in reducing the CPU cost of a RUNSTATS utility job. With DB2 10, sampling provides a LOT of help in this department, because the sampling percentage now applies to the percentage of pages examined (it used to refer to the percentage of data rows examined in gathering statistics for non-indexed columns -- you could sample 25% of the rows, but end up accessing ALL of the tablespace's pages). What's more, there's an optional specification that you can use to tell DB2 to figure out the sampling percentage to use for an execution of RUNSTATS.</span></li></ul><span style="font-family: arial;"><span style="font-weight: bold;">Where to go for more DB2 10 information.</span> IBM's Roger Miller has provided information about DB2 10 presentations available on IBM's Web site. They are in a folder accessible via this url: <a href="ftp://public.dhe.ibm.com/software/data/db2/zos/presentations/v10-new-function/">ftp://public.dhe.ibm.com/software/data/db2/zos/presentations/v10-new-function/</a><br /><br />In this folder, you'll find these presentations that were delivered at the IDUG conference in Tampa:<br /></span><ul><li><span style="font-family: arial;">Session A01: DBA improvements, by Roger Miller</span></li><li><span style="font-family: arial;">Session B02: What's new from the optimizer, by Terry Purcell</span></li><li><span style="font-family: arial;">Session A03: DB2 10 Performance Preview, by Akiko Hoshikawa</span></li><li><span style="font-family: arial;">Session A06: DB2 and System z Synergy, by Chris Crone</span></li><li><span style="font-family: arial;">Session A08: DB2 10 Availability Enhancements, by Haakon Roberts</span><br /></li></ul><span style="font-family: arial;">You'll also find these presentations from the recent IBM Information on Demand European Conference:<br /></span><ul><li><span style="font-family: arial;">Session 2908: DB2 10 for z/OS security features help satisfy your auditors, by Jim Pickel</span></li><li><span style="font-family: arial;">Session 2894: What’s coming from the optimizer in DB2 10 for z/OS, by Terry Purcell</span></li><li><span style="font-family: arial;">Session 3010: New pureXML Features in DB2 for z/OS: Breaking Relational Limits, by Guogen (Gene) Zhang</span><br /></li></ul><span style="font-family: arial;">I'll be back here in a few days with still more from my notes taken at the IDUG Conference in Tampa. Ciao for now.</span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com2tag:blogger.com,1999:blog-6806654330436722244.post-3723625315361395882010-05-24T21:52:00.000-07:002010-05-24T22:08:38.162-07:00Nuggets from DB2 by the Bay, Part 2<span style="font-family: arial;">More items of information from the 2010 International DB2 Users Group North American Conference, held earlier this month in Tampa (in fact by the bay -- the convention center is on the waterfront).<br /><br />IBM's Roger Miller delivered a session on DB2 for z/OS Version 10 (in beta release for the past couple of months) with typical enthusiasm (opening line: "This is a BIG version"). Some of his points:<br /><br /><span style="font-weight: bold;">Much attention was paid to making life easier for DBAs.</span> Among the labor-saving features of DB2 10:<br /></span><ul><li><span style="font-family: arial;">Automated collection of catalog stats, so you don't have to mess with the RUNSTATS utility if you don't want to.</span></li><li><span style="font-family: arial;">Worry-free scale-up, with DB2 thread-related virtual storage now above the 2 GB "bar" in the DB2 database services address space. The number of concurrently active threads can go WAY up in a DB2 10 environment.</span></li><li><span style="font-family: arial;">Access path stability, a favorite of many DB2 9 users, is enhanced. That makes for worry-free rebinding of packages (and you'll want to rebind to get the aforementioned below-the-bar virtual storage constraint relief, and to get the benefit of optimizer improvements).</span></li><li><span style="font-family: arial;">Reduced catalog contention will allow for more concurrency with regard to CREATE/ALTER/DROP activity.</span></li><li><span style="font-family: arial;">The ability to build a tablespace compression dictionary on the fly removes what had been a REORG utility execution requirement.</span></li></ul><span style="font-family: arial;"><span style="font-weight: bold;">Resiliency, efficiency, and growth:</span><br /></span><ul><li><span style="font-family: arial;">DB2 9 for z/OS gave us some nice utility performance enhancements (referring particularly to reduced CPU consumption). DB2 10 delivers significant CPU efficiency gains for user application workloads (batch and OLTP).</span></li><li><span style="font-family: arial;">More-granular DB2 authorities enable people to do their jobs while improving the safeguarding of data assets. An example is SECADM, which does not include data access privileges.</span></li><li><span style="font-family: arial;">"Release 2" of DB2's pureXML support improves performance for applications that access XML data stored in a DB2 database.</span></li><li><span style="font-family: arial;">An "ALTER-then-REORG" path makes it easier to convert existing tablespaces to the universal tablespace type introduced with DB2 9.</span><br /></li></ul><span style="font-family: arial;"><span style="font-weight: bold;">Counting down.</span> Roger's 10 favorite DB2 10 features:<br /></span><ul><li><span style="font-family: arial;">10. (tie) Hash access to data records (do with one GETPAGE what formerly might have required five or so GETPAGEs), and index "include" columns (define a unique index, then include one or more additional columns to improve the performance of some queries while retaining the original uniqueness-enforcement characteristic of the index).</span></li><li><span style="font-family: arial;">9. Improved XML data management performance and usability.</span></li><li><span style="font-family: arial;">8. Improved SQL portability.</span></li><li><span style="font-family: arial;">7. Support for temporal (i.e., "versioned") data (something that previously had to be implemented by way of application code).</span></li><li><span style="font-family: arial;">6. The new, more-granular security roles.</span></li><li><span style="font-family: arial;">5. More online schema changes.</span></li><li><span style="font-family: arial;">4. Better catalog concurrency.</span></li><li><span style="font-family: arial;">3. 5X-10X more concurrent users due to the removal of memory constraints.</span></li><li><span style="font-family: arial;">2. CPU cost reductions for DB2-accessing application programs.</span></li><li><span style="font-family: arial;">1. Productivity improvements.</span></li></ul><span style="font-family: arial;"></span><span style="font-family: arial;"><span style="font-weight: bold;">You get a lot of benefits in DB2 10 conversion mode:</span><br /></span><ul><li><span style="font-family: arial;">More CPU-efficient SQL.</span></li><li><span style="font-family: arial;">64-bit addressing support for more of the EDM pool and for DB2 runtime structures (thread-related virtual storage usage). You'll need to rebind to get this benefit.</span></li><li><span style="font-family: arial;">Improved efficiency of single-row retrieval operations outside of singleton SELECTs, thanks to OPEN/FETCH/CLOSE chaining.</span></li><li><span style="font-family: arial;">Distributed thread reuse for high-performance database access threads (aka DBATs).</span></li><li><span style="font-family: arial;">Improved elapsed times for insert operations, thanks to parallelized index updates (for tables on which multiple indexes have been defined).</span></li><li><span style="font-family: arial;">Support for 1 MB pages.</span></li><li><span style="font-family: arial;">Access path enhancements, including the ability to get index matching for multiple in-list predicates in a query.</span></li><li><span style="font-family: arial;">More query parallelism (good for zIIP engine utilization).</span></li><li><span style="font-family: arial;">More avoidance of view materialization (good for efficiency).</span><br /></li></ul><span style="font-family: arial;"><span style="font-weight: bold;">More stuff:</span><br /></span><ul><li><span style="font-family: arial;">Dynamic statement cache hits for statements that are identical except for the values of literals (this requires the use of a new attribute setting).</span></li><li><span style="font-family: arial;">CPU efficiency gains of up to 20% for native SQL procedures (you regenerate the runtime structure via drop and recreate).</span></li><li><span style="font-family: arial;">Hash access to data rows.</span></li><li><span style="font-family: arial;">Index include columns.</span></li><li><span style="font-family: arial;">In-line LOBs (storing of smaller LOB values in base table rows). Roger called these smaller LOBs "SLOBs." LOBs stored in-line in a compressed tablespace will be compressed. In-line storage of LOBs will require a universal tablespace that's in reordered row format (RRF). Said Roger: "RRF is the future."</span></li><li><span style="font-family: arial;">Universal tablespaces can be defined with the MEMBER CLUSTER attribute (good for certain high-intensity insert operations, especially in data sharing environments).</span></li><li><span style="font-family: arial;">"ALTER-then-REORG" to get to a universal tablespace, to change page size, to change DSSIZE (size of a tablespace partition), and to change SEGSIZE. With respect to "ALTER-then-REORG," you'll have the ability to reverse an "oops" ALTER (if you haven't effected the physical change via REORG) with an ALTER TABLESPACE DROP PENDING CHANGES.</span></li><li><span style="font-family: arial;">Online REORG for all catalog and directory tablespaces.</span></li></ul><span style="font-family: arial;"><span style="font-weight: bold;">Scalability improvements:</span><br /></span><ul><li><span style="font-family: arial;">Reduced latch contention.</span></li><li><span style="font-family: arial;">A new option that lets data readers avoid having to wait on data inserters.</span></li><li><span style="font-family: arial;">Much more utility concurrency.</span></li><li><span style="font-family: arial;">64-bit common storage to avoid ECSA constraints.</span></li><li><span style="font-family: arial;">Package binds, data definition language processes, and dynamic SQL can run concurrently.</span></li><li><span style="font-family: arial;">The skeleton package table in the directory will use LOBs. With CLOBs and BLOBs in the DB2 directory, the DSN1CHKR utility won't be needed because there won't be any more links to maintain.</span></li><li><span style="font-family: arial;">SMF records produced by DB2 traces can be compressed: major space savings (maybe 4:1) with a low cost in terms of overhead.</span></li><li><span style="font-family: arial;">"Monster" buffer pools can be used with less overhead.</span></li><li><span style="font-family: arial;">You'll be able to dynamically add active log data sets.</span></li><li><span style="font-family: arial;">You'll be able to grant DBADM authority to an ID for all databases, versus having to do this on a database-by-database basis.</span><br /></li></ul><span style="font-family: arial;"><span style="font-weight: bold;">More catalog and directory changes:</span><br /></span><ul><li><span style="font-family: arial;">The catalog and directory will utilize partition-by-growth universal tablespaces (64 GB DSSIZE).</span></li><li><span style="font-family: arial;">There will be more tablespaces (about 60 more).</span></li><li><span style="font-family: arial;">Row-level-locking will be used.</span></li><li><span style="font-family: arial;">The objects will be DB2-managed and SMS-controlled.</span><br /></li></ul><span style="font-family: arial;">It really is a BIG version -- and there's still more to it (I've just provided what I captured in my note-taking during Roger's session). More nuggets to come in my part 3 post about DB2-by-the-bay. Stay tuned.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0tag:blogger.com,1999:blog-6806654330436722244.post-3694964120120323242010-05-17T05:54:00.000-07:002010-05-17T06:35:23.887-07:00Nuggets from DB2 by the Bay, Part 1<span style="font-family: arial;">I had to smile when I saw the thread that Ed Long of Pegasystems started the other day on the <a href="http://www.idug.org/cgi-bin/wa?A0=DB2-L">DB2-L discussion list</a>. The subject line? "IDUG radio silence." Here it was, IDUG NA week, with the North American Conference of the International DB2 Users Group in full swing in Tampa, Florida, and the usual blogging and tweeting of conference attendees was strangely absent. What's with that? Well, I'll break the silence (thanks for the inspiration, Ed), and I'll start by offering my theory as to why the level of conference-related electronic communication was low: we were BUSY.<br /><br /><span style="font-style: italic;">Busy</span>. That's the word that comes first to mind when I think of this year's event, and I mean it in a good way. At last year's conference in Denver, the mood was kind of on the down side. Attendance was off, due largely to severe cutbacks in organizations' training and travel budgets - a widespread response to one bear of an economic downturn. Those of us who were able to make it to last May's get-together swapped stories with common themes: How tough is it on you? How many people has your company cut? How down is your business? A lot of us were in batten-down-the-hatches mode, and it was hard to get the ol' positive attitude going.<br /><br />What a difference a year makes. The vibe at the Tampa Convention Center was a total turnaround from 2009. Attendance appeared to be up significantly, people were smiling, conversation was upbeat and animated, and there was this overall sense of folks being on the move: heading to this session or that one, flagging someone down to get a question answered, lining up future business, juggling conference activities with work-related priorities -- stuff that happens, I guess, at every conference, but it seemed to me that the energy level was up sharply versus last May. To the usual "How's it going" question asked of acquaintances not seen since last year, an oft-heard response was: "Busy!" To be sure, some folks (and I can relate) are crazy busy, trying to work in some eating and sleeping when the opportunity arises, but no one seemed to be complaining. It felt OK to be burning the candle at both ends after the long dry spell endured last year. Optimism is not in short supply, and I hope these positive trends will be sustained in the months and years to come.<br /><br />In this and some other entries to come (not sure how many -- probably another couple or so) I'll share with you some nuggets of information from the conference that I hope you'll find to be interesting and useful. I'll start with the Tuesday morning keynote session.<br /><br /><span style="font-weight: bold;">The data tsunami: big challenges, but big opportunities, too.</span> The keynote speaker was Martin Wildberger, IBM's VP of Data Management Software Development. He started out talking about the enormous growth in the amount of data that organizations have to manage -- this on top of an already-enormous base. He showed a video with comments by some of the leading technologists in his group, and one of those comments really stuck with me (words to this effect): "You might think that the blizzard of data coming into an organization would blind you, but in fact, the more data you have, the clearer you see." Sure, gaining insight from all that data doesn't just happen -- you need the right technology and processes to <span style="font-style: italic;">make</span> it happen -- but the idea that an organization can use its voluminous data assets to see things that were heretofore hidden -- things that could drive more revenue or reduce costs -- is compelling. As DB2 people, we work at the foundational level of the information software "stack." There's lots of cool analytics stuff going on at higher levels of that stack, but the cool query and reporting and cubing and mining tools just sit there if the database is unavailable. And, data has to get to decision-makers fast. And, non-traditional data (images, documents, XML) has to be effectively managed right along with the traditional numbers and character strings. Much will be demanded of us, and that's good (it'll keep us <span style="font-style: italic;">busy</span>).<br /><br />Martin mentioned that IBM's overall information management investment priorities are aimed at helping organizations to:<br /></span><ul><li><span style="font-family: arial;">Lower costs</span></li><li><span style="font-family: arial;">Improve performance</span></li><li><span style="font-family: arial;">Reuse skills</span></li><li><span style="font-family: arial;">Reduce risk</span></li><li><span style="font-family: arial;">Reduce time-to-value</span></li><li><span style="font-family: arial;">Innovate</span></li></ul><span style="font-family: arial;">He talked up IBM's partnership with Intel, IBM's drive to make it easier for companies to switch to DB2 from other database management systems (especially Oracle), and the "game-changing" impact of DB2 <a href="http://catterallconsulting.blogspot.com/2009/10/wow-db2-data-sharing-comes-to-aixpower.html">pureScale</a> technology, which takes high availability in the distributed systems world to a whole new level. Martin also highlighted the Smart Analytics Systems, including the 9600 series, a relatively new offering on the System z platform (this is a completely integrated hardware/software/services package for analytics and BI -- basically, the "appliance" approach -- that has been available previously only on the IBM Power and System x server lines). There was also good news on the cloud front: DB2 is getting a whole lot of use in Amazon's cloud.<br /><br /><span style="font-weight: bold;">DB2 10 for z/OS: a lot to like.</span> John Campbell, an IBM Distinguished Engineer with the DB2 for z/OS development organization, took the stage for a while to provide some DB2 10 for z/OS highlights (this version of DB2 on the mainframe platform is now in Beta release):</span><span style="font-family: arial;"><br /></span><ul><li><span style="font-family: arial;"><span style="font-style: italic;">CPU efficiency gains.</span> </span><span style="font-family: arial;">For programs written in SQL procedure language, or SQLPL (used to develop "native" SQL procedures and -- new with DB2 10 -- SQL user-defined functions), CPU consumption could be reduced by up to 20% versus DB2 9.</span><span style="font-family: arial;"> Programs with embedded SQL could see reduced in-DB2 CPU cost (CPU cost of SQL statement execution) of up to 10% versus dB2 9, just by being rebound in a DB2 10 system.</span><span style="font-family: arial;"> High-volume, concurrent insert processes could see in-DB2 CPU cost reductions of up to 40% in a DB2 10 system versus DB2 9.</span></li><li><span style="font-family: arial;"><span style="font-style: italic;">64-bit addressing for DB2 runtime structures.</span> John's "favorite DB2 10 feature." With DB2 thread storage going above the 2 GB virtual storage "bar" in a DB2 10 system (after a rebind in DB2 10 Conversion Mode), people will have options that they didn't before (greater use of the RELEASE(DEALLOCATE) bind option, for one thing). DB2 subsystem failures are rare, but when they do happen it's often because of a virtual storage constraint problem. DB2 10 squarely addresses that issue.</span></li><li><span style="font-family: arial;"><span style="font-style: italic;">Temporal data.</span><span style="font-weight: bold; font-style: italic;"> </span>This refers to the ability to associate "business time" and "system time" values to data records. John pointing out that the concept isn't new. What's new is that the temporal data capabilities are in the DB2 engine, versus having to be implemented in application code.</span></li><li><span style="font-family: arial;"><span style="font-style: italic;">Getting to universal.</span> John pointed out that DB2 10 would provide an "ALTER-then-REORG" path to get from segmented and partitioned tablespaces to universal tablespaces.</span></li><li><span style="font-family: arial;"><span style="font-style: italic;">Access plan stability. </span>This is a capability in DB2 10 that can be used to "lock down" access paths for static AND dynamic SQL.</span></li><li><span style="font-family: arial;"><span style="font-style: italic;">Enhanced dynamic statement caching.</span> In a DB2 10 environment, a dynamic query with literals in the predicates can get a match in the prepared statement cache with a statement that is identical except for the literal values (getting a match previously required the literal values to match, too).</span></li></ul><span style="font-family: arial;"></span><span style="font-family: arial;"> <span style="font-weight: bold;">DB2 for LUW performance.</span> John was followed on stage by Berni Schiefer of the DB2 for Linux/UNIX/Windows (LUW) development team. Berni shared some of the latest from the DB2 for LUW performance front:<br /></span><ul><li><span style="font-family: arial;">Performance PER CORE is not an official TPC-C metric, but it matters, because the core is the licensing unit for LUW software. It's on a per-core basis that DB2 for LUW performance really shines versus Sun/Oracle and HP systems.</span></li><li><span style="font-family: arial;">SAP benchmarks show better performance versus competing platforms, with FEWER cores.</span></li><li><span style="font-family: arial;">TPC-C benchmark numbers show that DB2 on the IBM POWER7 platform excels in terms of both performance (total processing power) AND price/performance.</span></li><li><span style="font-family: arial;">DB2 is number one in terms of Windows system performance, but the performance story is even better on the POWER platform.</span></li><li><span style="font-family: arial;">Berni pointed out that DB2 is the ONLY DBMS that provides native support for the DECFLOAT data type (based on the IEEE 754r standard for decimal floating point numbers). The POWER platform provides a hardware boost for DECFLOAT operations.</span></li><li><span style="font-family: arial;">DB2 for LUW does an excellent job of exploiting flash drives.</span></li></ul><span style="font-family: arial;"><span style="font-weight: bold;">Back to Martin for a keynote wrap-up.</span> Martin Wildberger came back on stage to deliver a few closing comments, returning to the topic of DB2 pureScale. pureScale is a distributed systems (AIX/POWER platform) implementation of the shared data architecture used for DB2 for z/OS data sharing on a parallel sysplex mainframe cluster. That's a technology that has delivered the availability and scalability goods for 15 years. So now DB2 for AIX delivers top-of-class scale-up AND scale-out capabilities.<br /><br />Martin closed by drawing attention to the IBM/IDUG <a href="http://www.idug.org/do-you-db2/do-you-db2-front-page.html">"Do You DB2?"</a> contest. Write about your experience in using DB2, and you could win a big flat-screen TV. If you're based in North America, check it out (this initial contest does have that geographic restriction).<br /><br />More nuggets from IDUG in Tampa to come in other posts. Gotta go now. I'm BUSY.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com4tag:blogger.com,1999:blog-6806654330436722244.post-14459024406762812032010-04-28T18:31:00.001-07:002010-04-28T19:21:30.872-07:00This blog has moved<br /> This blog is now located at http://catterallconsulting.blogspot.com/.<br /> You will be automatically redirected in 30 seconds, or you may click <a href='http://catterallconsulting.blogspot.com/'>here</a>.<br /><br /> For feed subscribers, please update your feed subscriptions to<br /> http://catterallconsulting.blogspot.com/feeds/posts/default.<br /> Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0tag:blogger.com,1999:blog-6806654330436722244.post-19806705792264492352010-04-28T08:33:00.000-07:002011-09-20T09:12:54.498-07:00Using DB2 Stored Procedures, Part 3: Schema Visibility<span style="font-family:arial;">This is the third of a three-part entry on various factors you could consider in determining how (or whether) DB2 stored procedure technology might be employed in your organization's application environment. In <a href="http://catterallconsulting.blogspot.com/2010/04/using-db2-stored-procedures-part-1.html">part one</a> I described some advantages associated with client-side and server-side SQL and pointed out that, if you like your SQL on the database server side of things (and I do), DB2 stored procedures can help you go that route. In <a href="http://catterallconsulting.blogspot.com/2010/04/using-db2-stored-procedures-part-2.html">part two</a>, I examined stored procedure usage from the static SQL angle, noting that stored procedures provide a way for client-side programs to easily -- and dynamically, if needs be -- invoke static SQL statements. In this final entry in the series, I'll look at stored procedures as a means of limiting database schema visibility in an application development sense.<br /><br /><span style="font-weight: bold;">"Schema" clarified.</span> In a strictly DB2 context, the term "schema" essentially refers to a categorizing mechanism -- a way to logically set apart a set of set of database objects through the use of a common high-level qualifier in the objects' names. So, for example, I could have a REGION4 schema that might include tables, views, and stored procedures -- all with a high-level qualifier of REGION4 (table REGION4.SALES, stored procedure REGION4.NEW_ORDER, etc.). Schemas are handy because they allow me to code SQL statements with unqualified object names and to use these statements with a particular set of objects by supplying the schema name at package bind time (for static SQL) or via the CURRENT SCHEMA special register (for dynamic SQL).<br /><br />With that said, this is NOT the way I'm using "schema" in this blog entry. Instead, I'm using the term in the more general sense, as a reference to the design of a database: its tables, columns, relationships, etc. There was a time, not so long ago, when people developing application programs that would retrieve or change DB2-managed data had to know a good bit about the design of the target database. In recent years, application architecture has shifted from monolithic to layered and service-oriented, and today's applications often have loosely-coupled tiers that broadly concern user interface (UI), business logic, and data access functionality. In such an application environment, you should think about how "high up," within an application's functional layers, database schema visibility should extend. To put it another way: at what levels within the functional stack should programmers be require to have knowledge of the underlying database design?<br /><br /><span style="font-weight: bold;">Who should know what?</span> This isn't a question of turf I'm talking about -- it has nothing to do with database people saying, "Hey, developers! That's MY knowledge, and I'm not going to share it! Nyah, nyah!" Some of the more vocal advocates of database schema abstraction are application architects. I recall overhearing a top-notch programmer complaining to a colleague that visibility of the database schema extended all the way to the UI layer of an application in development. It wasn't that he couldn't deal with this -- he had excellent SQL skills, and he knew a lot about database design himself. The crux of this guy's argument was that he shouldn't HAVE to be concerned with details of the database design when coding at a higher level of the application.<br /><br />That programmer is not a whiner. He has a good point. For many application architects, the database schema is "plumbing" about which most developers shouldn't be concerned. If coders can retrieve, manipulate, and persist data without having to know a lot about the design of the underlying relational database, they can focus more of their attention on providing functionality needed by the business and by application users, thereby boosting their productivity. DB2 stored procedures can facilitate database schema abstraction: they provide data access services needed by (for example) business-layer application programmers, and they limit the requirement for database design knowledge to the people who develop the stored procedures. [I should point out here that some people consider stored procedures themselves to be part of the schema of a database, and that, as such, they should also be abstracted from the perspective of a consumer of stored procedure-provided services. These folks would argue that stored procedures should be invoked by higher-level programs not through SQL CALLs, but by way of something like Web services calls. A DB2 stored procedure can indeed be exposed as a Web service, and going with this approach would involve weighing the benefit of more complete schema abstraction against the additional path length introduced by the Web services call on top of the SQL CALL for stored procedure invocation.]<br /><br /><span style="font-weight: bold;">A benefit for DBAs, too.</span> When business-logic developers code to the schema of a database, the flexibility that DBAs have to effect performance-enhancing database design changes can be severely limited. I was once involved in a discussion among some DB2 DBAs about a database design change that had the potential to significantly improve the CPU cost-efficiency of a key application process. The proposed modification was ultimately shelved because one of the DB2 tables that would be redesigned was accessed by something like 1000 different application programs, and changing all that code was not a feasible proposition. When stored procedures provide access to the database, schema changes tend to be more do-able -- yes, code in affected stored procedures has to be altered, but the scope of this effort will often be less than that which would be faced if business-logic programs directly referenced database tables. If a given stored procedure services 10 business-logic programs, changing the one stored procedure to accommodate a database schema change looks better to me than modifying the 10 business-logic programs that would have to be updated were the stored procedure not serving their data access needs.<br /><br /><span style="font-weight: bold;">And don't forget security.</span> Database schema information -- table names, column names, entity relationships -- can be of value to someone who wants to gain access to your organization's data in an unauthorized manner. The more limited the dissemination of database design details, the less likely it is that this information will be used for malicious purposes. If your DB2 database is accessed by way of stored procedures, only stored procedure developers need detailed knowledge of the database schema. It's a good risk mitigation move.<br /><br />So, there's my reason number three for using DB2 stored procedures: they enable database schema abstraction, which 1) allows business-logic and UI developers to focus less on application "plumbing" and more on application functionality, 2) provides DBAs with more flexibility in terms of implementing performance-improving database design changes, and 3) limits the spread of detailed database design information that could potentially be used by someone with ill intent. I hope that you'll find a reason to put DB2 stored procedures to work at your organization.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0tag:blogger.com,1999:blog-6806654330436722244.post-20931214062956977282010-04-19T19:39:00.000-07:002011-09-20T09:10:18.364-07:00Using DB2 Stored Procedures, Part 2: The Static SQL Angle<span style="font-family:arial;">This is the second of a three-part entry on various factors you could consider in determining how (or whether) DB2 stored procedure technology might be employed in your organization's application environment. In <a href="http://catterallconsulting.blogspot.com/2010/04/using-db2-stored-procedures-part-1.html">part one</a> I described some advantages associated with client-side and server-side SQL and pointed out that, if you like your SQL on the database server side of things (and I do), DB2 stored procedures provide a very useful means of going that route. In this entry I'll examine stored procedure usage in a static SQL context. In part three, I'll look at how your use of stored procedures might be influenced by your desire for more, or less, database schema visibility from an application development perspective.<br /><br /><span style="font-weight: bold;">The pros of static SQL. </span>Static SQL is, as far as I know, an exclusively DB2 concept, so if you're kind of new to DB2 you might want to check out <a href="http://catterallconsulting.blogspot.com/2010/01/some-basic-information-about-sql-in-db2.html">the blog entry that I posted a few weeks ago</a>, on the basics of SQL (including static SQL) in DB2-accessing programs. Those of you who are familiar with static SQL probably know that it delivers two important advantages versus dynamic SQL:<br /></span><ul><li><span style="font-family:arial;"><span style="font-style: italic;">Better performance</span> -- In particular, I'm talking about the database server CPU consumption aspect of application performance. The CPU efficiency advantage delivered by static SQL is, of course, based on the fact that preparation of static SQL statements (referring to preparation for execution by DB2, which includes things such as data access path selection by the DB2 optimizer) is accomplished before the statements are ever issued for execution by the associated application program. This is important because for some simple, quick-running SQL statements, the CPU cost of statement preparation can be a several times the cost of statement execution. Can dynamic statement caching reduce the database-server-CPU-cost gap between static and dynamic SQL? Yes, but even if you have repeated execution of parameterized dynamic SQL statements (so as to have a high "hit ratio" in the prepared statement cache), generally the best you can hope for is database server CPU consumption that approaches -- but doesn't match -- the CPU cost of an equivalent static SQL workload (the effectiveness of dynamic statement caching gets a boost with DB2 10 for z/OS, which provides for better prepared statement cache matching for dynamic SQL statements that include literal values -- versus parameter markers -- in query predicates).</span><br /></li></ul><ul><li><span style="font-family:arial;"><span style="font-style: italic;">More-robust security</span> -- For a dynamic SQL statement to execute successfully, the authorization ID of the application process issuing the statement has to have read (for queries) and/or data-change privileges on the tables (and/or views) named in the statement. Such is not the case for a static SQL statement. All that is needed, authorization-wise, for successful execution of a static SQL statement is the execute privilege on the statement-issuing program's DB2 package.</span></li></ul><span style="font-family:arial;">Now, I'll concede that there are times when dynamic SQL is a very good choice for an application program. A developer at one of my clients had to write a program that would retrieve information from a DB2 database according to a combination of search arguments that a user could enter on a screen. There were quite a few search options, and coding a static DECLARE CURSOR statement for every possible combination would have been a major piece of work. The developer opted instead to have his program build the SELECT statement dynamically, based on the choices entered by the user. This was a good choice, as it enabled him to deliver the needed functionality in a much more timely manner than would have been the case had he opted for the static SQL approach. Still, with exceptions such as this one duly noted, my preference is to go with static SQL in most cases, especially in OLTP and batch application environments (dynamic SQL often dominates in a data warehouse system, and that's OK).<br /><br />Can static SQL statements be issued from client-side application programs? The answer to that question depends on the programming language used. If a client-side program is written in assembler, C, C++, COBOL, Fortran, PL/I or REXX, it can issue static SQL statements (for assembler and PL/I, the client platform has to be a mainframe). If the language is Java, static SQL can be used by way of SQLJ. Another Java option for static SQL is IBM's Optim pureQuery Runtime product, which can also enable the use of static SQL when client-side programs are written in VB.NET or C#. If the language used on the client side is Perl, Python, PHP, or Ruby, the only possibility is dynamic SQL.<br /><br />This is where DB2 stored procedures come in. Suppose you want to use static SQL for your application, but you want to use, say, Ruby on the client side? Or, suppose you're using Java on the client side, but your Java developers don't want to use SQLJ (and you don't have Optim pureQuery Runtime)? Well, how about putting the static SQL in DB2 stored procedures, and having your client-side programs invoke these by way of dynamic CALLs? You get the performance and security benefits of static SQL, and your client-side developers get to use their language (and data-access interface) of choice. Everybody's happy!<br /><br />That's reason number two for using DB2 stored procedures: they provide a means whereby static SQL can be easily -- and dynamically, if needs be -- invoked by client-side programs. Stop by the blog again soon for my reason number three.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com2tag:blogger.com,1999:blog-6806654330436722244.post-81411732797181130352010-04-16T20:02:00.000-07:002010-04-18T21:50:52.423-07:00Looking Forward to DB2 by the Bay<span style="font-family:arial;">That would be Tampa Bay, and I'm talking about the International DB2 Users Group North America Conference that will be held May 10 - 14 at the Tampa Convention Center (you can get all the details at <a href="http://www.idug.org/db2-north-american-conference/idug-2010-north-america.html">IDUG's Web site</a>).<br /><br />I've participated in every IDUG North America conference since 1997, and I've always found these events to be very well worth my time and dollars. I'll be delivering a presentation on <a href="http://www.idug.org/ocs/index.php/NA10/NA10/paper/view/784">mainframe DB2 data warehousing</a> at this year's conference, and I'll also participate, along with Paul Zikopoulos of IBM's DB2 for Linux/UNIX/Windows (LUW) development organization, in a <a href="http://www.idug.org/ocs/index.php/NA10/NA10/paper/view/803">"Face 2 Face"</a> interactive-discussion session on DB2 Data Warehousing (such sessions were known in the past as SIGs, or Special-Interest Groups).<br /><br />Why do I attend the IDUG North American Conference every year? There are lots of reasons. For one thing, I really enjoy hearing the latest about DB2 technology from folks in the IBM DB2 development organization -- people like Curt Cotner, IBM Fellow and DB2 Chief Technology Officer; Terry Purcell, Mr. DB2 for z/OS Optimizer; Jeff Josten, from whom I've learned so much about DB2 data sharing; John Campbell, who brings a wealth of lessons learned working with early implementers of new DB2 for z/OS releases; Guy Lohman, a Big Thinker (and doer) from IBM's Almaden Research Center; Matt Huras, Chief Architect of DB2 for LUW; Chris Eaton, who always delivers a ton of DB2 for LUW "news you can use"; and Leon Katsnelson, DB2 for LUW jock and cloud computing savant (you can see the whole <a href="http://www.idug.org/ocs/index.php/NA10/NA10/schedConf/schedule#schedule">conference schedule</a> on the IDUG Web site).<br /><br />In addition to learning lots from IBM's DB2 top guns, I get a boatload of great information from fellow DB2 consultants who present at the conference: Bonnie Baker, Dave Beulke, Sheryl Larsen, Susan Lawson, Dan Luksetich, and Fred Sobotka, just to name a few. Also among the speakers are professional DB2 instructors like Themis's David Simpson, and technical experts from leading vendors of DB2 tools, such as Phil Grainger from Cogito, Steen Rasmussen from CA, and Rick Weaver from BMC.<br /><br />And then you have the user presentations. These are what really make IDUG a special conference. You can't beat the in-the-trenches experiences shared by Dave Churn of DST Systems, Rob Crane of FedEx, John Mallonee of Highmark, Bernie O'Connor of Anixter, Bryan Paulsen of John Deere, Billy Sundarrajan of Fifth Third Bank, and others who work where the DB2 rubber meets the road.<br /><br />Of course, I also learn plenty thanks to encounters in the "coffee track" -- a reference to the refreshment and meal breaks during which I'm likely to get in on conversations amongst people who are dealing with challenges and issues that are of particular interest to me. These networking opportunities alone are almost worth the price of admission. The learning continues in the exhibitors hall, where I can catch up on the latest DB2-related offerings from a wide variety of vendors (and pick up a few t-shirts, pens, and notebooks, to boot).<br /><br />As if all that weren't enough, the IDUG North American Conference is a great place to take a DB2 certification exam (free for attendees) and get some hands-on at any of several lab sessions.<br /><br />The location's a winner, too. IDUG negotiated a special rate at the Marriott Waterside Hotel, right next to the convention center, and some of those discounted rooms are, I think, <a href="http://www.marriott.com/hotels/travel/tpamc-tampa-marriott-waterside-hotel-and-marina/?toDate=5/16/10&groupCode=iduidua&fromDate=5/7/10&app=resvlink">still available</a>. Tampa's a great town. Personally, I like to get up in the morning and take a run along Bayshore Boulevard, just outside of downtown. Here you'll find the world's longest continuous sidewalk (six miles), so there are no worries about cars -- just cruise along with the waters of Tampa Bay on your left (if outbound) and the beautiful historic homes of the Hyde Park neighborhood on your right. There's plenty of nightlife in Ybor City, a short streetcar ride away. Great Cuban food, fresh seafood, palm trees -- all this, and a great conference, too? Who wouldn't want to be there? I hope some of you will be able to attend. Find me and say hi if you do.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0tag:blogger.com,1999:blog-6806654330436722244.post-38818785069792438962010-04-11T19:10:00.000-07:002010-04-11T19:27:10.305-07:00Using DB2 Stored Procedures, Part 1: Where Do You Want Your SQL?<span style="font-family: arial;">Over the past few weeks, I've had a number of interesting discussions with people from various organizations on the subject of DB2 stored procedures. These conversations have generally involved questions pertaining to the use of stored procedures in a DB2 environment. The question, "How should we use DB2 stored procedures?" will be answered differently by different organizations, based on their particular requirements and priorities. That said, their are some<br />factors that ought to be considered in any review of DB2 stored procedure utilization. I'll cover these factors in a three-part entry beginning with this post, which will focus on the issue of client-side versus server-side SQL ("server" here referring to a database server, versus an application server). In part two I'll take a look at static versus dynamic SQL, and in part three I'll examine the issue of database schema visibility.<br /><br /><span style="font-weight: bold;">Some advantages of client-side SQL:</span> I've heard various opinions regarding client-side versus server-side SQL (and from the DB2 data server perspective, "client-side" will usually refer to programs, running in an application server, that access a DB2 database). Proponents of having SQL data manipulation language, or DML, statements (i.e., SELECT, INSERT, UPDATE, and DELETE) issued by client-side programs cite, among other things, these benefits:<br /></span><ul><li><span style="font-family: arial;"><span style="font-style: italic;">Data-consuming and data-retrieval programs can be implemented in a single, client-side deployment</span> -- This as opposed to a client-side deployment of data-consuming programs and a server-side deployment of associated data retrieval programs. A "one-side" deployment can indeed require less in the way of coordination, with maybe one programming team involved, versus two, and one platform team -- of application server administrators -- with primary responsibility for system management (though it's hoped that DB2 DBAs would be in the loop, as well).</span></li></ul><ul><li><span style="font-family: arial;"><span style="font-style: italic;">Data-consuming and data-retrieval programs are likely to be written in the same language.</span> It's true that client-side and server-side programs are often coded using different languages, especially when the application and database servers are running on different platforms (it's common, for example, to have sever-side SQL embedded in COBOL programs when the DB2 server is a mainframe, while data-consuming programs might be written in Java and might run on a Linux-based application server). When different programming languages are used on client and data-server platforms, communication between the respective development groups can be a little more challenging than it otherwise would be (particularly when an object-oriented language is used on the one side and a procedural language on the other).</span></li></ul><span style="font-family: arial;"><span style="font-weight: bold;">Server-side SQL pluses:</span> People who like their SQL on the server side favor this approach for several reasons, including the following:<br /></span><ul><li><span style="font-family: arial;"><span style="font-style: italic;">It makes it easy to use static instead of dynamic SQL.</span> I'll cover static SQL and its connection with stored procedures in part two of this three-part entry. For now, I'll point out that, compared to dynamic SQL, static SQL generally delivers better performance (especially in terms of database server CPU time) and provides for more robust security (an authorization ID does not have to be granted access privileges on database tables in order to execute static SQL statements). Can static SQL be issued from client-side programs? Of course, but it's not always a straightforward matter, as I'll point out in my part-two post.</span><br /></li></ul><ul><li><span style="font-family: arial;"><span style="font-style: italic;">It can increase the degree to which data-access code is reused.</span> When data-access code is packaged in server-side programs, it's generally pretty easy to invoke that code from data-consuming programs written in a variety of languages and running on a variety of platforms (including the platform on which the DB2 data server is running). Is this technically possible when the data-access code is running on an application server? Yes, but -- as is the case regarding the use of static versus dynamic SQL -- I believe that data-access code reuse is a more straightforward proposition when that code runs on the data server.</span><br /></li></ul><ul><li><span style="font-family: arial;"><span style="font-style: italic;">It makes it easier to utilize developers with different programming language skills for the same application project.</span> Admittedly, this is only an advantage if your organization HAS groups of developers with expertise in different programming languages and WANTS to use them for the same project. If that is indeed your situation, server-side SQL could be the ticket for you. It's quite common for data-consuming programs written in languages such as Java and C# to invoke data-access programs written in COBOL or SQL (the latter being a reference to SQL stored procedures).</span></li></ul><ul><li><span style="font-family: arial;"><span style="font-style: italic;">It makes it easier to leverage the talents of skilled SQL coders.</span> SQL becomes more and more rich with each release of DB2 (one indicator being the number of pages in the DB2 SQL Reference). That's a good thing, but it also means that SQL mastery becomes a taller order as time goes by. I've seen in recent years the emergence of professionals who consider themselves to be SQL programmers (this as opposed to, say, Java programmers who know SQL). I feel that server-side SQL facilitates the leveraging of individuals who have top-notch SQL skills.</span></li></ul><ul><li><span style="font-family: arial;"><span style="font-style: italic;">It improves CPU efficiency for transactions that issue multiple SQL DML statements.</span> When SQL DML statements are issued from client-side programs, there are network send and receive operations associated with each such statements. These network trips increase application overhead when transactions issue multiple SQL DML statements -- overhead that is reduced when a client-side program invokes a server-side program that issues the multiple SQL DML statements locally to the DB2 server.</span></li></ul><span style="font-family: arial;"><span style="font-weight: bold;">The stored procedure angle:</span> If you like your SQL on the DB2 server side of an application (I do), stored procedures provide a great way to go this route. They can be written in a number of languages (including, as mentioned, SQL), and they come with a nice familiarity factor, in that the stored procedure pattern is well-known to many client-side application developers, including those who have worked with DBMSs other than DB2. Note that a DB2 stored procedure<br />program can declare and open a cursor in such a way that the result set rows can be fetched by the calling client program -- a useful feature for set-level data retrieval operations. [Mainframers should be aware that native SQL procedures, introduced with DB2 9 for z/OS, can run substantially on zIIP engines when called by DRDA clients through the DB2 distributed data facility -- a prime opportunity to put cost-effective zIIP MIPS to good use.] <br /><br />So, reason number one for using DB2 stored procedures: they are an excellent choice for the packaging of SQL statements in database server-side programs. My reasons two and three for using DB2 stored procedures will be explained in parts two and three of this three-part blog entry.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0tag:blogger.com,1999:blog-6806654330436722244.post-18312890724130710542010-03-25T14:34:00.000-07:002011-09-20T09:06:10.023-07:00A Closer Look at DB2 9 for z/OS Index Compression<span style="font-family:arial;">In May of last year I posted <a href="http://catterallconsulting.blogspot.com/2009/05/much-ado-about-db2-indexes-part-1.html">a blog entry</a> that included some information about the index compression capability introduced with DB2 9 for z/OS. It's a good time, I think, to add to that information, and I'll do that by way of this post.<br /><br /><span style="font-weight: bold;">How does DB2 do it?</span> In that entry from last year, I noted that DB2 9 index compression is not dictionary-based, as is DB2 data compression (with dictionary-based compression, commonly occurring strings of data values are replaced with shorter strings, and this replacement is reversed when the data is accessed). For a tablespace defined with COMPRESS YES, DB2 will place as many compressed data rows as it can into a 4K page in memory (or an 8K or 16K or 32K page, depending on the buffer pool to which the tablespace is assigned), and the page size in memory is the same as the page size on disk. Index compression reduces space requirements on disk but not in memory: the size of a leaf page in a compressed index will be smaller on disk than in memory (only leaf pages are compressed, but the vast majority of most indexes' pages are leaf pages). Index compression is based on getting the contents of an 8K or 16K or 32K index leaf page in memory into a 4K page on disk, without using a dictionary (an index has to be assigned to an 8K or 16K or 32K buffer pool in order to be compressed). To do this, DB2 uses a combination of three compression mechanisms:<br /></span><ul><li><span style="font-family:arial;"><span style="font-style: italic;">Prefix compression</span>: Suppose you had a 3-column key on state, city, and telephone number. You might then have a LOT of duplicates of some combinations of column 1 and column 2 values (e.g., state = 'Texas' and city = 'Houston'). If compression is used for this index, DB2 will not repeatedly store those duplicate “prefix” values in leaf pages on disk; instead, DB2 will store a given key prefix once in a compressed leaf page on disk, and store along with that prefix the part of the whole-key key value that is different from one entry to the next (e.g., phone number = '713-111-2222', phone number = '713-222-3333', etc.). Note that while this example (for the sake of simplicity) presents a prefix that breaks along key-column lines, this is not a restriction. In other words, a prefix, in the context of prefix compression, can include just a portion of a key column value (for example, 'ROBERT' could be a prefix for the last names 'ROBERTS' and 'ROBERTSON').</span><br /></li></ul><span style="font-family:arial;"></span><ul><li><span style="font-family:arial;"><span style="font-style: italic;">RID list compression</span>: If a given index key value has many duplicates, several of these duplicate values could be in rows that are located in the same page of a table. If the index is not compressed, the full RID (row ID) of each of these rows will be stored following the key value in a leaf page on disk, even though only one byte of that four- or five-byte RID (the byte that indicates the row's position in the data page) will be different from one value to the next in the RID chain (the page number, occupying 4 bytes for a partitioned tablespace with a DSSIZE of 4 GB or larger, and 3 bytes otherwise, will stay the same). If that index were to be compressed, DB2 would save space on disk by storing the multi-byte page number once in the RID chain, followed by the single-byte row location indicators, until the page number (or the key value) changes. This compression technique is particularly effective for indexes that<br />have relatively low cardinality and are either clustering or have a high degree of correlation with the table's clustering key.</span></li></ul><ul><li><span style="font-family:arial;"><span style="font-style: italic;">In-memory-only key map</span>: An uncompressed index leaf page contains a key map, which itself contains a 2-byte entry for each distinct key value stored in the page. If the index is compressed, this map will not be stored on disk (it will be reconstructed, at relatively low cost, when the leaf page is read into memory). This compression technique nicely complements the RID list compression mechanism, as it is most effective for high-cardinality indexes (especially those with short keys, as the more distinct key values a page holds, the more space the key map occupies).</span></li></ul><span style="font-family:arial;">These compression techniques often deliver impressive results, with plenty of DB2 9 for z/OS users reporting disk savings of 50-70% after enabling compression for an index. Still, they have their limits, and when DB2 determines that a leaf page in memory already holds as much as can be compressed onto a 4K page on disk, it will stop placing entries in that page, even if that means letting a good bit of space go unused in the in-memory page. This is why you want to run the DSN1COMP utility for an index prior to compressing it. DSN1COMP will provide estimates of disk space savings on the one hand, and in-memory page space wastage on the other hand, that you could expect to see based on your choice of an 8K, 16K, or 32K page size for the to-be-compressed index. The right index page size will be the one that maximizes disk space savings while minimizing in-memory page space wastage.<br /><br /><span style="font-weight: bold;">Index compression overhead: it's about I/Os, not access.</span> The differences in the way that data and indexes are compressed in a DB2 9 for z/OS environment lead to differences regarding the associated CPU overhead. First of all, data compression is hardware-assisted (it takes advantage of a microcode assist built into the System z server line) while index compression is not. Second, in the case of data compression, the overhead cost is paid when data is accessed in a buffer pool in memory, as rows are not decompressed until they are retrieved by DB2 on behalf of an application process (similarly, new or changed rows are compressed and placed in pages in memory as part of insert and update operations). For a compressed index, the overhead cost is incurred at I/O time, since pages are decompressed when read into memory and compressed when written to disk. So, once a leaf page of a compressed index is in memory, repeated accesses of that page will not involve additional overhead due to compression, whereas data compression overhead is incurred every time a row is retrieved from, or placed into, a page in memory. With respect to the I/O-related cost of compression, the situation is reversed: there is no additional overhead associated with reading a compressed data page into memory from disk, or writing such a page to disk, while for a compressed index the CPU cost of reading a leaf page from disk, or writing a changed leaf page to disk, will be higher than it would be for a non-compressed index. One take-away from this is that large buffer pools are a good match for compressed indexes, as fewer disk I/Os means lower compression overhead.<br /><br />This "pay at I/O time" aspect of the CPU cost of index compression has implications for <span style="font-style: italic;">where</span> that cost shows up. If the I/O is of the prefetch read variety, or a database write, the leaf page compression cost will be charged to the DB2 database services address space (aka DBM1). If it's a synchronous read I/O, index compression overhead will affect the class 2 CPU time of the application process for which the on-demand read is being performed. Thus, for an application that accesses index leaf pages that are read in from disk via prefetch reads (as might be the case for a batch job or a data warehouse query), the cost of index compression may appear to be close to zero because it's being paid by the DB2 database services address space.<br /><br />So, what kind of overhead should you expect to see, in terms of the in-DB2 CPU cost of applications that access compressed indexes? Because of the multiple variables that come into play, mileage will vary, but my expectation is that you'd see in-DB2 CPU consumption that would be higher by some single digit of percent versus the non-compressed case. Remember: keep the I/Os down to keep that cost down.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com1tag:blogger.com,1999:blog-6806654330436722244.post-83822508547846469172010-03-17T20:03:00.000-07:002010-03-17T20:27:14.821-07:00My Favorite DB2 9 for z/OS BI Feature<span style="font-family:arial;">A lot of the consulting work that I do relates to the use of DB2 for z/OS as a data server for business intelligence (BI) applications. DB2 9 for z/OS delivered a number of new features and functions that are particularly attractive in a data warehousing context, including index compression, index-on-expression, instead-of triggers, the INTERSECT and EXCEPT set operators, and the OLAP functions RANK, DENSE_RANK, and ROW_NUMBER. That's all great stuff, but my favorite BI-friendly DB2 9 feature -- and the subject of this blog entry -- is global query optimization.<br /><br />I'll admit here that I didn't fully appreciate the significance of global query optimization when I first heard about it. There was something about virtual tables, and correlating and de-correlating subqueries, and moving parts of a query's result set generation process around relative to the location of query blocks within an overall query -- interesting, but kind of abstract as far as I was concerned. Time and experience have brought me around to where I am today: a big-time global query optimization advocate, ready to tell mainframe DB2 users everywhere that this is one outstanding piece of database technology. I'll give you some reasons for my enthusiasm momentarily, but before doing that I'd like to explain how global query optimization works.<br /><br />Adarsh Pannu, a member of IBM's DB2 for z/OS development team, effectively summed up the essence of global query optimization when he said in a recent presentation that it's about "improved subquery processing." That really is it, in a nutshell, and here's the first of the BI angles pertaining to this story: queries that contain multiple subquery predicates are common in data warehouse environments. These subquery predicates might be in the form of non-correlated in-list subqueries, like this one:<br /><br /><span style="font-family:courier new;">WHERE T1.C1 IN (SELECT C2 FROM T2 WHERE…)</span><br /><br />Alternatively, a subquery predicate might be a match-checking correlated subquery like this one:<br /><br /><span style="font-family:courier new;">WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.C2 = T1.C1…)</span><br /><br />Prior to Version 9, in evaluating a query containing one or more subqueries DB2 would optimize each SELECT in isolation, without regard to the effect that the access path chosen for a subquery might have on the performance of the query overall. Furthermore, DB2 would handle subquery processing based on the position of the subquery within the overall query -- in other words, if a subquery were a good ways "down" in an overall query, it wouldn't be evaluated "up front" at query execution time. Using this approach, DB2 might well choose the optimal access path for each individual SELECT in a query, but the access path for the query overall could end up being quite sub-optimal in terms of performance.<br /><br />DB2 9 takes a different approach when it comes to optimizing a query containing one or more subquery predicates. For one thing, it will evaluate a subquery predicate in the context of the overall query in which the subquery appears. Additionally, DB2 9 can do some very interesting transformative work in optimizing the overall query. It might, for example, change a correlated subquery predicate to a non-correlated subquery, materialize that result set in a work file, and -- treating this materialized result set as a virtual table -- "move” it to a different part of the overall query and join it to another table referenced in the query. Needless to say, such transformations -- correlated subquery to non-correlated subquery (or vice versa), subquery to join -- are always accomplished in such a way as to preserve the result set of the query as initially coded.<br /><br />An example can be very useful in showing how all this comes together in the processing of a subquery-containing query in a DB2 9 environment. Suppose you have a query like the one below, in which a correlated subquery predicate has been highlighted in green:<br /><br /><span style="font-family:courier new;">SELECT * FROM TABLE_1</span><br /><span style="color: rgb(0, 153, 0); font-weight: bold;font-family:courier new;" >WHERE EXISTS<br /></span><span style="color: rgb(0, 153, 0); font-weight: bold;font-family:courier new;" >(SELECT 1 FROM TABLE_2<br /></span><span style="color: rgb(0, 153, 0); font-weight: bold;font-family:courier new;" >WHERE TABLE_1.COL1 = TABLE_2.COL1</span><br /><span style="color: rgb(0, 153, 0); font-weight: bold;font-family:courier new;" >AND TABLE_2.COL2 = 1234<br /></span><span style="font-weight: bold;font-family:courier new;" ><span style="color: rgb(0, 153, 0);">AND…)</span> </span><br /><br />The EXPLAIN output for the query, showing the working of global query optimization, might look like this (and here we see just a few of the relevant columns from a PLAN_TABLE):<br /><br /></span><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://www.catterallconsulting.com/uploaded_images/Plan_table-702617.bmp"><img style="display: block; margin: 0px auto 10px; text-align: center; cursor: pointer; width: 400px; height: 161px;" src="http://www.catterallconsulting.com/uploaded_images/Plan_table-702576.bmp" alt="" border="0" /></a><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://www.catterallconsulting.com/uploaded_images/Plan_table-756251.bmp"><br /></a><br /><span style="font-family:arial;">Here's what the EXPLAIN output is telling you: the optimizer changed the match-checking correlated subquery highlighted in green to a non-correlated subquery (evidenced by “NCOSUB” in the QBTYPE column of the PLAN_TABLE). This result set (which you’d get if you removed the predicate in the correlated subquery that contains the correlation reference to a column in TABLE_1) is sorted to remove duplicates (this to help ensure that the overall query’s result set will not be changed due to the query transformation) and to get the result set rows ordered so as to boost the performance of a subsequent join step (see the “Y” values under SORTC_UNIQ (shortened to SCU) and SORTC_ORDERBY (SCO) of the PLAN_TABLE). Then, the materialized and sorted subquery result set, identified as “virtual table” DSNWFQB(02), with the “02” referring to the query block from which the result set was generated (the “de-correlated” subquery), is moved to the “top” of the query and nested-loop joined to TABLE_1 (see the “1” under the METHOD (shortened to M) column of the PLAN_TABLE). It all boils down to this: the correlated subquery predicate is used to match rows in TABLE_1 with rows in TABLE_2, and in this case the optimizer has determined that the required row-matching can be accomplished more efficiently with a join versus the correlated subquery. It's a big-picture -- as opposed to a piece-part -- approach to query optimization.</span><br /><br /><span style="font-family:arial;">So, why am I big on query optimization? Reason 1 is the potentially huge improvement in performance that can be realized for a subquery-containing query optimized by DB2 Version 9 versus DB2 Version 8 (I'm talking about an order of magnitude or more improvement in elapsed and CPU time in some cases). Reason 2 is the fact that global query optimization can greatly improve query performance without requiring that a query be rewritten. That's important because BI queries are often generated by tools, and that generally means that query rewrite is not an option. It's possible that some of the complex queries in a data warehouse environment might be generated by application code written by an organization’s developers (or by people contracted to write such code), and if such is the situation you could say that query rewrite (via modification of the query-generating code) is technically possible. Even in that case, though, rewriting a query could be very difficult. In my experience, custom-coded “query-building engines” may construct a query by successively applying various business rules, and this often takes the form of adding successive subquery predicates to the overall query (these might be nested in-list non-correlated subqueries). To “just rewrite” a query built in this way is no small thing, because coding the query with more in the way of joins and less in the way of subqueries would require more of an “all at once” application of business rules versus the more-straightforward successive-application approach. Thankfully, global query optimization will often make query rewrite a non-issue.<br /><br /></span><span style="font-family:arial;">I'll conclude with this thought: if you have a data warehouse built on DB2 for z/OS Version 8, global query optimization is one of the BEST reasons to migrate your system to DB2 9. If you're already using DB2 9, but not in a BI-related way, global query optimization is a great reason to seriously consider using DB2 9 for data warehousing.</span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0tag:blogger.com,1999:blog-6806654330436722244.post-46419448801249222662010-03-05T07:27:00.000-08:002011-09-20T09:03:55.636-07:00Another Note on DB2 for z/OS Buffer Pool Page-Fixing<span style="font-family:arial;">In the summer of 2008, I posted <a href="http://catterallconsulting.blogspot.com/2008/07/note-on-db2-for-zos-page-fixed-buffer.html">a blog entry on page-fixing DB2 buffer pools</a>, a feature introduced with DB2 for z/OS Version 8. A recent discussion I had with a client about buffer pool page-fixing brought to light two aspects of this performance tuning option that, I believe, are overlooked by some DB2 users. In this post I'll describe how you can make a quick initial assessment as to whether or not the memory resource of a mainframe system is sufficient to support buffer pool page-fixing, and I'll follow that with a look at the "bonus" performance impact that can be realized by buffer pool page-fixing in a DB2 data sharing environment.<br /><br /><span style="font-weight: bold;">Gauging the server memory situation.</span> As pointed out in the aforementioned 2008 blog entry on the topic, page-fixing a buffer pool can reduce CPU consumption by eliminating the requests that DB2 would otherwise have to make of z/OS to fix in memory -- and to subsequently release -- a buffer for every read of a page from, or write of a page to, the disk subsystem. These page fix/page release operations are individually inexpensive, but the cumulative CPU cost can be significant when the I/Os associated with a pool number in the hundreds (or thousands) per second. The prospect of removing that portion of a DB2 workload's CPU utilization may have you thinking, "Why not?" Well, there's a reason why PGFIX(NO) is the default setting for a DB2 buffer pool, and it has to do with utilization of a mainframe server's (or z/OS LPAR's) memory resource.<br /><br />With PGFIX(NO), the real storage page frames occupied by DB2 buffers are candidates for being stolen by z/OS, should the need arise. If something has to be read into memory from disk, and there is no available page frame to accommodate that read-in, z/OS will make one available by moving its contents to a page data set on auxiliary storage (if that relocated page is subsequently referenced by a process, it will be brought back into server memory from auxiliary storage -- this is known as demand paging). z/OS steals page frames according to a least-recently-used algorithm: the longer a page frame goes without being referenced, the closer it moves to the front of the steak queue. If a DB2 buffer goes a long time without being referenced, it could be paged out to auxiliary storage.<br /><br />So, page-fixing a buffer pool in memory would preclude z/OS from considering the associated real storage page frames as candidates for stealing. The important question, then, is this: would some of those pages be stolen by z/OS if they weren't fixed in memory from the get-go? If so, then page-fixing that pool's buffers might not be such a great idea: in taking away some page frames that z/OS might otherwise steal, buffer pool page fixing could cause page-steal activity to increase for other subsystems and application processes in the z/OS LPAR. Not good.<br /><br />Fortunately, there's a pretty easy way to get a feel for this: using either your DB2 monitor (an online display or a statistics report) or the output of the DB2 command -DISPLAY BUFFERPOOL DETAIL, look for fields labeled "PAGE-INS REQUIRED FOR READ" and "PAGE-INS REQUIRED FOR WRITE" (or something similar to that). What these fields mean: a page-in is required for a read if DB2 wants to read a page from disk into a particular buffer, and that buffer has been paged out to auxiliary storage (i.e., the page frame occupied by the buffer was stolen by z/OS). Similarly, a page-in is required for a write if DB2 needs to write the contents of a buffer to disk and the buffer is in auxiliary storage.<br /><br />If, for a pool, the PAGE-INS REQUIRED FOR READ and PAGE-INS REQUIRED FOR WRITE fields both contain zeros, it is likely that the pool, from a memory perspective, is "V=R" anyway (that is to say, the amount of real storage occupied by the pool is probably very close to, if not the same as, its size in terms of virtual storage). In that case, going with PGFIX(YES) should deliver CPU savings without increasing pressure on the server memory resource, since the page frames being stolen are probably not those that are occupied by that pool's buffers. If you want an added measure of assurance on this score, issue a -DISPLAY BUFFERPOOL DETAIL(*) command. The (*) following the DETAIL keyword tells DB2 that you want statistics for the pool since the time it was last allocated. That might have been days, or even weeks, ago (the command output will tell you this), and if you see that the "PAGE-INS REQ" fields in the read and write parts of the command output contain zeros for that long period of time, it's a REALLY good bet that the pool's occupation of real storage won't increase appreciably if you go with PGFIX(YES). For even MORE assurance that the memory resource of the z/OS LPAR in which DB2 is running is not under a lot of pressure, check the "PAGE-INS REQUIRED" numbers for the lower-activity pools (those with fewer GETPAGE requests than others). If even these show zeros, you should be in really good shape, memory-wise.<br /><br />With all this said, keep a couple of things in mind. First, even though your "PAGE-INS REQUIRED" numbers may give you a high degree of confidence that going to PGFIX(YES) for a buffer pool would be a good idea, make sure to coordinate this action with your z/OS systems programmer. That person has responsibility for seeing that z/OS system resources (such as server memory) are effectively managed and utilized, and you need to make sure that the two of you are on the same page (no pun intended) regarding buffer pool page-fixing. If you've done your homework, and you let the z/OS systems programmer do his (or her) homework (such as looking at z/OS monitor-generated system paging statistics), getting to agreement should not be a problem. Second, be selective in your use of the PGFIX(YES) buffer pool option. The greater the amount of I/O activity for a pool, the greater the benefit of PGFIX(YES). I'd recommend considering page-fixing for pools for which the rate of disk I/O activity is at least in the high double digits (writes plus reads) per second (and be sure to include prefetch reads when calculating the rate of disk I/O operations for a buffer pool). By staying with PGFIX(NO) for your lower-activity pools, you ensure that DB2 will make some buffer pool-associated page frames available to z/OS for page-out, should something cause the LPAR's memory resource to come under significant pressure. <br /><br /><span style="font-weight: bold;">And for you data sharing users...</span> Just a couple of weeks ago, someone told me that he was under the impression that page-fixing buffer pools would have a negative performance impact in a DB2 data sharing environment. NOT SO. Assuming (as mentioned above) that your server memory resource is sufficient to accommodate page-fixing for one or more of your buffer pools, the resulting CPU efficiency benefit should be MORE pronounced for in a data sharing group versus a standalone DB2 system. How so? Simple: the buffer pool page fix/page release activity that occurs for DB2 reads to, and writes from, the disk subsystem with PGFIX(NO) in effect also occurs for writes of pages to, and reads of pages from, coupling facility group buffer pools. Like disk I/Os, page read and write actions involving a group buffer pool can number in the thousands per second. PGFIX(YES) eliminates the overhead of page fix/page release requests for disk I/Os AND for group buffer pool page reads and writes. So, if you're running DB2 in a data sharing configuration, you have another incentive to check out the page-fix option for your high-use buffer pools.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com1tag:blogger.com,1999:blog-6806654330436722244.post-64656189157266899642010-02-22T08:04:00.000-08:002010-02-22T08:31:19.395-08:00A Couple of Notes on DB2 Group Buffer Pools<span style="font-family:arial;">I have recently done some work related to DB2 for z/OS data sharing, and that has me wanting to share with you a couple of items of information concerning group buffer pools (coupling facility structures used to cache changed pages of tablespaces and indexes that are targets of inter-DB2 read/write interest). First I'll provide some thoughts on group buffer pool sizing. After that, I'll get into the connection between local buffer pool page-fixing and group buffer pool read and write activity. [Lingo alert: GBP is short for group buffer pool, and "GBP-dependent" basically means that there is inter-DB2 read/write interest in a page set (i.e., a tablespace or or an index or a partition).]<br /><br /><span style="font-weight: bold;">How do you know if bigger is better?</span> A lot of folks know that a group buffer pool should be at least large enough to prevent directory entry reclaims (reclaims are basically "steals" of in-use GBP directory entries to accommodate registration of newly, locally cached pages of GBP-dependent page sets, and you want to avoid them because they result in invalidation of "clean" pages cached in local buffer pools). The key to avoiding directory entry reclaims is to have enough directory entries in a GBP to register all the different pages that could be cached in the GBP and in the associated local buffer pools at any one time (you also want to make sure that there are no GBP write failures due to lack of storage, but there won't be if the GBPs are large enough to prevent directory entry reclaims). For a GBP associated with a 4K buffer pool, and with the default 5:1 ratio of directory entries to data entries, sizing to prevent directory entry reclaims is pretty simple: you add up the size of the local pools and divide that figure by three to get your group buffer pool size; so, if there are two members in a data sharing group, and if BP1 has 6000 buffers on each member, directory entry reclaims will not occur if the size of GBP1 is at least 16,000 KB (the size of BP1 on each of the two DB2 members is 6000 X 4 KB = 24,000 KB, so the GBP1 size should be at least (2 X 24,000 KB) / 3, which is 16,000 KB). Let's say that your GBPs are all large enough to prevent directory entry reclaims (you can check on this via the output of the DB2 command -DISPLAY GROUPBUFFERPOOL GDETAIL). If you have enough memory in your coupling facility LPARs to make them larger still, should you? If you do enlarge them, how do you know if you've done any good?<br /><br />Start by checking on the success rate for GBP reads caused by buffer invalidations (when a local buffer of DB2 member X holds a table or index page that is changed by a process running on DB2 member Y, the buffer in member X's local pool will be marked invalid and a subsequent request for that page will cause member X to request the current version of the page, first from the GBP and then, in case of a "not found" result, from the disk subsystem). Information about these GBP reads can be found in a DB2 monitor report or online display of GBP information, or in the output of a -DISPLAY GROUPBUFFERPOOL MDETAIL command. In a DB2 monitor report the fields of interest may be labeled as follows (field names can vary slightly from one monitor product to another -- note that "XI" is short for "cross-invalidation," which refers to buffer invalidation operations):<br /><br /><span style="font-family:courier new;">GROUP BP1<span style="color: rgb(255, 255, 255);">..........................</span>QUANTITY<br /></span><span style="font-family:courier new;">---------------------------<span style="color: rgb(255, 255, 255);">........</span>--------<br /></span><span style="font-family:courier new;">SYN.READS(XI)-DATA RETURNED<span style="color: rgb(255, 255, 255);">............</span>8000<br /></span><span style="font-family:courier new;">SYN.READS(XI)-NO DATA RETURN<span style="color: rgb(255, 255, 255);">...........</span>2000<br /></span><br />In -DISPLAY GROUPBUFFERPOOL MDETAIL output, you'd be looking for this:<br /><br /><span style="font-family:courier new;">DSNB773I - MEMBER DETAIL STATISTICS<br /></span><span style="font-family:courier new;"><span style="color: rgb(255, 255, 255);">.............</span>SYNCHRONOUS READS<br /></span><span style="font-family:courier new;"><span style="color: rgb(255, 255, 255);">...............</span>DUE TO BUFFER INVALIDATION<br /></span><span style="font-family:courier new;"><span style="color: rgb(255, 255, 255);">.................</span>DATA RETURNED<span style="color: rgb(255, 255, 255);">..................</span>= 8000<br /></span><span style="font-family:courier new;"><span style="color: rgb(255, 255, 255);">.................</span>DATA NOT RETURNED<span style="color: rgb(255, 255, 255);">..............</span>= 2000<br /></span><br />The success rate, or "hit rate," for these GBP reads would be:<br /><br /><span style="color: rgb(0, 153, 0);">(reads with data returned) / ((reads with data returned) + (reads with data not returned))</span><br /><br />Using the numbers from the example output above, the success rate for GBP reads due to buffer invalidation would be 8000 / (8000 + 2000) = 80%.<br /><br />Here's why this ratio is useful: buffer invalidations occur when a GBP directory entry pointing to a buffer is reclaimed (not good, as previously mentioned), or when a page cached locally in one DB2 member's buffer pool is changed by a process running on another member of the data sharing group (these invalidations are good, in that they are required for the preservation of data coherency in a data sharing environment). If you don't have any buffer invalidations resulting from directory entry reclaims, invalidations are occurring because of page update activity. Because updated pages of GBP-dependent pages sets are written to the associated GBP as part of commit processing, a DB2 member looking for an updated page in a GBP should have a reasonably good shot at finding it there, if the GBP is large enough to provide a decent page residency time.<br /><br />So, if you make a GBP bigger and you see that the hit ratio for GBP reads due to invalid buffer has gone up for the member DB2 subsystems, you've probably helped yourself out, performance-wise, because GBP checks for current versions of updated pages are more often resulting in "page found" situations. Getting a page from disk is fast, but getting it from the GBP is 2 orders of magnitude faster (3 orders of magnitude if you have to get the page from spinning disk versus disk controller cache).<br /><br />By the way, the hit ratio for GBP reads due to "page not in buffer pool" (labeled as such in -DISPLAY GROUPBUFFERPOOL MDETAIL output, and as something like SYN.READS(NF) in a DB2 monitor report or display) is not so useful in terms of gauging the effect of a GBP size increase. These numbers reflect GBP reads that occur when DB2 member is looking in the GBP for a page it needs and which it doesn't have in a local buffer pool. This has to be done prior to requesting the page from disk if the target page set is GBP-dependent, but a GBP "hit" for such a read is, generally speaking, not very likely.<br /><br />One more thing: if you make a GBP bigger and you are duplexing your GBPs (and I hope that you are), be sure to enlarge the secondary GBP along with the primary GBP. If you aren't duplexing your GBPs (and why is that?), make sure that all your structures can still fit in one CF LPAR (in a two-CF configuration) after the target GBP has been made larger.<br /><br /><span style="font-weight: bold;">Buffer pool page-fixing: good for more than disk I/Os. </span>Buffer pool page-fixing, introduced with DB2 for z/OS V8, is one of my favorite recent DB2 enhancements (I blogged about it in an <a href="http://www.catterallconsulting.com/2008/07/note-on-db2-for-zos-page-fixed-buffer.html">entry posted in 2008</a>). People tend to think of the performance benefit of buffer pool page-fixing as it relates to disk I/O activity. That benefit is definitely there, but so is the benefit -- and this is what lots of people don't think about -- associated with GBP read and write activity. See, every time DB2 writes a page to a GBP or reads a page from a GBP, the local buffer involved in the operation must be fixed in server memory (aka central storage). If the buffer is in a pool for which PGFIX(YES) has been specified, that's already been done; otherwise, DB2 will have to tell z/OS to fix the buffer in memory during the GBP read or write operation and then release the buffer afterwards. A single "fix" or "un-fix" request is inexpensive, CPU-wise, but there can be hundreds of page reads and writes per second for a GBP, and the cumulative cost of all that buffer fixing and un-fixing can end up being rather significant. So, if you are running DB2 in data sharing mode and you aren't yet taking advantage of buffer pool page-fixing, now you have another reason to give it serious consideration.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com5tag:blogger.com,1999:blog-6806654330436722244.post-79858445519030640292010-02-09T13:36:00.000-08:002010-02-09T13:50:59.816-08:00Good News on the Mainframe DB2 Data Warehousing Front<span style="font-family: arial;">Last week, I attended a 1-day IBM System z "Technology Summit" education event in Atlanta. It was a multi-track program, and the DB2 for z/OS track ("Track 2") was excellent, in terms of both content and quality of presentation delivery (and it was FREE -- check out the remaining North American cities and dates for this event at http://www-01.ibm.com/software/os/systemz/summit/). The first presentation of the day, delivered by Jim Reed of IBM's Information Management software organization, focused on mainframe market trends and IBM's DB2 for z/OS product strategy. Jim's talk contained several nuggets of information that underscore the solid present and bright future of DB2 for z/OS as a platform for business intelligence applications. In this post, I'll share this information with you, along with some of my own observations on the topic.<br /><br /><span style="font-weight: bold;">BI important? How about most important?</span> Jim started out with a reference to a recent IBM Global CIO survey which asked participants to identify their top priority. You know what's hot in IT circles these days: virtualization, mobility apps, regulatory compliance. So, what came out on top with regard to CIO priorities? Analytics and business intelligence. That's not very surprising, as far as I'm concerned. Having spent years on optimizing efficiency, squeezing costs out of every facet of their operations, organizations are increasingly focused on optimizing performance. Are they offering the right mix of products and services to their customers? Are they selling to the right people? Are they delivering value in a way that separates them, in the eyes of their customers, from their competitors? Data warehouse systems are key drivers of success here, enabling companies to generate actionable intelligence from their data assets (and the breadth of these data assets keeps expanding, including now not just traditional point-of-sale and other business transactions, but e-mails, customer care interactions -- even company- and product-related comments posted on external-to-the-enterprise Web sites).<br /><br /><span style="font-weight: bold;">Big iron has big mo.</span> At the same time that BI is heating up as an area of corporate endeavor, the mainframe -- long seen as a workhorse for run-the-business OLTP and batch workloads -- is growing in popularity as a platform for BI applications. Jim spoke of several factors that are putting wind in System z's sails with regard to data warehousing. He cited a Gartner report that spotlighted key BI issues with which companies are grappling now. This list of front-burner concerns included:<br /></span><ul><li><span style="font-family: arial;"><span style="font-style: italic;">High availability</span>. Data warehouses are more likely these days to get a "mission critical" designation. Many (including one I worked on just last month) are customer-facing systems, and a lot of those are the subject of service level agreements.</span></li><li><span style="font-family: arial;"><span style="font-style: italic;">Mixed workload performance</span>. This was identified as the number one performance issue for data warehouses. Mixed BI workloads, in which fast-running, OLTP-like queries vie with complex, data-intensive analytic processes for system resources, are becoming common as so-called "operational BI" gains prominence.</span><br /></li></ul><span style="font-family: arial;">Then, of course, there's the matter of data protection, on which so much else depends. Jim mentioned that 33% of people recently surveyed indicated that they would QUIT doing business with a company if that company experienced a data security or privacy breach and was seen as being responsible for the incident.<br /><br />So, to address these key issues, you'd probably want to build your data warehouse on a hardware/software platform known for high availability, sophisticated workload management capabilities, and strong, multi-layered data protection and access control. Hmmm. Sounds like mainframe DB2 to me. Keep in mind, too, that the well-known availability and workload management strengths of System z and z/OS and DB2 are made even stronger when DB2 is deployed in data sharing mode on a parallel sysplex mainframe cluster configuration.<br /><br />Oh, and let's not forget that the legendary reliability of mainframe systems is not just a matter of advanced hardware and software technology (good as that stuff is) -- it also reflects the deep skills and robust processes (around change management, performance monitoring and tuning, capacity planning, business continuity, etc.) that typify the teams of professionals that support organizations' mainframe computing environments. As BI applications continue to move from "nice to have" to "must have" in the eyes of corporate leaders, it stands to reason that IT executives would want to house these essential systems on the server platform that exemplifies "rock solid," and to assign their care to the people in whom they have the utmost trust and confidence -- mainframe people.<br /><br />One more trend driving BI workloads to System z is the increased frequency with which data warehouse databases are being updated. Not long ago, the "query by day, update at night" model predominated. Now, many BI application users demand that updates of source data values be reflected more quickly in the data warehouse database -- sometimes in a near-real-time manner. A lot of the source data that supplies data warehouses comes from mainframe databases and files, and locating the data warehouse close to that source data can facilitate round-the-clock updating.<br /><br /><span style="font-weight: bold;">Let's make a deal.</span> The technical arguments for building a data warehouse on a mainframe platform are many and strong, but what about the financial angle? IBM has been pretty busy in this area of late. I already knew of <a href="http://www-01.ibm.com/software/data/db2/zos/edition-vue.html">DB2 Value Unit Edition</a> pricing, which makes DB2 for z/OS available for a one-time charge for net new workloads of certain types, including data warehousing. I'll admit to not having known about IBM's System z Solution Edition series (announced in August of last year) before Jim talked about them during his presentation. Included in this set of offerings is the <a href="http://www-03.ibm.com/systems/z/solutions/editions/dw/index.html">System Z Solution Edition for Data Warehousing</a>, a package of hardware, software (including DB2), and services that can help an organization to implement a mainframe-based data warehouse system in a cost-competitive way.<br /><br />If your organization is serious about data warehousing, get serious about your data warehouse platform. Mainframes deliver the availability, mixed workload performance management, security, and -- yes -- total cost of ownership that can improve your chances of achieving BI success. Analyze that.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0tag:blogger.com,1999:blog-6806654330436722244.post-79001907416171289172010-01-27T19:17:00.000-08:002010-01-27T19:35:40.821-08:00Some Basic Information About SQL in DB2-Accessing Programs<span style="font-family: arial;">DB2 has been around for a long time (more than 25 years), and a lot of people who work with DB2 have been doing so for a long time (myself included). Many of the younger folks I meet who are professionally engaged in DB2-related activity are developers. Some of these who came to DB2 after working with other relational database management systems might have been initially confused on hearing their DB2-knowledgeable colleagues talk about SQL as being "embedded" or "static" or "dynamic." Waters may have been further muddied when familiar words such as "package," "collection," and "plan" took on unfamiliar meanings in discussions about DB2-based applications. Throw in a few terms like "DBRM" and "consistency token," and you can really have a DB2 newbie scratching his or her head. Of late, I've seen enough misunderstanding in relation to programming for DB2 data access. My hope is that this post will provide some clarity. Although I am writing from a DB2 for z/OS perspective, the concepts are essentially the same in a DB2 for Linux/UNIX/Windows environment (some of the terminology is a little different).<br /><br />First up for explanation: embedded SQL. Basically, this refers to SQL statements, included in the body of a program, that are converted into a structure, called a package, that runs in the DB2 database services address space when the program executes. The package is generated through a mechanism, known as the bind process, that operates on a file called a database request module, or DBRM. The DBRM, which contains a program's embedded SQL statements in a bind-ready form, is one of two outputs produced when the program is run through the DB2 precompiler. The other of these outputs is a modified version of the source program, in which the embedded SQL statements have been commented out and to which calls to DB2 have been added -- one call per SQL statement. Each of these DB2 calls contains a statement number, the name of the program's DB2 package, and a timestamp-based identifier called a consistency token. The statement numbers and the consistency token are also included in the aforementioned DBRM, and these serve to tie the program in it's compiled and linked form to the package into which the DBRM is bound: at program execution time, a DB2 call indicates the package to use (the match is on package name and and consistency token value), and identifies the section of the package that corresponds to the SQL statement to be executed.<br /><br />The above paragraph is kind of a mouthful. Here's the key concept to keep in mind: the package associated with a program containing embedded SQL is generated before the program ever runs. To put it another way, DB2 gets to see the embedded SQL statements and prepare them for execution (by doing things like access path selection) before they are issued by the application program.<br /><br />A few more items of information related to packages:</span><span style="font-family: arial;"><br /></span><ul><li><span style="font-family: arial;">Packages are persistent -- they are stored in a system table in the DB2 directory database and loaded into memory when needed. Once cached in the DB2 for z/OS environmental descriptor manager pool (aka the EDM pool) a package is likely to stay memory-resident for some time, but if it eventually gets flushed out of the pool (as might happen if it goes for some time without being referenced), it will again be read in from the DB2 directory when needed.</span></li><li><span style="font-family: arial;">Packages are organized into groups called collections.</span></li><li><span style="font-family: arial;">For </span><span style="font-family: arial;">application processes that are local to a DB2 for z/OS subsystem (i.e., that run in the same z/OS system as the target DB2 data server), packages are executed through plans. So, a batch job running in a JES initiator address space -- or a CICS transaction, or an IMS transaction -- will provide to DB2 the name of a plan, which in turn points to one or more collections that contain the package or packages associated with the embedded SQL statements that the application process will issue. Applications that are remote to the DB2 subsystem and communicate with DB2 through the Distributed Data Facility using the DRDA protocol (Distributed Relational Database Architecture) make use of packages, but they do not refer to DB2 plans.</span><span style="font-family: arial;"></span></li></ul><span style="font-family: arial;">What about dynamic versus static SQL? Plenty of people who know a lot about DB2 will tell you that "static SQL" means the same thing as "embedded SQL." In my mind, the two terms are almost equivalent (some would say that this "almost" of mine is a matter of splitting hairs). It's true that static SQL is seen by DB2 and prepared for execution before the associated program is executed. That's what I said about embedded SQL, isn't it? Yes, but there is something called embedded dynamic SQL. That would be an SQL statement that is placed in a host variable that is subsequently referenced by a PREPARE statement. The SQL statement string in the host variable is dynamic (that is to say, it will be prepared by DB2 for execution when it is issued by the program), but -- and this is the splitting-hairs part -- PREPARE itself is not a dynamic SQL statement.<br /><br />Dynamic SQL statements (again, those being statements that are prepared when issued by a program, versus being prepared beforehand through the previously described bind process) can of course be presented to DB2 without the use of PREPARE -- they can, for instance, take the form of ODBC (Open Database Connectivity) or JDBC (Java Database Connectivity) calls. They can also be issued interactively through tools such as SPUFI (part of the TSO/ISPF interface to DB2 for z/OS) and the command line processor (a component of DB2 for Linux/UNIX/Windows and of the DB2 Client).<br /><br />Some DB2 people hear "dynamic SQL" and think "ad-hoc SQL." In fact, these terms are NOT interchangeable. Ad-hoc SQL is free-form and unpredictable -- it could be generated by someone using a query tool in a data warehouse environment. Ad-hoc SQL will be dynamic (prepared for execution by DB2 when issued), but dynamic SQL certainly doesn't have to be ad-hoc. There are tons of examples of applications -- user-written and vendor-supplied -- that send SQL statements to DB2 in a way that will result in dynamica statement preparation. That doesn't mean that users have any control over the form of statements so issued (users might only be able to provide values that will be substituted for parameter markers in a dynamic SQL statement). "Structured dynamic" is the phrase I use when referring to this type of SQL statement. Just remember: static SQL CANNOT change from execution to execution (aside from changes in the values of host variables). Dynamic SQL CAN change from execution to execution, but it doesn't HAVE to.<br /><br />I'll close by pointing out that dynamic SQL statements are not, in fact, always prepared by DB2 at the time of their execution. Sometimes, they are prepared before their execution. I'm referring here to DB2's dynamic statement caching capability (active by default in DB2 V8 and V9 systems). When a dynamic SQL statement is prepared for execution, DB2 will keep a copy of the prepared form of the statement in memory. When the same statement is issued again (possibly with different parameter values if the statement was coded with parameter markers), DB2 can use the cached structure associated with the previously prepared instance of the statement, thereby saving the CPU cycles that would otherwise be consumed in re-preparing the statement from scratch. Dynamic statement caching is one of the key factors behind the growing popularity and prevalence of dynamic SQL in mainframe DB2 environments.<br /><br />I hope that this overview of SQL in DB2-accessing programs will be useful to application developers and others who work with DB2. When all is said and done, the value of a database management system to an organization depends in large part on the value delivered by applications that interact with the DBMS. Code on!</span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com2tag:blogger.com,1999:blog-6806654330436722244.post-76526093049613409402010-01-13T08:17:00.000-08:002011-09-20T09:00:45.759-07:00DB2 for z/OS Data Sharing: Then and Now (Part 2)<span style="font-family:arial;"><a href="http://catterallconsulting.blogspot.com/2010/01/db2-for-zos-data-sharing-then-and-now.html">In part 1</a> of this two-part entry, posted last week, I wrote about some of the more interesting changes that I've seen in DB2 for z/OS data sharing technology over the years (about 15) since it was introduced through DB2 Version 4. More specifically, in that entry I highlighted the tremendous improvement in performance with regard to the servicing of coupling facility requests, and described some of the system software enhancements that have made data sharing a more CPU-efficient solution for organizations looking to maximize the availability and scalability of a mainframe DB2 data-serving system. In this post, I'll cover changes in the way that people configure data sharing groups, and provide a contemporary view of a once-popular -- and perhaps now unnecessary -- application tuning action.<br /><br /><span style="font-weight: bold;">Putting it all together.</span> Of course, before you run a DB2 data sharing group, you have to set one up. Hardware-wise, the biggest change since the early years of data sharing has been the growing use of internal coupling facilities, versus the standalone boxes that were once the only option available. The primary advantage of internal coupling facilities (ICFs), which operate as logical partitions within a System z server, with dedicated processor and memory resources, is economic: they cost less than external, standalone coupling facilities. They also offer something of a performance benefit, as communication between a z/OS system on a mainframe and an ICF on the same mainframe is a memory-to-memory operation, with no requirement for the traversing of a physical coupling facility link.<br /><br />When internal coupling facilities first came along in the late 1990s, organizations that acquired them tended to use only one in a given parallel sysplex (the mainframe cluster on which a DB2 data sharing group runs) -- the other coupling facility in the sysplex (you always want at least two, so as to avoid having a single point of failure) would be of the external variety. This was so because people wanted to avoid the effect of the so-called "double failure" scenario, in which a mainframe housing both an ICF and a z/OS system participating in the sysplex associated with the ICF goes down. Group buffer pool duplexing, delivered with DB2 Version 6 (with a subsequently retrofit to Version 5), allayed the double-failure concerns of those who would put the group buffer pools (GBPs) in an ICF on a server with a sysplex-associated z/OS system: if that server were to fail, taking the ICF down with it, the surviving DB2 subsystems (running on another server or servers) would simply use what had been the secondary group buffer pools in the other coupling facility as primary GBPs, and the application workload would continue to be processed by those subsystems (in the meantime, any DB2 group members on the failed server would be automatically restarted on other servers in the sysplex). Ah, but what of the lock structure and the shared communications area (SCA), the other coupling facility structures used by the members of a DB2 data sharing group? Companies generally wanted these in an external, standalone CF (or in an ICF on a server that did not also house a z/OS system participating in the sysplex associated with the ICF). Why? Because a double-failure scenario involving these structures would lead to a group-wide failure -- this because successful rebuild of the lock structure or SCA (which would prevent a group failure) requires information from ALL the members of a DB2 data sharing group. If a server has an ICF containing the lock structure and SCA (these are usually placed within the same coupling facility) and also houses a member of the associated DB2 data sharing group, and that server fails, the lock structure and SCA will not be rebuilt (because a DB2 member failed, too), and without those structures, the data sharing group will come down.<br /><br />Nowadays, parallel sysplexes configured with ICFs and <span style="font-weight: bold;">no</span> external CFs are increasingly common. For one thing, the double-failure scenario is less scary to a lot of folks than it used to be, because a) actually losing a System z server is exceedingly unlikely (it's rare enough for a z/OS or a DB2 or an ICF to crash, and rarer still for a mainframe itself to fail), and b) even if a double-failure involving the lock structure and SCA were to occur, causing the DB2 data sharing group to go down, the subsequent group restart process that would restore availability would likely complete within a few minutes (with duplexed group buffer pools, there would be no data sets in group buffer pool recover pending status, and restart goes much more quickly when there's no GRECP). Secondly, organizations that want insurance against even a very rare outage situation that would probably not exceed a small number of minutes in duration can eliminate the possibility of an outage-causing double-failure by implementing system duplexing of the lock structure and the SCA. System duplexing of the lock structure and SCA increases the overhead of running DB2 in data sharing mode (more so than group buffer pool duplexing), and that's why some organizations use it and some don't -- it's a cost versus benefit decision.<br /><br />Another relatively recent development with regard to data sharing set-up is the availability of 64-bit addressing in Coupling Facility Control Code, beginning with the CFLEVEL 12 release (Coupling Facility Control Code is the operating system of a coupling facility, and I believe that CFLEVEL 16 is the current release). So, how big can an individual coupling facility structure be? About 100 GB (actually, 99,999,999 KB -- the current maximum value that can be specified when defining a structure in the specification of a Coupling Facility Resource Management, or CFRM, policy). For the lock structure and the SCA, this structure size ceiling (obviously well below what's possible with 64-bit addressing) is totally a non-issue, as these structures are usually not larger than 128 MB each. Could someone, someday, want a group buffer pool to be larger than 100 GB? Maybe, but I think that we're a long way from that point, and I expect that the 100 GB limit on the size of a coupling facility structure will be increased well before then.<br /><br /><span style="font-weight: bold;">DEALLOCATE or COMMIT?</span> DB2 data sharing is, for the most part, invisible to application programs, and organizations implementing data sharing groups often find that use of the technology necessitates little, if anything, in the way of application code changes. That said, people have done various things over the years to optimize the CPU efficiency of DB2-accessing programs running in a data sharing environment. For a long time, one of the more popular tuning actions was to bind programs executed via persistent threads (i.e., threads that persist across commits, such as those associated with batch jobs and with CICS-DB2 protected entry threads) with the RELEASE(DEALLOCATE) option. This was done to reduce tablespace lock activity: RELEASE(DEALLOCATE) causes tablespace locks (not page or row locks) acquired by an application process to be retained until thread deallocation, as opposed to being released (and reacquired, if necessary) at commit points. The reduced tablespace lock activity would in turn reduce the type of data sharing global lock contention called XES contention (about which I wrote in last week's <a href="http://catterallconsulting.blogspot.com/2010/01/db2-for-zos-data-sharing-then-and-now.html">part 1</a> of this two-part entry). There were some costs associated with the use of RELEASE(DEALLOCATE) for packages executed via persistent threads (the size of the DB2 EDM pool often had to be increased, and package rebind procedures sometimes had to be changed or rescheduled to reflect the fact that some packages would remain in an "in use" state for much longer periods of time than before), but these were typically seen as being outweighed by the gain in CPU efficiency related to the aforementioned reduction in the level of XES contention.<br /><br />All well and good, but then (with DB2 Version 8) along came data sharing Locking Protocol 2 (also described in last week's <a href="http://catterallconsulting.blogspot.com/2010/01/db2-for-zos-data-sharing-then-and-now.html">part 1</a> post). Locking Protocol 2 drove XES contention way down, essentially eliminating XES contention reduction as a rational for pairing RELEASE(DEALLOCATE) with persistent threads. With this global locking protocol in effect, the RELEASE(DEALLOCATE) versus RELEASE(COMMIT) package bind decision is essentially unrelated to your use of data sharing. There are still benefits associated with the use of RELEASE(DEALLOCATE) for persistent-thread packages (e.g., a slight improvement in CPU efficiency due to reduced tablespace lock and EDM pool resource release and reacquisition, more-effective dynamic prefetch), but take away the data sharing efficiency gain of old that is now provided by Locking Protocol 2, and you might decide to be more selective in your use of RELEASE(DEALLOCATE). If you implement a DB2 data sharing group, you may just leave your package bind RELEASE specifications as they were in your standalone DB2 environment.<br /><br />What new advances in data sharing technology are on the way? We'll soon see: IBM is expected to formally announce DB2 Version "X," the successor to Version 9, later this year. I'll be looking for data sharing enhancements among the "What's New?" items delivered with Version X, and I'll likely report in this space on what I find. Stay tuned.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0tag:blogger.com,1999:blog-6806654330436722244.post-21036547649359423822010-01-08T10:29:00.000-08:002010-01-08T10:39:14.733-08:00DB2 for z/OS Data Sharing: Then and Now (Part 1)<span style="font-family: arial;">A couple of months ago, I did some DB2 data sharing planning work for a financial services organization. That engagement gave me an opportunity to reflect on how far the technology has come since it was introduced in the mid-1990s via DB2 for z/OS Version 4 (I was a part of IBM's DB2 National Technical Support team at the time, and I worked with several organizations that were among the very first to implement DB2 data sharing groups on parallel sysplex mainframe clusters). This being the start of a new year, it seems a fitting time to look at where DB2 data sharing is now as compared to where it was about fifteen years ago. I'll do that by way of a 2-part post, focusing here on speed gains and smarter software, and in part 2 (sometime next week) on configuration changes and application tuning considerations.<br /><br /><span style="font-weight: bold;">Speed, and more speed.</span> One of most critical factors with respect to the performance of a data sharing group is the speed with which a request to a coupling facility can be processed. In a parallel sysplex, the coupling facilities provide the shared memory resource in which DB2 structures such as the global lock structure and the group buffer pools are located. Requests to these structures (e.g., the writing of a changed page to a group buffer pool, or the propagation of a global lock request for a data page or a row) have to be processed exceedingly quickly, because 1) the volume of requests can be very high (thousands per second) and 2) most DB2-related coupling facility requests are synchronous, meaning that the mainframe engine that drives such a request will wait, basically doing nothing, until the coupling facility response is received (this is so for performance reasons: the request-driving mainframe processor is like a runner in a relay race, waiting with outstretched hand to take the baton from a teammate and immediately sprint with it towards the finish line). This processor wait time associated with synchronous coupling facility requests, technically referred to as "dwell time," has to be minimized because it is a key determinant of data sharing overhead (that being the difference in the CPU cost of executing an SQL statement in a data sharing environment versus the cost of executing the same statement in a standalone DB2 subsystem).<br /><br />In the late 1990s, people who looked after DB2 data sharing systems were pretty happy if they saw average service times for synchronous requests to the group buffer pools and lock structure that were under 250 and 150 microseconds, respectively. Nowadays, sites report that their average service times for synchronous group buffer pool and lock structure requests are less than 20 microseconds. This huge improvement is due in large part to two factors. First, coupling facility engines are MUCH faster than they were in the old days. If you know mainframes, you know about this even if you are not familiar with coupling facilities, because coupling facility microprocessors are identical, hardware-wise, to general purpose System z engines -- they just run Coupling Facility Control Code instead of z/OS. Today's z10 microprocessors pack 10 times the compute power delivered by top-of-the-line mainframe engines a decade ago. The second big performance booster with regard to coupling facility synchronous request service times is the major increase in coupling facility link capacity versus what was available in the 1999-2000 time frame. Back then, many of the links in use had an effective data rate of 250 MB per second. Current links can move information at 2 GB (2000 MB) per second.<br /><br />This big improvement in performance related to the servicing of synchronous coupling facility requests helped to improve throughput in data sharing systems. Did it also reduce the CPU cost of data sharing? Yes, but it's only part of that story. DB2 data sharing is a more CPU-efficient technology now than it was in the 1990s: overhead in an environment characterized by lots of what I call inter-DB2 write/write interest (referring to the updating of individual database objects -- tables and indexes -- by multiple application processes running concurrently on different members of a DB2 data sharing group) was once generally in the 10-20% range. Now the range is more like 8-15%. That improvement wasn't helped along all that much by faster coupling facility engines. Sure, they lowered service times for synchronous coupling facility requests, but the resulting reduction in the aforementioned mainframe processor "dwell time" was offset by the fact that much-faster mainframe engines forgo handling a lot more instructions during a given period of "dwelling" versus their slower predecessors (in other words, as mainframe processors get faster, you have to drive down synchronous request service times just to hold the line on data sharing overhead). Faster coupling facility links helped to reduce overhead, but I think that improvements in DB2 data sharing CPU efficiency have at least as much to do with system software changes as with speedier servicing of coupling facility requests. A tip of the hat, then, to the DB2, z/OS, and Coupling Facility Control Code development teams.<br /><br /><span style="font-weight: bold;">Working smarter, not harder.</span> Over the years, IBM has delivered a number of software enhancements that lowered the CPU cost of DB2 data sharing. Some of these code changes boosted efficiency by reducing the number of coupling facility requests that would be generated in the processing of a given workload. A DB2 Version 5 subsystem, for example (and recall that data sharing was introduced with DB2 Version 4), was able to detect more quickly that a data set that it was updating and which had been open on multiple members of a data sharing group was now physically closed on the other members. As a result, the subsystem could take the data set out of the group buffer pool dependent state sooner, thereby eliminating associated coupling facility group buffer pool requests. DB2 Version 6 introduced the MEMBER CLUSTER option of CREATE TABLESPACE, enabling organizations to reduce coupling facility accesses related to space map page updating for tablespaces with multi-member insert "hot spots" (these can occur when multiple application processes running on different DB2 members are driving concurrent inserts to the same area within a tablespace). DB2 Version 8 took advantage of a Coupling Facility Control Code enhancement, the WARM command (short for Write And Register Multiple -- a means of batching coupling facility requests), to reduce the number of group buffer pool writes necessitated by a page split in a group buffer pool dependent index from five to one.<br /><br />These code improvements were all welcome, but my all-time favorite efficiency-boosting enhancement is the Locking Protocol 2 feature delivered with DB2 Version 8. Locking Protocol 2 lowered data sharing overhead by virtually eliminating a type of global lock contention known as XES contention. Prior to Version 8, if an application process running on a member of a data sharing group requested an IX or IS lock on a tablespace (indicating an intent to update or read from the tablespace in a non-exclusive manner), that request would be propagated by XES (Cross-System Extended Services, a component of the z/OS operating system) to the coupling facility lock structure as an X or S lock request (exclusive update or exclusive read) on the target object, because those are the only logical lock states known to XES. Thus, if process Q running on DB2 Version 7 member DB2A requests an IX lock on tablespace XYZ, that request will get propagated to the lock structure as an X lock on the tablespace. If process R running on DB2 Version 7 member DB2B subsequently requests an IS lock on the same tablespace, that request will be propagated to the lock structure as an S lock on the resource. If process Q on DB2A still holds its lock on tablespace XYZ, the lock structure will detect the incompatibility of the X and S locks on the tablespace, and DB2B will get a contention-detected response from the coupling facility. The z/OS image under which DB2B is running will contact the z/OS associated with DB2A in an effort to resolve the apparent contention situation. DB2A's z/OS then drives an IRLM exit (IRLM being the lock management subsystem used by DB2), supplying the target resource identifier (for tablespace XYZ) and the actual logical lock requests involved in the XES-perceived conflict (IX and IS). IRLM will indicate to the z/OS system that these logical lock states are in fact compatible, and DB2B's z/OS will be informed that application process R can in fact get it's requested lock on tablespace XYZ. The extra processing required to determine that the conflict perceived by XES is in fact not a conflict adds to the cost of data sharing.<br /><br />With locking protocol 2, the aforementioned scenario would play out as follows: the request by application process Q on DB2 Version 8 (or 9) member DB2A for an IX lock on tablespace XYZ gets propagated to the lock structure as a request for an S lock on the object. The subsequent request by application process R on DB2 Version 8 (or 9) member DB2B is also propagated as an S lock request, and because S locks on a resource are compatible with each other, no contention is indicated in the response to DB2B's system from the coupling facility, and the lock request is granted. Because IX and IS tablespace lock requests are very common in a DB2 environment, and because IX-IX and IX-IS lock situations involving application access to a given tablespace from different data sharing members are not perceived by XES as being in-conflict when Locking Protocol 2 is in effect, almost all of the XES contention that would be seen in a pre-Version 8 DB2 data sharing group goes away, and data sharing CPU overhead goes down. I say almost all, because the S-IS tablespace lock situation (exclusive read on the one side, and non-exclusive read on the other), which would not result in XES contention with Locking Protocol 1, does cause XES contention with Locking Protocol 2 (the reason being that Locking Protocol 2 causes an S tablespace lock request to be propagated to the lock structure as an X request -- necessary to ensure that an X-IX tablespace lock situation will be correctly perceived by XES as being in-conflict). This is generally not a big deal, because S tablespace lock requests tend to be quite unusual in most DB2 systems.<br /><br />So, DB2 for z/OS data sharing technology, which was truly ground-breaking when introduced, has not remained static in the years since -- it's gotten better, and it will get better still in years to come. That's good news for data sharing users. If your organization doesn't have a DB2 data sharing group, make a new year's resolution to look into it. In any case, stop by the blog next week for my part 2 entry on the "then" and "now" of data sharing.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0tag:blogger.com,1999:blog-6806654330436722244.post-34765391651922808802009-12-29T07:13:00.000-08:002009-12-29T08:32:14.057-08:00Clearing the Air Re: Indexes on DB2 for z/OS Partitioned Tables<span style="font-family:arial;">With the end of the year in sight, it's a good time to tie up loose ends, as we say here in the USA. Thus it is that I've decided to focus, in this last post to my blog in 2009, on indexes as they pertain to DB2 for z/OS partitioned tables. That subject qualifies as a "loose end," because several <span>years</span> after the introduction of table-controlled partitioning with DB2 for z/OS V8, some folks are still not certain as to what can and cannot be done with indexes defined on partitioned tables. I'll try, in this entry, to clear things up.<br /><br />When a table is partitioned by way of an index specification (the only way to partition a table prior to DB2 V8), index options for the table are pretty straightforward. The index that describes the partitioning scheme (i.e., the one with the PART <span style="font-style: italic;">integer</span> VALUES (<span style="font-style: italic;">constant</span>) clause in the CREATE INDEX statement) is called the partitioning index. <span>No other index</span><span style="font-weight: bold;"> </span>on that table is called a partitioning index, and no other index on the table is physically partitioned. <span>Any</span> index on a unique key can be defined as UNIQUE. </span><span style="font-family:arial;"><span>Only</span><span style="font-weight: bold;"> </span>the partitioning index can be the table's clustering index.</span><br /><span style="font-family:arial;"><br />Starting with DB2 V8, table partitioning can be controlled by way of a table's definition (through the PARTITION BY and PARTITION <span style="font-style: italic;">integer</span> ENDING AT (<span style="font-style: italic;">constant</span>) clauses of CREATE TABLE). Table-controlled partitioning (enhanced in DB2 V9 via the partition-by-range universal tablespace) is way better than index-controlled partitioning, but this important DB2 advancement did change -- considerably -- the landscape as far as index options are concerned. First and foremost: for a table-controlled partitioned table, <span style="font-weight: bold;">any</span> index on a key that starts with the table's partition-by column or columns is called a partitioning index </span><span style="font-family:arial;">(and "starts with" means that the columns of a multi-column partitioning key appear in the order specified in the CREATE TABLE statement)</span><span style="font-family:arial;">; so, if a table's partitioning key is COL_X, COL_Y then an index on COL_X, COL_Y, COL_A is a partitioning index, and so is an index on COL_X, COL_Y, COL_B (but an index on COL_Y, COL_X, COL_D would not be a partitioning index, because the order of the partition-by columns does not match the order specified in the table's definition). Among the implications of this rule: a table-controlled partitioned table may have several partitioning indexes, or it may not have <span style="font-weight: bold;">any</span> partitioning indexes. Furthermore, a partitioning index may or may not be physically partitioned. Continuing with the example of the table partitioned on COL_X, COL_Y, an index on COL_X, COL_Y, COL_D that does not have the PARTITIONED clause in its definition is partitioning (because its key begins with the table's partitioning key) but not partitioned (because it was not defined with the PARTITIONED clause). Possible, of course, doesn't necessarily mean advisable -- I don't see why you would have a partitioning index that is not also partitioned.<br /><br />Next: for a table-controlled partitioned table, a secondary index (i.e., one that is not a partitioning index) can itself be partitioned -- you just have to specify PARTITIONED in the definition of the index (this will cause the index to be physically partitioned along the lines of the underlying table, so that partition 1 of the secondary index will contain the keys for rows in partition 1 of the table). A secondary index that is partitioned is called a data-partitioned secondary index, or DPSI (an index that is not partitioned is called a non-partitioned index, or NPI).<br /><br />Third: there is a restriction on DPSIs with regard to uniqueness (a restriction that was loosened somewhat with DB2 V9). In a DB2 for z/OS V8 environment, <span style="font-weight: bold;">no</span> DPSI can be defined as unique (and remember: a DPSI is a partitioned index that is not a partitioning index -- a partitioning index can be unique). If you try to create a secondary index with both UNIQUE and PARTITIONED in the index definition, you'll get a -628 SQL code (and an accompanying error message indicating that "clauses are mutually exclusive"). In a DB2 V9 environment (and this was recently pointed out by DB2 consultant Peter Backlund in a thread on the <a href="http://www.idug.org/cgi-bin/wa?A0=DB2-L">DB2-L</a> discussion forum), a DPSI <span style="font-weight: bold;">can</span> be defined as UNIQUE <span style="font-weight: bold;">if</span> the index key contains the table's partition by column or columns. Once again, consider the table partitioned on COL_X, COL_Y. A DPSI defined on COL_A, COL_Y, COL_B, COL_X could have the UNIQUE attribute because the index key contains all of the table's partition-by columns (and note that the partition-by columns do not have to be in any particular order within the DPSI's key -- they just have to be present within the key). Can there be multiple unique DPSIs defined on a DB2 V9 table-controlled partitioned table? Yes -- again, what's required is that the underlying table's partition-by columns be included in the key of a DPSI that is to be defined as UNIQUE.<br /><br />Finally: with regard to the CLUSTER attribute, you have flexibility with a table-controlled partitioned table that you don't have with an index-controlled partitioned table. For an index-controlled partitioned table, the partitioning index <span style="font-weight: bold;">will be</span> the table's clustering index. For a table-controlled partitioned table, <span style="font-weight: bold;">any</span> one index can be the table's clustering index (and of course, a table can only have one index with the CLUSTER attribute). The clustering index could be a partitioning index or a secondary index (whether a DPSI or an NPI). The ability to cluster a table with one key and partition it by another key is, in my opinion, one of the key advantages of table-controlled partitioning over index-controlled partitioning (other pluses include the ability to add partitions to a table-controlled partitioned table, and the ability to rotate partitions in a "first to last" manner).<br /><br />Is all that clear? I hope so. Table-controlled partitioning is a VERY good thing -- well worth the effort of getting your arms around the new rules regarding indexes on table-controlled partitioned tables.<br /><br />Throughout my 27 years in IT, I've enjoyed the constancy of opportunities to learn new things. I look forward to more of the same in 2010. Have fun ringing in the new year!<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0tag:blogger.com,1999:blog-6806654330436722244.post-65556501231277691302009-12-10T06:21:00.000-08:002011-09-20T08:57:37.688-07:00DB2 for z/OS and the Disk Subsystem<span style="font-family:arial;">Earlier this week, we in the <a href="http://www.praxiumgroup.com/adug.html">Atlanta DB2 Users Group</a> were treated to a day with Roger Miller, the ebullient IBMer who, more than anyone else, has been the "face" of the DB2 for z/OS development organization since the product debuted more than 25 years ago (Roger spends a great deal of time in front of DB2 users -- at customer sites, at the briefing center at IBM's Silicon Valley Lab, and at user group meetings). Roger provided us with six hours of DB2 information, laced with a little bit of Shakespeare (appropriate, as Roger's presentation style certainly brings to mind the line, "All the world's a stage," from Shakespeare's <span style="font-style: italic;">As You Like It</span>). Some of the material that I found to be particularly interesting had to do with advances in disk I/O technology over the years, and the effect of those enhancements on DB2-related I/O performance. In this post, I'll share some of that information with you, and I'll include a few of my own observations on DB2 and the disk subsystem, based on my work with DB2 for z/OS-using organizations.<br /><br /><span style="font-weight: bold;">Faster and faster.</span> Roger talked about the major gains seen in mainframe processor performance over the past 10 years (the "clock rate" of the engines in IBM's top-of-the line z10 mainframe server is 4.4 GHz, versus 550 MHz for the G6 line in 1999), and he went on to point out that improvements in disk I/O performance have been just as dramatic. As I noted <a href="http://catterallconsulting.blogspot.com/2008/10/so-what-makes-for-good-db2-io.html">in a blog entry on DB2 I/O performance</a> that I posted last year, a target of 20-30 milliseconds of wait time per synchronous read (i.e., for an on-demand read of a single 4K DB2 page into memory from the disk subsystem) was pretty much the norm well into the 1990s (this figure is usually obtained from a DB2 monitor accounting report or an online display of DB2 accounting data). Nowadays, 2 milliseconds of wait time per synchronous read is fairly common. There are a number of factors behind this order-of-magnitude improvement in disk I/O performance, none more important than the large increase in disk controller cache memory sizes since the mid-90s (32 MB of cache once seemed like a lot in the old days -- now you can get more than 300 GB of cache on a control unit), and the development of sophisticated algorithms to optimize the effective use of the cache resource (enterprise-class disk controllers run these algorithms on multiple high-performance microprocessors).<br /><br />The cache impact on disk I/O performance was underscored by some numbers that Roger shared with our group: while the time required to retrieve a DB2 page from spinning disk will generally be between 4 and 8 milliseconds (a big improvement versus 1990s-era disk subsystems), a disk controller cache hit results in a wait time of 230 to 290 microseconds for a synchronous read (the low end of this range is for a system with the z10 High-Performance FICON I/O architecture, also known as zHPF).<br /><br />For DB2 prefetch reads, the gains are even more impressive. A decade or so ago, reading 64 4K pages into a DB2 buffer pool in 90 milliseconds was thought to be good performance. These days, it's possible to get 64 pages into memory in 1.5 milliseconds -- a 60X improvement over the old standard.<br /><br />Looking beyond cache, a fairly recent arrival on the enterprise storage scene is solid-state disk technology (SSD). Actually, SSD itself isn't all that new -- devices of this type have been in use for about 30 years. What's new is the cost-competitiveness of enterprise-class SSD systems -- still several times more expensive than a traditional spinning-disk system, on a cost-per-gigabyte basis, but close enough now to warrant serious consideration for certain performance-critical database objects that tend to be accessed in a random fashion (as versus a sequential processing pattern). Random access is the SSD sweet spot because the technology eliminates the seek time that elongates random I/O service times when pages are read from spinning disk; thus, the wait time for a DB2 synchronous read from an SSD devices will likely be about 740-840 microseconds, versus the aforementioned 4-8 milliseconds for a read from spinning disk.<br /><br />64-bit addressing means that DB2 buffer pools can be way bigger than before, and that's good for performance because no I/O operation -- even one that results in a cache hit -- will come close to the speed with which a page in memory can be accessed. That said, a large-scale DB2 application accessing a multi-terabyte database is likely to drive a lot of I/O activity, even if you have a really big buffer pool configuration. Advanced disk storage and I/O subsystem technology make those page reads much less of a drag on application performance than they otherwise would be.<br /><br /><span style="font-weight: bold;">Disk space utilization: don't overdo it. </span>High-performance storage systems cost plenty of money, and people who purchase the devices don't want to have a lot of unused disk capacity on the floor. Some, however, push the target space-utilization threshold too far. See, a storage system isn't just about application performance -- it's also about application availability. Fill your disk volumes too full, and you're asking for DB2 data set space allocation failures (and associated application outages). How full is too full? I can tell you that in my own experience, DB2 data set space allocation failures tend to be a problem when an organization goes for a disk space utilization rate of 90% or more (and keep in mind that we're not just talking about data set extensions related to data-load operations -- we're also talking about utility-related disk space usage for things like sort work files and online REORG shadow data sets). At the other end of the spectrum, I've seen a situation in which an organization set a 60% threshold for disk volume space utilization. This company's DBAs liked that policy a lot (it really cut down on middle-of-the-night rousting of whoever was on-call), but I can't say that I'd advocate such a target -- it seems too low from a cost-efficiency perspective.<br /><br />Where do I stand? I'm pretty comfortable with a disk space utilization target in the 70-80% range. I might lean towards the lower end of that range in a DB2 Version 9 environment, as partition-level online REORG jobs will reorganize non-partitioning indexes in their entirety, thereby necessitating more shadow data set space (a potentially compensating factor would be DB2 9 index compression, which can significantly reduce disk space requirements for index data sets). Something else to keep in mind: in addition to avoiding overly-high utilization of disk space, another good practice with regard to minimizing DB2 data set space allocation failures is to let DB2 determine the amount of space that will be requested when a data set has to be extended. This capability, sometimes called sliding-scale space allocation, was introduced with DB2 for z/OS Version 8. <a href="http://catterallconsulting.blogspot.com/2009/09/db2-managed-disk-space-allocation.html">I blogged about it a few weeks ago</a>, and I highly recommend its usage. People who have taken advantage of this functionality have expressed great satisfaction with the results: much less DBA time spent in managing DB2 data set allocation processing, and a sharp drop in the occurrence of DB2 space allocation-related failures. Check it out.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com4tag:blogger.com,1999:blog-6806654330436722244.post-61354225753285981752009-11-30T20:13:00.000-08:002011-09-20T08:54:14.289-07:00DB2: DPSI Do, or DPSI Don't?<span style="font-family:arial;">One of the more interesting features delivered in recent years for DB2 for z/OS is the data-partitioned secondary index, or DPSI (pronounced "DIP-see"). Introduced with DB2 for z/OS Version 8, DPSIs are defined on table-controlled partitioned tablespaces (i.e., tablespaces for which the partitioning scheme is controlled via a table-level -- as opposed to an index-level -- specification), and are a) non-partitioning (meaning that the leading column or columns of the index key are not the same as the table's partitioning key), and b) physically partitioned along the lines of the table's partitions (meaning that partition 1 of a DPSI on partitioned table XYZ will contain entries for all rows in partition 1 of the underlying table -- and only for those rows).<br /><br />[For more information about partitioned and partitioning indexes on table-controlled partitioned tablespaces, <a href="http://catterallconsulting.blogspot.com/2009/09/of-db2-for-zos-indexes-partitioneduh.html">see my blog entry on the topic</a>, posted a few weeks ago.]<br /><br />Here's what makes DPSIs really interesting to me:<br /></span><ul><li><span style="font-family:arial;">They are a great idea in some situations.</span></li><li><span style="font-family:arial;">They are a bad idea in other situations.</span></li><li><span style="font-family:arial;">You may change the way you think about them in a DB2 9 context, versus a DB2 V8 environment.</span><br /></li></ul><span style="font-family:arial;"><span style="font-weight: bold;">DPSI do:</span> a great example of very beneficial DPSI use showed up recently in the form of a question posted to the DB2-L discussion list (a great forum for technical questions related to DB2 for z/OS and DB2 for Linux, UNIX, and Windows -- visit <a href="http://www.idug.org/cgi-bin/wa?A0=DB2-L">the DB2-L page</a> on the Web to join the list). The DB2 DBA who sent in the question was working on the design of a data purge process for a particular table in a database managed by DB2 for z/OS V8. The tablespace holding the data was partitioned (in the table-controlled way), with rows spread across seven partitions -- one for each day of the week. The sole purge criterion was date-based, and the DBA was thinking of using a partition-level LOAD utility, with REPLACE specified and an empty input data set, to clear out a partition's worth of data (a good thought, as purging via SQL DELETE can be pretty CPU-intensive if there are a lot of rows to be removed from the table at one time). He indicated that several non-partitioning indexes (or NPIs) were defined on the table, and he asked for input from "listers" (i.e., members of the DB2-L community) as to the impact that these NPIs might have on his proposed partition-level purge plan.<br /><br />Isaac Yassin, a consultant and noted DB2 expert, was quick to respond to the question with a warning that the NPIs would have a very negative impact on the performance of the partition-level LOAD REPLACE operation. Isaac's answer was right on the money. It's true that the concept of a logical partition in an NPI (i.e., the index entries associated with rows located in a partition of the underlying table) makes a partition-level LOAD REPLACE compatible, from a concurrency perspective, with application access to other partitions in the target tablespace (a LOAD REPLACE job operating on partition X of table Y will get a so-called drain lock on logical partition X of each NPI defined on table Y). Still, technically feasible doesn't necessarily mean advisable. For a partitioned tablespace with no NPIs, a LOAD REPLACE with an empty input data set is indeed a very CPU- and time-efficient means of removing data from a partition, because it works at a data set level: the tablespace partition's data set (and the data set of the corresponding physical partition of each partitioned index) is either deleted and redefined (if its is a DB2-managed data set) or reset to empty (if it is a user-managed data set, or if it is DB2-managed and REUSE was specified on the utility control statement), and -- thanks to the empty input data set -- you have a purged partition at very little cost in terms of CPU consumption.<br /><br />Put NPIs in that picture, and the CPU and elapsed time story changes for the worse. Because the NPIs are not physically partitioned, the index-related action of the partition-level LOAD REPLACE job (the deletion of index entries associated with rows in the target table partition) is a page-level operation. That means lots of GETPAGEs (CPU time!), potentially lots of I/Os (elapsed time!), and lots of index leaf page latch requests (potential contention issue with respect to application processes concurrently accessing other partitions, especially in a data sharing environment). Nick Cianci, an IBMer in Australia, suggested changing the NPIs to DPSIs, and that's what the DBA who started the DB2-L thread ended up doing (as I confirmed later -- he's a friend of mine). With the secondary indexes physically partitioned along the lines of the table partitions, the partition-level LOAD REPLACE with the empty input data set will be what the DBA wanted: a very fast, very efficient means of removing a partition's worth of data from the table without impacting application access to other partitions.<br /><br /><span style="font-weight: bold;">DPSI don't:</span> there's a flip side to the partition-level utility benefits that can be achieved through the use of data partitioned (versus non-partitioned) secondary indexes, and it has to do with query performance. When one or more of a query's predicates match the columns of the key of a non-partitioned index, DB2 can quickly zero in on qualifying rows. If that same index is data-partitioned, and if none of the query's predicates reference the partitioning key of the underlying table, DB2 might not be able to determine that a partition of the table contains any qualifying rows without checking the corresponding partition of the DPSI (in other words, DB2 might not be able to utilize page range screening to limit the number of partitions that have to be searched for qualifying rows). If a table has just a few partitions, this may not be such a big deal. But if the table has hundreds or thousands of partitions, it could be a very big deal (imagine a situation in which DB2 has to search 1000 partitions of a DPSI in order to determine that the rows qualified by the query are located in just a few of the table's partitions -- maybe just one). In that case, degraded query performance might be too high of a price to pay for enhanced partition-level utility operations, and NPIs might be a better choice than DPSIs.<br /><br />That's exactly what happened at a large regional retailer in the USA. A DBA from that organization was in a DB2 for z/OS database administration class that I taught recently, and he told me that his company, on migrating a while back to DB2 V8, went with DPSIs for one of their partitioned tablespaces in order to avoid the BUILD2 phase of partition-level online REORG. [With NPIs, an online REORG of a partition of a tablespace necessitates BUILD2, during which the row IDs of entries in the logical partition (corresponding to the target tablespace partition) of each NPI are corrected to reflect the new physical location of each row in the partition. For a very large partition containing tens of millions of rows, BUILD2 can take quite some time to complete, and during that time the logical partitions of the NPIs are unavailable to applications. This has the effect of making the corresponding tablespace partition unavailable for insert and delete activity. Updates of NPI index key values are also not possible during BUILD2.]<br /><br />The DPSIs did indeed eliminate the need for BUILD2 during partition-level online REORGs of the tablespace (this because the physical DPSI partitions are reorganized in the same manner as the target tablespace partition), but they also caused response times for a number of queries that access the table to increase significantly -- this because the queries did not contain predicates that referenced the table's partitioning key, so DB2 could not narrow the search for qualifying rows to just one or a few of a DPSI's partitions. The query performance problems were such that the retailer decided to go back to NPIs on the partitioned table. To avoid the BUILD2-related data availability issue described above, the company REORGs the tablespace in its entirety (versus REORGing a single partition or a subset of partitions). [By the way, DPSI-related query performance problems were not an issue for the DBA who initiated the DB2-L thread referenced in the "DPSI do" part of this blog entry, because 1) the table in question is accessed almost exclusively for inserts, with only the occasional data-retrieval operation; and 2) the few queries that do target the table contain predicates that reference the table's partitioning key, so DB2 can use page-range screening to avoid searching DPSI partitions in which entries for qualifying rows cannot be present.]<br /><br /><span style="font-weight: bold;">DPSIs and DB2 9:</span> the question as to whether or not you should use DPSIs is an interesting one, and it's made more so by a change introduced with DB2 9 for z/OS: the BUILD2 phase of partition-level online REORG has been eliminated (BUILD2 is no longer needed because the DB2 9 REORG TABLESPACE utility will reorganize NPIs in their entirety as part of a partition-level online REORG operation). Since some DB2 V8-using organizations went with DPSIs largely to avoid the data unavailability situation associated with BUILD2, they might opt to redefine DPSIs as NPIs after migrating to DB2 9, if DPSI usage has led to some degradation in query performance (as described above in the "DPSI don't" part of this entry). Of course BUILD2 avoidance (in a DB2 V8 system) is just one justification for DPSI usage. Even with DB2 9, using DPSIs (versus NPIs) is important if you need a partition-level LOAD REPLACE job to operate efficiently (see "DPSI do" above).<br /><br />Whether you have a DB2 V8 or a DB2 V9 environment, DPSIs may or may not be a good choice for your company (and it may be that DPSIs could be used advantageously for some of your partitioned tables, while NPIs would be more appropriate for other tables). Understand the technology, understand your organization's requirements (and again, these could vary by table), and make the choice that's right in light of your situation.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com4tag:blogger.com,1999:blog-6806654330436722244.post-60689495367643726562009-11-17T07:17:00.000-08:002009-11-18T07:00:21.275-08:00Mainframe DB2 Data Serving: Vision Becomes Reality<span style="font-family:arial;">One of the things I really like about attending DB2 conferences is the face-to-face time I get with people who otherwise would be on the other side of e-mail exchanges. I get a whole lot more out of in-person communication versus the electronic variety. Case in point: at IBM's recent Information on Demand event in Las Vegas, I ran into a friend who is a DB2 for z/OS database engineering leader at a large financial services firm. He talked up a new mainframe DB2 data serving reference architecture recently implemented for one of his company's mission-critical applications, and did so with an enthusiasm that could not have been fully conveyed through a text message. I got pretty fired up listening to the story this DBA had to tell, in part because of the infectious excitement with which it was recounted, but also because the system described so closely matches a vision of a DB2 for z/OS data-serving architecture that I've had in mind -- and have advocated -- for years. To see that vision validated in the form of a real-world system that is delivering high performance and high availability in a demanding production environment really made my day. I am convinced that what the aforementioned financial services firm (hereinafter referred to as Company XYZ) is doing represents the future of mainframe DB2 as a world-class enterprise data-serving platform. Read on if you want to know more.<br /><br />Three characteristics of the reference DB2 for z/OS data architecture (so called because it is seen as the go-forward model by the folks at Company XYZ) really stand out in my mind and make it an example to be emulated:<br /></span><ol><li><span style="font-family:arial;">It is built on a DB2 data sharing / parallel sysplex foundation, for maximum availability and scalability (not only that -- these folks have done data sharing Really Right, as I'll explain).</span></li><li><span style="font-family:arial;">It leverages Big Memory (aka 64-bit addressing) for enhanced performance.</span></li><li><span style="font-family:arial;">The software stack on the mainframe servers is pretty short -- these are database machines, plain and simple.</span></li></ol><span style="font-family:arial;">A little elaboration now on these three key aspects of Company XYZ's DB2 for z/OS reference architecture:<br /><br /><span style="font-weight: bold;">The robust foundation: a DB2 data sharing group on a parallel sysplex mainframe cluster.<span style="font-weight: bold;"> </span></span>It's well known that a standalone System z server running z/OS and DB2 can be counted on to provide very high levels of availability and scalability for a data-serving workload. These core strengths of the mainframe platform are further magnified when concurrent read/write access to the database is shared by multiple DB2 members of a data sharing group, running in the multiple z/OS LPARs (logical partitions) and multiple System z servers of a parallel sysplex. You're not going to beat the uptime delivered by that configuration: formerly planned outages for maintenance purposes are virtually eliminated (service levels of of DB2, z/OS, and other software components can be updated, and server hardware can be upgraded, with no -- I mean zero -- interruption of application access to the database), and the impact of an unplanned failure of a DB2 member or a z/OS LPAR or a server is greatly diminished (only data pages and/or rows that were in the process of being changed by programs running on a failed DB2 subsystem are temporarily unavailable following the failure, and those retained locks will be usually be freed up within a couple of minutes via automatic restart of the failed member). And scalability? Up to 32 DB2 subsystems (which could be running on 32 different mainframe servers) can be configured in one data sharing group.<br /><br />Now, you can set up a DB2 data sharing group the right way, or the Really Right way. Company XYZ did it Really Right. Here's what I mean:<br /></span><ul><li><span style="font-family:arial;"><span style="font-style: italic;">More z/OS LPARs and DB2 members than mainframes in the sysplex.</span> I like having more than one z/OS LPAR (and DB2 subsystem) per mainframe in a parallel sysplex, because 1) you can route work away from one of the LPARs for DB2 or z/OS maintenance purposes and still have access to that server's processing capacity, and 2) more DB2 members means fewer retained locks and quicker restart in the event of a DB2 subsystem failure.</span></li><li><span style="font-family:arial;"><span style="font-style: italic;">Dynamic VIPA network addressing</span>. Availability and operational flexibility are optimized when remote DRDA clients use a dynamic VIPA (virtual IP address) to connect to the DB2 data sharing group (as long as at least one member of the data sharing group is up, a connection request specifying the group's VIPA can be successfully processed). A sysplex software component called the Sysplex Distributor handles load balancing across DB2 members for initial connection requests from remote systems (these will often be application servers), while load balancing for subsequent requests is managed at the DB2 member level.<br /></span></li><li><span style="font-family:arial;"><span style="font-style: italic;">Internal coupling facilities</span>. ICFs (basically, coupling facility control code running in an LPAR on a mainframe server) are less expensive than external coupling facilities, not only with respect to acquisition cost, but also in terms of environmental expenses (floor space, power, cooling). [It's true that if the mainframe containing the ICF holding the lock structure and the shared communications area (SCA) should fail, and if on that mainframe there is also a member of the DB2 data sharing group, the result will be a group-wide outage unless the lock structure and SCA are duplexed in the second ICF. Company XYZ went with system-managed duplexing of the lock structure and SCA (DB2 manages group buffer pool duplexing in a very low-overhead way). Some other organizations using ICFs exclusively (i.e., no external coupling facilities) decide not to pay the overhead of system-managed lock structure and SCA duplexing, on the ground that a) a mainframe server failure is exceedingly unlikely, b) the group-wide outage would only occur if a <span style="font-style: italic;">particular</span> mainframe (the one with the ICF in which the lock structure and SCA are located) were to fail, and c) the group-restart following a group-wide outage should complete within a few minutes. The right way to go regarding the use or non-use of system-managed lock structure and SCA duplexing will vary according to the needs of a given organization.]</span></li></ul><span style="font-family:arial;"><span style="font-weight: bold;">Taking advantage of 64-bit addressing.</span> Each of the LPARs in the parallel sysplex on which Company XYZ's model DB2 for z/OS data-serving system is built has more than 20 GB of central storage, and each DB2 subsystem (there is one per LPAR) has a buffer pool configuration that exceeds 10 GB in size. In these days of Big Memory (versus the paltry 2 GB to which we were limited not long ago), I don't think of a production-environment DB2 buffer pool configuration as being large unless the aggregate size of all pools in the subsystem is at least 10 GB. The reduced level of disk I/O activity that generally comes with a large buffer pool configuration can have a significant and positive impact on both the elapsed time and CPU efficiency of data access operations.<br /><br /><span style="font-weight: bold;">Lean, mean, data-serving machines.</span> A production instance of DB2 for Linux, UNIX, and Windows (LUW) usually runs on a machine that is a <span style="font-style: italic;">dedicated</span> data server -- data access code executes there, and that's it. Business-logic programs? They run on application servers. Presentation-logic programs? They might run on yet another tier of servers. The DB2 for LUW server Just Does Data. When I started my IT career in the early 1980s, a mainframe-based application was almost always entirely mainframe-based, by which I mean that all application functionality -- data access logic, business logic, and presentation logic -- was implemented in programs that ran on a mainframe server. Nowadays, I believe that the unmatched availability, scalability, reliability, and security offered by the System z platform is put to most advantageous use in the servicing of data access requests. In other words, I feel that a DB2 for z/OS system should be thought of, in an architectural sense, as a dedicated data server, just as we tend to think of DB2 for LUW systems (and other database management systems that run on Linux, UNIX, and/or Windows platforms) as dedicated data servers.<br /><br />That's how DB2 for z/OS functions in Company XYZ's reference architecture: it just does data. Consequently, the software stack on the data-serving mainframes is relatively short, consisting of z/OS, DB2, RACF (security management), a data replication tool, some system automation and management tools, some performance monitoring tools, and little else. Transaction management is handled on application servers. Database access requests come in via the DB2 Distributed Data Facility (DDF), and much of the access logic is packaged in stored procedures (the preference at Company XYZ is DB2 9 native SQL procedures, because they perform very well and -- when invoked through calls that come through DDF -- much of their processing can be handled by zIIP engines).<br /><br />Does this system, which looks so good on paper, deliver the goods? Absolutely. Volume has been taken north of 1400 transactions per second with excellent response time, and my DBA friend is confident that crossing the 2000-trans-per-second threshold won't be a problem. On the availability side, Company XYZ is getting industry-leading uptime. The message: System z is more than just capable of functioning effectively as a dedicated data server -- it works exceptionally well when used in that way. This is a clean, modern architecture that leverages what mainframes do best -- scale, serve, protect, secure -- in a way that addresses a wide range of application design requirements.<br /><br />Here's a good coda for you: still at IOD in Las Vegas, and shortly after my conversation with the DBA from Company XYZ, I encountered another friend -- a lead DB2 technical professional at another company. He told me about the new DB2 for z/OS reference architecture that had recently been approved by his organization's IT executive management. The pillars of that architecture are a DB2 data sharing / parallel sysplex mainframe cluster, z/OS systems functioning as dedicated data servers, data requests coming in via the DB2 DDF, and data access logic packaged in DB2 9 native SQL procedures. I told this friend that he and his colleagues are definitely on the right track. It's a track that more and more DB2 for z/OS-using companies are traveling, and it could well be the right one for your organization.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0tag:blogger.com,1999:blog-6806654330436722244.post-35582347361294810632009-11-11T07:17:00.000-08:002011-09-20T08:52:11.522-07:00DB2 9 for z/OS: Converting from External to Native SQL Procedures<span style="font-family:arial;">As some of you may know, I'm a big fan of the native SQL procedure functionality introduced with DB2 for z/OS Version 9 (I've written a number of blog entries on the subject, <a href="http://catterallconsulting.blogspot.com/2008/11/db2-9-for-zos-stored-procedure-game.html">starting with one posted last year</a>). Native SQL procedures offer a number of advantages versus external SQL procedures (formerly known simply as SQL procedures in pre-Version 9 DB2 environments), including (generally) better performance, zIIP engine eligibility when called from a remote client via DRDA, and simplified lifecycle processes (referring to development, deployment, and management). These advantages have plenty of folks looking to convert external SQL procedures to native SQL procedures, and that's fine, but some of these people are under the impression that the conversion process involves nothing more than dropping an external SQL procedure and re-issuing that routine's CREATE PROCEDURE statement, minus the EXTERNAL NAME and FENCED options (if either had been specified in creating the external SQL procedure). This may in fact do the trick for a very simple SQL procedure, but in many cases the external-to-native conversion will be a more involved process. In this post I'll provide some information as to why this is so, along with a link to a well-written "technote" on IBM's Web site that contains further details on the topic.<br /><br />First, a little more on this drop-and-recreate-without-EXTERNAL-NAME-or-FENCED business. It is true that, in a DB2 9 New Function Mode system, a SQL procedure (i.e., a stored procedure for which the routine source is contained within the CREATE PROCEDURE statement) will be external if it is created with the EXTERNAL NAME and/or FENCED options specified, and native if created with neither EXTERNAL NAME nor FENCED specified; however, it is not necessarily the case that an external SQL procedure re-created sans EXTERNAL NAME and FENCED will behave as you want it to when executed as a native SQL procedure. Why is this so? Well, some of the reasons are kind of obvious when you think about it. Others are less so. On the obvious side, think about options that you'd specify for an external SQL procedure (which ends up becoming a C language program with embedded SQL) at precompile time (e.g., VERSION, DATE, DEC) and at bind time (e.g., QUALIFIER, CURRENTDATA, ISOLATION). For a native SQL procedure, there's nothing to precompile (as there is no associated external-to-DB2 program), and the package is generated as part of CREATE PROCEDURE execution (versus by way of a separate BIND PACKAGE step). That being the case, these options for a native SQL procedure are specified via CREATE PROCEDURE options (some of which have names that are slightly different from the corresponding precompile options, an example being the PACKAGE OWNER option of CREATE PROCEDURE, which corresponds to the OWNER option of the BIND PACKAGE command). Here's another reason to pay attention to these options of CREATE PROCEDURE when converting an external SQL procedure to a native SQL procedure: the default options for CURRENTDATA and ISOLATION changed to NO and CS, respectively, in the DB2 9 environment.<br /><br />A less-obvious consideration when it comes to external-to-native conversion of SQL procedures has to do with condition handlers. These are statements in the SQL procedure that are executed in the event of an error or warning situation occurring. External SQL procedures do not allow for nested compound SQL statements (a compound SQL statement is a set of one or more statements, delimited by BEGIN and END, that is treated as a block of code); so, if you had within an external SQL procedure a compound SQL statement, and you wanted within that compound SQL statement a multi-statement condition handler, you couldn't do that by way of a nested compound statement. What people would often do instead in that case is code the condition handler in the form of an IF statement containing multiple SQL statements. In converting such an external SQL procedure to a native SQL procedure, the IF-coded condition handler should be changed to a nested compound SQL statement set off by BEGIN and END (in fact, it would be a good native SQL procedure coding practice to bracket even a single-statement condition handler with BEGIN and END). This change would be very much advised not just because nested compound statements are allowed in a native SQL procedure, but also because, in a native SQL procedure, an IF statement intended to ensure execution of multiple statements in an IF-based condition handler (e.g., IF 1=1 THEN...) would itself clear the diagnostics area, thereby preventing (most likely) the condition handler from functioning as desired (in an external SQL procedure, it so happens that a trivial IF statement such as IF 1=1 will not clear the diagnostics area).<br /><br />Also in the not-so-obvious category of reasons to change the body of a SQL procedure when converting from external to internal: resolution of unqualified parameter, variable, and column names differs depending on whether a SQL procedure is external or native. Technically, there's nothing to prevent you from giving to a parameter or variable in a SQL procedure a name that's the same as one used for a column in a table that the SQL procedure references. If a statement in an external SQL procedure contains a name that could refer to a variable or a parameter or a column, DB2 will, in processing that statement, check to see if a variable of that name has been declared in the SQL procedure. If a matching variable name cannot be found, DB2 will check to see if the name is used for one of the procedure's parameters. If neither a matching variable nor a matching parameter name is found, DB2 will assume that the name refers to a column in a table referenced by the procedure. If the same statement is encountered in a native SQL procedure, DB2 will first check to see if the name is that of a column of a table referenced by the procedure. If a matching column name is not found, DB2 will then look for a matching variable name, and after that for a matching parameter name. If no match is found, DB2 will return an error if VALIDATE BIND was specified in the CREATE statement for the native SQL procedure (if VALIDATE RUN was specified, DB2 will assume that the name refers to a column, and will return an error if no such column is found at run time). Given this difference in parameter/variable/column name resolution, it would be a good idea to remove ambiguities with respect to these names in an external SQL procedure prior to converting the routine to a native SQL procedure. This could be done either through a naming convention that would distinguish variable and parameter names from column names (perhaps by prefixing variable and parameter names with v_ and p_, respectively) or by qualifying the names. Variable names are qualified by the label of the compound statement in which they are declared (so if you're going to go this route, put a label before the BEGIN and after the END that frame the compound statement), parameter names are qualified by the procedure name, and column names are qualified by the name of the associated table or view.<br /><br />Then there's the matter of the package collection name that will be used when the SQL procedure is executed. For an external SQL procedure, this name can be specified via the COLLID option of the CREATE PROCEDURE statement. [If NO COLLID -- the default -- is specified, the name will be the same as the package collection of the calling program. If the calling program does not use a package, the SQL procedure's package will be resolved using the value of CURRENT PACKAGE PATH or CURRENT PACKAGESET, or the plan's PKLIST specification.] When a native SQL procedure is created, the name of the associated package's collection will be the same as the procedure's schema. In terms of external-to-native SQL procedure conversion, here's what that means:<br /></span><ul><li><span style="font-family:arial;">If external SQL procedures were created with a COLLID value equal to the procedure's schema, it's smooth sailing ahead.</span></li><li><span style="font-family:arial;">If external procedures were create with a COLLID value other than the procedure's schema, a relatively minor adjustment is in order. This adjustment could take one of two forms: a) go with the one "root" package for the native SQL procedure in the collection with the name matching the routine's schema, and ensure that this collection will be searched when the procedure is called, or b) </span><span style="font-family:arial;">add a SET CURRENT PACKAGESET statement to the body of the SQL procedure, specifying the collection name used for COLLID in creating the external SQL procedure, and (</span><span style="font-family:arial;">via BIND PACKAGE COPY) place a copy of the "root" package of the native SQL procedure in that collection.</span></li><li><span style="font-family:arial;">If external SQL procedures were bound with NO COLLID, there could be a good bit of related work in converting those routines to native SQL procedures, especially if a number of "variants" of an external SQL procedure's "root" package were generated and placed in different collections. The external SQL procedure package variants will have to be identified, SET CURRENT PACKAGESET will be needed in the native SQL procedure to navigate to the desired collection at run time (perhaps using a value passed by the caller as a parameter), and variants of the native SQL procedure's "root" package (again, that being the one in the collection with the name matching the procedure's schema) will need to be copied into the collections in which the external SQL procedure package variants had been placed.</span></li></ul><span style="font-family:arial;">The information I've provided in this blog entry is not exhaustive with respect to external-to-native SQL procedure conversion -- my intent was to cover the major issues that should be taken into consideration in planning your conversion process. For more details, check out the excellent "technote" document written by Tom Miller, a senior member of IBM's DB2 for z/OS development team and an authority on SQL procedures (both external and native). To access this document, go to the <a href="http://www-01.ibm.com/software/data/db2/support/db2zos/">DB2 for z/OS Support page</a> on IBM's Web site, enter "external native" as your search terms, and click on the search button -- Tom's document should be at the top of the search result list.<br /><br />Native SQL procedures are the way of the future, and I encourage you to develop a plan for converting external SQL procedures to native SQL procedures (if you have any of the former). It's very do-able, and for some external SQL procedures the conversion will in fact be very straightforward. For others, more conversion effort will be required, but the payoff should make that extra effort worthwhile.<br /></span>Robert Catterallhttp://www.blogger.com/profile/12629696535422235653noreply@blogger.com0