HP Neoview Release 2.5 Software User Manual

Page 84

Example 9-7 Identify All Jobs Executed From Host abc Using the Latest Version of Control File
/home/control files/ControlFile.txt

SELECT DISTINCT A.JOB_NAME
FROM HP_TRANSPORTER.BASE_JOB_STATS A,
HP_TRANSPORTER.CONTROL_FILES B
WHERE A.FILE_ID = B.FILE_ID
AND B.FILE_HOST = 'abc'
AND B.FILE_ABS_PATH = '/home/control_files/ControlFile.txt'
AND B.FILE_VERSION = (SELECT MAX(FILE_VERSION)
FROM HP_TRANSPORTER.CONTROL_FILES
WHERE FILE_HOST = B.FILE_HOST
AND FILE_ABS_PATH = B.FILE_ABS_PATH)
FOR READ UNCOMMITTED ACCESS;

JOB_NAME
july_info_load
july_orders_load
july_revenue_load

Example 9-8 Delete All Job Statistics For Jobs Started Three Or More Months Ago

DELETE FROM HP_TRANSPORTER.BASE_JOB_STATS
WHERE DATEDIFF(MONTH, START_TIME, CURRENT_TIMESTAMP) >= 3;

Example 9-9 Delete All Versions of Control File /home/control files/ControlFile.txt
That Have Been Used On Host abc

NOTE:

The three delete statements must be used in this order.

DELETE FROM HP_TRANSPORTER.BASE_JOB_STATS
WHERE FILE_ID IN (SELECT FILE_ID
FROM HP_TRANSPORTER.CONTROL_FILES
WHERE FILE_HOST = 'abc'
AND FILE_ABS_PATH = '/home/control_files/ControlFile.txt');

DELETE FROM HP_TRANSPORTER.CONTROL_FILE_TEXT
WHERE FILE_ID IN (SELECT FILE_ID
FROM HP_TRANSPORTER.CONTROL_FILES
WHERE FILE_HOST = 'abc'
AND FILE_ABS_PATH = '/home/control_files/ControlFile.txt');

DELETE FROM HP_TRANSPORTER.CONTROL_FILES
WHERE FILE_HOST = 'abc'
AND FILE_ABS_PATH = '/home/control_files/ControlFile.txt';

Troubleshooting