Detecting RI datatype mismatches

[BD]

Hi, all.

I'm running 8.2 on Windows.

This is a development platform for a project whose production
environment is running on a mainframe.

I believe that the RI compilation process is not quite as robust on
Windows as it is in other environments. Here's what I'm finding:

The data model is being maintained in ERwin.

I create a couple of tables, and establish an FK in TABLE_1 against
the PK of TABLE_2.

I then change the datatype of the FK column in TABLE_1. Erwin allows
this (YUCK). ((I also find that if I change the datatype of the parent
column, propagation of that change to the child(ren) seems pretty
spotty - sometimes it happens, sometimes not.))

I spit out the DDL, and run it. No errors are generated. So I now have
a child column whose datatype differs from the parent, and yet the
constraint compiles.

I'm rather concerned about this, as I have some indication that
problems like this won't be caught in the Windows environments at all,
but will cause errors when the DDL code goes to Production.

My background is Oracle, and I could see where I'd be able to generate
some PL/SQL in Oracle, using a cursor or two, to check for datatype
mismatches like this so they can be corrected in the ERwin model.
Would there be some equivalent system views in db2 that I could look
at for helpful RI details? Views that would include tables and columns
that are involved with FK constraints, and datatypes of specific
columns?

Thanks!!

BD

[Lennart]

BD wrote:

Hi, all.

I'm running 8.2 on Windows.

This is a development platform for a project whose production
environment is running on a mainframe.

I believe that the RI compilation process is not quite as robust on
Windows as it is in other environments. Here's what I'm finding:

The data model is being maintained in ERwin.

I create a couple of tables, and establish an FK in TABLE_1 against
the PK of TABLE_2.

I then change the datatype of the FK column in TABLE_1. Erwin allows
this (YUCK). ((I also find that if I change the datatype of the parent
column, propagation of that change to the child(ren) seems pretty
spotty - sometimes it happens, sometimes not.))

I spit out the DDL, and run it. No errors are generated. So I now have
a child column whose datatype differs from the parent, and yet the
constraint compiles.

I'm rather concerned about this, as I have some indication that
problems like this won't be caught in the Windows environments at all,
but will cause errors when the DDL code goes to Production.

My background is Oracle, and I could see where I'd be able to generate
some PL/SQL in Oracle, using a cursor or two, to check for datatype
mismatches like this so they can be corrected in the ERwin model.
Would there be some equivalent system views in db2 that I could look
at for helpful RI details? Views that would include tables and columns
that are involved with FK constraints, and datatypes of specific
columns?

Can you reproduce this with only two tables and post the script
generated from Erwin?

/Lennart

[Roger]

On Oct 30, 3:07 am, Lennart <[e-mail]:

BD wrote:

Hi, all.

I'm running 8.2 on Windows.

This is a development platform for a project whose production
environment is running on a mainframe.

I believe that the RI compilation process is not quite as robust on
Windows as it is in other environments. Here's what I'm finding:

The data model is being maintained in ERwin.

I create a couple of tables, and establish an FK in TABLE_1 against
the PK of TABLE_2.

I then change the datatype of the FK column in TABLE_1. Erwin allows
this (YUCK). ((I also find that if I change the datatype of the parent
column, propagation of that change to the child(ren) seems pretty
spotty - sometimes it happens, sometimes not.))

I spit out the DDL, and run it. No errors are generated. So I now have
a child column whose datatype differs from the parent, and yet the
constraint compiles.

I'm rather concerned about this, as I have some indication that
problems like this won't be caught in the Windows environments at all,
but will cause errors when the DDL code goes to Production.

My background is Oracle, and I could see where I'd be able to generate
some PL/SQL in Oracle, using a cursor or two, to check for datatype
mismatches like this so they can be corrected in the ERwin model.
Would there be some equivalent system views in db2 that I could look
at for helpful RI details? Views that would include tables and columns
that are involved with FK constraints, and datatypes of specific
columns?

Can you reproduce this with only two tables and post the script
generated from Erwin?

/Lennart

yes...syscat. sysrels or sysibm.sysrels The first one is a view and
the second is a real catalog table.

HTH

Roger

[Knut Stolze]

Roger wrote:

the second is a real catalog table.

.... which you shouldn't use if you prefer stable applications.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Lennart]

On Oct 31, 3:15 am, Roger <[e-mail]:
[...]

Can you reproduce this with only two tables and post the script
generated from Erwin?

/Lennart

yes...syscat. sysrels or sysibm.sysrels The first one is a view and
the second is a real catalog table.

I dont follow, and I dont see any output from Erwin.

/Lennart

[The Boss]

BD wrote:

Hi, all.

I'm running 8.2 on Windows.

This is a development platform for a project whose production
environment is running on a mainframe.

"mainframe" is somewhat ambiguous these days, as it can run different OS's.
If your production environment will be running Linux on zSeries, having a
dev environment on Windows is more or less OK, as both will be running "DB2
for LUW".
However if your prod environment is running zOS, you should be aware that
"DB2 for zOS" isn't the same product as "DB2 for LUW" (different
architecture; differences in DDL and system tables/views; other utilities
etc.)

HTH

--
Jeroen

[BD]

On Oct 31, 2:56 pm, "The Boss" <[e-mail]:

BD wrote:

Hi, all.

I'm running 8.2 on Windows.

This is a development platform for a project whose production
environment is running on a mainframe.

