DB2/400 to DB2 for Linux 
Author Message
 DB2/400 to DB2 for Linux

Dear All,

We want to move our DB2 UDB from AS/400 (V4R?) to Linux
(V7.1). The problem is how can we move the data from our
DB2/400 to DB2 on Linux. can we do "INSERT INTO xxx SELECT *
FROM yyy" where xxx and yyy are two tables with same
structure but with different data sources (AS/400 and
Linux)?

I've tried exporting the data in AS/400 to text files of
delimited format, and importing/loading the data to the DB2
on Linux, but some data could not be imported/loaded
successfully.

For example, a record has 2 fields. The first one is numeric
while the second one is varchar. But the main point is that
the second field can contain any usual ASCII character like
", ', _, (, ), %, etc, and can contain newline character.
Thus, using any character as the string delimiter does not
guarantee the string will not contain that delimiter
character itself. Below is an example of two records in a
text file to be loaded into a DB2 table:

*** RECORD 1 ***
100, "Line 1
Line 2
Line 3"
*** RECORD 2 ***
101, "Added ""something"" to the system"

I use double quotation " as the delimiter in this example.
The string in record 1 contains newline characters, record 2
contains double quotation mark. If I use default setting in
MODIFIED BY in the LOAD command, both records could not be
loaded into the table. After I added DELPRIORITYCHAR, record
1 could be loaded. But I still cannot load record 2
successfully after I tried for a few times, even when I set
NODOUBLEDEL (actually I don't fully understand what this
option means). So how to solve this problem? Thanks a lot
for help.

Regards,
Eric



Sat, 22 Nov 2003 01:40:57 GMT
 DB2/400 to DB2 for Linux

Are you using DB2 Connect for Linux to do the export?
Quote:

> Dear All,

> We want to move our DB2 UDB from AS/400 (V4R?) to Linux
> (V7.1). The problem is how can we move the data from our
> DB2/400 to DB2 on Linux. can we do "INSERT INTO xxx SELECT *
> FROM yyy" where xxx and yyy are two tables with same
> structure but with different data sources (AS/400 and
> Linux)?

> I've tried exporting the data in AS/400 to text files of
> delimited format, and importing/loading the data to the DB2
> on Linux, but some data could not be imported/loaded
> successfully.

> For example, a record has 2 fields. The first one is numeric
> while the second one is varchar. But the main point is that
> the second field can contain any usual ASCII character like
> ", ', _, (, ), %, etc, and can contain newline character.
> Thus, using any character as the string delimiter does not
> guarantee the string will not contain that delimiter
> character itself. Below is an example of two records in a
> text file to be loaded into a DB2 table:

> *** RECORD 1 ***
> 100, "Line 1
> Line 2
> Line 3"
> *** RECORD 2 ***
> 101, "Added ""something"" to the system"

> I use double quotation " as the delimiter in this example.
> The string in record 1 contains newline characters, record 2
> contains double quotation mark. If I use default setting in
> MODIFIED BY in the LOAD command, both records could not be
> loaded into the table. After I added DELPRIORITYCHAR, record
> 1 could be loaded. But I still cannot load record 2
> successfully after I tried for a few times, even when I set
> NODOUBLEDEL (actually I don't fully understand what this
> option means). So how to solve this problem? Thanks a lot
> for help.

> Regards,
> Eric



Sat, 22 Nov 2003 13:12:30 GMT
 DB2/400 to DB2 for Linux

One character that I've never had a problem with is the ASCII Tab
character.  You can generate that on the AS/400 by specifying X'05' as
the value for the delimiter parameter on the CPYTOIMPF.  

Use RCDDLM(*CRLF) DTAFMT(*DLM) STRDLM(*NONE) FLDDLM(X'05')

and specify tab delimiters on the DB2 import command on the Linux box.

Barry

Quote:

> Dear All,

