On 23-Jun-2011 14:15 , Jeffrey Carey wrote:
As mentioned, you could CPYF from a DDM and then SQL against that.
Alternatively (especially if that remote file is large), you could
connect to the remote database,
CREATE TABLE <name> AS
(SELECT <your selection criteria>) WITH DATA
This will put the results of your query into a table, albeit still on
the remote system (even if you specify library QTEMP). Then you
could build a DDM over that remote file and CPYF to the local system
(or use other methods of shipping the file over).
The advantage here is that you may only care about a bit of that
original remote table for your local query (this month's records, for
instance). Also, it will use SQE instead of CQE for the SQL part (but
not the DDM part if you are going that route).
Why copy the data twice [once remotely, then again from remote to
local], or even create a second DDMFile? Combining both the DRDA and
the DDM, that would effect two separate communication connections?
Seems like a lot of extra work that could easily exceed the benefits of
getting SQE to run on the target system; i.e. the selection would
probably need to be fairly complex for great benefit from SQE.?
I had not seen anywhere in this thread where there is any comment
about the CPYF command\utility having some selection capabilities; i.e.
all of the data from the remote file need not be copied by CPYF if only
a subset of the data is required [for both columns and rows]. For
simple selection [e.g. FROMKEY() to get desired month] a much easier
process might be to just CPYF CRTFILE(*YES) FROMFILE(DDMFILE)
TOFILE(QTEMP/LocalFile) and then reference that local-file in QTEMP on
the FROM\JOIN of the local SQL SELECT; CRTFILE(*NO) and FMTOPT(*MAP
*DROP) if removing columns of data. If the CPYF selection is not robust
enough, then the related copy utility feature "Copy From Query File"
exists also, combining both of the following CL requests: OPNQRYF
FILE((DDMFile)) OPNID(GETRMTDATA) QRYSLT() and CPYFRMQRYF CRTFILE(*YES)
FROMOPNID(GETRMTDATA) TOFILE(QTEMP/LocalFile). For both CPYF and
CPYFRMQRYF, that is accomplished using only the already existing DDMF
and the DDM file communications setup, and just the one remote
connection being established.
It would probably be very useful to connect to different tables in
different databases in one SQL statement on i - especially when you
start dealing with IASP, where each IASP is a different database.
CAAC or LUG requirement anyone?
I believe a search term is "federated"; seen as: "data federation"
"federated server" "federated database" "federation server". I believe
some feature of WebSphere provides the capability, but I am not aware of
the capabilities of DB2 for i as the client, though I would expect
probably from JDBC and CLI.?