Re: Executing SQL in the Background?

From: "Mark D. Stock" <mdstock@mydas.freeserve.co.uk> > >Obnoxio The Clown wrote: > > > > From: "Mark D. Stock" <mdstock@mydas.freeserve.co.uk> > > > > > >Obnoxio The Clown wrote: > > > > > > > > In the year of Our Lord Fri, 15 Dec 2000 23:21:08 GMT, Jonathan >Leffler > > > > <jleffler@informix.com> broke a vow of silence to utter: > > > > > > > > >Nebojsa Sevo wrote: > > > > >> On Thu, 14 Dec 2000 16:59:02 -0500, Don Ignacio > > ><dignacio@openratings.com> > > > > >> wrote: > > > > >> > > > > >> >UNIX: > > > > >> > You can place the sequence of sql statements in a file > > > > >> > with an extension of ".sql". And enter the command: > > > > >> > "dbaccess <file>.sql" as a cron job. > > > > >> > > > > > >> OR > > > > >> nohup dbaccess db_name <file>.sql & > > > > > > > > > >While admirable solutions as far as they go, these answers really > > > > >acknowledge Peter's second paragraph and don't address Peter's >first > > > > >question -- can you achieve the same effect inside a single running > > >copy > > > > >of DB-Access. > > > > > > > > > >The answer to that is 'No', because the only way to achieve it >would be > > > > >with a multi-threaded application, and DB-Access is not >multi-threaded. > > > > >Neither is SQLCMD, so my usual fallback remedy for the ills of > > >DB-Access > > > > >is no help this time. > > > > > > > > > >It's interesting to contemplate how to design the language to >express > > > > >that two SQL statements should be run in parallel. How would you > > >handle > > > > >the errors and output from the two statements? > > > > > > > > Well, ignoring errors, how about: > > > > > > > > dbaccess dbname <<! # could be from a file too! > > > > !dbaccess dbname script1 & > > > > !dbaccess dbname script2 & > > > > !dbaccess dbname script3 & > > > > !dbaccess dbname script4 & > > > > !dbaccess dbname script5 & > > > > > >Isn't that the same as: > > > > > >dbaccess dbname script1 & > > >dbaccess dbname script2 & > > >dbaccess dbname script3 & > > >dbaccess dbname script4 & > > >dbaccess dbname script5 & > > > > > >? > > > > Yes, but yours uses the power (?) of the shell. Mine just uses dbaccess, > > which was, I believe, original question. > >Well it just looks like shELL to me with the hereto syntax, or can you >use that in NT? ;-) Well, I just did a test, and it did seem to fire up "sub shells", however, it looked like they were run sequentially, although it was hard to say that it was a definitive test. :-) _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

"Obnoxio The Clown" <obnoxio@hotmail.com> writes: > Well, I just did a test, and it did seem to fire up "sub shells", however, > it looked like they were run sequentially, although it was hard to say that > it was a definitive test. :-) I believe dbaccess wait()s for the shell sub-processes to finish before interpreting the next command. I've been waiting for years for 4gl to support some degree of parallel sql queries, but I havn't been holding my breath. Think you gotta get your parallelism with a shell and use "wait" for query/sub-process synchronization. Perl may be another option, but it's one I haven't explored. -- Forte International, P.O. Box 1412, Ridgecrest, CA 93556-1412 Ronald Cole <ronald@forte-intl.com> Phone: (760) 499-9142 President, CEO Fax: (760) 499-9152 My GPG fingerprint: C3AF 4BE9 BEA6 F1C2 B084 4A88 8851 E6C8 69E3 B00B

Ronald Cole wrote: > "Obnoxio The Clown" <obnoxio@hotmail.com> writes: > > Well, I just did a test, and it did seem to fire up "sub shells", however, > > it looked like they were run sequentially, although it was hard to say that > > it was a definitive test. :-) > > I believe dbaccess wait()s for the shell sub-processes to finish > before interpreting the next command. But the ampersand at the end of each command tells the shell to run the process in the background. Now, if the shell does not exit until its children terminate, that is a separate problem, overcome by using: ( dbaccess dbase scriptN.sql & ) This causes the first shell that is run (the parent shell) to run a sub-shell, and the sub-shell in turn runs the DB-Access command in the background and then exits immediately. The parent shell should terminate immediately too, as the DB-Access process is not one of its children. > I've been waiting for years for 4gl to support some degree of parallel > sql queries, but I havn't been holding my breath. > > Think you gotta get your parallelism with a shell and use "wait" for > query/sub-process synchronization. > > Perl may be another option, but it's one I haven't explored. Perl doesn't help very much. The Perl threading model is a bit iffy. The DBI module ensures serial access, thus undoing any benefit from threading. DBD::Informix does not expect to build with the threaded libraries (though it should work OK with them). However, it is not written to be thread-safe (because the DBI interface that is required is not thread-safe -- there are global variables, etc). And ESQL/C only provides concurrent execution of SQL statements on separate database connections; each connection is synchronous. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

