The LONG VARCHAR and LONG 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 (2) gigabytes (GB) in size. The three data types are: Binary Large OBjects (BLOBs), single-byte Character Large OBjects (CLOBs), and Double-Byte Character Large OBjects (DBCLOBs).
Along with storing large objects (LOBs), a way is also needed to refer to, and to use and modify, each LOB in the database. 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 4 terabytes of LOB data. The content of the LOB column of a particular row at any point in time has a large object value.
You can refer to and manipulate LOBs using host variables just as you would any other data type. However, host variables use the client memory buffer which may not be large enough to hold LOB values. Other means are necessary to manipulate these large values. Locators are useful to identify and manipulate a large object value at the database server and for extracting pieces of the LOB value. File reference variables are useful for physically moving a large object value (or a large part of it) to and from the client.
The subsections that follow discuss in more detail those topics introduced above.
Large object data types store data ranging in size from zero bytes to two gigabytes - 1.
The three large object data types have the following definitions:
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. For specific sizes of the LOB descriptor, see the CREATE TABLE statement in the SQL Reference.
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. For more information on the CREATE TABLE and ALTER TABLE statements, see the SQL Reference.
With their potentially very 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 near several hundred megabytes 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 improves performance, changes to the LOB values after the most recent backup are lost during roll-forward recovery. For more information on these topics, see the Administration Guide.
Conceptually, LOB locators represent a simple idea that has been around for a while; use a small, easily managed value to refer to a much larger value. Specifically, a LOB locator is a 4-byte value stored in a host variable that a program can use to refer to a LOB value (or LOB expression) held in the database system. Using a LOB locator, a program can manipulate the LOB value as if the LOB value was stored in a regular host variable. The difference in using the LOB locator is that there is no need to transport the LOB value from the server to the application (and possibly back again).
The LOB locator is associated with a LOB value or LOB expression, not a row or physical storage location in the database. Therefore, after selecting a LOB value into a locator, there is no operation that you could perform on the original row(s) or tables(s) that would have any effect on the value referenced by the locator. The value associated with the locator is valid until the unit of work ends, or the locator is explicitly freed, whichever comes first. 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.
LOB locators can also be passed between DB2 and UDFs. There are special APIs available for UDFs to manipulate the LOB values using LOB locators. For more information on these APIs see "Using LOB Locators as UDF Parameters or Results".
When selecting a LOB value, you have three options:
The use of the LOB value within the program can help the programmer determine which method is best. 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.
If the program needs the entire LOB value regardless of the size, then there is no choice but to transfer the LOB. Even in this case, there are still three options available to you. You can select the entire value into a regular or file host variable, but it may also work out better to select the LOB value into a locator and read it piecemeal from the locator into a regular host variable, as suggested in the following example.
In this example, the application program retrieves a locator for a LOB value; then it uses the locator to extract the data from the LOB value. Using this method, the program allocates only enough storage for one piece of LOB data (the size is determined by the program) and it needs to issue only one fetch call using the cursor.
The CHECKERR macro/function is an error checking utility which is external to the program. The location of this error checking utility depends upon the programming language used:
See "Using GET ERROR MESSAGE in Example Programs" for the source code for this error checking utility.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "util.h"
EXEC SQL INCLUDE SQLCA;
#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;
int main(int argc, char *argv[]) {
#ifdef DB2MAC
char * bufptr;
#endif
EXEC SQL BEGIN DECLARE SECTION; (1)
char number[7];
long deptInfoBeginLoc;
long 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;
printf( "Sample C program: LOBLOC\n" );
if (argc == 1) {
EXEC SQL CONNECT TO sample;
CHECKERR ("CONNECT TO SAMPLE");
}
else if (argc == 3) {
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd;
CHECKERR ("CONNECT TO SAMPLE");
}
else {
printf ("\nUSAGE: lobloc [userid passwd]\n\n");
return 1;
} /* endif */
/* Employee A10030 is not included in the following select, because
the lobeval program manipulates the record for A10030 so that it is
not compatible with lobloc */
EXEC SQL DECLARE c1 CURSOR FOR
SELECT empno, resume FROM emp_resume WHERE resume_format='ascii'
AND empno <> 'A00130';
EXEC SQL OPEN c1;
CHECKERR ("OPEN CURSOR");
do {
EXEC SQL FETCH c1 INTO :number, :resume :lobind; (2)
if (SQLCODE != 0) break;
if (lobind < 0) {
printf ("NULL LOB indicated\n");
} else {
/* EVALUATE the LOB LOCATOR */
/* Locate the beginning of "Department Information" section */
EXEC SQL VALUES (POSSTR(:resume, 'Department Information'))
INTO :deptInfoBeginLoc;
CHECKERR ("VALUES1");
/* Locate the beginning of "Education" section (end of "Dept.Info" */
EXEC SQL VALUES (POSSTR(:resume, 'Education'))
INTO :deptInfoEndLoc;
CHECKERR ("VALUES2");
/* Obtain ONLY the "Department Information" section by using SUBSTR */
EXEC SQL VALUES(SUBSTR(:resume, :deptInfoBeginLoc,
:deptInfoEndLoc - :deptInfoBeginLoc)) INTO :deptBuffer;
CHECKERR ("VALUES3");
/* Append the "Department Information" section to the :buffer var. */
EXEC SQL VALUES(:buffer || :deptBuffer) INTO :buffer;
CHECKERR ("VALUES4");
} /* endif */
} while ( 1 );
#ifdef DB2MAC
/* Need to convert the newline character for the Mac */
bufptr = &(buffer[0]);
while ( *bufptr != '\0' ) {
if ( *bufptr == 0x0A ) *bufptr = 0x0D;
bufptr++;
}
#endif
printf ("%s\n",buffer);
EXEC SQL FREE LOCATOR :resume, :deptBuffer; (3)
CHECKERR ("FREE LOCATOR");
EXEC SQL CLOSE c1;
CHECKERR ("CLOSE CURSOR");
EXEC SQL CONNECT RESET;
CHECKERR ("CONNECT RESET");
return 0;
}
/* end of program : LOBLOC.SQC */
Identification Division.
Program-ID. "lobloc".
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 empnum pic x(6).
01 di-begin-loc pic s9(9) comp-5.
01 di-end-loc pic s9(9) comp-5.
01 resume USAGE IS SQL TYPE IS CLOB-LOCATOR.
01 di-buffer USAGE IS SQL TYPE IS CLOB-LOCATOR.
01 lobind pic s9(4) comp-5.
01 buffer USAGE IS SQL TYPE IS CLOB(1K).
EXEC SQL END DECLARE SECTION END-EXEC.
77 errloc pic x(80).
Procedure Division.
Main Section.
display "Sample COBOL program: LOBLOC".
* 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.
* Employee A10030 is not included in the following select, because
* the lobeval program manipulates the record for A10030 so that it is
* not compatible with lobloc
EXEC SQL DECLARE c1 CURSOR FOR
SELECT empno, resume FROM emp_resume
WHERE resume_format = 'ascii'
AND empno <> 'A00130' END-EXEC.
EXEC SQL OPEN c1 END-EXEC.
move "OPEN CURSOR" to errloc.
call "checkerr" using SQLCA errloc.
Move 0 to buffer-length.
perform Fetch-Loop thru End-Fetch-Loop
until SQLCODE not equal 0.
* display contents of the buffer.
display buffer-data(1:buffer-length).
EXEC SQL FREE LOCATOR :resume, :di-buffer END-EXEC. (3)
move "FREE LOCATOR" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL CLOSE c1 END-EXEC.
move "CLOSE CURSOR" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL CONNECT RESET END-EXEC.
move "CONNECT RESET" to errloc.
call "checkerr" using SQLCA errloc.
End-Main.
go to End-Prog.
Fetch-Loop Section.
EXEC SQL FETCH c1 INTO :empnum, :resume :lobind (2)
END-EXEC.
if SQLCODE not equal 0
go to End-Fetch-Loop.
* check to see if the host variable indicator returns NULL.
if lobind less than 0 go to NULL-lob-indicated.
* Value exists. Evaluate the LOB locator.
* Locate the beginning of "Department Information" section.
EXEC SQL VALUES (POSSTR(:resume, 'Department Information'))
INTO :di-begin-loc END-EXEC.
move "VALUES1" to errloc.
call "checkerr" using SQLCA errloc.
* Locate the beginning of "Education" section (end of Dept.Info)
EXEC SQL VALUES (POSSTR(:resume, 'Education'))
INTO :di-end-loc END-EXEC.
move "VALUES2" to errloc.
call "checkerr" using SQLCA errloc.
subtract di-begin-loc from di-end-loc.
* Obtain ONLY the "Department Information" section by using SUBSTR
EXEC SQL VALUES (SUBSTR(:resume, :di-begin-loc,
:di-end-loc))
INTO :di-buffer END-EXEC.
move "VALUES3" to errloc.
call "checkerr" using SQLCA errloc.
* Append the "Department Information" section to the :buffer var
EXEC SQL VALUES (:buffer || :di-buffer) INTO :buffer
END-EXEC.
move "VALUES4" to errloc.
call "checkerr" using SQLCA errloc.
go to End-Fetch-Loop.
NULL-lob-indicated.
display "NULL LOB indicated".
End-Fetch-Loop. exit.
End-Prog.
stop run.
program lobloc
implicit none
include 'sqlenv.f'
include 'sql.f'
EXEC SQL INCLUDE SQLCA
EXEC SQL BEGIN DECLARE SECTION (1)
character*8 userid
character*18 passwd
character*6 empnum
integer*4 di_begin_loc
integer*4 di_end_loc
SQL TYPE IS CLOB_LOCATOR resume
SQL TYPE IS CLOB_LOCATOR di_buffer
integer*2 lobind
SQL TYPE IS CLOB(1K) buffer
EXEC SQL END DECLARE SECTION
character*80 errloc
print *, 'Sample Fortran Program: LOBLOC'
print *, 'Enter your user id (default none):'
read 101, userid
101 format (a8)
if( userid(1:1) .eq. ' ' ) then
EXEC SQL CONNECT TO sample
else
print *, 'Enter your password :'
read 101, passwd
EXEC SQL CONNECT TO sample USER :userid USING :passwd
end if
errloc = 'CONNECT'
call checkerr (sqlca, errloc, *999)
* Employee A10030 is not included in the following select, because
* the lobeval program manipulates the record for A10030 so that it is
* not compatible with lobloc
EXEC SQL DECLARE c1 CURSOR FOR
+ SELECT empno, resume FROM emp_resume
+ WHERE resume_format = 'ascii'
+ AND empno <> 'A00130'
EXEC SQL OPEN c1
errloc = 'OPEN CURSOR'
call checkerr (sqlca, errloc, *999)
10 continue
EXEC SQL FETCH c1 INTO :empnum, :resume :lobind (2)
if (sqlcode .ne. 0) goto 100
if (lobind .lt. 0) goto 50
* The CLOB value exists.
* Locate the beginning of the "Department Information" section
EXEC SQL VALUES (POSSTR(:resume, 'Department Information'))
+ INTO :di_begin_loc
errloc = 'VALUES1'
call checkerr (sqlca, errloc, *999)
* Locate the beginning of the "Education" section (end of Dept.Info)
EXEC SQL VALUES (POSSTR(:resume, 'Education'))
+ INTO :di_end_loc
errloc = 'VALUES2'
call checkerr (sqlca, errloc, *999)
* Obtain ONLY the "Department Information" section by using SUBSTR
EXEC SQL VALUES(SUBSTR(:resume, :di_begin_loc,
+ :di_end_loc - :di_begin_loc)) INTO :di_buffer
errloc = 'VALUES3'
call checkerr (sqlca, errloc, *999)
* Append the "Department Information" section to the :buffer variable
EXEC SQL VALUES(:buffer || :di_buffer) INTO :buffer
errloc = 'VALUES4'
call checkerr (sqlca, errloc, *999)
goto 10
50 print *,'NULL LOB indicated'
goto 10
100 print *, buffer
EXEC SQL FREE LOCATOR :resume, :di_buffer (3)
errloc = 'FREE LOCATOR'
call checkerr (sqlca, errloc, *999)
EXEC SQL CLOSE c1
errloc = 'CLOSE CURSOR'
call checkerr (sqlca, errloc, *999)
EXEC SQL CONNECT RESET
errloc = 'CONNECT RESET'
call checkerr (sqlca, errloc, *999)
999 stop
end
There is no movement of the bytes of a LOB value until the assignment of 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 is called deferring evaluation of a LOB expression.
In this example, a particular resume (empno = '000130') is sought within a table of resumes EMP_RESUME. The Department Information section of the resume is copied, cut, and then appended to the end of the resume. This new resume will then be inserted into the EMP_RESUME table. The original resume in this table remains unchanged.
Locators permit 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.
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 but may also require fewer disk I/Os.
In summary, LOB locators are ideally suited for a number of programming scenarios:
The CHECKERR macro/function is an error checking utility which is external to the program. The location of this error checking utility depends upon the programming language used:
See "Using GET ERROR MESSAGE in Example Programs" for the source code for this error checking utility.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "util.h"
EXEC SQL INCLUDE SQLCA;
#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;
int main(int argc, char *argv[]) {
EXEC SQL BEGIN DECLARE SECTION; (1)
char userid[9];
char passwd[19];
long hv_start_deptinfo;
long hv_start_educ;
long 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;
CHECKERR ("CONNECT TO SAMPLE");
}
else if (argc == 3) {
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd;
CHECKERR ("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)
CHECKERR ("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)
CHECKERR ("VALUES1");
EXEC SQL VALUES (POSSTR(:hv_doc_locator1, 'Education'))
INTO :hv_start_educ;
CHECKERR ("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;
CHECKERR ("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;
CHECKERR ("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;
CHECKERR ("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)
CHECKERR ("INSERT");
printf ("LOBEVAL completed\n");
/* free the locators */ (5)
EXEC SQL FREE LOCATOR :hv_doc_locator1, :hv_doc_locator2, : hv_doc_locator3;
CHECKERR ("FREE LOCATOR");
EXEC SQL CONNECT RESET;
CHECKERR ("CONNECT RESET");
return 0;
}
/* end of program : LOBEVAL.SQC */
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.
For normal host variables in an application program, when selecting a NULL value into a host variable, a negative value is assigned to the indicator variable 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. The NULL information is kept local to the client using the indicator variable value -- the server does not track NULL values with valid locators.
File reference variables are similar to host variables except they are used to transfer data to and from client files, and not to and from memory buffers. 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 values.
For very large objects, files are natural containers. In fact, it is likely that most LOBs begin as data stored in files on the client before they are moved to the database on the server. The use of file reference variables assists in moving LOB data. Programs use file reference variables to transfer LOB data from the client file directly to the database engine. The client application does not have to write utility routines to read and write files using host variables (which have size restrictions) to carry out the movement of LOB data.
| Note: | The file referenced by the file reference variable must be accessible from (but not necessarily resident on) the system on which the program runs. For a stored procedure, this would be the server. |
A file reference variable has a data type of BLOB, CLOB, or DBCLOB. It is used either as the source of data (input) or as the target of data (output). The file reference variable may have a relative file name or a complete path name of the file (the latter is advised). The file name length is specified within the application program. The data length portion of the file reference variable is unused during input. During output, the data length is set by the application requestor code to the length of the new data written to the file.
When using file reference variables there are different options on both input and output. You must choose an action for the file by setting the file_option field in the file reference variable structure. Choices for assignment to the field covering both input and output values are shown below.
Values (shown for C) and options when using input file reference variables are as follows:
Values and options when using output file reference variables are as follows:
Notes:
For more information on file reference variables, see the SQL Reference.
This program example shows how CLOB elements can be retrieved from a table into an external file.
The CHECKERR macro/function is an error checking utility which is external to the program. The location of this error checking utility depends upon the programming language used:
See "Using GET ERROR MESSAGE in Example Programs" for the source code for this error checking utility.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sql.h>
#include "util.h"
EXEC SQL INCLUDE SQLCA;
#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;
int main(int argc, char *argv[]) {
EXEC SQL BEGIN DECLARE SECTION; (1)
SQL TYPE IS CLOB_FILE resume;
short lobind;
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;
printf( "Sample C program: LOBFILE\n" );
if (argc == 1) {
EXEC SQL CONNECT TO sample;
CHECKERR ("CONNECT TO SAMPLE");
}
else if (argc == 3) {
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd;
CHECKERR ("CONNECT TO SAMPLE");
}
else {
printf ("\nUSAGE: lobfile [userid passwd]\n\n");
return 1;
} /* endif */
strcpy (resume.name, "RESUME.TXT"); (2)
resume.name_length = strlen("RESUME.TXT");
resume.file_options = SQL_FILE_OVERWRITE;
EXEC SQL SELECT resume INTO :resume :lobind FROM emp_resume (3)
WHERE resume_format='ascii' AND empno='000130';
if (lobind < 0) {
printf ("NULL LOB indicated \n");
} else {
printf ("Resume for EMPNO 000130 is in file : RESUME.TXT\n");
} /* endif */
EXEC SQL CONNECT RESET;
CHECKERR ("CONNECT RESET");
return 0;
}
/* end of program : LOBFILE.SQC */
Identification Division.
Program-ID. "lobfile".
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 resume USAGE IS SQL TYPE IS CLOB-FILE.
01 lobind pic s9(4) comp-5.
EXEC SQL END DECLARE SECTION END-EXEC.
77 errloc pic x(80).
Procedure Division.
Main Section.
display "Sample COBOL program: LOBFILE".
* 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.
move "RESUME.TXT" to resume-NAME. (2)
move 10 to resume-NAME-LENGTH.
move SQL-FILE-OVERWRITE to resume-FILE-OPTIONS.
EXEC SQL SELECT resume INTO :resume :lobind (3)
FROM emp_resume
WHERE resume_format = 'ascii'
AND empno = '000130' END-EXEC.
if lobind less than 0 go to NULL-LOB-indicated.
display "Resume for EMPNO 000130 is in file : RESUME.TXT".
go to End-Main.
NULL-LOB-indicated.
display "NULL LOB indicated".
End-Main.
EXEC SQL CONNECT RESET END-EXEC.
move "CONNECT RESET" to errloc.
call "checkerr" using SQLCA errloc.
End-Prog.
stop run.
In the path description of the following C program segment:
The following example shows how to insert data from a regular file referenced by :hv_text_file into a CLOB column (note that the path names used in the example are for UNIX-based systems):
strcpy(hv_text_file.name, "/u/userid/dirname/filnam.1");
hv_text_file.name_length = strlen("/u/userid/dirname/filnam.1");
hv_text_file.file_options = SQL_FILE_READ; /* this is a 'regular' file */
EXEC SQL INSERT INTO CLOBTAB
VALUES(:hv_text_file);