Runtime Column Propagation Passing Metadata in runtime

nansekar · Post by **nansekar** » Fri Aug 03, 2012 1:26 am

Hi

I have a Job structure as
Source(dataset ) and target is (database)
I want to pass the Dataset file name as Parameter and the Table name in the target as Parameter and enable runtime column propagation as I want to load 40 different tables from different datasets.

When I want to run this Job, how will I specify the Source and Target metadata definition during runtime..

ArndW · Post by **ArndW** » Fri Aug 03, 2012 1:42 am

If the names and datatypes for the datasets and their corresponding tables are identical then you have a perfect situation and the solution is very easy. You declare your source and target with RCP turned on and define no columns at all, datastage takes care of the rest.

If columns are missing in the dataset that exist in the table they would need to be nullable and would then receive null values. Columns in the dataset that don't exist in the table would get silently dropped.

nansekar · Post by **nansekar** » Fri Aug 03, 2012 1:58 am

Thank you. The datatypes are column names are identical,
My one doubt is is it not necessary to pass the schema file, if so where will i pass it during run time.

ArndW · Post by **ArndW** » Fri Aug 03, 2012 2:17 am

You don't need a schema file. The schema is embedded in the dataset and the columns are automatically used when RCP is turned on at this stage. Try a test job, declare your dataset and write to a peek stage, compile & run and ...voila... your columns are all visible in the peek.

nansekar · Post by **nansekar** » Fri Aug 03, 2012 2:39 am

Yeah, I was able to test without a Schema File, i was able to load a dataset into table.
It took the Dataset metadefiniton implicity from the datastage.

There was one scenario, For a particular column the Nullable property was set as Yes in the source (dataset) where as in target( table) at the Database level its as Nullable No.
Now my Dataset metadefiniton says that Null value can be accepted, but in database it wil not allow Null, so there the Job fails.

In such cases , is the only way to recreate the Dataset with Properties(column name, datatype, length,Nullable) exaclty as Target , or any possible ways to change the manually change the schema file or pass it as a parameter.

ArndW · Post by **ArndW** » Fri Aug 03, 2012 2:50 am

What exactly is the error message you are getting. In some cases with nullability differences you can set default values, but in others the error is a "hard" one which cannot be circumvented. Dynamically handling any nullable columns in a generic job is a bit more complicated and would involve using schema files.

nansekar · Post by **nansekar** » Fri Aug 03, 2012 4:31 am

Thank you so much for theinfo,
The error message was : "Type mismatch " for that particular column,
I am not sure, if i can create a schema file and pass it while running, if i want to pass the schema file a a parameter, how can i do it ..

ArndW · Post by **ArndW** » Fri Aug 03, 2012 4:39 am

What stage are you using and what is the exact message? You might be able to get by with nullable columns in this case.

nansekar · Post by **nansekar** » Fri Aug 03, 2012 4:56 am

Thank you so much for theinfo,
The error message was : "Type mismatch " for that particular column,
I am not sure, if i can create a schema file and pass it while running, if i want to pass the schema file a a parameter, how can i do it ..

ArndW · Post by **ArndW** » Fri Aug 03, 2012 5:02 am

What exactly is the error message you are getting (try cut-and-paste). What is your database output stage? There might be a way around the nullability issue.

chulett · Post by **chulett** » Fri Aug 03, 2012 6:30 am

And stop using the Reply with quote link for no reason, use the Reply to topic link instead. Please.

nansekar · Post by **nansekar** » Fri Aug 10, 2012 2:14 am

Hi ArndW

The error message

I had a column ABC_ID in the source dataset the option was NULLABLE Yes,
but in the database the ABC_ID column was a NOT null .

so when i try reading the dataset and and load the data, it was showing a type mismatch error..
the Nullability issue is resolved.

I just wanted to know, is ther any specific methods if we edit the schema file which we view in datasetfile managment.

As we can create a .SCH for sequential files. is there any way to edit the dataset schema which already exists in Datastage, without changing it through the dataset stage.