DSXchange

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

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

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

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

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!!

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!!

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

Regards
Sreeni

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

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

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.

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

anusha wrote: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 wrote: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

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