Importing null tmsp from a var-length delimited file

[bcarlson]

How do I import a nullable timestamp field?

For the sake of conversation, assume the record is an integer, timestamp, and a char:

12345||"Joe Smith"

So, the timestamp field we recieve from our source is either the full tiemstamp (19 bytes) or an empty set. I tried setting the null_value to '', but got the error message (warning msg, then fatal msg):

W - besttransaction: At field "NULL_TMSP_FLD": "null_field" length (0) must match field's fixed width (19) [impexp/group_comp.C:4453]
F - besttransaction: At field "NULL_TMSP_FLD": A fixed length field cannot have a "null_field" of length 0 on import [impexp/group_comp.C:4469]

I am sure this is a very rudimentary question, but we do so little with normal pipe delimited ASCII data from our sources. About 90% of our 100+ sources are EBCDIC from the mainframe, so I'm rusty on your average Unix data file...

Thanks!

Brad.

[chulett]

Your problem seems to be you've said this is a fixed-width file when in fact it is not. Hence the need to always find 19 bytes in the timestamp field, even when "empty".

[ray.wurlod]

If you have no control over the structure, specify the data type as VarChar and convert the type within your job (if necessary). Only VarChar can legitimately have a Null Field Value of "" in a parallel job.

[chulett]

So, it is the data type of "timestamp" that makes the field need to be a fixed width?

[ray.wurlod]

Seems to be. In any case, "" is not valid as a timestamp. The Sequential File stage requires in-band nulls (that is, a substitute value that is valid for the data type).

[bcarlson]

Hmmm, so I could declare it a varchar(19) - since the timestamp would be 19 bytes - and use a null field length of '' to catch when it should be null, then convert with a modify.

Sounds like a plan. Thanks!

Brad