Cannot preserve the partitioning of the parallel data set

[sumesh.abraham]

Hi All,

I'm facing an issue in my job. The job extracts data from an Oracle table (using Oracle Enterprise Stage) and a data set, does an outer join on the records from these and filters the records into 2 output sequential files (Exception records for each source) and a data set (Matching records from both sources). The filter conditions used against the links are

1) not(leftRec_ITEM=rightRec_ITEM and leftRec_LOC=rightRec_LOC) and leftRec_LOC=0
2) not(leftRec_ITEM=rightRec_ITEM and leftRec_LOC=rightRec_LOC) and rightRec_LOC=0
3) not(leftRec_ITEM=rightRec_ITEM and leftRec_LOC=rightRec_LOC) and rightRec_LOC=0

When I run the job, I'm getting 2 warnings as

1) main_program: The NLS character map <ISO-8859-1> is specified, but NLS_LANG is not set;
the NLS character map must be set to an ICU codepage which is equivalent tothe character set specified by NLS_LANG.

How can I resolve this as warnings are not allowed as per the requirements.

2) Sequential_File1: When checking operator: A sequential operator cannot preserve the partitioning of the parallel data set on input port 0
The Preserve Partitioning is set as Default in the Filter stage.
When I make it as Clear, this warning doesn't happen. Please throw some light on these issues. Also let me know how can I check whether a column value is NULL in the Where condition of the filter stage.

Thanks,
Sumesh

[Cr.Cezon]

This warning says that if you use a sequential file with partitioning the partitioning is not necesary because a seq file can't be particioning because is sequential and have not indexes.

DS export the seq file into datasets, works with them, and finally import data into seq file another time, but the export and the import works insequential mode (in case: seq - trx - seq)

if you use the option clear, the data is not partitioned and that is why the warning disappear.

[priyadarshikunal]

Hi All,

I'm facing an issue in my job. The job extracts data from an Oracle table (using Oracle Enterprise Stage) and a data set, does an outer join on the records from these and filters the records into 2 output sequential files (Exception records for each source) and a data set (Matching records from both sources). The filter conditions used against the links are

1) not(leftRec_ITEM=rightRec_ITEM and leftRec_LOC=rightRec_LOC) and leftRec_LOC=0
2) not(leftRec_ITEM=rightRec_ITEM and leftRec_LOC=rightRec_LOC) and rightRec_LOC=0
3) not(leftRec_ITEM=rightRec_ITEM and leftRec_LOC=rightRec_LOC) and rightRec_LOC=0

When I run the job, I'm getting 2 warnings as

1) main_program: The NLS character map <ISO-8859-1> is specified, but NLS_LANG is not set;
the NLS character map must be set to an ICU codepage which is equivalent tothe character set specified by NLS_LANG.

How can I resolve this as warnings are not allowed as per the requirements.

2) Sequential_File1: When checking operator: A sequential operator cannot preserve the partitioning of the parallel data set on input port 0
The Preserve Partitioning is set as Default in the Filter stage.
When I make it as Clear, this warning doesn't happen. Please throw some light on these issues. Also let me know how can I check whether a column value is NULL in the Where condition of the filter stage.

Thanks,
Sumesh

Three Questions in one thread

Don't do this from the next time to make future searches easy.


1) NLS character set is not proper, identify the correct character set and use it in your job. if not required put that off.

2) As the name suggests its a sequential stage. I/O for this stage is done sequentially, so your partitioning will not preserved at that stage,
so clearing the partioning will remove that warning.



3) use the constraint as you write in sql

abc is null

don't use link name

[sumesh.abraham]

Thanks for the replies. Setting Clear for partitioning solves the fiorst problem.

I checked the NLS section in the DS Administrator. It is set as ISO-8859-1.
I tried to set it as OFF at the filter stage, but the job shows the same warning. Does setting NLS_LANG will resolve the issue? If so, what should I do to set the same.

Thanks,
Sumesh

[priyadarshikunal]

Thanks for the replies. Setting Clear for partitioning solves the fiorst problem.

I checked the NLS section in the DS Administrator. It is set as ISO-8859-1.
I tried to set it as OFF at the filter stage, but the job shows the same warning. Does setting NLS_LANG will resolve the issue? If so, what should I do to set the same.

Thanks,
Sumesh

set that off in Oracle stage

[priyadarshikunal]

Thanks for the replies. Setting Clear for partitioning solves the fiorst problem.

I checked the NLS section in the DS Administrator. It is set as ISO-8859-1.
I tried to set it as OFF at the filter stage, but the job shows the same warning. Does setting NLS_LANG will resolve the issue? If so, what should I do to set the same.

Thanks,
Sumesh

Can you check the NLS_Character set used in oracle (database)
is it same as your datastage project default?

run this Query on your database

Code: Select all

select * from nls_database_parameters where parameter ='NLS_CHARACTERSET'

[sumesh.abraham]

I executed the query and it shows UTF-8.
I tried to set UTF-8 in the DS Administrator and in the filter stage I set it as Project Default(OFF). Still I get the warning.

[ray.wurlod]

Just set NLS_LANG. Oracle needs it.

[sumesh.abraham]

Please let me know how I can set NLS_LANG? Do I need to set in Oracle or DataStage.

Thanks,
Sumesh

[sumesh.abraham]

Please have a look at the settings below.

The settings in DS and Oracle are as follows.

DataStage
**********
In Job parameters section of the job:

Default map for stages: Project default(ISO8859-1)
Default collation locale for stages: Project(OFF)

NLS Map tab of Oracle Enterprise Stage:

Select category to set map for: NCHAR/NVARCHAR2
Map name for 'NCHAR/NVARCHAR2': Project default(ISO8859-1)

NLS section of DS Admin

Default map name under Server Maps: ISO8859-1

All values under Server Locales tab are US-ENGLISH
Default map name under Parallel Maps tab: ISO8859-1
Value for Collate under Parallel Locales tab: OFF

Oracle
******
NLS_CHARACTERSET parameter in the nls_database_parameters table has the value UTF8
NLS_LANGUAGE parameter in the nls_database_parameters table has the value AMERICAN

Please let me have your comments.

[ray.wurlod]

Wherever you set ORACLE_HOME is a good place also to set NLS_LANG. Since you set ORACLE_HOME in the dsenv script for DataStage processes, that would be a good place for NLS_LANG too.

Or you could set up NLS_LANG as an environment variable in the Administrator client, and therefore use different values in different projects.