Db2 for z/OS SQL and Application New Features v8-v12

POSTED BY TONY ANDREWS:
WRITTEN BY TONY ANDREWS:

With every Db2 version since V8, there has been great improvements in Db2 for z/OS. Many of the improvements affect developers specifically, especially in the SQL programming language and optimization. As you can see in the following highlights, there have been upwards of 75+ enhancements that could find their way in application development. Many of the new features are being used quite frequently, while others not so much. This article is a reminder, and may not be a complete list. Each could have its own article. A short definition follows each feature.

Following are some of the many application features from Db2 V8:

1. More Stage 1 predicates: Stage 1 predicates are more efficient than stage 2 predicates. Some additional SQL WHERE logic is now being assigned as stage 1 by the optimizer. V8 was specific to mismatched datatypes between the column and host variable definitions. When a predicate is stage 1, it becomes eligible for index processing.
   
   
2. Multi Row Fetch, Update, and Insert: Also called rowset processing. This is the processing of multiple rows at a time referencing arrays versus singular processing of rows. Often time more efficient.
   
   
3. Multiple Distinct: For example:

   SELECT COUNT(DISTINCT LASTNAME),  COUNT(DISTINCT FIRSTNME)
   FROM EMP

4. Expressions in the ‘Group By’: For example:

   GROUP BY SUBSTR(DEPTNO, 1,1), GROUP BY YEAR(HIREDATE)

5. Common Table Expression: WITH SQL clause for table expressions. Used to only have the Neste Table Expression coded in the FROM clause.
   
   • At times a CTE can be more efficient that an NTE. This occurs when a table expression is referenced more than once in an SQL statement
     
     
6. Dynamic Scrollable Cursors: True dynamic scrollable cursor that reference the base table and not a copied result set in storage.
   
   
7. Sequences versus Identity Columns: To have Db2 automatically assign the next sequential number for a column in an insert, defining a sequence provides another way. Sequences are not tied directly to a table or bound to a specific column.
   
   
8. Materialized Query Tables (MQTs): Great for housing Derived or Summarized Data and allowing the optimizer to rewrite and use the MQT in place of the base table(s) coded if there is a fit.
   
   
9. Recursive SQL : Recursive processing in Db2 is enabled by coding a self-referencing common table expression (CTE). Bill of material table designs work well with this SQL, along with looping logic to generate output.
   
   
10. More efficient use of indexes. Forward and Backward scans
    
    
11. New XML functions: Many new built-in XML functions.
    • Functions Added: XML2CLOB, XMLELEMENT, XMLATTRIBUTES, XMLFOREST, XMLCONCAT,  XMLAGG
 
