ÀÀ¿ëÇÁ·Î±×·¥ °³¹ß ¾È³»¼­


¼º´É Çâ»ó

ÆÄƼ¼ÇµÈ ȯ°æÀÌ Á¦°øÇÏ´Â ¼º´É»óÀÇ ÀÌÁ¡À» Ȱ¿ëÇÏ·Á¸é, Ưº°ÇÑ ÇÁ·Î±×·¡¹Ö ±â¹ý »ç¿ëÀ» °í·ÁÇØ¾ß ÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, »ç¿ëÀÚÀÇ ÀÀ¿ëÇÁ·Î±×·¥ÀÌ µÑ ÀÌ»óÀÇ µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥ ±¸È¹À¸·ÎºÎÅÍ DB2 µ¥ÀÌÅÍ¿¡ ¾×¼¼½ºÇÏ´Â °æ¿ì, ±× ¾È¿¡ µé¾î ÀÖ´Â Á¤º¸¸¦ °í·ÁÇØ¾ß ÇÕ´Ï´Ù. ÆÄƼ¼ÇµÈ ȯ°æ¿¡ ´ëÇÑ °³¿ä´Â °ü¸® ¾È³»¼­ ¹× SQL ÂüÁ¶¼­ Ã¥À» ÂüÁ¶ÇϽʽÿÀ.

FOR READ ONLY Ä¿¼­ »ç¿ë

Àб⸸À» À§ÇÑ Ä¿¼­¸¦ ¼±¾ðÇÏ·Á´Â °æ¿ì, OPEN CURSOR ¼±¾ð¿¡ FOR READ ONLY ¶Ç´Â FOR FETCH ONLY¸¦ Æ÷ÇÔ½ÃŰ½Ê½Ã¿À. FOR READ ONLY¿Í FOR FETCH ONLY´Â µ¿ÀÏÇÑ ¸í·É¹®ÀÔ´Ï´Ù. FOR READ ONLY Ä¿¼­¸¦ »ç¿ëÇÏ¿© Á¶Á¤ÀÚ ÆÄƼ¼ÇÀº ÇÑ ¹ø¿¡ ¿©·¯ ÇàÀ» °Ë»öÇÔÀ¸·Î½á ÈÄ¼Ó FETCH¹®ÀÇ ¼º´ÉÀ» Å©°Ô Çâ»ó½Ãų ¼ö ÀÖ½À´Ï´Ù. ¸í½ÃÀûÀ¸·Î Ä¿¼­¸¦ FOR READ ONLY·Î ¼±¾ðÇÏÁö ¾ÊÀ¸¸é, Á¶Á¤ÀÚ ÆÄƼ¼ÇÀº À̵éÀ» °»½Å °¡´ÉÇÑ Ä¿¼­·Î Ãë±ÞÇÕ´Ï´Ù. °»½Å °¡´ÉÇÑ Ä¿¼­´Â Á¶Á¤ÀÚ ÆÄƼ¼ÇÀÌ FETCH ÇÑ ¹ø¿¡ ÇÑ ÇุÀ» °Ë»öÇϵµ·Ï Çϱ⠶§¹®¿¡ ºñ¿ë ¼Ò¸ð°¡ Å®´Ï´Ù.

¹æÇâÀÌ ÁöÁ¤µÈ DSS¿Í Áö¿ª ¹ÙÀÌÆÐ½º »ç¿ë

¿Â¶óÀÎ Æ®·£Àè¼Ç ó¸® ÀÀ¿ëÇÁ·Î±×·¥À» ÃÖÀûÈ­Çϱâ À§ÇØ, »ç¿ëÀÚ´Â ¸ðµç µ¥ÀÌÅÍ ÆÄƼ¼Ç¿¡¼­ÀÇ Ã³¸®¸¦ ¿ä±¸ÇÏ´Â ´Ü¼øÇÑ SQL¹®À» ÇÇÇÏ·ÁÇÒ ¼ö ÀÖ½À´Ï´Ù. »ç¿ëÀÚ´Â SQL¹®ÀÌ ÇϳªÀÇ ÆÄƼ¼Ç¿¡¼­ µ¥ÀÌÅ͸¦ °Ë»öÇÒ ¼ö ÀÖµµ·Ï ÀÀ¿ëÇÁ·Î±×·¥À» ¼³°èÇØ¾ß ÇÕ´Ï´Ù. ÀÌ ±â¹ýÀº Á¶Á¤ÀÚ ÆÄƼ¼ÇÀÌ ¿¬°üµÈ ÆÄƼ¼Ç Çϳª ¶Ç´Â ¸ðµÎ¿Í Åë½ÅÇϴµ¥ ¼Ò¸ðµÇ´Â ºñ¿ëÀ» ÁÙÀÏ ¼ö ÀÖ½À´Ï´Ù.

¹æÇâÀÌ ÁöÁ¤µÈ DSS

ºÐ»ê ÇÏÀ§ ¼½¼Ç(DSS)´Â ÇÏÀ§ ¼½¼ÇÀ» º´·Ä Á¶È¸¿¡ ´ëÇØ ¸î °¡Áö ÀÛ¾÷À» ¼öÇàÇØ¾ß ÇÏ´Â µ¥ÀÌÅͺ£À̽º ÆÄƼ¼ÇÀ¸·Î º¸³»´Â Á¶Ä¡ÀÔ´Ï´Ù. ÀÌ´Â ¶ÇÇÑ ÇÏÀ§ ¼½¼ÇÀ» OLTP ȯ°æ¿¡ ÀÖ´Â º¯¼öÀÇ °ª°ú °°Àº ƯÁ¤ °ªÀ» È£ÃâÇÏ¿© ½ÃÀÛÇÏ´Â ¹æ¹ýÀ» ¼³¸íÇÕ´Ï´Ù. ¹æÇâÀÌ ÁöÁ¤µÈ DSS´Â Á¶È¸¸¦ ´ÜÀÏ ÆÄƼ¼ÇÀ¸·Î ÁöÁ¤Çϱâ À§ÇØ Å×ÀÌºí ÆÄÆ¼¼Ç ۸¦ »ç¿ëÇÕ´Ï´Ù. »ç¿ëÀÚ ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ ÀÌ·¯ÇÑ À¯ÇüÀÇ Á¶È¸¸¦ »ç¿ëÇÏ¿© ¸ðµç ³ëµå·Î Á¶È¸ ºê·Îµåij½ºÆ®¸¦ ÇÏ´Â µ¥ ÇÊ¿äÇÑ Á¶Á¤ÀÚ ÆÄƼ¼ÇÀÇ ¿À¹öÇìµå¸¦ ÇÇÇϽʽÿÀ.

¹æÇâÀÌ ÁöÁ¤µÈ DSS¸¦ Ȱ¿ëÇÒ ¼ö ÀÖ´Â ¿¹Á¦ SELECT¹®ÀÇ ÀϺδ ´ÙÀ½°ú °°½À´Ï´Ù.

     SELECT ... FROM t1
       WHERE PARTKEY=:hostvar

Á¶Á¤ÀÚ ÆÄƼ¼ÇÀÌ Á¶È¸¸¦ ¼ö½ÅÇϸé, À̰ÍÀº ¾î¶² ÆÄƼ¼ÇÀÌ :hostvarÀÇ µ¥ÀÌÅÍ ºÎ¼Ó ÁýÇÕÀ» °¡Áö°í ÀÖ´ÂÁö¸¦ ÆÇº°Çϰí, Á¶È¸¸¦ ÇØ´ç ÆÄƼ¼ÇÀ¸·Î ÁöÁ¤ÇÕ´Ï´Ù.

