Developer ToolsFree referencePDF, Excel, Word exportIBM iAll IBM i (AS/400) ERPs tools

IBM i Services SQL Reference: QSYS2 and SYSTOOLS Views and Functions

IBM i Services are views, table functions and procedures in QSYS2 and SYSTOOLS that return system information as SQL result sets. This reference lists the ones admins and developers use most, the CL command each replaces, and a statement you can run from Run SQL Scripts, STRSQL or any ODBC or JDBC client.

Download:

Service	Type	Replaces or mirrors	Returns	Sample SQL	Release note
QSYS2.ACTIVE_JOB_INFO	Table function	WRKACTJOB	Active jobs with CPU, I/O, status, function	SELECT JOB_NAME, AUTHORIZATION_NAME, JOB_STATUS, FUNCTION FROM TABLE(QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'NONE')) X WHERE JOB_STATUS = 'MSGW'	Parameters SUBSYSTEM_LIST_FILTER, JOB_NAME_FILTER, CURRENT_USER_LIST_FILTER, DETAILED_INFO
QSYS2.JOB_INFO	Table function	WRKUSRJOB, WRKSBMJOB	Jobs in any state: ACTIVE, JOBQ, OUTQ	SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_STATUS_FILTER => 'JOBQ', JOB_USER_FILTER => 'ALL')) X	Filters: JOB_STATUS_FILTER, JOB_TYPE_FILTER, JOB_SUBSYSTEM_FILTER, JOB_USER_FILTER
QSYS2.SYSTEM_STATUS_INFO	View	WRKSYSSTS	One row: jobs in system, ASP used, CPU, memory, job table	SELECT TOTAL_JOBS_IN_SYSTEM, ACTIVE_JOBS_IN_SYSTEM, SYSTEM_ASP_USED, CONFIGURED_CPUS FROM QSYS2.SYSTEM_STATUS_INFO	Use SYSTEM_STATUS_INFO_BASIC for a cheaper subset when polling often
QSYS2.SYSTEM_STATUS_INFO_BASIC	View	WRKSYSSTS	Faster subset of SYSTEM_STATUS_INFO	SELECT * FROM QSYS2.SYSTEM_STATUS_INFO_BASIC	Prefer for monitoring scripts
QSYS2.SYSTEM_ACTIVITY_INFO	Table function	WRKSYSACT (summary)	CPU utilisation figures	SELECT * FROM TABLE(QSYS2.SYSTEM_ACTIVITY_INFO())	Call with no parameters for current CPU and activity figures
QSYS2.SYSTEM_VALUE_INFO	View	WRKSYSVAL, DSPSYSVAL	Current value and shipped default per system value	SELECT SYSTEM_VALUE_NAME, SYSTEM_VALUE, SHIPPED_DEFAULT_VALUE FROM QSYS2.SYSTEM_VALUE_INFO WHERE SYSTEM_VALUE_NAME LIKE 'QPWD%'	Good for audit diffs between partitions
QSYS2.USER_INFO	View	DSPUSRPRF, WRKUSRPRF	User profiles: status, last sign-on, special authorities, password dates	SELECT AUTHORIZATION_NAME, STATUS, PREVIOUS_SIGNON, SPECIAL_AUTHORITIES FROM QSYS2.USER_INFO WHERE PREVIOUS_SIGNON < CURRENT TIMESTAMP - 90 DAYS	Needs OBJOPR and READ on the profiles; USER_INFO_BASIC is faster
QSYS2.USER_INFO_BASIC	View	DSPUSRPRF	Faster subset of USER_INFO	SELECT AUTHORIZATION_NAME, STATUS FROM QSYS2.USER_INFO_BASIC WHERE STATUS = '*DISABLED'	Use for large profile counts
QSYS2.OBJECT_STATISTICS	Table function	DSPOBJD, WRKOBJ	Objects in a library with size, created, last used, days used	SELECT OBJNAME, OBJTYPE, OBJSIZE, LAST_USED_TIMESTAMP FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB', 'PGM SRVPGM')) X ORDER BY OBJSIZE DESC	Third parameter '*ALLSIMPLE' returns names fast without detail
QSYS2.LIBRARY_INFO	Table function	DSPLIB, RTVLIBD	Library size and object count	SELECT OBJECT_COUNT, LIBRARY_SIZE FROM TABLE(QSYS2.LIBRARY_INFO('MYLIB')) X	DETAILED_INFO => 'NO' skips the size calculation
QSYS2.SYSTABLESTAT	Catalog view	DSPFD (member sizes)	Rows, deleted rows and size per table	SELECT TABLE_NAME, NUMBER_ROWS, NUMBER_DELETED_ROWS, DATA_SIZE FROM QSYS2.SYSTABLESTAT WHERE TABLE_SCHEMA = 'MYLIB' ORDER BY NUMBER_DELETED_ROWS DESC	Db2 for i catalog; finds files that need RGZPFM
QSYS2.SYSPARTITIONSTAT	Catalog view	DSPFD per member	Statistics per table partition or member	SELECT TABLE_NAME, TABLE_PARTITION, NUMBER_ROWS FROM QSYS2.SYSPARTITIONSTAT WHERE TABLE_SCHEMA = 'MYLIB'	Use for multi-member physical files
QSYS2.SYSTABLES / QSYS2.SYSCOLUMNS	Catalog view	DSPFFD	Tables and their columns, types, lengths	SELECT COLUMN_NAME, DATA_TYPE, LENGTH, NUMERIC_SCALE FROM QSYS2.SYSCOLUMNS WHERE TABLE_SCHEMA = 'MYLIB' AND TABLE_NAME = 'CUSTMAST'	Db2 for i catalog
QSYS2.HISTORY_LOG_INFO	Table function	DSPLOG	QHST messages with timestamp, ID, severity, from job	SELECT MESSAGE_TIMESTAMP, MESSAGE_ID, FROM_JOB, MESSAGE_TEXT FROM TABLE(QSYS2.HISTORY_LOG_INFO(START_TIME => CURRENT TIMESTAMP - 1 DAY)) X WHERE SEVERITY >= 40	Parameters START_TIME, END_TIME
QSYS2.MESSAGE_QUEUE_INFO	View	DSPMSG	Messages on queues such as QSYSOPR	SELECT MESSAGE_TIMESTAMP, MESSAGE_ID, MESSAGE_TEXT FROM QSYS2.MESSAGE_QUEUE_INFO WHERE MESSAGE_QUEUE_NAME = 'QSYSOPR' AND MESSAGE_TYPE = 'INQUIRY'	Also available as a table function
QSYS2.JOBLOG_INFO	Table function	DSPJOBLOG	Job log messages of a job	SELECT MESSAGE_ID, MESSAGE_TEXT FROM TABLE(QSYS2.JOBLOG_INFO('*')) X	'*' is the current job
QSYS2.OUTPUT_QUEUE_ENTRIES	View	WRKOUTQ	One row per spooled file in every output queue	SELECT OUTPUT_QUEUE_NAME, SPOOLED_FILE_NAME, USER_NAME, SIZE, CREATE_TIMESTAMP FROM QSYS2.OUTPUT_QUEUE_ENTRIES WHERE OUTPUT_QUEUE_NAME = 'PRT01'	OUTPUT_QUEUE_ENTRIES_BASIC is much faster
QSYS2.SPOOLED_FILE_INFO	Table function	WRKSPLF	Spooled files filtered by user, time, status, queue	SELECT SPOOLED_FILE_NAME, STATUS, SIZE, CREATION_TIMESTAMP FROM TABLE(QSYS2.SPOOLED_FILE_INFO(USER_NAME => 'BATCHUSR')) X	Other filters: STARTING_TIMESTAMP, STATUS, JOB_NAME, OUTPUT_QUEUE, USER_DATA
QSYS2.NETSTAT_INFO	View	NETSTAT *CNN	TCP and UDP connections, ports, state	SELECT REMOTE_ADDRESS, REMOTE_PORT, LOCAL_PORT, TCP_STATE FROM QSYS2.NETSTAT_INFO WHERE LOCAL_PORT = 8471	8471 is the database host server port
QSYS2.NETSTAT_JOB_INFO	View	NETSTAT option 3 then F14	Connections with the job and user that own them	SELECT JOB_NAME, AUTHORIZATION_NAME, REMOTE_ADDRESS, LOCAL_PORT FROM QSYS2.NETSTAT_JOB_INFO WHERE LOCAL_PORT = 8471	Find which PC holds an ODBC job
QSYS2.PTF_INFO	View	DSPPTF	PTFs with loaded status and IPL action	SELECT PTF_PRODUCT_ID, PTF_IDENTIFIER, PTF_LOADED_STATUS, PTF_IPL_ACTION FROM QSYS2.PTF_INFO WHERE PTF_IPL_ACTION <> 'NONE'	PTF_IPL_ACTION shows what changes at the next IPL
QSYS2.GROUP_PTF_INFO	View	WRKPTFGRP	PTF groups installed and their status	SELECT * FROM QSYS2.GROUP_PTF_INFO ORDER BY PTF_GROUP_NAME	Local information only
SYSTOOLS.GROUP_PTF_CURRENCY	View	Manual check of the IBM PSP site	Installed vs available level for each PTF group	SELECT PTF_GROUP_ID, PTF_GROUP_TITLE, PTF_GROUP_LEVEL_INSTALLED, PTF_GROUP_LEVEL_AVAILABLE FROM SYSTOOLS.GROUP_PTF_CURRENCY ORDER BY PTF_GROUP_LEVEL_AVAILABLE - PTF_GROUP_LEVEL_INSTALLED DESC	Needs internet access from the partition to IBM
QSYS2.JOURNAL_INFO	View	WRKJRNA	Journals with attached receiver, receiver count and total size	SELECT JOURNAL_LIBRARY, JOURNAL_NAME, ATTACHED_JOURNAL_RECEIVER_NAME, NUMBER_JOURNAL_RECEIVERS, TOTAL_SIZE_JOURNAL_RECEIVERS FROM QSYS2.JOURNAL_INFO ORDER BY TOTAL_SIZE_JOURNAL_RECEIVERS DESC	Sizes in KB
QSYS2.JOURNAL_RECEIVER_INFO	View	WRKJRNRCV, DSPJRNRCVA	Receiver size, entry count, status, attach and detach times	SELECT JOURNAL_RECEIVER_NAME, SIZE, NUMBER_OF_JOURNAL_ENTRIES, STATUS, DETACH_TIMESTAMP FROM QSYS2.JOURNAL_RECEIVER_INFO WHERE JOURNAL_RECEIVER_LIBRARY = 'MYJRNLIB'	SIZE in KB
QSYS2.DISPLAY_JOURNAL	Table function	DSPJRN	Journal entries with code, type, object, timestamp, entry data	SELECT ENTRY_TIMESTAMP, JOURNAL_CODE, JOURNAL_ENTRY_TYPE, OBJECT FROM TABLE(QSYS2.DISPLAY_JOURNAL('MYJRNLIB', 'MYJRN', STARTING_TIMESTAMP => CURRENT TIMESTAMP - 1 HOUR)) X	First two parameters: journal library, journal name
QSYS2.SCHEDULED_JOB_INFO	View	WRKJOBSCDE	Job schedule entries with next run, command, last result	SELECT SCHEDULED_JOB_NAME, STATUS, NEXT_SUBMISSION_DATE, COMMAND_STRING FROM QSYS2.SCHEDULED_JOB_INFO ORDER BY NEXT_SUBMISSION_DATE	Full visibility needs *JOBCTL
QSYS2.JOB_QUEUE_INFO	View	WRKJOBQ	Job queues, status, jobs waiting, subsystem	SELECT JOB_QUEUE_NAME, JOB_QUEUE_STATUS, NUMBER_OF_JOBS, SUBSYSTEM_NAME FROM QSYS2.JOB_QUEUE_INFO WHERE NUMBER_OF_JOBS > 0	Spot held queues before the night batch
QSYS2.SUBSYSTEM_INFO	View	WRKSBS	Subsystems, status, current active jobs	SELECT SUBSYSTEM_DESCRIPTION, STATUS, CURRENT_ACTIVE_JOBS FROM QSYS2.SUBSYSTEM_INFO WHERE STATUS = 'ACTIVE'
QSYS2.RECORD_LOCK_INFO	View	WRKOBJLCK, DSPRCDLCK	Record locks held and requested	SELECT * FROM QSYS2.RECORD_LOCK_INFO WHERE TABLE_SCHEMA = 'MYLIB' AND TABLE_NAME = 'CUSTMAST'	Use with RPG status 01218
QSYS2.ASP_INFO	View	WRKDSKSTS (capacity), WRKASPCFG	Capacity and free space per ASP in MB	SELECT ASP_NUMBER, ASP_STATE, TOTAL_CAPACITY, TOTAL_CAPACITY_AVAILABLE FROM QSYS2.ASP_INFO
QSYS2.LICENSE_INFO	View	WRKLICINF	Licensed products, usage limit, count, expiration	SELECT PRODUCT_ID, FEATURE_ID, USAGE_LIMIT, USAGE_COUNT, LICENSE_EXPIRATION FROM QSYS2.LICENSE_INFO WHERE LICENSE_EXPIRATION <= CURRENT DATE + 14 DAYS	Spot expiring keys before they stop a product
QSYS2.OBJECT_PRIVILEGES	View	DSPOBJAUT	Private and public authorities per object	SELECT SYSTEM_OBJECT_NAME, AUTHORIZATION_NAME, OBJECT_AUTHORITY FROM QSYS2.OBJECT_PRIVILEGES WHERE SYSTEM_OBJECT_SCHEMA = 'MYLIB' AND AUTHORIZATION_NAME = '*PUBLIC'	Also a table function form
QSYS2.SERVER_SBS_ROUTING	View	Manual QSYS2.SET_SERVER_SBS_ROUTING review	Users routed to alternate subsystems for host servers like QZDASOINIT	SELECT AUTHORIZATION_NAME, QZDASOINIT_SUBSYSTEM, QZDASOINIT_ROLLOVER FROM QSYS2.SERVER_SBS_ROUTING	Set with QSYS2.SET_SERVER_SBS_ROUTING
QSYS2.IFS_OBJECT_STATISTICS	Table function	WRKLNK, DSPLNK	IFS objects with size, owner, change and use times	SELECT PATH_NAME, OBJECT_TYPE, DATA_SIZE, DATA_CHANGE_TIMESTAMP FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/home', SUBTREE_DIRECTORIES => 'YES')) X ORDER BY DATA_SIZE DESC	Large trees take time: start narrow
QSYS2.IFS_READ	Table function	DSPF, EDTF (read)	Lines of a stream file (LINE_NUMBER, LINE)	SELECT LINE_NUMBER, LINE FROM TABLE(QSYS2.IFS_READ(PATH_NAME => '/home/dev/readme.txt', END_OF_LINE => 'CRLF')) X	IFS_READ_BINARY and IFS_READ_UTF8 variants exist
QSYS2.QCMDEXC	Procedure	Running CL from SQL	Runs a CL command	CALL QSYS2.QCMDEXC('ADDLIBLE LIB(PRODLIB2)')	Also a scalar function returning 1 or -1; command authority rules apply
QSYS2.HTTP_GET	Scalar function	Java or curl calls from CL	Response body as CLOB	VALUES QSYS2.HTTP_GET('https://example.com/api/status', '{"headers":{"Accept":"application/json"}}')	QSYS2 HTTP functions arrived with 7.3 TR11 and 7.4 TR5. The headers JSON object needs a recent Db2 PTF Group (7.4 level 23, 7.5 level 3); older levels use the single header option. The SYSTOOLS HTTPGETCLOB functions use Java
SYSIBM.SYSDUMMY1	Catalog view	The DUAL table in Oracle	One row, one column, for evaluating expressions	SELECT CURRENT TIMESTAMP, CURRENT SERVER FROM SYSIBM.SYSDUMMY1	Or use VALUES CURRENT TIMESTAMP with no table

