Report Problem

D

dicowins

ADSM.ORG Member
Joined
Nov 8, 2002
Messages
44
Reaction score
6
Points
0
Website
Visit site
I used this SQL which generates the following. However ID like to SUM the ELAPTIMES where the Process if the same Number to reduce the Number of Lines. Anybody Help?

>SELECT cast((activity) as char(20)) as "Activity",cast((TO_CHAR(CHAR(start_time),'DD/MM/YY HH24:MI')) as char(14)) as "START_TIME" ,cast((TO_CHAR(CHAR(end_time),'DD/MM/YY HH24:MI')) as char(14)) as "END_TIME", cast((number) as char(7)) as "Process",TRANSLATE('abc:de:fg',DIGITS(end_time-start_time) ,'_______abcdefgh_____',' ') as "ELAPTIME",cast((successful) as char(7)) as "Success" FROM summary WHERE (activity='MIGRATION' OR activity='FULL_DBBACKUP' OR activity='RECLAMATION' OR activity='STGPOOL BACKUP' ) AND start_time> current_timestamp-30 hours and end_time< current_timestamp-8 hours order by START_TIME

Activity START_TIME END_TIME Process ELAPTIME Success
--------------------- --------------- --------------- -------- ---------- --------
RECLAMATION 16/01/19 08:54 16/01/19 09:01 5477 000:07:37 YES
RECLAMATION 16/01/19 09:01 16/01/19 09:07 5477 000:05:51 YES
RECLAMATION 16/01/19 09:07 16/01/19 09:13 5477 000:05:41 YES
FULL_DBBACKUP 16/01/19 09:15 16/01/19 11:11 5493 001:56:21 YES
RECLAMATION 16/01/19 09:54 16/01/19 09:59 5544 000:05:25 YES
RECLAMATION 16/01/19 09:59 16/01/19 10:03 5544 000:04:08 YES
RECLAMATION 16/01/19 10:03 16/01/19 10:08 5544 000:04:57 YES
RECLAMATION 16/01/19 10:54 16/01/19 10:59 5610 000:05:38 YES
RECLAMATION 16/01/19 10:59 16/01/19 11:04 5610 000:04:23 YES
RECLAMATION 16/01/19 11:04 16/01/19 11:07 5610 000:03:34 YES
RECLAMATION 16/01/19 11:54 16/01/19 11:59 5683 000:05:02 YES
RECLAMATION 16/01/19 11:59 16/01/19 12:04 5683 000:04:59 YES
RECLAMATION 16/01/19 12:04 16/01/19 12:08 5683 000:04:29 YES
RECLAMATION 16/01/19 12:08 16/01/19 12:15 5683 000:07:10 YES
RECLAMATION 16/01/19 12:15 16/01/19 12:19 5683 000:03:11 YES
RECLAMATION 16/01/19 12:19 16/01/19 12:22 5683 000:03:20 YES
RECLAMATION 16/01/19 12:22 16/01/19 12:27 5683 000:04:44 YES
RECLAMATION 16/01/19 12:27 16/01/19 12:31 5683 000:04:05 YES
RECLAMATION 16/01/19 12:31 16/01/19 12:36 5683 000:05:02 YES
RECLAMATION 16/01/19 12:36 16/01/19 12:40 5683 000:03:54 YES
RECLAMATION 16/01/19 12:40 16/01/19 12:44 5683 000:04:36 YES
RECLAMATION 16/01/19 12:44 16/01/19 12:48 5683 000:03:51 YES
RECLAMATION 16/01/19 12:48 16/01/19 12:52 5683 000:03:44 YES
RECLAMATION 16/01/19 12:52 16/01/19 12:56 5683 000:04:12 YES
RECLAMATION 16/01/19 12:56 16/01/19 13:00 5683 000:03:29 YES
RECLAMATION 16/01/19 13:00 16/01/19 13:04 5683 000:04:47 YES
RECLAMATION 16/01/19 13:04 16/01/19 13:08 5683 000:03:13 YES
RECLAMATION 16/01/19 13:08 16/01/19 13:09 5683 000:01:33 YES
RECLAMATION 16/01/19 13:09 16/01/19 13:12 5683 000:03:14 YES
RECLAMATION 16/01/19 13:54 16/01/19 13:58 5930 000:04:19 YES
RECLAMATION 16/01/19 13:58 16/01/19 14:02 5930 000:04:03 YES
RECLAMATION 16/01/19 14:02 16/01/19 14:06 5930 000:03:27 YES
RECLAMATION 16/01/19 14:06 16/01/19 14:10 5930 000:04:10 YES
RECLAMATION 16/01/19 14:10 16/01/19 14:13 5930 000:03:24 YES
RECLAMATION 16/01/19 14:13 16/01/19 14:17 5930 000:03:21 YES
RECLAMATION 16/01/19 14:17 16/01/19 14:21 5930 000:04:01 YES
RECLAMATION 16/01/19 14:21 16/01/19 14:25 5930 000:04:51 YES
RECLAMATION 16/01/19 14:25 16/01/19 14:30 5930 000:04:23 YES
RECLAMATION 16/01/19 14:54 16/01/19 14:56 6071 000:02:30 YES
RECLAMATION 16/01/19 14:56 16/01/19 14:59 6071 000:02:50 YES
RECLAMATION 16/01/19 14:59 16/01/19 15:02 6071 000:02:26 YES
RECLAMATION 16/01/19 15:02 16/01/19 15:04 6071 000:02:19 YES
RECLAMATION 16/01/19 15:04 16/01/19 15:07 6071 000:02:36 YES
RECLAMATION 16/01/19 15:07 16/01/19 15:09 6071 000:02:08 YES
RECLAMATION 16/01/19 15:09 16/01/19 15:11 6071 000:01:53 YES
RECLAMATION 16/01/19 15:54 16/01/19 15:56 6218 000:01:55 YES
 
