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

¿¹: LOB Ç¥Çö½Ä Æò°¡ ¿¬±â

¸ñÇ¥ ¸ñÀûÁö·ÎÀÇ LOB Ç¥Çö½Ä ÁöÁ¤ÀÌ µÉ ¶§±îÁö LOB °ª ¹ÙÀÌÆ®¿¡´Â À̵¿ÀÌ ¾ø½À´Ï´Ù. ÀÌ´Â ¹®ÀÚ¿­ ÇÔ¼ö ¹× ¿¬»êÀÚ¿Í ÇÔ²² »ç¿ëµÇ´Â LOB °ª À§Ä¡ ÁöÁ¤ÀÚ°¡ ÁöÁ¤µÉ ¶§±îÁö Ç¥Çö½ÄÀÌ ¿¬±âµÇ´Â Ç¥Çö½ÄÀ» ÀÛ¼ºÇÒ ¼ö ÀÖÀ½À» ÀǹÌÇÕ´Ï´Ù. À̸¦ LOB Ç¥Çö½ÄÀÇ Æò°¡ ¿¬±â¶ó°í ÇÕ´Ï´Ù.

ÀÌ ¿¹¿¡¼­´Â EMP_RESUME Àç°³ Å×ÀÌºí ³»¿¡¼­ ƯÁ¤ Àç°³(empno = '000130')°¡ Ž»öµË´Ï´Ù. Àç°³ÀÇ ºÎ¼­ Á¤º¸ ÀýÀº Àç°³ ³¡ºÎºÐ¿¡ º¹»ç, Àß¶ó³»±â ¹× Ãß°¡µË´Ï´Ù. ±×·± ÈÄ ÀÌ »õ·Î¿î Àç°³´Â EMP_RESUME Å×ÀÌºí¿¡ »ðÀԵ˴ϴÙ. ÀÌ Å×À̺íÀÇ ¿ø·¡ Àç°³´Â º¯°æµÇÁö ¾ÊÀº »óÅ·Π³²¾Æ ÀÖ½À´Ï´Ù.

À§Ä¡ ÁöÁ¤ÀÚ´Â ¿ø·¡ Àç°³·ÎºÎÅÍ ¹ÙÀÌÆ®¸¦ ½ÇÁ¦ À̵¿½ÃŰ°Å³ª º¹»çÇÏÁö ¾Ê°í »õ·Î¿î Àç°³ÀÇ °ËÅä ¹× ¾î¼Àºí¸®¸¦ Çã¿ëÇÕ´Ï´Ù. ¹ÙÀÌÆ®ÀÇ À̵¿Àº ÃÖÁ¾ ÇÒ´çµÉ ¶§±îÁö Áï, INSERT¹® -- ¹× ¼­¹ö¿¡¼­¸¸ ¹ßÇàÇÏÁö ¾Ê½À´Ï´Ù.

Æò°¡ ¿¬±â´Â DB2¿¡°Ô LOB I/O ¼º´ÉÀ» Çâ»ó½Ãų ±âȸ¸¦ Á¦°øÇÕ´Ï´Ù. LOB ±â´É ÃÖÀûÈ­ ¾Ë°í¸®ÁòÀÌ LOB Ç¥Çö½ÄÀ» ´ëü Ç¥Çö½ÄÀ¸·Î º¯È¯ÇÏ·Á Çϱ⠶§¹®¿¡ ¹ß»ýÇÕ´Ï´Ù. ÀÌ·¯ÇÑ ´ëü Ç¥Çö½ÄÀº µ¿ÀÏÇÑ °á°ú¸¦ »êÃâÇÏÁö¸¸ º¸´Ù ÀûÀº ¼öÀÇ µð½ºÅ© I/O°¡ ÇÊ¿äÇÒ ¼öµµ ÀÖ½À´Ï´Ù.

