OnDemand User Group
Support Forums => z/OS Server => Topic started by: ibmarthin 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?
-
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.
-
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.
-
Thank you both.
While fixing the problem, the use of the Segment field seems work now
-
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.
-
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;