[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.
Smarter OCI stage wrt table creation
Smarter OCI stage wrt table creation
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.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.
Does it really need to be about the why? Can't I just say "because I wanna"?kduke wrote:Why do you need to drop and recreate? What about truncate?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
That is why they want to stay in control of things they are responsable for.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.
It's all about the why, still you can say "because I wanna" anywaychulett wrote: Does it really need to be about the why? Can't I just say "because I wanna"?
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? )
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org