HP Neoview Release 2.3 Software - Manual (Page 71)

Example 9-6 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-7 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-8 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';
Job Statistics on the Neoview Platform 71