check http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html and the db2 function timestampdiff()
Op 3 okt. 2013, om 14:31 heeft Richard Rhodes <[email protected]> het volgende geschreven: > A puzzle for a true db2/sql type person who would like to tackle a v5 to > v6 sql problem . . . > > This does not work on v6. I've played with it for quite some time and > can't come up with a v6 version. > > This is an attempt to identify slow backups that may need attention. > These are backup sessions that: > run in the past 24 hr (1441 min) > kb/s < 500 (less than 1/2 mb/s throughput) > backup sessions took > 2.5hr (150 min)(slow sessions that aren't real > long are ok) > > > dsmadmc -se=$tsm -id=$adminid -password=$adminpwd -noc <<EOD > select entity, - > cast(bytes/1024/1024 as decimal(9,0)), - > activity, - > cast((end_time - start_time)hours as decimal(12,1)), - > cast(cast(bytes/1024 as decimal(15,3)) / cast((end_time - > start_time)second as decimal(15,3)) as decimal(15,1)), - > cast(nodes.client_version as char) - > || '.' - > || cast(nodes.client_release as char) - > || '.' - > || cast(nodes.client_level as char) - > || '.' - > || cast(nodes.client_sublevel as char(2)) - > from summary, nodes - > where summary.entity = nodes.node_name - > and activity = 'BACKUP' - > and cast((current_timestamp - end_time)minute as decimal(5,0)) < > 1441 - > and cast(cast(bytes/1024 as decimal(15,3)) / cast((end_time - > start_time)second as decimal(15,3)) as decimal(15,1)) < 500 - > and cast((end_time - start_time)minute as decimal(12,1)) \> 150 - > order by 1 > EOD > > > The V5 report output looks like this: > >> slow backups: duration > 2.5hr and throughput < 500k/s > >> server mb activity duration-hr throughput-k/s > >> xxx1 0 BACKUP 2.0 0.0 6.3.0.0 >> yyy2 19461 BACKUP 66.0 83.6 6.2.4.0 >> zzz3 1389 BACKUP 2.0 51.6 6.2.4.0 >> www4 2216 BACKUP 6.0 98.1 6.2.4.0 > > > ----------------------------------------- > The information contained in this message is intended only for the > personal and confidential use of the recipient(s) named above. If > the reader of this message is not the intended recipient or an > agent responsible for delivering it to the intended recipient, you > are hereby notified that you have received this document in error > and that any review, dissemination, distribution, or copying of > this message is strictly prohibited. If you have received this > communication in error, please notify us immediately, and delete > the original message. -- Met vriendelijke groeten/Kind Regards, Remco Post [email protected] +31 6 248 21 622
