This month I want to run through all the purely SQL changes that we have received in Db2 12 and in Db2 13 right up until Db2 13 FL504. I was reading about agile development and how fast the deliveries are and so I wondered how many purely SQL changes have we, as SQL Users, actually received over the last six to seven years?
Back to the facts…
Ok, in Db2 12 FL501 we got LISTAGG which was very cool indeed, apart from the fact that you could not use ORDER BY which irritated all the SQL developers I know quite a bit! IBM also created a whole bunch of Accelerator only “pass-thru” functions that I cannot ever use as I do not know whether or not any of my customers actually has an Accelerator… so, for me, they do not really count. In total 28 BiF’s either got pass-thru or extra Accelerator support so if you *have* an accelerator good news indeed! This was all enabled over Db2 12 FL Levels 504 and 507 as well as APAR PH48480.
Uniwhat?
A bunch of, for me, weird things were the UNI_60 and UNI_90 support added to LOWER, TRANSLATE and UPPER for both Db2 12 and 13. There must be a use case out there but I am lucky enough not to have found it yet!
Super MERGE
MERGE got a major overhaul in Db2 12 with the addition of DELETE support but the story of MERGE did not end there! It still had a major performance problem if any of the index columns got updated as then it was forced to fallback to a tablespace scan. With Db2 13 FL504 (APAR PH47581) this problem was solved – nearly…
The following conditions must be met to enable Db2 to use an index for a MERGE operation when index key columns are being updated:
Db2 SQL Reference
– The MERGE statement contains a corresponding predicate in one of the
following forms, for each updated index key column:
index-key-column = literal-value, where literal-value is a constant or any
expression that can be treated as a literal, including a host variable, parameter
marker, or non-column expression.
index-key-column IS NULL
– If a view is involved, WITH CHECK OPTION is not specified.
MERGE has basically become one of the most powerful SQL statements out there and you can actually cause terrible trouble if you use DRDA with VALUES clauses and hard coded “FOR 10 ROWS” style of SQLs. All is very well documented and worth a read under the heading:
DRDA considerations when NOT ATOMIC CONTINUE ON SQLEXCEPTION is specified (or the NOT ATOMIC CONTINUE ON SQLEXCEPTION clause is not specified and source-values (VALUES) is specified)
Db2 SQL Reference
This Db2 13 APAR also enabled the chance of getting List Prefetch as an access path which is, as far as I can tell, the only “new” access path in Db2 13.
Pagination anyone?
The use of OFFSET was a great innovation for Db2 but the “other” pagination was better! I mean data-dependent pagination which changed this old chestnut of an SQL:
WHERE (LASTNAME = 'SMITH' AND FIRSTNAME > 'JOHN') OR (LASTNAME > 'SMITH')
Into this modern SQL:
WHERE (LASTNAME, FIRSTNAME) > ('SMITH', 'JOHN')Much much better and for online generated dynamic SQL, I am talking to you CICS, a fantastic win! To verify this you get a range-list index scan “NR” in your ACCESSTYPE PLAN_TABLE column when you EXPLAIN it.
One at a time please
Piece-wise DELETE using the FETCH FIRST nnnn ROWS was also a really good idea instead of causing possible lock escalations and/or timeouts. A simple loop around the DELETE statement and Bob’s your uncle!
Db2 13 – What’s New?
The big stuff here was the extension of the PROFILE table (I keep talking about it don’t I?) as now it also handles Local things – This is a game changer! Starting with CURRENT_LOCK_TIMEOUT & DEADLOCK_RESOLUTION_PRIORITY but I am sure this list will grow and grow. The PROFILE table is just way to good not to use these days!
AI got a major boost
We got a bunch of AI stuff in Db2 13 (SQL Data Insights) but the first new “agile” one was in FL504 when AI_COMMONALITY was released. It will hopefully enable shops to find outliers in the data which were not there at training time.
Db2 V8 finally done!
Finally, the last thing that was started way back when in DB2 V8 was done! The length of a column name has been expanded from 30 up to 128 bytes. However, do not do this! The SQLDA is *not* designed for this and so it might look nice on paper but, depending on how you interface to them, it might cause serious grief!
Lower cadence higher quality
IBM have announced a cadence of two FL’s per year down from the 3 – 4 when Agile all started and so I am happy that the list of changes will keep getting longer and the quality of the code higher.
Just SQL!
Please remember all I am talking about here is SQL relevant enhancements – there are tons of others as well – just think about Utilities or FTB etc. etc. For a full list always download and read the latest “What’s New?” guide.
Did I miss anything? Drop me a line if you think so!
TTFN,
Roy Boxwell