Case Study 32.2: Static vs Dynamic SQL — A Performance Decision

Background

Meridian National Bank's audit department has requested a new query program (ADTQUERY) that allows auditors to search the MERIDIAN.TRANSACTIONS table based on various combinations of criteria:

• Account number (exact match)
• Transaction date range
• Transaction amount range
• Transaction type (debit, credit, fee, interest)
• Branch ID
• Any combination of the above

The TRANSACTIONS table contains 450 million rows spanning 7 years of history. It is partitioned by transaction date (monthly partitions) with indexes on ACCOUNT_NUM, TXN_DATE, and a composite index on (BRANCH_ID, TXN_DATE).

The auditors currently use a static SQL program with 15 pre-coded queries covering the most common search patterns. They are frustrated because they cannot combine criteria flexibly. The development team proposes replacing this with a dynamic SQL program.

The Static SQL Approach (Current)

The current program has 15 cursor declarations:

      * Query 1: By account number
       EXEC SQL
           DECLARE CSR-Q01 CURSOR FOR
               SELECT TXN_ID, ACCOUNT_NUM, TXN_DATE, TXN_TYPE,
                      TXN_AMOUNT, TXN_DESC, BRANCH_ID
               FROM MERIDIAN.TRANSACTIONS
               WHERE ACCOUNT_NUM = :HV-ACCT-NUM
               ORDER BY TXN_DATE DESC
       END-EXEC.

      * Query 2: By date range
       EXEC SQL
           DECLARE CSR-Q02 CURSOR FOR
               SELECT TXN_ID, ACCOUNT_NUM, TXN_DATE, TXN_TYPE,
                      TXN_AMOUNT, TXN_DESC, BRANCH_ID
               FROM MERIDIAN.TRANSACTIONS
               WHERE TXN_DATE BETWEEN :HV-START-DATE
                                  AND :HV-END-DATE
               ORDER BY TXN_DATE DESC
       END-EXEC.

      * Query 3: By account and date range
       EXEC SQL
           DECLARE CSR-Q03 CURSOR FOR
               SELECT TXN_ID, ACCOUNT_NUM, TXN_DATE, TXN_TYPE,
                      TXN_AMOUNT, TXN_DESC, BRANCH_ID
               FROM MERIDIAN.TRANSACTIONS
               WHERE ACCOUNT_NUM = :HV-ACCT-NUM
                 AND TXN_DATE BETWEEN :HV-START-DATE
                                  AND :HV-END-DATE
               ORDER BY TXN_DATE DESC
       END-EXEC.

      * ... 12 more cursor declarations for other combinations

The program uses an EVALUATE statement to select which cursor to open based on which input fields the auditor provided.

Advantages of this approach: - Every query has an optimized static access path - No runtime SQL parsing overhead - Authorization is managed at bind time - Predictable performance — each query's plan is known and tested

Disadvantages: - 15 cursor declarations and 15 OPEN/FETCH/CLOSE code paths make maintenance difficult - Cannot support new combinations without code changes - Combinatorial explosion: 5 filter criteria with optional presence means 2^5 = 32 possible combinations. The current 15 queries do not even cover all of them.

The Dynamic SQL Approach (Proposed)

The proposed replacement builds the WHERE clause dynamically:

       BUILD-QUERY.
           MOVE 0 TO WS-PARM-COUNT.
           STRING 'SELECT TXN_ID, ACCOUNT_NUM, TXN_DATE, '
                  'TXN_TYPE, TXN_AMOUNT, TXN_DESC, BRANCH_ID '
                  'FROM MERIDIAN.TRANSACTIONS WHERE 1=1 '
               DELIMITED BY SIZE INTO WS-SQL-TEXT.

           IF HV-ACCT-NUM NOT = SPACES
               STRING WS-SQL-TEXT DELIMITED BY '  '
                      ' AND ACCOUNT_NUM = ? '
                      DELIMITED BY SIZE
                   INTO WS-SQL-TEXT
               ADD 1 TO WS-PARM-COUNT
           END-IF.

           IF HV-START-DATE NOT = SPACES
               STRING WS-SQL-TEXT DELIMITED BY '  '
                      ' AND TXN_DATE >= ? '
                      DELIMITED BY SIZE
                   INTO WS-SQL-TEXT
               ADD 1 TO WS-PARM-COUNT
           END-IF.

           IF HV-END-DATE NOT = SPACES
               STRING WS-SQL-TEXT DELIMITED BY '  '
                      ' AND TXN_DATE <= ? '
                      DELIMITED BY SIZE
                   INTO WS-SQL-TEXT
               ADD 1 TO WS-PARM-COUNT
           END-IF.

           IF HV-TXN-TYPE NOT = SPACES
               STRING WS-SQL-TEXT DELIMITED BY '  '
                      ' AND TXN_TYPE = ? '
                      DELIMITED BY SIZE
                   INTO WS-SQL-TEXT
               ADD 1 TO WS-PARM-COUNT
           END-IF.

           IF HV-BRANCH-ID NOT = 0
               STRING WS-SQL-TEXT DELIMITED BY '  '
                      ' AND BRANCH_ID = ? '
                      DELIMITED BY SIZE
                   INTO WS-SQL-TEXT
               ADD 1 TO WS-PARM-COUNT
           END-IF.

           STRING WS-SQL-TEXT DELIMITED BY '  '
                  ' ORDER BY TXN_DATE DESC '
                  DELIMITED BY SIZE
               INTO WS-SQL-TEXT.

