Static SQL in DB2® is
a powerful capability that can streamline data access at run time
and improve the security of your applications.
Static SQL
offers the following advantages:
- Avoidance of of the dynamic statement cache
- Using static SQL reduces contention for DB2's dynamic statement
cache, improving performance for applications that use dynamic SQL.
- Consistency of access paths
- Static SQL makes response times predictable and stable by locking
in access paths before an application runs. By contrast, access paths
for dynamic SQL are computed at run time.
- Potential for improved performance of your applications
- Static SQL can improve the performance of your applications.
- Because access plans are determined before run time, the need
for preparing SQL statements at run time is eliminated.
- Because each statement does not need to be prepared and described,
there is less network traffic between client applications and database
servers.
- Static SQL strictly enforces data types for host variables or
parameters that are used in predicates. This strict enforcement ensures
that input data matches target types in the database.
- Tighter security
- You can grant users the EXECUTE privilege on DB2 packages, rather than granting
them privileges on database objects.
- Ease of revising packages
- Versioning DB2 packages
allows you to rebind packages without the risk of losing prior, better,
access paths.
pureQuery offers
three ways of running SQL statements statically
on DB2 databases.