Guru: More End Of Year Feedback

December 9, 2019 Ted Holt

You are busy. The people you serve need you to do more than one human being can do. You don’t have time to look for comments or updates to the articles we run in this august publication or any other. For this reason, I was pleased to publish some of your feedback in last week’s issue. This week I am pleased to share a bit more.

In response to Guru: MERGE, Chicken, and Eggs, John asked a good question and made a good point:

How is using this merge technique under commitment control any different than just doing the original update and insert directly under commitment control? Also, IBM now natively supports temporal tables, so that could be an alternate way to solve this problem, although queries against older time frames would need to be done using temporal based SQL syntax.

As far as commitment control is concerned, there is no difference. Whether you use an insert followed by an update, an update followed by an insert, or a single merge, you need to run the transaction under commitment control so that the system can back out individual transactions if the transaction as a whole is unsuccessful. Using merge only solves the problem of deciding which runs first — the insert or the update. I mentioned commitment control only to dispel the false notion that merge is an all-or-nothing transaction. Without commitment control, merge can perform a partial transaction, corrupting the database.

Temporal tables, for those who aren’t familiar with them, are tables that can be queried as of a certain point in time. I have an academic understanding of temporal tables, but no production experience with them, so I don’t have an educated opinion about whether the use of temporal tables would be an appropriate way to handle the scenario I described or not. From what I know about them, I believe temporal tables would work very well, as standard costs and catalog prices typically do not change often.

We’ve not covered temporal tables yet only because I have not found anyone to write the article. I could write something that describes the techniques, but I need more than that. I need someone who has real-world experience to write that article. I need someone who can tell me and all the other readers when to use and when not to use temporal tables. Finding such a person is not easy, and finding such a person who can write an article and has the time to write an article increases the difficulty. Will someone volunteer?

In the meantime, the interested reader can find plenty of information about temporal tables on the Web, but most of what I’ve seen has concentrated on the mechanics of using them, not on proper usage. Here’s one article, written from a Microsoft SQL Server perspective, that is an exception.

Regarding Jon Paris’ treatise of varying-length character variables, Chris asked:

Any chance you can do same article for COBOL, Jon?

Jon replied that there is no direct support for varying-length variables in COBOL. Before I took my present job, I worked in a predominately COBOL shop, and out of curiosity I found a way. This is academic — I’ve never used this technique in production.

The trick is to define a group item in COBOL with the same structure as a varying-length character variable in RPG. Let me illustrate.

Suppose RPG program VARLEN2 requires a varying-length parameter.

**free                                      
                                            
dcl-pr  VarLen2           extpgm('VARLEN4');
           Message        varchar(48);      
end-pr  VarLen2;                            
                                            
dcl-pi  VarLen2;                            
           Message        varchar(48);      
end-pi  VarLen2;                            
                                            
*inlr = *on;                                
Message = 'My dog''s name is Chuckie.';     
return;                                     

Suppose further that COBOL program VARLEN1 needs to call the RPG program. Here’s the code:

id  division.
program-id.  VarLen1.

environment division.

data division.
working-storage section.

01  Message-var.
    02  Message-var-Length    pic s9(4)   comp-4.
    02  Message-var-Data      pic x(48).

procedure division.
main-logic.

    move "My cat's name is Fido." to Message-var-Data

    if Message-var-data not equal spaces
        move length of Message-var-Data to Message-var-length
        perform until Message-var-Data (Message-var-length: 1)
                         not equal space
            subtract 1 from Message-var-length
        end-perform
    else
        move zero to Message-var-length
    end-if

    call "VARLEN2" using Message-var

    goback.

Group item Message-var is defined like a varying-length character field in RPG. This example loads the message into Message-var-Data and sets Message-var-length to the message length without trailing blanks. The call passes the entire data structure.

You can also make a COBOL program receive a varying-length parameter if such suits your purposes.

data division.
working-storage section.
01  Temp-value                pic x(48).

linkage section.
01  Some-var.
    02  Some-var-Length       pic s9(4)   comp-4.
    02  Some-var-Data         pic x(48).

procedure division using Some-var.
main-logic.
    if Some-var-Length > zero
        move Some-var-Data (1: Some-var-Length) to Temp-value
    else
        move spaces to Temp-value
    end-if

A separate but related topic is that of varying-length character fields in externally described files. IBM has a Web page with more information about that. You’ll see that they handle variable-length fields in the same manner.

Jason had another idea on how to write a function to find the difference in days between two dates.

Rather than defining the function as an external SQL function in RPGLE, wouldn’t it instead make more sense to simply define the function in SQL using the code you already defined?

create function diffDays
   (ShipDate decimal(8), DueDate decimal(8))
returns decimal(8)
begin
return days(date(fmtdate(ShipDate,'CYMD', 'ISO-'))) -
       days(date(fmtdate(DueDate, 'CYMD', 'ISO-')));
end

I can’t argue with this. I’ve been a proponent of building reusable object code for decades. I’ll say a couple of things in response.

First, each subprocedure that an RPG program calls adds a new entry to the call stack. I assume the same is true with SQL. If so, then writing an RPG subprocedure that accomplishes a task directly should perform better than something that uses a lot of nested functions to do the same thing. But I don’t know this for a fact.

Second, I am accustomed to writing SQL functions in RPG, simply because many of the functions I build are specific to an installation. I can take business logic from an existing program, convert it to a subprocedure, convert the subprocedure into an SQL function, and I have a new powerful tool that I can use from within SQL. So writing the function in RPG may have been nothing more than force of habit.

All that being said, I think Jason’s idea makes more sense than mine.

In response to Guru: Addressing A Legitimate Question, thoughtful reader Bruce wrote:

When you write “Rather than insult the person who asks me such a question . . . ” you just insulted anyone who has ever asked you that question. But I’m sure that wasn’t your intent.

I’m glad you’re sure, Bruce, and I hope everyone else feels the same way. There’s so much acrimony on the Web, and I suppose it had gotten to me that day. For some reason I must have felt the need to emphasize that I don’t want to put anybody down. If I’ve ever insulted anyone, I hereby ask for forgiveness.

Thanks to everyone who took the time to post comments to the articles, to email me, or to tell me in person what they took away from anything we’ve published. I have more feedback to work through and I’ll do my best to get to it when I can.

I hope you enjoy the holidays and maybe even get a little downtime from the everyday grind.

RELATED STORIES

Guru: End Of Year Feedback

Guru: MERGE, Chicken, and Eggs

SQL Server Temporal Tables: How-To Recipes

Guru Classic: The Efficiency of Varying Length Character Variables

IBM Knowledge Center: Variable-length Fields

Guru: Easy Date Difference

Guru: Addressing A Legitimate Question