DB2 Basics

Exploit file type modifiers that are built into the DB2 data movement utilities

Comments

The IBM® DB2® Universal DatabaseTM (DB2 UDB) data movement utilities (export, import, and load) support a great variety of options that can accommodate virtually any type of scenario you are likely to encounter. The syntax of the corresponding DB2 UDB commands is fairly complex. To minimize this complexity while maximizing the versatility of these utilities, the syntax includes the powerful MODIFIED BY clause, which represents an elegant way to specify processing options that depend on what input or output file types are being used (see Figure 1). These processing options that depend on file type are known as file type modifiers.

Figure 1. The MODIFIED BY clause is part of the syntax of the DB2 EXPORT, IMPORT, and LOAD commands. More than one modifier can be specified in the same command.
Figure 1
Figure 1

File type modifiers in detail

There are currently almost 50 file type modifiers (see Table 1; click on the name of a modifier to jump to its description in one of the tables that follow). Some of them are designed to make the utilities run faster, and most of them enhance the versatility and flexibility of the data movement utilities.

This article describes each of the supported file type modifiers and provides you with working examples that show you exactly how to use the modifiers. The examples are taken from command scripts; you can copy, paste and run them yourself. Most of the examples are based on tables in the SAMPLE database that comes with DB2 UDB. A few require scenarios that are not part of the SAMPLE database, but you can use a connection to the SAMPLE database as a starting point.

The modifiers are described in Tables 2-6. There is a separate table for each file type category: All file types, ASC or DEL, ASC, DEL, and PC/IXF.

Table 1. Import, export, and load utility file type modifiers that are available in supported versions of DB2 UDB through Version 8.1.4.

ModifiehrApplicable File TypeModifierApplicable File Type
anyorderAllindexschema=schemaPC/IXF
binarynumericsASCkeepblanksDEL
chardelxDELlobsinfileAll
codepage=xASC or DELno_type_idAll
coldelxDELnochardelDEL
compound=xAllnochecklengthsASC or PC/IXF
dateformat="x"ASC or DELnodefaultsAll
datesisoDELnodoubledelDEL
decplusblankDELnoheaderALL
decptxDELnorowwarningsAll
delprioritycharDELnullindchar=xASC
dldelxDELpackeddecimalASC
dumpfile=xASC or DELpagefreespace=xAll
fastparseASC or DELreclen=xASC
forceinPC/IXFstriplzerosDEL
generatedignoreAllstriptblanksASC
generatedmissingAllstriptnullsASC
generatedoverrideAlltimeformat="x"ASC or DEL
identityignoreAlltimestampformat="x"ASC or DEL
identitymissingAlltotalfreespace=xAll
identityoverrideAllusedefaultsAll
implieddecimalASC or DELusegraphiccodepageASC or DEL
indexfreespace=xAllzoneddecimalASC
indexixfPC/IXF

Table 2. File type modifiers that can be used with all supported file types