"mainframe" is somewhat ambiguous these days, as it can run different OS's.
If your production environment will be running Linux on zSeries, having a
dev environment on Windows is more or less OK, as both will be running "DB2
for LUW".
However if your prod environment is running zOS, you should be aware that
"DB2 for zOS" isn't the same product as "DB2 for LUW" (different
architecture; differences in DDL and system tables/views; other utilities
etc.)

HTH

--
Jeroen

Yes, and the distinctions are many. However, the client's handling all
the necessary processes to run the builds in the Prod environment
(it's zOS).

[BD]

On Oct 30, 7:15 pm, Roger <[e-mail]:

On Oct 30, 3:07 am, Lennart <[e-mail]:

BD wrote:

Hi, all.

I'm running 8.2 on Windows.

This is a development platform for a project whose production
environment is running on a mainframe.

I believe that the RI compilation process is not quite as robust on
Windows as it is in other environments. Here's what I'm finding:

The data model is being maintained in ERwin.

I create a couple of tables, and establish an FK in TABLE_1 against
the PK of TABLE_2.

I then change the datatype of the FK column in TABLE_1. Erwin allows
this (YUCK). ((I also find that if I change the datatype of the parent
column, propagation of that change to the child(ren) seems pretty
spotty - sometimes it happens, sometimes not.))

I spit out the DDL, and run it. No errors are generated. So I now have
a child column whose datatype differs from the parent, and yet the
constraint compiles.

I'm rather concerned about this, as I have some indication that
problems like this won't be caught in the Windows environments at all,
but will cause errors when the DDL code goes to Production.

My background is Oracle, and I could see where I'd be able to generate
some PL/SQL in Oracle, using a cursor or two, to check for datatype
mismatches like this so they can be corrected in the ERwin model.
Would there be some equivalent system views in db2 that I could look
at for helpful RI details? Views that would include tables and columns
that are involved with FK constraints, and datatypes of specific
columns?

Can you reproduce this with only two tables and post the script
generated from Erwin?

/Lennart

yes...syscat. sysrels or sysibm.sysrels The first one is a view and
the second is a real catalog table.

HTH

Roger- Hide quoted text -

- Show quoted text -

Thanks. I'll have a look.

[BD]

On Oct 30, 1:07 am, Lennart <[e-mail]:

BD wrote:

Hi, all.

I'm running 8.2 on Windows.

This is a development platform for a project whose production
environment is running on a mainframe.

I believe that the RI compilation process is not quite as robust on
Windows as it is in other environments. Here's what I'm finding:

The data model is being maintained in ERwin.

I create a couple of tables, and establish an FK in TABLE_1 against
the PK of TABLE_2.

I then change the datatype of the FK column in TABLE_1. Erwin allows
this (YUCK). ((I also find that if I change the datatype of the parent
column, propagation of that change to the child(ren) seems pretty
spotty - sometimes it happens, sometimes not.))

I spit out the DDL, and run it. No errors are generated. So I now have
a child column whose datatype differs from the parent, and yet the
constraint compiles.

I'm rather concerned about this, as I have some indication that
problems like this won't be caught in the Windows environments at all,
but will cause errors when the DDL code goes to Production.

My background is Oracle, and I could see where I'd be able to generate
some PL/SQL in Oracle, using a cursor or two, to check for datatype
mismatches like this so they can be corrected in the ERwin model.
Would there be some equivalent system views in db2 that I could look
at for helpful RI details? Views that would include tables and columns
that are involved with FK constraints, and datatypes of specific
columns?

Can you reproduce this with only two tables and post the script
generated from Erwin?

/Lennart- Hide quoted text -

- Show quoted text -

In short, yes.

But there's nothing exotic (or even post-worthy, IMO) about the DDL
that's generated from Erwin; It's simple create statements. It's just
that the child FK column remains defined as the same as it had been
before the parent's datatype was changed.

I'm not an ERwin expert, but I've been advised that ERwin should not
be expected to propagate such datatype changes from parent to child.
Which would be fine, except for the fact that I've seen situations
where the propagation occurs - and some where it does not.

[Lennart]

On Nov 1, 6:43 pm, BD <[e-mail]:
[...]

But there's nothing exotic (or even post-worthy, IMO) about the DDL
that's generated from Erwin; It's simple create statements. It's just
that the child FK column remains defined as the same as it had been
before the parent's datatype was changed.

What's interesting is whether db2 accepts it or not

/Lennart

[...]

[Knut Stolze]

Lennart wrote:

On Nov 1, 6:43 pm, BD <[e-mail]:
[...]

>But there's nothing exotic (or even post-worthy, IMO) about the DDL
that's generated from Erwin; It's simple create statements. It's just
that the child FK column remains defined as the same as it had been
before the parent's datatype was changed.

What's interesting is whether db2 accepts it or not

I haven't followed all of your discussion, but DB2 allows a different data
type in referencing tables than in the referenced table:

$ db2 "create table a ( a int not null primary key )"
DB20000I The SQL command completed successfully.
8 local:~/ $ db2 "create table b ( a smallint, foreign key(a) references
a )"
DB20000I The SQL command completed successfully.

And I don't see a particular reason why this should not be allowed...

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Lennart]

On Nov 5, 1:27 pm, Knut Stolze <[e-mail]:
[...]

$ db2 "create table a ( a int not null primary key )"
DB20000I The SQL command completed successfully.
8 local:~/ $ db2 "create table b ( a smallint, foreign key(a) references
a )"
DB20000I The SQL command completed successfully.

And I don't see a particular reason why this should not be allowed...

Neither do I as long as they are eqtypes. Anyhow, I suspect that the
problem lies within Erwin and has nothing to do with db2. Still, I can
understand the OP's worry about what will happen on the mainframe