Hi all,
We have a number of Server jobs that load data into Oracle tables. I plan on partitioning these tables by month so that partitions older than 3 months could be archived and then dropped.
Does anyone know of ways for adding partitions to Oracle tables from DataStage server jobs?
At the moment, the only way that I know is to add two Oracle OCI stages and have them call Oracle stored procedure whose job would be to add partition to the destination table.
Thank you very much,
Seyed
Adding table partition from DS server job
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Surely the table only needs to be partitioned once. As you send a stream of data to the table, Oracle will worry about which partition to load each record into.
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.
However, I've been places that add partitions 'on-the-fly' as needed. And yes, it generally turns into a proc that either gets called 'when needed' or one that is smart enough to know if it needs to add new partitions or not.
You can use a dedicated Stored Procedure stage or the Before SQL area of your target stage but I'd suggest sticking with a separate stage, that way you can (optionally) pass information to it if needed, say like a max date from your source.
You can use a dedicated Stored Procedure stage or the Before SQL area of your target stage but I'd suggest sticking with a separate stage, that way you can (optionally) pass information to it if needed, say like a max date from your source.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers