Hi,

I want to create a SP which will print a series of no.s its similar to C or C++ program in which we give a no. as input like 10 and the program will display output from 1 to 10.

We use C (printf) and C++(scanf) in oracle pl/sql (dbms_output.put_line) to display output.

What to use in DB2 SQL/PL??

cout in C++

if you have v9.7, you can use DBMS_OUTPUT.PUT_LINE


DBMS_OUTPUT module - IBM DB2 9.7 for Linux, UNIX, and Windows

ok. but any alternative for db2 v9.5??

Please see

A UDF for File Output and Debugging from SQL

Making Operating System Calls from SQL

"A UDF for File Output and Debugging from SQL" article is for v7 and v8.

and in v7 and v8 also that UDF is inserting value in other variable or file.

My requirement is to display std. o/p.

The UDF works in the same way for V9 - there hasn't changed anything significantly in the way how UDFs are to be implemented. (Only new functionality was added since then.)

Now, remember the architecture of your environment. We are talking about a client/server system. A SQL statement is executed on the server while you are sitting in front of a client and the client is what shows you - as human - the output on some displaying/viewing device. Writing anything to the standard output on the server would only show up on the server and not at the client - unless some measures were taken to capture the output on the server and deliver it to the client. Then it is the client's responsibility to do something with this data. (This is conceptually the exact same as with query results - only the source of the data varies.)

So it comes down to what is your client? If it is the DB2 CLP, you won't have any facilities available so far. If you have your own application, you can do what has been suggested: write debug/trace output to a file and then capture that information (either through another SQL statement or some other communication mechanism). Then your application, which is running on the client, can display the output in whichever way you like.

Hi,

function defn :

CREATE FUNCTION PUT_LINE(VARCHAR(4000))
RETURNS VARCHAR(1)
NOT FENCED
RETURNS NULL ON NULL INPUT
NO SQL
DBINFO
EXTERNAL ACTION
LANGUAGE C
PARAMETER STYLE DB2SQL
EXTERNAL NAME 'put_line!put_line2';


SP defn.

db2 "create procedure test()
language sql
begin
DECLARE dummy_variable VARCHAR(10);
values(put_line('Hi')) into dummy_variable;
end"

I am getting this error.

db2 "call test()"

SQL0444N Routine "PUT_LINE" (specific name "SQL120106153113100") is
implemented with code in library or path ".../sqllib/function/put_line",
function "put_line2" which cannot be accessed. Reason code: "5".
SQLSTATE=42724

I am executing this on db2 server. on $ prompt.

You did compile and install the library that contains the UDF implementation?

On which physical machine your DB2 client resides, doesn't matter. You still have a client/server architecture. Sometimes, it makes things easier to understand by thinking about 2 different machines. Even on the same machine, we have at least 2 separate processes (DB2 actually has a few more than 1 for the server functionality) and things written to the standard output of one process cannot magically appear on the standard output of another process.

Yes . I have compiled it.

I have followed the same steps mentioned in article

A UDF for File Output and Debugging from SQL

Regards
Sumit

Have a look here at reason code 5 and possibly actions: SQL0444N

Since the function is rather simple, I don't think you'll have a memory issue. So it is either 32bit vs. 64bit, or some undefined symbol. Undefined symbols can easily be addressed by creating a small "main" function, which prepares the parameters for the UDF. With such a main function, you can create a standalone executable, which bypasses DB2 and allows easier unit testing and debugging capabilities.