Friday, February 18, 2022

Db2 for i DBE Duties

 It’s not too hard to see that the alliteration was on my mind when I came up with the title for this entry. And I could have added one more “D” to the title with “Discussion” or “Definition”.  I view this as a discussion since the exact duties of a DBE (Database Engineer) can vary depending on how responsibilities are divided up within your IT organization. Even if there are other teams that own functions like security, a DBE should function as an extended member of those other teams focusing on the database-specific requirements.

 

On client engagements, our Lab Services Db2 team is often asked “What should be the duties of a Db2 for i DBE?”. The short answer is that a DBE performs the same role as a Database Administrator (DBA) on other platforms minus the low-level administrative tasks. And that’s why our team uses DBE instead of DBA to highlight that difference.

 

Recently another blogger wrote about the responsibilities of a traditional DBA, so I thought it would be useful to compare that list with the to-do list of a Db2 for i DBE. You can find the complete DBA writeup here. I’ve summarized that article by copying some of the key high-level topics here and adding my Db2 for i commentary. I have used green to highlights those responsibilities that are exact matches for a DBE and the red for those tasks that are not as applicable to Db2 for i.

·        Data modeling and database design – Going back to the AS/400 days, this design work often was done by the application development team. Unfortunately, this also explains some of the subpar data models that our team encounters. Now that Db2 for i has much a wider set of technology that can be applied to the design, a DBE should be leading these efforts. 

·        Database schema creation and management – Allowing others to create and change Db2 objects is a mistake because it increases the risks that those without deep Db2 knowledge will perform the actions incorrectly resulting in database performance and security issues. 

·        Metadata management and repository usage – “Data about the data” is important. Probably a small part of a DBE’s job, but they must support the company’s efforts to document the business data. 

·        SQL code reviews and walk-throughs – Another intersection point between a DBE and the developers. These reviews are a great way to prevent query performance problems. In the process, developers learn more about SQL programming performance best practices.

·        Programming and development – A DBE doesn’t need to worry about the state of an SQL program object causing performance problems. Traditional DBAs often have to BIND/REBIND SQL programs when the database or server has changed to notify the optimizer about the change. The Db2 for i engine automatically recognizes changes that have been made to the database or server changes that may impact performance.

·        Procedural coding and debugging – With proliferation of procedural SQL, a DBE needs a good understanding of these SQL objects. In addition, procedural SQL makes it easy for a DBE to programmatically use Db2 for i utilities and services for database maintenance.

·        Performance management and tuning – This role is a no-brainer. On a system with SQL usage, a DBE should be continually focused on proactive and reactive performance tuning. A solid indexing strategy is a critical success factor in this area.

·        Ensuring availability, Backup and recovery – Both of these tasks are usually handled at the system-level in IBM i shops, but a DBE needs to be aware of the database impacts for the different types of system approaches to keep database availability high and providing input to the system strategy. 

·        Data security – A DBE is going to be working hand in hand with the security administrator to ensure the best database controls are used to meet and enforce the company’s security policy.

·        Data movement, Data archiving – DBE should be leading these efforts to ensure that data is moved/archived safely and efficiently to minimize impacts on system performance.

·        Ensuring data integrity – The other article highlights the use of database constraints and triggers to ensure data integrity. However, I would argue those are options a DBE would consider in the database design. The reason this topic is highlighted as not applicable for Db2 for i databases is because it talks about a DBA ensuring the structural integrity of database – looking for corruption in the disk page and structures associated with table and index objects.

·        Storage management techniques – Thanks to IBM i single-level storage, a DBE doesn’t have to spend time allocating disk storage for Db2 for i objects and trying to evenly spread the Db2 object data across disk devices to minimize hot spots. As Db2 for i objects grow or shrink in size, IBM i automatically handles the requirement for more or less disk space.

·        Capacity planning – Yes, a DBE needs to understand future data growth requirements for their databases, but this work is done at a much higher level. This is due to the fact that IBM i single-level storage simplifies the storage allocation and management for Db2 for i objects.

 

Hopefully, you can see how this list from the other article easily reinforces the proposition that a Db2 for i DBE should be focused on the same work as traditional DBAs minus a lot of the low-level administrative tasks. Thanks largely to Db2 for i and IBM i operating system automating many of those low-level tasks.

 

As I wrap up this discussion, I wanted to share a discovery that I made while putting this entry together. I learned that the DBA acronym is also used for the phrase “Don’t Be Afraid”.  One could make a compelling argument that IT organizations with a DBA don’t have to be afraid because the DBA role has their business data covered.  These orgs have less risk because they’ve dedicated resources to making sure that their databases are secure, available, extensible, and scalable. And that’s why our Lab Services team continues to remind IBM i clients about the importance of staffing the DBE role… it minimizes the risks associated with your companies most valuable asset – the business data stored in Db2 for i.


NOTE: After this entry was originally published, a follow-on entry highlighted Tools for the Db2 for i DBE