Db2

Db2

Connect with Db2, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

Family matters: The SQL Reference for Cross-Platform Development 

Mon March 09, 2020 05:33 PM

Posted by: Serge Rielau

Background

The DB2 family is comprised of three products:
  • DB2 for Linux, Unix and Windows (LUW) and Infosphere Warehouse
    That's the product this blog mostly deal with and within which I have grown up as a developer.
    Some folks still like to call it "UDB" which is wrong on so many levels, but we know what you mean anyway.
  • DB2 for z/OS
    That's the product that runs on the mainframe's native OS.
    (Not to be confused with DB2 for LUW running on z/Linux).
    Some folks like to call it the "big" DB2. Again wrong, but I won't hold it against you.
  • DB2 for i
    That's the product that run's on System i.
    Some folks still call it DB2 for AS/400, that's OK, too.
Each product has its own history with its own sets of strengths and weaknesses. 
And each product has its own set of loyal and new customers.
Naturally different customers and applications mean that there are different needs for features when planning for new releases.
To maintain the highest level of compatibility between the platform as new versions of DB2 are developed and to enable partners and customers to share skills and applications a number measures are taken including:
  1. Any SQL changes going through our "SQL Language Council" to receive cross platform agreement.
    This way features get implemented with the same syntax and semantics once they are implemented on more than one platform.
    Call it an IBM internal SQL Standard's body if you will.
  2. A least common denominator of cross DB2 supported SQL is maintained and published for partners and customers to develop against if they wish to write 100% portable code.

Cross-Platform SQL Reference Version 4

That being said there is a new version 4 of this SQL Reference for Cross-Platform Development available here.

The document is lined up with:
  • DB2 for LUW Version 9.7,
  • DB2 for zOS Version 10
  • DB2 for i Version 7.1
Here are the highlights of Version 4:
  • XML data type
  • Datetime constants
  • Extended indicator variables
  • Enhanced untyped parameter marker support
  • XMLCAST specification
  • New functions:
    • Aggregate Functions: XMLAGG
    • Datetime Scalar Functions: ADD_MONTHS, EXTRACT, LAST_DAY, MONTHS_BETWEEN, NEXT_DAY, ROUND_TIMESTAMP, and TRUNC_TIMESTAMP
    • Numeric Scalar Functions: BITAND, BITANDNOT, BITNOT, BITOR, BITXOR
    • String Scalar Functions: TRIM
    • XML Scalar Functions
    • MQSeries Functions
  • Enhanced TIMESTAMP_FORMAT and VARCHAR_FORMAT functions
  • CROSS JOIN
  • Retrieval of result sets in embedded SQL (ALLOCATE CURSOR and ASSOCIATE LOCATORS statements)
  • Expressions in a CALL statement
  • Distinct types in a temporary table
  • MERGE statement
  • CONCURRENT ACCESS RESOLUTION in PREPARE statement
  • SET host variable statement
  • Additional control statements in SQL functions

If you want to get older versions of the SQL Reference or keep track of future version you can find them on developer works here.


#Db2

Statistics
0 Favorited
1 Views
0 Files
0 Shares
0 Downloads