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?

Ancelus: an Intriguing New Type of Database

There are many different DBMS types and products on the market these days. We have relational, key/value, document, wide column stores, graph, in-memory, multi-model, and even pre-relational DBMS types. It is rare to see anything truly new and interesting in the realm of DBMS offerings, but I think you will agree than Ancelus is indeed something new and interesting.

Billed as the nanosecond database, Ancelus has been able to achieve incredible performance benchmark results that are orders of magnitude faster than comparable relational database management system results. Better and faster performance is always at the top of the list of what organizations want from their database systems. And Ancelus can deliver. Benchmark results show constant read/write latency under 100 nanoseconds regartdless of the amount of data, up to 16 exabytes. Ancelus ran a billion-row search in 2.4 microseconds; and it adds only about 500 nanoseconds for each 10x increase in database size. Basically, queries that run for hours or days on typical database implementations can run in microseconds on Ancelus.

A database system capable of such speed is intriguing for many applications such as financial analysis, options trading, AI/ML, and many others.

Sometimes databases and applications sacrifice data integrity for speed, but not so with Ancelus. It is ACID-compliant while delivering extreme speed, complexity handling, and massive scale all within a single system.

Furthermore, Ancelus works efficiently for both transactions and analytics, supporting HTAP (Hybrid Transaction Analytical Processing) and translytical environments. Ancelus is not a SQL database system; data is typically accessed by a native API. However, Ancelus does offer TQL (Threaded Query Language), which converts Ancelus schema structures into SQL-readable formats for ease of integration.

What Makes Ancelus Work

The Ancelus database uses a patented algorithmic approach to data storage that replaces predefined storage structures with an algorithm that determines the physical storage location of data. This algorithmic approach decides where to put data based on efficiency of access to it. This means that physical data storage and retrieval are independent of any predefined data layout.

Ancelus couples this storage approach with in-memory data access techniques which are also based on patented memory management methods. By combining algorithmic data storage and in-memory data management, Ancelus has achieved constant performance even for very large databases and complex applications.

There is no duplication of data in an Ancelus database. Each data element is stored only once and data is linked using pointers to its data dictionary. A consequence of this design is that referential integrity is not optional—it is designed right into the database … and it does not impede performance.

With this architecture, users can implement multiple logical schemas over the data at the same time, to access the data as it is in relational tables (even though it is not). The actual physical schema is not exposed to users.

Ancelus can be run on Linux, UNIX, and Windows platforms.

Consider taking a look at Ancelus if you are in the market for a high-performance database system to power your complex applications. You may be able to reduce cost, speed up development, and improve operations using Ancelus. Indeed, some applications that may have been perceived as impractical or too costly may become achievable with Ancelus.

Happy SysAdmin Day… for DBAs, too!

Today, July 26, 2019 is SysAdmin Day, otherwise known as System Administrator Appreciation Day. Actually, it is the 20th annual SysAdmin Day!

What is SysAdmin Day, you may ask? Well, it is a day to celebrate, appreciate, and thank your IT system administration professionals. These are the folks that keep your IT infrastructure up and running. You know, the people who, if they are doing their job, you don’t even recognize that they are there. The only time they are usually recognized is when you have a problem and they are contacted and dispatched to fix it… but they work every day to minimize the number of problems you experience.

So the SysAdmin Day, take a minute or two to recognize the people at your shop who keep the servers, network, and databases (yes, DBAs are a form of system administrator) running. Buy them a donut.

Or take them a nice cup of coffee (you know, something better than that swill that is brewed in your office coffee maker).

Here’s to you, Mr. and Mrs. SysAdmin! Have a great day… and I hope you are not working late this weekend.

A (Very) Quick Intro to Cloud Computing

Cloud computing refers to storing and accessing data and programs over the Internet instead of on your own computers. The “cloud” is a metaphor for the Internet. When you hear the term “cloud,” you can translate that to “someone else’s computer.”

However, things are not quite as uncloudy as that in terms of grasping what cloud means. Sometimes, when folks refer to cloud they are talking about a type of development using APIs and microservices. And for private cloud (or local cloud) implementations there may be no Internet access involved. It can get confusing.

