Dynamic SQL in DB2 for z/OS

Dynamic SQL concepts

You meet dynamic SQL in four places:

• Interactive SQL — SPUFI, the command line processor, QMF. Everything you type is prepared dynamically.
• Embedded dynamic SQL — COBOL, PL/I, C, or assembler puts the text in a host variable and issues PREPARE and EXECUTE. The PREPARE statement is static; the payload is not. You still precompile and bind the program.
• Deferred embedded SQL — statements that look embedded but are prepared at run time with bind-time rules (owner and qualifier from the package).
• ODBC, JDBC, and native REST — the driver sends dynamic SQL. You do not precompile those client programs the COBOL way.

Use static SQL when you know the statement before run time. Use dynamic SQL when the table list, selected columns, or ORDER BY must change per request—or when the client is not a bound COBOL program. Dynamic SQL pays prepare CPU unless the statement cache hits. Authorization follows DYNAMICRULES, not the same path as static SQL unless you bound DYNAMICRULES(BIND).

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE prepares and runs a statement in one step. The statement cannot be a SELECT that returns a result set (no cursor), and it cannot contain parameter markers. It is fine for occasional DDL, a one-off INSERT with literals you already validated, or SET statements.

1
2
3
EXEC SQL
  EXECUTE IMMEDIATE :SQL-STMT
END-EXEC.

Every EXECUTE IMMEDIATE is a prepare. If the same string runs thousands of times, switch to PREPARE once and EXECUTE in a loop—or at least keep the text identical so the global cache can hit.

PREPARE and EXECUTE

PREPARE names a statement and compiles the string. EXECUTE runs a non-SELECT prepared statement. You can PREPARE once per unit of work and EXECUTE many times with different values.

1
2
3
4
5
6
7
8
MOVE 'UPDATE HR.EMPLOYEE SET BONUS = ? WHERE EMPNO = ?'
  TO SQL-STMT.
EXEC SQL
  PREPARE BONUSUPD FROM :SQL-STMT
END-EXEC.
EXEC SQL
  EXECUTE BONUSUPD USING :HV-BONUS, :HV-EMPNO
END-EXEC.

After COMMIT, the prepared name is gone unless KEEPDYNAMIC(YES) is in effect (or the global cache still holds a matching copy that a new PREPARE can find). PREPARE with the same statement identifier replaces the old prepared form. DEFER(PREPARE) (often set automatically with REOPT ALWAYS/ONCE/AUTO) postpones the real prepare until OPEN or EXECUTE.

Parameter markers

A parameter marker is a ? where a value belongs. Markers are not names; order on EXECUTE USING or in the SQLDA is the order of the question marks. Markers stand in for expressions or host variables—not for table names, column names, or SQL keywords. Those identifiers still have to be literal text in the string, which is why identifier concatenation is the injection risk.

Typed parameter markers (CAST(? AS INTEGER)) help the optimizer and DESCRIBE INPUT when the context does not pin down a type. Untyped markers take a type from how they are used (compared to a column, assigned to a column). REOPT(ONCE), REOPT(AUTO), or REOPT(ALWAYS) can use the actual marker values for access path selection. With CONCENTRATE STATEMENTS WITH LITERALS, literals are considered only when REOPT is ONCE or AUTO.

Prefer markers over stuffing numbers and quotes into the string. Cache matching is then stable, types stay correct, and users cannot close a quote to inject SQL.

Dynamic SQLDA

The SQL Descriptor Area (SQLDA) is a structure the program and Db2 share when the number or type of variables is not fixed at precompile time. It has an eye-catcher SQLDA, a length SQLDABC, SQLN (allocated SQLVAR slots), SQLD (slots in use), and an array of SQLVAR entries (SQLTYPE, SQLLEN, SQLDATA pointer, SQLIND pointer, SQLNAME).

You need an SQLDA when:

• The SELECT list is built at run time (unknown columns).
• The number of parameter markers varies.
• You DESCRIBE and then allocate storage from the types Db2 returns.

