469,578 Members | 1,512 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,578 developers. It's quick & easy.

export to pipe

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
2 15148
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
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.

Similar topics

1 post views Thread by Alex | last post: by
1 post views Thread by mark.heyden | last post: by
3 posts views Thread by chudson007 | last post: by
6 posts views Thread by jefftyzzer | last post: by
2 posts views Thread by dogman_2000 | last post: by
1 post views Thread by db2admin | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.