¹æÇâÀÌ ÁöÁ¤µÈ DSS¸¦ »ç¿ëÇÏ¿© ÀÀ¿ëÇÁ·Î±×·¥À» ÃÖÀûÈ­ÇÏ·Á¸é, º¹ÀâÇÑ Á¶È¸¸¦ ¿©·¯°³ÀÇ ´Ü¼øÇÑ Á¶È¸·Î ³ª´©½Ê½Ã¿À. ¿¹¸¦ µé¾î, ´ÙÀ½ Á¶È¸¿¡¼­ Á¶Á¤ÀÚ ÆÄƼ¼ÇÀº ÆÄƼ¼Ç ۸¦ ¿©·¯ °ª°ú ´ëÀÀÇÕ´Ï´Ù. Á¶È¸¸¦ ¸¸Á·½ÃŰ´Â µ¥ÀÌÅͰ¡ ¿©·¯ ÆÄƼ¼Ç¿¡ Á¸ÀçÇϹǷÎ, Á¶Á¤ÀÚ ÆÄƼ¼ÇÀº ÀÌ Á¶È¸¸¦ ¸ðµç ÆÄƼ¼ÇÀ¸·Î ºê·Îµåij½ºÆ®ÇÕ´Ï´Ù.

     SELECT ... FROM t1
       WHERE PARTKEY IN (:hostvar1, :hostvar2)

´ë½Å ÀÌ Á¶È¸¸¦ ÇϳªÀÇ È£½ºÆ® º¯¼ö¸¦ °®´Â ¿©·¯°³ÀÇ SELECT¹®À¸·Î ³ª´©°Å³ª UNIONÀ» °®´Â ÇϳªÀÇ SELECT¹®À» »ç¿ëÇÏ¿© °°Àº °á°ú¸¦ ¾òÀ¸½Ê½Ã¿À. Á¶Á¤ÀÚ ÆÄƼ¼ÇÀº ´õ ´Ü¼øÇÑ SELECT¹®À» Ȱ¿ëÇÏ¿© ÇÊ¿äÇÑ ÆÄƼ¼Ç°ú Åë½ÅÇϱâ À§ÇØ ¹æÇâÀÌ ÁöÁ¤µÈ DSS¸¦ »ç¿ëÇÕ´Ï´Ù. ÃÖÀûÈ­µÈ Á¶È¸´Â ´ÙÀ½°ú °°½À´Ï´Ù.

     SELECT ... AS res1 FROM t1
       WHERE PARTKEY=:hostvar1
       UNION
     SELECT ... AS res2 FROM t1
       WHERE PARTKEY=:hostvar2

À§ÀÇ ±â¹ýÀº UNIONÀÇ selectÀÇ ¼ö°¡ ÆÄƼ¼Ç ¼öº¸´Ù ÈξÀ ÀûÀ» ¶§¿¡¸¸ ¼º´ÉÀ» Çâ»ó½Ãų ¼ö ÀÖ½À´Ï´Ù.

Áö¿ª ¹ÙÀÌÆÐ½º »ç¿ë

¹æÇâÀÌ ÁöÁ¤µÈ DSS Á¶È¸ÀÇ Æ¯º°ÇÑ ÇüŰ¡ Á¶Á¤ÀÚ ÆÄƼ¼Ç¿¡¸¸ ÀúÀåµÇ¾î ÀÖ´Â µ¥ÀÌÅÍ¿¡ ¾×¼¼½ºÇÕ´Ï´Ù. Á¶Á¤ÀÚ ÆÄƼ¼ÇÀÌ ´Ù¸¥ ÆÄƼ¼Ç°ú Åë½ÅÇÒ Çʿ䰡 ¾øÀÌ Á¶È¸¸¦ ¿Ï·áÇϱ⠶§¹®¿¡ À̰ÍÀ» Áö¿ª ¹ÙÀÌÆÐ½º¶ó°í ÇÕ´Ï´Ù.

Áö¿ª ¹ÙÀÌÆÐ½º´Â °¡´ÉÇÒ ¶§¸¶´Ù ÀÚµ¿À¸·Î ÀÛµ¿°¡´ÉÇØÁö´Âµ¥, ÇØ´ç Æ®·£Àè¼Ç¿¡ ´ëÇÑ µ¥ÀÌÅͰ¡ µé¾î ÀÖ´Â ÆÄƼ¼ÇÀ¸·Î Æ®·£Àè¼ÇÀ» ¶ó¿ìÆÃÇÔÀ¸·Î½á ±× »ç¿ëÀ» Áõ°¡½Ãų ¼ö ÀÖ½À´Ï´Ù. À̸¦ ¼öÇàÇÏ´Â ÇÑ °¡Áö ±â¹ýÀº ¿ø°Ý Ŭ¶óÀÌ¾ðÆ®°¡ °¢ ÆÄƼ¼ÇÀ¸·ÎÀÇ ¿¬°áÀ» À¯Áöº¸¼öÇϵµ·Ï ÇÏ´Â °ÍÀÔ´Ï´Ù. ±×·¯¸é Æ®·£Àè¼ÇÀº ÀÔ·Â ÆÄƼ¼Ç Ű¿¡ ±Ù°ÅÇÏ¿© ¿Ã¹Ù¸¥ ¿¬°áÀ» »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. ¶Ç ´Ù¸¥ ±â¹ýÀº ÆÄƼ¼Çº°·Î Æ®·£Àè¼ÇÀ» ±×·ìÈ­Çϰí, °¢ ÆÄƼ¼Ç¿¡ º°µµÀÇ ÀÀ¿ëÇÁ·Î±×·¥ ¼­¹ö¸¦ µÎ´Â °ÍÀÔ´Ï´Ù.

Æ®·£Àè¼Ç µ¥ÀÌÅͰ¡ »óÁÖÇÏ´Â ÆÄƼ¼Ç ¼ö¸¦ ÆÇº°ÇÏ·Á¸é, sqlugrpn API(Çà ÆÄÆ¼¼Ç ¼ö ±¸Çϱâ)¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌ API¸¦ »ç¿ëÇÏ¿© ÀÀ¿ëÇÁ·Î±×·¥Àº ÆÄƼ¼Ç ۰¡ ÁÖ¾îÁ³À» ¶§, ÇàÀÇ ÆÄƼ¼Ç ¼ö¸¦ È¿À²ÀûÀ¸·Î °è»êÇÒ ¼ö ÀÖ½À´Ï´Ù. sqlugrpn API¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ Á¤º¸´Â Administrative API Reference Ã¥À» ÂüÁ¶ÇϽʽÿÀ.

¶Ç ´Ù¸¥ ¹æ¹ýÀº db2atld À¯Æ¿¸®Æ¼¸¦ »ç¿ëÇÏ¿© ÆÄƼ¼Ç ¹øÈ£º°·Î ÀÔ·Â µ¥ÀÌÅ͸¦ ³ª´©°í °¢ ÆÄƼ¼Ç¿¡ ´ëÇÏ¿© ÀÀ¿ëÇÁ·Î±×·¥ »çº»À» ¼öÇàÇÏ´Â °ÍÀÔ´Ï´Ù. db2atld À¯Æ¿¸®Æ¼¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ Á¤º¸´Â Command Reference Ã¥À» ÂüÁ¶ÇϽʽÿÀ.

¹öÆÛ »ðÀÔ »ç¿ë

¹öÆÛ »ðÀÔÀº »ðÀÔµÈ ÇàÀ» ¹öÆÛ¿¡ ³Ö±â À§ÇØ Å×ÀÌºí ´ë±âÇà·ÄÀ» Ȱ¿ëÇÏ´Â »ðÀÔ ¸í·É¹®À¸·Î, À̰ÍÀ» ÅëÇØ ¼º´ÉÀÌ Å©°Ô Çâ»óµÉ ¼ö ÀÖ½À´Ï´Ù. ¹öÆÛ »ðÀÔÀ» »ç¿ëÇÏ¿© ÀÀ¿ëÇÁ·Î±×·¥Àº ÁغñµÇ°Å³ª INSERT BUF ¿É¼Ç°ú ¹ÙÀεåµÇ¾î¾ß ÇÕ´Ï´Ù.

¹öÆÛ »ðÀÔ °á°ú »ðÀÔÀ» ¼öÇàÇÏ´Â ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ ±Ùº»ÀûÀ¸·Î ¼º´ÉÀÌ Çâ»óµÉ ¼ö ÀÖ½À´Ï´Ù. ÀϹÝÀûÀ¸·Î ÇÑ ·çÇÁ ³»¿¡¼­ ¸¹Àº ÇàÀ» »ðÀÔ½Ã۱â À§ÇØ ÇϳªÀÇ »ðÀÔ ¸í·É¹®ÀÌ »ç¿ëµÇ°í ´Ù¸¥ µ¥ÀÌÅͺ£À̽º ¼öÁ¤ ¸í·É¹®ÀÌ »ç¿ëµÇÁö ¾Ê°í, µ¥ÀÌÅÍ ¼Ò½º°¡ INSERT¹®ÀÇ VALUESÀýÀÎ ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ ¹öÆÛ »ðÀÔÀ» »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀϹÝÀûÀ¸·Î INSERT¹®Àº ¿¬¼ÓµÇ´Â ·çÇÁÀÇ ½ÇÇà Áß¿¡ ±× °ªÀÌ ¹Ù²î´Â Çϳª ÀÌ»óÀÇ È£½ºÆ® º¯¼ö¸¦ ÂüÁ¶ÇÕ´Ï´Ù. VALUESÀýÀº ÇϳªÀÇ Çà ¶Ç´Â º¹¼ö ÇàÀ» ÁöÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù.

ÀϹÝÀûÀÎ °áÁ¤ Áö¿ø ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­´Â »õ·Î¿î µ¥ÀÌÅÍÀÇ ·Îµå ¹× ÁÖ±âÀû »ðÀÔÀ» ÇÊ¿ä·Î ÇÕ´Ï´Ù. ÀÌ µ¥ÀÌÅÍ´Â ¼ö¹é, ¼öõ ÇàÀ¸·Î ±¸¼ºµÇ¾úÀ» ¼ö ÀÖ½À´Ï´Ù. Å×À̺íÀ» ·ÎµåÇÒ¶§ ¹öÆÛ »ðÀÔÀ» »ç¿ëÇϱâ À§ÇØ ÀÀ¿ëÇÁ·Î±×·¥À» ÁغñÇÏ°í ¹ÙÀεåÇÒ ¼ö ÀÖ½À´Ï´Ù.

ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ¹öÆÛ »ðÀÔÀ» »ç¿ëÇϵµ·Ï ÇÏ·Á¸é, PREP ¸í·ÉÀ» »ç¿ëÇÏ¿© ÀÀ¿ëÇÁ·Î±×·¥ ¼Ò½º ÆÄÀÏÀ» ó¸®Çϰųª °á°ú ¹ÙÀÎµå ÆÄÀÏ¿¡¼­ BIND ¸í·ÉÀ» »ç¿ëÇϽʽÿÀ. µÎ °¡Áö °æ¿ì ¸ðµÎ¿¡¼­ INSERT BUF ¿É¼ÇÀ» ÁöÁ¤ÇØ¾ß ÇÕ´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥ ¹ÙÀεù¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ Á¤º¸´Â ¹ÙÀεùÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ. ÀÀ¿ëÇÁ·Î±×·¥ Áغñ¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ Á¤º¸´Â ¼Ò½º ÆÄÀÏ ÀÛ¼º ¹× ÁغñÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
ÁÖ:¹öÆÛ »ðÀԽà ´ÙÀ½°ú °°Àº ´Ü°è°¡ ¼öÇàµË´Ï´Ù.

  1. µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥ÀÌ Å×À̺íÀÌ »óÁÖÇÏ´Â °¢ ³ëµå¿¡ ´ëÇÏ¿© 4 KB ¹öÆÛ¸¦ ¿±´Ï´Ù.
  2. ÀÀ¿ëÇÁ·Î±×·¥¿¡ ÀÇÇØ ¹ßÇàµÈ VALUESÀýÀ» °®´Â INSERT¹®À» »ç¿ëÇÏ¿© ÇàÀÌ ÀûÀýÇÑ ¹öÆÛ¿¡ µé¾î°¥ ¼ö ÀÖ°Ô ÇÕ´Ï´Ù.
  3. µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº ÀÀ¿ëÇÁ·Î±×·¥¿¡ Á¦¾î¸¦ ¸®ÅÏÇÕ´Ï´Ù.
  4. ¹öÆÛ°¡ °¡µæ Â÷°Å³ª ÀϺθ¸ Âù ¹öÆÛÀÇ ÇàÀ» ¼Û½ÅÇϵµ·Ï ÇÏ´Â À̺¥Æ®°¡ ¹ß»ýÇÑ °æ¿ì, ¹öÆÛÀÇ ÇàÀÌ ÆÄƼ¼ÇÀ¸·Î ¼Û½ÅµË´Ï´Ù. ÀϺΠÂù ¹öÆÛ´Â ´ÙÀ½ Áß Çϳª°¡ ¹ß»ýÇÏ´Â °æ¿ì ºñ¿öÁý´Ï´Ù.

    • ÀÀ¿ëÇÁ·Î±×·¥Àº ¾Ï½ÃÀûÀ¸·Î ¶Ç´Â ÀÀ¿ëÇÁ·Î±×·¥ Á¾·á¸¦ ÅëÇØ ¸í½ÃÀûÀ¸·Î COMMITÀ̳ª ROLLBACKÀ» ÁöÁ¤ÇÕ´Ï´Ù.
    • ÀÀ¿ëÇÁ·Î±×·¥Àº ÀúÀåÁ¡À» ¹ß»ýÇϵµ·Ï ÇÏ´Â ´Ù¸¥ ¸í·É¹®À» ÁöÁ¤ÇÕ´Ï´Ù. OPEN, FETCH ¹× CLOSE Ä¿¼­ ¸í·É¹®Àº ÀúÀåÁ¡ÀÌ ¹ß»ýÇϵµ·Ï ÇÏÁöµµ ¾Ê°í, ¿­·Á ÀÖ´Â ¹öÆÛ »ðÀÔÀ» ´ÝÁöµµ ¾Ê½À´Ï´Ù.

      ´ÙÀ½ÀÇ SQL¹®Àº ¿­·Á ÀÖ´Â ¹öÆÛ »ðÀÔÀ» ´Ý½À´Ï´Ù.

      • BEGIN COMPOUND SQL
      • COMMIT
      • DDL
      • DELETE
      • END COMPOUND SQL
      • EXECUTE IMMEDIATE
      • GRANT
      • ´Ù¸¥ Å×À̺í·Î INSERT
      • À̸§À¸·Î ¹öÆÛ »ðÀÔÀ» ¼öÇàÇÏ´Â µ¿ÀÏÇÑ µ¿Àû ¸í·É¹®ÀÇ PREPARE
      • REDISTRIBUTE NODEGROUP
      • RELEASE SAVEPOINT
      • REORG
      • REVOKE
      • ROLLBACK
      • ROLLBACK TO SAVEPOINT
      • RUNSTATS
      • SAVEPOINT
      • SELECT INTO
      • UPDATE
      • ´Ù¸¥ ¸í·É¹®ÀÇ ½ÇÇà, ±×·¯³ª ¹öÆÛ INSERTÀÇ ¶Ç ´Ù¸¥ ·çÇÎ ½ÇÇàÀº ¾Æ´Ô.
      • ÀÀ¿ëÇÁ·Î±×·¥ÀÇ Á¾·á

      ´ÙÀ½ÀÇ API´Â ¿­·Á ÀÖ´Â ¹öÆÛ »ðÀÔÀ» ´Ý½À´Ï´Ù.

      • BIND (API)
      • REBIND (API)
      • RUNSTATS (API)
      • REORG (API)
      • REDISTRIBUTE (API)

    ´Ù¸¥ ¸í·É¹®µéÀÌ ¹öÆÛ »ðÀÔÀ» ´Ý´Â ÀÌ·¯ÇÑ »óȲ¿¡¼­, Á¶Á¤ÀÚ ³ëµå´Â ¸ðµç ³ëµå°¡ ¹öÆÛ¸¦ ¼ö½ÅÇϰí ÇàÀÌ »ðÀ﵃ ¶§±îÁö ±â´Ù¸³´Ï´Ù. ±×¸®°í³ª¼­ ¸ðµç ÇàÀÌ ¼º°øÀûÀ¸·Î »ðÀԵǸé, ´Ù¸¥ ¸í·É¹®(¹öÆÛ »ðÀÔÀ» ´Ý´Â ¸í·É¹®)À» ½ÇÇàÇÕ´Ï´Ù. Ãß°¡ ¼¼ºÎ»çÇ×Àº ¹öÆÛ »ðÀÔ »ç¿ë½Ã °í·Á»çÇ×ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

