Associate Vs. Allocate: DB2 Stored Procedure Example

Here’s all about the Associate/Allocate cursor. The purpose of Associate is to get the cursor from the Stored procedure that’s called. The Allocate just assigns a cursor name to it.

DB2 nested Stored procedure

 CREATE PROCEDURE EMP_NAME (OUT p_name VARCHAR(30)) 
  BEGIN
    DECLARE result RESULT_SET_LOCATOR VARYING;
 
    -- Call the procedure returning the result set
    CALL emp();
 
    -- Get a cursor for the result set
    ASSOCIATE RESULT SET LOCATOR (result) WITH PROCEDURE emp;
    ALLOCATE cur CURSOR FOR RESULT SET result;
 
    -- Cursor already open, so you can start fetching rows
    FETCH cur INTO p_name;
    -- ...
    CLOSE cur;
END

Recent Posts

References

Examples of DB2 Associate and Allocate Cursor

Srini

Data Engineer with deep AI and Generative AI expertise, crafting high-performance data pipelines in PySpark, Databricks, and SQL. Skilled in Python, AWS, and Linux—building scalable, cloud-native solutions for smart applications.