In the year of Our Lord Tue, 19 Dec 2000 18:51:28 GMT, Jonathan Leffler <jleffler@informix.com> broke a vow of silence to utter: >Ronald Cole wrote: >> "Obnoxio The Clown" <obnoxio@hotmail.com> writes: >> > Well, I just did a test, and it did seem to fire up "sub shells", however, >> > it looked like they were run sequentially, although it was hard to say that >> > it was a definitive test. :-) >> >> I believe dbaccess wait()s for the shell sub-processes to finish >> before interpreting the next command. > >But the ampersand at the end of each command tells the shell to run the >process in the background. Now, if the shell does not exit until its >children terminate, that is a separate problem, overcome by using: > >( dbaccess dbase scriptN.sql & ) > >This causes the first shell that is run (the parent shell) to run a >sub-shell, and the sub-shell in turn runs the DB-Access command in the >background and then exits immediately. The parent shell should >terminate immediately too, as the DB-Access process is not one of its >children. Doesn't work in NT.

Obnoxio The Clown wrote: > > In the year of Our Lord Tue, 19 Dec 2000 18:51:28 GMT, Jonathan Leffler > <jleffler@informix.com> broke a vow of silence to utter: > > >Ronald Cole wrote: > >> "Obnoxio The Clown" <obnoxio@hotmail.com> writes: > >> > Well, I just did a test, and it did seem to fire up "sub shells", however, > >> > it looked like they were run sequentially, although it was hard to say that > >> > it was a definitive test. :-) > >> > >> I believe dbaccess wait()s for the shell sub-processes to finish > >> before interpreting the next command. > > > >But the ampersand at the end of each command tells the shell to run the > >process in the background. Now, if the shell does not exit until its > >children terminate, that is a separate problem, overcome by using: > > > >( dbaccess dbase scriptN.sql & ) > > > >This causes the first shell that is run (the parent shell) to run a > >sub-shell, and the sub-shell in turn runs the DB-Access command in the > >background and then exits immediately. The parent shell should > >terminate immediately too, as the DB-Access process is not one of its > >children. > > Doesn't work in NT. Well of course not - I was talking about a real operating system. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

Jonathan Leffler <jleffler@informix.com> writes: > But the ampersand at the end of each command tells the shell to run the > process in the background. Now, if the shell does not exit until its > children terminate, that is a separate problem, overcome by using: It would be nice if Informix's behavior in this regard were consistent and documented. I have number of regression test suites where I have to do a "sleep 5" before dropping a table because dbaccess will exit while the back end is still working on the table. :P pbbbbbt.... > And ESQL/C only provides concurrent execution of SQL statements on > separate database connections; each connection is synchronous. Which is why I consider shell/dbaccess to be *the* superior language for programming asynchronous query algorithms. But like the Clown says: NT is screwed in this department. -- Forte International, P.O. Box 1412, Ridgecrest, CA 93556-1412 Ronald Cole <ronald@forte-intl.com> Phone: (760) 499-9142 President, CEO Fax: (760) 499-9152 My GPG fingerprint: C3AF 4BE9 BEA6 F1C2 B084 4A88 8851 E6C8 69E3 B00B

The ONLY way to actually get the engine to execute multiple SQL statements COMPLETELY in parallel (see below) is to have multiple connections and issue separate commands on each connection. These will be fully parallelized by the engine as it assignes a separate SQLEXEC thread to each connection. Keep in mind, however, that CURSORs permit the CONCURRENT, though not actually simultaneous, execution of multiple SELECT statements on a single connection, yes EVEN in 4GL, by OPENing multiple cursors and FETCHing from them interleaved as needed. This is NOT the same as using multiple connections but it works. With multiple connections, in ESQL/C and perhaps Perl, you can use interrupts and call_back functions to make the multiple SQL statements completely backgrounded. Art S. Kagel Ronald Cole wrote: > > "Obnoxio The Clown" <obnoxio@hotmail.com> writes: > > Well, I just did a test, and it did seem to fire up "sub shells", however, > > it looked like they were run sequentially, although it was hard to say that > > it was a definitive test. :-) > > I believe dbaccess wait()s for the shell sub-processes to finish > before interpreting the next command. > > I've been waiting for years for 4gl to support some degree of parallel > sql queries, but I havn't been holding my breath. > > Think you gotta get your parallelism with a shell and use "wait" for > query/sub-process synchronization. > > Perl may be another option, but it's one I haven't explored. > > -- > Forte International, P.O. Box 1412, Ridgecrest, CA 93556-1412 > Ronald Cole <ronald@forte-intl.com> Phone: (760) 499-9142 > President, CEO Fax: (760) 499-9152 > My GPG fingerprint: C3AF 4BE9 BEA6 F1C2 B084 4A88 8851 E6C8 69E3 B00B