djm408@tijc02.UUCP (David Marks ) (06/15/88)
I have heard that some SQL databases can import fields from a file
into attributes of relations. I have also heard that this can be done from
a file that originated from a device externally networked to the device on
which the SQL database resides. Has anybody had any experience with this?
How efficient/inefficient is this compared to a remote SQL query from a
device networked to an SQL db?
Please send e-mail, and I will summarize to the net. Thanks.
-----------------------------------------------------------------------------
#include <standard/disclaimer.h>
LIFE IS NOT A MALFUNCTION!
David J. Marks
Texas Instruments
Ms 3520
Erwin Highway/P. O. Drawer 1255
Johnson City, TN. 37605
mcnc!rti-sel!tijc02!djm408donovan@hpsmtc1.HP.COM (Donovan Hsieh) (06/17/88)
In the article, David Marks <djm408@tijc02.UUCP> writes : >I have heard that some SQL databases can import fields from a file >into attributes of relations. I have also heard that this can be done from >a file that originated from a device externally networked to the device on >which the SQL database resides. Has anybody had any experience with this? >How efficient/inefficient is this compared to a remote SQL query from a >device networked to an SQL db? I believe that many SQL based database provide such kind of import/export capabilities between external files and their database relations. HPSQL, a SQL based relational database from Hewlett-Packard provides LOAD & UNLOAD commands which will perform exactly what you are asking. As for the import/ export from different devices on the network (I assume that your "device externally networked" refers to the remote mounte disks using NFS or RFA), I don't see any difficulty on the UNIX implementation. As for the efficiency ( I bet you mean performance) issue, my experience is that it's acceptable on HPSQL as long as the network traffic is not too bad. One of the concern I think you should pay attention to is the mapping of attributes from the imported file to the database relations. Sometimes it may become very messy if the field length or data type specified by the import/ export does not map correctly. It may insert tuples which contains garbage information. Donovan Hsieh Software Development Technology Lab Hewlett-Packard, Cupertino California
sytek@tekgen.BV.TEK.COM (Mike Ewan) (06/20/88)
In article <11410001@hpsmtc1.HP.COM> donovan@hpsmtc1.HP.COM (Donovan Hsieh) writes: >In the article, David Marks <djm408@tijc02.UUCP> writes : > >>I have heard that some SQL databases can import fields from a file >>into attributes of relations. ... > >I believe that many SQL based database provide such kind of import/export >capabilities between external files and their database relations. HPSQL, a >SQL based relational database from Hewlett-Packard provides LOAD & UNLOAD ... UNIFY also has this capability. They call it INSERT or UPDATE. The syntax is: insert into relation: from 'filename'/. This works fairly well as the file only needs to be ascii delimited with '|'. (Assume usual disclaimers)
allbery@ncoast.UUCP (Brandon S. Allbery) (06/29/88)
As quoted from <3102@tekgen.BV.TEK.COM> by sytek@tekgen.BV.TEK.COM (Mike Ewan): +--------------- | In article <11410001@hpsmtc1.HP.COM> donovan@hpsmtc1.HP.COM (Donovan Hsieh) writes: | >In the article, David Marks <djm408@tijc02.UUCP> writes : | >>I have heard that some SQL databases can import fields from a file | >>into attributes of relations. ... | > | >I believe that many SQL based database provide such kind of import/export | >capabilities between external files and their database relations. HPSQL, a | >SQL based relational database from Hewlett-Packard provides LOAD & UNLOAD ... | | UNIFY also has this capability. They call it INSERT or UPDATE. The syntax +--------------- It is also available as an external program, DBLOAD (in fact, the SQL "insert" command is just an interface to DBLOAD). And using the "lines 0" command and SELECT ... INTO will perform the unload operation. Informix-SQL also has LOAD and UNLOAD statements, which derive from Informix 3.x's LOAD ASCII / UNLOAD ASCII, plus an external "dbload" program. But these require that you turn on transaction logging even when it's acceptable to do a "tar" backup of the database every day. I consider this a misfeature, especially since there's no way to turn it off once it's on (I once accomplished it by manually updating informix.systables, but it was a nightmare). Oracle has an (extra cost) utility that does the same thing, although I found writing a simple (load complete table) loader to be trivial. (Insofar as the fixed-record-size-oriented PCC is "trivial" to use under Unix. Boo, hiss! -- they should use arbitrary-length lines in PCC rather than forcing me to check to see if I went beyond column 80 and including an option if I did -- and G*d help me if I went beyond column 132. [--uh, get off the soapbox, Brandon. . . .]) -- Brandon S. Allbery | "Given its constituency, the only uunet!marque,sun!mandrill}!ncoast!allbery | thing I expect to be "open" about Delphi: ALLBERY MCI Mail: BALLBERY | [the Open Software Foundation] is comp.sources.misc: ncoast!sources-misc | its mouth." --John Gilmore
aland@infmx.UUCP (Dr. Scump) (07/19/88)
In article <8190@ncoast.UUCP>, allbery@ncoast.UUCP (Brandon S. Allbery) writes: > > (discussion about various vendors' ASCII file load utilities) > > Informix-SQL also has LOAD and UNLOAD statements, which derive from Informix > 3.x's LOAD ASCII / UNLOAD ASCII, plus an external "dbload" program. But > these require that you turn on transaction logging even when it's acceptable > to do a "tar" backup of the database every day. I consider this a This is not true. Not at present, anyway, and I am unable to find any past bug which mandated logging. (Note also that INFORMIX 3.X had no transaction logging capability anyway, though it did have audit trails). There is the situation that if you *are* using transaction logging and you are running on a non-system-call-locking port, you need to lock the table first or you can run out of record-level locks (normally a good idea anyway). > I consider this a > misfeature, especially since there's no way to turn it off once it's on (I > once accomplished it by manually updating informix.systables, but it was a > nightmare). I agree that there should be an straightforward RDSQL statement to turn off logging, but I wouldn't consider the current "workaround" a "nightmare" to use. All you need to do is run this statement: (Kids, don't try this at home unless you *really* want to stop logging...) DELETE FROM SYSTABLES WHERE TABNAME = "syslog" If that's your worst nightmare, you must sleep really well :-] (Don't get me wrong; I agree fully with your assertion that it should be its own command, and this has been submitted as a feature request) For Turbo, the procedure is different. To change logging modes for one or more databases: 1> run an archive from the TBMONITOR menu; (don't exit TBMONITOR yet!) 2> go to the Databases option of the Logical Logs menu, move the highlight to each desired database, hit CTRL-B, and change status as desired (Unbuffered logging / Buffered logging / No logging). > Brandon S. Allbery | "Given its constituency, the only Hope this helps. If you were misinformed by someone here regarding the need for transaction logging, I'm sorry... -- Alan S. Denney | Informix Software, Inc. | {pyramid|uunet}!infmx!aland Disclaimer: These opinions are mine alone. If I am caught or killed, the secretary will disavow any knowledge of my actions. Santos' 4th Law: "Anything worth fighting for is worth fighting *dirty* for"