ModifierExportImportLoadDescriptionExample
anyordernonoyesSpecifies that the load utility can process the input data in any order, resulting in better performance on symmetric multiprocessing (SMP) systems. If the value of the CPU_PARALLELISM parameter on the LOAD command is 1, this modifier is ignored.create table newemp like employee;
export to empdata.del of del messages export.msg select * from employee;
load from empdata.del of del modified by anyorder messages load.msg insert into newemp cpu_parallelism 2;
compoundxnoyesnoSpecifies that the DB2 import utility is to insert data one block (of x rows) at a time, rather than one row at a time. This can result in better performance. The value of x can be any integer between 1 and 100, inclusive. The import utility uses non-atomic compound SQL to insert the data: all of the inserts are attempted, regardless of errors.create table emptemp like employee;
export to empdata.ixf of ixf messages export.msg select * from employee;
import from empdata.ixf of ixf modified by compound=100 messages import.msg insert into emptemp;
generatedignorenoyesyesSpecifies that any generated column values in the import or load input file are to be ignored, and that a new value is to be generated for each row.Given load input file staffdata.del with content: "Dirk",90000.00,1000.00:
create table newstaff (name varchar(16) not null, salary decimal(9,2), bonus decimal(9,2) generated always as (salary/10));
load from staffdata.del of del modified by generatedignore messages load.msg insert into newstaff;
select * from newstaff;
generatedmissingnoyesyesSpecifies that the import or load input file does not contain any values for the generated column in the target table.Given load input file staffdata.del with content: "Crystal",102000.00:
create table newstaff (name varchar(16) not null, salary decimal(9,2), bonus decimal(9,2) generated always as (salary/10));
load from staffdata.del of del modified by generatedmissing messages load.msg insert into newstaff;
select * from newstaff;
generatedoverridenonoyesSpecifies that generated column values in the load input file are to be used when loading data into a table that has a GENERATED ALWAYS column. If you use this modifier, your table will be placed in check pending state following the load operation, to give you the opportunity to verify the integrity of the new data. Integrity in this case means consistency with the generated column specification. To take the table out of this state without verifying the input values, issue the following command after the load operation: set integrity for <table-name> generated column immediate unchecked. To take the table out of check pending state and verify the input values, issue the following command instead: set integrity for <table-name> immediate checked.Given load input file staffdata.del with content: "Jack",500000.00,50000:
create table newstaff (name varchar(16) not null, salary decimal(9,2), bonus decimal(9,2) generated always as (salary/10));
load from staffdata.del of del modified by generatedoverride messages load.msg insert into newstaff;
set integrity for newstaff immediate checked;
select * from newstaff;
identityignorenoyesyesSpecifies that any identity column values in the import or load input file are to be ignored, and that a new identity value is to be generated for each row.Given load input file custdata.del with content: 1,"Jacek":
create table customers (custno smallint not null generated always as identity (start with 500, increment by 1), custname varchar(16));
load from custdata.del of del modified by identityignore messages load.msg insert into customers;
select * from customers;
identitymissingnoyesyesSpecifies that the import or load input file does not contain any values for the identity column in the target table.Given load input file custdata.del with content: "Helmut":
create table customers (custno smallint not null generated always as identity (start with 500, increment by 1), custname varchar(16));
load from custdata.del of del modified by identitymissing messages load.msg insert into customers;
select * from customers;
identityoverridenonoyesSpecifies that identity column values in the load input file are to be used when loading data into a table that has a GENERATED ALWAYS identity column; any rows with no values (or null values) for the identity column are rejected.Given load input file custdata.del with content: 3,"Shrek":
create table customers (custno smallint not null generated always as identity (start with 500, increment by 1), custname varchar(16));
load from custdata.del of del modified by identityoverride messages load.msg insert into customers;
select * from customers;
indexfreespace=xnonoyesSpecifies an integer value between 0 and 99, inclusive, that represents the percentage of each index page that is to be left as free space when rebuilding an index that is defined on the target table. More free space means that future data insertions and updates may result in a reduced need for table reorganization. The indexfreespace value takes precedence over the value of the PCTFREE parameter that was specified during index creation.create table newemp like employee;
export to empdata.del of del messages export.msg select * from employee;
load from empdata.del of del modified by indexfreespace=30 messages load.msg insert into newemp;
lobsinfileyesyesyes1Specifies that large object (LOB) data is to be exported to locations that are identified in the LOBS TO clause, or imported or loaded from locations that are identified in the LOBS FROM clause.export to empphoto.ixf of ixf lobs to /home/melnyk/ lobfile photo modified by lobsinfile messages export.msg select * from emp_photo;
no_type_idnoyesnoSpecifies that the import data file contains no type_id column data. This modifier is useful when importing data (originally exported from a regular or non-typed table) into a single subtable in a hierarchy.A working example of how to export data from a regular table, and then import it into a single subtable in a hierarchy is beyond the scope of this article. For information on moving data between typed tables, see the IBM DB2 Universal Database Data Movement Utilities Guide and Reference.
nodefaultsnoyesnoSpecifies that the default value defined on a column is not to be imported if the source column is missing from the input data file.Given import data file tabdata.del with content: 9999:
create table tab1 (col1 integer, col2 integer with default 25);
import from tabdata.del of del modified by nodefaults method p (1) messages import.msg insert into tab1;
select * from tab1;
noheadernonoyesSpecifies that there is no partition header at the beginning of the input data file. In a partitioned database environment, during a LOAD_ONLY operation, partition headers are used to verify that the file only contains data belonging to a specific database partition. This modifier can be specified when loading data into a table that is defined on a single database partition, because in that case, there is no chance of loading data into the wrong database partition.A working example of how to load data in a partitioned database environment is beyond the scope of this article. For information on moving data in partitioned database environments, see the IBM DB2 Universal Database Data Movement Utilities Guide and Reference.
norowwarningsnoyesyesSpecifies that warning messages about rows that were rejected during an import or load operation are to be suppressed.Given load input file tabdata.del with content: 12f,"Sally":
create table tab1 (col1 integer not null, col2 varchar(10), primary key (col1));
load from tabdata.del of del modified by norowwarnings messages load.msg insert into tab1;
pagefreespace=xnonoyesSpecifies an integer value between 0 and 100, inclusive, that represents the percentage of each data page that is to be left as free space. More free space means that future data insertions and updates may result in a reduced need for table reorganization. The pagefreespace value takes precedence over the value of the PCTFREE parameter for the table, which can be changed through the ALTER TABLE statement.create table newemp like employee;
export to empdata.del of del messages export.msg select * from employee;
load from empdata.del of del modified by pagefreespace=50 messages load.msg insert into newemp;
totalfreespace=xnonoyesSpecifies an integer value greater than or equal to 0 that represents the percentage of the total number of pages in the table that is to be appended to the table as free space.create table newemp like employee;
export to empdata.del of del messages export.msg select * from employee;
load from empdata.del of del modified by totalfreespace=110 messages load.msg insert into newemp;
usedefaultsnoyesyesSpecifies that the default value defined on a column is to be imported or loaded if the input data file contains no value for that column.Given import data file tabdata.del with content: 9999,:
create table tab1 (col1 integer, col2 integer with default 25);
import from tabdata.del of del modified by usedefaults messages import.msg insert into tab1;
select * from tab1;
1 This modifier cannot be used with the CURSOR file type.

