# Infor XA IBM i SQL Services Health Query Generator

> Source: https://netray.co/tools/infor-xa-ibm-i-sql-services-health-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in your XA library, thresholds and subsystem and get five ready-to-run Db2 for i queries for file size, reorganise candidates, inactive users, active XA jobs and history file growth.

## Category

developer

## Erp

Infor XA

## Audience

- ERP administrator
- IBM i administrator
- DBA

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### XA data library

**Key:** library

**Type:** text

**Default:** AMFLIB

**Help:** Upper case, as stored in the catalog

#### Top N files

**Key:** topn

**Type:** number

**Default:** 25

#### Deleted-row ratio threshold (%)

**Key:** threshold

**Type:** number

**Default:** 20

#### User profile prefix filter

**Key:** prefix

**Type:** text

**Help:** Leave blank for all profiles

#### XA subsystem name

**Key:** subsystem

**Type:** text

**Help:** Leave blank to list jobs in all subsystems

#### Days since last sign-on

**Key:** days

**Type:** number

**Default:** 90

**Blocks:**

#### 1. Largest files in {{library}}

**Body:** -- Largest physical files in the XA library by data space size
SELECT TABLE_NAME,
       NUMBER_ROWS,
       NUMBER_DELETED_ROWS,
       DEC(DATA_SIZE / 1048576.0, 15, 1) AS DATA_MB,
       LAST_CHANGE_TIMESTAMP
  FROM QSYS2.SYSTABLESTAT
 WHERE TABLE_SCHEMA = '{{library}}'
 ORDER BY DATA_SIZE DESC
 FETCH FIRST {{topn}} ROWS ONLY;

#### 2. RGZPFM candidates (deleted rows above {{threshold}}%)

**Body:** -- Files where deleted records exceed the threshold, with the CL command to run
-- Run RGZPFM only when the file is not in use (XA users and batch stopped, backup taken)
SELECT TABLE_NAME,
       NUMBER_ROWS,
       NUMBER_DELETED_ROWS,
       DEC(NUMBER_DELETED_ROWS * 100.0
           / NULLIF(NUMBER_ROWS + NUMBER_DELETED_ROWS, 0), 7, 2) AS DELETED_PCT,
       DEC(DATA_SIZE / 1048576.0, 15, 1) AS DATA_MB,
       'RGZPFM FILE(' CONCAT TRIM(TABLE_SCHEMA) CONCAT '/'
         CONCAT TRIM(SYSTEM_TABLE_NAME) CONCAT ')' AS RGZ_COMMAND
  FROM QSYS2.SYSTABLESTAT
 WHERE TABLE_SCHEMA = '{{library}}'
   AND NUMBER_DELETED_ROWS * 100.0
       / NULLIF(NUMBER_ROWS + NUMBER_DELETED_ROWS, 0) > {{threshold}}
 ORDER BY NUMBER_DELETED_ROWS DESC;

-- Example CL for one file:
-- RGZPFM FILE({{library}}/IMHIST)

#### 3. IBM i user list report: profiles not signed on in {{days}} days

**Body:** -- IBM i profiles (prefix '{{prefix}}') with no sign-on in the last {{days}} days
SELECT AUTHORIZATION_NAME,
       STATUS,
       PREVIOUS_SIGNON,
       TEXT_DESCRIPTION,
       GROUP_PROFILE_NAME
  FROM QSYS2.USER_INFO
 WHERE AUTHORIZATION_NAME LIKE '{{prefix}}%'
   AND (PREVIOUS_SIGNON IS NULL
        OR PREVIOUS_SIGNON < CURRENT TIMESTAMP - {{days}} DAYS)
 ORDER BY PREVIOUS_SIGNON;

#### 4. Active jobs in the XA subsystem

**Body:** -- Active jobs, highest CPU first (CPU_TIME is in milliseconds)
-- An empty SUBSYSTEM_LIST_FILTER returns jobs in all subsystems
SELECT JOB_NAME,
       SUBSYSTEM,
       AUTHORIZATION_NAME,
       JOB_STATUS,
       CPU_TIME,
       ELAPSED_CPU_PERCENTAGE
  FROM TABLE(QSYS2.ACTIVE_JOB_INFO(
         SUBSYSTEM_LIST_FILTER => '{{subsystem}}')) X
 ORDER BY CPU_TIME DESC;

#### 5. XA history file sizes

**Body:** -- Row counts and size of the main XA history files
SELECT TABLE_NAME,
       NUMBER_ROWS,
       NUMBER_DELETED_ROWS,
       DEC(DATA_SIZE / 1048576.0, 15, 1) AS DATA_MB,
       LAST_CHANGE_TIMESTAMP
  FROM QSYS2.SYSTABLESTAT
 WHERE TABLE_SCHEMA = '{{library}}'
   AND TABLE_NAME IN ('IMHIST', 'MOHMST', 'MOHDTA', 'MOHRTG', 'MOHTRN',
                      'POHSTM', 'POHISTI', 'MBFBREP', 'MBDRREP')
 ORDER BY DATA_SIZE DESC;

