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.

 

January 19, 2018


DB2Night Replays

The DB2Night Show #201: Favorite Tuning Tips and Tricks for Db2 LUW

Special Guest: Joe Geller, JP Morgan Chase Favorite Tuning Tips and Tricks for Db2 LUW 100% of our audience learned something! SELECTIVITY, OPTGUIDELINES, Runstats, Indexes, Bufferpool Design, REORGs, monitoring advice, and more!! No wonder Joe won Best User Speaker at IDUG Lisbon 2017! Joe's presentation is full of terrific advice and tips! Watch and learn...

(Read more)
 

January 17, 2018


Triton Consulting

DB2 LUW Consultancy Taster Day

Here at Triton Consulting we are celebrating our 21st birthday. Although it doesn’t feel like much has changed since we set up shop, looking back it’s hard to believe that in the same year the internet had only just taken … Continue reading →

(Read more)
 

January 16, 2018


Triton Consulting

BLU Hits and Misses – the replay

Replay! If you missed the last DB2Night Show of 2017 then fear not, our partners over at DBI Software HQ have made the replay of Mark Gillis’ DB2 11 Performance: BLU Hits and Misses available as a replay. Take a … Continue reading →

(Read more)

DB2utor

Enhancements for Db2 Native REST Web Services

IBM recently released some fixes and new functionality for Db2 for z/OS. If you're currently using and or planning to use Db2 native RESTful web services, you should apply this maintenance. For Db2 11, apply APAR PI86867; for Db2 12, apply APAR PI86868.
 

January 12, 2018

Big Data University

Deploy Watson Conversation Chatbots to WordPress

If you’re reading this then you’ve most likely heard all the buzz around chatbots. In fact, you may have come up with a few scenarios where it would be really helpful for you to use one.

Most people consider chatbots to be in the realm of what only programmers can create, out of reach of business users who would otherwise have a need for them.

Thankfully, IBM provides the Watson Conversation service on their IBM Cloud platform which, combined with our WordPress plugin, solves that.

The plugin provides you with an easy way to deploy chatbots you create with IBM Watson Conversation to WordPress sites. In fact, you may have noticed a floating chatbot icon at the bottom of this page. Click on it to see the plugin in action.

What is Watson Conversation?

Watson Conversation is IBM’s chatbot service. Its intuitive interface allows chatbot creators to build their chatbot and have it ready to deploy in short time. You can sign up for a free IBM Cloud Lite account to get started.

Building your chatbot won’t be covered in this article but we have a great Chatbot course that guides you through this process and doesn’t require any coding expertise.

 

 

How do I add a chatbot to my website?

This is where the Watson Conversation WordPress plugin saves you time and money. If you have a website built using WordPress, deploying your chatbot to your website takes about 5 minutes and no code at all (as opposed to having to build your own application just to deploy a chatbot on the web.)

You can install it like any other WordPress plugin from your Admin page, that is, the first page you see after signing in.

 

 

Just search for Watson Conversation in the “Add New” section of the Plugins page and click “Install Now”.

Now you can find a page for “Watson” in your Settings. This is where you’ll find all the settings and customization to do with the plugin. When you first open it, you’ll see several tabs along the top.

For now, the only one you have to worry about is “Main Setup”.

 

 

You can find the credentials for the three required fields on the Deploy page of your Watson Conversation workspace.

 

 

Now just click save changes and you’re done. Browse your website and see your chatbot in action!

If you’re not quite satisfied with the appearance, you can customize this in the “Appearance” tab of the settings page.

You can also choose which pages to display the chat box on from the “Behaviour” tab. However, that’s not all you can do.

If you want to make the options clear to the user, you can create predefined responses to the chatbot messages for the users to select. The VOIP feature can connect users to your phone line over the internet from directly within the plugin.

In this brief article, we focused on how to deploy Watson Conversation chatbots to WordPress. Stay tuned for future articles on how to customize and use these exciting advanced features!

The post Deploy Watson Conversation Chatbots to WordPress appeared first on Cognitive Class.


DB2Night Replays

The DB2Night Show #Z83: Copy Smarter - Unload/Load, DSN1COPY and beyond

Presented by: Kai Stroh UBS Hainer "The DB2Night Show #Z83: Copy Smarter - Unload/Load, DSN1COPY and beyond" Replays available in WMV and M4V formats! 96% of our studio audience learned something!Kai described the requirements and issues of copying Db2 data between environments. Watch the replay...

(Read more)

Adam Gartenberg

Upcoming Change in Connections Metrics

For those of you who use the metrics function in Connections, we published a tech note today regarding an upcoming change that you should be aware of. (The formal announcement on this change should...

(Read more)
 

January 10, 2018


Dave Beulke

Five Data Management Predictions for 2018

Happy New Year! I hope everyone has their 2018 filled with good health, happiness and prosperity. Once again I am offering you my data management predictions for 2018. Thank you all for great support in 2017 and countless emails regarding my conference, user group presentations and blogs last...

(Read more)
 

January 09, 2018


DB2utor

Converting from 6- to 10-byte RBA/LRSN

Db2 11 introduced an option to convert the log relative byte address (RBA) and data sharing log record sequence number (LRSN) from the basic 6-byte format to the extended 10-byte format. Timm Zimmermann of the IBM Db2 for z/OS development "swat team" has a great presentation that goes into the details on the different formats. More importantly, Timm lays out the dwindling time that customers have left to convert their old 6-byte addresses and numbers to the soon-to-be standard 10-byte format.
 

January 08, 2018


Henrik Loeser

DeDUG-Treffen in Karlsruhe bei Fiducia GAD (Db2 User Group Meeting)

Db2 User Group Happy New Year! I hope you had a great start into 2018. How about meeting in person soon? I am going to speak next week at the German Db2 User Group (DeDUG) meeting in Karlsruhe. It...

(Read more)
 

January 04, 2018


Kim May

2018 New Year’s Resolutions – Let’s Get Started!

As always the holidays are over too soon.  Here at The Fillmore Group we are back to work, starting the year with the usual goal setting and head scratching.  Three initiatives we worked on in 2017...

(Read more)
 

January 03, 2018


DB2utor

IBM Z Trial Program

Mainframe customers running z/OS know all too well the amount of the work that comes with a proof-of-concept. The process requires months of planning among system programmers, security and storage administrators and end users.
 
 

January 02, 2018


Data and Technology

Data Technology Today in 2017

Here we are in the first week of 2017 so it is time, once again, to look at what happened this past year in the blog. First of all, there were 16 new blog posts this year so I averaged …...

(Read more)
 

December 31, 2017


Robert Catterall

Db2 for z/OS: Opportunities for CPU Efficiency When Data Clustering is not a Priority

Not long ago, a veteran Db2 for z/OS DBA contacted me with an interesting question. He had, in the system he supported, a table with a clustering index, and he wanted to know how he could get Db2 to act, with regard to INSERT operations and also with regard to executions of the REORG utility, as though the table had no clustering index. In other words, the DBA had a table for which INSERT and REORG CPU efficiency mattered more than having rows physically ordered per a clustering key. That's...

(Read more)
 

December 29, 2017


Data and Technology

Happy Holidays 2017!

Just a short post to end the year wishing all of my readers everywhere a very happy holiday season – no matter which holidays you celebrate, I hope they bring you joy, contentment, and let you...

(Read more)
 

December 27, 2017

Jack Vamvas

How to create a Date Series in DB2

Question: How can I create a Date Series  in DB2 LUW. e.g. 01-01-2017 to 30-01-2017 . ? 

Answer:  There are a number of different ways to create a date series in DB2 LUW.One way is the example used below, which lists the dates from 1st January 2017 - 30 January 2017. :

 

 db2 "with tempdateseries (date) as (
select date('01.01.2017') as date from sysibm.sysdummy1
union all
select date + 1 day from tempdateseries
where date < date('30.01.2017')
)   select * from tempdateseries"

This method will give you an output example such as :

01/01/2017
02/01/2017
03/01/2017
04/01/2017
05/01/2017
06/01/2017
07/01/2017
08/01/2017
09/01/2017
10/01/2017
11/01/2017
12/01/2017
13/01/2017
14/01/2017
15/01/2017
16/01/2017
17/01/2017
18/01/2017
19/01/2017
20/01/2017
21/01/2017
22/01/2017
23/01/2017
24/01/2017
25/01/2017
26/01/2017
27/01/2017
28/01/2017
29/01/2017
30/01/2017

If it's a high impact query - it may be more useful to INSERT the results into a table - where the code can reference as required.

 

 

December 25, 2017


Craig Mullins

Seasons Greetings 2017