°£´ÜÈ÷ ¸»Çؼ­ LOB À§Ä¡ ÁöÁ¤ÀÚ´Â ¼ö¸¹Àº ÇÁ·Î±×·¡¹Ö ½Ã³ª¸®¿À¿¡ ÀÌ»óÀûÀÔ´Ï´Ù.

  1. º¸´Ù Å« LOB¿¡¼­ ÀÛÀº ºÎºÐ¸¸ Ŭ¶óÀÌ¾ðÆ® ÇÁ·Î±×·¥À¸·Î À̵¿½Ãų ¶§.
  2. Àüü LOB°¡ ÀÀ¿ëÇÁ·Î±×·¥ÀÇ ¸Þ¸ð¸®¿Í ¸ÂÁö ¾ÊÀ» ¶§.
  3. ÇÁ·Î±×·¥¿¡ LOB Ç¥Çö½ÄÀ¸·ÎºÎÅÍÀÇ Àӽà LOB °ªÀÌ ÇÊ¿äÇÏÁö¸¸ °á°ú¸¦ ÀúÀåÇÒ Çʿ䰡 ¾øÀ» ¶§.
  4. (LOB Ç¥Çö½ÄÀÇ Æò°¡¸¦ ¿¬±âÇÏ¿©) ¼º´ÉÀÌ Áß¿äÇÑ °æ¿ì.

»ùÇà LOBEVAL ÇÁ·Î±×·¥ ÀÛ¾÷ ¹æ¹ý

  1. È£½ºÆ® º¯¼ö Á¤ÀÇ. BEGIN DECLARE SECTION ¹× END DECLARE SECTION¹®Àº È£½ºÆ® º¯¼ö ¼±¾ðÀ» ºÐ¸®ÇÕ´Ï´Ù. È£½ºÆ® º¯¼ö´Â SQL¹®¿¡¼­ ÂüÁ¶µÉ ¶§ ÄÝ·Ð(:)À¸·Î ½ÃÀ۵˴ϴÙ. CLOB LOCATOR È£½ºÆ® º¯¼ö°¡ ¼±¾ðµË´Ï´Ù.
  2. LOB °ªÀ» LOCATOR È£½ºÆ® º¯¼ö·Î ÆäÄ¡. CURSOR ¹× FETCH ·çƾÀ» »ç¿ëÇÏ¿© µ¥ÀÌÅͺ£À̽º¿¡¼­ÀÇ LOB Çʵå À§Ä¡¸¦ È£½ºÆ® º¯¼ö À§Ä¡ ÁöÁ¤ÀÚ·Î È®º¸ÇÕ´Ï´Ù.
  3. LOB µ¥ÀÌÅÍ´Â LOCATORS »ç¿ëÀ» ÅëÇØ Á¶À۵˴ϴÙ. ´ÙÀ½ ´Ù¼¸°³ÀÇ SQL¹®Àº LOB Çʵ忡 Æ÷ÇÔµÈ ½ÇÁ¦ µ¥ÀÌÅ͸¦ À̵¿½ÃŰÁö ¾Ê°í LOB µ¥ÀÌÅ͸¦ Á¶ÀÛÇÕ´Ï´Ù. ÀÌ´Â LOB LOCATORS »ç¿ëÀ» ÅëÇØ ¼öÇàµË´Ï´Ù.
  4. LOB µ¥ÀÌÅͰ¡ ¸ñÇ¥ ¸ñÀûÁö·Î À̵¿µË´Ï´Ù. ¸ñÇ¥ ¸ñÀûÁö·Î ÇÒ´çµÈ LOBÀÇ Æò°¡´Â ÀÌ SQL¹®±îÁö Áö¿¬µË´Ï´Ù. ÀÌ LOB¹®ÀÇ Æò°¡°¡ Áö¿¬µÇ¾ú½À´Ï´Ù.
  5. LOB LOCATORS ºñ¿ì±â. ÀÌ ¿¹¿¡¼­ »ç¿ëÇÏ´Â LOB LOCATORS´Â ÀÌÀü¿¡ ¿¬°áµÈ °ªÀ¸·ÎºÎÅÍ À§Ä¡ ÁöÁ¤ÀÚ¸¦ ¸±¸®½ºÇÏ¿© ºñ¿öÁý´Ï´Ù.

CHECKERR ¸ÅÅ©·Î/ÇÔ¼ö´Â ¿À·ù üũ À¯Æ¿¸®Æ¼ÀÔ´Ï´Ù. ÀÌ ¿À·ù üũ À¯Æ¿¸®Æ¼ÀÇ À§Ä¡´Â »ç¿ëµÈ ÇÁ·Î±×·¡¹Ö ¾ð¾î¿¡ µû¶ó ´Þ¶óÁú ¼ö ÀÖ½À´Ï´Ù.

C
DB2 API¸¦ È£ÃâÇÏ´Â C ÇÁ·Î±×·¥¿¡ ´ëÇØ¼­´Â utilapi.c¿¡¼­ sqlInfoPrint ÇÔ¼ö´Â utilapi.h¿¡¼­ API_SQL_CHECK·Î¼­ÂüÁ¶µË´Ï´Ù. C embedded SQL ÇÁ·Î±×·¥¿¡ ´ëÇØ¼­´Â utilemb.sqc¿¡¼­ sqlInfoPrint ÇÔ¼ö´Â utilemb.h¿¡¼­ EMB_SQL_CHECK·Î¼­ ÂüÁ¶µË´Ï´Ù.

COBOL
CHECKERRÀº checkerr.cbl¶ó°í ÇÏ´Â ¿ÜºÎ ÇÁ·Î±×·¥ÀÔ´Ï´Ù.

ÀÌ ¿À·ù üũ À¯Æ¿¸®Æ¼¿¡ ´ëÇÑ ¼Ò½º Äڵ忡 ´ëÇØ¼­´Â ¿¹Á¦ ÇÁ·Î±×·¥¿¡¼­ GET ERROR MESSAGE »ç¿ëÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

C ¿¹: LOBEVAL.SQC

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "utilemb.h"
 
EXEC SQL INCLUDE SQLCA;
 
int main(int argc, char *argv[]) {
 
   EXEC SQL BEGIN DECLARE SECTION; (1)
      char userid[9];
      char passwd[19];
      sqlint32        hv_start_deptinfo;
      sqlint32        hv_start_educ;
      sqlint32        hv_return_code;
      SQL TYPE IS CLOB(5K) hv_new_section_buffer;
      SQL TYPE IS CLOB_LOCATOR hv_doc_locator1;
      SQL TYPE IS CLOB_LOCATOR hv_doc_locator2;
      SQL TYPE IS CLOB_LOCATOR hv_doc_locator3;
   EXEC SQL END DECLARE SECTION;
 
   printf( "Sample C program: LOBEVAL\n" );
 
   if (argc == 1) {
      EXEC SQL CONNECT TO sample;
	  EMB_SQL_CHECK("CONNECT TO SAMPLE");
   }
   else if (argc == 3) {
      strcpy (userid, argv[1]);
      strcpy (passwd, argv[2]);
      EXEC SQL CONNECT TO sample USER :userid USING :passwd;
      EMB_SQL_CHECK("CONNECT TO SAMPLE");
   }
   else {
      printf ("\nUSAGE: lobeval [userid passwd]\n\n");
      return 1;
   } /* endif */
   
   /* delete any instance of "A00130" from
     previous executions of this sample */
   EXEC SQL DELETE FROM emp_resume WHERE empno = 'A00130';
 
   /* Use a single row select to get the document */
   EXEC SQL SELECT resume INTO :hv_doc_locator1 FROM emp_resume
      WHERE empno = '000130' AND resume_format = 'ascii';  (2)
   EMB_SQL_CHECK("SELECT");
 
   /* Use the POSSTR function to locate the start of
      sections "Department Information" & "Education" */
   EXEC SQL VALUES (POSSTR(:hv_doc_locator1, 'Department Information'))
      INTO :hv_start_deptinfo; (3)
   EMB_SQL_CHECK("VALUES1");
 
   EXEC SQL VALUES (POSSTR(:hv_doc_locator1, 'Education'))
      INTO :hv_start_educ;
   EMB_SQL_CHECK("VALUES2");
 
   /* Replace Department Information Section with nothing */
   EXEC SQL VALUES (SUBSTR(:hv_doc_locator1, 1, :hv_start_deptinfo -1)
      || SUBSTR (:hv_doc_locator1, :hv_start_educ))
      INTO :hv_doc_locator2;
   EMB_SQL_CHECK("VALUES3");
 
   /* Move Department Information Section into the hv_new_section_buffer */
   EXEC SQL VALUES (SUBSTR(:hv_doc_locator1, :hv_start_deptinfo,
      :hv_start_educ -:hv_start_deptinfo)) INTO :hv_new_section_buffer;
   EMB_SQL_CHECK("VALUES4");
 
   /* Append our new section to the end (assume it has been filled in)
      Effectively, this just moves the Department Information to the bottom
      of the resume. */
   EXEC SQL VALUES (:hv_doc_locator2 || :hv_new_section_buffer) INTO
      :hv_doc_locator3;
   EMB_SQL_CHECK("VALUES5");
 
   /* Store this resume section in the table. This is where the LOB value
      bytes really move */
   EXEC SQL INSERT INTO emp_resume VALUES ('A00130', 'ascii',
      :hv_doc_locator3);  (4)
   EMB_SQL_CHECK("INSERT");
 
   printf ("LOBEVAL completed\n");
 
   /* free the locators */  (5)
   EXEC SQL FREE LOCATOR :hv_doc_locator1, :hv_doc_locator2, : hv_doc_locator3;
   EMB_SQL_CHECK("FREE LOCATOR");
 
   EXEC SQL CONNECT RESET;
   EMB_SQL_CHECK("CONNECT RESET");
   return 0;
}
/* end of program : LOBEVAL.SQC */

