Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Posts
    6

    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.

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

    Thanks in advance

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Dec 2004
    Posts
    6
    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Dec 2004
    Posts
    6
    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.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •