I have got a procedure that can execute up to 400 sql query (with DBC.SysExecSQL) in a row.
For now, it works like this : the SP build a query then execute it, wait for it to be done and go to the next one.
I wanted to know if it was possible to do something like the ampersand in a shell to execute it in a background.
So having something like this : the SP build a query then execute it, go to the next one without waiting to finish the query.
Thanks for your help
What kind of SQL statement are you executing? DML or DDL?
You can do multistatements in SysExecSQL/EXECUTE IMMEDIATE, but there's no way to execute it in the background (it's a single session).
I'm only doing DML queries.
Even with multistatements, I'm going to be limited at 64000 characters (the max size of a varchar) for each SysExecSQL, right ?
How should I append the queries to run them as multistatements with SysExecSQL ?
Like this is ok : "INSERT ...; INSERT ...; UPDATE...."
or it need to be like in BTEQ like this :
I found this for how to run MSR with SysExecSQL :
I was thinking of doing an array of strings, to use a new string every time I reach 64000 characters and then do the concatenations of all the strings during the SysExecSQL call. Is this the way to do it ?
Yep, the limitation is probably 64000 (or 32000 for Unicode).
Simply concat all the DMLs seperated by semicolons.
Sinlge row Inserts or Insert/Select?
Are those 400 Ins/Del into the same table or different tables, using a cursor?
I'm looping a list of tables with a cursor then for each table I do either an UPDATE FROM a working table following by a INSERT/SELECT or I'm doing a DELETE following by an INSERT/SELECT.
Example below with a working table called A_PRE and the final table A.
Scenario 1 : Update A from A_PRE
Insert A_PRE into A
Scenario 2 : Delete A
Insert A_PRE into A
The first scenario doesn't make a lot of sense to me, but in either case each pair of DML statements has to be executed serially. (It makes no sense to try to Delete A while you are inserting into it.) Therefore, multistatement doesn't seem to be an option -- all statements in a multistatement can execute concurrently.
What might work is to execute all the first's of these pairs in one or more multistatements, and then execute the second's of the pairs in a multistatement(s).
The only other option I can think of is to back up to a higher level and instantiate several of these procedures at once, each one operating on a subset of these pairs of DML statements. They could be launched from a shell script (using Bteq with &), or a C program that fork()'s little Embedded SQL programs that call the procedures, or a Java program that launches them in threads, et al.
A combination of these two approaches might work too, but if you can make this work it looks like it might keep the Teradata system real busy for awhile!
The optimizer follows the order of statements within a MSR, i.e. If there are dependent statements like Delete followed by Insert the optimizer will execute them serially. Only if they are independent they might be executed in parallel.
Ah, yes, of course! So it will be easy to use multistatements, and if you really want them all to run concurrently, and they don't fit in 64K, you can still instantiate multiple instances of the SP.
The first scenario is to take care of the history manually (we are not using temporal tables), that's why I need to do an update first.
In term of performance, is it better to do multiple MSR in one procedure or having a shell script (with Bteq and &) calling the SQL code for every pair ?
If it's roughly the same, maybe the easiest idea to manage this is by doing the logic outside in the shell script and not in the stored procedure.
Performance is roughly the same either way. In terms of exectution, the SP code is running in the Teradata system while bteq is running outside of it. Since 99.9% of the work is going to be in the DML statements themselves, it hardly matters. The nice thing about using a SP is that it is in the database, part of the record, restorable and easy to find. If you do it in Bteq/shell scripts, you will want to make sure there is a repository of some kind to record it in so that 5 years from now people won't be wondering, "whatever happened to that update script?"