decor
 

planetDB2 logo

Planet DB2 is an aggregator of blogs about the IBM DB2 database server. We combine and republish posts by bloggers around the world. Email us to have your blog included.

 

September 20, 2017


Kim May

IBM Think Conference Call for Speakers – Closes Today!!

Someone on the communications team at IBM seems to have persuaded IBM Management to postpone release of any time sensitive announcements or notifications until the very last minute.  Perhaps they...

(Read more)
 

September 19, 2017


DB2utor

Dealing with Db2 10-11 Migration Incompatibilities

Recently, someone on the DB2-L forum was seeking help with an application that started failing following an upgrade from Db2 for z/OS 10 to Db2 for z/OS 11. With Db2 10 going out of support on Sept. 30, there's undoubtedly a lot of migrations going on right now. With this in mind, I thought I'd point out some places to go for migration information.
Modern SQL

SQL extract — Get a Field from a Date or Time Value

SQL extract provides access to the components of temporal data types—i.e. date, time, timestamp, and interval.

SQL extract uses the keyword from to separate the field name from the value.

EXTRACT(<field> FROM <expression>)

The field names are SQL keywords too—you must not put them in double or single quotes.

SQL extract returns an exact numeric value. For second, it also includes fractions.0 The following table lists the extract fields defined by the SQL standard.

Meaningextract field
YearYEAR
MonthMONTH
Day of monthDAY
24 hourHOUR
MinuteMINUTE
Seconds (including fractions)SECOND
Time zone hourTIMEZONE_HOUR
Time zone minuteTIMEZONE_MINUTE

Related Features

Extract can only get single fields. To extract the full date (year, month, day) or time (hour, minute, second) from a timestamp, cast can be used:1

CAST(<timestamp> AS [DATE|TIME])

This is particularly useful for the group by clause. In the where clause, it is often the wrong choice. For more on this, see Inappropriate Use in The Where Clause below.


Caution: Oracle Database

The Oracle database doesn’t have a predefined date type without time components. Even the Oracle type date has a time component—in this regard, Oracle date is more like the standard timestamp.2 A type conversion (cast) to date therefore does not drop the time component in the Oracle database.

To use the date only—without time components—it is common practice to use the proprietary trunc function to set all time fields to zero (0):

trunc(<timestamp>)

Note that the result still has the time components—they are just set to zero. The effect is basically like the following cast expression in standard SQL:

CAST(CAST(<timestamp> AS DATE) AS TIMESTAMP)

Compatibility

SQL extract was available in SQL-92 (intermediate) and is now part of the optional feature F052, “Intervals and datetime arithmetic”. Despite its maturity and relevance, extract is still not supported by all major databases yet.

Availability of EXTRACT

Related Anti-Patterns

String Formatting Functions

A very common anti-pattern is to use string formatting functions (e.g. to_char) instead of extract to get single date or time fields. These string function often apply unintended formatting such as leading spaces or zeros, or a comma (,) instead of a period (.) as decimal mark based on the current locale.

This environmentally dependent behavior can lead to bugs that don’t show up in all environments and are thus hard to correct.

Inappropriate Use in The Where Clause

Consider the following anti-pattern:

WHERE EXTRACT(YEAR FROM some_date) = 2016

This anti-pattern is often followed to avoid specifying the “last moment of” the relevant time frame. This is, in fact, an important and desirable goal because specifying the “last moment of” is actually impossible:

Time units are not uniform

It is well known that the length of a month is not uniform. The rules for leap years are known at least in part. Just considering these facts, any “last moment of” could be determined algorithmically.

But there are also leap seconds, which are irregular. They are inserted occasionally on demand. For example, the last UTC second of the year 2016 happened to be 23:59:60. If you consider a day to end at 23:59:59 UTC, you might miss a whole second.3

Due to the irregularity and the rather short lead time when it comes to leap seconds insertions—the 2016 leap second was announced less than six month in advance—it is impossible to tell the last moment of a month for more than six month in advance.

In addition to this more or less theoretic special case, it is also good to avoid the need to specify the “last moment of” because it is rather awkward to calculate.

The time component’s resolution is unknown (at least in the future)

