Chore Commit

[jim wood]

Guys,

I always thought that TM1 data was committed upon exiting the tab. I have a situation where I am trying to stop more than one data load chore running at the same time. I have a chore status flag in a parameters cube. I have a start and end process. The start process checks to see if the flag is 1. If not it writes 1 to the cube if it is, it executes ChoreQuit. The end process then writes 0 to the parameters cube. If I run the start process on it's own and try to run a chore containing the start process then the chore doesn't run, but if I run a chore with the start process in and then try to run another chore with the start process in the second chore doesn't recognise that the first chore has written to the parameters cube. It doesn't look like the 1 is committed to the the status cube until after the chore has finished. Is this right? If so how has anybody else been able to stop 2 chores from running at the same time?

I know, I know I hate chores but I'm being pushed in to a corner on this one,

Jim.

[PlanningDev]

I had this same issue when working in 9.5.2 on an automation process for parallel loading our daily updated cubes. Essentially the data isn't committed till the end of all processes in the chore so what happens is that your 1st chore has written to a parallel copy and your second chore is reading the base data.

I didn't find a way around this using data but it may be possible to use a dimenion and to search for the existence of a member in a dimension. This opens up the DimensionElementInsertDirect functions which may allow you to get around the changes being held in a private copy.

[jim wood]

We wanted to avoid this as the meta change locks the service while it is running and the data loads chores are run during the day. A good shout though, thanks.

[mvaspal]

Hi,

I also think that TIs are commited only at the end of the chore. The data that you change in the first TI is visible from within the chore for the remaining TIs but is invisible for others outside of the chore.

If you are on 10.1, you can set single commit mode for the chore which means each TI is committed after it finished running.

Another option could be to use a SQL table instead of a parameter cube as SQL statements are committed immediately.

[PlanningDev]

Or you could check a value from a flat file

[jim wood]

That's what we are looking at now. Thanks guys.

[Alan Kirk]

I think PlanningDev's idea is probably the best one.

I always thought that TM1 data was committed upon exiting the tab.

I know that there's usually some guesswork in how chores and processes (and rules, and API functions, and...) operate due to some deficiencies {cheeeerrrkkhh} in the documentation, but it's interesting that the recent IBM Cognos TM1: The Official Guide book had this to say:

Chore Transaction
You can execute multiple TurboIntegrator processes in a chore (see also Chapter 7). The processes are executed in the defined sequence. A chore can also execute the same process multiple times (optional with different parameters). An example is shown in the following illustration. A chore can be scheduled to run frequently. If the chore runs on a scheduled event, it will run in its own thread.

All operations of all TurboIntegrator processes executed by the same chore are embedded in only one transaction. The modifications that are made are committed when the last process terminates. In Cognos TM1 version 10.1 or higher, there is an option to commit each TurboIntegrator process in a chore. In this case, locks from each TurboIntegrator process are not held for the duration of the chore but you get the benefit of sequentially executed processes. In the following illustration, you can see the two options “Single Commit Mode” and “Multiple Commit Mode.” “Single Commit Mode” is the “old version” of the Transaction mode. If you choose “Multiple Commit Mode,” each TurboIntegrator process of the chore commits all modifications and releases all locks.

(Kindle Locations 6161-6172, emphasis added.)

[jim wood]

We tested the flat file approach and it worked. We amended the start process so that did a file exist check first. If the file exists then ChoreQuit else using Text Output the process creates the file. The stop process then exports a batch file which is then executed to remove the file,

Jim.

[KMG]

Hi Guys,
I'm new at this Forum and I don't know whether I need to reply to this thread or create a new one.
I have an issue/question with the Chore Commit as well, if this does not belong here please let me know and I'll move it.

Context:
We need to update the metadata and data for 8 independent cubes on daily basis.

Current approach:
Logically I need to first refresh all the metadata update processes (with rejected logic as well) and then run the data ones. I have one chore than runs all TI's related to metadata refresh and, at the very end of the processes there an 'update flag' TI that insert 1 in an assumptions cube.
I've created 8 independent chores with 3 processes each that run every 5 minutes: the first one checks on that flag (<>1 ChoreQuit, else continue), the second one runs a wrapper for all data refresh (this is one TI with 15 ExecuteProcess lines each) and finally the third one resets the flag to 0. Notice that each Chore takes approx 120/180 minutes each (yes... I know.. 10 GB of data refresh on daily basis sounds unreasonable, but requirements are such at the moment), so there's no way that one chore can start and finish (therefore, reseting the flag to 0) in less than the timeframe of each Chore (5 minutes). Also, the TI's that check for the flag are the same ones for all Chores.

Problem/oddity:
At 3 a.m. the metadata Chore runs, it takes 90 minutes to run, at the end it successfully turns the flag to 1. All of the 8 Chores start (within a 5 minutes range), they check the flag (first process), then it runs the corresponding wrapper (second process) and finally they reset the flag (third one).
The real issue or bizarre behavior, is that while a Chore is running the second process (is not one in particular as it varies every day), this second process gets called again and starts over (I checked it in the log file). This happens even with the flag set to 0 (because other/s Chores finished and reset it). So, to understand the oddity, the process get called twice, but only ends once. All of these 8 processes have Multiple Commit mode. My understanding of this functionality is that objects get unlock after each individual TI of the Chore finishes, as opposed to the Single commit which has to wait until the last TI finishes so that locks from the first one get free.

Question:
Is multiple commit also like multithread? It respects the sequence that is specified in the Chore or it runs them separated? Since the TI is the same and the flag is set up in one same cell. Could it be possible that the flag check gets on hold by other Chore hence making a second Chore start with the second TI instead of the first one?

I've read http://www.tm1forum.com/viewtopic.php?f=3&t=9631 and at the very end it gives an answer from IBM saying that:

The issue happens in 10.1 RP1 because in multi-commit mode a cache is discarded on one of the 'commits' and then subsequent cell references precipitate a very deep and complex evaluation. This means that it was taking (in 10.1 RP1) about 1 minute to compute the value for one cell. The chore is not "looping" but rather is very busy.

Here's the interesting piece of the log:

Code: Select all

9276   []   INFO   2014-04-22 09:20:03.058   TM1.Chore   Chore "DAILY_RELOAD_PNL_STAGING" executed by scheduler
9276   []   INFO   2014-04-22 09:20:03.058   TM1.Process   Process HQPS_DDW_UPLOAD_CHECK_FLAG executed by chore DAILY_RELOAD_PNL_STAGING
9276   []   ERROR   2014-04-22 09:20:03.074   TM1.Process   Process "HQPS_DDW_UPLOAD_CHECK_FLAG": : Execution was aborted by ChoreQuit() Function.
9276   []   INFO   2014-04-22 09:20:03.074   TM1.Chore   Chore "DAILY_RELOAD_PNL_STAGING" time = 0.02 seconds
9276   []   INFO   2014-04-22 09:20:03.074   TM1.Chore   Chore "DAILY_RELOAD_PNL_STAGING" finished executing
9284   []   INFO   2014-04-22 09:20:05.102   TM1.Chore   Chore "DAILY_RELOAD_PNL_SUMMARY" executed by scheduler
9284   []   INFO   2014-04-22 09:20:05.102   TM1.Process   Process HQPS_DDW_UPLOAD_CHECK_FLAG executed by chore DAILY_RELOAD_PNL_SUMMARY
9284   []   ERROR   2014-04-22 09:20:05.102   TM1.Process   Process "HQPS_DDW_UPLOAD_CHECK_FLAG": : Execution was aborted by ChoreQuit() Function.
9284   []   INFO   2014-04-22 09:20:05.102   TM1.Chore   Chore "DAILY_RELOAD_PNL_SUMMARY" time = 0.00 seconds
9284   []   INFO   2014-04-22 09:20:05.102   TM1.Chore   Chore "DAILY_RELOAD_PNL_SUMMARY" finished executing
9300   []   INFO   2014-04-22 09:22:50.522   TM1.Process   Process "DDW_UPLOAD_PNL_SUM_ReconcileRGMtoHFM_ACTatBENCH_USD":  finished executing normally, elapsed time 1303.32 seconds
9300   []   INFO   2014-04-22 09:22:50.584   TM1.Process   Process "DDW_CALC_SLS_SUM_GR_VARIANCES" run from process "DDW_UPLOAD_RGM_SLS_SUM" by user "R*DAILY_RELOAD_SALES_SUMMARY"
9300   []   INFO   2014-04-22 09:23:05.592   TM1.Process   Process "DDW_CALC_SLS_SUM_GR_VARIANCES":  finished executing normally, elapsed time 15.01 seconds
9300   []   INFO   2014-04-22 09:23:05.592   TM1.Process   Process "DDW_CALC_SLS_SUM_ASP_CEU_EQU_UNITS" run from process "DDW_UPLOAD_RGM_SLS_SUM" by user "R*DAILY_RELOAD_SALES_SUMMARY"
9296   []   INFO   2014-04-22 09:23:05.655   TM1.Process   Process DDW_UPLOAD_RGM_SLS_STG executed by chore DAILY_RELOAD_SALES_STAGING
9296   []   INFO   2014-04-22 09:23:05.655   TM1.Process   Process "DDW_UPLOAD_SLS_STG_ACTatACT_NON_USD" run from process "DDW_UPLOAD_RGM_SLS_STG" by user "R*DAILY_RELOAD_SALES_STAGING"
9300   []   INFO   2014-04-22 09:23:05.670   TM1.Process   Process DDW_UPLOAD_RGM_SLS_SUM executed by chore DAILY_RELOAD_SALES_SUMMARY
9300   []   INFO   2014-04-22 09:23:05.670   TM1.Process   Process "DDW_UPLOAD_SLS_SUM_EXPORT_USD_ADJ_2" run from process "DDW_UPLOAD_RGM_SLS_SUM" by user "R*DAILY_RELOAD_SALES_SUMMARY"
9288   []   INFO   2014-04-22 09:23:05.670   TM1.Process   Process "HQPS_ACTUALS_CUBESAVEDATA":  finished executing normally, elapsed time 2416.80 seconds
9288   []   ERROR   2014-04-22 09:23:05.670   TM1.Process   Process "DDW_UPLOAD_HFM_PPL_STG":  finished executing with errors. Error file: <TM1ProcessError_20140422084248_DDW_UPLOAD_HFM_PPL_STG.log>
9288   []   INFO   2014-04-22 09:23:05.670   TM1.Process   Process HQPS_DDW_UPLOAD_RESET_FLAG executed by chore DAILY_RELOAD_PPL_STAGING
9288   []   INFO   2014-04-22 09:23:05.811   TM1.Process   Process "HQPS_DDW_UPLOAD_RESET_FLAG":  finished executing normally, elapsed time 0.13 seconds
9288   []   INFO   2014-04-22 09:23:05.811   TM1.Chore   Chore "DAILY_RELOAD_PPL_STAGING" time = 6059.73 seconds
9288   []   INFO   2014-04-22 09:23:05.811   TM1.Chore   Chore "DAILY_RELOAD_PPL_STAGING" finished executing
9300   []   INFO   2014-04-22 09:23:05.826   TM1.Process   Process "DDW_UPLOAD_SLS_SUM_EXPORT_USD_ADJ_2":  finished executing normally, elapsed time 0.16 seconds
9300   []   INFO   2014-04-22 09:23:05.826   TM1.Process   Process "DDW_UPLOAD_SLS_SUM_ACTatACT_NON_USD" run from process "DDW_UPLOAD_RGM_SLS_SUM" by user "R*DAILY_RELOAD_SALES_SUMMARY" 

