DBA Corner

DBA Corner 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.

Referential integrity (RI) is a method for ensuring the "correctness" of data within a DBMS. People tend to oversimplify RI, stating that it is merely the identification of relationships between relational tables. It is actually much more than this. RI embodies the integrity and usability of a relationship by establishing rules that govern that relationship.

Posted August 09, 2017

As every DBA should know, DBMS data is typically persisted using disk storage. So the data is stored on disk and when it is later read or modified it has to be accessed and changed on disk. To optimize these processes, most DBMSes use a cache, or buffer pool, to stage data in memory when it is accessed. By moving the data to memory subsequent accesses to the same data can be more efficient because disk I/O can be bypassed.

Posted July 05, 2017

When a failure occurs, the DBA must ascertain whether recovery is required. It is possible, though not very likely for active databases, that a failure does not impact the integrity of your data. Assuming that recovery is required, the DBA will need to determine what resources (backup copies and log files) are available and how best to perform the needed database recovery.

Posted June 01, 2017

As DBAs, we can get mired in the depths of performance tuning parameters and scripts, sometimes getting lost in all the details. It is a good idea to always have a set of goals and philosophies that you can lean on to keep you focused and working on the appropriate things. That is what I want to talk about in this month's DBA Corner column. Some high-level rules of thumb for achieving your DBMS-related performance goals and maintaining your sanity.

Posted May 05, 2017

Every good DBA understands that backing up their database data is a non-optional part of assuring data availability and integrity. As a DBA, you need to know the difference between a full image copy backup and an incremental image copy backup and implement the proper image copy backup strategy based on application needs and database activity.

Posted April 07, 2017

The heart of any relational database management system is the system catalog that documents the database objects and system settings being used. The system catalog offers a wealth of information about your DBMS. You can think of it as the knowledge base of every piece of data known to the system. For this reason, it is important that DBAs understand what is in the system catalog, as well as how to access and manipulate the information it contains.

Posted March 02, 2017

The role of the DBA is growing and becoming more complicated in the age of digital transformation. As the amount and type of information we store expands, DBAs must become versed in administering not just one type of DBMS (e.g., relational), but multiple types (document, key/value, wide column stores, and graph) and even non-DBMS data platforms (e.g., Hadoop). Furthermore, cloud computing can change the manner in which existing applications and databases operate.

Posted February 08, 2017

The industry is changing, the way that DBAs work is changing, and database systems are changing. We all need to come to grips with the fact that the way we worked in the past is no longer the way we work with today's modern database environment.

Posted January 03, 2017

The clear trend these days is to automate and enable computerized tasks to streamline and optimize administrative and maintenance tasks. Many database management tasks that today require oversight and handholding by DBAs can, over time, be turned over to intelligently automated software to manage. But automation is just the first step.

Posted December 01, 2016

Every year, Database Trends and Applications magazine conducts its Readers' Choice Awards and this year the winner for best DBA solution was Navicat. As a DBA myself, this piqued my curiosity, so I took some time install and learn Navicat Premium. And I'd like to share my findings with you.

Posted November 02, 2016

Typically, most applications consist of both batch and online workloads. This is true even today, when most of our attention has turned to online and web-based interaction. Sure, online activities are the most obvious, in-your-face component of countless applications, but batch processing still drives many actions behind the scenes. This can include applying updates, processing reports, integrating input from multiple sources and locations, data extraction, database utility processing, and more.

Posted October 07, 2016

The current driving force for many IT projects is big data and analytics. So how will the job of DBA be impacted as their companies deploy big data analytics systems? The answer, is quite a bit, but don't forget everything you already know!

Posted September 02, 2016

Regulatory compliance is a critical aspect of the IT landscape these days, and the ability to audit database activities showing who did what to which data when is a specific requirement of many industry and governmental regulations. There are six primary methods that can be used to accomplish database auditing.

Posted August 04, 2016

Database administration is undergoing some significant changes these days. The DBA, traditionally, is the technician responsible for ensuring the ongoing operational functionality and efficiency of an organization's databases and the applications that access that data. But modern DBAs are relied upon to do far more than just stoke the fires to keep database systems performing

Posted July 12, 2016

Keeping your DBMS software up-to-date can be a significant job. The typical release cycle for DBMS software is every 18 to 36 months for major releases, with constant bug fixes and maintenance updates delivered in between those major releases.

Posted June 09, 2016

Being able to assess the effectiveness and performance of your database systems and applications is one of the most important things that a DBA must be able to do. This can include online transaction response time evaluation, sizing of the batch window and determining whether it is sufficient for the workload, end-to-end response time management of distributed workload, and more. But in order to accurately gauge the effectiveness of your current environment and setup, service level agreements, or SLAs, are needed.

