Tuesday, August 23, 2011

Plan to Attend IDUG This November to Improve Your DB2 Skills

It is almost that time of year again, time for the annual European IDUG conference. And as anyone who has ever attended this conference can tell you, it will be chock full of news, education, and networking opportunities for DB2 programmers, DBAs, sysprogs, and users! And you can take advantage of great offers including FREE IBM certification training and DB2 migration workshops!

So be sure to register for the IDUG DB2 Tech Conference in Prague, Czech Republic, 13-18 November 2011, at the Clarion Congress Hotel Prague. If you register on or before 17 October 2011 and receive a discount of EUR 275.

IDUG is also extending the following special offers:

  • Multiple Delegate Discount
    For every three individuals who register from the same organisation, a fourth may attend at the discounted rate of EUR 730.
  • Mentor Program
    If you have attended three previous IDUG DB2 Conferences, you are eligible to bring a first-time colleague to Prague for an 80% discount off the full registration fee.
  • One-Day Educational Seminars - Friday, 18 November 2011
    Registration for paid full conference delegates is EUR 450; the cost for just the one-day seminar is EUR 495. Select from the following session topics:
    - DB2 10 for z/OS - In Depth, Phil Grainger, Cogito
    - DB2 Intermediate and Advanced SQL, Daniel Luksetich, Yevich Lawson & Assoc Inc.
    - I Didn't Know DB2 did THAT!, Bonnie Baker, Bonnie Baker Corporation
    - Optimising DB2 for z/OS System Performance Using DB2 Statistics Trace, John Campbell, IBM Corporation
    - Rocket Science: DB2 for LUW Performance Analysis and Tuning Workshop, Scott Hayes, DBI

Register Today!

Friday, August 12, 2011

Do You Still Use That DB2 Program?

A recurring problem for programmers and IT folks in general is determining whether or not a particular program is still required. As your organization grows and the number of programs increases, keeping track of them all can become quite difficult.

As administration and management burdens increase, a common desire is to get rid of programs that are no longer being used. But it can be difficult to determine which programs are no longer used.

You can always “ask around,” but few IT professionals would be willing to delete anything based on such a general tactic. Another common method is to review performance reports or extracts from a performance warehouse. But perhaps your performance traces are not turned on all the time.

The question is probably more common in DB2 environments because of the plans and packages that consume storage and "sit around" taking up space if their associated program is no longer being used.

Well, for DB2 professionals this type of question becomes easier to answer once you migrate to DB2 10 for z/OS. DB2 maintains a new column, LASTUSED, in the DB2 Catalog. The column exists in both SYSIBM.SYSPACKAGE and SYSIBM.SYSPLAN and is defined as a DATE data type. The date is changed when the package header is requested from EDM. The column is also maintained for triggers and stored procedures. After BIND (REPLACE) this column is reset to the default value (CURRENT DATE).

This is similar to the LASTUSED column added to SYSIBM.SYSINDEXSPACESTATS in DB2 9, which is used to show the last time an index was used.

Of course, you will have to give it some time –- because you might have a program that is used only rarely, yet still used. Most shops have queries and programs that run quarterly, or even annually, but nevertheless are very important. So don’t just start freeing packages a month after you’ve migrated to DB2 10!

But it is good to know that we now have additional usage information at our fingertips in the DB2 Catalog, isn’t it?

Monday, July 25, 2011

Bad Database Standards

Today's blog post is a metapost, of sorts. I am using my DB2 blog to point you to a series of posts I made in my other blog (Data and Technology Today). You see, I write for two blogs, this one that focuses on DB2 and mainframe topics, and another one on data and database management topics in general.

One of my more popular series of posts on Data and Technology Today was the one on bad database standards. The general idea of these posts is to dissect and criticize standards that are outdated, or simply wrong. The seven part series ran about a year ago and has generated a bunch of comments. And it will be useful to DB2 folks to read these posts, too.

So without any further ado, here are links to the seven bad database standards:

  1. Limiting the number of indexes.

  2. Too Many Columns!

  3. Limiting The Number of Tables in “Online” Joins

  4. Duplication of Data

  5. None Shall Pass!

  6. What’s In A Name?

  7. What Does Support Mean?

