Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "the method of importing and exporting data from DB2". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. basic format knowledge of data import and export

DEL: delimited ASCII files, row delimiters and column delimiters separate data. (different machine)

ASC: a fixed-length ASCII file with rows separated by line separators and columns of fixed length.

PC/IXF: can only be used to guide data between db2, numeric values are packaged into decimal or binary according to type, characters are saved as ASCII, only the length that the variable has used is saved, and the file includes table definition and table data. (native)

WSF: worksheet format for import and export. File types in this format are rarely used.

Db2 for different data import and export methods, support for different file types.

A brief introduction to the three import and export operations:

Export: export data, supporting IXF,DEL or WSF

Import: import data, you can import data into tables, supporting the four file types mentioned above.

Load: import data, the function is basically the same as import. Several file types mentioned above are supported.

About Export

Command format: export to filename of filetype select x from xx where

There are several issues to pay attention to:

1. On the export of different character sets

MODIFIED BY CODEPAGE=

Exprot to filename.del for del MODIFIED BY CODEPAGE=1386 select... From... Where...

Here, a conversion of the database code page is made when the data is poured out of the database

two。 Time field formatted

MODIFIED BY TIMESTAMPFORMAT= "yyyy-mm-dd hh:mm:ss tt"

Example: Exprot to filename.del for del MODIFIED BY TIMESTAMPFORMAT= "yyyy-mm-dd hh:mm:ss tt" select... From... Where...

About Import

Introduction of 1.Import mode

CREATE/INSERT/INSERT_UPDATE/REPLACE/REPLACE_CREATE

CREATE: first create the target table and its index, and then import the data into the new table. The only file format supported by this option is PC/IXF. You can also specify the name of the tablespace in which the new table resides

INSERT: inserts the imported data into the table. The target table must already exist.

INSERT_UPDATE: insert data into a table, or update rows in the table that have matching primary keys. The target table must already exist and a primary key is defined.

REPLACE: deletes all existing data and inserts the imported data into an existing target table.

REPLACE_CREATE: if the target table already exists, the import utility deletes the existing data and inserts the new data, just like the REPLACE option. If the target table is not already defined, create the table and its associated indexes first, and then import the data. As you might imagine, the input file must be in PC/IXF format, because that format contains a structured description of the exported table. REPLACE_CREATE cannot be used if the target table is a parent table referenced by a foreign key.

two。 Batch submission

COMMITCOUNT, which ensures that the data of insert is commit once after COMMITCOUNT, which is a good method for importing files with a large amount of data.

Example: Import from filename of del COMMITCOUNT 50000 insert into tabname

3. Batch insert

MODIFIED BY COMPOUND imports the COMPOUND line records from the file as a set, which is ideal for use with the batch commit above.

Example: Import from filename of del MODIFIED BY COMPOUND = 50 insert into tabname

4. Import record limit

ROWCOUNT: only import rowcount pieces of data, sometimes, business logic needs to import only part of the data, then ROWCOUNT is a good choice, but in my test ROWCOUNT has not played a role, hehe, who is familiar with here, help me improve.

Example: Import from filename of del ROWCOUNT 10000 insert into tabname

5. Import starting point

RESTARTCOUNT: imports from the RESTARTCOUNT record of the import file

Example: Import from filename of del RESTARTCOUNT 55 ROWCOUNT 10000 insert into tabname;-- starts with 55 items and imports 10000 pieces of data

6. There is a limit on the number of warning data

WARNINGCOUNT: when there are warnings or errors in the imported data (such as type mismatch, column mismatch, etc.) and the number of entries exceeds WARNINGCOUNT, import will be stopped.

Example: Import from filename of del WARNINGCOUNT 10 insert into tabname

7. Prohibition of issuing line warnings

MODIFIED BY NOROWWARNINGS

Example: Import from filename of del MODIFIED BY NOROWWARNINGS WARNINGCOUNT 10 insert into tabname

8.LOB file

LOBS FROM: indicates the path to LOB

Example: Import from filename of del LOBS FROM'/ home' MODIFIED BY NOROWWARNINGS WARNINGCOUNT 10 insert into tabname

9. For self-increasing sequences (GENERATED ALWAYS)

It is recommended not to perform import operations on tables of self-increasing sequences, because import only has operations in MODIFIED BY IDENTITYIGNORE and MODIFIED BY IDENTITYMISSING 2 for self-increasing sequences, both of which will change the original value of self-increasing sequences, so if there is an association based on self-increasing sequences between the exported table and the table, it will lose the meaning of the data itself, so it is recommended to minimize the operation of self-increasing tables based on import, what should be done? Load can be used instead of import, which we will talk about in the operation of load!

About Load

1. String interval, column interval, decimal point representation

CHARDEL/COLDEL/DECPT

