Start of change

CHANGE_USER_PROFILE table function

The CHANGE_USER_PROFILE table function changes a subset of user profile attributes.

For a detailed description of the parameters and their values, refer to the CHGUSRPRF CL command.

Authorization: This table function calls the Change User Profile (CHGUSRPRF) CL command. Any authority requirements for the CL command apply to the use of this function.

Read syntax diagramSkip visual syntax diagram CHANGE_USER_PROFILE ( P_USER_NAME =>  user-name ,P_PASSWORD => password,P_PASSWORD_EXPIRED => password-expired,P_STATUS => status,P_INITIAL_PROGRAM => initial-program,P_LIMIT_CAPABILITIES => limit-capabilities,P_TEXT => text,P_PASSWORD_EXPIRATION_INTERVAL => password-expiration-interval,P_JOB_DESCRIPTION => job-description,P_GROUP_PROFILE => group-profile,P_USER_EXPIRATION_DATE => user-expiration-date,P_USER_EXPIRATION_INTERVAL => user-expiration-interval,PREVIEW => preview )
The schema is SYSTOOLS.
user-name
A character string containing the name of the user profile whose values are to be changed.
password
A character string containing a new password value for the user profile. This is the PASSWORD parameter. The default is *SAME.
password-expired
A character string that specifies whether the password for this user is set to expired. This is the PWDEXP parameter. The default is *SAME.
status
A character string that specifies the status of the user profile. This is the STATUS parameter. The default is *SAME.
initial-program
A character string that specifies the initial program to call. This is the INLPGM parameter. The default is *SAME.
limit-capabilities
A character string that specifies the capabilities for a user. This is the LMTCPB parameter. The default is *SAME.
text
A character string that specifies the descriptive text for the user profile. This is the TEXT parameter. The default is *SAME.
password-expiration-interval
A character string that specifies the password expiration interval, in days. This is the PWDEXPITV parameter. The default is *SAME.
job-description
A character string that specifies the job description associated with this user profile This is the JOBD parameter. The default is *SAME.
group-profile
A character string that specifies the group profile associated with this user profile. This is the GRPPRF parameter. The default is *SAME.
user-expiration-date
A character string that specifies the date when the user profile expires and is automatically disabled. This is the USREXPDATE parameter. The default is *SAME.
user-expiration-interval
An integer value that specifies the expiration interval, in days, before the user profile is automatically disabled. This is the USREXPITV parameter. The default is NULL, meaning the value will not be changed.
preview
A character string that indicates whether the table function should execute the CHGUSRPRF command that has been constructed based upon the input parameters or whether only a preview of the potential action should be shown.
NO
The table function should change the user profiles
YES
The table function should return a preview of the changes. This is the default.
The result of the function is a table containing a single row with the details about the user profile change. The columns of the result table are described in the following table. The result columns are nullable.
Table 1. CHANGE_USER_PROFILE table function
Column Name Data Type Description
USER_NAME VARCHAR(10) The user profile being changed or previewed.
CHANGE_ATTEMPTED VARCHAR(3) Indicates whether the change was attempted.
NO
The change was not attempted
YES
The change was attempted
CHANGE_SUCCESSFUL VARCHAR(3) Indicates whether the change was successful.
NO
The change was not successful
YES
The change was successful

Contains the null value if CHANGE_ATTEMPTED is NO.
CHGUSRPRF_COMMAND VARCHAR(1000) The CHGUSRPRF command string.
FAILURE_MESSAGE_ID CHAR(7) The message ID.

Contains the null value if CHANGE_ATTEMPTED is NO or CHANGE_SUCCESSFUL is YES.
FAILURE_MESSAGE_TEXT VARGRAPHIC(1024)
CCSID 1200
 The text of the message.

Contains the null value if CHANGE_ATTEMPTED is NO or CHANGE_SUCCESSFUL is YES.

Note

This function is provided in the SYSTOOLS schema as a helper function to manage user profiles. Similar to other Db2 for i provided tools within SYSTOOLS, the SQL source can be extracted and used as a model for building similar helper functions, or to create a customized version within a user-specified schema.

Example

Disable all enabled user profiles that have no password set.

Preview the list of profiles that will be affected.

SELECT * FROM QSYS2.USER_INFO, 
              TABLE(SYSTOOLS.CHANGE_USER_PROFILE(
                                                 P_USER_NAME  => AUTHORIZATION_NAME, 
                                                 P_STATUS     => '*DISABLED',
                                                 PREVIEW      => 'YES'))
  WHERE STATUS = '*ENABLED' AND 
        NO_PASSWORD_INDICATOR = 'YES';

Build and execute the CHGUSRPRF commands..

SELECT * FROM QSYS2.USER_INFO, 
              TABLE(SYSTOOLS.CHANGE_USER_PROFILE(
                                                 P_USER_NAME  => AUTHORIZATION_NAME, 
                                                 P_STATUS     => '*DISABLED',
                                                 PREVIEW      => 'NO'))
  WHERE STATUS = '*ENABLED' AND 
        NO_PASSWORD_INDICATOR = 'YES';
End of change