IBM i Job End Code Analysis - SYSTOOLS.ENDED_JOB_INFO

Steps

Job End Code Reference

The JOB_END_CODE field in SYSTOOLS.ENDED_JOB_INFO indicates how a job terminated:

SQL Query 1: Job End Code Distribution Summary

This query provides a comprehensive summary of job end codes over a specified time period, showing the frequency and percentage of each end code type.

-- Job End Code Distribution Analysis
-- Purpose: Analyze the distribution of job end codes to identify patterns
-- and potential issues in job terminations

WITH job_end_summary AS (
  SELECT 
    JOB_END_CODE,
    CASE JOB_END_CODE
      WHEN 0 THEN 'Normal completion'
      WHEN 10 THEN 'Normal - controlled ending'
      WHEN 20 THEN 'Exceeded end severity'
      WHEN 30 THEN 'Abnormal ending'
      WHEN 40 THEN 'Ended before active'
      WHEN 50 THEN 'Ended while active'
      WHEN 60 THEN 'Subsystem abnormal end'
      WHEN 70 THEN 'System abnormal end'
      WHEN 80 THEN 'ENDJOBABN command'
      WHEN 90 THEN 'Forced end - time limit'
      ELSE 'Unknown'
    END AS END_CODE_DESCRIPTION,
    COUNT(*) AS JOB_COUNT,
    ROUND(
      DECIMAL(COUNT(*)) / DECIMAL(SUM(COUNT(*)) OVER()) * 100, 
      2
    ) AS PERCENTAGE
  FROM TABLE(
    SYSTOOLS.ENDED_JOB_INFO(
      START_TIME => CURRENT TIMESTAMP - 7 DAYS,
      END_TIME => CURRENT TIMESTAMP
    )
  )
  GROUP BY JOB_END_CODE
)
SELECT 
  JOB_END_CODE,
  END_CODE_DESCRIPTION,
  JOB_COUNT,
  PERCENTAGE,
  CASE 
    WHEN JOB_END_CODE IN (0, 10) THEN 'Normal'
    WHEN JOB_END_CODE IN (20, 30, 40, 50, 60, 70, 80, 90) THEN 'Requires Review'
    ELSE 'Unknown'
  END AS SEVERITY_CATEGORY
FROM job_end_summary
ORDER BY JOB_COUNT DESC;

Query Results Interpretation

• Normal completion (0, 10): Expected job terminations
• Exceeded end severity (20): Jobs that encountered errors exceeding the ENDSEV threshold
• Abnormal endings (30, 60, 70): Critical issues requiring immediate investigation
• Forced terminations (80, 90): Administrative interventions or timeout conditions
• Premature endings (40, 50): Jobs that failed to complete their lifecycle

SQL Query 2: Abnormal Job Termination Details

This query focuses on abnormal job terminations (end codes 30, 40, 50, 60, 70, 80, 90), providing detailed information to help diagnose issues.

-- Abnormal Job Termination Analysis
-- Purpose: Identify and analyze jobs with abnormal end codes
-- Focus: End codes 30, 40, 50, 60, 70, 80, 90

SELECT
  FROM_JOB_NAME,
  FROM_JOB_USER,
  FROM_JOB_NUMBER,
  SUBSYSTEM,
  JOB_TYPE,
  JOB_END_CODE,
  CASE JOB_END_CODE
    WHEN 30 THEN 'Abnormal ending'
    WHEN 40 THEN 'Ended before active'
    WHEN 50 THEN 'Ended while active'
    WHEN 60 THEN 'Subsystem abnormal end'
    WHEN 70 THEN 'System abnormal end'
    WHEN 80 THEN 'ENDJOBABN command'
    WHEN 90 THEN 'Forced end - time limit'
  END AS END_CODE_DESCRIPTION,
  JOB_END_DETAIL,
  SECONDARY_ENDING_CODE,
  SECONDARY_ENDING_CODE_DETAIL,
  MESSAGE_TIMESTAMP AS JOB_END_TIME,
  CPU_TIME,
  NUMBER_OF_STEPS,
  FROM_PROGRAM
