RE: Rép. : Re: Add fields to physical file without exclusive lock _ formerly Re:Stupid question of the century

I used a system once that had a file for "user defined fields" Layout was basically
<table>
Unique identifier (in my case an employee ID)
Unique field ID (in my case a number assigned my us)
Character field - length 60
Date field
Time field
Numeric field - length 15.5
<end table>
And there was an associated table that held the Unique field ID, it description and type (character, date, time, numeric). Adding a new field was just adding a new code to a table. Worked OK if access was only through RPG apps but was a real pain to deal with in Query and SQL

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Denis Robitaille
Sent: Monday, May 11, 2009 9:44 AM
To: Midrange Systems Technical Discussion
Subject: Rép. : Re: Add fields to physical file without exclusive lock _ formerly Re:Stupid question of the century

Hello,

I am glad you found my answer usefull.

The process I presented works but have some limitation so that we still
needed some exclusive time with files (like if a fields changes size
...). And the fact that the new fields could not be use for keys (or in
an OPENQRYF) created problems in the long run.

We now have a formal process in place where we have each month a few
hours dedicated to put in place changes. That solved the locking issus.


We use more and more SQL to access files, so we only need to recompile
programs that use new fields. The others just need to quit the program
(hence the monthly maintenance window).

I have also develop a technique to create "virtual fields" to simulate
a dynamic field addition. But explaining this would take to long.

Denis Robitaille
Directeur services technique TI
819 363 6130

SUPPORT
Jour (EST) Daytime : 819-363-6134
En-dehors des heures (EST) After hour : 819-363-6158
Network Status : 819-363-6157

Alan Shore <AlanShore@xxxxxxxx> 2009-05-11 09:17 >>>

Thanks for your reply Denis
The process you described DEFINITELY makes sense, and to a certain
extent
we are already doing this.
Whenever we need to add fields to an existing file, we also add extra
fields (alpha) to the end, of varying lengths
The unfortunate thing is, we never seem to add enough
However I like the idea of adding ONE LARGE field, and then taking bits
and
pieces of it as is needed
I found it interesting that you no longer recommend this method any
more.
Can you say why


Alan Shore
Programmer/Analyst, Distribution
E:AShore@xxxxxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

midrange-l-bounces@xxxxxxxxxxxx wrote on 05/11/2009 08:30:55 AM:

Hello,

I have done that (or at least something quite close). I had a

similar

challenge in the early 90's when we implemented level checking. I had

to

devise a way to add filed to a file without creating a level check

while

leaving level checking on.

Here is what you could do.
- For the first time, you will still need exclusive access to the

file

- you had one big alpha field to the file (1000 char. or more)
- you recompile the file and all programs that use it

Now you are set so you can had fields o the fly.

- You define an external data structure (basically a PF with no data

in

it) that redefine the big alpha filed added earlier
- You use that external DS in the D spec of the program to overlay

the

big field
-- This way, there is no level checking being done on the external

data

structure since it is not on the F spec
- When you need a new field, you just modify the external data
structure, recompile it (it is never locked)
-- be sure to add new fields at the end and to never move a field.
- then you create a one shot program to initialize the new field
(putting all "0" in it or whatever)
- you must only recompile the programs that access that new fileds.

Of course, there are some limitation to this technique. The biggest

is

that no filed added can be part of a key.

I do not recommend this method anymore, but it does achieve what you
want:
- Can add filed to a file while it is locked
- The only programs that need to be freed and recompile are those

using

the new fields
- imply very little change to all existing program

I hope this make sense to you.

Denis Robitaille
Directeur services technique TI
819 363 6130

SUPPORT
Jour (EST) Daytime : 819-363-6134
En-dehors des heures (EST) After hour : 819-363-6158
Network Status : 819-363-6157

Alan Shore <AlanShore@xxxxxxxx> 2009-05-09 13:18 >>>

Hi guys
I've just been asked the following question by my boss's boss
Is there anyway to add new fields (columns) "on the fly" to an
existing
file, without having an exclusive lock on that file.
In other words, the file is still being used while the field is

added

I know that chgpf is out of the question (unless someone proves me
wrong).
I know that SQL is also out of the question (ALTER TABLE x ADD
EXTRAFIELD
DEC(7, 2) NOT NULL DEFAULT 0) - again unless someone proves me wrong
If anyone knows of any way, any language, any procedure, any

function

to
achieve this, please drop me a line.

Thanks in advance


Alan Shore
Programmer/Analyst, Distribution
E:AShore@xxxxxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing
list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.