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.4 | IBM i 7.3 | IBM i 7.2 |
|---|---|---|---|
| --- Enhancements from 2020 --- | |||
| DROP support for IF EXISTS | SF99704 Level 10 | SF99703 Level 22 | Not Supported |
| CREATE SEQUENCE supports FOR SYSTEM NAME | SF99704 Level 10 | SF99703 Level 22 | Not Supported |
| SQL Precompiler additional support for WHENEVER | SF99704 Level 10 | SF99703 Level 22 | Not Supported |
| SQL precompilers support DBGVIEW(*STMT) and DBGVIEW(*LIST) | SF99704 Level 10 | SF99703 Level 22 | Not Supported |
| SELECTIVITY clause on queries | SF99704 Level 10 | SF99703 Level 22 | Not Supported |
| Weakly typed distinct types | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
| JSON_TABLE enhanced to support JSON arrays | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
| INTERPRET built-in function | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
| HASH_ROW built-in function | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
| Extended indicators in SQL PL | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
| USING SUBSET on EXECUTE and OPEN with Extended indicators | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
| Support TAG within ILE RPG Embedded SQL | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
| ARRAY_TRIM and ARRAY_MAX_CARDINALITY SQL built-in function synonyms | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
| QSYS2.ANALYZE_CATALOG | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
| QSYS2.COMPARE_FILE | SF99704 Level 7 | Not Supported | Not Supported |
| SYSTOOLS.VALIDATE_DATA, SYSTOOLS.VALIDATE_DATA_FILE, and SYSTOOLS.VALIDATE_DATA_LIBRARY | SF99704 Level 7 | SF99703 Level 18 | Not Supported |
| --- Enhancements from 2019 --- | |||
| Allow UTF-8 source for SQL precompiles | SF99704 Level 4 | SF99703 Level 16 | Not Supported |
| Add PROGRAM NAME for SQL routines | SF99704 Level 4 | SF99703 Level 16 | Not Supported |
| New Synonyms added for Db2 Family compatibility | SF99704 Level 4 | SF99703 Level 16 | Not Supported |
| SF99704 Level 4 | SF99703 Level 16 | Not Supported | |
| HASH built-in functions | SF99704 Level 1 | Not Supported | Not Supported |
| SQE improvements | SF99704 Level 1 | Not Supported | Not Supported |
| INSERT with DEFAULT VALUES | SF99704 Level 1 | SF99703 Level 3 | Not Supported |
| CREATE INDEX - optional alias and rename controls for field names | SF99704 Level 1 | SF99703 Level 3 | Not Supported |
| New QSQPRCED() - QAQQINI statement level override controls | SF99704 Level 1 | SF99703 Level 3 | Not Supported |
| SQL precompiler enhancements | SF99704 Level 1 | SF99703 Level 3 | Not Supported |
| SYSPROC.WLM_SET_CLIENT_INFO() procedure enhanced | SF99704 Level 1 | SF99703 Level 3 | Not Supported |
| QSYS2.PARSE_STATEMENT() table function enhanced | SF99704 Level 1 | SF99703 Level 3 | Not Supported |
| --- Enhancements from 2018 --- | |||
| OmniFind - Programmatic search of the IFS | Base | SF99703 Level 11 | SF99702 Level 23 |
| SQL precompiler SQL0312 and SQL5011 message enhancement | Base | SF99703 Level 11 | SF99702 Level 23 |
| Optional parameter support for external procedures and functions | Base | SF99703 Level 11 | SF99702 Level 23 |
| DECFLOAT_FORMAT() built-in function | Base | SF99703 Level 11 | SF99702 Level 23 |
| NOW() built-in function enhanced | Base | SF99703 Level 11 | SF99702 Level 23 |
| EXTRACT() built-in function enhanced | Base | SF99703 Level 11 | SF99702 Level 23 |
| VARCHAR_FORMAT() and TO_CHAR() built-in functions enhanced | Base | SF99703 Level 11 | SF99702 Level 23 |
| Table correlation name enhanced to be optional | Base | SF99703 Level 11 | SF99702 Level 23 |
| JSON - Uniqueness controls for Key names | Base | SF99703 Level 11 | SF99702 Level 23 |
| JTOpen JDBC driver – Alternate server support | Base | SF99703 Level 11 | SF99702 Level 23 |
| --- Enhancements from 2017 --- | |||
| JSON publishing functions | Base | SF99703 Level 7 | SF99702 Level 19 |
| JSON scalar functions | Base | SF99703 Level 7 | SF99702 Level 19 |
| DELETE and UPDATE pagination | Base | SF99703 Level 7 | SF99702 Level 19 |
| Full LIMIT and OFFSET support |
Base
|
SF99703 Level 7 | SF99702 Level 19 |
| JSON query predicates | Base | SF99703 Level 4 | SF99702 Level 16 |
| LISTAGG() built-in function | Base | SF99703 Level 4 | SF99702 Level 16 |
| Enhanced LTRIM() and RTRIM() built-in functions | Base | SF99703 Level 4 | SF99702 Level 16 |
| Ability to use 3-part names from within Trigger programs | Base | SF99703 Level 4 | SF99702 Level 16 |
| SQL RPG precompiler enhanced to generate Free Format code | Base | SF99703 Level 4 | SF99702 Level 16 |
| RPG compiler PTFs are included within the Db2 PTF Group | Base | SF99703 Level 4 | SF99702 Level 16 |
| --- Enhancements from 2016 --- | |||
| JSON_TABLE() table function | Base | SF99703 Level 3 | SF99702 Level 14 |
| QSYS2.PARSE_STATEMENT() table function | Base | SF99703 Level 3 | SF99702 Level 14 |
| Enhanced INCLUDE SQL statement | Base | SF99703 Level 3 | SF99702 Level 14 |
| Add BINDOPT and INCFILE to SET OPTION | Base | SF99703 Level 3 | SF99702 Level 14 |
| Add PERCENT_RANK as an ordered OLAP specification | Base | SF99703 Level 3 | |
| Enhanced QSYS2.SYSPARTITIONSTAT catalog | Base | SF99703 Level 3 | SF99702 Level 14 |
| Enhanced REPLACE() scalar built-in function | Base | SF99703 Level 3 | SF99702 Level 14 |
| Debugger enhanced for SQL procedures, functions and triggers | Base | SF99703 Level 3 | SF99702 Level 14 |
| OVERRIDE_QAQQINI() procedure | Base | Base | SF99702 Level 11 |
| TIMESTAMP_FORMAT() built-in function - enhanced | Base | Base | SF99702 Level 11 |
| VARCHAR_FORMAT() built-in function - enhanced | Base | Base | SF99702 Level 11 |
| TRUNCATE() built-in function - enhanced | Base | Base | SF99702 Level 11 |
| ROUND() built-in function - enhanced | Base | Base | SF99702 Level 11 |
| QSYS2.THREAD_ID built-in global variable - added | Base | Base | SF99702 Level 11 |
| QSYS2.PROCESS_ID built-in global variable - added | Base | Base | SF99702 Level 11 |
| --- Enhancements from 2015 --- | |||
| OFFSET and LIMIT | Base | Base | SF99702 Level 9 |
| Remote 3-part name support on ASSOCIATE LOCATOR | Base | Base | SF99702 Level 9 |
| LOCATE_IN_STRING() built-in function | Base | Base | SF99702 Level 9 |
| OVERLAY() built-in function | Base | Base | SF99702 Level 9 |
| Flexible views | Base | Base | SF99702 Level 9 |
| Direct control of system names for global variables | Base | Base | SF99702 Level 9 |
| Remove the 8-80 column restriction for free form RPG & embedded SQL | Base | Base |
SF99702 Level 9
&
PTFs SI58137 and SI58110
|
| Create OR REPLACE table | Base | Base |
SF99702 Level 5
(and enabling PTF SI57123)
|
| JSON Db2 store | Base | Base | SF99702 Level 5 |
| SQL functions (UDF & UDTF) parameter limit | Base | Base | SF99702 Level 5 |
| New binary scalar functions | Base | Base | SF99702 Level 5 |
| Line formatting preserved for RUNSQLSTM created objects | Base | Base | SF99702 Level 5 |
| --- Enhancements from 2014 --- | |||
| Pipelined table functions | Base | Base | SF99702 Level 3 |
| Regular expressions | Base | Base | SF99702 Level 3 |
| LPAD & RPAD scalar functions | Base | Base | SF99702 Level 3 |
| JOB_NAME & SERVER_MODE_JOB_NAME - DB2 built-in global variables | Base | Base | SF99702 Level 3 |
| RUNSQL control of output listing | Base | Base | SF99702 Level 3 |
| LOCK TABLE ability to target non-FIRST members | Base | Base | SF99702 Level 3 |
| QSYS2.GENERATE_SQL() procedure | Base | Base | Base |
| QSYS2.DELIMIT_NAME() UDF | 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
Related Information
[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]
Document Information
Modified date:
05 October 2020
UID
ibm11115697