If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: SQL Stuff in a DB2 crontab not working

Hi - I am v-e-r-y new to the DB2 environment on AIX, and have rather had this problem pushed on to myself...anyway - I can explain:

I have an SQL statement that I can merrily "paste" into a DB2 command line on an AIX box:

db2 "select substr(companyid,1,12) as "COMPANY", substr(loginid,1,10) as "LOGIN", substr(cmpfunction,1,35) as "COMPANY_FUNCTION", date(updatedttm) as "DATE", time(updatedttm) as "TIME" FROM sonedba.vfmaudit where (((cmpfunction NOT LIKE 'OBO%') and (companyid NOT LIKE 'N/A')) and (date(updatedttm) > (select max(date(updatedttm) - 7 DAYS) from sonedba.vfmaudit))) order by companyid, loginid, updatedttm, cmpfunction" | mailx –s "ACTIVITY SUMMARY" Support@bacb.co.uk

..now this is fine in a command line, but when I paste it into a crontab for the db user I get a message stating that the database connection does not exist (I am of course prefixing my statement in the crontab with all the neccessary stuff "0 0 * * 5 . /home/db2inst0/.profile && db2 connect to BACBDB01 && db2...")

I when I remove the statements: "where (((cmpfunction NOT LIKE 'OBO%') and (companyid NOT LIKE 'N/A'))" from the crontab, the script works fine.

I am no AIX expert, but you need to only have the double quotes(") at the beginning and the end of the SQL statement:

db2 "select substr(companyid,1,12) as COMPANY, substr(loginid,1,10) as LOGIN, substr(cmpfunction,1,35) as COMPANY_FUNCTION, date(updatedttm) as DATE, time(updatedttm) as TIME FROM sonedba.vfmaudit where (((cmpfunction NOT LIKE 'OBO%') and (companyid NOT LIKE 'N/A')) and (date(updatedttm) > (select max(date(updatedttm) - 7 DAYS) from sonedba.vfmaudit))) order by companyid, loginid, updatedttm, cmpfunction" | mailx –s "ACTIVITY SUMMARY"

Try doing this. Make a script file that contains all the DB2 commands you are trying to execute with cron. It should look like this (note the semicolons( at the end of each command:

-------------
connect to bacbdb;

select substr(companyid,1,12) as COMPANY, substr(loginid,1,10) as LOGIN, substr(cmpfunction,1,35) as COMPANY_FUNCTION, date(updatedttm) as DATE, time(updatedttm) as TIME FROM sonedba.vfmaudit where (((cmpfunction NOT LIKE 'OBO%') and (companyid NOT LIKE 'N/A')) and (date(updatedttm) > (select max(date(updatedttm) - 7 DAYS) from sonedba.vfmaudit))) order by companyid, loginid, updatedttm, cmpfunction ;

---------------

Then create another executable file that has this in it:

---------------------
. /home/db2inst0/.profile

db2 -tvsf myscript.sql

-----------------------

where myscript.sql is the name of the first file. Then change your crontab to execute the second script.

Further to this (and to show I am trying to work this out!): When I remove the "NOT Like 'OBO%'" statement, the cronjob works - this issue is therefore now only related to the statement "NOT Like 'OBO%'" in my crontab. More specifically, it doesn't seem to like the "%" percentage sign.