IBM i Automation

How to Build Safer SQL-Driven CL Automation on IBM i

A practical architecture for validating and executing IBM i CL commands from SQL using SYSTOOLS.CHECK_COMMAND_SYNTAX and QSYS2.QCMDEXC without creating an unrestricted command shell.

SQL can now validate an IBM i CL command and execute it in the current job. That combination is extremely useful for automation—but it must not become an unrestricted command shell hidden behind a table, stored procedure, API, dashboard, or scheduler.

Two services form the foundation:

SYSTOOLS.CHECK_COMMAND_SYNTAX
QSYS2.QCMDEXC

CHECK_COMMAND_SYNTAX answers:

Is this CL command syntactically valid?

QCMDEXC answers:

Can this command be executed successfully in the current job and authority context?

Those are different questions.

A safe design must also answer:

Is this command approved?
Are these parameters allowed?
Is the target environment correct?
Does the caller have the intended authority?
Could the command expose a secret?
Is the operation idempotent?
Can it be audited and reversed?

Why SQL-driven CL automation matters

IBM i applications and administrative workflows frequently need to perform CL operations such as:

• add or remove a library-list entry
• submit a batch job
• hold or release a job
• check an object
• create an output queue
• change an object description
• save an object
• start or stop a controlled subsystem
• call an existing CL wrapper
• perform a deployment step

SQL-driven command execution can simplify orchestration when the surrounding workflow already uses Db2 for i.

Examples include:

• a stored procedure
• an operational dashboard
• a deployment table
• a work queue
• a scheduled SQL script
• an API implemented over SQL
• a support utility

The benefit is not that SQL replaces CL.

The benefit is that SQL can select, validate, control, record, and coordinate the work while CL performs the IBM i operation.

Validate a command without executing it

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

It returns:

TRUE
FALSE

The command can be up to 32,000 characters.

A true result means the command passed syntax checking.

It does not mean:

• the current profile is authorized
• the target object exists
• the command is allowed in the current job type
• the command is safe
• the target library is approved
• the command will succeed when executed
• the command is appropriate in production

Syntax validation is one control—not the complete control model.

Execute a command from SQL

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

The result is:

 1   Command completed successfully
-1   Command execution failed

The command runs in the current job.

Behavior depends on the current:

• user profile
• adopted authority
• library list
• current library
• ASP group
• job type
• environment
• overrides
• message handling
• commitment-control state
• object locks

The same command can succeed in one job and fail—or affect a different object—in another.

Control command text in the job log

The current scalar function supports:

PRINT => 'NONE'
PRINT => 'ERROR'
PRINT => '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 only when execution fails.

VERBOSE

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

The command text is always written.

The dangerous design

A table like this may appear convenient:

CREATE TABLE AUTOMATION.COMMAND_QUEUE
(
    REQUEST_ID      BIGINT
                    GENERATED ALWAYS AS IDENTITY,
    COMMAND_TEXT    VARCHAR(32000),
    REQUESTED_BY    VARCHAR(128),
    STATUS          VARCHAR(20)
);

Then a processor runs:

SELECT QSYS2.QCMDEXC(COMMAND_TEXT)
FROM AUTOMATION.COMMAND_QUEUE
WHERE STATUS = 'READY';

This creates several risks:

• arbitrary commands can be inserted
• a query can execute more rows than expected
• the same row can execute twice
• destructive commands can be submitted
• target libraries can be changed
• sensitive values can enter logs
• a privileged processor can become an authority proxy
• one malformed row can disrupt the batch
• command text can be changed after approval
• audit evidence may not prove exactly what was executed

A command table is not automatically an automation platform.

Use a command catalogue, not free-form commands

Store approved command definitions separately from execution requests.

