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