DBIF_RSQL_SQL_ERROR" with exception CX_SY_OPEN_SQL_DBC

[Former Member]

Hi all,

We are uploading data from 0CO_OM_CCA_9 from R/3 to ODS & subsequently to cube. Upload till ODS is fine but when we are trying to push the data from ODS to Cube we are facing an error. We have 20 Million data records. Each data package contains 10000 records, so there are 200+ packages. Now while updating to the cube some of the packages are unable to generate update mesages from the cube. so some of the packages are remaining in yellow states whereas the following packages are turning green. And ultimately those packages are throwing a runtime error. When we check the log we found out DBIF_RSQL_SQL_ERROR" with exception CX_SY_OPEN_SQL_DBC.

The log states:

An exception occurred that is explained in detail below.

The exception, which is assigned to class 'CX_SY_OPEN_SQL_DB', was not ca

in

procedure "WRITE_ICFACT" "(FORM)", nor was it propagated by a RAISING cla

Since the caller of the procedure could not have anticipated that the

exception would occur, the current program is terminated.

The reason for the exception is:

The database system recognized that your last operation on the database

would have led to a deadlock.

Therefore, your transaction was rolled back.

We tried with reducing the data record per packet size but it didnt help. Would really appreciate any solution.

Thanks,

Abhishek

[former_member197993 · Accepted solution]

Hello Abhishek,

These deadlocks dumps occur during parallel insert operations because ORACLE

does not support a blocking concept for BITMAP indexes at data record level.

To avoid a deadlock you have two possiblities:

1) delete secondary indexes on the fact tables and recreate them

after having loaded the data

2) alternatively, you can also load requests/or data package serially

into InfoCubes.

There is no possibility to ensure that no deadlock occurs if concurrent inserts

happens into a database table where "BITMAP" Indexes exist.

( There is no row level lock in BITMAP indexes )

To be sure you MUST drop the secondary indexes before loading and rebuild

the indexes after loading!

There is no performance problem at all if you frequently compress requests

and the f-fact table contains only small amounts of data.

This problem exists since the beginning of BW and can only be solved by either

avoiding parallel processing of packages or the deletion of the BITMAP indexes

before loading.

I refer you to the following SAP notes:

631668 - DEADLOCK when loading data into InfoCube

Please ensure INITTRANS at DB level is set to the appropriate values.

Also:

84348 - Oracle deadlocks, ORA-00060

750033 - INITRANS parameter for InfoCube secondary indexes

FYI see also:

1013912 - FAQ: Oracle BW performance)

561961 - Switching the use of the fact table view on/off

Best Regards

Barry

[Former Member]

Thank You..The issue is resolved now..

Abhishek

[former_member209032]

Hi,

This is a common error. You have not dropped the index of the cube. Drop the index of the cube and then load the data to cube and recreate the index.

Regards,

Raghavendra.