Re: Remove special authority

So what you're looking for is something like this:
SELECT AUTHORIZATION_NAME, SPECIAL_AUTHORITIES,
REPLACE(SPECIAL_AUTHORITIES, '*IOSYSCFG', ''),
'CHGUSRPRF USRPRF(' CONCAT AUTHORIZATION_NAME CONCAT
') SPCAUT(' CONCAT REPLACE(SPECIAL_AUTHORITIES, '*IOSYSCFG', '')
CONCAT ')' as COMMAND,
STATUS, NO_PASSWORD_INDICATOR, PREVIOUS_SIGNON, TEXT_DESCRIPTION
FROM QSYS2.USER_INFO
WHERE SPECIAL_AUTHORITIES LIKE '%*IOSYSCFG%'
ORDER BY AUTHORIZATION_NAME;

But you want it to actually execute the command COMMAND.
At Dekko we created our own QCMDEXC UDF for just such things. So the
above would become:
REPLACE(SPECIAL_AUTHORITIES, '*IOSYSCFG', ''),
'CHGUSRPRF USRPRF(' CONCAT AUTHORIZATION_NAME CONCAT
') SPCAUT(' CONCAT REPLACE(SPECIAL_AUTHORITIES, '*IOSYSCFG', '')
CONCAT ')' as COMMAND,
QCMDEXC('CHGUSRPRF USRPRF(' CONCAT AUTHORIZATION_NAME CONCAT
') SPCAUT(' CONCAT REPLACE(SPECIAL_AUTHORITIES, '*IOSYSCFG', '')
CONCAT ')')
STATUS, NO_PASSWORD_INDICATOR, PREVIOUS_SIGNON, TEXT_DESCRIPTION
FROM QSYS2.USER_INFO
WHERE SPECIAL_AUTHORITIES LIKE '%*IOSYSCFG%'
ORDER BY AUTHORIZATION_NAME;

But if you want to stick with IBM you can write a stored procedure program
to do it.
See
http://ibm.biz/DB2foriServices


Rob Berendt