Aggregate Rollup ends with SQL422 error in Sybase

[former_member563260]

Introduction

This blog post describes how to resolve when an aggregate rollup process ends with the below error in ASE release 15.7 SP131.

Error: SQL Error: [ASE Error SQL422] Too many nested expressions or logical operators...

Problem

I have faced this issue where the aggregate rollup process has failed but the aggregate indicator would be running on the Infocube. I could not find any active jobs for this process either and no compression has been done on this Infocube.

Resolution

I'll share the solution I have come up with.

1. Firstly, implement the last versions of those SAP Notes:
   
   
   
   1. -> SAP Note 1821924 - "SYB: SAP BW 7.40 Correction Collection"
   
   
   2. -> SAP Note 1965755 - "SYB: Dictionary patch collection for SAP NetWeaver 7.40".
   
   
   
   


2. Try compression with one of these options

Remove secondary indexes of the F table and then run the compression. Indexes would be recreated again after compression finished

Or

Unpartition the F table if there are any partitions created, as suggested in KBA 2018715.
You'll need to ensure SAP Note 1691300 is implemented (it will be through the implementation of the BW Correction Collection mentioned above), and then apply the manual step which is to include in RSADMIN, the object entry SYBASE_UNPARTITIONED_F_FACT_01 with value <Infocube_Technical_Name>.

3. Deactivate the aggregates and run the compression.

Else

Upgrade the ASE to a higher version, and then run the compression.

(Recommended: ASE 16 SP03 PLx and the SAP BW 7.40 to a newer SP.)

Since upgrading the database is a complex approach to resolve this issue, I followed the other steps and got it resolved by deactivating the aggregates and deleting the indexes of the cube.

Conclusion

Hence, when the aggregate rollup fails but the aggregate indicator persists, the aggregates have to be deactivated first and the compression has to be performed again after following the above steps. The aggregates must be filled again after compression.

Note: Any operation on the cube is not possible until the aggregates have been deactivated.