Large Objects

Large Object (LOB) data types are used to store data greater than 32KB, such as long XML documents, audio files, or pictures (up to 2GB). Three kinds of LOB data types are provided by DB2:

• Binary Large Objects (BLOBs)

• Single-byte Character Large Objects (CLOBs)

• Double-Byte Character Large Objects (DBCLOBs)

You will need to take into account some performance considerations when dealing with LOBs. Refer to Chapter 12, "Performance Tuning," for more details.

LOBs can be used as parameters and local variables of SQL procedures. Figure 3.2 demonstrates a very simple usage of LOBs and returns a CLOB to the stored procedure caller.

Figure 3.2. SQL procedure STAFFRESUME.

 CREATE PROCEDURE staffresume  ( IN p_empno CHAR(6)                               , OUT p_resume CLOB(1M) )     LANGUAGE SQL     SPECIFIC staffresume                           -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                          -- applies to zSeries BEGIN     SELECT resume INTO p_resume         FROM emp_resume         WHERE empno=p_empno AND resume_format = 'ascii';     INSERT INTO emp_resume ( empno                            , resume_format                            , resume )         VALUES             ( p_empno                            , 'backupcopy'                            , p_resume ); END