FROM TABLE(
  SYSTOOLS.ENDED_JOB_INFO(
    START_TIME => CURRENT TIMESTAMP - 7 DAYS,
    END_TIME => CURRENT TIMESTAMP
  )
)
WHERE JOB_END_CODE IN (30, 40, 50, 60, 70, 80, 90)
ORDER BY MESSAGE_TIMESTAMP DESC
FETCH FIRST 100 ROWS ONLY;

Key Fields for Analysis

• JOB_END_DETAIL: Detailed description of how the job ended
• SECONDARY_ENDING_CODE: Additional ending code information
• SECONDARY_ENDING_CODE_DETAIL: Detailed description of the secondary ending code
• FROM_PROGRAM: The program that was running when the job ended

SQL Query 3: Job End Code Trends by Subsystem

This query analyzes job end code patterns by subsystem, helping identify subsystem-specific issues or configuration problems.

-- Job End Code Trends by Subsystem
-- Purpose: Analyze job end codes grouped by subsystem to identify
-- subsystem-specific issues or patterns

SELECT
  SUBSYSTEM,
  JOB_END_CODE,
  CASE JOB_END_CODE
    WHEN 0 THEN 'Normal completion'
    WHEN 10 THEN 'Normal - controlled ending'
    WHEN 20 THEN 'Exceeded end severity'
    WHEN 30 THEN 'Abnormal ending'
    WHEN 40 THEN 'Ended before active'
    WHEN 50 THEN 'Ended while active'
    WHEN 60 THEN 'Subsystem abnormal end'
    WHEN 70 THEN 'System abnormal end'
    WHEN 80 THEN 'ENDJOBABN command'
    WHEN 90 THEN 'Forced end - time limit'
    ELSE 'Unknown'
  END AS END_CODE_DESCRIPTION,
  COUNT(*) AS JOB_COUNT,
  ROUND(AVG(CPU_TIME), 3) AS AVG_CPU_TIME_SEC,
  ROUND(AVG(TOTAL_RESPONSE_TIME), 2) AS AVG_RESPONSE_TIME_MS,
  MIN(MESSAGE_TIMESTAMP) AS FIRST_OCCURRENCE,
  MAX(MESSAGE_TIMESTAMP) AS LAST_OCCURRENCE
FROM TABLE(
  SYSTOOLS.ENDED_JOB_INFO(
    START_TIME => CURRENT TIMESTAMP - 7 DAYS,
    END_TIME => CURRENT TIMESTAMP
  )
)
WHERE SUBSYSTEM IS NOT NULL
GROUP BY SUBSYSTEM, JOB_END_CODE
HAVING COUNT(*) > 5  -- Focus on patterns with multiple occurrences
ORDER BY
  SUBSYSTEM,
  CASE
    WHEN JOB_END_CODE IN (30, 60, 70, 80, 90) THEN 1
    WHEN JOB_END_CODE IN (20, 40, 50) THEN 2
    ELSE 3
  END,
  JOB_COUNT DESC;

Analysis Guidelines

1. High abnormal end codes in specific subsystems: May indicate subsystem configuration issues or resource constraints
2. Consistent end code 60: Suggests subsystem stability problems
3. Frequent end code 80/90: May indicate aggressive job management or timeout issues
4. End code 20 patterns: Review ENDSEV settings for affected subsystems

Best Practices

Monitoring Recommendations

1. Establish Baselines: Run Query 1 regularly to understand normal end code distributions
2. Alert on Anomalies: Set up monitoring for increases in abnormal end codes (30, 60, 70)
3. Review Forced Terminations: Investigate end codes 80 and 90 to ensure they're intentional
4. Subsystem Health: Use Query 3 to identify subsystems with recurring issues

Investigation Steps

When abnormal end codes are detected:

1. Review the job log using DISPLAY_JOURNAL or DSPJOBLOG
2. Check system history log for related messages using HISTORY_LOG_INFO
3. Examine subsystem configuration if end code 60 is frequent
4. Review system resources (CPU, memory, disk) for end codes 70
5. Verify job timeout settings for end codes 90