Question & Answer
Question
The LOAD command returns warnings in case the source data contains some errors. For example, input data does not fit into the target column, or missing data to be loaded against not nullable columns.
Users can determine the row number, which contains invalid data by warning message, but sometimes the warning message reports Fx-nnn as the row number as following.
SQL3114W Some data following "".sample"" in row "F4-697" and column "2" was not loaded.
SQL3116W The field value in row "F1-133" and column "1" is missing, but the target column is not nullable.
SQL3119W The field value in row "F0-3751" and column "1" cannot be converted to an INTEGER value. A null was loaded.
SQL3125W The character data in row "F2-304" and column "3" was truncatedbecause the data is longer than the target database column.
Cause
If CPU_PARALLELISM > 1, more than one load formatter thread (db2lfrmX) format input data to data pages.
In this case, each formatter keeps tracking the input number of rows, then it reports the row as Fx-nnn. For example, F0-100 means the 100th row handled by the formatter 0 (db2lfrm0).
Answer
Find SQL3227W messages to corresponding with the Fx-nnn to actual row number. For example, the following message shows that F4-697 is 653414th row in the input data.
SQL3227W Record token "F4-697" refers to user record number "653414".
Note: If anyorder file type modifier is enabled, LOAD command does not report SQL3227W. This modifier is implicitly turned on for all load operations for column-organized tables, multidimensional clustering (MDC) tables, and range-partitioned tables. If you need to identify wrong data and anyorder is mandatory, use CPU_PARALLELISM 1.
If number of warnings exceed WARNINGCOUNT, LOAD cannot keep tracking rhe actual row number. See the following APAR description for details.
[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PlJAAU","label":"Data Movement-\u003ELoad"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
08 September 2023
UID
ibm16853367