ÆÄƼ¼ÇµÈ ȯ°æ¿¡¼­ Ç¥ÁØ ÀÎÅÍÆäÀ̽º´Â ¹öÆÛ »ðÀÔ ¾øÀÌ ÇÑ ¹ø¿¡ ÇÑ ÇàÀ» ·ÎµåÇϸç, ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ÆÄƼ¼Ç Áß Çϳª¿¡¼­ Áö¿ªÀûÀ¸·Î ¼öÇàµÈ´Ù´Â °¡Á¤ ÇÏ¿¡ ´ÙÀ½°ú °°Àº ´Ü°è¸¦ ¼öÇàÇÕ´Ï´Ù.

  1. Á¶Á¤ÀÚ ³ëµå´Â ÀÌ ÇàÀ» °°Àº ³ëµåÀÇ µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥À¸·Î Àü´ÞÇÕ´Ï´Ù.
  2. µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº °£Á¢ ÇØ½³À» »ç¿ëÇÏ¿© ÀÌ ÇàÀÌ À§Ä¡ÇØ¾ß ÇÒ ÆÄƼ¼ÇÀ» ÆÇº°ÇϽʽÿÀ.
  3. Á¶Á¤ÀÚ ³ëµå°¡ ¸ñÇ¥ ÆÄƼ¼ÇÀ¸·ÎºÎÅÍ ÀÀ´äÀ» ¹Þ½À´Ï´Ù.
  4. Á¶Á¤ÀÚ ³ëµå´Â ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ÀÀ´äÀ» ÁÝ´Ï´Ù.

    »ðÀÔÀº ÀÀ¿ëÇÁ·Î±×·¥ÀÌ COMMITÀ» ÁöÁ¤Çϱâ Àü±îÁö´Â È®¾àµÇÁö ¾Ê½À´Ï´Ù.

  5. VALUESÀýÀÌ µé¾î ÀÖ´Â INSERT¹®Àº ¹«¾ùÀ̵ç Çà ¼ö³ª ÇàÀÇ ¿ä¼Ò ¼ö¿¡ »ó°ü¾øÀÌ ¹öÆÛ »ðÀÔÀÇ Èĺ¸ÀÔ´Ï´Ù. Áï, ¿ä¼Ò´Â »ó¼ö, Ư¼ö ·¹Áö½ºÅÍ, È£½ºÆ® º¯¼ö, Ç¥Çö½Ä, ÇÔ¼ö µîÀÌ µÉ ¼ö ÀÖ½À´Ï´Ù.

VALUESÀýÀ» °®´Â INSERT¹®ÀÇ °æ¿ì, DB2 SQL ÄÄÆÄÀÏ·¯´Â ¹®¹ý, ¼º´É ¶Ç´Â ±¸Çö½Ã °í·Á»çÇ׿¡ ±Ù°ÅÇÏ¿© »ðÀÔÀ» ¹öÆÛ¿¡ ³ÖÀ» ¼ö ¾ø½À´Ï´Ù. INSERT BUF ¿É¼ÇÀ» »ç¿ëÇÏ¿© ÀÀ¿ëÇÁ·Î±×·¥À» ÁغñÇϰųª ¹ÙÀεåÇÏ´Â °æ¿ì, ¹öÆÛ »ðÀÔ¿¡ Á¾¼ÓµÇÁö ¾Êµµ·Ï ÇϽʽÿÀ. À̰ÍÀº ´ÙÀ½À» ÀǹÌÇÕ´Ï´Ù.

¹öÆÛ »ðÀÔÀº ´ÙÀ½°ú °°Àº ¼º´É»óÀÇ ÀÌÁ¡ÀÌ ÀÖ½À´Ï´Ù.

INSERT BUF¿Í ¹ÙÀεåµÈ ÀÀ¿ëÇÁ·Î±×·¥Àº ¹öÆÛ »ðÀÔÀ» ´Ý´Â ¸í·É¹®À̳ª API°¡ ¹ßÇàµÇ±â Àü¿¡ VALUESÀýÀÌ ÀÖ´Â µ¿ÀÏÇÑ INSERT¹®ÀÌ ¹Ýº¹µÇµµ·Ï ÀÛ¼ºµÇ¾î¾ß ÇÕ´Ï´Ù.
ÁÖ:¹öÆÛ »ðÀÔÀÌ Æ®·£Àè¼Ç ·Î±×¸¦ ä¿ìÁö ¾Êµµ·Ï ¹æÁöÇϱâ À§ÇØ ÁÖ±âÀûÀÎ È®¾àÀ» ÇØ¾ß ÇÕ´Ï´Ù.

¹öÆÛ »ðÀÔ »ç¿ë½Ã °í·Á»çÇ×

