By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 1,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

export to pipe

P: n/a
I am attempting to move data between 2 DB2 Linux databases using a
pipe. I can't seem to get it working. Any assistance is appreciated.

I first issue:

mkfifo -m777 mypipe
db2 "connect to src_db"
db2 "export to mypipe of DEL MODIFIED BY COLDEL, MESSAGES exp.msg
select * from myschema.old_table"

Followed by:

db2 "connect to target_db"
db2 "import from mypipe of DEL MODIFIED BY COLDEL, MESSAGES imp.msg
replace into myschema.new_tbl"

The pipe gets created with proper permissions. I can issue a unix
"cat" command and the data comes back to the screen nice. The export
even completes successful when I issue the "cat".

However, When I issue the import commadn, I get the following on the
export side "

SQL3001C An I/O error (reason = "sqlofopn -2029060074") occurred while

opening the output file.

If I set up my test using an actual file instead of a pipe, it works
fine as well.

Any ideas are appreciated. Thanks.

Dec 29 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Ian
mike_dba wrote:
I am attempting to move data between 2 DB2 Linux databases using a
pipe. I can't seem to get it working. Any assistance is appreciated.

I first issue:

mkfifo -m777 mypipe
db2 "connect to src_db"
db2 "export to mypipe of DEL MODIFIED BY COLDEL, MESSAGES exp.msg
select * from myschema.old_table"

Followed by:

db2 "connect to target_db"
db2 "import from mypipe of DEL MODIFIED BY COLDEL, MESSAGES imp.msg
replace into myschema.new_tbl"

The pipe gets created with proper permissions. I can issue a unix
"cat" command and the data comes back to the screen nice. The export
even completes successful when I issue the "cat".

However, When I issue the import commadn, I get the following on the
export side "

SQL3001C An I/O error (reason = "sqlofopn -2029060074") occurred while

opening the output file.

If I set up my test using an actual file instead of a pipe, it works
fine as well.

Any ideas are appreciated. Thanks.
The error occurs because of how DB2 opens the file (pipe) for the
import utility. You can do:

mkfifo exportpipe
mkfifo importpipe

db2 "export to exportpipe ... "
cat < exportpipe importpipe &
db2 "import from importpipe ..."

Note, you'll have to execute the import and export from separate shells
because of the architecture of the DB2 CLP.

Exporting/importing through pipes is a kludge, however.

If you're moving a small amount of data (and I expect you are, if you
are using IMPORT), just export to a file and then import from the file.

If you're moving a large amount of data, try setting up a nickname in
the target database that points to your source database and then use the
LOAD FROM CURSOR functionality. Much better.

Dec 29 '06 #2

P: n/a
Ian,

Thanks for the reply. I suspected something on the open as you point
out. I think I will try the latter proposal of Federating the two
databases.

Much appreciated !

Ian wrote:
mike_dba wrote:
I am attempting to move data between 2 DB2 Linux databases using a
pipe. I can't seem to get it working. Any assistance is appreciated.

I first issue:

mkfifo -m777 mypipe
db2 "connect to src_db"
db2 "export to mypipe of DEL MODIFIED BY COLDEL, MESSAGES exp.msg
select * from myschema.old_table"

Followed by:

db2 "connect to target_db"
db2 "import from mypipe of DEL MODIFIED BY COLDEL, MESSAGES imp.msg
replace into myschema.new_tbl"

The pipe gets created with proper permissions. I can issue a unix
"cat" command and the data comes back to the screen nice. The export
even completes successful when I issue the "cat".

However, When I issue the import commadn, I get the following on the
export side "

SQL3001C An I/O error (reason = "sqlofopn -2029060074") occurred while

opening the output file.

If I set up my test using an actual file instead of a pipe, it works
fine as well.

Any ideas are appreciated. Thanks.

The error occurs because of how DB2 opens the file (pipe) for the
import utility. You can do:

mkfifo exportpipe
mkfifo importpipe

db2 "export to exportpipe ... "
cat < exportpipe importpipe &
db2 "import from importpipe ..."

Note, you'll have to execute the import and export from separate shells
because of the architecture of the DB2 CLP.

Exporting/importing through pipes is a kludge, however.

If you're moving a small amount of data (and I expect you are, if you
are using IMPORT), just export to a file and then import from the file.

If you're moving a large amount of data, try setting up a nickname in
the target database that points to your source database and then use the
LOAD FROM CURSOR functionality. Much better.
Jan 2 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.