Get size of all columns on a DB2 table

Question

I have been asked to determine how much data our application uses and how fast it is growing. The problem is many applications share the same database and tables with a column being used to determine which application the data belongs to. It is a DB2 database.

Is there any way to find the size in bytes of all the columns a table uses for a given row? It is important that I select only those rows that belong to my application.

If a column is not nullable I do not include it in the SQL I just multiply its size by the row count. I am primarily trying to determine the average size of nullable and variable size columns (we use VARCHAR and BLOB).

At the moment what I am doing looks something like this:

SELECT VALUE(LENGTH(COLUMN_1), 0) AS LEN_COL_1, repeat for each variable size column
FROM TABLE T
WHERE T.APP_ID = my app

Had to ask - the answer was "If you can include indexes that would be great, but it is not essential". — BigMac66

Gilbert Le Blanc Gilbert Le Blanc · Accepted Answer · 2013-02-13T13:48:16

The most accurate way to determine size would be to look at the sizes of the files that make up the DB2 tables.

Divide the file sizes by the percentage of rows that belong to your application.

This way, you count most of DB2's overhead size, including indexes.

Get size of all columns on a DB2 table

1 Answers