Pentaho

 View Only

 Change setting in 'Feature List' for 'IBM DB2 '

Klemen Vrhovsek's profile image
Klemen Vrhovsek posted 05-12-2023 05:14

When you create new conection in pentaho for 'IBM DB2', 'Native (JDBC)'. The 'Feature List' has default setting for 'SQL: truncate table' as 'ALTER TABLE "TABLE" ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE' how can I change it to 'TRUNCATE TABLE "TABLE" immediate'

I tried changing setting but it doesn't save. Looked for it in files but couldn't find it.

To get this error set database setting for blocknonlogged in SYSIBMADM.DBCFG to true. Create new job where in output you tick 'Trucate'. The job will faill.
Couldn't execute SQL: ALTER TABLE TEST.XXX ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE DB2 SQL Error: SQLCODE=-628, SQLSTATE=42613, SQLERRMC=ACTIVATE NOT LOGGED INITIALLY, DRIVER=3.61.75

Upgreding pentaho to 9.1 dosen't do anything and upgreding jdbc to 3.72.54 (last one that uses db2jcc.jar) also dosen't change anything. 
DB is vesrion 'DB2 v11.5.8.0'
If you run 'truncate table... immediate' it works.

Petr Prochazka's profile image
Petr Prochazka posted 05-19-2023 14:27

Hi Klemen,
this is not possible. Truncate table is defined by used database type, in source code. You can try use generic database type, bud this database meta uses DELETE FROM SQL statement.
So you have to truncate table before import explicitly.
Or create own database meta for DB2 with this behaviour (create Java class as database plugin).

Klemen Vrhovsek's profile image
Klemen Vrhovsek posted 05-23-2023 03:18

@Petr Prochazka thank you for replaying.

If you change the file in kettle-db.jar. 
Location kettle-db/org/pentaho/di/core/database/DB2Database/DB2DatabaseMeta.class

It is possible to get the result


But this was done by someone else so I do not know the procedure

Petr Prochazka's profile image
Petr Prochazka posted 05-25-2023 10:24

Yes, this is one way howto change behaviour, replace this class by 

Or create database plugin jar and this add to plugins directory, ex. plugins/databases/db2-custom.jar.
Example plugin jar is as attachment. This plugin replace original implementation of DB2 metadata.

package org.pentaho.di.core.database;

import org.pentaho.di.core.plugins.DatabaseMetaPlugin;

/**
 * Custom DB2 Database meta.
 *
 * @author Petr Procházka (petrprochy)
 */
@DatabaseMetaPlugin(type = "DB2", typeDescription = "IBM D2")
public class CustomDB2DatabaseMeta extends DB2DatabaseMeta {
  @Override
  public String getTruncateTableStatement(String tableName) {
    return "TRUNCATE TABLE " + tableName;
  }
}


After you can check plugin list in PDI (I have custom MariaDB database meta) and see some similar for DB2 that meta is loaded from jar file.

Loaded DB metadata classes







Attachment  View in library
pentaho-db2-9.4.0.0-1-SNAPSHOT-kettle.jar 2 KB

Related Content