Delete table before loading
Moderators: chulett, rschirm, roy
Delete table before loading
In px job if i want to delete the table before loading how do we acheive it.
truncate is not working.
Also i dont want to call sqlplus thru unix shell in before job routine..
truncate is not working.
Also i dont want to call sqlplus thru unix shell in before job routine..
Hello Sun786,
it depends upon the database you are using and how you connect. For instance, if you connect straight to Oracle you can use the write method "Replace" which does a "Replace by dropping table and creating new".
it depends upon the database you are using and how you connect. For instance, if you connect straight to Oracle you can use the write method "Replace" which does a "Replace by dropping table and creating new".
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
You might also want to explain what "truncate is not working" means. If it's a permissions problem, I'd be surprised if you could turn around and drop the table. Plus, don't forget you'd lose everything associated with that table when you drop it - grants, indexes, yada yada yada. Those (if any) would need to be recreated.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
sun786,
in your Px Oracle stage, choose the attribute for write method on the left side, and in the drop-down list on the right you can choose "replace". More details can be found on page 13-12 of the Parallel Job Developer's Guide.
in your Px Oracle stage, choose the attribute for write method on the left side, and in the drop-down list on the right you can choose "replace". More details can be found on page 13-12 of the Parallel Job Developer's Guide.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
How you delete a UniVerse table depends solely on how it was created; there are three or four possibilities. Searching the forum will yield all of them.
Never delete a DataStage repository table; they are too difficult to recover. Exception: if you are instructed to do so by support, as part of recovering from an even worse situation, then follow their instructions exactly.
Never delete a DataStage repository table; they are too difficult to recover. Exception: if you are instructed to do so by support, as part of recovering from an even worse situation, then follow their instructions exactly.
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.
I created the table via a UniVerse stage. I checked the box "Create table in target database". On the Edit DDL tab, I clicked on the "Create DDL" button and did not specify any of the Create table options.ray.wurlod wrote:How you delete a UniVerse table depends solely on how it was created; there are three or four possibilities. Searching the forum will yield all of them.
Is there any other documentation on UniVerse tables besides the Server Job Developer's Guide? I've tried to search using several criteria on this forum but do not see anything about the 3 or 4 possibilities of creating/deleting. Perhaps I need more training in forum searches....
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
DROP TABLE if created with CREATE TABLE or UV stage
DELETE.FILE if created with CREATE.FILE or with Hashed File stage in account
rm -rf (or DEL /S) if created with mkdbfile or with Hashed File stage in directory (a subsequent DELETE VOC 'hashedfilename' may be required if SETFILE has been used to create a VOC pointer)
Other information can be found in on-line help for the Engine (use the command HELP SQL CREATE TABLE for example in a dssh environment) or in UniVerse manuals that can be downloaded at no charge from IBM's web site
DELETE.FILE if created with CREATE.FILE or with Hashed File stage in account
rm -rf (or DEL /S) if created with mkdbfile or with Hashed File stage in directory (a subsequent DELETE VOC 'hashedfilename' may be required if SETFILE has been used to create a VOC pointer)
Other information can be found in on-line help for the Engine (use the command HELP SQL CREATE TABLE for example in a dssh environment) or in UniVerse manuals that can be downloaded at no charge from IBM's web site
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.
I was able to DROP TABLE, but it was really ugly how I did it. In the UniVerse stage, I checked the "Create table in target database" again and edited the DDL with DROP Table "tablename". I ran the job to drop the table. It solved my problem. Is this the only way to use the DROP TABLE command (via EDIT DDL tab in UniVerse stage)?ray.wurlod wrote:DROP TABLE if created with CREATE TABLE or UV stage
Thanks so much Ray....I apologize for making you dig everything up for me. I'm quite new to this forum and it seems like you would be a great ally to have.