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.
deleting dependant table rows
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I doubt that you can have two simultaneous writes to the same Sequential File. Try this design instead.
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!
Code: Select all
SeqFileA------->LoadTransform-------->universeA
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!
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.
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]
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]
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.
Check out this post where Ray and Ken explained it all for me.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
...
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.
...