Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Новый топик    Ответить
 GET DIAGNOSTICS + SQLCODE?  [new]
ananas2
Member

Откуда:
Сообщений: 174
Добрый день!

Делаю подобную конструкцию в ХП:

delete from select ... fetch first 10000 rows

В цикле LOOP надо получить и SQL код и количество удаленных записей.
Подряд IF SQLCODE END IF и GET DIAGNOSTICS DEL_ROWS = ROW_COUNT не работают. Решил попробовать так:

loop: loop
delete from select ... fetch first 10000 rows
GET DIAGNOSTICS RET_STAT = DB2_RETURN_STATUS;
GET DIAGNOSTICS DEL_ROWS = ROW_COUNT;
IF RET_STAT <> -2147221503 then leave loop;
end if;

Где -2147221503 - это код, который возвращается на SQL0100W(проверил на нескольких серверах). Меня только смущает описание в мануалах этого DB2_RETURN_STATUS

Identifies the status value returned from the procedure associated with the previously executed SQL statement, provided that the statement was a CALL statement invoking a procedure that returns a status. If the previous statement is not such a statement, then the value returned has no meaning and could be any integer.

Собственно, вопрос в том, можно ли этот код использовать (всегда ли он будет одинаковым)? Есть ли другие варианты, чтобы получить и сигнал для выхода из лупа и количество записей?

Заранее, спасибо!
14 фев 11, 01:27    [10229652]     Ответить | Цитировать Сообщить модератору
 Re: GET DIAGNOSTICS + SQLCODE?  [new]
ananas2
Member

Откуда:
Сообщений: 174
ananas2
loop: loop
delete from select ... fetch first 10000 rows
GET DIAGNOSTICS RET_STAT = DB2_RETURN_STATUS;
GET DIAGNOSTICS DEL_ROWS = ROW_COUNT;
IF RET_STAT <> -2147221503 then leave loop;
end if;


Опечатка, конечно, так
...
IF RET_STAT = -2147221503 then leave loop;
...
14 фев 11, 11:37    [10230923]     Ответить | Цитировать Сообщить модератору
 Re: GET DIAGNOSTICS + SQLCODE?  [new]
Mark Barinstein
Member

Откуда: Москва
Сообщений: 4946
ananas2,

Добрый день.

DB2_RETURN_STATUS вы можете использовать только после CALL, иначе оно вернёт мусор.
Если я правильно понял задачу, вам надо удалять по 10 000 строк из таблицы до тех пор, пока не удалится всё по условию в селекте?
Тогда:
loop: loop
 delete from (select ... fetch first 10000 rows);
 GET DIAGNOSTICS DEL_ROWS = ROW_COUNT;
 IF SQLCODE<>100 or ROW_COUNT<10000
  then leave loop;
 end if;
-- commit;
end loop;
-- commit;
GET DIAGNOSTICS не меняет SQLCODE и SQLSTATE, выставленные после выполнения предыдущей команды.
Если надо сохранить SQLCODE от delete, то его надо сразу же после GET DIAGNOSTICS присвоить какой-то переменной.
Раскомментируйте строки с commit, если смысл этого упражнения в том, чтобы не переполнять журнал при таком массивном удалении.
14 фев 11, 12:45    [10231504]     Ответить | Цитировать Сообщить модератору
 Re: GET DIAGNOSTICS + SQLCODE?  [new]
ananas2
Member

Откуда:
Сообщений: 174
Mark Barinstein
ananas2,

Добрый день.

DB2_RETURN_STATUS вы можете использовать только после CALL, иначе оно вернёт мусор.
Если я правильно понял задачу, вам надо удалять по 10 000 строк из таблицы до тех пор, пока не удалится всё по условию в селекте?
Тогда:
loop: loop
 delete from (select ... fetch first 10000 rows);
 GET DIAGNOSTICS DEL_ROWS = ROW_COUNT;
 IF SQLCODE<>100 or ROW_COUNT<10000
  then leave loop;
 end if;
