Re: A Few Questions on the subject of bulk data loading.

Hi Chez, Chez David wrote: > A few questions: > Our product, Sagent Data Mart Solution, is an end to end data mart product. > An important piece of the "equation" is the ongoing population of the data > mart (aka, data movement). A critical factor in data movement is > performance (especially when having to "fit" into a nightly batch window). > Therefore, for the major databases supported we provide some form of batch > loading. > > "Problem Space" > The two most important considerations regarding HOW we provide batch > loading are: > 1. Ability to integration load process into Sagent - the best method is to > take advantage of the database vendor client > library's batch load API (if available). Unfortunately, not all > database vendors provide such an interface (ex, SQL Server > and Sybase do but Oracle does not). In Oracle's case we provide > command line execution of Oracle's SQL*Loader > batch load utility. The two critical questions for the utility are > can accept standard input and/or read from a pipe and the > other key question is can it read ASCII delimited data? > 2. Performance - this is the most important issue. Not much else needs to > be said. > > Questions: > - Does the CLI API provide for the batch loading of data? The LOAD SQL > statement? Anything else? No. The LOAD statement is *not* supported in CLI. It is a 'pseudo' SQL statement specific to the DbAccess utility. If you *must* load via CLI you will have to code the load logic yourself. > - If the LOAD SQL statement is the only means of batch loading via CLI can > it handle standard input or reading from a pipe? > - Which of the different utilities might be best? dbaccess, dbload, hpl > (high performance load) If performance is important, HPL is the clear winner. HPL can read from a pipe and will accept many different formats of input data (including delimited Ascii). The LOAD statement *only* works with delimited Ascii from a file (not stdin or pipe). Dbload can use delimited ascii or fixed format ascii, again from a file only (no stdin or pipe). > - Must any of the different utilities run on the same machine as the > Informix server (ie, can the utility run remotely? what about remotely > across platforms - NT to UNIX?) DbAccess and Dbload can work client/server on different systems (network bandwidth will be important for performance). HPL can be made to work client/server for Deluxe mode but *not* for express mode. > - Will the HPL (7.2/UNIX only) ever run on NT? I am not aware of any plans. Hope that helps. Chris -- Chris Jenkins (chrisj@informix.com) Advanced Technology Group >>> All statements and opinions are mine and should in no way <<< >>> be construed as representing those of Informix Software <<<