Solved

DB2 Stored Procedure Output Value Command Prompt

Posted on 2007-11-16
9
6,139 Views
Last Modified: 2008-06-16
Hi experts,

This should be the simple question.

I am calling db2 stored procedure having output parameter from the command prompt.

How to display (ECHO) the output parameter value of the stored procedure at the command prompt?

Thanks
0
Comment
Question by:ajexpert
  • 4
  • 4
9 Comments
 
LVL 5

Expert Comment

by:ocgstyles
ID: 20302537
Hi ajexpert,

Here's an example (using Linux):

$ db2 -td@ "create procedure test(
in p_in1 int,
in p_in2 int,
out p_out int
)
language sql
begin
 set p_out = p_in1 + p_in2;
end@"

DB20000I  The SQL command completed successfully.

$ db2 "call test(5,6,?)"

  Value of output parameters
  --------------------------
  Parameter Name  : P_OUT
  Parameter Value : 11

  Return Status = 0

Every output parameter when you call the procedure should have a "?" as a place holder.

As far as putting a line in the procedure, using the above as an example:

echo p_out;

... won't work.

Hope that helps.

Keith
0
 
LVL 14

Author Comment

by:ajexpert
ID: 20304767
Hi Keith,
Thanks for example but I have tried the same.
I dont need 'Value of out put parameters' as its there by default
I am executing on Windows and thru command prompt I need something like
ECHO %P_OUT%
This should give me only the value and not other information.
I hope my requirement is clear now.
Let me know If I need to elborate more.

I am ready to increase the points if the solution is great :)
Thanks,

ajexpert
0
 
LVL 5

Expert Comment

by:ocgstyles
ID: 20305044
Hi ajexpert,

I figured that's wechat you were looking to do, but unfortunately DB2 doesn't support it.  The only way I can think to get the value is to pipe the output of the stored procedure into a script/executable to extract the value you want.

Keith
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 14

Author Comment

by:ajexpert
ID: 20307785
Hi Keith,
It would be good if you can explain the same with code example.

Thanks
0
 
LVL 5

Accepted Solution

by:
ocgstyles earned 50 total points
ID: 20308910
Hi ajexpert,

I can give an example, but I'm not sure it will help, since you are working in a Windows environment.  But in case you are familiar with both DOS batch scripting and Unix shell scripting, maybe you can use my shell script as an example to create a DOS batch.

Create a executable file named "get_param_value" that contains:

/usr/bin/awk -v P=$1 '
        /Parameter Name/ { PARAM=$4 }
        /Parameter Value/ { VAL=$4 }
        /^$/ { if( PARAM == P ) print VAL }'

What this awk script does is look for a parameter that you pass into this script.  When the awk script finds the variable, it finds the value on the next line, then when it encounters the next blank line, it outputs the value if we found the param name.

So, if you use the stored procedure I put in my first post, you can do this:

$ db2 "call test(5,6,?) | ./get_param_value P_OUT

In this case, it would print just the value 11.

Sorry again that this is Unix, but I'm not sure how to do a Windows equivalent.

Keith
0
 
LVL 5

Expert Comment

by:ocgstyles
ID: 20309404
Hi,

There's a slight oversight in the above example, it doesn't account for string parameters (values with spaces), so here's a modified version... if anyone else find's value with it...

/usr/bin/awk -F":" -v P=$1 '
        function ltrim(s){
                sub(/^ */, "", s);
                return s;
        }

        function rtrim(s){
                sub(/ *$/, "", s);
                return s;
        }

        function trim(s){
                return ltrim(rtrim(s));
        }

        /Parameter Name/ { PARAM=$2 }
        /Parameter Value/ { VAL=$2 }
        /^$/ { if( trim(PARAM) == P ) print trim(VAL) }'


0
 
LVL 14

Author Comment

by:ajexpert
ID: 20312996
Thanks for your efforts but I need to execute on Windows Environment as of now.

Is there any way to accomplish this in Windows Environment?
0
 

Expert Comment

by:nagarjuna009
ID: 20675351
Hi ,

 thank you for providing above script. Actually i have more than on OUT parameters. when i tried to change code accordingly it is giving an error. can u provide me the code for getting multiple OUT parameters. as  i am new to shell script pls do this help.. thanks in advance..
0
 
LVL 14

Author Comment

by:ajexpert
ID: 20905346
Hi,
I am giving points to ocgstyles for his efforts, though this doesn't quite resolve the query.

Thanks anyways ocgstyles
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql is not working correctly 23 361
Problem to insert 6 375
AS400 DB2 to MS SQL Server 2012 4 1,409
DB2 return first match 3 111
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question