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.

 

May 18, 2018


DB2Night Replays

The DB2Night Show #205: Deep Dive Db2 LUW Locks, Logs, and pureScale

Follow @mohankumarsp Follow @rcollins963 Special Guests: Mohan Saraswatipura and Kent Collins, Authors DEEP DIVE on Db2 LUW Locks, Logs, and pureScale 100% of our audience learned something! Mohan took us into the bowels of Db2 pureScale locking and logging, complete with sample SQL, monitoring commands, configuration considerations, important stuff you need to know for Db2 V11.1 Certification Testing, and much more! Get the PDF for...

(Read more)

Triton Consulting

Stay in the loop!

Unless you’ve been hibernating for the last six months you’ll be well aware of GDPR which comes into force on 25th May.  As data management specialists we’re working hard to make sure that we’re fully compliant with the new rules. … Continue reading →

(Read more)

Triton Consulting

Database Availability – A CIO View

Data sits at the heart of all organisations and without it everyday functions simply cannot be performed. Reliance on data availability is growing. It is vital to ensure that your organisations’ Database Availability and Recovery procedures are robust and able … Continue reading →

(Read more)
 

May 16, 2018


Kim May

IBM Extends Support Options to Include PostgreSQL

IBM’s strategy is difficult to understand at first, but often makes eventual sense.  There’s been a great deal of discussion recently about the future of Db2.  Db2 on the mainframe has an...

(Read more)

Triton Consulting

Managing the Mainframe in 2018

For many large organisations the Mainframe is the workhorse powering business critical IT systems and services. Managing that beast is an immense task and one that can seemingly take ever more time and budget. In this blog we take a … Continue reading →

(Read more)

Triton Consulting

Top 5 DB2 Support Nightmares 2018 – No.1

Single User Access The set-up   When you install DB2 you obviously have an all-powerful instance owner ID and password. The best option, once you’ve got DB2 up and running, is to ensure that no-one, except essential support staff, has … Continue reading →

(Read more)
 

May 15, 2018


DB2utor

Extended Plan Management and Migration

I’ve written often about plan management over the years. Most recently I wrote about a study of how customers are setting Db2 zparm.
 

May 14, 2018


Craig Mullins

Improving Performance by Caching Select Statement Results

A significant trend in the database world these days is moving more data management and processing into memory. If you can bypass disk I/O you can achieve tremendous performance gains. There are many reasons for this, but of course, the most important reason is that disk access is much slower than memory access. It is orders of magnitude more efficient to access data from computer memory than it...

(Read more)
 

May 11, 2018


Data and Technology

Monitoring MySQL and MariaDB Instances

Database performance monitoring and tuning is one of the biggest DBA responsibilities. The old maxim that if something can go wrong, it will, seems to be the general rule in the world of database...

(Read more)
 

May 08, 2018


DB2utor

Submitting an RFE

I was recently asked about submitting a request for enhancement (RFE) for Db2 for z/OS. The answer is simple: Just go to IBM developerWorks and conduct an appropriate search (e.g., "Db2 for z").
 

May 07, 2018


Kim May

IDUG 2018 Thoughts and Notes

Last week’s annual International Db2 Users Group (IDUG) Conference in Philadelphia was interesting and – as always – a great time to reconnect with friends and colleagues.  With IBM...

(Read more)

Data and Technology

Everybody Lies!

I was walking though the airport in Las Vegas after the IBM Think conference this year when I saw a book with the intriguing title of: Everybody Lies: Big Data, New Data, and What the Internet Can...

(Read more)
 

May 01, 2018


Triton Consulting

I already have a DBA. Why do I need RemoteDBA?

Skilled DBAs are in high demand. A DBA’s workload generally covers a huge array of tasks including; day to day database management and monitoring, database design, capacity planning, programming and development, database availability. The list goes on! With all this … Continue reading →

(Read more)

DB2utor

Db2 12 for z/OS Tools Compatibility

Before migrating from Db2 11 to Db2 12 for, you must verify that all IBM Db2 tools are supported. In this blog, I've listed the different product areas and supported version of each product.
 

April 30, 2018


Robert Catterall

Db2 for z/OS Buffer Pools - Time to Give AUTOSIZE(YES) a Try?

Db2 9 for z/OS became generally available back in 2007. In the years since, most of the features introduced with Db2 9 have been broadly put to use. An interesting exception is the auto-sizing of buffer pools, implemented via the AUTOSIZE(YES) option of the -ALTER BUFFERPOOL command. Based on my experience, it seems that few Db2 for z/OS sites have gone with buffer pool auto-sizing. I find that a bit perplexing. In the Db2 for Linux/UNIX/Windows user community, a feature called self-tuning...

(Read more)
 

April 27, 2018


Henrik Loeser

Db2: CTE and CONNECT BY - two kinds of recursion

Writing recursive SQL Recently, I gave a talk on SQL Recursion. One of the cool features in Db2 that I (re-)discovered for that presentation is that there are two different forms of syntax for...

(Read more)
 

April 25, 2018


Data and Technology

SQL Injection Still Causing Trouble

An on-going and important aspect of managing database security is designing your applications to avoid SQL injection attacks. SQL injection is a form of web hacking whereby SQL statements are...

(Read more)
Modern SQL

One Giant Leap For SQL: MySQL 8.0 Released

One Giant Leap For SQL: MySQL 8.0 Released

“Still using SQL-92?” is the opening question of my “Modern SQL” presentation. When I ask this question, an astonishingly large portion of the audience openly admits to using 25 years old technology. If I ask who is still using Windows 3.1, which was also released in 1992, only a few raise their hand…but they’re joking, of course.

Clearly this comparison is not entirely fair. It nevertheless demonstrates that the know-how surrounding newer SQL standards is pretty lacking. There were actually five updates since SQL-92—many developers have never heard of them. The latest version is SQL:2016.

As a consequence, many developers don’t know that SQL hasn’t been limited to the relational algebra or the relational model since 1999. SQL:1999 introduced operations that don't exist in relational algebra (with recursive, lateral) and types (arrays!) that break the traditional interpretation of the first normal form.0

Since then, so for 19 years, whether or not a SQL feature fits the relational idea isn’t important anymore. What is important is that a feature has well-defined semantics and solves a real problem. The academic approach has given way to a pragmatic one. Today, the SQL standard has a practical solution for almost every data processing problem. Some of them stay within the relational domain, while others do not.


Resolution

Don’t say relational database when referring to SQL databases. SQL is really more than just relational.


It’s really too bad that many developers still use SQL in the same way it was being used 25 years ago. I believe the main reasons are a lack of knowledge and interest1 among developers along with poor support for modern SQL in database products.

Let’s have a look at this argument in the context of MySQL. Considering its market share, I think that MySQL’s lack of modern SQL has contributed more than its fair share to this unfortunate situation. I once touched on that argument in my 2013 blog post “MySQL is as Bad for SQL as MongoDB is to NoSQL”. The key message was that “MongoDB is a popular, yet poor representative of its species—just like MySQL is”. Joe Celko has expressed his opinion about MySQL differently: “MySQL is not SQL, it merely borrows the keywords from SQL”.

You can see some examples of the questionable interpretation of SQL in the MySQL WAT talk on YouTube.2 Note that this video is from 2012 and uses MySQL 5.5 (the current GA version at that time). Since then, MySQL 5.6 and 5.7 came out, which improved the situation substantially. The default settings on a fresh installation are much better now.3

It is particularly nice that they were really thinking about how to mitigate the effects of changing defaults. When they enabled ONLY_FULL_GROUP_BY by default, for example, they went the extra mile to implement the most complete functional dependencies checking among the major SQL databases:

Availability of Functional Dependencies

About the same time MySQL 5.7 was released, I stopped bashing MySQL. Of course I'm kidding. I'm still bashing MySQL occasionally…but it has become a bit harder since then.

By the way, did you know MySQL still doesn’t support check constraints? Just as in previous versions, you can use check constraints in the create table statement but they are silently ignored. Yes—ignored without warning. Even MariaDB fixed that a year ago.

Availability of CHECK constraints

Uhm, I’m bashing again! Sorry—old habits die hard.

Nevertheless, the development philosophy of MySQL has visibly changed over the last few releases. What happened? You know the answer already: MySQL is under new management since Oracle bought it through Sun. I must admit: it might have been the best thing that happened to SQL in the past 10 years, and I really mean SQL—not MySQL.

The reason I think a single database release has a dramatic effect on the entire SQL ecosystem is simple: MySQL is the weakest link in the chain. If you strengthen that link, the entire chain becomes stronger. Let me elaborate.

MySQL is very popular. According to db-engines.com, it’s the second most popular SQL database overall. More importantly: it is, by a huge margin, the most popular free SQL database. This has a big effect on anyone who has to cope with more than one specific SQL database. These are often software vendors that make products like content management systems (CRMs), e-commerce software, or object-relational mappers (ORMs). Due to its immense popularity, such vendors often need to support MySQL. Only a few of them bite the bullet and truly support multiple database—Java Object Oriented Querying (jOOQ) really stands out in this regard. Many vendors just limit themselves to the commonly supported SQL dialect, i.e. MySQL.

Another important group affected by MySQL’s omnipresence are people learning SQL. They can reasonably assume that the most popular free SQL database is a good foundation for learning. What they don't know is that MySQL limits their SQL-foo to the weakest SQL dialect among those being widely used. Based loosely on Joe Celko’s statement: these people know the keywords, but don’t understand their real meaning. Worse still, they have not heard anything about modern SQL features.

Last week, that all changed when Oracle finally published a generally available (GA) release of MySQL 8.0. This is a landmark release as MySQL eventually evolved beyond SQL-92 and the purely relational dogma. Among a few other standard SQL features, MySQL now supports window functions (over) and common table expressions (with). Without a doubt, these are the two most important post-SQL-92 features.

The days are numbered in which software vendors claim they cannot use these features because MySQL doesn't support them. Window functions and CTEs are now in the documentation of the most popular free SQL database. Let me therefore boldly claim: MySQL 8.0 is one small step for a database, one giant leap for SQL.4

It gets even better and the future is bright! As a consequence of Oracle getting its hands on MySQL, some of the original MySQL team (among them the original creator) created the MySQL fork MariaDB. Apparently, their strategy is to add many new features to convince MySQL users to consider their competing product. Personally I think they sacrifice quality—very much like they did before with MySQL—but that’s another story. Here it is more relevant that MariaDB has been validating check constraints for a year now. That raises a question: how much longer can MySQL afford to ignore check constraints? Or to put it another way, how much longer can they endure my bashing ;)

Besides check constraints, MariaDB 10.2 also introduced window functions and common table expressions (CTEs). At that time, MySQL had a beta with CTEs but no window functions. MariaDB is moving faster.5

In 10.3, MariaDB is set to release “system versioned tables”. In a nutshell: once activated for a table, system versioning keeps old versions for updated and deleted rows. By default, queries return the current version as usual, but you can use a special syntax (as of) to get older versions. Your can read more about this in MariaDBs announcement.

System versioning was introduced into the SQL standard in 2011. As it looks now, MariaDB will be the first free SQL database supporting it. I hope this an incentive for other vendors—and also for users asking their vendors to support more modern SQL features!

Now that the adoption of modern SQL has finally gained some traction, there is only one problem left: the gory details. The features defined by the standard have many subfeatures, and due to their sheer number, it is common practice to support only some of them. That means it is not enough to say that a database supports window functions. Which window functions does it actually support? Which frame units (rows, range, groups)? The answers to these questions make all the difference between a marketing gag and a powerful feature.

In my mission to make modern SQL more accessible to developers, I’m testing these details so I can highlight the differences between products. The results of these tests are shown in matrices like the ones above. The rest of this article will thus briefly go through the new standard SQL features introduced with MySQL 8.0 and discuss some implementation differences. As you will see, MySQL 8.0 is pretty good in this regard. The notable exception is its JSON functionality.

