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.5 | IBM i 7.4 | IBM i 7.3 | IBM i 7.2 |
---|---|---|---|---|
--- Enhancements from 2024 --- | ||||
ANY_VALUE aggregate function | SF99950 Level 7 | SF99704 Level 28 | Not Supported | Not Supported |
CREATE ALIAS with *LAST member | SF99950 Level 7 | SF99704 Level 28 | Not Supported | Not Supported |
--- Enhancements from 2023 --- | ||||
ENCRYPT_AES256 scalar function | SF99950 Level 5 | SF99704 Level 26 | Not Supported | Not Supported |
HTML_ENTITY_DECODE and HTML_ENTITY_ENCODE scalar functions | SF99950 Level 5 | SF99704 Level 26 | Not Supported | Not Supported |
QSYS2 HTTP Functions - New HTTP header response controls | SF99950 Level 5 | SF99704 Level 26 | Not Supported | Not Supported |
QSYS2.PARSE_STATEMENT() table function enhanced | SF99950 Level 5 | SF99704 Level 26 | Not Supported | Not Supported |
RESTRICT ON DROP for functions and procedures | SF99950 Level 4 | Not Supported | Not Supported | Not Supported |
ADD_DAYS, ADD_HOURS, ADD_MINUTES, ADD_SECONDS, ADD_YEARS scalar functions | SF99950 Level 4 | SF99704 Level 25 | Not Supported | Not Supported |
QSYS2 HTTP Functions - support BLOB type | SF99950 Level 4 | SF99704 Level 25 | Not Supported | Not Supported |
Extend Watson Geospatial Analytics | SF99950 Level 4 | SF99704 Level 25 | Not Supported | Not Supported |
--- Enhancements from 2022 --- | ||||
Geospatial Analytics | SF99950 Level 3 | SF99704 Level 23 | Not Supported | Not Supported |
REMOTE TABLE | SF99950 Level 3 | SF99704 Level 23 | Not Supported | Not Supported |
REPLICATION_OVERRIDE global variable for system generated values | SF99950 Level 3 | SF99704 Level 23 | Not Supported | Not Supported |
TIMESTAMPDIFF_BIG scalar function | SF99950 Level 3 | SF99704 Level 23 | Not Supported | Not Supported |
FIRST_DAY scalar function | SF99950 Level 3 | SF99704 Level 23 | Not Supported | Not Supported |
JSON_UPDATE scalar function | SF99950 Level 3 | SF99704 Level 23 | Not Supported | Not Supported |
QSYS2 HTTP Functions - HTTP options support for "headers" | SF99950 Level 3 | SF99704 Level 23 | Not Supported | Not Supported |
Boolean data type | Base | Not Supported | Not Supported | Not Supported |
RESTRICT ON DROP | Base | Not Supported | Not Supported | Not Supported |
TRY_CAST specification | Base | SF99704 Level 20 | Not Supported | Not Supported |
RUNSQLSTM command - MARGINS(*SRCFILE) | Base | SF99704 Level 20 | SF99703 Level 28 | Not Supported |
HTTP_PATCH and HTTP_PATCH_VERBOSE functions | Base | SF99704 Level 20 | SF99703 Level 28 | Not Supported |
New response header format option for verbose HTTP functions | Base | SF99704 Level 20 | SF99703 Level 28 | Not Supported |
--- Enhancements from 2021 --- | ||||
New HTTP functions based in QSYS2 | Base | SF99704 Level 15 | SF99703 Level 26 | Not Supported |
SIGNAL detail from within a trigger can be accessed with GET DIAGNOSTICS | Base | SF99704 Level 13 | SF99703 Level 24 | Not Supported |
SYSIBM.CLIENT_IPADDR generated columns enhanced for non-TCP/IP based connections | Base | SF99704 Level 13 | SF99703 Level 24 | Not Supported |
RUNSQLSTM command - return TGTRLS in the listing | Base | SF99704 Level 13 | SF99703 Level 24 | Not Supported |
--- Enhancements from 2020 --- | ||||
DROP support for IF EXISTS | Base | SF99704 Level 10 | SF99703 Level 22 | Not Supported |
CREATE SEQUENCE supports FOR SYSTEM NAME | Base | SF99704 Level 10 | SF99703 Level 22 | Not Supported |
SQL Precompiler additional support for WHENEVER | Base | SF99704 Level 10 | SF99703 Level 22 | Not Supported |
SQL precompilers support DBGVIEW(*STMT) and DBGVIEW(*LIST) | Base | SF99704 Level 10 | SF99703 Level 22 | Not Supported |
SELECTIVITY clause on queries | Base | SF99704 Level 10 | SF99703 Level 22 | Not Supported |
SYSTOOLS.SPLIT table function supports ESCAPE parameter | Base | SF99704 Level 10 | SF99703 Level 22 | Not Supported |
Weakly typed distinct types | Base | SF99704 Level 7 | Not Supported | Not Supported |
JSON_TABLE enhanced to support JSON arrays | Base | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
INTERPRET built-in function | Base | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
HASH_ROW built-in function | Base | SF99704 Level 7 | Not Supported | Not Supported |
Extended indicators in SQL PL | Base | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
USING SUBSET on EXECUTE and OPEN with Extended indicators | Base | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
Support TAG within ILE RPG Embedded SQL | Base | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
ARRAY_TRIM and ARRAY_MAX_CARDINALITY SQL built-in function synonyms | Base | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
QSYS2.ANALYZE_CATALOG | Base | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
QSYS2.COMPARE_FILE | Base | SF99704 Level 7 | Not Supported | Not Supported |
SYSTOOLS.VALIDATE_DATA, SYSTOOLS.VALIDATE_DATA_FILE, and SYSTOOLS.VALIDATE_DATA_LIBRARY | Base | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
--- Enhancements from 2019 --- | ||||
Allow UTF-8 source for SQL precompiles | Base | SF99704 Level 4 | SF99703 Level 16 | Not Supported |
Add PROGRAM NAME for SQL routines | Base | SF99704 Level 4 | SF99703 Level 16 | Not Supported |
New Synonyms added for Db2 Family compatibility | Base | SF99704 Level 4 | SF99703 Level 16 | Not Supported |
Support greater than 32 routines over an external service program | Base | SF99704 Level 4 | SF99703 Level 16 | Not Supported |
HASH built-in functions | Base | SF99704 Level 1 | Not Supported | Not Supported |
SQE improvements | Base | SF99704 Level 1 | Not Supported | Not Supported |
INSERT with DEFAULT VALUES | Base | SF99704 Level 1 | SF99703 Level 15 | Not Supported |
CREATE INDEX - optional alias and rename controls for field names | Base | SF99704 Level 1 | SF99703 Level 15 | Not Supported |
New QSQPRCED() - QAQQINI statement level override controls | Base | SF99704 Level 1 | SF99703 Level 15 | Not Supported |
SQL precompiler enhancements | Base | SF99704 Level 1 | SF99703 Level 15 | Not Supported |
SYSPROC.WLM_SET_CLIENT_INFO() procedure enhanced | Base | SF99704 Level 1 | SF99703 Level 15 | Not Supported |
QSYS2.PARSE_STATEMENT() table function enhanced | Base | SF99704 Level 1 | SF99703 Level 15 | Not Supported |
--- Enhancements from 2018 --- | ||||
OmniFind - Programmatic search of the IFS | Base | Base | SF99703 Level 11 | SF99702 Level 23 |
SQL precompiler SQL0312 and SQL5011 message enhancement | Base | Base | SF99703 Level 11 | SF99702 Level 23 |
Optional parameter support for external procedures and functions | Base | Base | SF99703 Level 11 | SF99702 Level 23 |
DECFLOAT_FORMAT() built-in function | Base | Base | SF99703 Level 11 | SF99702 Level 23 |
NOW() built-in function enhanced | Base | Base | SF99703 Level 11 | SF99702 Level 23 |
EXTRACT() built-in function enhanced | Base | Base | SF99703 Level 11 | SF99702 Level 23 |
VARCHAR_FORMAT() and TO_CHAR() built-in functions enhanced | Base | Base | SF99703 Level 11 | SF99702 Level 23 |
Table correlation name enhanced to be optional | Base | Base | SF99703 Level 11 | SF99702 Level 23 |
JSON - Uniqueness controls for Key names | Base | Base | SF99703 Level 11 | SF99702 Level 23 |
JTOpen JDBC driver – Alternate server support | Base | Base | SF99703 Level 11 | SF99702 Level 23 |
--- Enhancements from 2017 --- | ||||
JSON publishing functions | Base | Base | SF99703 Level 7 | SF99702 Level 19 |
JSON scalar functions | Base | Base | SF99703 Level 7 | SF99702 Level 19 |
DELETE and UPDATE pagination | Base | Base | SF99703 Level 7 | SF99702 Level 19 |
Full LIMIT and OFFSET support | Base |
Base
|
SF99703 Level 7 | SF99702 Level 19 |
JSON query predicates | Base | Base | SF99703 Level 4 | SF99702 Level 16 |
LISTAGG() built-in function | Base | Base | SF99703 Level 4 | SF99702 Level 16 |
Enhanced LTRIM() and RTRIM() built-in functions | Base | Base | SF99703 Level 4 | SF99702 Level 16 |
Ability to use 3-part names from within trigger programs | Base | Base | SF99703 Level 4 | SF99702 Level 16 |
SQL RPG precompiler enhanced to generate Free Format code | Base | Base | SF99703 Level 4 | SF99702 Level 16 |
RPG compiler PTFs are included within the Db2 PTF Group | Base | Base | SF99703 Level 4 | SF99702 Level 16 |
--- Enhancements from 2016 --- | ||||
JSON_TABLE() table function | Base | Base | SF99703 Level 3 | SF99702 Level 14 |
QSYS2.PARSE_STATEMENT() table function | Base | Base | SF99703 Level 3 | SF99702 Level 14 |
Enhanced INCLUDE SQL statement | Base | Base | SF99703 Level 3 | SF99702 Level 14 |
Add BINDOPT and INCFILE to SET OPTION | Base | Base | SF99703 Level 3 | SF99702 Level 14 |
Add PERCENT_RANK as an ordered OLAP specification | Base | Base | SF99703 Level 3 | Not Supported |
Enhanced QSYS2.SYSPARTITIONSTAT catalog | Base | Base | SF99703 Level 3 | SF99702 Level 14 |
Enhanced REPLACE() scalar built-in function | Base | Base | SF99703 Level 3 | SF99702 Level 14 |
Debugger enhanced for SQL procedures, functions and triggers | Base | Base | SF99703 Level 3 | SF99702 Level 14 |
OVERRIDE_QAQQINI() procedure | Base | Base | Base | SF99702 Level 11 |
TIMESTAMP_FORMAT() built-in function - enhanced | Base | Base | Base | SF99702 Level 11 |
VARCHAR_FORMAT() built-in function - enhanced | Base | Base | Base | SF99702 Level 11 |
TRUNCATE() built-in function - enhanced | Base | Base | Base | SF99702 Level 11 |
ROUND() built-in function - enhanced | Base | Base | Base | SF99702 Level 11 |
QSYS2.THREAD_ID built-in global variable - added | Base | Base | Base | SF99702 Level 11 |
QSYS2.PROCESS_ID built-in global variable - added | Base | Base | Base | SF99702 Level 11 |
--- Enhancements from 2015 --- | ||||
OFFSET and LIMIT | Base | Base | Base | SF99702 Level 9 |
Remote 3-part name support on ASSOCIATE LOCATOR | Base | Base | Base | SF99702 Level 9 |
LOCATE_IN_STRING() built-in function | Base | Base | Base | SF99702 Level 9 |
OVERLAY() built-in function | Base | Base | Base | SF99702 Level 9 |
Flexible views | Base | Base | Base | SF99702 Level 9 |
Direct control of system names for global variables | Base | Base | Base | SF99702 Level 9 |
Remove the 8-80 column restriction for free form RPG & embedded SQL | Base | Base | Base |
SF99702 Level 9
&
PTFs SI58137 and SI58110
|
Create OR REPLACE table | Base | Base | Base |
SF99702 Level 5
(and enabling PTF SI57123)
|
JSON Db2 store | Base | Base | Base | SF99702 Level 5 |
SQL functions (UDF & UDTF) parameter limit | Base | Base | Base | SF99702 Level 5 |
New binary scalar functions | Base | Base | Base | SF99702 Level 5 |
Line formatting preserved for RUNSQLSTM created objects | Base | Base | Base | SF99702 Level 5 |
--- Enhancements from 2014 --- | ||||
Pipelined table functions | Base | Base | Base | SF99702 Level 3 |
Regular expressions | Base | Base | Base | SF99702 Level 3 |
LPAD & RPAD scalar functions | Base | Base | Base | SF99702 Level 3 |
JOB_NAME & SERVER_MODE_JOB_NAME - DB2 built-in global variables | Base | Base | Base | SF99702 Level 3 |
RUNSQL control of output listing | Base | Base | Base | SF99702 Level 3 |
LOCK TABLE ability to target non-FIRST members | Base | Base | Base | SF99702 Level 3 |
QSYS2.GENERATE_SQL() procedure | Base | Base | Base | Base |
QSYS2.DELIMIT_NAME() UDF | Base | Base | Base | Base |
Older Enhancements:
- Dynamic Compound statement
- Additional Free form RPG & Embedded SQL
- RDB alias support for 3-part SQL statements
- Db2 Connect - Date and time format controls
- QSYS2.QCMDEXC() procedure no longer requires a command length
- Direct control of system names for tables, views and indexes
- Multiple events supported in a single SQL trigger
- QSQPRCED() accepts client special registers
- New HTTP functions added to SYSTOOLS
- 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
- RUNSQLSTM command - OPTION parameter added to control listing
- 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
- Native JDBC driver support added for J2SE 7
- SQL0440 warning on SQL routine build
- 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
- TINYINT in CLI
- 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:
27 April 2024
UID
ibm11115697