DB2 10.5 for Linux, UNIX, and Windows

FORCEIN option

The forcein file type modifier permits import of a PC/IXF file despite code page differences between data in the PC/IXF file and the target database. It offers additional flexibility in the definition of compatible columns.

General semantics of forcein

The following general semantics apply when using the forcein file type modifier in either an SBCS or a DBCS environment:
  • The forcein file type modifier should be used with caution. It is usually advisable to attempt an import without this option enabled. However, because of the generic nature of the PC/IXF data interchange architecture, some PC/IXF files might contain data types or values that cannot be imported without intervention.
  • Import with forcein to a new table might yield a different result than import to an existing table. An existing table has predefined target data types for each PC/IXF data type.
  • When LOB data is exported with the lobsinfile file type modifier, and the files move to another client with a different code page, then, unlike other data, the CLOBS and DBCLOBS in the separate files are not converted to the client code page when imported or loaded into a database.

Code page semantics for forcein

The following code page semantics apply when using the forcein file type modifier in either an SBCS or a DBCS environment:
  • The forcein file type modifier disables all import utility code page comparisons.

    This rule applies to code page comparisons at the column level and at the file level as well, when importing to a new or an existing database table. At the column (for example, data type) level, this rule applies only to the following database manager and PC/IXF data types: character (CHAR, VARCHAR, and LONG VARCHAR), and graphic (GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC). The restriction follows from the fact that code page attributes of other data types are not relevant to the interpretation of data type values.

  • forcein does not disable inspection of code page attributes to determine data types.

    For example, the database manager allows a CHAR column to be declared with the FOR BIT DATA attribute. Such a declaration sets both the SBCS CPGID and the DBCS CPGID of the column to zero; it is the zero value of these CPGIDs that identifies the column values as bit strings (rather than character strings).

  • forcein does not imply code page translation.

    Values of data types that are sensitive to the forcein file type modifier are copied "as is". No code point mappings are employed to account for a change of code page environments. Padding of the imported value with spaces might be necessary in the case of fixed length target columns.

  • When data is imported to an existing table using forcein:
    • The code page value of the target database table and columns always prevails.
    • The code page value of the PC/IXF file and columns is ignored.

    This rule applies whether or not forcein is used. The database manager does not permit changes to a database or a column code page value once a database is created.

  • When importing to a new table using forcein:
    • The code page value of the target database prevails.
    • PC/IXF character columns with IXFCSBCP = IXFCDBCP = 0 generate table columns marked FOR BIT DATA.
    • All other PC/IXF character columns generate table character columns with SBCS and DBCS CPGID values equal to those of the database.
    • PC/IXF graphic columns generate table graphic columns with an SBCS CPGID of "undefined", and a DBCS CPGID equal to that of the database (DBCS environment only).

Consider a PC/IXF CHAR column with IXFCSBCP = '00897' and IXFCDBCP = '00301'. This column is to be imported into a database CHAR column whose SBCS CPGID = '00850' and DBCS CPGID = '00000'. Without forcein, the utility terminates, and no data is imported, or the PC/IXF column values are ignored, and the database column contains NULLs (if the database column is nullable). With forcein, the utility proceeds, ignoring code page incompatibilities. If there are no other data type incompatibilities (such as length, for example), the values of the PC/IXF column are imported "as is", and become available for interpretation under the database column code page environment.

The following two tables show:
  • The code page attributes of a column created in a new database table when a PC/IXF file data type with specified code page attributes is imported.
  • That the import utility rejects PC/IXF data types if they are invalid or incompatible.
Table 1. Summary of Import Utility Code Page Semantics (New Table) for SBCS
CODE PAGE ATTRIBUTES of PC/IXF DATA TYPE CODE PAGE ATTRIBUTES OF DATABASE TABLE COLUMN
Without forcein With forcein
(0,0) (0,0) (0,0)
(a,0) (a,0) (a,0)
(x,0) reject (a,0)
(x,y) reject (a,0)
(a,y) reject (a,0)
(0,y) reject (0,0)
Note:
  1. This table assumes there is no conversion table between a and x. If there were, items 3 and 4 would work successfully without forcein.
  2. See the notes for Table 2.
