This is one of the problems we faced while using ODBC Stage in DS 8.1.
We used to define the 'Truncate Table' option in ODBC Stage.
Then we noticed a regular space issue with the tempdb.
On analysis, we found that the tempdb had a lot of delete log files for the tables which were loaded in the TRUNCATE table mode through the ODBC Stage in the DS job.
The solution was to manually specify a 'Delete' Query in the ODBC Stage instead of running it in the 'TRUNCATE TABLE' mode.
-------------------------------------------------------------------------------------
We have found, after some tests with the DB admin, that DataStage, when truncating a table, doesn't send a 'TRUNCATE' query to the DB, but a 'DELETE' query. That is why the logs table is quicly full. If we solve this problem, we would not have such problem, and even, improve significatively jobs performance (delete needs far more ressources than truncate).
When you check the log of any stage, you can see that:
ODB_REPORT: [IIS-CONN-ODBC-000106] Statement generated is delete from dbo.SR_MVDE_FFASVL
[IIS-CONN-ODBC-000097] Truncate table statement executed successfully
The DB admin confirms that a delete is sent to the DB.
We used to define the 'Truncate Table' option in ODBC Stage.
Then we noticed a regular space issue with the tempdb.
On analysis, we found that the tempdb had a lot of delete log files for the tables which were loaded in the TRUNCATE table mode through the ODBC Stage in the DS job.
The solution was to manually specify a 'Delete' Query in the ODBC Stage instead of running it in the 'TRUNCATE TABLE' mode.
-------------------------------------------------------------------------------------
We have found, after some tests with the DB admin, that DataStage, when truncating a table, doesn't send a 'TRUNCATE' query to the DB, but a 'DELETE' query. That is why the logs table is quicly full. If we solve this problem, we would not have such problem, and even, improve significatively jobs performance (delete needs far more ressources than truncate).
When you check the log of any stage, you can see that:
ODB_REPORT: [IIS-CONN-ODBC-000106] Statement generated is delete from dbo.SR_MVDE_FFASVL
[IIS-CONN-ODBC-000097] Truncate table statement executed successfully
The DB admin confirms that a delete is sent to the DB.