Expert analysis: correctly export LOB data in DB2

Expert analysis: correctly export LOB data in DB2

For example, there is a table (USER_INFO) for employee information. This table contains three fields: employee ID, employee name, and employee ID card copy. Because the size of the employee ID card copy is relatively large, the LOB data type is used in the database. When you need to export the data in this table to a specific file, as long as the size of copies of these ID cards exceeds 32 KB, these copies of ID cards will not be seen in the exported target file. During export, the system saves copies of these ID cards in the folder specified by the user. In the export destination file, only the corresponding names of these copies are available. Therefore, if a table has a LOB data type, you need to differentiate it when exporting it to a file in a specific format.

If a strap contains data of the LOB type, pay special attention when writing the Export Statement. To write the Export Statement in the following format:

 
 

  
  
Export to user_info.del of del 
  
  
Lob to d:\photo1,d:\photo2 Lobfile p1,p2 modifiled by lobsinfile 
  
  
Select * from user_info. 
 
 

1. Some special options for exporting a LOB table compared to a common table.

Compared with normal export statements, when exporting data with the LOB data type, Lob to d: \ photo1, d: \ photo2 Lobfile p1 is added to the statement, p2 modifiled by lobsinfile. The lob to option defines the path TO store the LOB file. When defining this path, you can define multiple directories. You can specify a directory list that uses the comma separator to define its file storage location. If no directory is defined, the database uses the current directory as the location for storing the LOB file. Similar to a common operating system directory, the name of the LOB file in different directories can be different. However, the name of the LOB file in the same directory must be unique. The second one needs to specify the naming rules for the LOB file. When exporting data, many LOB files are generated. The files automatically created by these databases must have the file name. In this case, the database administrator must specify the naming rules for these LOB files. This naming rule is determined by the LOBFILE option. With this option, the database administrator can increase the list of basic names separated by commas. The operating system generates the name of the LOB file based on these names. Note that the database administrator only provides a basic name or a prefix of the LOB file name. When a LOB file is generated, the system automatically adds a three-digit extension to the base name (or prefix. For example, if I have provided p1 as an extension of the LOB file name, the last generated file name may include p1001 and p1002. It can be seen that the system administrator provides a file prefix name, and the system will automatically expand 999 available LOB file names. Because the operating system will add a three-digit Arabic extension after the LOB file prefix. This option is optional like the directory option. If you do not specify the basic names of available files, DB2 database uses db2exp as the basic file extension. Then, the name is followed by a three-digit extension to generate the name of the file with 1000 available LOB files.

Note that when you want to export a table to a specific file, you can separate the LOB file from other information to export the LOB file, you also need to add a special keyword to the Export Statement. 2. Determine available file names.

When exporting the LOB data type, the database system creates a file in the directory specified by the user to save these LOB data types. In the database, you need to create a file for a LOB data segment. That is to say, if two fields in the table use the LOB data type and the table has 20 thousand records, 40 thousand files will be created during export. A file corresponds to a file name, so the database administrator must ensure that there are 40 thousand available file names (not the basic file name ). If there are too many LOB data types during export and the available file names are not enough, the export job will end up failing.

Before exporting data, the database administrator needs to evaluate the number of LOB data segments in the data table. Then, determine the number of available file names based on the quantity. Then, based on the quantity, determine the number of stored directories and basic file names. As shown in the example above, what are the final names of available files?