DB2 9.7 »õ·Î¿î ±â´É – ÀÚÀ² Æ®·£Àè¼Ç(Autonomous Transactions)
1. ¼Ò°³
DB2 V9.7¿¡ »õ·ÎÀÌ Ãß°¡µÈ ÀÚÀ² Æ®·£Àè¼Ç¿¡ ´ëÇØ ¾Ë¾Æ º¸´Â ½Ã°£À» °¡Á® º¸µµ·Ï ÇÏ°Ú½À´Ï´Ù.
ÀÚÀ² Æ®·£Àè¼ÇÀº DB °ü¸®ÀÚ ³ª °³¹ßÀÚ¶ó¸é ÃæºÐÈ÷ ¸Å·ÂÀûÀ̶ó°í ´À²¸Áú ¼ö ÀÖ½À´Ï´Ù.
¿¹Á¦¸¦ ÅëÇÑ ÀÚÀ² Æ®·£Àè¼Ç µ¿ÀÛÀ» ¾Ë¾Æ º¼ ¼ö ÀÖµµ·Ï ÇÏ¿´À¸¸ç, ½Ç½ÀÀ» À§Çؼ± DB2 V9.7 LUW Á¦Ç°ÀÌ ÇÊ¿äÇÕ´Ï´Ù.
2. ÀÚÀ² Æ®·£Àè¼Ç¿¡ ´ëÇÑ ¼Ò°³
ÀÚÀ² Æ®·£Àè¼ÇÀº È£ÃâÀÚÀÇ ¾ðÄ¿¹ÌÆ®µÈ º¯°æ °á°ú¿¡ ¿µÇâÀ» ¹ÞÁö ¾Ê´Â ÀڽŠ¸¸ÀÇ COMMIT, ROLLBACK ¹üÀ§¸¦ °¡Áö°í ÀÖ½À´Ï´Ù. µ¡ºÙ¿© ¸»Çϸé, ¿äûµÈ ¼¼¼ÇÀÇ COMMIT, ROLLBACKÀÌ ¾Æ´Ñ ÀÚÀ² Æ®·£Àè¼Ç ³»ÀÇ ¿äû¿¡ ÀÇÇØ È®Á¤À» Áþ°Ô µË´Ï´Ù.
¿äû ÁßÀÎ ¼¼¼Ç¿¡ ´ëÇؼ´Â ¿äûµÈ ¼¼¼ÇÀÇ Á¦¾î±ÇÀÌ ³Ñ¾î°¡´Â ½ÃÁ¡±îÁö À¯Áö°¡ µÇÁö¸¸, ÀÚÀ² Æ®·£Àè¼Ç »ó¿¡¼´Â ½ÇÇà ÁßÀÎ ¼¼¼Ç¿¡ ´ëÇØ µ¿½ÃÀûÀÎ »ç¿ëÀÌ °¡´ÉÇÕ´Ï´Ù.
3. ÀÚÀ² Æ®·£Àè¼Ç ¸¸µé±â
DB2 ¾È¿¡¼ÀÇ ÀÚÀ² Æ®·£Àè¼ÇÀº ÀÚÀ² ÇÁ·Î½ÃÀú¿¡ ÀÇÇØ Á¦°øµË´Ï´Ù. CREATE PROCEDURE ¹®ÀÇ AUTONOMOUS Å°¿öµå¸¦ ÅëÇØ ÀÚÀ² ÇÁ·Î½ÃÀú¸¦ »ý¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù. [Ç¥ 1. Âü°í]
Ç¥ 1. CREATE PROCEDURE ÀÛ¼º ¿¹
CREATE OR REPLACE your_procedure_name
LANGUAGE SQL
AUTONOMOUS
BEGIN
do autonomous work ;
END
|
ÀÚÀ² ÇÁ·Î½ÃÀú´Â Æ®·£Àè¼ÇÀÇ µ¶¸³µÈ ¿äû¿¡ ÀÇÇØ ÀڽŸ¸ÀÇ ¼¼¼Ç¿¡¼ ¼öÇàÀÌ µË´Ï´Ù. Á¤»ó ÀÚÀ² ÇÁ·Î½ÃÀúÀÇ °æ¿î ¹¬½ÃÀûÀ¸·Î comitÀÌ ¼öÇàµÇ¸ç, ºñ Á¤»óÀÎ °æ¿ì rollbackÀÌ ¼öÇà µË´Ï´Ù. ÀÌ´Â Æ®·£Àè¼ÇÀÇ ¼öÇà°ú´Â ¹«°üÇÕ´Ï´Ù.
4. ½Ç »ç¿ë ¿¹
[½Ã³ª¸®¿À] B ÀºÇà¿¡¼´Â Å×ÀÌºí¿¡ Æ÷ÇÔµÈ °í°´ Á¤º¸°¡ ó¸® µÇ´Â °Í¿¡ ´ëÇØ ·Î±×°¡ ³²±â¸¦ ¿øÇÏ°í ÀÖ½À´Ï´Ù. ±×·¡¼ B ÀºÇà ¾îÇø®ÄÉÀÌ¼Ç °³¹ßÀÚ´Â °í°´ µ¥ÀÌÅ͸¦ Á¶È¸ÇÏ´Â ÀÎÅÍÆäÀ̽º¿¡ ´ëÇØ ½ºÅä¾îµå ÇÁ·Î½ÃÀú·Î Á¦°øÇÏ¿´½À´Ï´Ù. ÀÌ ½ºÅä¾îµå ÇÁ·Î½ÃÀú¿¡´Â Å×ÀÌºí¿¡ ´ëÇÑ Á¤º¸¸¦ Á¶È¸ÇÏ´Â »ç¿ëÀÚID, Äõ¸®¿¡ »ç¿ëµÈ °í°´ ¹øÈ£, Á¶È¸ ¿äûµÈ ½Ã°£ÀÌ ·Î±ë µÇµµ·Ï ¸¸µé¾îÁ³½À´Ï´Ù.
Ø ½Ç½À ½Ã À¯ÀÇ »çÇ×
l µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°áÀÌ µÇ¾î ÀÖ¾î¾ß ÇÔ.
l Auto commit´Â off »óÅ·Î
l ¸ðµç ¹®ÀåÀº ÆÄÀÏ·Î ¸¸µé¾î ¼öÇàÇϵµ·Ï ÇÏ¸é ¹®ÀåÀÇ Á¾°á ¹®ÀÚ´Â ‘%’¸¦ »ç¿ë
l ¸í·ÉÀº ‘DB2 CLP(Command Line Processor) »ó¿¡¼ ¹ßÇàÇϸç, ´ÙÀ½°ú °°Àº CLP ¸í·É¹®À» ÀÌ¿ëÇÕ´Ï´Ù.
db2 +c –td% -f <ÆÄÀϸí>
4.1. Å×À̺í ÀÛ¼º
ÇÊ¿ä Å×ÀÌºí¿¡ ´ëÇÑ Á¤ÀǸ¦ ÇÕ´Ï´Ù. ÇÑ °³ÀÇ Å×ÀÌºí¿¡´Â °í°´¿¡ ´ëÇÑ Á¤º¸¸¦ °¡Áö°í ÀÖ°í, ´Ù¸¥ Å×ÀÌºí¿¡´Â °í°´ Á¤º¸ Á¶È¸ ·Î±×¸¦ ÀúÀåÇÕ´Ï´Ù. [Ç¥ 2. Âü°í]
Ç¥ 2. Å×À̺í DDL
DROP TABLE customerSensitiveInfo %
CREATE TABLE customerSensitiveInfo (
customerAccountNumber integer,
amountOverdue integer) %
DROP TABLE log_table %
CREATE TABLE log_table (
queryingEmployeeID varchar(100),
customerAccNumber integer,
when timestamp) %
COMMIT %
|
´ÙÀ½À¸·Î °í°´ Á¤º¸¿¡ ´ëÇÑ Á¢±Ù ½Ã log_table¿¡ ±â·ÏÇÏ´Â ÇÁ·Î½ÃÀú¸¦ »ý¼ºÇÕ´Ï´Ù. [Ç¥ 3. Âü°í]
Ç¥ 3. LOG Å×ÀÌºí µ¥ÀÌÅÍ ±â·Ï ÇÁ·Î½ÃÀú
CREATE OP REPLACE PROCEDURE log_query
(in queryingEmployee varchar(100), in accNumber integer, in when timestamp)
LANGUAGE SQL
BEGIN
Insert into log_table values (queryingEmployee, accNumber, when) ;
END %
COMMIT %
|
B ÀºÇà ¾îÇø®ÄÉÀÌ¼Ç °³¹ßÀÚ´Â get_AmountOverdue ÇÁ·Î½ÃÀú¸¦ ÅëÇØ customerSeneitiveInfo Å×À̺í·Î ºÎÅÍ µ¥ÀÌÅ͸¦ Á¶È¸ÇØ ¿À¸é¼ ÀÌ Á¶È¸ ±â·ÏÀ» ³²±â±â À§ÇØ log_query ÇÁ·Î½ÃÀú¸¦ ½ÇÇàÇϵµ·Ï ÇÏ¿´½À´Ï´Ù. [Ç¥ 4. Âü°í]
Ç¥ 4. get_AmountOverdue ÇÁ·Î½ÃÀú
CREATE OR REPLACE PROCEDURE get_AmountOverdue
(in accountNumber integer, out overdue integer)
LANGUAGE SQL
BEGIN
DECLARE due integer ;
DECLARE currentTime timestamp ;
SET currentTime = CURRENT TIMESTAMP ;
CALL log_query(CURRENT USER, accountNumber, currentTime) ;
SELECT amountOverdue INTO due FROM customerSensitiveInfo
WHERE customerAccountNumber=accountNumber ;
SET overdue=due;
END %
COMMIT %
|
Get_AmountOverdue ÀÎÅÍÆäÀ̽º°¡ ¸¸µé¾î Á³´Ù¸é customerSensitiveInfo Å×ÀÌºí¿¡ °¡»óÀÇ °í°´ Á¤º¸¸¦ ÀÔ·ÂÇϵµ·Ï ÇÏ°Ú½À´Ï´Ù. [Ç¥ 5. Âü°í]
Ç¥ 5. °í°´ µ¥ÀÌÅÍ ÀÔ·Â
INSERT INTO customerSensitiveInfo VALUES (123435, 10000), (12346, 20000) %
COMMIT %
|
ÀÔ·ÂµÈ °í°´ µ¥ÀÌÅÍ°¡ Àß µé¾î °¬´ÂÁö Á¶È¸ÇØ º¸°Ú½À´Ï´Ù. [±×¸² 1. Âü°í]
±×¸² 1 . CustomerSensitiveInfo
ÀÌÁ¨ get_AmountOverdue ÇÁ·Î½ÃÀú¸¦ ÅëÇØ ‘12345’ °í°´¿¡ ´ëÇÑ Á¤º¸¸¦ Á¶È¸ ÇØ º¸µµ·Ï ÇÏ°Ú½À´Ï´Ù. Á¶È¸ ÈÄ ¹Ù·Î ROLLBACK ¸í·ÉÀÌ ¹ßÇà µÇ¾úÀ» ¶§ ¾î¶² °á°ú°¡ ³ª¿À´ÂÁö Á¶ÀÇ ±í°Ô º¸½Ã±â ¹Ù¶ø´Ï´Ù. [±×¸² 2. Âü°í]
±×¸² 2. °í°´ Á¤º¸ Á¶È¸ ¹× ·Î±× ±â·Ï È®ÀÎ
°í°´ Á¤º¸ Á¶È¸ ½Ã ROLLBACKÀÌ ÀÏ¾î³ °æ¿ì log_table¿¡ Á¶È¸ ±â·ÏÀÌ ³²Áö ¾ÊÀº °ÍÀ» È®ÀÎ ÇϼÌÀ» °Ì´Ï´Ù. ÀÌÁ¨ log_query ÇÁ·Î½ÃÀú¿¡ AUTONOMOUS Å°¿öµå¸¦ »ç¿ëÇÏ¿© ÀÚÀ² ÇÁ·Î½ÃÀú·Î Àç »ý¼º ÇØ º¸°Ú½À´Ï´Ù. [Ç¥ 6. Âü°í]
Ç¥ 6. ÀÚÀ² ÇÁ·Î½ÃÀú·Î ¸¸µé¾îÁø log_query ÇÁ·Î½ÃÀú
CREATE OP REPLACE PROCEDURE log_query
(in queryingEmployee varchar(100), in accNumber integer, in when timestamp)
LANGUAGE SQL
AUTONOMOUS
BEGIN
Insert into log_table values (queryingEmployee, accNumber, when) ;
END %
COMMIT %
|
°í°´ Á¤º¸¸¦ ´Ù½Ã Çѹø Á¶È¸ ÇØ º¸µµ·Ï ÇÏ°Ú½À´Ï´Ù. [±×¸² 3. Âü°í]
±×¸² 3. AUTONOMOUS »ç¿ë ½Ã log_table ±â·Ï ¹Ý¿µ
°í°´ Á¤º¸¸¦ Àаí rollback ÇÏ´Â Æ®·£Àè¼ÇÀÌ ¹ßÇà µÇ¾úÀ½¿¡µµ log_table¿¡´Â commit ÀÌ µÇ¾î ÀÖ´Â °ÍÀ» È®ÀÎ ÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌ °°Àº ±â´ÉÀ» ÀÌ¿ëÇÑ´Ù¸é Ä¿¹Ô µÇÁö ¾Ê´Â ±â·Ï¿¡ ´ëÇؼ¶óµµ µ¥ÀÌÅÍ Á¢±Ù¿¡ ´ëÇÑ ±â·ÏÀ» ³²±æ ¼ö°¡ ÀÖ½À´Ï´Ù.
|