Table 3. File type modifiers that can be used with ASCII (ASC or DEL) file types

ModifiehrExportImportLoadDescriptionExample
codepage=xSee Table 5.yesyes1Specifies an ASCII character string that is interpreted as the source code page of the data that is to be imported or loaded. This is useful if you want to avoid corruption when moving data between systems running in different code pages. Character data in the input file is first converted from the code page specified by this modifier to the current system code page, and then from the current system code page to the database code page. Keep in mind that if the input file contains a character that is not recognized by the current system code page, that character cannot be imported or loaded into the database. Moreover, data that expands during code page conversion might be truncated. For more information about code page conversion through the DB2 data movement utilities, see DB2 UDB National Language Support for the Command Line Processor and Utilities.create table stafftemp like staff;
export to staffdata.del of del messages export.msg select * from staff;
load from staffdata.del of del modified by codepage=850 messages load.msg insert into stafftemp;
dateformat="x"noyesyesSpecifies a character string that is interpreted as the date format for the imported or loaded data. A value of 1 is assigned to each unspecified element. For a description of all the valid date elements, see the IBM DB2 Universal Database Data Movement Utilities Guide and Reference.Given import data file salesdata.asc with content: 23.04.2004LUCCHESSI++++++Ontario-South+++++++++++102:
create table salestemp like sales;
import from salesdata.asc of asc modified by dateformat="DD.MM.YYYY" method l (1 10, 11 25, 26 40, 41 51) messages import.msg insert into salestemp;
dumpfile=xnonoyesSpecifies the fully qualified name of an exception file to which rejected rows are to be written.Given load input file orgdata.asc with content: ++++++l0Head Office++++++++160Corporate+New York+++++2:
create table orgtemp like org;
load from orgdata.asc of asc modified by dumpfile=/home/melnyk/DUMP.FILE method l (1 8, 9 22, 23 30, 31 40, 41 53) messages load.msg insert into orgtemp;
fastparsenonoyes3Specifies that reduced syntax checking is to be done on load input data. This modifier can substantially improve performance, and is recommended when you know with certainty that your data is clean.Given load input file orgdata.asc with content: ++++++10Head Office++++++++1z0Corporate+New York+++++2:
create table orgtemp like org;
load from orgdata.asc of asc modified by fastparse method l (1 8, 9 22, 23 30, 31 40, 41 53) messages load.msg insert into orgtemp;
implieddecimalnoyesyesSpecifies that the location of an implied decimal point is to be determined by the column definition, and not assumed to be at the end of the value.Given import data file decdata.asc with content: 12345:
create table decimals (dec1 decimal(7,2));
import from decdata.asc of asc modified by implieddecimal method l (1 5) messages import.msg insert into decimals;
timeformat="x"noyesyesSpecifies a character string that is interpreted as the time format for the imported or loaded data. A value of 0 is assigned to each unspecified element. For a description of all the valid time elements, see the IBM DB2 Universal Database Data Movement Utilities Guide and Reference.Given load input file timedata.asc with content: 10.56 PM:
create table times (timeofday time);
load from timedata.asc of asc modified by timeformat="HH.MM TT" method l (1 8) messages load.msg insert into times;
timestampformat="x"See Table 5.yesyesSpecifies a character string that is interpreted as the timestamp format for the imported or loaded data. Be sure to avoid ambiguity when specifying the month and the minute elements, which both use the letter "M". A month element must be adjacent to other date elements, and a minute element must be adjacent to other time elements. For a description of all the valid timestamp elements, see the IBM DB2 Universal Database Data Movement Utilities Guide and Reference.create table in_traytemp like in_tray;
insert into in_tray values ('2004-04-16-17.12.30.000000', 'Melnyk', 'Any subject', 'Any note text');
export to in_traydata.del of del modified by timestampformat="yyyy.mm.dd hh:mm tt" messages export.msg select * from in_tray;
load from in_traydata.del of del modified by timestampformat="yyyy.mm.dd hh:mm tt" messages load.msg insert into in_traytemp;
usegraphiccodepagenoyesyesSpecifies that graphic or variable-length graphic data in a file with both single-byte and double-byte data is to be imported or loaded using the graphic code page. This modifier supports input data files that have data in both character and graphic code pages. The modifier is particularly useful during dropped table recovery if the table being recovered has graphic data. The ROLLFORWARD DATABASE command gives you the option of writing recovered data from a previously dropped table to a DEL file, which could contain both character and graphic code pages. Do not use this modifier with data files produced by the DB2 export utility; such files contain only one code page.Given load input file data_1208_1200, which contains the values "adil" (VARCHAR(20)) and "adil" (VARGRAPHIC(20)):
create database db1208 using codeset UTF-8 territory US;
connect to db1208;
create table t1 (c1 varchar(20), c2 vargraphic(20));
load from data_1208_1200 of del modified by codepage=1208 usegraphiccodepage messages load.msg insert into t1;
select * from t1;
The character data is in code page 1208 (UTF-8), and the graphic data is in code page 1200 (UTF-16). The load input file (data_1208_1200) could have been created during recovery of a table that had been dropped from this Unicode database.
1 This modifier cannot be used with the CURSOR file type.
2Each plus sign (+) character in the input data represents a blank character. The plus sign characters are included for clarity only, and should be replaced with blank characters before you try this example.
3This modifier cannot be used with the CURSOR or the PC/IXF file type.

Table 4. File type modifiers that can be used with the non-delimited ASCII (ASC) file type

MhodifierExportImportLoadDescriptionExample
binarynumericsnonoyesSpecifies that numeric (not decimal) data in the load input file is in binary format (not to be confused with binary numerals, such as 00110110). This modifier can improve performance, because costly conversions are avoided; it must be specified together with the reclen modifier. The equivalent modifier for decimal data is packeddecimal.Given load input file bigtbl_bin.asc, which contains the values -32767 (SMALLINT), 2147483646 (INTEGER), and 9223372036854775799 (BIGINT) in binary format:
load from bigtbl_bin.asc of asc modified by reclen=15 binarynumerics method l (1 2, 3 6, 7 14) messages load.msg insert into bigtbl;
nochecklengthsnoyesyesSpecifies that an attempt should be made to import or to load each row, even if the input data exceeds the size of the target table column. This modifier is useful if you know that the input data will fit in every case.Given import data file orgdata.asc with content: ++++++10Head Office++++++++160Corporate+New York (The Big Apple)1:
create table orgtemp like org;
import from orgdata.asc of asc modified by nochecklengths method l (1 8, 9 22, 23 30, 31 40, 41 64) messages import.msg insert into orgtemp;
nullindchar=xnoyesyesSpecifies that the value of x (a single character) is to be used in place of 'Y' as the null indicator value.Given import data file namesdata.asc with content: Rosemary+++++++++++++++n1:
create table names (firstname varchar(12), lastname varchar(12));
import from namesdata.asc of asc modified by nullindchar=N method l (1 12, 13 24) null indicators (12, 24) messages import.msg insert into names;
select * from names;
packeddecimalnonoyesSpecifies that decimal data in the load input file is in packed decimal format. (Packed decimal encoding uses one byte to represent two decimal digits.) This modifier can improve performance, because costly conversions are avoided; it must be specified together with the reclen modifier. The equivalent modifier for non-decimal numeric data is binarynumerics.Given load input file bigtbl_bp.asc, which contains the value -45.50 (DECIMAL(4,2)) in packed decimal format:
load from bigtbl_bp.asc of asc modified by reclen=4 packeddecimal method l (1 3) messages load.msg insert into bigtbl;
reclen=xnoyesyesSpecifies that the DB2 import utility or the DB2 load utility is to read x characters for each row, and that a new-line character is not to be interpreted as the end of a row. The maximum value of x is 32,767.Given import data file namesdata.asc with content: Rosemary++++Joanna++++++1:
create table names (name varchar(12));
import from namesdata.asc of asc modified by reclen=12 method l (1 12, 13 24) messages import.msg insert into names;
select * from names;
striptblanksnoyesyesSpecifies that any trailing blank characters are to be truncated when importing or loading data into a variable-length column.Given load input file orgdata.asc with content: ++++++10Head Office++++++++160Corporate+New York+++++1:
create table orgtemp like org;
load from orgdata.asc of asc modified by striptblanks method l (1 8, 9 22, 23 30, 31 40, 41 53) messages load.msg insert into orgtemp;
select location concat division from orgtemp;
striptnullsnoyesyesSpecifies that any trailing null characters are to be truncated when importing or loading data into a variable-length column.Given load input file orgdata.asc with content: ++++++10Head Office++++++++160Corporate+New York+----1,2:
create table orgtemp like org;
load from orgdata.asc of asc modified by striptnulls method l (1 8, 9 22, 23 30, 31 40, 41 53) messages load.msg insert into orgtemp;
select location concat division from orgtemp;
zoneddecimalnonoyesSpecifies that decimal data in the load input file is in zoned decimal format. (Zoned decimal encoding uses one byte to represent one decimal digit.) This modifier can improve performance, because costly conversions are avoided; it must be specified together with the reclen modifier. The equivalent modifier for non-decimal numeric data is binarynumerics.Given load input file zoneddata.asc, which contains the value -123.45 (DECIMAL(5,2)) in zoned decimal format:
load from zoneddata.asc of asc modified by reclen=6 zoneddecimal method l (1 5) messages load.msg insert into zoned_dec;
1Each plus sign (+) character in the input data represents a blank character. The plus sign characters are included for clarity only, and should be replaced with blank characters before you try this example.
2Each minus sign (-) character in the input data represents a null character. The minus sign characters are included for clarity only, and should be replaced with null characters before you try this example. You can use a hex editor to generate null characters, which have a hex value of zero.

