Is LOAD really this brain damaged?

[aj]

DB2 WSUE LUW v8.2 FP4 (aka v8.1 FP11)
RHEL AS 4

I am EXPORTing in IXF format from one schema and then LOADing into
another schema on another server.

The DB modeling tool I am using likes to put the PK columns at the
very top of the CREATE TABLE ddl, so the ordering of the columns in
the FROM schema is sometimes different from the ordering in the TO schema.

I have discovered that:
1. If the ordering of the columns is different, I *must* use the
METHOD N (colx,coly,colz) clause on the LOAD. All the column names
are the same - why can't LOAD figure it out?

2. If the ordering of the columns is the same I *must not* use the
METHOD N (colx,coly,colz) clause on the LOAD. If I do, I'll get:
SQL3086N There was no source column specified to be loaded into
database column "0" or the specified source column does not exist,
but the database column is not nullable.

So if I'm specific about the column ordering when I really don't need
to be, the LOAD won't work? I can't be TOO specific??

Am I missing something? <scratching head>

aj

PS - Before you ask, I am using LOAD rather than IMPORT because I need
IDENTITYOVERRIDE.

[Serge Rielau]

aj wrote:

DB2 WSUE LUW v8.2 FP4 (aka v8.1 FP11)
RHEL AS 4

I am EXPORTing in IXF format from one schema and then LOADing into
another schema on another server.

The DB modeling tool I am using likes to put the PK columns at the
very top of the CREATE TABLE ddl, so the ordering of the columns in
the FROM schema is sometimes different from the ordering in the TO schema.

I have discovered that:
1. If the ordering of the columns is different, I *must* use the
METHOD N (colx,coly,colz) clause on the LOAD. All the column names
are the same - why can't LOAD figure it out? I guess they want to be rather save than sorry...

2. If the ordering of the columns is the same I *must not* use the
METHOD N (colx,coly,colz) clause on the LOAD. If I do, I'll get:
SQL3086N There was no source column specified to be loaded into
database column "0" or the specified source column does not exist,
but the database column is not nullable.

It seems more like you're missing a column in your specification.
What is the signature of the target column and the source?

I think a complete repro scenario might be in order here.
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[aj]

Thanks for the reply, Serge.

....snip..

Serge Rielau wrote:

2. If the ordering of the columns is the same I *must not* use the
METHOD N (colx,coly,colz) clause on the LOAD. If I do, I'll get:
SQL3086N There was no source column specified to be loaded into
database column "0" or the specified source column does not exist,
but the database column is not nullable.

It seems more like you're missing a column in your specification.
What is the signature of the target column and the source?

I don't think so. If I simply remove the METHOD N (colx,coly,colz)
clause on the LOAD where the column ordering matches, it works
**perfectly**.

aj

[Serge Rielau]

aj wrote:

Thanks for the reply, Serge.

...snip..

Serge Rielau wrote:

2. If the ordering of the columns is the same I *must not* use the
METHOD N (colx,coly,colz) clause on the LOAD. If I do, I'll get:
SQL3086N There was no source column specified to be loaded into
database column "0" or the specified source column does not exist,
but the database column is not nullable.

It seems more like you're missing a column in your specification.
What is the signature of the target column and the source?

I don't think so. If I simply remove the METHOD N (colx,coly,colz)
clause on the LOAD where the column ordering matches, it works
**perfectly**.

aj

So, could it be you have 4 columns instead of 3?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[aj]

Serge Rielau wrote:

aj wrote:

Thanks for the reply, Serge.

...snip..

I don't think so. If I simply remove the METHOD N (colx,coly,colz)
clause on the LOAD where the column ordering matches, it works
**perfectly**.

aj

So, could it be you have 4 columns instead of 3?

No. The number, order, and names of the columns match exactly....

I was using (colx,coly,colz) just to be demonstrative. I actually
have as few as 2 columns in some tables and dozens in others..

aj

[Knut Stolze]

aj wrote:

DB2 WSUE LUW v8.2 FP4 (aka v8.1 FP11)
RHEL AS 4

I am EXPORTing in IXF format from one schema and then LOADing into
another schema on another server.

The DB modeling tool I am using likes to put the PK columns at the
very top of the CREATE TABLE ddl, so the ordering of the columns in
the FROM schema is sometimes different from the ordering in the TO schema.

I have discovered that:
1. If the ordering of the columns is different, I *must* use the
METHOD N (colx,coly,colz) clause on the LOAD. All the column names
are the same - why can't LOAD figure it out?
You are assuming that identical column names imply a match. I don't think
that you want to have this in the general case, do you? (At least it
should be controlled by _some_ option.)
2. If the ordering of the columns is the same I *must not* use the
METHOD N (colx,coly,colz) clause on the LOAD. If I do, I'll get:
SQL3086N There was no source column specified to be loaded into
database column "0" or the specified source column does not exist,
but the database column is not nullable.

So if I'm specific about the column ordering when I really don't need
to be, the LOAD won't work? I can't be TOO specific??

Works for me:

$ db2 "create table t1 ( a int, b int )"
$ db2 "insert into t1 values (1, 2)"
$ db2 "export to f of ixf select * from t1"
$ db2 "create table t2 like t1"
$ db2 "load from f of ixf insert into t2"

What is different in your scenario?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[aj]

I took your example and added
MODIFIED BY ANYORDER METHOD N (a,b)
and it worked. Mine didn't act like that.

<scratching head>???

I'm doing something like:
LOAD FROM "tbl.ixf" OF IXF MODIFIED BY ANYORDER
IDENTITYOVERRIDE METHOD N (a,b) MESSAGES "tbl.in"
REPLACE INTO tbl NONRECOVERABLE INDEXING MODE AUTOSELECT

