Over 180 folks attended the “45 Minutes to Understand IBM Replication – Sources & Targets, Lower Latency, Costs” webinar on April 30, 2020 hosted by my colleague @KimMayTFG and me.  Thank you for joining us – along with the excellent questions and feedback.

These are the presentation materials IBM Data Replication Update 2020-04-30

You’ll find the recording of the webinar here.

Dr. Sheldon Cooper of “The Big Bang Theory” regularly records a YouTube video called “Fun with Flags” so I’m going to call this blog post “Fun with SQL”.  Sheldon may be a future Nobel Prize winner, but I’ve been named an IBM Champion for Analytics and System z for 2019. 🙂

The problem statement: a large US government agency is contemplating migrating a packaged application from System z to Linux.  The current and future database repositories are Db2 for z/OS and Db2 for LUW respectively.  As a System z Champion I’ve presented on the continued relevancy and efficacy of the IBM mainframe, but that’s not the focus of this post.  One of the challenges of porting data is the conversion of character encoding from EBCDIC (Extended Binary Coded Decimal Interchange Code) which is the legacy encoding protocol used on IBM mainframes and System i to Unicode.  Db2 for z/OS supports Unicode, but I’m not going to address anything other than EBCDIC encoding at the source and Unicode at the target (Sorry, ASCII).  In brief, EBCDIC represents an 8 bit binary encoding scheme that can represent 256 different characters.  For example: the letter “F” (one of my faves) is represented as 1100 0110 in binary and C6 in hexadecimal. For national language support amid the variety of Single Byte Character Sets (SBCS) used around the world – think the German umlaut (i.e. ä ö ü) – the 256 byte universe of EBCDIC wasn’t nearly enough.  The Unicode solution is to use more than 1 byte to represent some characters.  For example: the “¢” cent sign takes one byte to represent in EBCDIC, but 2 bytes in Unicode.

select length(ebcdic_str('¢')) as EBCDIC, length(unicode_str('¢')) as Unicode from sysibm.sysdummy1;

EBCDIC    UNICODE

     1          2

This problem has manifested itself as an error when moving a CHAR(5) column of data from Db2 for z/OS to Db2 for LUW.  If, say, the pesky “¢” is present in the string and all 5 characters are significant (i.e. no trailing blanks), the string won’t fit in the same column width on the target platform.  Most customers find this out the hard way when the Db2 for LUW LOAD command or IMPORT utility posts errors.  There are two possible solutions:

• data cleanup (Do we really need the pesky ‘¢’ in a Comments column?)
• lengthen the target columns to accommodate Unicode expansion

Both can be time-consuming and expensive and will depend on a variety of factors beyond the scope of this blog post.  The purpose here today is to identify the extent of the problem before you actually try to port the data.  I’ve constructed this SQL statement to do just that. Read More…

A customer came to me recently looking for Db2 Express-C which he had previously used to distribute to developers in his organization.  I couldn’t find Db2 Express-C v11.  Was there no longer a current free version of Db2 for developers and academics?!?!?

Seems it has just been rebranded and expanded.  Now there’s the Db2 Developer Edition available for free that’s even better.

1. There are three form factors:
   1. Code that you can install yourself on bare metal or in a virtualized environment.
   2. A Docker container.
   3. A fully-managed DBaaS cloud implementation.

These are fully-featured editions of Db2 for a proof-of-concept or even a small production implementation.

Caveats:

• Free means support comes from your peers, not IBM.
• You are limited in terms of data stored and memory exploited.

You can acquire support and eliminate the limits for a Db2 Developer Edition by purchasing and applying a license key.  No re-installation or data migration will be necessary.

Get started with Db2 today.

On Monday, October 1, 2018 from 9:00 a.m. to 4:00 p.m. EDT IBM will stream Day 1 of the IBM Gold Consultants briefing “Live from the Lab” from the IBM Toronto software development lab.  This is free and open to everyone.

Topics include:

1. Db2 for LUW v11.1.4.4
2. Db2 Event Store
3. IBM Integrated Analytics System (IIAS) – Db2 Warehouse appliance
4. Db2 on Cloud
5. Db2 Virtualization
6. Db2 Tools
7. Open source DB (MongoDB and PostgreSQL)

