Terry Frangos is a manager of DB2 LUW technical support. Terry has been with IBM since 1994 and with the DB2 Technical support team since 2000. This blog will share information from the DB2 LUW technical support team with occasional guest bloggers providing some best practices, ideas and methods to help DB2 LUW clients.

Solving problems faster with Knowledge Collections

In my last post I wrote about Must Gather Documents for DB2 and how they could help you solve problems faster.

In this post I want to tell you about Knowledge Collections which are another way of finding information faster and therefore potentially solving your concerns faster.

A Knowledge Collection is a focused compilation of links to documents that share a common theme. Knowledge Collections are navigation aids that organize content to help users quickly find relevant information. Knowledge Collections are not designed to be an all-inclusive list of all documents dealing with the specific theme.

Many times when you search for a certain issue there are other related issues that may be good to know about in order to help you with increasing your knowledge in an area or even more importantly solving the current problems you are facing.

You can find some good DB2 LUW Knowledge Collections at this site DB2 LUW Knowledge Collections

I'll also be tweeting about these and other tips. Follow me on Twitter to get the information.

Solving DB2 problems faster. Collecting data using Must Gathers.

So you've run into a DB2 problem and you are going to open a PMR so you can get the support team to help you out. In many cases you are well educated in DB2 but don't have enough info to solve the concern yourself.

This is where the Collecting Data documents come into play. They are also know as Must Gather information. "Must" is a bit of a strong word but a good one to convey the fact that if you do open a PMR we will probably ask you to gather certain information based on the type of issue you are facing.
Have a look at this technote technote that talks about why it is good to gather the information early or before you open the PMR.

Also visit this page for a list of the DB2 LUW Must Gathers.

I've also been tweeting about these and other tips. Follow me on Twitter to get the information.

Support policy statement for DB2 for Linux, UNIX and Windows fix packs, special builds

Time to Upgrade to DB2 LUW V9

DB2 LUW V8 is going out of support April 30, 2009

• Provide a minimum of 5 years of product technical support beginning at the planned availability date of the version/release of the product.
• Ensure support is available for all IBM components of a product or until the product or bundle is withdrawn from support. In addition, all components of a bundle have a common End of Service date.
• Publish a notice of support discontinuance ("End of Service") for a product at least 12 months prior to the effective date.
• Align the effective date of support discontinuance ("End of Service") to occur on common dates either in the months of April or September.
• Make support extensions available, for an additional fee, for a minimum period of three (3) years following the product's effective support discontinuance date. Support extensions are designed to allow migration to the current release to be successfully completed.

October 1st Online Community Day

What does this mean?

Take just one simple action to demonstrate your participation in the broader community. Just one. You could, for example:

DB2 Best Practices

It includes such topics as:
• Database Storage
• Physical Database Design
• Data Life Cycle Management
• Minimizing Planned Outages
• IBM Data Server Security
• Deploying IBM DB2 Products
• Writing and Tuning Queries for Optimal Performance
• Tuning and Monitoring Database System Performance
• Deep Compression
• Managing XML Data
• Improving Data Server Utilization and Management through Virtualization
• and of course my favorite which I posted about earlier the Frequently Asked Questions from Problem Management Reports

When should a reorg be run?

Over the years we have had many questions about when or how often should a reorg be run on tables in a database.

Runstat and reorgchk should be run on a regular basis.. whereas reorg on tables should be run on an as needed basis depending on the output from reorgchk.
Rerogchk can be run with option or on table
For more command options check the reorgchk command options.

Now, how to make a descision if a reorgchk needs to be run or not.
In the output depending on the table statistics and index statistics output

REORG
    Each hyphen (-) displayed in this column indicates that the calculated results were within the set bounds of the corresponding formula, and each asterisk (*) indicates that the calculated results exceeded the set bounds of its corresponding formula.

1. - or * on the left side of the column corresponds to F1 (Formula 1)
2. - or * in the middle of the column corresponds to F2 (Formula 2)
3. - or * on the right side of the column corresponds to F3 (Formula 3)

  For example, --- indicates that, since the formula results of F1, F2, and F3 are within the set bounds of the formula, no table reorganization is suggested. The notation *-* indicates that the results of F1 and F3 suggest table reorganization, even though F2 is still within its set bounds. The notation *-- indicates that F1 is the only formula exceeding its bounds.

REORG
  Each hyphen (-) displayed in this column indicates that the calculated results were within the set bounds of the corresponding formula, and each asterisk (*) indicates that the calculated result exceeded the set bounds of its corresponding formula.

1. - or * on the left column corresponds to F4 (Formula 4)
2. - or * in the second from left column corresponds to F5 (Formula 5)
3. - or * in the middle column corresponds to F6 (Formula 6)
4. - or * in the second column from the right corresponds to F7 (Formula 7)
5. - or * on the right column corresponds to F8 (Formula 8)

  * If the results of the calculations for Formula 1,2 and 3 do not exceed the bounds set by the formula and the results of the calculations for Formula 4,5 or 6 do exceed the bounds set, then index reorganization is recommended.

  * If only the results of the calculations Formula 7 exceed the bounds set, but the results of Formula 1,2,3,4,5 and 6 are within the set bounds, then cleanup of the indexes using the CLEANUP ONLY option of REORG INDEXES is recommended.

  * If the only calculation result to exceed the set bounds is the that of Formula 8, then a cleanup of the pseudo empty pages of the indexes using the CLEANUP ONLY PAGES option of REORG INDEXES is recommended.


Categories : [   database  |  reorg  |  reorgchk  |  tables  ]

Jun 24 2008, 09:50:43 AM EDT Permalink