Service names, columns and parameters follow IBM Documentation for IBM i 7.5 (IBM i Services and the Db2 for i catalog). Many services arrive on earlier releases through the Db2 PTF Group; check the IBM i Technology Updates pages for the level your partition needs. Sample library and object names are placeholders.

Get a daily IBM i health check script

Tell us your release and what you monitor today and we will send a set of IBM i Services queries for a daily check, matched to your PTF level.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Search by the CL command you already know, for example WRKACTJOB or DSPLOG, and the table shows the service that returns the same information as rows. Copy the sample SQL, change the library or user names, and run it. Views are queried directly (SELECT ... FROM QSYS2.USER_INFO); table functions need TABLE(...) around the call and accept named parameters with =>; procedures are run with CALL. Because the results are ordinary SQL, you can join them, filter them, export them from ACS to Excel, or read them from SQL Server through a linked server over the IBM i Access ODBC driver.

Run SQL Scripts in IBM i Access Client Solutions is the easiest place to start
STRSQL works too but does not show long columns well
From SSMS, use OPENQUERY over an ODBC linked server so the SQL runs on IBM i

Worked example: find jobs waiting on a message

A night batch has stopped and users report locks. Run SELECT JOB_NAME, AUTHORIZATION_NAME, JOB_STATUS, FUNCTION FROM TABLE(QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'NONE')) X WHERE JOB_STATUS = 'MSGW'. Each row is a job waiting on an inquiry message. Then SELECT MESSAGE_TIMESTAMP, MESSAGE_ID, MESSAGE_TEXT FROM QSYS2.MESSAGE_QUEUE_INFO WHERE MESSAGE_QUEUE_NAME = 'QSYSOPR' AND MESSAGE_TYPE = 'INQUIRY' shows what they are asking. If a job holds record locks, QSYS2.RECORD_LOCK_INFO filtered on the table shows the holder. Keeping DETAILED_INFO at 'NONE' makes ACTIVE_JOB_INFO fast enough to run every few minutes from a monitoring job.

