duplicate record for key column in ODBC stage
Moderators: chulett, rschirm, roy
duplicate record for key column in ODBC stage
Hi All,
I am facing a problem while using ODBC stage in my job.
i am getting duplicate records while retrieving the data through ODBC stage for a primary key column in database.
In data base the column is the key column and i am checking the key option in ODBC stage.
I am getting the same value of the column after 14 records,and the issue is same when i am changing the order of the column also in ODBC stage.
Waiting for some useful inputs as i have to revert back to client with probable problem and soluntion for that ie. whether its a stage problem or Schema problem.
with regards,
sandeep
I am facing a problem while using ODBC stage in my job.
i am getting duplicate records while retrieving the data through ODBC stage for a primary key column in database.
In data base the column is the key column and i am checking the key option in ODBC stage.
I am getting the same value of the column after 14 records,and the issue is same when i am changing the order of the column also in ODBC stage.
Waiting for some useful inputs as i have to revert back to client with probable problem and soluntion for that ie. whether its a stage problem or Schema problem.
with regards,
sandeep
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
By definition duplicates are impossible in a primary key. Execute the following query in the source table:
If this returns any rows, castigate your DBA for failing to create a UNIQUE index on the primary key.
Code: Select all
SELECT Key, COUNT(*) FROM tablename GROUP BY Key HAVING COUNT(*) > 1;
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.
Ray, Thank yiu for your response. When I am executing the query in the database I am not getting any rows, meaning there are no duplicates in the table. But then, the odbc stage is giving me the same value for the PK after 40 rows, other column values are changing.
I dont know why this is happening, any thoughts?
I dont know why this is happening, any thoughts?
SELECT
CLMNT_ID,
SBSCR_ID,
CLMNT_FST_NM,
CLMNT_LST_NM,
DEPN_NBR,
TO_CHAR(CLMNT_BTH_DT,'YYYYMMDD') CLMNT_BTH_DT,
GDR_CD,
CLMNT_REL_CD,
DATA_SOURCE_CD,
MIDL_NM,
INDV_ID,
MBR_ID
FROM
Tablename
the above is the SQL query i am giving through User defined sql in ODBC stage.
CLMNT_ID is the KEy column in the database, but ODBC is giving me same duplicate records after around 40 distinct records.
CLMNT_ID,
SBSCR_ID,
CLMNT_FST_NM,
CLMNT_LST_NM,
DEPN_NBR,
TO_CHAR(CLMNT_BTH_DT,'YYYYMMDD') CLMNT_BTH_DT,
GDR_CD,
CLMNT_REL_CD,
DATA_SOURCE_CD,
MIDL_NM,
INDV_ID,
MBR_ID
FROM
Tablename
the above is the SQL query i am giving through User defined sql in ODBC stage.
CLMNT_ID is the KEy column in the database, but ODBC is giving me same duplicate records after around 40 distinct records.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Are you using Entire partitioning, by any chance?
Even if you're not, the ODBC Enterprise stage emulates parallelism by creating as many connections to the data source as there are partitions for it to run in. This explains the duplicates; you are running the same query on each connection.
Change the execution mode of the ODBC Enterprise stage to sequential and/or the partitioning algorithm (on the input of the downstream parallel stage) to Hash on the key.
Even if you're not, the ODBC Enterprise stage emulates parallelism by creating as many connections to the data source as there are partitions for it to run in. This explains the duplicates; you are running the same query on each connection.
Change the execution mode of the ODBC Enterprise stage to sequential and/or the partitioning algorithm (on the input of the downstream parallel stage) to Hash on the key.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: