RE: Modernizing Database using Surrogate File

You are correct that there is not a TIMFMT on the table or column commands.
The SET OPTION is not valid when I tried it in the script to create the
able. However I did change the default JDBC connection configuration to use
formats *HMS with the ':' separator for time and *USA for date, saved it as
default, applied it as default and recreated the table but alas I still have
the same issue.

I also appreciate your thoughts on PTF Group's and we are a few groups
behind so I will download them and apply them this weekend and let you guys
know it that helped.

Thanks again Rob, and if you think of anything else please email me.

Hoss

"I am not sure what my spirit animal is, but I am confident it has rabies."

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> On Behalf Of Rob Berendt
Sent: Thursday, July 12, 2018 7:11 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Modernizing Database using Surrogate File

I would ensure that you are on the latest group ptfs as shown at
http://www-01.ibm.com/support/docview.wss?uid=nas4PSPbyNum

See also:

SET OPTION TIMFMT = *HMS
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzsoption.ht
m

There are ways to set up the defaults when you generate stuff. Normally
different for each tool. For example, in STRSQL, F13, 1. Change session
attributes, Time format. In "Run SQL Scripts" you look in Connection, JDBC
Configurations.

I do not believe that TIMFMT is any option on CREATE TABLE.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "Bruce Collins" <bacollins@xxxxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 07/12/2018 07:42 AM
Subject: RE: Modernizing Database using Surrogate File
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Mark, I even tried

create table xxxsql.dlytrn_b as ( select * from xxxoldf.dlytrn) definition
only rcdfmt PFDLYT

But I still get the same result. So just as you said It has to be that
TIME
field. In the DDS it had a TIMFMT(*HMS) and when SQL creates it the time
format is *ISO.

Hoss

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> On Behalf Of mlazarus
Sent: Wednesday, July 11, 2018 6:24 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Modernizing Database using Surrogate File

Bruce,

Are there any date, time or timestamp fields in the file? There was a
bug
introduced a while back that had the SQL version produce a different
(incorrect?) hash.

Another possibility is making sure there are no null values allowed in
any
of the SQL created fields, which is unlikely to be in the DDS version of
the
file.

-mark

On 7/11/2018 7:13 PM, Bruce Collins 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