Do you think you know IBM i?

IBM i 7.3 Announcement & Material

• IBM® DB2™ for i Temporal Support, enabling clients to conduct accurate analysis of data within a specific historical time period
• More advanced analytics capability driven by enhanced OLAP functionality, allowing clients to better forecast, define gaps and build new strategies for business
• Security authority collection, a new addition that better secures critical business data
• Plus, many more enhancements that increase efficiency and functionality

Here are some links which might be helpful as you start to learn more about IBM i 7.3 and the promotional material surrounding it:

• IBM i on Power: http://www-03.ibm.com/systems/power/software/i/
• IBM i Strategy & Roadmap white paper: http://bit.ly/IBM_i_2016_Roadmap
• IBM i 7.3 Announce Overview blog: http://www.ibmsystemsmag.com/Blogs/You-and-i/April-2016/IBM-i-7-3-Announce/
• #IBMi Twitter conversations: http://twitter.com/search?q=%23IBMi&src=typd

The major new features include:

• System-period temporal tables
• Attach and detach partitions for a table
• Additional OLAP functions
• Additional aggregate functions
• PROCESS_ID and THREAD_ID built-in global variables
• Enhancements to ROUND, TRUNCATE, TIMESTAMP_FORMAT, and VARCHAR_FORMAT scalar functions
• Inlining for SQL table functions
• Support for the EXTIND option in SET OPTION for SQL functions, procedures, and triggers
• Required Db2 PTF Group level tracking added for static statements to SYSPROGRAMSTMTSTAT
• Increase maximum number of parameters for scalar functions and table functions to 2000
• Increase number of return columns for table functions to 8000

In addition, the following enhancements are new since 7.2:

• JOB_NAME and SERVER_MODE_JOB_NAME built-in global variables
• REGEXP_xxx scalar functions and REGEXP_LIKE predicate for regular expression handling
• OVERLAY and LOCATE_IN_STRING scalar functions
• VARBINARY_FORMAT and VARCHAR_FORMAT_BINARY scalar functions
• LIMIT and OFFSET support for queries
• REPLACE option for CREATE TABLE
• System name for CREATE VARIABLE
• Support for PIPE statement in SQL table functions
• Increase maximum number of indexes on a table to approximately 15000

What’s new as of October 2020

• For CREATE FUNCTION, an external name that is a service program name does not require a library qualifier when the string form of the name is used. Naming conventions
• The SELECTIVITY clause allows user hints for individual predicates: Search conditions
• CREATE SEQUENCE supports the FOR SYSTEM NAME clause: CREATE SEQUENCE
• DROP supports the IF EXISTS clause: DROP
• The WHENEVER statement can call an internal procedure when a monitored condition is encountered: WHENEVER
• SYSCOLUMNS2_SESSION view returns columns in QTEMP: SYSCOLUMNS2_SESSION

What’s new as of April 2020

• The language used to define SQL functions, procedures, and triggers is referred to as SQL PL (SQL Procedural Language)
• Synonyms for existing functions: ARRAY_MAX_CARDINALITY and ARRAY_TRIM
• INTERPRET function assigns a data type to binary data: INTERPRET
• JSON_TABLE, JSON_QUERY, JSON_VALUE, JSON_TO_BSON, and BSON_TO_JSON have been modified to support a JSON expression that is an array: JSON_TABLE, JSON_QUERY, JSON_VALUE, JSON_TO_BSON, BSON_TO_JSON
• Extended indicators can be used in OPEN and EXECUTE to allow omitting host variables from the USING list: OPEN, EXECUTE
• TAG statement added to complement WHENEVER statement for fully free form ILE RPG: TAG
• Extended indicators supported in SQL PL: assignment-statement
• Documented table SYSDUMMY1: SYSDUMMY1
• Variable length segment and blocked insert information added to SYSPARTITIONSTAT and SYSTABLESTAT views: SYSPARTITIONSTAT, SYSTABLESTAT

What’s new as of October 2019

• ISNULL and NOTNULL are synonyms for the IS NULL and IS NOT NULL predicates: NULL predicate
• Synonyms for existing functions: NVL, POW, RANDOM, STRLEFT, STRRIGHT, STRPOS, TO_CLOB: NVL, POWER or POW, RANDOM or RAND, STRLEFT, STRRIGHT, STRPOS, and TO_CLOB
• CREATE FUNCTION (SQL scalar), CREATE_FUNCTION (SQL table), and CREATE PROCEDURE (SQL) support the PROGRAM NAME clause to allow the program or service program name to be specified: CREATE FUNCTION (SQL scalar), CREATE FUNCTION (SQL table), and CREATE PROCEDURE (SQL)
• CREATE FUNCTION (external scalar), CREATE_FUNCTION (external table), and CREATE PROCEDURE (external) removed the limit of 32 routine attributes that can be saved in the associated program or service program object.
• Additional index invalidation information added to SYSINDEXSTAT, SYSPARTITIONINDEXES, and SYSPARTITIONINDEXSTAT: SYSINDEXSTAT, SYSPARTITIONINDEXES, and SYSPARTITIONINDEXSTAT

What’s new as of April 2019

• CREATE INDEX allows index columns to be renamed: CREATE INDEX
• INSERT a row containing only default values: INSERT

What’s new as of August 2018

• The table correlation name for nested table expressions, table functions, JSON_TABLE, XMLTABLE, and UNNEST is optional: table-reference
• DECFLOAT_FORMAT scalar function: DECFLOAT_FORMAT
• JSON_OBJECT and JSON_OBJECTAGG have a new option for duplicate key behavior. The default is to allow duplicate keys: JSON_OBJECT and JSON_OBJECTAGG
• JSON_TABLE, JSON_QUERY, and JSON_VALUE tolerate duplicate keys: JSON_TABLE, JSON_QUERY, and JSON_VALUE
• EXTRACT function has more options: EXTRACT
• NOW function allows optional precision: NOW
• VARCHAR_FORMAT supports 2 new timestamp format string elements for microseconds and milliseconds: VARCHAR_FORMAT
• Unqualified functions, variables, and types within an SQL procedure or function will record a value of CURRENT PATH in SYSROUTINEDEP: CREATE PROCEDURE (SQL), CREATE FUNCTION (SQL scalar), and CREATE FUNCTION (SQL table)
• SYSPARTITIONSTAT returns information about partial transactions: SYSPARTITIONSTAT

What’s new as of October 2017

• JSON publishing and scalar functions: JSON_ARRAYAGG, JSON_OBJECTAGG, JSON_ARRAY, JSON_OBJECT, JSON_QUERY, and JSON_VALUE
• LIMIT and OFFSET restrictions removed: fetch-clause and offset-clause
• DELETE and UPDATE allow ORDER BY, FETCH, and OFFSET: DELETE and UPDATE
• GRANT and REVOKE on schemas: GRANT (schema privileges) and REVOKE (schema privileges)

What’s new as of March 2017

• IS JSON and JSON_EXISTS predicates
• LISTAGG aggregate function
• LTRIM and RTRIM functions support a second argument
• CREATE TRIGGER allows dynamic statements to use 3-part names

What’s new as of October 2016

• New OLAP function: PERCENT_RANK
• REPLACE function’s third argument is optional
• JSON_TABLE table function
• Functions can be defined as STATEMENT DETERMINISTIC
• INCLUDE statement supported for SQL procedures, functions, and triggers
• SYSPARTITIONSTAT returns the TEXT value
• SYSPROGRAMSTAT returns module name