IBM i: The SQL Way · #19

Run IBM i CL Commands from SQL and Control Job-Log Output

Use the QSYS2.QCMDEXC scalar function to execute a CL command from SQL, check its return value, and control whether the command text is written to the job log.

QSYS2.QCMDEXC allows an SQL statement to execute an IBM i CL command. The current scalar-function enhancement adds control over whether the command text is written to the job log, which improves diagnostics but also creates an important security decision.

IBM provides QCMDEXC in two SQL forms:

QSYS2.QCMDEXC procedure
QSYS2.QCMDEXC scalar function

The procedure is useful for a direct command call.

The scalar function is useful when the command result must participate in an SQL expression, query, conditional operation, or generated report.

Basic scalar-function example

VALUES QSYS2.QCMDEXC(
    'ADDLIBLE LIB(MYLIB)'
);

The scalar function returns:

1    Command completed successfully
-1   Command execution failed

The command can be up to 32,000 characters.

New PRINT parameter

The current enhancement adds:

PRINT

to the scalar function.

Supported values are:

NONE
ERROR
VERBOSE

NONE

VALUES QSYS2.QCMDEXC(
    COMMAND => 'ADDLIBLE LIB(MYLIB)',
    PRINT   => 'NONE'
);

The command text is not written to the job log.

This is the default.

ERROR

VALUES QSYS2.QCMDEXC(
    COMMAND => 'ADDLIBLE LIB(MYLIB)',
    PRINT   => 'ERROR'
);

The command text is written to the job log only when command execution fails.

This can provide useful diagnostic context without logging every successful command.

VERBOSE

VALUES QSYS2.QCMDEXC(
    COMMAND => 'ADDLIBLE LIB(MYLIB)',
    PRINT   => 'VERBOSE'
);

The command text is always written to the job log.

Use this only when the command contains no confidential information.

Check the return value

SELECT
    CASE QSYS2.QCMDEXC(
        COMMAND => 'ADDLIBLE LIB(MYLIB)',
        PRINT   => 'ERROR'
    )
        WHEN 1 THEN 'COMMAND COMPLETED'
        ELSE 'COMMAND FAILED'
    END AS COMMAND_STATUS
FROM SYSIBM.SYSDUMMY1;

The integer return value is useful when an SQL-driven process must record success or failure.

Run a command for selected rows

IBM’s documentation demonstrates using QCMDEXC with active-job information.

A simplified administrative example is:

SELECT
    JOB_NAME,
    CASE
        WHEN QSYS2.QCMDEXC(
                 COMMAND =>
                     'QSYS/HLDJOB JOB('
                     CONCAT JOB_NAME
                     CONCAT ')',
                 PRINT => 'ERROR'
             ) = 1
          THEN 'JOB HELD'
        ELSE 'JOB NOT HELD'
    END AS COMMAND_RESULT
FROM TABLE(
    QSYS2.ACTIVE_JOB_INFO(
        DETAILED_INFO => 'ALL'
    )
)
WHERE SQL_STATEMENT_START_TIMESTAMP
      < CURRENT TIMESTAMP - 2 HOURS;

This is powerful—and potentially disruptive.

Do not execute a command against a result set until the selection query has been reviewed independently.

First run:

SELECT
    JOB_NAME,
    SQL_STATEMENT_START_TIMESTAMP,
    SQL_STATEMENT_TEXT
FROM TABLE(
    QSYS2.ACTIVE_JOB_INFO(
        DETAILED_INFO => 'ALL'
    )
)
WHERE SQL_STATEMENT_START_TIMESTAMP
      < CURRENT TIMESTAMP - 2 HOURS;

Only after validating the target rows should the command-execution expression be considered.

Build a command from table data

SELECT
    OBJECT_LIBRARY,
    OBJECT_NAME,
    QSYS2.QCMDEXC(
        COMMAND =>
            'QSYS/CHKOBJ OBJ('
            CONCAT TRIM(OBJECT_LIBRARY)
            CONCAT '/'
            CONCAT TRIM(OBJECT_NAME)
            CONCAT ') OBJTYPE(*FILE)',
        PRINT => 'ERROR'
    ) AS COMMAND_RESULT
FROM MYTOOLS.OBJECT_CHECK_QUEUE
WHERE STATUS = 'READY';

Dynamic command construction must validate every value.

Even when the values come from a database table, the table may contain:

• unexpected characters
• unapproved object names
• data entered by an application user
• stale environment values
• values copied from another partition

Validate syntax first

Combine CHECK_COMMAND_SYNTAX with QCMDEXC:

WITH COMMAND_TO_RUN (COMMAND_TEXT) AS
(
    VALUES
      'ADDLIBLE LIB(MYLIB)'
)
SELECT
    COMMAND_TEXT,
    CASE
        WHEN SYSTOOLS.CHECK_COMMAND_SYNTAX(
                 COMMAND_TEXT
             ) IS NOT TRUE
          THEN -2
        ELSE QSYS2.QCMDEXC(
                 COMMAND => COMMAND_TEXT,
                 PRINT   => 'ERROR'
             )
    END AS COMMAND_RESULT
