Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2011
    Posts
    5

    Unanswered: Stored procedure OUT parameters from CLP

    I'm trying to use the CLP on Z/OS Linux to call a stored procedure that has some IN and some OUT parameters. Do I need to declare variables to hold the OUT values? How do I do that in the CLP?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am not sure on Z/OS, but on LUW you just use a ? for the out parameters in CLP.

    Andy

  3. #3
    Join Date
    Nov 2011
    Posts
    5
    What does that do? Does it discard the output? Does it write it to stdout?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The CLP writes all the out parameters to stdout.

    Andy

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What do you mean exactly by "CLP". Please be very specific.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    For me, it means the DB2 Command Line Processor.

    Andy

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ARWinner View Post
    For me, it means the DB2 Command Line Processor.

    Andy
    Sorry, I thought he was referring to z/OS, but I guess he means z/Linux, so should be the same as CLP in LUW.

    No need to declare variables or ref cursors from the command line (outside of the SP) like is required in Oracle.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Nov 2011
    Posts
    5
    Quote Originally Posted by Marcus_A View Post
    Sorry, I thought he was referring to z/OS, but I guess he means z/Linux, so should be the same as CLP in LUW.

    No need to declare variables or ref cursors from the command line (outside of the SP) like is required in Oracle.
    Yes, Z/Linux, with the IBM Command Line Processor for DB2 Client 9.7.0

    Thanks for the answer, I will try passing ? and checlking stdout (when I get permissions granted to actually call the stored proc, that is...)

  9. #9
    Join Date
    Nov 2011
    Posts
    5
    OK I can now call the stored procedure, and as long as there are no records to update, it works. If there is work for it to do, though, it returns this output:
    Code:
    PSQLCODE: -303
    PSQLSTATE: 42806
    PSQLERRMC: A VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER 18 BECAUSE THE DATA TYPES ARE NOT COMPARABLE
    So, at least the passing back of the PSQLCODE, PSQLSTATE and PSQLERRMC is working.

    The developer of the stored proc says that I need to pass 0 instead of ? in the 7th parameter. If I do that, though, I get this error:
    Code:
    DB21105E  Parameter 7 for stored procedure "DA03.EROL_CLOSURE" should be an
    OUTPUT parameter.
    Any ideas? I don't have the source to the stored proc at the moment, I will try and get it.

  10. #10
    Join Date
    Nov 2011
    Posts
    5
    It's working now - the error message in the previous post is simply a bug within the stored procedure, which is nothing to do with the rest of this thread. Thanks all for patience and assistance.

Tags for this Thread

Posting Permissions

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