export to pipe

[mike_dba]

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.

[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.

[mike_dba]

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.