Even if you have correctly determined the last day and last second of a period, you might need to include a sufficient number of fractional digits to specify the “last moment of” a period. If you know that the type of the relevant column does not allow fractions (e.g., timestamp(0)), you don’t need to consider any fractions right now. But if the type is changed to timestamp(6) later, chances are the “last moment of” assumptions are not updated.

It is therefore a very good practice to avoid using the “last moment of”. Using extract, cast, or string formatting functions is just the wrong approach to reaching that goal.

The following where clause is equivalent to the extract example from above and still avoids specifying the “last moment of” the year 2016:

WHERE some_date >= DATE'2016-01-01'
  AND some_date <  DATE'2017-01-01'

Note the pattern: use an inclusive comparison (>=) for the lower bound but an excluding comparison (<) for the upper bound. Consequently you need to specify the first moment to be excluded from the result as the upper bound. The inclusive/exclusive pattern avoids the need to specify the “last moment of” the relevant time frame by using the less troublesome “first moment of” twice.

Compared to the extract solution, this approach has two advantages:

It works for arbitrary time frames

You can easily select a single month, day, …—even if it is not aligned to the calendar. Consider how you would implement the following example using extract, to_char, or something similar:4

WHERE some_date >= DATE'1994-03-11'
  AND some_date <  DATE'1995-03-11'
It can use an index on the date/time column

An index on some_date is mostly useless if the where clause wraps the indexed columns through a function or expression like extract.5 The explicit inclusive/exclusive pattern can make use of such an index. Learn more about indexing use at Use The Index, Luke!

Proprietary Extensions: Additional Fields

Some databases support further extract fields. The following table summarizes the more commonly available proprietary extract fields. Please note that these are proprietary extensions: they may behave differently from product to product. The field week, for example, works in three tested database, but returns a different result for each of them.

Proprietary extensions of EXTRACT

Proprietary Alternatives

Most databases offer enough functionality to get the same result as the standard extract expression. For those databases that do not (fully) support extract, you can find the proprietary alternative below.

Proprietary alternatives to EXTRACT

datepart: SQL Server

Microsoft SQL Server offers the proprietary datepart function. The following example is equivalent to extract(year from <datetime>).

DATEPART(year, <datetime>)

The return type is always an integer. Fractions of seconds can be retrieved as separate fields (e.g. millisecond).

The following expression behaves like extract(second from <datetime>) with up to nine fractional digits:

  DATEPART(second , <datetime>) 
+ CAST(DATEPART(nanosecond, <datetime>)
    AS NUMERIC(9,0)
      )/power(10,9)

See “DATEPART (Transact-SQL)” for the full list of available fields.

strftime — SQLite

SQLite offers the strftime function to format dates and times as strings.6 To extract a single component, just format that component and cast it to a numeric type if needed. The following example is equivalent to extract(year from <datetime>).

CAST(STRFTIME ('%Y', <datetime>) AS NUMERIC)

Note that the format string '%S' (for seconds) does not include fractions. Use '%f' instead (seconds including three fractional digits):

CAST(STRFTIME ('%f', <datetime>) AS NUMERIC)

extract(second_microsecond …) — MySQL, MariaDB

MySQL’s extract and MariaDB’s extract both always return integer values. To get the seconds with fractions, use the proprietary second_microsecond extract field:

EXTRACT(second_microsecond FROM <datetime>)/power(10,6)

SQL extract — Get a Field from a Date or Time Value” by Markus Winand was originally published at modern SQL.

 

September 18, 2017


Craig Mullins

The Db2 12 for z/OS Blog Series - Part 17: A New Privilege for UNLOAD

Db2 12 for z/OS introduces a new privilege that, when granted, enables a user to be able to unload data using the DB2 IBM UNLOAD utility. In past releases, the SELECT privilege (or other higher level admin privileges) was required to unload data using the UNLOAD utility. But this was less than desirable. Why? Well, one reason is that it created a potential security gap. Consider the situation...

(Read more)
 

September 14, 2017


Data and Technology

Hey, DBAs… Should You Be a Jack-of-all-Trades?

You know what a Jack-of-All-Trades is, don’t you? It is that person that always seems to have know-how on a topic or project that is helpful. They have a comprehensive knowledge of all things...

