DB2 import: FK constraint connector target role

[Uffe]

Hi all,


Using 13.5.1352 on Windows 7x64, importing from a z/OS DB2 12 database through IBM DB2 Connect.

After import, some but not all foreign key constraint connectors are out of whack. The Involved Columns list is empty (as is the Join on Constraint field), and the target role is set to some random string of garbage.

The same garbage string gets reused in several broken connectors, but from one import there may be more than one such garbage string.
The behaviour is not consistent; the garbage strings and which connectors get them vary from import to import (of the same database).

Has anyone else seen this? If so, what's going on?

I can go through and set the Join on Constraint property manually. The dropdown is properly populated.
But does anyone know if this indicates that something else is broken in the model?


Secondly, EA crashes after import. Far as I can tell, this is an unrelated error.
The crash occurs even if I tell EA not to import any constraints or foreign keys at all, and it occurs whether or not I tell it to draw diagrams.

The Windows log shows a fault somewhere in or near ntdll.dll.

Anyone else see this?

Cheers,


/Uffe

[Paolo F Cantoni]

Hi Uffe,

I reported a bug with self-referential FK Constraints not being imported.  That was for SQL Server.

Paolo

[Geert Bellekens]

Yes,

We've seen those problems and a few others.
I reported them as bugs back then, and we devised a workaround based upon parsing the DDL and adding/correcting the missing/wrong info.

This functionality is part of the EA Database Transformer add-in and was specifically made for DB2.
The add-in is open source, so if some parts do not work exactly how you need then you can change the code yourself (or ask me to change it for you)

Geert

[Uffe]

We've seen those problems and a few others.
I reported them as bugs back then, and we devised a workaround based upon parsing the DDL and adding/correcting the missing/wrong info.

Any response on those bug reports?
Are you in a position to check if they've been addressed in the 14 beta?

And have you got anything written down as to the symptoms, causes, effects and/or workarounds?
Specifically, did you see EA crashing at the end of every DB2 import like we do?
Not that I'm asking you to do my work for me or anything.... 

This functionality is part of the EA Database Transformer add-in and was specifically made for DB2.
The add-in is open source, so if some parts do not work exactly how you need then you can change the code yourself (or ask me to change it for you)

I'll keep that in my back pocket, although the primary option must be to get the functionality we've paid for out of the tool where it's supposed to have been implemented already.

The usage section on the page is a little scant -- am I correct in that the intended way of working is to

• Import the schema into EA using EA's built-in functionality,
• Export the DDL from the DBMS separately,
• Use the Add-In to patch ("complete") the model with the contents of the exported DDL.

?

Thanks,


/Uffe

[Geert Bellekens]

Hi Uffe,

I don't remember what the exact response was, but I'm pretty sure it was not satisfactory.
Something along the lines of "works on my machine" IIRC.

Anyway, we needed a solution short term, for a one-off import, so I stopped investing time (=> my clients money) into debugging EA and started with the workaround.

I don't have a list of issues right at hand, but you can derive that from the code at https://github.com/GeertBellekens/Enterprise-Architect-Add-in-Framework/blob/master/EAAddinFramework/Databases/Transformation/DB2/DB2DatabaseTransformer.cs
It does:

Code: [Select]

      this.fixUniqueIndexes               (database, withDDL);
      this.fixClusteredIndexes            (database, withDDL);
      this.fixOnDeleteRestrictForeignKeys (database, withDDL);
      this.fixWithDefaultFields           (database, withDDL);
      this.fixIncludedFieldsInIndex       (database, withDDL);
      this.fixCheckConstraints   (database, withDDL);