¹öÆÛ »ðÀÔÀº ÀÀ¿ëÇÁ·Î±×·¥¿¡ ¿µÇâÀ» ¹ÌÄ¥ ¼ö ÀÖ´Â ±â´ÉÀ» °¡Áö°í ÀÖ½À´Ï´Ù. ÀÌ ±â´ÉÀº ¹öÆÛ »ðÀÔÀÇ ºñµ¿±âÀû Ư¼ºÀ¸·ÎºÎÅÍ ±âÀÎÇÕ´Ï´Ù. ÇàÀÇ ÆÄƼ¼Ç Ű¿¡ ±âÃÊÇÏ¿© °¢ »ðÀÔµÈ ÇàÀº ¿Ã¹Ù¸¥ ÆÄƼ¼Ç¿¡ ÁöÁ¤µÈ ¹öÆÛ¿¡ ³õÀÌ°Ô µË´Ï´Ù. ÀÌ ¹öÆÛµéÀÌ °¡µæ Â÷°Ô µÇ¸é ¸ñÀûÁö ÆÄƼ¼ÇÀ¸·Î ¼Û½ÅµÇ°Å³ª, À̺¥Æ®¸¦ ¹ß»ý½ÃÄÑ ºñ¿ì°Ô µË´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥À» ¼³°èÇϰí ÄÚµùÇÒ ¶§ ´ÙÀ½°ú °°Àº Á¡µéÀ» ¼÷ÁöÇÏ°í °í·ÁÇØ¾ß ÇÕ´Ï´Ù.

¹öÆÛ INSERT¹®Àº ¿­·Á Àְųª ´ÝÇô ÀÖ½À´Ï´Ù. ÀÌ ¸í·ÉÀ» óÀ½ È£ÃâÇÏ¸é ¹öÆÛ INSERT¸¦ ¿­°í, ÇàÀº ÀûÀýÇÑ ¹öÆÛ¿¡ Ãß°¡µÇ¸ç, Á¦¾î´Â ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ¸®Åϵ˴ϴÙ. ÈÄ¼Ó È£ÃâÀº ÇàÀ» ¹öÆÛ¿¡ Ãß°¡ÇÏ°í ¸í·É¹®Àº ¿­¸° »óÅ·ΠµÓ´Ï´Ù. ÀÌ ¸í·É¹®ÀÌ ¿­·Á ÀÖ´Â µ¿¾È ¹öÆÛ´Â ÇàµéÀÌ ¸ñÇ¥ Å×À̺íÀÇ ÆÄƼ¼ÇÀ¸·Î »ðÀԵǴ ¸ñÀûÁö ÆÄƼ¼ÇÀ¸·Î ¼Û½ÅµÉ ¼ö ÀÖ½À´Ï´Ù. ¹öÆÛ »ðÀÔÀ» ´Ý´Â ¸í·É¹®À̳ª API°¡ ´Ù¸¥ ¹öÆÛ INSERT¹®ÀÇ È£ÃâÀ» Æ÷ÇÔÇÏ¿© ¹öÆÛ INSERT¹®ÀÌ ¿­·Á ÀÖ´Â µ¿¾È È£ÃâµÇ¾ú°Å³ª, PREPARE¹®ÀÌ ¿­·Á ÀÖ´Â ¹öÆÛ INSERT¹®¿¡ ´ëÇÏ¿© ¹ßÇàµÈ °æ¿ì, ¿­±â ¸í·É¹®Àº »õ·Î¿î ¿äûÀÌ Ã³¸®µÇ±â Àü¿¡ ´ÝÈü´Ï´Ù. ¹öÆÛ INSERT¹®ÀÌ ´ÝÈù °æ¿ì, ³ª¸ÓÁö ¹öÆÛ´Â ºñ¿öÁý´Ï´Ù. ±×¸®°í³ª¼­ ÇàÀº ¸ñÇ¥ ÆÄƼ¼ÇÀ¸·Î ¼Û½ÅµÇ°í »ðÀԵ˴ϴÙ. ¸ðµç ¹öÆÛ°¡ ¼Û½ÅµÇ°í ¸ðµç ÇàÀÌ »ðÀÔµÈ ÈÄ¿¡¾ß »õ·Î¿î ¿äûÀÌ Ã³¸®¸¦ ½ÃÀÛÇÕ´Ï´Ù.

INSERT¹®À» ´Ý´Â Áß¿¡ ¿À·ù°¡ ŽÁöµÈ °æ¿ì, »õ·Î¿î ¿äû¿¡ ´ëÇÑ SQLCA°¡ ¿À·ù ¼­¼ú·Î ä¿öÁö¸ç, »õ·Î¿î ¿äûÀº ¼öÇàµÇÁö ¾Ê½À´Ï´Ù. ¶ÇÇÑ INSERT¹®ÀÌ ¿­¸° ÀÌÈÄ ¹öÆÛ INSERT¹®À» ÅëÇØ »ðÀÔµÈ Çà ±×·ì Àüü°¡ µ¥ÀÌÅͺ£À̽º·ÎºÎÅÍ Á¦°ÅµË´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥ »óÅ´ ¹ß°ßµÈ ƯÁ¤ ¿À·ù¿¡ ´ëÇÏ¿© Á¤Àǵ˴ϴÙ. ¿¹¸¦ µé¸é ´ÙÀ½°ú °°½À´Ï´Ù.

¿¹¸¦ µé¾î, ¹öÆÛ »ðÀÔ ¿É¼Ç°ú ¹ÙÀÎµå µÈ ´ÙÀ½°ú °°Àº ÀÀ¿ëÇÁ·Î±×·¥À» »ý°¢ÇØ º¾½Ã´Ù.

     EXEC SQL UPDATE t1 SET COMMENT='about to start inserts';
     DO UNTIL EOF OR SQLCODE < 0;
       READ VALUE OF hv1 FROM A FILE;
       EXEC SQL INSERT INTO t2 VALUES (:hv1);
       IF 1000 INSERTS DONE, THEN DO
          EXEC SQL INSERT INTO t3 VALUES ('another 1000 done');
          RESET COUNTER;
     END;
     END;
     EXEC SQL COMMIT;

¿¹¸¦ µé¾î, ÆÄÀÏ¿¡ 8,000°³ÀÇ °ªÀÌ µé¾î ÀÖÀ¸³ª °íÀ¯ Ű À§¹Ý µîÀ¸·Î 3,258 °ªÀÌ ÀûÀýÄ¡ ¾Ê´Ù°í °¡Á¤ÇϽʽÿÀ. 1,000°³ÀÇ »ðÀÔÀÌ °¢°¢ ´Ù¸¥ SQL¹®À» ½ÇÇàÇϰí, ÀÌ´Â INSERT INTO t2¹®À» ´Ý½À´Ï´Ù. 1,000°³ÀÇ »ðÀÔÀ¸·Î ÀÌ·ç¾îÁø ³× ¹øÂ° ±×·ì¿¡¼­ 3,258 °ª¿¡ ´ëÇÑ ¿À·ù°¡ ŽÁöµÇ¾ú½À´Ï´Ù. À̰ÍÀº ´Ù¸¥ °ªÀÌ ´õ »ðÀÔµÈ ÈÄ¿¡(¹Ýµå½Ã ´ÙÀ½ »ðÀÔÀº ¾Æ´Ô) ŽÁöµÉ ¼ö ÀÖ½À´Ï´Ù. ÀÌ·¯ÇÑ »óȲ¿¡¼­ ¿À·ù Äڵ尡 INSERT INTO t2¹®¿¡ ´ëÇÏ¿© ¸®Åϵ˴ϴÙ.

¶ÇÇÑ ¿À·ù´Â Å×À̺í t3¿¡¼­ »ðÀÔÀÌ ½ÃµµµÉ ¶§ ŽÁöµÉ ¼öµµ Àִµ¥, À̰ÍÀº INSERT INTO t2¹®À» ´Ý½À´Ï´Ù. ÀÌ·¯ÇÑ »óȲ¿¡¼­ ¿À·ù´Â Å×À̺í t2¿¡ Àû¿ëµÈ´Ù°í ÇÏ´õ¶óµµ ¿À·ù ÄÚµå´Â INSERT INTO t3¹®¿¡ ´ëÇÏ¿© ¸®Åϵ˴ϴÙ.

´ë½Å »ðÀÔÇÒ ÇàÀÌ 3,900°³ ÀÖ´Ù°í °¡Á¤ÇϽʽÿÀ. Çà ¹øÈ£ 3,258¿¡ ´ëÇÑ ¿À·ù º¸°í¸¦ ¹Þ±â Àü¿¡, ÀÀ¿ëÇÁ·Î±×·¥Àº ·çÇÁ¸¦ ºüÁ®³ª¿Í COMMIT ¹ßÇàÀ» ½ÃµµÇÒ ¼ö ÀÖ½À´Ï´Ù. °íÀ¯ Ű À§¹Ý ¸®ÅÏ Äڵ尡 COMMIT¹®¿¡ ´ëÇÏ¿© ¹ßÇàµÇ°í, COMMITÀº ¼öÇàµÇÁö ¾Ê½À´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥ÀÌ Áö±Ý±îÁö µ¥ÀÌÅͺ£À̽º¿¡ ÀÖ´Â 3000ÇàÀ» COMMITÇÏ·Á°í ÇÏ´Â °æ¿ì(EXEC SQL INSERT INTO t3 ...ÀÇ ÃÖÁ¾ ½ÇÇàÀÌ À̵é 3000 Çà¿¡ ´ëÇÑ ÀúÀåÁ¡À» Á¾·áÇÔ), COMMITÀº ´Ù½Ã ¹ßÇàµÇ¾î¾ß ÇÕ´Ï´Ù. ROLLBACK¿¡¼­µµ ºñ½ÁÇÑ °í·Á»çÇ×ÀÌ ¸¶Âù°¡Áö·Î Àû¿ëµË´Ï´Ù.
ÁÖ:¹öÆÛ »ðÀÔ »ç¿ë½Ã, Å×À̺íÀÌ ºÒÈ®½ÇÇÑ »óÅ¿¡ ÀÖÁö ¾Êµµ·Ï ÇÏ·Á¸é ¸®ÅÏµÈ SQLCODES¸¦ ÁÖÀDZí°Ô ¸ð´ÏÅÍÇØ¾ß ÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, À§ÀÇ ¿¹¿¡¼­ THEN DO¹®¿¡¼­ SQLCODE < 0ÀýÀ» Á¦°ÅÇÑ °æ¿ì, Å×À̺íÀº ºÒºÐ¸íÇÑ Çà ¼ö¸¦ Æ÷ÇÔÇÑ Ã¤ Á¾·áÇÒ ¼ö ÀÖ½À´Ï´Ù.

¹öÆÛ »ðÀÔ »ç¿ë½Ã Á¦ÇÑ »çÇ×

´ÙÀ½ÀÇ Á¦ÇÑ »çÇ×ÀÌ Àû¿ëµË´Ï´Ù.

ÀÀ¿ëÇÁ·Î±×·¥Àº Áö¿øµÇ´Â Ŭ¶óÀÌ¾ðÆ® Ç÷§ÆûÀ̶ó¸é ¾îµð¿¡¼­µç ¼öÇàµÉ ¼ö ÀÖ½À´Ï´Ù.

¿¹: ´ë¿ë·®ÀÇ µ¥ÀÌÅÍ ÃßÃâ(largevol.c)

DB2 Universal Database°¡ º´·Ä Á¶È¸ 󸮿¡ ¶Ù¾î³­ ±â´ÉÀ» Á¦°øÇÏÁö¸¸, ÀÀ¿ëÇÁ·Î±×·¥À̳ª EXPORT ¸í·É¿¡ À־ ´ÜÀÏ ¿¬°áÁ¡Àº ´ë¿ë·®ÀÇ µ¥ÀÌÅ͸¦ ÃßÃâÇÒ ¶§ º´¸ñÀÌ µÉ ¼ö ÀÖ½À´Ï´Ù. À̰ÍÀº µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥¿¡¼­ ÀÀ¿ëÇÁ·Î±×·¥À¸·Î µ¥ÀÌÅ͸¦ Àü´ÞÇÏ´Â °ÍÀÌ ÇϳªÀÇ ³ëµå, Áï º¸Åë ´ÜÀÏ ÇÁ·Î¼¼¼­¿¡¼­ ½ÇÇàµÇ´Â CPU Áý¾àÀûÀÎ ÇÁ·Î¼¼½ºÀ̱⠶§¹®ÀÔ´Ï´Ù.

DB2 Universal Database¿¡¼­´Â ÀÌ·¯ÇÑ º´¸ñÀ» ¹æÁöÇÒ ¼ö ÀÖ´Â ¸î °¡Áö ¹æ¹ýÀ» Á¦°øÇÏ¿©, ÃßÃâµÈ µ¥ÀÌÅÍ ¿ë·®ÀÌ ÇÁ·Î¼¼¼­ÀÇ ¼ö¸¦ Áõ°¡½ÃŰ¸é¼­ ½Ã°£ ´ÜÀ§ º°·Î Ä¿Áöµµ·Ï ÇÕ´Ï´Ù. ´ÙÀ½ÀÇ ¿¹¿¡¼­´Â ÀÌµé ¹æ¹ýÀÇ ±Ù°£ÀÌ µÇ´Â °³³äÀ» ¼³¸íÇÕ´Ï´Ù.

»ç¿ëÀÚ¿¡°Ô 20°³ÀÇ ³ëµå¿¡ ÀúÀåµÇ¾î ÀÖ´Â EMPLOYEE¶ó´Â Å×À̺íÀÌ ÀÖ°í, Á¤´çÇÑ ºÎ¼­¿¡¼­ ±Ù¹«ÇÏ´Â(Áï WORKDEPT´Â NULLÀÌ ¾Æ´Ô) ¸ðµç Á÷¿øµéÀÇ ¿ìÆí ¸ñ·Ï(FIRSTNME, LASTNAME, JOB)À» »ý¼ºÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ.

´ÙÀ½ÀÇ Á¶È¸´Â °¢ ³ëµå¿¡¼­ º´·Ä·Î ¼öÇàµÇ°í ÇϳªÀÇ ³ëµå, Áï Á¶Á¤ÀÚ ³ëµî¿¡¼­ Àüü ÀÀ´ä ¼¼Æ®¸¦ »ý¼ºÇÕ´Ï´Ù.

     SELECT FIRSTNME, LASTNAME, JOB FROM EMPLOYEE WHERE WORKDEPT IS NOT NULL

±×·¯³ª ´ÙÀ½ÀÇ Á¶È¸´Â µ¥ÀÌÅͺ£À̽ºÀÇ °¢ ÆÄƼ¼Ç¿¡¼­ ¼öÇàµÉ ¼ö ÀÖ½À´Ï´Ù. Áï, 5°³ÀÇ ÆÄƼ¼ÇÀÌ ÀÖ´Â °æ¿ì °¢ ÆÄƼ¼Ç¿¡ Çϳª¾¿ 5°³ÀÇ º°µµÀÇ Á¶È¸°¡ ÇÊ¿äÇÕ´Ï´Ù. °¢ Á¶È¸´Â ±× ±â·ÏÀÌ Á¶È¸°¡ ¼öÇàµÇ´Â ƯÁ¤ ±¸È¹¿¡ ÀÖ´Â ¸ðµç Á÷¿ø À̸§ ¼¼Æ®¸¦ »ý¼ºÇÕ´Ï´Ù. °¢ Áö¿ª °á°ú ¼¼Æ®´Â ÆÄÀÏ·Î °æ·Î ÀçÁöÁ¤µÉ ¼ö ÀÖ½À´Ï´Ù. ±×¸®°í³ª¼­ °á°ú ¼¼Æ®µéÀº ÇϳªÀÇ °á°ú ¼¼Æ®·Î º´ÇյǾî¾ß ÇÕ´Ï´Ù.

