Solved

DB2 Stored Procedure Output Value Command Prompt

Posted on 2007-11-16
9
6,450 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

630 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