Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DB2 Stored Procedure Output Value Command Prompt

Posted on 2007-11-16
9
Medium Priority
?
6,598 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

730 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