PTF currency and housekeeping checks

SYSTOOLS.GROUP_PTF_CURRENCY compares the PTF group levels installed on the partition with the levels IBM publishes, so ordering by the difference shows which groups are furthest behind. It needs the partition to reach IBM over the internet; on an isolated partition use QSYS2.GROUP_PTF_INFO and compare manually. QSYS2.PTF_INFO with PTF_IPL_ACTION <> 'NONE' shows what will change at the next IPL. For storage, QSYS2.SYSTABLESTAT ordered by NUMBER_DELETED_ROWS finds files that need RGZPFM, and QSYS2.JOURNAL_INFO ordered by TOTAL_SIZE_JOURNAL_RECEIVERS finds journals whose receivers are not being cleaned up.

Gotchas: releases, PTFs and authority

IBM adds and extends services through Technology Refreshes and the Db2 PTF Group, so a service or column in the 7.5 documentation may be missing on a 7.4 partition that is behind on PTFs. Check the IBM i Technology Updates pages before relying on a parameter. Services respect object authority: a user without *JOBCTL sees fewer jobs, USER_INFO needs authority to the profiles, and some columns return null without *ALLOBJ, *SECADM or *JOBCTL. Some services do a lot of work per call (OBJECT_STATISTICS on *ALLUSR, IFS_OBJECT_STATISTICS on /), so filter early and test outside business hours.

