Creating another Index - AIX DB2

[wwwalton]

Running 8.3.  Anyone know of any gotchas if we use DB2 to create another index on a table to improve performance?  The field is defined only as a filter in the AppGroup.  I know in 8.4 we can alter, but in 8.3, it's day forward only correct?
Thanks,
-walt

[Stephen McNulty]

There is a way that you can change the filter field to an index at the db2 level by modifying the ARSAGFLD table, then create the index.  Bear in mind all the usual database backup precautions, etc and that this is not supported by IBM.

here is a quick rundown of the process from memory, you should verify in a test environment and confirm that all you are choosing the correct values, etc before boldly jumping in.

Ensure that DB2 has been stopped and started properly.  Make sure the OnDemand application is not running during this process. 

DB2 ?SELECT name,agid,agid_name FROM arsag?

DB2 ?UPDATE ARSAGFLD SET MASK=2 WHERE AGID=(application group id  AND NAME=?? name of field to change to an index?
DB2 ?CREATE INDEX (table name index) on (table name) (name of field to change to an index)?
DB2 CONNECT RESET
DB2 TERMINATE
Once finished and OnDemand has started, run the database reorg ;
ARSDB ?m

[Justin Derrick]

It might not have been clear from the sample code Stephen provided, but you'll need to run that 'create index' command for each table with AG data in it.  Not only will this take some time, but it'll use more disk space. 

If the field you've added is particularly large, or there are many (hundreds of millions or billions) of them, be prepared to see a substantial growth in your database.

Is there a reason you're stuck at 8.3?

-JD.

[wwwalton]

Resource constraints..... my mind.  I'm the only one supporting; there is an upgrade project in place but's it's been low priority due to other business projects; everyone understands (I hope) the risks of running on an unsupported release.

The table has about 3.5 mil and adds about 80k recs per week. The field in question is VARCHAR at 255.  API only uses it with LIKE driving a tablescan but worse, it's a web app and they issue: LOGIN, SQLSEARCH, LOGOFF many times.  There are 3 indexes defined but api wasn't using them and it has been more a political thing to get developer to change, Mgmnt would like to 'just give it more resources'. 

Just needed some ammunition to say, 'indexing this field is not the solution'. 

Thanks,
-walt

[Justin Derrick]

Gotcha.  And yes, it doesn't sound like this will help you at all.

I've wagged my finger at web app developers many times over the years for not knowing even the most fundamental basics about querying databases.

When it comes to CMOD, the golden rules are:

1)  ALWAYS restrict your search with a narrow date range, like, oh, I don't know...  the number of days the docs are kept in cache?
2)  ALWAYS search for a 'mostly-unique' value like customer number which is indexed in the database.  (And not, for example, zip code.)
3)  ALWAYS maintain a persistent connection for high-volume apps -- ie more than one query/minute at peak times.

There are others, but these are the big ones.

Good luck with your upgrade!

-JD.

[Alessandro Perucchi]

...
DB2 ?SELECT name,agid,agid_name FROM arsag?

DB2 ?UPDATE ARSAGFLD SET MASK=2 WHERE AGID=(application group id  AND NAME=?? name of field to change to an index?

...

Wooooooooooww... WARNING...
By doing that, it means you are setting the MASK to 2. But this field contains LOTS of signification. Each bit means something.
So if you put the value 2, it means you are setting all other bit to 0, which could destroy completely your datamodel and even produce some undesirable consequence...

I would propose something like:

DB2 ?UPDATE ARSAGFLD SET MASK=MASK+2 WHERE AGID=(application group id  AND NAME=?? name of field to change to an index?

So in that case you are sure that you touch ONLY the bit 2 (of course you need to check before that it was to 0).

You could do it also like that:

DB2 ?UPDATE ARSAGFLD SET MASK=MASK+2 WHERE AGID=(application group id)  AND NAME=?? name of field to change to an index? AND BITAND(MASK,2)=0

Cheers,
Alessandro