Treasury of new DB2 6.1 (V6R1) Features, Part 5: New Functions and Change Stamp Columns

Published: April 1, 2009

by Michael Sansoterra

Today's tip addresses the new built-in functions available in DB2 for i (a.k.a. DB2 for i5/OS, SQL/400, and DB2 UDB for iSeries), change stamp columns and row change expressions.

ASCII and CHR Functions

The ASCII and CHR functions are great additions to DB2 for i. The ASCII function will take the left most character of a string expression and return the ASCII character code equivalent. Take the following example:

Select ASCII(' ') From SysIBM/SysDummy1

This command will return a value of 32 (ASCII code for space) instead of the expected EBCDIC value of 64.

The CHR (character) function behaves the opposite of ASCII. It accepts a code (number between 0 and 255) and returns the ASCII character. For example:

Select CHR(48) From SysIBM/SysDummy1

This code returns the character for ASCII code 48: '0'. The expression CHR(65) as shown here:

Select * From (Values(CHR(65))) ASCII (CHRCODE)

This command returns an 'A'.

While these function will be helpful in helping EBCDIC developers better handle ASCII conversion issues, I think the greater use for them will be in cross-platform compatibility as many developers on other platforms use these functions to perform various tricks and tasks.

For instance, if you find yourself needing to write an SQL function to convert binary data to Base64 or to encode or decode an HTTP URL, then there's no sense in re-inventing the wheel because there are already many examples available on the Internet. Many of these custom-written functions use ASCII or CHR type functions. As I discussed in a prior FHG tip, when searching the Internet for "already invented code," don't limit yourself to looking for DB2 code. As the tip demonstrates , it is often relatively easy to locate and convert many SQL Server T-SQL (or other database) functions to DB2.

New Encryption Options

V5R3 introduced the first encryption function into the DB2 for i5/OS SQL dialect, the ENCRYPT_RC2 function. V5R4 followed suit with ENCRYPT_TDES and now V6R1 gives us ENCRYPT_AES (Advanced Encryption Standard). Simply put, ENCRYPT_AES allows DB2 to encrypt data using the industry standard (and popular) AES encryption algorithm. It's good to know that more options are becoming available in this area as it makes encrypted data increasingly easier to work with.

For example, if I have a .NET or SQL Server application that encrypts a credit card number using AES, I can securely pass that value for storage directly to DB2 for decryption at a later time. (Assuming the key is known from the .NET system!) Likewise, if a business partner mandates data exchanges are to be encrypted using AES, DB2 can help with this request.

For more information on the encryption functionality in DB2, see the "Encryption Functions" notes on the V5R3 SQL enhancements article. This section discusses how to encrypt, decrypt, set a password, and even set and retrieve a password hint.

MONTHS_BETWEEN Function

This new function accepts two dates and returns a fractional approximation of the number of months between the two dates. The result type of this function is DEC(31,15).

MONTHS_BETWEEN ( expression1 , expression2 )

In this case expression1 can be considered the "ending date" and expression2 the "starting date. " If expression1 is greater than expression2, then a negative value is returned.

The number of months between St. Patrick's Day and Thanksgiving of 2007 can be calculated with this expression:

MONTHS_BETWEEN('2007-11-22', '2007-03-17')

The result is 8.1613 months. Remember, the advantage of MONTHS_BETWEEN as opposed to the related TIMESTAMP_DIFF function is that MONTHS_BETWEEN gives a fractional result.

RID Function

This function returns the relative record number of a row for a given table identifier.

The new RID function appears to behave the same as the relative record number (RRN) function with the exception that it returns its result as a BIGINT data type. A BIGINT requires 8 bytes of storage. For the record, RRN returns a DEC(15,0) data type, which also requires 8 bytes of storage but can't store the range of numbers that BIGINT can. Honestly I haven't approached a table size approaching 10 trillion rows, but maybe someday! For compatibility with the DB2 family and for safety's sake (I used to laugh at querying a 1 million row table), it is probably best to start using RID instead of RRN.

Also keep in mind it is a little dangerous working with relative record numbers because they can change when a physical file is reorganized. Personally, I don't use relative record number functions except when performing some form of trickery. When I do use it, the queries are usually for testing or utility purposes--not for an application. If you need a counter in your query to place row numbers in your result set, consider using the ROW_NUMBER OLAP function.

Timestamp Functions: Timestamp_Format and To_Date

The Timestamp_Format accepts a string representation of a timestamp and a formatting code, and returns an actual timestamp. This new function will be a blessing to anyone who has had problems importing date/time data from an external application. For instance, say you have imported a spreadsheet that contains timestamps that look like this: '08/17/2008 10:30'.

Timestamp_Format will aid converting this specific string format to an actual timestamp data type by telling DB2 how the string representation is formatted. The formatting code is built using any of the following format units:

Select Timestamp_Format('08/17/2008 10:30',
                        'MM/DD/YYYY HH24:MI')
  From SysIBM/SysDummy1

VARCHAR_FORMAT(Ship_Stamp, 'MM/DD/YYYY HH24:MI') As Ship_DateTime

Select CurTime,Format,VarChar_Format(CurTime,Format) As SampleOutput
  From (Values('HH24:MI:SS YYYY-MM-DD',Current_Timestamp),
              ('MM/DD/YYYY HH24:MI',Current_Timestamp))
       Demo(Format,CurTime)

ROUND_TIMESTAMP(Current_Timestamp, 'HH')

ROUND_TIMESTAMP(Current_Timestamp, 'SS')
TRUNC_TIMESTAMP(Current_Timestamp, 'SS')

ROUND_TIMESTAMP(Current_Timestamp, 'Q')

Create Table AppData/DataTable 
(RowId Integer As Identity Primary Key, 
 Data1 VarChar(1000) Not Null, 
 AddStamp Timestamp  Not Null Default Current_Timestamp,
 ChgStamp Timestamp  Not Null 
          FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP)

Select *
  From AppData/DataTable DT
 Where Row Change Timestamp For DT>=Current_Timestamp-21 Days

Select *
  From AppData/DataTable DT
 Where DT.ChgStamp>=Current_Timestamp-21 Days

Select RowId,Row Change Timestamp For DT As "Last Change"
  From AppData/DataTable DT

Select CusNum,Row Change Token For Cust As "Change Token"
  From QIWS/QCUSTCDT Cust