The Trends

The clear direction these days is that many components of the IT infrastructure are moving from on premises to the cloud. Enterprises are choosing which applications, and how much of the infrastructure supporting those applications, should be moved into the cloud.

There are several options that can be chosen. The Public Cloud refers to an infrastructure that is provisioned by a cloud provider for open use by the general public. The Private Cloud is where the infrastructure is typically provisioned solely for a single organization, whether managed internally or by a third-party and hosted internally or externally. Finally, a Hybrid Cloud solution is a combination of two or more clouds bound together, delivering the benefits of multiple deployment models.

Another type of hybrid development is where an organization combines components on premises and in the cloud to deliver services and applications. Many organizations are taking this approach as they try cloud computing options.

Facts and Figures

There is a pervasive belief, especially among industry analysts, that the cloud is going to take over everything and on premises computing will wither away. Gartner estimates that the cloud computing market will reach $411 billion by 2020 (and that is just next year)!

Organizations’ confidence in the cloud, including the ability to protect and secure data and applications is rising. And this increased confidence should correlate with growing cloud adoption. According to a 2017 study, cloud usage increased from 24 percent of workloads in 2015 to 44 percent at the time of the study. Furthermore, they predicted that by 2019 65% of workloads would be in the cloud.

My Take

Clearly, there are benefits to cloud computing including economies of scale and the ability to scale up and down as needed. But there are deteriments, too, including less control of your data and latency issues. What happens to all of those applications built on the cloud if your Internet service is disrupted?

Contrary to the current popular belief, on-premises computing is not going to disappear any time soon… think of all those mainframes still humming away out there. And according to various sources (compiled by Syncsort):

• Mainframes handle 68 percent of the world’s production IT workloads
• 71 Percent of Fortune 500 Companies Use Mainframes
• Mainframes handle 87 percent of all credit card transactions

So mainframes alone, which are not going away, will still handle a large amount of enterprise computing workload.

That said, I believe that public cloud adoption will most likely be much lower than most predictions through 2022, and probably even beyond that. Even if demand is high, Cloud Service Providers (CSPs) cannot possibly build out their infrastructure fast enough to support all the existing data center capacity “out there.”  Mark Thiele shared a concise, insightful article on LinkedIn that is worth reading summarizing these thoughts quite well.

The Bottom Line

At any rate, cloud computing is a viable method for building enterprise applications. It can be used to reduce the cost of managing and maintaining your IT systems. At the same time, the cloud can enhance flexibility, deliver quick scalability, and ensure that you are running with current, up-to-date system software.

A New and Improved Navicat Monitor

DBAs are always looking for ways to better manage the performance of their database systems and the applications that access those databases. Indeed, monitoring and tuning are perhaps the most frequent tasks that DBAs perform. So, it makes sense that DBAs want to use tools that ease this burden.

One tool that should be on the radar for DBAs looking to improve their performance management capabilities is the latest release of Navicat Monitor, version 2.0, which now includes support for Microsoft SQL Server. That means that Navicat Monitor can now support the monitoring of Microsoft SQL Server, MySQL, MariaDB, and Percona Server databases.

Navicat Monitor is an agentless remote server monitoring tool that runs on Windows, Mac or Linux and can be accessed from anywhere via a web browser. Navicat Monitor can be installed on any local computer or virtual machine. It also supports cloud services including AWS, Oracle Cloud, Alibaba Cloud, Google Cloud, Microsoft Azure and others. It does not require any software installation on the servers being monitored.

It is common for DBAs these days to manage multiple different types of DBMSes, and Navicat Monitor can help these DBAs with its intuitive dashboard interface. Navicat can display summary information for all your database instances on its main dashboard screen. And with the compact view you can see over a hundred instances on a single screen!

In Figure 1 we can see a screen shot of the dashboard showing how multiple servers can be monitored from a single pane. You can filter by DBMS type and search for specific instances, simplifying the way in which DBAs manage the performance of all the different databases under their purview.

Figure 1. Navicat Monitor Dashboard

Using the dashboard DBAs can view a one-page summary of the real-time health and performance of all the database instances they are responsible for. And the dashboard can be customized to enable DBAs to view the information they want in the manner they want to see it.

