Blogs

By Mark Gillis

In the first part of this blog I outlined an issue I’d come across with the conferring of Role-based permissions to Groups for certain objects. Here’s one way around it and some further observations on the underlying issue.

Possible workaround

I did say I didn’t entirely like this option but it’s all I have been able to come up with so far.

If you want to enforce the structure of Users being allocated to Groups by your Windows or Unix Admins / Customers, you might have to keep the CREATE option up at the Database Administrator level. Once a View, Proc or whatever is in existence, the subsequent dynamic operations will be validated against the Group credentials and that can be operated by the Users who are members of appropriate Groups. This lets your User population stay fluid, whilst the Groups and Roles can stay relatively, if not entirely, static.

Read the blog in full.

By Damir Wilder

Damir recently found himself in a situation where he needed to expand (quickly and without downtime) the Db2 Transaction Log space in a customer’s live production database, which had been steadily filling up (for several hours) by a very long and intensive transaction. Also contributing to the log space usage were numerous other (smaller) transactions that were executing during the same time and biting off their chunks of the log space.

In his latest blog, Damir shares his solution for transaction log space usage with Db2 v11.5. Read the blog in full. 

Using take action to invoke predefined command to cancel a thread is available from OMPE’s Tivoli Enterprise Portal (TEP) interface. This requires granting Db2 authority to the user ID of the OMPE started task which for some customers is not desired and considered a security concern.

With OMPE V540 APAR PH47423/UI82806 and fixpack 5.4.0-TIV-KD5-FP0025, two new prefixes “DP:” and “D5:” are introduced to pair with the existing cancel thread command. With prefixed command, the cancel thread request issues the command using the Db2 authority of the TEP logon ID. Prefixed cancel thread commands can also be used in a situation take action to automate cancelling threads violating certain Service Level Agreements (SLAs).

The prefixed take action commands are also available with OMPE V550 APAR PH48556/UI90100 and fixpack 5.5.0-TIV-KD5-FP0005.

The newly added take actions are "Cancel Thread with Db2 Authority" and "Cancel DSG Thread with Db2 Authority".

New “Cancel Thread with Db2 Authority” Take Action

This Take Action is defined with the “DP:” prefix, and it can be used with “Thread Activity” or “Detailed Thread Exception” workspace from any TEP single Db2 node.

New “Cancel DSG Thread with Db2 Authority” Take Action

This Take Action is defined with “D5:” prefix, and it can be used with “Data Sharing Thread Activity” workspace from any TEP DSGROUP node.

Response from the Prefixed Cancel Thread Take Actions

If the TEP logon user ID has the Db2 authority to cancel a thread, the following message will be displayed after the prefixed cancel thread take action is executed:

If the logon user ID does not have the Db2 authority to cancel a thread, the following message will be displayed after the prefixed cancel thread take action is executed:

The similar responses are also displayed from D5 agent log:

Prefixed Take Action in Situation

The prefixed cancel thread command can also be used in a situation action where the Db2 thread will be cancelled after situation is triggered.

Conclusion

The newly introduced prefixed cancel thread command greatly simplifies the configuration task for DBAs to manage the security aspects regarding Db2 thread cancelling. With the prefixed cancel thread take action, the Db2 authority of the user controls whether particular Db2 thread can be cancelled or not. It is no longer needed to grant Db2 authority to the user ID of the OMPE started task.

By Mark Gillis 

Mark Gillis has been doing some migration work; porting a Db2 database to a PostgreSQL one. You could say that is going from an Enterprise strength solution to a simpler, but less expensive option, but it’s not a choice Mark is in a position to ignore.

Customers are being presented with a wealth of database options as they migrate to the Cloud, and many of them are embracing the options of simpler and less licence hungry products.

There are many positives to PostgreSQL but there are some pitfalls in attempting such a migration.

Find out more from Mark in Db2 v PostgreSQL

By Mark Gillis