Table 5. File type modifiers that can be used with the delimited ASCII (DEL) file type

MhodifierExportImportLoadDescriptionExample
chardelxyesyesyesSpecifies that the value of x (a single character) is to be used in place of the double quotation mark to enclose character string data. To specify the single quotation mark as the character string delimiter, specify it twice, as shown in the example.export to orgdata.del of del modified by chardel'' messages export.msg select * from org;
codepage=xyesSee Table 3.See Table 3.Specifies an ASCII character string that is interpreted as the target code page of the exported data. This is useful if you want to avoid corruption when moving data between systems running in different code pages. Character data is first converted from the database code page to the current system code page, and then from the current system code page to the code page specified by this modifier. For more information about code page conversion through the DB2 data movement utilities, see DB2 UDB National Language Support for the Command Line Processor and Utilities.export to staffdata.del of del modified by codepage=850 messages export.msg select * from staff;
coldelxyesyesyesSpecifies that the value of x (a single character) is to be used in place of the comma to separate column data.create table orgtemp like org;
export to orgdata.del of del modified by coldel; messages export.msg select * from org;
import from orgdata.del of del modified by coldel; messages import.msg insert into orgtemp;
datesisoyesnonoSpecifies that date values are to be exported in ISO format ("YYYY-MM-DD").export to salesdata.del of del modified by datesiso messages export.msg select * from sales;
decplusblankyesyesyesSpecifies that positive decimal values are to be prefixed with a blank space instead of a plus sign (+).create table stafftemp like staff;
import from staffdata.del of del modified by decplusblank messages import.msg insert into stafftemp;
decptxyesyesyesSpecifies that the value of x (a single character) is to be used in place of a period as the decimal point character.create table stafftemp like staff;
export to staffdata.del of del modified by decpt# messages export.msg select * from staff;
load from staffdata.del of del modified by decpt# messages load.msg insert into stafftemp;
delprioritycharnoyesyesSpecifies that delimiter processing during import or load operations is to occur in the following order of precedence: character, row, column. This will ensure that any carriage return or line feed characters that appear within character string data are not misinterpretted as row delimiters, which would be the case if the default precedence order (row, character, column) were being enforced.Given load input file contactsdata.del with content: "Tallerico","123 Anyplace Street
Ourtown H0H 0H0":
create table contacts (lastname varchar(16), address varchar(64));
load from contactsdata.del of del modified by delprioritychar messages load.msg insert into contacts;
select * from contacts;
dldelxyesyesyesSpecifies that the value of x (a single character) is to be used in place of a semicolon as the field separator for DATALINK values.create table mydl (name varchar(16), dldata datalink);
insert into mydl values ('Tallerico', DLVALUE('http://dlfs.almaden.ibm.com/x/y/a.b','URL','A comment'));
export to mydldata.del of del modified by dldel* messages export.msg select * from mydl;
keepblanksnoyesyesSpecifies that any leading or trailing blank characters (not enclosed by character delimiters) in columns of type CHAR, VARCHAR, LONG VARCHAR, or CLOB are to be preserved during an import or load operation. This modifier is especially useful if you have a column defined as NOT NULL that contains one or more blank characters, and these blank characters represent valid data. If you do not specify the keepblanks modifier when importing or loading such data, the utility attempts to replace the blank characters with a null value and, because the column is not nullable, the utility returns an error. Note that CHAR columns are always padded with trailing blanks to fill the column length when loaded, but that the keepblanks modifier is necessary to preserve any leading blanks in CHAR columns.create table names (lastname char(16), firstname char(16));
create table newnames like names;
insert into names values (' Tallerico ', ' Teresa ');
export to names.del of del modified by nochardel messages export.msg select * from names;
load from names.del of del modified by keepblanks messages load.msg insert into newnames;
select firstname concat lastname from newnames;
nochardelyesyesyesSpecifies that character delimiters are to be parsed as column data. This modifier is provided to support vendor data files that do not have character delimiters. Use this modifier with care; improper usage can result in data loss or corruption.create table stafftemp like staff;
export to staffdata.del of del modified by nochardel messages export.msg select * from staff;
import from staffdata.del of del modified by nochardel messages import.msg insert into stafftemp;
nodoubledelyesyesyesSpecifies that the recognition of double character delimiters is to be suppressed. A delimiter character is usually doubled when that character is meant to be treated as actual data. Introduced to support backward compatibility, this modifier is useful, for example, if you run the DB2 export utility to produce an input data file for a vendor application that is unfamiliar with the double delimiter logic, which has been around since DB2 UDB Version 5.export to in_traydata.del of del modified by nodoubledel messages export.msg select * from in_tray;
striplzerosyesnonoSpecifies that any leading zeros are to be stripped from exported decimal columns.export to staffdata.del of del modified by striplzeros messages export.msg select * from staff;
timestampformat="x"yesSee Table 3.See Table 3.Specifies a character string that is interpreted as the timestamp format for the exported data. Be sure to avoid ambiguity when specifying the month and the minute elements, which both use the letter "M". A month element must be adjacent to other date elements, and a minute element must be adjacent to other time elements. For a description of all the valid timestamp elements, see the IBM DB2 Universal Database Data Movement Utilities Guide and Reference.insert into in_tray values ('2004-04-16-17.12.30.000000', 'Melnyk', 'Any subject', 'Any note text');
export to in_traydata.del of del modified by timestampformat="yyyy.mm.dd hh:mm tt" messages export.msg select * from in_tray;