Window Functions

There is SQL before window functions and SQL after window functions. Without exaggeration, window functions are a game changer. Once you understood window functions, you cannot imagine how you could ever have lived without them. The most common use cases, for example finding the best N rows per group, building running totals or moving averages, and grouping consecutive events, are just the tip of the iceberg. Window functions are one of the most important tools to avoid self-joins. That alone makes many queries less redundant and much faster. Window functions are so powerful that even newcomers like several Apache SQL implementations (Hive, Impala, Spark), NuoDB and Google BigQuery introduced them years ago. It’s really fair to say that MySQL is pretty late to this party.

The following matrix shows the support of the over clause for some major SQL databases. As you can see, MySQL’s implementation actually exceeds the capabilities of “the world’s most advanced open source relational database”, as PostgreSQL claims on its new homepage. However, PostgreSQL 11 is set to recapture the leader position in this area.

Availability of OVER

The actual set of window functions offered by MySQL 8.0 is also pretty close to the state of the art:

Availability of Window-Functions

Common Table Expressions (with [recursive])

The next major enhancement for MySQL 8.0 are common table expressions or the with [recursive] clause. Important use cases are traversing graphs with a single query, generating an arbitrary number of rows, converting CSV strings to rows (reversed listagg / group_concat) or just literate SQL.

Again, MySQL’s first implementation closes the gap.

Availability of WITH

Other Standard SQL Features

Besides window functions and the with clause, MySQL 8.0 also introduces some other standard SQL features. However compared to the previous two, these are by no means killer features.

Other new standard SQL features in MySQL 8.0

As you can see, Oracle pushes standard SQL JSON support. The Oracle database and MySQL are currently the leaders in this area (and both are from the same vendor!). The json_objectagg and json_arrayagg functions were even backported to MySQL 5.7.22. However, it’s also notable that MySQL doesn’t follow the standard syntax for these two functions. Modifiers defined in the standard (e.g. an order by clause) are generally not supported. Json_objectagg neither recognizes the keywords key and value nor accepts the colon (:) to separate attribute names and values. It looks like MySQL parses these as regular functions calls—as opposed to syntax described by the standard.

It’s also interesting to see that json_arrayagg handles null values incorrectly, very much like the Oracle database (they don’t default to absent on null6). Seeing the same issue in two supposedly unrelated products is always interesting. Adding the fact that both products come from the same vendor adds another twist.

The two last features in the list, grouping function (related to rollup) and column names in the from clause are solutions to pretty specific problems. Their MySQL 8.0 implementation is basically on par with that of other databases.

Furthermore, MySQL 8.0 also introduced standard SQL roles. The reason this is not listed in the matrix above is simple: the matrices are based on actual tests I run against all these databases. My homegrown testing framework does not yet support test cases that require multiple users—currently all test are run with a default user, so I cannot test access rights yet. However, the time for that will come—stay tuned.

Other Notable Enhancements

I'd like to close this article with MySQL 8.0 fixes and improvements that are not related to the SQL standard.

One of them is about using the desc modifier in index declarations:

CREATE INDEX … ON … (<column> [ASC|DESC], …)

Most—if not all—databases use the same logic in the index creation as for the order by clause, i.e. by default, the order of column values is ascending. Sometimes it is needed to sort some index columns in the opposite direction. That’s when you specify desc in an index. Here’s what the MySQL 5.7 documentation said about this:

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

“They are parsed but ignored”? To be more specific: they are parsed but ignored without warning very much like check constraints mentioned above.

However, this has been fixed with MySQL 8.0. Now there is a warning. Just kidding! Desc is honored now.

There are many other improvements in MySQL 8.0. Please refer to “What’s New in MySQL 8.0?” for a great overview. How about a small appetizer:

One Giant Leap For SQL: MySQL 8.0 Released” by Markus Winand was originally published at modern SQL.

 

April 24, 2018


Henrik Loeser

Automated, regular database jobs with IBM Cloud Functions (and Db2)