It is that time of year again... A time to reflect on the year gone by and to enjoy time with our friends, family and loved ones.  A time to put down the work that consumes us most of the year and to celebrate and enjoy... to remember past years and to consider the upcoming year.   No matter what holiday tradition you celebrate, I wish you an enjoyable holiday season. Seasons...

(Read more)
 

December 20, 2017


Dave Beulke

All Application Languages Can Access Db2 z/OS

Almost all application languages can use Db2 for z/OS. It surprises me when discussing the Db2 interfaces with management, architects, application managers and developers that they are not aware of wide variety, new languages and interfaces available to Db2 for z/OS and Db2 LUW. Below is only a...

(Read more)
 

December 19, 2017


DB2utor

The Evolution of Compression: Db2 12

With each release of Db2, enhancements have been made to improve compression performance, availability and scalability. An important change in Db2 12 is the introduction of the online deferred option, ALTER INDEX COMPRESS YES. This allows you to able compression for an index without causing an outage.
 

December 18, 2017


DB2Night Replays

The DB2Night Show #200: IBM Db2 V11 Performance: BLU Hits and Misses!

Follow @db2geek Special Guest: Mark Gillis, Triton Consulting Db2 V11 Performance: BLU Hits and Misses 100% of our audience learned something! Based on his popular IDUG Lisbon Db2 technical session, Mark covered an overview of BLU, examples of workload performance in Db2 V10.5, the benefits of Db2 V11 for some workloads, showed examples of BLU savings, and shared a few BLU gotchas. If you're using Db2 BLU, or thinking about it, you must...

(Read more)

Craig Mullins

The Db2 12 for z/OS Blog Series - Part 20: Fast Insert: An Alternative INSERT Algorithm

Db2 12 offers many performance improvements that can help you to speed up your applications. The Fast Insert algorithm, also called Insert Algorithm 2, is one such improvement, but you have to understand what it is and when it can be helpful. The general idea behind this alternative Insert algorithm is to improve performance of Insert-intensive workloads for unclustered data. This is a common...

(Read more)
 

December 15, 2017


Adam Gartenberg

Connections Customizer now Generally Available

An important part of the "Pink" vision for the future of IBM Connections is the ability to extend and customize the environment. We took a big step forward in that vision with the general...

(Read more)
 

December 14, 2017


Kim May

First Half 2018 Classes Posted at www/ibm.com/training

The Fillmore Group 1H 2018 Class Schedule As an IBM Business Partner that truly believes that proper training is one of the most critical factors influencing the successful deployment and use of...

(Read more)
Modern SQL

Modern SQL: Three-Valued Logic (3VL) — Purpose, Benefits and Special Cases

The Three-Valued Logic of SQL

SQL uses a three-valued logic: besides true and false, the result of logical expressions can also be unknown. SQL’s three valued logic is a consequence of supporting null to mark absent data. If a null value affects the result of a logical expression, the result is neither true nor false but unknown.

The three-valued logic is an integral part of Core SQL and it is followed by pretty much every SQL database.

Comparisons to null

The SQL null value basically means “could be anything”. It is therefore impossible to tell whether a comparison to null is true or false. That’s where the third logical value, unknown, comes in. Unknown means “true or false, depending on the null values”.

The result of each of the following comparisons is therefore unknown:0

NULL = 1
NULL <> 1
NULL > 1
NULL = NULL

Nothing equals null. Not even null equals null because each null could be different.


Mnemonic

For comparisons every null is a different null. This is different in group by, partition by and related operations.


That’s why SQL has the is null predicate to test whether a value is null or not and the is not distinct from predicate to compare two values while treating two null values as the same.

Logical Operations Involving Unknown

In logical connections (and, or), unknown behaves like the null value in comparisons: The result is unknown if it depends on an operand that is unknown. In contrast to comparisons, this principle leads to cases in which the result of a logical connection is not unknown even though one operand is unknown. The reason is that the result of a logical connection is only unknown if it actually depends on an operand that is unknown.

Consider the following example:

(NULL = 1) OR (1 = 1)

Although the comparison to null makes the first operand of the or operation unknown, the total result is still true because or operations are true as soon as any operand is true.

Another way to look at it is to mentally replace each null with a call to a random() function. If the overall result of the expression is inevitably the same, no matter which value random() returns, the result obviously does not depend on the null value and it is therefore not unknown.

In the example above you can assume the values 0 and 1 instead of null to make the result of the first operand false and true respectively. But the result of the complete expression is true in both cases—it does not depend on the value you assume for null.


Important

The logical value unknown indicates that a result actually depends on a null value.


A similar case applies to the and operator: and connections are false as soon as any operand is false. The result of the following expression is therefore false:

(NULL = 1) AND (0 = 1)

In all other cases, any unknown operand for not, and, and or causes the logical operation to return unknown.1

General Rule: where, having, when, etc.

The where, having, and when clauses (e.g. in case expressions) require true conditions.2 It is not enough that a condition is not false.

The result of the following query is therefore always the empty set:

SELECT col
  FROM t
 WHERE col = NULL

The result of the equals comparison to null is always unknown. The where clause thus rejects all rows.

Use the is null predicate to search for null values:

WHERE col IS NULL

Odd Consequence: P or not P is not always true

As the name “three-valued logic” suggests, there are three values to consider in logical expressions. At first sight the following where clause looks like a tautology—i.e. a condition that is always true. Nonetheless, it behaves entirely differently as it considers only two out of the three possible values—namely that the condition col = NULL is true or false.

SELECT col
  FROM t
 WHERE      col = NULL
    OR NOT (col = NULL)

(1) The result of the comparison col = null is unknown in both cases; (2) not(unknown) is also unknown; (3) or only evaluates as true if one operand is true. Consequently, the condition is always unknown so that the where clause rejects all rows. This is clearly quite the opposite of what two-valued logic would suggest.3

Odd Consequence: not in (null, …) is never true

Consider this example:

WHERE 1 NOT IN (NULL)

To understand this example, read null as “could be anything” or random() if you prefer. Then try to find two values for null that make the expression true and false respectively. Let’s take 0 and 1. For 0, the expressions becomes 1 NOT IN (0), which is true. For 1, the expression becomes 1 NOT IN (1), which is clearly false. The result of the original expression is therefore unknown, because it changes if null is replaced by different values.

If we extend this example we will quickly see that the result of not in predicates that contain a null value is never true:

WHERE 1 NOT IN (NULL, 2)

This expression is again unknown because substituting different values for null (e.g. 0 and 1) still influences the result. It is nevertheless easy to show that not in predicates that contain a null value can be false:

WHERE 1 NOT IN (NULL, 1)

No matter which value you substitute for the null (0, 1 or any other value) the result is always false.4


Tip

Don’t allow null in not in lists.

When using a subquery, consider using not exists instead of not in5 or add a where condition to the subquery that removes possible null values.


Exception: Check Constraints

Check constraints follow the reverse logic: they reject false, rather than accepting true as the other clauses do.6 Consequently, check constraints accept true and unknown.

In the following example, the column a or b can have a value greater 10 if the other column is null:

CREATE TABLE t (
    a NUMERIC CHECK (a >= 0),
    b NUMERIC CHECK (b >= 0),
    CHECK ( a + b <= 10 )
)

Truth Value Tests: is [not] (true|false|unknown)

Similar to is null, the SQL standard defines an optional feature to directly test for all three truth values:7

is [not] (true|false|unknown)

Note that this is test never returns unknown (very much like is [not] null).8

Compatibility below describes which databases support this feature.


Tip

Use (<expr>) is not false instead of (<expr>) or (<expr>) is null. See also Binary Decisions Based on Three-Valued Results below.


Boolean Data Type: Literals true, false and unknown

The barely supported optional feature T031, “BOOLEAN data type”, introduces the keywords true, false and unknown outside of the is predicate.

Note that the truth value unknown is indistinguishable from the null for the Boolean type.9 Otherwise, the Boolean type would have four logical values.

The difference between the literals null and unknown is that unknown is of type Boolean while null can take any type. Putting a not null constraint on a column of the SQL type Boolean makes it a classical two-valued Boolean.

Binary Decisions Based on Three-Valued Results

The three-valued logic of SQL postpones a binary decision if a logical expression cannot be said to be unconditionally true or false. However, in the end there is always a truly binary decision, such as taking or rejecting a row due to a where clause.10

As explained above, the SQL standard generally treats unknown like false when it eventually has to make a binary decision (exception: check constraints). Think of it like an implied is true test on every where, having, and so on.

Treating unknown like false is not always the right choice. If you need another behavior, just use an explicit is [not] (true|false|unknown) test.

Consider the following example that uses nullif to prevent a potential division by zero error. Consequently, the where condition becomes unknown for rows where d is zero (0) and those rows are rejected by the where clause.

SELECT n, d
  FROM t
 WHERE n/NULLIF(d,0) > 1

