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

    Unanswered: call one procedure only when another procedure is finished

    Hello,

    I am wndering if you can help here.

    I have multiple procedures.

    I have created a master procedure that calls all the procedures. I would like the procedures to only be called when the procedure before it has fully finished.
    I have put commit; after every statement in the procedures but it does not appear to help. Is their any advice on this ?

    Code:
    CREATE PROCEDURE CATALOG1.MASTER_PROC()
    SPECIFIC CATALOG1.MASTER_PROC
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL
    
    
    BEGIN NOT ATOMIC
    
    	
    		--DELETES ALL TEMP DATA FROM THE LAST TIME THE ETL WAS RUN.DELETES ALL DATA FROM SCHEMA's AND PARTIAL DATA FROM STAGING.
    		CALL CATALOG1.DELETE_DATA();
    		COMMIT;
    		
    		--IMPORTS DATA FROM DVCH DATABASE, THE IXF FILES FROM THIS DATABASE NEEDS TO BE STORED ON THE IOP20DB NODE /tmp/ DIRECTORY INORDER FOR THE IMPORT TO WORK. 
    		CALL CATALOG1.IMPORT_DATA();
    		COMMIT;
    		
    END@

    Code:
    CREATE PROCEDURE CATALOG1.DELETE_DATA()
    SPECIFIC CATALOG1.DELETE_DATA
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL
    
    
    BEGIN 
    		
    DECLARE SQLCODE INTEGER DEFAULT 0;
    		
    	
    	--	CONTINUING FOR ALL HANDLERS SO THAT IT DOES NOT STOP WHEN SQL EXCEPTION WAS FOUND
    		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; COMMIT;
    		DELETE FROM CATALOG1.MESSAGES2; COMMIT;
    		DELETE FROM CATALOG1.MESSAGES3; COMMIT;
    		
    
    
    END@
    Code:
    
    CREATE PROCEDURE CATALOG1.IMPORT_DATA()
    SPECIFIC CATALOG1.IMPORT_DATA
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL
    
    BEGIN 
    		DECLARE SQLCODE INTEGER DEFAULT 0;
    		DECLARE retcode INTEGER DEFAULT 0;
    
    		
    		
    		
    	IMPORT_RECORDS:BEGIN NOT ATOMIC
    	--	CONTINUING FOR ALL HANDLERS SO THAT IT DOES NOT STOP WHEN SQL EXCEPTION WAS FOUND
    		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; 
    
    		SET retcode = SQLCODE;
    		
    
    		CALL SYSPROC.ADMIN_CMD('IMPORT FROM /tmp/table1.ixf of ixf COMMITCOUNT AUTOMATIC MESSAGES ON SERVER INSERT INTO table1'); COMMIT;
    		CALL SYSPROC.ADMIN_CMD('IMPORT FROM /tmp/table2.ixf of ixf COMMITCOUNT AUTOMATIC MESSAGES ON SERVER INSERT INTO table2'); COMMIT;
    		
    	END;
    	
    	
    END@
    Last edited by alexandra123; 12-17-13 at 06:37.

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    It should work as you want: first DELETE_DATA SP is called, when i is finished, IMPORT_DATA SP is called. There are any reasons that make you think is otherwise?
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Aug 2012
    Posts
    41
    I don't think it works because
    first off I noticed that my delete statements where happening twice. So it was like the master proc was calling
    delete data
    import data
    delete data

    to help overcome this I put a return; at the end of the last procedure call to stop it from looping.

    secondly - I left out some code that basically gets the sqlcode and puts it into a message log table. This code is not resetting itself quick enough before the next import statement or the commit is not working.

    Code:
    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; 
    
    		SET retcode = SQLCODE;
    		
    		SET taskname='IMPORT_TABLE1';
    		CALL SYSPROC.ADMIN_CMD('IMPORT FROM /tmp/TABLE1.ixf of ixf COMMITCOUNT 1000 MESSAGES ON SERVER INSERT INTO TABLE1'); COMMIT;
    		INSERT INTO CATALOG1.MESSAGES VALUES (retcode,taskname,starttime,"type",schemas,commenttext); COMMIT;
    		
    		
    		SET taskname='IMPORT_TABLE2';
    		CALL SYSPROC.ADMIN_CMD('IMPORT FROM /tmp/TABLE2.ixf of ixf COMMITCOUNT 1000 MESSAGES ON SERVER INSERT INTO TABLE2'); COMMIT;
    		INSERT INTO CATALOG1.MESSAGES VALUES (retcode,taskname,starttime,"type",schemas,commenttext); COMMIT;
    		
    		
    		SET taskname='IMPORT_TABLE3';
    		CALL SYSPROC.ADMIN_CMD('IMPORT FROM /tmp/TABLE3.ixf of ixf COMMITCOUNT 1000 MESSAGES ON SERVER INSERT INTO TABLE3'); COMMIT;
    		INSERT INTO CATALOG1.MESSAGES VALUES (retcode,taskname,starttime,"type",schemas,commenttext); COMMIT;
    whats happening above is that if
    table 1 passes, return code is 100
    table 2 fails - return code is 20397
    table 3 passes - but return code is still 20397 so it makes the user think it failed when actually it passed.


    Also - to run the code in db2 control center it takes a couple of minutes. To run it via the scheduler it takes 5 seconds. I don't understand why the difference.

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Quote Originally Posted by alexandra123 View Post
    I don't think it works because
    whats happening above is that if
    table 1 passes, return code is 100
    table 2 fails - return code is 20397
    table 3 passes - but return code is still 20397 so it makes the user think it failed when actually it passed.
    It is normal:
    - when table 2 fails, the continue handler is executed and retcode variable is set to SQLCODE (20397)
    - the code continues with table 3
    - table 3 passes, no continue handler is executed, so retcode variable stays to 20397
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    When you have time, take a look at UTL_FILE module. I've used those routines to create a debugging system that writes anything I want (e.g values of variables) into a text file.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  6. #6
    Join Date
    Aug 2012
    Posts
    41
    thanks very much my head has been wrecked trying to fix it.
    I will look at that and get_diagnostics command to put something more solid in place.

Posting Permissions

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