Question on ODBC Output

[jim wood]

Guys,

I got in to a debate with a guy that I'm on site with. He was saying that he always exports to flat file rather than use odbc for 3 reasons:

1) Sychronus actions in the ODBC (hmmm)
2) ODBC Output is slow as it generates a log (Tiny data packet so I wan't buying)
3) A DB pulling is faster than being pushed to. (Again tiny data packet)

What do you guys use and why? Do you stick one method and why?

Jim.

[declanr]

I prefer having data in a database as a medium but agree that individual updates are slow using ODBCoutput, as such I do asciiouputs followed by a single ODBCOutput to run a bulk insert, then delete the flat file.

So my answer is yes and no but that wasn't an option.

[tomok]

My answer is it depends. If I am doing minor updates, like setting a flag or doing an incremental extract, then I might use ODBCOutput and write directly to the database. If I am writing thousands of records to a DB then I would most likely dump to a flat file and use some other tool to import that into the database.

[jim wood]

Good point Declan. The post and the poll are kind of the same but seperate. My post was more an aswering of my friends point. The poll is to find out how many people use it. I (as usual) didn't explain myself very well.

[Alan Kirk]

I'm glad that it's there and if I was writing to a small, localised database I would (and indeed have on some occasions) use it; essentially the same kind of thing as Tomok described. However the feed that we do to our BW system at present is flat file based. That was at their request since their own ETL tool needs to do some redirecting of the various fields, but even if it wasn't I would be reluctant to use a direct connection to feed something like an ERP because you end up losing control over your server's performance. If something is happening on the ERP side that is causing updates in general to run.... really.... slowly.... then you can end up with an effectively hung server for an indefinite amount of time. Granted the risk is small (assuming that the ERP back end is well designed and maintained), but it's there, and unless the updates are time critical I don't see the need to take it.

I therefore voted no, but it's a qualified no.

[AnthonyT]

Same as Tomok for me

[lotsaram]

Also same as Declan and Tom. I use it all the time but usually for small DBs that I'm in control of. For large inserts (10s of K records) or to an external DB that I don't have any control over, then a bulk upsert is much more preferable.

[Wim Gielis]

Me too.

[tomok]

Let me just add that IF you decide to use the ODBCOutput command you REALLY should create a stored procedure on your database server and then call that, instead of issuing a direct INSERT INTO type command. When you call stored procedures instead of direct SQL calls then the syntax checking portion of the SQL workload can be skipped, saving you a few processing cycles. When you processing a lot of records the syntax checking time can add up fast.

[Gabor]

I typically use it to set and remove flags in control tables of source DB and run optimization commands like DBMS_STATS.GATHER_TABLE_STATS to boost subsequent SQL queries.