How to get variable value from inside a stored procedure in DB2?

Question

This seems like it should be very easy...anyway, it is in MS SQL Server

In a DB2 stored procedure, how can I just get the value of a variable? Say I have the following stored procedure:

CREATE PROCEDURE etl.TestABC( 
) 
LANGUAGE SQL 
  BEGIN 
    declare      Stmt              varchar(2048); 
    set Stmt = 'this is a test';
-- print Stmt;
-- select Stmt;


    return 0; 
END @ 

I'd like to print out the value of Stmt after I set it. Print doesn't work, select doesn't work. Somebody said I have to insert it to a table first, and then get it after I run the stored procedure. Is this really necessary?

Thanks, Sylvia

EDIT: I should have made clearer that I want to see the value of Stmt each time after I set it, and I may need to set it multiple times within the same stored procedure.

Answer 1

Hi,
you must declare the variable in the constructor of the procedure:

CREATE PROCEDURE etl.TestABC(
    OUT Stmt varchar(2048)
) 
LANGUAGE SQL 
BEGIN 
    --    declare      Stmt              varchar(2048); 
    set Stmt = 'this is a test';
    -- print Stmt;
    -- select Stmt;

    return 0;
END @ 

This will allow you to access it i.e. from the console:

call etl.TestABC(?)

with output similar as this:

Value of output parameters

Parameter Name  : STMT
Parameter Value : this is a test

Return Status = 0

---

NB: I don't have a access to our DB2 server at the moment, but I believe that the above should do the trick. Examples based on: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.sql.doc/doc/t0007055.htm

Answer 2

Try the following line:

values Stmt;

Answer 3

If you want to see the values while you're developing / testing your stored procedure, then you should use IBM Data Studio and debug your stored procedure. This provides normal debugger functionality, so you can see the value of variables as your procedure executes.

If, however, you just want to execute your stored procedure and see a history of values, I suggest creating a global temporary table, and inserting a row into it in your code:

declare global temporary table StmtHistory (
  when timestamp not null with default current timestamp, 
  stmt varchar(2048) not null
  ) on commit preserve rows;

Then, at points in your stored procedure, add:

insert into session.StmtHistory (stmt) values (stmt);

After you execute your stored procedure, you can query the table session.StmtHistory and see the values you're looking for.

Also, DB2 9.7 added Oracle compatibility, so if you are using this version you may actually be able to use DBMS_OUTPUT.PUT_LINE and DBMS_OUTPUT.GET_LINES to accomplish this, if you prefer.