第09章 大对象、用户定义的不同类型.doc

第9章 大对象和自定义结构化类型数据库经常需要存储大型数据对象，如视频数据、音频数据、图片数据，DB2为存储这些对象提供了嵌入式类型支持，本章主要介绍如何使用大对象和自定义结构类型，开发人员能够使用这个功能特性实现应用开发。本章主要内容：l 大对象l 用户定义的不同类型l 用户定义的结构类型9.1 大对象VARCHAR和VARGRAPHIC数据类型具有32KB的存储限制。这些数据类型可以用于存储小的文件数据，但是，应用经常需要存储大文本数据，如声音、视频、图片和图象信息。DB2提供了三种类型，可以用来存储这些数据，最大能够到达2GB字节的数据。这三种大对象数据类型是BLOB、CLOB和DBCLOB。每个DB2表都可以包含与LOB数据相关的任何单个LOB值，单个列可以包含24GB的LOB数据，一个表可以包含2GB字节的LOB数据。不同的数据库位置存储所有的大对象数据值在表的记录外面。表中的每行都具有一个大对象的描述符。当操纵LOB的时候，访问大对象描述符价格导致小量溢出。每个大对象列的最大尺寸是在CREATE TABLE语句声明的大对象类型的一部分。大对象列的最大尺寸决定了列中的任何LOB描述符的最大尺寸。其结果是，它决定了能够满足单行的所有数据类型的列的数量。LOB描述符的空间从60到300字节，这依赖于对应列的最大尺寸。使用潜在的大对象数据，在移动和移出数据库的时候，LOB将显示数据库系统的性能降级。即使DB2不允许对LOB值的日志信息超过1GB，但是，LOB的日志信息将很快达到日志容量。如果LOB的日志超过1GB，将返回一个SQLCODE-355。当选择一个LOB值的时候，具有三个选项：1 选择整个LOB的值到一个本地变量。整个LOB值从服务器拷贝到客户机上。这样做的效率很低，有时是不可行的。本地变量使用客户内存缓冲区，可能没有足够的容量来存储LOB值。2 选择一个LOB定位器到本地变量。LOB值依然保留在服务器上，LOB定位器移动到客户上。如果LOB的值非常大，需要作为一个或者多个子序列SQL语句的输入值，最好将值保存在定位器中。使用定位器减少了客户机和服务器之间通信负载。3 选择整个LOB值到单个文件引用变量中，LOB值移动到客户机上的文件中。不需要通过应用内存。一个大对象定位器是带有4字节值的本地变量，标识数据库服务器中的单个LOB。LOB定位器为用户提供了在应用中操纵非常大的数据的机制，使得将整个LOB值存储在应用可以访问的客户机器上。后续语句能够使用定位器对数据执行操作，不需要提取整个大对象。定位器变量用于减少访问LOB的存储需求，通过减少服务器和客户机之间的数据流，提高了执行性能。LOB定位器对于下面几种情况，非常适合：1 当移动非长大的LOB数据的一小部分到客户程序。2 当整个LOB不能放在应用内存中的时候。3 当程序需要来自LOB表达式的临时LOB值，不需要保存 结果。LOB定位器能够表示与LOB表达式相关的值，例如，下面的LOB定位器能够表示与下面这个表达式相关的值：SUBSTR( CONCAT CONCAT, , )理解LOB定位器是表示一个值，而不是表示数据库行和位置是非常重要的。值被选择到定位器中，用户将不能对源行和表执行操作，来影响定位器的值。与定位器相关的值是合法的，直到事务结束，或者直到定位器被显式释放。LOB定位器是用于在事务中引用LOB值的一种机制，不会在创建的事务外面存在。FREE LOCATOR语句将释放定位器的值，通用，提交和回滚事务，将释放与事务相关的所有LOB定位器。而且，LOB定位器不是一个数据库类型，它不能存储到数据库中，其结果是不能参与视图和约束检查。但是，由于LOB定位器是LOB类型的客户描述，对于LOB定位器具有SQLTYPE，使得它们能够在SQLDA结构中描述，被FETCH、OPEN和EXECUTE使用。如果需要从LOB提取数据，可以使用LOB定位器。如果需要访问的LOB非常大，这是一个比较好的选择。如果传递的LOB数据量很小，可以将数据直接传递到客户，避免使用定位器。下面是在C中使用嵌套SQL的示例，使用LOB定位器提取LOB值：1 声明LOB定位器本地变量。EXEC SQL BEGIN DECLARE SECTION;char number7;sqlint32 deptInfoBeginLoc;sqlint32 deptInfoEndLoc;SQL TYPE IS CLOB_LOCATOR resume;SQL TYPE IS CLOB_LOCATOR deptBuffer;short lobind;char buffer1000=;char userid9;char passwd19;EXEC SQL END DECLARE SECTION;2 连接应用到数据库。EXEC SQL DECLARE c1 CURSOR FORSELECT empno, resume FROM emp_resume WHERE resume_format=asciiAND empno A00130;EXEC SQL OPEN c1;3 声明和打开游标。EXEC SQL FETCH c1 INTO :number, :resume :lobind;4 提取LOB值到本地变量定位器。EXEC SQL VALUES (POSSTR(:resume, Department Information)INTO :deptInfoBeginLoc;5 评估LOB定位器。a. 定位department information部分的开始。EXEC SQL VALUES (POSSTR(:resume, Department Information)INTO :deptInfoBeginLoc;b. 定位Education部分的开始。EXEC SQL VALUES (POSSTR(:resume, Education)INTO :deptInfoEndLoc;c. 使用SUBSTR厚的Department Information的开始。EXEC SQL VALUES(SUBSTR(:resume, :deptInfoBeginLoc,:deptInfoEndLoc - :deptInfoBeginLoc) INTO :deptBuffer;d. 提交Department Inormation部分到:buffer变量。EXEC SQL VALUES(:buffer | :deptBuffer) INTO :buffer;6 释放LOB定位器resume和deptBuffer；EXEC SQL FREE LOCATOR :resume, :deptBuffer;7 关闭游标。EXEC SQL CLOSE c1;8 终止程序。LOB值的字节只有在赋予一个LOB表达式给目的地以后才能被移动。这意味着十哦那个串函数和操作符地LOB值定位器能够创建表达式，它的值在赋值的时候才被传递。这种技术称为LOB表达式的defer评估。Defer评估使得BD2能够提高LOB I/O的性能。这是因为LOB函数优化器能够尝试转换LOB表达式为替换表达式。这种替换表达式生成相同的结果，但是需要更少的磁盘I/O操作。下面的示例在C中使用嵌入式SQL。Defer LOB表达式的评估。1 声明LOB定位器本地变量。EXEC SQL BEGIN DECLARE SECTION;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;char userid9;char passwd19;EXEC SQL END DECLARE SECTION;2 连接应用到数据库。3 提取LOB的值到本地变量定位器。EXEC SQL SELECT resume INTO :hv_doc_locator1 FROM emp_resumeWHERE empno= 000130 AND resume_format = ascii;4 使用定位器操纵LOB数据。这五个语句不移动LOB域中包含的实际数据，操作LOB数据。a. 使用POSSTR函数定位Department Information部分的开始。EXEC SQL VALUES (POSSTR(:hv_doc_locator1, Department Information)INTO :hv_start_deptinfo;b. 使用POSSTR函数定位Education部分的开始。EXEC SQL VALUES (POSSTR(:hv_doc_locator1, Education)INTO :hv_start_educ;c. 替换Department Information部分。EXEC SQL VALUES (SUBSTR(:hv_doc_locator1, 1, :hv_start_deptinfo -1)| SUBSTR (:hv_doc_locator1, :hv_start_educ)INTO :hv_doc_locator2;d. 移动Department Information部分到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;e. 将新的部分移到最后，这只需移动Department Information到resume的下面。EXEC SQL VALUES (:hv_doc_locator2 | :hv_new_section_buffer)INTO :hv_doc_locator3;5 移动LOB数据到目标地。EXEC SQL INSERT INTO emp_resumeVALUES (A00130, ascii, :hv_doc_locator3);6 释放LOB定位器hv_doc_locator1, hv_doc_locator2, hv_doc_locator3:EXEC SQL FREE LOCATOR :hv_doc_locator1, :hv_doc_locator2,: hv_doc_locator3;7 终止程序。如果需要访问在数据库之外的CLOB列的数据，将数据写到文本文件中。这个过程的示例在C中使用嵌入式SQL。将数据从CLOB列写到文本中的过程如下：1 声明CLOB FILE REFERENCE本地变量。EXEC SQL BEGIN DECLARE SECTION;SQL TYPE IS CLOB_FILE resume;char userid9;char passwd19;short lobind;EXEC SQL END DECLARE SECTION;2 连接应用到数据库。3 设置CLOB FILE REFERENCE本地变量。strcpy (, RESUME.TXT);_length = strlen(RESUME.TXT);resume.file_options = SQL_FILE_OVERWRITE;4 将CLOB列的数据选择出来，插入特定的文本中。EXEC SQL SELECT resume INTO :resume :lobind FROM emp_resumeWHERE resume_format=ascii AND empno=000130;5 终止程序。如果需要数据库处理在文本文件中的CLOB数据，将它插入到CLOB列中，下面是具体的过程：1 声明CLOB FILE REFERENCE本地变量。hv_text_file表示文件。EXEC SQL BEGIN DECLARE SECTION;SQL TYPE IS CLOB_FILE hv_text_file;EXEC SQL END DECLARE SECTION;2 连接应用到数据库。3 设置CLOB FILE REFERENCE本地变量。Userid表示当前用户的目录，dirname表示目录名。filnam.1表示需要插入数据到表中的文件，clobtab是clobtab具有CLOB数据类型的表名。strcpy(hv_text_, /u/userid/dirname/filnam.1);hv_text__length = strlen(/u/userid/dirname/filnam.1);hv_text_file.file_options = SQL_FILE_READ;4 从hv_text_file插入数据到CLOB表中。EXEC SQL INSERT INTO CLOBTABVALUES(:hv_text_file);5 终止程序。下面是一个完整的C程序，描述了如何使用CLOB数据类型，程序所使用的SQL语句包括：l SELECT INTOl INSERTl DELETEl DECLARE CURSORl OPENl FETCHl CLOSEl VALUESl FREE LOCATOR代码示例如下：#include #include #include #include #include #include #include utilemb.hint BlobFileUse(void);int ClobUse(void);int ClobFileUse(void);int ClobLocatorUse(void);EXEC SQL BEGIN DECLARE SECTION; SQL TYPE IS BLOB_FILE blobFilePhoto; char photoFormat10; char empno7; SQL TYPE IS CLOB(5 K) clobResume; SQL TYPE IS CLOB_FILE clobFileResume; SQL TYPE IS CLOB_LOCATOR clobLocResume; sqlint32 posBeginDeptInfo; sqlint32 posBeginEducation; SQL TYPE IS CLOB(5 K) clobDeptInfo; SQL TYPE IS CLOB_LOCATOR clobLocShortResume; SQL TYPE IS CLOB_LOCATOR clobLocNewResume; short lobind;EXEC SQL END DECLARE SECTION;int main(int argc, char *argv) int rc = 0; char dbAliasSQL_ALIAS_SZ + 1; char userUSERID_SZ + 1; char pswdPSWD_SZ + 1; /* check the command line arguments */ rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd); if (rc != 0) return rc; printf(nTHIS SAMPLE SHOWS HOW TO USE THE LOB DATA TYPE.n); /* connect to the database */ rc = DbConn(dbAlias, user, pswd); if (rc != 0) return rc; rc = BlobFileUse(); rc = ClobUse(); rc = ClobFileUse(); rc = ClobLocatorUse(); /* disconnect from the database */ rc = DbDisconn(dbAlias); if (rc != 0) return rc; return 0; /* main */int BlobFileUse(void) struct sqlca sqlca; char fileName15; printf(n-); printf(nUSE THE SQL STATEMENTS:n); printf( SELECT INTOn); printf( INSERTn); printf( DELETEn); printf(TO SHOW HOW TO USE A BLOB FILE.n);#if(defined(DB2NT) strcpy(photoFormat, bitmap); strcpy(fileName, photo.BMP);#else /* UNIX */ strcpy(photoFormat, gif); strcpy(fileName, photo.GIF);#endif /* initialize blobFile */ strcpy(blobFileP, fileName); blobFileP_length = strlen(blobFileP); /* read BLOB data */ printf(n Read BLOB data in the file %s.n, fileName); blobFilePhoto.file_options = SQL_FILE_OVERWRITE; EXEC SQL SELECT picture INTO :blobFilePhoto:lobind FROM emp_photo WHERE photo_format = :photoFormat AND empno = 000130; EMB_SQL_CHECK(BLOB data - read); if (lobind 0) printf( NULL LOB indicated.n); return 1; /*写BLOB数据 */ printf( Write BLOB data from the file %s.n, fileName); blobFilePhoto.file_options = SQL_FILE_READ; EXEC SQL INSERT INTO emp_photo(empno, photo_format, picture) VALUES(000137, :photoFormat, :blobFilePhoto); EMB_SQL_CHECK(BLOB data - write); /* 删除新的记录*/ printf( Delete the new record from the database.n); EXEC SQL DELETE FROM emp_photo WHERE empno = 000137; EMB_SQL_CHECK(new record - delete); return 0; /* BlobFileUse */int ClobUse(void) struct sqlca sqlca; int charNb; int lineNb; printf(n-); printf(nUSE THE SQL STATEMENTS:n); printf( DECLARE CURSORn); printf( OPENn); printf( FETCHn); printf( CLOSEn); printf(TO SHOW HOW TO USE THE CLOB DATA TYPE.n); printf(n READ THE CLOB DATA:n); EXEC SQL DECLARE c1 CURSOR FOR SELECT empno, resume FROM emp_resume WHERE resume_format = ascii AND empno = 000130; EXEC SQL OPEN c1; EMB_SQL_CHECK(cursor - open); EXEC SQL FETCH c1 INTO :empno, :clobResume:lobind; EMB_SQL_CHECK(cursor - fetch); while (sqlca.sqlcode != 100) if (lobind 0) printf( NULL LOB indicated.n); else printf(n Empno: %sn, empno); printf( Resume length: %dn, clobResume.length); printf( First 15 lines of the resume:n); for (charNb = 0, lineNb = 0; lineNb 15 & charNb clobResume.length; charNb+) printf(%c, clobResume.datacharNb); if (clobResume.datacharNb = n) printf( ); lineNb+; EXEC SQL FETCH c1 INTO :empno, :clobResume:lobind; EMB_SQL_CHECK(cursor - fetch); EXEC SQL CLOSE c1; EMB_SQL_CHECK(cursor - close); return 0; /* ClobUse */int ClobFileUse(void) struct sqlca sqlca; char fileName15; printf(n-); printf(nUSE THE SQL STATEMENT:n); printf( SELECT INTOn); printf(TO SHOW HOW TO USE A CLOB FILE.n); strcpy(fileName, resume.TXT); /* initialize clobFile */ strcpy(clobFileR, fileName); clobFileR_length = strlen(clobFileR); /*读 CLOB 数据 */ printf(n Read CLOB data in the file %s.n, fileName); clobFileResume.file_options = SQL_FILE_OVERWRITE; EXEC SQL SELECT resume INTO :clobFileResume:lobind FROM emp_resume WHERE resume_format = ascii AND empno = 000130; EMB_SQL_CHECK(CLOB data - read); if (lobind 0) printf( NULL LOB indicated.n); return 1; return 0; /* ClobFileUse */int ClobLocatorUse(void) struct sqlca sqlca; int charNb; printf(n-); printf(nUSE THE SQL STATEMENTS:n); printf( SELECT INTOn); printf( INSERTn); printf( DELETEn); printf( VALUESn); printf( FREE LOCATORn); printf(TO SHOW HOW TO USE A CLOB LOCATOR.n); printf(n *n); printf( ORIGINAL RESUME - VIEWn); printf( *n); EXEC SQL SELECT resume INTO :clobResume FROM emp_resume WHERE empno = 000130 AND resume_format = ascii; EMB_SQL_CHECK(old resume - read); for (charNb = 0; charNb clobResume.length; charNb+) printf(%c, clobResume.datacharNb); printf(n *n); printf( NEW RESUME - CREATEn); printf( *n); EXEC SQL SELECT resume INTO :clobLocResume FROM emp_resume WHERE empno = 000130 AND resume_format = ascii; EMB_SQL_CHECK(resume - read); EXEC SQL VALUES(POSSTR(:clobLocResume, Department Information) INTO :posBeginDeptInfo; EMB_SQL_CHECK(Department Info position - get); EXEC SQL VALUES(POSSTR(:clobLocResume, Education) INTO :posBeginEducation; EMB_SQL_CHECK(Education position - get); printf(n Create short resume without Department Info.n); EXEC SQL VALUES(SUBSTR(:clobLocResume, 1, :posBeginDeptInfo - 1) | SUBSTR(:clobLocResume, :posBeginEducation) INTO :clobLocShortResume; EMB_SQL_CHECK(short resume - create); EXEC SQL VALUES(SUBSTR(:clobLocResume, :posBeginDeptInfo, :posBeginEducation - :posBeginDeptInfo) INTO :clobDeptInfo; EMB_SQL_CHECK(Department Info - create); printf( Append Department Info at the end of Short resume.n); EXEC SQL VALUES(:clobLocShortResume | :clobDeptInfo) INTO :clobLocNewResume; EMB_SQL_CHECK(new resume - create); printf( Insert the new resume in the database.n); EXEC SQL INSERT INTO emp_resume(empno, resume_format, resume) VALUES(000137, ascii, :clobLocNewResume); EMB_SQL_CHECK(new resume - write); EXEC SQL FREE LOCATOR :clobLocResume, :clobLocShortResume, :clobLocNewResume; EMB_SQL_CHECK(locators - free); printf(n *n); printf( NEW RESUME - VIEWn); printf( *n); EXEC SQL SELECT resume INTO :clobResume FROM emp_resume WHERE empno = 000137; EMB_SQL_CHECK(new resume - read); for (charNb = 0; charNb clobResume.length; charNb+) printf(%c, clobResume.datacharNb); printf(n *n); printf( NEW RESUME - DELETEn); printf( *n); EXEC SQL DELETE FROM emp_resume WHERE empno = 000137; EMB_SQL_CHECK(new resume - delete); return 0; /* LobLocatorUse */9.2 用户定义的不同类型用户自定义数据类型能够从已有数据类型分离的数据类型。但是,需要考虑它们之间的差异和兼容性.UDT使得用户能够扩展嵌入式类型,创建自定义数据类型。对于用户自定义数据类型具有两种分类：l 区别类：同嵌入式数据类型共享一样的描述方式。l 结构类型：具有单独类型的属性序列的描述。例如，区别类型表示多种利润，如US_Dollar和Canadian_Dollar。这两种类型在内部表示嵌入式数据类型。例如，如果定义两种利润为DECIMAL，它们在系统中表示十进制数据类型。DB2具有存储和操作大对象的嵌入式数据类型。区别类型可以基于其中一个大对象数据类型，用于存储声音和视频流。下面的示例显示了区别类型的创建：CREATE DISTINCT TYPE AUDIO AS BLOB (1M)虽然AUDIO具有嵌入式数据类型BLOB相同的表示，但是它可以被认为是不同的数据类型，不能同BLOB和任何其他数据类型兼容。使用区别数据类型具有如下优势：1 扩展性：通过定义新的数据类型，用户可以增加DB2的类型集合，支持应用。2 灵活性：用户可以使用用户自定义函数UDF定义新的类型的语法和行为。3 一致性行为：强类型能够保证区别类型能够正常操作。保证了在区别类型上的函数能够应用到区别类型的实例。4 封装性：应用到区别类型上的函数和操作符集合定义了区别类型的行为。这提供了实现上的灵活性，因为运行应用程序不需要依赖于所选择的类型的内部描述。5 性能：区别类型能够集成到数据库管理器中。由于区别类型的内部表示同嵌入式数据类型相同，它们共享同样高效的代码，用于实现嵌入式函数、比较操作符、索引等等。当定义一个区别类型的时候，执行CREATE DISTINCT TYPE语句，定义类型名和源类型，例如：CREATE DISTINCT TYPE new_type AS SMALLINT WITH COMPARISONS由于上面定义的区别类型基于SMALLINT，WITH COMPARISONS参数必须定义。为了创建一个基于区别类型的表，执行如下过程：1 定义区别类型。CREATE DISTINCT TYPE t_educ AS SMALLINT WITH COMPARISONS2 创建表，命名区别类型，T_EDUC作为列类型。CREATE TABLE employee(empnoCHAR(6) NOT NULL,firstnme VARCHAR(12) NOT NULL,lastname VARCHAR(15) NOT NULL,workdept CHAR(3),phoneno CHAR(4),photo BLOB(10M) NOT NULL,edlevel T_EDUC)IN RESOURCE下面是一个C程序，显示了如何创建、使用和删除用户自定义区别类型。使用的SQL语句包括：l CREATE DISTINCT TYPE l EXECUTE IMMEDIATEl DROP DISTINCT TYPE代码示例如下：#include #include #include #include #include #include utilemb.hint UdtCreate(void);int UdtUse(void);int UdtDrop(void);EXEC SQL BEGIN DECLARE SECTION; char strStmt257;EXEC SQL END DECLARE SECTION;int main(int argc, char *argv) int rc = 0; char dbAliasSQL_ALIAS_SZ + 1; char userUSERID_SZ + 1; char pswdPSWD_SZ + 1; /* 检查命令行参数*/ rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd); if (rc != 0) return rc; printf(nTHIS SAMPLE SHOWS HOW TO CREATE/USE/DROP UDTs.n); /* 连接数据库*/ rc = DbConn(dbAlias, user, pswd); if (rc != 0) return rc; rc = UdtCreate(); rc = UdtUse(); rc = UdtDrop(); /* disconnect from the database */ rc = DbDisconn(dbAlias); if (rc != 0) return rc; return 0; /* end main */int UdtCreate(void) struct sqlca sqlca; printf(n-); printf(nUSE THE SQL STATEMENTS:n); printf( CREATE DISTINCT TYPEn); printf( COMMITn); printf(TO CREATE UDTs.n); printf(n CREATE DISTINCT TYPE udt1 AS INTEGER WITH COMPARISONS); EXEC SQL CREATE DISTINCT TYPE udt1 AS INTEGER WITH COMPARISONS; EMB_SQL_CHECK(udt1 - create); printf(n CREATE DISTINCT TYPE udt2 AS CHAR(2) WITH COMPARISONS); EXEC SQL CREATE DISTINCT TYPE udt2 AS CHAR(2) WITH COMPARISONS; EMB_SQL_CHECK(udt2 - create); printf( n CREATE DISTINCT TYPE udt3 AS DECIMAL(7, 2) WITH COMPARISONSn); EXEC SQL CREATE DISTINCT TYPE udt3 AS DECIMAL(7, 2) WITH C