DBMS 2
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
여러 가지 옵션에 의해 미리 설정된 특정한 시간이 되면 백업과 같이 미리 작성된 이벤트 핸들링 루틴이 실행됩니다. START TIME 혹은 BETWEEN 절, EVERY 절, ON 절, START DATE 절을 잘 조합하면 원하는 시점에 자동으로 정기적인 DBA 작업을 수행할 수 있습니다. 기술된 것처럼 특정한 사건이 발생하면 이를 자동으로 감지하여 이벤트 핸들링 루틴을 실행합니다. 대상이 되는 사건으로는 백업의 종료, 접속 성공, 접속 실패, 접속 종료 등이 있으며 이를 이용하며 접속에 관련된 데이터를 취합, 분석할 수 있습니다. 이벤트 타입으로 Connect와 Disconnect 사용시에 반드시 전후에 " 를 사용하여야 문법 접속에 관련오류를 방지할 수 있습니다. // 접속에 성공하면handle-routine 이 실행됨 EVENT_PARAMETER( 'ConnectionID' | 'User' | 'EventName' | 'Executions' | 이벤트 핸들링 루틴에서 context 정보를 제공하는 함수로서 제공되는 context에는 사용 자명, 접속ID, 이벤트명, 이벤트 실행 횟수 등이 있습니다. TRIGGER EVENT event-name ; 특정한 이벤트를 이벤트 조건이 아닌 이 명령어를 이용하여 실행시킬 수 있습니다. 이는 개발자가 이벤트 실행 테스트를 위해 해당 조건까지 기다리지 않아도 되는 편리함을 제공 합니다. TRIGGER EVENT Summarize ; 실제 이벤트에 대한 내용을 보기 위해서는 sysevent 테이블의 action, event_name 컬럼을 확인하면 됩니다. DROP EVENT event-name ;Event & Schedule
Event & Schedule
create event
[ TYPEevent-type |SCHEDULEschedule-name schedule-spec ]
[ ENABLE | DISABLE ]// default는enable
HANDLER
BEGIN
handling-routine;
END;
create event - schedule 기능
syntax1
SCHEDULE schedule-name
START TIME start-time | BETWEEN start-time AND end-time
[ EVERY period HOURS | MINUTES | SECONDS ]
[ ON (day-of-week | day-of-month) ]
[ START DATE start-date ]
event-handling routine;
매주 월요일 ~ 금요일 오전 9시부터 오후 6시까지 매시간마다 통계작업을 실행하는 예
SCHEDULE BETWEEN '9:00 am' AND '6:00 pm'
EVERY 1 HOURS
ON ( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' )
HANDLER
BEGIN
INSERT INTO DBA.OrderSummary
SELECT MAX( date_ordered ), COUNT( * ), SUM( amount )
FROM DBA.Orders
WHERE date_ordered = CURRENT DATE ;
END
매일 오전 1시가 되면 백업을 실행하는 예
SCHEDULE START TIME '6:00 pm' EVERY 24 HOURS
HANDLER
BEGIN
BACKUP DATABASE ……;
END
create event - event 기능1
syntax2
TYPE BackupEnd // 백업이 종료되면
| "Connect" // 접속이 성공하면
| ConnectFailed // 접속이 실패하면
| "Disconnect" // 접속을 끊으면
event-handling routine;
예)
TYPE "Connect"
HANDLER
BEGIN
handling-routine;
END
create event - event 2
syntax3
TYPE DBDiskSpace | GrowDB | // ASA 의.db 영역
| LogDiskSpace | GrowLog| // ASA 의.log 영역
| TempDiskSpace | GrowTemp // ASA 의 임시($ASTMP) 영역
| "RAISEERROR" // 오류가
발생하면
| ServerIdle // db 가idle 하다면
WHERE EVENT_CONDITION (
DBFreePercent | DBFreeSpace | // TYPE 이DBDiskSpace 일때 사용
DBSize | // TYPE 이GrowDB 일때 사용
LogFreePercent | LogFreeSpace | // TYPE 이LogDiskSpace 일때 사용
LogSize | // TYPE 이GrowLog 일때 사용
TempFreePercent | TempFreeSpace | // TYPE 이TempDiskSpace 일때
TempSize | // TYPE 이GrowTemp 일때 사용
ErrorNumber | // TYPE 이RAISEERROR 일때
IdleTime | // TYPE 이ServerIdle
Interval ) comparison-operator value // 모든 조건에 사용
event-handling routine;
예)
TYPE ServerIdle
WHERE EVENT_CONDITION( 'IdleTime' ) >= 600
AND EVENT_CONDITION( 'Interval' ) >= 3600
HANDLER
BEGIN
handling-routine;
END
// 서버가 한시간에 한번10 분 동안idle 하다면 이벤트 핸들링 루틴을 실행
event parameter
syntax
'ScheduleName' | 'DisconnectReason' | 'APPINFO' );
예)
BEGIN
……
SELECT EVENT_PARAMETER('User') INTO …
// 이 이벤트를 유발시킨 사용자ID 를 출력합니다.
……
END
예제
사용자의 접속이 이루어지면 특정 파일에 원하는 정보를 기록합니다.
TYPE "Connect"
HANDLER
BEGIN
DECLARE v_username CHAR(50);
DECLARE v_eventtime TIMESTAMP;
DECLARE v_userip CHAR(50);
DECLARE v_cmd VARCHAR(250);
SELECT EVENT_PARAMETER('User'), NOW(),
connection_property('NodeAddress',EVENT_PARAMETER('ConnectionID'))
INTO v_username, v_eventtime, v_userip;
SET v_cmd = 'echo connection,' || v_username || ',' || v_eventtime || ','
|| v_userip || ',' || EVENT_PARAMETER('ConnectionID') || ', >> S.out';
CALL xp_cmdshell(v_cmd);
COMMIT;
END;
사용자의 접속이 끊어지면 특정 파일에 원하는 정보를 기록합니다.
TYPE "DisConnect"
HANDLER
BEGIN
DECLARE v_username CHAR(50);
DECLARE v_eventtime TIMESTAMP;
DECLARE v_userip CHAR(50);
DECLARE v_cmd VARCHAR(250);
SELECT EVENT_PARAMETER('User'), now(),
connection_property('NodeAddress',EVENT_PARAMETER('ConnectionID'))
INTO v_username, v_eventtime, v_userip;
SET v_cmd = 'echo disconnection,' || v_username || ',' || v_eventtime || ','
|| v_userip || ',' || EVENT_PARAMETER('ConnectionID') || ', >> S.out';
CALL xp_cmdshell(v_cmd);
COMMIT;
END;
사용자의 접속이 끊어지면 특정 파일에 원하는 정보를 기록합니다.
TYPE "DisConnect"
HANDLER
BEGIN
DECLARE v_username CHAR(50);
DECLARE v_eventtime TIMESTAMP;
DECLARE v_userip CHAR(50);
DECLARE v_cmd VARCHAR(250);
SELECT EVENT_PARAMETER('User'), now(),
connection_property('NodeAddress',EVENT_PARAMETER('ConnectionID'))
INTO v_username, v_eventtime, v_userip;
SET v_cmd = 'echo disconnection,' || v_username || ',' || v_eventtime || ','
|| v_userip || ',' || EVENT_PARAMETER('ConnectionID') || ', >> S.out';
CALL xp_cmdshell(v_cmd);
COMMIT;
END;
오전 9시에서 오후 7시 동안 매 1 시간마다 Schedule을 통해서 사용자 접속 이력 테이블에 저장합니다. 기존 파일을 Rename하여 저장한 후 Rename된 파일을 Load합니다
SCHEDULE SCH_Load BETWEEN '09:00AM' AND '07:00PM' EVERY 1 HOURS
HANDLER
BEGIN
DECLARE v_cmd VARCHAR(250);
DECLARE v_eventtime VARCHAR(20);
SELECT DATEFORMAT(NOW(), 'YYYYMMDD_HH') INTO v_eventtime;
SET v_cmd = 'mv S.out S.out.' || v_eventtime;
CALL xp_cmdshell(v_cmd);
COMMIT;
EXECUTE IMMEDIATE ' LOAD TABLE User_Conn_Event ' || // 미리
생성
' (Event_Type, User_Name, Event_Time, User_IP, Conn_ID) ' ||
' FROM ''S.out.' || v_eventtime || '''' ||
' QUOTES OFF ESCAPES OFF ' ||
' DELIMITED BY '',' || '''' ||
' ROW DELIMITED BY ''\\n' || '''';
COMMIT;
END;
trigger event
syntax
예)
// Summarize 이벤트를 유발시키는 조건이 아닌 지금 바로 대상 이벤트를 실행
참고
FROM sysevent
WHERE event_name = 'Summarize';
drop event
syntax
DROP EVENT EVT_Connect;