Thanks everyone who tried to help - all your suggestions will prove
extremely valuable for my store of knowledge.
The solution was actually badly documented - but documented it was - I
needed to use the -aw switch in the db2move parameters, so that it would
create my complete .lst file - otherwise, despite of creating an ixf
file for the tables with warnings, it does NOT add them to the .lst
file, which means they cannot be used in IMPORT or LOAD.
Thanks.
Aurora
-----Original Message-----
From: Bauer Maureen [mailto:[Email address protected]
Sent: 15 November 2007 13:24
To: Dell'Anno,Aurora Emanuela,BUSSIGNY,Contractor for NBS IS/IT
Subject: RE: Problem with db2move and VARCHAR columns
Good Morning,
Try generating your exports and imports with the following :
There are some rules when dealing with long/lob data, you need to use
ixf files (especially for LOB data), you need to have enough .LOBS files
on the export (rule used to be 1 for every 999 records that may have
been relaxed), and you need to have enough room in your unix directory.
Good luck!
--;
select 'Import from C:\Files\'||name||'.ixf of IXF modified by
COMPOUND=5 COMMITCOUNT 1000 MESSAGES C:\Files\'||name||'.log'||' replace
into '||RTRIM(creator)||'.'||name||';'
from sysibm.systables
where name not in
(select distinct tbname
from sysibm.syscolumns
where coltype in ('BLOB','CLOB','LONGVAR')) and creator =
'YOURSCHEMA'
ORDER BY 1;
--import for lob data;
select 'IMPORT FROM C:\Files\'||name||'.ixf OF IXF LOBS FROM C:\Files\
MODIFIED BY COMPOUND=5 NOCHECKLENGTHS COMMITCOUNT 1000 MESSAGES
C:\Files\'||name||'.log replace INTO '||RTRIM(CREATOR)||'.'||name||';'
from sysibm.systables
where name in
(select distinct tbname
from sysibm.syscolumns
where coltype in ('BLOB','CLOB','LONGVAR')) and creator =
'YOURSCHEMA'
order by 1;
--generate export commands;
--;
select 'EXPORT TO C:\Files\'||name||'.ixf OF IXF MESSAGES
C:\Files\'||name||'.log SELECT * FROM '||RTRIM(CREATOR)||'.'||name||';'
from sysibm.systables
where name not in
(select distinct tbname
from sysibm.syscolumns
where coltype in ('BLOB','CLOB','LONGVAR')) and creator =
'YOURSCHEMA'
ORDER BY 1;
--export for LOB data;
SELECT 'EXPORT TO C:\Files\'||name||'.ixf OF IXF LOBS TO C:\Files\'||'
LOBFILE '||name||'.1.Lobs1, '||name||'.2.Lobs1, '||name||'.3.Lobs1,
'||name||'.4.Lobs1, '||name||'.5.Lobs1, '||name||'.6.Lobs1,
'||name||'.7.Lobs1, '||name||'.8.Lobs1, '||name||'.9.Lobs1,
'||name||'.10.Lobs1 MODIFIED BY LOBSINFILE MESSAGES
C:\Files\'||name||'.log SELECT * FROM '||RTRIM(CREATOR)||'.'||name||';'
from sysibm.systables
where name in
(select distinct tbname
from sysibm.syscolumns
where coltype in ('BLOB','CLOB','LONGVAR')) and creator =
'YOURSCHEMA'
ORDER BY 1;
-----Original Message-----
From: Dell Anno Aurora Emanuela BUSSIGNY Contractor for NBS IS/IT [Email
address protected]
Sent: Wednesday, November 14, 2007 7:59 AM
To: LazyDBA Discussion
Subject: RE: Problem with db2move and VARCHAR columns
Alex:
I have deleted the whole directory and it simply repeats the same
messages for EACH table :-P AT least this time specifying the lobpath it
has given me all 365 tables instead of 111.
The definition of these problem columns is LONG VARCHAR :-s
Thorsten:
No I am only trying the EXPORT for now, I have not even got near the
LOAD or IMPORT phase
Ed:
I am going to forward this to the vendor and let them see if the data
really is truncated - they insist it always is but I have a feeling they
stop at looking at the messages...
I'll obviously keep y'all posted.
Thanks all, fingers crossed.
-----Original Message-----
From: Alex Levy
[mailto:db2udbdba-ezmlmshield-x9300870.[Email address protected]
Sent: 14 November 2007 13:53
To: LazyDBA Discussion
Subject: RE: Problem with db2move and VARCHAR columns
I see these messages repeat (i.e. are appended at each run). Does the
timestamp on the message file correspond to your last run of db2move,
i.e.
are we quite sure these messages are not historic? Would you try
deleting the message files and retrying?
If this fails, what are the column definitions of ARITHOPVALUEX and
LOGICALOPTEXTX?
-----Original Message-----
From: Dell Anno Aurora Emanuela BUSSIGNY Contractor for NBS IS/IT
[mailto:db2udbdba-ezmlmshield-x74659279.[Email address protected]
Sent: 14 November 2007 12:37
To: LazyDBA Discussion
Subject: RE: Problem with db2move and VARCHAR columns
Nope I'm afraid nothing quite so harmless.
The message from one of them reads:
$ more tab342.msg
SQL3104N The Export utility is beginning to export data to file
"tab342.ixf".
SQL3132W The character data in column "ARITHOPVALUEX" will be truncated
to size "255".
SQL3132W The character data in column "LOGICALOPTEXTX" will be
truncated to size "255".
SQL3105N The Export utility has finished exporting "15" rows.
SQL3104N The Export utility is beginning to export data to file
"tab342.ixf".
SQL3132W The character data in column "ARITHOPVALUEX" will be truncated
to size "255".
SQL3132W The character data in column "LOGICALOPTEXTX" will be
truncated to size "255".
SQL3105N The Export utility has finished exporting "15" rows.
tab342.msg: END
-----Original Message-----
From: Alex Levy
[mailto:db2udbdba-ezmlmshield-x63814532.[Email address protected]
Sent: 14 November 2007 13:32
To: LazyDBA Discussion
Subject: RE: Problem with db2move and VARCHAR columns
OK, and what are the warnings in the message files tab2.msg and
tab5.msg?
My guess is they're harmless sql3100w messages.
-----Original Message-----
From: Dell Anno Aurora Emanuela BUSSIGNY Contractor for NBS IS/IT
[mailto:db2udbdba-ezmlmshield-x21818007.[Email address protected]
Sent: 14 November 2007 12:21
To: LazyDBA Discussion
Subject: RE: Problem with db2move and VARCHAR columns
Hi Alex,
I'll take your word for loving AIX, meantime, this does NOT seem to
change things - have a look at the output I get:
$ db2move EDIDCE07 export -l /db2/backup/db2move_20071114/lobdata_move
***** DB2MOVE *****
Action: EXPORT
Start time: Wed Nov 14 12:19:09 2007
Connecting to database EDIDCE07 ... successful! Server: DB2 Common
Server
V8.2.7
EXPORT: 28 rows from table "TXDSD2 "."PARSEEDIACTIONS"
*** Table "TXDSD2 "."PARTIES": ERROR 3107. Check message file
tab2.msg!
*** SQL Warning! SQLCODE is 3107
*** SQL3107W There is at least one warning message in the message
file.
EXPORT: 66 rows from table "TXDSD2 "."PARTYBUSINESSPROTOCOLS"
EXPORT: 0 rows from table "TXDSD2 "."PARTYCATEGORIES"
*** Table "TXDSD2 "."PARTYCERTIFICATEIDS": ERROR 3107. Check message
file tab5.msg!
*** SQL Warning! SQLCODE is 3107
*** SQL3107W There is at least one warning message in the message
file.
And so on and so forth.
Boo.
Any other ideas or suggestions please?
Thanks.
Aurora
-----Original Message-----
From: Alex Levy
[mailto:db2udbdba-ezmlmshield-x94895898.[Email address protected]
Sent: 14 November 2007 13:13
To: LazyDBA Discussion
Subject: RE: Problem with db2move and VARCHAR columns
Hello Aurora
Well we feel your pain! But you're just going to love UDB.
It looks like the issue here is that you have LONG VARCHARs that are
getting truncated. All long data, not just LOBs, needs to be exported to
a LOB file.
(And remember this makes your life easier too, as there is a display
length limit on a *NIX line).
Please try db2move dbname export -l lobpath and advise how you get on.
You can specify a comma separated list of lobpaths if you prefer.
I'm sorry, I never use db2move as it's a bit old fashioned, but hope the
above will work for you.
Regards
Alex Levy
-----Original Message-----
From: Dell Anno Aurora Emanuela BUSSIGNY Contractor for NBS IS/IT
[mailto:db2udbdba-ezmlmshield-x21442060.[Email address protected]
Sent: 14 November 2007 11:17
To: LazyDBA Discussion
Subject: RE: Problem with db2move and VARCHAR columns
Hi Ed,
DB21085I Instance "db2insd8" uses "32" bits and DB2 code release
"SQL08027"
with level identifier "03080106".
Informational tokens are "DB2 v8.1.1.128", "s061108", "U810098", and
FixPak "14".
Thanks.
Aurora
DB2 & UDB email list listserv db2-l LazyDBA home page