Multiple statements in a single tAS400Row Component

Five Stars

Multiple statements in a single tAS400Row Component

Does anyone know if it is possible to execute multiple SQL statements in a tAS400Row Component similar to tMySQLRow using "allowMultiQueries=true"

 

This doesn't appear to work for tAS400 components & throws the usual end of statement error

 

.Capture.PNG

 

We are using Talend version 6.4.1

 

Thanks in advance


Accepted Solutions
Eleven Stars

Re: Multiple statements in a single tAS400Row Component

it depend of the queries and the file structure.
Read queries: tFileInputRaw or Delimited (depend on file format)
Execute queries : tAS400Row tAS400Input (depend on queries)
It's possible to be compatible with all type of query (analyse query before execute)
It's possible to add comment into Script file to choose the destination of data...…
Regards

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: Multiple statements in a single tAS400Row Component

I have an update on this, an easier solution is to wrap the statements between BEGIN and END statements

 

eg:

BEGIN

    DROP TABLE schema.table1;

    CREATE TABLE schema.table1 .....(table stuff here);

    GRANT SELECT on schema.table1 to USER;

END

 

This greatly simplifies maintenance of code & processes automated using talend. 


All Replies
Eleven Stars

Re: Multiple statements in a single tAS400Row Component

allowMultiQueries=true, depend on the JDBC Driver option.
I think that there is no similar option in AS400.
but if you rely need it, you can configure parale run on Talend Job.

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: Multiple statements in a single tAS400Row Component

Thanks for the reply.

 

I don't actually need the statements to run in parallel, they execute sequentially, but the job I'm trying to automate would have 450 statements to execute in sequence. Trying to build that one row component at a time would be painful so I was hoping to be able to just paste my whole script into a single component. 

 

If that option isn't available, then I suppose I will have to do it the long way!

Eleven Stars

Re: Multiple statements in a single tAS400Row Component

you can read your script file and run query one by one.
Regards,

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: Multiple statements in a single tAS400Row Component

At the risk of sounding completely dense - which components would you use to execute the commands from the file using tAS400 components?
Eleven Stars

Re: Multiple statements in a single tAS400Row Component

it depend of the queries and the file structure.
Read queries: tFileInputRaw or Delimited (depend on file format)
Execute queries : tAS400Row tAS400Input (depend on queries)
It's possible to be compatible with all type of query (analyse query before execute)
It's possible to add comment into Script file to choose the destination of data...…
Regards

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: Multiple statements in a single tAS400Row Component

I have an update on this, an easier solution is to wrap the statements between BEGIN and END statements

 

eg:

BEGIN

    DROP TABLE schema.table1;

    CREATE TABLE schema.table1 .....(table stuff here);

    GRANT SELECT on schema.table1 to USER;

END

 

This greatly simplifies maintenance of code & processes automated using talend. 

Cloud Free Trial

Try Talend Cloud free for 30 days.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.