Here is the link to register

On July 23 and 24, 2018 The Fillmore Group delivered a hands-on IBM Db2 Analytics Accelerator (IDAA) v7 Workshop to 12 students across 5 different enterprises at the IBM Dublin, Ohio (USA) Technical Education Center.  This was the first IDAA v7 hands-on Workshop delivered to customers anywhere.  Many thanks to Atlee, Bill, Bob, Dom, Emmi, Larry, Marshall, Mohammed, Naga, Ruth, Shawn, and Toby for all of the great questions.  Kudos to the IBM Poughkeepsie Benchmark Center for standing up the IDAA v7 environment in record time with just-released functionality like Incremental Update.  And thanks to IBMers Rich Gast, Mike Hood, Sandro Ramirez (aka “Nicky’s Dad”), Julie Efthymiou, and Rudy Benvenuto for all of their support.

Finally – and most importantly – the Workshop’s flawless execution from scheduling, invitations and outreach to customers, to welcome packets for travelers, to food service, and overall logistics was the direct result of many months of hard work on the part of my colleague, Kim May.  I really appreciate it!

The presentation materials are here:IDAA Workshop 2018-07

Since IDAA v7 is so radically different from it’s predecessors, the Workshop generated lots of questions which will be incorporated into future sessions.  Watch this space.

On Tuesday, January 13, 1987 I sat in the kitchen of my 11 foot wide rowhouse in the Highlandtown section of Baltimore and typed out on a manual typewriter the Articles of Incorporation for The Fillmore Group.  My son, Nathaniel, was five weeks old.

Some back-of-the-envelope metrics across the past 30 years:

• approximately 300 clients
• about 60 consultants have helped train these customers and implement their IBM Analytics software
• As a consultant, instructor, and/or presenter at technical conferences I have traveled to approximately 20 countries.  Prague is my favorite foreign city; I’ve been there three times and look forward to returning.

I have had two consultants, Ray and Jim, tell me that working for The Fillmore Group “has changed [their] life”.  In the beginning it was a common occurrence that an employment verification form from a mortgage underwriter would arrive shortly after a consultant started with TFG.  I know of several consultants that purchased their first home while working with us.

My favorite work environment was The World Bank.  The cosmopolitan atmosphere – working with colleagues from all over the planet – was delightful.  I helped implement a telephone billing system there using SQL/DS.

My most satisfying project was the Oracle to DB2 migration at JP Morgan Chase.  It was a tough, demanding environment with a timeline that was half of the 18 months originally estimated.  But we got it done with the help of Jim, Joe G., Joe L., John, Rebecca, and Teresa.  The customer had an equally talented, hard-working team.  Our contribution to that project was recognized by IBM SVP Steve Mills at the Insight conference that year.

As a small business owner, my favorite question from an IBMer (Scott): “Since the deadline can’t change, if money were no object what would you do?”  The answer: supply five more consultants to the project.  It was completed on time – if not under budget.

As a technician, my favorite question from an IBMer (Hunter) in the parking lot after a detailed technical presentation to a customer involving replication and federation tools: “We can do that, right?”  The answer: yes we can.  It resulted in a massive data warehouse at a large federal government healthcare agency.

I have had the privilege to learn from and share friendships with some of IBM’s original developers of relational database technology including Pat, Curt, Don and Don, and Hamid.  For IBM’s Hybrid Transaction/Analytic Platform (HTAP) implementations of today I rely on Paul, Tim, Gary, Namik, Knut, Patric, and Udo among many, many others.

I also cherish the friendships I have formed with other DB2 Gold Consultants like David, Ted, Bonnie, Jan, Juergen, Julian, Jackie, Kermit, Sheryl, Susan – and especially Gerry.

IBM has been a maddening company with which to partner, but the vast majority of the time The Fillmore Group has benefited from the technical excellence and basic decency of IBM employees.  In return, TFG consultants like Roger and Ravi have delivered innovative, cost-effective IT solutions to our mutual customers.  After more than two decades I still rely on Roger for his calm, wise counsel.