CREATE TABLE AUTOMATION.COMMAND_CATALOG
(
    COMMAND_ID          VARCHAR(30) PRIMARY KEY,
    COMMAND_NAME        VARCHAR(10) NOT NULL,
    COMMAND_TEMPLATE    VARCHAR(32000) NOT NULL,
    ALLOWED_ENVIRONMENT VARCHAR(10) NOT NULL,
    LOGGING_POLICY      VARCHAR(10) NOT NULL,
    REQUIRES_APPROVAL   CHAR(1) NOT NULL,
    IS_ENABLED          CHAR(1) NOT NULL,
    UPDATED_BY          VARCHAR(128) NOT NULL,
    UPDATED_TIMESTAMP   TIMESTAMP NOT NULL
);

Possible entries:

CHECK_OBJECT
SUBMIT_REPORT
HOLD_APPROVED_JOB
ADD_DEPLOY_LIBRARY
SAVE_APPLICATION_OBJECT

Execution requests should reference an approved identifier:

CREATE TABLE AUTOMATION.COMMAND_REQUEST
(
    REQUEST_ID            BIGINT
                          GENERATED ALWAYS AS IDENTITY,
    COMMAND_ID            VARCHAR(30) NOT NULL,
    PARAMETER_JSON        CLOB(64K),
    ENVIRONMENT           VARCHAR(10) NOT NULL,
    REQUESTED_BY          VARCHAR(128) NOT NULL,
    REQUESTED_TIMESTAMP   TIMESTAMP NOT NULL,
    APPROVED_BY           VARCHAR(128),
    APPROVED_TIMESTAMP    TIMESTAMP,
    STATUS                VARCHAR(20) NOT NULL,
    ATTEMPT_COUNT         INTEGER NOT NULL DEFAULT 0,
    EXECUTION_JOB         VARCHAR(28),
    STARTED_TIMESTAMP     TIMESTAMP,
    COMPLETED_TIMESTAMP   TIMESTAMP,
    RESULT_CODE           INTEGER,
    RESULT_MESSAGE        VARCHAR(2048),
    EXECUTED_COMMAND_HASH VARCHAR(128)
);

The request contains business parameters—not an unrestricted CL string.

The processor builds the final command from:

• a controlled template
• validated parameters
• an approved environment
• a fixed command name

Allowlist the command name

Do not determine authorization from the first word of arbitrary text alone.

This is weak:

WHERE UPPER(COMMAND_TEXT) LIKE 'SBMJOB%'

It does not fully control:

• nested command content
• parameters
• qualified command resolution
• proxy commands
• target libraries
• the program called by the command

Use an explicit internal command identifier and a template owned by the automation system.

Example template:

QSYS/CHKOBJ OBJ({LIBRARY}/{OBJECT}) OBJTYPE({OBJECT_TYPE})

Approved parameter rules might be:

LIBRARY      restricted to an environment-specific allowlist
OBJECT       valid IBM i system name
OBJECT_TYPE  one of *FILE, *PGM, *SRVPGM

Validate every parameter

Suppose the request contains:

{
  "library": "MYLIB",
  "object": "ORDERS",
  "objectType": "*FILE"
}

Validate each value independently before constructing the command.

Library allowlist

SELECT 1
FROM AUTOMATION.ALLOWED_LIBRARY
WHERE ENVIRONMENT = :ENVIRONMENT
  AND LIBRARY_NAME = :LIBRARY
  AND IS_ENABLED = 'Y';

Object type allowlist

SELECT 1
FROM AUTOMATION.ALLOWED_OBJECT_TYPE
WHERE COMMAND_ID = 'CHECK_OBJECT'
  AND OBJECT_TYPE = :OBJECT_TYPE;

Where possible, use:

• controlled lookup tables
• object-catalog queries
• dedicated validation routines
• IBM i name-validation services
• strict length and character rules

Avoid generic string replacement as the primary security control.

Fully qualify command and object names

Prefer:

QSYS/CHKOBJ OBJ(MYLIB/ORDERS) OBJTYPE(*FILE)

over:

CHKOBJ OBJ(ORDERS) OBJTYPE(*FILE)