COBOL ¿¹: LOBEVAL.SQB

       Identification Division.
       Program-ID. "lobeval".
 
       Data Division.
       Working-Storage Section.
 
           copy "sqlenv.cbl".
           copy "sql.cbl".
           copy "sqlca.cbl".
 
           EXEC SQL BEGIN DECLARE SECTION END-EXEC. (1)
       01 userid            pic x(8).
       01 passwd.
         49 passwd-length   pic s9(4) comp-5 value 0.
         49 passwd-name     pic x(18).
       01 hv-start-deptinfo pic s9(9) comp-5.
       01 hv-start-educ     pic s9(9) comp-5.
       01 hv-return-code    pic s9(9) comp-5.
       01 hv-new-section-buffer USAGE IS SQL TYPE IS CLOB(5K).
       01 hv-doc-locator1   USAGE IS SQL TYPE IS CLOB-LOCATOR.
       01 hv-doc-locator2   USAGE IS SQL TYPE IS CLOB-LOCATOR.
       01 hv-doc-locator3   USAGE IS SQL TYPE IS CLOB-LOCATOR.
           EXEC SQL END DECLARE SECTION END-EXEC.
 
       77 errloc          pic x(80).
 
       Procedure Division.
       Main Section.
           display "Sample COBOL program: LOBEVAL".
 
      * Get database connection information.
           display "Enter your user id (default none): "
                with no advancing.
           accept userid.
 
           if userid = spaces
             EXEC SQL CONNECT TO sample END-EXEC
           else
             display "Enter your password : " with no advancing
             accept passwd-name.
 
      * Passwords in a CONNECT statement must be entered in a VARCHAR
      * format with the length of the input string.
           inspect passwd-name tallying passwd-length for characters
              before initial " ".
 
           EXEC SQL CONNECT TO sample USER :userid USING :passwd
               END-EXEC.
           move "CONNECT TO" to errloc.
           call "checkerr" using SQLCA errloc.
 
      * Delete any instance of "A00130" from previous executions
           EXEC SQL DELETE FROM emp_resume
                    WHERE empno = 'A00130' END-EXEC.
 
      * use a single row select to get the document
           EXEC SQL SELECT resume INTO :hv-doc-locator1  (2)
                    FROM emp_resume
                    WHERE empno = '000130'
                    AND resume_format = 'ascii' END-EXEC.
           move "SELECT" to errloc.
           call "checkerr" using SQLCA errloc.
 
      * use the POSSTR function to locate the start of sections
      * "Department Information" & "Education"
           EXEC SQL VALUES (POSSTR(:hv-doc-locator1,
                    'Department Information'))
                    INTO :hv-start-deptinfo END-EXEC. (3)
           move "VALUES1" to errloc.
           call "checkerr" using SQLCA errloc.
 
           EXEC SQL VALUES (POSSTR(:hv-doc-locator1,
                    'Education')) INTO :hv-start-educ END-EXEC.
           move "VALUES2" to errloc.
           call "checkerr" using SQLCA errloc.
 
      * replace Department Information section with nothing
           EXEC SQL VALUES (SUBSTR(:hv-doc-locator1, 1,
                    :hv-start-deptinfo - 1) ||
                    SUBSTR(:hv-doc-locator1, :hv-start-educ))
                    INTO :hv-doc-locator2 END-EXEC.
           move "VALUES3" to errloc.
           call "checkerr" using SQLCA errloc.
 
      * move Department Information section into hv-new-section-buffer
           EXEC SQL VALUES (SUBSTR(:hv-doc-locator1,
                    :hv-start-deptinfo,
                    :hv-start-educ - :hv-start-deptinfo))
                    INTO :hv-new-section-buffer END-EXEC.
           move "VALUES4" to errloc.
           call "checkerr" using SQLCA errloc.
 
      * Append the new section to the end (assume it has been filled)
      * Effectively, this just moves the Dept Info to the bottom of
      * the resume.
           EXEC SQL VALUES (:hv-doc-locator2 ||
                    :hv-new-section-buffer)
                    INTO :hv-doc-locator3 END-EXEC.
           move "VALUES5" to errloc.
           call "checkerr" using SQLCA errloc.
 
      * Store this resume in the table.
      * This is where the LOB value bytes really move.
           EXEC SQL INSERT INTO emp_resume  (4)
                    VALUES ('A00130', 'ascii', :hv-doc-locator3)
                    END-EXEC.
           move "INSERT" to errloc.
           call "checkerr" using SQLCA errloc.
 
           display "LOBEVAL completed".
 
           EXEC SQL FREE LOCATOR :hv-doc-locator1, :hv-doc-locator2, (5)
                    :hv-doc-locator3 END-EXEC.
           move "FREE LOCATOR" to errloc.
           call "checkerr" using SQLCA errloc.
 
           EXEC SQL CONNECT RESET END-EXEC.
           move "CONNECT RESET" to errloc.
           call "checkerr" using SQLCA errloc.
 
       End-Prog.
           stop run.

