Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Space calculation for table creation.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vinaya kumar

New User


Joined: 06 May 2009
Posts: 4
Location: Bangalore

PostPosted: Wed May 06, 2009 6:45 pm    Post subject: Space calculation for table creation.
Reply with quote

hi,

I am not much aware of how space is calculated during table space and table creation. I see below DDL format in our DB

CREATE TABLESPACE TSDEBPR IN CAPS002
NUMPARTS 7
(PART 1 USING STOGROUP CAPSG10
PRIQTY 21600
SECQTY 3600
ERASE NO
FREEPAGE 0
PCTFREE 10
TRACKMOD YES
COMPRESS NO
,PART 2 USING STOGROUP CAPSG10
PRIQTY 48
SECQTY 96
ERASE NO
FREEPAGE 0
PCTFREE 10
TRACKMOD YES
COMPRESS NO
and so on

now if I want to create a table space where table having a Lrecl of 120 and 7 000 000 records can be inserted and it should handle such amount of data. how should I calculate the value for primary and secondary quantity.

secondly when and where Partitioned TS should be defined, how to make the decision and how to set the space value for the same?

Please suggest.
Back to top
View user's profile Send private message

Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Wed May 06, 2009 10:29 pm    Post subject: Reply to: Space calculation for table creation.
Reply with quote

Hi vinaya kumar

Quote:
now if I want to create a table space where table having a Lrecl of 120


What is Lrecl in table and how do you calculated this to 120?

Regards
Raghu
Back to top
View user's profile Send private message
vinaya kumar

New User


Joined: 06 May 2009
Posts: 4
Location: Bangalore

PostPosted: Thu May 07, 2009 11:31 am    Post subject:
Reply with quote

Raghu,

I calculate based on column length. My concern is how to calculate space now for creating TS for 7000000 record capacity.

Please suggest.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu May 07, 2009 1:44 pm    Post subject:
Reply with quote

Hi vinay,

Row Length = 120
Quantity = 7000000

I calculated for a segmented tablespace.
For partition tablespace, you must calculate for each partitions

Code:
PRIQTY  SECQTY  ERASE  FRPAGE  %FR  COMP  TRKMOD
1245216 124560  NO     3       10   NO    YES


Now, how did i do this ?
I have an in-house tool called SPACE CALCULATOR.

Get familiar with the tools used in your shop.

Thank You,
Sushanth
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu May 07, 2009 3:40 pm    Post subject:
Reply with quote

You need to compute how many rows will fit on each page. The "Administration Guide - Planning" has detailed information about how to do this. See the section "Space requirements for user table data" for detailed information.

A quick estimate for a 4k page size is: (1+freespace percentage)*num rows/(int(4028/(rowsize+10)))

Don't forget to plan for space for indexes.

Rows with varchar data in them will, as a general rule, take up less space than the maximum. This will (usually) allow more rows per page than the basic calculation indicates. Use the average length of the varchar column when computing row size.

At the other end of the scale, if the row size is very small, then you can hit the limit of maximum rows on a page.

There are also some other considerations that come into play when the row size is a large percentage of the page size. These relate the data manager's ability to find space for a new row to be inserted, getting in contact with your site-DBA might help further.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu May 07, 2009 3:47 pm    Post subject:
Reply with quote

sushanth bobby wrote:
Get familiar with the tools used in your shop.
Not necessarily every shop has such a tool (SPACE CALCULATOR) . . . icon_smile.gif and who knows what actually happens in the back-end . . .
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu May 07, 2009 4:32 pm    Post subject:
Reply with quote

Anuj,

Since this might be a usual scenario when a new tablespace is created. Every shop will be having their own strategy to deal with it.

Thank You,
Sushanth
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts The TS7720 is out of cache space. Rex Montemayor All Other Mainframe Topics 2 Mon Sep 25, 2017 7:18 am
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us