Example: LOAD CLIENT FROM'F:\ s 1.del' OF DEL MODIFIED BY CHARDEL (COLDEL= DECPT? INSERT INTO "DB2ADMIN". "ZXTABLES"

two。 A situation in which a newline character exists in a database record so that the data cannot be loaded

MODIFIED BY DELPRIORITYCHAR

Db2 default load priority policy is record delimiter, character delimiter, column delimiter, so record delimiter has the highest priority, so if the original file has line breaks, load thinks it is the new record. If the user contains line breaks in some cases (such as a post in the forum, it is impossible to delete the line breaks), you must use delprioritychar to change the default priority. Ensure that the data between "" is considered to be the same record with or without newline characters.

Example: LOAD CLIENT FROM'F:\ s 1.del' OF DEL MODIFIED BY DELPRIORITYCHAR INSERT INTO DB2ADMIN.ZXTABLES

Processing of table space suspension after 3.load

Copy YES/ NONRECOVERABLE

For DMS tablespaces, load defaults to copy NO mode. In this mode, after load is completed, the tablespace will be in a suspended state. At this time, you can only look up the data in the table, and you need to make a backup of the tablespace before you can perform update, insert and other operations on the table. Then we can use the above two commands to automatically perform a backup operation after the completion of Copy YES,load. NONRECOVERABLE pointed out that load is unrecoverable, and this command will not cause table space suspension or automatic table space backup, but it has a disadvantage that it cannot be restored and cannot be restored when the database is rolled back. The danger level is still a little higher, but I think NONRECOVERABLE is more practical.

Example: LOAD CLIENT FROM'F:\ s 1.del' OF DEL INSERT INTO DB2ADMIN.ZXTABLES NONRECOVERABLE

LOAD CLIENT FROM'F:\ s 1.del' OF DEL INSERT INTO DB2ADMIN.ZXTABLES COPY YES

4. Load IXF type files to multi-partition database

Partitioned db configmode load_only_verify_part part_file_location

When the data moves between two databases with different number of nodes, it will be tricky if you still want to use load to load IXF data. At that time, nothing was found in the official documents of IBM, and when you were depressed, the wolf appeared and gave a trick to share it with you.

First, copy the ixf file with the same number of partitions as the file with the suffix .000 (partition number)

For example, 2 partitions, then I need to copy 2 of the original a.ixf and name it a.ixf.000 and a.ixf.001

Then, load from staff.ixf of ixf replace into T1 partitioned db configmode load_only_verify_part part_file_location xxxx (part_file_location is the directory where a.ixf.000 is located), of course, if you are under unix, you can make a connection through ln-s.

5. For self-increasing sequences (GENERATED ALWAYS)

MODIFIED BY IDENTITYOVERRIDE: this parameter can be understood as using the self-increment in the load file as the value of the table self-increment sequence, so as to ensure the consistency of the data in the case of business significance for the self-increment sequence or association logic (primary and foreign key association).

Example: LOAD CLIENT FROM'F:\ s 1.del' OF DEL MODIFIED BY IDENTITYOVERRIDE INSERT INTO DB2ADMIN.ztables

6. For self-increasing sequences (GENERATED by default)

GENERATED by default can load data directly through the general import method, but sometimes there is a slight problem. The auto-increment sequence is not allocated, that is to say, the original table has 50 records, and the next allocation value of the self-increment sequence is 50. When you have imported 1000 records, and then find that the next allocation value of the self-increment sequence is still 50, this happens infrequently, but occasionally. What is more depressing is that when the table continues to insert data, the next allocation will conflict, especially if it is added as a primary key, it will violate the unique constraint.

Solution: first find the maximum value assigned by this sequence, select max (id) from tablea

Then use the following command, alter table tablea alter column restart with max (id) + 1

7.ROWCOUNT/WARNINGCOUNT

This is the same as IMPORT.

8.terminate,restart

If there is a fault in the process of load, such as the connection is dropped by force, the connection query table data will report an error SQL0668N, reason code 3, and you can deal with it through the load command with terminate!

LOAD CLIENT FROM'F:\ s 1.del' OF DEL terminate INTO DB2ADMIN.ztables

9. Set integrity command

If there are constraints on the table, then after giving the table load data, it will generally report an error when connecting the table, SQL0668N, reason code 1, which indicates that the table is in the state of "setting integrity pending no access", then the following command is required to operate.

Set integrity for schema.tablename immediate checked

10. Database parameters to improve import and export speed

CONFIGURATION: application heap size, which indicates the maximum amount of memory that can be used by Backup,restore,load and load recovery application colleagues. This value can be set to the number of 10000*cpu.

DB2 'UPDATE DATABASE CONFIGURATION USING UTIL_HEAP_SZ 113486'

This is the end of the content of "DB2 methods for importing and exporting data". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.