Just a quick note this morning to let folks in Zurich, Switzerland and London, UK know about a three-day performance workshop for DB2 V8 coming up in June. The workshop is based on the IBM Redbook DB2 for z/OS Version 8 Performance Topics, SG24-6465. If you are looking for an overview and update of performance-related issues you can tackle with DB2 V8, consider this workshop. According to the redbook web site this workshop helps "you understand the performance implications of migrating from DB2 V7 to DB2 V8, highlights the key performance functions and sets the right expectations. It provides the type of information needed to evaluate the performance impact of DB2 V8 and the capacity planning needs."
Of course, this workshop information is directed mostly at my European readers... unless, of course, you are a USA reader with some extra travel budget and your manager will allow you to travel overseas!?!? (I know, I know, you can't even get budget to travel to Scranton, let alone Zurich...)
Here is the information for each:
That's all for today!
News, views, and items of interest on IBM's Db2 database management system and mainframes.
Monday, March 19, 2007
Wednesday, March 14, 2007
IDUG News
A lot of new stuff has been going on at the International DB2 User's Group (IDUG) the past few months, so I thought I'd write a quick blog entry to update folks about what's new.
First of all, this year's North American IDUG conference will be held the week of May 6 thru 10, 2007, in San Jose, CA. As always, the conference offers a fantastic learning and networking opportunity for DB2 and Informix users with hundreds of technical sessions, as well as 16 full day pre-conference seminars, the opprotunity to take certification exams for free, numerous special interest groups, and a great vendor exhibition. Also, this year's event is the first to offer IMS content, too - so if you are an IMS DBA or technician now is the time to add IDUG to your educational opportunities!
In other IDUG news, the group is expanding its conference coverage to India. The 2007 IDUG India Forum, taking place May 31 – June 2, 2007 in Bangalore, India, is geared towards professional application developers and DBAs. This three-day event will present content that will appeal to new users as well as experienced professionals. So if you are in India, IDUG is coming your way!
Finally, if you haven't visited the IDUG web site in awhile, now is the time to check it out again. IDUG volunteers have put a lot of effort into revamping the site and it looks great.
That's it for now... check in again soon!
First of all, this year's North American IDUG conference will be held the week of May 6 thru 10, 2007, in San Jose, CA. As always, the conference offers a fantastic learning and networking opportunity for DB2 and Informix users with hundreds of technical sessions, as well as 16 full day pre-conference seminars, the opprotunity to take certification exams for free, numerous special interest groups, and a great vendor exhibition. Also, this year's event is the first to offer IMS content, too - so if you are an IMS DBA or technician now is the time to add IDUG to your educational opportunities!
In other IDUG news, the group is expanding its conference coverage to India. The 2007 IDUG India Forum, taking place May 31 – June 2, 2007 in Bangalore, India, is geared towards professional application developers and DBAs. This three-day event will present content that will appeal to new users as well as experienced professionals. So if you are in India, IDUG is coming your way!
Finally, if you haven't visited the IDUG web site in awhile, now is the time to check it out again. IDUG volunteers have put a lot of effort into revamping the site and it looks great.
That's it for now... check in again soon!
Tuesday, March 06, 2007
DB2 9 for z/OS General Availability Announced
Today, March 6, 2007, IBM announced the general availability of the next version of DB2 for z/OS, DB2 9, for March 16, 2007. The full announcement can be read here.
You can read a high-level overview of DB2 9 for z/OS in my DB2 Magazine article titled DB2 9 for z/OS Roars to Life.
You can read a high-level overview of DB2 9 for z/OS in my DB2 Magazine article titled DB2 9 for z/OS Roars to Life.
Wednesday, February 28, 2007
QMF vs. SPUFI
As regular readers of my blog know, I sometimes use the blog to answer questions I get via e-mail. This is one of those times...
The question I received is this:
Can you bring out the major differences between QMF and SPUFI?
Here is my response:
The biggest difference between QMF and SPUFI is that QMF is a query/reporting environment with the ability to format reports. SPUFI is just a quick and dirty SQL execution engine.
(Here is a trivia question: what does the acronym SPUFI stand for? The answer is provided at the bottom, so page down if you want to know...)
If you need to produce nice-looking reports, enable user input to a query, or store your queries and reports for future usage, QMF is a much better technology for doing so. QMF also offers data formatting and translation capabilities that are difficult (sometimes impossible) to accomplish using SPUFI and SQL alone.
A typical end-user might have difficulty using SPUFI because it requires using data sets to store your SQL statements and pass them to DB2. The results are also delivered to another data set. Most end users are not comfortable managing and manipulating mainframe data sets. QMF, on the other hand, stores its queries in tables and hides this fact from the user with a nice interface for saving and recalling SQL queries (and results).
Keep in mind, though, that SPUFI comes for free with DB2 whereas QMF is an add-on product and costs money. Not every DB2 customer will have QMF, whereas every DB2 (mainframe) customer will have SPUFI.
OK, now, what does SPUFI stand for?
The answer: SQL Processor Using File Input.
The question I received is this:
Can you bring out the major differences between QMF and SPUFI?
Here is my response:
The biggest difference between QMF and SPUFI is that QMF is a query/reporting environment with the ability to format reports. SPUFI is just a quick and dirty SQL execution engine.
(Here is a trivia question: what does the acronym SPUFI stand for? The answer is provided at the bottom, so page down if you want to know...)
If you need to produce nice-looking reports, enable user input to a query, or store your queries and reports for future usage, QMF is a much better technology for doing so. QMF also offers data formatting and translation capabilities that are difficult (sometimes impossible) to accomplish using SPUFI and SQL alone.
A typical end-user might have difficulty using SPUFI because it requires using data sets to store your SQL statements and pass them to DB2. The results are also delivered to another data set. Most end users are not comfortable managing and manipulating mainframe data sets. QMF, on the other hand, stores its queries in tables and hides this fact from the user with a nice interface for saving and recalling SQL queries (and results).
Keep in mind, though, that SPUFI comes for free with DB2 whereas QMF is an add-on product and costs money. Not every DB2 customer will have QMF, whereas every DB2 (mainframe) customer will have SPUFI.
OK, now, what does SPUFI stand for?
The answer: SQL Processor Using File Input.
Tuesday, February 27, 2007
Dynamic SQL and Performance
The performance of dynamic SQL is one of the most widely debated DB2 issues. Some shops try to avoid it, but in this day and age of ERP/CRM systems, Java/JDBC programming, and web applications, all of which heavily rely on dynamic SQL, its use is becoming more ubiquitous.
Still, many shops that allow dynamic SQL try to place strict controls on its use. But as new and faster versions of DB2 are released, many of the restrictions on dynamic SQL use can be eliminated.
I suppose that you can still find some valid reasons for prohibiting dynamic SQL. For example, you should avoid dynamic SQL when the dynamic SQL statements are just a series of static SQL statements in disguise. Consider an application that needs two or three predicates for one SELECT statement that is otherwise unchanged. Coding three static SELECT statements can be more efficient than coding one dynamic SELECT with a changeable predicate. The static SQL takes more time to code but probably less time to execute.
Another reason for avoiding dynamic SQL is that it can require more overhead to process than equivalent static SQL. Dynamic SQL incurs overhead because the cost of the dynamic bind, or PREPARE, must be added to the processing time of all dynamic SQL programs. But this overhead is not quite as costly as many people seem to think it is.
To determine the cost of a dynamic bind, consider running some queries using SPUFI with the DB2 Performance trace turned on. Then examine the performance reports or performance monitor output to determine the elapsed and TCB time required to perform the PREPARE. The results should show elapsed times less than 1 second and subsecond TCB times. The actual time required to perform the dynamic prepare will vary with the complexity of the SQL statement. In general, the more complex the statement, the longer DB2 will take to optimize it. So be sure to test SQL statements of varying complexity.
Of course, the times you get will vary based on your environment, the type of dynamic SQL you use, and the complexity of the statement being prepared. Complex SQL statements with many joins, table expressions, unions, and subqueries take longer to PREPARE than simple queries. However, factors such as the number of columns returned or the size of the table being accessed have little or no effect on the performance of the dynamic bind.
And prepared dynamic SQL can be cached in the EDM pool so that the same SQL statement can reuse the mini plan for the statement the next time it runs. Of course, the statement has to be exactly the same in order to benefit from the dynamic SQL cache.
Keep in mind, too, that performance is not the only factor when deciding whether or not to use dynamic SQL. For example, if a dynamic SQL statement runs a little longer than a static SQL statement but saves days of programming cost then perhaps dynamic SQL is the better choice. It all depends on what is more important -- the cost of development and maintenance or squeezing out every last bit of performance at any cost.
Overhead issues notwithstanding, there are valid performance reasons for favoring dynamic SQL, too. For example, dynamic SQL can enable better use of indexes, choosing different indexes for different SQL formulations. Properly coded, dynamic SQL can use the column distribution statistics stored in the DB2 catalog, whereas static SQL is limited in how it can use these statistics. Use of the distribution statistics can cause DB2 to choose different access paths for the same query when different values are supplied to its predicates.
The REOPT bind parameter can be used to allow static SQL containing host variables or special registers to behave like incremental-bind statements. When dynamic reoptimization is activated, a dynamic bind similar to what is performed for dynamic SQL is performed. This means that these statements get compiled at the time of EXECUTE or OPEN instead of at BIND time. During this compilation, the access plan is chosen, based on the real values of these variables.
Specifying REOPT ONCE causes the access plan to be cached after the first OPEN or EXECUTE request and it is used for subsequent execution of this statement. With REOPT ALWAYS, the access plan is regenerated for every OPEN and EXECUTE request, and the current set of host variable, parameter marker, and special register values is used to create this plan.
Additionally, consider that the KEEPDYNAMIC bind option can enhance the performance of dynamic SQL. When a plan or package is bound specifying KEEPDYNAMIC(YES), the prepared statement is maintained across COMMIT points. Contrast that with KEEPDYNAMIC(NO), where only cursors using the WITH HOLD option keep the prepared statement after a COMMIT.
Dynamic SQL usually provides the most efficient development techniques for applications with changeable requirements (for example, numerous screen-driven queries). In addition, dynamic SQL generally reduces the number of SQL statements coded in your application program, thereby reducing the size of the plan and increasing the efficient use of system memory.
So, if you have a compelling reason to use dynamic SQL, then by all means, go ahead and code up your program to use dynamic SQL. I mean, after all, it is no longer the early, dark days of DB2 when dynamic SQL almost certainly meant performance problems. And, as I mentioned in the beginning, dynamic SQL is likely to be foisted on you in your new, more modern applications even if you continue to desperately keep it out of your COBOL programs.
For more in-depth details on dynamic SQL, consider downloading the following IBM redbook: DB2 for z/OS and OS/390 : Squeezing the Most Out of Dynamic SQL.
Still, many shops that allow dynamic SQL try to place strict controls on its use. But as new and faster versions of DB2 are released, many of the restrictions on dynamic SQL use can be eliminated.
I suppose that you can still find some valid reasons for prohibiting dynamic SQL. For example, you should avoid dynamic SQL when the dynamic SQL statements are just a series of static SQL statements in disguise. Consider an application that needs two or three predicates for one SELECT statement that is otherwise unchanged. Coding three static SELECT statements can be more efficient than coding one dynamic SELECT with a changeable predicate. The static SQL takes more time to code but probably less time to execute.
Another reason for avoiding dynamic SQL is that it can require more overhead to process than equivalent static SQL. Dynamic SQL incurs overhead because the cost of the dynamic bind, or PREPARE, must be added to the processing time of all dynamic SQL programs. But this overhead is not quite as costly as many people seem to think it is.
To determine the cost of a dynamic bind, consider running some queries using SPUFI with the DB2 Performance trace turned on. Then examine the performance reports or performance monitor output to determine the elapsed and TCB time required to perform the PREPARE. The results should show elapsed times less than 1 second and subsecond TCB times. The actual time required to perform the dynamic prepare will vary with the complexity of the SQL statement. In general, the more complex the statement, the longer DB2 will take to optimize it. So be sure to test SQL statements of varying complexity.
Of course, the times you get will vary based on your environment, the type of dynamic SQL you use, and the complexity of the statement being prepared. Complex SQL statements with many joins, table expressions, unions, and subqueries take longer to PREPARE than simple queries. However, factors such as the number of columns returned or the size of the table being accessed have little or no effect on the performance of the dynamic bind.
And prepared dynamic SQL can be cached in the EDM pool so that the same SQL statement can reuse the mini plan for the statement the next time it runs. Of course, the statement has to be exactly the same in order to benefit from the dynamic SQL cache.
Keep in mind, too, that performance is not the only factor when deciding whether or not to use dynamic SQL. For example, if a dynamic SQL statement runs a little longer than a static SQL statement but saves days of programming cost then perhaps dynamic SQL is the better choice. It all depends on what is more important -- the cost of development and maintenance or squeezing out every last bit of performance at any cost.
Overhead issues notwithstanding, there are valid performance reasons for favoring dynamic SQL, too. For example, dynamic SQL can enable better use of indexes, choosing different indexes for different SQL formulations. Properly coded, dynamic SQL can use the column distribution statistics stored in the DB2 catalog, whereas static SQL is limited in how it can use these statistics. Use of the distribution statistics can cause DB2 to choose different access paths for the same query when different values are supplied to its predicates.
The REOPT bind parameter can be used to allow static SQL containing host variables or special registers to behave like incremental-bind statements. When dynamic reoptimization is activated, a dynamic bind similar to what is performed for dynamic SQL is performed. This means that these statements get compiled at the time of EXECUTE or OPEN instead of at BIND time. During this compilation, the access plan is chosen, based on the real values of these variables.
Specifying REOPT ONCE causes the access plan to be cached after the first OPEN or EXECUTE request and it is used for subsequent execution of this statement. With REOPT ALWAYS, the access plan is regenerated for every OPEN and EXECUTE request, and the current set of host variable, parameter marker, and special register values is used to create this plan.
Additionally, consider that the KEEPDYNAMIC bind option can enhance the performance of dynamic SQL. When a plan or package is bound specifying KEEPDYNAMIC(YES), the prepared statement is maintained across COMMIT points. Contrast that with KEEPDYNAMIC(NO), where only cursors using the WITH HOLD option keep the prepared statement after a COMMIT.
Dynamic SQL usually provides the most efficient development techniques for applications with changeable requirements (for example, numerous screen-driven queries). In addition, dynamic SQL generally reduces the number of SQL statements coded in your application program, thereby reducing the size of the plan and increasing the efficient use of system memory.
So, if you have a compelling reason to use dynamic SQL, then by all means, go ahead and code up your program to use dynamic SQL. I mean, after all, it is no longer the early, dark days of DB2 when dynamic SQL almost certainly meant performance problems. And, as I mentioned in the beginning, dynamic SQL is likely to be foisted on you in your new, more modern applications even if you continue to desperately keep it out of your COBOL programs.
For more in-depth details on dynamic SQL, consider downloading the following IBM redbook: DB2 for z/OS and OS/390 : Squeezing the Most Out of Dynamic SQL.
Subscribe to:
Posts (Atom)