Table 6. File type modifiers that can be used with the PC/IXF file type

ModifiehrExportImportLoadDescriptionExample
forceinnoyesyesSpecifies that the import or the load utility is not to reject data on account of code page mismatches, and that it is to suppress translation between code pages. This modifier should be used with caution, but can be useful when processing files containing data types or values that cannot otherwise be imported or loaded. For detailed information about this modifier and its use, see the IBM DB2 Universal Database Data Movement Utilities Guide and Reference.create table resumes like emp_resume;
export to emp_resumedata.ixf of ixf messages export.msg select * from emp_resume;
load from emp_resumedata.ixf of ixf modified by forcein messages load.msg insert into resumes;
indexixfnoyesnoSpecifies that the import utility is to drop all indexes defined on the table, and to create new ones from the index definitions in the PC/IXF file. This modifier can only be used when the contents of the table are being replaced.create table newemp like employee;
export to empdata.ixf of ixf messages export.msg select * from employee;
import from empdata.ixf of ixf modified by indexixf messages import.msg replace_create into newemp;
indexschema=schemanoyesnoSpecifies a schema name for use during index creation. If you do not specify a value for schema, uses the connection user ID instead. If the indexschema modifier is not specified, uses the schema in the PC/IXF file.create table newemp like employee;
export to empdata.ixf of ixf messages export.msg select * from employee;
import from empdata.ixf of ixf modified by indexschema=SHREK messages import.msg insert into newemp;
nochecklengthsnoyesyesSpecifies that an attempt should be made to import or to load each row, even if the input data exceeds the size of the target table column. This modifier is useful if you know that the input data will fit in every case.create table resumes like emp_resume;
export to emp_resumedata.ixf of ixf messages export.msg select * from emp_resume;
load from emp_resumedata.ixf of ixf modified by nochecklengths messages load.msg insert into resumes;

Summary

The DB2 data movement utility file type modifiers give you many different options for customizing your data movement operations. They help make it easy to export, import, or load data in a variety of configurations, and can be used to make the utilities run more efficiently when large amounts of data are being moved in or out of database tables. You can easily adapt the examples that are included in this article to experiment with your own data and find new ways to exploit these modifiers in your environment.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13665
ArticleTitle=DB2 Basics: Exploit file type modifiers that are built into the DB2 data movement utilities
publish-date=05062004