Help with "timestamp" for OCI-9
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
If PROCESS_TIMESTAMP is the column that the modification was applied on. Then,
How is the column "PROCESS_TIMESTAMP" defined in your target?
Is it a key or have a unique constraint on it? I mean in your Oracle DB.
How is the column "PROCESS_TIMESTAMP" defined in your target?
Is it a key or have a unique constraint on it? I mean in your Oracle DB.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Any clue as of why this is happening?
I tried changing the key of the oracle table but it dosent work. The key previously was the MACRO "DSJobStartTimeStamp" I'v chosen this one because the job start time is never the same usually.
Now i have changed it to the current time stamp using OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS") but the same warning pops up "Unique Constraint Violated".
Am I missing something here ?..
I tried changing the key of the oracle table but it dosent work. The key previously was the MACRO "DSJobStartTimeStamp" I'v chosen this one because the job start time is never the same usually.
Now i have changed it to the current time stamp using OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS") but the same warning pops up "Unique Constraint Violated".
Am I missing something here ?..
Any clue as of why this is happening?
I tried changing the key of the oracle table but it dosent work. The key previously was the MACRO "DSJobStartTimeStamp" I'v chosen this one because the job start time is never the same usually.
Now i have changed it to the current time stamp using OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS") but the same warning pops up "Unique Constraint Violated".
Am I missing something here ?..
I tried changing the key of the oracle table but it dosent work. The key previously was the MACRO "DSJobStartTimeStamp" I'v chosen this one because the job start time is never the same usually.
Now i have changed it to the current time stamp using OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS") but the same warning pops up "Unique Constraint Violated".
Am I missing something here ?..
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Ofcourse, it is a "UNIQUE KEY VIOLATON".
DSJobStartTimestamp is the timestamp at the start of a dsjob run and will be the same for the all the records that are being processed by the job for that particular run.
Whale.
DSJobStartTimestamp is the timestamp at the start of a dsjob run and will be the same for the all the records that are being processed by the job for that particular run.
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Well !
You got me !.
Can you please post your target table definiton and the error messages from the director log?
Whale.
![Smile :)](./images/smilies/icon_smile.gif)
Can you please post your target table definiton and the error messages from the director log?
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
beaditya ,
get rid of the index on that particular field.
If it is not a primary key and you had index on it then it is fine for you to remove the index on that particular column .
If you have access to TOAD or other Oracle query tool you can do it .
If not involve DBA to get rid of the index on column .
get rid of the index on that particular field.
If it is not a primary key and you had index on it then it is fine for you to remove the index on that particular column .
If you have access to TOAD or other Oracle query tool you can do it .
If not involve DBA to get rid of the index on column .
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Without prior deletion you can only ever INSERT a particular key once into an Oracle table that has a primary key defined.
Perhaps you need to revisit the design of that table if there are to be more than one run with the same process date. Or maybe you're just not generating the seconds part of the timestamp.
Perhaps you need to revisit the design of that table if there are to be more than one run with the same process date. Or maybe you're just not generating the seconds part of the timestamp.
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.
@ PADDU,
That field is not the primary key for the table and as of now but it was marked as the key when I loaded the table definition. There were 4 keys when loaded the table definition but we didnt want them as keys so I changed the "KEY" option to "NO" for all of them and I use a column which has current "date and timestamp" as a derivation as a key for that table now just to keep it unique.
I have no idea abt the INDEX you are talkin about. I do not have permissions to change anything on the database so if you are sure abt the INDEX problem I will escalate the situation to the DBA.
Thanks
That field is not the primary key for the table and as of now but it was marked as the key when I loaded the table definition. There were 4 keys when loaded the table definition but we didnt want them as keys so I changed the "KEY" option to "NO" for all of them and I use a column which has current "date and timestamp" as a derivation as a key for that table now just to keep it unique.
I have no idea abt the INDEX you are talkin about. I do not have permissions to change anything on the database so if you are sure abt the INDEX problem I will escalate the situation to the DBA.
Thanks
![Confused :?](./images/smilies/icon_confused.gif)
Can the two of you get together and start a single new thread where your remaining issue is clearly defined now that all the other silliness is out of the way? Sheesh.
ps. Do not escalate this to your DBA. This is your issue, not theirs.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
@Chulett ... U got us
... We work on the same team.... just on different jobs and locations, and thats why u guys have 2 confusing threads !
Yeah, we're workin on it as of now and NO, we didnt escalate the situation to the DBA.... and YES, i think we have figured out a solution .... will keep you posted when we get somewhere with this ! :D
![Wink :wink:](./images/smilies/icon_wink.gif)
Yeah, we're workin on it as of now and NO, we didnt escalate the situation to the DBA.... and YES, i think we have figured out a solution .... will keep you posted when we get somewhere with this ! :D
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune
The above error is due to the Primary key voilation. U need to check the key combination of the target table. More than one rows are having the same key combinationbeaditya wrote: TESTBMC2..Transformer_11: ORA-00001: unique constraint (ETQ.PK_VALIDATION_VALUES) violated
The table i'm inserting into is "ETQ.VALIDATION_VALUES"
SMB
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune
If u are inserting only one record in the target table for log purpose the its better to have a update action of 'Update Existing rows only' instead of any other optionbeaditya wrote:There is only 1 record being inserted. We are inserting a row into a table everytime we run a specific job. It kinda leaves a log stating it has been when it was run.
SMB