º¥Ä¡¸¶Å© ÇÁ·Î±×·¥À» ¼³°èÇϰí ÀÛ¼ºÇÒ ¶§¿¡´Â °í·ÁÇØ¾ß ÇÒ ¿©·¯ °¡Áö ¿ä¼Ò°¡ ÀÖ½À´Ï´Ù. ÇÁ·Î±×·¥ÀÇ ÁÖµÈ ¸ñÀûÀÌ »ç¿ëÀÚ ÀÀ¿ëÇÁ·Î±×·¥À» ¸ðÀÇ ½ÇÇèÇÏ´Â °ÍÀ̹ǷÎ, Àü¹ÝÀûÀÎ ÇÁ·Î±×·¥ÀÇ ±¸Á¶´Â ´Ù¾çÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥ Àüü¸¦ º¥Ä¡¸¶Å©·Î¼ »ç¿ëÇϰí, ´ÜÁö ºÐ¼®µÉ SQL¹®ÀÇ ½Ã°£À» ÃøÁ¤ÇÏ´Â ¼ö´Ü¸¸À» µµÀÔÇÒ ¼öµµ ÀÖ½À´Ï´Ù. Å©°Å³ª º¹ÀâÇÑ ÀÀ¿ëÇÁ·Î±×·¥¿¡ ÀÖ¾î¼, Áß¿äÇÑ ¸í·É¹®À» °¡Áö°í ÀÖ´Â ºí·ÏÀ» Æ÷ÇÔÇÏ´Â °ÍÀÌ ´õ ½ÇÁ¦ÀûÀÏ ¼öµµ ÀÖ½À´Ï´Ù.
ƯÁ¤ SQL¹®À» Å×½ºÆ®Çϱâ À§ÇÑ ¶Ç´Ù¸¥ ¹æ¹ýÀº ÀÌ ¸í·É¹®¸¸À» ÇÊ¿äÇÑ CONNECT, PREPARE, OPEN ¹× ´Ù¸¥ ¸í·É¹®°ú ½Ã°£ÃøÁ¤(timing) ¸ÞÄ¿´ÏÁò°ú ÇÔ²² º¥Ä¡¸¶Å© ÇÁ·Î±×·¥¿¡ Æ÷ÇÔ½ÃŰ´Â °ÍÀÔ´Ï´Ù.
°í·ÁÇØ¾ß ÇÒ ¶Ç´Ù¸¥ ¿äÀÎÀº »ç¿ëÇÒ º¥Ä¡¸¶Å©ÀÇ À¯ÇüÀÔ´Ï´Ù. ÇÑ °¡Áö ¹æ¹ýÀº ÀÏ·ÃÀÇ SQL¹®À» ÀÏÁ¤ ½Ã°£ °£°ÝÀ» µÎ°í ¹Ýº¹ÀûÀ¸·Î ½ÇÇà½ÃŰ´Â °ÍÀÔ´Ï´Ù. ½ÇÇàµÈ ¸í·É¹®ÀÇ ¼ö¿Í ÀÌ·¯ÇÑ ½Ã°£ °£°ÝÀÇ ºñÀ²ÀÌ ÀÀ¿ëÇÁ·Î±×·¥¿¡ ´ëÇÑ ½Ã°£´ç ó¸®À²À» ³»¾î ÁÝ´Ï´Ù. ¶Ç´Ù¸¥ ¹æ¹ýÀº °³º°ÀûÀÎ SQL¹®À» ½ÇÇà½ÃŰ´Â µ¥ ÇÊ¿äÇÑ ½Ã°£À» ÃøÁ¤ÇÏ´Â °ÍÀÔ´Ï´Ù.
º¥Ä¡¸¶Å© ÇÁ·Î±×·¥ÀÇ À¯Çü¿¡ °ü°è¾øÀÌ, °³º°ÀûÀÎ SQL¹®À̰ųª ÀÀ¿ëÇÁ·Î±×·¥ Àüü¸¦ ´ë»óÀ¸·Î Çϰųª °æ°ú ½Ã°£À» »êÃâÇϱâ À§Çؼ´Â È¿À²ÀûÀÎ ½Ã°£ ÃøÁ¤ ½Ã½ºÅÛÀÌ ÇÊ¿äÇÕ´Ï´Ù. °¢°¢ÀÇ SQL¹®ÀÌ °³º°ÀûÀ¸·Î ½ÇÇàµÉ ¼ö ÀÖ´Â ÀÀ¿ëÇÁ·Î±×·¥ ȯ°æÀ» ¸¸µé±â À§Çؼ´Â CONNECT, PREPARE ¹× COMMIT¹®¿¡ ¼Ò¿äµÇ´Â ½Ã°£À» °í·ÁÇÏ´Â °ÍÀÌ Áß¿äÇÕ´Ï´Ù. ±×·¯³ª, ¸¹Àº ¼öÀÇ ´Ù¾çÇÑ ¸í·É¹®À» Å×½ºÆ®ÇÏ´Â ÇÁ·Î±×·¥ÀÇ °æ¿ì¿¡´Â ¾Æ¸¶µµ ´Ü ÇϳªÀÇ CONNECT³ª COMMIT¹®ÀÌ ÇÊ¿äÇÒ °ÍÀ̹ǷÎ, °¢°¢ÀÇ ¸í·É¹® ¼öÇà¿¡ ¼Ò¿äµÇ´Â ½Ã°£À» ÃøÁ¤ÇÏ´Â °ÍÀÌ ´õ¿í Áß¿äÇÕ´Ï´Ù.
°¢ Á¶È¸ÀÇ °æ°ú ½Ã°£ÀÌ ¼º´ÉÀ» ºÐ¼®ÇÏ´Â µ¥ Áß¿äÇÑ ¿ä¼ÒÀÎ ¹Ý¸é¿¡, º´¸ñÇö»óÀ» ¹Ýµå½Ã ¹àÇô³½´Ù°í ÇÒ ¼ö´Â ¾ø½À´Ï´Ù. ¿¹¸¦ µé¾î, CPUÀÇ »ç¿ë »óȲ, Àá±Ý »óÅ ¹× ¹öÆÛ Ç® ÀÔÃâ·Â µî¿¡ ´ëÇÑ Á¤º¸¸¦ ÅëÇØ ÀÀ¿ëÇÁ·Î±×·¥ÀÌ CPUÀÇ Àüü ¿ë·®À» »ç¿ëÇϱ⠺¸´Ù´Â ÀÔÃâ·Â À§ÁÖÀÓÀ» ¾Ë ¼ö ÀÖ½À´Ï´Ù. º¥Ä¡¸¶Å© ÇÁ·Î±×·¥Àº ÇÊ¿äÇÑ °æ¿ì ´õ ¼¼ºÎÀûÀÎ ºÐ¼®À» À§ÇØ ÀÌ·¯ÇÑ Á¾·ùÀÇ µ¥ÀÌÅ͸¦ ¾òÀ» ¼ö ÀÖµµ·Ï ÇØÁÖ¾î¾ß ÇÕ´Ï´Ù.
¸ðµç ÀÀ¿ëÇÁ·Î±×·¥¿¡¼, Á¶È¸·ÎºÎÅÍ °Ë»öµÈ ¸ðµç °á°ú¸¦ Ãâ·Â ÀåÄ¡·Î º¸³¾ ÇÊ¿ä´Â ¾ø½À´Ï´Ù. ¿¹¸¦ µé¾î, ¾î¶² ÀÀ¿ëÇÁ·Î±×·¥¿¡¼´Â °á°ú Àüü¸¦ ´Ù¸¥ ÇÁ·Î±×·¥ÀÇ ÀÔ·ÂÀ¸·Î »ç¿ëÇϱ⵵ ÇÕ´Ï´Ù(Áï, ¾î¶°ÇÑ Ç൵ Ãâ·ÂµÇÁö ¾Ê½À´Ï´Ù). ȸéÀ¸·Î Ãâ·ÂÇϱâ À§ÇØ µ¥ÀÌÅ͸¦ Çü½ÄÈÇÏ´Â °ÍÀº ÀϹÝÀûÀ¸·Î CPUÀÇ ºñ¿ëÀÌ ¸¹ÀÌ µé±â ¶§¹®¿¡ »ç¿ëÀÚÀÇ Çʿ信 ºÎÇÕµÇÁö ¾ÊÀ» ¼öµµ ÀÖ½À´Ï´Ù. Á¤È®ÇÑ ½Ã¹Ä·¹À̼ÇÀ» Á¦°øÇϱâ À§Çؼ´Â, º¥Ä¡¸¶Å© ÇÁ·Î±×·¥ÀÌ Æ¯Á¤ ÀÀ¿ëÇÁ·Î±×·¥À» ¾î¶»°Ô ÇÏ´ÂÁö ³ªÅ¸³»¾î¾ß ÇÕ´Ï´Ù. °á°ú°¡ Ãâ·Â ÀåÄ¡·Î º¸³»Áö¸é ºñÈ¿À²ÀûÀÎ Çü½ÄÈÇÏ´Â µ¥ CPU ½Ã°£ ´ëºÎºÐÀ» »ç¿ëÇÏ°Ô µÇ¹Ç·Î, SQL¹® ÀÚü¸¦ ó¸®ÇÏ´Â ½ÇÁ¦ ¼º´ÉÀÌ Á¦´ë·Î ³ªÅ¸³ªÁö ¾ÊÀ» ¼ö ÀÖ½À´Ï´Ù.
db2batch º¥Ä¡¸¶Å© µµ±¸: º¥Ä¡¸¶Å© µµ±¸(db2batch)°¡ »ç¿ëÀÚÀÇ ÀνºÅϽº sqllib µð·ºÅ丮ÀÇ bin ¼ºêµð·ºÅ丮¿¡ Á¦°øµË´Ï´Ù. ÀÌ µµ±¸´Â º¥Ä¡¸¶Å© ÇÁ·Î±×·¥À» ÀÛ¼ºÇÏ´Â °Í¿¡ ´ëÇØ¼ À§¿¡¼ ¾ð±ÞÇÑ ¸¹Àº »çÇ×À» °í·ÁÇϰí ÀÖ½À´Ï´Ù. ÀÌ µµ±¸´Â SQL¹®À» ÀÏ¹Ý ÆÄÀÏ ¶Ç´Â Ç¥ÁØ ÀÔ·Â ÀåÄ¡·ÎºÎÅÍ Àоîµé¿© ÇØ´ç ¸í·É¹®À» ±×¶§±×¶§¸¶´Ù ¼³¸íÇϰí ÁغñÇÏ¿© °á°ú¸¦ ¸®ÅÏÇÕ´Ï´Ù. Ãâ·Â ÀåÄ¡·Î º¸³»Áú °Ë»ö °á°úÀÇ Çà ¼ö»Ó¸¸ ¾Æ´Ï¶ó, °Ë»ö °á°ú Å©±â ÀÚü¸¦ Á¶Á¤ÇÒ ¼ö ÀÖ´Â À¯¿¬¼ºµµ Á¦°øÇÕ´Ï´Ù.
¶ÇÇÑ °æ°ú ½Ã°£, CPU ¹× ¹öÆÛ Ç® »ç¿ë »óȲ, Àá±Ý ¹× ±âŸÀÇ µ¥ÀÌÅͺ£À̽º ¸ð´ÏÅͷκÎÅÍ ¾òÀ» ¼ö ÀÖ´Â ¼º´É¿¡ °ü·ÃµÈ Åë°è Á¤º¸ÀÇ ·¹º§À» ¸í½ÃÇÒ ¼öµµ ÀÖ½À´Ï´Ù. SQL¹® ¼¼Æ®ÀÇ ½Ã°£À» Àç´Â ÁßÀ̶ó¸é, db2batch°¡ ¼º´É °á°ú¸¦ ¿ä¾àÇϰí, »ê¼ú Æò±Õ ¹× ±âÇÏ Æò±Õ(geometric mean)µµ Á¦°øÇÕ´Ï´Ù. ½ÇÇà ±¸¹® ¹× ¿É¼Ç¿¡ ´ëÇØ¼´Â ¸í·ÉÇà¿¡¼ db2batch -h¸¦ ÀÔ·ÂÇϽʽÿÀ.
db2batch¿¡ ´ëÇØ¼´Â Command Reference ¸Å´º¾óÀ» ÂüÁ¶ÇÒ ¼öµµ ÀÖ½À´Ï´Ù.
´ÙÀ½Àº db2batch°¡ db2batch.sql ÀÔ·Â ÆÄÀϰú ÇÔ²² »ç¿ëµÉ ¼ö ÀÖ´Â ¹æ¹ý¿¡ ´ëÇÑ ¿¹ÀÔ´Ï´Ù.
±×¸² 97. »ùÇà º¥Ä¡¸¶Å© ÀÔ·Â ÆÄÀÏ: db2batch.sql
-- db2batch.sql -- ------------ --#SET PERF_DETAIL 3 ROWS_OUT 5 -- This query lists employees, the name of their department -- and the number of activities to which they are assigned for -- employees who are assigned to more than one activity less than -- full-time. --#COMMENT Query 1 select lastname, firstnme, deptname, count(*) as num_act from employee, department, emp_act where employee.workdept = department.deptno and employee.empno = emp_act.empno and emp_act.emptime < 1 group by lastname, firstnme, deptname having count(*) > 2; --#SET PERF_DETAIL 1 ROWS_OUT 5 --#COMMENT Query 2 select lastname, firstnme, deptname, count(*) as num_act from employee, department, emp_act where employee.workdept = department.deptno and employee.empno = emp_act.empno and emp_act.emptime < 1 group by lastname, firstnme, deptname having count(*) <= 2; |
º¥Ä¡¸¶Å© µµ±¸¸¦ ´ÙÀ½°ú °°ÀÌ È£ÃâÇϸé,
db2batch -d sample -f db2batch.sql
´ÙÀ½°ú °°Àº Ãâ·ÂÀÌ ³ª¿É´Ï´Ù.
±×¸² 98. db2batch·ÎºÎÅÍÀÇ »ùÇà Ãâ·Â(1ºÎ)
--#SET PERF_DETAIL 3 ROWS_OUT 5 Query 1 Statement number: 1 select lastname, firstnme, deptname, count(*) as num_act from employee, department, emp_act where employee.workdept = department.deptno and employee.empno = emp_act.empno and emp_act.emptime < 1 group by lastname, firstnme, deptname having count(*) > 2 |
±×¸² 99. db2batch·ÎºÎÅÍÀÇ Ãâ·Â ¿¹(1 ºÎ)
LASTNAME FIRSTNME DEPTNAME NUM_ACT
---------------------------------------------------------------------------
JEFFERSON JAMES ADMINISTRATION SYSTEMS 3
JOHNSON SYBIL ADMINISTRATION SYSTEMS 4
NICHOLLS HEATHER INFORMATION CENTER 4
PEREZ MARIA ADMINISTRATION SYSTEMS 4
SMITH DANIEL ADMINISTRATION SYSTEMS 7
Number of rows retrieved is: 5
Number of rows sent to output is: 5
Elapsed Time is: 0.074 seconds
Locks held currently = 0
Lock escalations = 0
Total sorts = 5
Total sort time (ms) = 0
Sort overflows = 0
Buffer pool data logical reads = 13
Buffer pool data physical reads = 5
Buffer pool data writes = 0
Buffer pool index logical reads = 3
Buffer pool index physical reads = 0
Buffer pool index writes = 0
Total buffer pool read time (ms) = 23
Total buffer pool write time (ms) = 0
Asynchronous pool data page reads = 0
Asynchronous pool data page writes = 0
Asynchronous pool index page reads = 0
Asynchronous pool index page writes = 0
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 0
Asynchronous read requests = 0
LSN Gap cleaner triggers = 0
Dirty page steal cleaner triggers = 0
Dirty page threshold cleaner triggers = 0
Direct reads = 8
Direct writes = 0
Direct read requests = 4
Direct write requests = 0
Direct read elapsed time (ms) = 0
Direct write elapsed time (ms) = 0
Rows selected = 5
Log pages read = 0
Log pages written = 0
Catalog cache lookups = 3
Catalog cache inserts = 3
Buffer pool data pages copied to ext storage = 0
Buffer pool index pages copied to ext storage = 0
Buffer pool data pages copied from ext storage = 0
Buffer pool index pages copied from ext storage = 0
Total Agent CPU Time (seconds) = 0.02
Post threshold sorts = 0
Piped sorts requested = 5
Piped sorts accepted = 5
±×¸² 100. db2batch·ÎºÎÅÍÀÇ »ùÇà Ãâ·Â(2ºÎ)
--#SET PERF_DETAIL 1 ROWS_OUT 5 Query 2 Statement number: 2 select lastname, firstnme, deptname, count(*) as num_act from employee, department, emp_act where employee.workdept = department.deptno and employee.empno = emp_act.empno and emp_act.emptime < 1 group by lastname, firstnme, deptname having count(*) <= 2 LASTNAME FIRSTNME DEPTNAME NUM_ACT --------------------------------------------------------------------------- GEYER JOHN SUPPORT SERVICES 2 GOUNOT JASON SOFTWARE SUPPORT 2 HAAS CHRISTINE SPIFFY COMPUTER SERVICE DIV. 2 JONES WILLIAM MANUFACTURING SYSTEMS 2 KWAN SALLY INFORMATION CENTER 2 Number of rows retrieved is: 8 Number of rows sent to output is: 5 Elapsed Time is: 0.037 seconds Summary of Results ================== Elapsed Agent CPU Rows Rows Statement # Time (s) Time (s) Fetched Printed 1 0.074 0.020 5 5 2 0.037 Not Collected 8 5 Arith. mean 0.055 Geom. mean 0.052 |
À§ÀÇ »ùÇà Ãâ·Â¿¡´Â µ¥ÀÌÅͺ£À̽º ½Ã½ºÅÛ ¸ð´ÏÅͰ¡ ¸®ÅÏÇÑ Æ¯Á¤ µ¥ÀÌÅÍ ¿ä¼Òµµ Æ÷ÇԵǾî ÀÖ½À´Ï´Ù. ÀÌ·¯ÇÑ »çÇ× ¹× ¸ð´ÏÅÍ ¿ä¼Ò¿¡ ´ëÇØ¼´Â ½Ã½ºÅÛ ¸ð´ÏÅÍ ¾È³» ¹× ÂüÁ¶¼ ¸Å´º¾óÀ» ÂüÁ¶ÇϽʽÿÀ.
´ÙÀ½ ¿¹(UNIX¿¡¼)¿¡¼´Â, ¿ä¾à Å×ÀÌºí¸¸ ÀÛ¼ºµË´Ï´Ù.
db2batch -d sample -f db2batch.sql -r /dev/null,
-r ¿É¼ÇÀ» »ç¿ëÇϸé, outfile1ÀÌ /dev/null·Î ´ëüµÇ°í outfile2(¿ä¾à Å×ÀÌºí¸¸ Æ÷ÇÔ)´Â ºñ°Ô µÇ¹Ç·Î db2batch´Â Ãâ·ÂÀ» ȸéÀ¸·Î º¸³À´Ï´Ù.
±×¸² 101. db2batch·ÎºÎÅÍÀÇ »ùÇà Ãâ·Â -- ¿ä¾à Å×ÀÌºí¸¸
Summary of Results ================== Elapsed Agent CPU Rows Rows Statement # Time (s) Time (s) Fetched Printed 1 0.074 0.020 5 5 2 0.037 Not Collected 8 5 Arith. mean 0.055 Geom. mean 0.052 |
ÀÌ º¥Ä¡¸¶Å· µµ±¸¿¡´Â CLI ¿É¼Çµµ ÀÖ½À´Ï´Ù. ÀÌ ¿É¼ÇÀ¸·Î, ij½¬ Å©±â¸¦ ÁöÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù. ´ÙÀ½ ¿¹¿¡¼, db2batch´Â ¸í·É¹® 30ÀÇ Ä³½¬ Å©±â¸¦ °¡Áø CLI ¸ðµå¿¡¼ ¼öÇàµË´Ï´Ù.
db2batch -d sample -f db2batch.sql -cli 30