Qualification reduces dependency on:

• current library
• library list
• alternate command definitions
• unexpected object resolution

It does not remove the need for authority or target validation.

Validate syntax after construction

Build the complete command only after every parameter passes validation.

Then run:

VALUES SYSTOOLS.CHECK_COMMAND_SYNTAX(
    'QSYS/CHKOBJ OBJ(MYLIB/ORDERS) OBJTYPE(*FILE)'
);

A safe sequence is:

1. Authorize the requested operation.
2. Validate the environment.
3. Validate every parameter.
4. Build from an approved template.
5. Fully qualify command and objects.
6. Validate the complete syntax.
7. Lock the request against changes.
8. Execute once.
9. Store the result and diagnostics.

Combine validation and execution

WITH COMMAND_TO_RUN (COMMAND_TEXT) AS
(
    VALUES
      'QSYS/CHKOBJ OBJ(MYLIB/ORDERS) OBJTYPE(*FILE)'
)
SELECT
    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 internal meanings:

 1   Executed successfully
-1   Execution failed
-2   Syntax rejected

Production logic should also preserve SQL diagnostics and relevant job messages.

Prevent multiple execution through SELECT cardinality

A scalar function can execute once per qualifying row.

This statement can run many commands:

SELECT QSYS2.QCMDEXC(COMMAND_TEXT)
FROM AUTOMATION.COMMAND_REQUEST
WHERE STATUS = 'READY';

If 500 rows qualify, up to 500 command calls can occur.

Before executing, inspect the target set:

SELECT
    REQUEST_ID,
    COMMAND_ID,
    ENVIRONMENT,
    REQUESTED_BY,
    STATUS
FROM AUTOMATION.COMMAND_REQUEST
WHERE STATUS = 'READY'
ORDER BY REQUEST_ID;

For controlled processing, claim one request at a time or claim a bounded batch.

Claim a request before execution

UPDATE AUTOMATION.COMMAND_REQUEST
SET
    STATUS = 'RUNNING',
    STARTED_TIMESTAMP = CURRENT TIMESTAMP,
    EXECUTION_JOB = QSYS2.JOB_NAME,
    ATTEMPT_COUNT = ATTEMPT_COUNT + 1
WHERE REQUEST_ID = :REQUEST_ID
  AND STATUS = 'READY';

Proceed only when exactly one row was updated.

Additional design is needed for:

• worker failure after claiming
• abandoned RUNNING requests
• retry limits
• reconciliation
• commands that completed but whose status update failed

Idempotency matters

A retry can be safe for:

Check whether an object exists
Add a library only when absent
Create a report with a unique output name
Hold a job that is already held

A retry can be dangerous for:

Submit a financial batch twice
Delete an object
Send a message twice
Create duplicate output
Trigger an interface twice
Run a save operation unexpectedly

For non-idempotent operations, use:

• a unique business request key
• a pre-execution state check
• an execution token
• a durable completion record
• reconciliation logic
• manual review before retry

Do not assume a return value of -1 means the command performed no work.

A command may partially complete before failing.

Use a dedicated execution profile

Avoid:

*ALLOBJ for convenience
shared administrator profiles
interactive operator credentials
developer personal profiles

Prefer:

• a non-interactive service profile
• minimum object authority
• controlled command authority
• restricted function usage
• no unnecessary special authority
• explicit ownership
• auditable job descriptions
• environment-specific profiles

QCMDEXC does not bypass command authority.

The command’s normal authority requirements still apply.

Be deliberate about adopted authority

Review:

• object ownership
• program adoption
• USRPRF(*OWNER) behavior
• routine security behavior
• adopted-authority suppression
• caller authority
• function usage
• public authority

A low-authority caller must not gain a general-purpose privileged command interface through a stored procedure.

The safest interface exposes approved operations such as:

SUBMIT_DAILY_REPORT
CHECK_APPLICATION_OBJECT
PROMOTE_RELEASE

