DB2 Associate Vs Allocate in Stored Procedure

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

Stored Procedure Within another 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