Blogs

Searching for gaps within consecutive numbers using SQL

By Tony Andrews posted 23 days ago
1 person recommends this.
Written by Eddy Coppens Posted By Tony Andrews If you would have asked me one month ago to come up with a query that coughs up missing numbers, I would have told you that there is no such a need … until a few days ago. In preparation of a migration of our Db2 (LUW) instances from one hardware platform to another we saw that we exhausted all our IP ports. Counting the number of instances per environment indicated some of the IP port ranges were missed. It is not that we would not be able to find them if we were to look for open ranges, but that would minimize the re-usability of it. As we register the used IP ports into a database, a repeatable ...
0 comments

The Db2 Instrumentation Facility

By Marcus Davage posted 27 days ago
1 person recommends this.
Earlier this year, I attended the GSE BeLux Conference in Brussels. Jan Cannaerts of Solidaris gave a really enjoyable presentation on his use of the Db2 instrumentation facility. Here is his presentation in blog form. So put the kettle on, make a cuppa, sit back and have a read... Marcus Davage IDUG Content Committee The Db2 Instrumentation Facility Disclaimer This project is the result of someone trying to solve a problem the fun way. Often, the fun way is both time consuming in its creation and its maintenance. Sadly, this makes it not economically viable. The fun way was adopted because I’ve not set up our vendor products correctly, and am using them ...
0 comments

Db2 Attachment Facilities

By Emil Kotrc posted Oct 31, 2023
1 person recommends this.
Db2 Attachment Facilities Intro You want to write a program that connects to Db2 for z/OS and runs queries, updates the tables, and all the stuff you would expect from a database application. Let's assume your application needs to run on z/OS. What options do you have to connect to Db2 from a local z/OS application? That is what we will discuss in this blog. Attachment Facilities First, you need to understand the environment your program will be running in. If it is REXX, you will use DSNREXX. If it is Java, your choice would be JDBC or even DDF. If it is C or C++ you may want to use ODBC. For other modern ways you may want to refer to the ...
0 comments

Db2 by the back door (part 1)

By Marcus Davage posted Oct 20, 2023
Be the first person to recommend this.
Db2 by the back door (part 1) Introduction 65 million years ago, or so it seems to many, COBOL-programming dinosaurs roamed the earth. They accessed their Db2 data with highly efficient static SQL calls. They built their databases and tinkered with their data with SPUFI. All was well with the world. Then meteors struck their comfortable habitat. Meteors called “Java,” “Python,” and “REST.” ISPF gave way to Visual Studio Code. SPUFI to Command Line Processors. Their world changed forever… There are now many methods of getting at your database and its data. Some of them might be alien to your traditional Db2 DBA on Z, yet strangely familiar to DBAs ...
0 comments

The Book of Db2 Pacemaker – Chapter 3: Pacemaker Resource Model: Into the Pacemaker-Verse

By Joe Geller posted Oct 09, 2023
2 people recommend this.
The Book of Db2 Pacemaker – Chapter 3: Pacemaker Resource Model: Into the Pacemaker-Verse Written by Alan Lee Let me openly admit that my first treatment of this topic was a very thorough top-down, lecture style, technical approach … until Michael Keaton inspired me to do it differently with his recent appearance in “The Flash” movie where he used 3 to 4 uncooked Spaghetti to explain the mind-bending “Multiverse” concept ! It was then I decided to borrow a page and use the following analogy to explain the resource model concept in Pacemaker. In simple terms, it’s “ Cause and Effect ”. The patient in the diagram ...
0 comments

The Book of Db2 Pacemaker – Chapter 2: Pacemaker Cluster … Assemble!

By Joe Geller posted Sep 26, 2023
2 people recommend this.
The Book of Db2 Pacemaker – Chapter 2: Pacemaker Cluster … Assemble! Written by Gerry Sommerville First off, I strongly recommend readers to review Chapter 1 of this series - The Book of Db2 Pacemaker – Chapter 1: Red pill or Blue pill ? It kicks off this new series by reiterating the many reasons and benefits to adopt Pacemaker in your environment, how to determine if there is a match to make such move in your current deployment, and many others. In Chapter 2, I will switch into a more technical gear to give the nuts and bolts of assembling a common HADR cluster with Pacemaker. The Goal Let’s set the goal to walk ...
1 comment

Modern SQL tips and why to use them

