Delete table before loading

sun786 · Post by **sun786** » Wed Feb 23, 2005 7:08 am

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..

ArndW · Post by **ArndW** » Wed Feb 23, 2005 7:26 am

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".

sun786 · Post by **sun786** » Wed Feb 23, 2005 7:44 am

Yes I am using oracle. Could you give me more details.how to go ahead to implement that.

chulett · Post by **chulett** » Wed Feb 23, 2005 7:52 am

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.

ArndW · Post by **ArndW** » Wed Feb 23, 2005 8:00 am

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.

sun786 · Post by **sun786** » Wed Feb 23, 2005 8:01 am

Thanks, I will try and let you know.

bchau · Post by **bchau** » Thu Mar 10, 2005 11:09 pm

how do you delete a UniVerse table?

richdhan · Post by **richdhan** » Fri Mar 11, 2005 1:56 am

Hi,

You can delete the Universe table(Hash file) using DELETE.FILE command.

HTH
Rich

chulett · Post by **chulett** » Fri Mar 11, 2005 7:57 am

No, a Universe table is something else and you would use DROP TABLE on it, from what I recall.

T42 · Post by **T42** » Fri Mar 11, 2005 4:33 pm

Now put down that Universe weapon and slowly back away. It's dangerous to manipulate your own project tables directly, even if you think you know what you're doing.

ray.wurlod · Post by **ray.wurlod** » Sat Mar 12, 2005 10:25 pm

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.

bchau · Post by **bchau** » Sun Mar 13, 2005 7:38 pm

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.

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.

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....

bchau · Post by **bchau** » Sun Mar 13, 2005 7:44 pm

OK, I think I found it......drop table, not delete table is the magic criteria

ray.wurlod · Post by **ray.wurlod** » Sun Mar 13, 2005 8:57 pm

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

bchau · Post by **bchau** » Sun Mar 13, 2005 11:46 pm

ray.wurlod wrote:DROP TABLE if created with CREATE TABLE or UV stage

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)?

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.