Ȩ > ÀÚ·á½Ç > News Letter
 
Download
News Letter
Site Link
XpertMon ´º½º·¹ÅÍ 60È£ - DB2 9.7 »õ·Î¿î ±â´É
2010/02/10 14:49 17188

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 ÀÌ µÇ¾î ÀÖ´Â °ÍÀ» È®ÀÎ ÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌ °°Àº ±â´ÉÀ» ÀÌ¿ëÇÑ´Ù¸é Ä¿¹Ô µÇÁö ¾Ê´Â ±â·Ï¿¡ ´ëÇؼ­¶óµµ µ¥ÀÌÅÍ Á¢±Ù¿¡ ´ëÇÑ ±â·ÏÀ» ³²±æ ¼ö°¡ ÀÖ½À´Ï´Ù.

¸ñ·Ï ±Û¾²±â ¼öÁ¤