I have searched for this and didn't come across a solution yet.
I am trying to create an SP that will execute some of its code as parallel queries and other parts as sequential queries.
Let's say I have the following queries:
CREATE TABLE first_table AS (SELECT id, field1 FROM some_table); CREATE TABLE second_table AS (SELECT id, field2 FROM other_table); CREATE TABLE third_table AS (SELECT a.id, a.field1, b.field2 FROM some_table a inner join other_table b on a.id = b.id); CREATE TABLE fourth_table AS (SELECT * from third_table);
I would like my SP to execute the first and second statement in parallel (like pressing F9 in Teradata SQL Assistant) and when those are done, to continue executing in a serial manner. I hope I have explained the issue clearly.
I'm really hoping there is a way to do this.
When you show me how you run two CREATEs in SQL Assistant using F9 suceesfully, I will show you to do the same in an SP :-)
As per @dnoeth 's reply this cannot be done using an SP. The reason why is as follows:
'Create table' is a DDL statement.
SQL rules say that a DDL statement must be the only statement or the last statement in a Transaction. Teradata rules say that a DDL statement must be the only statement in a Request.
An SP runs on a single session and Teradata only allows one active request per session.
If you try using F9 for two 'create table' statements in SQLA it fails with: " Only an ET or null statement is legal after a DDL Statement."
Thank you both for your answers.
I was not aware that this wasn't possible. I now realise I have never tried to execute CREATE statements in parallel, only INSERTs.
I have rewritten just a small part of what I want to execute. So, I am goint to present you with a modified problem =)
This would be my new code
CREATE TABLE first_table (id INT, field1 VARCHAR(8)); CREATE TABLE second_table (id INT, field2 VARCHAR(8)); INSERT INTO first_table SELECT id, field1 FROM some_table; INSERT INTO second_table SELECT id, field1 FROM other_table;
I would make the SP to execute the CREATE statements serially, and then (assuming this time I'm not incredibly wrong as before) to execute the INSERT statements in parallel. Would it be possible to make the SP do this?
Thank you again!
Now...the question would be, how to make the SP do this? I have no idea how to tell the SP to execute certain code in serial and some other portions of code in parallel.
Would you be so kind of teaching me that syntax? Or pointing me to where could I find this information? I wasn't able to find it.
BEGIN REQUEST; -- single request from here ... INSERT INTO first_table SELECT id, field1 FROM some_table; INSERT INTO second_table SELECT id, field1 FROM other_table;
END REQUEST; -- ... to here
This is a Multi Statement Request within a Stored Procedure.
Of course both Inserts are a single transaction now, if one fails both are rolled back.
I just checked the manuals, it's hard to find, no details :-(