If you need to return the rows with d = 0 as well, you can add OR d = 0 to the where clause. Of course this is a correct solution, but it requires an understanding of the condition. A more generic approach is to repeat the entire null-able expression in order to explicitly include the null case: OR (n/NULLIF(d,0)) IS NULL. Still, that is not exactly elegant.

The idiomatic way to reduce a three-valued result to a two-valued one is the truth value test is [not] (true|false|unknown):

 WHERE (n/NULLIF(d,0) > 1) IS NOT FALSE

This accepts both results—true and unknown—and is logically equivalent to the solutions that use an or connection. The benefit is that it does not require any repetition or semantic understanding of the condition.


Tip

Put the condition in parenthesis to avoid ambiguity:

() IS NOT FALSE

The is not false predicate belongs to the optional feature F571, “Truth value tests”, which is still not generally supported. It is nevertheless possible to implement the same logic, without repeating parts of the expression, in practically all SQL databases with a case expression:

 WHERE CASE WHEN NOT(n/NULLIF(d,0) > 1)
            THEN 0
            ELSE 1
        END = 1

This expression explicitly tests for the false case (when not (…)) and uses the else clause to catch the two other cases (true and unknown). This allows for the required mapping without repeating any part of the condition. The numeric literals were arbitrarily chosen to represent “false” (0) and “true or unknown” (1). The concluding comparison (= 1) is always true or false because neither operand can ever become null.

The workaround with case can map unknown to either true or false. This covers four out of the six possible cases: is [not] (true|false). The two remaining cases, is unknown and is not unknown, cannot be implemented using case without repeating some parts of the logical expression.

To emulate the is [not] unknown test, you can exploit the fact that unknown is the same as null for Boolean values. In principle, it is possible to use is [not] null to test for unknown. In practice, this is hardly useful because most databases that don’t support is [not] unknown don’t support the Boolean type either.

That means that you must test the operands of the comparison for null and combine the result logically.

The following example demonstrates this:

(<expr1> <op> <expr2>) IS UNKNOWN

This is logically equivalent to:

   <expr1> IS NULL
OR <expr2> IS NULL

For an is not unknown test you have to use is not null tests and combine them with a logical and operation.11

Compatibility

Three-valued logic has been in the SQL standard from the beginning. It is an integral and widely supported aspect of SQL.

SQL:1999 added two related optional features:

  • F571, “Truth value tests”: extends the is operator for all three logical values.

  • T031, “BOOLEAN data type”: defines the Boolean data type, the aggregate functions every, any, and some, as well as the literals true, false, and unknown (other than following is [not]).

Availability of Features Coping With Three-Valued Logic

The Three-Valued Logic of SQL” by Markus Winand was originally published at modern SQL.

 

December 13, 2017


Triton Consulting

Have you got Christmas covered?

With Christmas just around the corner now is a good time to consider additional support over the holiday period. Perhaps you have a small in-house team or are struggling to find DB2 resources. Providing adequate cover for your DB2 databases … Continue reading →

(Read more)
 

December 12, 2017


Henrik Loeser

News on IBM Cloud and Db2 - December 2017 Edition

Another month and a lot of news In the middle of November I reported about significant changes to Bluemix and IBM's cloud offerings. A month has passed and I want to give you an update to some news...

(Read more)

DB2utor

The Evolution of Compression: Db2 11

This is my fourth installment in a series detailing the history of Db2 compression. As is the case with all previous releases, the Db2 engine takes advantage of the z System hardware platform in Db2 Version 11 for z/OS. With Db2 11 and zEC12 hardware, compression overhead was reduced by as much as 15 percent.
 

December 11, 2017

Jack Vamvas

How to check DB2 LUW load status

Question: How can I check load status in DB2 LUW?

Answer: Checking the DB2 LUW load status is achieved by using the load query command. The best way to explain is by demonstrating with a simple example.

The DB2 LUW command is used to load data into a DB2 LUW table. The data can be stored in many different forms – including named pipe and tape

Starting the load process

 

connect to mydb; 
create table table2 like table1; 
load from somedata.del of del insert into table2; 


While the load process is in flight , create another connection and view the progress

connect to mydb; 
load query table table2; 
connect reset;

 Read More on topics related to How to check DB2 LUW load status 

db2move to copy data between databases

 

December 08, 2017


DB2Night Replays

The DB2Night Show #199: Awaken your Data with RESTful Access to Db2!

