Copilot instructions

1---
2applyTo: "**/*.sql, **/mapper/**/*.xml, **/mappers/**/*.xml, **/repository/**/*.xml"
3---
4 
5## Context
6 
7This instruction file applies to SQL files and MyBatis XML mapper files. The primary database platform is **IBM DB2** (both z/OS and Linux/Unix/Windows variants). SQL must be compatible with DB2 dialect. Additional awareness of embedded SQL in COBOL programs (via `EXEC SQL ... END-EXEC` blocks) also applies. Security and correctness of queries is paramount — SQL injection via string concatenation is a critical vulnerability that must never appear.
8 
9---
10 
11## Coding Standards
12 
13- **Always qualify table names** with the schema prefix: `SCHEMA.TABLE_NAME` — never bare table references
14- **Parameterized queries only** — never concatenate user input into SQL strings
15- **No `SELECT *`** — always enumerate required columns explicitly
16- **Row limiting:** Use DB2 syntax `FETCH FIRST n ROWS ONLY` — never `ROWNUM` (Oracle) or `LIMIT` (MySQL)
17- **Upserts:** Prefer `MERGE` statement over separate `INSERT` / `UPDATE` logic
18- **Index hints:** Only with a DBA approval comment explaining the rationale
19- **Schema migration scripts:** Use sequential versioned names (`V001__description.sql`) for Flyway/Liquibase
20- **Transactions:** Explicit `COMMIT` / `ROLLBACK` in batch scripts; in Java, defer to Spring `@Transactional`
21- **NULL handling:** Always account for nullable columns — use `COALESCE` or explicit `NULL` checks
22 
23---
24 
25## Preferred Patterns
26 
27### Parameterized Query (JDBC)
28 
29```java
30// CORRECT: PreparedStatement with named parameters via NamedParameterJdbcTemplate
31String sql = "SELECT c.customer_id, c.customer_name, c.balance " +
32             "FROM SCHEMA.CUSTOMERS c " +
33             "WHERE c.customer_id = :customerId " +
34             "AND c.status = :status";
35 
36MapSqlParameterSource params = new MapSqlParameterSource()
37    .addValue("customerId", customerId)
38    .addValue("status", status.getCode());
39 
40return jdbc.queryForObject(sql, params, new CustomerRowMapper());
41```
42 
43### DB2-Compliant SELECT with FETCH FIRST
44 
45```sql
46-- Paginated query with DB2 row limiting syntax
47SELECT
48    c.customer_id,
49    c.customer_name,
50    c.email_address,
51    c.created_timestamp
52FROM
53    SCHEMA.CUSTOMERS c
54WHERE
55    c.status = 'ACTIVE'
56    AND c.created_timestamp >= :cutoffDate
57ORDER BY
58    c.customer_name ASC
59FETCH FIRST 100 ROWS ONLY
60```
61 
62### MERGE for Upsert
63 
64```sql
65MERGE INTO SCHEMA.CUSTOMER_PREFERENCES AS target
66USING (VALUES (:customerId, :preferenceKey, :preferenceValue))
67    AS source (customer_id, pref_key, pref_value)
68ON (target.customer_id = source.customer_id
69    AND target.pref_key = source.pref_key)
70WHEN MATCHED THEN
71    UPDATE SET
72        pref_value = source.pref_value,
73        updated_timestamp = CURRENT TIMESTAMP
74WHEN NOT MATCHED THEN
75    INSERT (customer_id, pref_key, pref_value, created_timestamp)
76    VALUES (source.customer_id, source.pref_key, source.pref_value, CURRENT TIMESTAMP)
77```
78 
79### DB2 Window Function
80 
81```sql
82SELECT
83    o.order_id,
84    o.customer_id,
85    o.order_amount,
86    SUM(o.order_amount) OVER (
87        PARTITION BY o.customer_id
88        ORDER BY o.created_date
89        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
90    ) AS running_total
91FROM
92    SCHEMA.ORDERS o
93WHERE
94    o.status = 'COMPLETED'
95```
96 
97### MyBatis XML Mapper
98 
99```xml
100<?xml version="1.0" encoding="UTF-8" ?>
101<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
102    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
103<mapper namespace="com.example.repository.CustomerMapper">
104 
105    <!-- resultMap preferred over inline resultType for complex mappings -->
106    <resultMap id="customerResultMap" type="com.example.domain.Customer">
107        <id property="id" column="customer_id"/>
108        <result property="name" column="customer_name"/>
109        <result property="email" column="email_address"/>
110        <result property="balance" column="balance"/>
111        <result property="status" column="status" typeHandler="com.example.handler.StatusTypeHandler"/>
112        <association property="address" resultMap="addressResultMap"/>
113    </resultMap>
114 
115    <select id="findById" parameterType="long" resultMap="customerResultMap">
116        SELECT
117            c.customer_id,
118            c.customer_name,
119            c.email_address,
120            c.balance,
121            c.status,
122            a.street,
123            a.city,
124            a.postal_code
125        FROM
126            SCHEMA.CUSTOMERS c
127            LEFT JOIN SCHEMA.ADDRESSES a ON a.customer_id = c.customer_id
128        WHERE
129            c.customer_id = #{id}
130        FETCH FIRST 1 ROWS ONLY
131    </select>
132 
133    <select id="findByStatus" resultMap="customerResultMap">
134        SELECT
135            c.customer_id,
136            c.customer_name,
137            c.email_address,
138            c.balance,
139            c.status
140        FROM
141            SCHEMA.CUSTOMERS c
142        WHERE
143            c.status = #{status}
144        <if test="createdAfter != null">
145            AND c.created_timestamp >= #{createdAfter}
146        </if>
147        ORDER BY c.customer_name ASC
148    </select>
149 
150    <insert id="insert" parameterType="com.example.domain.Customer">
151        INSERT INTO SCHEMA.CUSTOMERS
152            (customer_id, customer_name, email_address, balance, status, created_timestamp)
153        VALUES
154            (#{id}, #{name}, #{email}, #{balance}, #{status}, CURRENT TIMESTAMP)
155    </insert>
156 
157</mapper>
158```
159 
160### DB2 Embedded SQL (COBOL context)
161 
162```cobol
163      * CORRECT: Host variables with proper SQLCODE check
164           MOVE WS-INPUT-ID TO HV-CUSTOMER-ID
165           EXEC SQL
166               SELECT CUSTOMER_NAME,
167                      BALANCE,
168                      STATUS
169               INTO   :HV-CUST-NAME,
170                      :HV-BALANCE,
171                      :HV-STATUS
172               FROM   SCHEMA.CUSTOMERS
173               WHERE  CUSTOMER_ID = :HV-CUSTOMER-ID
174           END-EXEC
175           EVALUATE SQLCODE
176               WHEN 0
177                   CONTINUE
178               WHEN 100
179                   MOVE 'NOT FOUND' TO WS-ERROR-MSG
180               WHEN OTHER
181                   MOVE SQLCODE TO WS-SQLCODE
182                   PERFORM 9900-SQL-ERROR
183           END-EVALUATE.
184```
185 
186---
187 
188## Anti-Patterns — Do NOT Generate
189 
190```java
191// WRONG: string concatenation in SQL — SQL injection vulnerability [BLOCKER]
192String sql = "SELECT * FROM CUSTOMERS WHERE ID = " + customerId;
193Statement stmt = conn.createStatement();
194stmt.execute(sql);
195 
196// WRONG: SELECT * — fetches unnecessary columns, breaks on schema changes
197"SELECT * FROM SCHEMA.ORDERS WHERE status = :status"
198 
199// WRONG: ROWNUM syntax (Oracle) — DB2 uses FETCH FIRST
200"SELECT id FROM SCHEMA.ORDERS WHERE ROWNUM <= 10"
201 
202// WRONG: LIMIT syntax (MySQL/PostgreSQL) — DB2 uses FETCH FIRST
203"SELECT id FROM SCHEMA.ORDERS LIMIT 10"
204 
205// WRONG: unqualified table name — breaks cross-schema deployments
206"SELECT id FROM ORDERS WHERE status = :status"
207 
208// WRONG: inline resultType for complex mappings — use resultMap
209<select id="find" resultType="com.example.Customer">
210```
211 
212```sql
213-- WRONG: non-parameterized dynamic SQL in a stored procedure
214SET V_SQL = 'SELECT * FROM ' || V_TABLE_NAME || ' WHERE ID = ' || V_INPUT_ID;
215-- Use parameterized EXECUTE IMMEDIATE with USING clause instead
216```
217 
218---
219 
220## Dependencies & Versions
221 
222| Technology | Version | Notes |
223|-----------|---------|-------|
224| DB2 | 12.x (z/OS) / 11.x (LUW) | Use DB2-specific syntax for row limiting, window functions |
225| MyBatis | 3.5.x | XML mapper format documented above |
226| Spring JDBC | (with Spring) | `NamedParameterJdbcTemplate` preferred over plain `JdbcTemplate` |
227| Flyway | 9.x+ | Migration scripts: `V{version}__{description}.sql` |
228| Liquibase | 4.x+ | Alternative migration tool — YAML or XML changesets |
229 
230---
231 
232## Test Conventions
233 
234- Test SQL and MyBatis mappers with `@DataJpaTest` (JPA) or an embedded H2 database (JdbcTemplate/MyBatis)
235- When H2 compatibility mode is insufficient for DB2 syntax, use Testcontainers with `ibmcom/db2` image
236- Verify both the happy path (row found) and not-found cases for every query
237- Test `MERGE` statements by setting up pre-existing rows and verifying both insert and update branches
238- Validate that pagination queries respect `FETCH FIRST` limits and return the correct page
239