Symptom
Target for Data Services job is local or Global temporary Hana table
and the execution logs show that data is inserted into it. But when
checking from Hana or View data nothing gets stored in the tables.
Environment
- SAP Data Services 4.x
- Hana 1.00.x
Reproducing the Issue
- Create a job with hana global temporary table as target. Run the job. You can see rows getting inserted but no data is accessible after job finishes execution. Data cannot be viewed in Hana Studio or in View Data in Data Services.
Cause
This is due to following properties of global and local Hana temporary tables and by Hana Design.
Global Temporary Table -
Table definition is globally available to any connection once created. It means metadata of the table is shared across sessions. But the data in a global temporary table is session-specific meaning that data inserted by a session can only be accessed by that session. The table is dropped when the last connection using it is closed.
Local Temporary Table -
Temporary table is visible only in the current session. Data in a local temporary table is session-specific meaning only the owner session of the local temporary table is allowed to insert/read/truncate the data. It exists for the duration of the session and data from the local temporary table is automatically dropped when the session is terminated.
Global Temporary Table -
Table definition is globally available to any connection once created. It means metadata of the table is shared across sessions. But the data in a global temporary table is session-specific meaning that data inserted by a session can only be accessed by that session. The table is dropped when the last connection using it is closed.
Local Temporary Table -
Temporary table is visible only in the current session. Data in a local temporary table is session-specific meaning only the owner session of the local temporary table is allowed to insert/read/truncate the data. It exists for the duration of the session and data from the local temporary table is automatically dropped when the session is terminated.
Resolution
Do not use Global or Local Temporary tables with Data Services, use regular tables instead.
See Also
The behavior can also be reproduced outside Data Services by creating
Hana Global Temporary table. Inserting and sleecting from it in 1
session, which returns whatever is inserted, if you insert and select in
separate SQL it would not return any data.
Keywords
ds data services hana global temp table local
No comments:
Post a Comment