IBM Cloud Functions and Db2 Yesterday, I blogged about the latest tutorial I wrote. The tutorial discusses how to combine serverless and Cloud Foundry for data retrieval and analytics. That...

(Read more)

DB2utor

Additional Support for JSON

The JSON_VAL function provides an SQL interface to extract and retrieve JSON data into SQL data types from BSON objects. BSON is a standardized binary representation format for serializing JSON documents. It allows for fast traversal of JSON documents.
 

April 23, 2018


Kim May

Free Hands-On IDAA V7.1 Training June 19th and 20th

I am pre-announcing (if that’s a word) our upcoming free, hands-on IDAA training class scheduled for June 19th and 20th at the Radisson Cross Keys in Baltimore.  This class is being updated to...

(Read more)

Henrik Loeser

Use Db2 and IBM Cloud to analyze GitHub traffic data (tutorial)

Architecture: GitHub Traffic Analytics In a new solution tutorial, I show you how to automatically retrieve and store GitHub traffic data the serverless way with IBM Cloud Functions and Db2. The...

(Read more)
 

April 17, 2018


DB2utor

Previewing the Upcoming IDUG Conference

The annual IDUG North America conference is only two weeks away. This year's event is set for April 29 to May 3 in Philadelphia.
 

April 13, 2018


Triton Consulting

Is your industry ready to face a growing IT skills gap?

There have long been concerns in the IT industry over the potential impact of the growing skills gap in the IT sector. This is particularly relevant in the world of Mainframe where 43% of the workforce are in the 50-64 … Continue reading →

(Read more)

Henrik Loeser

IBM Cloud and Watson security questionnaires

If you are reading my blog regularly, you are aware of the entries on cloud security. Today, I want to point you to some newer reading material at the Cloud Security Alliance. They now have...

(Read more)
 

April 10, 2018


DB2utor

Continuous Delivery the Subject of New Redbook

IBM recently released a new Redbook covering the topic of continuous delivery. In this blog post, I'll provide details regarding the key points about this publication.
 

April 06, 2018


DB2Night Replays

The DB2Night Show #Z86: Pre-IDUG Db2 for z/OS Update

Presented by: Jeff Josten Distinguished Engineer, IBM SVL "The DB2Night Show Z86: Pre-IDUG Db2 for z/OS Update" Replays available in WMV and M4V formats! 100% of our studio audience learned something!Jeff gave a summary of some of the latest news and deliveries from Db2 for z/OS development. Watch the replay...

(Read more)
 

April 04, 2018


Kim May

Win a *FREE* Pass to IDUG North America!

All Baltimore/Washington Db2 Users Group Members are invited to participate in a drawing for a *free* IDUG pass – a $2195 value, thanks to IBM and the World of Db2. The annual International Db2...

(Read more)

Craig Mullins

Catch You in Philly for the IDUG Db2 Tech Conference 2018

Well, this year’s IDUG North American DB2 Tech Conference is almost upon us and I hope you have made plans to be there. If not, it is still not too late, though. The conference is being held in Philadelphia, PA this year -- The Cradle of Liberty -- from April 29 - May 3, 2018. I don’t know about you, but every year I mark my calendar ahead of time and then look forward to the week of IDUG...

(Read more)

Triton Consulting

Still running DB2 V10.1 or older? Don’t panic!

It is still possible to get access to specialist DB2 skills, even if you’re running an out of support version of DB2. Consultancy on Demand (CoD) from Triton Consulting is available on all versions of DB2 including those which are … Continue reading →

(Read more)
 

April 03, 2018


DB2utor

Upgrading Data Server Clients and Drivers for Db2 12

As you move to new function levels in Db2 for z/OS Version 12, you'll need to upgrade your remote Db2 data server clients and drivers. IBM's Db2 12 Knowledge Center provides instructions.
 

April 02, 2018


Data and Technology

Minimizing Database Outages with Replication

A primary goal of most organizations is to minimize downtime and improve the availability of their IT systems and applications. According to a recent study on achieving 2018 database goals by...

(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