data block size 2

[rrrkrishnan]

A couple of questions.

What are the factors that we need to consider to set the data block size for a table or is default preferred.
Where in the data dictionary can I find the size of the data block as specified for each table.

Any response is much appreciated.

Thanks!!!

 

[tdatgod]

hi,
The default Datablock size is set by the DBSCONTROL program.

display file

one of the Parameters is Perm DB size and defaults to 127 Sectors. ( 63.5 K ) For all tables which do not Have a specific datablock size set this is the value that will be used.

If this value is changed it will effect every table on the system that doesn't have a Specific Datablock table level attribute.

You can set the datablock size for a particular table when you create it or you can alter the table later.

you can do

help 'SQL CREATE TABLE' ;
help 'SQL ALTER TABLE' ;

for more information.

 

[tdatgod]

Hi,
I forgot to tell you the real answer to your question.

Show Table

will display the datablock size if it is set for they table.

If you don't see the datablock size in the display then that table will then use the SYSTEM DEFAULT from DBSCONTROL.

 

[rrrkrishnan]

Thanks tdatgod.
Can you please let me know where I can find some info. about the factors that we need to consider to set the data block size for a table.

 

[dnoeth]

If you have lots of full table scans:
use larger blocks (up to 255 sectors since V2R4.1, but it still defaults to 127) -> less IOs
If you have lots of PI accesses with a small number of rows:
use smaller blocks -> less overhead accessing those rows

For some large tables you could decide for each table which blocksize to use. But most tables are ok using the default, so don't modify the system default.

Btw, you won't find blocksize within the system tables, it's only stored within the table header (only Show Table returns it)

Dieter