(Read more)
 

September 12, 2017


Henrik Loeser

Db2 with External Tables: First Tests

External Tables - New in Db2 Db2 Warehouse recently added a new feature named external table. Because I was interested in that feature for a while and I have an instance of Db2 Warehouse on Cloud...

(Read more)

DB2utor

Data Compression Enhanced in Db2 12

Db2 12 function level V12R1M500 introduces the capability to compress LOB table space using zEnterprise data compression (zEDC) hardware.
 

September 11, 2017


Kim May

It’s Back to School Season – Register for a TFG Class

With school back in session it feels like a good time to learn something new.  Why not consider taking a class?  IBM Analytics classroom training continues to deliver value to individuals and their...

(Read more)
 

September 08, 2017


Craig Mullins

See You in Lisbon for the EMEA IDUG DB2 Tech Conference!

This year the IDUG EMEA DB2 Tech Conference is being held in Lisbon, Portugal from October 1st through October 5th. Lisbon is a beautiful city with a lot to see and do, so you can plan for a great week of DB2 and analytics education... and then follow it up with a visit to truly remarkable city. Whether or not you care about vacationing is irrelevant, though, because a week at an IDUG...

(Read more)
 

September 06, 2017


Craig Mullins

DB2 for z/OS Coupling Facility Sizing

Just a very brief blog post today to inform you about something that I ran across doing some web research today... and that is a free, web-based Coupling Facility sizing tool from IBM named CFSIZER.  The CFSIZER tool will connect to your live server to get information. You will need to input some values and some estimation of the type of workload for your system and CFSIZER will output its...

(Read more)
 

September 05, 2017


DB2utor

A New Utility Stored Procedure

The stored procedure DSNUTILS has been deprecated as of Db2 12. It's used to call utility program DSNUTILB using EBCIDIC encoded parameters. While your application will continue to function when DSNUTILS is called, it could be removed from future releases. For this reason, you should replace DSNUTILS with either of two current support procedures, DSNUTILU and DSNUTILV.
 

September 01, 2017


Craig Mullins

The Db2 12 for z/OS Blog Series - Part 16: Db2 Catalog Availability Improvements

IBM has improved the availability of accessing Db2 Catalog objects when maintenance is being run in Db2 12 for z/OS. This impacts access during CATMAINT and online REORG. This change is largely being driven by dynamic SQL, which is more prevalent but can cause problems. When dynamic SQL statement is executed, Db2 must dynamically prepares the SQL to determine access paths in order to run it....

(Read more)
 

August 31, 2017


Robert Catterall

Db2 12 for z/OS SQL Enhancements: Advanced Triggers

In this, the fourth of a set of four entries covering SQL enhancements introduced with Db2 12 for z/OS, I will describe new trigger capabilities delivered by way of what are called "advanced triggers" (the kind of trigger that you could define prior to Db2 12 -- and which you can still create in a Db2 12 system -- is now referred to as a "basic trigger"). Before getting to the details of advanced trigger functionality, I want to emphasize that there are considerably more than four SQL-related...

(Read more)

DB2utor

How to Determine Current Db2 Function Level

With the introduction of continuous delivery making it possible for customers to put new features into production through regular maintenance, awareness of Db2 function levels takes on greater importance. So how do you determine what level you're at and what level of maintenance has been applied?
 

August 30, 2017


Dave Beulke

SQL Big Data Performance Tips

After data validation, in most instances SQL performance is the most important big data performance factor in big database applications. Understanding the fine details of DB2 SQL performance tips can make a huge difference in processing time, especially true when going against a big data database....

(Read more)
 

August 25, 2017


Craig Mullins

The Db2 12 for z/OS Blog Series - Part 15: DSN1COPY and Data Validation Improvements

