Hi,
I have 2 jobs, one is a server job (Job S) and another is a parallel job (Job P). Both are doing only extraction from an ODBC source and both are built the same way. The problem is that, the server Job S returns value correctly for ColumnValue1 = 834 (Decimal,length = 13) but the same parallel Job P returns a wrong value for ColumnValue1 = 222 (Decimal,length = 13).
Is there any reasons why it is returning a wrong value for parallel job? The NLS settings for both jobs are the same. Even when I tried to extract in Job P, setting the where clause to select only ColumnValue1=834, the results that I viewed is still 222.
Please help. Anyone can explain what's the cause of this and its solutions? Thankss
Server Job and Parallel Job returning different results
Moderators: chulett, rschirm, roy
-
Daddy Doma
- Premium Member
- Posts: 62
- Joined: Tue Jun 14, 2005 7:17 pm
- Location: Australia
- Contact:
Let me ask some stupid questions:
1. Are you connecting to the same ODBC source in both the server and parallel jobs?
1b. If not, have you double, triple checked that the data is the same in each database?
2. What is your job design?
2b. Is there anything in the parallel job that could change the value of ColumnValue1, i.e. a Transformer or Aggregation stage?
3. Where/how are you checking ColumnValue1 in each job? Are you using View data or outputting to a sequential file or dataset?
1. Are you connecting to the same ODBC source in both the server and parallel jobs?
1b. If not, have you double, triple checked that the data is the same in each database?
2. What is your job design?
2b. Is there anything in the parallel job that could change the value of ColumnValue1, i.e. a Transformer or Aggregation stage?
3. Where/how are you checking ColumnValue1 in each job? Are you using View data or outputting to a sequential file or dataset?
When you know that you are destined for greatness by virtue of your mutant heritage it is difficult to apply yourself to normal life. Why waste the effort when you know that your potential is so tremendous?
Hi my saviour 
Please see my reply in blue below
1. Are you connecting to the same ODBC source in both the server and parallel jobs?
Yes i m connecting both server and parallel jobs to the same ODBC source
1b. If not, have you double, triple checked that the data is the same in each database?
I have checked the data like n-th times, and its the same data that I am tryin to retrieve
2. What is your job design?
2b. Is there anything in the parallel job that could change the value of ColumnValue1, i.e. a Transformer or Aggregation stage?
None at all, cos I am just trying to do only extraction, there is no other stages; I viewed the data from the ODBC source itself using the "View Data" function in the ODBC stage
3. Where/how are you checking ColumnValue1 in each job? Are you using View data or outputting to a sequential file or dataset?
Same as 2b above, I am using the "View Data" function.
Any idea what went wrong? The NLS is the same in both jobs, so I couldnt figure out why the data is extracted as a another value in the parallel job. Some how the data got converted when it is retrieved from the ODBC source. But it works fine in a server job
Please see my reply in blue below
1. Are you connecting to the same ODBC source in both the server and parallel jobs?
Yes i m connecting both server and parallel jobs to the same ODBC source
1b. If not, have you double, triple checked that the data is the same in each database?
I have checked the data like n-th times, and its the same data that I am tryin to retrieve
2. What is your job design?
2b. Is there anything in the parallel job that could change the value of ColumnValue1, i.e. a Transformer or Aggregation stage?
None at all, cos I am just trying to do only extraction, there is no other stages; I viewed the data from the ODBC source itself using the "View Data" function in the ODBC stage
3. Where/how are you checking ColumnValue1 in each job? Are you using View data or outputting to a sequential file or dataset?
Same as 2b above, I am using the "View Data" function.
Any idea what went wrong? The NLS is the same in both jobs, so I couldnt figure out why the data is extracted as a another value in the parallel job. Some how the data got converted when it is retrieved from the ODBC source. But it works fine in a server job
-
Daddy Doma
- Premium Member
- Posts: 62
- Joined: Tue Jun 14, 2005 7:17 pm
- Location: Australia
- Contact:
What are the column definitions/data types for ColumnValue1 in the two different jobs? Are they the same?
Are you using custom SQL or Auto-generated SQL in the P Job?
If the P job is still returning a record when you put in the Where clause, it implies to me that the record is being found in the database correctly but is getting transformed by DataStage along the way.
Have you tried copying the SQL that the P Job ODBC stage generates and running it against the database itself? I.e. do not use View Data but Browse the database directly.
Are you using custom SQL or Auto-generated SQL in the P Job?
If the P job is still returning a record when you put in the Where clause, it implies to me that the record is being found in the database correctly but is getting transformed by DataStage along the way.
Have you tried copying the SQL that the P Job ODBC stage generates and running it against the database itself? I.e. do not use View Data but Browse the database directly.
When you know that you are destined for greatness by virtue of your mutant heritage it is difficult to apply yourself to normal life. Why waste the effort when you know that your potential is so tremendous?
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Are you absolutely sure that you're looking at the same row? The database server will deliver rows in whatever order it happens to find them, unless you specify (a) a single key value in a WHERE clause, (b) an ORDER BY clause, or (c) a GROUP BY clause.
If you have a reproducible case that proves that View Data does not display the data properly then you have a bug report to make through your support provider. Have you viewed the OSH that View Data generates? Have you run View Data with APT_DUMP_SCORE set to True?
If you have a reproducible case that proves that View Data does not display the data properly then you have a bug report to make through your support provider. Have you viewed the OSH that View Data generates? Have you run View Data with APT_DUMP_SCORE set to True?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi all,
You guys lead me and my colleagues to finally find the cause of the problem. Both the Parallel job and Server job design is exactly the same.
While trying to check the database records and its settings, my colleague found the culprit. It has something to do with MS SQL Server little-ENDIAN and big-ENDIAN thingy; some conversion error due to settings it seems. Thats the reason why data coming from ODBC connecting to MS SQL Server was returning different values.
You guys been a big help, we wont be able to find the problem if we keep thinking its the Datastage problem. Thanks thanks
You guys lead me and my colleagues to finally find the cause of the problem. Both the Parallel job and Server job design is exactly the same.
While trying to check the database records and its settings, my colleague found the culprit. It has something to do with MS SQL Server little-ENDIAN and big-ENDIAN thingy; some conversion error due to settings it seems. Thats the reason why data coming from ODBC connecting to MS SQL Server was returning different values.
You guys been a big help, we wont be able to find the problem if we keep thinking its the Datastage problem. Thanks thanks