By Brian Laube posted Sep 23, 2023
1 person recommends this.
The following is a collection of SQL tips and reminders about modern SQL that some people may have forgotten. These SQL tips are reminders that are not magic but some people may have forgotten or not considered the implication or minor benefit. Hence this article! If you have additional modern SQL or tips then please add a “comment” below or email me! Limit SELECT result set size with LIMIT SQL Pagination - avoids programmatically dealing with result sets GROUP BY GROUPING SETS to produce result sets that look like a simple report COALESCE can turn ugly NULL into a pretty displayed result Match on COLUMN sets ...
1 comment

On Clause vs Where Clause Filtering Predicates in Joins

By Tony Andrews posted Sep 18, 2023
1 person recommends this.
When coding SQL joins, is there any difference in where you code your filtering predicates? We know that when you code SQL using the ANSI-92 syntax, the join predicate must be coded as part of the ON clause. But the filtering predicates can be coded as part of the ON or the WHERE clauses. This ANSI-92 syntax is best for a couple of reasons: - You can never have an orphan table that is not being joined to, thus avoiding cartesian joining. Also called cross joins. - Separates the join relationship logic in the ON clause from the filtering logic in the WHERE clause. Many say this is cleaner and easier to ...
0 comments

SQL Scalar Fullselects

By Tony Andrews posted Sep 12, 2023
1 person recommends this.
This term relates to any SELECT that returns a single value (one column, one row). It can be used as an expression in the SELECT, WHERE, or HAVING parts of an SQL query. If the scalar fullselect returns more than 1 row or 1 value, an error occurs. If nothing is found based on the query logic, a null is returned. The error for more than 1 row returned would be: ‘SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row. SQLSTATE=21000’ The error for more than 1 value returned would be: Multiple columns are returned from a subquery that is allowed only one column.. SQLCODE=-412, SQLSTATE=42823 ...
0 comments

September 2023 President's Letter

By Ian Bjorhovde posted Sep 08, 2023
Be the first person to recommend this.
Hello IDUG Members! Summer is nearly over, and I hope that you've had a chance to enjoy some time away from the keyboard, joining family instead of tables! IDUG has been working hard to prepare for the EMEA Conference in Prague. We're excited to celebrate IDUG's 35th Anniversary with our attendees in Prague. The grid for the conference is available and looks fantastic; we're very excited to announce that Julian Stuhler, who recently retired from an extraordinary career, will be onsite in Prague to deliver the opening keynote address. We will also have keynotes from Steve Astorino, the VP of Development for Data & AI at IBM, Greg Lotko, Broadcom's GM of ...
0 comments

More IDAA Content

By David Simpson posted Aug 31, 2023
1 person recommends this.
As a follow up to our previous article on IDAA we also wanted to bring attention to several years worth of content on IDAA that might still be relevant: Check out the links to previous articles and presentations below: Our Road to Success with the Db2 Analytics Accelerator Db2 Analytics Accelerator for z/OS Integrated Synchronization: Use Cases and Experiences from the Field IBM Data Studio – Introduction to the IDAA Plug-In by Billy Sundarrajan The 5 Things We Learned After 5 Years of IBM Db2 Accelerator Usage IBM Db2 Analytics Accelerator can now be deployed on IBM Z
0 comments

SQL: Self Joins

By Tony Andrews posted Aug 24, 2023
1 person recommends this.
SQL: Self Joins Why is it sometimes we see SQL queries that list a table more than once in the same query. And this isn’t where you see a table in both the outer query, and also in a subquery. But when the same table is coded more than once in the same FROM clause. For example you might see: SELECT ……. FROM EMP E1 INNER JOIN …. PROJ P INNER JOIN … EMP E2 WHERE …. There are a number of reasons (logic wise) where this self-joining is needed in SQL, along with some performance gains. This article lists 3 of the more common reasons for seeing a table twice, along with descriptions and examples. 1. ...
0 comments

A Db2 Analytics Assessment can help you get more from Db2 for z/OS

By David Simpson posted Aug 08, 2023
2 people recommend this.
The following article was provided by Cuneyt Goksu from IBM: The Db2 Analytics Accelerator for z/OS (Accelerator) has improved the performance and reduced the resource consumption of hundreds of Db2 for z/OS applications. Db2 for z/OS Data Gate (Db2 Data Gate) provides synchronized Db2 data to cloud-based applications reducing read-only operational processing and the associated zSystems resources and offers query redirection and acceleration. Many organizations want to understand more specifically how these technologies could improve their unique workloads. That is why IBM developed the Db2 Analytics Assessment process. A workload assessment can help evaluate ...
0 comments

The Book of Db2 Pacemaker – Chapter 1: Red pill or Blue pill ?

