On 23 Oct 2012 17:44, Troy Hyde wrote:
I have recently been approached by someone in my shop about their
inability to transfer (using FTP) SQL tables that contain LOB rows
(BLOB in this case).
We receive error 426-Unable to open or create target file
Followed by 426 Data transfer ended
Using a PUT, the joblog of the client requester likely logs an error
describing the problem. However per the above errors, I presume that a
GET is being used instead.? If so...
http://archive.midrange.com/midrange-l/201210/msg00878.html
"Best to review the joblog for the error that precipitates the FTP426 to
see what the OS Data Management says is the problem origin; i.e. why the
Write to the file was prohibited. <<SNIP>> I typically issue the
following FTP requests to get that joblog, after which I use WRKSPLF at
the server to find the output:
quote rcmd ovrprtf *prtf splfown(*curusrprf) ovrscope(*JOB)
quote rcmd dspjoblog output(*print)
"
It appears to me that there may be an inability with the OS/400 FTP
to transfer tables containing LOBs but I have been unable to verify
it.
While non-SQL database I\O can effect an "open" of a file with BLOB
and CLOB data, only some special features enable a non-query request to
open and perform I/O on a database file with LOB data type(s) without
experiencing the Data Management open error CPF428A rc1.
Can anyone confirm or deny the ability and perhaps instruct me why
it might not work if it is in fact allowed.
I am not sure that the special C LOB enabler switches [or whatever
other languages extended similarly] are used by FTP I\O, and that even
if they were, whether the LOB data is really even directly accessible
into the buffer, or whether the buffer data appears to the requester as
the string '*POINTER' just as DSPPFM FILE(FileHasLOB) would show.
We are running V6R1 on both servers and the table exists on both
servers. It is empty on the target (server) system. I am in binary
stream mode.
If even possibly supported [not in v5r3; nor in newer releases as I
recall], presumably BLOCK mode is either required or at least best.
Block requires EBCDIC.
http://archive.midrange.com/midrange-l/201004/msg01144.html
The data could be transported as saved object and data, *SAVRST data,
transported in binary stream; e.g. save files. Another option is to
export the data, transport the exported data, then import the
exported+sent data. These same alternatives are normally required
anyhow for data other than plain text and [IIRC still] when database
NULL values are present [i.e. CPF5035 rc19 prevents I/O of rows with NULLs].
But the easiest and best bet is to use a Database transport method
instead of the simplistic binary-or-text "record data transport" as
provided by the FTP. SQL using DRDA is one option. Others have noted
enhancements in v7r1 which make that easier with statement [naming]
support directly in SQL. Earlier releases using STRQM RUN QUERY and
SAVE DATA AS can get the data without writing a program to perform the
CONNECT, FETCH, and writing the data locally.
midrange.com
