QSYS2 is a library that has lots of system data readily available. More and more data that has traditionally been the reserve of a particular command or API is now available for query. These views have been around for years and IBM are adding more and every release.
The SQL view will have a long name equivalent for easier SQL readability, as well as, the standard IBM object notation for use in tools like Query/400 rather than SQL statements. (See Table as the end of this blog)
For example the file for querying data area's on the system is 'DATA_AREA_INFO' in SQL parlance but 'DTAARA_INF' when referred to directly as an object.
Let's take a look at some sample for the kind of data you can mine on your IBMi
Who is taking up spool file space?
SELECT USER_NAME, SUM(SIZE) AS TOTAL_SPOOL_SPACE
FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
GROUP BY USER_NAME
ORDER BY TOTAL_SPOOL_SPACE DESC LIMIT 25
Do we have very large spool files that can be purged?
SELECT * FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC ORDER BY SIZE DESC
FETCH FIRST 100 ROWS ONLY
Do we have some really old ones?
SELECT * FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
ORDER BY CREATE_TIMESTAMP
FETCH FIRST 100 ROWS ONLY
What Data area's (*DTAARA) are there in a given library and their values?
SELECT DATA_AREA_NAME, DATA_AREA_VALUE
FROM QSYS2.DATA_AREA_INFO
WHERE DATA_AREA_LIBRARY = 'YOUR_LIB'
What about the current library list?
SELECT DATA_AREA_LIBRARY, DATA_AREA_NAME, DATA_AREA_VALUE
FROM QSYS2.DATA_AREA_INFO
WHERE DATA_AREA_LIBRARY
IN (SELECT SCHEMA_NAME FROM QSYS2.LIBRARY_LIST_INFO)
I'm only interested in the user portion!
SELECT DATA_AREA_LIBRARY, DATA_AREA_NAME, DATA_AREA_VALUE
FROM QSYS2.DATA_AREA_INFO
WHERE DATA_AREA_LIBRARY
IN (SELECT SCHEMA_NAME FROM QSYS2.LIBRARY_LIST_INFO
WHERE TYPE = 'USER')
Want to know the *JOBD's that use a particular library?
SELECT JOB_DESCRIPTION_LIBRARY, JOB_DESCRIPTION, LIBRARY_LIST
FROM QSYS2.JOB_DESCRIPTION_INFO
WHERE LIBRARY_LIST LIKE '%YOUR_LIB%'
Profiles with *SECADM that are *ENABLED
SELECT * FROM QSYS2.USER_INFO
WHERE SPECIAL_AUTHORITIES LIKE '%*ALLOBJ%'
AND STATUS = '*ENABLED' ORDER BY PREVIOUS_SIGNON
Stagnant user profiles? Assist with your security compliance.
SELECT * FROM QSYS2.USER_INFO
WHERE STATUS = '*ENABLED' ORDER BY PREVIOUS_SIGNON
To explore for yourselves take a look at this page, the above is really the tip of the iceberg when it comes to managing your system.
Or look for the files in the QSYS2 library and google the documentation. You may well find this useful link.
Worst case you have the tried and tested WRKOBJ discovery method.
WRKOBJ QSYS2/*ALL *FILE
Thanks for reading.
Lee.
This is a summary of the files available for SQL. We've only just scratched the surface with these. We are only limited by our imagination with what tasks we can automate, improve etc.
SQL Object
ASP_INFO ASP_INFO
ASP_JOB_INFO ASPJ_INFO
ASP_VARY_INFO VARY_INFO
AUTHORITY_COLLECTION AUTH_COL
AUTHORITY_COLLECTION_DLO AUTH_COLDL
AUTHORITY_COLLECTION_FSOBJ AUTH_COLFS
AUTHORITY_COLLECTION_LIBRARIES AUTH_COLLI
AUTHORITY_COLLECTION_OBJECT AUTH_COLOB
AUTHORIZATION_LIST_INFO AUTHL_INFO
AUTHORIZATION_LIST_USER_INFO AUTL_USERS
AUTOSTART_JOB_INFO AUTOJ_INFO
BINDING_DIRECTORY_INFO BNDDIR_INF
BOUND_MODULE_INFO MODULE_INF
BOUND_SRVPGM_INFO SRVPGM_INF
COLLECTION_SERVICES_INFO CS_INFO
COMMAND_INFO CMD_INFO
COMMUNICATIONS_ENTRY_INFO COMM_INFO
DATA_AREA_INFO DTAARA_INF
DATA_QUEUE_INFO DTAQ_INFO
DATABASE_MONITOR_INFO DBMON_INFO
DB_TRANSACTION_INFO TRANS_INFO
DRDA_AUTHENTICATION_ENTRY_INFO DRDA_AUTHE
ELECTRONIC_SERVICE_AGENT_INFO ESA_INFO
ENVIRONMENT_VARIABLE_INFO ENV_VARS
EXIT_POINT_INFO EXIT_POINT
EXIT_PROGRAM_INFO EXIT_PGM
FUNCTION_INFO FCN_INFO
FUNCTION_USAGE FCN_USAGE
HARDWARE_RESOURCE_INFO HW_INFO
HTTP_SERVER_INFO HTTP_SRVR
JOB_DESCRIPTION_INFO JOBD_INFO
JOB_QUEUE_INFO JOBQ_INFO
JOURNAL_INFO JRNINFO
JOURNAL_INHERIT_RULES LIB_JRN
JOURNAL_RECEIVER_INFO JRNRCV_INF
JOURNALED_OBJECTS JRN_OBJS
LIBRARY_LIST_INFO LIBLIST
LICENSE_INFO LIC_INFO
LOCKING_POLICY_INFO LOCK_POL
MEDIA_LIBRARY_INFO MEDIA_INFO
MESSAGE_FILE_DATA MSGF_DATA
MESSAGE_QUEUE_INFO MSGQ_INFO
NETSTAT_INFO NS_INFO
NETSTAT_INTERFACE_INFO NS_INTER
NETSTAT_JOB_INFO NS_JOB
NETSTAT_ROUTE_INFO NS_ROUTE
NVME_INFO NVME_INFO
OBJECTCONNECT_INFO OBJC_INFO
OBJECT_LOCK_INFO OBJ_LOCK
OBJECT_OWNERSHIP OBJ_OWN
OBJECT_PRIVILEGES OBJ_PRIV
OUTPUT_QUEUE_ENTRIES OUTQ_INFO
OUTPUT_QUEUE_ENTRIES_BASIC OUTQ_INFOB
OUTPUT_QUEUE_INFO OUTQ_DTL
PRESTART_JOB_INFO PREJ_INFO
PROGRAM_EXPORT_IMPORT_INFO EXPIMP_INF
PROGRAM_INFO PGM_INFO
QUERY_SUPERVISOR QRY_SUPER
QRY_SUPERI
QRY_SUPERT
QRY_SUPERX
RECORD_LOCK_INFO RCD_LOCK
REMOTE_JOURNAL_INFO RMT_JRNS
REPLY_LIST_INFO REPLYLIST
ROUTING_ENTRY_INFO RTG_INFO
SCHEDULED_JOB_INFO SCHED_JOB
SECURITY_INFO SEC_INFO
SERVER_SBS_CONFIGURATION SERVER_CFG
SERVER_SBS_ROUTING SRVR_RTG
SERVER_SHARE_INFO SHARE_INFO
SERVICES_INFO SERV_INFO
SOFTWARE_PRODUCT_INFO SFW_PROD
SUBSYSTEM_INFO SBS_INFO
SUBSYSTEM_POOL_INFO SBS_POOL
SYSLIMITS SYSLIMITS
SYSLIMITS_BASIC SYSLIMIT_B
SYSPACKAGESTMTSTAT PKGSTMSTAT
SYSPROGRAMSTMTSTAT PGMSTMSTAT
SYSTEM_STATUS_INFO SYS_STATUS
SYSTEM_STATUS_INFO_BASIC SYS_STAT_B
SYSTEM_VALUE_INFO SYSVALINFO
TELNET_SERVER_ATTRIBUTES TN_ATTR
TIME_PROTOCOL_INFO TIME_PROTO
TRACKED_JOB_QUEUES TRACKED_JQ
USER_INDEX_INFO USRIDX_INF
USER_INFO USER_INFO
USER_INFO_BASIC USER_INFOB
USER_SPACE_INFO USRSPC_INF
USER_STORAGE USER_STG
WATCH_INFO WATCH_INFO
WORKLOAD_GROUP_INFO WLG_INFO
Additional SYS files. That might come in handy. Again lots and lots of information readily available.
SYSCAT1
SYSCAT2
SYSCHARSET
SYSCHKCST
SYSCHRSET1
SYSCHRSET2
SYSCOLAUTH
SYSCOLUMNS
SYSCOLUMN2
SYSCOL2SSN
SYSCONTROL
SYSCST
SYSCSTAT
SYSCSTCOL
SYSCSTDEP
SYSCTRLDEP
SYSDISKS
SYSFEATURE
SYSFIELDS
SYSFILES
SYSFUNCS
SYSHIST
SYSINDEXES
SYSIXADV
SYSIXADVIX
SYSIXSTAT
SYSJARCONT
SYSJAROBJ
SYSKEYCST
SYSKEYS
SYSLANGS
SYSLIMPO_A
SYSLIMPO_B
SYSLIMTBL
SYSMQTSTAT
SYSPACKAGE
SYSPARMS
SYSPDISK
SYSPERIODS
SYSPGSTAT
SYSPIDISK
SYSPINDEX
SYSPISTAT
SYSPKAUTH
SYSPKSTAT
SYSPMQT
SYSPROCS
SYSPSTAT
SYSREFCST
SYSROUTDEP
SYSROUTINE
SYSROUTINP
SYSRTNAUTH
SYSRTNDEP
SYSSCHAUTH
SYSSCHEMAS
SYSSEQ
SYSSEQAUTH
SYSSEQOBJ
SYSTABAUTH
SYSTABDEP
SYSTABLES
SYSTISTAT
SYSTMPSTG
SYSTRIGCOL
SYSTRIGDEP
SYSTRIGGER
SYSTRIGUPD
SYSTSTAT
SYSTXTCLMN
SYSTXTCNFG
SYSTXTCOLI
SYSTXTDFLT
SYSTXTINDI
SYSTXTINDX
SYSTXTLBLS
SYSTXTSRHS
SYSTXTSRVR
SYSTYPES
SYSUDTAUTH
SYSVARAUTH
SYSVARDEP
SYSVARS
SYSVIEWDEP
SYSVIEWS
SYSXSRAUTH
