Поиск по этому блогу

вторник, 30 октября 2012 г.

Администрирование 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