By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

Packages, Bind, Prepare and Execute

P: n/a
Hi,

I have started using DB2 UDB ESE v8.2 in Linux recently. I have
seen a lot of articles on packages, prepare statement, bind and execute
statement.

I havent got a clear idea.

I have written a few stored procedures but havent used prepare and
execute.
what are its advantages. ?

What is a package in DB2 UDB and how do you bind and rebind (I really
dont know what to bind, even) ?
Thanks in advance.

Mar 3 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
rAinDeEr wrote:
Hi,

I have started using DB2 UDB ESE v8.2 in Linux recently. I have
seen a lot of articles on packages, prepare statement, bind and execute
statement.

I havent got a clear idea.

I have written a few stored procedures but havent used prepare and
execute.
what are its advantages. ? You use PREPARE and EXECUTE if the SQL statement text s not known in
advance. SQL allows only values to be passed in at execution (through
parameter markers or variables).
Identifiers (such as a table name) cannot be variable.
That is:
'SELECT * FROM ? WHERE ?.? = 5' is not legal SQL.
So, if you want to compose SQL on the fly you have to glue together the
SQL string:
SET txt = 'SELECT * FROM ' || mytable || 'WHERE ' || mytable || '.' ||
mycolumn || '= 5'
And then prepare (compile) and execute the statement.
Note that queries (as above) are executed by opening a CURSOR, while
UPDATE/DELETE/INSERT/MERGE/CALL and DDL statements get executed using
the EXECUTE statement (they don't return resultsets).

What is a package in DB2 UDB and how do you bind and rebind (I really
dont know what to bind, even) ?

If are are using SQL Procedures you can afford to stay reasonably
ignorant on those.
Compilation of any procedure happens in up to three phases:
1. PREP
Separate the SQL from the programming logic (e.g. C)
2. BIND compile the SQL and store the result in a PACKAGE on the server
3. Compile and Link the programming logic for external procedure

For an SQL Procedure there are really only 2 interfaces you have with
the package:
REBIND_ROUTINE_PACKAGE() which you call after statistics for tables used
in the procedure have been updated or e.g. an index has been dropped.
http://publib.boulder.ibm.com/infoce...n/r0009863.htm

SET_ROUTINE_OPTS()
which you can call before creating a procedure to change its semantics
(such as the isolation level) from the default.
http://publib.boulder.ibm.com/infoce...n/r0011873.htm

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 3 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.