Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Posts
    11

    Unanswered: Logging in DB2 Stored Procedures

    Hi,

    We are using DB2 V8 on z/OS and want to log values of input and output parameters in DB2 SQL stored procedures. Can anyone please help me with this?

    Regards,
    Yogesh.

  2. #2
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    What are you coding your stored procs in? If you're using something like COBOL, just use a DISPLAY and you'll see the messages in the SYSOUT of your WLM job. You could also allocate a file and write to that, but that's a little trickier. For an SQL stored proc, you could INSERT values to a table.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    For SQL procedures, you can call a UDF that writes the information to a file residing on the server. One example of such a UDF can be found here: http://www.ibm.com/developerworks/db...303stolze.html
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    For SQL procedures, you can call a UDF that writes the information to a file residing on the server. One example of such a UDF can be found here: http://www.ibm.com/developerworks/db...303stolze.html
    Does that work on DB2 for z/OS?

    Personally, I would alter the SP to log the parms to a DB2 table. I would also log an negative return codes to a DB2 table.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Quote Originally Posted by Marcus_A
    Does that work on DB2 for z/OS?

    Personally, I would alter the SP to log the parms to a DB2 table. I would also log an negative return codes to a DB2 table.
    At the environments where I work we have created a general audit table where we log information like process name, start time, stop time, row counts, and notes (a column we use for whatever we had forgoten)

    It has always worked well for us excpecialy when we needed to report on the process regarding timings and performance issues.

    It is interesting that you can capture the error codes. That is something that i would be interested in. Can you elaborate on how you would do that?
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Marcus_A
    Does that work on DB2 for z/OS?
    Yes, it does. However, it can be made simpler if the UDF runs in WLM and writes its output to STDERR. Then you could have a look at it directly in the WLM logs.

    Personally, I would alter the SP to log the parms to a DB2 table. I would also log an negative return codes to a DB2 table.
    That works well if you use a temporary table because you can use
    Code:
    DECLARE GLOBAL TEMPORARY TABLE ... NOT LOGGED ON ROLLBACK PRESERVE ROWS
    Otherwise you would loose the logged information when an explicit or implicit rollback occurs.
    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
  •