Author Topic: Too many tables in Appl. group  (Read 2714 times)

ibmarthin

  • Jr. Member
  • **
  • Posts: 68
    • View Profile
Too many tables in Appl. group
« on: September 26, 2018, 02:15:27 AM »
Hi

We suddenly ran into a problem with too many tables for one of the Appl. Groups. After table number 260 the SELECT statements became too long.

So question is: Has anyone expierenced this?
Also: can I concatenate all the 260 tables into one larger table BAA261 - close the table BAA260 and create an new Application group with a larger row number - and then modify the ARSSEG table to use only the new tables -and how.?

Or is there a better way to do this to avoid modifing ARSSEG?

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 2228
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
Re: Too many tables in Appl. group
« Reply #1 on: September 26, 2018, 05:03:36 AM »
I've run into this before, and I've developed a utility to do all the work for you.  Send me a private message for more info.  You can also tweak the DB2 statement heap size to keep the queries running temporarily -- although this is a temporary patch that will make things worse if you don't fix the root cause of the problem.

Also, you'll want to increase the default table size in the AG config under 'Advanced'.  There's an article on the Wiki about optimizing IBM CMOD Application Group table size:  https://cmod.wiki/index.php?title=Table_Segmentation_in_Content_Manager_OnDemand  Doing this will also likely result in an increase in performance in the future.  (That's the other reason I developed the utility to join tables.)

-JD.
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

Greg Ira

  • Full Member
  • ***
  • Posts: 240
    • View Profile
Re: Too many tables in Appl. group
« Reply #2 on: September 26, 2018, 05:12:26 AM »
We've run into this as well and developed our own process to join tables.  One possible quick fix to keep things running until you correct the issue is to force users to use the field you designated as your segment field so your queries don't use all 260 segments.

ibmarthin

  • Jr. Member
  • **
  • Posts: 68
    • View Profile
Re: Too many tables in Appl. group
« Reply #3 on: September 26, 2018, 05:58:34 AM »
Thank you both.
While fixing the problem, the use of the Segment field seems work now

ibmarthin

  • Jr. Member
  • **
  • Posts: 68
    • View Profile
Re: Too many tables in Appl. group
« Reply #4 on: October 10, 2018, 12:53:28 AM »
The IBM Lab suggested to use this in ars.cfg:
ARS_DB_MAX_SQL_TABLES=250
This is for now the solution . After this - and making a new much larger table - we don't get errors on searches caused by the large SQL statement.

scottnys

  • Jr. Member
  • **
  • Posts: 38
    • View Profile
Re: Too many tables in Appl. group
« Reply #5 on: October 16, 2018, 01:57:07 PM »
As Greg had said, we have collapsed tables.  Can give you details if needed.  Something else we did was to generate a report of how frequently Application Group Segments we being created by CMOD.  Creating too many active will cause this problem as well.  Throwing multiple Application Groups in one folder speeds up the process too.
Sizing the "Maximum Rows" properly goes a long way.  Our general rule of thumb is a Segment per Year's worth of index records.  This also depends on how long you are retaining them.  That way, proper growth is manageable.

fyi:

003400   SELECT
003500       SUBSTR(NAME,1,10)       AS AG_NAME      ,
003600       SEG.AGID  ,
003800       TABLE_NAME,
003900       DATE(START_DT) AS START_DATE,
004000       DATE(CLOSED_DT) AS CLOSED_DATE,
004100       DAYS(CLOSED_DT) - DAYS(START_DT) AS DAYS_TERM
004200   FROM OND03P.ARSSEG SEG, OND03P.ARSAG  AG
004300   WHERE
004400       SEG.AGID = AG.AGID AND
004500       (DAYS(CLOSED_DT) - DAYS(START_DT)) < 200 AND
004600       DATE(CLOSED_DT) > DATE(DAYS(CURRENT DATE) - 90)
004610   ORDER BY AG_NAME, TABLE_NAME;