Posted May 04, 2016

When users require access to multiple databases on multiple servers distributed across different physical locations, database security administration can become quite complicated. The commands must be repeated for each database, and there is no central repository for easily modifying and deleting user security settings on multiple databases simultaneously. At a high level, database security boils down to answering four questions.

Posted March 31, 2016

Every DBA should take advantage of the mechanisms provided by the DBMS to ensure data integrity. When DBMS-provided methods are used, fewer data integrity problems are likely to be found. Fewer data integrity problems mean higher quality databases and more proficient end users.

Posted March 03, 2016

One of the biggest challenges facing organizations today is making sure that the right information gets to the right people. It requires attention, diligence, and planning to ensure that data is used appropriately and accurately. Unfortunately, few organizations treat data as the corporate asset it truly is.

Posted February 10, 2016

If you are a working DBA, the actual work you do these days is probably significantly different than it was when you first began work as a DBA. So is the term DBA really accurate any longer? Or has the job grown into something more?

Posted January 07, 2016

As a DBA, establishing a reasonable backup schedule for your databases can be a challenging project. It requires you to balance two competing demands: the need to take image copy backups frequently enough to assure reasonable recovery time, and the need to not interrupt daily business. The DBA must be capable of balancing these two objectives based on usage criteria and the capabilities of the DBMS.

Posted December 02, 2015

There are many different ways to look at database administration. It can be done by task, by discipline, by DBMS, by server, and so on. But one useful way to look at database administration is in terms of the type of support being delivered to applications. You can paint a broad brush stroke across the duties of the DBA and divide them into two categories: those that support development work and those that support the production systems.

Posted November 09, 2015

Too little emphasis overall is placed on the integrity and recoverability of the data—and too much is placed on performance. Yes, performance is probably the most visible aspect of database systems, at least from the perspective of the end user. But the underlying assumption of the end user is always that they want to access accurate and, usually, up-to-date data. But what good does it do to quickly access the wrong data? Anybody can provide rapid access to the wrong data!

Posted October 07, 2015

Whenever I get into a discussion about database standards I invariably bring up one of my favorite quotes on the topic: "The best thing about standards is that there are so many to choose from." It shouldn't be true, but it is.

Posted September 09, 2015

As a data professional, you have heard the term "unstructured data." And you probably know what is meant by that term, as well. For those who do not, unstructured data is a general term used to define data that is not numbers, letters, and dates stored or viewed as rows and columns. But it is a horrible term. In fact, unstructured data is a lie. Let me tell you why.

Posted August 10, 2015

You'd be surprised at the variety of sages that have uttered pithy pieces of wisdom that prove useful in some way to DBAs. So with that in mind, let's review some of the could-have-been-DBAs through history by reviewing their own words!

Posted July 08, 2015

The landscape for database management systems is changing more rapidly these days than it has since the earliest days of the relational DBMS. Not only do we have an onslaught of NoSQL database systems of various different forms (column, document, key/value, and graph databases), but we also see a burgeoning market for in-memory database management, where the DBMS relies on main memory instead of disk for data storage, management, and manipulation. But there is another "category" of DBMS evolving that is known as "NewSQL."

Posted June 09, 2015

Every now and then, somebody will raise the age-old question "How can I measure the effectiveness and quality of my DBA staff?" This can be a difficult question to answer. And it almost always hides the actual question that is begging to be asked, which is "How many DBAs do we need?"

Posted May 14, 2015

When databases are built from a well-designed data model, the resulting structures provide increased value to the organization. The value derived from the data model exhibits itself in the form of minimized redundancy, maximized data integrity, increased stability, better data sharing, increased consistency, more timely access to data, and better usability.

Posted April 06, 2015

One of the trickiest aspects of relational database management can be dealing with missing information. The standard method of representing missing information is to set the "value" to null.

Posted March 12, 2015

When the data requirements of an organization change, the databases used to store the data must also change. Unfortunately, today's database systems do not make managing database change particularly easy.

Posted February 11, 2015

In 2014, the big data drumbeat continued to pound, major DBMS vendors expanded their product offerings, Microsoft hired a new CEO, and a range of new technology offerings were introduced. In retrospect, what stands out?

Posted January 07, 2015

Lock contention issues can be frustrating problems to investigate and debug. Before blaming the database system (as is the usual response), there are a few questions to consider that can help identify the cause of the locking problems.

Posted December 03, 2014

Designing an appropriate set of indexes can be one of the more troubling aspects of developing efficient relational database applications. Perhaps, the most important thing you can do to assure optimal application performance when accessing data in a relational/SQL database is to create the correct indexes for your tables based on the queries your applications use. Of course, this is easier said than done. With the caveat that appropriate index creation can be complicated, let's look at the top 10 steps to building the right indexes on your tables.