Frequently Asked Questions

How do I run an SQL query on AS400?

Use Run SQL Scripts in IBM i Access Client Solutions, STRSQL on a green screen, or any ODBC or JDBC client with the IBM i Access driver. Type a statement such as SELECT * FROM QSYS2.SYSTEM_STATUS_INFO and run it. With system naming use library/file; with SQL naming use library.file, which ACS uses by default.

What is the dummy table in IBM i SQL?

SYSIBM.SYSDUMMY1 is the one-row table used to evaluate expressions, the equivalent of DUAL in Oracle, for example SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1. Db2 for i also supports VALUES, so VALUES CURRENT DATE returns the same result with no table at all.

Can I query AS400 system information from SQL Server SSMS?

Yes. Create a linked server in SQL Server over the IBM i Access ODBC driver, then use OPENQUERY so the statement runs on IBM i, for example SELECT * FROM OPENQUERY(IBMI, 'SELECT JOB_NAME, JOB_STATUS FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) X'). The connecting user's IBM i authority still decides which rows come back.

Which IBM i Services replace WRKACTJOB and DSPLOG?

QSYS2.ACTIVE_JOB_INFO returns what WRKACTJOB shows, with filters for subsystem, job name and user, and a DETAILED_INFO level that controls how many columns are computed. QSYS2.HISTORY_LOG_INFO returns the QHST history log that DSPLOG shows, with START_TIME and END_TIME parameters to limit the window.

If you want admins and managers to ask questions like these in plain English and get answers from your IBM i, with the SQL shown and nothing leaving your network, we can set that up on your servers.

Talk to an Expert Explore More Free Tools