By Joe Geller posted Aug 07, 2023
8 people recommend this.
The Book of Db2 Pacemaker – Chapter 1: Red pill or Blue pill ? Written by Alan Lee If you have heard about Db2 support of Pacemaker and have currently deployed Db2 in a highly available environment with either integrated Tivoli System Automation & MultiPlatforms (a.k.a TSA) or other user-managed cluster managers, you might be struggling with this question – When is the right time to push my organization to take the red pill to have the new Pacemaker experience or the blue pill to remain status quo with TSA. Fair question and make no mistake, you are not alone. My hope with this opening chapter of what is currently planned as ...
0 comments

June 2023 President's Letter

By Ian Bjorhovde posted Jun 20, 2023
Be the first person to recommend this.
Hello IDUG Members! After volunteering for IDUG for over 10 years, I'm incredibly honored to take over the helm from Iqbal Goralwalla, who served as IDUG's president for the past two years. I'll be working hard to try and fill his incredibly capable shoes. IDUG is celebrating its 35th anniversary and just wrapped up a very successful North America conference in Philadelphia, where over 500 people attended, both in person and virtually, to learn about Db2 from colleagues, experts and IBMers alike. It was a fantastic week and the NA Conference Planning Committee put together a packed event of world-class Db2 education. In Philadelphia, we announced that next ...
0 comments

May 2023 President's Letter

By Iqbal Goralwalla posted May 11, 2023
1 person recommends this.
Hello again to our IDUG members all over the world! As I excitedly prepare for travel this week to Philadelphia for IDUG’s 2023 North America Db2 Tech Conference, I am also reflecting quite a bit. This is my final President’s Message! I am so proud of how hard and how well all IDUG’s volunteers and stakeholders worked during the once-in-a-century global pandemic. While our world got smaller…IDUG got bigger! We produced 100% virtual conferences that continued our reach and that reached out and touched so many in the Db2 community who had not been able to participate regularly at our face-to-face events. While other User Groups shrunk or ceased to exist, ...
0 comments

Db2Z and Redirected Recovery - user experience – Part 1 – What it is and WHY and How

By Brian Laube posted May 02, 2023
2 people recommend this.
Db2 Z V12 introduced interesting new functionality to the “RECOVERY” utility. This new functionality is called ‘redirected recovery’ and it has great potential! Ø This blog is PART 1 of a series on my experiences with redirected recovery. Ø Read them all for a complete picture! 1. Db2Z and Redirected Recovery - user experience – Part 1 – What it is and WHY and How o What is redirected recovery? o Why might we use it? o How do we use it? Example JCL 2. Db2Z and Redirected Recovery - user experience – Part 2 – Details and Caveats o Thoughts and caveats ...
1 comment

Temporary Tables in Db2 (along with a painful lesson)

By Brian Laube posted Apr 23, 2023
5 people recommend this.
Introduction: What is a Temporary Table A temporary table (in most RDMS) is a place to keep data for the duration of some application process. The data is basically private and only available to the application user. The typical end-user of a temporary table ranges from simple ad-hoc dynamic end-users (like you or me) up to real application programs (native SQL stored procedures or COBOL). Temporary tables are used in dynamic and static SQL. A temporary table is a place to store relevant to your needs. Whether you are an application or some end-user. It is a place to keep data. It helps give SQL coding options. They can help reduce SQL complexity! ...
0 comments

How to find Missing Db2 for z/OS static SQL access path information from PLAN_TABLE?

By Brian Laube posted Apr 23, 2023
1 person recommends this.
How to use “package” access path information to solve a real-life application issue! Get access path from the “package”! The new SQL EXPLAIN statement options! There are NEW (as of Db2 for z/OS V10) SQL “EXPLAIN” statement options to get the hidden details of the actual access path instructions from the actual package (which is actually used at run time) and stuff that impossible-to-read package instruction “explanation” into the friendly-to-read PLAN_TABLE! And then you can query the PLAN_TABLE and understand what is going on! Review: What is an “access path”? Just as a refresher…… what is “access path”? The “access paths” ...
0 comments

Db2Z and Redirected Recovery - user experience – Part 3 -Automatic Schema Comparison

By Brian Laube posted Apr 21, 2023
1 person recommends this.
Db2 Z V12 introduced interesting new functionality to the “RECOVERY” utility. This new functionality is called ‘redirected recovery’ and it has great potential! Ø This blog is one of a series on my experiences with redirected recovery. Ø Read them all for a complete picture! 1. Db2Z and Redirected Recovery - user experience – Part 1 – What it is and WHY and How o What is redirected recovery? o Why might we use it? o How do we use it? Example JCL 2. Db2Z and Redirected Recovery - user experience – Part 2 – Details and Caveats o Thoughts and caveats on ...
0 comments