Then PREPARE and execute with a dynamic cursor:

       EXEC SQL
           PREPARE DYNSTMT FROM :WS-SQL-TEXT
       END-EXEC.

       EXEC SQL
           DECLARE CSR-DYNAMIC CURSOR FOR DYNSTMT
       END-EXEC.

      * OPEN with appropriate USING clause based on WS-PARM-COUNT
      * (requires SQLDA for variable parameter count)

Advantages: - Supports all 32 possible combinations with one code path - Easy to add new filter criteria - Cleaner, more maintainable code

Disadvantages: - Runtime PREPARE cost on every execution - Access path chosen at runtime — may not be optimal - Requires EXECUTE authority on the underlying tables (or DYNAMICRULES(BIND)) - More complex parameter handling with SQLDA

Performance Analysis

The team ran benchmarks with representative queries:

Key observations: - For short-running indexed lookups, the PREPARE overhead is proportionally significant (50% overhead on a 3ms query) - For longer-running queries that scan many rows, the PREPARE overhead is negligible (3-4% on queries over 1 second) - The optimizer chose the same access path for dynamic SQL in all tested cases (statistics were up-to-date)

The Hybrid Solution

After analysis, the team implemented a hybrid approach:

1. PREPARE once, EXECUTE many. The program keeps a cache of previously prepared statements. If the same combination of criteria is requested again, it reuses the prepared statement without re-PREPARing.

2. Use parameter markers (?) exclusively. Never concatenate literal values into the SQL text. This maximizes DB2's dynamic statement cache hit rate.

3. BIND with DYNAMICRULES(BIND). This allows the dynamic SQL to use the package owner's authorization, matching the security model of the static approach.

4. Keep the top 3 most common queries as static SQL. Analysis of audit query logs showed that 78% of queries used just three patterns: by account, by date range, and by account + date range. These remain static for maximum performance.

5. Route remaining queries to dynamic SQL. The less common combinations (22% of queries) go through the dynamic path.

       EVALUATE TRUE
           WHEN WS-QUERY-PATTERN = 'ACCT-ONLY'
               PERFORM STATIC-QUERY-ACCT
           WHEN WS-QUERY-PATTERN = 'DATE-RANGE'
               PERFORM STATIC-QUERY-DATE
           WHEN WS-QUERY-PATTERN = 'ACCT-DATE'
               PERFORM STATIC-QUERY-ACCT-DATE
           WHEN OTHER
               PERFORM DYNAMIC-QUERY-BUILD
       END-EVALUATE.

Results

The hybrid approach delivered: - 78% of queries use static SQL with optimal performance - 22% of queries use dynamic SQL with acceptable overhead - 100% of combinations are supported (up from 15 out of 32) - Maintenance effort reduced by 60% (from 15 code paths to 4) - Auditor satisfaction significantly improved — they can now combine any criteria

Discussion Questions

1. At what query duration does the PREPARE overhead become negligible? Is there a rule of thumb?
2. How does the DB2 dynamic statement cache affect the performance comparison? What if the cache is cold vs warm?
3. What are the security implications of DYNAMICRULES(BIND) vs DYNAMICRULES(RUN)?
4. Could you achieve similar flexibility with static SQL using "catch-all" WHERE clauses like WHERE (ACCOUNT_NUM = :hv OR :hv = ' ')? What are the optimizer implications?
5. How would you monitor the dynamic SQL cache hit rate to validate the design decision?