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.

 

October 20, 2017


Kim May

We’re Here – Use Us!

I really enjoyed Lisbon and seeing everyone at the IDUG Conference.  More than one person commented on the turnout (excellent) and the crowd (young!)  There seems to be some new energy in the Db2...

(Read more)

Triton Consulting

Db2 Day – Db2 on Linux, Unix, and Windows. You’re invited!

IBM has just announced details of a complimentary event, Db2 day – Db2 on Linux, Unix and Windows. The face to face event, presented by George Baklarz, IBM Db2 Program Manager will cover the latest enhancements to Db2. Attendees will … Continue reading →

(Read more)
 

October 18, 2017


Dave Beulke

5 Ways to Save Millions When Migrating from Oracle to Db2

Working with a large number of consulting customers brings many different corporate attitudes, database platforms, innovative ideas, styles of database utilization and cost structures for database operations. Software licensing, support and application development costs often lead into strategic...

(Read more)

Frank Fillmore

IBM Db2 Analytics Accelerator (IDAA) v7 Announced #IBMz #zAnalytics

On July 23, 2010 I attended the announcement of the IBM Smart Analytics Optimizer (ISAO).  This was IBM’s first iteration of heterogeneous scale-out to enable mixed OLTP and OLAP workloads to...

(Read more)
 

October 17, 2017


Data and Technology

Gaining Value from Analytics

Data volume and higher transaction velocities associated with modern applications are driving change into organizations across all industries. This is happening for a number of reasons. Customer and...

(Read more)

DB2utor

Db2 12 Brings Enhanced Data Validation Capabilities to DSN1COPY

This post is outlines DSN1COPY, which is a stand-alone utility to copy Db2 VSAM data sets.
 

October 16, 2017

 

October 13, 2017


DB2Night Replays

The DB2Night Show #Z80: DevOps and Cloud Provisioning for Db2 z

Presented by: Patrick Bossman DB2 for z/OS SWAT team "The DB2Night Show #Z80: DevOps and Cloud Provisioning for Db2 z" Replays available in WMV and M4V formats! 100% of our studio audience learned something!Patrick gave many tips for improving developer and DBA productivity by reducing waits in the development process. Watch the replay...

(Read more)
 

October 11, 2017


Craig Mullins

The Db2 12 for z/OS Blog Series - Part 18: Adaptive Indexes

Have you ever had one of those tough queries that was always a challenge to keep performing well? This type of query usually experiences fluctuating filtering. By that I mean that the filtering can change, sometimes dramatically, between executions of the query. Some of the things that can cause fluctuating filtering are predicates with ranges that vary, sometimes returning a small subset of...

(Read more)
 

October 10, 2017


DB2utor

Db2 12 Avoids CPU and I/O During PIT Recovery

Traditionally when a point-in-time (PIT) recovery is needed, all the tables that have a referential relationship will be recovered simultaneously to the same PIT. The problem is there's no way to determine whether one or more parent tables or related child tables have changed. Besides substantially lengthening the recovery time, this can consume a lot of CPU resources, which of course drives up recovery costs.
 

October 05, 2017

 

October 03, 2017


DB2utor

DBaaS and Streamlined Development

The term Database-as-a-Service (DBaaS) can convey different things. You may think of off-premise cloud deployment of a DBMS, like IBM's Db2 on Cloud (formerly known as dashDB).
 

September 30, 2017


ChannelDB2 Videos

HADR Tutorial Part 6 - TCPIP SND/RCV Buffer impedes HADR Startup in Primary Database


Thumbnail

Description: OS TCPIP SND/RCV Buffer impedes HADR Startup in Primary Database Happy Learning & Sharing http://db2luwacademy.blogspot.in

Robert Catterall

Db2 for z/OS: Lock Avoidance

Not long ago, I served as part of the teach team for a Db2 12 for z/OS Technology Workshop that IBM offered in a city in the USA. During that workshop, I covered, among other things, a Db2 12 enhancement that can improve the CPU efficiency of a Db2 data sharing system by reducing locking activity in that environment. During a break, one of the workshop attendees told me (in words to this effect), "That lock avoidance enhancement is good news, but I think a fair number of Db2 for z/OS people are...

(Read more)
 

September 29, 2017


Henrik Loeser

App Security Scanner: My Cloud Database App is Secure

Cloud Application Security Over the past years I have written a couple cloud-based apps. Most of them have a database backened. I know - to a good degree - how to secure the database system. I have...

(Read more)
 

September 28, 2017


ChannelDB2 Videos

DB2 HADR Part 5 - Stand By Database continues Activities in spite of Primary Not Reachable


Thumbnail

Stand By Database continues Activities in spite of Primary Not Reachable. Stand By Database continues execution of shipped log records can resynch later. Sta...
 

September 26, 2017


Leons Petrazickis

Integrate your Rails app with Open edX SSO and Oauth2

OpenEdX is an open source platform for MOOCs. I put together the omniauth-cognitiveclass gem for integrating Rails apps with Open edX SSO using Oauth2 The post Integrate your Rails app with Open edX...

(Read more)
Jack Vamvas

DB2 Archived Log Compression

