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.

Please, does anybody have any ideas / workarounds...?

Thanks in advance

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"

Andy

Thanks for that advice - saves me putting double quotes all over my statements at least!

I still get the error message when including the "NOT LIKE" conditions in my Crontab, although I don't get an error when pasting the statement into a command line...

Your "cron" job executed on bacbeds on Tue Jun 5 13:25:00 BST 2007
. /home/db2inst0/.profile && db2 connect to bacbdb && db2 "select substr(company
id,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 soned
ba.vfmaudit where (((cmpfunction NOT LIKE 'OBO


produced the following output:


Database Connection Information

Database server = DB2/6000 8.1.0
SQL authorization ID = DB2INST0
Local database alias = BACBDB

SQL1024N A database connection does not exist. SQLSTATE=08003


************************************************** ***************

Any more advice would be gratefully recevied - thanks

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.

Andy

Thanks for your reply - which I will investigate this afternoon.

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.

Like I said, I am not an AIX expert, but AIX is probably thinking that the % is for it to process and is not passing it to DB2. By putting the DB2 stuff into a SQL script, that will not happen.

Andy