Hi Guru's.

I need some pointers from the experts so I can put in my own hard work and swat up on the right subjects, i'm still new. Lot of detail here to ensure I don't miss anything.

We are running IBM Content Manager 8.3 (but please don't let this put you off ) which uses DB2 8.1-FP17 also known as 8.2-FP10 system. I am planning to upgrade back to supportable ICM 8.4 and DB2 9.x ASAP. The issue appears to be in the DB2 layer.

Problem
We have stored 400k lines in the ICMNLSDB, no serious problems. Each line represents a document.
The ICM interface hides much of the DB2 level stuff from me as an Admin but because performance was slowly degrading I had to start looking at the DB2 layer (IBM sold it as a blackbox )

I added two indexes via ICM to the two main attributes (columns), this reduced search times from 1.2 second to 600ms. I then reviewed and ran the standard DB2 optimisation steps which are frequently referred to on the forums and documentation. Perhaps this was a bit fool-hardy but the result for the indexed attributes where reduced from 600ms to 12ms. Awesome.

• RUNSTAT using a script for all tables where type=T
• REORGCHK
• REORG highlighted tables (db2 REORG TABLE [Table Name] ALLOW NO ACCESS) and (db2 REORG INDEXES ALL FOR TABLE [Table Name] ALLOW NO ACCESS)
• RUNSTAT again
• REBIND

But after the optimisation searches using non-indexed fields were much worse then before. I can only assume that the Reorg unbalaned some natural order that made table scans somewhat easier? I don't really understand this part and stupidly assumed non-indexed columns searches would remain the same. For example:

Search index field, DocID = NULL- 518msec
Search on non-indexed field, Date = 26/05/2010- 523msec.

This is not terrible but what I truly don't understand is if I do a combo search on two fields DB2 runs its search for 40minutes where as previously it took no more then a few seconds. Example:

Search on DocID = NULL and Date = 01/06/2010 = query time upto +40minutes before I finally kill the query in DB2 Activity Monitor.

Furthermore; I did observe at one point that the Database size was rapidly expanding, I assume this was due to temp table growth in an attempt to manage the query.


Question 1 Is the user of NULL a problem for DB2?

Question 2 Would something in the Reorg affect the natural order of data in the tables? I assume this must be the case but need confirmation. Further assumption is the it uses the RUNSTATs to would out the best order?

Question 3 I have the option to index all attributes (columns) but this seems a bit design lazy. Is it lazy if the users come back with a requirement to search on any attribute?

FYI: DocID is NULL

Lenny

we reorg table indexes first and then the table as some of our tables have clustered indexes. per db2 v9.5 docs in these cases:
"If you do not specify the name of an index, the records are reorganized without regard to order. If the table has a clustering index defined, however, and no index is specified, then the clustering index is used to cluster the table."

Same here and we are some fixpacks behind you.
Do not let the "black box" fool you. I is true: when you add an item-type the DDL is dynamicly changed and tables & views are added to your schema.
Your story sounds too simple. I'd say: every table in the ICMUT0 series represents a document. The table and column names are very cryptic, but there is always a user-view with a more logical name and the same column names as you use in the CM8 system.
As for tuning: let db2advis do the work for you. Follow that advice. A far better approach if you ask me. So drop those CM8-indices and follow the db2advice advice, use db2 instead of cm8.
b.t.w. are you using Symbaloo | Access your bookmarks anywhere ? If so I'd like to share my "webmix" with links

Thanks Dr. and MarkhamDB

I'll review the db2advis again and go back to scratch. My training was entirely around CM8, DB2 didn't factor in during the Marchitecture talks from IBM / 3rd Party sales.

The interface tools like db2advis are something I really need to readup on, i'm never sure what the impact will be on completing the wizards. Just nerves. I recently found a bunch of tables called EXPLAIN and with my USERID factored in *GULP*.

Let me double-check Dr.'s advice on Indices. Use the DB2 indexes rather then the index options in the Item Type creation? Seems a radical step from my training.

Thanks again, it's very helpful to have some input from experienced people!

Yes, but IBM propagates it as well: IBM - Improving the performance of document routing process updates
See?

In your opening post you mention an upgrade to CM8.4 and DB2V9. I am not sure about the changes you made directly to db2 in such case. It might be wise to drop them all just before upgrading (bringing the database in the original state). So document your changes!

Thanks Dr.

I'll read the posted in more detail but at a glance the content looks very helpful and strangely unambiguous for IBM.

All the indexes i've added have been via the CM8 interface, the DB2 backend hasn't be altered directly so far, this information seems to be a departure. In it's defence the indexing via CM8 seems good, I just didn't want to necessarily index all attributes just because of crap business requirements. I'll have to look at both options.

As for the the upgrade to CM8.4, I'm engaging with a third party IBM reseller and support agent. 3 weeks of planning and then i'll be recording the whole process in minute detail. The data migration is the scary bit and I've been told to categorically rule out the in place upgrade (which i think we all know would bomb horribly )