In his new blog, Mark Gillis explores how to modify queries on the fly using Jupyter Notebook, widgets and interactive SQL.  

OK, fair enough, that does sound a bit specialised and will maybe put some people off. Nothing like a good blast of technobabble to send us scurrying to the coffee machine.

What this is getting at is the ability to modify queries on the fly. It’s one of the reasons I’m quite taken with Jupyter Notebooks; the ability to provide some SQL and to run it, take a look at the result set and then tweak the original SQL and re-run it until you get the output you want.

Read the blog, Jupyter Notebooks, Widgets and Interactive SQL in full. 

Congratulations to John Campbell, Db2 for z/OS DE, on his Retirement! Living Db2 Legend!

John Campbell IBM DE for Db2 for z/OS will be retiring from IBM on the 5th of August 2022 after four decades working on IBM Db2 for z/OS. He has been the face of “World of Db2” for decades and I know he will be greatly missed by all of us. 

John started working on Db2 for z/OS Version 1 in the 80's, here is a brief summary of John's career with Db2 for z/OS.  John has worked with many, many Db2 customers, business partners and IBMers over the decades, this is an opportunity for everyone to say goodbye to John before he leaves IBM.

John is a living legend of the Db2 community, especially a familiar face at conferences, World of Db2, IBM and IDUG Events, supported Db2 for z/OS books, Redbooks, whitepapers etc. He will greatly be missed at events, conferences, panels etc 

John has attended and presented at nearly 60 IDUG conferences in North America, EMEA, Australia, South America and India.  He has been voted regularly as the “Top 10 Speaker" on majority of these conferences and been voted “Best "Presenter” at least 3 times and entered the IDUG Speaker Hall of Fame. He has presented many, many Db2 for z/OS webcasts over his career, I have personally worked with John for the past ten years and his webcasts have always achieved up to 1000+ registrations and which is a record for webcasts. 

He has also supported many, many Db2 regional User Groups all over the world.

I know John really wanted an opportunity to meet many of his customers, partners and Db2 family before his retirement and say "goodbye". However, with "Covid Pandemic” it does not look like it is possible, so we have arranged a virtual party.

Check out some of the highlights and memories of the last four decades Click Here

If you have any photos that you would like please upload to this community. Please send your wishes and memories, I will try and print and share in his retirement card. 

John started working on Db2 in the 80's as started his career as IBM Systems Engineer. - here is a brief backgrpound of JC's career. JC's words 

• Technical branch system engineer helping the early customers migrate and exploit Db2 V1R1 thru Db2 V2R3. Helped design and implement major operational and decision support systems.
• Top system engineer in his IBM System Engineering School.
• Lead subject matter expert for Db2 in IBM UK Country Technical Support. Major contributor to IBM Redbook projects, and UK and Intentional Conference.
• Assignment to Db2 Lab in San Jose working as a performance analyst for Db2 V3 and V4. Technical leader in Performance Team for the design and development of Data Sharing and Type 2 Index Manager.
• Appointed IBM Distinguished Engineer and entry to the IBM Academy of Technology in 2003.
• Several major IBM Outstanding Technical Achievement Awards including for data sharing.
• Technical leader of the Db2 Worldwide SWAT Team since 1999.
• Voted best speaker at multiple major IDUG Conferences around the world, and member of the IDUG Speaker Hall of fame.
• I hope I will be remembered for my deep technical knowledge, my enthusiasm, customer handling skills, inspiring others, and being generous with my knowledge and time in helping colleagues and customers. Most importantly as the voice of the customer and the voice of reason within Db2 Development.

Join the IBM team at the IDUG North America 2022 Db2 Tech Conference in Boston on Monday July 11th for a complimentary, action-packed day created by developers, for developers. You'll have the opportunity to learn about Db2's app development and experience hands-on Db2. 

REGISTER FOR DEVELOPER DAY HERE

Date: Monday, July 11th 

10:00 AM - 3:00 PM EST

Where: In person only, Sheraton Boston 39 Dalton Street, The Fens - 5th Floor
Boston Massachusetts