Question: Can I compress DB2 LUW archived logs? How can I configure a DB2 database to apply compression to the archived logs automatically?

Answer: It is possible to configure a DB2 LUW database to compress Archived Logs. The two primary database configurables are to use Primary log archive compression (logarchcompr1)  and secondary log archive compression (logarchcompr2).

By simply changing the default value of NO to YES , does not automatically mean there will be archival logging compression.

LOGARCHMETH1 must be set to either DISK, TSM, or VENDOR. If these values are not used there will be no compression of archivedlog files

Some other ideas to explore include compressed tables . Compressed tables will pass compressed record images to the transaction logs , which makes the log files smaller.

Note: Often managing DB2 LUW transaction logs can lead to space issue , generating alerts for full disk. It can be tempting to compress active logs, BE CAREFUL . If you’re lucky it may only negatively impact backups – but it’s always possible your database may become unusable. If someone accidentally compresses an active log file – and your ONLINE backup is failing , take an OFFLINE as soon as possible.

Notes on DB2 LUW archive log and BACKUP command

Troubleshooting DB2 Return Codes, failed to archive log and SQLUV_INIT_FAILED

Jack Vamvas

DB2 Archived Log Compression

Question: Can I compress DB2 LUW archived logs? How can I configure a DB2 database to apply compression to the archived logs automatically?

Answer: It is possible to configure a DB2 LUW database to compress Archived Logs. The two primary database configurables are to use Primary log archive compression (logarchcompr1)  and secondary log archive compression (logarchcompr2).

By simply changing the default value of NO to YES , does not automatically mean there will be archival logging compression.

LOGARCHMETH1 must be set to either DISK, TSM, or VENDOR. If these values are not used there will be no compression of archivedlog files

Some other ideas to explore include compressed tables . Compressed tables will pass compressed record images to the transaction logs , which makes the log files smaller.

Note: Often managing DB2 LUW transaction logs can lead to space issue , generating alerts for full disk. It can be tempting to compress active logs, BE CAREFUL . If you’re lucky it may only negatively impact backups – but it’s always possible your database may become unusable. If someone accidentally compresses an active log file – and your ONLINE backup is failing , take an OFFLINE as soon as possible.

Notes on DB2 LUW archive log and BACKUP command

Troubleshooting DB2 Return Codes, failed to archive log and SQLUV_INIT_FAILED


Triton Consulting

Heads-up! DB2 Geek Geocaching Competition

With less than a week to go until IDUG EMEA conference in Lisbon we thought we’d give our fellow DB2 Geeks a heads-up on what they’ll need in advance of the Triton and DBI Software competition set to run from … Continue reading →

(Read more)

DB2utor

Use of NEWFUN versus APPLCOMPAT

I was recently asked about the differences between DSNHDECP NEWFUN(V11) and the APPLCOMPAT=V11R1 zparm system parameter. It's certainly an interesting question, so let's answer it here.
 

September 25, 2017


Henrik Loeser

2017 IDUG Db2 Tech Conference in Lisbon, Portugal

IDUG EMEA 2017 Conference The EMEA edition of the IDUG Db2 Tech Conference is around the corner, staring the coming Sunday. I am going to talk about Db2 Security on Monday, October 2nd, in session...

(Read more)
 

September 24, 2017


ChannelDB2 Videos

DB2 HADR Part 4 - Deactivation of HADR Stand By Database in spite of running Activities


Thumbnail

Deactivation of HADR Stand By Database is possible in spite of running Activities, Utilities, Queries etc. Stand By Database need not be CONSISTENT State dur...

ChannelDB2 Videos

DB2 HADR Part 4 - Deactivation of HADR Stand By Database in spite of running Activities


Thumbnail

Deactivation of HADR Stand By Database is possible in spite of running Activities, Utilities, Queries etc. Stand By Database need not be CONSISTENT State dur...

ChannelDB2 Videos

What happened in the background while taking backup in DB2?


Thumbnail

What happened in the background while taking backup in DB2? As we all know in db2 architecture EDUs play a vital role, same here also. In backup process mode...

ChannelDB2 Videos

How to create a new database same as Sample in DB2.


Thumbnail

We can create a sample database using db2sampl command in DB2, we all know that. If there is any requirement that we have to to create a database with the sa...

ChannelDB2 Videos

How to create a new database same as Sample in DB2.


Thumbnail

We can create a sample database using db2sampl command in DB2, we all know that. If there is any requirement that we have to to create a database with the sa...
 

September 22, 2017


DB2Night Replays

The DB2Night Show #196: Db2 LUW Transaction Log Expedition w/ Ember Crooks

@ember_crooks Special Guest: Ember Crooks Director, Xtivia IBM DB2 GOLD Consultant SQL Infusion: A Db2 LUW Transaction Log Expedition! 96% of our audience learned something! Ember took us on an educational journey that covered just about everything related to Db2 transaction logs, active logs, archive logs, configuration settings, recommendations, and sample SQL for examining your systems. As for the 4% that didn't learn something --- they...

(Read more)
 

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

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.

Note that SQL’s between cannot be used for this pattern because between includes both boundary values.

Compared to the extract solution, the inclusive/exclusive condition 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.

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