Blogs

Consistency of zParms in Db2 Data Sharing Groups

By Emil Kotrc posted 12 days ago
1 person recommends this.
Consistency of zParms in Db2 Data Sharing Groups Written by: Emil Kotrc, Broadcom I really never paid too much attention to the consistency of zParms in a data sharing group until recently. If you plan to prepare for Db2 13 migration and you are about to activate the very last function level of Db2 12 - V12R1M510, you need to make sure that all your packages that are still in use were last rebound with Db2 11 at least. The activation process of this function level runs a query , which relies on the LASTUSED column of SYSPACKAGE catalog table. More on that in this blog . However, the LASTUSED column might not provide accurate information in case when ...
0 comments

DSNACICS. Why use DSNACICS? calling DSNACICS through REST API – A user experience

By Brian Laube posted 22 days ago
Be the first person to recommend this.
DSNACICS is a useful IBM supplied procedure for Db2. What does DSNACICS do? It allows one to invoke a CICS server program. To be clear. you don’t invoke a CICS transaction. You invoke a CICS program! The official IBM reference: https://www.ibm.com/docs/en/db2-for-zos/12?topic=db2-dsnacics See end of this blog for a few interesting caveats on using DSNACICS Why would one use a DSNACICS? Let us say you have a distributed application with the occasional need for some information that is easily available via a CICS program. Examples include: A VSAM file is attached to a CICS region that maintains the data values, and you want to ...
0 comments

Fun with SQL: Solve Sudoku with recursive SQL

By Brian Laube posted Dec 07, 2022
1 person recommends this.
Solve Sudoku using SQL!!! This festive season, impress your friends and colleague with your quick Sudoku skills and some clever SQL! How? Using recursive SQL, one can “solve” Sudoku. Just one SQL statement! Amazing. I think it is pretty fun! The SQL below works in both Db2 z/OS and Db2 LUW. The SQL should be able to run in any RDBMS with little modification. The SQL statement only uses SYSDUMMY1 as the initial driver. The original Sudoku puzzle is entered as one long string of 81 characters with digits of 1-9 to represent the values in the known squares and using a “.” (period) to represent the unknown squares. The solution ...
0 comments

Prove your changes; NEW, FINAL and OLD tables, Mark Gillis, Triton

By Tony Andrews posted Oct 31, 2022
1 person recommends this.
Written By: Mark Gillis . Posted By: Tony Andrews New, Final and Old tables are features that have been around in Db2 for several versions, at least back to v9.7, but they don’t seem to get a lot of use. I’m not sure why unless it’s just because they require an extra bit of syntax. I still spend an appreciable percentage of my working life running scripts for our customers. Quite often something as straight-forward as some Inserts, Updates and Deletes but I think they’re handed to us in case they go wrong so that we can establish that they’re going to do what is expected; no more, no less That’s fair enough; we bill ourselves as Db2 experts ...
1 comment

When Indexes Are Only Partially Effective

By David Simpson posted Oct 04, 2022
1 person recommends this.
Case Study #1: The Query: SELECT ... FROM T1 JOIN T2 ON T1.C1 = T2.C1 AND T1.C2 = T2.C2 WHERE T1.C4 = ‘Something’ AND T1.C6 > 27 The Environment: The only available interesting index on T2 is a single column index on C1. Table T2 has 2.4 Billion Rows Column Cardinality (COLCARDF) statistics on T2: C1 = 250 C2 = 10,000,000 It is likely that T1 will be the first table accessed for the join since both of the local predicates in the WHERE clause filter that table. The only helpful index on T2 is on C1 only AND C1 only has 250 unique values out of 2.4 billion rows. This likely means that millions of hits on the actual table for ...
3 comments

SQL PAGINATION – DATA-DEPENDENT (NEW SYNTAX) AND NUMERIC-BASED