Table 2. Summary of Import Utility Code Page Semantics (New Table) for DBCS
CODE PAGE ATTRIBUTES of PC/IXF DATA TYPE CODE PAGE ATTRIBUTES OF DATABASE TABLE COLUMN
Without forcein With forcein
(0,0) (0,0) (0,0)
(a,0) (a,b) (a,b)
(x,0) reject (a,b)
(a,b) (a,b) (a,b)
(x,y) reject (a,b)
(a,y) reject (a,b)
(x,b) reject (a,b)
(0,b) (-,b) (-,b)
(0,y) reject (-,b)
Note:
  1. This table assumes there is no conversion table between a and x.
  2. Code page attributes of a PC/IXF data type are shown as an ordered pair, where x represents a non-zero single-byte code page value, and y represents a non-zero double-byte code page value. A '-' represents an undefined code page value.
  3. The use of different letters in various code page attribute pairs is deliberate. Different letters imply different values. For example, if a PC/IXF data type is shown as (x,y), and the database column as (a,y), x does not equal a, but the PC/IXF file and the database have the same double-byte code page value y.
  4. Only character and graphic data types are affected by the forcein code page semantics.
  5. It is assumed that the database containing the new table has code page attributes of (a,0); therefore, all character columns in the new table must have code page attributes of either (0,0) or (a,0).

    In a DBCS environment, it is assumed that the database containing the new table has code page attributes of (a,b); therefore, all graphic columns in the new table must have code page attributes of (-,b), and all character columns must have code page attributes of (a,b). The SBCS CPGID is shown as '-', because it is undefined for graphic data types.

  6. The data type of the result is determined by the rules described in Data type semantics for forcein.
  7. The reject result is a reflection of the rules for invalid or incompatible data types.
The following two tables show:
  • That the import utility accepts PC/IXF data types with various code page attributes into an existing table column (the target column) having the specified code page attributes.
  • That the import utility does not permit a PC/IXF data type with certain code page attributes to be imported into an existing table column having the code page attributes shown. The utility rejects PC/IXF data types if they are invalid or incompatible.
Table 3. Summary of Import Utility Code Page Semantics (Existing Table) for SBCS
CODE PAGE ATTRIBUTES OF PC/IXF DATA TYPE CODE PAGE ATTRIBUTES OF TARGET DATABASE COLUMN RESULTS OF IMPORT
Without forcein With forcein
(0,0) (0,0) accept accept
(a,0) (0,0) accept accept
(x,0) (0,0) accept accept
(x,y) (0,0) accept accept
(a,y) (0,0) accept accept
(0,y) (0,0) accept accept
(0,0) (a,0) null or reject accept
(a,0) (a,0) accept accept
(x,0) (a,0) null or reject accept
(x,y) (a,0) null or reject accept
(a,y) (a,0) null or reject accept
(0,y) (a,0) null or reject null or reject
Note:
  • This table assumes there is no conversion table between a and x.
  • See the notes for Table 1.
  • The null or reject result is a reflection of the rules for invalid or incompatible data types.
Table 4. Summary of Import Utility Code Page Semantics (Existing Table) for DBCS
CODE PAGE ATTRIBUTES OF PC/IXF DATA TYPE CODE PAGE ATTRIBUTES OF TARGET DATABASE COLUMN RESULTS OF IMPORT
Without forcein With forcein
(0,0) (0,0) accept accept
(a,0) (0,0) accept accept
(x,0) (0,0) accept accept
(a,b) (0,0) accept accept
(x,y) (0,0) accept accept
(a,y) (0,0) accept accept
(x,b) (0,0) accept accept
(0,b) (0,0) accept accept
(0,y) (0,0) accept accept
(0,0) (a,b) null or reject accept
(a,0) (a,b) accept accept
(x,0) (a,b) null or reject accept
(a,b) (a,b) accept accept
(x,y) (a,b) null or reject accept
(a,y) (a,b) null or reject accept
(x,b) (a,b) null or reject accept
(0,b) (a,b) null or reject null or reject
(0,y) (a,b) null or reject null or reject
(0,0) (-,b) null or reject accept
(a,0) (-,b) null or reject null or reject
(x,0) (-,b) null or reject null or reject
(a,b) (-,b) null or reject null or reject
(x,y) (-,b) null or reject null or reject
(a,y) (-,b) null or reject null or reject
(x,b) (-,b) null or reject null or reject
(0,b) (-,b) accept accept
(0,y) (-,b) null or reject accept
Note:
  • This table assumes there is no conversion table between a and x.
  • See the notes for Table 1.
  • The null or reject result is a reflection of the rules for invalid or incompatible data types.

