Large Object Locators . . . 166 Retrieving a LOB Value with a LOB Locator 168 Deferring the Evaluation of LOB Expressions 170 Large Object File Reference Variables . . . 173
Writing Data from a CLOB Column to a Text File . . . 175 Inserting Data from a Text File into a CLOB Column . . . 176
Large Object Usage
The VARCHAR and VARGRAPHIC data types have a limit of 32K bytes of storage. While this may be sufficient for small to medium size text data, applications often need to store large text documents. They may also need to store a wide variety of additional data types such as audio, video, drawings, mixed text and graphics, and images. DB2®provides three data types to store
these data objects as strings of up to two gigabytes (GB) in size. The three large object (LOB) data types are Binary Large Objects (BLOBs), Character Large Objects (CLOBs), and Double-Byte Character Large Objects (DBCLOBs).
Note: CLOBs can contain either single-byte or double-byte characters. DBCLOBs can contain either four-byte or double byte characters. Each DB2 table may have a large amount of associated LOB data. Although any single LOB value may not exceed 2 gigabytes, a single row may contain as much as 24 gigabytes of LOB data, and a table may contain as much as 2 terabytes of LOB data.
A separate database location stores all large object values outside their records in the table. There is a large object descriptor for each large object in each row in a table. The large object descriptor contains control information used to access the large object data stored elsewhere on disk. It is the storing of large object data outside their records that allows LOBs to be 2 GB in size.
Accessing the large object descriptor causes a small amount of overhead when manipulating LOBs. (For storage and performance reasons you would likely not want to put small data items into LOBs.)
The maximum size for each large object column is part of the declaration of the large object type in the CREATE TABLE statement. The maximum size of a large object column determines the maximum size of any LOB descriptor in that column. As a result, it also determines how many columns of all data types can fit in a single row. The space used by the LOB descriptor in the row ranges from approximately 60 to 300 bytes, depending on the maximum size of the corresponding column.
The lob-options-clause on CREATE TABLE gives you the choice to log (or not) the changes made to the LOB column(s). This clause also allows for a compact representation for the LOB descriptor (or not). This means you can allocate only enough space to store the LOB or you can allocate extra space for future append operations to the LOB. The tablespace-options-clause allows you to identify a LONG table space to store the column values of long field or LOB data types.
With their potentially large size, LOBs can slow down the performance of your database system significantly when moved into or out of a database. Even though DB2 does not allow logging of a LOB value greater than 1 GB, LOB values with sizes approaching 1GB can quickly push the database log to near capacity. An error, SQLCODE -355 (SQLSTATE 42993), results from attempting to log a LOB greater than 1 GB in size. The lob-options-clause in the CREATE TABLE and ALTER TABLE statements allows users to turn off logging for a particular LOB column. Although setting the option to NOT LOGGED will improve performance, changes to the LOB values after the most recent backup are lost during roll-forward recovery.
When selecting a LOB value, you have three options:
1. Select the entire LOB value into a host variable. The entire LOB value is copied from the server to the client. This is inefficient and is sometimes not feasible. Host variables use the client memory buffer, which may not have the capacity to hold larger LOB values.
2. Select just a LOB locator into a host variable. The LOB value remains on the server; the LOB locator moves to the client. If the LOB value is very large and is needed only as an input value for one or more subsequent SQL statements, then it is best to keep the value in a locator. The use of a locator eliminates any client/server communication traffic needed to transfer the LOB value to the host variable and back to the server.
3. Select the entire LOB value into a file reference variable. The LOB value (or a part of it) is moved to a file at the client without going through the application’s memory.
Related concepts:
v “Large Object Locators” on page 166
v “Large Object File Reference Variables” on page 173
Large Object Locators
A large object locator (or LOB locator) is a host variable with a 4-byte value that represents a single LOB value in the database server. LOB locators provide users with a mechanism by which they can easily manipulate very large objects in application programs without requiring them to store the entire LOB value on the client machine where the application program may be
running. Subsequent statements can then use the locators to perform operations on the data without necessarily retrieving the entire large object. Locator variables are used to reduce the storage requirements for applications that access LOBs, and improve their performance by reducing the flow of data between the client and the server.
LOB locators are ideally suited for a number of programming scenarios:
1. When moving only a small part of a much larger LOB to a client program.
2. When the entire LOB cannot fit in the application’s memory.
3. When the program needs a temporary LOB value from a LOB expression but does not need to save the result.
LOB locators can also represent the value associated with a LOB expression. For example, a LOB locator might represent the value associated with: SUBSTR( <lob 1> CONCAT <lob 2> CONCAT
<lob 3>, <start>, <length> )
It is important to understand that a LOB locator represents a value, not a row or location in the database. Once a value is selected into a locator, there is no operation that one can perform on the original row or table that will affect the value that is referenced by the locator. The value associated with a locator is valid until the transaction ends, or until the locator is explicitly freed, whichever comes first. Locators do not force extra copies of the data in order to provide this function. Instead, the locator mechanism stores a description of the base LOB value. The materialization of the LOB value (or expression, as shown above) is deferred until it is actually assigned to some location -- either into a user buffer in the form of a host variable or into another record’s field value in the database.
A LOB locator is only a mechanism used to refer to a LOB value during a transaction; it does not persist beyond the transaction in which it was created. The FREE LOCATOR statement releases a locator from its associated value. In a similar way, a commit or roll-back operation frees all LOB locators
associated with the transaction. Furthermore, a LOB locator is not a database type; it is never stored in the database and, as a result, cannot participate in views or check constraints. However, since a LOB locator is a client
representation of a LOB type, there are SQLTYPEs for LOB locators so that they can be described within an SQLDA structure that is used by FETCH, OPEN and EXECUTE statements. They can also be passed between DB2® and UDFs.
For normal host variables in an application program, when selecting a NULL value into a host variable, the indicator variable is set to -1, signifying that the value is NULL. In the case of LOB locators, however, the meaning of indicator variables is slightly different. Since a locator host variable itself can never be
NULL, a negative indicator variable value indicates that the LOB value represented by the LOB locator is NULL.
Related concepts:
v “Large Object Usage” on page 165
Related tasks:
v “Retrieving a LOB Value with a LOB Locator” on page 168 v “Deferring the Evaluation of LOB Expressions” on page 170
Related samples:
v “dtlob.out -- HOW TO USE THE LOB DATA TYPE (C)” v “dtlob.sqc -- How to use the LOB data type (C)”
v “dtlob.out -- HOW TO USE THE LOB DATA TYPE (C++)” v “dtlob.sqC -- How to use the LOB data type (C++)” v “dtLob.bas -- Get/set Large Objects (LOBs)”
v “DtLob.java -- How to use LOB data type (JDBC)” v “DtLob.out -- HOW TO USE LOB DATA TYPE (JDBC)”
v “lobeval.sqb -- Demonstrates how to use a Large Object (LOB) (IBM COBOL)”
v “lobloc.sqb -- Demonstrates the use of LOB locators (IBM COBOL)”
Retrieving a LOB Value with a LOB Locator
If you need to extract data from a LOB you can use LOB locators. This is a good alternative if the LOB to be accessed is large. Transferring the entire LOB to a client when only a subset of the LOB data is needed is avoided with the use of locators.
The example uses embedded SQL in C.
Procedure:
To retrieve a LOB value with a LOB locator:
1. Declare the LOB locator host variables: EXEC SQL BEGIN DECLARE SECTION;
char number[7];
sqlint32 deptInfoBeginLoc; sqlint32 deptInfoEndLoc;
SQL TYPE IS CLOB_LOCATOR resume; SQL TYPE IS CLOB_LOCATOR deptBuffer; short lobind;
char buffer[1000]=""; char userid[9]; char passwd[19];
EXEC SQL END DECLARE SECTION; In the host variable declaration section:
v number will contain the value returned by empno in the SELECT statement to be issued by the cursorc1.
v deptInfoBeginLoc and deptInfoEnd will temporarily hold LOB locator values.
v resume and deptBuffer are LOB locators.
v lobind is used to indicate if the LOB read is null or not. v buffer will contain the data extracted from the LOB.
v userid and passwd represent a userid and password combination, which are needed for the application to connect to a database.
2. Connect the application to the database.
3. Declare and open a cursor: EXEC SQL DECLARE c1 CURSOR FOR
SELECT empno, resume FROM emp_resume WHERE resume_format=’ascii’ AND empno<> ’A00130’;
EXEC SQL OPEN c1;
4. Fetch the LOB value into the host variable locator. EXEC SQL FETCH c1 INTO :number, :resume :lobind;
5. Evaluate the LOB locator:
a. Locate the beginning ofDepartment Information section: EXEC SQL VALUES (POSSTR(:resume, ’Department Information’))
INTO :deptInfoBeginLoc;
b. Locate the beginning ofEducation section (end of Department Information):
EXEC SQL VALUES (POSSTR(:resume, ’Education’)) INTO :deptInfoEndLoc;
c. Obtain only the Department Information section by using SUBSTR: EXEC SQL VALUES(SUBSTR(:resume, :deptInfoBeginLoc,
:deptInfoEndLoc - :deptInfoBeginLoc)) INTO :deptBuffer;
d. Append theDepartment Information section to the :buffer variable: EXEC SQL VALUES(:buffer || :deptBuffer) INTO :buffer;
6. Free the LOB locatorsresume and deptBuffer: EXEC SQL FREE LOCATOR :resume, :deptBuffer;
7. Close the cursor: EXEC SQL CLOSE c1;
Related concepts:
v “Large Object Usage” on page 165 v “Large Object Locators” on page 166
Related tasks:
v “Connecting an Application to a Database” in the Application Development Guide: Programming Client Applications
v “Ending an Application Program” in the Application Development Guide: Programming Client Applications
v “Deferring the Evaluation of LOB Expressions” on page 170
Related samples:
v “dtlob.out -- HOW TO USE THE LOB DATA TYPE (C)” v “dtlob.sqc -- How to use the LOB data type (C)”
v “dtlob.out -- HOW TO USE THE LOB DATA TYPE (C++)” v “dtlob.sqC -- How to use the LOB data type (C++)” v “dtLob.bas -- Get/set Large Objects (LOBs)”
v “DtLob.java -- How to use LOB data type (JDBC)” v “DtLob.out -- HOW TO USE LOB DATA TYPE (JDBC)”
v “lobeval.sqb -- Demonstrates how to use a Large Object (LOB) (IBM COBOL)”
v “lobloc.sqb -- Demonstrates the use of LOB locators (IBM COBOL)”
Deferring the Evaluation of LOB Expressions
The bytes of a LOB value do not move until you assign a LOB expression to a target destination. This means that a LOB value locator used with string functions and operators can create an expression where the evaluation is postponed until the time of assignment. This technique is known as deferring the evaluation of a LOB expression.
Deferring evaluation gives DB2 an opportunity to increase LOB I/O performance. This occurs because the LOB function optimizer attempts to transform the LOB expressions into alternative expressions. These alternative expressions produce equivalent results and usually require fewer disk I/Os. The example uses embedded SQL in C.
Procedure:
To defer the evaluation of a LOB expression:
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 userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION; In the host variable declaration section:
v hv_start_deptinfo, hv_return_code, and hv_start_educ will temporarily hold LOB locator values.
v hv_new_section_buffer will contain the data extracted from the LOB. v hv_doc_locator1, hv_doc_locator2, and hv_doc_locator3 are LOB
locators.
v userid and passwd represent a userid and password combination, which are needed for the application to connect to a database.
2. Connect the application to the database.
3. Fetch the LOB value into the host variable locator:
EXEC SQL SELECT resume INTO :hv_doc_locator1 FROM emp_resume WHERE empno= ’000130’ AND resume_format = ’ascii’;
4. Manipulate LOB data with locators. These five statements manipulate LOB data without moving the actual data contained in the LOB field.
a. Use the POSSTR function to locate the start of theDepartment Information section:
EXEC SQL VALUES (POSSTR(:hv_doc_locator1, ’Department Information’)) INTO :hv_start_deptinfo;
b. Use the POSSTR function to locate the start of theEducation section: EXEC SQL VALUES (POSSTR(:hv_doc_locator1, ’Education’))
INTO :hv_start_educ;
c. Replace the 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;
d. Move the 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;
e. Append the new section to the end. Effectively, this just moves the Department Information section to the bottom of the resume.
EXEC SQL VALUES (:hv_doc_locator2 || :hv_new_section_buffer) INTO :hv_doc_locator3;
5. Move LOB data to the target destination: EXEC SQL INSERT INTO emp_resume
VALUES (’A00130’, ’ascii’, :hv_doc_locator3);
The evaluation of the LOB assigned to the target destination is postponed until this statement. It is at this point that LOB value bytes finally move.
6. Free the LOB locatorshv_doc_locator1, hv_doc_locator2, and hv_doc_locator3:
EXEC SQL FREE LOCATOR :hv_doc_locator1, :hv_doc_locator2, : hv_doc_locator3;
7. End the program.
In this example, a particular resume (empno = ’000130’) was sought from within a table of resumes EMP_RESUME. TheDepartment Information section of the resume was copied, cut, and then appended to the end of the resume. This new resume was then inserted into the EMP_RESUME table. The original resume in this table was left unchanged.
Locators permitted the assembly and examination of the new resume without actually moving or copying any bytes from the original resume. The
movement of bytes does not happen until the final assignment; that is, the INSERT statement -- and then only at the server.
Related concepts:
v “Large Object Usage” on page 165 v “Large Object Locators” on page 166
Related tasks:
v “Connecting an Application to a Database” in the Application Development Guide: Programming Client Applications
v “Ending an Application Program” in the Application Development Guide: Programming Client Applications
v “Retrieving a LOB Value with a LOB Locator” on page 168
Related samples:
v “dtlob.out -- HOW TO USE THE LOB DATA TYPE (C)” v “dtlob.sqc -- How to use the LOB data type (C)”
v “dtlob.out -- HOW TO USE THE LOB DATA TYPE (C++)” v “dtlob.sqC -- How to use the LOB data type (C++)” v “dtLob.bas -- Get/set Large Objects (LOBs)”
v “DtLob.out -- HOW TO USE LOB DATA TYPE (JDBC)”
v “lobeval.sqb -- Demonstrates how to use a Large Object (LOB) (IBM COBOL)”
Large Object File Reference Variables
LOB file reference variables facilitate the movement of LOB values from the database server to a client application without going through the client application’s memory. File reference variables are similar to host variables except that they are used to transfer data to and from client files, and not to and from memory buffers. With this approach, client applications do not have to call utility routines to read and write files using host variables (which have size restrictions) to carry out the movement of LOB data.
A file reference variable represents (rather than contains) the file, just as a LOB locator represents (rather than contains) the LOB value. Database queries, updates, and inserts may use file reference variables to store, or to retrieve, single LOB column values.
File reference variables are used for direct file input and output for LOBs, and can be defined in all host languages. Since they are not native data types, SQL extensions are used and the precompilers generate the host language
constructs necessary to represent each variable. A file reference variable has the following properties:
1. Data Type: BLOB, CLOB, or DBCLOB. This property is specified when the variable is declared.
2. File name: The application program must specify this at run time. It is one of:
v The complete path name of the file (which is advised).
v A relative file name. If a relative file name is provided, it is appended to the current path of the client process. Within an application, a file should only be referenced in one file reference variable.
3. File Name Length: The application program must specify this at run time. It is the length of the file name (in bytes).
4. File Options: An application must assign one of a number of options to a file reference variable before it makes use of that variable. Options are set by an INTEGER value in a field in the file reference variable structure. One of the file options must be specified for each file reference variable:
File option (by language) Direction Description
C: SQL_FILE_READ COBOL: SQL-FILE-READ FORTRAN: sql_file_read
input This is a regular file that can be opened, read and closed.
File option (by language) Direction Description
C: SQL_FILE_CREATE COBOL: SQL-FILE-CREATE FORTRAN: sql_file_create
output Create a new file. If the file already exists, an error is returned.
C: SQL_FILE_OVERWRITE COBOL: SQL-FILE-OVERWRITE FORTRAN: sql_file_overwrite
output If an existing file with the specified name exists, it is overwritten; otherwise, a new file is created.
C: SQL_FILE_APPEND COBOL: SQL-FILE-APPEND FORTRAN: sql_file_append
output If an existing file with the specified name exists, the output is appended to it; otherwise a new file is created.
5. Data Length: This is unused on input. On output, the implementation sets the data length (in bytes) to the length of the new data written to the file. For normal host variables in an application program, when selecting a NULL value into a host variable, the indicator variable is set to -1, signifying that the value is NULL. In the case of file reference variables, however, the meaning of indicator variables is slightly different. Since a file reference variable itself can never be NULL, a negative indicator variable value indicates that the LOB value represented by the file reference variable is NULL.