Richard, you can't beat the ease and speed of table changes using
CHGPF/ALTER TABLE. It maintains the logicals as well, and rebuilding them
can take a significant amount of time. For years, from the days of the
System/38, I changed databases with CPYF and utilities to rebuild logicals;
the introduction of CHGPF allowed me to completely redesign my process and
to speed up the process by 5x or better.
My file conversion job stream would post the new DDS source (although you
could use ALTER TABLE in a script as well), "change" the table, add new
logicals, indexes, and views, and then run a program that finds level
checks in PF's, LF's, and device files and recompiles the affected programs
(it's pretty easy; I did it originally on the System/38).
There's no avoiding level checks when changing a file's layout--running
LVLCHK(*NO) is as dangerous as junkies sharing a needle: eventually, you'll
get stuck. I have a strong object naming convention and separate libraries
for source, object, and database; that simplifies creating utilities to
automate the recreation of objects. In the CISC days, a hundred big
compiles could take hours; with today's hardware, I'm compiling 10,000 line
SQLRPG programs with copy books in 10 seconds, so the time required to
compile is not usually an issue. But big systems with big databases often
have the narrowest windows for this kind of maintenance and other
approaches may be required. In many environments, the daily built is a
critical checkpoint to ensure nothing's been broken in the last 24 hours;
System i shops should, IMO, be able to completely recreate their
environment from program source files and a configuration file.
Everything, including subsystem and job descriptions.
I have a master build command that recompiles any object; that greatly
simplifies the process. I routinely change PF's and my process to change
the table and recompile the affected programs takes 5-20 minutes,
depending upon how many programs touch the DDS file (for SQL programs, I
don't "SELECT * FROM..."; I always list the specific columns I need, and
these programs don't level-check but may get SQLSTT=01004 when executing).
One non-trivial database change is converting numeric dates and times into
ISO format and I've built a tool to do it. The utility adds dummy columns
for each numeric date/time (all date and time columns are defined by a
field reference file), updates the dummy ISO columns with the numeric
values, drops the numeric column names, adds *ISO fields with the same name
as the original numeric columns and in the same relative position in the
file layout, updates the old-name-new-format columns with data in the dummy
columns, and then drops the dummy *ISO columns. Any logicals with
select/omit criteria need to be changed by hand, and of course your
triggers need to be addressed. But otherwise it's fast, comprehensive, and
recoverable should the system crash. And it doesn't require hand-written,
error-prone manually-coded programs or SQL scripts to copy data between
files. One other little tool for a change of this type--which can require
touching every program referencing a date or time field--is a copy book
with hard-coded procedures for changing an *ISO date into a *MDY date, and
accounting for dates/times with *LOVAL, and "editing" *ISO dates/times to
show blanks for *LOVAL. Those procedures simplify *and standardize* common
date/time manipulation tasks.
Stay safe!
On Wed, Nov 24, 2021 at 6:32 AM Richard Schoen <richard@xxxxxxxxxxxxxxxxx>
wrote:
Hey all looking for best way to auto deploy database table updates on
Ibmi. Usually you move or rename the table, create new table and then copy
data back to new table, but it is rather manual,
Thoughts welcome.
Regards
Richard Schoen
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
midrange.com
