In DB2 application development, what is the main difference between embedded (static) SQL and dynamic SQL?

Introduction / Context:
Embedded versus dynamic SQL is a classic topic in DB2 interviews. Understanding this difference is crucial for performance tuning and flexible application design. The question focuses on when SQL statements are prepared and bound, rather than on artificial limitations around which operations each style can support.

Given Data / Assumptions:

• We are writing applications that access DB2 databases.
• Some SQL is known at compile time (embedded or static SQL).
• Other SQL statements must be built dynamically, perhaps from user input, at run time.

Concept / Approach:
Embedded SQL statements are written directly in the source code and processed by a precompiler that extracts them into a DBRM and binds them to a specific plan or package before execution. Their access paths are determined ahead of time. Dynamic SQL, by contrast, is built as character strings at run time and sent to DB2 for PREPARE, which parses and optimizes the statement then and there. Therefore, the correct answer must distinguish compile or bind time versus run time preparation.

Step-by-Step Solution:
Step 1: Recall that embedded SQL is precompiled, creating a DBRM that is later bound to a plan or package. Step 2: Understand that access paths for embedded SQL are usually stable until a REBIND operation is performed. Step 3: Remember that dynamic SQL is held in variables and passed to DB2 through PREPARE and EXECUTE calls. Step 4: Note that DB2 parses and optimizes dynamic SQL when PREPARE is executed, based on current catalog statistics and environment. Step 5: Choose the option that explicitly describes this difference in preparation timing and optimization.

Verification / Alternative check:
In practice, application traces show that embedded SQL statements do not require PREPARE calls at run time because they have already been bound, whereas dynamic SQL introduces PREPARE overhead but can adapt to different SQL text. This observed behavior supports the conceptual distinction described above and validates option A as the correct one.

Why Other Options Are Wrong:
Option B is wrong because both embedded and dynamic SQL can perform all standard operations such as SELECT, INSERT, UPDATE, and DELETE.
Option C is wrong because embedded SQL still uses the DB2 optimizer during bind; it does not bypass optimization, and dynamic SQL is not guaranteed to be slower in every case.
Option D is wrong because dynamic SQL is still ordinary SQL executed at run time, not a different language outside the standard.

Common Pitfalls:
A common pitfall is to think that dynamic SQL is always bad for performance; in reality, it provides flexibility and can benefit from current statistics and environment conditions. Another mistake is to forget that changes in access paths for embedded SQL require REBIND operations. Understanding these tradeoffs helps developers choose appropriate techniques for different parts of an application and answer interview questions more confidently.

Final Answer:
The main difference is that embedded SQL is parsed and bound at compile or bind time, while dynamic SQL is prepared and optimized at run time based on statement text.