topic Re: DB2: Termination Character in the tDB2Row component in Talend Studio

DB2: Termination Character in the tDB2Row component

_AnonymousUser — Wed, 22 Oct 2014 09:06:21 GMT

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...

Re: DB2: Termination Character in the tDB2Row component

_AnonymousUser — Wed, 22 Oct 2014 09:55:09 GMT

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

Re: DB2: Termination Character in the tDB2Row component

Anonymous — Wed, 22 Oct 2014 11:20:53 GMT

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.

Re: DB2: Termination Character in the tDB2Row component

_AnonymousUser — Wed, 22 Oct 2014 11:43:23 GMT

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.