Rozmiar tabel w DB2

Aby sprawdzić rozmiar tabel w naszej bazie wykonujemy polecenie:

db2> SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,SUBSTR(TABNAME,1,30) TABNAME,(DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE) AS TOTAL_SIZE_IN_KB FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA NOT LIKE 'SYS%' ORDER BY TOTAL_SIZE_IN_KB DESC

TABSCHEMA          TABNAME                        TOTAL_SIZE_IN_KB    
------------------ ------------------------------ --------------------
BPMADMIN           CONTENT                                   427216896
BPMADMIN           LSW_TASK_EXECUTION_CONTEXT                111915008
BPMADMIN           BPM_SHARED_OBJECT_INSTANCE                 89714688
BPMADMIN           LSW_STORED_SYMBOL_TABLE                    86072320
...
BPMADMIN           SITESETTINGS                                   4096
BPMADMIN           SIDTOGUID                                      4096
BPMADMIN           CONVERSIONSETTINGS                             4096
BPMADMIN           LSW_CA_TEMPLATE_DATA                           2048

  342 record(s) selected.

Można też sprawdzić rozmiar z rozbiciu na poszczególne elementy tabeli (dane, indeksy itd)

db2 => SELECT TABSCHEMA, TABNAME, SUM(DATA_OBJECT_P_SIZE) as DATA_SIZE, SUM(INDEX_OBJECT_P_SIZE) as INDEX_SIZE, SUM(LONG_OBJECT_P_SIZE) as LONG_SIZE, SUM(LOB_OBJECT_P_SIZE) as LOB_SIZE, SUM(XML_OBJECT_P_SIZE) as XML_SIZE FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA like 'BPMADMIN' GROUP BY TABSCHEMA, TABNAME

TABSCHEMA                                                                                                                        TABNAME                                                                                                                          DATA_SIZE            INDEX_SIZE           LONG_SIZE            LOB_SIZE             XML_SIZE            
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- -------------------- --------------------
BPMADMIN                                                                                                                         LSW_COLUMN                                                                                                                                       2048                 2048                    0                    0                    0
BPMADMIN                                                                                                                         LSW_DATA_TRANSFER_ERRORS                                                                                                                         2048                 2048                    0                 4096                    0
BPMADMIN                                                                                                                         LSW_LOAD_TRACE                                                                                                                                   2048                 2048                    0                    0                    0
...
BPMADMIN                                                                                                                         TG_PROCESSFLOWS                                                                                                                                  2048                 2048                    0                    0                    0
BPMADMIN                                                                                                                         TG_SLASTATUS                                                                                                                                     2048                 2048                    0                    0                    0
BPMADMIN                                                                                                                         TG_SLATHRESHOLDTRAVERSALS                                                                                                                        2048                 2048                    0                    0                    0

  47 record(s) selected.
Ten wpis został opublikowany w kategorii DB2 i oznaczony tagami . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz