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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

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 (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…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
Suggested Courses

738 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