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

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

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

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

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

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

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

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

Thanks Art.

Just to confirm, if I wanted use a here document for the SQL statements, I'd need to run:

dbaccess -e - - << EOF

DATABASE stores7;

UNLOAD TO cust.unl SELECT * FROM customer;

UNLOAD TO ord.unl SELECT * FROM orders;

EOF

Correct?

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

Thanks Art.

Just to confirm, if I wanted use a here document for the SQL statements, I'd need to run:

dbaccess -e - - << EOF

DATABASE stores7;

UNLOAD TO cust.unl SELECT * FROM customer;

UNLOAD TO ord.unl SELECT * FROM orders;

EOF

Correct?

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

Thanks Art.

Just to confirm, if I wanted use a here document for the SQL statements, I'd need to run:

dbaccess -e - - << EOF

DATABASE stores7;

UNLOAD TO cust.unl SELECT * FROM customer;

UNLOAD TO ord.unl SELECT * FROM orders;

EOF

Correct?

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

Thanks Art.

Just to confirm, if I wanted use a here document for the SQL statements, I'd need to run:

dbaccess -e - - << EOF

DATABASE stores7;

UNLOAD TO cust.unl SELECT * FROM customer;

UNLOAD TO ord.unl SELECT * FROM orders;

EOF

Correct?

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

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

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

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