Results 1 to 10 of 10
  1. #1
    Join Date
    May 2009
    Posts
    16

    Unanswered: Loop execution incorrect for cursor declared in DB2 Stored procedure

    Hi
    I am getting SQL out as - 284, for the following declared cursor, I have declared a cursor and feteched the same and inserted teh results in a temp table.

    further did proccessing form retriving from temp tables like manipulation on temp table data.

    but I am not getting any output form cursor
    CREATE PROCEDURE SP_Clean_tab
    (IN CLEAN_PERIOD INTEGER,
    OUT SQLSTATE_OUT CHAR(5),
    OUT SQLCODE_OUT INTEGER )
    DYNAMIC RESULT SETS 2
    LANGUAGE SQL
    P1: BEGIN
    -- Declare variables
    DECLARE CLEAN_PERIOD_VAR INTEGER;
    DECLARE CLEAN_POID BIGINT;
    DECLARE CLEAN_PACKID BIGINT;
    DECLARE VAR_INVOICE_ID VARCHAR(20);
    DECLARE END_TABLE INT DEFAULT 0;
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INT DEFAULT 0;
    -- Declare cursors
    DECLARE C1 CURSOR WITH HOLD WITH RETURN FOR
    SELECT PO_UNI_ID, PACK_UNI_ID
    FROM INS.INS_PO
    WHERE (TIMESTAMPDIFF(64,CHAR((CURRENT_TIMESTAMP) - (CHECKED_DTTM)))) > (MAX(12, CLEAN_PERIOD_VAR))
    AND CHECKED_DTTM is NOT NULL
    AND STATUS_CODE = 'CHECKED ‘;
    -- Declare handler
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT FROM SYSIBM.SYSDUMMY1;

    DECLARE Continue HANDLER FOR NOT FOUND
    Begin
    SET END_TABLE = 1;
    End;

    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_CLEAN
    (DEL_POID BIGINT, DEL_PACKID INTEGER, DEL_INVOICE_ID VARCHAR(20), DEL_PERIOD INTEGER)IN TPACE1 ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED;
    -- INITIALIZE OUTPUT SQL PAAMAETERS
    SET CLEAN_PERIOD_VAR = CLEAN_PERIOD;
    OPEN C1;
    L1: REPEAT
    FETCH C1 INTO CLEAN_POID, CLEAN_PACKID;
    IF SQLCODE = 0 THEN
    -- POPULATING TEMP TABLE FOR FURTHER PROCESSING.
    SELECT INVOICE_ID INTO VAR_INVOICE_ID FROM INS.INS_PO_CLASS_ELEMENTS WHERE PO_UNI_ID = CLEAN_POID;
    INSERT INTO SESSION.TEMP_CLEAN VALUES (CLEAN_POID, CLEAN_PACKID, VAR_MRN, VAR_INVOICE_ID, VAR_UPLOAD_ID, VAR_UPLOAD_SN, CLEAN_PERIOD_VAR);
    COMMIT;
    END IF;
    UNTIL END_TABLE = 1
    END REPEAT L1;
    CLOSE C1;

    --CLEANS THE FETCHED POS FROM INS_PO TABLE.
    DELETE FROM INS.INS_PO WHERE PO_UNI_ID IN (SELECT DEL_POID FROM SESSION.TEMP_CLEAN);
    -- CLEAN CORRESPONDING PACK_UNI_ID FROM INS_PACK_PO TABLE
    DELETE FROM INS.INS_PACK_PO A
    WHERE (0 = (SELECT COUNT(*) FROM INS.INS_PO C WHERE C.PACK_UNI_ID = A.pack_UNI_ID))
    AND A.PACK_UNI_ID IN (SELECT DEL_PACKID FROM SESSION.TEMP_CLEAN);


    SET SQLSTATE_OUT = SQLSTATE;
    SET SQLCODE_OUT = SQLCODE;
    END P1

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Read the error message:

    Code:
    SQL0284N  The table was not created because the table space
          "<tablespace-name>" following the clause "<clause>" is a
          "<tablespace-type>" table space.
    
    Explanation:
    
    The CREATE TABLE or DECLARE GLOBAL TEMPORARY TABLE statement specified a
    table space named "<tablespace-name>" following the clause "<clause>"
    that is not a valid type of table space for this clause.
    
    This can occur in the following situations:
    *  For regular tables, "<tablespace-name>" was specified on the IN
       clause and the table space is not a REGULAR or LARGE table space.
    *  For declared temporary tables, "<tablespace-name>" was specified on
       the IN clause and the table space is not a USER TEMPORARY table
       space.
    *  "<tablespace-name>" was specified on the LONG IN clause and the table
       space is not a LARGE, managed by database table space.
    *  "<tablespace-name>" was specified on the INDEX IN clause and the
       table space is not a REGULAR or LARGE, managed by database, table
       space.
    
    User response:
    
    Correct the CREATE TABLE statement to specify a table space with the
    correct type for the "<clause>" clause.
    
     sqlcode: -284
    You are specifying the wrong type of tablespace for the temp table.

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I'm wondering: why don't you do a simple INSERT INTO ... SELECT ... ?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2009
    Posts
    16
    Thanks for your effots and time.

    I checked and re ran the procedure with table space as Tempspace2 which is created, but still I am getting same sql code -284 and sql state as 42838.

    And I am not using Insert Select combined because , I have to select few columns from one table and few from another table for inserting into teh table.

    Is there a some way I can debug my procedure or we canuse Signal.

    Thanks in advance.
    Regards
    Nee

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Nee, Unless I am missing something, you are defining TEMP_CLEAN with 4 columns. But your Insert into TEMP_CLEAN has 7 values.

    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_CLEAN
    (DEL_POID BIGINT
    ,DEL_PACKID INTEGER
    ,DEL_INVOICE_ID VARCHAR(20)
    ,DEL_PERIOD INTEGER)
    IN TPACE1 ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED;

    INSERT INTO SESSION.TEMP_CLEAN
    VALUES
    (CLEAN_POID
    ,CLEAN_PACKID
    ,VAR_MRN
    ,VAR_INVOICE_ID
    ,VAR_UPLOAD_ID
    ,VAR_UPLOAD_SN
    ,CLEAN_PERIOD_VAR);


    I agree with stolze, you should be able to use INSERT INTO table-name Select...

    Something like this should work:

    INSERT INTO SESSION.TEMP_CLEAN
    SELECT A.PO_UNI_ID, A.PACK_UNI_ID, B.INVOICE_ID, CLEAN_PERIOD
    FROM INS.INS_PO AS A
    , INS.INS_PO_CLASS_ELEMENTS AS B
    WHERE A.PO_UNI_ID = B.PO_UNI_ID
    AND TIMESTAMPDIFF(64,CHAR(CURRENT_TIMESTAMP - A.CHECKED_DTTM)) > MAX(12, CLEAN_PERIOD)
    AND A.CHECKED_DTTM is NOT NULL
    AND A.STATUS_CODE = 'CHECKED'

    PS Personally, instead of TIMESTAMPDIFF, I would use MONTH(CURRENT_TIMESTAMP - A.CHECKED_DTTM) > MAX(12, CLEAN_PERIOD). To me it is easier to see what is going on (but it is a personal preference.

  6. #6
    Join Date
    May 2009
    Posts
    16
    Thanks folks,

    Defintely this way of insert is good and I can avoid using cursor, it will work.
    but basically again i am getting sql code as -284 and sql state out put as 42838.

    Basically I am having stored proc as

    create procedure sp_clean_pos
    (IN clean_PERIOD INTEGER,
    OUT SQLSTATE_OUT CHAR(5),
    OUT SQLCODE_OUT INTEGER )
    DYNAMIC RESULT SETS 2
    LANGUAGE SQL



    DECLARE clean_PERIOD_VAR INTEGER;
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INT DEFAULT 0;


    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_CLEAN
    (DEL_POID BIGINT
    ,DEL_PACKID INTEGER
    ,DEL_INVOICE_ID VARCHAR(20)
    )
    IN Tempspace2 ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED

    -- INITIALIZE OUTPUT SQL PAAMAETERS
    SET CLEAN_PERIOD_VAR = CLEAN_PERIOD;

    INSERT INTO SESSION.TEMP_CLEAN
    SELECT A.PO_UNI_ID, A.PACK_UNI_ID, B.INVOICE_ID,
    FROM INS.INS_PO AS A
    , INS.INS_PO_CLASS_ELEMENTS AS B
    WHERE A.PO_UNI_ID = B.PO_UNI_ID
    AND TIMESTAMPDIFF(64,CHAR(CURRENT_TIMESTAMP - A.CHECKED_DTTM)) MAX(12, CLEAN_PERIOD)
    AND A.CHECKED_DTTM is NOT NULL
    AND A.STATUS_CODE = 'CHECKED'
    COMMIT;
    SET SQLSTATE_OUT = SQLSTATE;
    SET SQLCODE_OUT = SQLCODE;

    Can you please suggest that I have created a separate tempspace2 for using to declare GTTs, but still it gives sql out as -284.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Nee, a -284 indicates a problem with the Table space you specified (as ARWinner pointed out).

    From the SQL Reference manual under Declare Global Temporary Table:

    IN tablespace-name

    Identifies the table space in which the global temporary table will be instantiated.The table space must exist and be a USER TEMPORARY table space (SQLSTATE 42838), over which the authorization ID of the statement has USE privilege (SQLSTATE 42501).

    Check the type of Table space Tempspace2 to ensure it is a User Temporary table space and/or who has the Use privilege.

    PS This could just be a typo but you have an extra comma in the Select list:

    SELECT A.PO_UNI_ID, A.PACK_UNI_ID, B.INVOICE_ID, <==
    FROM INS.INS_PO AS A

  8. #8
    Join Date
    May 2009
    Posts
    16

    Red face

    Yes thats true the sql code -284 is problem with tablespace used in GTT declaration.
    I have used the GTT decalaration as follows
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_CLEAN
    (DEL_POID BIGINT
    ,DEL_PACKID INTEGER
    ,DEL_INVOICE_ID VARCHAR(20)
    )
    IN Tempspace2 ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED
    and
    the tempsace2 i have created as

    CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE2
    IN DATABASE PARTITION GROUP IBMTEMPGROUP
    PAGESIZE 4 K
    MANAGED BY DATABASE
    USING ( FILE '/db2/db2inst4/db2inst4/.../...TMP' 5000)
    EXTENTSIZE 32
    BUFFERPOOL IBMDEFAULTBP
    TRANSFERRATE 0.06;
    still same error and we cannot grant privileges to system temporary tablespace.
    then i tried creating normal tablespace as
    CREATE TABLESPACE tempsPACE2 IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
    PAGESIZE 4 K MANAGED BY DATABASE USING ( FILE '/db2/db2inst4/db2inst4/../../....TMP' 5000) EXTENTSIZE 32 BUFFERPOOL IBMDEFAULT
    BP TRANSFERRATE 0.06.
    and granted privilleges to Publlic for this tablespace, still same error as sql code -284.

    Thanks for all your efforts and support.
    Regards

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you read the manual you will see that for DECLARE GLOBAL TEMPORARY TABLE, the tablespace must be a USER TEMPORARY tablespace, not a SYSTEM TEMPORARY one.

    Andy

  10. #10
    Join Date
    May 2009
    Posts
    16
    Thanks Folks for all my learnings.

    My proc is working with Insert.. Select statment and w/o cursor.
    Also I have reacreated teh tempspace2 as user temporary tablespace.

    Regards

    Neet

Posting Permissions

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