The reason is related between the iiq database and the iiq server. Im using a csv demo authorative application (follow steps from Fundamentals of IdentityIQ Implementation training)
So in the database the employeeId is store as “extended2”
The name attribute is the internal name for the attribute and used for instance in the UIConfig
The extendedNumber="2" is to link the attribute with teh database columnextended2 (using hibernate). With the assignment of an extended attribute number, the attribute will also be searchable (via Advanced Analytics)
The displayName is used to show to the users in the UI/webpages
Notes:
Be careful using debug, as it can completely mess up things. Best is to copy the contents of the object to notepad before changing (or use SSB or alike)
the extendedNumber values may appear only once otherwise you have 2 attributes using the same database column
There is a limit in the extendedNumbers (searchable attributes)
Where is the employeeId attribute coming from in the logs? I don’t see the attribute employeeId mentioned anywhere? It is not recommended to change any columns in IIQ tables directly. It can result it into many issues. I would suggest you revert back any changes made directly to DB tables.
Were you getting the following error for empId before mapping. If a named attribute is not mapped it will throw a similar error message in the Identity Mapping page.
Is the attribute name mentioned in Identity Mapping same as that in hbm file
All these information is available in the White Paper shared by Remold.
After making all the required changes I would suggest you do a server restart
Hi Remold thank you for your help
What about if I what to rename the extended2 column in the database? then in the UI → identity configuration should be like this?
This what I have now, according to your solution is just to add extendedNumber=“2”
So first I would like to solve this, then change the column extended2 in the db which contains the employeeid
The issue is between extended2 and the declaration of empId in the IdentityExtended.hbm.xml located at intallation_tomcat\webapps\identityiq\WEB-INF\classes\sailpoint\object
IdentityExtended.hbm.xml
I want to merge extended2 with empId
<!-- (c) Copyright 2008 SailPoint Technologies, Inc., All Rights Reserved. -->
<!--
Extended attribute mappings for Identity.
By default we will map 10 extended attributes, but this may be raised to
a maximum of 20. Using ExtendedPropertyAccessor can add attributes beyond
the limit of 20 and have meaningful names. Example of ExtendedPropertyAccessor
is shown below.
Only the first five attributes are indexed, to provide
an example of how to specify an index. In a production deployment you
will usually index most of the extended attributes since they are
usually added for searching, but index maintenance slows down updates to
the identity cube so add them only if necessary.
We need the extended attributes to be large enough to hold arbitrary
customer data but not so large that Hibernate turns them into a blob or
other type which cannot be used in searches.
The maximum size of a VARCHAR on various sysems is:
Oracle - 4000
SQLServer - 8000
MySQL 5 - 65K
DB2 8.1 - 32K
With each dialect, Hibernate will use a non-indexable type for strings
of a certain size length. This size cliff is often less than the max size
for varchar on that system, so be careful if extending the sizes of these
fields beyond what is specified in this file.
SQL Server will not index a value greater than 900 "bytes".
Now that we're using NVARCHAR on SQL Server there are two bytes per
characcter so the maximum size of an indexed string column is 450.
If you know you are not running on SQL Server, or you know you will
not need an index on the column, you may raise the size safely.
-->
<!-- (c) Copyright 2008 SailPoint Technologies, Inc., All Rights Reserved. -->
<!--
Extended attribute mappings for Identity.
By default we will map 10 extended attributes, but this may be raised to
a maximum of 20. Using ExtendedPropertyAccessor can add attributes beyond
the limit of 20 and have meaningful names. Example of ExtendedPropertyAccessor
is shown below.
Only the first five attributes are indexed, to provide
an example of how to specify an index. In a production deployment you
will usually index most of the extended attributes since they are
usually added for searching, but index maintenance slows down updates to
the identity cube so add them only if necessary.
We need the extended attributes to be large enough to hold arbitrary
customer data but not so large that Hibernate turns them into a blob or
other type which cannot be used in searches.
The maximum size of a VARCHAR on various sysems is:
Oracle - 4000
SQLServer - 8000
MySQL 5 - 65K
DB2 8.1 - 32K
With each dialect, Hibernate will use a non-indexable type for strings
of a certain size length. This size cliff is often less than the max size
for varchar on that system, so be careful if extending the sizes of these
fields beyond what is specified in this file.
SQL Server will not index a value greater than 900 "bytes".
Now that we're using NVARCHAR on SQL Server there are two bytes per
characcter so the maximum size of an indexed string column is 450.
If you know you are not running on SQL Server, or you know you will
not need an index on the column, you may raise the size safely.
-->
<property name="location" type="string" length="450"
access="sailpoint.persistence.ExtendedPropertyAccessor"
index="spt_identity_location_ci"/>
<property name="extended2" type="string" length="450"
index="spt_identity_extended2_ci"/>
<property name="region" type="string" length="450"
access="sailpoint.persistence.ExtendedPropertyAccessor"
index="spt_identity_region_ci"/>
<property name="extended4" type="string" length="450"
index="spt_identity_extended4_ci"/>
<property name="extended5" type="string" length="450"
index="spt_identity_extended5_ci"/>
<property name="extended6" type="string" length="450"
index="spt_identity_extended6_ci"/>
<property name="extended7" type="string" length="450"
index="spt_identity_extended7_ci"/>
<property name="extended8" type="string" length="450"
index="spt_identity_extended8_ci"/>
<property name="extended9" type="string" length="450"
index="spt_identity_extended9_ci"/>
<property name="extended10" type="string" length="450"
index="spt_identity_extended10_ci"/>
<property name="empId" type="string" length="450"
access="sailpoint.persistence.ExtendedPropertyAccessor"
index="spt_identity_empId_ci"/>
<property name="status" type="string" length="450"
access="sailpoint.persistence.ExtendedPropertyAccessor"
index="spt_identity_status_ci"/>
<!-- An example of using ExtendedPropertyAccessor to add
attributes beyond the limit of 20 numbered
SailPointObject properties and have meaningful names.
<property name="costCenter" type="string" length="450"
access="sailpoint.persistence.ExtendedPropertyAccessor"/>
-->
<!--
<property name="extended11" type="string" length="450"/>
<property name="extended12" type="string" length="450"/>
<property name="extended13" type="string" length="450"/>
<property name="extended14" type="string" length="450"/>
<property name="extended15" type="string" length="450"/>
<property name="extended16" type="string" length="450"/>
<property name="extended17" type="string" length="450"/>
<property name="extended18" type="string" length="450"/>
<property name="extended19" type="string" length="450"/>
<property name="extended20" type="string" length="450"/>
-->
<many-to-one name="extendedIdentity1" class="sailpoint.object.Identity"/>
<many-to-one name="extendedIdentity2" class="sailpoint.object.Identity"/>
<many-to-one name="extendedIdentity3" class="sailpoint.object.Identity"/>
<many-to-one name="extendedIdentity4" class="sailpoint.object.Identity"/>
<many-to-one name="extendedIdentity5" class="sailpoint.object.Identity"/>
By default there is no need to adjust the IdentityExtended.hbm.xml as it already contains extended attributes.
IdentityIQ is using Hibernate (https://hibernate.org/) as abstraction layer between the IdentityIQ and the database. When setting the extendedNumber to a value, Hibernate will change this to the column extended_identity<number>
With the screenshots you shared you are doing too much, as you both have an extra column in the database and assign the employeeId attribute to column extended_identity2.
Simplest to fix is to remove 1 or the other, updating the hibernate condig is the best as you will be back to what is the default.
Please remove the following from IdentityExtended.hbm.xml
@Remold Krol
If I delete that my lab crash, here are the logs errors
2023-07-13T13:08:13,813 WARN main springframework.context.support.ClassPathXmlApplicationContext:559 - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘sessionFactory’ defined in class path resource [hibernateBeans.xml]: Invocation of init method failed; nested exception is org.hibernate.boot.InvalidMappingException: Could not parse mapping document: sailpoint/object/Identity.hbm.xml (RESOURCE)
jul 13, 2023 1:08:13 PM org.apache.catalina.core.StandardContext listenerStart
SEVERE: Exception sending context initialized event to listener instance of class [sailpoint.web.StartupContextListener]
org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘sessionFactory’ defined in class path resource [hibernateBeans.xml]: Invocation of init method failed; nested exception is org.hibernate.boot.InvalidMappingException: Could not parse mapping document: sailpoint/object/Identity.hbm.xml (RESOURCE)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBe
Caused by: org.hibernate.boot.InvalidMappingException: Could not parse mapping document: sailpoint/object/Identity.hbm.xml (RESOURCE)
at org.hibernate.boot.jaxb.internal.InputStreamXmlSource.doBind(InputStreamXmlSource.java:46)
at org.hibernate.boot.jaxb.internal.UrlXmlSource.doBind(UrlXmlSource.java:36)
Caused by: org.hibernate.boot.MappingException: Unable to perform unmarshalling at line number 0 and column 0. Message: null : origin(sailpoint/object/Identity.hbm.xml)
at org.hibernate.boot.jaxb.internal.AbstractBinder.jaxb(AbstractBinder.java:178)
I advice not to rename the extended column i the database, but just add a new column. This will be a named attribute. For this you should remove the extendedNumber=“2”
For IdentityIQ it does not matter if you use a named attribute or an extended attribute. The only difference is the limit of attributes as there is a limit on extended attributes, while there is no limit for named attributes.
To make sure the values are correct in the database run a Refresh-task for all identities with option Refresh identity attributes enabled after the changes to the database and ObjectConfig. This will populate the values in the correct columns (as configured).
Still i dont understnad if I rename the column in the db from extended2 to employeeId i get the exception
engine.jdbc.spi.SqlExceptionHelper:142 - Unknown column ‘identity0_.employee_id’ in ‘field list’
2023-07-13 13:58:06,832 ERROR main sailpoint.server.InternalContext:408 - Initialization error: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
