Populating Profiles – long search filter error

A customer wanted to use a series of nested groups to populate Profiles. The theory is that the parent group has a number of child groups which are controlled by various location specific administrators.

Initially I hoped to be able to achieve this by using a special query (LDAP_MATCHING_RULE_IN_CHAIN) which would walk to the root and thus include all members of the nested groups.

“(&(objectClass=user)(member:1.2.840.113556.1.4.1941:(=CN=IBM Connections Users,DC=acme,DC=com)))”

I couldn’t get this to work using ldapsearch so I had their AD admins investigate. They too could not get it to work so the work around was to add all the groups to the source_ldap_search_filter value in profiles_tdi.properties. The search filter consisted of over 26000 characters!!

On running sync_all_dns I saw failures (after enabling debug from Profiles MustGather) in the ibmdi.log. The errors matched Population or Synchronization fails trying to update the Peopledb with the error SQLCODE: -302, SQLSTATE: 22001

I was hesitant to go ahead and make the changes details and read else where that LONG VARCHAR is deprecated in 9.7 potentially meaning CLOB datatype was required. I raised a PMR with IBM to check and they came back and said that there is no change made to PROF_SOURCE_URL in the later versions of Connections. The one pain could be if a side by side migration to 4.5 (or later) is performed as the new database will have VARCHAR as the datatype. With some forward planning this migration failure can be removed.

My DB2 colleague and I decided to back up PEOPLEDB and then run the following command to dump the definitions of the database before altering the datatype.

db2look -d PEOPLEDB -f -l -e -x > D:\db2look_peopledb_prechange.txt

To alter the datatype we used the Control Center which gave us the below SQL.

CONNECT TO PEOPLEDB;
CALL SYSPROC.ALTOBJ ( ‘APPLY_CONTINUE_ON_ERROR’, ‘CREATE TABLE EMPINST.EMPLOYEE ( PROF_KEY VARCHAR (36)  NOT NULL , PROF_UID VARCHAR (256)  NOT NULL , PROF_UID_LOWER VARCHAR (256)  NOT NULL , PROF_LAST_UPDATE TIMESTAMP  NOT NULL , PROF_MAIL VARCHAR (256) , PROF_MAIL_LOWER VARCHAR (256) , PROF_GUID VARCHAR (256)  NOT NULL , PROF_SOURCE_UID VARCHAR (256)  NOT NULL , PROF_DISPLAY_NAME VARCHAR (256) , PROF_LOGIN VARCHAR (256) , PROF_LOGIN_LOWER VARCHAR (256) , PROF_GIVEN_NAME VARCHAR (128) , PROF_SURNAME VARCHAR (128) , PROF_ALTERNATE_LAST_NAME VARCHAR (64) , PROF_PREFERRED_FIRST_NAME VARCHAR (32) , PROF_PREFERRED_LAST_NAME VARCHAR (64) , PROF_TYPE VARCHAR (64) , PROF_MANAGER_UID VARCHAR (256) , PROF_MANAGER_UID_LOWER VARCHAR (256) , PROF_SECRETARY_UID VARCHAR (256) , PROF_IS_MANAGER CHARACTER (1) , PROF_GROUPWARE_EMAIL VARCHAR (128) , PROF_GW_EMAIL_LOWER VARCHAR (128) , PROF_JOB_RESPONSIBILITIES VARCHAR (128) , PROF_ORGANIZATION_IDENTIFIER VARCHAR (64) , PROF_ISO_COUNTRY_CODE VARCHAR (3) , PROF_FAX_TELEPHONE_NUMBER VARCHAR (32) , PROF_IP_TELEPHONE_NUMBER VARCHAR (32) , PROF_MOBILE VARCHAR (32) , PROF_PAGER VARCHAR (32) , PROF_TELEPHONE_NUMBER VARCHAR (32) , PROF_WORK_LOCATION VARCHAR (32) , PROF_BUILDING_IDENTIFIER VARCHAR (64) , PROF_DEPARTMENT_NUMBER VARCHAR (24) , PROF_EMPLOYEE_TYPE VARCHAR (256) , PROF_FLOOR VARCHAR (16) , PROF_EMPLOYEE_NUMBER VARCHAR (16) , PROF_PAGER_TYPE VARCHAR (16) , PROF_PAGER_ID VARCHAR (32) , PROF_PAGER_SERVICE_PROVIDER VARCHAR (50) , PROF_PHYSICAL_DELIVERY_OFFICE VARCHAR (32) , PROF_PREFERRED_LANGUAGE VARCHAR (100) , PROF_SHIFT VARCHAR (4) , PROF_TITLE VARCHAR (256) , PROF_COURTESY_TITLE VARCHAR (64) , PROF_TIMEZONE VARCHAR (64) , PROF_NATIVE_LAST_NAME VARCHAR (256) , PROF_NATIVE_FIRST_NAME VARCHAR (256) , PROF_BLOG_URL VARCHAR (256) , PROF_FREEBUSY_URL VARCHAR (256) , PROF_CALENDAR_URL VARCHAR (256) , PROF_DESCRIPTION CLOB  (1048576   )  LOGGED  NOT  COMPACT , PROF_EXPERIENCE CLOB  (1048576   )  LOGGED  NOT  COMPACT , PROF_SOURCE_URL “LONG VARCHAR” , PROF_SRC_UID_LOWER VARCHAR (256)  NOT NULL , TENANT_KEY VARCHAR (36)  NOT NULL  WITH DEFAULT ‘00000000-0000-0000-0000-040508202233’ , PROF_STATE INTEGER  NOT NULL  WITH DEFAULT 0   ) IN USERSPACE32K INDEX IN USERSPACE4K LONG IN USERSPACE32K ‘, -1, ? );
CONNECT RESET;

After running this the datatype of the column changed to LONG VARCHAR.

We run the definitions dump again and compared the contents with the pre-change information. The contents were the same albeit in a different order.

db2look -d PEOPLEDB -f -l -e -x > D:\db2look_peopledb_prechange.txt

This gave us confidence to continue and started Connections. At which point the sync_all_dns completed successfully and the users in the nested groups are populated to Profiles. Checking EMPLOYEE.PEOPLEDB shows the very long search filter in the PROF_SOURCE_URL for those that were added recently.

Cannot share folders with a community

A customer notified me of a problem a user faced when trying to share a folder to a community. Quickly we found the problem was with the community and not the folder as the folder could be shared with other communities and various folders could not be shared with this specific community. This was a community that was created in Connections 3.0.1.

The user saw different errors in the web browser compared with the Windows connector.

2013-07-15_170714

2013-07-15_170726

I found  a forum entry but it did not provide any resolution or technical details. I did some looking into SNCOMM and FILES and could not see anything obviously wrong so I raised a PMR.

IBM quickly came back and asked me to run FilesDataIntegrityService.syncAllCommunityShares(). This is a command that should be run after upgrade/migration to Connections 4.0 so with some dubiousness I ran the command. In my wsadmin window I observed a number of lines of output but two for the particular community.

[08/07/13 15:08:18:462 IST] 000028fd SyncCommunity I   EJPVJ9418I: The community 8ac6c344-43d8-4321-a292-2b952c55bd9d has been synchronized and now has visibility PRIVATE and name Parent Community.
[08/07/13 15:08:20:541 IST] 000028fd SyncCommunity I   EJPVJ9418I: The community feba9b69-6b05-45db-adb9-ea1c6d26073f has been synchronized and now has visibility PRIVATE and name sub-community.

I’m not sure what these lines mean (awaiting an answer from IBM) but it worked and the user can now share his folder with the community.