Do you need a DBA for DB2 for i? Maybe more than you realize.
As I travel around the world engaging clients, one question that I am always asked is "Do we need a DBA"? Sometimes, the question takes the form of apparent confusion, such as, "We have an SQL Server DBA. We have an Oracle DBA. Why don't we have a DB2 for i DBA"? In other situations, I hear clients, business partners, and IBMers proudly state, "You don't need a DBA"!
With this article, I hope to illuminate the many facets of the DBA question and, more importantly, make the case for why you should seriously consider adding or retaining someone who focuses on DB2 for i.
From the Beginning
First, let's start with some history. Once upon a time, in a small, southeastern Minnesota town, a team of very clever IBM software engineers were cooking up a new operating system to run a revolutionary computer system. At the time, the notion of a "database management system" was innovative and novel. But the concept of storing, managing, and manipulating "rows and columns" was starting to catch on in the industry. A new Structured Query Language (SQL) was just being invented. More importantly, these hardy IBM engineers had the forethought to embed a full-fledged database management system right into the very fabric of their new OS. Once this DBMS was in place, even the operating system could take advantage of it. Furthermore, the IBM hardware engineers could optimize their part of the new system's computing, memory, and storage components to provide support for the DBMS. Of course, all of this "integration" from the Minnesota pioneers conspired to make the entire system easy to install, easy to use, and easy to maintain. As a matter of fact, the database management system didn't even have a name for the first half of its existence. And, unlike other computers, there was certainly no need for a systems administrator much less a database administrator. For the next three decades, the life of a business solution programmer was to be easier, thanks to the little prairie town of Rochester, Minnesota.
As database management systems became more and more common--a must-have component--IT companies started to seriously peddle their information management software. IBM's Software Group developed, marketed, and sold "DB2". Because IBM has different computer systems, with different architectures, different histories, and different users, there are various flavors of what we call DB2. When I talk about DB2, I describe three different DB2 family members:
- DB2 for z/OS
- DB2 for i
- DB2 for Linux, UNIX, Windows (LUW)
Each DB2 family member represents its own code base with its own features, functions, and benefits. This is in part based on the underlying operating system and the hardware where each runs. Please keep in mind that the family members do share many of the same features and functions, even if they are implemented differently. And of course, they all handle standard SQL.
I must also mention that for a few years, every DB2 family member was considered a universal database and made use of the nomenclature "Universal Database" (or UDB), including DB2 for i. Since DB2 for LUW was the first to use and market the UDB label, most people in the industry still refer to DB2 for LUW as "UDB." The UDB label has subsequently been dropped from the official IBM naming scheme. Other than DB2 for i, these database management systems represent separate middleware that must be purchased, installed, configured, and managed. In other words, they are not fully integrated into their respective operating systems. This is also true of the other major DBMS offerings, such as Informix, MySQL, Oracle, SQL Server, and Sybase.
Fast-forward 30+ years to today. After huge worldwide success and a few name changes along the way, programmers and users are still benefiting from the integrated database system known as DB2 for i. Even though DB2 for i has been systematically enhanced, and in some cases re-engineered with each release, the foundation and architectural tenets remain in place. In other words, DB2 for i is still integrated into the operating system currently known as IBM i, and it continues to be a native file system with the ability to store and retrieve rows and columns naturally. Let me be clear: if you are using an AS/400, iSeries, System i or running OS/400, i5/OS or IBM i, you are using DB2 for i. You have at your fingertips a robust DB2 database management system!
So Who Needs a DBA?
If DB2 for i is so wonderful--practically running itself--then why do you need a database administrator? I say, you don't. You don't need an administrator.
You see, the other information management systems require administration. They require someone to install them, configure them, manage them, etc. The integrated nature of DB2 for i eliminates much, if not all, of the administration tasks. Yet, just like the other offerings, DB2 for i is a true relational database management system supporting SQL through open interfaces. It also embodies a state-of-the-art query optimizer and database engine to support sophisticated data-centric applications. As such, there is a real need for someone in your organization to understand the science and art of getting the most out those applications.
To illustrate this point, let's take the simple exercise of defining and creating a database table. When using any DBMS, you need someone with knowledge of basic data modeling to lay out the column definitions; knowledge of data-centric programming to define the table, row, and column attributes; and knowledge of SQL data definition language (DDL) to properly code and execute the CREATE TABLE statement. In the case of DB2 for i, that's basically it: CREATE TABLE. The operating system and database system handle the actual creation of the database object. This includes the data space or table space, any supporting data structures, and physical storage. With the other database systems--such as Oracle, SQL Server, DB2 for z/OS, and DB2 for LUW--a DBA must get involved to define and create table spaces, partitioning schemes, parallel access attributes, and physical storage of data. As the table grows in size, the DBA must also get involved to extend and reorganize the table space(s). In other words, the DBA must do a lot of administration to get a table defined, created, and extended.
Once that table is created and populated, users will want access to the data. The standard and strategic way to access and process data is with SQL data manipulation language (DML). Whether the SQL request is static or dynamic and is communicated to the DBMS via DRDA, ODBC, JDBC, or CLI, it is still SQL. At this point, you might be thinking, "Hey, we don't use much SQL. We use the traditional COBOL and RPG high-level language data access methods such as READ, READE, CHAIN, START, SETLL, and WRITE to access physical and logical files". Well, if it ain't broke, don't fix it. Remember our history lesson. The roots of DB2 for i go back before SQL was available. Thus, we have another set of methods to create, open, and access database information directly from high-level languages. On the other hand, SQL is the strategic language of database, and it is where all of the latest and greatest enhancements have been implemented. Besides, any current business solution you acquire will be using SQL.
Some of the DB2 for i enhancements are autonomic, and you receive the benefits automatically. Other enhancements are very sophisticated and require proper planning, design, and implementation. Let's take the example of indexing. Remember those users who want access to data? They will be running queries, and it will be best if you provide more methods than just a full table scan. A proper indexing strategy will be critical to good database performance. This is basically true for any DBMS. With DB2 for i, indexing advice--and in many cases temporary index creation--is automatic (i.e., no user intervention required). Yet query performance monitoring, analysis, and tuning can be very rewarding, if not a critical success factor when implementing a business solution. To make the best use of the latest tools and indexing technology, someone in your organization needs to fully understand the science and art of DB2 for i indexing, just like they would on any other DBMS. To test my assumption, ask your local SQL Server or Oracle DB what an encoded vector index is.
In the many query performance situations I find myself investigating, one interesting organizational dynamic always appears. It normally starts with an application programmer using SQL without much knowledge or understanding of either what exactly is being asked of the database or how much work is really involved to return the result set. The attitude of this bright but new SQL coder is that "SQL is magic." This is usually followed by the system infrastructure team complaining about the use of SQL and the propensity of the database applications to use up all the CPU, for no apparent reason. If there happens to be anyone in the organization with some knowledge of database management systems, their comments often include such profound proclamations as "SQL is not magic" and "We can handle that much better on a different system; just give me the data." Precisely at this nexus is where the need for a DB2 for i database administator arises. Your organization can truly benefit from someone connecting the business data architects, data-centric programmers, and computing infrastructure teams (Figure 1).
Figure 1: A DB2 for i database administrator can connect the business data architects, data-centric programmers, and computing infrastructure teams. (Click image to enlarge.)
The DB2 for i database administrator can fit the bill. To be more accurate, I prefer to use the word "engineer" instead of "administrator." But in some companies, a DBA group or department already exists, and continuing to use "DBA" is probably more acceptable and reasonable.
What a Good DBE/DBA Is
So, just what does a DB2 for i database engineer do anyway? Well, for starters, this person needs a good understanding of SQL: DDL and DML syntax, the various SQL interfaces, and the power of set-at-a-time processing vs. record-at-a-time processing. Of course, the SQL knowledge and skill must be focused on DB2 for i, including how SQL and native database objects and access can be co-mingled to preserve existing business logic and data.
One of the most critical success factors is acquiring and maintaining knowledge and experience in the area of query optimization and query execution. Frankly, this is where DB2 for i really differs from other database systems. What a DBA will do on SQL Server, Oracle, or DB2 LUW to monitor, analyze, and tune queries is very different than what he or she will do on DB2 for i.
Once the DBE (note "engineer" vs. "administrator") has a good grasp of SQL and an understanding of DB2 for i query optimization and performance, many beneficial assignments can be undertaken. The responsibilities can include these:
- Logical and physical data modeling and database design
- Database creation, maintenance, and management
- Metadata management and usage
- Database performance monitoring, tuning, and management
- Indexing and statistics strategy and management
- SQL code reviews
- Database security and integrity
- Database availability, backup, and recovery
- Database server networking, access, and work management
In general, a DB2 for i DBA or DBE will spend more time working on solving data-centric business problems and less time on database system administration. Once upon a time, an experienced DBA once remarked, "...with this system [i], we can avoid the three Rs: rebind, reorg, and run stats..."!
If you are still with me, you are probably wondering how to obtain the benefits of database engineering in your organization. In other words, how does one get a DB2 for i DBA or DBE? I propose the following options:
- Buy one
- Acquire one
- Build one
- Convert one
Many companies choose to initially engage a knowledgeable and experienced DB2 for i consultant to assist them with their immediate needs. The duration can be either short- or lon-term and can include periodic reviews on a quarterly or annual basis. Retaining a DB2 for i expert can also provide support for the other options of build or convert. In other words, the DB2 for i expert can act as your consultant and educator.
To be honest, identifying and acquiring someone who has deep and current knowledge of DB2 for i is problematic. There just aren't many folks in the marketplace who have been practicing this craft. Don't get me wrong; there are many, many fine DB2 for i application developers around the world; just not too many of them would claim to be a database administrator or engineer. This makes the pool of experienced resources available for permanent employment rather shallow.
Building a DB2 for i DBA or DBE is something I highly recommend. This strategy can be very effective and rewarding. It normally starts by identifying someone in your company who already has good skills and experience with i5/OS, and more importantly, someone who knows your business and its processes. It's a bonus if this person expresses an interest in or demonstrates an aptitude for data-centric applications and database technology. To build upon this foundation, you must invest in the education needed to acquire the science and art of SQL as well as DB2 for i query optimization and execution. You must also give the new DBE the appropriate responsibilities and proper authority to carry out their duties.
Converting an existing DBA into a DB2 for i expert is something I have had some success with. The most critical factor is the willingness of the DBA to unlearn the old DBMS and relearn DB2 for i. When this occurs, I have witnessed the most diehard SQL Server or Oracle DBAs turn into DB2 for i zealots. So watch out!
Protect Your Assets
In conclusion, I would like to remind you of a very simple, but important fact: your data is an asset. And as such, you must be willing to effectively organize it, protect it, and use it. DB2 for i is a very robust, open, and secure database management system that will help you accomplish this. When you start to wonder if you should move your data and applications, first ask yourself and your organization, "Are we using DB2 effectively"? My experience suggests that a database engineer can help you get the very best out of your system.
If you want to discuss the topic of database engineering or anything else related to DB2 for i, please feel free to send me a note. My email address is
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online