Allocate enough SQLVAR entries before DESCRIBE. If SQLD comes back larger than SQLN, enlarge the SQLDA and DESCRIBE again. LOB, XML, and some distinct types need extra SQLVAR entries (doubled SQLDA). Languages differ: COBOL often copies DSNXxxxx sample SQLDA layouts; C uses struct sqlca / sqlvar from the SQLCA include.

DESCRIBE, DESCRIBE INPUT, DESCRIBE OUTPUT

DESCRIBE statement-name INTO :SQLDA (DESCRIBE OUTPUT) fills the SQLDA with result-column metadata after you PREPARE a SELECT: types, lengths, nullability, names. That is how a generic query tool paints column headings without hard-coding them.

DESCRIBE INPUT statement-name INTO :SQLDA describes each parameter marker instead. You then set SQLDATA and SQLIND to the addresses of your input host variables and EXECUTE ... USING DESCRIPTOR :SQLDA.

1
2
3
4
EXEC SQL PREPARE SQLOBJ FROM :SQLSTMT END-EXEC.
EXEC SQL DESCRIBE INPUT SQLOBJ INTO :INSQLDA END-EXEC.
* set SQLDATA / SQLIND for each marker
EXEC SQL EXECUTE SQLOBJ USING DESCRIPTOR :INSQLDA END-EXEC.

DESCSTAT(YES) on BIND PACKAGE stores DESCRIBE information for static SQL; for dynamic SQL you always DESCRIBE at run time after PREPARE. DESCRIBE of a statement that is not a SELECT (and has no result set) returns SQLD = 0.

Dynamic cursors and result sets

A dynamic SELECT is not EXECUTE’d for the rows. You DECLARE a cursor FOR the prepared statement name, OPEN it (USING markers if the SELECT has ?), FETCH, then CLOSE.

1
2
3
4
5
EXEC SQL DECLARE C1 CURSOR FOR DYNSEL END-EXEC.
EXEC SQL PREPARE DYNSEL FROM :SEL-TEXT END-EXEC.
EXEC SQL DESCRIBE DYNSEL INTO :OUTSQLDA END-EXEC.
EXEC SQL OPEN C1 USING :HV-DEPT END-EXEC.
EXEC SQL FETCH C1 USING DESCRIPTOR :OUTSQLDA END-EXEC.

Cursor attributes (WITH HOLD, WITH RETURN, SENSITIVE) belong on the DECLARE or on PREPARE attributes, depending on the form you use. Stored procedures that return dynamic result sets PREPARE and OPEN a WITH RETURN cursor and leave it open for the caller. Ambiguous cursors plus CURRENTDATA and ISOLATION still apply: a dynamic cursor is often ambiguous unless you declare it FOR READ ONLY or FOR UPDATE.

Dynamic statement cache

Full PREPARE is expensive: parse, authorize, optimize, generate. The dynamic statement cache (subsystem parameter CACHEDYN=YES) keeps prepared statements so a later identical PREPARE can skip that work. “Identical” includes statement text (including blanks), the SQL path, special registers that affect the statement, QUALIFIER/DYNAMICRULES behaviour, and REOPT settings. Literal concentration (CONCENTRATE STATEMENTS WITH LITERALS) can turn many similar statements into one cached skeleton.

Monitoring

IFCID 316 and 317 (and IFI reads of the cache), EXPLAIN STMTCACHE, and DSN_STATEMENT_CACHE_TABLE show what is cached, how often it ran, and CPU. -DISPLAY THREAD and statistics traces show prepare versus short-prepare counts. A high ratio of full prepares to executions means the cache is not hitting: text differs, KEEPDYNAMIC is NO and the local copy is dropped, or the cache is too small / flushed.

Invalidation

