Any SQL statement within DB2 can be executed either in a static way or dynamic way. While static gives the performance bonus at runtime, dynamic gives flexibility to decide on the query at run time itself. Any SQL statement execution goes through various phases like compilation, symentic analysis, query rewrite, access plan generation and execution etc. The basic difference between static and dynamic execution is time when an SQL goes through these phases. Static behavior takes the benefit of SQL known at the compile time and hence an opportunity to create the access plan at compile time itself.So at runtime DB2 will only execute the access plan. In dynamic all these phases will happen only at the run time.So one can say that any SQL can be run dynamically but not all can run statically. For static behavior to occur, SQL should be known at the compile time and the object referenced by the SQL should exist in the database as they are required to complete the access plan generation phase at compile time.
DB2 provides different ways of running an SQL statement statically. While C language provide embedded C for static behavior, java provides SQLj language (Embedded SQLj in Java) for static execution. For any statement to run statically, DB2 need to store the access plan in the database so that it can be used at the runtime. The object which is used to store this information is called packages. For each static application, DB2 creates a package with contains the details of each access plan and the corresponding SQL statement. For example code snippets for static and dynamic execution have a look at sqllib/samples directory. It contains samples for CLI (under cli directory), embedded C (under c directory), JDBC (Under java/jdbc directory) and SQLj (under java/sqlj directory).