XpertMon for DB2 UDB News Letter :
XpertMon for DB2 UDB Á¦Ç°À» »ç¿ëÇϽô °í°´¿©·¯ºÐ ¹× DB2 »ç¿ëÀÚ ¿©·¯ºÐ ¾È³çÇϽʴϱî?
DB2 UDB ±â¼úÁö¿ø ¹× XpertMon for DB2 UDB ±â¼úÁö¿ø ¹× ÄÁ¼³ÆÃÀ» Àü¹®À¸·Î ÇÏ´Â ¢ßITEG ÀÔ´Ï´Ù.
XpertMon for DB2 UDB´Â ÀúÈñ ȸ»ç°¡ º¸À¯ÇÑ »ó´ç¼öÀÇ ÇÁ·ÎÁ§Æ® °æÇè°ú DB2 Know-How¸¦ Á¢¸ñ½ÃŲ
Á¦Ç°À¸·Î¼ DB2 ½Ã½ºÅÛÀ» º¸´Ù ½±°í È¿°úÀûÀ¸·Î °ü¸®ÇÒ ¼ö ÀÖµµ·Ï µµ¿ÍÁÖ´Â
DB2 Àü¹® ¼º´É°ü¸® ToolÀÔ´Ï´Ù.
ÇöÀçµµ ÀúÈñ´Â °í°´¿©·¯ºÐÀÇ ¸¸Á·µµ¸¦ ³ôÀÌ°í Á¦Ç°ÀÇ ¿Ï¼ºµµ¸¦ ³ôÀ̱â À§ÇØ °è¼ÓÀûÀ¸·Î
DB2 UDB¸¦ ¿¬±¸ÇÏ°í Á¦Ç°¿¡ Àû¿ëÇÏ°í ÀÖÀ¸¸ç, ±×·¯ÇÑ ³»¿ëµéÀ» °í°´¿©·¯ºÐ°ú ³ª´©°íÀÚ
Á¤±âÀûÀÎ ´º½º·¹Å͸¦ ±âȹÇÏ°Ô µÇ¾ú½À´Ï´Ù.
´º½º·¹ÅÍ´Â DB2´Â ¹°·Ð XpertMon¿¡ À̸£±â±îÁö °í°´¿©·¯ºÐ²² ÇÊ¿äÇÑ ³»¿ë°ú ¼Ò½ÄÀ»
´Ù·ç°Ô µÉ °ÍÀÔ´Ï´Ù.
¾Æ¹«ÂÉ·Ï ÀÌ ´º½º·¹ÅÍ°¡ °í°´ ¿©·¯ºÐ²² ¸¹Àº µµ¿òÀÌ µÇ½Ã±æ ºô¸ç, Ãß°¡ÀûÀ¸·Î ÀÌ ´º½º·¹Å͸¦
Àü´ÞÇÏ°í ½ÍÀº °í°´ºÐÀÌ °è½Ã´Ù¸é ¼º¸í,À̸ÞÀÏÁÖ¼Ò,¿¬¶ôó¸¦ º¸³»ÁÖ½Ã¸é °¨»çÇÏ°Ú½À´Ï´Ù.
DB2 UDB V8 ÀÇ Infinite LOG ±â´É
- LOG Full Àå¾Ö¿Í V7 ÀÌÀü±îÁöÀÇ ÇØ°á ¹æ¹ý
- Infinite Log (¹«Çѷα×) Á¤ÀÇ ¹× ¼³Á¤ ¹æ¹ý
- ¹«ÇÑ ·Î±× Å×½ºÆ®(1) - LOG FULL ¹ß»ý
- ¹«ÇÑ ·Î±× Å×½ºÆ®(2) - ¹«ÇÑ ·Î±×·Î ÇØ°á
- XpertMon À» ÀÌ¿ëÇÏ¿© Logging ¸ð´ÏÅ͸µ
LOG FULL Àå¾Ö¿Í ÇØ°á ¹æ¹ý
ÀÌ ±ÛÀ» Àд »ç¿ëÀÚ²²¼´Â ÇѹøÂëÀº Commit ¾øÀÌ ¿À·¡ ¼öÇàµÇ´Â ´ë·®ÀÇ TransactionÀ¸·Î ÀÎÇØ
¡®LOG FULL¡¯ À̶ó´Â Àå¾Ö¸¦ °æÇèÇØ º¸¼ÌÀ» °ÍÀÔ´Ï´Ù.
DATABASE¿¡ º¯°æÀÛ¾÷ÀÌ ÀÌ·ç¾îÁú ¶§¸¶´Ù LOG°¡ ¹ß»ýµÇ´Âµ¥ ÀûÀýÇÑ ÀÛ¾÷·®À¸·Î LOG Å©±â¸¦
Á¦¾îÇÏÁö ¸øÇÑ´Ù¸é LOG FULL Àå¾Ö°¡ ¹ß»ýÇÏ°Ô µË´Ï´Ù.
´ë·®ÀÇ µ¥ÀÌÅ͸¦ ó¸®ÇØ¾ß µÇ´Âµ¥ ·Î±×Å©±â°¡ ÀÛ´Ù¸é ¿©·¯ºÐÀº ¾î¶»°Ô ÇÏ°Ú½À´Ï±î?
°¡Àå ½¬¿î ¹æ¹ýÀº LOG Å©±â¸¦ È®ÀåÇÏ´Â °ÍÀÔ´Ï´Ù. ÇÏÁö¸¸, ÀÚÁÖ »ç¿ëÇÏÁö ¾Ê´Â
ƯÁ¤ Application À» À§ÇØ LOG Å©±â¸¦ Áõ°¡½ÃÅ°´Â °ÍÀº µð½ºÅ© ³¶ºñ¿ä¼Ò°¡ µÉ ¼ö ÀÖ½À´Ï´Ù.
±×¸®°í LOG Å©±â¿¡µµ Á¦ÇÑÀÌ ÀÖ¾î °è¼Ó ´Ã·Á ÁÙ ¼öµµ ¾ø½À´Ï´Ù.
LOG¿Í °ü·ÃµÈ Á¦ÇÑ »çÇ×Àº ¾Æ·¡¿Í °°½À´Ï´Ù.
³»¿ë | V 7 (MIN / MAX) | V 8 (MIN / MAX) |
Çã¿ëµÇ´Â ÃÖ´ë 1Â÷ ·Î±× °³¼ö
| 2/128 | 2/256 |
Çã¿ëµÇ´Â ÃÖ´ë 2Â÷ ·Î±× °³¼ö | 0/126 | 0/254 |
ÃÖ´ë ·Î±× °³¼ö | 128 | 256 |
·Î±×ÃÖ´ë Å©±â | 65,535(4KB) | 262,144(4KB)
|
ÃÖ´ë ·Î±× °ø°£ Å©±â | 32GB | 256GB |
Infinite LOG (¹«ÇÑ ·Î±×) Á¤ÀÇ ¹× ¼³Á¤ ¹æ¹ý
V8¿¡¼ LOG¿Í °ü·ÃµÈ ¼º´ÉÀÌ ¸¹ÀÌ ÁÁ¾ÆÁö±ä ÇßÁö¸¸ ÀÌ·¯ÇÑ ¼³Á¤¿ª½Ã 256GBÀÇ Á¦ÇÑÀÌ ÀÖ½À´Ï´Ù.
±×·¸´Ù¸é ¸¸¾à LOG Å©±â°¡ 256GB¶ó°í Çصµ ÇØ°áµÇÁö ¾ÊÀº Long TransactionÀ» ¼öÇàÇØ¾ß µÈ´Ù¸é
¾î¶»°Ô ÇϽðڽÀ´Ï±î?
V8¿¡¼ »õ·Ó°Ô Á¦°øµÇ´Â Infinite ±â´ÉÀ» ÀÌ¿ëÇÑ´Ù¸é À§¿Í °°Àº ¹®Á¦¸¦ ÇØ°á ÇÒ ¼ö ÀÖ½À´Ï´Ù.
Infinite LOG ±â´ÉÀ» »ç¿ëÇϽ÷Á¸é ´ÙÀ½°ú °°ÀÌ ¼³Á¤ÇϽʽÿÀ.
¸ÕÀú DB CFG ±¸¼º Áß userexit ¼³Á¤ °ªÀ» ON ½ÃÅ°°í, logsecond ¼³Á¤ °ªÀ» -1·Î ¼³Á¤À» ÇÕ´Ï´Ù.
(À§ÀÇ µÎ °³ÀÇ ¼³Á¤ Áß logsecond ¼³Á¤ °ªÀ» Àû¿ëÇϱâ À§Çؼ´Â userexit °ªÀ» ¸ÕÀú Àû¿ëÇؾß
µË´Ï´Ù.)
¸í·É¹®Àº ¾Æ·¡¿Í °°½À´Ï´Ù.
- db2 update db cfg for sample using userexit on
- db2 update db cfg for sample using logsecond -1
¹«ÇÑ ·Î±× Å×½ºÆ®(1) - LOG FULL ¹ß»ý
´ÙÀ½ÀÇ ¿¹¿¡¼ ½Ã½ºÅÛÀÇ ·Î±×¿Í °ü·ÃµÈ Çã¿ëµÈ ÃÖ°íÄ¡ÀÇ ¼³Á¤ °ªÀ» º¯°æÇØ °¡¸é¼ commit¾ø´Â
Long TransactionÀ» ½ÇÇàÇؼ ¹ß»ýÇÏ´Â Çö»ó°ú Infinite LOG°¡ ¾î¶»°Ô ÀϾ´ÂÁö »ìÆ캸µµ·Ï
ÇÏ°Ú½À´Ï´Ù.
¿ì¼± Å×½ºÆ®¸¦ À§ÇØ ´ÙÀ½ Å×À̺íÀ» ÀÛ¼ºÇÏ¿´½À´Ï´Ù.
- DB¸í : SAMPLE , TABLE¸í : EMPLOYEE , °Ç¼ö : 100¸¸ °Ç ÀÌ»ó
¸ÕÀú ·Î±× file»çÀÌÁîÀÇ Å©±â¸¦ ÁÙÀÌ°í Çã¿ë °¡´ÉÇÑ ÃÖ´ëÄ¡ÀÇ primary logs¸¦ ¼³Á¤ÇÕ´Ï´Ù.
- db2 update db cfg for samle using logfilsiz 4
- db2 update db cfg for sample using logsecond 0
- db2 update db cfg for sample using logprimary 256
- db2 connect reset
- db2 connect to sample
À§¿Í ¸í·É¹®À¸·Î ÀÎÇØ ¼³Á¤ °ªÀÌ ¾î¶»°Ô º¯°æµÆ´ÂÁö È®ÀÎÇØ º¸¸é ´ÙÀ½°ú °°½À´Ï´Ù.
- db2 get db cfg for sample |grep ¡°Log¡±
Log file size(4KB) (LOGFILSIZ)=4
Number of primary log files (LOGPRIMARY)=256
Number Of secondary log files (LOGSECOND)=0
User exit for logging enabled (USEREXIT)=OFF
¿©±â¼ Àá±ñ !
XpertMon ÀÇ DBM/CFG ±â´ÉÀ» ÀÌ¿ëÇÏ¿© ¿©·¯ °³ÀÇ ÆĶó¹ÌÅ͸¦ ½±°Ô º¯°æÇÒ ¼ö ÀÖ½À´Ï´Ù.
Å×½ºÆ®¸¦ À§ÇØ ¾Æ·¡ SQLÀ» ¼öÇàÇØ º¾½Ã´Ù. ¾Æ·¡¿Í °°Àº ¿¡·¯°¡ ³ªÅ¸³¯ °ÍÀÔ´Ï´Ù.
- db2 update employee set salary=salary +1.0
SQL0964C The transaction log for the database is full. SQLSTATE=57011
À§ÀÇ ERROR°¡ ³ »óȲÀ» µÇÁý¾îº¸¸é LOG °³¼ö¸¦ MAX·Î ¼³Á¤À» ÇßÁö¸¸ ´ë·®ÀÇ µ¥ÀÌÅ͸¦
ó¸®Çϱ⿡´Â LOG Å©±â°¡ ºÎÁ·Çؼ Àå¾Ö°¡ ³ »óȲÀÔ´Ï´Ù.
¹«ÇÑ ·Î±× Å×½ºÆ®(2) - ¹«ÇÑ ·Î±×·Î ÇØ°á
ÀÚ ±×·¸´Ù¸é Infinite LOG ±â´ÉÀ» ÀÌ¿ëÇϱâ À§ÇØ ´ÙÀ½°ú °°ÀÌ ¼³Á¤ °ªÀ» º¯°æÇØ º¸½Ã±â ¹Ù¶ø´Ï´Ù.
¸ÕÀú Primary log °³¼ö¸¦ 3°³·Î µÇµ¹¸³´Ï´Ù.
- db2 update db cfg for sample using logprimary 3
±× ´ÙÀ½ infinite loggingÀ» ÀÌ¿ëÇÒ ¼ö ÀÖµµ·Ï Àû¿ëÀ» ÇϽñ⠹ٶø´Ï´Ù.
- db2 update db cfg for sample using userexit on
- db2 update db cfg for sample using logsecond -1
- db2 connect reset
< ÁÖÀÇ»çÇ× >
º» ¿¹¹®Àº Archive LoggingÀ» ÀÌ¿ëÇÏ°í ÀÖ´Â ÀüÁ¦·Î ¼³¸íÀ» ÇÏ°í ÀÖ½À´Ï´Ù.
¸¸¾à Circular LoggingÀ» »ç¿ëÇÏ°í ÀÖ´Ù¸é infiniteÀ» Àû¿ëÇϱâ Àü¿¡ Archive Logging
¹æ½ÄÀ¸·Î º¯°æÀ» ÇÑ ´ÙÀ½¿¡ ÁøÇàÀ» ÇϽñ⠹ٶø´Ï´Ù.
À§¿Í °°ÀÌ ¼³Á¤ °ªÀÌ ¹Ù²î¾ú´Ù¸é ´Ù½Ã Çѹø EMPLOYEEE Å×ÀÌºí¿¡ UPDATE ¸¦ ¼öÇàÇÏ¿© º¾´Ï´Ù.
- db2 update employee set salary=salary +1.0
DB20000I The SQL command completed successfully.
logsecond =-1 °ªÀ¸·Î Infinite LOG¸¦ Àû¿ëÇÑ »óÅÂÀ̱⠶§¹®¿¡ LOG°¡ °è¼Ó Ãß°¡·Î »ý¼ºµÇ°í
¸¸¾à Active LOG µð·ºÅ丮°¡ °¡µæÂ÷°Ô µÇ¸é USEREXIT À» ÀÌ¿ëÇÏ¿© Active LOG¸¦
ÇØ´ç µð·ºÅ丮·Î Archive ÇÏ¸é¼ ApplicationÀ» ¿Ï·á ÇÒ ¼ö ÀÖ°Ô µË´Ï´Ù.
XpertMonÀ» ÀÌ¿ëÇÏ¿© Logging ¸ð´ÏÅ͸µ
±×·³, XpertMonÀ» ÀÌ¿ëÇÏ¿© SQLÀ» ¼öÇàÇغ¸°í ¸ð´ÏÅ͸µ Çغ¸µµ·Ï ÇÏ°Ú½À´Ï´Ù.
¾î¶»°Ô ÀÛ¾÷À» ¼öÇàÁßÀÎÁö ¼¼¼Ç Component¸¦ ÀÌ¿ëÇÏ¿© ¸ð´ÏÅ͸µÇÕ´Ï´Ù.
¾Æ·¡ ȸé°ú °°ÀÌ ¼¼¼Ç ±â´É¿¡¼´Â ApplicationÀÌ »ç¿ëÇÏ°í ÀÖ´Â LOG ÀÇ ¾çÀ» ¾Ë ¼ö ÀÖ½À´Ï´Ù.
À§¿¡¼ ¼öÇàÇÑ ÇÁ·Î±×·¥Àº 72¹ø Handle °ªÀ» °¡Áö°í ÀÖÀ¸¸ç, IP °¡ 200.0.0.109 ½Ã½ºÅÛ¿¡¼ ¼öÇàÇÑ
Application ÀÔ´Ï´Ù.
ÇöÀç±îÁö »ç¿ëÇÑ CPU(ÃÊ) ´Â system CPU 0.29ÃÊ, user CPU 1.69ÃÊÀÔ´Ï´Ù.
Lock Àº 3°³¸¦ held ÇÏ°í ÀÖÀ¸¸ç, Sort ´Â ¼öÇàÇÏ°í ÀÖÁö ¾ÊÀ¸¸ç, ÇöÀç±îÁö »ç¿ëÁßÀÎ LogÀÇ ¾çÀº
20MB ÀÔ´Ï´Ù.
Á» ´õ »ó¼¼ÇÑ Á¤º¸´Â ApplicationÀ» ´õºíŬ¸¯ÇÏ¸é ¾Ë ¼ö ÀÖ½À´Ï´Ù.
ÇöÀç 75,351°ÇÀÇ Row¸¦ ÀÐ¾î¼ º¯°æÁßÀ̸ç, ÇöÀç ApplicationÀÌ ¼öÇàÁßÀÎ SQL ¹®ÀåÀ» º¼ ¼ö
ÀÖ½À´Ï´Ù.
XpertMon for DB2 UDB V2
DB »ç¾÷ºÎ
Tel : 02-2108-1458
Fax : 02-2108-1459
Mobile : 011-896-6545
E-mail :
hjlee@iteg.co.kr
URL :
http://iteg.co.kr