• State Verified Answer
  • Locked Locked
  • Replies 5 replies
  • Answers 1 answer
  • Subscribers 94 subscribers
  • Views 5571 views
  • Users 0 members are here
Options
Related
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Native DB Connector for DB2 - [2415001] Invalid reference target

craig.rose
over 7 years ago

I am getting the error "VI.Base.ViException: Invalid reference target (USERACCOUNT.USERACCOUNT_ID)! The target must be unique." after defining foreign key references in the Native DB connector for DB2.

 

The 1IM environment is version 7.1.1. 

Have confirmed that the values contained within the column "USERACCOUNT.USERACCOUNT_ID" are unique so I don't believe it is that.

 

The connector configuration, inclusive of this foreign key reference is fine when connected to another instance of the same DB so I believe that it's likely that the problem has something to do with the DB as opposed to the connector but I cannot work out what it is. 

The following shows the crash log from the sync editor when trying to update the target system schema after defining the foreign key:

2017-07-14 12:44:21 SqlLog
select ref.CONSTNAME as Name,
ref.TabSchema as RelationSchema,
coluseSrc.TABNAME as TableName,
coluseSrc.ColName as ColumnName,
coluseTrg.TABNAME as TargetTableName,
coluseTrg.ColName as TargetColumnName,
coluseSrc.ColSeq as Position,
1 as IsReferentialIntegrityEnabled
from SYSCAT.References ref
join SYSCAT.KeyColUse coluseSrc on ref.TABSCHEMA = coluseSrc.TABSCHEMA
and ref.CONSTNAME = coluseSrc.CONSTNAME
join SYSCAT.KeyColUse coluseTrg on ref.TABSCHEMA = coluseTrg.TABSCHEMA
and ref.REFKEYNAME = coluseTrg.CONSTNAME

where coluseSrc.ColSeq = coluseTrg.ColSeq
order by 1, 2, 3, 5
2017-07-14 12:44:21 SqlLog Done in 275ms
2017-07-14 12:44:21 DB2MetaDataReader [2415001] Invalid reference target (USERACCOUNT.USERACCOUNT_ID)! The target must be unique.

 

The following shows the results logged by NLog:

 

2017-07-14 12:44:20.9441 ERROR (ProjectorFactory ) : Error getting types from assembly. System.Reflection.ReflectionTypeLoadException: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.
at System.Reflection.RuntimeModule.GetTypes(RuntimeModule module)
at System.Reflection.RuntimeModule.GetTypes()
at System.Reflection.Assembly.GetTypes()
at VI.Projector.Factory.ProjectorFactory.<CollectAllTypes>d__1`1.MoveNext() at System.Reflection.RuntimeModule.GetTypes(RuntimeModule module)
at System.Reflection.RuntimeModule.GetTypes()
at System.Reflection.Assembly.GetTypes()
at VI.Projector.Factory.ProjectorFactory.<CollectAllTypes>d__1`1.MoveNext()
2017-07-14 12:44:21.4130 WARN (DB2MetaDataReader <static>) : [2415001] Invalid reference target (CLAIM_TYPE.CLAIM_TYPE_ID)! The target must be unique.
2017-07-14 12:44:21.5066 ERROR (VI.FormBase.ExceptionMgr ) : Error updating schema of system connection (Database (XXXXX))! VI.Base.ViException: Error updating schema of system connection (Database (XXXXX))! ---> VI.Base.ViException: Invalid reference target (USERACCOUNT.USERACCOUNT_ID)! The target must be unique.
at VI.Projector.Database.Native.MetaData.MetaDataReference.set_Target(IMetaDataReferenceElement value)
at VI.Projector.Database.Native.Schema.Description.UserSchemaDescriptor.OnApply(IDatabaseConnection connection, MetaDataContainer metaData)
at VI.Projector.Database.Native.Systems.DatabaseSystemAdapter.CreateMetaData(INativeSchemaDescriptor schemaDescriptor, MetaDataCreateOptions options)
at VI.Projector.Database.Native.Schema.NativeDatabaseSchema..ctor(String displayName, IDatabaseSystemAdapter adapter, SystemDescription systemIdentity, INativeSchemaDescriptor schemaDescriptor, IEnumerable`1 ownerWhiteList)
at VI.Projector.Database.Native.NativeDatabaseConnector.OnGetSchema()
at VI.Projector.Connection.SystemConnector.get_Schema()
at VI.Projector.Connection.SystemConnection.UpdateSchema(ISchemaUpdater updater)
--- End of inner exception stack trace ---
at VI.Projector.Connection.SystemConnection.UpdateSchema(ISchemaUpdater updater)
at VI.Projector.Editor.Internal.GlobalActionHandler._UpdateSchema(ISystemConnection connection)
at VI.Projector.Editor.Internal.GlobalActionHandler.HandleEvent(Object sender, String eventName, Object[] args) at VI.Projector.Connection.SystemConnection.UpdateSchema(ISchemaUpdater updater)
at VI.Projector.Editor.Internal.GlobalActionHandler._UpdateSchema(ISystemConnection connection)
at VI.Projector.Editor.Internal.GlobalActionHandler.HandleEvent(Object sender, String eventName, Object[] args)

 

There seems to be a difference in the way that the connector detects the Primary key referred to in the above information between the working environment and the broken environment, but again I am not sure what causes the difference in the discovery. The below image shows the column in question from the working environment and broken environment respectively.

  • 0 over 7 years ago
    I believe at the point in time of the error the connector is just checking the schema of your database and not the content itself.

    So what i would do, i would check the table, column and constraint definition for both references that are part of your posted error message (CLAIM_TYPE.CLAIM_TYPE_ID and USERACCOUNT.USERACCOUNT_ID) in both DB2 databases.

    I think this is the fastest way to find the difference.
  • +1 over 7 years ago
    You could check your UniqueIndex definitions, maybe on the database that is not working the index is defined with UniqueRule =U instead of UniqueRule=P. Only those with UniqueRule=P will be detected as unique column as the other one would allow multiple NULL values in the column.
  • 0 over 7 years ago

    Hi Markus,

    Is it possible that the error thrown during discovery could be caused by the same index existing across multiple DB2 schemas on the server which we are having problems with?

    I modified the query that the connector is using during discovery and the following is the result from the DB which we are experiencing the problems with:

     

    The following is the result yielded from executing the same query against the DB that we can successfully connect with:

     

    Interestingly, the DB which we are able to integrate with successfully has both P and U index types defined for both columns whereas the environment that we are having issues with only has the P type defined. In addition the environment which we are having trouble with has indexes with the same name tables / columns defined across different schemas for the USERACCOUNT_ID column but not the CLAIM_TYPE_ID column.

     

    Thanks

  • 0 over 7 years ago

    So your databases do not have the same schema which is kind of bad when you compare a dev. and a prod. environment.

    Anyways.

    The error only occurs because you have manually specified a references to that columns in the connection configuration of the DB2 connector.

    What you can do, to get rid of the error message, manually mark the column as a unique column (as show in the screenshot).

    HtH

  • 0 over 7 years ago
    Hey Markus,

    I agree that the problem is with the schema definition of the database.

    Unfortunately sometimes it is much easier to identify the cause of the problems that are causing us the most pain and ask for these to be re-mediated than it is to get a complete environment audit and remediation.

    Thanks for the assistance.