Low Values in Binary Data

[pinakigit]

#Background

Currently we are using Cobrix with Databricks to read EBCDI files from Mainframe and convert them to Parquet. All is working fine. Some of the fields have Low values along with data in the Character/String fields. When we read the binary files they look like empty boxes. All is good till here.But when we load them to Postgres DB the load is failing as it is not able to recognize these characters.

Question

We can definitely write a spark code to replace these as Spaces or something else. But will like to know if Cobrix has any function which we can apply so that it can convert these unrecogniized characters to a Default Value?

[yruslan]

Hi @pinakigit,

Cobrix can skip decoding of lower (control) when using a non-extended code page, e.g.

.option("ebcdic_code_page", "cp037") // Not "cp037_extended"

It does it by default actually since the default encoding is "common".

It is quite strange that PostgreSQL rejects some characters when loading data. Can you share the error message and the operation (maybe the piece of code) you are using that causes the error?

[pinakigit]

PostgreSQL does not allow the NULL byte (0x00, \u0000) in any text, varchar, or json field.
This is a strict rule in PostgreSQL’s UTF‑8 handling: the null byte is not a valid UTF‑8 character and cannot appear in text values.
So when you load a file that contains \u0000, PostgreSQL immediately throws:

• \u0000 is a null byte, not a printable character.
• UTF‑8 encoding forbids null bytes inside text strings.
• PostgreSQL enforces UTF‑8 strictly.

I don't think its a character difference between CP037 and CP037 extended.

[yruslan]

Hi @pinakigit ,

I couldn't reproduce the issue. Here are some more questions:

• What is the column definition in the copybook that contains 0x00 character?
• And what are options you pass to spark-cobol?

I ran a quick unit test and Cobrix automatically removes 0x00 from alphanumeric columns:

  "test" in {
    val copybook =
      """         01  R.
           05  FLD           PIC X(5).
    """

    val binFileContentsLengthField: Array[Byte] = Array[Byte](
      0x00.toByte, 0xC1.toByte, 0xF3.toByte, 0xF1.toByte, 0x00.toByte,
      0x00.toByte, 0xC2.toByte, 0xF3.toByte, 0xF2.toByte, 0x00.toByte,
      0x00.toByte, 0xC3.toByte, 0x00.toByte, 0xF3.toByte, 0x00.toByte
    )


    withTempBinFile("test", ".dat", binFileContentsLengthField) { tmpFileName =>
      val df = spark
        .read
        .format("cobol")
        .option("copybook_contents", copybook)
        .option("record_format", "F")
        .option("pedantic", "true")
        .load(tmpFileName)

      val df2 = df.withColumn("FLD_HEX", hex(df("FLD")))

      val actual = SparkUtils.convertDataFrameToPrettyJSON(df2)

      println(actual)
    }
  }

Produces:

[ {
  "FLD" : "A31",
  "FLD_HEX" : "413331"
}, {
  "FLD" : "B32",
  "FLD_HEX" : "423332"
}, {
  "FLD" : "C 3",
  "FLD_HEX" : "432033"
} ]

i.e. leading and trailing 0x00 were removed, and 0x00 in the middle of the field is replaced by space (0x20).

[pinakigit]

Hi @yruslan,

Thanks for checking. Yes, your explanation makes sense.

What I’m seeing is related to the EBCDIC code page used to generate the file.With CP037, X'00' (LOW-VALUES) is converted to spaces when interpreted as alphanumeric.But with CP037_Extended, the same bytes appear as \u0000 or other control characters (\u0000–\u001F) when Spark reads them.I tested with a file created using CP037_Extended, and the character fields contained several control characters in the 0x00–0x1F range. When loading this into PostgreSQL, the issue occurs specifically because PostgreSQL rejects the NULL byte:ERROR: invalid byte sequence for encoding "UTF8": 0x00

Do we know which EBCDIC characters would change in the output if we switch from CP037_Extended back to CP037? I’m trying to understand exactly which bytes differ between the two code pages and how Cobrix interprets them.

Hi @yruslan,
Thanks for checking. Yes, your explanation makes sense.
What I’m seeing is related to the EBCDIC code page used to generate the file.
With CP037, X'00' (LOW-VALUES) is converted to spaces when interpreted as alphanumeric.
But with CP037_Extended, the same bytes appear as \u0000 or other control characters (\u0000–\u001F) when Spark reads them.
I tested with a file created using CP037_Extended, and the character fields contained several control characters in the 0x00–0x1F range. When loading this into PostgreSQL, the issue occurs specifically because PostgreSQL rejects the NULL byte:
ERROR: invalid byte sequence for encoding "UTF8": 0x00

I’ll run more tests to confirm how other characters behave and what happens to x'FF' i.e. High Values and which chacraters apart from Low Values we are not able to load to Postgres.