• Guru: Quirky SQL Creations

  December 11, 2017 Ted Holt

  Hey, Ted:

  We are creating a view from a source member using the Run SQL Statements (RUNSQLSTM) command. None of the objects are qualified in the source member. The system always creates the view in the wrong library, no matter how we set the current library. Can you tell me what is happening?

  –William

  William ran up against the quirky behavior of the SQL CREATE VIEW statement. It sure threw me for a loop. I would have thought that the view would be created in the current library. Not so. William found the answer to his question in the IBM …

  Read more

• VAI Covers All the Bases With Modernization

  December 6, 2017 Alex Woodie

  There are a lot of ways to “modernize” your IBM i application. You could create Web and mobile user interfaces. You could adopt free form RPG and migrate the database to modern SQL. You could host it all on the cloud. You could integrate analytic capabilities into it. Or, if you’re the IBM i ERP software vendor VAI, you could do all those things.

  VAI has, in fact, done all those things leading up to this year’s release of S2K Enterprise 6.0, and according to CEO and founder Bob Vormittag, the company is reaping big rewards as a result.

  “We’re …

  Read more

• Guru: Using SELECT * With Cursors

  November 27, 2017 Ted Holt

  From time to time someone brings to my attention the use of SELECT * with SQL cursors in RPG programs. Specifically, is that a good idea or a bad idea? I have learned that the answer to that question is “It depends.” Using SELECT * in a cursor declaration may or may not get you into trouble.

  To set the stage, let’s begin with a simple example — an RPG program that reads one table (physical file) and prints each row (record). Even though most programs use data from more than one table, programs that read only one table are …

  Read more

• Guru: Faster Fetching, Revisited

  November 13, 2017 Paul Tuohy

  While visiting a client recently, I was shown how they were using a multi-row FETCH into a multiple-occurrence data structure to retrieve large sets of data. When I asked why they were not using a data structure array instead, I was referred to an IT Jungle article in which Ted Holt answered a question on whether it was faster to perform a row-at-a-time FETCH or a multi-row FETCH when using embedded SQL.

  Ted provided a comparison between a row-at-a-time FETCH, a multi-row FETCH into a data structure array, and a multi-row FETCH into a multiple-occurrence data structure. At the end …

  Read more

• Database Modernization: Methodology To Solve Problems

  November 6, 2017 Dan Burger

  Innovation is a combination of tools and processes. Big mistakes get made when there is too much emphasis on the tools and too little on the processes. Innovation in a can, a bottle or a box isn’t reality-based strategy, although it’s often thought of that way, particularly at the executive level where the goal of competitive advantage is sometimes tied to specific products and technologies.

  Tools and processes support innovation. They aren’t the innovation. Not at the individual business level where competitive advantage is differentiated and honed with years of experience. Too often experience is discounted or even disregarded when …

  Read more

• Guru: Logging SQL Errors And Warnings

  October 30, 2017 Birgitta Hauser

  If something goes wrong with an embedded SQL statement or an SQL routine (a trigger, stored procedure or user-defined function), the system does not crash. Instead, DB2 returns a negative SQL code and an SQL state that starts with something other than 00, 01, or 02. If the routine does not handle the error, the program continues to run.

  It is good practice to log errors to a table, especially unexpected ones. Before you can log any errors, you need to know how an SQL error can be trapped and handled. You need to know how to write a condition …

  Read more

• Guru: Wow! I Could Have Had Long Column Names! – Take 2

  October 23, 2017 Ted Holt

  In 1990, three computer science professors named Gorla, Benander, and Benander wrote about debugging effort in COBOL programs. Among their claims was that debugging is easier if variable names were between 10 and 16 characters long. The original native data definition facilities allowed variable names up to 10 characters, but nowadays we can define alias names that Gorla, Benander, and Benander would be proud of.

  It is common in many DB2 for i shops to have physical files with field names of six characters or less. This practice dates to predecessor systems, such as the System/36, for which the …

  Read more

• Guru: Error Handling in SQL PL, Part 2

  October 16, 2017 Ted Holt

  In the previous episode of this exciting, action-packed series, I introduced you to the exception-handling methods that IBM has built into SQL PL. The cliff-hanger has lasted four months now, much too long. It’s high time I explained RESIGNAL, as I promised I would. I will explain SIGNAL while I’m at it.

  Before I start the syntax lesson, I need to explain a bit of philosophy. I use the hit-the-ball-drag-Harry method of exception-handling when I embed SQL in RPG and COBOL programs. That is, I execute a command, test the SQL state, execute a command, test the SQL state, …

  Read more

• Guru: Making Sense of Codes

  October 9, 2017 Ted Holt

  I have most likely never seen your database, yet I can tell you with confidence that it is full of codes. We can’t live without them. Codes give us shortcuts for all sorts of types and categories. They consume less storage than the values they represent. They help us keep the database clean and consistent within itself.

  But they surely can be hard to read. Some codes are obvious. M for male and F for female, for instance. My experience is that most are not so. Look at this and see how much sense you can make of it.

  select 

  …

  Read more

• TRs for IBM i 7.3 and 7.2: Enhancements, No Big Surprises

  October 4, 2017 Dan Burger

  With each Technology Refresh, we are reminded that the pace of enhancements IBM brings to its IBM i operating system and related software products is significant and that certain areas are more significant than others. So, the IBM i community is either aligned with these upgrades and eager to put them to use or it’s not yet ready, willing or able to be technologically current. As usual, there are IBM i shops watching as the enhancements unfold and making decisions on whether the enhancements can deliver benefits to their business in terms of productivity and solving business challenges.

  Steve Will, …

  Read more

Previous Articles Next Articles