DB2 sql average row lenght in bytes

Question

I am new to DB2.

I am trying to determine the average row length in bytes for a number of tables (TABLE_1, TABLE_2, TABLE_3, TABLE_4 and TABLE_5). I need to do some analysis on the size of the tables and then to extrapolate the information I get.

I have a script which shows me the number of rows for a given table:

SELECT (data_object_p_size + index_object_p_size + long_object_p_size + 
   lob_object_p_size + xml_object_p_size) as total_p_size 
   FROM TABLE( SYSPROC.ADMIN_GET_TAB_INFO_V97( 'SCHEMA', 'TABLE_1' )) AS T;

But I can not find anywhere information to get the average row length. I also have access to IBM Data Studio.

That query does not show you the number of rows in the table, it shows you the number of bytes allocated to the table. Note that this total will include empty space in the table, so it's not necessarily useful for determining average row size (if you were to take this `total_p_size` and divide by the number of rows in the table). — Ian Bjorhovde, Jul 23 '13 at 19:50

score 2 · Accepted Answer · answered Jul 23 '13 at 18:02

2

After collecting the table statistics (e.g. using the RUNSTATS command) you can

select avgrowsize from syscat.tables
 where tabschema = 'YOURSCHEMA' and tabname = 'YOURTABLE'

answered Jul 23 '13 at 18:02

mustaccio

18,234
16
48
57

DB2 sql average row lenght in bytes

1 Answers1