Практика использования Oracle Data Integrator (ODI): Переполнение главной таблицы SNP_EXP

Привет всем.

Как известно, таблица SNP_EXP_TXT содержит текстовые части команд ODI.
В старых версиях, с 10.1.3.2.0 по 10.1.3.5.1 для ключевой колонки I_TXT из этой таблицы выделялось недостаточно места, таким образом, с течением времени могла возникнуть ситуация, когда свободные номера в колонке заканчивались, и она начинала выделять значения с 0, что приводило к ошибке ORA-00001: unique constraint (SDS1.PK_EXP_TXT) violated.

В версии 10.1.3.5.2 поле увеличили до 19 знаков.
Но, тем не менее, может возникнуть ситуация когда необходимо будет перестроить данную таблицу и убрать из нее дырки в нумерации ключей.

Один из наших коллег получил такой совет от техподдержки компании Оракл:

Как перенумеровать записи в таблице SNP_EXP_TXT рабочего репозитория ODI(Doc ID 753313.1).

Применимо к: Oracle Data Integrator - начиная с версии 10.1.3.2.0 [Релиз: 10gR3 и более поздние]
Информация в этом документе применима к системам на любых платформах.
Цель: данный документ предоставляет детальные SQL команды и пошаговые инструкции чтобы перенумеровать значения ключа в таблице SNP_EXP_TXT и связанных таблицах, а также чтобы переинициализоровать счетчик для ключа.

В А Ж Н О:

1. SQL команды, описанные ниже, не должны запускаться сами по себе, а обязательно должны удовлетворять требованиям описанным в документе Note 603109.1.

Я просмотрел этот документ, там рекомендуется очистить логи выполнения сессий в Операторе, удалить неиспользуемые записи в таблице SNP_EXP_TXT, а уже затем приступать к перенумерации записей.

2. На текущий момент скрипты полностью закончены и протестированы только для репозиториев сохраненных в СУБД Oracle или Microsoft SQLServer.

Решение

Шаг 1. Остановить все процессы Oracle Data Integrator, такие как запущенные клиентские приложения, выполняющиеся сценарии, агенты и т.п.

Шаг 2. Установите соединение с базой данных и схемой, в которых содержатся данные рабочего репозитория. Включите режим работы с использованием транзакций и без автоматического закрепления транзакций.

Шаг 3. Подготовка к работе. Копирование существующего ключа таблицы SNP_EXP_TXT.I_TXT во временную таблицу.

Oracle:
/* Пересчет статистики и перераспределение занимаемого места */

analyze table SNP_EXP_TXT compute statistics;
alter table SNP_EXP_TXT enable row movement;

analyze table SNP_SESSION compute statistics;
alter table SNP_SESSION enable row movement;

analyze table SNP_STEP_LOG compute statistics;
alter table SNP_STEP_LOG enable row movement;

analyze table SNP_SESS_TASK_LOG compute statistics;
alter table SNP_SESS_TASK_LOG enable row movement;

analyze table SNP_SESS_TXT_LOG compute statistics;
alter table SNP_SESS_TXT_LOG enable row movement;

analyze table SNP_SESS_STEP compute statistics;
alter table SNP_SESS_STEP enable row movement;

analyze table SNP_SESS_TASK compute statistics;
alter table SNP_SESS_TASK enable row movement;

analyze table SNP_TASK_TXT compute statistics;
alter table SNP_TASK_TXT enable row movement;

analyze table SNP_VAR_SESS compute statistics;
alter table SNP_VAR_SESS enable row movement;

analyze table SNP_VAR_SCEN compute statistics;
alter table SNP_VAR_SCEN enable row movement;

analyze table SNP_STEP_REPORT compute statistics;
alter table SNP_STEP_REPORT enable row movement;

analyze table SNP_SCEN_REPORT compute statistics;
alter table SNP_SCEN_REPORT enable row movement;

/* ТОЛЬКО для Oracle 10g+ и при установленной, для табличных пространств, опции autoextend*/

alter table SNP_EXP_TXT shrink space cascade;
alter table SNP_SESSION shrink space cascade;
alter table SNP_STEP_LOG shrink space cascade;
alter table SNP_SESS_TASK_LOG shrink space cascade;
alter table SNP_SESS_TXT_LOG shrink space cascade;
alter table SNP_SESS_STEP shrink space cascade;
alter table SNP_SESS_TASK shrink space cascade;
alter table SNP_TASK_TXT shrink space cascade;
alter table SNP_VAR_SESS shrink space cascade;
alter table SNP_VAR_SCEN shrink space cascade;
alter table SNP_STEP_REPORT shrink space cascade;
alter table SNP_SCEN_REPORT shrink space cascade;