Microsoft SQL Server Support

But the big news for Navicat Monitor Version 2.0 is the addition of support for Microsoft SQL Server. All the performance management capabilities you have been using for MySQL and MariaDB are now available for SQL Server. This means you can measure database performance metrics such as CPU load, RAM usage, I/O bottlenecks, locking issues and more for each database type and instance you support.

A major component of performance monitoring and tuning for Microsoft SQL Server involves determining the cause of observed or reported SQL Server issues and implementing the requisite changes to improve performance. Changes may be required to any component of the technology stack supporting the applications including the SQL Server database, application code, operating system configuration, and hardware components. From a database perspective, tuning can require modifications to many different facets of your SQL Server environment, including Transact-SQL (whether in queries, stored procedures, or programs), optimized execution plans, indexing, database structures, your SQL Server configuration, operating system configuration, and the physical hardware you use including memory, disk and other data storage mechanisms, and really, any parameters or configuration options.

The challenge is to identify what is causing the performance issue and that is where Navicat Monitor shines. DBAs can use Navicat Monitor to gain visibility into instance resource utilization, performance, and operational health. Using Navicat Monitor you can get a complete overview of all your instances and how they are performing. You can interact with Navicat Monitor using its graphical visualization of performance metrics for a high-level view, and then drill down into a more detailed analysis.

Navicat Monitor uses a performance repository to capture historical performance metrics which you can use to evaluate performance trends and diagnose problems. DBAs can set up customized rules to alert when specific performance thresholds are reached, delivering notifications via email, SMS, SNMP, or Slack. And if you do not have time to customize your own rules Navicat Monitor comes preconfigured with more than 40 alert policies right out-of-the-box. Of course, these rules can be customized later to conform to the metrics and thresholds most important to your environment.

But probably the single most vexing issue for DBAs and developers is SQL performance. It is estimated that as much as 80% of relational database performance problems can be attributed to poorly performing SQL. And without the capabilities of Navicat Monitor it can be extremely challenging to identify, analyze and improve the performance of poorly performing SQL queries.

Navicat Monitor’s Query Analyzer feature delivers the ability to identify, analyze and optimize the performance of SQL queries. Using Query Analyzer to regularly track the performance of your top resource-consuming SQL statements can help you to constantly improve the overall performance of your applications by finding, and then tuning the worst performers first.

Figure 2. Navicat Monitor Query Analyzer

Refer to Figure 2. You can use Query Analyzer to implement a common best practice, identifying and tuning a top five list of problem queries. Query Analyzer gathers information about SQL queries and identifies the Top 5 Queries, which are the 5 most time-consuming query statements, along with additional details.

Take a look at the Query Table section of the Query Analyzer, shown toward the bottom of the screen shot in Figure 2. Here we see that Navicat has identified the Top 5 queries based on total execution time, along with additional details including the SQL text, a count of how many times the SQL was run, and cumulative execution time.

You can use Query Analyzer to dive down and acquire more information on all of the longest-running queries in your environment. Figure 3 shows the Long Running Queries screen. Here we can perform an in-depth analysis of the long running queries examining when they are run along with additional details including execution plan details, lock waits, I/O, and all the relevant database and system details to help you optimize your SQL.

Figure 3. Long Running Queries

 

Summary

If you already use Navicat Monitor, you’ll love the new version. If you have yet to use it, now is the time to take a look. And with the new Microsoft SQL Server support, Navicat Monitor 2.0 will be beneficial to even more organizations and DBA teams than ever before.

Digital Transformation and Database Scalability

Today’s business systems are undergoing a revolutionary transformation to work within the digital economy. This phenomenon is known as digital transformation. There are four over-arching trends that are driving digital transformation today summarized by the acronym SMAC: social, mobile, analytics and cloud.

Mobile computing, is transforming the way most people interact with applications. Just about everybody has a smartphone, a tablet, or both. And the devices are being used to keep people engaged throughout the day, no matter where they are located. This change means that customers are engaging and interacting with organizations more frequently, from more diverse locations than ever before, and at any time around-the-clock. End users are constantly checking their balances, searching for deals, monitoring their health, and more from mobile devices. And their expectation is that they can access their information at their convenience.

