DB2 Mobile Data Summary One

The Move tool has
Import/export
Load

Db2look/db2move

Scenarios and issues with data movement
1. When making a large number of changes to a table, especially when CR, check the database backup record, whether there is a recent backup file, if so, please confirm if there is a problem is available, the other is to do a temporary backup of the data, then we need datamovement Tools
How to estimate the amount of data in a table

How to easily see if the table contains an identity column
DB2 "SELECT distinct identity from syscat.columns where tabname = ' XXXX '"

How to easily see if the table contains LOB columns
DB2 "SELECT Tabschema concat '. ' | | TabName from Syscat.columns where TypeName like '%lob ' and Tabschema ' don't like '%ibm% ' and ' Don't like ' sys% ' | Grep-i xxxxx

Another way is to view the definition of a table
db2look-d db_name-t T1 t2-e-o db2look_tables.out;cat db2look_tables.out
e.g. db2look-d sample-t dbv97fp7.employee-e
Note: If you do an alias for a table in the-t option, the data will not be exported

2. In the case of a cross-operating system, restore the database, in which case the offline or online backup file cannot be used for database recovery
It seems that export can not do this kind of work, because the syntax is to need a SELECT statement to export data, how to implement it?
Db2look+db2move

3. Table data Migration, sometimes customers need to migrate data from one table to another, where the definition of a table may be inconsistent

Identity columns:
Note: Considerations for identifying column import and export
1) Import to a table with an identity column without an identity column in the original data
A b c---> a b i1 c
Note: This is a special case where the number of columns in the original data is less than the number of columns in the target table

2) Import to a table with an identity column in the original data with an identity column
A b i1 c---> a b i1 c

3) target table with generated always identity column, no need to import the identity column values in the file

4) target table with generated always identity column, need to use the value of the identity column loaded into the original data
Note: Can the normal identity use the Identityoverride file modifier

5) The import file contains an identity column, but the table does not contain an identity column
Note: This is a special case where the number of columns in the target table is less than the original data column

Generate Columns:
Import Considerations:
When the file type modifier is not used, the following rules are followed
1) A generated column value is created when the corresponding row in the data file is missing the value of the generated column or provides a null value;
The direct import will result in the column values after the column is generated, and cannot be imported using the following methods
2) If a non-null value is provided for the generated column, the row will be rejected (sql3550w)

The build column has a similar problem with the identity column
The import and export build columns also have file modifiers similar to the identity column Generatedignore,generatedmissing,generatedoverride
Note: Using the load and file modifiers Generatedoverride will cause the following problems, how to handle
sql0668n operation not allowed for reason code "1" on table "DBV97FP7. TBGE ". sqlstate=57016????

3) If a null value is created for a non-nullable generated column, for example, if the column is defined as the sum of two table columns, but the file in both table columns contains a null value, the entire row is rejected (sql0407n)

Import and export of large objects:
When you export a table for a large object (LOB) column, the default action is to export up to 32KB for each LOB value so that it is placed in the same file as the rest of the column data. If you are exporting LOB values that exceed 32KB, you should write the LOB data to a separate file for truncation.
To specify that the LOB should be written to its own file, you can use the Lobsinfile file modifier. This file type modifier instructs Export to use the program to place the LOB data in the directory specified by the lobs to clause. The Lobsinfile file type modifier is implicitly activated using LOBs to or lobfile.
By default, the LOB value is written to the same path as the exported relational data. If the lobs to option specifies one or more paths, the export usage program loops through the LOB paths so that each successful LOB value is written to the appropriate lob file.
You can use the Lobfile option to specify a name for the output lob file. If the Lobfile option is specified, the format of the lobfilename is Lobfilespecxxx.lob, where Lobfilespec is the value specified for the Lobfile option, and XXX is the ordinal of the LOB file that the export uses to survive, otherwise, The format of Lobfilename is Exportfilename.xxx.lob, where exportfilename is the output file format name specified for the Export command, and XXX is the serial number of the LOB file exported by the Export utility
By default, multiple lobs are written to a single file, and if each lob is stored in a different file, you can use the Lobsinfepfiles file modifier to write each lob to a separate file

Null value:
There are many problems with the handling of null values, such as
1) When the import column is null, the completion of inserting a default value, not NULL, to avoid the existence of a column with a non-null limit, resulting in data import failure;
2) for the word uniquely said, for the imported data, often want to remove the character trailing space automatically, but backfired, the imported data will retain the trailing null or blank;
3) database By default, the spaces in front of the string are eliminated, and sometimes we want to keep, for example, the front of the name

The following file modifier is provided for the null processing database Nullindchar striptnulls striptblanks keepblanks

Delimiter:
What is a delimiter?
What are the types of delimiters, and what do they mean? Record delimiter, character delimiter, column delimiter

The role of delimiters is often to avoid database recognition of erroneous file data.
1) Our data, sometimes we need to import the following data
I am 6 "tall
The data does not contain the character delimiter, "" is the case of data rather than delimiters;----Here are two ways to handle this (remove the character delimiter and modify the character delimiter to other)
2) The data we want to export does not contain character delimiters;
3) Character delimiter "" conflict with data, want to change character delimiter;
4) "Vincent <row delimiter> is a manager", <row delimiter>
"Vincent
is a manager "
"Bob
is a employee "
In the imported database;

The database provides the following file modifiers: Nodoubledel,
Chardel,coldel,delprioritychar
Double character delimiter
""---explain the double-character delimiter
Nodoubledel
Character delimiter, column delimiter, Row delimiter (record delimiter)

PC/IXF:

Date format:
Date formats are prone to problems when moving data on different platforms and systems.
These file modifiers are only used for ASC and Del formats
DateFormat
Datesiso
TimeFormat
Timestampformat

DB2 Mobile Data Summary One