Silly dbaccess question

I'm not sure if this will work, as this is my first attempt to post a new thread via email. The community web page keeps giving me errors when I try to create this as a new thread. It keeps telling me that I don't have access to save to draft in this community or something similar. Hopefully there won't be any issues with the formatting. Anyway, as the subject says, this is a silly dbaccess question. Or maybe it's not so silly. Maybe it used to work and now doesn't? I'm running 14.10.FC11W1 on RHEL 8.10, so if anyone on 11.x or 12.10 wants to check this, please do. I thought that there was a way when running dbaccess in non-interactive mode to have it output each SQL statement as it began execution, and then output the results as well. And I thought that way was with the '-e' option: dbaccess - -e myfile.sql So that if myfile.sql contained: DATABASE stores7; UNLOAD TO my_unload.unl SELECT * FROM customer;then at runtime I would get something akin to: DATABASE stores7;Database selected. UNLOAD TO myfile.unl SELECT * FROM customer;28 row(s) unloaded. However, when I run 'dbaccess - -e myfile.sql', I get no output whatsoever, and it does not execute the UNLOAD statement. The same is true if I run 'dbaccess - -e < myfile.sql'. Running 'dbaccess - < myfile.sql' gives only get the result lines "Database selected." and "28 row(s) unloaded." To get around this, I've resorted to the trick where dbaccess considers any line starting with '!' to be an OS command, using echo statements to get what I want: !echo DATABASE stores7; DATABASE stores7;!echo unloading from customer UNLOAD TO myfile.unl SELECT * FROM customer;The issue is that this is cumbersome and not very flexible. For instance, if I wanted to use environment variables and a 'here' document to allow customization at runtime, I can't see the final SQL that is executed unless I completely replicate the statement as a separate '!echo' line. dbaccess - -e << EOF !echo DATABASE stores7; DATABASE stores7;-- -- I don't want to have multiple 'echo' statements for this -- one SQL statement, but I do want to see the final version -- of the SQL statement after the values of the environment -- variables get substituted in. -- SELECT customer_num, company, order_num, order_date FROM customer c INNER JOIN orders o ON o.customer_num = c.customer_num WHERE o.order_date BETWEEN "${START_DATE}" AND "${END_DATE}" AND o.ship_date < o.order_date + ${MAX_DAYS} UNITS DAY ORDER BY c.customer_num, o.order_date, o.order_num;EOF Please remind me how to get dbaccess to echo the SQL statements to STDOUT (or even STDERR) as it runs them. Thanks. Mark Collins

Try it without the hyphen after the dbaccess command, e.g. dbaccess -e myfile.sqlThis will display the SQL statements as it executes them - which comes in very useful! ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

I forgot to mention that particular quirk. When I tried 'dbaccess -e myfile.sql', it threw me into interactive mode in dbaccess and displayed " 354: Incorrect database or cursor name format." ------------------------------ mark collins ------------------------------

My recollection was that you had to include the '-' if you wanted to have the DATABASE statement inside the SQL script. Otherwise, dbaccess expects you to specify at the command prompt what database you are trying to connect to. ------------------------------ mark collins ------------------------------

Ugh, my fault. You DO need to supply a database name, e.g. to run against a database called "mydb" - I left it out in my example. dbaccess -e mydb myfile.sqlIf you want to specify the database name in the SQL file, then you can just specify sysmaster on the command line. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

This works for me. It displays the SQL statements when they are executed an if I have the database name at the start of the SQL file, with "database mydb;" dbaccess -e - myfile.sql ------------------------------Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Mike, Thanks for that idea. It had not crossed my mind to try to trick it with sysmaster. Unfortunately, when I run 'dbaccess sysmaster -e myfile.sql', I get no output, and no execution. I then tried 'dbaccess sysmaster < myfile.sql', and I get the results of each SQL statement, but not the actual SQL statements. I'm beginning to wonder whether there is something really wrong with my environment, as I also have a weird error in dbschema and dbexport output. I've got a table with a CHECK constraint involving a couple of columns so that either both are null or both are not null. The SQL that gets created in that case has a syntax error due to a missing space in ' AND (invoicenoIS NOT NULL)'. I've opened a ticket w/IBM on that one, but no response yet. I'm not saying the two situations are related, but it's very odd that something so simple as dbaccess is fighting me. ------------------------------ mark collins ------------------------------

THANK YOU! That was the trick. It seems odd, as I would expect the '-e' to look for its parameter as the next argument in the command line ('-' in this case). But I guess '-' is a shorthand for 'see input file for DATABASE statement'. It would be nice if there was a way to get dbaccess to display a Usage message, as that probably would have been disclosed there. ------------------------------ mark collins ------------------------------

Ok. You figured it out. So, just to clarify: dbaccess - - The first dash says connect to the engine with no database yet. The second dash says "put me into command line mode". To have it echo the SQL it executes you would put the -e before the database name, so, in this case, before the first dash. So: dbaccess -e - - <file.sqlArt ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thanks Art. Just to confirm, if I wanted use a here document for the SQL statements, I'd need to run: dbaccess -e - - << EOFDATABASE stores7; UNLOAD TO cust.unl SELECT * FROM customer; UNLOAD TO ord.unl SELECT * FROM orders;EOF Correct?

I always put a - before the EOF ie -EOF Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC

Paul, Mark: The difference between <<EOF and <<-EOF is that leading tab characters are stripped from the "here document", and since SQL doesn't care about tabs at all, it's mostly a preference thing. Mark: Yes, I use here documents with dbaccess all the time and that is exactly how I do it. Since I write shell scripts mostly with emacs in shell mode, all I have to do is type the "<<" and emacs automatically fills in the EOF at that point and on the next line to end the here document. Art Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Yep, but as most my SQL is buried in scripts it allows me to maintain indentation - except for the very early implementation of bash under Solaris where the dash was just ignored Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC

Paul: Yes, but that was my point. I do the indentations as well. But for an SQL script, one can do the indenting even without the dash since SQL ignores the leading tabs. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thanks Art. So to use a here document, I'd have to do: dbaccess -e - - << EOFdatabase xxx; unload to file select * from table;EOF Correct? ------------------------------ mark collins ------------------------------

Mark: Yes, I use here documents with dbaccess all the time and that is exactly how I do it. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------