TDV SQL Support for SAP Tables
This section describes how SAP tables work within TDV. It is divided into three parts:
| • | Introspection and SAP Tables |
| • | Capabilities and SAP Tables |
| • | Joins and SAP Tables |
Introspection and SAP Tables
The introspection process examines SAP for its list of tables, allows the user to select tables, and translates the SAP table metadata into relational tables for TDV. The important considerations are:
| • | Resource Hierarchy |
| • | Metadata Mapping |
Resource Hierarchy
The SAP Adapter provides logical SAP tables from the SAP data dictionary as resources in TDV. Typically, any table that can be accessed via the SAP function RFC_READ_TABLE can be queried from TDV. Exceptions to this rule are discussed in Metadata Mapping.
SAP tables are introspected by drilling down into a two-level hierarchy of folders. SAP tables are organized into folders based on the first two characters of their name. For example, the SAP table named VBAK is located in V > VB.
Metadata Mapping
When translating metadata from SAP tables to TDV table resources, each column of the SAP table becomes a column in TDV with the same name. Primary key columns in the SAP table become primary key columns in TDV.
Each data type in SAP is mapped to a TDV SQL-based type that is reported in the FIELDS table returned by RFC_READ_TABLE or YRFC_READ_TABLE. TDV reads the table and uses the field lengths listed there.
Note: TDV recommends using the YRFC_READ_TABLE if it is implemented on your SAP system.
The following table lists the SAP data types, whether they are supported in TDV, and their corresponding TDV type.
|
SAP Data Type Name |
ABAP Type |
Supported |
TDV Data Type |
|
CHAR, UNIT, CUKY, CLNT, LANG, LCHR, VARC |
C |
Yes |
VARCHAR |
|
DATS |
D |
Yes |
DATE |
|
FLTP |
F |
No – see below |
|
|
STRING, XSTRING |
g, y |
No – see below |
|
|
INT4, INT2, INT1 |
I |
Yes |
BIGINT |
|
NUMC |
N |
Yes |
VARCHAR |
|
CURR, QUAN, DEC, PREC |
P |
Yes – see below |
DECIMAL, NUMERIC |
|
TIMS |
T |
Yes |
TIME |
|
LRAW, RAW |
X |
Yes – see below |
VARCHAR |
In addition to the columns, descriptive text is retrieved from SAP and stored in the Annotation field of TDV.
Several limitations occur if you introspect with RFC_READ_TABL:
| • | By default, columns greater than 512 bytes cannot be read, and are automatically ignored, when introspecting SAP tables. |
| • | RAW and LRAW columns (ABAP Type X) are not encoded properly, and may appear truncated when selected. |
| • | Tables containing columns of STRING or RAWSTRING data type cannot be accessed. These tables appear in the introspector, but an exception is thrown when they are used. |
To avoid returning silently incorrect data, the SAP Adapter raises an error when it encounters rows that contain truncated values.
TDV recommends using YRFC_READ_TABLE for your SAP data source whenever possible.
SAP note 758278 provides an implementation of YRFC_READ_TABLE that addresses decimal issues. To apply this fix, install the YRFC_READ_TABLE function module on SAP, then modify the following three advanced data source properties:
| • | Table read function: YRFC_READ_TABLE |
| • | Table row length: 4010 |
| • | Table decimal fix: Checked |
Capabilities and SAP Tables
Capabilitiesdetermine how a SQL statement is divided for execution among SAP, TDV, and other data sources that may be referenced in the query. Capabilities are a system of classifying SQL elements to account for the unique features and limitations of data sources. Where a capability is lacking in a data source but required to run a query, TDV performs the work itself.
The following table lists commonly used capabilities and how they apply to SAP tables. limitations For efficient queries, minimize use of non-push capabilities.
|
Capability |
Supported in SAP? |
Pushed to SAP? |
Notes |
|
Filter |
Yes |
Yes |
Filter comparing two columns of same table is not allowed by SAP. |
|
Filter–BETWEEN |
Yes |
Yes |
|
|
Filter–IN |
Yes |
Yes |
|
|
Filter-LIKE |
Yes |
Yes |
|
|
Functions–CAST |
Yes |
Yes |
|
|
CASE |
Yes |
No |
|
|
DISTINCT |
Yes |
No |
|
|
Functions–aggregate |
Yes |
No |
|
|
Functions–others |
Yes |
No |
|
|
GROUP BY |
Yes |
No |
|
|
Join |
Yes |
No |
|
|
ORDER BY |
Yes |
No |
|
|
Subquery |
Yes |
No |
|
|
UNION |
Yes |
No |
|
|
DELETE |
No |
No |
SAP tables are read-only. |
|
INSERT |
No |
No |
SAP tables are read-only. |
|
Transactions |
No |
No |
INSERT/UPDATE/DELETE not supported. |
|
UPDATE |
No |
No |
SAP tables are read-only. |
Joins and SAP Tables
This sectiondescribes how to join SAP tables with each other and with other resources. Joins cannot be pushed to SAP; instead, every row of the joined tables must be fetched (a procedure known as a table scan). The technology used to communicate to SAP from TDV is not optimized for working with large data sets, so table scans should be avoided.
A semijoin is the best way to reduce the number of SAP rows that TDV retrieves and processes. To force a semijoin to occur in a query, add it immediately before the tables to be joined. For example:
SELECT * FROM A INNER { option semijoin } JOIN B ON A.K = B.K
Values of A.K are collected and passed in a query to B as the filter.
SELECT * FROM A
SELECT * FROM B WHERE K IN ({values of A.K from previous query})
Many rows in A can result in lengthy queries against B. If queries against B are large, TDV automatically breaks them into smaller queries, executes them separately, and reassembles a unified result set.
Put the larger table on the right side of the join whenever possible. When running a new query for the first time, activate the Execution Plan tab in Studio and click Execute and Show Statistics. Examine each node’s row count and query after processing has begun, to make sure filters are pushed down to SAP.
If the interaction between TDV and SAP is still unclear and performance is poor, enable debug logging as described in the TDV Installation and Upgrade Guide.
Tips from Expert
This section discusses the best practice to follow for an improved performance in query execution.
The SAP function that is used to read SAP data (RFC_READ_TABLE) is limited to reading 512 bytes of data per row. If a SQL request in TDV includes columns whose length sums to greater than 512 bytes, then TDV must make multiple requests to SAP for the same row of data (using the primary key), one per 512-byte data. This will affect the performance.
To avoid this performance hit, either avoid making "wide" queries (reduce the number of columns projected), or apply (defunct) SAP Note 758278, which increases the row length of an RFC_READ_TABLE-equivalent function to 4,000 bytes. By increasing the row length in SAP, we eliminate the overhead of the "wide" rows. Rows up to 4,000 bytes can then be queried with a single RFC invocation. Note that this is only applicable for SAP Tables and not other types of resources.