/* Создание и заполнение временной таблицы */

create table XXX_EXP_TXT
(
NEW_ID NUMBER (19),
I_TXT NUMBER (19) NOT NULL,
primary key (I_TXT)
)

insert into XXX_EXP_TXT
(
I_TXT
)
select distinct I_TXT
from SNP_EXP_TXT

analyze table XXX_EXP_TXT compute statistics

Microsoft SQLServer:
/* Создание и заполнение временной таблицы */

create table XXX_EXP_TXT
(
ID NUMERIC(19) IDENTITY,
NEW_ID NUMERIC(19) NOT NULL,
I_TXT NUMERIC(19) NOT NULL,
primary key (I_TXT, NEW_ID)
)

insert into XXX_EXP_TXT
(
I_TXT, NEW_ID
)
select distinct I_TXT, 0
from SNP_EXP_TXT

IBM DB2/400:
Сначала запустите RGZPFM для таблицы SNP_EXP_TXT, чтобы провести ее реорганизацию.
Затем:

create table XXX_EXP_TXT
(
NEW_ID NUMERIC (19) WITH DEFAULT,
I_TXT NUMERIC (19) NOT NULL WITH DEFAULT,
primary key (I_TXT)
)

insert into XXX_EXP_TXT
(
I_TXT
)
select distinct I_TXT
from SNP_EXP_TXT

IBM DB2 UDB:

create table XXX_EXP_TXT
(
NEW_ID NUMERIC (19) WITH DEFAULT,
I_TXT NUMERIC (19) NOT NULL WITH DEFAULT,
primary key (I_TXT)
)

insert into XXX_EXP_TXT
(
I_TXT
)
select distinct I_TXT
from SNP_EXP_TXT

Шаг 4. Реорганизация идентификаторов, используемых в таблице SNP_EXP_TXT.
Для строк во временной таблице XXX_EXP_TXT выполним перенумерацию, выделив новое значение идентификатора начинающееся с номера 1.

Oracle: (использование стандартной функции ROWNUM):

update XXX_EXP_TXT
set NEW_ID = rownum * 1000 + to_number(substr(to_char(I_TXT), length(to_char(I_TXT)) - 2, 3))

Microsoft SQLServer: (использование механизма IDENTITY колонок):

update X
set X.NEW_ID = X.ID * 1000 + substring(convert(varchar,I_TXT), len(convert(varchar,I_TXT)) - 2, 3)
from XXX_EXP_TXT X

IBM DB2/400: (решение с использованием функции Relative Record Number):

update XXX_EXP_TXT
set NEW_ID = rrn(XXX_EXP_TXT) * 1000 + bigint(substring(digits(I_TXT), length(digits(I_TXT)) - 2, 3))

IBM DB2 UDB: (использование последовательности БД):

create sequence XXX_EXP_TXT_SEQ
start with 1

update XXX_EXP_TXT
set NEW_ID = (next value for XXX_EXP_TXT_SEQ) * 1000 + integer(substr(digits(I_TXT), length(digits(I_TXT)) - 2, 3))

Шаг 5. Обновляем SNP_EXP_TXT и связанные таблицы.

Oracle: (часть 1. см. ниже часть 2):

create table YYY_EXP_TXT
as
select S.*
from SNP_EXP_TXT S
where 1=2

insert into YYY_EXP_TXT
(
FIRST_DATE,
FIRST_USER,
IND_CHANGE,
INT_VERSION,
I_TXT,
LAST_DATE,
LAST_USER,
TXT,
TXT_ORD
)
select
S.FIRST_DATE,
S.FIRST_USER,
S.IND_CHANGE,
S.INT_VERSION,
X.NEW_ID,
S.LAST_DATE,
S.LAST_USER,
S.TXT,
S.TXT_ORD
from SNP_EXP_TXT S,
XXX_EXP_TXT X
where X.I_TXT = S.I_TXT

truncate table SNP_EXP_TXT

insert into SNP_EXP_TXT
select * from YYY_EXP_TXT

drop table YYY_EXP_TXT

/* Сбор статистики и переразмещение таблиц */

/* ТОЛЬКО для Oracle 10g+ и при установленной, для табличных пространств, опции autoextend*/

IBM DB2/400 и IBM DB2 UDB: (часть 1. см. ниже часть 2):

update SNP_EXP_TXT S
set S.I_TXT = (select X.NEW_ID
from XXX_EXP_TXT X
where S.I_TXT = X.I_TXT
)

Часть 2 для Oracle:, IBM DB2/400 и IBM DB2 UDB::

/* SNP_SESS_TASK_LOG: Ошибки и предупреждения для задач сессий */

update SNP_SESS_TASK_LOG S
set S.I_TXT_TASK_MESS = (select X.NEW_ID
from XXX_EXP_TXT X
where S.I_TXT_TASK_MESS = X.I_TXT
)
where S.I_TXT_TASK_MESS is not null
and exists (select 'X'
from SNP_SESS_TASK_LOG S2
where S.I_TXT_TASK_MESS = S2.I_TXT_TASK_MESS
)

/* SNP_STEP_LOG: Ошибки и предупреждения для шагов сессий */

update SNP_STEP_LOG S
set S.I_TXT_STEP_MESS = (select X.NEW_ID
from XXX_EXP_TXT X
where S.I_TXT_STEP_MESS = X.I_TXT
)
where S.I_TXT_STEP_MESS is not null
and exists (select 'X'
from SNP_STEP_LOG S2
where S.I_TXT_STEP_MESS = S2.I_TXT_STEP_MESS
)

/* SNP_VAR_SESS (1): Значения переменных во время выполнения */

update SNP_VAR_SESS S
set S.I_TXT_VAR = (select X.NEW_ID
from XXX_EXP_TXT X
where S.I_TXT_VAR = X.I_TXT
)
where S.I_TXT_VAR is not null
and exists (select 'X'
from SNP_VAR_SESS S2
where S.I_TXT_VAR = S2.I_TXT_VAR
)

/* SNP_VAR_SESS (2): Значения переменных по-умолчанию во время выполнения */

update SNP_VAR_SESS S
set S.I_TXT_DEF_T = (select X.NEW_ID
from XXX_EXP_TXT X
where S.I_TXT_DEF_T = X.I_TXT
)
where S.I_TXT_DEF_T is not null
and exists (select 'X'
from SNP_VAR_SESS S2
where S.I_TXT_DEF_T = S2.I_TXT_DEF_T
)

/* SNP_SESSION (1): Параметры и ключевые слова для сессий */

update SNP_SESSION S
set S.I_TXT_SESS_PARAMS = (select X.NEW_ID
from XXX_EXP_TXT X
where S.I_TXT_SESS_PARAMS = X.I_TXT
)
where S.I_TXT_SESS_PARAMS is not null
and exists (select 'X'
from SNP_SESSION S2
where S.I_TXT_SESS_PARAMS = S2.I_TXT_SESS_PARAMS
)

/* SNP_SESSION (2): Ошибки и предупреждения для сессий */

update SNP_SESSION S
set S.I_TXT_SESS_MESS = (select X.NEW_ID
from XXX_EXP_TXT X
where S.I_TXT_SESS_MESS = X.I_TXT
)
where S.I_TXT_SESS_MESS is not null
and exists (select 'X'
from SNP_SESSION S2
where S.I_TXT_SESS_MESS = S2.I_TXT_SESS_MESS
)

/* SNP_STEP_REPORT: Ошибки и предупреждения для шагов сценариев */

update SNP_STEP_REPORT S
set S.I_TXT_STEP_MESS = (select X.NEW_ID
from XXX_EXP_TXT X
where S.I_TXT_STEP_MESS = X.I_TXT
)
where S.I_TXT_STEP_MESS is not null
and exists (select 'X'
from SNP_STEP_REPORT S2
where S.I_TXT_STEP_MESS = S2.I_TXT_STEP_MESS
)

/* SNP_SCEN_REPORT: Ошибки и предупреждения для сценариев */

update SNP_SCEN_REPORT S
set S.I_TXT_SESS_MESS = (select X.NEW_ID
from XXX_EXP_TXT X
where S.I_TXT_SESS_MESS = X.I_TXT
)
where S.I_TXT_SESS_MESS is not null
and exists (select 'X'
from SNP_SCEN_REPORT S2
where S.I_TXT_SESS_MESS = S2.I_TXT_SESS_MESS
)

... Данные сценариев:
/* SNP_SCEN : Описания сценариев */

update SNP_SCEN S
set S.I_TXT_SCEN = (select X.NEW_ID
from XXX_EXP_TXT X
where S.I_TXT_SCEN = X.I_TXT
)
where S.I_TXT_SCEN is not null
and exists (select 'X'
from SNP_SCEN S2
where S.I_TXT_SCEN = S2.I_TXT_SCEN
)

/* SNP_SCEN_FOLDER: Описание папок сценариев */

update SNP_SCEN_FOLDER S
set S.I_TXT_DESCRIPTION = (select X.NEW_ID
from XXX_EXP_TXT X
where S.I_TXT_DESCRIPTION = X.I_TXT
)
where S.I_TXT_DESCRIPTION is not null
and exists (select 'X'
from SNP_SCEN_FOLDER S2
where S.I_TXT_DESCRIPTION = S2.I_TXT_DESCRIPTION
)

... Данные сценариев и многострочных переменных:
/* SNP_VAR_SCEN (1): Значение переменных во время выполнения сценариев */

update SNP_VAR_SCEN S
set S.I_TXT_VAR = (select X.NEW_ID
from XXX_EXP_TXT X
where S.I_TXT_VAR = X.I_TXT
)
where S.I_TXT_VAR is not null
and exists (select 'X'
from SNP_VAR_SCEN S2
where S.I_TXT_VAR = S2.I_TXT_VAR
)

/* SNP_VAR_SCEN (2): Значения переменных по-умолчанию при выполнении сценариев */

update SNP_VAR_SCEN S
set S.I_TXT_DEF_T = (select X.NEW_ID
from XXX_EXP_TXT X
where S.I_TXT_DEF_T = X.I_TXT
)
where S.I_TXT_DEF_T is not null
and exists (select 'X'
from SNP_VAR_SCEN S2
where S.I_TXT_DEF_T = S2.I_TXT_DEF_T
)

/* SNP_VAR_DATA: Значения многострочных переменных */

update SNP_VAR_DATA S
set S.I_TXT_VAR_T = (select X.NEW_ID
from XXX_EXP_TXT X
where S.I_TXT_VAR_T = X.I_TXT
)
where S.I_TXT_VAR_T is not null
and exists (select 'X'
from SNP_VAR_DATA S2
where S.I_TXT_VAR_T = S2.I_TXT_VAR_T
)

Microsoft SQLServer::

select S.*
into YYY_EXP_TXT
from SNP_EXP_TXT S
where 1=2

insert into YYY_EXP_TXT
(
FIRST_DATE,
FIRST_USER,
IND_CHANGE,
INT_VERSION,
I_TXT,
LAST_DATE,
LAST_USER,
TXT,
TXT_ORD
)
select
S.FIRST_DATE,
S.FIRST_USER,
S.IND_CHANGE,
S.INT_VERSION,
X.NEW_ID,
S.LAST_DATE,
S.LAST_USER,
S.TXT,
S.TXT_ORD
from SNP_EXP_TXT S,
XXX_EXP_TXT X
where X.I_TXT = S.I_TXT

truncate table SNP_EXP_TXT

insert into SNP_EXP_TXT
select * from YYY_EXP_TXT

... данные о выполнениях:
/* SNP_SESS_TASK_LOG: Ошибки и предупреждения для задач сессий */

update S
set S.I_TXT_TASK_MESS = X.NEW_ID
from SNP_SESS_TASK_LOG S,
XXX_EXP_TXT X
where S.I_TXT_TASK_MESS = X.I_TXT
and S.I_TXT_TASK_MESS is not null

/* SNP_STEP_LOG: Ошибки и предупреждения для шагов сессий */

update S
set S.I_TXT_STEP_MESS = X.NEW_ID
from SNP_STEP_LOG S,
XXX_EXP_TXT X
where S.I_TXT_STEP_MESS = X.I_TXT
and S.I_TXT_STEP_MESS is not null

/* SNP_VAR_SESS (1): Значения переменных во время выполнения */

update S
set S.I_TXT_VAR = X.NEW_ID
from SNP_VAR_SESS S,
XXX_EXP_TXT X
where S.I_TXT_VAR = X.I_TXT
and S.I_TXT_VAR is not null

/* SNP_VAR_SESS (2): Значения переменных по-умолчанию во время выполнения */

update S
set S.I_TXT_DEF_T = X.NEW_ID
from SNP_VAR_SESS S,
XXX_EXP_TXT X
where S.I_TXT_DEF_T = X.I_TXT
and S.I_TXT_DEF_T is not null

/* SNP_SESSION (1): Параметры и ключевые слова для сессий */

update S
set S.I_TXT_SESS_PARAMS = X.NEW_ID
from SNP_SESSION S,
XXX_EXP_TXT X
where S.I_TXT_SESS_PARAMS = X.I_TXT
and S.I_TXT_SESS_PARAMS is not null

/* SNP_SESSION (2): Ошибки и предупреждения для сессий */

update S
set S.I_TXT_SESS_MESS = X.NEW_ID
from SNP_SESSION S,
XXX_EXP_TXT X
where S.I_TXT_SESS_MESS = X.I_TXT
and S.I_TXT_SESS_MESS is not null

/* SNP_STEP_REPORT: Ошибки и предупреждения для шагов сценариев */

update S
set S.I_TXT_STEP_MESS = X.NEW_ID
from SNP_STEP_REPORT S,
XXX_EXP_TXT X
where S.I_TXT_STEP_MESS = X.I_TXT
and S.I_TXT_STEP_MESS is not null

/* SNP_SCEN_REPORT: Ошибки и предупреждения для сценариев */

update S
set S.I_TXT_SESS_MESS = X.NEW_ID
from SNP_SCEN_REPORT S,
XXX_EXP_TXT X
where S.I_TXT_SESS_MESS = X.I_TXT
and S.I_TXT_SESS_MESS is not null

... данные сценариев:
/* SNP_SCEN : Описания сценариев */

update S
set S.I_TXT_SCEN = X.NEW_ID
from SNP_SCEN S,
XXX_EXP_TXT X
where S.I_TXT_SCEN = X.I_TXT
and S.I_TXT_SCEN is not null

/* SNP_SCEN_FOLDER: Описание папок сценариев */

update S
set S.I_TXT_DESCRIPTION = X.NEW_ID
from SNP_SCEN_FOLDER S,
XXX_EXP_TXT X
where S.I_TXT_DESCRIPTION = X.I_TXT
and S.I_TXT_DESCRIPTION is not null

update S
set S.I_TXT_VAR = X.NEW_ID
from SNP_VAR_SCEN S,
XXX_EXP_TXT X
where S.I_TXT_VAR = X.I_TXT
and S.I_TXT_VAR is not null

/* SNP_VAR_SCEN (2): Значения переменных по-умолчанию во время выполнения */

update S
set S.I_TXT_DEF_T = X.NEW_ID
from SNP_VAR_SCEN S,
XXX_EXP_TXT X
where S.I_TXT_DEF_T = X.I_TXT
and S.I_TXT_DEF_T is not null

/* SNP_VAR_DATA: Значения многострочных переменных */

update S
set S.I_TXT_VAR_T = X.NEW_ID
from SNP_VAR_DATA S,
XXX_EXP_TXT X
where S.I_TXT_VAR_T = X.I_TXT
and S.I_TXT_VAR_T is not null

Шаг 6. Установим следующее доступное значение для новых идентификаторов таблицы SNP_EXP_TXT. Установим верное значение в таблице SNP_ID соответствующее увеличенному на единицу максимальному значению SNP_EXP_TXT.I_TXT:

Oracle, Microsoft SQLServer, IBM DB2/400 и IBM DB2 UDB:

update SNP_ID
set ID_NEXT = (select max(round(NEW_ID/1000,0)) + 1 from XXX_EXP_TXT)
where ID_TBL = 'SNP_EXP_TXT'

Шаг 7.. Применяем изменения и закрепляем транзакцию.

commit

Шаг 8. Удаляем временные объекты БД.

Oracle: и IBM DB2/400

drop table XXX_EXP_TXT

Microsoft SQLServer:

drop table XXX_EXP_TXT
drop table YYY_EXP_TXT

IBM DB2 UDB:

drop table XXX_EXP_TXT
drop table YYY_EXP_TXT /* if step 6ii has been applied */

drop sequence XXX_EXP_TXT_SEQ

Источник:
http://forums.oracle.com/forums/thread.jspa?threadID=1072642

См. также удаление неиспользованной информации из СНП_ТЕКСТ.
Типы переменных.

Практика использования Oracle Data Integrator (ODI)

Страницы

понедельник, 30 мая 2011 г.

Переполнение главной таблицы SNP_EXP_TXT.

Комментариев нет:

Отправить комментарий

Страницы

понедельник, 30 мая 2011 г.

Переполнение главной таблицы SNP_EXP_TXT.

Комментариев нет:

Отправить комментарий

понедельник, 30 мая 2011 г.