IDUG Db2 Table Talk Podcast

I recently had the privilege to sit down with Marcus Davage and Julia Carter to discuss Db2, data, and my career on the IDUG Db2 Table Talk podcast. 

The podcast is a monthly occurrence and IDUG uses it to promote Db2 and for practitioners to discuss the experiences and techniques they use in the field. I hope you will take the time to listen to the podcast, not just this month, but regularly! 

You can view it on the IDUG website or download the podcast at this link.

Tech Sharmit Podcast

I recently had the privilege to sit down with Armit Sharma to discuss Db2, data, and my career on his Tech Sharmit podcast. 

Armit is an IBM Champion and his podcast series is always entertaining and informative. If you are interested in mainframes, Db2, data and databases, and my journey in that world, be sure to check out the podcast. 

You can view the podcast at this link.

Db2 Productivity-aid Sample Programs: Useful for Development & Administration

As you work on your Db2 databases and applications, you inevitably will come across certain small, but important tasks that you need to perform. You know, things like moving data from one place to another or modifying a set of values in a table or just querying data. Of course, you can always write your own programs to do any of those things, but wouldn’t it be better if you didn’t have to?

Well, IBM supplies several Db2 productivity-aid sample programs that you can use to simplify, automate, or optimize common database tasks. There are four sample programs that are provided free-of-charge with Db2 that you can use as helpful productivity aids. These programs are shipped as source code, so you can modify them and use them for whatever purposes you may have.

OK, so what type of sample programs does IBM provide? Let’s see.

DSNTIAUL

The first Db2 productivity aid that most people encounter is DSNTIAUL, a sample program for unloading data. Today, it is viewed as an alternative to the UNLOAD utility, but it was around long before IBM ever offered an UNLOAD utility (which was added in DB2 V7).

Prior to the introduction of the UNLOAD utility, data generally was unloaded using the sample program DSNTIAUL (or perhaps a BMC or CA unload program). Fortunately, this DSNTIAUL sample program is still available and can be used for unloading your Db2 data. And, of course, the IBM Db2 utilities  (or any other vendor utilities) must be purchased additionally to Db2, whereas DSNTIAUL is free of charge.

DSNTIAUL is written in Assembler language. It can be used to unload some or all rows from up to 100 Db2 tables. With DSNTIAUL, you can unload data of any Db2 built-in data type or distinct type. DSNTIAUL unloads the rows in a form that is compatible with the LOAD utility and generates utility control statements for LOAD. You can also used DSNTIAUL to execute any SQL non-SELECT statement that can be executed dynamically.

DSNTEP2

DSNTEP2 is a sample dynamic SQL program that can issue any SQL statement that can be executed dynamically. DSNTEP2 is especially useful for running ad-hoc SQL statements without requiring the overhead of writing and compiling a full application/program.

DSNTEP2 can execute valid SQL statements dynamically. This includes SELECT, INSERT, UPDATE, DELETE, COMMIT, ROLLBACK, and DDL statements (like CREATE and DROP). DSNTEP2 runs in batch mode, typically submitted using JCL.

The drawback is that DSNTEP2 does not allow advanced features like conditional logic or loops. If you need to perform such tasks, you will have to write a program with embedded SQL. Additionally, formatting the output of DSNTEP2 is not as flexible as with custom programs.

DSNTEP2 is written in PL/I and available in two versions: a source version that you can modify to meet your needs or an object code version that you can use without the need for a PL/I compiler.

DSNTEP4

Everything that can be said about DSNTEP2 can also be said about DSNTEP4. It is a sample program that can be used to issue SQL statements dynamically. In point of fact, DSNTEP4 is identical to DSNTEP2, except that DSNTEP4 uses multi-row fetch. For this reason, I recommend using DSNTEP4 instead of DSNTEP2 because it has the potential to improve performance.

Check out this blog post for a comparison of DSNTEP2 and DSNTEP4.

Like DSNTEP2, DSNTEP4 is written in PL/I and available in a source version that you can modify and an object code version (if you do not have a PL/I compiler).

DSNTIAD

Finally, we have the DSNTIAD sample program. DSNTIAD is an assembler application program that can issue the same DB2 dynamic SQL statements as DSNTEP2/DSNTEP4, with the exception of the SELECT statement. For this reason, applications programmers usually prefer to use DSNTEP2/4 rather than DSNTIAD.

