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:
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