Inside the Data Reading Room – Some Books to Start the New Year (2020)

Regular readers of my blog know that I periodically post quick reviews of the technology books that I have been reading. With that in mind, here is the first set of book reviews for 2020, taking a look at three recent books on topics that are in the news: AI, blockchain, and Big Data.

First up, we have The AI Delusion by Gary Smith. This is an interesting book on AI that is easy to read quickly. It is not going to give you any in-depth AI algorithms or models, but will help you to cut through the hype. Because right now, let’s face it, everything and everybody is touting how AI will change the world. And it probably will, but not with computers that think and behave like humans. This book helps you to understand why this is so – that computers lack “understanding.”

If you are at all interested in AI and its potential impact on humanity, you will enjoy reading Smith’s The AI Delusion .

 

Blockchain is another hyped, but useful technology, and Paul Tatro’s Blockchain Unchained: An Illustrated Guide to Understanding Blockchain can help you understand the ins and outs of what blockchain is and how it works. Indeed, if you are looking for a great, readable introduction to blockchain technology then look no further. Tatro’s Blockchain Unchained is in-depth, while at the same time easy to comprehend and digest. This is important for a topic that is potentially as confusing as the blockchain.

In 146 pages and just over a hundred figures, Tatro manages to instruct even beginners on the technology and capabilities of blockchain. Consider reading Blockchain Unchained to give yourself a firm blockchain foundation so you can understand its capabilities.

 

And finally, we have Building Big Data Applications by world-reknowned expert on all-things-data, Krish Krishnan. Although there are many books on the market that tackle the subject area of big data, Krishnan’s new tome on the topic, Building Big Data Applications, provides readers with a succinct overview of big data infrastructure, development, and technologies for designing and creating applications.

Building Big Data Applications offers up guidance for beginners, but one of the nice features of the book is the well-curated lists of resources for additional reading and discovery at the end of each chapter. The book also offers up nice examples of big data use cases that can be helpful to developers with similar requirements.

 

Have any new books that you would like to have reviewed here? Drop me a note to let me know about them…

The DBA Corner Column

Today’s post is to remind my readers that I write a monthly column for Database Trends & Applications magazine on database and DBA issues and trends by the name of DBA Corner. And I have been writing this column for over 20 years now!  A complete history of the column can always be found on my web site.

The DBA Corner column focuses on issues of interest to data architects, database analysts and database administrators. Issues addressed by the column include data modeling and database design, database implementation, DBA practices and procedures, performance management and tuning, application development, optimization techniques, data governance, regulatory compliance with regard to data, and industry trends.

The first DBA Corner column of 2020 was published last week on the topic of SQL Performance Testing and DevOps.

In 2019, I wrote about the follow topics in the DBA Corner column:

• The Rise of the Data Catalog. December 2019.
• The Five Key Factors for Database Performance. November 2019.
• Best Practices for Managing Database Infrastructure. October 2019.
• Analytics Projects Offer Value and Challenges. September 2019.
• Data Breaches: Pervasive and Costly. August 2019.
• Db2 – The AI Database … and more! July 2019.
• Implementing DBA Best Practices. June 2019.
• The Challenge of Managing the Modern IT Infrastructure. May 2019.
• Three Important Database Security Features. April 2019.
• Database Scalability, Elasticity, and Availability. March 2019.
• What is a DBA? February 2019.
• Teradata Vantage: The Platform for Pervasive Data Intelligence. January 2019.

So be sure to check the DBTA.com web site every month for each new edition of DBA Corner!

 

And drop me a note here as a comment if you have any topics you would like to see me cover in future DBA Corner columns.

Happy New Year 2020!

Hello everybody… and welcome to a new year. Just a quick post today to wish everybody out there a very Happy New Year!

 

Whether you rang in the year with drinks, dinner, a party, or watching the year roll in on television, I wish for you all the happiness and success that a new beginning (like the start of a new year) can bring.

And be sure to keep on coming back to this blog, Data and Technology Today, for news, advices, and my ruminations on all things data.

Happy New Year

Welcome to 2020 everybody! Just a quick post to wish you all a very happy and prosperous new year.

I hope that your holiday season was enjoyable and relaxing and that you are ready to get back to work soon so that we can all make 2020 a great year for database systems and data management!

Cheers!

Happy Holidays