There have been tough times, too.  The Fillmore Group overcame at least three financial near-death experiences (1993, 2002, 2006).  At the risk of tempting fate: we never missed a payroll or failed to pay a debt on time.

But the best time, by far, was the day Kim May arrived to rent The Fillmore Group’s technical training classroom in downtown Baltimore in 2003.  That was the day my life changed forever.

This is reverie, not valedictory.  I am still too young to retire.  And I am as jazzed about the possibilities of HTAP, data repositories, and data interoperability (aka “plumbing”) in 2017 as I was when I first started working with SQL/DS (maximum storage capacity: 64GB) in the mid 1980s.  We’re currently working on a database rehosting project for a local government targeting DB2 for z/Linux and have an IBM DB2 Analytics Accelerator (IDAA) v6 Workshop scheduled next month.

But I wanted to take a moment to jot down a few thoughts on the past three decades.  Of course, I haven’t called out everyone by name who has contributed to our success over the years.  But I truly, sincerely appreciate your hard work, dedication, integrity, smarts, and moxie.  Thank you!

In closing, when Curt retired from IBM I asked if he was pleased with a career that had reached a pinnacle as an IBM Fellow.  His response: “It turned out better than I ever could have imagined.”  Me too.

On Monday, March 14, 2016 DB2 Gold Consultant and IBM Champion Frank Fillmore will present on Hybrid Analytic/Transaction Processing (HTAP) at the Heart of America DB2 Users Group in Overland Park, KS.

A complete agenda, presentation abstracts, and speaker biographies can be found here.

The concept of Hybrid Transaction/Analytic Processing was coined by Gartner. IBM implements HTAP in two ways:
– IBM DB2 Analytics Accelerator (IDAA) for DB2 for z/OS data (using Netezza hardware)
– DB2 BLU Acceleration – DB2 for Linux, Unix, Windows (a software-only implementation)

Click here for the recording of the webinar.  The presentation materials: Hybrid Transaction Analytic Processing – HTAP

Many thanks to my colleague, Kim May, and IBMer Bill Kincaid for an excellent presentation on DB2 for LUW feature/functionality and pricing advantages over competing offerings.  And thank you to the many IBMers and customers that attended.  We received great feedback from you during the poll questions and the Q&A.

To view the webinar click here.

The presentation materials are here: Top Three Reasons DB2 is Best Presention 9.25.14

Performance tuning for both DB2 and Q Replication are an ongoing process.  However, there are three tools which provide an initial set of recommendations for bufferpools, Database Manager (DBM or instance) and database configuration parameters, indexes, and other application table objects.

DB2 Configuration Advisor

The DB2 Configuration Advisor recommends the initial values of the bufferpool size, database configuration parameters, and database manager configuration parameters.

To use the Configuration Advisor, specify the AUTOCONFIGURE command for an existing database, or specify AUTOCONFIGURE as an option of the CREATE DATABASE command.

Example:

db2 autoconfigure using mem_percent 50 num_stmts 100000 tpm 6000 admin_priority performance num_local_apps 144 num_remote_apps 0 isolation UR apply none

DB2 Design Advisor

The DB2 Design Advisor is used to improve application SQL performance by creating (or dropping) indexes, Materialized Query Tables (MQTs), and MultiDimensional Clustering (MDCs).  The Design Advisor analyzes an SQL workload against existing table structures and recommends changes to improve performance.  The workload can be derived from Dynamic Statement Cache (DSC).  The DB2 Design Advisor command can be invoked by the db2advis command.

A prerequisite is creating the DB2 EXPLAIN tables for the schema conducting the analysis.

cd /<path>/sqllib/misc

db2 –vtf EXPLAIN.DDL

Then run the db2advis command.

db2advis -d mydb -g -a myuser -x -c USERSPACE1 -f -m IMCP -o db2advis_output

Q Replication Dashboard Performance Advisor

The Q Replication Performance Advisor helps you identify latency issues with your replication environment and suggests ways to lower end-to-end latency.  The Performance Advisor tab is located within each Monitoring Group.  The Q Replication Dashboard Performance Advisor can recommend updating statistics or reorganizing tables.