OLD_COPY_DN gotcha (or rather, got my customer)

[Roy Hann]

Anyone who still hangs out here will know I was doing an 11.2 upgrade
for a customer over the weekend.

It all went smoothly, except one job that had made it through UAT but
started failing when they went live. The job used COPY...FROM to load
data from flat files. The format specification used the "d" (dummy)
specification to skip junk.

It has come to light that the behaviour of the dummy specification
changed in 11.1. You can read about it by looking up SET SESSION
OLD_COPY_DN in the SQL Guide. As far as I can tell you can't read about
it anywhere else...like say, in the Installation Guide.

Roy

[G Jones]

It's in the 'patchbugs' doc, though surprisingly not marked with "attention".
On reading that bug description again the last bit worries me.
"Please note that using d0 and text(0) continues to be unsafe. They produces undefined behaviour".
Hmm. We use 'text(0)' quite frequently.

[Colin Hay]

We use d0 quite frequently too - whats the alternative ?

[Roy Hann]

SET SESSION OLD_COPY_DN;

Or as Paul Mason points out, you can configure it globally in CBF. You
may need to first do:

iiinitres old_copy_dn

Roy

[Paul White]

I found these entry in Patch Bugs

First mentioned in P15477

Bug 135261 II-4611 (ATTENTION, GENERIC)
Add a CBF parameter 'old_copy_dn' which reverts the default behaviour of a copy dummy format to the behaviour prior to the fix for bug 134443.
Previously a format of "col1 = Dn" would skip over n characters when doing a COPY FROM. However a COPY INTO would write the column name ("col1") n times. The fix for 134443 made these the same so that COPY FROM now skips n * length of column name (n * 4 in this example).
After applying this patch the new parameter is available. To add it to an existing installation first create it with the following command:

iiinitres old_copy_dn

This will add it with its default value of OFF. To enable the old behaviour set it to ON in CBF.
This behaviour can also be changed at the session level using:
SET SESSION [NO]OLD_COPY_DN



And from P15682
Bug 138448 II-7901 (GENERIC)

COPY column formats d0 and text(0) must have a delimiter defined to be able to be safely unloaded and reloaded. For backwards compatibility the old_copy_dn config paramter and the "SET SESSION OLD_COPY_DN" statements were added. With the old behaviour selected some COPY statements still fail with "E_US16F0 COPY: Column 'nl', format 'd0' requires a delimiter." Please note that using d0 and text(0) continues to be unsafe. They produces undefined behaviour and reloads are likely to fail.