Pentaho Data Integration displays only the last 50 log entries but anytime you open a transformation it has to read the whole log table. When the log table gets large, opening a transformation may take long time and PDI freezes for this time.
A solution is to archive old entries from the log table.
Create an archive table ETL_LOG_ARCHIVE with the structure identical to the structure of the log table ETL_LOG:
SELECT * INTO ETL_LOG_ARCHIVE FROM ETL_LOG WHERE 0=1
Move old rows from ETL_LOG to ETL_LOG_ARCHIVE:
DELETE FROM ETL_LOG OUTPUT DELETED.* INTO ETL_LOG_ARCHIVE WHERE ID_BATCH IN ( SELECT ID_BATCH FROM ( SELECT ID_BATCH, ROW_NUMBER() OVER ( PARTITION BY TRANSNAME ORDER BY ID_BATCH DESC) RN FROM ETL_LOG ) A WHERE RN > 50 ) AND ID_BATCH NOT IN ( SELECT MAX(ID_BATCH) FROM ETL_LOG WHERE STATUS='end' GROUP BY TRANSNAME )
We want to keep the last 50 rows for each transformation. But also we need to keep the last successful execution for each transformation. Otherwise there might be a problem with incremental load if a transformation has been failing more than 50 times.