--- title: Blog • View IBM i heath using SQL • PacSol UK description: Need a simple way to retrieve details on how your IBM i Power Server is performing without the need for complex APIs? IBM have provided a way using SQL. image: https://www.pacsol.co.uk/hubfs/Partner%20Logos/ibmi.jpg --- [Skip to content](https://www.pacsol.co.uk/blog/ibm_i_health#main-content) [![PacSol UK Document Management Software](https://www.pacsol.co.uk/hs-fs/hubfs/PacSol%20Logos/PacSol_Logo_tagline_gadget_registered_96dpi.png?width=1280&height=255&name=PacSol_Logo_tagline_gadget_registered_96dpi.png)](https://www.pacsol.co.uk/) - [Home](https://www.pacsol.co.uk/) - Solutions Show submenu for Solutions - [Document Capture](https://www.pacsol.co.uk/document-capture) - [Document Management](https://www.pacsol.co.uk/document-management) - [Business Process Automation](https://www.pacsol.co.uk/business-process-automation) - [Email Processing](https://www.pacsol.co.uk/email-processing) - [Forms Processing](https://www.pacsol.co.uk/forms-processing) - [Sectors](https://www.pacsol.co.uk/sectors) - [Services](https://www.pacsol.co.uk/services) - [Support](https://www.pacsol.co.uk/pacsol-support) - [Contact](https://www.pacsol.co.uk/contact) Open main navigation Close main navigation - [Home](https://www.pacsol.co.uk/) - Solutions Show submenu for Solutions - [Document Capture](https://www.pacsol.co.uk/document-capture) - [Document Management](https://www.pacsol.co.uk/document-management) - [Business Process Automation](https://www.pacsol.co.uk/business-process-automation) - [Email Processing](https://www.pacsol.co.uk/email-processing) - [Forms Processing](https://www.pacsol.co.uk/forms-processing) - [Sectors](https://www.pacsol.co.uk/sectors) - [Services](https://www.pacsol.co.uk/services) - [Support](https://www.pacsol.co.uk/pacsol-support) - [Contact](https://www.pacsol.co.uk/contact) - [Return to Blog Listings](https://www.pacsol.co.uk/blog) [Return to Blog Listings](https://www.pacsol.co.uk/blog) Jan 24, 2024 9:30:00 AM # View IBM i health - using SQL! [Mark Wheadon](https://www.pacsol.co.uk/blog/author/mark-wheadon) [facebook-f icon](http://www.facebook.com/share.php?u=https://www.pacsol.co.uk/blog/ibm_i_health) [linkedin-in icon](http://www.linkedin.com/shareArticle?mini=true&url=https://www.pacsol.co.uk/blog/ibm_i_health) [envelope icon](mailto:?body=https://www.pacsol.co.uk/blog/ibm_i_health) ![IBM i for Business Logo](https://www.pacsol.co.uk/hs-fs/hubfs/Partner%20Logos/ibmi.jpg?width=140&height=141&name=ibmi.jpg)[IBM](https://www.ibm.com/uk-en) has made it possible to retrieve many details about how your [IBM i](https://www.ibm.com/products/ibm-i) is running using SQL. When I first heard of this ability I was certainly surprised to say the least. Previously the only way to get this information was through APIs. Though this was useful it involved quite a lot of programming. Using SQL may not seem intuitive, after all it is not really data from a relational database, but it works very well through simple and easy to understand SQL statements. A further advantage is that if you wanted to go further you could execute the SQL statements from a generic dashboard function. The dashboard will display the information in graphic form and provide alerts before resources run out. Let’s get to some examples. ## Active Jobs // Top 10 jobs; SELECT JOB\_NAME, authorization\_name, function\_type, function, temporary\_storage, elapsed\_cpu\_percentage FROM TABLE (QSYS2.ACTIVE\_JOB\_INFO(DETAILED\_INFO => 'WORK')) X ORDER BY ELAPSED\_CPU\_PERCENTAGE desc fetch first 10 rows only; The above command works in a similar way to WRKACTJOB (With with Active Jobs) and shows the top 10 jobs by CPU usage. If a program is running away with the CPU then it will show here. If you want to reset the statistics, to reset the elapsed time for example, then use reset\_statistics=>'YES' : SELECT JOB\_NAME, authorization\_name, function\_type, function, temporary\_storage, elapsed\_cpu\_percentage FROM TABLE (QSYS2.ACTIVE\_JOB\_INFO(reset\_statistics=>'YES', DETAILED\_INFO => 'WORK')) X ORDER BY ELAPSED\_CPU\_PERCENTAGE desc fetch first 10 rows only; There is more information at on the active job functions [here](https://www.ibm.com/docs/en/i/7.3?topic=services-active-job-info-table-function). ## System Status If you just want a simple overview of the system similar to WRKSYSSTS (Work with System Status) then try this: SELECT system\_asp\_used, total\_jobs\_in\_system, active\_jobs\_in\_system, elapsed\_cpu\_used FROM QSYS2.SYSTEM\_STATUS\_INFO\_BASIC; On our development system, this query returned the following: | SYSTEM\_ASP\_USED | TOTAL\_JOBS\_IN\_SYSTEM | ACTIVE\_JOBS\_IN\_SYSTEM | ELAPSED\_CPU\_USED | | --- | --- | --- | --- | | 38.98 | 26256 | 300 | 2.60 | The first and last values are percentages. You can see that we have a lot of jobs running, these are all our development and testing systems. However, the IBM i is a high performance machine and so the CPU usage is very low. There is more information about these commands [here](https://www.ibm.com/docs/en/i/7.3?topic=services-system-status-info-basic-view). This article has just shown a couple of simple examples of what is possible with [IBM i](https://www.ibm.com/products/ibm-i) Work Management SQL queries. There are many other SQL statements and a great many fields that can be retrieved. Why not give it a try yourself? PacSol have been working with and supporting document management services on IBM i (as/400) power systems since 1995 and our collective experience with IBM platforms extends for far longer. --- ![Mark Wheadon. PacSol UK Managing Director](https://www.pacsol.co.uk/hs-fs/hubfs/Staff%20Photos/mw_colour_500x.png?width=124&height=124&name=mw_colour_500x.png) Mark Wheadon. Managing Director at PacSol. January 2024     --- [![PacSol UK Document Management Process Banner](https://www.pacsol.co.uk/hs-fs/hubfs/website%20banners/PacSol_Logo_SocialMedia_DMAdvert_CompanyBanner_96dpi-1024x158.png?width=1024&height=158&name=PacSol_Logo_SocialMedia_DMAdvert_CompanyBanner_96dpi-1024x158.png)](https://www.pacsol.co.uk) #pacsoluk #ibmi #systemperformance #documentmanagement #reporting [Newsletter](https://www.pacsol.co.uk/blog/tag/newsletter), [IBMi](https://www.pacsol.co.uk/blog/tag/ibmi), [IBM](https://www.pacsol.co.uk/blog/tag/ibm), [environment](https://www.pacsol.co.uk/blog/tag/environment), [Platform Maintenance](https://www.pacsol.co.uk/blog/tag/platform-maintenance) ## Related posts [![i-UG User Group Logo](https://www.pacsol.co.uk/hs-fs/hubfs/Partner%20Logos/i-UG-ibm-style-Logo-final-v4.png?height=200&name=i-UG-ibm-style-Logo-final-v4.png)](https://www.pacsol.co.uk/blog/do-you-iug) [Newsletter](https://www.pacsol.co.uk/blog/tag/newsletter), [IBMi](https://www.pacsol.co.uk/blog/tag/ibmi), [IBM](https://www.pacsol.co.uk/blog/tag/ibm), [document management](https://www.pacsol.co.uk/blog/tag/document-management) ## [Use IBM i? Do you i-UG?](https://www.pacsol.co.uk/blog/do-you-iug) [Toby Gilbertson](https://www.pacsol.co.uk/blog/author/toby-gilbertson) Feb 28, 2024 8:30:00 AM For over 28 years, PacSol have provided organisations with powerful and reliable document... [Read more](https://www.pacsol.co.uk/blog/do-you-iug) [![PacSol ImageView Document Management Logo](https://www.pacsol.co.uk/hs-fs/hubfs/Product%20Logos/PacSol_ImageView_White_alt1.png?height=200&name=PacSol_ImageView_White_alt1.png)](https://www.pacsol.co.uk/blog/the-future-of-imageview-email-integration) [Newsletter](https://www.pacsol.co.uk/blog/tag/newsletter), [IBMi](https://www.pacsol.co.uk/blog/tag/ibmi), [document management](https://www.pacsol.co.uk/blog/tag/document-management), [ImageView](https://www.pacsol.co.uk/blog/tag/imageview) ## [The Future of ImageView - Email Integration](https://www.pacsol.co.uk/blog/the-future-of-imageview-email-integration) [Martin Pentlow](https://www.pacsol.co.uk/blog/author/martin-pentlow) Aug 21, 2020 10:09:49 AM With our new web-based viewer, WebView2, email integration for ImageView document management has... [Read more](https://www.pacsol.co.uk/blog/the-future-of-imageview-email-integration) [![ImageView Product Logo](https://www.pacsol.co.uk/hs-fs/hubfs/Product%20Logos/PacSol_ImageView_White_alt1.png?height=200&name=PacSol_ImageView_White_alt1.png)](https://www.pacsol.co.uk/blog/new-imageview-viewer-in-the-pipeline) [Newsletter](https://www.pacsol.co.uk/blog/tag/newsletter), [document management](https://www.pacsol.co.uk/blog/tag/document-management), [ImageView](https://www.pacsol.co.uk/blog/tag/imageview) ## [New ImageView Viewer in the pipeline](https://www.pacsol.co.uk/blog/new-imageview-viewer-in-the-pipeline) [Mark Wheadon](https://www.pacsol.co.uk/blog/author/mark-wheadon) May 4, 2020 5:25:43 PM Fully embracing modern web technologies, the new ImageView Viewer offers a fresh, responsive,... [Read more](https://www.pacsol.co.uk/blog/new-imageview-viewer-in-the-pipeline) --- [![PacSol UK Document Management Software](https://www.pacsol.co.uk/hs-fs/hubfs/PacSol%20Logos/PacSol_Logo_tagline_gadget_registered_96dpi.png?width=1280&height=255&name=PacSol_Logo_tagline_gadget_registered_96dpi.png "PacSol UK Document Management Software")](https://www.pacsol.co.uk) 134 High Street, Tonbridge, Kent TN9 1BB Copyright © 2026, Pacific Solutions International Limited ```json { "@context" : "https://schema.org", "@type" : "BlogPosting", "author" : { "@type" : "Person", "name" : "Mark Wheadon", "url" : "https://www.pacsol.co.uk/blog/author/mark-wheadon" }, "dateModified" : "2024-01-26T14:42:19.474Z", "datePublished" : "2024-01-24T09:30:00.000Z", "headline" : "Blog • View IBM i heath using SQL • PacSol UK", "image" : [ "https://www.pacsol.co.uk/hubfs/Partner%20Logos/ibmi.jpg" ], "mainEntityOfPage" : { "@id" : "https://www.pacsol.co.uk/blog/ibm_i_health", "@type" : "WebPage" }, "publisher" : { "@type" : "Organization", "logo" : { "@type" : "ImageObject", "url" : "https://www.pacsol.co.uk/hubfs/PacSol%20Logos/PacSol_Logo_tagline_gadget_registered_96dpi.png" }, "name" : "Pacific Solutions International Limited" } } ```