Cicso UCM IdB connector - Query request too large

Ok, problem is definitely on our end (more accurately a UCM limitation, but nothing you can do about it), so no worries there - must not have come up before because APRA has a relatively small set of users.

I'll need a bit of time to figure out how to deal with this, as there's severe limitations on what SQL queries we can use in Cisco UCM (unlike SQL Server where we can "page" without too much trouble).

Issuing you with a code fix means you'll need to upgrade to APRA's version. The main issue here is that there has been extensive changes to support APRA's use cases. I think they've only affected specific connectors and not the generic "Unify.ReadWriteDelete" Cisco connector you're using, but we'll need to work together to ensure nothing has regressed.

Will get back to you on Monday with an update.

Any progress on this?

From: Patrick Johannessen (JIRA) jira@unifysolutions.jira.com
Sent: Friday, 31 August 2012 3:04 PM
To: Carol Wapshere
Subject: JIRA (SSICT-64) Cicso UCM IdB connector - Query request too large

https://unifysolutions.jira.com/s/en_USt7uz6t-418945332/804/89/_/jira-logo-scaled.png

https://unifysolutions.jira.com/secure/useravatar?avatarId=10152Patrick Johannessen<https://unifysolutions.jira.com/secure/ViewProfile.jspa?name=patrick.johannessen> commented on Product Group Support SSICT-64<https://unifysolutions.jira.com/browse/SSICT-64>
Cicso UCM IdB connector - Query request too large<https://unifysolutions.jira.com/browse/SSICT-64>

Ok, problem is definitely on our end (more accurately a UCM limitation, but nothing you can do about it), so no worries there - must not have come up before because APRA has a relatively small set of users.

I'll need a bit of time to figure out how to deal with this, as there's severe limitations on what SQL queries we can use in Cisco UCM (unlike SQL Server where we can "page" without too much trouble).

Issuing you with a code fix means you'll need to upgrade to APRA's version. The main issue here is that there has been extensive changes to support APRA's use cases. I think they've only affected specific connectors and not the generic "Unify.ReadWriteDelete" Cisco connector you're using, but we'll need to work together to ensure nothing has regressed.

Will get back to you on Monday with an update.

This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Hi Carol,

Having trouble getting access to the Cisco VMs I need but I'll try and get to it today. When are you back from TechEd?

In the office in Canberra today, and then heading back out tomorrow for TechEd Aus.

From: Patrick Johannessen (JIRA) jira@unifysolutions.jira.com
Sent: Monday, 10 September 2012 9:47 AM
To: Carol Wapshere
Subject: JIRA (SSICT-64) Cicso UCM IdB connector - Query request too large

https://unifysolutions.jira.com/s/en_USt7uz6t-418945332/804/89/_/jira-logo-scaled.png

https://unifysolutions.jira.com/secure/useravatar?avatarId=10152Patrick Johannessen<https://unifysolutions.jira.com/secure/ViewProfile.jspa?name=patrick.johannessen> commented on Product Group Support SSICT-64<https://unifysolutions.jira.com/browse/SSICT-64>
Cicso UCM IdB connector - Query request too large<https://unifysolutions.jira.com/browse/SSICT-64>

Hi Carol,

Having trouble getting access to the Cisco VMs I need but I'll try and get to it today. When are you back from TechEd?

This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Hi Carol,

I've uploaded a new version of the Cisco UCM connector (3.0.1.3) that should resolve this issue. You'll need to add the following to the "communicator" element of the problem connector:

sqlListPageSize="1000"

Change the "1000" to a page size you deem appropriate. Instead of a single SQL call of:

SELECT * FROM ENDUSER

It will do this:

SELECT SKIP 0 FIRST 1000 * FROM ENDUSER
SELECT SKIP 1000 FIRST 1000 * FROM ENDUSER
SELECT SKIP 2000 FIRST 1000 * FROM ENDUSER

As mentioned before there's been a huge amount of APRA changes made to this version of the connector, but I've tested it in my environment with your config and there shouldn't be any problems.

Import into IdB went fine, but now I cannot import into FIM. Error is the same as reported in https://unifysolutions.jira.com/browse/APRA-37 and https://unifysolutions.jira.com/browse/bce-182.

Interestingly when I run this command (using the Adapter GUID) in SQL I get exactly 1000 records:

select COUNT from dbo.Entity
where PartitionId = '23ad9134-6540-4b43-a234-aacc39938ecf'

When I run a similar query against the Cisco User connector I get 7190 records. The Marval adpter (which is a simple one and not composite like the UCM adapter) has 36602 records.

Could something be causing IdB to give up at 1000 records in the composite adapter?

Hi Carol,

Firstly, is the attached AdapterEngine still accurate? It doesn't appear to be a composite adapter.

Matt has suggested you check to make sure there are no DTC errors, as it usually kicks in for the first time during change detection (which starts at 1000 entities).

I've spoken with Adam (who has experienced what might be the same thing on BCE-182) and he's provided the following:

1. Run sql trace (sql server profiler) that captures both RPC:Starting and RPC:Completed (along with the other things by default). If there is a lot of activity on the server add a filter on the database name to the Identity Broker database.
2. About when the process slows down, there may be a query in the trace that has a start time, but no end time. This should look similar to the query on BCE-182.
3. Run that query in SQL Server management studio, it should run quickly. If it does then the problem is parameter sniffing.

Perhaps I am using the wrong term? I thought it was a composite because it combines three connectors.

I had already disabled the parameter sniffing based on that BCE issue. Didn't make any difference.

I did try the profiler which running the import but couldn't actually see anything from the IdB service account at al - which seemed off.

I haven't seen any DTC errors - they would be in the IdB log?

Similar; a composite adapter is a collection of adapters. The result is (in the case of FIM) one MA that has several object classes. See IDB40:Composite Adapters.

You may have to restart SQL Server in order for it to take effect. That is to say, we haven't read anything from Microsoft that says you do, but one of us did have to in our own environment.

They should be in the IdB log, but it can't hurt to look at Event Viewer for any problems as well.

Failing all that, there's nothing more we can think of right now - if it's still a problem you'll have to escalate so we can invest more time on a non-R&D billing code.