CDS views on Oracle: falling into the cluster trap
Short blog about a nasty nasty bit of error mishandling I bumped into today.
In summary: CDS views cannot use cluster tables, but no obvious errors are produced. The activation log reports issues in a cheery green and looks like all is good to go.
*Edit: This is not specific to Oracle, but as it worked when tested on Sybase I assumed it was Oracle. The real reason was that the table CDPOS is a transparent table on Sybase and HANA, but a cluster table on Oracle.
The longer story:
I built a few CDS views and they worked very nicely. Then I copied them to another system running on Oracle and it all appeared to work. But one of the detail views didn’t return any result.
Following an association via the Eclipse SQL preview just sits there with nothing happening – neither result nor error.
Activating the view says it’s all fine and dandy.
Previewing it directly finally gave a message: ZMYCDS_V does not exist on the database
SE11 told me the same thing. Activation showed a green log. But afterwards it still gave a message that it didn’t exist on the DB.
Note 242686 gave some hints but none applied to our system.
It did provide a report RUTDDLSACT which I ran and after I combed through the 160+ lines of log in detail I finally spotted it:
Seriously SAP? Why is this green and not a big red fail? Never mind that this should not be activated with a dependent failure; there were several other points at which I would have expected failures, but most just behaved as if all is normal.
Note to self: Don’t just skim a log because it’s green. Read it. All of it.
This doco is usually up-to-date with all limitations when working with CDS on non-HANA DB's.
Hope it helps next time!
Thanks, I know the document, and it does not mention this scenario or cluster tables.
However I did find a note in the ABAP Doco about it:
A CDS database view is created for each CDS view and this database view supports only transparent tables, which means that pooled tables and cluster tables cannot be accessed using CDS views.
Nevertheless that doesn't excuse SAP from quietly ignoring it.
For what it's worth, the table in question is change document items (CDPOS), which is a transparent table on Sybase and in HANA. As it worked on Sybase, I assumed it was an Oracle-specific issue, it didn't occur to me to look at the table type until I spotted the log entries.
Yes, I get it... fair point. And SAP is moving away from this Cluster/Pool concept at the database layer anyway, surprises me that this is a Cluster on the Oracle version!
But question: How did you solve it? Did another view using the under-layer tables?
Yes, I didn’t expect it to be clustered.
I am not sure this can be solved with CDS. You cannot use a cluster’s constituent tables in a CDS view, and even if you could this table is not suitable. They basically serialized all field-level changes (CDPOS items belonging to a CDHDR) and stored it in a BLOB (Table CDCLS).
For our purposes the CDSs are OData service providers to a UI5 app. The only solution I can see is building a custom Gateway service with good ol’ fashioned ABAP SELECTs at the back end. The drawback is that we don’t have nice navigation binding from header to the items, which previously worked very well via CDS associations.
Yeah, got it.. I had use these tables before, but always having HANA as the DB.. no idea they had become that confusing when running on Oracle.
And you are probably right.. old SEGW's project could do the trick... If you are building a solution to run in both HANA and non-HANA, maybe you could go for a BOPF, build all associations there and just redirect the selects either to DB Tables or CDS Views according to the BD type, just to unify the product. But either way, way more work than just type associations on a CDS view.
Good luck and thanks for the explanation!
"SAP is moving away from this Cluster/Pool concept at the database layer anyway"
I would like to know more on this.
could you please share a link or something to read on this and current status of it.
Great tip, pretty strange it should be red. I would have for sure missed it. All error like these deserve to be in Red.
A great learning experience. Thanks for sharing.
I just felt in this trap.
Hearing about a real-life positive impact is probably the most rewarding and motivating thing about posting stuff on the interweb, so thanks for taking the time to post your experiences 🙂
There is something curious here where I am working right now, in DEV and PRD system the table CDPOS is cluster but in QAS is transparent.
How is it possible?
There are a few options. Basis may have performed a conversion from cluster to transparent, or QA may have been a new installation with all transports applied, or some other way of performing a production copy.
Between 7.4 and 7.52 they can be in either format and converted to transparent. As of 7.53 they are no longer supported:
Also see notes 1892354 and 2227432