-1

I want to put the following SQL statements into a shell script using only one variable that I can mail to 3 people as part of my daily health checks on our client side. Please help me out with the ideas here. I also want to add 2 commands to check the diaglog and space. 

!df -h;
!db2diag -f;


SELECT  DB_STATUS as "DB Status",
        LAST_BACKUP as "Last Backup",
        APPLS_CUR_CONS "No. of Current Connections"
  FROM  TABLE(MON_GET_DATABASE(-2));

 select
    NUM_INDOUBT_TRANS as "Indoubt Transactions"
  from table(mon_get_transaction_log(-2));

 SELECT lock_name,
       hld_member,
       lock_status,
       hld_application_handle
  FROM TABLE (MON_GET_APPL_LOCKWAIT(NULL, -2));

 select
    varchar(tbsp_name, 30) as tbsp_name,
    varchar(tbsp_state, 40) as tbsp_state
  from table(mon_get_tablespace('',-2));

So far I have the following:

connect to DBNAME

Health=`db2 -x "SELECT  DB_STATUS as "DB Status",
        LAST_BACKUP as "Last Backup",
        APPLS_CUR_CONS "No. of Current Connections"
  FROM  TABLE(MON_GET_DATABASE(-2))"`
echo $Health

mail -s "health Check" mail@who.co.za <<< "$Health"

Getting the below error. Even when taking out the " " I still receive the same errors. Please help

SQL0104N An unexpected token "Status" was found following "ECT DB_STATUS as DB". Expected tokens may include: "". SQLSTATE=42601

mustaccio
  • 18,234
  • 16
  • 48
  • 57
user7611057
  • 1
  • 2
  • Consider a different design. Arrange for the health check script to run scheduled and create an output file. Then separately email that output file to recipients. For the shell-script, choose either to have the SQL inside the shell-script, or alternately put the SQL into a text file and get the shell-script to run the Db2 clp on that text-file (db2 -o output-file -tvf text-file.sql ). If you put the SQL into a text file you avoid quoting issues which may cause the errors you see. If you put the SQL inline into the shell-script then you must take greater care with quotes. – mao Sep 11 '17 at 12:32
  • You need to escape double quotes around "DB Status" inside the double-quoted SQL statement. – mustaccio Sep 11 '17 at 16:59

1 Answers1

0

If you are using 'db2 -x' then the headings will not be passed to $Health so you could just use:

db2 -x 'SELECT DB_STATUS, LAST_BACKUP, APPLS_CUR_CONS FROM TABLE(MON_GET_DATABASE(-2))'

Otherwise as mustaccio said the double quotes are an issue:

db2 'SELECT DB_STATUS as "DB Status", LAST_BACKUP as "Last Backup", APPLS_CUR_CONS "No. of Current Connections" FROM TABLE(MON_GET_DATABASE(-2))'

dgodson
  • 5
  • 4