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

The import utility uses SQL INSERT statements to populate tables, type tables, or views with data. If the table or view that receives the imported data already contains data, the input data can either replace or append to the existing data.

IMPORT FROM filename OF {IXF | ASC | DEL | WSF}

[LOBS FROM lob-path [{, lob-path}...] ]

[XML FROM xml-path [{, xml-path}...] ]

[MODIFIED BY filetype-mod... ]

[METHOD {L (col-start col-end [{, col-start col-end}...] )

[NULL INDICATORS (col-position [{, col-position}...] )] |

N (col-name [{, col-name}...] ) |

P (col-position [{, col-position} …] )

}

]

[XMLPARSE {STRIP | PRESERVE} WHITESPACE]

[XMLVALIDATE USING

{XDS [DEFAULT schema-sqlid]

[IGNORE (schema-sqlid [{, schema-sqlid}...])]

[MAP

((schema-sqlid,schema-sqlid))

[{(schema-sqlid,schema-sqlid)}...]

)

] |

SCHEMA schema-sqlid |

SCHEMALOCATION HINTS

}

]

[allow {NO | WRITE} ACCESS]

[COMMITCOUNT {n | AUTOMATIC}]

[{RESTARTCOUNT | SKIPCOUNT} n] [ROWCOUNT n] [WARNINGCOUNT n]

[NOTIMEOUT]

[MESSAGES message-file]

{{INSERT | INSERT_UPDATE | REPLACE | REPLACE_CREATE}

INTO {table-name [(insert-column, …)] | | hierarchy-description} |

CREATE INTO {table-name [(insert-column, …)] | |

Hierarchy-description

{AS ROOT TABLE | UNDER sub-table-name}

[IN tablespace-name [INDEX IN tablespace-name]

[LONG IN tablespace-name]

]

}

}

Filetype-mod:

COMPOUND=x 、 INDEXSCHEMA=schema 、 FORCEIN 、 INDEXIXF 、 IMPLIEDDECIMAL 、

NOCHECKLENGTHS 、 NOEOFCHAR 、 NULLINDCHAR=x 、 RECLEN=x 、 STRIPTBLANKS 、

STRIPTNULLS 、 NO_TYPE_ID 、 NODOUBLEDEL 、 LOBSINFILE 、 USEDEFAULTS 、

CHARDELx 、 COLDELx 、 DLDELx 、 DECPLUSBLANK 、 DECPTx 、 DATESISO 、

DELPRIORITYCHAR 、 IDENTITYMISSING 、 IDENTITYIGNORE 、

GENERATEDMISSING 、 GENERATEDIGNORE 、 DATEFORMAT=x 、 TIMEFORMAT=x 、

TIMESTAMPFORMAT=x 、 KEEPBLANKS 、 CODEPAGE=x 、 NOROWWARNINGS 、

NOCHARDEL 、 NODEFAULTS 、 USEGRAPHICCODEPAGE 、 FORCECREATE 、 XMLCHAR 、

XMLGRAPHIC, SECLABELCHAR and SECLABELNAME

Hierarchy-description:

{ALL TABLES | (sub-table-name [(insert-column, …)] ,... )} [IN]

HIERARCHY {STARTING sub-table-name | (sub-table-name, … )}

Description:

1. The MODIFIED BY clause specifies the file type modifier to change the data format and tells the IMPORT utility how to handle the data to improve performance.

2. By default, the IMPORT utility is not implemented until the import is successful, with some exceptions for ALLOW WRITE ACCESS imports. Although this will change the import speed, given the parallelism, restartability, and activity log space considerations, it is best to specify that the implementation occurs during the import. One way is to set the COMMITCOUNT parameter to "automatic", which instructs IMPORT to internally determine when the implementation should be implemented. Or set COMMITCOUNT to a specific number, instructing IMPORT to perform an implementation when the specified number of imported records is reached.

3. Because the IMPORT utility is an embedded SQL application and performs SQL memory access internally, the optimizations applied to SQL operations are also applied to IMPORT. You can use the compound file type modifier to insert a specified number of lines at a time, rather than line by line by default. If you expect a large number of warnings to be generated during the import (and therefore slow down the operation), you can specify the norowwarnings file type modifier to suppress warnings about rejected lines.

4. The MESSAGES parameter specifies the message file, and during import, a standard ASCII text message file is written to contain error messages, warning messages, and reference messages related to the operation. You can access the message file while the import is in progress to monitor the progress of the import. If the import operation fails, you can use the message file to determine the restart location because the message file indicates the last line that was successfully imported. Note: if the amount of output messages generated by the import operation for the remote database exceeds the 60KB, the utility retains the pre-30KB and post-30KB.

First, there are five ways of IMPORT:

There are five ways to import, which are used to determine how to import data. The first three methods are INSERT, INSERT_UPDATE, and REPLACE, which are used when the target table already exists. All three methods support IXF, WSF, ASC, and DEL data formats. However, only INSERT and INSERT_UPDATE can be used with nicknames.

INSERT

Insert input data into the target table without changing existing data

INSERT_UPDATE

Updates a row with a matching primary key value with the value of the input row, and if there is no matching row, the imported row is inserted into the table.

REPLACE

Delete all existing data and insert imported data while retaining table and index definitions

The other two methods are REPLACE_CREATE and CREATE, which are used when the target table does not exist. They can only be used with input files in PC/IXF format, which contains a structural description of the table to be created. If the object table has any dependencies other than itself, the import cannot be performed in these ways. Instead of using the CREATE and REPLACE_CREATE methods of IMPORT, it is recommended to use the db2look utility instead.

REPLACE_CREATE

Delete all existing data and insert the imported data while retaining the table and index definitions; if the target table and index do not exist, create the target table and index

CREATE

Create the target table and index; you can specify the tablespace name in which the new table is created

2. IMPORT work steps:

1. Lock the table

Depending on whether parallel access to the table is allowed, IMPORT acquires exclusive (X) or non-exclusive (IX) locks on the existing target table.

2. Find and retrieve data

IMPORT uses the FROM clause to find input data. If the command indicates that XML or LOB data exists, IMPORT looks for that data.

3. Insert data

IMPORT replaces existing data or adds new rows to the table.

4. Check constraints and fire triggers

After the data is written, IMPORT ensures that each inserted row meets the constraints defined for the target table. Information about rejected lines is written to the message file. IMPORT also fires existing triggers.

5. Implement the operation

IMPORT saves the changes and releases the lock on the target table. You can also specify that it is implemented on a regular basis during the import.

III. Permissions required for IMPORT

Any type of import operation can be performed with DATAACCESS permissions. The following are additional permissions to perform the appropriate import type:

INSERT

CONTROL or INSERT and SELECT

INSERT_UPDATE

CONTROL or INSERT, SELECT, UPDATE, DELETE

REPLACE

CONTROL or INSERT, SELECT, DELETE

REPLACE_CREATE

When the target table exists: CONTROL or INSERT, SELECT, DELETE

When the target table does not exist: CREATETAB (for the database), USE (for the table space), and when the schema does not exist: IMPLICIT_SCHEMA (for the database) or when the schema exists: CREATEIN (for the schema)

CREATE

CREATETAB (for database) and USE (for tablespace), and when schema does not exist: IMPLICIT_SCHEMA (for database) or when schema exists: CREATEIN (for schema)

Example: import the staff table from the sample database into the test database.

D:\ > db2 export to myfile.ixf of ixf messages msg.txt select * from staff-- Export staff table data in sample database

Number of rows exported: 35

D:\ > db2look-d sample-e-t staff-o staff.dml-- get DDL

-- USER is: *

The db2look utility will consider only the specified tables

-- DDL that is creating tables

The output is sent to the file: staff.dml

-- automatically binding packages.

-- bind successfully

-- automatically binding packages.

-- bind successfully

The content of DDL is as follows:

CREATE TABLE "*". "STAFF" (

"ID" SMALLINT NOT NULL

"NAME" VARCHAR (9)

"DEPT" SMALLINT

"JOB" CHAR (5)

"YEARS" SMALLINT

"SALARY" DECIMAL (7. 2)

"COMM" DECIMAL (7. 2))

IN "USERSPACE1"

According to DDL, create the staff table in the test database.

D:\ > db2 import from myfile.ixf of ixf messages msg2.txt insert into staff-- Import data into the staff table in the test database

Number of rows read = 35

Skip rows = 0

Number of rows inserted = 35

Number of update rows = 0

Number of rejected rows = 0

Number of rows implemented = 35

D:\ > db2 select * from staff-- verify the import result

Id name dept job years salary comm

10 Sanders 20 Mgr 7 98357.50-

20 Pernal 20 Sales 8 78171.25 612.45

30 Marenghi 38 Mgr 5 77506.75-

40 O'Brien 38 Sales 6 78006.00 846.55

50 Hanes 15 Mgr 10 80659.80-

60 Quigley 38 Sales-66808.30 650.25

70 Rothman 15 Sales 7 76502.83 1152.00

80 James 20 Clerk-43504.60 128.20

90 Koonitz 42 Sales 6 38001.75 1386.70

100 Plotz 42 Mgr 7 78352.80-

110 Ngan 15 Clerk 5 42508.20 206.60

120 Naughton 38 Clerk-42954.75 180.00

130 Yamaguchi 42 Clerk 6 40505.90 75.60

140 Fraye 51 Mgr 6 91150.00-

150 Williams 51 Sales 6 79456.50 637.65

160 Molinare 10 Mgr 7 82959.20-

170 Kermisch 15 Clerk 4 42258.50 110.10

180 Abrahams 38 Clerk 3 37009.75 236.50

190 Sneider 20 Clerk 8 34252.75 126.50

200 Scoutten 42 Clerk-41508.60 84.20

10 Lu 10 Mgr 10 90010.00-

220 Smith 51 Sales 7 87654.50 992.80

230 Lundquist 51 Clerk 3 83369.80 189.65

240 Daniels 10 Mgr 5 79260.25-

250 Wheeler 51 Clerk 6 74460.00 513.30

260 Jones 10 Mgr 12 81234.00-

270 Lea 66 Mgr 9 88555.50-

280 Wilson 66 Sales 9 78674.50 811.50

290 Quill 84 Mgr 10 89818.00-

300 Davis 84 Sales 5 65454.50 806.10

310 Graham 66 Sales 13 71000.00 200.30

320 Gonzales 66 Sales 4 76858.20 844.00

330 Burke 66 Clerk 1 49988.00 55.50

340 Edwards 84 Sales 7 67844.00 1285.00

350 Gafney 84 Clerk 5 43030.50 188.00

35 records have been selected.

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.