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
IDENTITYOVERRID E. 10 4037
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
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
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
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
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
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
IDENTITYOVERRID E 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?
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 IDENTITYOVERRID E 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
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
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). This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dagoberto |
last post by:
can somebody tell me how can I get old data from a recently damaged table?
|
by: Raquel |
last post by:
On Linux, suppose a recoverable database has crashed due to a damaged
tablespace (the container on which the tablespace existed has gone
bad). On restarting the database, I understand that the tablespace
will be in roll forward pending state. Because the container has gone
bad, I cannot 'fix' the container. So, what are my options? Can I
restore the 'tablespace' to some other good container and then roll
forward to the end of logs? What is...
|
by: XXX |
last post by:
Hi,
I've got a problem with a damaged access 2000 database. When I try to repair
and compact the database, the process starts normaly, but then (after appr.
50 %) it hangs. Neither is it possible to import the database-objects to a
new database. Has someone got experience in solving this problem? I would
appreciate your help.
Thanks
|
by: maniac |
last post by:
Gang:
"There was an error opening this document. The file is damaged and could not
be repaired."
Has anybody had issue with not being able to open pdf in any browser using
..net application?
The pdf is fine if use Adobe to read it; or just a browser, but not when
combined with .net.
|
by: lauralucas |
last post by:
I'm starting to suspect something is damaged in my working environment.
(asp.net 1.1 and visual studio 2003)
for example: is this normal?
I add a label
I assign the label a value of "hello" in design time.
in codebehind, I change the value of the label.
private void Page_Load(object sender, System.EventArgs e)
{
| |
by: MrWriteLA |
last post by:
I've been trying to code a fairly uncomplicated program that will read some values from an Excel worksheet into Illustrator variables.
I tried VBA first, being careful to add the Illustrator Reference Library. I could swear that my first attempt (the usual "Hello World" thing from the Illustrator VB Reference Guide) was successful, but maybe I'm delusional. Anyway, I wish now I'd saved that code, because every attempt afterward generated a...
|
by: sara |
last post by:
Hi -
I have been struggling with solution ideas for this now for almost 2
weeks, and have not been able to figure this out.
I have a user who creates a Purchase Order (tblPOData). In some
circumstances, this or another user must create an invoice to go with
the PO (I know - that makes no sense, but this is the business
case).
I have the user go to a form to create the invoice:
|
by: olle |
last post by:
How to deal with a VBA-project that is damaged?
Hi everyone.
I am BigOlle from sweden and I have been working with Accees for ten
years
I am now working on a project that started in Access97 and since a few
months it continues in Access2000.
My plan is to use Access2000 as it is supposed that mdb-files in
Access 2000-format can be opened from all Access versions after that
|
by: hemantbasva |
last post by:
We have designed an aspx page having five ajax tab in it. the data of first four are designed on page whereas for the fifth tab it renders a user control named MYDOMAIN. in the tab container's even onactivetabindexchanged we have called a method loadtabpanel() which is defined in javascript in same page.the problem is it sometime give the message load tab panel undefined. this error does not come regularly. it comes in usercontrol rendering . i...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |