Schema IQUERY
RTVUSRPRF( user_name => '*CURRENT',
info_detail => '*ALL' | '*ALLSIMPLE')
The Retrieve User Profile (RTVUSRPRF) User Defined Table Function returns one row with the specified User Profile Description. The information returned is similar to the QSYRUSRI API with format USRI0300.
Default: *CURRENT
The User Profile whose information is to be returned. In addition to a user profile, the special value '*CURRENT' may be specified or the SQL Registers: USER, CURRENT_USER, or SYSTEM_USER may be specified.
Default: *ALL
Specifies the level of detail returned. When *ALLSIMPLE is specified, only the User Profile Status, Last Signon, and Password related information is returned.
select home_dir, group_Profile, curlib, initial_program, initial_program_library
INTO :homeDir, :group, :curlib, :inlpgm, :inlpgmLib
FROM TABLE( iQuery.RTVUSRPRF( 'RON' )) usr;
| Column |
Data Type |
Description |
| USER_PROFILE |
VARCHAR(10) |
The name of the object whose description is
returned. |
| PREVIOUS_SIGNON |
TIMESTAMP | The library name of the object returned (as
specified on the UDTF itself. |
| SIGN_ON_ATTEMPTS_NOT_VALID |
INT | The object type of the object. |
| USER_STATUS |
VARCHAR(10) | The *ENABLED or *DISABLED status of the User
Profile. |
| NO_PASSWORD |
VARCHAR(10) | If the User Profile has no password
associated with it, this column contains *NOPWD otherwise it
is blank. |
| PASSWORD_EXPIRATION_INTERVAL |
SMALLINT | The frequency of how often password change is required. (in RPG IV, this is an int(5) value) |
| PASSWORD_CHANGED_DATE |
TIMESTAMP | The The date/time the password was last
changed. |
| PASSWORD_EXPIRATION_DATE |
TIMESTAMP |
The Date on which the password expires. |
| PASSWORD_DAYS_UNTIL_EXPIRES |
SMALLINT |
The Number of days until the password
expires. (in RPG IV, this is an int(5) value) |
| SET_PASSWORD_TO_EXPIRE |
VARCHAR(10) |
*YES if the password will expire, otherwise
*NO. |
| DISPLAY_SIGNON_INFO |
VARCHAR(10) |
*YES if the User is shown the previously signon information when they sign onto the system, otherwise *NO. |
| USER_CLASS |
VARCHAR(10) |
The class assigned to the User Profile. Valid
entries include: *USER, *PGMR, *SYSOPR, *SECADM, and *SECOFR |
| SPECIAL_AUTH |
VARCHAR(96) |
The list of special authorities assigned to
the User Profile, including: *ALLOBJ *SECADM *JOBCTL *SPLCTL
*SAVSYS *SERVICE *AUDIT *IOSYSCFG |
| OWNER | VARCHAR(10) |
The User Profile of the Owner of this User
Profile. |
| GROUP_PROFILE |
VARCHAR(10) | The Group Profile assigned to this User
Profile. |
| GROUP_AUTH |
VARCHAR(10) | The Authority assigned to the Group Profile
when this user creates an object. Valid entries include:
*NONE, *ALL, *CHANGE, *USE, and *EXCLUDE |
| SUP_GROUP_COUNT |
SMALLINT |
If the User Profile is assigned any
additional group profiles ("Supplemental Groups) the number
of them are returned to this column. |
| SUP_GROUP_PROFILES |
VARCHAR(165) | If the SUP_GROUP_COUNT is > 0, this column
contains each of the supplemental group profiles. Each Group
Profile occupies 11 bytes so that the list is
blank-separated. |
| ASSISTANCE_LEVEL |
VARCHAR(10) | The User Interface Assistance level for the
User Profile. Valid entries are: *INTERMED, *ADVANCED, and
*BASIC. If *SYSVAL is returned, the the System Value QASTLVL
is used. |
| CURLIB | VARCHAR(10) | The name of the library that is assigned as the Current Library (*CURLIB) when the user starts a job on the system. If the is no *CURLIB assigned to this User Profile the value *CRTDFT is returned which typically refers to QGPL as the current library. |
| INITIAL_MENU |
VARCHAR(10) | The name of the initial menu shown tot he
user after signon. If this column returns *SIGNOFF then no
Menu is assigned to the User profile and interactive jobs
will sign off after the initial program runs. |
| INITIAL_MENU_LIBRARY |
VARCHAR(10) | The library where the Initial Menu exists. If
the Initial Menu is *SIGNOFF this column returns blanks. |
| INITIAL_PROGRAM |
VARCHAR(10) | The name of the initial program to run when
the User Profile starts a job an interactive job the
system. If no initial program is assigned to the User
Profile this column returns *NONE. |
| INITIAL_PROGRAM_LIBRARY |
VARCHAR(10) |
The library name where the initial program
exists. If the Initial Program is *NONE, then this column
returns blanks. |
| TEXT_DESCRIPTION | VARCHAR(50) | The text description associated with User
Profile. |
| LIMIT_CAPABILITIES |
VARCHAR(10) |
The Limit Capabilities flag for the User
Profile, then this column returns *YES, When it is not set,
this column returns *NO. When partial limited capabilities
is specified, this column returns *PARTIAL |
| LIMIT_DEVICE_SESSIONS |
VARCHAR(10) |
The maximum number of 5250 sessions the User
Profile can activate at one time. *YES means the User is
limited to 1 device. *NO means the user is not limited. In
addition, a value of *SYSVAL means the system value
QLMTDEVSSN controls this attribute. In addition, numeric
values (returned as text/character) of 0 thru 9 may be
returned. |
| KEYBOARD_BUFFERING |
VARCHAR(10) |
The type-ahead buffer property for 5250 jobs
the User Profile starts. *SYSVAL means use the value in the
QKBDBUF System value. *YES means the type-ahead is enabled,
*NO means type-ahead is disabled, and *TYPEAHEAD means the
type ahead is on except for the Attn (Attention) key. |
| MAXIMUM_STORAGE |
BIGINT |
The maximum storage the user is permitted to own. In RPG IV this is an INT(20) value. Note that a -1 value is returned when *NOMAX is intended. The actual *NOMAX value returned is X' 7FFFFFFFFFFFFFFF'. The numerical value for *NOMAX is: 9223372036854775807 |
| STORAGE_USED |
BIGINT |
The amount of storage the User Profile
currently owns. Note: There is a nice little macro in SQL
iQuery that generates a pie chart that can be viewed or
emailed. To run it, use this command: RUNiQRY
*STGUSERS EMAIL(xxx@yyyy.com) |
| HIGHEST_SCHEDULING_PRIORITY |
CHAR(1) |
The highest scheduling priority allowed for
the User Profile. Valid results are 0 to 9. In this context,
the lower the number, the higher the priority. |
| JOBD |
VARCHAR(10) |
The Job Description associated with the
User's Interactive Jobs. |
| JOBD_LIBRARY | VARCHAR(10) |
The library for the Job Description. If there
is no job description, this column returns blanks, but can
also contain *LIBL if that were specified for the job
description. |
| ACCOUNTING_CODE |
VARCHAR(15) | The accounting code associated with the User
Profile. |
| MSGQ |
VARCHAR(10) | The Message Queue associated with the user's
interactive jobs. |
| MSGQ_LIBRARY | VARCHAR(10) | The name of the library for the Message
Queue. |
| MSGQ_DELIVERY |
VARCHAR(10) |
The delivery for the user's message quque
*NOTIFY, *BREAK, etc. |
| MSGQ_SEV |
SMALLINT | The lowest message severity level that
triggers a break or notification message for the message
queue associated with the User Profile. |
| OUTQ |
VARCHAR(10) | The Output Queue assigned to jobs the user
profile starts. |
| OUTQ_LIBRARY |
VARCHAR(10) | The output queue library name. |
| PRINT_DEVICE |
VARCHAR(10) | The name of the print device. |
| SPECIAL_ENV |
VARCHAR(10) | The legacy compatibility flag for the old
System/36 ("S36E") environment. Virtually all users have
*NONE or *SYSVAL for this column which can typically be
ignored. |
| ATTN_KEY_PROGRAM |
VARCHAR(10) | The name of a program to run when the User
presses the Attn (Attention) key on interactive jobs. This
is normally marked "esc" on modern keyboards. |
| ATTN_KEY_LIBRARY |
VARCHAR(10) | The Attention key program's library name. |
| LANGUAGE_ID | VARCHAR(10) | The Language ID for this User. The Language
ID is typically 3-character in length, however *SYSVAL can
be returned indicating that the QLANGID system value is
used. |
| COUNTRY_ID |
VARCHAR(10) | The Country Code assigned to the User. The
Country Code is typically 2-characters, however *SYSVAL can
be returned indicating that the QCNTRYID system value is
used. |
| CCSID | INT | The CCSID associated with the User Profile.
When this column returns -2, it means the system value
QCCSID is used. |
| USER_OPTIONS |
VARCHAR(96) | The User custom options. Values include
*CLKWD, *EXPERT, *HLPFULL (full screen helP) *STSMSG,
*NOSTSMSG, *ROLLKEY, *PRTMSG. |
| SORT_SEQ_TABLE | VARCHAR(10) | The Sort Sequence table object used to
compare string/text values. Special values that may be
returned include: *HEX, *LANGIDUNQ, *LANGIDSHR, or *SYSVAL
to indicate that sort sequence is derived from the QSRTSEQ
system value. |
| SORT_SEQ_TABLE_LIBRARY | VARCHAR(10) | The Source Sequence library. |
| OBJECT_AUDITING | VARCHAR(10) | The object auditing setting. Valid results include: *NONE, *CHANGE, *ALL, *NOTAVL. The value *NOTAVL is returned if the User running the RTVUSRPRF UDTF does NOT have *ALLOBJ or *AUDIT special authority. |
| USER_ACTION_AUDIT_LEVEL |
VARCHAR(64) | The action audit values for this user profile. The user running the RTVUSRPRF UDTF must have *ALLOBJ or *AUDIT special authority to retrieve this value. |
| GROUP_AUTH_TYPE |
VARCHAR(10) | The type of authority assigned to the Group
Profile for objects that the User Profile creates. The valid
results include: *PRIVATE which means the Group Profile is
assigned specific/private authority to the object, while
*PGP means the Group Profile is the primary group for the
objects created by the user profile. |
| USER_ID | INT | The internal ID for the User Profile. |
| GROUP_ID | INT | The internal ID for the User Profile when it
is also a Group Profile. |
| IS_GROUP_PROFILE |
VARCHAR(10) |
If this User Profile is a Group Profile, *YES
is returned, otherwise *NO is returned. |
| LOCAL_PASSWORD_MANAGEMENT |
VARCHAR(10) |
If Local Password Management is used for this
User Profile, *YES is returned, otherwise *NO is returned. |
| BLOCK_PASSWORD_CHANGE |
VARCHAR(10) |
Controls when a User Profile is blocked from
changing their password. *NONE means they can always change
it. *SYSVAL means the System Value QPWDCHGBLK is used to
control this setting. Otherwise a numeric value (as text) is
returned with a value of 0 to 99 to indicate the number of
hours the user has to wait between password changes. |
| USER_ENTITLEMENT_REQUIRED |
VARCHAR(10) |
If User Entitlement is require for feature
code 5052 this column returns *YES, otherwise it returns
*NO. |
| USRPRF_EXPIRATION_INTERVAL |
INT |
The number of days before the User Profile
expires and the USRPRF_EXPIRATION_ACTION is performed. This
column returns 1 to 366 or if the user profile does not
expire, this column contains 0. |
| USRPRF_EXPIRATION_DATE |
TIMESTAMP |
The date/time this User Profile expires or
NULL if it never expires. This date is calculated by the
system. |
| USRPRF_EXPIRATION_ACTION |
VARCHAR(8) |
When the User Profile expires, the action to
be taken is returned to this column. Valid results include:
*NONE (nothing will happen, the User Profile will not
expire) *DISABLE (the user profile will have its USER_STATUS
changed to *DISABLED) or *DELETE (the system will attempt to
delete the user profile). |
| HOME_DIR |
VARCHAR(1024) |
The Home directory for the User Profile. The
first 1024 bytes of this directory are returned. |
| LOCALE_PATH_NAME |
VARCHAR(1024) |
The Locale (pronounced LO-Cal) that contains
the location settings for the User Profile. |
| ERRMSGID |
VARCHAR(7) | Normally this column should be blank. If it
contains a valid CPF message ID it represents the error
returned when the UDTF called one of the low-level
interfaces. |