Just a quick post to wish all of my readers and followers a very happy holiday season. To you and yours, wherever you are and however you celebrate, may you have the best of times enjoying the holidays!

And I’ll see you all again after the start of the New Year.

Cheers!

Navicat 15 Adds New Capabilities

Navicat Premium is a database development tool that allows you to simultaneously connect to MySQL, MariaDB, Microsoft SQL Server, MongoDB, Oracle, PostgreSQL, and SQLite databases from a single application. It is compatible with cloud databases like Alibaba Cloud AsparaDB, Amazon RDS, Amazon Aurora, Amazon Redshift, Microsoft Azure, Oracle Cloud, Google Cloud. Huawei Cloud, MongoDB Atlas, and TencentDB. Using Navicat you can quickly and easily build, manage and maintain your databases.

Of course, if you only use one of those database systems, you can purchase a Navicat license for just that DBMS (for example MySQL or Oracle). I have discussed Navicat in the blog before (see here) but today I’d like to talk about the new features added to the latest and greatest release of Navicat, version 15.

The first important new feature is support for native Linux for the user interface. Navicat already is supported on Windows and MacOS, but the new Linux support makes it easier for Linux users to deploy Navicat on the operating system they prefer. Navicat 15 supports the following Linux variants: Ubuntu 12.04, Ubuntu 14.04, Ubuntu 16.04, Ubuntu 18.04, CentOS 6.7, CentOS 7, Fedora 22, Fedora 23, Fedora 26, Linux Mint 13, Linux Mint 17.3, openSUSE 13.2, openSUSE 42.1, and Debian 9.

Charting

A significant new feature of Navicat 15 is the ability to turn your database data into visuals to have clear insights through charts and graphs. Let’s walk through a short example.

I created a table with my record and CD collection in a MySQL database. Here is a screen shot of Navicat displaying the first rows of the table.

 

Now suppose I want to create a pie chart of the various formats of recorded music in my collection (CD, records, etc.). I would choose the Charts option from the Navicat menu bar as shown here:

I then select the data source (my record collection table), refresh the data, and choose a chart type from the selection bar. I chose a pie chart, as shown here:

The resulting pie chart shows what I suspected, that I have more CDs than I have vinyl records!  Here is the pie chart created by Navicat.

It really is that simple. And this is just one of the many types of charts that you can create from your data using Navicat 15. In addition to pie charts, you can create bar charts, line charts, area charts, scatter charts, heat maps, and tree maps, all with a few simple clicks.

Data Transfer

Moving data is always a tricky and time-consuming endeavor. DBAs and developers are always looking for easier ways to move data (or subsets of data) from one place to another. Well, Navicat 15 also offers a new approach for transferring data.

This new design simplifies moving and transferring data using Navicat with an intuitive user interface. You can customize the fields and specify the number of rows you wish to transfer.

An example of the user interface is shown here:

SQL Builder

Another significant new capability in Navicat 15 is the SQL Builder, which can be used to build queries visually. This feature is particularly helpful for new coders who are not overly familiar with SQL syntax, but it can also be beneficial to old pros who just want to click and build SQL instead of hammering out the code themselves.

Choose the New Query option on the toolbar, and then click the Query Builder to open it. You will be presented with a screen showing your database objects on the left, and on the right you will see two sections: a diagram design and the SQL syntax below it, as shown below:

The SQL Builder only support SELECT statements, that is, it is a SQL query builder. Of course, Navicat supports building any SQL statement in a non-visual fashion using its SQL Editor.

Once you have the SQL Builder up all you have to do to get started is to drag a table or view from the left pane to the Diagram Design pane or double-click it to add it to query. Here we see the same panel as above after I dragged my albums table to the top pane:

Next, we need to decide on the columns to include in the query. This is accomplished by checking the column names in the Diagram Design pane. Here we see the results of me having clicked on three columns:

You can further supply additional components of the SQL by pointing and clicking, such as Group By, Having, and Order By clauses. When complete, you simply click Build to create the SQL, or Build and Run to create and execute the statement.

Here we see the results of running the simple SQL statement I built:

Additional New Features

Navicat 15 comes with more in-depth modeling features. You can use it to deliver a full picture of differences between a model and the database. And you can generate scripts to update the destination database with any differences you discover.

