A customer would like to know which IBM Connections Communities have a Sharepoint widget added. The following SQL statement should give the UUID and the name of these Communities:
select COMMUNITY_UUID,NAME from SNCOMM.COMMUNITY where COMMUNITY_UUID IN(select EXT_RESOURCE_ID from SNCOMM.LC_EXTENSIONS where dbms_lob.instr(EXT_VALUE_EXTENDED,utl_raw.cast_to_raw ('SharePointFiles'), 1,1)>0); The customer also wanted to send a mail to all creators of these Communities. So he wanted to get a list of names and mail adresses of them. The following SQL statement creates that list:
select DISTINCT DISPLAY,EMAIL from SNCOMM.MEMBERPROFILE where MEMBER_UUID IN (select CREATED_BY from SNCOMM.MEMBER where COMMUNITY_UUID in (select COMMUNITY_UUID from SNCOMM.COMMUNITY where COMMUNITY_UUID IN (select EXT_RESOURCE_ID from SNCOMM.LC_EXTENSIONS where dbms_lob.instr(EXT_VALUE_EXTENDED,utl_raw.cast_to_raw ('SharePointFiles'), 1,1)>0))); As the original creator of a Community might no longer be in the company, it would be better to get a list of all Community owners who do have at least one Community with a Sharepoint widget added. You get that list with this statement:
select DISTINCT DISPLAY,EMAIL from SNCOMM.MEMBERPROFILE,SNCOMM.COMMUNITY where MEMBER_UUID IN (select MEMBER_UUID from SNCOMM.MEMBER where ROLE='1' AND COMMUNITY_UUID in (select COMMUNITY_UUID from SNCOMM.COMMUNITY where COMMUNITY_UUID IN (select EXT_RESOURCE_ID from SNCOMM.LC_EXTENSIONS where dbms_lob.instr(EXT_VALUE_EXTENDED,utl_raw.cast_to_raw ('SharePointFiles'), 1,1)>0))); You can use the same statements to find out about other widgets. Just replace “SharePointFiles” with the widget id of your choice.
The statements do work for ORACLE and you may need to adapt them for DB2.