Re: Making the most, or over-thinking it - SQL for uploads

On Tue, Aug 30, 2016 at 3:37 PM, Duane Scott <dscott@xxxxxxxxxxx> wrote:

And even if they are restricted to a database, can a single SQL for a DB2 connection be crossed with one for JDBC or any other?

The phrase "DB2 connection" is a kind of abstract thing. It can be
implemented using various interfaces. JDBC is one such interface, and
a JDBC connection to a DB2 database is very much a "DB2 connection".
So is one that uses ODBC. There are other kinds as well, but those two
are probably the most common.

So the phrasing you used is kind of confusing. But I think what you
are asking is: Can a single SQL statement operate on one table in one
database and another table in a different database. The answer is
"yes, with some conditions". But not in a way that materially improves
your situation.

If SQL can read a record as text, then the record text can be sub stringed and manipulated easily enough to INSERT it into named fields of another database. Example:
INSERT INTO TESTLIB/MYFILE (COD, DESCP) SELECT substr(f1,7,7), substr(f1,17,60) FROM mytextfile WHERE substr(f1,15,1) = '1'

By now, several people have chimed in to corroborate what I had
thought, which is: no, there isn't some way for arbitrary text files
to be used directly by SQL. At least not yet. You always have to load
the data into a database first to work with it using SQL.

What most people are saying, and I have to agree, is that transferring
the data from the (entire) text file into a database is ultimately not
a big deal. It's a "separate step" but that step is not a very
expensive one, and if you are building a recurring process, it can be
automated so that you don't even see it happening.

John Y.