Cloud computing, which is the practice of using a network of remote servers hosted on the internet to store, manage, and process data and applications, rather than a local host, enables more types and sizes of organizations than ever before to be able to deploy and make use of computing resources—without having to own those resources. Applications and databases hosted in the cloud need to be resilient in order to adapt to changing workloads.

And the Big Data phenomenon has boosted the amount of data being created and stored.
The amount and types of data that can be accessed and analyzed continue to grow by leaps and bounds. And when analytics is performed on data from mobile, social, and cloud computing, it becomes more accessible and useful by anyone, anywhere, at any time.

All of these trends have caused organizations to scale their database implementations and environments to accommodate data and workload growth. But how can databases be scaled?

Well, at a high level, there are two types of database scalability: vertical scaling and horizontal scaling.

Vertical scaling , also known as scaling up, is the process of adding resources, such as
memory or more powerful CPUs to an existing server. Removing memory or changing to a less powerful CPU is known as scaling down.

Adding or replacing resources to a system typically results in performance gains, but to realize such gains can require reconfiguration and downtime. Furthermore, there are limitations to the amount of additional resources that can be applied to a single system, as well as to the software that uses the system.

Vertical scaling has been a standard method of scaling for traditional RDBMSs that are architected on a single-server type model. Nevertheless, every piece of hardware has limitations that, when met, cause further vertical scaling to be impossible. For example, if your system only supports 256 GB of memory when you need more memory you must migrate to a bigger box, which is a costly and risky procedure requiring database and application downtime.

Horizontal scaling , sometimes referred to as scaling out, is the process of adding more
hardware to a system. This typically means adding nodes (new servers) to an existing system. Doing the opposite, that is removing hardware, is known as scaling in.

With the cost of hardware declining, it can make sense to adopt horizontal scaling  using low-cost commodity systems for tasks that previously required larger computers, such as mainframes. Of course, horizontal scaling can be limited by the capability of software to exploit networked computer resources and other technology constraints. And keep in mind that traditional database servers cannot run on more than a few machines. Scaling is limited, in that you are scaling to several machines, not to 100x or more.

Horizontal and vertical scaling can be combined, with resources added to existing servers to scale vertically and additional servers added to scale horizontally when required. It is wise to consider the tradeoffs between horizontal and vertical scaling as you consider each approach.

Horizontal scaling results in more computers networked together and that will cause increased management complexity. It can also result in latency between nodes and complicate programming efforts if not properly managed by either the database system or the application. Vertical scaling can be less costly; it typically costs less to reconfigure existing hardware than to procure and configure new hardware. Of course, vertical scaling can lead to overprovisioning which can be quite costly. At any rate, virtualization perhaps can help to alleviate the costs of scaling.

 

Craig Mullins Presenting at IDUG Db2 Tech Conference in Charlotte, NC

Those of you who are Db2 users know that the 2019 IDUG North American Db2 Tech Conference is being held this year in Charlotte, NC the week of June 2-6, 2019. IDUG stands for the International Db2 User Group and it has been meeting every year since 1988.

If you will be there, be sure to attend my speaking presentations on Tuesday, June 4th. My conference session is bright and early Tuesday at 8:00 AM (Session E05) titled Coding Db2 for Performance: By the Book. This session is based on my latest book and it is aimed at application developers. The general idea is to give an overview of the things that you can do as you design and code your Db2 programs (for z/OS or LUW) with performance in mind. All too often performance is an afterthought – and that can be quite expensive. Nail down the basics by attending this session!

Also on Tuesday, but later in the morning, I will deliver a vendor-sponsored presentation for Infotel, at 10:40 AM. This presentation, titled Improving Db2 Application Quality for Optimizing Performance and Controlling Costs, will be delivered in two parts. I will be presenting the first half of the VSP discussing the impact of DevOps on Db2 database management and development. The second half will be delivered by Carlos Almeida of Infotel, who will talk about how their SQL quality assurance solutions can aid the DevOps process for Db2 development.

I hope to see you there!