Update for DB2 10 and all new for DB2 11
This newsletter completes my walk through of new Scalar functions that I began a few years ago. I will start today with two new ones that were introduced by APAR PM56631 in May 2012 to DB2 10. PACK (a Scalar Function), and its opposite number UNPACK (a Row Function).
New Scalar functions in DB2 10:
Some PACK examples
PACK basically “packs” together a list of columns, or literals, into one variable length binary string in UNICODE format. Here’s some real PACK examples—note that SYDUMMYU is used!
SELECT PACK(CCSID 1208, 'ALICE', DATE('2014-07-22') , DOUBLE(8.6))
FROM SYSIBM.SYSDUMMYU;
---------+---------+---------+---------+---------+---------+---------+--
00000301C0018001E004B80005414C494345201407224021333333333333
DSNE610I NUMBER OF ROWS DISPLAYED IS 1SELECT PACK(CCSID 1208, 'ROY', BIGINT(123456789) ,
TIMESTAMP('2014-07-22-07.43.23.123456'))
FROM SYSIBM.SYSDUMMYU;
---------+---------+---------+---------+---------+---------+---------+--
00000301C001EC018804B80003524F5900000000075BCD15000620140722074323123456
DSNE610I NUMBER OF ROWS DISPLAYED IS 1SELECT PACK(CCSID 1208,
TIMESTAMP('2014-07-22-07.55.23.123456'))
FROM SYSIBM.SYSDUMMYU;
---------+---------+---------+---------+---------+---------+---------+--
0000010188000620140722075523123456
DSNE610I NUMBER OF ROWS DISPLAYED IS 1You can see the format that it creates is actually pretty standard and can have uses in normal processing!
Using the second example above, we can see what is in the output:
The VARBIN string starts with a control flag byte which we can happily ignore
00
Then comes how many elements are in the data
0003
Then comes a set of “number of element” SQLTYPES. If any are odd numbered then the data value is *not* in the string as it is in fact NULL
01C0 Varying Character data 448 in decimal 01EC BIGINT data 492 in decimal 0188 TIMESTAMP data 392 in decimal
The first data element CHAR containing “ROY” looks like
04B8 CCSID of String 1208 in decimal 0003 Length of string 524F59 Data in UNICODE
The second data element BIGINT containing 123456789 looks like
00000000075BCD15 BIGINT data big endian always!
The third data element TIMESTAMP containing ‚2014-07-22-07.43.23.123456‘ looks like
0006 TIMESTAMP precision 20140722074323123456 TIMESTAMP data
UNPACK statements
Now, these VARBIN strings can get very long indeed, but what can you do with all of the data? Well, obviously UNPACK springs to mind… Here’s the above data again, but getting input to an UNPACK statement you have to create a FUNCTION first… something like this:
--#SET TERMINATOR $
CREATE FUNCTION PACKUDF ()
RETURNS VARBINARY(128)
VERSION V1
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
CALLED ON NULL INPUT
STATIC DISPATCH
NOT SECURED
DISABLE DEBUG MODE
BEGIN
DECLARE PACKSTR VARBINARY(128);
SET PACKSTR =
PACK(CCSID 1208
,VARCHAR('ROY' , 40)
,BIGINT(123456789)
,TIMESTAMP('2014-07-22-07.43.23.123456')
);
RETURN PACKSTR;
END
$
This just does what the second example did, but buried in a Scalar SQL Function. This can then easily be called by just doing this:
SELECT UNPACK(PACKUDF()).* AS (USER_NAME VARCHAR(40) CCSID UNICODE ,LARGE_NUMBER BIGINT ,WHEN_USED TIMESTAMP) FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+-..--------+---------+---------+---------+---------+--- USER_NAME .. LARGE_NUMBER WHEN_USED ---------+---------+---------+-..--------+---------+---------+---------+---------+--- ROY 123456789 2014-07-22-07.43.23.123456 DSNE610I NUMBER OF ROWS DISPLAYED IS 1
I deleted some white space (..) above just to get it all to fit on the page, by the way! Now quite *what* you would do with PACK and UNPACK I’ll leave up to you!!!
On now to DB2 11: Aggregate Functions and 11 new scalar functions for new ARRAY data type
Here we got three new Aggregate Functions and 11 new Scalar Functions. Most of these are for the new ARRAY data type that was introduced in DB2 11. First, here’s how you can create ARRAYs, so that you can understand a bit more about the Functions that follow:
CREATE TYPE BOXWELL.ROYARRAY AS INTEGER ARRAY ??(??) ; CREATE TYPE BOXWELL.ROYARRAY4 AS CHAR(16) ARRAY ??( 32 ??) ; CREATE TYPE BOXWELL.ROYARRAY5 AS CHAR(16) ARRAY ??( VARCHAR(8) CCSID UNICODE FOR MIXED DATA ??) ;
Note that I use trigraphs ( ??( = [ and ??) = ] ), as I *hate* the square brackets! (I would *love* to meet that developer in a dark alley one night…I know, I know, it is like that in the “other” SQL world but it doesn’t mean I have to use them!).
Now arrays are pretty cool for passing data back from SQL Scalar Functions, or for Parameters of SQL Scalar Functions and Native SQL Procedures, as it simplifies the handling of lots of same type parameters. This is really good for all the JAVA programmers out there who use array types all the time!
In fact, all of the ARRAY stuff is *only* allowed within the confines of Stored Procedures and SQL PL.
What also arrived in DB2 11 is the ability to “unravel the array” using the new “collection-derived-table” syntax—otherwise known as UNNEST—and here are the two examples from the doc:
Example 1: Suppose that PHONENUMBERS is a user-defined array type that is defined as an ordinary array.
RECENT_CALLS is an array variable of the PHONENUMBERS type. RECENT_CALLS contains the following phone numbers:
9055553907
4165554213
4085553678
The following SELECT statement uses UNNEST to retrieve the list of phone numbers from the Array:
SELECT T.ID, T.NUM FROM UNNEST(RECENT_CALLS) WITH ORDINALITY AS T(NUM, ID);
ID NUM
1 9055553907
2 4165554213
3 4085553678
SET PHONELIST[’Home’] = ’4443051234’; SET PHONELIST[’Work’] = ’4443052345’; SET PHONELIST[’Cell’] = ’4447893456’;
The following SELECT statement is executed:
SELECT T.ID, T.PHONE FROM UNNEST(PHONELIST) AS T(ID, PHONE);
The result table looks like this, although the order of rows might differ:
ID PHONE
Cell 4447893456
Home 4443051234
Work 4443052345
The Array Aggregate Function, ARRAY_AGG, and all of the Scalar Functions:
ARRAY_DELETE,
ARRAY_FIRST,
ARRAY_LAST,
ARRAY_NEXT,
ARRAY_PRIOR,
CARDINALITY,
MAX_CARDINALITY,
and TRIM_ARRAY,
are all available to use but only within the confines of SQL PL.
CHAR9 and VARCHAR9
Also new in DB2 11 are CHAR9 and VARCHAR9, which are the good old functions from DB2 9 but now keeping the way they used to work forever—I hope they will not actually be used!
XSLTRANSFORM was also delivered to do XML transformations, but I have never actually tried it. You must note this bit of text:
This user-defined function requires IBM SDK for z/OS, Java Technology Edition Version 6. This user-defined function uses the XSLT support that is provided by the W3C XSL Transformations V1.0 Recommendation.
GROUPING & MEDIAN
Then we got two new Aggregate Functions. The first is GROUPING—used in the OLAP extensions for doing automatic sub- and grand totaling, and then MEDIAN—which *only* works if you have the IDAA switched on!
So, lots of interesting stuff here. You may want to go back to the 3 newsletters from a few years ago (2012) to recap all of these “cool” features. That way you’ll have plenty to read if you get bored on holiday!
- 2012-06: SOUNDEX and other “cool” features – part three for DB2 10 –
- 2012-05: SOUNDEX and other “cool” features – part two for DB2 V9 –
- 2012-04: SOUNDEX and other “cool” features – part one for DB2 V8 –
Feel free to send me your comments and ask questions!
Link back to general newsletter page
