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
