CODEWITHSUNDEEP
db2
sapatos
sapatos

Reputation: 1304

db2batch using stored procedures with parameter files

I'm trying to find examples of how to use db2batch with stored procedure calls and parameter files.

I have a stored procedure I'd like to call a number of times and provide the parameters from a file however I'm struggling to find documentation that shows how this might be done.

Upvotes: 1

Views: 1500

Answers (1)

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11052

The documentation on using parameter files is certainly not very good. However you can indeed call a stored procedure** using db2batch.

Here is an example of the command line:

db2batch -d yourdb -iso cs -f stmts.sql -m stmts.data

The SQL file (stmts.sql) that calls the procedure would look something like this:

--#BGBLK 5
call my.storedproc(?, ?);
--#EOBLK

The number after the --#BGBLK instructs db2batch to execute the statement(s) in the block 5 times.

Your parameter file (stmts.data) would look like this (this example is for a stored procedure where the first argument to the stored proc is an INT and the second argument is a VARCHAR(15), and both are IN parameters):

1 'First'
2 'Second'
3 'Third'
4 'Fourth'
5 'Fifth'
6 'Sixth'
7 'Seventh'
8 'Eighth'
9 'Ninth'
10 'Tenth'

There are 10 pairs of parameters in this file, but since the --#BGBLK identifier specified a repeat count of only 5, db2batch will read only the first 5 lines in the parameter file. If you specify a repeat count that is larger than the number of lines in the parameter file you'll get errors for the repetitions that don't have corresponding values from the parameter file.

**Note: db2batch will work on procedures that have IN parameters, and will even handle stored procedures that return result set(s), but I am not aware of a way to get it to work with stored procedures that have OUT parameters.

Upvotes: 1

Related Questions