在衡量一个数据库的表的大小的时候，除了使用记录条数，使用表的物理大小来说明一个表，也是一种常用的方法，查询一张数据库表的物理大小的方法有以下三种：
1、使用db2pd -d sample -tcbstats
这种方法，要求数据库表被访问过，才能有结果显示
db2pd -d sample -tcbstats
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:20:53
TCB Table Information:
Address TbspaceID TableID PartID MasterTbs MasterTab TableName SchemaNm ObjClass DataSize LfSize LobSize XMLSize
0x00002B92FB68B9D8 0 1 n/a 0 1 SYSBOOT SYSIBM Perm 1 0 0 0
0x00002B92FBB6B358 2 -1 n/a 2 -1 INTERNAL SYSIBM Perm 1 0 0 0
0x00002B92FBBF0ED8 5 -1 n/a 5 -1 INTERNAL SYSIBM Perm 1 0 0 0
0x00002B92FBB597D8 0 5 n/a 0 5 SYSTABLES SYSIBM Perm 29 0 480 0
0x00002B92FBB2C358 8 -1 n/a 8 -1 INTERNAL SYSIBM Perm 1 0 0 0
0x00002B92FBB2CB58 0 6 n/a 0 6 SYSCOLUMNS SYSIBM Perm 160 0 0 0
0x00002B92FBB2B858 0 35 n/a 0 35 SYSTRIGGERS SYSIBM Perm 1 0 32 0
0x00002B92FBB5B8D8 0 113 n/a 0 113 SYSTASKS SYSIBM Perm 1 0 1 0
0x00002B92FB73B5D8 0 -1 n/a 0 -1 INTERNAL SYSIBM Perm 3 0 0 0
0x00002B92FBBECB58 3 -1 n/a 3 -1 INTERNAL SYSIBM Perm 1 0 0 0
0x00002B92FBB1B558 6 -1 n/a 6 -1 INTERNAL SYSIBM Perm 1 0 0 0
0x00002B92FBBEDB58 4 -1 n/a 4 -1 INTERNAL SYSIBM Perm 1 0 0 0
0x00002B92FBB5B0D8 5 4 n/a 5 4 POLICY SYSTOOLS Perm 1 0 32 0
TCB Table Stats:
Address TableName SchemaNm Scans UDI RTSUDI PgReorgs NoChgUpdts Reads FscrUpdates Inserts Updates Deletes OvFlReads OvFlCrtes RowsComp RowsUncomp CCLogReads StoreBytes BytesSaved
0x00002B92FB68B9D8 SYSBOOT SYSIBM 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 - -
0x00002B92FBB6B358 INTERNAL SYSIBM 0 0 0 0 0 45 0 0 0 0 0 0 0 0 0 - -
0x00002B92FBBF0ED8 INTERNAL SYSIBM 0 0 0 0 0 11 0 0 0 0 0 0 0 0 0 - -
0x00002B92FBB597D8 SYSTABLES SYSIBM 4 4 4 0 0 2011 0 0 0 0 5 0 0 0 0 - -
0x00002B92FBB2C358 INTERNAL SYSIBM 0 0 0 0 0 3 0 0 0 0 0 0 0 0 0 - -
0x00002B92FBB2CB58 SYSCOLUMNS SYSIBM 0 96 96 0 0 5 0 0 0 0 0 0 0 0 0 - -
0x00002B92FBB2B858 SYSTRIGGERS SYSIBM 0 0 0 0 0 4 0 0 0 0 0 0 0 0 0 - -
0x00002B92FBB5B8D8 SYSTASKS SYSIBM 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 - -
0x00002B92FB73B5D8 INTERNAL SYSIBM 0 0 0 0 0 399 0 0 0 0 0 0 0 0 0 - -
0x00002B92FBBECB58 INTERNAL SYSIBM 0 0 0 0 0 4 0 0 0 0 0 0 0 0 0 - -
0x00002B92FBB1B558 INTERNAL SYSIBM 0 0 0 0 0 3 0 0 0 0 0 0 0 0 0 - -
0x00002B92FBBEDB58 INTERNAL SYSIBM 0 0 0 0 0 21 0 0 0 0 0 0 0 0 0 - -
0x00002B92FBB5B0D8 POLICY SYSTOOLS 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 - -
在这里，我们发现不了表LEO_BIG_DATA等建立的表，原因在于这些表并没有被访问过；
2、使用sysibmadm.admintabinfo视图进行查看：
$ db2 "select TABNAME,DATA_OBJECT_P_SIZE from sysibmadm.admintabinfo"
TABNAME DATA_OBJECT_P_SIZE
-------------------------------------------------------------------------------------------------------------------------------- --------------------
SYSTABLES 288
SYSCOLUMNS 1312
SYSINDEXES 192
SYSVIEWS 96
SYSVIEWDEP 96
SYSPLAN 224
SYSPLANDEP 224
SYSSECTION 224
SYSSTMT 256
SYSDBAUTH 64
SYSPLANAUTH 64
SYSTABAUTH 96
SYSINDEXAUTH 64
SYSRELS 64
SYSROUTINES 512
SYSROUTINEPARMS 1568
SYSTABCONST 64
SYSKEYCOLUSE 64
SYSCHECKS 64
SYSCOLCHECKS 64
SYSDATATYPES 64
SYSCONSTDEP 64
SYSCOLDIST 2176
SYSEVENTMONITORS 64
SYSEVENTS 64
SYSTABLESPACES 64
SYSDEPENDENCIES 224
SYSTRIGGERS 64
SYSCOLAUTH 64
SYSSCHEMAAUTH 64
SYSSCHEMATA 64
SYSUSERAUTH 96
SYSNODEGROUPDEF 64
SYSNODEGROUPS 64
SYSPARTITIONMAPS 64
SYSBUFFERPOOLS 64
SYSBUFFERPOOLNODES 64
SYSCOLPROPERTIES 64
SYSATTRIBUTES 64
SYSHIERARCHIES 64
SYSTBSPACEAUTH 64
SYSCOLOPTIONS 64
SYSFUNCMAPOPTIONS 64
SYSFUNCMAPPARMOPTIONS 64
SYSFUNCMAPPINGS 64
SYSINDEXEXPLOITRULES 64
SYSINDEXEXTENSIONPARMS 64
SYSINDEXEXTENSIONS 64
SYSINDEXEXTENSIONMETHODS 64
SYSINDEXOPTIONS 64
SYSPASSTHRUAUTH 64
SYSPREDICATESPECS 64
SYSTYPEMAPPINGS 64
SYSSERVEROPTIONS 64
SYSSERVERS 64
SYSTABOPTIONS 64
SYSTRANSFORMS 64
SYSUSEROPTIONS 64
SYSWRAPOPTIONS 64
SYSWRAPPERS 64
SYSJARCONTENTS 64
SYSJAROBJECTS 64
SYSNAMEMAPPINGS 64
SYSSEQUENCES 64
SYSINDEXCOLUSE 96
SYSVERSIONS 64
SYSCOLUSE 64
SYSROUTINEAUTH 192
SYSLIBRARIES 64
SYSLIBRARYBINDFILES 64
SYSLIBRARYVERSIONS 64
SYSCOLGROUPS 64
SYSCOLGROUPSCOLS 64
SYSCOLGROUPDIST 64
SYSCOLGROUPDISTCOUNTS 64
SYSLIBRARYAUTH 64
SYSEVENTTABLES 64
SYSROUTINEPROPERTIES 64
SYSCOMMENTS 64
SYSSEQUENCEAUTH 64
SYSCODEPROPERTIES 64
SYSXMLSTRINGS 64
SYSXMLPATHS 64
SYSXSROBJECTS 64
SYSXSROBJECTCOMPONENTS 64
SYSXSROBJECTAUTH 64
SYSXSROBJECTHIERARCHIES 64
SYSINDEXXMLPATTERNS 64
SYSXDBMAPGRAPHS 64
SYSXDBMAPSHREDTREES 64
SYSDATAPARTITIONS 64
SYSDATAPARTITIONEXPRESSION 64
SYSJOBS 64
SYSTASKS 64
SYSTUNINGINFO 64
SYSSECURITYLABELCOMPONENTS 64
SYSSECURITYLABELCOMPONENTELEMENTS 64
SYSSECURITYPOLICIES 64
SYSSECURITYPOLICYCOMPONENTRULES 64
SYSSECURITYLABELS 64
SYSSECURITYLABELACCESS 64
SYSSECURITYPOLICYEXEMPTIONS 64
SYSSURROGATEAUTHIDS 64
SYSROUTINEOPTIONS 64
SYSROUTINEPARMOPTIONS 64
SYSROLES 64
SYSROLEAUTH 64
SYSCONTEXTS 64
SYSCONTEXTATTRIBUTES 64
SYSCOLLATIONS 64
SYSVARIABLES 64
SYSVARIABLEAUTH 64
SYSWORKLOADS 64
SYSWORKLOADCONNATTR 64
SYSWORKLOADAUTH 64
SYSSERVICECLASSES 64
SYSWORKCLASSSETS 64
SYSWORKCLASSES 64
SYSWORKACTIONSETS 64
SYSWORKACTIONS 64
SYSTHRESHOLDS 64
SYSHISTOGRAMTEMPLATES 64
SYSHISTOGRAMTEMPLATEBINS 64
SYSHISTOGRAMTEMPLATEUSE 64
SYSAUDITUSE 64
SYSAUDITPOLICIES 64
SYSMODULEAUTH 64
SYSMODULES 64
SYSINVALIDOBJECTS 64
SYSINDEXPARTITIONS 64
CL_SCHED 512
DEPARTMENT 512
EMPLOYEE 512
EMP_PHOTO 512
EMP_RESUME 512
PROJECT 512
PROJACT 512
EMPPROJACT 512
ACT 512
IN_TRAY 512
ORG 512
STAFF 512
SALES 512
STAFFG 512
ADEFUSR 512
EMPMDC 3584
PRODUCT 512
INVENTORY 512
CUSTOMER 512
PURCHASEORDER 512
CATALOG 512
SUPPLIERS 512
PRODUCTSUPPLIER 512
POLICY 64
HMON_ATM_INFO 96
HMON_COLLECTION 64
LEO_BIG_DATA 38389760
DMS_TS_AUTO_TEST 256
158 record(s) selected.
可以发现，表的物理大小（单位KB）都被显示出来了，无论其是否被访问过；
3、使用表函数sysproc.admin_get_tab_info('模式','表名')进行表的大小的查看
$ db2 "select * from table(sysproc.admin_get_tab_info('DB2INST1','LEO_BIG_DATA'))"
TABSCHEMA TABNAME TABTYPE DBPARTITIONNUM DATA_PARTITION_ID AVAILABLE DATA_OBJECT_L_SIZE DATA_OBJECT_P_SIZE INDEX_OBJECT_L_SIZE INDEX_OBJECT_P_SIZE LONG_OBJECT_L_SIZE LONG_OBJECT_P_SIZE LOB_OBJECT_L_SIZE LOB_OBJECT_P_SIZE XML_OBJECT_L_SIZE XML_OBJECT_P_SIZE INDEX_TYPE REORG_PENDING INPLACE_REORG_STATUS LOAD_STATUS READ_ACCESS_ONLY NO_LOAD_RESTART NUM_REORG_REC_ALTERS INDEXES_REQUIRE_REBUILD LARGE_RIDS LARGE_SLOTS DICTIONARY_SIZE
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------- -------------- ----------------- --------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ---------- ------------- -------------------- ------------ ---------------- --------------- -------------------- ----------------------- ---------- ----------- --------------------
DB2INST1 LEO_BIG_DATA T 0 0 Y 38389760 38389760 0 0 0 0 0 0 0 0 - N - - N N 0 - Y Y 0
1 record(s) selected.
$
这里需要注意的是，模式名，需要使用大写。
阅读(7792) | 评论(0) | 转发(0) |
