new load with a date for an index table that is already closed

[ewirtz]

Hi,

i'm just thinking about segment field usage.

If a business date is used as a segment field, the following could happen:

- a segment is closed for the date intervall between 2007 and 2010
- you have a current segment 2011-2011
- you get a new report in 2011 for 2007 (some correction for the past)

What is the influence of such a load?

Will we have the following segements afterwards?

- 2007-2010
- 2007-2011

If this is true, the segmentation would not help at all for such kind of business segment fields.

Does anybody know the details?

[Alessandro Perucchi]

Hi,

Documents are only loaded into the active segment, in your case 2011.
Meaning, that you will have the following case:

Segment 1    2007
Segment 2    2008
Segment 3    2009
Segment 4    2010
Segment 5    2007 + 2011

I use normally the Segment ID with the archiving date and not the business date.

And for the expiration, I prefer to use Expiration by Load and not by Segment.

Concerning the performance of selecting on several tables...
well for the moment it was never a big issue, at least for the moment.

Well what you could do (but that is not nice...) would be, close 2011, do your load correction on the new table, close the new table, and the load of 2011 will go in a new table:

Segment 1    2007 (1st Part)
Segment 2    2008
Segment 3    2009
Segment 4    2010
Segment 5    2011 (1st Part)
Segment 6    2007 (2nd Part)
Segment 7    2011 (2nd Part)

Not nice, but better from a performance point of view!

I hope somebody will have some better idea than me in that regard!

Cheers,
Alessandro

[Justin Derrick]

One minor correction to Alessandro's explanation:

Segment 5 will contain a 'minimum' date of 2007, and a 'maximum' date of 2011.

For the purpose of Alessandro's example, a query for data from 2009 will query two segments:  Segment 3 and Segment 5.

In fact for ANY query between 2007 and 2011, Segment 5 will ALWAYS be queried, even though it doesn't contain any information for 2008, 2009, and 2010.

This is a performance problem, especially as you get MANY segments like this, and an end user performs certain types of wildcard searches.

I always advise customers to load data in chronological order.  And *IF* there needs to be a historical load, create a duplicate Application Group for 'Historical' data only, then bind them together at the Folder level in CMOD.

Hope this helps.

-JD.

[Alessandro Perucchi]

Thanks for the correction :-)

[ewirtz]

Hi,

for me this implies that segmenting does not really help if reports can be delivered for the past. In general it makes no sense to use technical segmentation dates because business people will look for business relevant fields. They should not need not to know the time of the report creation.

A simple soulution of this performance issue is to avoid segmentation. By using DB2 partitioning we can reach the aim of segmentation with a better performance.


Egon

[Alessandro Perucchi]

Hi,

I have no experience with DB2 Database partitioning, but I have found that:

http://publib.boulder.ibm.com/infocenter/cmod/v8r5m0/topic/com.ibm.ondemand.installingmp.doc/ars1i07183.htm#wq110

And what I don't know, if it is possible to activate it afterwards.
Meaning if you can activate it in an application group, when you have already 10000000000 documents in it.

Somebody knows that?

Cheers,
Alessandro

[Justin Derrick]

Hi Egon...

Really, the best answer to this question will require more research.  Do your users frequently search for historical documents?  Which fields do they use to find these documents?  Is it just one or two fields, or is it a variety of fields?  What's your total monthly volume of documents (old and new) and how many docs are in the archive already?

While CMOD is designed more for chronological loads, there are a few things you can do (from inside the admin client, which are supported) to minimize the impact of your situation.

Expanding the number of rows per database table is the first.  This sounds counter-intuitive, but if combined with the right indexes, it can improve performance by reducing table joins disk I/O on indexes.

-JD.

[ewirtz]

Hi Justin,
you are right. A lot of tricks can be used to improve performance. I have a lot of experience in DB2 optimizing (Z/OS). If we have a business date that would imply overlapping intervals of segmentation I think DB2 patitioning is a simple solution for this issue. You could implement this in the 'Table space creation exit' which uses the OnDemand configuration to find a canonic mappng it to DB2.

- table split is deactivated
- segmantatu?on field is used for partitioning
- all indices with leading segmantation field will be partioned indexes.

I think this is a general soultion for this class of issues. Because of the mapping logic the tablespace creation is still controlled by the admin configuration. It's near 'out of the box'

cheers

Egon

[Justin Derrick]

I agree that your solution is more than feasible -- it uses existing features and functionality in DB2 to achieve what CMOD can't take advantage of automatically.

Unfortunately, your changed will likely create an unsupported configuration -- so you'll want to closely examine your management's tolerance for risk.

-JD.