Monday, December 19, 2011

DB2 Doesn't Make A Difference

I've been working with some IBM reps/consultants lately, and I find it kind of funny how they talk about TSM. We were discussing the issue with some queries to the TSM DB being so hard to process that many times they don't return any data, when the IBM rep said "With DB2 that wont happen." I laughed and said, "DB2 didn't help that much." For example try something like this and see how long it takes to get a response.


select  cast(sum(b.file_size/1073741824) as decimal(18,2)) AS GB_SIZE from backups a, contents b where a.node_name in ('DEV01_ORA','DEV02_ORA','DEV03_ORA','PRD01_ORA','PROD02_ORA') and a.backup_date < '2011-11-01 00:00:00' and a.object_id=b.object_id

I'm running this query to determine the amount of space I would free up if I deleted old oracle backup objects that they DBA's never reconciled through RMAN. I ran it over 30 minutes ago.....still waiting! The problem is the schema has not changed enough in the TSM table structure to make some select statements run any better than in pre-DB2 days. Anyone else seen this? 


(Yes! I know if I used a specific NODE_NAME then TSM would probably return some data, but handles queries 1000x times more complex than these in the non-TSM world)

8 comments:

  1. Hi Chad,

    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.

    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.

    select \
    sum(cast(bk.bfsize/1024/1024 as decimal(6,2))) AS MB \
    from \
    backup_objects bk \
    where \
    bk.nodeid in (select nr.nodeid from nodes nr where nr.nodename in ('DEV01_ORA','DEV02_ORA','DEV03_ORA','PRD01_ORA','PROD02_ORA')) and \
    bk.insdate<'2011-11-01 00:00:00'

    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.

    Hope this helps,

    Tom

    ReplyDelete
  2. 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.

    ReplyDelete
  3. Chad,

    TSM seems to translate NODES into NODESVIEW, which has slightly different column names. I'm able to run the following query from the DSMADMC:

    select \
    sum(cast(bk.bfsize/1024/1024 as decimal(16,4))) as MB \
    from \
    backup_objects bk \
    where \
    bk.nodeid in (select nodeid from nodesview where node_name in ('DEV01_ORA','DEV02_ORA','DEV03_ORA','PRD01_ORA','PROD02_ORA')) and \
    bk.insdate<'2011-11-01 00:00:00'

    Does this work for you?

    Thanks,

    Tom

    ReplyDelete
  4. That worked...


    tsm: PRDTSM7>select \
    sum(cast(bk.bfsize/1024/1024 as decimal(16,4))) as MB \
    from \
    cont> sum(cast(bk.bfsize/1024/1024 as decimal(16,4))) as MB \
    cont> from \
    cont> backup_objects bk \
    cont> where \
    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 \
    cont> bk.insdate<'2011-11-01 00:00:00'

    MB
    ------------------
    20532356.0000

    ReplyDelete
  5. Chad,

    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?

    Thanks,

    Tom

    ReplyDelete
  6. It took about 10 minutes. Not quick, but at least it returned a value.

    ReplyDelete
  7. For giggles, what does the following query return?

    select count(*) \
    from backup_objects \
    where nodeid in \
    (select nodeid \
    from nodesview \
    where node_name in \
    ('DEVDOM3_ORA','DMADOM4_ORA','PRDORA3_ORA','SAZGTD_ORA','SA ZS001_ORA','TSTGTD1_ORA'))

    ReplyDelete
  8. the value returned by above command varies with occupancy values , both not matching

    ReplyDelete