Posted November 12, 2014

Check constraints are supported in all of the major DBMS products, including DB2, Oracle, and SQL Server. But they are too-often ignored and not implemented in production databases. Properly defined, check constraints can provide enhanced data integrity without requiring procedural logic.

Posted October 08, 2014

Recounting the evolution of IBM's relational database technology DB2, Craig S. Mullins goes back a full 3 decades in his latest column, starting with the wild and woolly 1980s and ending with DB2 10 and 11 for z/OS as the currently supported versions of DB2, which have added important new functionality.

Posted September 10, 2014

Programs that read database data can access numerous rows and are therefore susceptible to concurrency problems. To get around this issue, most major RDBMS products support read-through locks, also known as "dirty read" or "uncommitted read," to help overcome concurrency problems. When might you want to consider using dirty reads in your applications?

Posted August 05, 2014

Lists help us to remember, impose order upon tasks, and provide guidance. Some lists have taken on great importance in the world and in our lives. The Bill of Rights is such a list. So is the Ten Commandments. But although we use lists to administer, program, and maintain our databases, there really is no overarching list of "things you should (or should not) do" to ensure an effective, accurate, well-designed database. So here is the list of Ten Database Commandments.

Posted July 03, 2014

Good data quality starts with metadata. Accurate data definitions are required in order to apply the controls for compliance to the correct data. Without proper metadata definitions, it is impossible to apply regulatory compliance to data.

Posted June 11, 2014

Recursive SQL can be very elegant and efficient. However, because of the difficulty developers can have understanding recursion, it is sometimes thought of as "too inefficient to use frequently." But, if you have a business need to walk or explode hierarchies in your database, recursive SQL will likely be your most efficient option.

Posted May 08, 2014

Driving a wide range of applications, from operational applications such as fraud detection to strategic analysis such as customer segmentation, advanced analytics goes deeper than traditional business intelligence activities into the "why" of the situation, and delivers likely outcomes.

Posted April 04, 2014

Database disaster recovery must be an integral component of your overall business recovery plan. Just because your organization has not yet experienced a disaster, or is not in a high-risk area, does not absolve you from the need for contingency planning. Here is what all DBAs should do to be prepared.

Posted March 12, 2014

Metadata is required to make data usable by applications, yet many data professionals do not understand or simply ignore metadata management. Just think about the metadata you must manage on your iPod—or whatever MP3 player you use—to be able to successfully select the music you want to hear.

Posted February 10, 2014

2013 saw its fair share of important data-related acquisitions. Key players like Oracle, IBM and SAP were all busy with data-related acquisitions. Last year also saw the announcement and release of a lot of great new DBMS technology, too. Let's look back over the most significant data and database-related events of the previous year.

Posted January 07, 2014

DB2 11 for z/OS brings with it a bevy of interesting and useful new features. They range the gamut from development to admin­istration to performance to integration with big data. Even if your company won't be migrating right away, now that DB2 11 is out in the field and available, the time has come for all DB2 users to take some time to learn what this latest and greatest version of DB2 can do.

Posted December 04, 2013

Although the concept of NoSQL is relatively new, some of the players have actually been around for a while. Yet, the concept of what NoSQL means continues to change. With all that in mind, let's take a look at the world of NoSQL.

Posted November 13, 2013

A successful DBA must understand application development and the issues involved in programming and design. Here are some things that every DBA must know about application development and the design projects of their organization.

Posted October 09, 2013

DBAs need to make many different types of changes to a database over its lifetime. Some will be simple and easy to implement, others much more difficult and complex. It is the DBA's job to understand the best way to implement any type of database change, but often, simple changes become more difficult in the real world. Database change management tools help to make this job easier and is one of the first tools acquired by many organizations when they implement a database of any size.

Posted September 11, 2013

Just about every company with a DBMS has that binder full of corporate and/or IT standards. That one over there in the corner with the cobwebs on it — the one that you only use when you need an excuse to avoid work. Okay, well, maybe it's not quite that bad. Your standards documents could be on the company intranet or some other online mechanism (but chances are there will be virtual cobwebs on your online standards manuals, too).

Posted August 07, 2013

Perhaps the most common question I am asked by readers is "How can I become a DBA?" The question is actually not as simple as it seems and there are many different aspects to the answer. Sometimes it is an application programmer who wants to become a DBA. Sometimes a DBA for one DBMS wants to support a different DBMS; for example, a SQL Server DBA wants to move to become an Oracle DBA. And other times someone with no IT background at all asks the question. The answer is different for all of these folks.

Posted July 09, 2013