How to decode SAP text from from STXL.CLUSTD?

Question

I know ! The "proper" way to read STXL.CLUSTD is through SAP ABAP function. But I'm sorry, we are suffering badly from performance problem. We have already make our decision to go directly to the database (Oracle), and we don't have any plan to revert our decision yet since everything goes so much better so far.

However, we've came across this issue. The text in STXL.CLUSTD field was stored in an incomprehensible format. We cannot find any information about its encoding format via google. Anybody can hint me how to decode text from STXL.CLUSTD ?

Thanks

Solution 1

Short version: You don't. Use the function module READ_TEXT.

Long version: You're looking at a so-called cluster table. See http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb3bf8358411d1829f0000e829fbfe/frameset.htm for the details. The data you see is an internal representation of the text, somehow related to the way the ABAP kernel handles the data internally. This data does not make any sense without the metadata. If you change the original structure in an incompatible way, the data can no longer be read. Oh, and did I mention that the data does not contain a reference to the metadata? When reading the contents of these tables, even in ABAP, you need to know the original source data structure, otherwise you're doomed. Without the metadata and the knowledge of how the kernel handles these data types at runtime, you'll have a hard time deciphering the contents.

Personal opinion: Direct access to the database below the SAP R/3 system is a really bad idea since this not only bypasses all safety measures, but it also makes you very vulnerable to all structural changes of the database. The only real reason for accessing the database directly is not lack of performance, but lack of (ABAP) knowledge, and that should be curable :-)

Solution 2

You can definitely read clusters and pools without running any ABAP code, or invoking RFC's or BAPI's, etc. it is a very good approach, highly performant, and easy to use.

I don't like people flogging their products in StackOverflow, but the information that you must use ABAP to access SAP data has been outdated for over 7 years now.

Thanks,

Bill MacLean

• I just noticed this thread and I work for Simplement. Snow_FFFF is correct (BTW, that user is not me, and ASFAIK is not anyone in our company). The Data Liberator product has been de-clustering and de-pooling tables (and many other things) for our customers since 2009.

Comments

[Sake]

READ_TEXT is not exactly the cause of performance problem. But inability to fine tune SQL produce by ABAP is my problem at that time. That's why staying away from ABAP interface and goes directly to SQL database was considered as a solution.

[PATRY Guillaume]

agreed. you don't acces database tables, and never a cluster table.

[Sake]

Okay. I gave up. I'll try to convince my boss to invest more resource on ABAP things. Thanks

[Balazs Gunics]

We have an ABAP way to extract cluster data, but we want to use real time data replication, and for that accessing the data in the cluster tables would be the key. So does anyone happen to know how to decompress VARDATA field(s)? What kind of compression does SAP use?

[snow_FFFFFF]

Simplement has products for real-time replication of SAP data that includes decoding of cluster and pool tables: simplement.us

[James K]

Welcome to SO Bill. There's no need to sign your answers. When you have a little more reputation you can add comments to other people's answers.

[Thomas Matecki]

Agreed. Also, It is unlikely that directly reading the cluster table will alleviate the performance problem. How about posting some traces(or the results of some other profiling tools)? I suspect the better question is "why is reading STXL so slow?"

[Eralper]

@Bill, is there a SQL code that you can share with us to read these clusters for text info

[Bill]

@Erlaper, Sorry for the long delay. There isn't any SQL code that will allow you to read these clusters without the Data Liberator. The formats are are real mess. The Data Liberator renders them to real tables, and then you just use normal SQL to SELECT from the table/view etc. Regular SQL CONVERT() functions and the like won't work.