IBM i: The SQL Way · #4

Validate an IBM i CL Command Without Running It

Use SYSTOOLS.CHECK_COMMAND_SYNTAX to verify the syntax of an IBM i CL command from SQL before storing, scheduling, or conditionally executing it.

A command stored in a table, submitted by an application, or scheduled for later should be checked before execution. SYSTOOLS.CHECK_COMMAND_SYNTAX provides a simple SQL Boolean result without running the CL command.

The new scalar function is:

SYSTOOLS.CHECK_COMMAND_SYNTAX

It accepts a CL command string and returns:

true   The command syntax is valid
false  The command syntax is not valid

The function validates a command of up to 32,000 characters.

Basic example

VALUES SYSTOOLS.CHECK_COMMAND_SYNTAX(
    'ADDLIBLE LIB(MYLIB)'
);

Expected result:

true

An invalid command returns false:

VALUES SYSTOOLS.CHECK_COMMAND_SYNTAX(
    'ADDLIBLE LIB('
);

Expected result:

false

The command is checked, but it is not executed.

Why this is useful

Applications sometimes store commands for later processing.

Examples include:

• job schedulers
• deployment utilities
• support menus
• command queues
• configurable integrations
• operational automation
• database-driven workflows

Without validation, a malformed command may not fail until the scheduled job or downstream process attempts to run it.

SQL validation allows the application to reject the command earlier.

Validate a command stored in a table

Assume a table contains:

COMMAND_ID
COMMAND_TEXT

Query the validation result:

SELECT
    COMMAND_ID,
    COMMAND_TEXT,
    SYSTOOLS.CHECK_COMMAND_SYNTAX(COMMAND_TEXT)
        AS SYNTAX_IS_VALID
FROM MYLIB.COMMAND_QUEUE;

Show only invalid commands:

SELECT
    COMMAND_ID,
    COMMAND_TEXT
FROM MYLIB.COMMAND_QUEUE
WHERE SYSTOOLS.CHECK_COMMAND_SYNTAX(COMMAND_TEXT)
      IS NOT TRUE;

Using IS NOT TRUE also protects the filter from an unexpected null result.

Validate before inserting

INSERT INTO MYLIB.COMMAND_QUEUE
(
    COMMAND_ID,
    COMMAND_TEXT,
    STATUS
)
SELECT
    1001,
    'SBMJOB CMD(CALL PGM(MYLIB/MYPGM))',
    'READY'
FROM SYSIBM.SYSDUMMY1
WHERE SYSTOOLS.CHECK_COMMAND_SYNTAX(
          'SBMJOB CMD(CALL PGM(MYLIB/MYPGM))'
      ) IS TRUE;

If the syntax is invalid, no row is inserted.

In an application, it is usually better to return a clear validation message rather than silently inserting zero rows.

Return a readable validation message

WITH COMMAND_TO_CHECK (COMMAND_TEXT) AS
(
    VALUES
      'SBMJOB CMD(CALL PGM(MYLIB/MYPGM))'
)
SELECT
    COMMAND_TEXT,
    CASE
        WHEN SYSTOOLS.CHECK_COMMAND_SYNTAX(COMMAND_TEXT)
             IS TRUE
          THEN 'Command syntax is valid'
        ELSE 'Command syntax is not valid'
    END AS VALIDATION_RESULT
FROM COMMAND_TO_CHECK;

Validate and conditionally run a command

IBM provides an example that combines validation with QSYS2.QCMDEXC.

A clearer version is:

WITH COMMAND_TO_RUN (COMMAND_TEXT) AS
(
    VALUES 'ADDLIBLE LIB(MYLIB)'
)
SELECT
    CASE
        WHEN SYSTOOLS.CHECK_COMMAND_SYNTAX(COMMAND_TEXT)
             IS TRUE
          THEN QSYS2.QCMDEXC(COMMAND_TEXT)
        ELSE -1
    END AS COMMAND_RESULT,
    COMMAND_TEXT
FROM COMMAND_TO_RUN;

This example executes the command only when the syntax check succeeds.

Validate many commands at once

SELECT
    COMMAND_ID,
    COMMAND_TEXT,
    CASE
        WHEN SYSTOOLS.CHECK_COMMAND_SYNTAX(COMMAND_TEXT)
             IS TRUE
          THEN 'VALID'
        ELSE 'INVALID'
    END AS SYNTAX_STATUS
FROM MYLIB.COMMAND_QUEUE
ORDER BY COMMAND_ID;

This can be useful before:

• migrating scheduler definitions
• promoting commands to production
• activating a new interface
• replaying failed automation
• changing libraries or object names

Use it in a stored procedure

CREATE OR REPLACE PROCEDURE MYLIB.ADD_COMMAND
(
    IN  P_COMMAND_TEXT VARCHAR(32000),
    OUT P_SUCCESS      BOOLEAN,
    OUT P_MESSAGE      VARCHAR(500)
)
LANGUAGE SQL
BEGIN
    IF SYSTOOLS.CHECK_COMMAND_SYNTAX(P_COMMAND_TEXT)
       IS TRUE THEN

        INSERT INTO MYLIB.COMMAND_QUEUE
        (
            COMMAND_TEXT,
            STATUS
        )
        VALUES
        (
            P_COMMAND_TEXT,
            'READY'
        );

        SET P_SUCCESS = TRUE;
        SET P_MESSAGE = 'Command accepted';
    ELSE
        SET P_SUCCESS = FALSE;
        SET P_MESSAGE = 'The CL command syntax is not valid';
    END IF;
END;

The procedure validates the command before storing it.

It still should not trust arbitrary commands merely because they are syntactically correct.

Security considerations

A command-execution framework should control:

• who can submit commands
• which commands are allowed
• which parameters are allowed
• which profile executes the command
• whether adopted authority is involved
• how commands are logged
• whether commands can be changed after approval
• how destructive operations are blocked

Do not expose unrestricted QCMDEXC execution to user-controlled input.

For a product or internal utility, consider an allowlist:

DSPJOB
DSPMSG
WRKACTJOB
SBMJOB with approved parameters
application-specific commands

Reject commands outside the permitted set even when the syntax is valid.

What the function does not prove

A true result does not prove that:

• the command will run successfully
• the object exists
• the user is authorized
• the command is safe
• the command belongs in the current environment
• the command will produce the intended business result

It proves that the command string passes syntax validation.

Native API versus SQL

The underlying native approach is the IBM-supplied:

QCMDCHK

program.

Use QCMDCHK directly when:

• writing CL or another HLL program
• prompting a user for command parameters
• working entirely within a native program
• you need the API’s native behavior

Use SYSTOOLS.CHECK_COMMAND_SYNTAX when:

• the command is already in SQL
• commands are stored in a table
• validation is part of a stored procedure
• a report must identify invalid command definitions
• the result must be used in an SQL expression

Release requirement

The service is delivered for current IBM i 7.6 and IBM i 7.5 TR levels through their corresponding Db2 for i PTF groups.

Confirm that the current Db2 for i Group PTF is installed before using the function.

Authority

CHECK_COMMAND_SYNTAX is delivered in SYSTOOLS as an example service built on IBM i interfaces.

Its effective authority requirements depend on the interfaces used by the implementation.

To inspect the supplied SQL source in ACS:

Schemas
  SYSTOOLS
    Functions
      CHECK_COMMAND_SYNTAX
        Generate SQL

A practical workflow

1. Receive or read the command string.
2. Enforce the maximum allowed length.
3. Apply an application command allowlist.
4. Validate the syntax with CHECK_COMMAND_SYNTAX.
5. Reject invalid syntax with a clear message.
6. Store the approved command and audit information.
7. Execute under a controlled profile.
8. Capture the command result and job log.

Final takeaway

SYSTOOLS.CHECK_COMMAND_SYNTAX gives SQL applications a clean way to answer:

Is this a validly constructed IBM i CL command?

It does so without running the command.

That makes it useful for schedulers, deployment tools, support utilities, and any application that stores or processes CL commands—but syntax validation must remain only one layer of a controlled command-execution design.