FROM COMMAND_TO_RUN;

Possible application meanings:

 1   Command succeeded
-1   Command execution failed
-2   Command syntax was rejected before execution

Syntax validation does not prove that a command is authorized, safe, or appropriate.

Use an allowlist

A secure command service should not accept arbitrary user-supplied CL.

A simple table-driven allowlist could contain:

COMMAND_NAME
ALLOWED_ENVIRONMENT
REQUIRES_APPROVAL
LOGGING_POLICY

Before calling QCMDEXC, validate:

• the command name
• allowed parameters
• target library
• target object type
• environment
• execution profile
• approval state
• whether sensitive data is present

Procedure form

For a direct call where no scalar return value is needed:

CALL QSYS2.QCMDEXC(
    'ADDLIBLE LIB(MYLIB)'
);

Use the procedure when:

• the command is the statement’s primary action
• no row-by-row expression is required
• success or failure can be handled through SQL diagnostics

Use the scalar function when:

• a return value is needed
• command execution is conditional
• commands are driven by selected rows
• the outcome must be included in a query result

Authority

The user invoking QCMDEXC must have the authority required by the CL command being executed.

QCMDEXC does not bypass IBM i object authority.

For example, this may be syntactically valid:

VALUES QSYS2.QCMDEXC(
    'DLTLIB LIB(MYLIB)'
);

but it should fail unless the executing profile has the required authority.

That failure is a protection—not an inconvenience to work around with excessive authority.

Adopted authority and execution context

When QCMDEXC is called from:

• a stored procedure
• an SQL function
• an RPG program
• a service program
• a submitted job

review the complete authority model.

Questions include:

• Which profile owns the program?
• Is adopted authority active?
• Is the SQL routine defined with definer or invoker behavior?
• Which library list is active?
• Which current library is used?
• Which ASP group is attached?
• Which job log receives the command text?
• Which audit entries are generated?

The same command can behave differently under another job context.

Avoid relying on the library list

For automation, qualify command and object names where practical:

VALUES QSYS2.QCMDEXC(
    COMMAND =>
      'QSYS/CHKOBJ OBJ(MYLIB/MYFILE) OBJTYPE(*FILE)',
    PRINT => 'ERROR'
);

Qualification improves repeatability and reduces the chance that an unexpected library-list entry changes the target.

Store command results

CREATE TABLE MYTOOLS.COMMAND_AUDIT
(
    AUDIT_ID          BIGINT
                      GENERATED ALWAYS AS IDENTITY,
    REQUEST_TIMESTAMP TIMESTAMP NOT NULL,
    REQUESTED_BY      VARCHAR(128) NOT NULL,
    COMMAND_TEXT      VARCHAR(32000) NOT NULL,
    COMMAND_RESULT    INTEGER,
    COMPLETED_TIMESTAMP TIMESTAMP
);

A production audit design should avoid storing credentials or sensitive parameter values.

Consider storing:

• a redacted command
• a command template identifier
• separately protected parameter values
• a hash of the approved request
• the qualified execution job
• associated change-ticket number

Do not run destructive commands from a SELECT casually

A scalar function can be invoked once for every qualifying row.

A query that unexpectedly returns 500 rows may execute 500 commands.

Before adding QCMDEXC:

1. Run the selection by itself.
2. Confirm the exact row count.
3. Review duplicate rows.
4. Confirm idempotency.
5. Add a narrow environment filter.
6. Consider a transaction-independent work queue.
7. Test with a non-destructive command.

Job-log guidance

Use:

PRINT => 'NONE'

when:

• the command text is sensitive
• separate secure audit logging exists
• job-log volume must be minimized

Use:

PRINT => 'ERROR'

when:

• failure diagnosis needs the exact non-sensitive command
• successful commands do not need to be logged

Use:

PRINT => 'VERBOSE'

when:

• a controlled test is being traced
• every command must be visible
• the command contains no secrets

Release requirement

The scalar function itself is available on earlier supported releases.

The PRINT parameter is enhanced at:

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

On an earlier PTF level, a call using PRINT may fail because the parameter is not recognized.

A safe workflow

1. Build the target SELECT without QCMDEXC.
2. Validate the row count and exact objects or jobs.
3. Validate the CL command syntax.
4. Apply a command and parameter allowlist.
5. Confirm the executing profile and authority model.
6. Choose a PRINT policy.
7. Execute the smallest possible batch.
8. Capture the return result and SQL diagnostics.
9. Review the job log and audit journal.
10. Confirm the intended system change.

Final takeaway

QSYS2.QCMDEXC creates a powerful bridge between SQL selection logic and IBM i CL commands.

The new PRINT parameter improves control over diagnostics, but it also makes command-content protection part of the design.

Use the function for controlled automation—not as an unrestricted command shell hidden inside SQL.