D&l Limitations

[catch.saravana]

Hi All,
I see a set of D&L limitations on Progress documentation; out of which they say ROWID/RECID will be dumped as '?' (unknown value). Exact statement below;

If you define a database field with a data type of ROWID or RECID, then the ROWID values in that field are dumped and reloaded as Unknown value (?). You must write your own dump and reload procedures to accommodate ROWID fields.

OpenEdge 11.6 Documentation

We have 3 tables with one RECID field in each of them.

Have anyone faced this scenario?

I didn't check this yet with the actual tables but I tried the following example where I didn't see this limitation;
1. Create 2 sports2000 DB
2. In one DB, I created a table with RECID and stored RECID's of customer table
3. I tried to dump this table and load the data to a table with same structure on the other sports2000 DB
4. Rebuild the index

I queried the values on the DB where I did a load and I could see proper values (didn't see any '?').

Please advise.

 

[Rob Fitzpatrick]

Short answer:
I'm not sure why you're seeing something other than the docs describe. Fortunately I've never had to face this situation as we don't have any recid fields in our schemas.

Longer answer:
As I see it the fundamental problem is this:

• A recid (or rowid) is a numeric encoding of the physical location of a record in a given storage area; the address of the record block and the offset of the record within that block.
• A dump and load rearranges the physical locations of your data (which is why it is useful) so it is virtually guaranteed to change all of your records' recids.

So let's say that in your source DB, prior to D&L, record A in your parent table contains a value of 12345 in its child-table-recid field. Even if you manage to successfully export the value 12345 to the parent table dump file and load it into the target DB, it is very likely that the related record in the child table that had recid 12345 in the source DB has a different recid in the target DB. Even if the data was carried over intact, the relation is permanently lost; the parent now points to the wrong record or to a non-existent record. So recids/rowids should never be used as foreign keys to relate records in different tables. Never, never, never. Use a character field with a GUID instead (and rewrite the code accordingly).

Even without a D&L, a recid is not guaranteed to remain static for the lifetime of a record, so it should never be stored long-term (i.e. more than a second or two). So even if you never D&L that database, a proutil tablemove will break your application.

 

[catch.saravana]

We are done with 1 round of migration on a test machine, integrated the downstream applications and is in testing phase. Testers haven't reported anything on it - may be they didn't stamp on scenario that is using this table. To be honest I never thought about it until I came across that limitation page from progress documentation (was actually looking for something else). Then I went back to check our DF and it had 3 fields storing RECID's. I had a word with developers here and they say that's how the tables are designed and been used for a long time and they never had any issue in past which shows they have never done a TABLEMOVE or D&L on this table for a long time now. These tables have around 1K records each and am looking into code base to see what is the purpose of these tables.

Thanks Rob!

 

[RealHeavyDude]

Using RECID or ROWID to join tables is a big NO GO for all the reasons Rob mentioned. Although it might not cause any problem as long you don't have to perform any maintenance task on your database ( like dump & load - or a simple table move which, for a table with 1K records, could even be performed online ), but there will come a point in time when you need to.

Suppose you wan't to move your database to another platform ( for example from a supported Unix to a supported Linux - not to mention Windows ) - the only supported way is a dump and load. If RECIDs or ROWIDs are used to join tables you are basically stuck until you solve the issue. That is until you have replaced the RECIDs or ROWIDs with database fields of another type.

IMHO, the developers you've talked to should be subject to training. I've heard this myself ... just because one didn't have an issue yet does not mean it is good practice or won't cause serious trouble in the future.

 

[Cringer]

I'm assuming the RECID storage (by the way RECID is deprecated and shouldn't be used anymore anyway) is a way of providing a single field unique foreign key for a table with complex uniqueness rules. That's the only reason I can see for it. If that is the case then giving the table in question a simple GUID field which is populated on create of the record will solve all these problems.