Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    13

    Unanswered: calling db2 SP within SP (limits?)

    Hi dbforums, hope somebody could help me with this. Im creating a SP that calls 20+ sp inside it. However I have this issue, I cant get it pass calling sp #5. Are there any limit on db2 sp or something like that Im running into? Im using db2 iseries v7r1 and does the coding in I-navigator. Here's what the sp looks like. Thank you so much.


    CREATE PROCEDURE TEST/MAINSP (
    IN PARM1 VARCHAR(10) ,
    IN GETSP1 SMALLINT ,
    IN GETSP2 SMALLINT ,
    IN GETSP3 SMALLINT ,
    IN GETSP4 SMALLINT ,
    IN GETSP5 SMALLINT ,
    IN GETSP6 SMALLINT ,
    IN GETSP7 SMALLINT ,
    IN GETSP8 SMALLINT ,
    IN GETSP9 SMALLINT,
    IN GETSP10 SMALLINT )
    DYNAMIC RESULT SETS 10
    LANGUAGE SQL
    SPECIFIC TEST/MAINSP

    BEGIN

    DECLARE GLOBAL TEMPORARY TABLE SESSION / TEMPGETSP1
    ( FIELD1 VARCHAR ( 10 ) , FIELD2 VARCHAR ( 256 ) )
    WITH REPLACE
    ON COMMIT PRESERVE ROWS
    NOT LOGGED ;

    DECLARE GLOBAL TEMPORARY TABLE SESSION / TEMPGETSP2
    ( FIELD1 VARCHAR ( 10 ) , FIELD2 VARCHAR ( 256 ) )
    WITH REPLACE
    ON COMMIT PRESERVE ROWS
    NOT LOGGED ;

    DECLARE GLOBAL TEMPORARY TABLE SESSION / TEMPGETSP3
    ( FIELD1 VARCHAR ( 10 ) , FIELD2 VARCHAR ( 256 ) )
    WITH REPLACE
    ON COMMIT PRESERVE ROWS
    NOT LOGGED ;

    DECLARE GLOBAL TEMPORARY TABLE SESSION / TEMPGETSP4
    ( FIELD1 VARCHAR ( 10 ) , FIELD2 VARCHAR ( 256 ) )
    WITH REPLACE
    ON COMMIT PRESERVE ROWS
    NOT LOGGED ;

    DECLARE GLOBAL TEMPORARY TABLE SESSION / TEMPGETSP5
    ( FIELD1 VARCHAR ( 10 ) , FIELD2 VARCHAR ( 256 ) )
    WITH REPLACE
    ON COMMIT PRESERVE ROWS
    NOT LOGGED ;

    P1 : BEGIN

    DECLARE DONE INT DEFAULT 0 ;
    DECLARE VFIELD1 VARCHAR ( 10 ) ;
    DECLARE VFIELD2 VARCHAR ( 256 ) ;
    DECLARE RESULTGETSP1 RESULT_SET_LOCATOR VARYING ;
    DECLARE RESULTGETSP2 RESULT_SET_LOCATOR VARYING ;
    DECLARE RESULTGETSP3 RESULT_SET_LOCATOR VARYING ;
    DECLARE RESULTGETSP4 RESULT_SET_LOCATOR VARYING ;
    DECLARE RESULTGETSP5 RESULT_SET_LOCATOR VARYING ;
    DECLARE CGETSP1 CURSOR WITH RETURN FOR
    SELECT * FROM SESSION / TEMPGETSP1 ;
    DECLARE CGETSP2 CURSOR WITH RETURN FOR
    SELECT * FROM SESSION / TEMPGETSP2 ;
    DECLARE CGETSP3 CURSOR WITH RETURN FOR
    SELECT * FROM SESSION / TEMPGETSP3 ;
    DECLARE CGETSP4 CURSOR WITH RETURN FOR
    SELECT * FROM SESSION / TEMPGETSP4 ;
    DECLARE CGETSP5 CURSOR WITH RETURN FOR
    SELECT * FROM SESSION / TEMPGETSP5 ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1 ;

    IF GETSP1 = 1 THEN

    CALL SP1 ( ) ;
    ASSOCIATE RESULT SET LOCATORS ( RESULTGETSP1 )
    WITH PROCEDURE SP1 ( ) ;
    ALLOCATE REFGETSP1 CURSOR FOR RESULT SET RESULTGETSP1 ;

    CUR1_LOOP : WHILE ( DONE = 0 ) DO

    FETCH FROM REFGETSP1 INTO VFIELD1 , VFIELD2 ;

    IF DONE = 1 THEN
    LEAVE CUR1_LOOP ;
    END IF ;

    INSERT INTO SESSION / TEMPGETSP1
    VALUES ( VFIELD1 , VFIELD2 ) ;

    END WHILE ;

    CLOSE REFGETSP1 ;
    END IF ;
    IF GETSP2 = 1 THEN

    CALL SP2 ( ) ;
    ASSOCIATE RESULT SET LOCATORS ( RESULTGETSP2 )
    WITH PROCEDURE SP2 ( ) ;
    ALLOCATE REFGETSP2 CURSOR FOR RESULT SET RESULTGETSP2 ;

    CUR2_LOOP : WHILE ( DONE = 0 ) DO

    FETCH FROM REFGETSP2 INTO VFIELD1 , VFIELD2 ;

    IF DONE = 1 THEN
    LEAVE CUR2_LOOP ;
    END IF ;

    INSERT INTO SESSION / TEMPGETSP2
    VALUES ( VFIELD1 , VFIELD2 ) ;

    END WHILE ;

    CLOSE REFGETSP2 ;
    END IF ;
    IF GETSP3 = 1 THEN

    CALL SP3 ( ) ;
    ASSOCIATE RESULT SET LOCATORS ( RESULTGETSP3 )
    WITH PROCEDURE SP3 ( ) ;
    ALLOCATE REFGETSP3 CURSOR FOR RESULT SET RESULTGETSP3 ;

    CUR3_LOOP : WHILE ( DONE = 0 ) DO

    FETCH FROM REFGETSP3 INTO VFIELD1 , VFIELD2 ;

    IF DONE = 1 THEN
    LEAVE CUR3_LOOP ;
    END IF ;

    INSERT INTO SESSION / TEMPGETSP3
    VALUES ( VFIELD1 , VFIELD2 ) ;

    END WHILE ;

    CLOSE REFGETSP3 ;
    END IF ;
    IF GETSP4 = 1 THEN

    CALL SP4 ( ) ;
    ASSOCIATE RESULT SET LOCATORS ( RESULTGETSP4 )
    WITH PROCEDURE SP4 ( ) ;
    ALLOCATE REFGETSP4 CURSOR FOR RESULT SET RESULTGETSP4 ;

    CUR4_LOOP : WHILE ( DONE = 0 ) DO

    FETCH FROM REFGETSP4 INTO VFIELD1 , VFIELD2 ;

    IF DONE = 1 THEN
    LEAVE CUR4_LOOP ;
    END IF ;

    INSERT INTO SESSION / TEMPGETSP4
    VALUES ( VFIELD1 , VFIELD2 ) ;

    END WHILE ;

    CLOSE REFGETSP4 ;

    IF GETSP5 = 1 THEN

    CALL SP5 ( ) ;
    ASSOCIATE RESULT SET LOCATORS ( RESULTGETSP5 )
    WITH PROCEDURE SP5 ( ) ;
    ALLOCATE REFGETSP5 CURSOR FOR RESULT SET RESULTGETSP5 ;

    CUR5_LOOP : WHILE ( DONE = 0 ) DO

    FETCH FROM REFGETSP5 INTO VFIELD1 , VFIELD2 ;

    IF DONE = 1 THEN
    LEAVE CUR5_LOOP ;
    END IF ;

    INSERT INTO SESSION / TEMPGETSP5
    VALUES ( VFIELD1 , VFIELD2 ) ;

    END WHILE ;

    CLOSE REFGETSP5 ;

    END IF ;
    OPEN CGETSP1 ;
    OPEN CGETSP2 ;
    OPEN CGETSP3 ;
    OPEN CGETSP4 ;
    OPEN CGETSP5 ;
    END P1 ;

    END ;


    and generate this error message

    SQL State: 42904
    Vendor Code: -7032
    Message: [SQL7032] SQL procedure, function, trigger, or variable MAINSP in TEST not created. Cause . . . . . : SQL procedure, function, trigger, or variable MAINSP in TEST was not created. The compile was not successful. SQL creates an SQL procedure, function, trigger, or variable as a C program that contains embedded SQL. Errors not found during the initial parsing of the CREATE PROCEDURE, ALTER PROCEDURE, CREATE FUNCTION, ALTER FUNCTION, CREATE TRIGGER. or CREATE VARIABLE statement can be found during the precompile. Recovery . . . : If a compile error occurred, see the appropriate listing in QSYSPRT. If the SQL precompile failed, there is always a listing with the error. If the C compile failed, the listing is only created if requested. Specify SET OPTION OUTPUT=*PRINT prior to the routine body in the CREATE PROCEDURE, CREATE FUNCTION, or CREATE TRIGGER statement if listings are required.

    Processing ended because the highlighted statement did not complete successfully


    If i get rid of the code inside if getsp5 =1, I can create/compile the sp successfully. I already tried exchanging the codes and still having error on the last sp that is called. I really need it to call more than 20 other sp on it. Again thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Format your code by appropriate line spacings/indentions.
    I thought you might forgot "END IF ;" before "IF GETSP5 = 1 THEN".

  3. #3
    Join Date
    Jul 2012
    Posts
    13
    thanks tonkuma.

    I'd figured out a work around. Just declared CURSOR WITH RETURN TO CLIENT on the child sp's and call then directly in the main sp..something like this

    MAIN SP
    BEGIN
    call sp1;
    call sp2;
    call sp3;

    END

    thanks dbforum

Tags for this Thread

Posting Permissions

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