Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
5 replies Latest Post - ‏2012-12-14T13:35:48Z by suncarolina
suncarolina
suncarolina
20 Posts
ACCEPTED ANSWER

Pinned topic db2 command line to log only errors, and continue processing

‏2012-12-11T22:27:42Z |
Running scripts that insert data into DB2 LUW 9.7 database. If there is an error in the script, how can the script keep processing, but only log the errors into a log file? I know the -s option will stop processing and log the error. But I don't want to stop processing if there an error. And I don't want to log every message. I just want to log only the error, and keep processing.

I've tried so many differnet combinations with no success. If command line won't work, is there another method?

db2 -tvf FILE1.SQL -l FILE1.RPT

where FILE1.SQL contains thousands of inserts, and where FILE1.RPT is the output log file.

INSERT INTO TABLE1 VALUES (1,2,3);
INSERT INTO TABLE1 VALUES (4,5,6);
INSERT INTO TABLE1 VALUES (7,8,9);
Updated on 2012-12-14T13:35:48Z at 2012-12-14T13:35:48Z by suncarolina
  • nivanov1
    nivanov1
    231 Posts
    ACCEPTED ANSWER

    Re: db2 command line to log only errors, and continue processing

    ‏2012-12-12T14:10:45Z  in response to suncarolina
    One approach would be to write an SQL PL block with a signal handler and use DBMS_OUTPUT.PUT_LINE() to print out errors.
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: db2 command line to log only errors, and continue processing

    ‏2012-12-12T20:04:00Z  in response to suncarolina
    Hi,

    You could put the inserts into an annonymous block, with a continue handler that inserts the error in a table. At the end, you can export the content of that table.

    You could also use variables for the inserted values, and you use those variables to write the error message in the table.

    Here, the simplest example.

    
    begin atomic DECLARE CONTINUE HANDLER FOR SQLEXCEPTION INSERT into logs values (xxxxx);   INSERT INTO TABLE1 VALUES (1,2,3); INSERT INTO TABLE1 VALUES (4,5,6); INSERT INTO TABLE1 VALUES (7,8,9); end@   db2 -td@ -vf xxx.sql
    


    @nivanov1 DBMS_OUTPUT.PUT_LINE() is a good option, however it does not work in Express-C edition.
    • SystemAdmin
      SystemAdmin
      17917 Posts
      ACCEPTED ANSWER

      Re: db2 command line to log only errors, and continue processing

      ‏2012-12-13T01:30:01Z  in response to SystemAdmin
      In our shop, we load staging tables, using the LOAD command and exception tables (which captures more data than an insert statement, which is easier to interpret if there are errors). Once it's successfully loaded, we use the 'insert into select from' statement to load our primary table. This works well and gives us a chance to review records before they are inserted into our primary table.

      Hope this helps,
      Randy Wilson
      BlueCross BlueShield of TN
      • bluey01
        bluey01
        50 Posts
        ACCEPTED ANSWER

        Re: db2 command line to log only errors, and continue processing

        ‏2012-12-14T02:45:38Z  in response to SystemAdmin
        You could use DataStudio for the insert script. There is an option to Stop or Continue on an error. Then you review the results log for any errors. have used this for a few tasks and it works well.
        • suncarolina
          suncarolina
          20 Posts
          ACCEPTED ANSWER

          Re: db2 command line to log only errors, and continue processing

          ‏2012-12-14T13:35:48Z  in response to bluey01
          Thanks for all your replies. I opened a PMR and asked this question, and unfortunatley, logging only errors is not possible with just the db2 command line options.

          The problem we have is our log file is about a million lines and growing. We search the log file for DB2?????E with DB2 in position 0-2 and E 7th position. Some think we only have to search for "SQLSTATE" but not all "SQLSTATE" are negative errors.

          So I was looking for how to run these massive number of scripts and only log errors, but keep processing the rest of the statements.

          It sounds like I need to find some sample code.

          Btw, this is db2 LUW on Windows, but I could possibly need java code, to run on Unix systems.