> We want to move our DB2 UDB from AS/400 (V4R?) to Linux
> (V7.1). The problem is how can we move the data from our
> DB2/400 to DB2 on Linux. can we do "INSERT INTO xxx SELECT *
> FROM yyy" where xxx and yyy are two tables with same
> structure but with different data sources (AS/400 and
> Linux)?

> I've tried exporting the data in AS/400 to text files of
> delimited format, and importing/loading the data to the DB2
> on Linux, but some data could not be imported/loaded
> successfully.

> For example, a record has 2 fields. The first one is numeric
> while the second one is varchar. But the main point is that
> the second field can contain any usual ASCII character like
> ", ', _, (, ), %, etc, and can contain newline character.
> Thus, using any character as the string delimiter does not
> guarantee the string will not contain that delimiter
> character itself. Below is an example of two records in a
> text file to be loaded into a DB2 table:

> *** RECORD 1 ***
> 100, "Line 1
> Line 2
> Line 3"
> *** RECORD 2 ***
> 101, "Added ""something"" to the system"

> I use double quotation " as the delimiter in this example.
> The string in record 1 contains newline characters, record 2
> contains double quotation mark. If I use default setting in
> MODIFIED BY in the LOAD command, both records could not be
> loaded into the table. After I added DELPRIORITYCHAR, record
> 1 could be loaded. But I still cannot load record 2
> successfully after I tried for a few times, even when I set
> NODOUBLEDEL (actually I don't fully understand what this
> option means). So how to solve this problem? Thanks a lot
> for help.

> Regards,
> Eric



Sun, 23 Nov 2003 02:54:03 GMT
 DB2/400 to DB2 for Linux

Quote:
> Are you using DB2 Connect for Linux to do the export?

I use IBM Client Access's "Data Transfer from AS400" to export.

Regards,
Eric



Sun, 23 Nov 2003 10:05:26 GMT
 DB2/400 to DB2 for Linux

Quote:
> One character that I've never had a problem with is the ASCII Tab
> character.  You can generate that on the AS/400 by specifying X'05' as
> the value for the delimiter parameter on the CPYTOIMPF.  
> Use RCDDLM(*CRLF) DTAFMT(*DLM) STRDLM(*NONE) FLDDLM(X'05')
> and specify tab delimiters on the DB2 import command on the Linux box.

Thanks, but could you tell me how to specify tab delimiters
in the import/load command? It seems that I could change it
to some punctuations but not tab character.

Regards,
Eric



Sun, 23 Nov 2003 10:11:48 GMT
 DB2/400 to DB2 for Linux

Quote:


> > One character that I've never had a problem with is the ASCII Tab
> > character.  You can generate that on the AS/400 by specifying X'05' as
> > the value for the delimiter parameter on the CPYTOIMPF.

> > Use RCDDLM(*CRLF) DTAFMT(*DLM) STRDLM(*NONE) FLDDLM(X'05')

> > and specify tab delimiters on the DB2 import command on the Linux box.

> Thanks, but could you tell me how to specify tab delimiters
> in the import/load command? It seems that I could change it
> to some punctuations but not tab character.

> Regards,
> Eric

I don't have DB2 loaded right now but I assume that if you use '\t' as
the character you'll get what you're after.

Barry



Tue, 25 Nov 2003 03:25:31 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. DB2 on linux vs. DB2 on AS/400

2. Adding db2(AS/400) to cc on db2(linux)

3. DBD::DB2-Linux-client connect to AS/400-DB2-database

4. desire to move data from OS 390 DB2 to AS/400 DB2 using DTS

5. DB2 Newbie: Connecting to AS/400 DB2 with JDBC problems

6. db2/400 vs. db2/Solaris

7. DB2 UDB 7.2 and DB2 UDB for AS/400 V5R1

8. Difference between DB2 UDB 7.2 and DB2/400

9. Random Sample in DB2 and DB2/400?

10. Accessing DB2 on AS/400 by DB2-Connect

11. DB2 Connect and DB2 OS/400

12. DB2/400 and DB2 Connect


 
Powered by phpBB® Forum Software