Author Topic: Changing Application Group Field Indexes to Filters  (Read 1449 times)

tjspencer2

  • Jr. Member
  • **
  • Posts: 80
    • View Profile
Changing Application Group Field Indexes to Filters
« on: October 20, 2020, 08:33:30 AM »
I have a 9.5.0.4 CMOD OnDemand MP solution running on AIX 7.2 and DB2 10.5, and TSM 7.1 that takes FOREVER (many hours) for me to load a 300MB load file of statements.

As a comparison, I have many other AGs to which I can load the same size files in a fraction (less than an hour) of the time.

What I have found is my AG developer created an inordinate amount of indexes (10), and I think this is the problem with load performance.

All the other AGs that load in a fraction of the time include at most 3 indexes.

Onto my question - Can I just update my AG to change the fields that are Indexes to Filters?

How does DB2 handle this when I drop effectively about 7 indexes?

Thanks so much!!


« Last Edit: October 20, 2020, 08:36:22 AM by tjspencer2 »

Mehmet S Yersel

  • Jr. Member
  • **
  • Posts: 57
    • View Profile
    • LinkedIn Profile
Re: Changing Application Group Field Indexes to Filters
« Reply #1 on: October 20, 2020, 09:01:56 AM »
From the manual "Indexes provide fast and direct database access, but require more time to create and maintain." which you have already identified as the culprit because you have 10 indexes.

Also from the manual, " [A filter] is not used to identify a document or the field is always used with an index field to refine the results of a query. A filter causes a sequential search of the database."

So, there will be a sequential search at retrieval time each time any of the filter fields is provided as input, thus elongating the retrieval times. Personally, I would not remove all 7 indexes all at once without observing the impact on online/real-time user experience. If retrievals are going to be impacted to a degree not acceptable to users, maybe dropping 1 index at a time and observing for a period of few days would ensure you don't rock the boat for users.
« Last Edit: October 20, 2020, 09:05:53 AM by myersel »
#zOS #Multiplatforms
#DB2 #OAM
#AFP #RiCOH AFP2PDF #SnowBound
#Finance #Telecom #Airlines
#ICN #IHS #WAS ND #Cert and Key Management
#Migrations #Data Modeling #RACF-2-CMOD Synch
#FileTek AMMO #ABI #RMDS #RADAR

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 2229
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
Re: Changing Application Group Field Indexes to Filters
« Reply #2 on: October 20, 2020, 10:21:04 AM »
IBM CMOD Professional Services: http://TenaciousConsulting.com
Call:  +1-866-533-7742  or  eMail:  jd@justinderrick.com
IBM CMOD Wiki:  https://CMOD.wiki/
FREE IBM CMOD Education & Webinars:  https://CMOD.Training/

Interests: #AIX #Linux #Multiplatforms #DB2 #TSM #SP #Performance #Security #Audits #Customizing #Availability #HA #DR

tjspencer2

  • Jr. Member
  • **
  • Posts: 80
    • View Profile
Re: Changing Application Group Field Indexes to Filters
« Reply #3 on: November 08, 2020, 08:24:04 AM »
Great link - thanks for sharing Justin!!!