you know a possibility to change the segment field in the application group?

[ewirtz]

Hi,
we want to optimze searching in production. Sometimes the segment field is bad. We want to change such a segmentation field. Do you know how to change it. (maybe some tricky SQL)

Egon

[Alessandro Perucchi]

Hello Egon,

Just to be sure I understand correctly. You have an application group with a field which is set with the "Segment field".
After some test of optimization you've seen that you wanted to remove this "Segment field" from this field?


If it is, then you better not touch it, even with some tricky SQL. Why? Because CMOD use a lot of logic behind to track the segments, the date of expiration, etc...
You might find that in changing this field to "Index" it would help in your searching speed.
Maybe you want that OnDemand stop creating new segment each 10'000'000 rows, then in that case you'll need to change the value in the application group.

And if you really want to do it... then the only option I can advise you, would be to extract and reimport all documents in this application group to another one.


If it not what you want, could you clarify you question please?

Cheers,
Alessandro

[Justin Derrick]

I'll also make a request for more information.

What do you mean by 'Sometimes the segment field is bad'?  Is the incoming data incorrect?  Is the query for that data causing problems?
What exactly do you want to change, and what are you hoping to achieve by it?

-JD.

[ewirtz]

Hi Justin,

I have added some details before. But I cannot see them in the post chain. So I post it again.

Sometimes a technical date ist taken for segmentation. If you are searching for a business date two segments have to be searched because the end of the year (business date) is stored in the next year (technical date). So it would be fine to switch from technical date to business date in segmentation.

with kind regards

Egon

[Alessandro Perucchi]

Hi Egon,

Well... the way it is, I would not recommend to change the "Segment Field" directly in the database. The SQL to change would be trivial, but then you have a problem with all the segment already in place and old data, all the parameters in Application Group, Segment, arsload tables... which all are dependend on the field which was selected at the beginning.
And probably you'll have other strange behaviour like in the cache, and maybe lots of other things I don't have yet in mind...

For me the most clean and secure way to really change a "Segment field" would be to export all your data, and reimport to a new Application Group with the correct data model.
That could be a very long effort depending on how much data you have stored already and in which archiving storage the data are stored (like tapes) for this export-import.

Cheers,
Alessandro

[Justin Derrick]

Hrm.  If I understand correctly, you have ONE segment field, and it can contain EITHER the 'business' date, or the 'technical' date, which makes searching for a particular document difficult.

If I understand your problem correctly, the best recommendation that the two dates be stored separately, so that a user can search on the field of their choice (either 'business' or 'technical' date).

If that's not possible, I'd increase the range of the default query to cover a range of dates that would catch BOTH dates, or require that an additional field (something that is 'mostly unique') be queried at the same time, and add an index to that field.

Of course, this means you're doing MORE work, not LESS work (like you originally asked).

If this can't be solved simply, I'd start looking carefully at ways to optimize your database performance to make the queries move a little more quickly.

[ewirtz]

Hi Alessandro,
Hi Justin,

if you want to see the the reports of one fiscal year, you have to look in two years, if the search and segment field is a technical date.

It would be better to search for the fiscal year itself. This implies to look in all segments (even if it is indexed), if the technical date is still the segment field.

But if you both state that the segment field shall not be touched I think it's just a topic lessns learned.

Cheers

Egon

[Alessandro Perucchi]

Hi Egon,

But if you both state that the segment field shall not be touched I think it's just a topic lessns learned.

Sadly... yes it's a lessons learned...

What you could do, is to store all the new documents from this year in a new Application Group, and migrate slowly your old data to the new structure piece by piece.
It will take time, and will be nearly transparent for your users.

Cheers,
Alessandro