By Tony Andrews posted Oct 03, 2022
Be the first person to recommend this.
Written By: Joe Geller Posted By: Tony Andrews SQL Pagination – New Syntax Problem Statement One of the oldest problems in Db2 performance was the efficient processing of pagination. Pagination is a programming paradigm in which an online application needs to process an indeterminate number of rows, but does not want to fetch them all in before presenting them to the user. Instead, it fetches a fixed number, returns these to the user, and then pages forward at the user’s request. In particular, this is a best practice for CICS transactions (browser based applications present a new set of challenges because the UI makes it easy for the user to scroll ...
0 comments

Db2 for z/OS SQL and Application New Features v8-v12

By Tony Andrews posted Sep 28, 2022
3 people recommend this.
Posted By Tony Andrews: Written By Tony Andrews: With every Db2 version since V8, there has been great improvements in Db2 for z/OS. Many of the improvements affect developers specifically, especially in the SQL programming language and optimization. As you can see in the following highlights, there have been upwards of 75+ enhancements that could find their way in application development. Many of the new features are being used quite frequently, while others not so much. This article is a reminder, and may not be a complete list. Each could have its own article. A short definition follows each feature. Following are some of the many application ...
0 comments

INDEXES–RECAP of important recent blogs from IDUG CC

By Brian Laube posted Sep 17, 2022
1 person recommends this.
September 2022 has a content committee primary focus on indexes . Indexes are those critical Db2 objects that define uniqueness and/or importantly help us quickly find our data. There have been some great IDUG blogs via the content committee in recent years. This article you are reading here is a RECAP blog and pointer back to some of those important blogs from the past. Click on the links below and let yourself be distracted. Db2 Z introduced fast index traversal in V12. In this article from 2017 , Akiko Hoshikawa & Nina Bronnikova from the IBM Silicon Valley Lab explain this new concept. In March of 2022, Tony Andrews gave ...
0 comments

Db2 Index Overview

By David Simpson posted Sep 07, 2022
3 people recommend this.
Indexes perform a critical role in any relational database and Db2 is no exception. Applications expect to receive near instant response when querying multi-billion row tables Indexing tables in a manner appropriate to the workload is a big part of a Database Administrator’s job.When no index exists to service a query then every row will be examined to determine which records qualify (i.e. tablespace scan). Let’s take a look at the way indexes work in a Db2 environment. Readers can probably think of many practical (non database) examples of indexes. My favorite is a trip to your local library. We can think of the non-fiction section as an unordered collection ...
0 comments

Are you Packaged and Bound for Db2 13?

By Emil Kotrc posted Aug 18, 2022
2 people recommend this.
Are you Packaged and Bound for Db2 13? Written by: Julia Carter and Emil Kotrc , Broadcom Package rebinds can sometimes be a real challenge for DBAs when it comes to Db2 upgrades. There are often a group which everyone knows by name and dreads the prospect of having to rebind them. Maybe they are part of one of the most core and frequently used transactions, or maybe they are related to internet banking login, or an application lookup which someone or something is calling 24x7, and in the past you have effectively needed an outage of the application in order to make that rebind. Maybe their access path is really sensitive and there have been issues ...
0 comments

Db2 V13 - what is new and exciting from a user perspective

By Brian Laube posted Aug 03, 2022
4 people recommend this.
Db2 Version 13 is upon us! After V12, IBM was a bit vague about when a new version number for Db2 would be created. Why? IBM was using Db2 “ continuous delivery ” and function levels to introduce new functionality to Db2 Version 12. IBM and Db2 were agile and fast. Great. New functionality would come constantly and we would not need to wait years for a new version number to bring new functionality. Previously, for the past few version numbers, IBM would introduce a new version number to Db2 every 3 years. But it has been almost 6 years since Version 12 was introduced ( Db2 V12 GA - Oct 21, 2016 ). IBM has now decided it is time for Db2 V13 which was ...
0 comments

Introducing Db2 Click to Containerize

By Joe Geller posted Jul 08, 2022
Be the first person to recommend this.
Introducing Db2 Click to Containerize Written by George Baklarz and Phil Downey Many customers are looking at modernizing their databases by moving them into a containerized environment such as OpenShift, Kubernetes, or Cloud Pak for Data. What usually makes a DBA nervous is the complexity involved in moving a database into this environment. You need to: Upgrade your database to Version 11.5.x since that is the release level of the Db2U container. Learn how to work in the containerization environment Rebuild the database with export and import commands Getting to the end goal of containerizing your database takes a lot of time and effort! ...
0 comments

Modernizing Db2 Containerization Footprint with Db2U

By Joe Geller posted Jul 04, 2022
1 person recommends this.
Modernizing Db2 Containerization Footprint with Db2U Written by Aruna De Silva Containerization takes Db2 to the world of Cloud Paks, Red Hat OpenShift, Public Cloud Kubernetes platforms (AWS, Azure, Google Cloud and IBM Cloud), and other Kubernetes flavours (Rancher). We are moving at a fast pace; we will bring you up to speed and share the next steps of our journey forward in this article. There are numerous references to future product plans, deliverables and support statements throughout this article which are subject to notes [1] , notices and disclaimers [2] mentioned at the end. Evolution of Db2 Containerization Looking back, ...
0 comments

Cool factor of z16

By Emil Kotrc posted Jun 07, 2022
Be the first person to recommend this.
Cool Factor of IBM z16 Recently, I have been visiting a technical university with a short speech at a seminar and my topic was IBM mainframes. Fortunately, this was after IBM's announcement of the new machine IBM z16, so I had some great content to cover. One of the discussion point of course was the new processor - IBM Telum, and because the audience was geeky enough, a question was how does this chip compare with other processors, especially with Apple M1 Ultra released a bit sooner. Apparently, this is not a surprising question, I have heard this one even on IBM's session by Christian Jacobi (you can also listen to this podcast or terminal talk ...
0 comments

Db2 catalog tables - Statistics

By Denis Tronin posted May 24, 2022
1 person recommends this.
Db2 catalog tables – Statistics Written By: IBM Data Management Console Development Group Jun Liu , Guo Bin Zhao, Yinmei Chen Db2 maintains a set of catalog tables that describes the metadata of each database, including tables, indexes, views, schemas, procedures, sequences, etc. Authorized users can query those catalog tables to get useful information like how many tables are stored in the database, how many columns are defined for each table and what’s the column types, etc. But most of catalog tables are used by Db2 itself. A good example is the statistics associated with those database objects. Statistics in Db2 database is a very important kind of ...
0 comments

Exploring the Db2 for z/OS Catalog

By Emil Kotrc posted May 16, 2022
Be the first person to recommend this.
Exploring the Db2 for z/OS Catalog Written by Sowmya Kameswaran Navigating and exploring the Db2 catalog is a very common activity for anyone who interacts with the database engine. Database administrators and application developers browse the object catalog for a multitude of reasons. Whether they want to view all objects, check the status of an object, locate statistics, browse the data, generate DDL, or any other action that involves working with catalog objects, having an intuitive and easy-to-use interface is a key component of working productively and efficiently. Most Db2 users are familiar with the object-type-based navigation provided ...
0 comments

Catalog Queries for Db2 for z/OS

By Emil Kotrc posted May 05, 2022
Be the first person to recommend this.
Catalog Queries for Db2 for z/OS Introduction If you are experienced with database management systems outside of mainframe, you've probably heard about an information schema . This is a set of views that provide information about all tables, columns, and all other database objects defined in the system. Db2 for z/OS does not have an information schema, but has a Db2 catalog containing all the metadata about the objects. There is already a lot of blogs, articles, presentations about the Db2 catalog (for instance you can start with Denis' blog , which is an overview of the catalog, but also contains useful links - check it out); so why to ...
0 comments

Db2 for z/OS Dual Logging: zHyperWrite and zHyperLink—are they related?

By Emil Kotrc posted Apr 13, 2022
Be the first person to recommend this.
Db2 for z/OS Dual Logging: zHyperWrite and zHyperLink—are they related? Sharon Roeder, IBM zHyperWrite and zHyperLink certainly sound very similar. Both have definite performance benefits for Db2 dual logging, and both require DFSMS Media Manager software to leverage specific z/OS features. However, when it comes to Db2 Log Manager, they are really two different sets of functionality controlled by two different system parameters. This discussion focuses on Db2 dual active logging. zHyperWrite In Db2 10 for z/OS, zHyperWrite support was added to improve overall Db2 performance for customer shops that use Peer-to-Peer Remote Copy (PPRC) where log ...
0 comments

Back to Basics: Algorithms for Recovery and Isolation Exploiting Semantics

By Emil Kotrc posted Apr 05, 2022
Be the first person to recommend this.
Back to Basics: Algorithms for Recovery and Isolation Exploiting Semantics Most likely, you understand how logging in Db2 for z/OS works in general. There are log buffers, active logs, which get offloaded to archive logs, and all is being tracked in the bootstrap dataset. For more details about the basics refer to Martin Hubel's IDUG article . All conceptually pretty clear. However, have you ever heard about ARIES (Algorithms for Recovery and Isolation Exploiting Semantics)? The link before points to wikipedia where you can find more information, most importantly you can find there a link to a paper written by IBM Fellow, Dr. C. Mohan. Dr. Mohan's ...
0 comments

Travel through time with Db2 SQL to investigate complex application data change questions

By Brian Laube posted Mar 15, 2022
1 person recommends this.
Travel through time with Db2 SQL to investigate complex application data change questions The poor beleaguered support staff sometimes come up with the strangest questions. Why does policy 123 in the POLICY table have status code X. I could have sworn the status code should be Y. How could this be? Who did it? When? Why? That is actually a very interesting question and the application DBA might be able to help. In our case, the POLICY table has helpful field called “update_timestamp”. But the value for this row is from 10 years ago. Apparently, the application code must maintain this field and we should not always trust ...
0 comments