Smarter OCI stage wrt table creation

Do you have features you'd like to see in future releases of DataStage, MetaStage, Parameter Manager, Version Control or one of the other tools represented on this forum? Post your ideas here!

Moderators: chulett, rschirm

Post Reply
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Smarter OCI stage wrt table creation

Post by chulett »

[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. :evil:

[/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. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:

Post by kduke »

Why do you need to drop and recreate? What about truncate?
Mamu Kim
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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. :wink:
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"? :cry:
-craig

"You can never have too many knives" -- Logan Nine Fingers
roy
Participant
Posts: 2598
Joined: Wed Jul 30, 2003 2:05 am
Location: Israel

Post by roy »

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. :wink:
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"? :cry:
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 :roll:, 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
Image
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:

Post by kduke »

Nice, can I quote you?
Mamu Kim
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Guess you already did! :lol:

Living large in a Kim Duke sig...
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply