MIDRANGE dot COM Mailing List Archive

Home » MIDRANGE-L » October 2012

Re: Unable to FTP an SQL table containing LOBs between two System I servers.


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

"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

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.

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.

Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact