Stored procedure OUT parameters from CLP

PhilHibbs · #1 (**permalink**) 11-10-11, 09:34

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?

ARWinner · #2 (**permalink**) 11-10-11, 09:41

I am not sure on Z/OS, but on LUW you just use a ? for the out parameters in CLP.

Andy

PhilHibbs · #3 (**permalink**) 11-10-11, 09:54

What does that do? Does it discard the output? Does it write it to stdout?

ARWinner · #4 (**permalink**) 11-10-11, 09:55

The CLP writes all the out parameters to stdout.

Andy

Marcus_A · #5 (**permalink**) 11-10-11, 09:55

What do you mean exactly by "CLP". Please be very specific.

ARWinner · #6 (**permalink**) 11-10-11, 10:08

For me, it means the DB2 Command Line Processor.

Andy

Marcus_A · #7 (**permalink**) 11-10-11, 10:24

Quote:

Originally Posted by ARWinner

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.

PhilHibbs · #8 (**permalink**) 11-10-11, 10:48

Quote:

Originally Posted by Marcus_A

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...)

PhilHibbs · #9 (**permalink**) 11-11-11, 06:31

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.

PhilHibbs · #10 (**permalink**) 11-11-11, 07:59

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech