Four important considerations for DB2 data import

        

Before putting the database into production, the database administrator often needs to import large amounts of data into the database first. Only a large number of basic data in the database, the application software built on the database can play an effective role.

This paper focuses on some considerations in the process of importing data from external files into the database. Perhaps this content can help administrators solve problems.

First, the default value and empty value of the battle.

At the time of database table design, for data integrity, some default values are often set for some columns. In particular, if a field is not allowed to be empty, set the default value. When the user does not enter a corresponding value, the default value is used to populate it. If there is a table for the sales order. There is a field on this table that is "Order Date". When the user does not enter data, the order date is assumed to be the current system date, thereby simplifying the user input. The question now is, at the time of the initial data import, if the target file does not have a value for that column, and this field sets the default value, what will happen ? (DB2 Database and SQL Server the similarities and differences of database )

Typically, whenever a field has no non-null constraint ( that is, a null value is allowed ), regardless of whether the field has a default value set, it will be used whenever the target file does not have a corresponding content . NULL value to replace the missing column in the destination file. If the field has a non-empty constraint ( that is, null values are not allowed ), the database system prompts for an error message that does not allow null values. That is, the setting of the default value of a field in a table is generally not valid during the import process. When a value does not exist in the destination file, the database is not populated with the default values for the columns, but instead is populated with NULL values.

However, if the database administrator must have the default values to populate these columns, is it possible??in theDB2database, this is possible. As long as the destination table already has a default value defined, you can use it in the imported statementusedefauitsoption, you can let the database system(Oracleexperience of using database system in six)Use the default values in the imported vehicle concept to populate data that does not exist in the destination file. Note that the default value is used only when the field value for the corresponding record in the destination file does not exist. If present, the value in the destination file is still used. Visible thisNULLA war between a value and a column default value, the final result is still in the hands of the user. Users can passUsedefaultoption to determine if a default value is required. If a field is set to be non-empty and the column is set to a default value, then the author suggests using the default value, which can greatly simplify the work of data import.

Second, the import process of performance considerations.

When a large amount of data is inserted into a database table, it consumes more database resources, which affects the performance of the database. To do this, when importing data into the database, in addition to the need to select a user access to a small amount of time. It is also necessary to use the appropriate means to improve the performance of data import when importing. For example, in the DB2 Database, the tools for data import are categorized into two categories, namely, the load using tool and the Import tool. When the amount of data imported more, and users of performance and more stringent requirements, then the author set up the use of loading tools. Because in general, when importing large amounts of data, loading using tools can provide high performance.

Adding a compound clause can also improve the performance of the database when importing data on the client rather than directly importing the data on the server. This clause is primarily used to group the data that needs to be inserted, that is, how many records to send the insert request to the server. Because the data needs to be passed between the client and the server. It takes a while for the client to read data from an external file to the database server ; It also takes some time for the database server to insert data into the table when it accepts requests to the client. If you use n statements to group the large number of records that need to be inserted, you can improve the performance of the database. On the one hand, the client and the server side of the division of labor, the client side from the external files read into the data, the server side to insert data, thereby reducing the time to insert. On the other hand, the data step in the network transmission in the network, can also improve the performance of network transmission, also can play the purpose of high performance. In summary, the data import job tends to degrade the performance of the database as it works. It is best to choose a more appropriate time when importing data. and adopt some of the above suggested measures to minimize the negative impact of import work on the normal operation of the database.

Third, let the activity log to be implemented in sub-times.

In the process of data import, it is ultimately necessary to passInsertstatement to implement. andDB2when the database uses this statement, the database engine will routinely record the updates on behalf of the import operation. It's like it's inOracleThe operation of this table update is logged in the database as in the log file. Therefore, when more records are imported, the potential for a long time to occupy the current activity log, eventually causing the database manager to consume the log space. For this reason, when importing data , it is best to useCommitcount Nclause. The main function of this clause is to tell the database system to insertNlog implementation is required after the record. When this clause is used, if the import operation fails, all changes performed after the last implementation will be canceled during the transaction rollback. This can also reduce the loss of data import failures. If this clause is not used, the database system undoes all imported records if the import fails. In other words, everything has to start over. However, if this statement is used, the database administrator can re-enable it based on the information of the last implementation point provided by the system .Importcommand, and by providingRestartcount Nclause to instruct this command to skip the number of lines that have previously been reported as successfully implemented into the input file. That is, avoid repeated imports to improve the efficiency of data import. The database administrator can set this appropriately based on how much of the recording volumeNvalues. In general, this value is not set too large. However, if it is too small, it can also affect the performance of data import. If the amount of data is large, I tend to set this value to -. However, depending on the specifics of the situation, the database administrator still needs to choose a suitable value based on experience.

Iv. using formatting options in the import statement

When importing data, external files are generally required to adhere to a strict format. If the format is not accurate, then data import will often fail. Therefore, sometimes the database administrator in the process of import, but also have to use some file type modifiers, also known as formatting options, to standardize the format of external files. Enables the database system to correctly identify external files, thus successfully importing external data.

By default, the columns and columns of the external file are separated by commas in the English state. If the external file is not delimited by this symbol, but is delimited with a colon or TAB symbol, the database administrator has two choices. The first is to change the separator symbol in the original file. This delimiter can be changed by using the relevant commands of the operating system or other software. such as through the excle software to change and so on. The second is to use the format option when importing, so that the import tool can recognize the delimiter. If you can include the Coldel: clause in the import statement , tell the database system that the column delimiter now used is not a comma, but a colon. You can also import data smoothly without changing the original file.

db2 ( tend to be line-breaking symbols has the highest priority level. As a result, the above mentioned situation will occur. To avoid   a record, Display modified by  Delprioritychar

article: www.dbfen.com Reprint Please indicate the source

This article is from the "Big Meatball" blog, please make sure to keep this source http://12478147.blog.51cto.com/9663367/1606698

Four important considerations for DB2 data import