0

I noticed that importing data that truncates, only raises a warning. T thought it would be simple to just catch that warning and signal an error via a handler, but I don't seem to be able to do that. Any suggestions?

CREATE OR REPLACE PROCEDURE FILE_IMPORT_HANDLER(filename VARCHAR(255), tablename 
VARCHAR(255))
LANGUAGE SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '22001'
        SIGNAL SQLSTATE '75002'
            SET MESSAGE_TEXT = '...';

    CALL ADMIN_CMD('import from ' || filename || ' of del insert into ' || tablename);
END @

I would like to fail hard if 22001 is encountered. In a sense, raise the severity for data truncation during the import

2 Answers 2

0

Set the WARNINGCOUNT 1 option:

WARNINGCOUNT n

Stops the import operation after n warnings. Set this parameter if no warnings are expected, but verification that the correct file and table are being used is required. If the import file or the target table is specified incorrectly, the import utility generates a warning for each row that it attempts to import, which causes the import to fail. If n is zero, or this option is not specified, the import operation continues regardless of the number of warnings issued.

(emphasis mine). In that case you won't even need your handler.


A good example of an X-Y Problem™, by the way. You don't actually need to "raise severity for warnings", you just need IMPORT to terminate upon the first warning.

2
  • Thanks, but I would like to signal an error so that the execution of consecutive stmts stops. Commented Jan 25 at 13:08
  • I'm guessing that, when IMPORT terminates after the first warning, ADMIN_CMD might exit with a particular SQLSTATE, which you can then handle. It's probably not 22001 though. "If the execution of the administrative command is not successful, an SQL20397W warning message is returned by the ADMIN_CMD".
    – mustaccio
    Commented Jan 25 at 14:49
0

This appears to do the trick:

CREATE OR REPLACE PROCEDURE FILE_IMPORT_HANDLER(filename VARCHAR(255), tablename VARCHAR(255))
LANGUAGE SQL
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLWARNING, SQLEXCEPTION -- SQLSTATE '22001'
        SIGNAL SQLSTATE '75000'
                SET message_text = 'Import failed';

    CALL ADMIN_CMD('import from ' || filename || ' of del warningcount 1 insert into ' || tablename);
END @


db2 "call file_import_handler('/tmp/lelle.txt', 'lelle.test')"
SQL0438N  Application raised error or warning with diagnostic text: "Import failed".  SQLSTATE=75000

Not sure it is possible to have a finer granularity in the handler. If I run:

db2 -e s "call admin_cmd('import ...)"

with a file that contains the error I'm trying to catch, an empty sqlstate is returned.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.