During this time, you'll learn:

• Training on Db2 platform
• How to build and deploy a music recognition iOS app with Db2 Warehouse
• How to build a movie recommendation engine with Db2 ML
• And much more!

Agenda: 

10:00am: Why Db2
10:15am: How to download, set up, and install Db2
10:45am: Db2 Architecture Overview
11:15am: Building and deploying a music recognition iOS app with Db2 Warehouse
1:00pm: Building a move recommendation engine with Db2 ML

We are looking forward to a wicked awesome time at the IDUG NA22 in Boston, so sign up today to join us!
If you have any questions regarding this course, please email Donna Milano. 

IDUG North America 2022 | Boston, MA | July 11-14  REGISTER NOW

Registration is Open Now!

IDUG's 2022 North America Db2 Tech Conference will be held at the Sheraton Boston on July 11-14, 2022 [some optional pre-conference events could occur on July 10, 2022]. 

This year’s North America event is a hybrid conference: you can register to attend on-site or to participate online. Register now for the conference, then (if you will be participating on-site) reserve your lodging at the Sheraton Boston!

Conference Sponsorships Available... 

Connect with your customers at IDUG 2022 NA! Click here for the sponsor prospectus that explains exhibit and marketing opportunities to share your expertise with our audience. IDUG will be bringing together the global Db2 community for four days of in-person exploration and collaboration in July. Reach out to us at cyndi@teamycc.com to learn about all our opportunities.

Schedule & Speakers Announcements Coming Soon...

IDUG’s North America Conference Planning Committee (CPC) is finalizing the schedule and speakers for IDUG 2022 NA. If you have any questions about the schedule and speakers, please reach out to CPC member Chris Muncan at chris.muncan@gmail.com. 

By Mark Gillis

This should be pretty straight-forward: you can look in the TABLES System Catalogue and find references for the data (TBSPACE), Indexes (INDEX_TBSPACE) and Large Objects (LONG_TBSPACE). But DB2 throws a few curved balls here; partitioned tables (where the data, indexes and LOBs can be in multiple tablespaces), particular types of index that don’t seem to be in the catalogues at first glance, etc. Let’s see if we can put something together that shows the complete picture.

Overview

I’ve got a little database with all sorts of weird and wacky objects. It’s tiny, in terms of volume, but includes row and column-organized tables, range-partitioned tables, MQTs and a bunch of other stuff. Some tables have the full “INDEX IN … LONG IN ….” Tablespace definitions but don’t actually use them, some don’t have any or all the tablespace directives. I want to be able to see the full picture, so how do I go about that?

Click here to find out. 

    Don't Miss the IBM Key Note at IDUG EMEA 2021 Db2 Tech Conference

In part one of this blog James Cockayne looked at what might happen to a DB2 database that was attacked by ransomware encryption.  

In part two James shares four dos and four don’ts to help protect DB2 databases from ransomware attacks.

https://www.triton.co.uk/ransomware-and-the-db2-database-part-two/

By Mark Gillis

There are easily accessible means of checking what your Stored Procedure needs in the way of dependent objects (SYSCAT.ROUTINEDEP, basically). So, what if you find a, or a number of, Stored Procs that are marked as needing a REBIND and then, when you do that rebind, you get an SQL0440 indicating that “something” is missing. How do you go about checking that situation out? Find out here

By James Cockayne

By now I’m sure everyone has heard of the malicious practice known as ransomware attacks, where miscreants break into a corporate network and encrypt data before demanding huge sums of money to provide a method to decrypt that data and make it accessible again.  The attacks tend to be insidious – sometimes the attacker is in the network for months before they gain access to the systems they are interested in, and they are known to target backup servers as well as the primary systems to cause maximum inconvenience to the target organisation. 

Find out what an attack on a DB2 Database would look like. Continue reading James Cockayne's latest blog. 

https://www.triton.co.uk/ransomware-and-the-db2-database-part-1/

