TSM 5.5 --> 6.3 running process script

[LanSchroeder]

Hello everyone, I recently upgraded our main backup server from 5.5 to 6.3.4 because IBM said it was a good idea if you like support. Everything is running ok but a script I used quite freqently does not work with the new DB2 database. Is there any scripting gurus out there that can help me get my query to work in TSM v6? here is my 5.5 script...

select process,current_timestamp-start_time as "Elapsed Time",cast(bytes_processed*.000000001 as decimal(18,3)) as "GBytes Processed",(bytes_processed*.0000036)/(cast((current_timestamp-start_time)seconds as decimal(18,10))) as "GB / Hour",status from processes

It is a pretty basic query to show me the current active processes, with elapsed time, and data moved total and average...

example output...


PROCESS : Space Reclamation
Elapsed Time : 0 00:36:21.000000
GBytes Processed : 65.337
GB / Hour : 107.847
STATUS : Volume 000941L4 (storage pool LTOPOOL1), Moved Files: 91985, Moved Bytes: 65,822,875,174, Unreadable Files: 0, Unreadable Bytes: 0. Current Physical File (bytes): 51,245,819 Current input volume: 000941L4. Current output volume: 000083L4.

Any help is appreciated! Thanks!

 

[rgg]

Hi,

It looks like the select itself runs, but the problem is that the bytes_processed column doesn't really seem to report anything for reclamation.

tsm: SERVER>select * from processes

PROCESS_NUM: 17
PROCESS: Space Reclamation
START_TIME: 2014-04-29 09:18:49.000000
FILES_PROCESSED: 0
BYTES_PROCESSED: 0
STATUS: Volume M00044L3 (storage pool VTLTAPE), Moved Files: 3523,
Moved Bytes: 436,435,931, Unreadable Files: 0, Unreadable
Bytes: 0. Current Physical File (bytes): None Current input
volume: M00044L3. Current output volume(s): M00043L3.

I don't see any easy way to scrape the bytes moved in the STATUS column to be usable. Maybe forget the GB's processed and just print out the status column in its entirety?

 

[LanSchroeder]

Im getting closer to getting it... so far i have this.

select process,TO_CHAR(start_time,'YYYY-MM-DD HH24:MI:SS') AS START_TIME, TRANSLATE('a bc:de:fg', DIGITS(current_timestamp - start_time), '_______abcdefgh_____',' ') AS "ELAPTIME (D HHMMSS)",cast(bytes_processed/1024/1024/1024 as decimal(18,3)) as "GBytes Processed",(bytes_processed*0.0000036)/(cast((current_timestamp-start_time)seconds as decimal(18,10))) as "GB / Hour",status from processes

The gigabytes per hour field is still a bit off however...

PROCESS : Migration
START_TIME : 2014-04-29 11:24:24
ELAPTIME (D HHMMSS) : 0 00:59:56
GBytes Processed : 169.852
GB / Hour : 11724
STATUS : Disk Storage Pool DISKPOOL, Moved Files: 812, Moved Bytes: 182,377,832,448... omitting the rest....

 

[LanSchroeder]

I worked with our DBA who is amazing at whatever DBAs do... and we hammered it out correctly...
Turns out what i had was only calculating the seconds field in both variables... not the whole time. the correct syntax is as follows...

select process,TO_CHAR(start_time,'YYYY-MM-DD HH24:MI:SS') AS START_TIME, TRANSLATE('a bc:de:fg', DIGITS(current_timestamp - start_time), '_______abcdefgh_____',' ') AS "ELAPTIME (D HHMMSS)",cast(bytes_processed/1024/1024/1024 as decimal(18,3)) as "GBytes Processed",(bytes_processed*0.0000036)/cast(timestampdiff (2, char(timestamp(current_timestamp)-timestamp(start_time))) as decimal(18,10)) as "GB / Hour",status from processes

example output...

PROCESS : Migration
START_TIME : 2014-04-29 11:24:24
ELAPTIME (D HHMMSS) : 0 03:58:49
GBytes Processed : 656.346
GB / Hour : 177
STATUS : Disk Storage Pool DISKPOOL, Moved Files: 2091, Moved Bytes: 704,747,057,152,..... omitted the rest.

I'm happy! Hopefully others can use this now as well! I find it a helpful script!