Navicat 15 also supports dark mode. You can set Navicat to display a data theme as opposed to the typical white background. This setting can be quite useful for those with eyesight issues, or for anyone that prefers not to use a white theme. An example screen shot in dark mode is shown below:

Summary

The latest version of Navicat offers up some nice features that make it easier to manage, use, and administer your heterogeneous database environment. Consider taking a look at it to make simplify your database development and administration tasks. You can download a free trial here.

Does Your DBMS Support Temporal Data?

Data changes over time, and many applications and users need to access data at different points in time. A traditional DBMS stores data that is implied to be valid now — at the current point-in-time. It doesn’t track the past or future states of the data and the only information you can typically glean from the database is its present state. And for many users and applications the current, up-to-date values for the data are sufficient. But for others, accessing earlier versions of the data is needed. This requirement is known as temporal support. With temportal support it becomes possible to store different database states and to query the data “as of” those different states.

Temporal data stored in the DBMS differs from traditional data in that a time period is attached to the data to indicate when it was valid or changed in the database. Let’s quickly take a look at an example situation where temporal database support might be useful.

Business Time

Consider an insurance company that sells policies to its customers. The terms of any specific policy are valid over a period of time – a “business time.” Over time, the customer can choose to decline further coverage, continue with the existing coverage, or modify the terms of their coverage. So at any specific point in time, the terms of the customer’s policy can differ. Over time, customers will make claims against their policies. This claim information needs to be stored, managed, and analyzed. Accident histories for customers are also important pieces of data with a temporal element.

Now consider the complexity inherent in trying to develop not only a database design that accommodates changing policies, claims, and historical details, but also allows queries such that a user might be able to access a customer’s coverage at a given point in time. In other words, what policies were in effect for that customer as of, say September 11, 2001?

Of course, there are many other types of applications for which temporal support would be useful, it is not just limited to insurance. For example, financial applications, credit history, personnel management, transportation applications, reservation systems, and medical information management all may benefit from maintaining temporal data.

This concept of business time can become quite complex. Consider the situation where a customer has multiple policies that expire on different schedules. Add in the possibility for periods where coverage lapses. And then the database grows in size and the queries become more complex.

With business time temporal support, tables are setup with a period consisting of a beginning time and an end time. The data is valid during that period. There are more details, of course, that can differ from DBMS to DBMS, but the basic gist is that all data in a business time temproal table will have a period setup for which the data is valid. This then, enables queries to be written like this:

select coverage_details
from policy for business_time as of '2011-09-11'
where custno = '000010';

You should be able to see how this is much simpler than trying to implement tables and queries without temporal support.

System Time

There is another aspect of temporal data that needs to be considered, as well. Instead of business time, you also may need to track and manage “system time.” For example, a regulatory mandate may require you to be able to track changes to a piece of personally identifiable information (or PII). Examples of PII include name, phone number, social security number, and driver’s license number (there are many others). Tracking changes to PII is a requirement of many industry and governmental regulations.

A DBMS with system time temporal support can be setup to store every change made to the data. Typically, this is done via a related history table. System time tracking is sometime referred to as data versioning, because every version of the data’s state is stored and can be queried. Support for managing system changes enables users to be able to query the database as of a point in time returning the value of the data as of that timeframe.

The business time indicates the period during which the data is accurate with respect to the world. The system time indicates the period during which the data is stored in the database. These two time periods do not have to be the same for a single piece of data. Suppose, for example, that we wish to store temporal information about 20^th century events. The valid business time for this data would be within the range of 1900 and 1999. But if we were to add the information to the database now, perhaps the valid system time for the data would be at some point in 2012. So, of course, you may need to be able to support both business temporal data and system temporal data in the same table. This is called bi-temporal support.

DBMS Support

Many relational DBMS offerings have added temporal support capabilities in recent years. Examples of database systems that have temporal support include IBM Db2, Microsoft SQL Server, Oracle, and Teradata.

The Bottom Line

This overview of temporal support has been short. There are additional capabilities and nuances that need to be understood and digested before you implement temportal databases. For example, deleting data from a temporal table can cause it to have more rows after the delete than before.

So you should learn the temporal capabilities and syntax of the DBMS you are using before you implement temporal tables. And do not go overboard! Define time periods only for those tables with a business or regulatory requirement, and make sure you define your tables appropriately with business and system time periods.