AIX¿¡¼­´Â ³×Æ®¿öÅ© ÆÄÀÏ ½Ã½ºÅÛ(NFS) ÆÄÀϵéÀÇ Æ¯¼ºÀ» »ç¿ëÇÏ¿© º´ÇÕÀ» ÀÚµ¿È­ÇÒ ¼ö ÀÖ½À´Ï´Ù. ¸ðµç ÆÄƼ¼ÇÀÌ ÀÚ±âµéÀÇ ÀÀ´ä ¼¼Æ®¸¦ NFS ¸¶¿îÆ® »óÀÇ µ¿ÀÏÇÑ ÆÄÀÏ·Î °æ·Î¸¦ ÁöÁ¤Çϸé, °á°úµéÀÌ º´Çյ˴ϴÙ. ´ë¿ë·®ÀÇ ¹öÆÛ·Î ÀÀ´äÀ» ºí·ÎÅ·ÇÏÁö ¾Ê°í NFS¸¦ »ç¿ëÇÏ´Â °æ¿ì ¼º´ÉÀÌ ÀúÇϵʿ¡ ÁÖÀÇÇϽʽÿÀ.

     SELECT FIRSTNME, LASTNAME, JOB FROM EMPLOYEE WHERE WORKDEPT IS NOT NULL
                      AND NODENUMBER(NAME) = CURRENT NODE

°á°ú´Â Áö¿ª ÆÄÀÏ¿¡ ÀúÀåµÉ ¼öµµ ÀÖ°í(Áï ÃÖÁ¾ °á°ú°¡ 20°³ÀÇ ÆÄÀÏ¿¡ ´ã°Ü ÀÖÀ¸¸ç °¢°¢ÀÇ ÆÄÀÏ¿¡ Àüü ÀÀ´ä ¼¼Æ®ÀÇ ÀϺΰ¡ µé¾î ÀÖÀ½), ÇϳªÀÇ NFS ¸¶¿îÆ® ÆÄÀÏ¿¡ ÀúÀåµÉ ¼öµµ ÀÖ½À´Ï´Ù.

´ÙÀ½ÀÇ ¿¹¿¡¼­´Â µÎ ¹øÂ° ¹æ¹ýÀ» »ç¿ëÇÏ¿© °á°ú°¡ 20°³ÀÇ ³ëµå¿¡ °ÉÃÄ ¸¶¿îÆ®µÇ¾î ÀÖ´Â NFSÀÎ ÇϳªÀÇ ÆÄÀÏ¿¡ µé¾î ÀÖµµ·Ï ÇÕ´Ï´Ù. NFS Àá±Ý ¸ÞÄ«´ÏÁòÀ» ÅëÇØ ´Ù¸¥ ÆÄƼ¼ÇÀ¸·ÎºÎÅÍ °á°ú ÆÄÀÏ¿¡ ±â·ÏÇÏ´Â ¼ø¼­°¡ ü°èÈ­µË´Ï´Ù. ÀÌ ¿¹´Â NFS ÆÄÀÏ ½Ã½ºÅÛÀÌ ¼³Ä¡µÈ AIX Ç÷§Æû¿¡¼­ ¼öÇàµÊ¿¡ ÁÖÀÇÇϽʽÿÀ.

#define _POSIX_SOURCE
#define INCL_32
 
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <fcntl.h>
#include <sqlenv.h>
#include <errno.h>
#include <sys/access.h>
#include <sys/flock.h>
#include <unistd.h>
 
#define BUF_SIZE 1500000  /* Local buffer to store the fetched records */
#define MAX_RECORD_SIZE 80 /* >= size of one written record */
 
