Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49

    Unanswered: How to show std. output in db2 sql pl

    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??

  2. #2
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    cout in C++

  3. #3
    Join Date
    Aug 2011
    Posts
    46
    if you have v9.7, you can use DBMS_OUTPUT.PUT_LINE


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

  4. #4
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    ok. but any alternative for db2 v9.5??

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483

  6. #6
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    "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.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    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.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    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

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •