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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

828 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