int main(int argc, char *argv[]) {
 
    EXEC SQL INCLUDE SQLCA;
    EXEC SQL BEGIN DECLARE SECTION;
       char dbname[10];  /* Database name (argument of the program) */
       char userid[9];
       char passwd[19];
       char first_name[21];
       char last_name[21];
       char job_code[11];
    EXEC SQL END DECLARE SECTION;
 
       struct flock unlock ;  /* structures and variables for handling */
       struct flock lock ;  /* the NFS locking mechanism */
       int lock_command ;
       int lock_rc ;
       int iFileHandle ;  /* output file */
       int iOpenOptions = 0 ;
       int iPermissions ;
       char * file_buf ;  /* pointer to the buffer where the fetched
                             records are accumulated */
       char * write_ptr ;  /* position where the next record is written */
       int buffer_len = 0 ;  /* length of used portion of the buffer */
 
    /* Initialization */
 
       lock.l_type = F_WRLCK;  /* An exclusive write lock request */
       lock.l_start = 0;  /* To lock the entire file */
       lock.l_whence = SEEK_SET;
       lock.l_len = 0;
       unlock.l_type = F_UNLCK;  /* An release lock request */
       unlock.l_start = 0;  /* To unlock the entire file */
       unlock.l_whence = SEEK_SET;
       unlock.l_len = 0;
       lock_command = F_SETLKW;  /* Set the lock */
       iOpenOptions = O_CREAT;  /* Create the file if not exist */
       iOpenOptions |= O_WRONLY;  /* Open for writing only */
 
    /* Connect to the database */
 
       if (argc == 3) {
          strcpy( dbname, argv[2] ); /* get database name from the argument */
          EXEC SQL CONNECT TO :dbname IN SHARE MODE ;
          if ( SQLCODE != 0 ) {
             printf( "Error: CONNECT TO the database failed. SQLCODE = %ld\n",
                    SQLCODE );
              exit(1);
          }
       }
       else if ( argc == 5 ) {
         strcpy( dbname, argv[2] ); /* get  database name from the argument */
         strcpy (userid, argv[3]);
         strcpy (passwd, argv[4]);
         EXEC SQL CONNECT TO :dbname IN SHARE MODE USER :userid USING :passwd;
         if ( SQLCODE != 0 ) {
            printf( "Error: CONNECT TO the database failed. SQLCODE = %ld\n",
                    SQLCODE );
              exit( 1 );
         }
       }
       else {
           printf ("\nUSAGE: largevol txt_file database [userid passwd]\n\n");
           exit( 1 ) ;
       } /* endif */
 
      /* Open the input file with the specified access permissions */
 
     if ( ( iFileHandle = open(argv[1], iOpenOptions, 0666 ) ) == -1 ) {
       printf( "Error: Could not open %s.\n", argv[2] ) ;
       exit( 2 ) ;
    }
 
    /* Set up error and end of table escapes */
 
    EXEC SQL WHENEVER SQLERROR GO TO ext ;
    EXEC SQL WHENEVER NOT FOUND GO TO cls ;
 
    /* Declare and open the cursor */
 
    EXEC SQL DECLARE c1 CURSOR FOR
             SELECT firstnme, lastname, job FROM employee
             WHERE workdept IS NOT NULL
             AND NODENUMBER(lastname) = CURRENT NODE;
    EXEC SQL OPEN c1 ;
 
    /* Set up the temporary buffer for storing the fetched result */
 
    if ( ( file_buf = ( char * ) malloc( BUF_SIZE ) ) == NULL ) {
       printf( "Error: Allocation of buffer failed.\n" ) ;
       exit( 3 ) ;
    }
    memset( file_buf, 0, BUF_SIZE ) ; /* reset the buffer */
    buffer_len = 0 ;  /* reset the buffer length */
    write_ptr = file_buf ;  /* reset the write pointer */
    /* For each fetched record perform the following    */
    /*  - insert it into the buffer following the       */
    /*    previously stored record                      */
    /*  - check if there is still enough space in  the  */
    /*    buffer for the next record and lock/write/    */
    /*    unlock the file and initialize the buffer     */
    /*    if not                                        */
 
    do {
       EXEC SQL FETCH c1 INTO :first_name, :last_name, :job_code;
        buffer_len += sprintf( write_ptr, "%s %s %s\n",
                               first_name, last_name, job_code );
        buffer_len = strlen( file_buf ) ;
       /* Write the content of the buffer to the file if */
       /* the buffer reaches the limit                   */
       if ( buffer_len >= ( BUF_SIZE - MAX_RECORD_SIZE ) ) {
        /*  get excl. write lock */
        lock_rc = fcntl( iFileHandle, lock_command, &lock );
                 if ( lock_rc != 0 ) goto file_lock_err;
                 /*  position at the end of file */
                 lock_rc = lseek( iFileHandle, 0, SEEK_END );
                if ( lock_rc < 0 ) goto file_seek_err;
                /* write the buffer */
                lock_rc = write( iFileHandle,
                               ( void * ) file_buf, buffer_len );
                if ( lock_rc < 0 ) goto file_write_err;
                  /* release the lock */
                 lock_rc = fcntl( iFileHandle, lock_command, &unlock );
                 if ( lock_rc != 0 ) goto file_unlock_err;
                 file_buf[0] = '\0' ;  /* reset the buffer */
                  buffer_len = 0 ;  /* reset the buffer length */
                  write_ptr = file_buf ;  /* reset the write pointer */
       }
       else {
          write_ptr = file_buf + buffer_len ;  /* next write position */
       }
    } while (1) ;
 
cls:
    /* Write the last piece of data out to the file */
    if (buffer_len > 0) {
       lock_rc = fcntl(iFileHandle, lock_command, &lock);
       if (lock_rc != 0) goto file_lock_err;
      lock_rc = lseek(iFileHandle, 0, SEEK_END);
       if (lock_rc < 0) goto file_seek_err;
       lock_rc = write(iFileHandle, (void *)file_buf, buffer_len);
       if (lock_rc < 0) goto file_write_err;
       lock_rc = fcntl(iFileHandle, lock_command, &unlock);
      if (lock_rc != 0) goto file_unlock_err;
    }
    free(file_buf);
        close(iFileHandle);
    EXEC SQL CLOSE c1;
    exit (0);
 ext:
    if ( SQLCODE != 0 )
       printf( "Error:  SQLCODE = %ld.\n", SQLCODE );
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    EXEC SQL CONNECT RESET;
    if ( SQLCODE != 0 ) {
       printf( "CONNECT RESET Error:  SQLCODE = %ld\n", SQLCODE );
       exit(4);
    }
    exit (5);
 file_lock_err:
    printf("Error: file lock error = %ld.\n",lock_rc);
      /* unconditional unlock of the file */
    fcntl(iFileHandle, lock_command, &unlock);
    exit(6);
file_seek_err:
    printf("Error: file seek error = %ld.\n",lock_rc);
      /* unconditional unlock of the file */
    fcntl(iFileHandle, lock_command, &unlock);
   exit(7);
file_write_err:
    printf("Error: file write error = %ld.\n",lock_rc);
      /* unconditional unlock of the file */
    fcntl(iFileHandle, lock_command, &unlock);
    exit(8);
file_unlock_err:
    printf("Error: file unlock error = %ld.\n",lock_rc);
      /* unconditional unlock of the file */
   fcntl(iFileHandle, lock_command, &unlock);
    exit(9);
}

ÀÌ ¹æ¹ýÀº ´ÜÀÏ Å×À̺í·ÎºÎÅÍÀÇ ¼±Åÿ¡¸¸ Àû¿ëµÇ´Â °ÍÀÌ ¾Æ´Ï¶ó ´õ º¹ÀâÇÑ Á¶È¸¿¡µµ Àû¿ëµÉ ¼ö ÀÖ½À´Ï´Ù. ±×·¯³ª Á¶È¸¿¡ ºñÇÒ´ç Á¶ÀÛÀÌ ÇÊ¿äÇÑ °æ¿ì(Áï, ExplainÀÌ Á¶Á¤ÀÚ ºÎ¼Ó Àý ÀÌ¿ÜÀÇ µÑ ÀÌ»óÀÇ ºÎ¼Ó ÀýÀ» º¸¿©ÁÖ´Â °æ¿ì), Á¶È¸°¡ ¸ðµç ÆÄƼ¼Ç¿¡¼­ º´·Ä·Î ¼öÇàµÉ ¶§, À̰ÍÀº ÀϺΠÆÄƼ¼Ç¿¡¼­ ³Ê¹« ¸¹Àº ÇÁ·Î¼¼½º°¡ ¹ß»ýÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌ·¯ÇÑ °æ¿ì, Á¶È¸ °á°ú¸¦ Çʿ信 µû¶ó ÆÄƼ¼Ç¿¡¼­ Àӽà Å×À̺í TEMP¿¡ Á¶È¸ °á°ú¸¦ ÀúÀåÇÑ ÈÄ, TEMP·ÎºÎÅÍ º´·Ä·Î ÃÖÁ¾ ÃßÃâÀ» ¼öÇàÇÕ´Ï´Ù.

¼±ÅÃµÈ ÀÛ¾÷ ºÐ·ù¿¡ ´ëÇØ¼­ ¸ðµç Á÷¿øÀ» ÃßÃâÇϰíÀÚ ÇÏ´Â °æ¿ì, ´ÙÀ½°ú °°ÀÌ Ä÷³ À̸§, FIRSTNME, LASTNAME ¹× JOBÀ» »ç¿ëÇÏ¿© TEMP Å×À̺íÀ» Á¤ÀÇÇÒ ¼ö ÀÖ½À´Ï´Ù.

     INSERT INTO TEMP
     SELECT FIRSTNME, LASTNAME, JOB FROM EMPLOYEE WHERE WORKDEPT IS NOT NULL
            AND EMPNO NOT IN (SELECT EMPNO FROM EMP_ACT WHERE
                              EMPNO<200)

±×¸®°í³ª¼­ TEMP¿¡¼­ º´·Ä ÃßÃâÀ» ½ÇÇàÇÕ´Ï´Ù.

TEMP Å×À̺íÀ» Á¤ÀÇÇÒ ¶§, ´ÙÀ½À» °í·ÁÇϽʽÿÀ.

¸ðµç ³ëµå·ÎºÎÅÍÀÇ ºÎºÐÀû ÀÀ´äÀ» º´ÇÕÇÑ ÃÖÁ¾ ÀÀ´ä ¼¼Æ®¸¦ Á¤·ÄÇÒ Çʿ䰡 ÀÖ´Â °æ¿ì, ´ÙÀ½À» ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.


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