12. New ‘Get Diagnostics’ for warning and error information: Provides more information than can be obtained from the SQLCA. Also the only way to get more Db2 information within SQL PL code other than SQLCODE and SQLSTATE.
    
    
13. Select from an Insert statement: One statement to return what was inserted. Great for when a key value is assigned directly from Db2 and you want to know what the key value is. Example: Select PK_ID From Final Table
                                                                                                           (Insert into CUST_TABLE
                                                                                               Values( …….)                     ==> Skipping PK_ID as this will be assigned directly from Db2
    
    
14. Scalar Fullselect within a ‘Select’, ‘Case’, Update, etc. You can now code a SELECT within the WITH, SELECT, FROM, GROUP BY, ORDER BY parts of an SQL statement as long as it brings back 1 value only. One value, 1 Row is what’s called an SQL Scalar Fullselect.
    
    
15. Select with Order BY: When using ‘Fetch First 1 Row Only’ an Order By clause can be used to determine the row to be returned. 
    
    
16. V8 allowed the generating of frequency and distribution cardinality statistics on non-indexed columns allowing the optimizer to make better choices on how to process a query and the guessing the number of rows to be returned.
    
    
17. Distinct From: When comparing columns of data, and one or both are defined as nullable this SQL logic can come in handy. 
    For example: Comparing an employee’s Bonus and Commission columns where both are defined as nullable.

    SELECT EMPNO, LASTNAME
    FROM EMP
    WHERE BONUS = COMM     ==>=>=>=>=>>>>  or WHERE BONUS <> COMM

    • If one of the values in a row is null, does that make them not equal? If both values in a row are null, does that make them equal? NO! Nulls are unknown values and cannot be compared, thus drop out of result sets. 
    • Unless you code the following: In these examples nulls will equal nulls, and a null value will not equal an actual value and be part of the result set.
      • WHERE BONUS IS DISTINCT FROM COMM
      • WHERE BONUS IS NOT DISTINCT FROM COMM
        
        
18. Generate Unique: A built in function to return a unique CHAR(13) string value that is FOR BIT DATA. The value generated is a derivative of the current timestamp and unique across all members in a Sysplex and any other execution of the same function.

Following are some of the many application features in Db2 V9:

1. Set operations ‘Intersect’ and ‘Except’: One SQL result set to compare against another SQL result set. Coded just like a UNION/UNION ALL but using INTERSECT/INTERSECT ALL and EXCEPT/EXCEPT ALL.
   
   
2. Merge statement: for ‘Upsert’ processing. Insert or Update in one statement.
   
   
3. OLAP features for Ranking and Numbering of data. The ordinal ranking of each row in the result set based on a particular column or set of columns.
   
   
4. Native SQL Stored Procedures: Stored procedures coded in the SQL PL language.
   
   
5. ‘Instead of’ Triggers: The performing of Inserts/Updates/Deletes against a view.
   
   
6. New support and SQL for XML data: PureXML support. Create indexes on XML documents. XPath expressions to parse and search thru XML documents. New XML datatype allows for the storing of XML documents as XML and not character.
   
   
7. IBM Data Studio: Great tool for SQL visual explains, and editor for SQL PL coding and deployment.
   
   
8. Distinct sort avoidance with non unique indexes: Used to only be for unique indexes.
   
   
9. Indexing on Expressions: Indexes created using SQL functions. For example having an index that uses the YEAR function to help predicates like:

   WHERE YEAR(HIREDATE) = 2020
   CREATE INDEX XEMP4 ON EMP (YEAR(HIREDATE)) USING ……..

10. Skipped locked data: Ability to skip over some rows/pages that may be locked by another process in the fulfilling of a result set.
    
    
11. Truncate statement: Another option to delete all data from a table.
    
    
12. Optimizer Changes: Many optimizer improvements. More sort avoidance, more query rewrites especially with subqueries involved (called correlating and de-correlating)
    
    
13. Timestamp auto update for inserts and Updates: Use of GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP in the table definition for a timestamp column.
    
    
14. Optimistic locking: Ability to make sure when a row is selected and them updated that another user did not modify it in between. This goes along with #13 above.
    
    
15. New datatypes: DECFLOAT, BIGINT, BINARY/VARBINARY, XML
    
    
16. Select from Update or Delete getting old or new values. Ability to retrieve that data in a result set that is being deleted from the same SQL statement, and to see updates. This is through the use of coding OLD TABLE and FINAL TABLE in an SQL update or delete statement.
    
    
17. Fetch First, Order BY within subqueries
    
    
18. REOPT AUTO (Dynamic SQL): For cache dynamic SQL statements. The determining from Db2 if after the first execution, a re-optimization may be needed on subsequent executions.
    
    
19. New SQL Functions: Delivery of SOUNDEX and DIFFERENCE, RID functions, Date and Time functions like MONTHS_BETWEEN, TIMESTAMP_FORMAT, and EXTRACT.

Following are some of the many application features in Db2 V10:

1. Ranking, Moving Sum and AVG: New aggregation for SQL Sum and Avg.
   
   
2. Variable Inserts and Updates: More uses for null indicators on insert and update statements.
   
   
3. Extender indicator values: Null indicators can be used in updates to signal for a specific column whether to: 
   • Use the default value
   • Use the value in the host variable
   • Skip this column in the SQL update
     
     
4. Increased timestamp precision: Can now define a timestamp up to 12 microseconds.
   
   
5. Currently committed: Improves locking when locked by inserts or deletes.
   
   
6. Hash access design: Not used much. Specific for a table. Currently deprecated.
   
   
7. SQL PL enhancements in user defined functions. New in-line functions in the definition, especially the ‘Returns Table’ function.
   
   
8. XML Enhancements
   
   
9. Column Masking: The masking of data for specific column when returned to a result set. For example a stored SSN number to always be returned as XXX-XX-1234 (showing last 4 digits only).
   
   
10. Temporal Tables: Ability to easily define a table to maintain current and historical data at the same time. Provides for the ability to query ‘AS OF’ historical data.
    
    
11. Many Optimization improvements.
    
    
12. Index included columns: The ability to include other columns of data in a unique defined index and not altering the original unique definition. This allows filtering while processing the index and can reduce the number of table rows that have to be accessed and maybe give index only processing.
    
    
13. Row Permissions. Defining of what rows in a table a specific user or group may see.
    
    
14. Explain Package Statement: Ability to extract explain information from a package when there was no ‘Explain Yes’ defined as part of the BIND.
    
    
15. Additional SQL predicate rewrite from the optimizer in the form or transitive closure where additional predicates are added to the query (but never changing the logic).

Following are some of the many application features in Db2 V11:

1. Global Variables: Ability to share information between SQL statements or between programs and stored procedures. Similar to global temporary tables, but a variable only. Example:

   CREATE VARIABLE GV_INT INTEGER DEFAULT 0. 

   This variable can have its contents modified by an SQL SET statement and then referenced by different SQL statements.
   
   
2. Transparent Archiving: The automatic moving of a row when deleted to a defined archive table.
   
   
3. SQL Grouping Sets and Rollups: Automatics calculations of subtotals and grand totals with the use of SUM/AVG with GROUP BY.
   
   
4. SQL PL variable arrays: Ability to process data via an array within the code or passing of an array datatype as a parameter. 
   
   
5. More SQL PL enhancements: Autonomous stored procedure processing.
   
   
6. More XML enhancements: FLWOR expression logic introduced and XMLQUERY improvements. XMLTABLE function 
   
   
7. Global Temp Table enhancements: NOT LOGGED option. Ability to specify what is to happen if a ROLLBACK occurs:
   • ON ROLLBACK PRESERVE ROWS / ON ROLLBACK DELETE ROWS
8. New optimization features and improvements: More stage 1 predicates, more predicate rewrites.
9. Additional optimizer predicate rewrites: Many new optimizer predicate rewrites, especially when predicates are coded with functions SUBSTR, YEAR, DATE. For example the predicate:

   WHERE YEAR(HIREDATE) = 2020

    gets rewritten as

   WHERE HIREDATE BETWEEN 202-01-01 and 202-12-31

10. Access Path Stability: The ability to keep previous and current access path plans for packages to fall back on in time of need. The ability to compare a new bind package to a previous package for comparisons and request a warning or failure. And the ability to re-use a previous package Access path information when performing a new bind package.
    
    
11. SQL Cubing sets.

Following are some of the many application features in Db2 V12:

1. Additional support for triggers: Ability to reference global variables, additional SQL PL control statements. Explicitly specify bind options, and ability to have multiple versions of a trigger.
   
   
2. Pagination improvements: Performance improvements with ‘page next’ processing (resulting rows based on the previous set of rows). Ability to code:

   WHERE (LASTNAME, FIRSTNME) > (:HV-LASTNAME, :HV_FIRSTNME)

   There is another new way in Db2 12 to fetch the next set of rows. This is the OFFSET clause:

    OFFSET 20 ROWS FETCH FIRST 20 ROWS ONLY

3. Additional support for arrays’ Can now defined a global variable as an array datatype. ARRAY_AGG function improvements (new use in the ORDER BY).
   
   
4. SQL MERGE improvements: Can code multiple ‘matched’ column. Can code additional predicates within a MATCHED or NOT MATCHED. Ability to DELETE, ability to IGNORE, ability to SIGNAL. Also allows for the Source to be a Select statement and not just host variables.
   
   
5. Piece-wise deletes: SQL allows the ‘Fetch First xx Rows’ within a delete statement to limit the number of rows that will be deleted in a single SQL statement.
   
   
6. Db2 REST Services: REST Services support.
   
   
7. New SQL functions: PERCENTILE_CONT, PERCENTILE_DISC calculating a percentile value based on a group of data.
   
   
8. Dynamic Plan Stability: This will help achieve access path stability that is comparable to static SQL statements for repeating cached dynamic SQL statements, minimizing repeated full prepares.
   
   
9. V12 function levels highlights: New encryption functions (M505), LISTAGG (M501), IDAA passthrough functions (REGEXP - M504), CREATE OR REPLACE for procedures (M507), REBIND phase-in (M505), application granularity locking limits (M507), Temporal RI for UPDATE/DELETE (M509)

Following are some of the many application features in Db2 V13:

1. Application deadlock resolution (M501) and wait timeout (M500), SQL Data Insights (M500)