Delay during unload in 8.5

[Lars Bencze]

Hi all,

I was wondering if anyone out there with a deeper knowledge of the nuts and bolts inside / behind the curtains of the arsadmin Unload command could inform me on a matter.

We have a script which is run regularly to unload some data from LoadIds which we do not have the exact date range for. Basically the command looks like this:

> arsadmin unload -h ARCHIVE -g XYZ2 -u Username -p password -Q -L 4527-13-0-179721FAA-0-0

Even though the AppGroup contains a lot of documents, even small loads (LoadIds with maybe 100 docs in them) render a 6 minute delay.
This happens only in those Application Groups where we have Enhanced Retention Management activated. In other Application Groups with a similar amount of documents, the Unload is without delay.
DO NOTE HOWEVER that all Holds have been successfully removed BEFORE the command above is run. Even for large batches, this Hold removal (arsdoc hold_release ...) takes no more than 2-4 seconds. The problem does not lie in the removal of the holds, neither named or implied.

This snippet below is taken from the System Log. Please let me know if that Msg 65 that looks truncated and the Msg 283 is normal. I cannot find those when I do unload in version 9.0 on Windows, but this is an AIX-based CMOD system 8.5.0.9 with a DB2 10.5 database.

09:30:38  30  Login: ondemand.xyzcorp.net 10.111.192.11 non-SSL (AIX) (ARSADMIN) (8.5.0.9)
Note the 423 second delay before the next message.
09:37:41  65  Application Group Query: Name(XYZ2) Agid(4527) Sql(1 <-- Note that this message looks truncated
09:37:41 283 Application Group Check Unload: ApplGroupName(XYZ2) Agid(4527) Aid(4530) LoadId(4527-13-0-179721FAA-16245-16245)  Total Load Rows(101)  Total Queried Rows(101)  Total Held(0)  Hold(0)  Non-Implied(0)  Implied(0)  CFS-OD RM(0)  CFS-OD Fed(0)  Pct M
09:37:44  85  Application Group Unload Storage Manager: Name(XYZ2) Agid(4527) NodeName(EKSTG) Nid(13) Server(-LOCAL-) LoadId(179721FAA) Objects Deleted(2)
09:37:44  85  Application Group Unload Storage Manager: Name(XYZ2) Agid(4527) NodeName(-CACHE-) Nid(0) Server(-LOCAL-) LoadId(179721FAA) Objects Deleted(0)
09:37:44  84  Application Group Unload Database: Name(XYZ2) Agid(4527) LoadId(179721FAA-16245-16245) Rows Deleted(101) SM UnLoad Ready(1)

I am unable to find this message "283" in any V9.0 system i Search. Are they actually related to CFSOD? (We do not have CFSOD installed)
I suspect the delay is caused by either the "truncated" MsgNum 65, or by the (unnecessary?) check unload MsgNum 283.
Can I add an index to some table and field to speed this procedure up? If so, which ones?

By the way, the DB2 seems to be well tuned, it does not appear to suffer from any memory allocation storms or similar resource problems. It merely sat there during these delays.

I'd be grateful for any knowledge you may wish to share with me and other CMOD Admins.

All the best,

Lars

PS: MsgNum 283 also occurs during arsmaint.

[Justin Derrick]

Hi Lars.

The delay is likely caused by a huge arsload table (this one App Group has almost 200k loads), and the fact that you don't have date ranges to specify on the command line.  One of the ways that CMOD accelerates queries in its internal tables (just like metadata tables) is by building an index based on min/max date ranges.

You could build custom indexes on the arsload table, but things like the combination of AGID and Object Name will result in enormous indexes and gobble up a ton of space.  You'd have to get some snapshots of the database to determine which queries are running longest, and try to determine which fields (or combination of fields) to index to improve performance.

The only good news I have for you is that there are new indexes on these tables in CMOD v9.5+, which might alleviate your performance issue.

-JD.

[Lars Bencze]

Thank you Justin!

[Lars Bencze]

A little update.
There are some 280 million rows in the ARSHOLDMAP table as well.

When customer has "Use ERM?" set to "Yes", the time to Unload one Load ID takes about 3 minutes.
When customer turns this setting off (to "No") and continues to Unload from the same Application, each unload takes about 3 SECONDS.
A few samples from the System Log:
...
17-02-09 11:00:29 84 Name(APPGR2) Agid(6715) LoadId(5FAA-16870-16870) Rows Deleted(0) SM UnLoad Ready(1)
17-02-09 11:03:33 84 Name(APPGR2) Agid(6715) LoadId(6FAA-16926-16926) Rows Deleted(49) SM UnLoad Ready(1)
17-02-09 11:10:29 84 Name(APPGR2) Agid(6715) LoadId(7FAA-16926-16926) Rows Deleted(1) SM UnLoad Ready(1)
("Use ERM?" for APPGR2 is switched from "Yes" to "No" here)
17-02-09 11:11:51 84 Name(APPGR2) Agid(6715) LoadId(8FAA-16926-16926) Rows Deleted(1) SM UnLoad Ready(1)
17-02-09 11:11:52 84 Name(APPGR2) Agid(6715) LoadId(9FAA-16926-16926) Rows Deleted(37) SM UnLoad Ready(1)
17-02-09 11:11:54 84 Name(APPGR2) Agid(6715) LoadId(10FAA-16926-16926) Rows Deleted(33) SM UnLoad Ready(1)
...

It seems like the "Use ERM?" = No switch bypasses a whole lot of processing, and that the slowness to delete is not due to the number of Loads itself, but rather to the high number of LoadIDs in the ARSHOLDMAP table. That table is however indexed over 8 of its 10 columns, so how can it get any better?
Will try to run some statistics and automatic performance analysis on the DB to see if it recommends any new indices/indexes.