• Please help support our sponsors by considering their products and services.
    Our sponsors enable us to serve you with this high-speed Internet connection and fast webservers you are currently using at ADSM.ORG.
    They support this free flow of information and knowledge exchange service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions
  • Community Tip: Please Give Thanks to Those Sharing Their Knowledge.

    If you receive helpful answer on this forum, please show thanks to the poster by clicking "LIKE" link for the answer that you found helpful.

  • Community Tip: Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING)

    Click the link above to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This notice will disappear after you have made at least 3 posts.

Report Problem

dicowins

ADSM.ORG Member
#1
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
 

dicowins

ADSM.ORG Member
#3
Add
group by number
To the end of the SQL 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

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

ADSM.ORG Member
#4
>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
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
 

Advertise at ADSM.ORG

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

UpCloud high performance VPS at $5/month

Get started with $25 in credits on Cloud Servers. You must use link below to receive the credit. Use the promo to get upto 5 month of FREE Linux VPS.

The Spectrum Protect TLA (Three-Letter Acronym): ISP or something else?

  • Every product needs a TLA, Let's call it ISP (IBM Spectrum Protect).

    Votes: 17 19.5%
  • Keep using TSM for Spectrum Protect.

    Votes: 53 60.9%
  • Let's be formal and just say Spectrum Protect

    Votes: 10 11.5%
  • Other (please comement)

    Votes: 7 8.0%

Forum statistics

Threads
31,469
Messages
134,128
Members
21,569
Latest member
srinathkodela
Top