Load command on table, field timestamp primary key

[jay]

hi,
Question on Load/import command.
consider a sample table
create table table_name (
col1 timestamp not null default current timestamp,
col2 int,
col3 int,
col4 int,
primary key(col1) );
There is a file file.del containing data for col2,col3,col4. Data for
col1 is missing.

Is there a way I can use load/import command to get data into above
table from file? How would I insert timestamp values into col1?
Running DB2 Ver 7.2.0 on RedHat 8.0.

Any feedback is highly appreciated,
thanks
jay

[mecej4]

jay wrote:

hi,
Question on Load/import command.
consider a sample table
create table table_name (
col1 timestamp not null default current timestamp,
col2 int,
col3 int,
col4 int,
primary key(col1) );
There is a file file.del containing data for col2,col3,col4. Data for
col1 is missing.

Is there a way I can use load/import command to get data into above
table from file? How would I insert timestamp values into col1?
Running DB2 Ver 7.2.0 on RedHat 8.0.

Any feedback is highly appreciated,
thanks
jay

What you have described is inconsistent and will not work, unless you
want to and can do a s-l-o-w load, one that is slow enough that the
time stamp will change between loading one row and the next! If you
remove the requirement that col1 be a primary key, the load command
would be simply:

load from <filename> of del modified by coldel, insert into
table_name(col2,col3,col4);

To add time-stamps later, you would do an UPDATE.

Here is what I get with the primary key requirement removed (as you can
see, all four rows were loaded before the timestamp changed):

db2 => select * from table_name;

COL1 COL2 COL3 COL4
-------------------------- ----------- ----------- -----------
2005-02-19-10.48.22.275928 11 12 13
2005-02-19-10.48.22.275928 21 22 23
2005-02-19-10.48.22.275928 31 32 33
2005-02-19-10.48.22.275928 41 42 43

4 record(s) selected.

:- N. Shamsundar
:- University of Houston

[Serge Rielau]

mecej4 wrote:

jay wrote:

hi,
Question on Load/import command.
consider a sample table
create table table_name (
col1 timestamp not null default current timestamp,
col2 int,
col3 int,
col4 int,
primary key(col1) );
There is a file file.del containing data for col2,col3,col4. Data for
col1 is missing.

Is there a way I can use load/import command to get data into above
table from file? How would I insert timestamp values into col1?
Running DB2 Ver 7.2.0 on RedHat 8.0.

Any feedback is highly appreciated, thanks jay

What you have described is inconsistent and will not work, unless you
want to and can do a s-l-o-w load, one that is slow enough that the
time stamp will change between loading one row and the next! If you
remove the requirement that col1 be a primary key, the load command
would be simply:

load from <filename> of del modified by coldel, insert into
table_name(col2,col3,col4);

To add time-stamps later, you would do an UPDATE.

Here is what I get with the primary key requirement removed (as you can
see, all four rows were loaded before the timestamp changed):

db2 => select * from table_name;

COL1 COL2 COL3 COL4
-------------------------- ----------- ----------- -----------
2005-02-19-10.48.22.275928 11 12 13
2005-02-19-10.48.22.275928 21 22 23
2005-02-19-10.48.22.275928 31 32 33
2005-02-19-10.48.22.275928 41 42 43

4 record(s) selected.

:- N. Shamsundar
:- University of Houston

Here is a rather mean(!) trick:
CREATE FUNCTION bigliar() RETURNS TIMESTAMP
DETERMINISTIC -- evil laugh
NO EXTERNAL ACTION CONTAINS SQL
RETURN TIMESTAMP(generate_unique())
;
CREATE TABLE table_name (
col1 timestamp not null generated always as (bigliar()),
col2 int,
col3 int,
col4 int,
primary key(col1) );

You need DB2 UDB V8.2.
Prior to V8.2 you will need to use an external UDF.
After years of deliberation we concluded that customers have the right
to lie and DB2 has no business to prevent it.....

If you want to provide timestamps yourself you simply ALTER TABLE ...
ALTER COLUMN .. DROP EXPRESSION.
Adding an expression requires a detour through SET INTEGRITY OFF
and then SET INTEGRITY UNCHECKED IMMEIATE.

Oh.. and WHERE col1 = bigliar() would be a really bad idea.. In fact
don't mention bigliar() ever in any query...

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab