Overview

This document provides SQL-based approaches for ending IBM i jobs based on various criteria. These examples use the QSYS2.ACTIVE_JOB_INFO table function combined with QSYS2.QCMDEXC to identify and terminate jobs that meet specific conditions.

Important Considerations

• Consider using OPTION(*CNTRLD) instead of OPTION(*IMMED) for graceful shutdowns
• Ensure proper logging and audit trails are in place
• Coordinate with application owners and users before implementing automated job termination

Key Parameters:

• SUBSYSTEM LIKE 'QBATCH': Targets jobs in the batch subsystem
• JOB_ACTIVE_TIME + 10 HOURS: Adjustable threshold for maximum runtime
• OPTION(*IMMED): Immediate termination (use *CNTRLD for controlled)
• LOGLMT(0): Prevents excessive logging during job end, if a Joblog is required, remove this parameter from the command.

Key Parameters:

• SUBSYSTEM LIKE 'QINTER': Targets interactive jobs
• CURRENT_TIME > '17:00': Time-based trigger (adjust for your business hours)
• Consider adding exceptions for specific users or job names if needed
• LOGLMT(0): Prevents excessive logging during job end, if a Joblog is required, remove this parameter from the command.

Key Parameters:

• JOB_STATUS = 'DSPW': Specifically targets jobs in display wait
• Combines time-based and status-based criteria for precise targeting
• Useful for freeing up interactive job slots
• LOGLMT(0): Prevents excessive logging during job end, if a Joblog is required, remove this parameter from the command.

Key Parameters:

• JOB_USER = 'USERID': Replace with the target user profile
• Terminates jobs across all subsystems for the specified user
• Consider security implications and proper authorization
• LOGLMT(0): Prevents excessive logging during job end, if a Joblog is required, remove this parameter from the command.

Key Parameters:

• ELAPSED_CPU_PERCENTAGE > 50: Targets jobs using more than 50% CPU
• CPU_TIME > 300000: Minimum CPU time threshold (in milliseconds)
• DETAILED_INFO => 'ALL': Required to access CPU metrics
• Adjust thresholds based on your system's normal workload patterns
• LOGLMT(0): Prevents excessive logging during job end, if a Joblog is required, remove this parameter from the command.

Best Practices

Testing and Validation

1. Preview Before Execution

Remove the QSYS2.QCMDEXC call to preview which jobs would be affected:

SELECT JOB_NAME, JOB_USER, SUBSYSTEM, runtime_hours FROM TEMPJOB;

2. Start with Controlled Shutdown

Use OPTION(*CNTRLD) instead of OPTION(*IMMED) for initial testing:

QSYS2.QCMDEXC('ENDJOB JOB(' || JOB_NAME || ') OPTION(*CNTRLD) DELAY(30)')

3. Add Exclusions

Protect critical jobs by adding exclusion criteria:

AND JOB_NAME NOT LIKE 'CRITICAL%' AND JOB_USER NOT IN ('QSECOFR', 'ADMIN')

Scheduling and Automation

• Schedule these queries during maintenance windows when appropriate
• Implement notification mechanisms before automated job termination
• Maintain audit logs of all automated job terminations
• Document business rules and approval processes

Error Handling

• Monitor the "Success?" column for failed termination attempts
• Investigate jobs that cannot be ended (may indicate system issues)
• Implement retry logic for jobs that fail to terminate

Scheduling with Job Scheduler (ADDJOBSCDE)

To automate these job termination queries, you can schedule them using the IBM i Job Scheduler with the RUNSQLSTM command. This allows you to run SQL scripts at specific times or intervals.

Step 1: Create SQL Script Files

First, save your SQL query to a source physical file member. For example, to create a script for ending long-running batch jobs:

CRTSRCPF FILE(QGPL/SQLSCRIPTS) RCDLEN(112) ADDPFM FILE(QGPL/SQLSCRIPTS) MBR(ENDBATCH) TEXT('End long-running batch jobs')

Then edit the member (using STRSEU or EDTF) and add your SQL.

Step 2: Test the SQL Script

Before scheduling, test the script manually using RUNSQLSTM:

RUNSQLSTM SRCFILE(QGPL/SQLSCRIPTS) SRCMBR(ENDBATCH) COMMIT(*NONE) NAMING(*SQL)

Step 3: Schedule with ADDJOBSCDE

Once tested, schedule the script to run automatically using ADDJOBSCDE:

Example 1: Daily Execution at Specific Time

End long-running batch jobs daily at 11:00 PM:

ADDJOBSCDE JOB(ENDBATCH) CMD(RUNSQLSTM SRCFILE(QGPL/SQLSCRIPTS) SRCMBR(ENDBATCH) COMMIT(*NONE) NAMING(*SQL)) FRQ(*WEEKLY) SCDDATE(*NONE) SCDDAY(*ALL) SCDTIME(230000) TEXT('End long-running batch jobs')

Example 2: After-Hours Interactive Cleanup

End interactive sessions after business hours (5:30 PM) on weekdays:

ADDJOBSCDE JOB(ENDINTER) CMD(RUNSQLSTM SRCFILE(QGPL/SQLSCRIPTS) SRCMBR(ENDINTER) COMMIT(*NONE) NAMING(*SQL)) FRQ(*WEEKLY) SCDDATE(*NONE) SCDDAY(*MON *TUE *WED *THU *FRI) SCDTIME(173000) TEXT('End interactive sessions after hours')

Managing Scheduled Jobs

View scheduled jobs:

WRKJOBSCDE

Or using SQL:

SELECT JOB_NAME, SCHEDULED_DATE, SCHEDULED_TIME, SCHEDULED_DAY, FREQUENCY, STATUS, TEXT_DESCRIPTION FROM QSYS2.SCHEDULED_JOB_INFO WHERE JOB_NAME IN ('ENDBATCH', 'ENDINTER', 'ENDCPU') ORDER BY JOB_NAME;

Remove a scheduled job:

RMVJOBSCDE JOB(ENDBATCH)

Change schedule:

CHGJOBSCDE JOB(ENDBATCH) SCDTIME(220000)

Hold/Release a scheduled job:

HLDJOBSCDE JOB(ENDBATCH) RLSJOBSCDE JOB(ENDBATCH)

Best Practices for Scheduled Job Termination

1. Start with Logging Only: Initially schedule queries without QCMDEXC to log results only
2. Gradual Implementation: Begin with conservative thresholds and adjust based on results
3. Regular Review: Periodically review job logs to ensure proper operation
4. Error Handling: Monitor for failed executions and investigate causes
5. Documentation: Maintain records of scheduled jobs and their business justification

Additional Resources

• IBM i Services: ACTIVE_JOB_INFO
• IBM i Services: QCMDEXC
• ENDJOB Command Reference
• RUNSQLSTM Command Reference
• ADDJOBSCDE Command Reference

Conclusion

These SQL-based job termination techniques provide powerful tools for system administrators to manage IBM i workloads effectively. Always exercise caution, test thoroughly, and ensure proper authorization before implementing automated job termination procedures.