Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1

    Unanswered: Size table and database in db2 9.5

    Hi Colleagues,

    Somebody can say me how to know the size the table and database in db2 9.5. if you can send me a example is welcome.

    Thank you for advenced.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Providing that your runstats are current:

    Code:
    with t1 (object_type_code,object_type,schema,object,tabschema,tabname,stats_time,card,pagesize,fpages) as 
    (select 1,'Table',t.tabschema,t.tabname,t.tabschema,t.tabname,t.stats_time,t.card,ts.pagesize,double(t.fpages) from syscat.tables as t inner join syscat.tablespaces as ts on (t.tbspace = ts.tbspace) where t.type in ('T','S','H','U') 
    ) ,
    t2 (object_type_code,object_type,schema,object,tabschema,tabname,stats_time,card,pagesize,fpages) as 
    ( select 2,'Index',i.indschema,i.indname,i.tabschema,i.tabname,i.stats_time,cast(null as bigint),ts.pagesize,double(i****eaf) from syscat.indexes as i inner join syscat.tablespaces as ts on (i.tbspaceid = ts.tbspaceid) 
    ) ,
    t3 (object_type_code,object_type,schema,object,tabschema,tabname,stats_time,card,pagesize,fpages) as 
    ( select * from t1 
      union all 
      select * from t2 
    ),
    t4 (object_type_code,object_type,schema,object,tabschema,tabname,stats_time,card,pagesize,fpages,bytes) as 
    ( select t3.*,case when fpages < 0 then 0 else fpages*pagesize end as bytes from t3 
    ) ,
    t4a (object_type_code,object_type,schema,object,tabschema,tabname,stats_time,card,pagesize,fpages,bytes) as 
    ( select * from t4 
      union all 
      select 3,'Total',tabschema,tabname,tabschema,tabname,cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double),sum(bytes) from t4 
      group by (3,'Total',tabschema,tabname,tabschema,tabname,cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double)) 
      union all 
      select 4,'Schema Total',cast(null as varchar(128)),cast(null as varchar(128)),tabschema,cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double),sum(bytes) from t4 
      group by (4,'Schema Total',cast(null as varchar(128)),cast(null as varchar(128)),tabschema,cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double)) 
      union all 
      select 5,'Database Total',cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double),sum(bytes) from t4 
      group by (5,'Database Total',cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double)) 
      
    )select t4a.*,bytes/1024 as KB,(bytes/1024)/1024 as MB,((bytes/1024)/1024)/1024 as GB from t4a 
    order by tabschema,tabname,object_type_code,object
    Andy

  3. #3
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Thank you AR_Winner,

    You can explain me how to execute this you send me. Please.

    Greethings.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It is a query. Run it like you would any other.

    Andy

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Andy, I'm trying to execute your query and getting:

    SQL0104N An unexpected token "**" was found following "s.pagesize,double(i*".
    Expected tokens may include: "<factor>". SQLSTATE=42601


    What is "double(i****eaf) from syscat.indexes" ?

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It must have been some sort of cut and paste error. it should be: "double(i****eaf)"


    Andy

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It is some sort of posting error/bug. When I put the text in the message it looks fine, but when I post it it makes it look like that. I will try again. It should be: double(i. nleaf)

    I had to put a blank space before nleaf so it would come out properly. Remove it before you run the script.

    Andy

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... double(i. nleaf)
    ... Remove it before you run the script.
    Blanks before or after a period which separates a qualifier and a object name would be allowed in SQL syntax.
    So, removing blanks would be not neccesary.

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes, but the blank makes it looks funny (at least to me).

    Andy

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Thanks, I should have realized it was a posting error.

    How do you get the size of LF/LOB/XML columns? Do you use some table function / admin view? I used the inspect utility in the past.

  11. #11
    Join Date
    Oct 2007
    Posts
    246
    hi
    off topic question.
    is there way to find out how many % temp space is been used by particular application (agentid).

    regds
    Paul

  12. #12
    Join Date
    Jan 2010
    Posts
    335
    Yep,

    check "get snapshot for table.." output. It also lists Temp-Tables and the Agentid of the Owner. Tried to find an example on my databases, but had no luck.

  13. #13
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    You can also use db2pd with -tcb

  14. #14
    Join Date
    Oct 2007
    Posts
    246
    thks nvk and bella,

    bella but db2pd -tcb won't give the agentid details ??

    regds
    Paul

  15. #15
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    SchemaNm column should give you the application handle.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •