• Guru: SQL Writes CL

  November 30, 2020 Ted Holt

  SQL is the one tool I cannot work without. Take it away from me and I’ll start driving a truck for a living. Naturally I’m eager to find more ways to make SQL work for me. Today I’d like to share how I recently used SQL to write a huge CL command for me. This is a technique that’s good to know.

  My challenge was to copy all of the several hundred physical data files in a library to a save file so that those files could be loaded onto another IBM i system. The Save Library (SAVLIB) command was …

  Read more

• Additions To The Db2 Web Query Family

  November 9, 2020 Alex Woodie

  Change is in the air for Db2 Web Query, the primary business intelligence tool for IBM i environments from Big Blue. Earlier this year, IBM launched two new versions of the product, and this fall, IBM delivered version 2.3 of the product. Finally, there’s been a change of ownership of the third-party company behind the software.

  Let’s start with the change in ownership. Last month, TIBCO Software announced its intent to acquire Information Builders, the privately held New York City business intelligence software company that Gerald Cohen founded way back in 1975. IBI’s primary product is WebFOCUS, which forms …

  Read more

• IBM Delivered An RDi Update, Too

  November 4, 2020 Alex Woodie

  Next week, IBM is planning to deliver the PTFs that include the functionality contained in the Technology Refreshes it just unveiled for IBM i 7.3 and 7.4 last month. You will want to stay on the lookout for those updates. But some of the capabilities that IBM “announced” in the TRs shipped months ago, including those for Rational Developer for i (RDi).

  The latest release of RDi, or version 9.6.0.8, was included as a feature for IBM i 7.3 TR9 and IBM i 7.4 TR4. IBM, of course, announced those TRs on October 6 and says it plans to …

  Read more

• Guru: SQL and Expanding Subfiles

  October 26, 2020 Ted Holt

  I’ve heard it said on more than one occasion that SQL does not work as well as record-level access (RLA) when loading subfiles. I understand why people feel that way. They’re usually thinking about repositioning to a key value, and there is no SETLL (Set Lower Limit) op code in an SQL cursor.

  Yet I think SQL is better, and today I’d like to share one case that I think presents a good illustration. I had in mind the expanding subfile. There’s a parallel between the two. Consider:

  • Every time you clear the subfile, you open a new cursor.
  • Every

  …

  Read more

• Guru: Concerning The Stepping-On Of Feet

  October 19, 2020 Ted Holt

  It has come to my attention that once again I did not tell the truth, the whole truth, and nothing but the truth. In Three Suboptimal I/O Practices, I said that a simple SELECT INTO was preferable to a cursor that fetches one row. It turns out that there is at least one situation in which SELECT INTO will not serve the purpose, and one has no choice but to use a cursor that fetches one row.

  The situation is this: the program must lock the fetched row for update. Despite its power and simplicity, SELECT INTO cannot lock …

  Read more

• Db2 And SQL Services Get Upgrades With TRs

  October 12, 2020 Alex Woodie

  Among the biggest enhancements that IBM is bringing to IBM i with the latest batch of Technology Refreshes are new capabilities added to the integrated Db2 database and, specifically, the slew of SQL-based services that retrieve all types of data from the platform for IBM i professionals.

  As we previously told you, IBM has decided to rename SQL Services, which were the collection of pre-defined SQL queries that recreate traditional IBM i commands and sometimes bring something entirely new. The growing gaggle of services (or perhaps it’s a herd?) are now being officially referred to as IBM i services, …

  Read more

• How The Latest TRs Bolster The Core IBM i OS

  October 7, 2020 Alex Woodie

  Yesterday was Technology Refresh (TR) day for IBM and its IBM i operating system, and Big Blue didn’t disappoint. The company unveiled new releases of IBM i 7.4 and 7.3 that bring a slew of new capabilities to the core operating system as well as surrounding offerings like Db2 Mirror, ACS, and RDi. In this article, we’ll look at enhancements to the core OS and related key features.

  During a COMMON webcast Tuesday, IBM i Chief Architect Steve Will touched broadly on a number of enhancements that soon will be available in the TRs. Most of the new capabilities will …

  Read more

• Guru: DISTINCT Can Hide A Performance Problem

  September 28, 2020 Ted Holt

  When I see the word DISTINCT in an SQL query, a little red flag goes up inside my head. Not literally, of course. But it does make me pause and scrutinize the query more closely. I have found that poorly designed queries sometimes include the word DISTINCT as a final act of redemption to forcibly return the proper result set.

  The purpose of DISTINCT is to remove duplicate rows from a result set. As the DB2 for i SQL reference puts it:

  The keyword DISTINCT is not considered an argument of the function, but rather a specification of an operation …

  Read more

• The Dollars And Sense Of Business Continuity

  September 21, 2020 Timothy Prickett Morgan

  Those companies that have been in the IBM midrange market for a long time and are still alive and kicking have undergone a lot of change over the decades. But as is also the case, the core people at the company have been there for a long, long time and they understand how to leverage change to drive business and absorb change to help customers cope.

  That is one of the secrets of longevity for Datanational, founded in September 1979, which is located in the Farmington Hills suburbs of Detroit and which has always had a strong presence in …

  Read more

• Guru: SQL Can Read Program-described Data

  September 21, 2020 Ted Holt

  I thought I left program-described database files behind me in 1988. That’s when I left my last S/36 shop to begin working on the S/38. Well, I did, but not completely. From time to time I work on a system with program-described files, and even some externally described files have program-described fields. Fortunately — and I owe this to Scott Forstie — I have learned that SQL can read program-described data. Will wonders never cease?

  You may be thinking, “This article doesn’t apply to me. Our files are externally described.” You may be right. Then again, you may be wrong. …

  Read more

Previous Articles Next Articles