Ç¥½Ã±â º¯¼ö ¹× LOB À§Ä¡ ÁöÁ¤ÀÚ

ÀÀ¿ëÇÁ·Î±×·¥¿¡ ÀÖ´Â ÀÏ¹Ý È£½ºÆ® º¯¼öÀÇ °æ¿ì, È£½ºÆ® º¯¼ö¿¡ ³Î(NULL) °ªÀ» ¼±ÅÃÇÒ ¶§ ±× °ªÀÌ ³Î(NULL)ÀÓÀ» ÀǹÌÇϴ ǥ½Ã±â º¯¼ö¿¡ À½¼ö °ªÀÌ ÇÒ´çµË´Ï´Ù. ±×·¯³ª LOB À§Ä¡ ÁöÁ¤ÀÚÀÇ °æ¿ì Ç¥½Ã±â º¯¼öÀÇ Àǹ̴ ¾à°£¾¿ ´Ù¸¨´Ï´Ù. À§Ä¡ ÁöÁ¤ÀÚ È£½ºÆ® º¯¼ö ÀÚü´Â °áÄÚ ³Î(NULL)ÀÏ ¼ö ¾øÀ¸¹Ç·Î, À½¼ö Ç¥½Ã±â º¯¼ö °ªÀº LOB À§Ä¡ ÁöÁ¤ÀÚ¿¡ ÀÇÇØ Ç¥½ÃµÇ´Â LOB °ªÀÌ ³Î(NULL)ÀÓÀ» ³ªÅ¸³À´Ï´Ù. ³Î(NULL) Á¤º¸´Â Ç¥½Ã±â º¯¼ö °ªÀ» »ç¿ëÇÏ¿© Ŭ¶óÀÌ¾ðÆ®¿¡ Áö¿ª »óÅ·Π³²¾Æ ÀÖ½À´Ï´Ù. ¼­¹ö´Â À¯È¿ÇÑ À§Ä¡ ÁöÁ¤ÀÚ·Î ³Î(NULL) °ªÀ» ÃßÀûÇÏÁö ¾Ê½À´Ï´Ù.


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