Skip to content
Skills Library
Programming · Building & coding

sqlrpgle-db2

/sqlrpgle-db2

Embedded SQL in RPG (SQLRPGLE) and Db2 for i - SET OPTION, host variables, cursors, multi-row FETCH, SQLSTATE error handling, commitment control, DDL instead of DDS, and IBM i SQL services (QSYS2). Use when writing or reviewing SQL in RPG, converting native I/O to SQL, or writing DDL for Db2 for i.

Licence
GPL-3.0
Last repository change
29.10.2026

Assessment and security

85/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content20 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
2
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 29.10.2026, without references to retired models or APIs.

Licence

GPL-3.0, otwarta.

What it does and when to use it

Embedded SQL in RPG (SQLRPGLE) and Db2 for i - SET OPTION, host variables, cursors, multi-row FETCH, SQLSTATE error handling, commitment control, DDL instead of DDS, and IBM i SQL services (QSYS2). Use when writing or reviewing SQL in RPG, converting native I/O to SQL, or writing DDL for Db2 for i.

Original author description

Embedded SQL in RPG (SQLRPGLE) and Db2 for i - SET OPTION, host variables, cursors, multi-row FETCH, SQLSTATE error handling, commitment control, DDL instead of DDS, and IBM i SQL services (QSYS2). Use when writing or reviewing SQL in RPG, converting native I/O to SQL, or writing DDL for Db2 for i.

How it works

  1. IBM i services worth knowing
Original SKILL.md headings · 41 lines, 224 words
SKILL.md preview
---
name: sqlrpgle-db2
description: Embedded SQL in RPG (SQLRPGLE) and Db2 for i - SET OPTION, host variables, cursors, multi-row FETCH, SQLSTATE error handling, commitment control, DDL instead of DDS, and IBM i SQL services (QSYS2). Use when writing or reviewing SQL in RPG, converting native I/O to SQL, or writing DDL for Db2 for i.
---

# SQLRPGLE and Db2 for i

Patterns are in `reference/sql-patterns.md`. Apply these rules every time:

<Steps>
<Step>
**Start every SQLRPGLE source with `SET OPTION`** before any other SQL statement:
`exec sql SET OPTION COMMIT = *NONE, NAMING = *SYS, CLOSQLCSR = *ENDMOD, DATFMT = *ISO;`
Choose `COMMIT = *CHG` when the program updates several tables that must stay consistent.
</Step>
<Step>
**Check `SQLSTATE` after every statement.** `'00000'` = success, `'02000'` = no row,
class `'01'` = warning (still check data), anything else = error. Use the
`checkSql()` helper pattern from the reference file so handling is uniform.
</Step>
<Step>
**Use host variables or parameter markers, never string concatenation** for values.
Dynamic SQL (`PREPARE` / `EXECUTE`) is only for variable table/column names — validate
those against a list before use.
</Step>
<Step>
**Fetch in blocks** for anything more than a handful of rows:
`FETCH NEXT FROM c1 FOR :max ROWS INTO :rows` with a DS array, then
`GET DIAGNOSTICS :fetched = ROW_COUNT`.
</Step>
<Step>
**Close cursors** in every path, including error paths.
</Step>
<Step>
**New tables are DDL**, not DDS: source in `qsqlsrc/<name>.table`, with a short system
name (`FOR SYSTEM NAME`), `LABEL ON` text, and a primary key.
</Step>
</Steps>

## IBM i services worth knowing
`QSYS2.OBJECT_STATISTICS`, `QSYS2.PROGRAM_REFERENCES`, `QSYS2.SYSCOLUMNS`,
`QSYS2.ACTIVE_JOB_INFO`, `QSYS2.JOBLOG_INFO`, `QSYS2.QCMDEXC` (run CL from SQL),
`QSYS2.IFS_READ` / `IFS_WRITE`, `SYSTOOLS.HTTPGETCLOB` / `QSYS2.HTTP_GET`,
`JSON_TABLE`, `JSON_OBJECT`, `JSON_ARRAYAGG`.
Files in this package (2)
  • SKILL.md 2 KB
  • reference/sql-patterns.md 2 KB

Versions

The database has no other skills named sqlrpgle-db2.

Database snapshot: 13.11.2026 · package version 8a3bd99e1aff (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 90sql-postgres
    Write and review Postgres SQL (DDL, DML, indexes, constraints). Use for .sql, schemas, or Postgres mentions.
  • 100rpg-fixed-to-free
    Convert fixed-format or mixed RPG III / RPG IV source (H, F, D, C, P, O specs, /free blocks) to fully free-format RPG IV (**free) without changing behaviour. Use for any fixed-to-free conversion, modernization, or "convert this RPG" request.
  • 94pybr0s
    Use when working on PyBR0S (Python/Pygame 2D fighting/RPG).
  • 100database-designer
    Design a database schema: tables, relationships, indexes, normalization, SQL vs NoSQL, migration plans. Use whenever the user asks how to structure their data, which database to pick, or before writing DDL. Only invoke via the orcinus router.
  • 100rpg
    Build an RPG: stats and leveling, inventory and equipment, quests, branching dialogue, save/load, and combat. Use for an RPG/JRPG, or designing stat, inventory, quest, or combat systems.
  • 100sql-safety-review
    评估 SQL 查询的安全性、规范性和性能风险。覆盖 OLAP(Spark/Hive、Impala、ClickHouse)和 OLTP(MySQL)两大场景。审查维度:命名规范、编码格式、查询优化、索引策略、DDL/DML 规范、引擎特性、锁与事务。当用户贴出 SQL 问"能不能跑"、"会不会查爆"、"帮我 review"、"格式对不对"、"命名规范吗"、"这个 EXPLAIN 怎么看"、"为什么这么慢"、"能不能在生产跑"、"帮我看看这条 SQL"、"这个查询有没有问题"、"Code Review 一下"、"建表语句有问题吗"、"ETL…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.