Key column in schema file

[pravin1581]

Hi,

How to define a key column in schema file.

Thanks in advance.

[ray.wurlod]

{Key}

[pravin1581]

{Key} ...

Thanks for the reply. Can you please give an example, the way we were trying it was we picked the parallel layout of the table definition, defined one of the columns in the table as key but there was no change in the layout of the table definition and we searched the documentation on the schema file also nothing was mentioned for the key in the schema files.

[pravin1581]

Any replies or suggestions.

[ray.wurlod]

Any replies or suggestions.

Learn patience. Some people like to do other things than DSXchange on their weekends.

[ArndW]

Add "$OSH_PRINT_SCHEMAS" to a job's parameters and experiment for yourself how the key column setting is reflected in a schema.

[ray.wurlod]

Most record schemas don't need the key property, so don't store it in there but, instead, pick up the key property from the stored metadata at run time.

You can see this by opening any table definition that has a Key defined, select the Layout tab then select the Parallel option.

[pravin1581]

Actually I am running a upsert query on the table using schema file, hence I need to define the key in the schema file.

[pravin1581]

I added the $OSH_PRINT_SCHEMAS env variable to the job but there was no difference for the key column in the schema definition in the OSH script generated by the job.

[ray.wurlod]

The stage uses the (table definition) metadata stored within DataStage to determine which column references need to be included in the WHERE clause. As I mentioned earlier, this information is not obtained from the record schema. If it really irks you, you can include a comment in your record schema.

[antonyraj.deva]

The syntax for mentioning a record in a schema file is

Code: Select all

Column Name:Nullability:Data Type [Length] {Key}

Example

Code: Select all

record(name:not nullable:string:[255] {Key};value1:int32;date:date)

Thanks,
Tony

[sureshreddy2009]

Basically we are also using schema files to read the data from a sequential file stage , at job level and at stage level, we mentioned as key.In schema file we are using like following

If the datatype is string and nullable

colum_name:nullable string[max=10] {quote=none};

If the datatype is string and not nullable

colum_name:string[max=10] {quote=none};

If the datatype is integer and nullable

colum_name:nullable int32 {quote=none};

If the datatype is integer and not nullable

colum_name:int32 {quote=none};

we always bothered null and not null not a key

[ray.wurlod]

Sequential Files don't have keys anyway. They are accessed sequentially.