Data type semantics for forcein

The forcein file type modifier permits import of certain PC/IXF columns into target database columns of unequal and otherwise incompatible data types. The following data type semantics apply when using forcein in either an SBCS or a DBCS environment (except where noted):
  • In SBCS environments, forcein permits import of:
    • A PC/IXF BIT data type (IXFCSBCP = 0 = IXFCDBCP for a PC/IXF character column) into a database character column (non-zero SBCS CPGID, and DBCS CPGID = 0); existing tables only
    • A PC/IXF MIXED data type (non-zero IXFCSBCP and IXFCDBCP) into a database character column; both new and existing tables
    • A PC/IXF GRAPHIC data type into a database FOR BIT DATA column (SBCS CPGID = 0 = DBCS CPGID); new tables only (this is always permitted for existing tables).
  • The forcein file type modifier does not extend the scope of valid PC/IXF data types.

    PC/IXF columns with data types not defined as valid PC/IXF data types are invalid for import with or without forcein.

  • In DBCS environments, forcein permits import of:
    • A PC/IXF BIT data type into a database character column
    • A PC/IXF BIT data type into a database graphic column; however, if the PC/IXF BIT column is of fixed length, that length must be even. A fixed length PC/IXF BIT column of odd length is not compatible with a database graphic column. A varying-length PC/IXF BIT column is compatible whether its length is odd or even, although an odd-length value from a varying-length column is an invalid value for import into a database graphic column
    • A PC/IXF MIXED data type into a database character column.
Table 5 summarizes PC/IXF file import into new or existing database tables with forcein specified.
Table 5. Summary of PC/IXF File Import with forcein
PC/IXF COLUMN DATA TYPE DATABASE COLUMN DATA TYPE
SMALL INT INT BIGINT DEC FLT (0,0) (SBCS, 0)e (SBCS, DBCS)b GRAPHb DATE TIME TIME STAMP
-SMALLINT N                      
E E E Ea E              
-INTEGER   N                    
Ea E E Ea E              
-BIGINT     N                  
Ea Ea E Ea E              
-DECIMAL       N                
Ea Ea Ea Ea E              
-FLOAT         N              
Ea Ea Ea Ea E              
-(0,0)                        
          N            
-(SBCS,0)           E E w/F E w/F E w/F Ec Ec Ec
            N N        
-(SBCS, DBCS)           E E E   Ec Ec Ec
            N w/Fd N   Ec Ec Ec
          E E w/F E        
-GRAPHIC           N w/Fd     N      
          E     E      
-DATE                   N    
                  E    
-TIME                     N  
                    E  
-TIME STAMP                       N
                      E
a Individual values are rejected if they are out of range for the target numeric data type.
b Data type is available only in DBCS environments.
c Individual values are rejected if they are not valid date or time values.
d Applies only if the source PC/IXF data type is not supported by the target database.
e Data type is not available in DBCS environments.
Note: If a PC/IXF column can be imported into a database column only with forcein, the string 'w/F' is displayed together with an 'N' or an 'E'. An 'N' indicates that the utility is creating a new database table; an 'E' indicates that the utility is importing data to an existing database table. The forcein file type modifier affects compatibility of character and graphic data types only.
Related reference:
General rules governing PC/IXF file import into databases
PC/IXF data types