-- commit;
end loop;
-- commit;
GET DIAGNOSTICS не меняет SQLCODE и SQLSTATE, выставленные после выполнения предыдущей команды.
Если надо сохранить SQLCODE от delete, то его надо сразу же после GET DIAGNOSTICS присвоить какой-то переменной.
Раскомментируйте строки с commit, если смысл этого упражнения в том, чтобы не переполнять журнал при таком массивном удалении.


Спасибо, я почему-то был уверен, что GET DIAGNOSTICS меняет :)
14 фев 11, 14:02    [10232239]     Ответить | Цитировать Сообщить модератору
 Re: GET DIAGNOSTICS + SQLCODE?  [new]
ananas2
Member

Откуда:
Сообщений: 174
Написал процедуру, которая последовательно удаляет из таблиц записи по дате.

Проблема в том, что выход из LOOP происходит как-то странно.
В loop1 удаляется только 17К записей хотя, там еще остается 108К удовлетворяющих условию
delete from REACT.REPAIR_ACCESSORY where repair_id in
(select r.repair_id from REACT.repair r where r.customer_ctn = 'delete' .
И еще по завершению процедуры получаю:
SQL0385W Assignment to an SQLSTATE or SQLCODE variable in an SQL
routine may be over-written and does not activate any handler. SQLSTATE=01643

Есть какие-нибудь идеи почему?
SET SQLCODE = 0; наверно, лишний тут, но дело думаю не в нем.


Вот кусок процедуры:

create TABLE SESSION.DELETE_OUTDATED_REPAIRS
( TAB_NAME VARCHAR(50) NOT NULL,
COUNT_DELETED_ROWS BIGINT,
PROC_NAME VARCHAR(50)
)@

CREATE PROCEDURE TEMP.delete_outdated_repairs(IN REPAIR_STATUS_DATE TIMESTAMP)
BEGIN

DECLARE SQLCODE INTEGER;
DECLARE TABNAME VARCHAR(50);
DECLARE DELETE_ROWS INTEGER;

---updating old repairs ~70K (IF REPAIR_STATUS_DATE='2009-01-01 00:00:00.000000') ---
SET TABNAME='REPAIR';
INSERT INTO SESSION.DELETE_OUTDATED_REPAIRS VALUES (TABNAME, 0, 'TEMP.delete_outdated_repairs');

update REACT.REPAIR set customer_ctn = 'delete' where repair_id in
(select r.repair_id from REACT.repair r, REACT.repair_status rs, REACT.repair_status_code rsc
where r.last_repair_status_id = rs.repair_status_id and rs.repair_status_code_id = rsc.repair_status_code_id
and rsc.code in ('STORE:COLLECTED-CLOSED', 'STORE:CANCELLED-COLLECTED', 'REACT:FORCED-CLOSED')
and r.SUBMITTED_DT < REPAIR_STATUS_DATE);
GET DIAGNOSTICS DELETE_ROWS = ROW_COUNT;
commit;
UPDATE SESSION.DELETE_OUTDATED_REPAIRS SET COUNT_DELETED_ROWS = (SELECT COUNT_DELETED_ROWS + DELETE_ROWS from SESSION.DELETE_OUTDATED_REPAIRS where TAB_NAME = TABNAME AND PROC_NAME='TEMP.delete_outdated_repairs') where TAB_NAME = TABNAME AND PROC_NAME='TEMP.delete_outdated_repairs';
SET SQLCODE = 0;
SET DELETE_ROWS= 0;

---deleting old indicators ~ 2K ---
SET TABNAME='REPAIR_DAMAGE_INDICATOR';
INSERT INTO SESSION.DELETE_OUTDATED_REPAIRS VALUES (TABNAME, 0, 'TEMP.delete_outdated_repairs');

delete from REACT.REPAIR_DAMAGE_INDICATOR where REPAIR_ID in
(select r.repair_id from REACT.repair r where r.customer_ctn = 'delete');
GET DIAGNOSTICS DELETE_ROWS = ROW_COUNT;
commit;
UPDATE SESSION.DELETE_OUTDATED_REPAIRS SET COUNT_DELETED_ROWS = (SELECT COUNT_DELETED_ROWS + DELETE_ROWS from SESSION.DELETE_OUTDATED_REPAIRS where TAB_NAME = TABNAME AND PROC_NAME='TEMP.delete_outdated_repairs') where TAB_NAME = TABNAME AND PROC_NAME='TEMP.delete_outdated_repairs';
SET SQLCODE = 0;
SET DELETE_ROWS= 0;

---deleting old ACCESSORY ~ 125K ---
SET TABNAME='REPAIR_ACCESSORY';
INSERT INTO SESSION.DELETE_OUTDATED_REPAIRS VALUES (TABNAME, 0, 'TEMP.delete_outdated_repairs');

loop1: LOOP
delete from REACT.REPAIR_ACCESSORY where repair_id in
(select r.repair_id from REACT.repair r where r.customer_ctn = 'delete' FETCH FIRST 10000 ROWS ONLY);
GET DIAGNOSTICS DELETE_ROWS = ROW_COUNT;
IF SQLCODE = 100 then leave loop1;
end if;
commit;
UPDATE SESSION.DELETE_OUTDATED_REPAIRS SET COUNT_DELETED_ROWS = (SELECT COUNT_DELETED_ROWS + DELETE_ROWS from SESSION.DELETE_OUTDATED_REPAIRS where TAB_NAME = TABNAME AND PROC_NAME='TEMP.delete_outdated_repairs') where TAB_NAME = TABNAME AND PROC_NAME='TEMP.delete_outdated_repairs';
END LOOP loop1;
SET SQLCODE = 0;
SET DELETE_ROWS= 0;

loop2:
delete from tab2
.....
end loop2
SET SQLCODE = 0;
SET DELETE_ROWS= 0;

loop3
....
etc
1 мар 11, 15:09    [10306605]     Ответить | Цитировать Сообщить модератору
 Re: GET DIAGNOSTICS + SQLCODE?  [new]
Mark Barinstein
Member

Откуда: Москва
Сообщений: 4946
ananas2,

Если я правильно понимаю логику удаления из REACT.REPAIR_ACCESSORY по 10k записей, то вместо
delete from REACT.REPAIR_ACCESSORY where repair_id in
(select r.repair_id from REACT.repair r where r.customer_ctn = 'delete' FETCH FIRST 10000 ROWS ONLY);
надо:
delete from 
(
select 1 from REACT.REPAIR_ACCESSORY where repair_id in
(select r.repair_id from REACT.repair r where r.customer_ctn = 'delete')
FETCH FIRST 10000 ROWS ONLY
) t
а то у вас получается, что удаление из REACT.REPAIR_ACCESSORY производится:
- одной командой; повторный запуск этой команды не имеет смысла, если, конечно, больше никто не изменяет эти таблицы или оптимизатор вдруг не захочет вернуть другие 10000 записей (в отличном от первого запуска порядке, что очень маловероятно) из REACT.repair
- удаляются записи только с теми repair_id, которые встретились в repair_id из первых 10000 записей из REACT.repair

Ну и про присвоение SQLCODE - да, это не нужная операция.
1 мар 11, 15:52    [10306956]     Ответить | Цитировать Сообщить модератору
 Re: GET DIAGNOSTICS + SQLCODE?  [new]
ananas2
Member

Откуда:
Сообщений: 174
Да, точно. Как-то я совсем по-детски ошибся :).

Спасибо, Марк
1 мар 11, 16:02    [10307063]     Ответить | Цитировать Сообщить модератору
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Ответить