Are you Restoring a portion of your database for testing?

Today's post is provided by our guest blogger Shakil Choudhury.

Database Administrators sometimes may need to restore a portion of their databases from Production to a Test environment for various reasons, ( testing etc ). They need to restore a small portion of their Production database because the Production database is too big to restore in test env and/or the Production database restore takes a very long time.

It was not possible to do this prior to Version 9.1.

In Version 9.1 DB2 offers partial restore ( tablespace level ). The following example shows how to do that and it also explores a specific case where two tablespaces have dependencies in the database.

The example below shows that one table has been created in one tablespace and its CLOB data is kept in different tablespace and the DBA decides to avoid these two tablespaces ( partial restore ) and what a DBA may face after the restore is completed and are about to drop those tablespaces.

In DB2 Version 9.1 we have options of restoring a database without some of the tablespaces. This is new in V9.1.

The following shows a step by step test.

Lets first create a database name Testdb. Then create two tablespaces.

Tablespace creation syntex is not given here.

I have created one tablespace name myspace3 ( regular ) and which is DMS

I have Created another tablespace name myspace4 ( large ) and which is DMS

Create one table MYTABLE1 in myspace3, and insert some values in table MYTABLE1.

Then create another table MYTABLE2 in myspace3 with CLOB field and then make sure CLOB field goes to large tablespace myspace4.

Insert some values in the MYTABLE2.

Then do a offline backup of the database Testdb and then drop the database.

Then do a redirected restore of the database as below:

db2 "restore db Testdb rebuild with all tablespaces in image except tablespace (myspace3,myspace4) from '/home/shakilc/backup' into Newdb redirect"

db2 restore db testdb continue

Restore completed.

db2 connect to Newdb.
connect succcessful.
Then

db2 list tablespaces show detail

You will see:

Tablespace ID       = 3
Name                   = MYSPACE3
Type                   = Database managed space
Contents             = All permanent data. Regular table space.
State                  = 0xc000
  Detailed explanation:
    Offline
    Drop Pending

Tablespace ID      = 4
Name                   = MYSPACE4
Type                   = Database managed space
Contents            = All permanent data. Large table space.
State                  = 0xc000
  Detailed explanation:
    Offline
    Drop Pending

You will also see more tablespaces but for the sake of this doc, I am not showing those.

Now, We will try to drop MYTABLE1 and MYTABLE2 and then try to drop tablespace myspace3 and myspace4

/home/shakilc: db2 drop table MYTABLE1
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0290N Table space access is not allowed. SQLSTATE=55039

/home/shakilc: db2 drop table MYTABLE2
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0290N Table space access is not allowed. SQLSTATE=55039

/home/shakilc: db2 drop tablespace MYSPACE3
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0282N Table space "MYSPACE3" cannot be dropped because at least one of the tables in it, "SHAKILC.MYTABLE2", has one or more of its parts in another table space. SQLSTATE=55024

/home/shakilc: db2 drop tablespace MYSPACE4 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0282N Table space "MYSPACE4" cannot be dropped because at least one of the tables in it, "SHAKILC.MYTABLE2", has one or more of its parts in another table space. SQLSTATE=55024

Trick here is to drop both tablespaces in the same statement:

db2 => drop tablespace myspace3,myspace4
DB20000I The SQL command completed successfully.

Now you have databases without two tablespaces ( myspace3 and myspace4 ).

From doc:

You can drop a user table space that contains all of the table data including index and LOB data within that single user table space. You can also drop a user table space that might have tables spanned across several table spaces. That is, you might have table data in one table space, indexes in another, and any LOBs in a third table space. You must drop all three table spaces at the same time in a single statement. All of the table spaces that contain tables that are spanned must be part of this single statement or the drop request will fail.

Ref:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/t0005213.htm

Restore command:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0001976.htm

Out of Support? When did that happen?

Unlike many other products out there today, software doesn't have a best before date on the box and smelling the CD/DVD's to see if it is still fresh will only get you some strange looks. In many cases you probably don't even have a box or a CD/DVD as you downloaded the product from Passport Advantage. How are you supposed to know that a product is going out of support?

The IBM Software Support Lifecyle web site holds the key to that freshness
Besides explaining the Lifecycle policy you can also view when IBM products GA'd and their End of Service dates. Have a look at the site to see which of the products you own are coming close to their end of support date. This way you will have time to make decisions on what to do next. Perhaps migrate to the latest version or purchase a Service Extension. Either way, don't forget that everything these days has a best before date, from cereal to software be aware.

Categories : [   endofsupport  |  eol  |  eos  |  lifecycle  |  support  ]

Jun 16 2008, 05:22:26 PM EDT Permalink

DB2 LUW Frequently Asked Questions

I'm a little behind the ball on this as others have already blogged / posted about the existance of a document that has DB2 LUW's most frequently asked and answered questions in it.

• Best Practices for DB2 for Linux, UNIX, and Windows has a link to it at the bottom of the page.
• Susan wrote about it in her blog post as well.

What I do have over those other blogs is that I actually work closely with technical team of support analysts that get the calls and answer those questions. Most of the information is out there on the web someplace. In the Info Center or in Technotes but to my knowledge we have never taken all the most common questions and put them in a single document.

That document now resides here.
And it's also available in this Technote that we will be adding to as we get more common questions. Keep it handy and refer to it when you have a technical problem that someone else has probably asked.
I'll try to bring those technical folks closer to you with this blog. Some guest technical support bloggers with some interesting information that may help you with DB2 LUW.

Categories : [   DB2  |  FAQ  |  LUW  |  questions  |  support  ]

Jun 13 2008, 04:57:38 PM EDT Permalink