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.
