Unloading data in spanned record format

If you want to unload data from a table that has large LOB or XML fields, consider unloading the data in spanned record format to improve the performance of read/write operations.

About this task

When you unload data in spanned record format, all LOB and XML data for a table space or table space partition can be written to an individual sequential file. This file can reside on DASD and can span multiple volumes. Having such a single sequential file can improve the performance of read/write operations.

UNLOAD SPANNED YES ignores large block interface (LBI) if used by any output data sets.

Procedure

To unload data in spanned record format:

Specify the SPANNED YES option. Specify in the field specification list that all LOB and XML data are to be at the end of the record.

For example, the following UNLOAD statement specifies that the data from table TB1 is to be unloaded in spanned record format. Notice that in the field specification list, the CLOB columns are listed at the end and POSITION is not specified.

UNLOAD TABLESPACE TESTDB1.CLOBBASE SPANNED YES    
      FROM TABLE TB1                                  
        (ID                                           
        ,C1 INTEGER                                   
        ,C2 INTEGER                                   
        ,C3 CHAR(100)                                 
        ,C4 CHAR(100)                                 
        ,C5 INTEGER                                   
        ,C6 CHAR(100)                                 
        ,C7 CHAR(100)                                 
        ,C8 CHAR(100)                                 
        ,CLOB1 CLOB                                   
        ,CLOB2 CLOB                                   
        ,CLOB3 CLOB)

Results

The following code shows a conceptual example of a spanned record that was unloaded.

.----------------.------------------.-------------------------------------.
| C1 | C2 | C3| C4 | C5 | C6 | C7 | C8 | Start of CLOB 1                  |
+----------------+------------------+------------.------------------------+
| The rest of CLOB 1                              | Start of CLOB 2       |
+------------------------------------------------+------------------------+
| more of CLOB 2                                                          |
+----------------------.--------------------------------------------------+
| the rest of CLOB 2 |  CLOB 3                                            |
+----------------------+--------------------------------------------------+
| The next row........................
+---------------------------------------------

What to do next

When you run LOAD on data that was unloaded in spanned record format, use the LOAD statements that are in the SYSPUNCH data sets after UNLOAD runs. Those LOAD statements include SORTKEYS parameters with accurate values. During LOAD, Db2 cannot estimate the size of the sort work data sets by checking the contents of the SYSREC data sets that are produced during UNLOAD with SPANNED YES.