*** MY COMMENTS ***
Notice that the first 3 lines verify the flag, they find it turned off and the Chore ends.
In line number 12 you can see that there's a process called DDW_CALC_SLS_SUM_GR_VARIANCES that gets called from the wrapper 'DDW_UPLOAD_RGM_SLS_SUM' (Second TI in the Chore)
after this aboved mentioned TI there should be 4 more pending to be executed. However on line 17 you will see that the wrapper gets called again without being finished.
It says that it gets called by the Chore, however the Chore should first check the Flag by using the generic process and then move to the DDW_UPLOAD_RGM_SLS_SUM wrapper.
This is why I say that the process start once but end only once.

I hope I'm being clear enough on this as it's a bit tricky to explain.

Thanks in advance to all.

Nicolás

[TableManagerOne]

Is multiple commit also like multithread? It respects the sequence that is specified in the Chore or it runs them separated?

Multiple commit chores will respect the sequence you have defined. If your chore is composed of processes 1, 2, and 3, then 2 will only run after 1 completes. Likewise 3 will only run after 2 (and 1) has completed. It won't multi-thread things so that 1, 2, and 3 might be running simultaneously. This is still left as a manual exercise using ExecuteProcess.

My understanding of this functionality is that objects get unlock after each individual TI of the Chore finishes

Yes. But, another consequence of this is that the "rollback point" will be reset. When TM1 encounters a lock conflict/deadlock, it will rollback the transaction, possibly wait, and retry. In single commit mode, the transaction includes processes 1, 2, and 3. If rollback should occur anywhere in your chore, it would reset to "before 1" and retry. With multiple commit chores, 1 would be a transaction, 2 would be a transaction, and 3 would be a transaction. If a lock conflict/deadlock were encountered during the execution of, for example, 2, the "2 transaction" would rollback, and retry at "before 2". Process 1 would not be re-executed, but 2 would be.

You could enable the following logger to check if rollback is occurring somewhere:

Code: Select all

log4j.logger.TM1.Commit=DEBUG

Also, I might suggest watching (or more likely, logging) TM1Top output to see if any threads are rolling back and/or hanging around waiting.

[KMG]

Thanks for the comment!

Yesterday I set everything to single commit and, what you are saying about the rollback, is exactly what happened.
When the second TI was about to end it encountered a lock and the whole Chore was reset. Since the flag was turned off by one of the other Chores that previously finished, the 2nd process (the wrapper one) was never fired again and, therefore, the load was incomplete.

As I see it there are two ways around this:
Either I spend some time evaluating what causes the lock and intend to avoid it, or I get rid of the wrapper process and just input all the 15 TI's involved in it as part of the Chore.
By doing that each time it encounters a lock, it will only re-start that single process instead of the whole wrapper.

I do have an outstanding question though: how much time it waits for an object to get unlocked? The only common objects are processes and the cell that has the flag, but is only for reading purposes.

Thanks! I'll let you know how this ends up.

nico

[jim wood]

Have you tried using TM1runTI batch files? You can then execute them via a chore instead of the processes. I'm not sure if this will help but it'll take more behaviour away from chores. As you've probably guessed from reading above I have a healthy dislike of chores, enhanced further by issues like this one.