Hurry time is running out  register for this live webcast including client interview  Register now

Earlier this year, I hosted a client panel with several of our Db2 12 clients about the sucesses and migration of Db2 12, this event is now available on replay. 

.

In-Database AI Client Experiences with Db2 for z/OS + Demo 

Don't  miss this live webcast on 2nd November 2021 - 11:00AM EST
Tom Ramey will highlight some of the key challenges facing Db2 for z/OS clients and how AI is a breakthrough technology, that when applied to Db2 for z/OS performance management and resiliency can have a major impact. Tom will be joined by Benny Van Straten from Rabobank and Tom Beavin from IBM.  Tom Beavin will share Db2 AI use cases and host a live Db2 AI demo.Rabobank is a Dutch multinational banking and financial services company; Benny will share first-hand experiences and lessons learnt around Db2 AI for z/OS and the power of in-database AI.

 What will you learn by attending this webcast?

• Hear first-hand client feedback and experiences
• Learn how Db2 AI improves SQL performance using machine learning based on unique patterns found when executing the queries in a production environment. 
• Learn how Db2 AI automatically detects SQL access path performance regressions and automatically restores the performance back to its optimal level
• Learn how Db2 AI automatically stabilizes dynamic queries with their optimal access path, reducing prepare overhead
  
  

Tom Ramey IBM WW Director, Data and AI on IBM Z

Benny van Straten IT Specialist/DB2 Rabobank

Akiko Hoshikawa IBM Distinguished Engineer

Tom Beavin IBM Db2 AI for z/OS Development Machine Learning and Optimization

In DB2 12 for z/OS, DRDA Applications and Application Compatibility Part Two Gareth Copplestone-Jones provides guidance on the implementation of server-side configuration.

Server-side configuration

When considering how to manage managing Application Compatibility – APPLCOMPAT – for your distributed applications which use the NULLID packages, the main alternative to client-side configuration (discussed in the previous article) is server-side or DB2-side configuration. Although not without its challenges, the advantage of server-side configuration is that much of the necessary configuration is done in one place, using system profiles. Continue reading part two. 

Introduction

This, the first of two articles on how to manage the Application Compatibility level for DRDA applications, provides an introduction to the subject and considers two of the ways of doing this. In the second article Gareth Copplestone-Jones will concentrate on perhaps the most promising method and discusses its drawbacks.

A very brief history of Application Compatibility

With the release of DB2 11 for z/OS, IBM introduced Application Compatibility, which is intended to make migration from one DB2 release to another less burdensome by separating system migration from application migration, and by allowing you to migrate applications individually once system migration has completed. Application migration is managed using two controls: the APPLCOMPAT BIND option, with a default option provided by the APPLCOMPAT system parameter; and the CURRENT APPLICATION COMPATIBILITY special register.

The original announcement was that DB2 11 would support the SQL DML syntax and behaviour of both DB2 10 and DB2 11, and that DB2 12 would support that of all three. Then along came DB2 12 with Continuous Delivery and Function Levels.

Application Compatibility was extended in DB2 12 in two ways: to support function levels as well as release levels; and to support SQL DDL and DCL as well as DML. It still supports an Application Compatibility setting of V10R1.

One of the big practical issues with Application Compatibility has always been how to manage dynamic SQL packages, and in particular how to manage the NULLID packages used by DRDA clients connecting via DB2 Connect or the IBM data server clients and drivers. That’s what this article is about. Continue reading

DB2 for z/OS delivered native REST services support in the end of 2016. I wrote 2 white paper on how to create a DB2 REST service and how to consume this service from a mobile device. I start getting enquiries on how to consume a DB2 REST service from a node.js application. In the following blog, I am going to share my experience in implementing a node.js application to invoke a DB2 REST service.

https://www.ibm.com/developerworks/community/blogs/e429a8a2-b27f-48f3-aa73-ca13d5b69759/entry/Node_js_Application_and_DB2_REST_services?lang=en