not:

RUN_ANY_COMMAND

Separate request authority from execution authority

A user may be allowed to request an operation without being authorized to execute the underlying CL command directly.

That can be appropriate when:

• the request is constrained
• approval is required
• the processor has limited execution authority
• every parameter is validated
• the action is fully audited
• no arbitrary command text is accepted

This is a controlled privilege boundary.

It must not become an undocumented authority escalation path.

Add approval for high-impact commands

Examples requiring approval can include:

• ending jobs
• stopping subsystems
• changing system values
• deleting objects
• restoring production data
• changing network configuration
• changing security configuration
• deploying to production

Preserve:

Requester
Approver
Approval timestamp
Command identifier
Validated parameters
Environment
Change reference
Execution result

The approver should see the resolved operation and parameters—not merely an internal request number.

Hash the approved command

After approval and final command construction:

Rebuild the command.
Compute a hash.
Store the approved hash.
Recompute it before execution.
Reject the request if the values differ.

This helps show that the command being executed is the one that was approved.

Hashing is not encryption.

Sensitive command content still requires protection.

Avoid embedding passwords

Do not construct command strings containing passwords or other secrets and then store, approve, print, or log them.

Use:

• protected credential stores
• profile handles
• certificate-based authentication
• OAuth tokens managed outside command text
• an approved secrets manager
• application APIs that do not expose secrets as CL parameters

PRINT => 'NONE' reduces job-log exposure but does not make insecure command storage safe.

Choose the logging policy centrally

Add the policy to the command catalogue.

Use NONE when the command may contain sensitive data or when secure structured auditing exists elsewhere.

Use ERROR when the command is non-sensitive and failure diagnosis benefits from the resolved command text.

Use VERBOSE only for controlled tracing where every command must be visible and no secret data is present.

Do not let requesters choose the policy.

Capture structured audit data

A production audit record should include:

Request ID
Command ID
Environment
Validated parameter values
Requester
Approver
Processor profile
Qualified execution job
Start timestamp
Completion timestamp
Return result
SQLSTATE and SQLCODE
Resolved command hash
Retry number
Change reference

Store a redacted command representation when full text would expose sensitive values.

Preserve failure diagnostics

The scalar function returns only:

1
-1

Capture additional information such as:

• SQL diagnostics
• request status
• execution job
• job-log reference
• selected escape and diagnostic messages
• start and completion timestamps
• attempt number

For complex operations, call a controlled CL or RPG wrapper that returns a richer result.

The SQL layer can orchestrate while the wrapper owns operation-specific behavior.

Do not assume SQL rollback reverses the CL command

Some commands:

• are not transactional
• submit work to another job
• change objects immediately
• perform external I/O
• update system configuration
• partially complete before failure

A common pattern is:

Commit the claimed request.
Execute the external or non-transactional operation.
Record completion in a new transaction.
Reconcile uncertain outcomes.

This also avoids holding database locks during long-running CL operations.

Protect the environment boundary

Do not trust only a request column that says:

ENVIRONMENT = 'PROD'

Validate through multiple signals:

• system name
• partition identifier
• environment configuration
• execution profile
• approved library mapping
• job description
• target ASP
• deployment branch or release state

A production processor should not accept development targets merely because they appeared in request data.

Example controlled operation

Suppose an application must submit one approved report.

Command catalogue

COMMAND_ID: SUBMIT_DAILY_REPORT
COMMAND_NAME: SBMJOB
ENVIRONMENT: PROD
LOGGING_POLICY: ERROR
REQUIRES_APPROVAL: N

Approved template

QSYS/SBMJOB
  CMD(CALL PGM(REPORTS/DAILYRPT) PARM('{BUSINESS_DATE}'))
  JOB(DAILYRPT)
  JOBQ(REPORTS/REPORTQ)

Allowed input

BUSINESS_DATE

Validation

Must be an ISO date
Cannot be in the future
Must not already have a completed request
Must be within the supported reporting period

