DB2: Optimizing Database Search

I was looking for a way through which I can search through my DB2 9.7 db. I mean, if I have a value 'Obama' I want to query all tables in DB and find the tables which contain this value. I tried searching but did'nt find a way, so, i wrote a shell script myself. This works fine but takes a lot of time. Please suggest some ways by which I can optimize this script. Any help is appreciated. I am using Cygwin for running this script.

I do not think there is a simpler way to do it, short of installing additional database products such as Text Extenders or Lucene.

Even indexing all the character columns would not help, since you are performing an 'UPPER' function on the column, which would eliminate index usage. Indexing the numeric columns would provide some relief for those searches. You COULD generate columns for each of the character columns as 'upper(colname)', index those, and have the query search them.

Another alternative would be to create a 'character values' table that contains ALL the text values of all the character columns and use triggers on each of the other tables to update that with the text values you found. If I did something like that, I'd change the original tables to use a bigint for each text column and put the generated key of the text in the 'character value table'. I don't particularly like that solution, but it would simplify that lookup.

Let me take a guess -- your search queries ( meaning scripts ) were doing TBscan big-time -- please advise.
If that was the case, then there's little you or anyone else can do with respect to optimization. Let's face it - you were simply I/O bound.

That being said -- IBM has a sleek product called Information Analyzer that can do precisely what you intended to do "walk the database" in search of anything and lots more. Fancy this -- It can recognize the name "Obama" even when stored as "oBaMa" . This thing works like a charm!

Download and take it for a test drive. I believe it runs on both platforms. ( Win and Linux)

We have used the following in our Applications:
Last Name & "LAST_NAME_UPPER" in case of Obama.
Store the data twice one in original case and second time in upper case ( could go with lower case also ).
When search always search on the upper column

Where Upper ( Obama ) = Last_Name_upper

This solution will not work for Free Form text or if your data has spaces, etc.
If you are searching for McDonnald and Last Name is stored as MC DONNALD, this will fail to fetch the data.

The question is how can "Generate Columns" help him at this eleventh hour. His problem is mainly performance. He reported that his queries weren't optimized - meaning they were not using indexes thus running slow. How can we help this DBA. I recommended a product called IA ( Info Analyzer), I've had the pleasure of using this product to generate meta-data info for a truck parts database containing 90 tables; average record count = 20mil. At the completion, using the generated report, I was a able to list all tables with columns containing the word = "hex nut"

An index on expression that put the UPPER function in the index would shift the non-indexable stage 2 predicate to an indexable index. If there are many reads and few inserts or updates this could help. But if there are many inserts then moving the extra work to the insert could cause slow inserts.