Cached statements become invalid when a dependent object is dropped or altered, a needed privilege is revoked, or certain RUNSTATS / REBIND operations say so. FREE PACKAGE does not always flush every dynamic statement; DROP TABLE, ALTER TABLE, and explicit EXPLAIN STMTCACHE / instrumentation are the usual ways you notice a miss after a DDL change. Stabilized dynamic SQL (SYSQUERY*) is a later feature that keeps a chosen path across cache loss—covered in the access-path stability material.

KEEPDYNAMIC(YES) is per thread: the local prepared copy survives COMMIT. The global cache is per member (in data sharing, EXPLAIN STMTCACHE reads the member you are on). RELEASE(DEALLOCATE) plus KEEPDYNAMIC(YES) plus CACHEDYN=YES can honor DEALLOCATE for dynamic DML, which is how some high-volume JDBC workloads cut parent-lock cost.

Dynamic SQL security and SQL injection

Dynamic SQL runs with whatever DYNAMICRULES behaviour the package has. RUN uses the end user’s CURRENT SQLID—good for ad-hoc tools, dangerous if that user holds more privilege than the program needed. BIND uses the package owner—good for three-tier apps that must not grant table access to every client id, but then every dynamic statement shares that owner’s power. Always grant the owner only what the statements need.

SQL injection happens when untrusted text is copied into the statement string:

1
2
3
4
5
* Unsafe: user types  ' OR '1'='1
STRING 'SELECT * FROM EMP WHERE LASTNAME = ''' DELIMITED BY SIZE
        USER-NAME DELIMITED BY SIZE
        '''' DELIMITED BY SIZE
    INTO SQL-STMT

Use ? for values. If you must build an identifier (ORDER BY column, table name), compare it to an allow-list in the program and reject anything else. Never concatenate a screen field into SQL as a literal. Audit programs that build WHERE clauses from free-text filters.

Performance and REOPT

Cost drivers:

• Full prepare — avoid with identical text, markers instead of literals, and a warmed cache.
• REOPT(ALWAYS) — a new path every execution; use only when values are wildly different and the statement is not ultra-high frequency.
• REOPT(ONCE) — path from the first set of values; good default for cached dynamic SQL with markers.
• REOPT(AUTO) — Db2 may build a new path when filter factors change.
• REOPT(NONE) — bind/prepare-time defaults; simple equal predicates on unique indexes still get good paths.

KEEPDYNAMIC(YES) plus REOPT(ALWAYS) is invalid. Parameter markers hide values from NONE, which is why a SELECT with ? on a skewed column sometimes picks a tablespace scan until you set ONCE or AUTO. Measure with EXPLAIN STMTCACHE and IFCID 316 before you blanket ALWAYS on a CICS package.

Explain It Like I'm Five

Static SQL is a recipe printed in the cookbook (the package) before dinner. Dynamic SQL is writing a recipe on a sticky note while you cook. PREPARE is reading that sticky note and lining up the pans. EXECUTE is cooking. EXECUTE IMMEDIATE is reading and cooking in one breath, and you cannot leave blank spots for later ingredients. Parameter markers are blank circles you fill with today’s numbers. The SQLDA is a packing list that says how many boxes and what size. DESCRIBE is asking the kitchen what the boxes look like. The statement cache is keeping yesterday’s prepared sticky notes on the fridge so you do not rewrite them. Injection is letting a stranger write on your sticky note, including “and also open the cookie jar.”

Exercises

1. Rewrite an EXECUTE IMMEDIATE UPDATE that concatenates EMPNO into PREPARE with two parameter markers and EXECUTE USING.
2. List the SQLDA fields you must set before EXECUTE USING DESCRIPTOR.
3. Explain when you DESCRIBE INPUT versus DESCRIBE (output) for a dynamic SELECT with a ? in the WHERE clause.
4. A JDBC application PREPAREs the same SQL after every COMMIT and CACHEDYN is YES. What bind option would keep the local copy, and which REOPT value is allowed with it?
5. Find a dynamic statement in your shop that builds SQL from screen input. Identify any concatenation that should become a marker or an allow-list.