Novice question

JPalatianos · Post by **JPalatianos** » Thu Jul 13, 2006 2:42 pm

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

kcbland · Post by **kcbland** » Thu Jul 13, 2006 2:50 pm

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.

JPalatianos · Post by **JPalatianos** » Thu Jul 13, 2006 2:57 pm

Kenneth,
I plan on deleting the child table first.......This is the added sql I need to add. I don't have the option of selecting User Defined SQL with teh ODBC stage being the last in line.
Thanks - - John

kcbland · Post by **kcbland** » Thu Jul 13, 2006 3:12 pm

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)

bharathappriyan · Post by **bharathappriyan** » Thu Jul 13, 2006 4:27 pm

Hi,

Instead of using ODBC stage use Dymanic RDBMS stage. In that stage,
go to SQL tab. you can see Generated, user defined, before, after tabs.
In that before stage, put your query to delete the records in child table.
Hope that this will help you to resolve your problem.

kcbland · Post by **kcbland** » Thu Jul 13, 2006 4:52 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.

Krazykoolrohit · Post by **Krazykoolrohit** » Fri Jul 14, 2006 9:47 am

why do we want to delete all rows of child table?

i think we should drop the referential integrity of the parent table using before SQL and then recreate it using after SQL running the insert query in between

JPalatianos · Post by **JPalatianos** » Mon Jul 17, 2006 11:52 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

chulett · Post by **chulett** » Mon Jul 17, 2006 12:06 pm

What does the job's log show? Warnings?

JPalatianos · Post by **JPalatianos** » Mon Jul 17, 2006 12:47 pm

The log has no warnings and even shows:

NZUN_PLEE_LOAD..Load_NZUN_PLEE: DSD.StageRun Active stage finishing.
1000 rows read from DSLink3
1000 rows written to DSLink4
0.343 CPU seconds used, 0.703 seconds elapsed.

chulett · Post by **chulett** » Mon Jul 17, 2006 1:06 pm

Then perhaps they loaded somewhere other than where you expected?

JPalatianos · Post by **JPalatianos** » Mon Jul 17, 2006 1:15 pm

I did check that out.....but it is deleting the table I expected but just not writing to it. When I try a view data on DSLink4 I get a "data source is empty" message even though the logs show that 1000 records were written to DSLink4.

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Jul 17, 2006 1:17 pm

are you sure no nulls are being passed to a field which is not null? this happens when datstage drops the records but sometimes you still see all the rows traversing to next stage.

DSguru2B · Post by **DSguru2B** » Mon Jul 17, 2006 1:39 pm

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 ...?

JPalatianos · Post by **JPalatianos** » Mon Jul 17, 2006 1:44 pm

Thanks for all the suggestions......
I'll keep looking to see if it is loading elswhere, but I did try the following. Placed an Insert staement(one row) in the After tab and that is loading the data in the appropriate table??