DB2: Termination Character in the tDB2Row component

One Star

DB2: Termination Character in the tDB2Row component

Hi,
I want to dynamically create/replace a stored procedure via the tDB2Row component. The procedure changes and is part of our CDC-logic, because of this it needs to be updated by a talend job.
We create the procedure (as a String) in a Java class. the issue is that I can't set the terminator through the tDB2Row component.
When I tested the SP manually via Data Studio or the commandline I used the following commands which worked fine.
#SET TERMINATOR @

-or-
--#SET TERMINATOR @

This just results in errors:
Exception in component tDB2Row_1
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=#SET TERMINATOR @;BEGIN-OF-STATEMENT;<space>, DRIVER=4.14.111

-or-
Exception in component tDB2Row_1
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END@;CASE;
   END FOR;
;END, DRIVER=4.14.111

depending on which version of I used.

The other possibility would be to using
BEGIN ATOMIC
  SQL COMMANDS WITH THE NORMAL TERMINATOR;
END

but this also results in errors:
Exception in component tDB2Row_1
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-20112, SQLSTATE=3B002, SQLERRMC=null, DRIVER=4.14.111

I'd like to avoid using a sql file that'd have to be called via the commandline. I'm sure the error appears because the way I defined the TERMINATOR conflicts with the way talend sends this command to the database (java.sql.Statemen.execute())
I've tried multiple other ways but none seems to work. I need some advice/pointers...
One Star

Re: DB2: Termination Character in the tDB2Row component

On further investigating the SQL errors etc.:
It seems that the
BEGIN ATOMIC
  SQL COMMANDS WITH THE NORMAL TERMINATOR;
END

works well. The error
Exception in component tDB2Row_1
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-20112, SQLSTATE=3B002, SQLERRMC=null, DRIVER=4.14.111

has to do with SAVEPOINTS. There's no savepoints in my procedure and I can't find any in the query  that's sent to the DB2:
System.out.println((String)globalMap.get("tDB2Row_1_QUERY"));

only shows the command that I used. So there shouldn't be an issue with SAVEPOINTS
Seventeen Stars

Re: DB2: Termination Character in the tDB2Row component

Your mentioned commands to set the terminator are commands to the Data Studio and not SQL commands for the connection.
I would use the tDB2SP component instead of tDB2Row.
One Star

Re: DB2: Termination Character in the tDB2Row component

Thanks for the advice but tDB2SP can only call a procedure, not create/replace it. Am I wrong about that?
While I'm not sure about the root cause of the problem yet I found a solution / workaround:
Since we're working in a transaction that's started by our ETL tool we can just use "BEGIN NOT ATOMIC". This will provide us with the same notation/syntax (no Errors due to ';' as Terminator) but will not automatically create a new savepoint.
If you're building your own app/component or do not use a transaction already please DON'T USE this solution.

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now