I think I fixed the weird characters for foreign keys in a script (since that fix didn't need any input from the DDL)

Code: [Select]

'[path=\Projects\Project AC]
'[group=Acerta Scripts]
option explicit
!INC Local Scripts.EAConstants-VBScript
!INC Wrappers.Include

'
' Script Name:
' Author: Geert Bellekens
' Purpose: sets datatypes to uppercase
' - updates connector roles to primary keys
' - sets the "DEFAULT" for any "not null" field that isn't part of a FK or PK
' Does only work on SQL Server repositories
' Date: 2016-1-10
'
sub main
'select source database
dim physicalPackage as EA.Package
msgbox "select the database package (example: «database» GBDOAA01)"
set physicalPackage = selectPackage()
if not physicalPackage is nothing then
   dim packageID
   packageID = physicalPackage.PackageID
   dim sqlUpdate
   'update attribute types
   sqlUpdate = "update a set a.type = upper(a.type) " & _
" from t_attribute a " & _
" join t_object o on o.Object_ID = a.Object_ID " & _
" join t_package p on p.Package_ID = o.Package_ID " & _
" join t_package dbp on p.Parent_ID = dbp.Package_ID  " & _
" where a.Stereotype = 'column' " & _
" and " & packageID & " in (p.Package_ID, dbp.Package_ID)"
   Repository.Execute sqlUpdate

   'update parameter types
   sqlUpdate = "update opp set opp.Type = UPPER(opp.type) " & _
   " from t_operationparams opp " & _
   " inner join t_operation op on op.OperationID = opp.OperationID " & _
   " inner join t_object o on o.Object_ID = op.Object_ID " & _
   " join t_package p on p.Package_ID = o.Package_ID " & _
   " join t_package dbp on p.Parent_ID = dbp.Package_ID  " & _
   " where o.Stereotype = 'table' " & _
   " and " & packageID & " in (p.Package_ID, dbp.Package_ID)"
   Repository.Execute sqlUpdate

   'update connector roles for primary keys
   sqlUpdate = "update c set c.DestRole = op.Name, c.StyleEx = 'FKINFO=SRC=' + c.SourceRole + ':DST=' + op.Name + ':;' " & _
   " from t_connector c " & _
   " inner join t_object o on o.Object_ID = c.End_Object_ID " & _
   " inner join t_operation op on op.Object_ID = o.Object_ID " & _
   " and op.Name like 'PK%' " & _
   " and op.Stereotype = 'PK' " & _
   " join t_package p on p.Package_ID = o.Package_ID " & _
   " join t_package dbp on p.Parent_ID = dbp.Package_ID  " & _    
   " where c.SourceRole like 'FK%' " & _
   " and " & packageID & " in (p.Package_ID, dbp.Package_ID) " & _
   " and  " & _
   " (isnull(c.DestRole,'') <>  op.Name " & _
   " or " & _
   " isnull(convert( varchar(500),c.StyleEx),'') <> 'FKINFO=SRC=' + c.SourceRole + ':DST=' + op.Name + ':;')"
   Repository.Execute sqlUpdate
' 'set the "with default" values
' sqlUpdate = "begin tran update a set a.[Default] = 'DEFAULT' " & _
'   " from t_attribute a  " & _
'   " inner join t_object o on o.Object_ID = a.Object_ID " & _
'   " inner join t_package p on p.Package_ID = o.Package_ID " & _
'   " join t_package dbp on p.Parent_ID = dbp.Package_ID  " & _
'   " where a.Stereotype = 'column' " & _
'   " and " & packageID & " in (p.Package_ID, dbp.Package_ID)" & _
'   " and a.AllowDuplicates = 1 " & _
'   " and (a.[Default] is null or convert(varchar(500),a.[Default]) like 'CURRENT%') " & _
'   " and isnull(convert(varchar(500),a.[Default]),'') <> 'DEFAULT' " & _
'   " and not exists " & _
'   " (select opp.ea_guid from t_operation op " & _
'   " inner join t_operationparams opp on op.OperationID = opp.OperationID " & _
'   " where op.Object_ID = o.object_id " & _
'   " and op.Stereotype in ('PK','FK') " & _
'   " and opp.Name = a.Name) "
' Repository.Execute sqlUpdate
end if
end sub
main