Idempotency key

SUBMIT_DAILY_REPORT + BUSINESS_DATE + ENVIRONMENT

The requester cannot change:

• program
• job queue
• job name
• command
• target library
• logging policy

That is an automation interface.

A free-form command field is not.

Example procedure outline

CREATE OR REPLACE PROCEDURE AUTOMATION.SUBMIT_DAILY_REPORT
(
    IN P_BUSINESS_DATE DATE
)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
    DECLARE V_COMMAND VARCHAR(32000);
    DECLARE V_RESULT INTEGER;

    IF P_BUSINESS_DATE > CURRENT DATE THEN
        SIGNAL SQLSTATE '75001'
           SET MESSAGE_TEXT =
               'Business date cannot be in the future';
    END IF;

    SET V_COMMAND =
        'QSYS/SBMJOB CMD(CALL PGM(REPORTS/DAILYRPT) '
        CONCAT 'PARM('''
        CONCAT VARCHAR_FORMAT(P_BUSINESS_DATE, 'YYYY-MM-DD')
        CONCAT ''')) JOB(DAILYRPT) JOBQ(REPORTS/REPORTQ)';

    IF SYSTOOLS.CHECK_COMMAND_SYNTAX(V_COMMAND)
       IS NOT TRUE
    THEN
        SIGNAL SQLSTATE '75003'
           SET MESSAGE_TEXT =
               'Constructed command failed syntax validation';
    END IF;

    SET V_RESULT =
        QSYS2.QCMDEXC(
            COMMAND => V_COMMAND,
            PRINT   => 'ERROR'
        );

    IF V_RESULT <> 1 THEN
        SIGNAL SQLSTATE '75004'
           SET MESSAGE_TEXT =
               'Report submission command failed';
    END IF;
END;

This illustrates the pattern.

A production implementation should add durable lifecycle rows, concurrency control, diagnostics, execution-job capture, reconciliation, and environment-specific configuration.

Test the failure paths

Include tests for:

Invalid command syntax
Unauthorized command
Invalid object name
Disallowed library
Wrong environment
Duplicate request
Two workers claiming one row
Processor ending after command execution
Processor ending before completion update
Command partially completing
Job-log printing with sensitive parameters
Retry after uncertain outcome
Approval changed after request creation

Security and reliability problems usually appear in failure paths.

Recommended architecture

Requester
   |
   v
Approved operation interface
   |
   v
Parameter validation
   |
   v
Environment and authority checks
   |
   v
Command catalogue and fixed template
   |
   v
CHECK_COMMAND_SYNTAX
   |
   v
Durable request claim
   |
   v
QCMDEXC or controlled wrapper
   |
   v
Structured result and audit record
   |
   v
Reconciliation and alerting

When SQL-driven CL is a good fit

Use it when:

• the workflow already begins in SQL
• the CL operation is narrow and approved
• parameters can be strictly validated
• results can be audited
• authority can be minimized
• retries and idempotency are understood

When a wrapper program is better

Use an RPG or CL wrapper when:

• multiple commands form one operation
• detailed message handling is needed
• operation-specific recovery is required
• sensitive values must be retrieved securely
• adopted authority needs careful containment
• richer output is required
• commitment control must be coordinated
• the operation is reused outside SQL

SQL can still call the wrapper through one approved command.

Detailed SQL references

• Validate an IBM i CL Command Without Running It
• Run IBM i CL Commands from SQL and Control Job-Log Output

Final takeaway

CHECK_COMMAND_SYNTAX and QCMDEXC become dangerous when the surrounding design accepts arbitrary text, depends on excessive authority, ignores environment context, or cannot prove what was executed.

The safe model is:

Approved operation
Validated parameters
Fixed template
Qualified targets
Syntax check
Minimum authority
Single execution
Structured audit
Known recovery

Use SQL to orchestrate controlled IBM i capabilities—not to expose a general-purpose command line.