Re: Modernizing Database using Surrogate File

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Compare the output of DSPFFD for both original PF and new LF...

Charles

On Wed, Jul 11, 2018 at 5:13 PM Bruce Collins <bacollins@xxxxxxxxxxxxxxxxx>
wrote:

> 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
>
>
>
> --
> 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: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD