Results 1 to 10 of 10
  1. #1
    Join Date
    May 2003
    Posts
    37

    Unanswered: tablespace size estimation....

    Hi,
    How can I estimate the tablespace size while creating the database?


    Thanks,
    Narender

  2. #2
    Join Date
    Sep 2002
    Posts
    456

    Re: tablespace size estimation....

    Calculate your row size and multiply it by the number of rows you are expecting and add another 5 to 10 % for overhead.

    dollar

    Originally posted by pnarender
    Hi,
    How can I estimate the tablespace size while creating the database?


    Thanks,
    Narender

  3. #3
    Join Date
    May 2003
    Posts
    37

    Re: tablespace size estimation....

    Dollar,
    Thanks once again for the info.

    Narender

    Originally posted by dollar489
    Calculate your row size and multiply it by the number of rows you are expecting and add another 5 to 10 % for overhead.

    dollar

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: tablespace size estimation....

    You can also use , Control Centre .... I use it most of the time to get a rough estimate of how much space I need to allocate to tablespaces when moving from dev to prod ...

    If you right click by highlighting a table, the option is 'Estimate Size'


    HTH

    Sathyaram



    Originally posted by pnarender
    Dollar,
    Thanks once again for the info.

    Narender

  5. #5
    Join Date
    Aug 2003
    Location
    hyderabad
    Posts
    6

    estimating tablesizes and index size in db2

    dear Sathyaram

    I am new to db2dba

    Thanks for the input given. i had been working to get these details for the past week and i am bit confused.

    when Estimating size through control center should i insert record in to the table and do the estimate or run and calculate the size

    if i am to calculate for 1000 records should i insert 10% of data and estimet the size or run the estimate tool whith out any data.

    i inserted the value in "NEW TOTAL NO OF ROWS" as 1000 and the
    "AVG ROW LENGHT" ="NEW AVG ROW LENGHT" (no change in this)

    i shows me that (max)table < sum(max)Total index size


    Will under ant curcumstances table size will be less than Total index size for the table

    if so ? pl exchange me the concepts

    Thanks in adv

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: estimating tablesizes and index size in db2

    The more the number of rows in the table, the better is the estimate because the estimator uses the data in the table to know the average column length ....

    And you know, it is an "estimate" and may not be close to accurate ... Initial estimations are normally far from the real figures ... You'll have to constantly monitor their growth ...

    It is possible to have sum of maximum index sizes to be greater than the max size of the table ... For example, if you have 5 columns in atable and you use all five columns in the index , then obviosly your index will be larger ...

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Aug 2003
    Location
    hyderabad
    Posts
    6
    dear sathyaram_s

    thanks for clearing my doubts .

    As you have mentioned i had not indexed all the columns

    Table structure [ SC67]
    ================

    ctrct_id integer (18) primary key
    cust_f_nm varchar(80)
    cust_m_nm varchar(80)
    cust_l_nm varchar(80)
    maturity_date date
    + another 7 columns

    i had created index on these columns aonly

    CREATE INDEX ID1 ON SC67(CTRCT_ID);
    CREATE INDEX ID1 ON SC67(CUST_F_NM);
    CREATE INDEX ID1 ON SC67(CUST_M_NM);
    CREATE INDEX ID1 ON SC67(CUST_L_NM);

    out of 12 column i had created only for 4 columns .
    if there are varchar/char columns index ,the index space willbe more

    should i run reorg command on table or index before using the toll for estimating size in db2

    thanks in adv

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I'm not sure how db2 control centre estimates ... But, they are just estimates after all and may not be accurate .... So, start with a safe figure, like, if the minimum space is 100 mb, estimated is 125 mb and maximum is 150 mb, then , I'd settle in for about 135-140 mb .... Afer a few weeks, review it again to see how data gets populated ... Re-estimate at that point of time ...


    HTH

    Sathyaram


    Originally posted by banumaran
    dear sathyaram_s

    thanks for clearing my doubts .

    As you have mentioned i had not indexed all the columns

    Table structure [ SC67]
    ================

    ctrct_id integer (18) primary key
    cust_f_nm varchar(80)
    cust_m_nm varchar(80)
    cust_l_nm varchar(80)
    maturity_date date
    + another 7 columns

    i had created index on these columns aonly

    CREATE INDEX ID1 ON SC67(CTRCT_ID);
    CREATE INDEX ID1 ON SC67(CUST_F_NM);
    CREATE INDEX ID1 ON SC67(CUST_M_NM);
    CREATE INDEX ID1 ON SC67(CUST_L_NM);

    out of 12 column i had created only for 4 columns .
    if there are varchar/char columns index ,the index space willbe more

    should i run reorg command on table or index before using the toll for estimating size in db2

    thanks in adv
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Aug 2003
    Location
    hyderabad
    Posts
    6
    Dear Sathyaram


    Thanks for your very valuable advice.

    This has given me confidence to proceed with my tasks.

    Thanks
    Thiru

  10. #10
    Join Date
    May 2003
    Posts
    37

    Re: tablespace size estimation....

    Hi Satyaram,
    I had posted one querry on JDBC URL long back.
    I am not getting any reply from any one.
    So could you please help me in this regard.

    I need a JDBC URL syntax for connecting to informix database server using IPCSHM (Shared Memory) mechanism.


    Thanks,
    Narender

Posting Permissions

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