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

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.
boppanakAsked:
Who is Participating?
 
momi_sabagConnect With a Mentor Commented:
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
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
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
All Courses

From novice to tech pro — start learning today.