467,171 Members | 1,187 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,171 developers. It's quick & easy.

Packages, Bind, Prepare and Execute

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
  • viewed: 2545
Share:
1 Reply
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.

Similar topics

7 posts views Thread by Anthony Robinson | last post: by
2 posts views Thread by virgilio | last post: by
2 posts views Thread by claus.hirth@abraxas.ch | last post: by
reply views Thread by tfs | last post: by
2 posts views Thread by beena | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.