DSXchange

I need to just delete data from a table in a server job. How do I acheive this using DRS stage? I am trying the following:

[Transformer] > [DRS Stage]

In the DRS stage, I have configured the table name and given a User defined sql which will delete the data.

Now during compile, it complains Transformer requires an input link. How to get around that? I did a quick search but could not find anything. I remember it has something to do with setting a constraint but what exactly?

Any suggestions? All i need is to delete data from a oracle table and it has to be from a Server job.

Define a stage variable. That should calm down the compiler.

You need to define a stage variable so the job will compile, no need to actually use it. Then a constraint to ensure that only one record goes to the database stage, otherwise the job will run forver. I use:

@OUTROWNUM=1

you can use sequential file stage before transformer reading "dev/null" file.

Ah, yes... but you don't need to.

chulett wrote:You need to define a stage variable so the job will compile, no need to actually use it. Then a constraint to ensure that only one record goes to the database stage, otherwise the job will run forver. I use:

@OUTROWNUM=1

I tried this.
1. I tried with defining the table name in DRS. Also I defined one column. The sql tab has the delete sql statement.
In the transformer stage, I defined a stage variable and assigned it to this column on the tranformer out put link. Then I defined the constraint as you mentioned. Is this correct? I am getting an:

Abnormal termination of stage test1..Transformer_0 detected and job fails without any results.
=========
Also. I tried with just defining the table name in DRS (without specifying any cols). This gives me "No Cols on Transofrmer Output link" error.

That was just to get your job compiling and running with a chance of success. The actual setup of the DRS stage for this is a completely different issue.

Post your delete SQL.

chulett wrote:That was just to get your job compiling and running with a chance of success. The actual setup of the DRS stage for this is a completely different issue.

Post your delete SQL.

The sql is realy simple.

delete from tablename where col1=1

In the DRS stage, I have specified the tabel name in the "General" tab and Update action is "User-defined sql". I have specified col1 in the Columns tab(this also reflects in the Transofrmer output link. In that derived col I have put a constant say 5).

=======================
It seems simple, only thing is I dont understand if anything is wrong on the link going from Transofrmer to DRS stage.

I'd suggest stepping back and not using user-defined sql, it's not needed here. Create one column in the stage for col1 in the table and mark it as a Key field. Use an Update action of 'Delete existing rows only'. Set the value of the key field passed down from the Transfomer to 1. Done.

chulett wrote:I'd suggest stepping back and not using user-defined sql, it's not needed here. Create one column in the stage for col1 in the table and mark it as a Key field. Use an Update action of 'Delete existing rows only'. Set the value of the key field passed down from the Transfomer to 1. Done.

OK that works!!!
On similar lines in another job(same scenario) if I need

delete from tablename where col2 < firstday_of_currentmonth

here I was hoping to use the User -defined sql (oracle sql):
delete from tablename where col2 < trunc(sysdate,'MM')

This is throwing the same xfmer error. Any pearls of advise here?

Same pearl with a different shine. You'll probably need to use user-defined sql.

First, clarify what 'col2 < firstday_of_month' means. Col2 is a date? I doubt you can use 'MM' here without also including the year as well. I'm guessing you need a sql statement more akin to this:
Since the stage wants at least one column and that column must be bound into the query, you can play this game: make your one column in the link a dummy column, mark it as a key and set its value to '1' in the transformer. Then make your sql statement look like this:
You get the sql you need with a harmless "and 1=1" constraint tacked on the end.

chulett wrote:Same pearl with a different shine. You'll probably need to use user-defined sql.

First, clarify what 'col2 < firstday_of_month' means. Col2 is a date? I doubt you can use 'MM' here without also including the year as well. I'm guessing you need a sql statement more akin to this:

Code: Select all

delete from tablename where to_char(col2,'YYYY-MM) < to_char(sysdate,'YYYY-MM') 

Since the stage wants at least one column and that column must be bound into the query, you can play this game: make your one column in the link a dummy column, mark it as a key and set its value to '1' in the transformer. Then make your sql statement look like this:

Code: Select all

delete from tablename where to_char(col2,'YYYY-MM) < to_char(sysdate,'YYYY-MM') and 1=?

You get the sql you need with a harmless "and 1=1" constraint tacked on the end.

excellent Craig you totally rock man!
I used the only one col in xfmer as a dummy col(as a varchar) and then it worked.

delete from table where col2 < trunc(sysdate,'MM') and '1'=:1

I guess the main point i was missing here is that in the DRS stage any place other than the Before/After tabs , your sql (generated or user-defined) needs to atleast 1 column must be bound into the query.

Excellent, glad it all worked out. Seems like all of the database stages - with the exception of the ODBC stage for some reason - want to have all of the columns mentioned in the stage bound into the sql. And as you've found, it doesn't matter if it is generated or user-defined sql, bondage is required. Or should that be 'boundage'?

BINDING is what is required.

Did you miss the smiley, Uncle Ray?