News
Abstract
Db2 for i Functional Enhancements Landing Page
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Functional Enhancements
Db2 for i Functional Enhancements
| Db2 for i Enhancement | IBM i 7.6 | IBM i 7.5 | IBM i 7.4 | IBM i 7.3 |
|---|---|---|---|---|
| --- Enhancements from 2025 --- | ||||
| UPDATE and DELETE (SQL) statements supported as data-change table reference | Base | Not Supported | Not Supported | Not Supported |
| CONNECT statement supports additional factor for authentication | Base | Not Supported | Not Supported | Not Supported |
| SQLSTATE_INFO table | Base | SF99950 Level 9 | Not Supported | Not Supported |
| --- Enhancements from 2024 --- | ||||
| GENERATE_UUID and GENERATE_UUID_BINARY scalar functions | Base | SF99950 Level 8 | SF99704 Level 29 | Not Supported |
| ANY_VALUE aggregate function | Base | SF99950 Level 7 | SF99704 Level 28 | Not Supported |
| CREATE ALIAS with *LAST member | Base | SF99950 Level 7 | SF99704 Level 28 | Not Supported |
| --- Enhancements from 2023 --- | ||||
| ENCRYPT_AES256 scalar function | Base | SF99950 Level 5 | SF99704 Level 26 | Not Supported |
| HTML_ENTITY_DECODE and HTML_ENTITY_ENCODE scalar functions | Base | SF99950 Level 5 | SF99704 Level 26 | Not Supported |
| QSYS2 HTTP Functions - New HTTP header response controls | Base | SF99950 Level 5 | SF99704 Level 26 | Not Supported |
| QSYS2.PARSE_STATEMENT() table function enhanced | Base | SF99950 Level 5 | SF99704 Level 26 | Not Supported |
| SYSTOOLS.SQLCODE_INFO() | Base | SF99950 Level 5 | SF99704 Level 26 | Not supported |
| RESTRICT ON DROP for functions and procedures | Base | SF99950 Level 4 | Not Supported | Not Supported |
| ADD_DAYS, ADD_HOURS, ADD_MINUTES, ADD_SECONDS, ADD_YEARS scalar functions | Base | SF99950 Level 4 | SF99704 Level 25 | Not Supported |
| QSYS2 HTTP Functions - support BLOB type | Base | SF99950 Level 4 | SF99704 Level 25 | Not Supported |
| Extend Watson Geospatial Analytics | Base | SF99950 Level 4 | SF99704 Level 25 | Not Supported |
| --- Enhancements from 2022 --- | ||||
| Geospatial Analytics | Base | SF99950 Level 3 | SF99704 Level 23 | Not Supported |
| REMOTE TABLE | Base | SF99950 Level 3 | SF99704 Level 23 | Not Supported |
| REPLICATION_OVERRIDE global variable for system generated values | Base | SF99950 Level 3 | SF99704 Level 23 | Not Supported |
| TIMESTAMPDIFF_BIG scalar function | Base | SF99950 Level 3 | SF99704 Level 23 | Not Supported |
| FIRST_DAY scalar function | Base | SF99950 Level 3 | SF99704 Level 23 | Not Supported |
| JSON_UPDATE scalar function | Base | SF99950 Level 3 | SF99704 Level 23 | Not Supported |
| QSYS2 HTTP Functions - HTTP options support for "headers" | Base | SF99950 Level 3 | SF99704 Level 23 | Not Supported |
| Boolean data type | Base | Base | Not Supported | Not Supported |
| RESTRICT ON DROP | Base | Base | Not Supported | Not Supported |
| TRY_CAST specification | Base | Base | SF99704 Level 20 | Not Supported |
| RUNSQLSTM command - MARGINS(*SRCFILE) | Base | Base | SF99704 Level 20 | SF99703 Level 28 |
| HTTP_PATCH and HTTP_PATCH_VERBOSE functions | Base | Base | SF99704 Level 20 | SF99703 Level 28 |
| New response header format option for verbose HTTP functions | Base | Base | SF99704 Level 20 | SF99703 Level 28 |
| --- Enhancements from 2021 --- | ||||
| New HTTP functions based in QSYS2 | Base | Base | SF99704 Level 15 | SF99703 Level 26 |
| SIGNAL detail from within a trigger can be accessed with GET DIAGNOSTICS | Base | Base | SF99704 Level 13 | SF99703 Level 24 |
| SYSIBM.CLIENT_IPADDR generated columns enhanced for non-TCP/IP based connections | Base | Base | SF99704 Level 13 | SF99703 Level 24 |
| RUNSQLSTM command - return TGTRLS in the listing | Base | Base | SF99704 Level 13 | SF99703 Level 24 |
| --- Enhancements from 2020 --- | ||||
| DROP support for IF EXISTS | Base | Base | SF99704 Level 10 | SF99703 Level 22 |
| CREATE SEQUENCE supports FOR SYSTEM NAME | Base | Base | SF99704 Level 10 | SF99703 Level 22 |
| SQL Precompiler additional support for WHENEVER | Base | Base | SF99704 Level 10 | SF99703 Level 22 |
| SQL precompilers support DBGVIEW(*STMT) and DBGVIEW(*LIST) | Base | Base | SF99704 Level 10 | SF99703 Level 22 |
| SELECTIVITY clause on queries | Base | Base | SF99704 Level 10 | SF99703 Level 22 |
| SYSTOOLS.SPLIT table function supports ESCAPE parameter | Base | Base | SF99704 Level 10 | SF99703 Level 22 |
| Weakly typed distinct types | Base | Base | SF99704 Level 7 | Not Supported |
| JSON_TABLE enhanced to support JSON arrays | Base | Base | SF99704 Level 7 | SF99703 Level 18 |
| INTERPRET built-in function | Base | Base | SF99704 Level 7 | SF99703 Level 18 |
| HASH_ROW built-in function | Base | Base | SF99704 Level 7 | Not Supported |
| Extended indicators in SQL PL | Base | Base | SF99704 Level 7 | SF99703 Level 18 |
| USING SUBSET on EXECUTE and OPEN with Extended indicators | Base | Base | SF99704 Level 7 | SF99703 Level 18 |
| Support TAG within ILE RPG Embedded SQL | Base | Base | SF99704 Level 7 | SF99703 Level 18 |
| ARRAY_TRIM and ARRAY_MAX_CARDINALITY SQL built-in function synonyms | Base | Base | SF99704 Level 7 | SF99703 Level 18 |
| QSYS2.ANALYZE_CATALOG | Base | Base | SF99704 Level 7 | SF99703 Level 18 |
| QSYS2.COMPARE_FILE | Base | Base | SF99704 Level 7 | Not Supported |
| SYSTOOLS.VALIDATE_DATA, SYSTOOLS.VALIDATE_DATA_FILE, and SYSTOOLS.VALIDATE_DATA_LIBRARY | Base | Base | SF99704 Level 7 | SF99703 Level 18 |
| --- Enhancements from 2019 --- | ||||
| Allow UTF-8 source for SQL precompiles | Base | Base | SF99704 Level 4 | SF99703 Level 16 |
| Add PROGRAM NAME for SQL routines | Base | Base | SF99704 Level 4 | SF99703 Level 16 |
| New Synonyms added for Db2 Family compatibility | Base | Base | SF99704 Level 4 | SF99703 Level 16 |
| Support greater than 32 routines over an external service program | Base | Base | SF99704 Level 4 | SF99703 Level 16 |
| HASH built-in functions | Base | Base | SF99704 Level 1 | Not Supported |
| SQE improvements | Base | Base | SF99704 Level 1 | Not Supported |
| INSERT with DEFAULT VALUES | Base | Base | SF99704 Level 1 | SF99703 Level 15 |
| CREATE INDEX - optional alias and rename controls for field names | Base | Base | SF99704 Level 1 | SF99703 Level 15 |
| New QSQPRCED() - QAQQINI statement level override controls | Base | Base | SF99704 Level 1 | SF99703 Level 15 |
| SQL precompiler enhancements | Base | Base | SF99704 Level 1 | SF99703 Level 15 |
| SYSPROC.WLM_SET_CLIENT_INFO() procedure enhanced | Base | Base | SF99704 Level 1 | SF99703 Level 15 |
| QSYS2.PARSE_STATEMENT() table function enhanced | Base | Base | SF99704 Level 1 | SF99703 Level 15 |
| --- Enhancements from 2018 --- | ||||
| OmniFind - Programmatic search of the IFS | Base | Base | Base | SF99703 Level 11 |
| SQL precompiler SQL0312 and SQL5011 message enhancement | Base | Base | Base | SF99703 Level 11 |
| Optional parameter support for external procedures and functions | Base | Base | Base | SF99703 Level 11 |
| DECFLOAT_FORMAT() built-in function | Base | Base | Base | SF99703 Level 11 |
| NOW() built-in function enhanced | Base | Base | Base | SF99703 Level 11 |
| EXTRACT() built-in function enhanced | Base | Base | Base | SF99703 Level 11 |
| VARCHAR_FORMAT() and TO_CHAR() built-in functions enhanced | Base | Base | Base | SF99703 Level 11 |
| Table correlation name enhanced to be optional | Base | Base | Base | SF99703 Level 11 |
| JSON - Uniqueness controls for Key names | Base | Base | Base | SF99703 Level 11 |
| JTOpen JDBC driver – Alternate server support | Base | Base | Base | SF99703 Level 11 |
| --- Enhancements from 2017 --- | ||||
| JSON publishing functions | Base | Base | Base | SF99703 Level 7 |
| JSON scalar functions | Base | Base | Base | SF99703 Level 7 |
| DELETE and UPDATE pagination | Base | Base | Base | SF99703 Level 7 |
| Full LIMIT and OFFSET support | Base | Base |
Base
|
SF99703 Level 7 |
| JSON query predicates | Base | Base | Base | SF99703 Level 4 |
| LISTAGG() built-in function | Base | Base | Base | SF99703 Level 4 |
| Enhanced LTRIM() and RTRIM() built-in functions | Base | Base | Base | SF99703 Level 4 |
| Ability to use 3-part names from within trigger programs | Base | Base | Base | SF99703 Level 4 |
| --- Enhancements from 2016 --- | ||||
| JSON_TABLE() table function | Base | Base | Base | SF99703 Level 3 |
| QSYS2.PARSE_STATEMENT() table function | Base | Base | Base | SF99703 Level 3 |
| Enhanced INCLUDE SQL statement | Base | Base | Base | SF99703 Level 3 |
| Debugger enhanced for SQL procedures, functions and triggers | Base | Base | Base | SF99703 Level 3 |
| OVERRIDE_QAQQINI() procedure | Base | Base | Base | Base |
| --- Enhancements from 2015 --- | ||||
| OFFSET and LIMIT | Base | Base | Base | Base |
| Remote 3-part name support on ASSOCIATE LOCATOR | Base | Base | Base | Base |
| LOCATE_IN_STRING() built-in function | Base | Base | Base | Base |
| OVERLAY() built-in function | Base | Base | Base | Base |
| Flexible views | Base | Base | Base | Base |
| Direct control of system names for global variables | Base | Base | Base | Base |
| Create OR REPLACE table | Base | Base | Base | Base |
| New binary scalar functions | Base | Base | Base | Base |
| --- Enhancements from 2014 --- | ||||
| Pipelined table functions | Base | Base | Base | Base |
| Regular expressions | Base | Base | Base | Base |
| LPAD & RPAD scalar functions | Base | Base | Base | Base |
| JOB_NAME & SERVER_MODE_JOB_NAME - DB2 built-in global variables | Base | Base | Base | Base |
| LOCK TABLE ability to target non-FIRST members | Base | Base | Base | Base |
| QSYS2.GENERATE_SQL() procedure | Base | Base | Base | Base |
| QSYS2.DELIMIT_NAME() UDF | Base | Base | Base | Base |
Older Enhancements:
- Dynamic Compound statement
- Db2 Connect - Date and time format controls
- Direct control of system names for tables, views and indexes
- Multiple events supported in a single SQL trigger
- QSQPRCED() accepts client special registers
- Db2 Connect - system naming attribute
- Named arguments and defaults for parameters - Procedures
- CREATE TABLE with remote SUBSELECT
- Qualified name option added to Generate SQL
- OmniFind for IBM i - searching multiple member source physical files
- JTOpen Lite and JTLite - enabling mobile devices which use java
- Java stored procedures and functions - System naming option
- CPYTOIMPF and CPYFRMIMPF commands - Include column headings
- Modification of Global Variables within triggers and functions
- System naming convention expanded to permit (slash) and (dot) qualifiers
- New QAQQINI option - SQL_GVAR_BUILD_RULE
- XMLTABLE
- INSERT with remote SUBSELECT
- SQL procedure and function obfuscation
- Add ORDERBY parameter to CPYTOIMPF command
- Run SQL (RUNSQL) - new command
- Retrive short name support for long schema name (QDBRTVSN)
- Enable GET DIAGNOSTICS MESSAGE_TEXT to work against external UDF/UDTFs
- ADD LIBRARY LIST support to JDBC 4.0 getSchemas() method
- Return ROW_COUNT on CREATE TABLE / DECLARE GLOBAL TEMPORARY TABLE statements WITH DATA
- Allow LANGUAGE SQL functions to reference files in an IASP and files in *SYSBAS
- CONNECT BY
- OmniFind searching of non-Db2 tables
- OVRDBF SEQONLY(*YES, buffer length)
- Support PROGRAM NAME on CREATE TRIGGER
- CHGPFM and CHGLFM UNIT support
- QSYS2.SYSPARTITIONDISK and QSYS2.SYSPARTITIONDEXDISK
- EARLIEST_POSSIBLE_RELEASE
- SIGNAL support for native triggers
- QSYS2.INDEX_ADVICE() procedure
- SYSTOOLS index advice procedures
- Index Advisor enhancements
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CHeAAM","label":"IBM i Db2"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.2.0;and future releases"}]
Was this topic helpful?
Document Information
Modified date:
30 March 2025
UID
ibm11115697