Get empowered with IBM Power
It’d be a dream to speak here. I just submitted a talk about the extension and how we’re building a community around open-source for #ibmi, as well as how the extension opens the platform up for many years to come twitter.com/github/status/…
– Liam barry (@notesofbarry)11:51 – Jun 06, 2022Quoted Tweet:
Save the date! GitHub Universe is back this Nov. 9-10 for our first-ever hybrid conference experience 🥳
Learn more: github.co/3B97QxH
– GitHub (@github)13:30 – Jun 03, 2022
Great post written by Kent Milligan @ ibm on 7.5 Db2 for i additions db2ibmi.blogspot.com/2022/05/whats-…
– Liam barry (@notesofbarry)10:49 – Jun 06, 2022
This entry title harkens back to the Got Milk? advertising campaign. Who would have guessed that this campaign goes back almost 30 years?!? As they say, time really flies… now, back to the topic at hand.
If you’re reading this entry, hopefully you already know that the CTE acronym in the SQL world stands for Common Table Expression. Even if you already knew what a CTE is, you may not understand what the term “shared CTE” is referring to. A shared CTE is the term used to describe any CTE that is referenced more than once on a query definition.
Here’s an example of a query that contains a shared CTE. In this query, the staff CTE is referenced twice in the main query definition which qualifies the staff CTE to be categorized as a shared CTE.
In contrast, the following query contains multiple CTES, but no shared CTEs. Each of the CTEs (top10_2020 & top10_2021) are only referenced once in the main query, so they don’t meet the criteria of a shared CTE.
Knowing whether or not a CTE is a shared CTE is significant because an SQL Standards compatibility fix was recently delivered in the IBM i 7.5 release for SQL statements with shared CTEs. This fix had to be made because there’s a possibility that some queries with shared CTEs may return incorrect results if the queries are run while the tables referenced by a shared CTE are being changed. The fix will guarantee as dictated by the SQL standards that each reference to the shared CTE generates the same result set. The fix delivered by IBM may cause some queries with shared CTE references to run slower and some queries with shared CTEs to run faster.
Now, you may be thinking that it will be a long time before your company installs the IBM i 7.5 release, so why pay attention to this change. The reason that you should pay attention is that the code fixes for shared CTEs that were made for the IBM i 7.5 release will eventually be delivered as PTFs for the IBM i 7.4 releases IBM recommends analyzing queries with shared CTEs before the PTFs are delivered so that clients understand the possible impact of these PTFs and have time to change their SQL statements, if needed.
To help with this analysis effort, IBM in late 2021 delivered PTFs for IBM i 7.3 and 7.4 that flag SQL statements with shared CTEs in both Plan Cache Snapshot and SQL Performance Monitor collections. In addition, these flags classify whether or not a Shared CTE is estimated to generate a large result set. Shared CTEs that generate a large result set have a greater chance of having performance issues after the fix is delivered as compared to CTEs with a smaller result set size. However, all SQL statements using shared CTEs have the potential to perform differently once the PTFs are applied.
It is possible to predict the possible performance impact of the future PTFs on SQL requests containing Shared CTEs with a simple coding change. This simple change involves adding the following predicate, AND RAND() IS NOT NULL, to the CTE that is shared (i.e., referenced multiple times) on the SQL request. This predicate forces the Db2 query optimizer to use the same CTE runtime implementation which is used by the fix in the IBM i 7.5 release.
The IBM development team has published a detailed writeup which includes details on the SQL statement flagging that will aid analysis on 7.3 & 7.4 along with possible coding changes that you may want to consider. Our Db2 team in IBM Systems Lab Services can also be engaged to provide additional assistance, so let me know if we can help.
You have IBM i applications in place running business-critical operations.
Why?
Well, since its inception, the IBM i and its predecessors (iSeries, AS400, System i) is known for certain attributes:
Exceptional business resilience with the lowest total cost of ownership in its class
Near zero downtime ensuring mission-critical applications remain up and running
Enhanced security features protecting critical business applications and data.
IBM i applications themselves can encompass pretty much anything in an enterprise, from proprietary warehouse management to bespoke ERP systems within industries as varied as financial to manufacturing.
That transactional data means that the type of applications running on the IBM i are, by their very nature, business-critical – and you really don’t want them failing.
Over the last few years, we’ve highlighted some of the key findings from HelpSystems’ annual Marketplace Surveys in a series of posts. In 2021, the survey reported that 44% of IBM i based businesses ran anything from are continuing to run between 76-100% of their core applications on the platform.
In 2022, 70% of respondents reported running more than half of their core business applications on the IBM i.
That’s an awful lot of businesses across the globe relying on their IBM i applications. And relying on them for at least the medium term to help manage their business.
And, whatever those enterprises are planning on doing with those IBM i applications, those enterprises will want to make sure that they’re still running effectively and fit for purpose, while at the same time supported & maintained properly.
Depending on your wider IT strategy, you could also be looking at further development, adding more functionality and modernising those IBM i applications to make them more relevant now and for the next five, 10 or 15 years (and beyond).
You’ve built those applications. You’ve invested time and development resources over the years.
Now those developers are starting to retire – or at least coming up to retirement age – or are being moved into other areas within your IT ecosystem.
And as our partners, Fresche Solutions reported from a survey of more than 250 senior IT personnel, that 57% of businesses couldn’t find people with IBM i development skills.
And when you consider that 91% of those organisations are facing staffing challenges either in skills or the number of people.
So, with these challenges in mind, how do you keep your IBM i applications supported, maintained and relevant in the short, medium and long-term?
For many big companies, business-critical applications on the IBM i represent a significant investment of time and effort.
Many of these systems were first put in place in the mid-80s or early 90s, so they’ve been built and developed over a period of 30-35 years.
Typically, if you look at the development curve of these applications, you’ll see a big spike of development at the beginning, with teams of multiple developers and then that tapers to the point where you end up with two or three people that have been maintaining them. The others have either left, retired, or moved on to other projects, or to different parts of the business – or different priorities.
You’re down to a couple of people doing maintenance and small changes.
Because of the nature of those applications, the languages used to programme them in, they’re not just an off-the-shelf solution.
Those applications have been changed, manipulated, and developed over the years.
Often for many, many years.
As those IBM i applications have usually been customised to suit the demands and needs of individual enterprises, it can be incredibly difficult to replace them with off the shelf solutions.
All that knowledge about those applications is sometimes documented or written down, but often it’s locked away in somebody’s head.
When that person either decides to move on or retire (or worse) nobody can pick up that knowledge and quickly continue to maintain those applications.
It’s a real-world example of tribal knowledge. And with it comes great risk.
If you’re running business-critical applications on the IBM i, you may now be down to one, two or three developers, which, I’m sure, could be a huge cause for concern.
These applications are running the business, but you’re relying on a very small number of people to know how it all works.
Often, in these situations, the first plan is to migrate away from the IBM i, but that is tough to do and tends to take longer than you think it will.
The character-based transactions (like purchase orders or sales orders) that run on IBM i applications are fundamentally still the same actually now as they were 20 or 30 years ago. The applications are reliable and have a low cost of ownership, and they’re usually very complex as well.
Though migration can be at the top of the to-do list it can often take far longer than planned, get delayed for whatever reason or, in some cases, not get finished.
These applications end up having a much longer life than anticipated, and the developers are looking potentially to retire or to move into different roles.
Because a lot of tailoring has gone into those systems over the years, they’re very complex and very bespoke to that business. It’s unlikely that you’d be able to find a replacement in off-the-shelf software, so you may be restricted with what you might want to migrate to.
Migrating off it is not a simple step.
It’s not just a case of getting another system: are you going to change your processes? Are you going to get new development done in those systems?
It can be quite costly as well.
Complexity is still embedded in those applications. You’ve either got to change your business processes, find an application that works in the way that you work, or implement an application that is incredibly flexible and configurable, which then comes with its own set of challenges.
IT managers may be keen to move to a cloud platform or a Microsoft or a Windows platform that’s much more modern, but that still requires rewriting and redeveloping what they need, and working out what exactly you do need going forward.
Moving to a completely new platform and starting from scratch can be a massive risk. In so many ways.
Even if there is a plan to migrate to other platforms – that likely won’t happen for a few years.
So IT managers, even though they may have no real interest in the IBM i as a platform, have to make sure it keeps running.
And there are plenty of options for enterprises wanting to simply ‘keep the lights on’ both onshore and offshore.
But, if you’re still using the IBM i applications for business-critical tasks, and want to do some improvements and development projects, and need to know how certain things work, then outsourcing it to a company that hasn’t got into that experience and specialist skills is high-risk.
On the other hand, even if you are moving to a new platform, you’ll take it in stages, there are a lot of complexities and implications of doing that, so you’ll need to go one step at a time.
You may look at tools, like those from Fresche, that can help with porting parts of the existing system, or taking chunks of it out, or revamping screens. Because you still have to live with it, in the meantime.
The move off to a different platform can take 5-10 years, and you need to be able to continue to use the system, make the most of it and keep the lights on during that time. Regardless of what you do, there is going to be an investment – either of time or divest the entire thing to somebody else.
By passing that onto somebody else, like Proximity, you can have control over things like the costs, and also be able to push any problems towards us, and not have to have that hassle yourself. You’re paying for the expertise and the years of experience on our development team.
The post De-risking the Management of Your IBM i Applications appeared first on Proximity.
