°ü¸® ¾È³»¼­


º¥Ä¡¸¶Å© ÇÁ·Î±×·¥ ÀÛ¼º

º¥Ä¡¸¶Å© ÇÁ·Î±×·¥À» ¼³°èÇϰí ÀÛ¼ºÇÒ ¶§¿¡´Â °í·ÁÇØ¾ß ÇÒ ¿©·¯ °¡Áö ¿ä¼Ò°¡ ÀÖ½À´Ï´Ù. ÇÁ·Î±×·¥ÀÇ ÁÖµÈ ¸ñÀûÀÌ »ç¿ëÀÚ ÀÀ¿ëÇÁ·Î±×·¥À» ¸ðÀÇ ½ÇÇèÇÏ´Â °ÍÀ̹ǷÎ, Àü¹ÝÀûÀÎ ÇÁ·Î±×·¥ÀÇ ±¸Á¶´Â ´Ù¾çÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥ Àüü¸¦ º¥Ä¡¸¶Å©·Î¼­ »ç¿ëÇϰí, ´ÜÁö ºÐ¼®µÉ 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


[ ÆäÀÌÁöÀÇ ¸Ç À§ | ÀÌÀü ÆäÀÌÁö | ´ÙÀ½ ÆäÀÌÁö | ¸ñÂ÷ | »öÀÎ ]