VALID 상태가 아닌 루틴 재컴파일하기

데이터베이스 마이그레이션, 리스토어(RESTORE) 작업으로 인해 루틴이 INVALID 상태로 빠지는 경우가 있습니다. 아래에서 설명하는 sysproc.admin_revalidate_db_objects 프로시저를 사용해 오브젝트 별 또는 일괄로 루틴들을 재컴파일 할 수 있습니다.

Question

DB2 Package could be revalidated by either the REBIND or the BIND command, however it may not work for SQL routines, how to mark a routine to be VALID?

Cause

For some reasons, some packages or routines may be marked as INVALID. For package which is invalid, it will allow revalidation to occur implicitly when the package is first used, or to occur explicitly by either the REBIND or the BIND command.
The rebind_rouine_package function will rebind the package associate with the SQL procedure, it will mark the related package itself VALID as well.
-> 해당 루틴이 VALID인지는 syscat.routines 카탈로그 테이블을 참조하면 됩니다. 'N'이면 INVALID 상태입니다.

However it is not the case for routine, it could not mark the routine VALID by REBIND, BIND command, and the rebind_rouine_package function.

Answer

As REBIND validate the package only, for routine to be VALID, it needs to revalidate it implicitly by accessing it, or call function such as admin_revalidate_db_objects explicitly.

For example: call sysproc.admin_revalidate_db_objects('PROCEDURE','MY_SCHEMA','MY_PROCEDURE')
-> 특정 프로시저나 함수이름을 지정할 수 있고, 특정 스키마에 대한 일괄 수행을 하려면 NULL을 씁니다.

It is different to revalidate objects that are inside of a module,
If the routine is within one module, it needs to use the MODULE type with the name of a specific module in ADMIN_REVALIDATE_DB_OBJECTS call,
and all of the invalid objects include routines inside of that module will be revalidated.

Such as the routine SYSIBMADM.WRITE_BLOB could NOT be revalidated by:
call sysproc.admin_revalidate_db_objects('PROCEDURE','SYSIBMADM','WRITE_BLOB').

Instead, as it belongs to UTL_MAIL module, it needs to be revalidated by:
call sysproc.admin_revalidate_db_objects('MODULE','SYSIBMADM','UTL_MAIL').

https://www-01.ibm.com/support/docview.wss?uid=swg21472742