Initiate and Fill BIA Index Step Fails in Process Chain

[Former Member]

HI!

In our weekend scenario for a cube, we refresh the data in a cube. What we do is, we delete BIA indexes before the data refresh in the cube, load the data, recreate the indexes using "Initialize and Fill BIA" step.

Last run the filling process failed and in the log it showed this message:

Lock for table 'table name'. Job will be restarted again later

We have it for many tables and at the end of the log:

FILLING_FAILED >CHECK LOG!!!!!

Please any advice in this?

Thanks,

Ghassan Matar

[mirko_alay]

Check OSS note 2051814 and apply if needed.

[Former Member]

Tansu,

We have the following points in our system:

1- This is the only BIA index create Step we have in the system.

2- No MDChange Run jobs was running at the same time. We ran the change run in a job as a predecessor to this job.

3- We have 20,000 + records in the table.

Actually, One time I was creating BIA index manually using RSDDV and I have similar scenario. the creation of the index succeeded and the filling failed. I ran RSDDV again for the Cube and choose Continue filling. did you face any similar issue before?

Thanks,

Ghassan Matar

[Former Member]

HI!

After the investigation, we found that the root cause of the lock is the ODS Activation. We have two ODS's activation jobs running at the same time we were initialize and fill BIA indexes. We tested that in Staging and it causes the lock as well.

We implemented the Note 1223239 by setting the following parameters:

WRITEINDEX = 180

WAITTIME = 60

This weekend, the ODS Activation step ended before the BIA Initialize and Fill step by 4 minutes. The step created the indexes for the cube tables but it didn't continue to fill any index and it failed.

The Log is here.

-

---

NEW INDEX: /BI0/SVTYPE RSD_TREX 100 S

NEW INDEX: /BI0/SSALESORG RSD_TREX 100 S

NEW INDEX: /BIC/SZREGION RSD_TREX 100 S

Event RSPROCESS with parameter 4L1CWG1AC7C479GIO3Z5A5GPH successfully triggered RSMPC 090 S

Job finished 00 517 S

-

---

Also, after implementing the note: 1223239 with the parameters above, THE ODS Activation step was running in parallel with BIA Initalize and Fill step. the lock occured and after the completion of ODS Activation step all the locked tables get unlocked and filled properly except for 5 tables. It seems to me like kind of dead lock between these 5 tables. they take all the remaining time of the step and at the end BIA Initalize and Fill fails.

If you will notice in our log, it is only 5 tables remaining and they are repeated every minute. At the end, the step failed.

---

03:59:26 Lock for table '/BIC/SSEGRULE'. Job will be restarted again later

03:59:26 Lock for table '/BIC/SNPM_YEAR'. Job will be restarted again later

03:59:26 Lock for table '/BIC/SNPM_ENDYR'. Job will be restarted again later

03:59:26 Lock for table '/BIC/SNPMPERCNT'. Job will be restarted again later

03:59:26 Lock for table '/BIC/SDOMRULE'. Job will be restarted again later

04:00:26 Lock for table '/BIC/SSEGRULE'. Job will be restarted again later

04:00:26 Lock for table '/BIC/SNPM_YEAR'. Job will be restarted again later

04:00:26 Lock for table '/BIC/SNPM_ENDYR'. Job will be restarted again later

04:00:26 Lock for table '/BIC/SNPMPERCNT'. Job will be restarted again later

04:00:26 Lock for table '/BIC/SDOMRULE'. Job will be restarted again later

04:01:26 Lock for table '/BIC/SSEGRULE'. Job will be restarted again later

04:01:26 Lock for table '/BIC/SNPM_YEAR'. Job will be restarted again later

04:01:26 Lock for table '/BIC/SNPM_ENDYR'. Job will be restarted again later

04:01:26 Lock for table '/BIC/SNPMPERCNT'. Job will be restarted again later

04:01:26 Lock for table '/BIC/SDOMRULE'. Job will be restarted again later

04:01:26 FILLING_FAILED >CHECK LOG!!!!!

04:01:27 Entire chain now has status 'R'

04:01:28 Process ABAP Program, variant Rebuild BIA Indexes - Daily Inventory - End of Pro has status Undefined (instance )

---

Please advise.

Thanks,

Ghassan Matar

[Former Member]

You need to run the BIA indexes sequentially. It looks like there is a MD change run or another BIA cube roll-up is running.

By the way how many records are in the RSDDTREXNEWSID table?

thanks

[Former Member]

Hi Ghassan

Assuming that there is another job updating a shared master data or a change run, or it could be timeout or invalid data problem etc. In the job log(SM37) you should be able to find more clues about the failed job - yes the BWA initial index jobs show green even if they fail-.

By the way how long does the reload take? If most of the time is taking during the master data load, I can give you a tip to reduce the time; create an infocube with all your BIG master data, only the big ones such as customer, vendor, billing documents etc. The cube doesn't have to have any fact data, not even one record. Load it to BWA. Next time when you delete a cube with a big MD from BWA, when you reload it, it won't take time to reload your huge BWA data becuase it's already loaded in the fake cube!

Also, when you refresh the cube I recommend you to do a re-org ( in a quiet time)

Cheers

Tansu

[Former Member]

Hi,

As per you said the issue is as below while creating /"Initialize and Fill BIA"

Last run the filling process failed and in the log it showed this message:

Lock for table 'table name'. Job will be restarted again later

The reason is lock is occured for the object . The locks may created during the below cases

1. Any Other job is running , which is included the same object

2. If any ACR is running at the same time for the same object / ACR got failed and locked the object, the other jobs will not proceed further as the object is locked already

3. The lack of RFC connection between BW server and BIA server. If there is any problem in RFC connection also the job will get fail

In this case we need check the jobs in SM37, need to find out whether there are any Multiple jobs for the same object / for same activity (ex:- ACR/DROP CUBE/DROP INDEXES / same object)

Need to check the locks in SM12

Check the T.code : RSDDBIAMON2 for monitor the BIA ( Her you can see the status of BIA indexes)

if the indexs are not create you need to drop the indexes , and "Initialize and Fill BIA"

You can fill this Index filling by using the T.CODE : RSDDV >>> Give the Data target Name > BIA indexes > Create indexes > continue > once it is droped indexes , it will ask for filling the indexes

>> Then continue for filling of BIA indexes , the job you can monitor in SM37

it may help you

Regards.

Rambabu

Edited by: Rambabu velanati on Mar 20, 2011 7:58 AM

[Former Member]

Thanks for the reply. Yes I agree that there is another job is blocking the table but is it necessary to be BIA Index filling process or it could be other process.

These are the points I have after my invistigation:

1- No create or filling of other BIA index in our process chains at the fail time

2- No Attribute Change run at this time

3- There is aggregate rollup for another Accelerated InfoCube run at this time as we are keeping both aggregates and Indexes at this early time of our implementation. This process use SAP_AGGREGATES_ACTIVATE_FILL ABAP

1- What are the examples of possible processes or jobs that can cause the lock for (S, X, Y) tables.

2- Are they necessary to be BIA Accelerator related jobs only?

Please advise

Thanks,

Ghassan Matar

[Vitaliy-R]

Well, shared characteristic tables (S, X, Y) are locked for reading during indexing, so if you get two competing jobs - one will fail after few attempts (usually during 30 minutes). So, first thing you need to make sure there are no two (or more) indexing jobs that require data from the same tables. Cheers.