Copying files that contain large objects
You can specify CRTFILE(*YES) on the CPYF and CPYFRMQRYF commands when you copy files that contain large objects (LOBs). If the from-file is an SQL table, view, or index that contains a LOB, these commands create an SQL table. The i5/OS® operating system supports three large object data types: binary large objects (BLOBs), single-byte or mixed character large objects (CLOBs), and double-byte character large objects (DBCLOBs). When you copy files that contain these objects using the Copy File (CPYF) command, you should consider the following restrictions and requirements:
The following tables show how LOBs are mapped to other data types during copy operations. The first table shows the mapping when both fields contain LOB field types. In the tables, consider the following guidelines:
- LOB data is not copied when you copy from and to device files, when you copy to *PRINT, or when you specify values of *NOCHK or *CVTSRC on the FMTOPT parameter. In these cases, only the default buffer value for the LOB field is copied, including *POINTER. This is true even when you copy a file that contains a LOB field to an identical file. Valid LOB data is copied only when you have specified *NONE, *MAP, or *DROP on the FMTOPT parameter.
- LOB data is not copied when you copy to a tape or diskette. In these cases, only the buffer value (including *POINTER) is written to the tape or diskette. In addition, if you copy from the tape or diskette back to the same file, you might receive errors. This is because the file contains only the *POINTER value and not a valid pointer to actual LOB data.
- When you specify *UPDADD on the MBROPT parameter of the CPYF command, the to-file can contain a LOB field. LOB fields are also updated when duplicate keys are encountered.
- When you specify *CVTFLOAT or *NULLFLAGS on the FMTOPT parameter of the CPYF command, the to-file cannot contain a LOB field.
- If you want to print a file that contains LOB fields, specify *PRINT on the TOFILE parameter of the CPYF command. *POINTER will appear in the print listing in place of the LOB field data, and other non-LOB field data will also appear in the listing. If you have not specified *PRINT on the TOFILE parameter and you specified *COPIED, *EXCLUDE, or *ERROR on the PRINT parameter, then specify *NOCHK or *CVTSRC on the FMTOPT parameter for the copy operation to be allowed.
- You cannot specify LOB fields on the INCCHAR and INCREL parameters. You can specify *RCD or *FLD on the INCCHAR parameter, but only the fixed buffer length is compared instead of any actual LOB data.
- You cannot copy distributed data management (DDM) files that contain LOB fields to System i™ products.
- The mapping of LOBs from and to DATE or TIME types is not allowed.
- These mappings are valid only for FMTOPT(*MAP) except where noted.
- There are similar data restrictions for large objects as those for normal character data (single-byte, mixed, and double-byte).
Table 1. From-file and to-file mapping when both fields are large objects Field A type Field B type Allowed and copy direction Data CCSID or attributes CCSIDs Conversion translation performed Field A Field B BLOB BLOB Y* <—> 65535 65535 Same No CLOB CLOB Y* <—> Character Character Same No CLOB CLOB Y* <—> Open Open Same No DBCLOB DBCLOB Y* <—> Graphic Graphic Same No DBCLOB DBCLOB Y* <—> UCS2 UCS2 Same No CLOB CLOB Y <—> Character Character Different Yes CLOB CLOB Y <—> Open Open Different Yes DBCLOB DBCLOB Y <—> Graphic Graphic Different Yes DBCLOB DBCLOB Y <—> UCS2 UCS2 Different Yes CLOB CLOB Y <—> Character Open Different Yes CLOB DBCLOB N Character Graphic Different — CLOB DBCLOB Y <—> Open Graphic Different Yes CLOB DBCLOB Y <—> Character UCS2 Different Yes CLOB DBCLOB Y <—> Open UCS2 Different Yes DBCLOB DBCLOB Y <—> Graphic UCS2 Different Yes BLOB CLOB Y <—> 65535 Character Different No BLOB CLOB Y <—> 65535 Open Different No BLOB DBCLOB N 65535 Graphic Different — BLOB DBCLOB N 65535 UCS2 Different — DBCLOB DBCLOB Y <—> 1200 1200 Same No CLOB DBCLOB Y <—> Character 1200 Different Yes CLOB DBCLOB Y <—> Open 1200 Different Yes DBCLOB DBCLOB Y <—> Graphic 1200 Different Yes BLOB DBCLOB N 65535 1200 Different — CLOB CLOB Y <—> 1208 1208 Same No CLOB CLOB Y <—> Character 1208 Different Yes CLOB CLOB Y <—> Open 1208 Different Yes DBCLOB CLOB Y <—> Graphic 1208 Different Yes BLOB CLOB N 65535 1208 Different — * These mappings are valid for FMTOPT(*MAP), FMTOPT(*NONE), and FMTOPT(*DROP).
The second table shows the mapping between fixed-length data types and large objects.
Table 2. From-file and to-file mapping between fixed-length data types and large objects Field A type Field B type Allowed and copy direction Data CCSID or attributes CCSIDs Conversion translation performed Field A Field B Character BLOB Y <—> Character 65535 Different No Open BLOB Y <—> Open 65535 Different No Either BLOB Y <—> Either 65535 Different No Only BLOB Y <—> Only 65535 Different No Graphic BLOB N Graphic 65535 Different — UCS2 BLOB N UCS2 65535 Different — Character CLOB Y <—> Character Character Same/Different No/Yes Open CLOB Y <—> Open Character Different Yes Either CLOB Y <—> Either Character Different Yes Only CLOB Y <—> Only Character Different Yes Graphic CLOB N Graphic Character Different — UCS2 CLOB Y <—> UCS2 Character Different Yes Character CLOB Y <—> Character Open Different Yes Open CLOB Y <—> Open Open Same/Different No/Yes Either CLOB Y <—> Either Open Different Yes Only CLOB Y <—> Only Open Different Yes Graphic CLOB Y <—> Graphic Open Different Yes UCS2 CLOB Y <—> UCS2 Open Different Yes Character DBCLOB N Character Graphic Different – Open DBCLOB Y <—> Open Graphic Different Yes Either DBCLOB Y <—> Either Graphic Different Yes Only DBCLOB Y <—> Only Graphic Different Yes Graphic DBCLOB Y <—> Graphic Graphic Same/Different No/Yes UCS2 DBCLOB Y <—> UCS2 Graphic Different Yes Character DBCLOB Y <—> Not 65535 UCS2 Different Yes Open DBCLOB Y <—> Not 65535 UCS2 Different Yes Either DBCLOB Y <—> Not 65535 UCS2 Different Yes Only DBCLOB Y <—> Not 65535 UCS2 Different Yes Graphic DBCLOB Y <—> Graphic UCS2 Different Yes UCS2 DBCLOB Y <—> UCS2 UCS2 Same/Different No/Yes Character DBCLOB N 65535 UCS2 Different – Open DBCLOB N 65535 UCS2 Different – Either DBCLOB N 65535 UCS2 Different – Only DBCLOB N 65535 UCS2 Different – UTF8 BLOB N 1208 65535 Different – UTF8 CLOB Y <—> 1208 Character Different Yes UTF8 CLOB Y <—> 1208 Open Different Yes UTF8 DBLOB Y <—> 1208 Graphic Different Yes UTF8 DBLOB Y <—> 1208 UCS2 Same No UTF16 BLOB N 1200 65535 Different – UTF16 CLOB Y <—> 1200 Character Different Yes UTF16 CLOB Y <—> 1200 Open Different Yes UTF16 DBCLOB Y <—> 1200 Graphic Different Yes UTF16 DBCLOB Y <—> 1200 UCS2 Same No Binary character BLOB Y <—> 65535 65535 Same No Binary character CLOB Y <—> 65535 Character Different No Binary character CLOB Y <—> 65535 Open Different No Binary character DBCLOB N 65535 Graphic Different – Binary character DBCLOB N 65535 UCS2 Different – Binary character UTF-8 N 65535 1208 Different – Binary character UTF-16 N 65535 1200 Different – The second table shows the mapping variable-length data types and large object.
Table 3. From-file and to-file mapping between variable-length data types and large objects Field A type Field B type Allowed and copy direction Data CCSID or attributes CCSIDs Conversion translation performed Field A Field B VARLEN Character BLOB Y <—> Character 65535 Different No VARLEN Open BLOB Y <—> Open 65535 Different No VARLEN Either BLOB Y <—> Either 65535 Different No VARLEN Only BLOB Y <—> Only 65535 Different No VARLEN Graphic BLOB N Graphic 65535 Different — VARLEN UCS2 BLOB N UCS2 65535 Different — VARLEN Character CLOB Y <—> Character Character Same/Different No/Yes VARLEN Open CLOB Y <—> Open Character Different Yes VARLEN Either CLOB Y <—> Either Character Different Yes VARLEN Only CLOB Y <—> Only Character Different Yes VARLEN Graphic CLOB N Graphic Character Different — VARLEN UCS2 CLOB Y <—> UCS2 Character Different Yes VARLEN Character CLOB Y <—> Character Open Different Yes VARLEN Open CLOB Y <—> Open Open Same/Different No/Yes VARLEN Either CLOB Y <—> Either Open Different Yes VARLEN Only CLOB Y <—> Only Open Different Yes VARLEN Graphic CLOB Y <—> Graphic Open Different Yes VARLEN UCS2 CLOB Y <—> UCS2 Open Different Yes VARLEN Character DBCLOB N Character Graphic Different – VARLEN Open DBCLOB Y <—> Open Graphic Different Yes VARLEN Either DBCLOB Y <—> Either Graphic Different Yes VARLEN Only DBCLOB Y <—> Only Graphic Different Yes VARLEN Graphic DBCLOB Y <—> Graphic Graphic Same/Different No/Yes VARLEN UCS2 DBCLOB Y <—> UCS2 Graphic Different Yes VARLEN Character DBCLOB Y <—> Not 65535 UCS2 Different Yes VARLEN Open DBCLOB Y <—> Not 65535 UCS2 Different Yes VARLEN Either DBCLOB Y <—> Not 65535 UCS2 Different Yes VARLEN Only DBCLOB Y <—> Not 65535 UCS2 Different Yes VARLEN Graphic DBCLOB Y <—> Graphic UCS2 Different Yes VARLEN UCS2 DBCLOB Y <—> UCS2 UCS2 Same/Different No/Yes VARLEN Character DBCLOB N 65535 UCS2 Different – VARLEN Open DBCLOB N 65535 UCS2 Different – VARLEN Either DBCLOB N 65535 UCS2 Different – VARLEN Only DBCLOB N 65535 UCS2 Different – VARLEN UTF8 BLOB N 1208 65535 Different – VARLEN UTF8 CLOB Y <—> 1208 Open Different Yes VARLEN UTF8 DBCLOB Y <—> 1208 Graphic Different Yes VARLEN UTF8 DBCLOB Y <—> 1208 UCS2 Different/Same Yes/No VARLEN UTF16 BLOB N 1200 65535 Different – VARLEN UTF16 CLOB Y <—> 1200 Open Different Yes VARLEN UTF16 DBCLOB Y <—> 1200 Graphic Different Yes VARLEN UTF16 DBCLOB Y <—> 1200 UCS2 Different/Same Yes/No VARLEN Binary character BLOB Y <—> 65535 65535 Same No VARLEN Binary character CLOB Y <—> 65535 Character Different No VARLEN Binary character CLOB Y <—> 65535 Open Different No VARLEN Binary character DBCLOB N 65535 Graphic Different – VARLEN Binary character DBCLOB N 65535 UCS2 Different – VARLEN Binary character UTF-8 N 65535 1208 Different – VARLEN Binary character UTF-16 N 65535 1200 Different –
Parent topic:
Copying complex objects
Related information
CPYF command
CPYFRMQRYF command