DSNTAID is written in Assembler language. Because DSNTIAD is a sample program, its source code could be modified to accept SELECT statements if you so desired. But this task is complex and should not be undertaken by a beginning programmer. And there is really no reason to do so given the availability of DSNTEP2/4.

So why would anyone consider using DSNTIAD over DSNTEP2/4?

Well, DSNTIAD supports the LABEL ON statement, whereas DSNTEP2/4 does not. But unlike DSNTEP2/4, DSNTIAD does not accept comments embedded in SQL statements.

Also note that DSNTIAD can be a little more efficient than DSNTEP2 because it is written in Assembler.

Summary

Because these four programs also accept the static SQL statements CONNECT, SET CONNECTION, and RELEASE, you can use the programs to access Db2 tables at remote locations.

As a Db2 developer or DBA it is a good idea to know about the Db2 productivity-aid sample programs and to understand what each does. Using them appropriately can save you a lot of time and effort.

How About a Db2 Book for the Holidays?

If you are still on the look-out for a gift for the Db2 DBA or developer in your life, have you considered getting them a Db2 book? Technical books can be gifts that keep on giving throughout the year! And you'll be remembered as the kind gift-giver as the reader digests the information in the book, and then comes back to the book for reference as they work!

Technical books serve as vital resources for professionals and students, providing in-depth knowledge, practical guidance, and up-to-date information on Db2 subject areas. They can be used for knowledge expansion, skill development, problem solving, exam preparation, and staying up-to-date in your career.

Here are a few books you might want to consider for the Db2 person in your life.

• "DB2 Developer's Guide" offers numerous benefits for database professionals working with Db2 for z/OS. The book provides a comprehensive and practical approach to Db2 development and administration, covering essential topics from basic SQL and database design to advanced performance tuning and application development techniques. Its clear explanations, real-world examples, and best practices make it an invaluable resource for both novice and experienced Db2 developers. By mastering the concepts presented in this guide, developers can design efficient, robust, and high-performing Db2 applications, ultimately improving data management and business processes. The book's focus on practical application and problem-solving makes it a highly effective tool for enhancing Db2 development skills and optimizing database performance.
  
  
• Another useful book, particularly for application programmers and developers, is "A Guide to Db2 Performance for Application Developers" which offers invaluable insights for developers seeking to optimize Db2 database performance. This book focuses on how coding practices impact Db2's efficiency. It offers practical guidance on writing efficient SQL and designing effective data access strategies. By understanding these principles, developers can avoid common performance pitfalls, reduce resource consumption, and improve application responsiveness. The purpose of this book is to give advice and direction to Db2 application developers and programmers on writing efficient, well-performing programs. The material is written for all Db2 professionals, whether you are coding on z/OS (the mainframe) or on Linux, Unix or Windows (distributed systems). When there are pertinent differences between the platforms it is explained in the text. This guide empowers developers to proactively contribute to database performance optimization, leading to faster applications, reduced costs, and improved user experiences.
  
  
• If you are looking for a book for the mainframe professional in your life, consider "IBM Mainframe Specialty Processors: Understanding zIIPs, Licensing, and Cost Savings on the IBM System z." This book will clarify the purpose of specialty processors and how you can best utilize them for cost optimization. The book provides a high-level overview of pertinent mainframe internals such as control blocks, SRBs, and TCBs, and why they are important for understanding how zIIPs work. Additionally, because reducing mainframe software cost is essential to the purpose of specialty processors, the book proivdes a high-level introduction to understanding mainframe licensing and pricing. The book describes the types of workloads that can take advantage of specialty processors, including advice on how to promote zIIP usage in your applications and systems. Read a review of the book here.
  
  
• And finally, consider gifting "The Tao of Db2" to the Db2 DBA in your life. This short, low-cost but insightful book offers guidance on how to manage Db2 properly to achieve harmonious systems and applications that deliver quality and performance. It follow the exploits of a seasoned DBA and his intern as they learn "the way" of Db2 database management and administration. Learn along with them and improve your Db2 administration chops!

I want to wrap up this post by wishing all of my readers a very happy holiday season... and I hope you will consider grabbing at least one of these Db2-related books for the techie in your life... or even as a gift for yourself.

5 Big Concerns of Modern IT When Using Db2 for z/OS

Db2 for z/OS is an entrenched solution for managing data at the world's largest organizations. It is a strong, reliable DBMS and I wrote about its strength recently on the blog (here). You really cannot go wrong using Db2 for z/OS for mission-critical workloads.

