Solved

Get the Values of Out parameters into unix Shell variables and use them to populate the values into another table.

Posted on 2008-06-12
4
506 Views
Last Modified: 2012-06-27
I have a stored procedure which spits out values like data counts etc as out parameters.
Can some one suggest how I can capture just the values and send them to a Uinix shell variable to be used in another procedure being called later.

There is a need to log out of database and after a while execute another procedure which will take these as input parameters.
0
Comment
Question by:boppanak
[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 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 250 total points
ID: 21771767
I think the best approch will be to have the stored procedure return the SQL statement that invokes the next procedure and have that statement written to the output file that later will be fed as input to the clp
0
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 250 total points
ID: 21772927
Hi boppanak,

DB2 doesn't have an easy facility to write just selected data to an output file.  DB2 insists on writing it's own headers, connect info, prompts, result flags, etc.  You can edit them from the results with something like sed(1) (I have many times) but that's not always the easiest.

For your appication you might be able to create a new table that stores the result(s) and recalls them later.

CREATE TABLE myschema.parameters
(
  name    varchar (30),
  ivalue   integer,
  fvalue   double,
  cvalue   varchar (100)
);

INSERT INTO myschema.parameters (name, ivalue)
SELECT 'ITEMCOUNT', count(*)
FROM sometable
WHERE post_date > current_date - day (current_date);

INSERT INTO myschema.parameters (name, fvalue)
SELECT 'TOTALPAYROLL', sum(gross_pay)
FROM sometable
WHERE year (post_date) = year (current_date)
  AND month (post_date) = month (current_date);

etc.


Then you can just recall them from the table when you're ready to proceed with the next step.


Good Luck,
kent
0

Featured Post

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

631 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