DSXchange

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.

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".

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.

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

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).

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