DB2 统计表所有列长度之和

 

describe select * from syscat.column

select sun(length) as total_length 
from syscat.column 
where tabschema ='schemaname' and tabname ='tabname'

 

This statement can be used for caculate a tough table space occupied.

-----------------------------------------------------------------------------------------------------

About : SYSCAT

The database manager creates and maintains two sets of system catalog views that are defined on top of the base system catalog tables:SYSCAT and SYSSTAT

 

• SYSCAT views are read-only catalog views that are found in the SYSCAT schema. SELECT privilege on these views is granted to PUBLIC by default.
• SYSSTAT views are updatable catalog views that are found in the SYSSTAT schema. The updatable views contain statistical information that is used by the optimizer. The values in some columns in these views can be changed to test performance. (Before changing any statistics, it is recommended that the RUNSTATS command be invoked so that all the statistics reflect the current state.) Applications should be written to the SYSSTAT views rather than the base catalog tables.

 

All the system catalog views are created at database creation time. The catalog views cannot be explicitly created or dropped. The views are updated during normal operation in response to SQL data definition statements, environment routines, and certain utilities. Data in the system catalog views is available through normal SQL query facilities. The system catalog views (with the exception of some updatable catalog views) cannot be modified using normal SQL data manipulation statements.

An object (table, column, function, or index) will appear in a user's updatable catalog view only if that user created the object, holds CONTROL privilege on the object, or holds explicit DBADM authority.

The order of columns in the views may change from release to release. To prevent this from affecting programming logic, specify the columns in a select list explicitly, and avoid using SELECT *. Columns have consistent names based on the types of objects that they describe.

Described Object

Column Names

Table

TABSCHEMA, TABNAME

Index

INDSCHEMA, INDNAME

View

VIEWSCHEMA, VIEWNAME

Constraint

CONSTSCHEMA, CONSTNAME

Trigger

TRIGSCHEMA, TRIGNAME

Package

PKGSCHEMA, PKGNAME

Type

TYPESCHEMA, TYPENAME, TYPEID

Function

ROUTINESCHEMA, ROUTINENAME, ROUTINEID

Method

ROUTINESCHEMA, ROUTINENAME, ROUTINEID

Procedure

ROUTINESCHEMA, ROUTINENAME, ROUTINEID

Column

COLNAME

Schema

SCHEMANAME

Table Space

TBSPACE

Database partition group

NGNAME

Buffer pool

BPNAME

Event Monitor

EVMONNAME

Creation Timestamp

CREATE_TIME