cilesiz said:
Add
group by number
To the end of the SQL statement
Click to expand...

>SELECT cast((activity) as char(20)) as "Activity",cast((TO_CHAR(CHAR(start_time),'DD/MM/YY HH24:MI')) as char(14)) as "START_TIME" ,cast((TO_CHAR(CHAR(end_time),'DD/MM/YY HH24:MI')) as char(14)) as "END_TIME", cast((number) as char(7)) as "Process",TRANSLATE('abc:de:fg',DIGITS(end_time-start_time) ,'_______abcdefgh_____',' ') as "ELAPTIME",cast((successful) as char(7)) as "Success" FROM summary WHERE (activity='MIGRATION' OR activity='FULL_DBBACKUP' OR activity='RECLAMATION' OR activity='STGPOOL BACKUP' ) AND start_time> current_timestamp-30 hours and end_time< current_timestamp-8 hours order by START_TIME group by number

gives me

ANR0162W Supplemental database diagnostic information: -1:42601:-104 ([IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "SELECT cast ( ( activity ) as char ( 20 ) )
" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<from>". SQLSTATE=42601
).
ANR0516E SQL processing for statement SELECT cast ( ( activity ) as char ( 20 ) ) as "Activity" , cast ( ( TO_CHAR ( CHAR ( start_time ) , 'DD/MM/YY HH24:MI' ) ) as char ( 14 ) )
as "START_TIME" , cast ( ( TO_CHAR ( CHAR ( end_time ) , 'DD/MM/YY HH24:MI' ) ) as char ( 14 ) ) as "END_TIME" , cast ( ( number ) as char ( 7 ) ) as "Process" , TRANSLATE (
'abc:de:fg' , DIGITS ( end_time-start_time ) , '_______abcdefgh_____' , ' ' ) as "ELAPTIME" , cast ( ( successful ) as char ( 7 ) ) as "Success" FROM summary WHERE ( activity =
'MIGRATION' OR activity = 'FULL_DBBACKUP' OR activity = 'RECLAMATION' OR activity = 'STGPOOL BACKUP' ) AND start_time > current_timestamp-30 hours and end_time <
current_timestamp-8 hours order by START_TIME group by number failed.
ANS8001I Return code 3.

Ive tried group sets group by argh
 
dicowins said:
>SELECT cast((activity) as char(20)) as "Activity",cast((TO_CHAR(CHAR(start_time),'DD/MM/YY HH24:MI')) as char(14)) as "START_TIME" ,cast((TO_CHAR(CHAR(end_time),'DD/MM/YY HH24:MI')) as char(14)) as "END_TIME", cast((number) as char(7)) as "Process",TRANSLATE('abc:de:fg',DIGITS(end_time-start_time) ,'_______abcdefgh_____',' ') as "ELAPTIME",cast((successful) as char(7)) as "Success" FROM summary WHERE (activity='MIGRATION' OR activity='FULL_DBBACKUP' OR activity='RECLAMATION' OR activity='STGPOOL BACKUP' ) AND start_time> current_timestamp-30 hours and end_time< current_timestamp-8 hours order by START_TIME group by number

gives me

ANR0162W Supplemental database diagnostic information: -1:42601:-104 ([IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "SELECT cast ( ( activity ) as char ( 20 ) )
" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<from>". SQLSTATE=42601
).
ANR0516E SQL processing for statement SELECT cast ( ( activity ) as char ( 20 ) ) as "Activity" , cast ( ( TO_CHAR ( CHAR ( start_time ) , 'DD/MM/YY HH24:MI' ) ) as char ( 14 ) )
as "START_TIME" , cast ( ( TO_CHAR ( CHAR ( end_time ) , 'DD/MM/YY HH24:MI' ) ) as char ( 14 ) ) as "END_TIME" , cast ( ( number ) as char ( 7 ) ) as "Process" , TRANSLATE (
'abc:de:fg' , DIGITS ( end_time-start_time ) , '_______abcdefgh_____' , ' ' ) as "ELAPTIME" , cast ( ( successful ) as char ( 7 ) ) as "Success" FROM summary WHERE ( activity =
'MIGRATION' OR activity = 'FULL_DBBACKUP' OR activity = 'RECLAMATION' OR activity = 'STGPOOL BACKUP' ) AND start_time > current_timestamp-30 hours and end_time <
current_timestamp-8 hours order by START_TIME group by number failed.
ANS8001I Return code 3.

Ive tried group sets group by argh
Click to expand...

changed format of sql and it did

SELECT cast((activity) as char(20)) as "Activity", cast((number) as char(7)) as "Process",cast(sum(end_time-start_time) as int) as "ELAPTIME" FROM summary WHERE (activity='MIGRATION' OR activity='FULL_DBBACKUP' OR activity='RECLAMATION' OR activity='STGPOOL BACKUP' ) AND start_time> current_timestamp-30 hours and end_time< current_timestamp-8 hours group by activity,number

Activity Process ELAPTIME
--------------------- -------- ------------
RECLAMATION 2573 268
FULL_DBBACKUP 2574 4406
FULL_DBBACKUP 2575 22017
RECLAMATION 2576 351
RECLAMATION 2577 600

Thank you
 
You must log in or register to reply here.

Data Privacy Impact Assessment

Compliance Assurance in Every Byte: Discover the Impact of Our DPIA Services.

Sponsor ADSM.ORG

If you are reading this, so are your potential customer. Advertise at ADSM.ORG right now.

Navigation Menu

Forums
   IBM TSM
   EMC Backup and Recovery
   Symantec NetBackup  
   SAN  
   NAS
   Platforms / OS
Mailing List Archives
   ADSM-L
User Groups

NordVPN 3 Months FREE

Help Support ADSM.ORG and Get NordVPN with 64% Off and 3 Months FREE.

NordVPN with 64% off + 3 months FREE

Forum statistics

Threads
31,969
Messages
135,437
Members
21,948
Latest member
svermatsm
Back
Top