Follow @upintheairyyz Special Guest: George Baklarz, Program Director, IBM Canada Awaken your Data with RESTful Access to Db2! OData! 100% of our audience learned something! The new IBM Data Server Gateway for OData is a standards-based way for customers to leverage Db2 in modern Cloud and Mobile application development environments without requiring database drivers on the client. This show described what OData is, how it works with...

(Read more)
 

December 06, 2017

Modern SQL

Modern SQL: IS DISTINCT FROM — A comparison operator that treats two NULL values as the same

NULL-Aware Comparison: is [not] distinct from

In SQL null is not equal (=) to anything—not even to another null. According to the three-valued logic of SQL, the result of null = null is not true but unknown. SQL has the is [not] null predicate to test if a particular value is null.

With is [not] distinct from SQL also provides a comparison operator that treats two null values as the same.

<expression> IS NOT DISTINCT FROM <expression>

Note that you have to use the negated form with not to arrive at similar logic to the equals (=) operator.

The following truth table highlights the differences between the equals sign (=) and is not distinct from.

ABA = BA IS NOT DISTINCT FROM B
00truetrue
01falsefalse
0nullunknownfalse
nullnullunknowntrue

The result with equals (=) is unknown if one operator is null. The is not distinct from comparison is true if both values are null or false if only one is null.

Conforming Alternatives


Note

Although there are standard alternatives to is not distinct from, using a proprietary alternative is often the better choice.


Due to SQL’s three-valued logic, a fully equivalent substitute for A is not distinct from B that works in all SQL databases is surprisingly complex:

CASE WHEN (a = b) or (a IS NULL AND b IS NULL)
     THEN 0
     ELSE 1
 END = 0

The result of the expression in the when clause is true if both arguments are equal or both are null. If only one argument is null the result is unknown, not false. This is often not a problem because SQL generally treats unknown like false when making binary decisions such as accepting or rejecting a row for a where clause.

To get the fully equivalent functionality of is not distinct from—i.e. either true or false but never unknown—the case expression reduces the three-valued result into a two a two-valued one. In some databases is not false can be used instead of the case expression. This technique is explained in Binary Decisions Based on Three-Valued Results.

Another option is to use set operators, which use distinct comparisons internally. The following snippet uses intersect to determine a common subset. The two compared sets are just one value each (one row with one column). If it is twice the same value the common subset will be that value. Otherwise the common subset is empty. This logic can be easily tested in the where clause with an exists predicate:0

EXISTS (VALUES (A)
        INTERSECT
        VALUES (B)
       )

This has the advantage that it does not repeat any expressions. Unfortunately, it doesn't work on all databases due to the use of the values clause. A select from a one-row dummy table can be used to get a conforming and widely supported solution.

Compatibility

The is [not] distinct from predicate was introduced in two steps: SQL:1999 added T151, “DISTINCT predicate”. The optional negation with not was added by SQL:2003 as feature T152, “DISTINCT predicate with negation”.

Availability of IS [NOT] DISTINCT FROM

Proprietary Alternatives

Most database that do not offer is not distinct from offer a proprietary alternative that is more convenient than the conforming alternative described above. The following proprietary features are fully compatible—i.e. they have a two-valued result and never return unknown.

Proprietary Alternatives for IS [NOT] DISTINCT FROM

Exists, select without from, intersect

The standard solution using exists, values, and intersect can easily be modified to work on more databases by using select without from instead of the values clause:

EXISTS (SELECT c1
        INTERSECT
        SELECT c2
       )

decode — Db2, Oracle, H2

Db2, Oracle database, and H2 have the proprietary function decode that happens to use is not distinct from semantics internally.1 The following example has the same effect as A is not distinct from B:

DECODE(A, B, 0, 1) = 0

is — SQLite, H2

The is operator of SQLite (documentation) and H2 (documentation) is able to compare two expressions (not just is [not] null), and it has the same semantics as is not distinct from.

<=> — MySQL, MariaDB

MySQL offers the proprietary <=> comparison operator that works like is not distinct from.2

ANSI_NULLS — SQL Server

SQL Server’s deprecated ANSI_NULLS setting makes some equals comparison (=) act as though it was a is not distinct from comparison.


Warning

ANSI_NULLS OFF is deprecated: it’s use may cause errors in future versions of SQL Server.

Also note that it does not affect all equals signs, but only those where one side of the comparison is a variable or the null literal. It does not affect general <expression> = <expression> comparisons.


NULL-Aware Comparison: is [not] distinct from” 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