Results 1 to 7 of 7
  1. #1
    Join Date
    May 2012
    Posts
    3

    Unanswered: db2 sql statement error

    Hi,

    I have the following sql, when I execute it I am getting the below errors respectively, but the same script works fine in Oracle

    [CODE]
    db2 => delete from rep_audit_severity where servername||serverserial in ( select servername||serverserial from reporter_status where lastmodified<SYSDATE-30)\

    db2 => delete from rep_audit_severity where servername||serverserial in ( select servername||serverserial from reporter_status where lastmodified<SYSDATE-30)

    [CODE]

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0007N The character "\" following "modified<SYSDATE-30)" is not valid.
    SQLSTATE=42601


    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0182N An expression with a datetime value or a labeled duration is not
    valid. SQLSTATE=42816

    Basically what I am trying to do is "-- delete the number of days back I want to go, to delete the alerts--"

    Can someone help me?

    Thanks
    Din
    Last edited by dbashyam; 05-15-12 at 01:22. Reason: code

  2. #2
    Join Date
    Jul 2004
    Posts
    306
    You haven't said what version of DB2 you're using (the latest ones give more and more Oracle compatibility) but on first inspection I'd say try changing SYSDATE to Current Date or Current Timestamp.

  3. #3
    Join Date
    May 2012
    Posts
    3
    Hi,

    I am using DB2 9.7.3

    Thanks,
    Din

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Also, remove the "\" at the end of line 1.
    And for performance reasons I would avoid the "concat":
    Code:
    db2 => delete from rep_audit_severity where (servername,serverserial) in ( select servername,serverserial from reporter_status where lastmodified< CURRENT DATE - 30 days)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    May 2012
    Posts
    3
    Hi,

    Thanks for your replies.

    I have the following sql file

    Code:
    delete from rep_audit_severity
    where servername||serverserial in ( select servername||serverserial from
    reporter_status where lastmodified < CURRENT DATE - 30 days)
    commit;
    exit;
    and I have the following shell script to call this sql

    Code:
    #!/bin/bash
    set -x
    
    User=xxxxxx
    
    Passwd=xxxxx
    
    Hostname="$(hostname -s)"
    
    Server_instance=$Hostname"_"$User
    
    working_dir=$PWD
    
    logfile="TABLE_SIZES_$Server_instance$TIMESTAMP.log"
    
    touch $working_dir/$logfile
    
    >$working_dir/$logfile
    
    i=0;
    for i in `db2 list db directory | grep 'Database name' | grep -i 'WAREHOUS' | awk '{print $4}'`;
    do
    db2 CONNECT TO $i user $User using $Passwd >> $working_dir/$logfile;
    check=0
    while [ $check == 0  ]; do
    db2 -td@ -vf /home/tivadmin/scripts/rep_de1.sql >> $working_dir/$logfile;
    #db2 terminate >> $working_dir/$logfile;
    if [ $? == "0" ]; then
    check=1
    else
    /bin/sleep 20
    fi
    done
    db2 terminate >> $working_dir/$logfile;
    done
    But I get the following error

    DB21007E End of file reached while reading the command.

    What could be the reason?

    Thanks
    Din

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Your code has "db2 -td@ ..."
    but your SQL file has a delimiter of semicolon (

    The statement delimiter need to be consistent between the .sql file and the invoking script!

    Also, in your .sql file you should remove the 'exit' statement because that is not correct syntax in that location.

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    I'd again suggest "," instead of "||":
    Code:
    delete from rep_audit_severity
    where (servername,serverserial) in
       ( select servername,serverserial
         from reporter_status
         where lastmodified < CURRENT DATE - 30 days)
    @
    (COMMIT is not needed since by default the db2 CLI is in auto-commit mode.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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