- SAP Managed Tags
What is GTT?
In databases, Temporary table - is an object on database level, where data can be stored after select-operation; however data are stored during session only and not transferred from database to application. It gives option to apply SQL-operators to "temporary data" for calculations and do not fullfill extra-roundtrip between database and application servers. In terms of ABAP-development we could see GTT as alternative to "temporary internal tables" and UNION-operators.
GTT is a transparent table of special type in ABAP, which can store data during DB LUW and the data could be read only during the current session. At the beginning and at the end of session the table should be cleared implicitly (via DELETE FROM operator). The main purposes of GTT is to divide some complex selection on the several steps. GTT incurs lower administration costs than regular transparent tables and provide higher performance. We can say that GTT is like internal table but on the database side. Let us see demo-sample.
Demo sample for GTT (global temporary table)
GTT is really useful when we need to read data from different database-sources which has different purposes, but can be transformed to the same structure. Assume, we need to read list of materials (MATNR), which was specified in orders on some DATE1 and in invoices in some invoice date DATE1.
For the sake of demonstration I will create 4 custom-table (they would be like VBAK, VBAP, VBRK, VBRP, but far shortly).
This is huge simplified structure of VB*-sales tab, but for demo sample it is suitable. The main idea is that data are in different tabs. Then I will fill data with help of utility from here (github link). And now let's specify how we can solve the task above:
1. We can read data from every table (or join) and unite records on the application server side into one result table (it can be called classic way, so I will not pay much attention to this approach here).
2. We can use UNION from two JOINs and receive all data. Potential implementation could be found here.
3. We can use Global Temporary Table (GTT). We select from ORDER-tables and insert into GTT on database side without roundtrip to application server; then we select from INVOICE-tables and insert into GTT and the final step we read from GTT the final result. Implementation could be found here.
How to create GTT in SAP Net Weaver / DDIC
Let's see step by step how to create GTT for the demo.
The GTT created via tcode SE11 as transparent table, but we put mark that it is temporary table. We should specify L as delivered class.
Then we specify fields as usual
Then we are go by menu: Extras -> Change/Display Table Category
And put Table Category as Global Temporary table
Technical settings are not maintained for GTT (data class, size category etc.). Then we should activate GTT in data dictionary and it is done 🙂 . Limitations for GTT are described in abap-help.
Using GTT and reading data with GTT
The full implementation of the approach is here.
GTT allows us divide the data-selections on step-by-step approach. So we could split it into two abap-methods: _fill_gtt_from_order and _fill_gtt_from_invoice. So, it increase readability; also we could pay attention to the system variable sy-dbcnt and we can know how many records on each step was inserted into GTT. I want to highlight that we are not selecting into GTT, but we are inserting into GTT (we are doing INSERT INTO ZTC8A016_MAT_TMP).
METHOD _fill_gtt_from_order.
INSERT ztc8a016_mat_tmp FROM
(
SELECT DISTINCT
'O' AS src_type,
ordi~matnr AS matnr
FROM ztc8a016_ordi AS ordi
JOIN ztc8a016_ordh AS ordh ON ordi~vbeln EQ ordh~vbeln
WHERE ordh~order_date EQ @MV_trg_date
).
ENDMETHOD.
METHOD _fill_gtt_from_invoice.
INSERT ztc8a016_mat_tmp FROM
(
SELECT DISTINCT
'I' AS src_type,
invi~matnr AS matnr
FROM ztc8a016_invi AS invi
JOIN ztc8a016_invh AS invh ON invi~inv_num EQ invh~inv_num
WHERE invh~invoice_date EQ @MV_trg_date
).
ENDMETHOD.In case we need to add additional source (it can be: deliveries, purchasing docs etc), then we DO NOT NEED to change the current SELECT-operators, we will add additional abap-methods. Therefore, it is more readable and changeable approach.
After all insertions into GTT, we can read data from it. And it is important that we could apply almost any SQL-operators to it, including aggregated functions, JOINs, WHERE/HAVING etc. However after using GTT, we should keep it clear; there should not be any commit (including implicit).
_fill_gtt_from_order( ).
_fill_gtt_from_invoice( ).
""""""""" read
" we can use almost any SQL-operators
SELECT DISTINCT matnr
FROM ztc8a016_mat_tmp
ORDER BY matnr DESCENDING
INTO TABLE @ET_matnr_list
UP TO 3 ROWS.
IF mv_mode EQ '3'.
" if GTT is not empty - we will get RunTime Error (aka red Dump)
" implicit commit via message
MESSAGE i000(cl) WITH 'Runtime Error goes here'.
ENDIF.
""""""""" delete - MUST EXIST
DELETE FROM ztc8a016_mat_tmp.
MESSAGE i000(cl) WITH 'GTT is empty'.Full example is here for demo. ABAP-help for GTT is here.
Conclusion
Advantages of GTT:
1) we could avoid extra roundtrips with large amounts of data between application server and database server
2) we could divide select-logic into abap-methods and it does improve readability
3) because of low administrative costs the table has improved performance than temporary table.
Bluesky