But the bottom line is that applications requiring temporal support can greatly benefit from the efficiency of built-in temporal facilities of your DBMS.

 

 

Of course, you should implement temporal tables with care. Define time periods only for those table with a business or regulatory requirement, and make sure you define your tables appropriately with business and system time periods.

But the bottom line is that applications requiring temporal support can greatly benefit from the efficiency of DB2’s built-in facilities for temporal data.

Mainframe Modernization: The Why and How

Upcoming webinar: Mainframe Modernization: The Why and How
Tuesday, October 29, 2019
12:00 PM – 1:00 PM CDT

The mainframe has been around for more than 50 years now, but it is as vital and important as ever. And there have been billions of lines of code written to support business applications on the mainframe. Organizations rely on mainframe systems and the decades of business knowledge built into the applications that drive their businesses.

But an application created 20 or more years ago will not be as accessible to modern users as it should be. Digital transformation that enables users to access applications and data quickly is the norm, but this requires modernizing access to the rich data and processes on the mainframe.

Join me as I deliver a webinar sponsored by GT Software on mainframe modernization issues and trends. I will discuss the proposition of the mainframe, and trends driving its usage and capabilities. Additionally, we’ll take a look at IT infrastructure challenges including changing technology, cloud adoption, legacy applications, and development trends. And also look at the tactics that can be used to achieve mainframe modernization amid complexity and change.

Don’t miss it… register now!

 

New Features in Navicat Data Modeler 3.0

Before implementing a database of any kind, a sound model of the data needs to be developed. This process, known as data modeling, requires an analysis of the things of interest to your organization and how those things are related to each other. The end product of data modeling is, appropriately enough, a data model. It is typically expressed visually using figures that represent entities and lines that represent relationships, with varying levels of underlying detail and documentation. The data model is used as a guide in the design and implementation of a database.

A proper data model cannot be thrown together quickly by novices. What is required is a practiced and formal approach to gathering data requirements and modeling the data, that is, the discovery and identification of entities and data elements. Furthermore, a robust data modeling tool that can be used to capture and convey the information acquired by data modeling is required. A good data modeling tool provides the user with an easy-to-use palette for creating data models, as well as a rich data and metadata storage capability.

And that brings us to Navicat Data Modeler, which just released a new Version 3.0 edition of its product. I wrote about the core capabilities of Navicat Data Modeler here in the blog before, but today I want to discuss the new features in the latest version.

There are three significant new capabilities offered by Navicat Modeler 3.0:

1. Database structure synchronization
2. Dark mode UI option
3. Native Linux support

 

Database Structure Synchronization

The most significant of these three features is database structure synchronization. This feature can be used to compare a model with an existing database or schema, discover the differences between their structures, and synchronize the structures in model to the target schema.

For example, suppose you make changes to a data model and decide that those changes should be applied to a particular database schema. With database structure synchronization you can discover and capture the changes made in the model and apply them to a targeted schema. This can save a tremendous amount of time and reduce human effort, especially when data models and databases are copied or duplicated across an organization.

Let’s walk through a quick example. The first step is to define the source and the target to be compared. Let’s say we are comparing a modified data model to an actual MySQL database schema. The next step is to choose the options to be used during the synchronization. You can pick and choose which structures and features to compare and what to ignore during the process. Navicat Data Modeler 3.0 provides more than 20 different options, such as compare tables, indexes, triggers, views, referential constraints, and so on.

After choosing the options and running the comparison, the differences between the two chosen schemas are displayed, such as shown in Figure 1.

Figure 1. Database Structure Comparison

You can then select a database object and view a comparison of the DDL as well as a deployment script, as shown in Figure 2.

The DDL tab shows the actual DDL statements to create that object in the source and the target, and the Deployment Script tab shows the detailed SQL statements to actually deploy the changes in the target databases.

Figure 2. Database Synchronization: DDL and Deployment Scripts

You can then view the scripts and choose to actually deploy the changes or go back, make more changes, and run additional synchronizations until things look like you want, and then deploy the changes.

Dark Mode

The next significant new feature of Navicat Data Modeler 3.0 is support for dark mode. This capability deploys a data theme as opposed to the typical white background and is ideal for those with eyesight issues that make a white theme glaring and difficult to read. An example of a data model in dark mode is shown in Figure 3.

Figure 3. Dark Mode

Dark mode can also be useful for working in a dark environment because it produces less light/glare, or just for those who prefer the dark theme over a light one.

Linux Support

The third of the major new capabilities of Navicat Data Modeler 3.0 is Linux support. Simply stated, the product can now run in native Linux with a UI that matches the Linux environment.

And Additionally…

Additionally, Data Modeler 3.0 uses an all new engine under the covers. This new mechanism for data modeling is designed to improve the user experience.

Finally, Data Modeler 3.0 works in conjunction with the Navicat Cloud, giving you a central space for your team to collaborate on connection settings, queries and models. Using the Navicat Cloud for your data modeling efforts makes it easier for your team to share crucial metadata and database structure design and implementation details.

The new features and capabilities of Navicat Data Modeler 3.0 can be used to improve your data modeling experience. Take a look at Navicat Data Modeler 3.0 by downloading it here!

Implementing a Database Infrastructure

I recently received a question about how to put together a database environment from scratch. This is a quick question that requires an in-depth answer — usually moreso than can adequately be explained over email or texting. Furthremore, to do a thorough job of it would require a lot of planning and analysis of the organization in question.

Nevertheless, I always try to provide some good advice whenever somebody seeks me out to ask a question. So here is the answer I offered up:

That is indeed an interesting question that is not always easy to answer. Here is how I would go about determining the database architecture for a an organization.

The first thing to do is to determine the primary relational DBMS that will serve as the backbone of the organization’s transactional systems. This could be MySQL, of course, (note: the query talked about experience with MySQL) or you may need to go with a DBMS that has a stronger track record of enterprise support (such as IBM Db2, Microsoft SQL Server, or Oracle Database). Factors to examine include the O/S supported by the DBMS and your organization’s required O/S support, performance and availability requirements, scalability needs, available technicians, support for the cloud, total cost of ownership, and analyst recommendations.

Keep in mind that an organization may have multiple relational/SQL database systems due to packaged application support and historical needs. The lerading relational/SQL DBMSes will support transaction processing for most applications. The next step is to decide on an analytical DBMS for the backbone of analytical, data warehousing and data science type processing. Again, we should choose a relational/SQL DBMS, but it should have the capability to perform OLAP and column-based queries. If the DBMS supports HTAP (Hybrid Transaction Analytical Processing) then perhaps the same DBMS can be used for both transactions and analytics, but this often requires additional configuration and perhaps an appliance (ex. IBM Db2 Analytics Accelerator).

Then you need to review the use cases for all upcoming projects. If the project can be accomplished using the DBMSes selected already, this is ideal. However, life is not always ideal and sometimes this requires adding a new DBMS, and even a new type of DBMS. For example, consider the NoSQL DBMS variants (graph, wide column, document, and key/value databases). Each of these support specific use cases and might be required for web, social media, IoT, or other types of projects that are not easily supported by relational/SQL database systems.

And do not forget legacy DBMS systems required to run applications that have been around for awhile. This may mean that you will have to also support database systems like IMS (hierarchical), IDMS (network/CODASYL), Adabas (inverted list), or OO database systems.

Once you have your infrastructure set, which will likely consist of multiple DBMS products), you need to plan for the update cycle for each DBMS product. This means coordinating with the published release cycle for each vendor. You must always ensure that you are running a supported version of the DBMS. Vendors typically release new versions every 18 to 36t months, but support a product for 5 or more years running from GA (general availability), thru EOM (end of marketing – meaning no longer sold), and EOS (end of support – meaning no longer supported). You must plan to move to a new release of each DBMS before the EOS deadline.

Further complicating issues is that many vendors have moved to an agile, continuous availability model. This means that smaller, more frequent updates are being made available. You must decide where your organization fits on the Gartner technology adoption framework (https://www.gartner.com/it-glossary/type-a-b-and-c-enterprises) and create a strategy for how frequently you accept new modifications.

The cloud throws another monkey-wrench in to this because if you use a cloud database like SQL Azure, for example, then the cloud provider (in this case Microsoft) will always be running the most recent version of the DBMS. So you will have to adopt a more aggressive strategy for accepting change into your DBMS environment.

What do you think? Is this a good high-level approach to assembling a database infrastructure for an organization?