How do I call stored proc in DB2 using raw SQL (LINQPad)

Question

I have a stored procedure called MY_STORED_PROC. It has an integer input parameter and an integer output parameter.

I've tried all manner of DECLAREs, BEGINs and ENDs and calls, and I've started to look into changing statement terminators (??), and I can't for the life of me get it to work in raw SQL (LINQPad in my case).

This answer gives

DECLARE outParam NUMBER;
BEGIN
    TP.MY_STORED_PROC(26431414, outParam);
END;

which returns

ERROR [42601] [IBM][DB2/SUN64] SQL0104N An unexpected token "DECLARE OUTPARAM NUMBER@ BEGIN" was found following " ". Expected tokens may include: "<values>".

I have also tried setting the terminator/delimeter:

--#SET DELIMITER @
DECLARE outParam NUMBER@
BEGIN
    TP.MY_STORED_PROC(26431414, outParam);
END@

--#SET TERMINATOR @
DECLARE outParam NUMBER@
BEGIN
    TP.MY_STORED_PROC(26431414, outParam);
END@

both return

ERROR [42601] [IBM][DB2/SUN64] SQL0104N An unexpected token "DECLARE OUTPARAM NUMBER@ BEGIN" was found following " ". Expected tokens may include: "<values>".

call gives the same thing.

Calling it via OdbcCommand works fine with call:

var input = 789;
var sql = "call TP.MY_STORED_PROC (?, ?);";

using (var tx = new TransactionScope(TransactionScopeOption.Suppress))
using (var connection = new OdbcConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
using (var command = new OdbcCommand(sql, connection))
{
    command.Parameters.Add("@input_param", OdbcType.Int, 32).Value = input;
    command.Parameters.Add("@output_param", OdbcType.Int, 32).Direction = System.Data.ParameterDirection.Output;
    connection.Open();
    command.ExecuteNonQuery();

    object output = command.Parameters["@output_param"].Value;
    if (output is DBNull)
        return null;

    int output = Convert.ToInt32(output);
    return output;
}

How can I call this thing and get a meaningful result in the output parameter, which I then stuff in a result set and echo to the screen?

Ian Bjorhovde Ian Bjorhovde · Accepted Answer · 2013-05-07T06:50:49

If you are just trying to call the stored procedure from the DB2 Command Line Processor, you don't need to do anything fancy. Just use a parameter marker for any output parameters:

$ db2 "call tp.my_stored_proc(26431414, ?)"

The DB2 CLP will just print the name / value of each output parameter.

For example, the built-in GET_DBSIZE_INFO stored procedure has 3 output parameters. You call it like this:

$ db2 "call get_dbsize_info(?, ?, ?, -1)"

  Value of output parameters
  --------------------------
  Parameter Name  : SNAPSHOTTIMESTAMP
  Parameter Value : 2013-05-06-23.49.14.581776

  Parameter Name  : DATABASESIZE
  Parameter Value : 253607936

  Parameter Name  : DATABASECAPACITY
  Parameter Value : 2179940352

  Return Status = 0

How do I call stored proc in DB2 using raw SQL (LINQPad)

1 Answers