Search code examples
db2ibm-midrangedb2-400

Incorrect data population issue in numeric field in PF in AS400

I have a PF in DB2 which is showing a ++++ sign, the column value is defined as numeric 3 lengths.

enter image description here

I have tried ABS, ABSVAL, ROUND, TRUNCATE, REPLACE, and CHAR biffs on this column but none of them seems to show me what this ++++ actually is. Because of this ++++ sign, I cannot insert any data on this row, thereby stopping anything from being inserted after this row.

If possible, I am looking to remove this ++++ sign incorrect data from the file.

I shall be grateful for any help/guidance.

Solution

  • Thank you @jmarkmurphy and @charles for your thoughtful inputs, I have found my solution in both your suggestions.

    I have tried to summarize the issue for future readers as below.

    So apparently the ++++ sign or - sign is DB2's way of showing corrupt data. The possible reason for existence of such a data could be during data transmission between two systems, or improper handling of decimal data error done by the operator.

    However, I have researched a lot on this but there is no way we can see what this ++++ or - sign actually holds.

    But still just to our eyes satisfaction, as recommended by @jmarkmurphy use the hex() function on the column we can see something like

    enter image description here

    notice that the hex value for ++++ is 404040, which represents @ sign in ascii table

    Reference fore HEx to ascii conversion -

    https://www.freecodecamp.org/news/ascii-table-hex-to-ascii-value-character-code-chart-2/

    enter image description here

    The only possible way to deal with such corrupt data is to isolate them and remove them. thanks to @charles for this url

    What do hyphens signify in Db2 for i query results?