Minimum Folder Length - CICS folder search

[Michael Prouse]

We are in the process of migrating from OnDemand v2.1 to CMOD8. In v2.1, when defining a report ID or folder, we were able to define a minimum/maximum field length the user must enter when performing a search. In CMOD8, there is no minimum/maximum length on the application/folder; in fact, a user may enter 1 wildcard character and begin a search.

We have 14 billion documents archived in our system. We are very concerned about not being able to require a minimum length for the search criteria as these searches may be very CPU intensive and we could have several of these transactions spinning against DB2 for an excessive amount of time before the search is complete.

Has anyone else encountered this issue or have a "workaround" for this loss of functionality from ODv2.1 to CMOD8? I would to hear from anyone with any suggestions/thoughts/ideas on this issue. Thanks!


Michael A. Prouse
BlueCross BlueShield of SC

[Justin Derrick]

Hi Michael.

In the multiplatforms world, there are a few ways you can prevent sillyness like 14-billion-row-queries.

The simplest is simply limiting the number of queries that are returned.  In the Admin client, open up the Folder, choose the permissions tab, and fill in the 'Max Hits' box with an appropriate number.

The next easiest is by inserting a default date range that you think will satisfy MOST user requests, without hitting your CPU too hard.  In the Admin Client, open up the Folder, go to the 'Field Information' tab, select the date field you've chosen as your segment date (check the AG definition if you're not sure) and put in some defaults.  You can use 't' for 'today' and t-90 to signify '90 days ago'.

The complicated one is writing a query restriction.  In the Admin Client, open up the group that the users belong to, and under the 'Application Group Permissions' tab, choose an Application Group from the 'selected' box, and write some SQL that limits people's access based on any database field or condition that you can access in SQL.  You can use the query restriction field to separate people out by offices (branch = 123) or to specific geographies (zipcode between 12345 and 54321), etc.

Hope this helps, have a great holiday!

-JD.

[Michael Prouse]

Thanks.  I didn't intend to imply that we had 14 billion documents stored to the same application/folder. They are spread over approximately 1200 applications (report IDs) and a user would never be searching all documents at one time. However, we may have hundreds of users searching many different applications/folders at the same time.

We make use of most of the suggestions you presented. We have the max hits set to 200; however, the SQL query still retrieves all matches - it only displays the first 200 to the user. Since it still executes the full query and finds all matches before presenting the hitlist back to the user, it still takes the same amount of time. The default date range is set to 6 months, which is the requirement for most of our customer areas, but some areas load tens of millions of documents daily or weekly. We also utilize query restrictions for some of our customer areas; for example, some users are restricted to viewing only documents for certain divisions/cost centers.

Unfortunately, we have found that - if a user simply enters a wildcard and searches, without better qualifying their search criteria - it can take an unreasonable amount of time and can 'lock up' the user to the extent they have to cancel their CICS or Windows session. If we have dozens of users performing wildcard searches at the same time, this wouldn't be a great scenario for our CPU or DB2 utilization.

We've asked IBM for their input but I just wanted to seek advice/suggestions from the user's group as well...

[Justin Derrick]

I was under the impression that the newer versions of CMOD with DB2 used 'fetch first X rows only' and used this clause inside the optimizer to minimize the impact of large queries.  I'd try and investigate why CMOD isn't behaving this way on your system.

Since you're already using all of my other recommendations, I hope IBM can find/create a solution that fits your needs!

Take care...

-JD.

[Bill Dennis]

I had noticed this same problem at my last employer. The MAX HITS limit was only limiting the display in the folder and not the query. We started adding a query restriction of "FETCH FIRST 200 ROWS" until a maintenance fix came out (early 2008?) for enforcing the limit on the DB2 query. Sorry I don't have the particulars anymore.