Take a moment to click through to these links and peruse the "bad" standards and, if you are so inclined, post a comment (either here on this blog, or over on the other one) to share your "favorite?" bad standards.


Tuesday, July 19, 2011

Updating DB2 Developer's Guide (6th edition)

Just a brief blog post today to inform regular readers who might be concerned that I have not been blogging as frequently as in the past.

I am currently working on updating my book, DB2 Developer's Guide, for DB2 versions 9 and 10. This will be the sixth edition of the book and if all goes as planned, it should be available on IBM Press before the end of the year (2011).

Be sure to keep an eye on this blog and my web site (http://www.craigsmullins.com) for further details about the next edition of the book.

Sunday, June 19, 2011

The PIECESIZE Clause

One of the more troubling aspects of DB2 database design and creation is the non-partitioning index (NPI). Creating NPIs on tables in a partitioned table space can pose management and performance issues. Partitioned table spaces tend to be large and by their very design will span multiple underlying data sets. Any partitioning indexes will also span multiple data sets. But what happens when you need to define non-partitioning indexes on a table in a partitioned table space?

The PIECESIZE clause of the CREATE INDEX statement can be used during index creation to break an NPI into several data sets (or "pieces"). More accurately, the PIECESIZE clause specifies the largest data set size for a non-partitioned index. PIECESIZE can be specified in kilobytes, megabytes, or gigabytes. For example, the following statement will limit the size of individual data sets for the XACT2 index to 256 megabytes:

CREATE UNIQUE INDEX DSN8910.XACT2
ON DSN8910.ACT (ACTKWD ASC)
USING STOGROUP DSN8G910
PRIQTY 65536K
SECQTY 8192K
ERASE NO
BUFFERPOOL BP0
CLOSE NO
PIECESIZE 256M;

Basically, PIECESIZE is used to enable NPIs to be created on very large partitioned table spaces. It breaks apart the NPI into separate pieces that can be somewhat managed individually. Without PIECESIZE, NPIs would be quite difficult to manage and administer. Keep in mind, though, that PIECESIZE does not magically partition an NPI based on the partitioning scheme of the table space. This is a misperception held by some. So, if you have a partitioned table space with 4 partitions and then create an NPI with 4 pieces, the data in the NPI pieces will not match up with the data in the 4 partitions.

When using PIECESIZE, more data sets will be created and therefore you can obtain greater control over data set placement. Placing the pieces on separate disk devices can help to reduce I/O contention for SQL operations that access NPIs during read or update processing. The elapsed time improvement may be even greater when multiple tasks are accessing the NPI.

Separating the NPI into pieces allows for better performance of INSERT, UPDATE and DELETE processes by eliminating bottlenecks that can be caused by using only one data set for the index. The use of pieces also improves concurrency and performance of heavy INSERT, UPDATE, and DELETE processing against any size partitioned table space with NPIs.

Keep in mind that PIECESIZE is only a specification of the maximum amount of data that a piece (that is, a data set) can hold and not the actual allocation of storage, so PIECESIZE has no effect on primary and secondary space allocation. Each data set will max out at the PIECESIZE value, so specifying PRIQTY greater than PIECESIZE will waste space. But also make sure that you avoid setting the PIECESIZE too small. A new data set will be allocated each time the PIECESIZE threshold is reached. DB2 will increment the A001 component of the data set name each time. Ideally, the value of your primary quantity and secondary quantities should be evenly divisible into PIECESIZE to avoid wasting space.

To choose a PIECESIZE value, divide the overall size of the entire NPI by the number of data sets that you wish to have. For example, for an NPI that is 8 megabytes, you can arrive at 4 data sets for the NPI by specifying PIECESIZE 2M. Of course, if your NPI grows over 8 megabytes in total you will get additional data sets. Keep in mind that 32 pieces is the limit if the underlying table space is not defined with DSSIZE 4G or greater. The limit is 254 pieces if the table space is defined as DSSIZE 4G or greater.