DB2 LUW: Strange Behavior on Insert/Import/Select of Date Values

Brian Stewart

DB2 LUW: Strange Behavior on Insert/Import/Select of Date Values
Hello -

I'm experiencing strange behavior in DB2 8.2 running on Linux, and
would appreciate any input as to what is going on here. I have cut and
pasted the following from my terminal to show what is happening in a
simple scenario condensed from a problem a developer is having.

Consider the following:

create table test (
c1 date not null default current date,
c2 date default current date);

The contents of test.csv are two commas:
,,

db2 import from test.csv of del insert into test

SQL3109N The utility is beginning to load data from file "test.csv".

SQL3116W The field value in row "1" and column "1" is missing, but the target
column is not nullable.

SQL3110N The utility has completed processing. "1" rows were read from the
input file.

SQL3221W ...Begin COMMIT WORK. Input Record Count = "1".

SQL3222W ...COMMIT of any database changes was successful.

SQL3149N "1" rows were processed from the input file. "0" rows were
successfully inserted into the table. "1" rows were rejected.

-----------------------

Why allow a DEFAULT clause with a NOT NULL clause if the input cannot be NULL?

-----------------------

Let's try switching things around a bit - the definition of table TEST is now:

create table test (
c1 date default current date not null,
c2 date default current date);

The test.csv file remains unchanged

db2 import from test.csv of del insert into test

SQL3109N The utility is beginning to load data from file "test.csv".

SQL3116W The field value in row "1" and column "1" is missing, but the tar
column is not nullable.

SQL3110N The utility has completed processing. "1" rows were read from th
input file.

SQL3221W ...Begin COMMIT WORK. Input Record Count = "1".

SQL3222W ...COMMIT of any database changes was successful.

SQL3149N "1" rows were processed from the input file. "0" rows were
successfully inserted into the table. "1" rows were rejected.

Number of rows read = 1
Number of rows skipped = 0
Number of rows inserted = 0
Number of rows updated = 0
Number of rows rejected = 1
Number of rows committed = 1

-------------------------------------------------------------------

Just for fun....

The definition of table TEST is now:

create table test (
c1 date default current date,
c2 date default current date);

The test.csv file remains unchanged

db2 import from test.csv of del insert into test

SQL3109N The utility is beginning to load data from file "test.csv".

SQL3110N The utility has completed processing. "1" rows were read from the
input file.

SQL3221W ...Begin COMMIT WORK. Input Record Count = "1".

SQL3222W ...COMMIT of any database changes was successful.

SQL3149N "1" rows were processed from the input file. "1" rows were
successfully inserted into the table. "0" rows were rejected.


Number of rows read = 1
Number of rows skipped = 0
Number of rows inserted = 1
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 1

db2 "select * from test"

C1 C2
---------- ----------
- -

1 record(s) selected.

The fields are null, even though I specified a default of CURRENT DATE

db2 "select current date from sysibm.sysdummy1"

1
----------
01/25/2007

1 record(s) selected.

-----------------------------------------------

It would be nice to be able to have null values in a csv import file
and have DB2 insert the current date by default, but that capability
is apparently absent - even when I define the field as NOT NULL WITH
DEFAULT.

What am I missing here?

Much thanks in advance ...

Brian

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Rodney Krick

DB2 LUW: Strange Behavior on Insert/Import/Select of Date Values
(in response to Brian Stewart)


Brian,
from the Utilities Reference (DB2 Information Center):
<zitat>
Table 127. Valid file type modifiers for the import utility: All file
formats
(...)
usedefaults
If a source column for a target table column has been specified, but it
contains no data for one or more row instances, default values are loaded.
Examples of missing data are:
For DEL files: two adjacent column delimiters (",,") or two adjacent column
delimiters separated by an arbitrary number of spaces (", ,") are specified
for a column value.
For DEL/ASC/WSF files: A row that does not have enough columns, or is not
long enough for the original specification.
Note:
For ASC files, NULL column values are not considered explicitly missing,
and a default will not be substituted for NULL column values. NULL column
values are represented by all space characters for numeric, date, time, and
/timestamp columns, or by using the NULL INDICATOR for a column of any type
to indicate the column is NULL.
Without this option, if a source column contains no data for a row
instance, one of the following occurs:
For DEL/ASC/WSF files: If the column is nullable, a NULL is loaded. If the
column is not nullable, the utility rejects the row.
</zitat>
I don't have a DB2 LUW right now here, so I cannot try it out. Maybe it
could work with the following command:
db2 import from test.csv of del MODIFIED BY usedefaults insert into test
I think it also answers your question "Why allow a DEFAULT clause with a
NOT NULL clause if the input cannot be NULL?".
HTH.
Mit freundlichen Grüßen / Best regards,
Rodney Krick
Senior Consultant
____________________________
aformatik Training & Consulting GmbH & Co. KG
Tilsiter Str. 8, D-71065 Sindelfingen, Germany
Tel.: +49 (0)7031-23 807-0
Fax: +49 (0)7031-23 807-19
mailto:[login to unmask email]
___________________________________________
ECIS, Education Center for IBM Software, Websphere and DB2

Mit freundlichen Grüßen / Best regards,
Rodney Krick
Senior Consultant
____________________________
aformatik Training & Consulting GmbH & Co. KG
Tilsiter Str. 8, D-71065 Sindelfingen, Germany
Tel.: +49 (0)7031-23 807-0
Fax: +49 (0)7031-23 807-19
mailto:[login to unmask email]
___________________________________________
ECIS, Education Center for IBM Software, Websphere and DB2
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ian Bjorhovde

Re: DB2 LUW: Strange Behavior on Insert/Import/Select of Date Values
(in response to Rodney Krick)
On 1/25/07, Brian Stewart <[login to unmask email]> wrote:
>
> Why allow a DEFAULT clause with a NOT NULL clause if the input cannot be
> NULL?


The key to this is understanding how import works. The import command
generates a statement that, in your case, looks like this:

INSERT INTO TEST ("C1", "C2") VALUES (CAST(? AS DATE), CAST(? AS DATE))

So, import is doing:

INSERT INTO TEST ("C1", "C2") VALUES (CAST(NULL AS DATE), CAST(NULL AS
DATE))

If you want to use the default value for a column, you can't use NULL. You
have to use the DEFAULT keyword, or not specify the column:

INSERT INTO TEST ("C1", "C2") VALUES (CAST(DEFAULT AS DATE), CAST(NULL
AS DATE))

or

INSERT INTO TEST ("C2") VALUES (CAST(NULL AS DATE))


It would be nice to be able to have null values in a csv import file
> and have DB2 insert the current date by default, but that capability
> is apparently absent - even when I define the field as NOT NULL WITH
> DEFAULT.


As someone else suggested, using the 'modified by usedefaults' will do what
you're looking for. This requires 8.2 Fixpack 9.


Good luck,


Ian Bjorhovde

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm