Novice question
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 306
- Joined: Wed Jun 21, 2006 11:41 am
Novice question
Hi,
I am trying to take a flat file, make some transformations and then load to a Mainframe DB2 table. I also need to delete all the rows from the child table before the processing of the insert can take place (I have "Clear the table, then insert rows" as the Update action).
I get the following error which makes sense.....just wondering how I can work around it.
I have a sequential stage linked to a tranformer stage which is linked to an ODBC stage. I thought I could just edit the SQL in the last stage but it does not allow me to do that.
Thanks - - John
NZUN_CNTRCT_LOAD..NZUN_CNTRCT.DSLink4: DSD.BCIOpenW call to SQLExecDirect failed.
DELETE FROM NZUN.CNTRCT
SQLSTATE=23504, DBMS.CODE=-532
[DataStage][SQL Client][ODBC][IBM][CLI Driver][DB2] SQL0532N A parent row cannot be deleted because the relationship "PLANINF1" restricts the deletion. SQLSTATE=23504
I am trying to take a flat file, make some transformations and then load to a Mainframe DB2 table. I also need to delete all the rows from the child table before the processing of the insert can take place (I have "Clear the table, then insert rows" as the Update action).
I get the following error which makes sense.....just wondering how I can work around it.
I have a sequential stage linked to a tranformer stage which is linked to an ODBC stage. I thought I could just edit the SQL in the last stage but it does not allow me to do that.
Thanks - - John
NZUN_CNTRCT_LOAD..NZUN_CNTRCT.DSLink4: DSD.BCIOpenW call to SQLExecDirect failed.
DELETE FROM NZUN.CNTRCT
SQLSTATE=23504, DBMS.CODE=-532
[DataStage][SQL Client][ODBC][IBM][CLI Driver][DB2] SQL0532N A parent row cannot be deleted because the relationship "PLANINF1" restricts the deletion. SQLSTATE=23504
Change the SQL option to User-Defined, then you take 100% responsibility for well-crafted SQL. But, your issue is referential integrity. If there's a child row, how do you propose to remote the parent first? You have to either decouple the child rows, drop RI, or remove the child rows. Pick your poison.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Premium Member
- Posts: 306
- Joined: Wed Jun 21, 2006 11:41 am
To clear out the children, you're going to either:
1. Issue a command line statement to delete all rows (or import from /dev/null with the truncate syntax, or runs a stored procedure), possibilities include using a Before-job/stage routine call, a Command stage, a Routine stage, or a Batch job using the DSExecute API.
2. Write an output link with @INROWNUM=1 in the constraint to send one rows to an ODBC stage with deleting User-Defined SQL that doesn't care about the passed value in the one row, just delete all rows in the table. (hokey but it works)
1. Issue a command line statement to delete all rows (or import from /dev/null with the truncate syntax, or runs a stored procedure), possibilities include using a Before-job/stage routine call, a Command stage, a Routine stage, or a Batch job using the DSExecute API.
2. Write an output link with @INROWNUM=1 in the constraint to send one rows to an ODBC stage with deleting User-Defined SQL that doesn't care about the passed value in the one row, just delete all rows in the table. (hokey but it works)
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 47
- Joined: Fri Sep 23, 2005 6:01 pm
RDBMS or DB2 stages are options if you aren't committed to using the ODBC stage. They do have before/after SQL tables where you can put these statements or stored procedure calls.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
-
- Premium Member
- Posts: 306
- Joined: Wed Jun 21, 2006 11:41 am
Thanks guys......I ended up going with the Dynamic RDBMS stage and I coded the delete of the child table in the "Before" tab. I also have the update action as Clear Table then insert rows. All rows from both tables are now getting deleted.
New Problem:
When I execute, all rows for the two tables get deleted but even though the canvas(In designer) and Director show 1000 rows(I placed this limit on the Job Run Options screen) being loaded to the Parent table none are actually loaded. I verified both on the Mainframe Database and the view data button on the Dynamic RDBMS stage.
Any suggestions with my new problem?
Thanks - - John
New Problem:
When I execute, all rows for the two tables get deleted but even though the canvas(In designer) and Director show 1000 rows(I placed this limit on the Job Run Options screen) being loaded to the Parent table none are actually loaded. I verified both on the Mainframe Database and the view data button on the Dynamic RDBMS stage.
Any suggestions with my new problem?
Thanks - - John
-
- Premium Member
- Posts: 306
- Joined: Wed Jun 21, 2006 11:41 am
-
- Premium Member
- Posts: 306
- Joined: Wed Jun 21, 2006 11:41 am
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
Yes, but in an event of a rejection, warnings are generated. If you dont see any warnings then as Craig mentioned, maybe you are loading it somewhere else. Like say mabye in one environment and while hitting view data the default parameter might be pointing to someother environment. Possible ...?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Premium Member
- Posts: 306
- Joined: Wed Jun 21, 2006 11:41 am