**Notes:** Needs IBM i 7.2 or later with a current Db2 for i group PTF (SF99xxx) so that QSYS2.SYSTABLESTAT, QSYS2.USER_INFO and QSYS2.ACTIVE_JOB_INFO are present; check column names in IBM Docs for your release. QSYS2.USER_INFO can be slow on systems with many profiles. ELAPSED_CPU_PERCENTAGE is measured from a baseline, so the first run in a connection is not meaningful; run the query twice. Query 3 reports IBM i profiles; XA application users are also defined in AMFLIB/MZF0REP and need their own review.

## Content

**Intro:** This generator writes five Db2 for i queries that use IBM i SQL services to check the health of an Infor XA system: largest files, reorganise candidates, inactive users, active XA jobs and history file sizes. Fill in your library and thresholds, then paste the SQL into Run SQL Scripts in IBM i Access Client Solutions.

**Sections:**

#### How to use it

**Body:** Set the data library to the XA environment you want to check, normally AMFLIB, in upper case. Choose how many files to list, the deleted-row percentage above which a file is worth reorganising, and the number of days without sign-on that makes a profile inactive. Enter the subsystem your XA interactive and batch jobs run in, or leave it blank to see every active job. Run the queries from Run SQL Scripts with a profile that has read access to the catalog; query 3 needs authority to see other user profiles, which in practice means a security administrator profile. Save the output with the date so you can compare month to month.

#### What the queries read

**Body:** QSYS2.SYSTABLESTAT returns one row per table or physical file with NUMBER_ROWS, NUMBER_DELETED_ROWS, DATA_SIZE in bytes and LAST_CHANGE_TIMESTAMP. Deleted records still take space until the file is reorganised with RGZPFM or the space is reused because the file has REUSEDLT(*YES). QSYS2.USER_INFO returns one row per user profile with STATUS, PREVIOUS_SIGNON and GROUP_PROFILE_NAME. QSYS2.ACTIVE_JOB_INFO is a table function; SUBSYSTEM_LIST_FILTER takes up to 25 subsystem names separated by commas, and CPU_TIME is reported in milliseconds.

**Bullets:**

- Deleted percent = deleted rows x 100 / (rows + deleted rows)
- DATA_MB = DATA_SIZE / 1,048,576
- Profiles that never signed on have a null PREVIOUS_SIGNON and are included

#### Reading the results

**Body:** In most XA systems the top of query 1 is dominated by history: IMHIST, MOHTRN and the CO history files. A history file with a high deleted percentage usually means a purge has run but no reorganise followed, so the disk space has not come back. Schedule RGZPFM for those files in a maintenance window with XA users and batch stopped, and take a backup first. In query 3, check each inactive profile against the XA user file before disabling it: a service profile used by an integration can look inactive if it only connects through ODBC with a different profile. Query 4 shows which jobs are using CPU right now.

#### Version notes and gotchas

**Body:** SQL services are delivered through the Db2 for i group PTF, so a system that is behind on PTFs may be missing a column or the whole view. If a column is rejected, check IBM Docs for your release and drop it. File names in query 5 come from the XA 9.2.0 definition list; if your site has purged or renamed history, adjust the list. This tool reports IBM i profiles only. XA has its own user file (MZF0REP) and security file (MZGUCPP), and a full user review covers both. For a single-file extract of business data use the separate XA SQL extract generator.

**Cta:** If you want these checks run on a schedule and explained in plain English by an assistant on your own IBM i network, we can set that up.

**Faq:**

#### How do I get a user list report for Infor XA?

**Answer:** For IBM i profiles, query QSYS2.USER_INFO as in block 3: it returns each profile with status, previous sign-on, description and group profile. XA application users are also held in AMFLIB/MZF0REP, so compare the two lists. Profiles that exist in only one place are the first ones to review.

#### How do I find which XA files need RGZPFM?

**Answer:** Query QSYS2.SYSTABLESTAT for the XA library and compare NUMBER_DELETED_ROWS with NUMBER_ROWS. Files where deleted rows are more than about 20 percent of the total are worth reorganising. Block 2 builds the RGZPFM command for each one. Run it only with the file not in use, after a backup.

#### Which IBM i release do these queries need?

**Answer:** IBM i 7.2 or later with a current Db2 for i group PTF. SYSTABLESTAT has existed for a long time, while USER_INFO and ACTIVE_JOB_INFO have gained columns and parameters over several PTF levels. If a query fails with an unknown column, apply the latest group PTF or remove that column.

#### Is it safe to run these queries during the working day?

**Answer:** Yes, they only read catalog and system information, but QSYS2.USER_INFO can take a while on systems with thousands of profiles and ACTIVE_JOB_INFO adds a short burst of work. Run them from a single session, not from a dashboard that refreshes every few seconds.

## Search terms

infor xa user list report, infor xa tables, infor xa support, how to use infor xa, ibm i systablestat query, qsys2 user_info inactive users, rgzpfm deleted records sql, active_job_info subsystem

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json