Unable to write table to teradata

I create a table in knime and want to write a table to lab environment. I am able to a lab in the teradata environment using DB Connector, however, when I join the table and DB Connector to DB writer, I get the following error:

ERROR DB Writer 0:94 Execute failed: Error while adding rows #-1000 - #0, reason: [Teradata JDBC Driver] [TeraJDBC 16.20.00.08] [Error 1338] [SQLState HY000] A failure occurred while executing a PreparedStatement batch request.

I am not sure if something is configured incorrectly in the DB Connector or something is wrong with the table.

Hello EllenPaige,
can you change the batch size in the DB Writer node to 1. This will change the insertion mode from batch to row wise and you might get a better error message from Teradata which tells you what the actual problem is.
Bye
Tobias

Thanks! That helped to determine I wasn’t defining decimals correctly.

1 Like

@tobias.koetter

Do you all have a recommended JDBC / DB Connector configuration for Teradata? If so, do you mind sharing a workflow example? A few things that come to mind:

  1. In the connection settings:
    a. I’ve read that we need to make “Database Type” = to Microsoft SQL Server to make it quit trying to do LIMIT queries and instead use a “TOP” command. I personally would prefer a “SAMPLE” instead of TOP, but better than it failing to preview.
  2. In the Advanced tab
    a. Any changes to “Dialect capabilities”?
    b. At bottom (unlabeled section):
    • the “Create Temporary” table syntax looks to be Oracle specific. Should this be “CREATE VOLATILE TABLE”?
  3. In the JDBC Parameters tab, should we have any parameters set to make Teradata perform better? I’ve seen TYPE=FASTLOAD referenced in some cases, but haven’t had any success.
  4. In the Output Type Mapping - If we set this at the DB Connector, can we not do it downstream in the various other nodes?
    a. We currently set all number KNIME type’s = Decimal as default

Hello Austin,
sorry for the late reply. No we do not have any recommendations for Teradata since we do not have a test instance running. Maybe someone else from the community can help here.
Bye
Tobias

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.