Cleanest method to truncate a table- no inserting rows...

crsimms · Post by **crsimms** » Thu Jun 08, 2006 10:29 am

All,

This may sound silly, but what is the cleanest method to have a job truncate a table only. That is the job does not also insert rows afterwards into the table and does not require a driver table or file to kick off the process. Currently I am using a transformer stage with a stage variable to drive a Dynamic RDBMS stage where the Update action is defined as User-defined SQL and the SQL contains the truncate table statement. However, I am running into a "ORA-01036: illegal variable name/number" error which I attribute to the dummy column within the link.

Thanks,

CSimms

kris007 · Post by **kris007** » Thu Jun 08, 2006 10:35 am

Could you post the userdefined SQL in here. The error message says you have declared someting which is not acceptable
Also why do you want to use the User defined SQL. You can always set the Updateaction to "truncate table then insert rows" provided you have a constraint in transformer which is set to '@FALSE'. This won't insert any rows!

HTH
Kris.

crsimms · Post by **crsimms** » Thu Jun 08, 2006 10:45 am

Kris,

The SQL is as follows:

TRUNCATE TABLE STAGING.#J_TABLE_NAME#_SNAP_KEYS

I am attempting to not use the Truncate then Insert rows Update action as I do not want to define a particular column as the table name is dynamic. I have tried to use the Truncate only option which looks like it would work, but the job only hangs.

Thanks,

CSIMMS

DSguru2B · Post by **DSguru2B** » Thu Jun 08, 2006 10:50 am

I would suggest writing a shell script that executes all the truncates. You can fire that script in DataStage.
You sure you have the truncate permissions. The job shouldnt hang as its not a logged operation.
Try running it again and then contact your dba to see what is that thread, created by you, is doing at the database. Does it even reach the database or not?

kris007 · Post by **kris007** » Thu Jun 08, 2006 10:51 am

So, now what is the column you have defined in the columns tab?

One thing you could do is, define a dummy column in OCI stage, which I guess you are already doing. In your User defined sql write..

Code: Select all

Select "something" from dual

and In your Before SQL or After SQL tab use the truncate statement. I see that it can work.

HTH
Kris

chulett · Post by **chulett** » Thu Jun 08, 2006 10:58 am

Why do you need User Defined SQL? As you've found, that's going to cause you nothing but grief trying to bind things.

The DRS stage when set to Oracle has two truncate Update actions:

Truncate only
Truncate table then insert rows

Seems like either would work for you, considering you aren't planning on sending any rows to the stage.

Edited to add: Ah, reading comprehension is a wonderful thing. Just saw your comments on these two options. I'd suggest the generic sqlplus script that truncates a passed in table name, that's one method we've used in the past that's simple to implement.

crsimms · Post by **crsimms** » Thu Jun 08, 2006 11:18 am

All,

First, the database user has truncate permissions. I can do this under the user in a tool such as Toad. The job hangs though using the Truncate only option and I am not sure why. I have been at places where the script method was employed, but I am trying to keep the solution strictly within DataStage. because of the dynamic nature of the table names, maybe I should attempt what Kris has suggested. I still do not understand why the DRS stage is not working correctly.

Thanks,

DSguru2B · Post by **DSguru2B** » Thu Jun 08, 2006 11:23 am

Did you ask your DBA to see what that particular thread is doing on the database?

kris007 · Post by **kris007** » Thu Jun 08, 2006 11:35 am

I think you should also consider what DSGuru2B has suggested because I remember once when that happened to me. The truncate job took for ever. Though I can't recollect what happened exactly at that time and how I(we) fixed it, I would suggest to give that one a shot. What happens when you truncate the same table from SQLplus or TOAD or whatever tool you use? Does the table get truncated?

DSguru2B · Post by **DSguru2B** » Thu Jun 08, 2006 11:44 am

I bet it does get truncated from outside DataStage. My main point is that he should issue the truncate from within datastage and make sure that thread reaches the database for one, and investigate what that thread is doing at the database level by involving the DBA. Only he can monitor the process and let crsimms know whats going on.

crsimms · Post by **crsimms** » Thu Jun 08, 2006 11:48 am

DSguru2B and kris007,

Yes, the table successfully truncates under Toad. I do need to check with our DBA as the Truncate only Update action is truncating the table as well. However, I need to see if the thread is actually being released or is holding on for whatever reason.

Thanks,

chulett · Post by **chulett** » Thu Jun 08, 2006 12:15 pm

Would you be opposed to using an ODBC stage for this? It will support a link with a dummy column that is not bound into the sql and your user-defined sql can be simply 'TRUNCATE TABLE XXXX'.

Done that before as well.

crsimms · Post by **crsimms** » Thu Jun 08, 2006 12:15 pm

All,

The DSR stage is executing the truncate table command and the table is being truncated. According to our DBA, no locks are being maintained on table which is good but the session is listed as inactive which is bad. It appears that Oracle is waiting for DataStage to close the connection but DataStage is just sitting there.

Strange and aggravating at the same time.

DSguru2B · Post by **DSguru2B** » Thu Jun 08, 2006 12:19 pm

Dont let DataStage sit alone. Join it and have a cup of tea

Well atleast now we know that the thread reaches the database and truncates the table successfully. Let the job run. See how much time does it take to finish the job.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 08, 2006 4:18 pm

What is your commit size (rows per transaction)?