Администрирование DB2 на языке SQL

Для получения информации о состоянии бд, блокировок, операторов SQL и т. д. Используются административные виды (схема SYSIBMADM), и табличные функции.
SQL запрос для получения снимка выглядит следующим образом:

SELECT * from TABLE(ИмяФункции (ИмяБД, раздел)) as T;

Вместо ИмяБД можно указать NULL, если привести его к соответствующему типу

CAST (NULL AS VARCHAR(128)

Параметр Раздел:

-1 --- возврат информации о текущем разделе

-2 --- для всех разделов

NULL --- текущий раздел (по умолчанию)

Если функция начинается со слова
SNAP - это снимок
HEALTH - это индикаторы работоспособности

Информационные

Информация о системе (ОС и ее версия, сколько CPU, сколько оперативной памяти)

SELECT * from SYSIBMADM.ENV_SYS_INFO;

Информация о DB2 (версия db2, номер патча ...)

SELECT * FROM SYSIBMADM.ENV_INST_INFO

Переменные окружения

SELECT * from SYSIBMADM.REG_VARIABLES

Посмотреть конфигурацию экземпляра

SELECT * FROM SYSIBMADM.DBMCFG

Конфигурацию базы

SELECT * FROM SYSIBMADM.DBCFG

Размер базы данных

db2 CALL GET_DBSIZE_INFO(?, ?, ?, 0);

Снимок базы данных SNAP_GET_DB_V91

атрибуты

DB_NAME — имя базы данных

DB_STATUS – статус

LAST_BACKUP — время последнего резервного копирования

И последняя (пока) версия снимка SNAP_GET_DB_V97, принцип работы тот же, информации еще больше

Получить время последнего резервного копирования

SELECT

SUBSTR(DB_NAME, 1, 20) AS DB_NAME – имя БД

, DB_STATUS – статус

, LAST_BACKUP — время последнего резервного копирования

FROM TABLE(SNAP_GET_DB_V91(CAST (NULL AS VARCHAR(128)), -2)) AS T; // для всех бд

SELECT SUBSTR(DB_NAME, 1, 20) AS DB_NAME, DB_STATUS, LAST_BACKUP

FROM TABLE(SNAP_GET_DB_V91('ИмяБД', -2)) AS T; // для конкретной БД

Соединения

CONNECTIONS_TOP - максимальное количество

TOTAL_CONS — всего с момента активации БД

Блокировки

LOCKS_HELD — блокировок удерживается

LOCK_WAITS — ожидание блокировок

LOCK_WAIT_TIME — время ожидания

LOCK_LIST_IN_USE — итого

DEADLOCKS — обнаружено мертвых блокировок

LOCK_ESCALS — эскалация блокировок

X_LOCK_ESCALS — эксклюзивных блокировок

LOCKS_WAITING — текущий агент ожидает блокировки

LOCK_TIMEOUTS — таймауты блокировок

NUM_INDOUBT_TRANS — входящих транзакций

Пример

SELECT SUBSTR(DB_NAME, 1, 20) AS DB_NAME
, CONNECTIONS_TOP, TOTAL_CONS -- соединений
, LOCKS_HELD, LOCK_WAITS, LOCK_WAIT_TIME -- блокировок
, NUM_INDOUBT_TRANS -- входящих транзакций
FROM TABLE(SNAP_GET_DB_V91(CAST (NULL AS VARCHAR(128)), -2)) AS T;

Более подробную информацию о блокировках можно получить с помощью административных видов

LOCKS_HELD — удерживаемые блокировки

LOCKWAITS — ожидание блокировки

Для динамических запросов, для которых таблица <> nulls

select count(*),
  h.TBSP_NAME -- табличное пространство

  , h.TABSCHEMA -- схема

  , h.TABNAME -- таблица

  , h.LOCK_OBJECT_TYPE -- тип блокировки (строка, таблица ...)
  , I.CLIENT_NNAME -- имя хоста клиента
from
SYSIBMADM.LOCKS_HELD h
,table(SNAP_GET_APPL_INFO('', -2)) AS I
where h.agent_id = i.agent_id
group by h.TBSP_NAME, h.TABSCHEMA, h.TABNAME, h.LOCK_OBJECT_TYPE
, I.CLIENT_NNAME;

Кто удерживает блокировки

select
I.CLIENT_PID
, I.APPL_NAME
, I.APPL_ID
, I.PRIMARY_AUTH_ID
, I.CLIENT_NNAME -- host name
, I.CORR_TOKEN
, h.*
from
SYSIBMADM.LOCKS_HELD h
,table(SNAP_GET_APPL_INFO('', -2)) AS I
where h.agent_id = i.agent_id;

Про буферные пулы
Буферные пулы и их размеры (даже если они стоят в автомате, аналог db2mtrk -i -v -d)
SELECT * FROM SYSIBMADM.SNAPDB_MEMORY_POOL 

SELECT bp.BPNAME, bpm.POOL_SECONDARY_ID, bpm.POOL_CUR_SIZE, bp.PAGESIZE, bpm.POOL_CUR_SIZE/bp.PAGESIZE 

FROM SYSIBMADM.SNAPDB_MEMORY_POOL BPM, syscat.bufferpools bp

where BPM.POOL_ID = 'BP'

and bpm.POOL_SECONDARY_ID = char(bp.BUFFERPOOLID)

union

select '--all--', '', sum(bpm1.POOL_CUR_SIZE), 0, 0

from SYSIBMADM.SNAPDB_MEMORY_POOL BPM1

SELECT

 SUBSTR(DB_NAME, 1, 20) AS DB_NAME -- имя БД

  , DB_STATUS -- статус

  ,POOL_DATA_L_READS -- логическое чтение из буферного пула

  ,POOL_DATA_P_READS -- физическое чтение из буферного пула

  ,POOL_READ_TIME -- время чтения (физическое)

  ,POOL_WRITE_TIME -- время записи (физическое)

FROM TABLE(SNAP_GET_DB_V91('ИмяБД', -2)) AS T;

Для того, чтобы следить за тем правильно ли настроены буферные пулы можно использовать административный вид BP_HITRATIO. HITRATIO. (HITRATIO - коэффициент попадания в пул буферов)
SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, SUBSTR(BP_NAME,1,14) AS BP_NAME,
TOTAL_HIT_RATIO_PERCENT -- итого

, DATA_HIT_RATIO_PERCENT -- для данных

, INDEX_HIT_RATIO_PERCENT -- для индексов

, XDA_HIT_RATIO_PERCENT

, DBPARTITIONNUM
FROM SYSIBMADM.BP_HITRATIO ORDER BY DBPARTITIONNUM;

Сборная инфа по буферным пулам
SELECT mp.DB_NAME, mp.POOL_ID, mp.POOL_CUR_SIZE, mp.POOL_CONFIG_SIZE
, bp.BPNAME, bp.PAGESIZE
, bp.NPAGES ---  Если буферные пулы настроены на автоматик, то  <0
, (mp.POOL_CUR_SIZE/bp.PAGESIZE) as bppages
, hit.TOTAL_LOGICAL_READS, hit.TOTAL_PHYSICAL_READS, hit.TOTAL_HIT_RATIO_PERCENT
, hit.DATA_LOGICAL_READS, hit.DATA_PHYSICAL_READS
, hit.INDEX_LOGICAL_READS, hit.INDEX_PHYSICAL_READS
FROM SYSIBMADM.SNAPDB_MEMORY_POOL mp
left outer join
syscat.bufferpools as bp on (mp.POOL_ID='BP' and char(bp.BUFFERPOOLID)=char(mp.POOL_SECONDARY_ID))
left outer join
SYSIBMADM.BP_HITRATIO as hit on (trim(bp.BPNAME)=trim(hit.bp_name))
 --- where mp.POOL_ID='BP'
;
Итого по кучкам  POOL_ID 
SELECT mp.DB_NAME, mp.POOL_ID, sum(mp.POOL_CUR_SIZE)
FROM SYSIBMADM.SNAPDB_MEMORY_POOL mp
left outer join
syscat.bufferpools as bp on (mp.POOL_ID='BP' and char(bp.BUFFERPOOLID)=char(mp.POOL_SECONDARY_ID))
left outer join
SYSIBMADM.BP_HITRATIO as hit on (trim(bp.BPNAME)=trim(hit.bp_name))
group by mp.DB_NAME, mp.POOL_ID
;
Итого по базе
SELECT sum(mp.POOL_CUR_SIZE)
FROM SYSIBMADM.SNAPDB_MEMORY_POOL mp
left outer join
syscat.bufferpools as bp on (mp.POOL_ID='BP' and char(bp.BUFFERPOOLID)=char(mp.POOL_SECONDARY_ID))
left outer join
SYSIBMADM.BP_HITRATIO as hit on (trim(bp.BPNAME)=trim(hit.bp_name))
;

Контейнеры
SELECT SNAPSHOT_TIMESTAMP, TBSP_NAME, TBSP_ID, CONTAINER_NAME,
CONTAINER_ID, CONTAINER_TYPE, ACCESSIBLE
FROM TABLE(SNAP_GET_CONTAINER_V91('',-1)) AS T

Табличные пространства, имена, состояния
select * from SYSIBMADM.SNAPTBSP_PART 

Транзакции

SELECT SUBSTR(DB_NAME, 1, 20) AS DB_NAME, DB_STATUS -- имя БД и ее статус

, COMMIT_SQL_STMTS -- сколько было завершенных транзакций

, ROLLBACK_SQL_STMTS -- сколько завершилось откатом

, DYNAMIC_SQL_STMTS -- динамических запросов

, STATIC_SQL_STMTS -- статических запросов

, FAILED_SQL_STMTS -- завершилось обломом

, SELECT_SQL_STMTS -- запросы на выборку

, UID_SQL_STMTS -- запросы на модификацию (update, insert, delete)

FROM TABLE(SNAP_GET_DB_V91('БАЗА', -2)) AS T;

Получаем тело (SQL) динамических запросов.

SNAPDYN_SQL — административный вид

SNAP_GET_STMT - административный вид

SNAP_GET_DYN_SQL_V91 — табличная функция, параметры ('ИмяБазы', -2), аналог SNAP_GET_STMT, но позволяет получить данные для определенной бд и ее раздела

SNAPSTMT — административный вид, снимок оператора

Мониторинг базы, начиная с версии 9.7 LUW

db2 "CALL MONREPORT.DBSUMMARY(60)" >>/tmp/monreport.txt

Собирает основные индикаторы производительности

Part 1 - System performance

 Row processing
    ROWS_READ/ROWS_RETURNED        
    ROWS_MODIFIED                   

TOTAL_WAIT_TIME (I/O (POOLS), NETWORK, LOCK WAIT TIME )

Part 2 - Application performance drill down

Part 3 - Member level information

Общая сборная информация.

Кто влез в мою базу и удалил запись из таблицы

SELECT SUBSTR(STMT.STMT_TEXT,1,30) AS STMT_TEXT -- sql
, stmt.snapshot_timestamp -- time
, STMT.agent_id
, STMT.ROWS_READ, STMT.ROWS_WRITTEN
, STMT.STMT_TYPE -- static, dynamic
, STMT.STMT_OPERATION -- close commit none
, INFO.CLIENT_PID
, INFO.APPL_NAME
, INFO.APPL_ID
, INFO.PRIMARY_AUTH_ID
, INFO.CLIENT_NNAME -- host name
, INFO.CORR_TOKEN
from SYSIBMADM.SNAPSTMT as stmt,
table(SNAP_GET_APPL_INFO('ИмяБазы', -2)) AS INFO
WHERE STMT.agent_id = INFO.agent_id;
-- and UPPER(SUBSTR(STMT.STMT_TEXT,1,30)) like '% MYTABLE%'

SELECT
STMT.SNAPSHOT_TIMESTAMP,
, INFO.CLIENT_PID
, INFO.APPL_NAME
, INFO.APPL_ID
, INFO.PRIMARY_AUTH_ID
, INFO.CLIENT_NNAME -- HOST NAME
, INFO.CORR_TOKEN
STMT.AGENT_ID,
STMT.ROWS_READ,
STMT.ROWS_WRITTEN,
STMT.STMT_TYPE,
STMT.STMT_START,
STMT.STMT_STOP,
STMT.STMT_OPERATION,
STMT.QUERY_COST_ESTIMATE,
STMT.STMT_SYS_CPU_TIME_MS,
STMT.STMT_USR_CPU_TIME_MS
FROM
TABLE(SNAP_GET_STMT('', -2)) AS STMT,
TABLE(SNAP_GET_APPL_INFO('', -2)) AS INFO
WHERE
STMT.AGENT_ID = INFO.AGENT_I

Из командной строки динамические запросы можно получить
 db2 get snapshot for dynamic sql on ИмяБазы > ~/din.sql



Информация о таблице
SNAPTAB - административный вид
SNAP_GET_TAB_V91 - табличная функция

SELECT SUBSTR(TABSCHEMA,1,15) AS TABSCHEMA
, SUBSTR(TABNAME,1,15) AS TABNAME 
, TAB_TYPE -- тип таблицы (пользовательская, временная)
, ROWS_READ -- считано
, ROWS_WRITTEN - записано
, PAGE_REORGS - реорганизовано
DBPARTITIONNUM 
FROM TABLE(SNAP_GET_TAB('ИмяБазы',-2)) AS T

Отследить процесс реорганизации
SELECT SUBSTR(TABNAME, 1, 15) AS TAB_NAME, SUBSTR(TABSCHEMA, 1, 15)
AS TAB_SCHEMA, REORG_PHASE, SUBSTR(REORG_TYPE, 1, 20) AS REORG_TYPE,
REORG_STATUS, REORG_COMPLETION, DBPARTITIONNUM
FROM SYSIBMADM.SNAPTAB_REORG ORDER BY DBPARTITIONNUM

Состояние переключателей мониторов, аналог GET DBM MONITOR SWITCHES

SELECT

UOW_SW_STATE -- мониторинг записи

  ,STATEMENT_SW_STATE -- мониторинг SQL запросов

,TABLE_SW_STATE -- мониторинг активности таблиц

,BUFFPOOL_SW_STATE -- мониторинг активности буферных пулов

,LOCK_SW_STATE -- мониторинг блокировки

,SORT_SW_STATE -- мониторинг сортировок

,DBPARTITIONNUM -- партиция

FROM SYSIBMADM.SNAPSWITCHES

Вкл/Выкл мониторинга
 db2 -v update monitor switches using bufferpool on/off

Выполнение команд ADMIN_CMD
Создание backup'a
CALL SYSPROC.ADMIN_CMD('backup db ИмяБазы ')
Отключить приложения force application
CALL SYSPROC.ADMIN_CMD( 'force application (указываем handle приложения через запятую, 1177, 3245 )' )
С помощью этой команды можно запустить реорганизацию, сбор статистики, загрузку (LOAD,  IMPORT), выгрузку (EXPORT) и т.д.

Информация на сайте IBM:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0023485.htm

Консоль
db2 CALL GET_DBSIZE_INFO(?, ?, ?, 0); -физическое место под БД/ размер БД


db2ilist - список экземпляров
db2licm - управление лицензиями


список соединений с ODBC (Только для Windows)
LIST SYSTEM (USER) ODBC DATA SOURCES

список БД
db2 LIST DATABASE DIRECTORY

список табличных пространств в БД 
db2 list tablespaces show detail


список контейнеров в БД
db2 list tablespace containers for 3 show detail, где 3 это и есть ID табличного пространства, список контейнеров которого мы хотим получить


Мониторинг
db2fm - мониторинг отказов DB2 (unix only)

db2netrk - утилита слежения за памятью (instance, BD, agents)
может запускаться через определенный интервал (то же можно получить ч-з snapshot monitor)

db2pd - мониторинг действий механизмов и исправление ошибок

Диагностика (читаемый вывод лога db2diag )
Из командной строки (если win - db2cw):db2diag -H 1d -level "Severe, Critical"


db2dart - Database Analysis and Reporting Tool Command Examines databases for architectural correctness and reports any encountered errors.
>>-db2dart--database-name--+---------------------+-------------><
                           '-action--+---------+-'
                                     '-options-'


db2relocatedb - переименование БД, перенаправление контейнеров, log'а


DDL
db2look -d БазаДанных -a -e  -l  -x  -c  -f ИмяФайла.ddl;


Запустить скрипт DB2
db2 -td@ -s -f SAMPLE.DB2 -z SAMPLE.LOG
сам скрипт
-- script SAMPLE.DB2 --
!ECHO 'CONNECTING' > SAMPLE.FLG@
CONNECT TO SAMPLE_B@
-td@ - терминатор


To execute the CLP script, under the command window, enter:
db2 -tvg CLP_script
сам скрипт может выглядеть так
-- CLP_script --
connect to itsodb;
list tablespaces show detail;

db2cmd -c db2


http://www.ebenner.com/db2dba_blog/
http://www-01.ibm.com/support/knowledgecenter/api/content/SSEPGG_9.7.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0022000.html
http://www-01.ibm.com/support/knowledgecenter/api/content/SSEPGG_9.7.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0022001.html