Assignment of a NULL value to a NOT NULL

[nitkuar]

not sure why this is happening... but try using trimming in a stage variable with varchar datatype and use that stage variable in the column derivation.

[kennyapril]

[IBM][CLI Driver][DB2/SUN64] SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=3, TABLEID=5, COLNO=5" is not allowed. SQLSTATE=23502

the above message is the warning I get with out any errors when using only IsNull.



Thanks,

[kennyapril]

Tried that also but did not help....


thanks,

[nitkuar]

Kenny, I don't have datastage access at the moment, so can't try it on my side.

But still , want you to give a last shot by assigning datatype of this field as VARCHAR all throughout the job starting from source to target.

If it still doesn't works, will try it tomorrow if you don't get solution by that time.

[kennyapril]

tried the last shot also,but no solution.

will let you know if I find the solution by tomorrow.

thanks a lot for your input and effort nitkuar

[vinothkumar]

Instead of assigning ' ' to name, give some default value 'UNKNOWN' and test how it behaves.

[kennyapril]

tried the same way,assigned a value which says"no-name" but behaves the same way.

the flow is remove duplicates stage------>transformer------->table

does this change anything in the above ideas.

around 400 records come into removeduplicates stage as the duplicates are removed now the output records from the removeduplicates stage has around 100 records and in that there is only one null record and rest has values in it,only because of that one record all the others also cannot be loaded into the table.

[kennyapril]

the earlier one worked when the datatype is changed.

but now I see another waring which drops many records all the duplicate records,used a look up with continue also with duplicates...but I get the below kind of warnings

DB2_UDB_API_77,0: Warning: DSproject2.DB2_UDB_API_77: [IBM][CLI Driver][DB2/SUN64] 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 "USERS.TEST_USERS" from having duplicate values for the index key. SQLSTATE=23505

any suggestion to capture the dropped records also in the table

[swapnilverma]

Kenny,

Last warning is clearly for Dups ..

I am bit confused and have below queries ?

Do You have Nulls for Primary Key column ?

have you tried NullToEmpty() function to change Nulls into empty string ?



You can use reject Link in DB2 to capture dup records ...

[kennyapril]

there are some nulls but removed the nulls from the primary key column.
probably I need to change the column because when the records come in required way then nulls can be removed and change to values.

before this stage of removing nulls a lookup is done between a table and a file.
table as a primary input and file as a lookup table.
userid is the key field in both the file and table.
table has only userid and file has userid,name,appid.
I need userid,name,appid.

but the issue is userid in table has length 20 and char datatype with no null
and the usedid in file is char but no length specified and nullable.

so what will be the output....as userid from table or userid from file.
I also need duplicates from the file.

please suggest!!

[kennyapril]

That worked.....used trim to the records from the table and trim to the records from the file and now the lookup works.....well.

Thanks for all valuable inputs!!