Re: Using STRSQL against a DDM file

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

Regards, Chuck