Generate timestamp only if condition met.
Moderators: chulett, rschirm, roy
Generate timestamp only if condition met.
Hi Guys,
I have a situation. We have a column (exp_tp) that's the flag for the active record in the Datawarehouse (SCD 2). That column is of
Sql Type: Timestamp
Length : 26
Scale : 6
At the database level, that column is defaulted to 9999-12-31 23:59:59.999999.
Depending upon the incoming data i have to decide what goes into exp_tp column.
If the incoming timestamp is high 9s, i want to pass nothing so that the system generates 9999-12-31 23:59:59.999999.
If the incoming timestamp is not high 9s then i need to take that timestamp and load it into exp_ts
The problem
If the incoming timestamp is high 9s, i cannot pass 9999-12-31 23:59:59.999999 as that is the default value and the database will not accept that. Any other value, is accepted , even if its a nano second less , meaning 9999-12-31 23:59:59.999998 will be accepted but not 9999-12-31 23:59:59.999999
What i want to do
Is there a way where i can pass the value for exp_tp if my condition is true and not pass anything if my condition is not met so that the database can generate the high 9's ?
Want to add, if the sql type is changed to varchar, it accepts it. I have that to fall back upon, just want to know if i can achieve what i have described in "What i want to do".
I have a situation. We have a column (exp_tp) that's the flag for the active record in the Datawarehouse (SCD 2). That column is of
Sql Type: Timestamp
Length : 26
Scale : 6
At the database level, that column is defaulted to 9999-12-31 23:59:59.999999.
Depending upon the incoming data i have to decide what goes into exp_tp column.
If the incoming timestamp is high 9s, i want to pass nothing so that the system generates 9999-12-31 23:59:59.999999.
If the incoming timestamp is not high 9s then i need to take that timestamp and load it into exp_ts
The problem
If the incoming timestamp is high 9s, i cannot pass 9999-12-31 23:59:59.999999 as that is the default value and the database will not accept that. Any other value, is accepted , even if its a nano second less , meaning 9999-12-31 23:59:59.999998 will be accepted but not 9999-12-31 23:59:59.999999
What i want to do
Is there a way where i can pass the value for exp_tp if my condition is true and not pass anything if my condition is not met so that the database can generate the high 9's ?
Want to add, if the sql type is changed to varchar, it accepts it. I have that to fall back upon, just want to know if i can achieve what i have described in "What i want to do".
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You must pass a timestamp (or NULL) if you are passing the row. The only way you can avoid supplying a value is to generate an UPDATE in which the timestamp column is not mentioned, or to avoid passing the entire row.
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.
Well the column is not nullable. And it wont work if i supply NULL. The only way system generates timestamp is if nothing is passed for that column. I cant do that.
Well, thanks Ray for your input. I think i have to stick to my workaround.
Regards,
Well, thanks Ray for your input. I think i have to stick to my workaround.
Regards,
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Generate timestamp only if condition met.
Nothing, just clarifying how to trigger the default value on a required field.
What makes you say this? Why would the database care? (hint: it won't)DSguru2B wrote:If the incoming timestamp is high 9s, i cannot pass 9999-12-31 23:59:59.999999 as that is the default value and the database will not accept that.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
O yes it would Craig. Even i was dazzeled at first why it wasnt accepting a true high nine timestamp. After a little bit of research, i found that, that particular column had a default value of high nines and maybe thats the reason its not accepting it. As i mentioned earlier, if i send even a nano second less, it would gracefully accept it.
You are right, for the timestamp to be system generated i would requier DDL changes. Id rather stick to converting sql type to varchar for a handful of history tables that we have.
You are right, for the timestamp to be system generated i would requier DDL changes. Id rather stick to converting sql type to varchar for a handful of history tables that we have.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Actually, my point was around the issue of the 'default value' blocking sending that value in 'normally'... it doesn't. I have no idea why you can't send your 'high nines' time in directly but then you've never specified the actual error you get when you try to do so - or what your derivation or SQL looks like. That would help get to the bottom of this.
And I said you'd need to change your DML not your DDL. You've said you already have the 'system generated timestamp' as the default value in your field - correct? What DDL changes are you thinking would be required?
And I said you'd need to change your DML not your DDL. You've said you already have the 'system generated timestamp' as the default value in your field - correct? What DDL changes are you thinking would be required?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Sorry Craig, that was a typo, I meant DML changes.
The error message i am getting is
This is when the sql type is set to timestamp and high nines are sent in. And believe it or not, the default value is raising this error. I know its really odd.
And no i dont have the system generated timestamp in my field. When i say system generated, i mean the database generates it to high nines if i dont pass the column.
My Sql is generated for 'insert without clearing'. I am using the DB2 API stage.
The error message i am getting is
Code: Select all
[IBM][CLI Driver] CLI0114E An invalid datetime format was detected; that is, an invalid string representation or value was specified. SQLSTATE=22007
And no i dont have the system generated timestamp in my field. When i say system generated, i mean the database generates it to high nines if i dont pass the column.
My Sql is generated for 'insert without clearing'. I am using the DB2 API stage.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Can you have two separate links, one with an INSERT statement that lacks the timestamp column from its column list and VALUES list (used when null occurs) and the other that supplies a non-null 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.
Most definately. That is another option surely, and it will work. I just think changing the sql type from timestamp to varchar for this unique situation is much better. Having all the processing done in minimal stages.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Hi -
I assume that you fixed this but just to add some info. I am pretty sure it is a DataStage issue. Had the same problem a while ago. If you insert the value 2006-12-31-23.59.59.999999 via an sql tool it works fine but if you do it through DataStage you get an error - whenever the timestamps ends with 999999.
This eCase has been logged with IBM/Ascential :
Topic:
Rows rejected by DB2 server when inserting timestapms with time ending in 999999.
Product: DataStage
Release: 6.0.1
Date Entered: 2004-03-25
Fixed in Release:
Date Closed: 2005-08-17
Full Description:
Customer is experiencing timestamp problem when insertinf rows into db2 via DSDB2 plug-in. Where a row being inserted contains a timestamp
ending in all 9's, eg. "2004-03-09 05:42:13.999999", this causes
datastage job to reject the row with the following warning message:
[IBM][CLI Driver] CLI0114E An invalid datetime format was detected; hat is, an invalid string representation or value was specified. SQLSTATE=22007 DTTM_STAMP_SEC = 2004-3-9 5:42:13.999
After further testing where the problem was reproduced, the only timestamp value that failed was the one with all 9's at the end. If it had all 9's with the last digit being an 8 (eg. "2004-03-09 05:42:13.999998"), it works ok. This timestamp is only causing a problem when inserted from a Datastage job. Could you please look into this.
Also, if column type is set to char the insert works OK, but customer says this may cause problems when reading /writing to db2.
Resolution:
Workaound Provided:
Setting the TIMESTAMP column to char casused the row to be inserted ok.
I assume that you fixed this but just to add some info. I am pretty sure it is a DataStage issue. Had the same problem a while ago. If you insert the value 2006-12-31-23.59.59.999999 via an sql tool it works fine but if you do it through DataStage you get an error - whenever the timestamps ends with 999999.
This eCase has been logged with IBM/Ascential :
Topic:
Rows rejected by DB2 server when inserting timestapms with time ending in 999999.
Product: DataStage
Release: 6.0.1
Date Entered: 2004-03-25
Fixed in Release:
Date Closed: 2005-08-17
Full Description:
Customer is experiencing timestamp problem when insertinf rows into db2 via DSDB2 plug-in. Where a row being inserted contains a timestamp
ending in all 9's, eg. "2004-03-09 05:42:13.999999", this causes
datastage job to reject the row with the following warning message:
[IBM][CLI Driver] CLI0114E An invalid datetime format was detected; hat is, an invalid string representation or value was specified. SQLSTATE=22007 DTTM_STAMP_SEC = 2004-3-9 5:42:13.999
After further testing where the problem was reproduced, the only timestamp value that failed was the one with all 9's at the end. If it had all 9's with the last digit being an 8 (eg. "2004-03-09 05:42:13.999998"), it works ok. This timestamp is only causing a problem when inserted from a Datastage job. Could you please look into this.
Also, if column type is set to char the insert works OK, but customer says this may cause problems when reading /writing to db2.
Resolution:
Workaound Provided:
Setting the TIMESTAMP column to char casused the row to be inserted ok.
Ok. So it is a bug, and still is. Hmmm, well thanks for sharing that with me, as i had no idea that it was a bug. I was under the impression that since its set up as a default date, its acting up. Anywho, thanks for the info.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.