• State Not Answered
  • Replies 6 replies
  • Subscribers 93 subscribers
  • Views 1599 views
  • Users 0 members are here
Options
Related

DB Connector Object Lookup Returning Large result Set

gwickert
over 2 years ago

Hi All,

Running 8.1.4 using the DB connector to connect UNSAccountBInUNSGroupB to a target table containing a roleid and userid. When trying to provision a record from OneIM to the target, I noticed it would take almost 15 minutes for the AdHoc prejection to complete.

Upon further investigation, I found in the trace logs that the following statement is executed, presumably when trying to check for existence of the record in the target DB:

2021-06-10 14:31:46.3557 DEBUG (SqlLog SF_IAM@NZCHCGLOTABIZ1.fh.local) : select "UserId", "RoleId" from "dbo"."User_IAM_RoleMember_View" where (("UserId"=N'0051O00000ClpyAQAR') or ("RoleId"=N'a7V0n0000000os1EAA'))
2021-06-10 14:31:48.7845 TRACE (SqlLog Database Session GC) : Cleanup physical connection pool
2021-06-10 14:31:48.7845 TRACE (SqlLog Database Session GC) : 0 connections disposed.
2021-06-10 14:31:48.7845 TRACE (SqlLog Database Session GC) : Available = 4
2021-06-10 14:31:55.9015 TRACE (SqlLog Database Session GC) : Cleanup physical connection pool
2021-06-10 14:31:55.9015 TRACE (SqlLog Database Session GC) : 0 connections disposed.
2021-06-10 14:31:55.9015 TRACE (SqlLog Database Session GC) : Available = 1
2021-06-10 14:31:55.9484 DEBUG (SqlLog SF_IAM@NZCHCGLOTABIZ1.fh.local) : Done in 9586ms

EDIT: Running the query in SSMS shows that it is the query taking a long time to return - the 'Done in xxxxms' message seems wrong. Either way, the query generated it not correct.

The problem here is that the query uses OR and not AND when looking up the object.

I'm stumped as to why it would be searching like this - why would it not use AND to find a single unique User_IAM_RoleMember_View record?

Matching rules I figured was the obvious place to start - I've tried:

  • Logical expression rule - using the mapping for RoleID and UserId - i.e. vrtUNSAccountB___UserId AND vrtUNSGroupB___RoleId - doesn't seem to make a difference.
  • Using a value comparison matching rule - built a virtual property on either side, concatenating the userid/roleid values (+ translating the UIDs on OneIM side to the roleid/userid values) - still no luck.

All of that said, I'm fairly confident that the matching rule is correct - both ways I would have though were suitable.

What am I missing here? How do I make the above query generated by the connector do a lookup using UserId AND RoleId rather than UserId OR RoleId like it is in the above log?

Glen.