Join 2 tables in DB2

[kavuri]

Hi,
In my small job what I am having is 2 tables are there let me say "ABC" as first one and "DEF" as the second one.
Let me say "ABC" main table or left table. I am having a column in both the tables with the same name. I need to find data for that particular column in both the tables and put it back into first (ABC) table.
That is actually what I need to do here is compare column data from "DEF" with column data in "ABC" and if it is not available in "ABC" then add it otherwise do nothing. This may need row comparison.
Here my question is what stage is best suited for this, and how we need to design this job?

Any help is appreciated.

Thanks
Kavuri

[kavuri]

I tried by using join stage, but I am getting folowing error.

DB2_UDB_Enterprise_3,0: Error Idx = 12;
DB2 Driver Embedded SQL message: SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "BHI.PERSON_MEMBER_MAP" from having duplicate rows for those columns.
SQLSTATE=23505
;
sqlcode = -803;
sqlstate = 23505
Execute failed

and

DB2_UDB_Enterprise_3,0: The runLocally() of the operator failed.


Thanks
Kavuri

[kavuri]

I was able to clear the errors, but still I am getting warnings.

But I know that the logic what I had is wrong, because column in table "ABC" is primary key so I cannot insert duplicates, but in my present design I haven't mentioned regarding that anywhere.

Do I need to insert any other stage to do so or my total design is wrong? can anybody give me some hints.

The warnings I am getting are

Join_1: When checking operator: On input data set 1: When binding input interface field "CONSISTENT_MEMBER_ID" to field "CONSISTENT_MEMBER_ID": Converting a nullable source to a non-nullable result;
a fatal runtime error could occur;
use a modify operator to specify the value to which the null should be converted.

Thanks
Kavuri

[ArndW]

Kavuri,

as the message states, you have a nullable column and your database DDL states that the same column is not nullable. You need to make sure that null values cannot be present in that column. One way is via a modify stage. Another is to use a function like NullToValue(), NullToEmpty(), NullToZero() in a transform stage. Or even to handle nulls in the source stage itself.

[kavuri]

Hi ArndW,

Its OK. But can you give some code hint that I need to compare rows from both tables for the same column and add any rows which are not available in the left table from the roght table.

Thanks
Kavuri

[ArndW]

If both tables are in the same instance of DB2 you should just use the join SQL. If I have interpreted the question correctly, you might be better off doing a lookup on table DEF for the main input stream ABC.

[shamshad]

If I understood your question correctly, why not to run a simple SQL statement?

Bascially, you want to get all values on COLA from Table DEF that doesn't exist in Table ABC and then write those values to ABC table.

How is this SQL? Use UserDefined SQL at the DB2 source, get the rows in transformer and then use ABC as target.

SELECT COL_A FROM DEF WHERE DEF.COL_A NOT IN (SELECT COL_A FROM ABC) AND DEF.COL_A IS NOT NULL AND LENGTH(DEF.COL_A) > 0

Assuming COL_A is the column that is common in both DEF and ABC table.

[kavuri]

Thanks Shamshad and ArndW,
I made some more modifications to your ideas. Just I had taken a simple DB2/ODB enterprise stage there itself I put the query what Shamshad had given, passed it to a copy stage and passed to the target DB2 table, by using append mode and method write.
This is working fine for me I think.

I got it. But I am getting some warning.

DB2_UDB_Enterprise_2: When checking operator: When binding input interface field "CONSISTENT_MEMBER_ID" to field "CONSISTENT_MEMBER_ID": Converting a nullable source to a non-nullable result;
a fatal runtime error could occur;
use a modify operator to specify the value to which the null should be converted.

What is this modify operator, where I should incorparate it.

Thanks
Kavuri

[ray.wurlod]

Look at the generated OSH and at the score to determine that the modify operator has been inserted. You can emplace an explicit Modify stage to handle incoming nullable columns.