deleting dependant table rows

[Stef]

Hi All,

In 1 job of mine I am loadind data into table a with clear update action 'clear table, then insert rows' as I want to redo the whole thing from scratch. Unfortunately Two other tables have FKs pointing to that table. So the delete from table statement can not execute succesfuly.
They only way I found to fix this w/out modifying table definitions is to use a command 1 stage to purge(delete from) each table content.
I don't feel that's the proper way of doing it.
Please suggest any better way for this.

Thanks.

[rasi]

Get all the keys from your existing job which has the foreign key constraint. Then create a job which passes these keys to those table to delete records from parent table first. After this job is finished use your Clear table/insert logic. It will work

Thanks
Siva

[ray.wurlod]

Does your database support a CASCADE rule on DELETE ? If so, why not use that?

[Stef]

my tables are in the localuv dsn (universe).
I meant to say I simply want to restart from scratch on this table and delete all rows from the 2 dependant tables.
something like:
DELETE FROM tableB;
DELETE FROM tableC;

I thought I could acheive this in a before job/stage trigger but as I understand it it seems sql is not allowed. I then added 2 commandstage with execTCL each one with a specific delete from...

then my job looks like this:


CClearB
I
I
V
SeqFileA------->LoadTransform-------->universeA
A
I
I
CClearC

all links are rimary links(no dotted lines)

it that a sound design ?
is there a better automated way of doing this n DS ?

Thanks

[ray.wurlod]

I doubt that you can have two simultaneous writes to the same Sequential File. Try this design instead.

Code: Select all

SeqFileA------->LoadTransform-------->universeA 

Using ExecTCL from the before-stage subroutine in the Transformer stage (which is executed before any rows are processed), execute the command ClearBothTables.

Before you run the job, you need to construct the command. Open the Administrator client's command window in your project. Execute the DELETE FROM tableB; and DELETE FROM tableC; commands. Now observe that both commands are in the history. Multi-select the two of them and click Save. When prompted for a name, enter ClearBothTables.

To test, execute ClearBothTables from the command window!

[Stef]

Thanks Ray,

It works(OF COURSE!!!) and this is exactly what I was looking for.

regarding my previous design the drawing really shows stages so the seqfile is written into by the 2 cmd stages and the ouput of the seqfile stage contains only the fields on that seqfile (a date in this case as it is to build a time dimension table). The jobs executes fines, I only felt is was not cosher.

an additional comment: I do not see ClearBothTables neither in Designer nor in manager! Where is it located ? I expected to see it under the routine branches.

Thanks.[/url]

[chulett]

That's because it's something called a "Paragraph", one of those secret Universe under the covers type things.

Check out this post where Ray and Ken explained it all for me.

[Stef]

Hi All,

very instructive, thanks a lot to you guys who spend a lot of time helping.
after a little research this seems to be accessing the DS engine (like we can thru the DSSH form a dos shell or doint an open localhost. I am not very familiar with this environment and would like to find out some more.
Where could I go to get this ? anywhere in the DS doc ? I did not find much in in designer online help.

...