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