Modernizing Database using Surrogate File

Hey guys I could use another set of eyes on this problem. When I was with
another company we had Dan Cruikshank come and spend a week going over
database modernization. The method I have used before is the following.



Using Navigator I generate the DDL for the physical file (DLYTRN) I am going
to "modernize". My goal is to keep the Record Format Level Indicator from
changing so I do not have to deal with level checks.



With that said, I took the DDL that I had just generated and added a few
more columns, an Identity Column, RowID, and Row Change Timestamp. I then
created the table (DLYTRN_B) in schema XXXSQL and all is well.



I then built a logical file based on DLYTRN_B and giving it the original
name of the Physical file (DLYTRN).



I understand that the identifier is a hash based on several factors and I
tried to make sure that the following was done to create the appropriate ID
for the surrogate:



1) Logical File Name Matched original physical file

2) Format Name Matched Original Physical File

3) Only the columns that were in the original physical file were
included in the new logical file.

4) Made sure the columns are in the correct "Ordinal Position" as the
original Physical File.

5) Made sure the column names, data type, Length and Scale (If numeric)
are the same as the original Physical File.



I created the logical in the same schema as the newly created physical but
when I compared the Record Format Level Identifiers of the original physical
file and the surrogate I did not have a match. I did a DSPDBR over DLYTRN_B
and say that the new surrogate file was linked to it.



I went and verified once again, to my satisfaction, that I had not missed
anything. I created the new table (DLYTRN_B) and the logical (DLYTRN) again
and still they did not match.



Below is screen print of the significant data for the original physical file
and the new logical.





Record Format List for Physical File - DLYTRN

Record Format Level

Format Fields Length Identifier

PFDLYT 191 1791 2988BCEAE18FD

Text . . . DAILY TAG TRANSACTION

Total number of formats . . . . . . . . . . : 1

Total number of fields . . . . . . . . . . . : 191

Total record length . . . . . . . . . . . . : 1791







Record Format List for surrogate File - DLYTRN based on DLYTRN_B

Record Format Level

Format Fields Length Identifier

PFDLYT 191 1791 298742B2218FD

Text . . . DAILY TAG TRANSACTION

Total number of formats . . . . . . . . . . : 1

Total number of fields . . . . . . . . . . . : 191

Total record length . . . . . . . . . . . . : 1791



I feel that I am overlooking something and appreciate your help in advance.



If you feel up to it I have much more information but I do not want to clog
the list with it.



If you are interested just contact me off list and I will send you the info.





Thanks



Hoss