HP Neoview Release 2.4 Software - Manual (Page 77)

Example 9-5 Identify All Job Entries that Successfully Completed Within the Last Seven Days
SELECT B.FILE_HOST,
B.FILE_ABS_PATH,
B.FILE_MODTIME,
A.JOB_NAME,
A.SOURCE_NAME,
A.TARGET_NAME,
A.START_TIME,
A.END_TIME
FROM HP_TRANSPORTER.BASE_JOB_STATS A,
HP_TRANSPORTER.CONTROL_FILES B
WHERE A.FILE_ID = B.FILE_ID
AND A.END_TIME IS NOT NULL
AND DATEDIFF(DAY, A.END_TIME, CURRENT_TIMESTAMP) < 7
ORDER BY B.FILE_HOST,
B.FILE_ABS_PATH,
B.FILE_MODTIME,
A.JOB_NAME,
A.SOURCE_NAME,
A.TARGET_NAME,
A.START_TIME
FOR READ UNCOMMITTED ACCESS;
FILE_HOST FILE_ABS_PATH FILE_MODTIME JOB_NAME SOURCE_NAME TARGET_NAME START_TIME END_TIME
mynode.intranet.foo.bar.net /mydir/control-files/control_BAT001.txt 2008-02-13 16:06:05.0 load1 src1 tgt1
2008-02-13 16:06:22.592 2008-02-13 16:06:27.437
mynode.intranet.foo.bar.net /mydir/control-files/control_BAT001.txt 2008-02-13 16:30:47.0 load1 src1 tgt1
2008-02-13 16:31:01.407 2008-02-13 16:31:04.443
mynode.intranet.foo.bar.nett /mydir/control-files/control_BAT001.txt 2008-02-13 16:47:18.0 load1 src1 tgt1
2008-02-13 16:47:28.818 2008-02-13 16:47:31.875
mynode.intranet.foo.bar.net /mydir/control-files/control_BAT001.txt 2008-02-13 17:25:59.0 load1 src1 tgt1
2008-02-13 17:26:08.466 2008-02-13 17:26:12.965
mynode.intranet.foo.bar.nett /dbt/testdir/test-file.cf 2008-02-01 17:53:33.0 LOAD1 SRC1 TGT1 2008-02-13 18:04:50.0
2008-02-13 18:05:10.0
mynode.intranet.foo.bar.net /dbt/testdir/test-file.cf 2008-02-01 17:53:33.0 LOAD1 SRC1 TGT1 2008-02-13 18:10:43.0
2008-02-13 18:11:03.0
Example 9-6 Identify All Job Entries That Were Started in the Last 24 Hours But Have Not Completed
— Either Failed or In Progress
SELECT B.FILE_HOST,
B.FILE_ABS_PATH,
B.FILE_MODTIME,
A.JOB_NAME,
A.SOURCE_NAME,
A.TARGET_NAME,
A.START_TIME
FROM HP_TRANSPORTER.BASE_JOB_STATS A,
HP_TRANSPORTER.CONTROL_FILES B
WHERE A.FILE_ID = B.FILE_ID
AND A.END_TIME IS NULL
AND DATEDIFF(HOUR, A.START_TIME, CURRENT_TIMESTAMP) < 24
ORDER BY B.FILE_HOST,
B.FILE_ABS_PATH,
B.FILE_MODTIME,
A.JOB_NAME,
A.SOURCE_NAME,
A.TARGET_NAME,
A.START_TIME
FOR READ UNCOMMITTED ACCESS;
FILE_HOST FILE_ABS_PATH FILE_MODTIME JOB_NAME SOURCE_NAME TARGET_NAME START_TIME
CACSELLK T:\sellk_Java_DBT_1\transporter\DBTransporter\src\conf\fayecf1.txt 2008-02-13 15:24:16.0
extract_wjp_char_fix_20 wjp_char_fix_20_ex ex_wjp_fix_20 2008-02-13 15:38:40.224
anbe-xp C:\dbt\delimited+fixed\combined_cf.txt 2008-02-04 12:49:07.0 load1 src1 tgt1 2008-02-13 17:22:13.644
anbe-xp C:\dbt\delimited+fixed\combined_cf.txt 2008-02-04 12:49:07.0 load1 src1 tgt1 2008-02-13 17:28:12.421
anbe-xp C:\dbt\delimited+fixed\combined_cf.txt 2008-02-04 12:49:07.0 load1 src1 tgt1 2008-02-13 17:29:32.779
anbe-xp C:\dbt\delimited\TPCH_ORDERS_cf.txt 2008-02-13 17:34:22.0 load1 src1 tgt1 2008-02-13 17:34:55.228
mynode.intranet.foo.bar.net /mydir/control-files/control_BAT004.txt 2008-02-13 16:06:05.0 load1 src1 tgt1
2008-02-13 16:07:11.506
mynode.intranet.foo.bar.net /mydir/control-files/control_BAT004.txt 2008-02-13 16:06:05.0 load1 src2 tgt1
2008-02-13 16:07:11.506
mynode.intranet.foo.bar.net /dbt/mydir/test1.cf 2008-02-01 17:53:33.0 LOAD1 SRC1 TGT1 2008-02-13 15:31:53.0
mynode.intranet.foo.bar.net /dbt/mydir/test_data_50M-1.cf 2008-02-05 18:05:52.0 LOAD1 SRC1 TGT1 2008-02-13
18:15:10.0
mynode.intranet.foo.bar.net /dbt/mydir/control-files/NBATS/control_BAT006.txt 2008-02-13 16:26:32.0 LOAD1 SRC1
TGT1 2008-02-13 16:29:16.0
mynode.intranet.foo.bar.net /dbt/mydir/control-files/NBATS/control_BAT011.txt 2008-02-13 16:26:32.0 LOAD1 SRC1
TGT1 2008-02-13 16:31:12.0
mynode.intranet.foo.bar.net /dbt/mydir/control-files/NBATS/control_BAT100.txt 2008-02-13 18:13:03.0 LOAD1 SRC1
TGT1 2008-02-13 18:20:15.0
Job Statistics on the Neoview Platform 77