DB2 LUW存储过程全局临时表使用及DDL/DML循环执行报错问题

Hey there! Let's work through your two DB2 LUW v10.5.0.7 stored procedure issues step by step:

First, let's diagnose why your temp table code isn't working:

• Your DECLARE GLOBAL TEMPORARY TABLE syntax is incomplete — it needs ROWS after ON COMMIT PRESERVE (valid clauses are ON COMMIT PRESERVE ROWS or ON COMMIT DELETE ROWS).
• Static DDL statements like this can cause errors if the temp table already exists in your session. It's safer to handle existence checks dynamically.
• You were inserting from my_table, but your goal is to pull 1000 random IDs from source_table.

Here's a corrected implementation:

BEGIN
  -- Drop temp table if it exists in the current session (avoids creation errors on re-run)
  EXECUTE IMMEDIATE 'DROP TABLE SESSION.TEMP_TAB'
  EXCEPTION WHEN OTHERS THEN NULL; -- Ignore error if table doesn't exist

  -- Create the global temporary table with correct syntax
  DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TAB(
    ID INT
  ) NOT LOGGED ON COMMIT PRESERVE ROWS;

  -- Insert 1000 random IDs from source_table
  INSERT INTO SESSION.TEMP_TAB(ID)
  SELECT ID
  FROM (
    SELECT ID, RAND() AS RND
    FROM SOURCE_TABLE
  ) AS T
  ORDER BY RND
  FETCH FIRST 1000 ROWS ONLY;
END

Note: Global temporary tables are session-specific, so this table will persist for your current session until you drop it or the session ends.

Let's break down why the static INSERT fails but dynamic SQL works:

• DB2 precompiles static SQL statements in stored procedures when the procedure is created. When you dynamically add a new partition to the TEST table, the static INSERT still references the pre-compiled table structure (which doesn't include the new partition). This triggers the SQLCODE=-327 error because the inserted row falls outside the partitions known at precompile time.
• Dynamic SQL (EXECUTE IMMEDIATE) is parsed at runtime, so it uses the latest table structure including the newly added partition, fixing the error.

Your dynamic approach works, but we can make it safer and cleaner by using parameter binding instead of string concatenation (avoids SQL injection risks and date formatting headaches):

CREATE OR REPLACE PROCEDURE PROC1 (IN IN_REP INT)
BEGIN
  DECLARE V_DT DATE;
  DECLARE V_END_DT DATE;
  DECLARE V_ADD_PART VARCHAR(1024);
  DECLARE V_ID INT;
  DECLARE V_NEXT_ID INT;

  SELECT MAX(ID), MAX(DT) INTO V_ID, V_DT FROM TEST;
  SET V_END_DT = V_DT + IN_REP DAY;

  WHILE V_DT < V_END_DT DO
    SET V_DT = V_DT + 1 DAY;
    SET V_NEXT_ID = V_ID + 1;

    -- Dynamic DDL to add partition
    SET V_ADD_PART = 'ALTER TABLE TEST ADD PARTITION PART_' || V_NEXT_ID || ' STARTING FROM ' || V_NEXT_ID || ' ENDING AT ' || V_NEXT_ID;
    EXECUTE IMMEDIATE V_ADD_PART;

    -- Dynamic DML with parameter binding (safer than string concatenation)
    EXECUTE IMMEDIATE 'INSERT INTO TEST (ID, DT) SELECT ?, ? FROM SYSIBM.DUAL'
    USING V_NEXT_ID, V_DT;

    SET V_ID = V_NEXT_ID; -- Update ID counter for next iteration
  END WHILE;
END

The USING clause passes your variables directly to the dynamic SQL, eliminating the need to manually format dates as strings and reducing security vulnerabilities.

内容的提问来源于stack exchange，提问作者KnowNothing