Page 1 of 1
Cannot preserve the partitioning of the parallel data set
Posted: Mon Feb 04, 2008 9:53 am
by 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
Re: Cannot preserve the partitioning of the parallel data se
Posted: Mon Feb 04, 2008 10:19 am
by 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.
Re: Cannot preserve the partitioning of the parallel data se
Posted: Mon Feb 04, 2008 10:38 am
by priyadarshikunal
sumesh.abraham wrote: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
Posted: Mon Feb 04, 2008 11:23 am
by 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
Posted: Mon Feb 04, 2008 11:32 am
by priyadarshikunal
sumesh.abraham wrote: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
Posted: Mon Feb 04, 2008 11:34 am
by priyadarshikunal
sumesh.abraham wrote: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'
Posted: Mon Feb 04, 2008 11:51 am
by 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.
Posted: Mon Feb 04, 2008 5:10 pm
by ray.wurlod
Just set NLS_LANG. Oracle needs it.
Posted: Mon Feb 04, 2008 11:36 pm
by sumesh.abraham
Please let me know how I can set NLS_LANG? Do I need to set in Oracle or DataStage.
Thanks,
Sumesh
Posted: Tue Feb 05, 2008 12:19 am
by 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.
Posted: Tue Feb 05, 2008 3:34 am
by 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.