DB2 table operation tips: LOB data export compass

We describe how to export LOB data using the DB2 table operation technique in the hypothetical way. Suppose there is a table of employee information (USER_INFO. The 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.

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 data from this DB2 table to a specific file, as long as the size of copies of these ID cards exceeds 32 KB, the copies of these 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. Write the statement for exporting LOB data in the following format:

Some special options for exporting a LOB data 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 DB2 table to a specific file and export a separate file to the LOB, the LOB file will be stored separately from other information, 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?

First, the database administrator needs to know that a basic name can be expanded using three Arabic numerals to get the name of 1000 files. The same file name can be used in different directories. That is to say, in the directory specified above, different directories may have different file names. You only need to ensure that the same folder contains a file with the same name. Then, the basic names of the two files, the two directories, and the extension of the file name can get 4000 available file names.

Here is a simple calculation formula, that is, the number of basic file names * Number of defined directories * 1000. The final result is the number of available file names. If the number of finally generated LOB files is smaller than the available number of files, the files can be exported normally. However, if the number of LOB files in the table exceeds the number of available file names, the export may fail because the target LOB file cannot obtain the available file name.

When a failure occurs, it is difficult to query which LOB file is not exported. Therefore, it is best to estimate the number of file names that may be required before export. In fact, this budget is relatively simple. To know how many columns in the table to be exported are of the LOB data type, multiply the number of records. In general, the number of files available can only be larger than the actual number of LOB files, but cannot be less. If the number of files exceeds the limit, the data cannot be exported.

Sometimes, to avoid this problem, the database administrator has to add the target directory of the LOB file or increase the number of basic LOB file names, this allows the export to generate enough file names for all LOB data contained in the result set of the SELECT statement. Note that the database administrator cannot increase the number of extended digits. That is, the file name extension can only use three Arabic numbers.

In addition, it takes a long time to export data of the LOB data type to a specific file. Therefore, we do not recommend exporting the LOB data type at once. Add appropriate restrictions to the Select statement to export the LOB files in batches to reduce the time for each export.

For example, if you export copies of all employees' ID cards, you can classify them by gender or by part. There are two benefits. First, it can shorten the time for each export, and even if there is a problem in the middle, it can be controlled within the minimum range. Second, the exported LOB file names are random. For this reason, batch export makes it much easier to find the corresponding LOB file. The above content is related to the DB2 table operation skills-Introduction to the correct export of LOB data, I hope you will gain some benefits.