That said, there are concerns and issues facing organizations using Db2 for z/OS. One of the biggest concerns with Db2 for z/OS today is managing the cost and complexity of maintaining mainframe environments while still delivering high availability and performance. 

As such, here are 5 specific concerns facing large organizations using Db2 for z/OS today:

1. Skill Shortages: Many mainframe experts, especially those with deep Db2 for z/OS knowledge, are approaching retirement, creating a significant skills gap. The lack of trained professionals has made it challenging to manage and maintain Db2 for z/OS systems effectively.

2. Cost of Licensing and Maintenance: Mainframe systems come with substantial licensing costs. Many organizations are looking for ways to optimize usage or even repatriate workloads to more cost-effective platforms, where feasible, to reduce operational expenses. Whether or not such changes result in "actual" cost reductions is unfortunately irrelevant as many executives believe it will regardless of reality and studies to the contrary.

3. Integration with Modern Architectures: As companies adopt cloud, big data, and other modern architectures, integrating Db2 for z/OS with these systems can be complex and costly. Many seek seamless data integration between Db2 on mainframes and newer platforms like data lakehouses, which involves architectural and technological challenges.

4. Automation and DevOps Compatibility: Modern IT environments emphasize agility, continuous integration, and deployment, but the mainframe environment traditionally doesn’t integrate well with DevOps practices. Nevertheless, many companies are pushing for Db2 automation tools and integration with DevOps workflows to streamline operations and reduce manual workloads... and DevOps is being successfully deployed by mainframe organizations today using Zowe and other traditional DevOps tooling.

5. Performance and Availability: High performance and availability are always top concerns, especially as organizations process more data and need to meet stringent SLAs. Handling lock contention, optimizing query performance, and scaling resources efficiently continue to be challenges. But, to be fair, these are challenges with many DBMS implementations, not just Db2 for z/OS.

Organizations are adopting several strategies to address the challenges with Db2 for z/OS and ensure their mainframe environments remain relevant and efficient:

1. Workforce Development and Knowledge Transfer: To counter skill shortages, organizations are investing in training and upskilling initiatives for new IT staff, partnering with universities, or using mentoring programs to transfer knowledge from retiring mainframe experts to newer employees. Additionally, some companies are leveraging consulting firms or managed services providers with mainframe expertise to fill gaps temporarily.

2. Cost Optimization with Usage Analytics: Companies are using detailed workload and resource monitoring tools to optimize Db2 for z/OS usage, identify inefficient processes, and reduce costs. This includes tuning queries, scheduling batch jobs during off-peak hours, and leveraging IBM’s Workload Manager (WLM) to prioritize workloads based on business needs.

3. Hybrid Cloud and Data Lakehouse Integrations: To manage integration with modern architectures, organizations are implementing hybrid cloud strategies and data lakehouses that can interface with Db2 for z/OS. Tools such as IBM Db2 Analytics Accelerator allow data stored on Db2 for z/OS to be offloaded to faster, scalable platforms, enabling integration with big data and analytics environments without entirely migrating off the mainframe.

4. Automation and DevOps Integrations: Organizations are investing in DevOps and automation tools compatible with Db2 for z/OS, such as IBM UrbanCode and mainframe DevOps solutions from other ISVs such as Broadcom and BMC Software. By automating routine tasks like provisioning, patching, and deploying schema changes, organizations can adopt more agile, efficient processes. Integrating Db2 for z/OS with CI/CD pipelines helps streamline development workflows, bridging mainframe operations with modern DevOps practices. For more details on integrating Db2 for z/OS into DevOps, consult this blog post that highlights several posts I wrote on the topic!

5. Mainframe Modernization with AI and Machine Learning: Using AI and machine learning to optimize Db2 for z/OS operations is becoming common. AI-based monitoring tools, such as IBM’s Watson AIOps, can predict system issues and detect anomalies to prevent downtime. Machine learning algorithms can also be used for capacity planning, workload optimization, and tuning Db2 performance parameters, helping reduce manual intervention.

6. Resilience and High Availability Improvements: For performance and availability, companies are implementing high-availability solutions like IBM Geographically Dispersed Parallel Sysplex (GDPS) to ensure continuous uptime. They’re also using backup automation and disaster recovery solutions tailored for Db2 to meet stringent SLAs and minimize downtime in case of failures.

By combining these strategies, organizations are better equipped to manage the costs, complexity, and skills required to maintain and modernize Db2 for z/OS environments in today’s rapidly evolving IT landscape.