How to run Multiple Teradata SQL Queries from a single component

One Star

How to run Multiple Teradata SQL Queries from a single component

Hi
I have a specific requirement to run multiple sql queries from a tTeradataRow or tTeradataInput component.
everytime I run I get following exception.
Only an ET or null statement is legal after a DDL Statement.
I tried putting ETBT statements nothing worked.
I read there is a way of dealing this issue is stored procedure. But tTeradata has no SP component like oracle. Can any one please help me in this.
Thanks,
Amit
Moderator

Re: How to run Multiple Teradata SQL Queries from a single component

Hi,
Have you already checked document TalendHelpCenter:Executing multiple SQL statements with one tMysqlRow component?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to run Multiple Teradata SQL Queries from a single component

Thanks Sabrina,
But that does not work with teradata I am still getting the ET BT error message
for now I am just creating a table and then I am collecting stat on the fields of the newly created tables.
I checked JDBC Parameters for terdata I could not find any multi-statement or multi-query...
Thanks,
Amit
Moderator

Re: How to run Multiple Teradata SQL Queries from a single component

Hi,
So far, there is no tTeradataSP component. You can open a feature jira issue on Talend Bug Tracker, our component developer will check it.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Two Stars

Re: How to run Multiple Teradata SQL Queries from a single component

Hello,

I know this thread is a bit old, but wanted to give some updates on the same issue we are facing and our solution. We spent days researching and trying many different things, such as adding BT ET statements, changing JDBC parameters in our Teradata connection, changing auto-commit settings, etc. All with no luck!

Someone on my team opened a ticket in the Talend Bug Tracker and we were told that this is not a Talend bug, but rather a limitation with the Teradata JDBC driver (see ticket TDI-40898).

 

Here are our findings:

  • You CAN NOT execute multiple DDL queries (ex: create table, collect stats, drop table) within a single component.
  • You CAN execute multiple DML queries (ex: Delete, insert, update) within a single component.
  • You CAN NOT execute a mix of DDL and DML queries within a single component.

We have a requirement to execute a large numbers queries consecutively in our jobs, so breaking up our queries into separate tTeradataRow components to run 1 by 1 was very cumbersome and will not work for us.

 

I have therefore gotten creative and coded my own solution to this issue, which I hope others can copy and benefit from, as I found this very frustrating!

 

Here is how my solution works:

 

2018-10-01_11-29-32.jpg

 

Step 1:
Query File:
Our set of SQL queries is stored in a text file in a shared folder. All queries end with a semicolon in the file (except for the last query in the file). I read in the file using a tFileInputRaw component which will store the entire contents of the file as a long text string into a variable in the job. The variable that will store the string by default is row1.content.

2018-10-01_11-33-44.jpg

 

Step 2:
Parse Queries: 
Next, I added a tJavaRow component with some custom Java code. You can see what the code does in my comments in the code.

3.jpg

 

Steps 3 - 4. 
Loop
: Loop through the variables and execute the teradata command for each one, passing in the query text of the current iteration into the tTeradataRow component.

4.jpg

 

 

5.jpg

 

 

And there you have it! 

This has proven to work very well for us. It is a shame that I had to jump through hoops and take matters into my own hands to custom code this basic functionality. Although we were told that this is a Teradata JDBC driver limitation and not a Talend bug, I don't see why Talend wouldn't program it to work behind the scenes as I have programmed above.

 

Hope someone else finds this helpful!