If you’ve worked with Db2 for z/OS for awhile (note to IBM: I still have a problem with that lower case "b" but I'm trying), particularly as a DBA, you’ve almost certainly had the opportunity to use the DSN1COPY offline utility, sometimes called the Offline Copy utility. DSN1COPY can be used in many helpful ways. For example, it can be used to copy data sets or check the validity of table space...

(Read more)
 

August 23, 2017


Triton Consulting

Geocaching with the Geeks at IDUG

It’s almost that time of year again! The Triton Consulting and DBI Software drinks reception at IDUG EMEA, which has become an annual event, is less than 2 months away. Join us on Monday 2nd October from 8:30pm and chat … Continue reading →

(Read more)
 

August 22, 2017


DB2utor

Db2 for z/OS in the Cloud: What's Ahead

Troy Coleman explores how Db2 for z/OS on the IBM mainframe fits into the cloud.
 

August 18, 2017


Robert Catterall

Db2 12 for z/OS SQL Enhancements: a Better MERGE

Greetings, and welcome to the third in a series of entries covering my favorite Db2 12 for z/OS SQL enhancements. The first two posts in the series described piece-wise DELETE and result set pagination. This entry focuses on the new and very much improved MERGE functionality delivered via Db2 12 (usable at function level V12R1M500 and above). The MERGE statement, introduced with Db2 9 for z/OS, is sometimes referred to as the "upsert" statement, because it enabled, via a combination of...

(Read more)
 

August 16, 2017


Kim May

QMF Analytics for Multiplatforms – September Baltimore/Washington DB2 Users Group

Frank Fillmore just finished delivering a webcast with IBM’s Steve Mink and Doug Anderson, hosted by Blanca Borden, on the just-released QMF Analytics for Multiplatforms solution.  For the many...

(Read more)

Henrik Loeser

Combining Db2 and Cloud Object Storage

Db2 and Cloud Storage happily combined Since a while, Db2 has built-in support to directly access Cloud Object Storage (COS) like Amazon S3 and IBM COS (Softlayer / IBM Bluemix IaaS). It allows to...

(Read more)
 

August 15, 2017


DB2utor

System z Apprentice Program

Long-time readers of this blog know I've often discussed the mainframe skills shortage and cited various initiatives that aim to attract the next generation of talent. I came across new organization while attending last month's SHARE conference: LEAD-IT (Leadership, Empowerment, Apprenticeship and Diversity in Information Technology).

Henrik Loeser

Secure Your Db2 Data Base - Using Trusted Contexts

Db2 Security - Trusted Context You have that new app, maybe even in the cloud, with that user needing read and write access to the database? Don't want to open the floodgates for that favorite...

(Read more)
 

August 12, 2017

 

August 09, 2017


Data and Technology

Have You Thought About the Impact of eDiscovery on Your Data Management Strategy?

When thinking about data management it is unlikely that your first thought is about legal requirements. Nevertheless, the legal side of data management must be considered in this day and age of...

(Read more)

Kim May

Webcast: Introducing QMF Analytics for Multiplatforms

QMF is bringing its power and ease to the distributed platforms! In July QMF Analytics for Multiplatforms became generally available.  This distributed platform only solution is aimed at customers...

(Read more)

Henrik Loeser

Introduction to Cloud App Security - Part 2

(This is a continuation from part 1 of this introduction to Cloud App Security.) Providing a secure app or application is a fundamental requirement. This is especially true in a cloud environment....

(Read more)

Henrik Loeser

Introduction to Cloud App Security - Part 1

Security for Cloud Apps Providing a secure app or application is a fundamental requirement. This is especially true in a cloud environment. In my post about “Securing Workloads on the IBM Cloud” I...

(Read more)
 

August 08, 2017


DB2utor

New Function Level 501: Continuous Delivery Makes its First Delivery

Db2 12 introduces continuous delivery. Making it possible for customers to rapidly put new features into production through normal maintenance is huge for the mainframe's continued viability. Businesses will now have a more positive view of running applications on DB2 for z/OS because they know they'll no longer have to wait years for required features.
 

August 04, 2017

 

August 03, 2017


Henrik Loeser

Db2 Quiz: Oh what files!?

Today, it is time for another Db2-related quiz. Take a look at the screenshot below. What do you see, what is it? Db2 Quiz Need a hint? The quiz is related to my upcoming presentations at the...

(Read more)

Subscribe by email

 

About

planetDB2 is an aggregator of blogs about the IBM DB2 database server. We combine and republish posts by bloggers around the world. Email us to have your blog included.
 

Bloggers

decor