System Analysis Queries¶

Category: Database Administration & Analysis Tags: SQL, Oracle, system-analysis, troubleshooting, email-delivery, LIMS

Email Delivery System Analysis¶

Task Parameter Verification¶

1. Check EmailDelivery Task Setup

-- Verify the EmailDelivery task configuration
SELECT * FROM task_parameters WHERE task_name = 'Email Delivery';

2. Review EmailDelivery Parameters

-- Examine task parameter details (MaxAttachments should be 3)
SELECT * FROM task_parameter_details
WHERE task_seq = (SELECT task_seq FROM task_parameters WHERE task_name = 'Email Delivery')
ORDER BY slot;

Job Configuration Analysis¶

3. Find Email Delivery Jobs

-- Locate jobs that use RPTDELJOB (job_type = 'RPTDELJOB')
SELECT job_name, job_action, enabled, repeat_interval, comments
FROM user_scheduler_jobs
WHERE job_action LIKE '%RPTDELJOB%'
   OR job_action LIKE '%EmailDelivery%'
   OR job_action LIKE '%hui_utility.SendEmail%';

4. Check Report Delivery Table Structure

-- Review report_delivery table structure from fnshrev code
DESC report_delivery;

Data Analysis¶

5. Current Report Delivery Entries

-- Examine current report_delivery entries
SELECT delivery_seq, delivery_id, delivery_method, delivery_type, status,
       comp_date, filename, addr_seq, cust_id
FROM report_delivery
WHERE delivery_method = 'E'  -- Email from fnshrev code
ORDER BY delivery_seq DESC
FETCH FIRST 10 ROWS ONLY;

6. HUI Utility Package Analysis

-- Check hui_utility package (used in delivery_summary)
SELECT text FROM user_source
WHERE name = 'HUI_UTILITY'
  AND type = 'PACKAGE BODY'
  AND UPPER(text) LIKE '%SENDEMAIL%'
ORDER BY line;

Scheduled Job Investigation¶

7. Report Delivery Processing Jobs

-- Look for scheduled jobs that process report_delivery
SELECT job_name, program_name, job_action, enabled, repeat_interval
FROM user_scheduler_jobs
WHERE UPPER(job_action) LIKE '%REPORT_DELIVERY%'
   OR UPPER(job_action) LIKE '%HUI_UTILITY%'
   OR UPPER(job_action) LIKE '%SENDEMAIL%';

8. Delivery Summary Function Parameters

-- Check delivery_summary function parameters
SELECT text FROM user_source
WHERE name = 'DELIVERY_SUMMARY'
  AND type = 'FUNCTION'
  AND line <= 20
ORDER BY line;

9. Jobs Using Delivery Summary Function

-- Find jobs using delivery_summary function
SELECT job_name, job_action
FROM user_scheduler_jobs
WHERE UPPER(job_action) LIKE '%DELIVERY_SUMMARY%';

Sample Data Analysis¶

10. Samples Table Structure

-- Check samples table structure
DESC samples;

11. Sample Template Data

-- Examine sample data with template column
SELECT hsn, column_template_to_use, COUNT(*)
FROM samples
WHERE column_template_to_use IS NOT NULL
GROUP BY hsn, column_template_to_use
ORDER BY COUNT(*) DESC
FETCH FIRST 10 ROWS ONLY;

System Configuration¶

12. Map to HDM Table Structure

-- Check map_to_hdm table from fnshrev code
DESC map_to_hdm;

13. Email Queue Tables Discovery

-- Look for tables that might queue EmailDelivery jobs
SELECT table_name FROM user_tables
WHERE table_name IN (
  'EMAIL_QUEUE', 'JOB_QUEUE', 'TASK_QUEUE',
  'DELIVERY_QUEUE', 'EMAIL_JOBS', 'RPTDELJOB'
);

14. Sequence Status Check

-- Check sequences used in the code
SELECT sequence_name, last_number FROM user_sequences
WHERE sequence_name IN ('DELIVERY_SEQ', 'TASK_SEQ', 'HDM_SEQ');

Final Email Job Verification¶

15. Active Email Delivery Jobs

-- Final check - locate the actual email delivery job
SELECT job_name, job_type, job_action, enabled, repeat_interval, comments
FROM user_scheduler_jobs
WHERE enabled = 'TRUE'
  AND (UPPER(job_action) LIKE '%EMAIL%'
    OR UPPER(comments) LIKE '%EMAIL%'
    OR UPPER(comments) LIKE '%DELIVERY%');

Usage Instructions¶

Run queries in sequence - These queries are designed to be executed in order for systematic analysis
Check for NULL values - Pay attention to NULL results which may indicate missing configuration
Verify job status - Ensure email delivery jobs are enabled and properly scheduled
Monitor sequence numbers - Check if sequences are incrementing properly
Review error logs - Look for patterns in failed delivery attempts

Common Issues to Look For¶

Missing task parameters - EmailDelivery task not properly configured
Disabled jobs - Email delivery jobs that are not enabled
Sequence gaps - Indicating potential data integrity issues
Queue table absence - Missing tables that should queue email jobs
Function dependencies - Missing or broken package dependencies

Troubleshooting Notes¶

If EmailDelivery task parameters are missing, the email system won't function
MaxAttachments parameter should typically be set to 3
Check job scheduling intervals to ensure adequate processing frequency
Verify HUI_UTILITY package is properly compiled and accessible