tag:blogger.com,1999:blog-13518440.post2483083572648750253..comments2023-12-12T04:39:52.103-08:00Comments on TSMAdmin: DB2 Doesn't Make A DifferenceChad Smallhttp://www.blogger.com/profile/02637281120881655693noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-13518440.post-85332419671396019902016-12-22T04:43:46.733-08:002016-12-22T04:43:46.733-08:00the value returned by above command varies with oc...the value returned by above command varies with occupancy values , both not matching Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-13518440.post-3866980817426892172011-12-21T12:17:48.384-08:002011-12-21T12:17:48.384-08:00For giggles, what does the following query return?...For giggles, what does the following query return?<br /><br />select count(*) \<br />from backup_objects \<br />where nodeid in \<br />(select nodeid \<br />from nodesview \<br />where node_name in \<br />('DEVDOM3_ORA','DMADOM4_ORA','PRDORA3_ORA','SAZGTD_ORA','SA ZS001_ORA','TSTGTD1_ORA'))Tom Kylehttp://lanigera.com/wordpressnoreply@blogger.comtag:blogger.com,1999:blog-13518440.post-44050350867942917592011-12-21T12:07:36.055-08:002011-12-21T12:07:36.055-08:00It took about 10 minutes. Not quick, but at least ...It took about 10 minutes. Not quick, but at least it returned a value.Chad Smallhttps://www.blogger.com/profile/02637281120881655693noreply@blogger.comtag:blogger.com,1999:blog-13518440.post-54595276064460383812011-12-21T10:28:50.472-08:002011-12-21T10:28:50.472-08:00Chad,
Glad you were able to run the query. Is th...Chad,<br /><br />Glad you were able to run the query. Is the number what you were expecting? The CONTENTS view seemed to have several places from which it could pull FILE_SIZE. Also, how long did the query take to run?<br /><br />Thanks,<br /><br />TomTom Kylehttp://wordpress.com/lanigeranoreply@blogger.comtag:blogger.com,1999:blog-13518440.post-509929204116616352011-12-21T10:15:32.831-08:002011-12-21T10:15:32.831-08:00That worked...
tsm: PRDTSM7>select \
sum(cast...That worked...<br /><br /><br />tsm: PRDTSM7>select \<br />sum(cast(bk.bfsize/1024/1024 as decimal(16,4))) as MB \<br />from \<br />cont> sum(cast(bk.bfsize/1024/1024 as decimal(16,4))) as MB \<br />cont> from \<br />cont> backup_objects bk \<br />cont> where \<br />cont> bk.nodeid in (select nodeid from nodesview where node_name in ('DEVDOM3_ORA','DMADOM4_ORA','PRDORA3_ORA','SAZGTD_ORA','SA ZS001_ORA','TSTGTD1_ORA')) and \<br />cont> bk.insdate<'2011-11-01 00:00:00'<br /><br /> MB<br />------------------<br /> 20532356.0000Chad Smallhttps://www.blogger.com/profile/02637281120881655693noreply@blogger.comtag:blogger.com,1999:blog-13518440.post-69822082519844812302011-12-21T09:52:04.858-08:002011-12-21T09:52:04.858-08:00Chad,
TSM seems to translate NODES into NODESVIEW...Chad,<br /><br />TSM seems to translate NODES into NODESVIEW, which has slightly different column names. I'm able to run the following query from the DSMADMC:<br /><br />select \<br /> sum(cast(bk.bfsize/1024/1024 as decimal(16,4))) as MB \<br />from \<br /> backup_objects bk \<br />where \<br /> bk.nodeid in (select nodeid from nodesview where node_name in ('DEV01_ORA','DEV02_ORA','DEV03_ORA','PRD01_ORA','PROD02_ORA')) and \<br /> bk.insdate<'2011-11-01 00:00:00'<br /><br />Does this work for you?<br /><br />Thanks,<br /><br />TomTom Kylehttp://lanigera.com/wordpressnoreply@blogger.comtag:blogger.com,1999:blog-13518440.post-8203891791782589022011-12-21T08:58:33.466-08:002011-12-21T08:58:33.466-08:00The sad thing is that your query will only work di...The sad thing is that your query will only work directly on the DB2 database which Tivoli really doesn't want you touching. So the typical admin is going to work only with the views and not the complete DB. Remember the TSM group has always hidden tables from the public. Even a SELECT TABNAME FROM TABLES on a 6.2.3 server does not show the backup_objects table. Why they haven't opened it up more is frustrating.Chad Smallhttps://www.blogger.com/profile/02637281120881655693noreply@blogger.comtag:blogger.com,1999:blog-13518440.post-43543088953271605912011-12-20T23:30:43.118-08:002011-12-20T23:30:43.118-08:00Hi Chad,
BACKUPS and CONTENTS are defined as view...Hi Chad,<br /><br />BACKUPS and CONTENTS are defined as views, at least in the 6.2 instance I checked. You can view the DDL using the db2look utility (too long to post here) - there's a lot going on for your seemingly simple query. The CONTENTS view, in particular, is quite complex. and touches a lot of tables and columns that you probably don't need.<br /><br />I'm no SQL expert by any means, but one thing I have learned is that if a query runs slowly, it's usually the query and not the database. So, you may be able to streamline your query by using the underlying tables, rather than the views. Here's a (very) rough query, it seems to run quickly but I'm not 100% sure I believe it. Could be a good jumping off point, or maybe we'll be lucky and someone will clue us in :-D.<br /><br />select \<br />sum(cast(bk.bfsize/1024/1024 as decimal(6,2))) AS MB \<br />from \<br />backup_objects bk \<br />where \<br /> bk.nodeid in (select nr.nodeid from nodes nr where nr.nodename in ('DEV01_ORA','DEV02_ORA','DEV03_ORA','PRD01_ORA','PROD02_ORA')) and \<br /> bk.insdate<'2011-11-01 00:00:00'<br /><br />On a semi-related note, I found that the rounding when dividing by 1073741824 (1024^3) was...wrong. It seems to be off by a decimal place (I was getting 2GB instead of 21GB). Perhaps I am simply doing this way too late at night and misreading this, but the numbers were correct when only converting to MB.<br /><br />Hope this helps,<br /><br />TomTom Kylehttp://lanigera.com/wordpressnoreply@blogger.com