Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
520 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
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 1000 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 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 1000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month8 days, 7 hours left to enroll

876 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