How to handle Not Null columns using Schema file

[anusha]

Hi,

I have a generic parallel job that reads a schema file and loads in to target Database. I have a requirement of rejecting the rows if the value for a not null couln is NULL.
Here is my Schema file
record
{final_delim=end, record_delim='\n', delim=',', null_field="", quote=double}
( SITE_NAME: string[max=80];
BUSSIESS_ID: string[max=80];
BATCH_ID: string[max=80];
MATERIAL_ID: nullable string[max=80];
)

Schema file is not rejecting the records if the values for Not Null columns SITE_NAME,BUSSINESS_ID,BATCH_ID and MATERIAL_ID are coming as NULL. It is just populating the data in to target.
I tried giving NOT NULLABLE explicity for these coulmns in Schema file but my DataStage job is throwing an error

Please help me out to resolve the issue

Thanks in advance

[ArndW]

What error message are you seeing and is this schema used for both reading and writing?

[anusha]

ArndW,

Thanks for replying!!

Following is the error message that i am getting
Column_Import_1: In schema file "/local/apps/DRS_dstage/sandpit/tmp/dev/gdm2/ods/Sample_Schema1.schema": In field "SITE_NAME": Unknown schema type: not.

I am using this schema file only for reading.


Thanks

[ArndW]

There is no "not null" in schema files, if "nullable" is not present in the schema description then the field is not nullable.

[anusha]

Ya but it was not working even if that is not expilictly mentioned as Not NULL. I just tried trail and error on the schema file.

By default the coulmn are not null in the schema file.

Can u please suggest on how to procedd to handle not nulls using Schema files. Is there any environment variable to handle this?


Thanks in Advance!!

[anusha]

Ya but it was not working even if that is not expilictly mentioned as Not NULL. I just tried trail and error on the schema file.

By default the coulmn are not null in the schema file.

Can u please suggest on how to procedd to handle not nulls using Schema files. Is there any environment variable to handle this?


Thanks in Advance!!

[Sreenivasulu]

I think it should work. Try giving default='xxx' on SITE_NAME i.e
BATCH_ID: string[max=80] {default='xxx'};

Regards
Sreeni

[anusha]

Using this default value it will load into the target table with this default value for all the records that have null in the source for a particular NOT NULL column but i actually want to reject these records that are null for a not null column

[ArndW]

What happens when you put a reject link on your input file? (That was not really a question, but an answer...)

[anusha]

ArndW,

This option of keeping a reject link to the input file will not work in our case since we are reading the whole record as a single column in our DataStage job.

[ArndW]

Are you applying your schema to the one-column record in a column import stage? If so, you can use the reject mechanism there.

[Sreenivasulu]

Using this default value it will load into the target table with this default value for all the records that have null in the source for a particular NOT NULL column but i actually want to reject these records that are null for a not null column

Did you try this option ? At least this will prove that the syntax of your schema file is correct and the schema file is identifying the 'not null' values

Regards
Sreeni

[Sreenivasulu]

Using this default value it will load into the target table with this default value for all the records that have null in the source for a particular NOT NULL column but i actually want to reject these records that are null for a not null column

Did you try this option ? At least this will prove that the syntax of your schema file is correct and the schema file is identifying the 'not null' values

Regards
Sreeni

[anusha]

ArndW,

I am using the same mechanism of Rejecting the rows in the coulmn import stageusing the property Reject Mode=Output. But it is not rejecting the records that have null values in not null columns


Thanks