Today i read a question in the IBM Connections Forum about setting the EMPLOYEE_EXTENDED role to all users in a Connections deployment.

It would be easy to set it directly in the database, but that’s not supported by IBM.

I wrote a little script some weeks ago, because i had the same request, but never published it. The good thing here it only uses supported commands.

So i use this question to add it as a new script to my GitHub Repository .

Here you see the source, it is simple a join of the tables emp_role_map and employee:

connect to peopledb;
EXPORT TO mail.txt OF DEL MODIFIED by NOCHARDEL
select e.PROF_MAIL FROM EMPINST.EMPLOYEE e
    inner join EMPINST.EMP_ROLE_MAP r
    on r.PROF_KEY=e.PROF_KEY
    where r.ROLE_ID!='employee.extended';
connect reset;

Just call it with db2 -tvf scriptname.sql.

The script exports a list of mail addresses of users without the specified role. This can then be used with the wsadmin command

ProfilesService.setBatchRole(EMPLOYEE_EXTENDED, "mail.txt")

Or as a oneliner:

wsadmin.bat -lang jython -c 'ProfilesService.setBatchRole(EMPLOYEE_EXTENDED, "mail.txt")'

Scheduling the two commands with Windows Scheduler or cron is enough to update all users to the specified role.

Documentation is provided in the script head.