The IXF file is coming from a table residing in an 8.1
remote database that has been catalogued on my 8.2 server.
I wonder if that makes a difference...

Something else is going on here... I apologize for
assuming right away that LOAD was stupid...

Do you see anything obvious?

TIA
aj


Knut Stolze wrote:

aj wrote:

DB2 WSUE LUW v8.2 FP4 (aka v8.1 FP11)
RHEL AS 4

I am EXPORTing in IXF format from one schema and then LOADing into
another schema on another server.

The DB modeling tool I am using likes to put the PK columns at the
very top of the CREATE TABLE ddl, so the ordering of the columns in
the FROM schema is sometimes different from the ordering in the TO schema.

I have discovered that:
1. If the ordering of the columns is different, I *must* use the
METHOD N (colx,coly,colz) clause on the LOAD. All the column names
are the same - why can't LOAD figure it out?

You are assuming that identical column names imply a match. I don't think
that you want to have this in the general case, do you? (At least it
should be controlled by _some_ option.)

2. If the ordering of the columns is the same I *must not* use the
METHOD N (colx,coly,colz) clause on the LOAD. If I do, I'll get:
SQL3086N There was no source column specified to be loaded into
database column "0" or the specified source column does not exist,
but the database column is not nullable.

So if I'm specific about the column ordering when I really don't need
to be, the LOAD won't work? I can't be TOO specific??

Works for me:

$ db2 "create table t1 ( a int, b int )"
$ db2 "insert into t1 values (1, 2)"
$ db2 "export to f of ixf select * from t1"
$ db2 "create table t2 like t1"
$ db2 "load from f of ixf insert into t2"

What is different in your scenario?

[Ian]

aj wrote:

I took your example and added
MODIFIED BY ANYORDER METHOD N (a,b)
and it worked. Mine didn't act like that.

<scratching head>???

I'm doing something like:
LOAD FROM "tbl.ixf" OF IXF MODIFIED BY ANYORDER
IDENTITYOVERRIDE METHOD N (a,b) MESSAGES "tbl.in"
REPLACE INTO tbl NONRECOVERABLE INDEXING MODE AUTOSELECT

The IXF file is coming from a table residing in an 8.1
remote database that has been catalogued on my 8.2 server.
I wonder if that makes a difference...

You posted a similar question but it sounded like the columns
in your source database were in a different order than in the
target database. Is this no longer the case?

i.e. server1: tbl(a, b, c, d)
server2: tbl(c, d, b, a)
Ian

[aj]

In this particular case thats correct. I am LOADing a bunch of
tables. My modeling tool likes to put PK at top of CREATE TABLE
DDL. In some cases I had already done this, in others I had not.
So in some cases columns are same order, in others not.

Ian wrote:

You posted a similar question but it sounded like the columns
in your source database were in a different order than in the
target database. Is this no longer the case?

i.e. server1: tbl(a, b, c, d)
server2: tbl(c, d, b, a)
Ian

[Ian]

aj wrote:

Ian wrote:

You posted a similar question but it sounded like the columns
in your source database were in a different order than in the
target database. Is this no longer the case?

i.e. server1: tbl(a, b, c, d)
server2: tbl(c, d, b, a)

In this particular case thats correct. I am LOADing a bunch of
tables. My modeling tool likes to put PK at top of CREATE TABLE
DDL. In some cases I had already done this, in others I had not.
So in some cases columns are same order, in others not.

Well, then that's exactly the problem. If the columns in your data file
do not match the order in your database, then this is expected.

Why would you expect different? Because the IXF file includes
some information about the table you extracted from?

LOAD is meant to write data into the database FAST, and the tax you
pay for its speed is that you are required to supply the brains.
Note that, unlike IMPORT, LOAD can't create tables (i.e. IMPORT ...
CREATE INTO table).

[aj]

Forgive me - I don't think I explained myself well enough.

I EXPORTed a group of IXF files from an 8.1 DB, and then
tried to LOAD these IXF into empty tables in an
8.2 DB. In some cases, the column ordering was a bit
different, but I always had the same # of columns and
same names.

I though that, even tho the ordering was sometimes different,
if I had the same # of columns and same names, LOAD would be
able to figure things out. I was incorrect-it won't do that.
and thats fine.

So I went thru my script, and for *every* LOAD added a METHOD N
(col,col,col,..), specifying the order of the columns in the
empty tables I was LOADing into.

When I ran my script, LOAD worked for those empty tables that
had a different column ordering than the source tables.
LOAD *did not work* for those tables that had the same column
ordering as the source tables.

Just out of sheer frustration, I went back thru my script and
removed the METHOD N (col,col,..) for those tables that had the
same column ordering. I reran the script, and LOADs that failed
before worked fine.

So it looked to me like, if the column ordering was exactly the
same, you could not use METHOD N. And if the column ordering was
different, you must use METHOD N. Perhaps I am incorrect.

I am backtracking today and will figure out what is causing this.
I appreciate your assistance.

aj

Ian wrote:

aj wrote:

Ian wrote:

You posted a similar question but it sounded like the columns
in your source database were in a different order than in the
target database. Is this no longer the case?

i.e. server1: tbl(a, b, c, d)
server2: tbl(c, d, b, a)

> In this particular case thats correct. I am LOADing a bunch of
> tables. My modeling tool likes to put PK at top of CREATE TABLE
> DDL. In some cases I had already done this, in others I had not.
> So in some cases columns are same order, in others not.

Well, then that's exactly the problem. If the columns in your data file
do not match the order in your database, then this is expected.

Why would you expect different? Because the IXF file includes
some information about the table you extracted from?

LOAD is meant to write data into the database FAST, and the tax you
pay for its speed is that you are required to supply the brains.
Note that, unlike IMPORT, LOAD can't create tables (i.e. IMPORT ...
CREATE INTO table).