Re: Database I/O Modernization -- MIDRANGE-L

Just a question for the SQL advocates:

In the original example there was also reading previous and next, which means using a cursor in SQL.

What is the performance impact of opening and closing a cursor compared to the traditional RLA approach?

Just curious.

Regards,
Carel Teijgeler

Op 15-9-2017 om 8:30 schreef D*B:

<Nathan>
Now that the original file is referenced in hundreds, or perhaps thousands
of programs, the cost of changing the design would probably be very high.
Would it have helped if SQL had been used instead of "F" specs?
</Nathan>

Yes, it would help!!!
First step of refactoring database would be to eliminate all access to tables, replacing it by views. (no impact to any programm!!!)
now you could start to make first steps of normalisation. For example: seperate Shipment financial transactions: as long as you could provide your views in the view layer, with the same behaviour, your application won't even need recompiles, it would run as expected. Introduction of instead triggers was a great step forwards for refactoring database. Without instead triggers views based on joins lost update capability and refactoring was more complicated.

From the starting point of refactoring, new functions could use the Shipment financial transactions view. If an old programm needs modification, you would put some additional programming effort for refactoring to the modification effort. This could be to seperate access to the ancient view layer to the refactored view layer. Very important to keep in mind: never ever use rla!!! never ever use tables!!! use SQL to access the view layer only!!!

@Buck and select * : The important thing of select * into :someDS or select fieldlist into :varlist
The problem for fieldlists in SQL statements is the correct sequence of the fields. If you have a logical mismatch between fieldlist and valuelist (could happen for select and insert), you might have failures, you would not trap at compiletime and you would not trap immediate at runtime. You would simply notice some corruption in your data and it would be hard to find the programm with the bug and even harder to correct the corruption of your data.
On AS/400 and it's external DS capabilities, this could be avoided by using select * and the corresponding external DS. So I would normally recommend and use this. To avoid to get more tight coupling, another rule would be: never ever change a view (if you need additional fields, add a new view and from time to time, think about delteion of no more needed views). BTW: changing views isn't a good idea anyway, what about the programms using the old? Even adding fields at the end could have impact (think about insert...), or someone using an external DS (think about when the problem would arise!!!).

D*B