DSXchange

[rant]

It irks me that the OCI stages aren't as smart as the Hashed file stages when it comes to creating their underlying objects. A hashed file stage will create the underlying object once the 'first' it runs when it finds it doesn't exist. If you check the delete option, that happens each time.

For Oracle, something similar happens when you Generate DDL for both the 'table create' and the 'drop table' statements. Each time the job runs, it issues the drop table and then rebuilds it. This is great for something like staging tables that may vary greatly in size from run to run and also means one doesn't have to ensure the table is manually created before the first job run. And it *is* smart enough to ignore the failed 'drop' when it runs and the table doesn't exist.

Problem is we lose things when the table is dropped, things like indexes, grants and statistics. What I really like to be able to do is enable Generate DDL for the table but not enable the 'drop' of the table each time. It should be smart enough to create the table the first time and not issue the darn create if the table already exists.

[/rant]

I know there are 'work arounds' for this. Just felt like venting on something that would be fairly simple to implement that would make life so much easier in this regard.

Yes/no/maybe. The problem in Oracle is that the DBA usually wants to exert some control over what tablespaces (etc.) get used. It would be really tricky to implement this generically.

Why do you need to drop and recreate? What about truncate?

ray.wurlod wrote:Yes/no/maybe. The problem in Oracle is that the DBA usually wants to exert some control over what tablespaces (etc.) get used. It would be really tricky to implement this generically.

Not really. They assign default tablespaces per user so it's not typically anything one needs to worry about. If you end up in the wrong tablespace, blame goes straight back to the DBA.

kduke wrote:Why do you need to drop and recreate? What about truncate?

Does it really need to be about the why? Can't I just say "because I wanna"?

chulett wrote: Not really. They assign default tablespaces per user so it's not typically anything one needs to worry about. If you end up in the wrong tablespace, blame goes straight back to the DBA.

That is why they want to stay in control of things they are responsable for.

chulett wrote: Does it really need to be about the why? Can't I just say "because I wanna"?

It's all about the why, still you can say "because I wanna" anyway

I simply think there are very slim chances this is something we will see (at least for the next 5 years , unless some other product will implement such a feature, how about ranting in all ETL forums Craig? )

Nice, can I quote you?

Guess you already did!

Living large in a Kim Duke sig...