Considerations for importing external DB2 database files (1)

This article will introduce you to some important issues in the process of importing external files into and exporting databases in the DB2 database system for your reference.

Before putting a database into production, the database administrator often needs to import a large amount of data to the database. Only a large amount of basic data in the database can the application software built on the database make full use of the benefits.

This article focuses on some considerations when importing data from an external file to a database. These contents may help administrators solve problems.

I. Competition between default values and null values.

When designing a database table, some default values are usually set for certain columns for data integrity. In particular, when a field cannot be blank, you must set the default value. When the user does not enter the corresponding value, the default value is used for filling. If there is a sales order table. This table has a field "order date ". When the user does not enter data, the order date is the current system date by default, so as to simplify user input. The problem now is that at the beginning of data import, if the value of this column is not in the target file and the default value is set for this field, what will happen at this time? (Similarities and differences between DB2 database and SQLServer database)

Generally, as long as the field does not have a non-empty constraint (that is, null values are allowed), no matter whether the field has a default value set, when importing, as long as the target file does not have the corresponding content, all use NULL values to replace the missing columns in the target file. If this field has a non-null constraint (that is, null values are not allowed), the database system will prompt an error message indicating that null values are not allowed. That is to say, the default values of fields in the table are generally invalid during the import process. When a value does not exist in the target file, the database will not fill the value with the column default value, but will use the NULL value to fill the value.

However, if the database administrator must fill these columns with the default values, is it okay? This is acceptable in the DB2 database. As long as the default value has been defined for the target table, you can use the USEDEFAUITS option in the Import Statement to enable the database system (Oracle database system uses experience 6) use the default value in the imported concept to fill in the data that does not exist in the target file. Note that the default value is used only when the field value of the corresponding record in the target file does not exist. If yes, the value in the target file is still used. It can be seen that the war between the NULL value and the column default value is still in the hands of the user. You can use the USEDEFAULT option to determine whether the default value is required. If a field is set to be non-empty and the default value is set for this column, we recommend that you use the default value, which can greatly simplify data import.