IBM i: The SQL Way · #10

Parse a Qualified IBM i Job Name with SQL

Use SYSTOOLS.JOB_NAME, JOB_USER, JOB_NUMBER, and JOB_NAME_DETAILS to split a qualified IBM i job name into reusable SQL columns.

IBM i SQL services often return one qualified job-name column in the form job-number/job-user/job-name. New SYSTOOLS helpers split that value into its three components without repeating substring and delimiter logic.

A qualified job name returned by IBM i SQL services commonly looks like:

811603/QUSER_NC/QZDASOINIT

Its components are:

Job number   811603
Job user     QUSER_NC
Job name     QZDASOINIT

The new helpers are:

SYSTOOLS.JOB_NUMBER
SYSTOOLS.JOB_USER
SYSTOOLS.JOB_NAME
SYSTOOLS.JOB_NAME_DETAILS

Return one component

Job name

VALUES SYSTOOLS.JOB_NAME(
    '811603/QUSER_NC/QZDASOINIT'
);

Result:

QZDASOINIT

Job user

VALUES SYSTOOLS.JOB_USER(
    '811603/QUSER_NC/QZDASOINIT'
);

Result:

QUSER_NC

Job number

VALUES SYSTOOLS.JOB_NUMBER(
    '811603/QUSER_NC/QZDASOINIT'
);

Result:

811603

Return all three components

SELECT *
FROM TABLE(
    SYSTOOLS.JOB_NAME_DETAILS(
        '811603/QUSER_NC/QZDASOINIT'
    )
);

Result columns:

JOB_NUMBER
JOB_USER
JOB_NAME

Use JOB_NAME_DETAILS when all three values are needed. It parses the qualified name once and returns one row.

Parse a job column returned by another service

Assume a table contains:

QUALIFIED_JOB_NAME

Use scalar functions:

SELECT
    QUALIFIED_JOB_NAME,
    SYSTOOLS.JOB_NUMBER(
        QUALIFIED_JOB_NAME
    ) AS JOB_NUMBER,
    SYSTOOLS.JOB_USER(
        QUALIFIED_JOB_NAME
    ) AS JOB_USER,
    SYSTOOLS.JOB_NAME(
        QUALIFIED_JOB_NAME
    ) AS JOB_NAME
FROM MYLIB.JOB_ACTIVITY;

Use JOB_NAME_DETAILS with LATERAL

A lateral join is useful when parsing every row:

SELECT
    A.QUALIFIED_JOB_NAME,
    D.JOB_NUMBER,
    D.JOB_USER,
    D.JOB_NAME,
    A.EVENT_TIMESTAMP,
    A.EVENT_TYPE
FROM MYLIB.JOB_ACTIVITY AS A
CROSS JOIN LATERAL
    TABLE(
        SYSTOOLS.JOB_NAME_DETAILS(
            A.QUALIFIED_JOB_NAME
        )
    ) AS D
ORDER BY A.EVENT_TIMESTAMP DESC;

This keeps the parsing logic readable and exposes the three components as ordinary columns.

Parse active-job results

QSYS2.ACTIVE_JOB_INFO returns a qualified JOB_NAME value.

To avoid a naming conflict, alias the original column:

WITH ACTIVE_JOBS AS
(
    SELECT
        JOB_NAME AS QUALIFIED_JOB_NAME,
        JOB_STATUS,
        SUBSYSTEM,
        AUTHORIZATION_NAME,
        TEMPORARY_STORAGE
    FROM TABLE(
        QSYS2.ACTIVE_JOB_INFO(
            DETAILED_INFO => 'NONE'
        )
    )
)
SELECT
    A.QUALIFIED_JOB_NAME,
    D.JOB_NUMBER,
    D.JOB_USER,
    D.JOB_NAME AS SIMPLE_JOB_NAME,
    A.JOB_STATUS,
    A.SUBSYSTEM,
    A.AUTHORIZATION_NAME,
    A.TEMPORARY_STORAGE
FROM ACTIVE_JOBS AS A
CROSS JOIN LATERAL
    TABLE(
        SYSTOOLS.JOB_NAME_DETAILS(
            A.QUALIFIED_JOB_NAME
        )
    ) AS D
ORDER BY A.TEMPORARY_STORAGE DESC;

Group activity by job user

SELECT
    SYSTOOLS.JOB_USER(
        QUALIFIED_JOB_NAME
    ) AS JOB_USER,
    COUNT(*) AS EVENT_COUNT,
    MAX(EVENT_TIMESTAMP) AS LAST_EVENT
FROM MYLIB.JOB_ACTIVITY
GROUP BY
    SYSTOOLS.JOB_USER(
        QUALIFIED_JOB_NAME
    )
ORDER BY EVENT_COUNT DESC;

Find activity for one simple job name

SELECT
    QUALIFIED_JOB_NAME,
    EVENT_TIMESTAMP,
    EVENT_TYPE
FROM MYLIB.JOB_ACTIVITY
WHERE SYSTOOLS.JOB_NAME(
          QUALIFIED_JOB_NAME
      ) = 'ORDERJOB'
ORDER BY EVENT_TIMESTAMP DESC;

For large permanent tables, repeatedly applying a function in the predicate may be less efficient than storing the components separately or indexing generated columns.

Create a reusable view

CREATE OR REPLACE VIEW MYLIB.JOB_ACTIVITY_DETAIL AS
SELECT
    A.QUALIFIED_JOB_NAME,
    D.JOB_NUMBER,
    D.JOB_USER,
    D.JOB_NAME,
    A.EVENT_TIMESTAMP,
    A.EVENT_TYPE,
    A.EVENT_DETAIL
FROM MYLIB.JOB_ACTIVITY AS A
CROSS JOIN LATERAL
    TABLE(
        SYSTOOLS.JOB_NAME_DETAILS(
            A.QUALIFIED_JOB_NAME
        )
    ) AS D;

Applications can now query individual job components without knowing the parsing rules.

Null handling

JOB_NAME_DETAILS returns a row whose component columns are null when the input is null.

SELECT *
FROM TABLE(
    SYSTOOLS.JOB_NAME_DETAILS(
        CAST(NULL AS VARCHAR(28))
    )
);

The scalar functions also support null input according to their SQL definitions.

Preserve null when the source job is unknown. Do not replace it with the current job unless that is explicitly required by the application.

Expected format

The expected qualified format is:

job-number/job-user/job-name

Example:

811603/QUSER_NC/QZDASOINIT

Do not pass the command-prompt ordering used in informal conversation or a custom format.

When data comes from an external source, validate that it follows the IBM i qualified-job-name form before relying on the parsed components.

Return types

The functions return:

JOB_NUMBER   CHAR(6)
JOB_USER     VARCHAR(10)
JOB_NAME     VARCHAR(10)

The complete qualified value can be up to 28 characters.

Authority

IBM documents no special authority requirement for these helper functions.

The caller still needs authority to query the source view or table containing the qualified job name.

For example, parsing a string requires no special authority, but reading active-job details may require authority based on the service and requested detail level.

Why not use SUBSTR and LOCATE?

Before these helpers, SQL could parse a job name using delimiters:

SUBSTR
LOCATE

or:

SYSTOOLS.SPLIT

That works, but it repeats IBM i-specific parsing logic throughout the code.

The new functions make the intent explicit:

SYSTOOLS.JOB_USER(QUALIFIED_JOB_NAME)

is easier to understand than several nested substring calculations.

SYSTOOLS implementation note

IBM supplies these functions in SYSTOOLS as examples of common application logic implemented in SQL.

Their SQL source can be extracted and used as a model for:

• a customized version
• a user-owned schema
• additional input validation
• project-specific naming standards

Avoid modifying the IBM-supplied SYSTOOLS object directly.

Release and PTF requirement

IBM lists the job-name helpers as new IBM i services delivered with:

IBM i 7.6 — Db2 Group PTF SF99960 Level 3
IBM i 7.5 — Db2 Group PTF SF99950 Level 12

A practical workflow

1. Retrieve the qualified job name from an IBM i service.
2. Preserve the original value for commands and diagnostics.
3. Use JOB_NAME_DETAILS when all components are needed.
4. Use one scalar helper when only one component is needed.
5. Store components separately when they are queried frequently.
6. Keep the qualified value for unambiguous job identification.

Final takeaway

The qualified job name remains the best single identifier for an IBM i job.

JOB_NAME, JOB_USER, JOB_NUMBER, and JOB_NAME_DETAILS make that identifier easier to use in reports, joins, filters, and support utilities without duplicating delimiter logic.