Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    41

    Unanswered: db2 procedure fails when ixf file is empty

    hello,

    Looking for some advice here.

    I created a procedure that imports many files into many tables.
    I then created a db2 scheduler to run it every 2 hours.

    The files need to be populated in order.

    All the ixf files are stored in a /tmp/ directory

    If all the ixf files have data the scheduler will complete until the end.
    if any of the ixf files contain no data when it reaches the first ixf file that has no data it does not continue onto the next command.

    I verified this by adding data to an empty ixf file and ran the task again and it completed past the new file and stopped at another file that was empty.

    Is their a way around this ?

    The scheduler comes back as success =0 even though the procedure has stopped half way through it


    Example
    Code:
    BEGIN
    CALL SYSPROC.ADMIN_CMD('IMPORT FROM /tmp/table1.ixf of ixf COMMITCOUNT 1000 MESSAGES ON SERVER INSERT INTO table1');
    CALL SYSPROC.ADMIN_CMD('IMPORT FROM /tmp/table2.ixf of ixf COMMITCOUNT 1000 MESSAGES ON SERVER INSERT INTO table2');
    CALL SYSPROC.ADMIN_CMD('IMPORT FROM /tmp/table3.ixf of ixf COMMITCOUNT 1000 MESSAGES ON SERVER INSERT INTO table3');
    CALL SYSPROC.ADMIN_CMD('IMPORT FROM /tmp/table4.ixf of ixf COMMITCOUNT 1000 MESSAGES ON SERVER INSERT INTO table4');
    CALL SYSPROC.ADMIN_CMD('IMPORT FROM /tmp/table5.ixf of ixf COMMITCOUNT 1000 MESSAGES ON SERVER INSERT INTO table5');
    CALL SYSPROC.ADMIN_CMD('IMPORT FROM /tmp/table6.ixf of ixf COMMITCOUNT 1000 MESSAGES ON SERVER INSERT INTO table6');
    COMMIT;
    END@
    when I run the create procedure command I dont get any message numbers that I can use to retrieve the message from the procedure to see why it failed. You only seem to get message numbers when a small subset of procedures is created....is their a way around this aswel ?

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I think that SYSPROC.ADMIN_CMD fails when the import file is empty. If that's the case, you may define a CONTINUE error handler.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Aug 2012
    Posts
    41
    thanks Aflorin27 that worked for me.


    On setting up the conditional handler - I wanted to print all fails for each export command to a table. For some reason it is not working properly.
    I tried the
    if command
    and the do while command

    Can you someone take a quick look at my code and tell me where it is wrong ?
    Code:
    
    
    BEGIN 
    		DECLARE SQLCODE INTEGER DEFAULT 0;
    		DECLARE retcode INTEGER DEFAULT 0;
    		DECLARE taskname VARCHAR(1024);
    		DECLARE starttime timestamp;
    		
    	A: BEGIN	
    		DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET retcode = SQLCODE;
    		DECLARE CONTINUE HANDLER FOR SQLWARNING SET retcode = SQLCODE; 
    		DECLARE CONTINUE HANDLER FOR NOT FOUND SET retcode = SQLCODE; 
    		
    		DELETE FROM CATALOG1.MESSAGES;
          
    		DECLARE GLOBAL TEMPORARY TABLE SESSION.EXPORT_TEMP(
    		SQLCODES INT,
    		TASKNAME VARCHAR(1024),
    		STARTTIME TIMESTAMP);
    	
    	   
    		
    		BEGIN
    		SET retcode = SQLCODE;
    		SET taskname='starting_export';
    		SET starttime=current timestamp;
    		END;
    		
    
    		CALL SYSPROC.ADMIN_CMD('EXPORT TO /tmp/table1.ixf of ixf MESSAGES ON SERVER SELECT * FROM table1');
    		SET taskname='table1';
    		
    		CALL SYSPROC.ADMIN_CMD('EXPORT TO /tmp/table2.ixf of ixf MESSAGES ON SERVER SELECT * FROM table2');
    		SET taskname='table2';
    		
    		SET taskname='exporting complete';
    		COMMIT;
    	
    	B: 	BEGIN
    		
    		WHILE(retcode=20397)
    		DO
    		INSERT INTO CATALOG1.MESSAGES VALUES (retcode,taskname,starttime);
    		INSERT INTO SESSION.EXPORT_TEMP VALUES (retcode,taskname,starttime);
    		END WHILE;
    		CALL SYSPROC.ADMIN_CMD('EXPORT TO /tmp/EXPORT_RESULTS.csv of del SELECT * FROM SESSION.EXPORT_TEMP');
    		END;
    	END ;
    	
    	
    	
    END@

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I cannot tell what's wrong with your code, as I'm not a DB2 SQL expert. But I can tell how it works for me:

    Code:
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
       -- error logging mechanism
    END
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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