Issue with high value of TIMESTAMP column



IBM's flagship relational database management system

Issue with high value of TIMESTAMP column

Postby pranav283 » Tue Mar 20, 2018 12:15 am

Hi,

We are making a solution where a CICS program PGM1 calls a series of programs to invoke a web service SRVC01. And based on the response of the service, we make a decision:
A. If the response is '0' - we want to INSERT a row with all columns into the table TABLE1 (to be created anew-cloned from an existing one).
B. But If the response is '0' - we want to INSERT a row with but this time, only with some columns into the table TABLE1.

The rationale is, two separate automatically started transaction (after every 5 minutes) AAA2 and AAA3 linked to CICS programs PGM2 and PGM3 respectively reads rows from DB2 TABLE1 in a way:
1. PGM2 should read rows that were INSERTed as a result of '0' response code from the web service.
1. PGM3 should read rows that were INSERTed as a result of non '0' response code from the web service.

Now, there are two ways to store the response code from the service into the table TABLE1:
1. I recommended - We add an extra column SERVICE_CODE and simply move the response code from SRVC01 to it, so that the rows can be picked by PGM2 if SRVC01 = '0' or picked up by PGM3 if SRVC01 NOT = '0'.
2. Senior recommended - there exists a TIMESTAMP column. Senior suggested that rather than having an extra column in the table, we save the effort and update the TS column to be current TS if response is '0' and '9999-12-31 24:00:00.0' if resp is other than '0'. After that, PGM2 will FETCH rows where TS NOT = '9999-12-31 24:00:00.0' and PGM3 will FETCH rows where TS = '9999-12-31 24:00:00.0'.

I think the 2nd suggestion will work but sounds confusing to anyone who will analyse/work on this solution after that. Personally, just for saving the extra effort of asking DBA to include an extra column and having a working but misleading approach is not good.

Let the extra column (of one byte only) store the return code of the service and make things obvious and a lot easier to understand.

What is your suggestion? Does anyone think that PGM1- having to MOVE high value of TS and PGM2/PGM3 having to FETCH rows on the basis of such a value would create performance or any other problems?
pranav283
 
Posts: 47
Joined: Sat Aug 30, 2014 3:52 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Issue with high value of TIMESTAMP column

Postby prino » Tue Mar 20, 2018 2:36 pm

Never ever ever abuse data for purposes that have nothing to do with the original purpose of the data.

Want an example? See Keeping statistics - one way of doing it, and especially the section

Data format used by Prino's current program

The 'simple' format was used until the end of 1994. Due to the fact that Prino wanted to add some additional statistics to the output files, it was changed into something a bit more logical, although some people might find otherwise. (And they are right, it's a right-royal mess due to more additional requirements, and Prino would like to simplify some of the more esoteric uses of punctuation, but that's unlikely to happen any time soon as he has a list of additional tables he would like to add first)

Notes [4], [6], and [8] are the interesting ones...
Robert AH Prins
robert.ah.prins @ the.17+Gb.Google thingy
User avatar
prino
 
Posts: 635
Joined: Wed Mar 11, 2009 12:22 am
Location: Vilnius, Lithuania
Has thanked: 3 times
Been thanked: 28 times

Re: Issue with high value of TIMESTAMP column

Postby NicC » Wed Mar 21, 2018 7:10 pm

You should revisit your question. You say:
A. If the response is '0'...
B. But If the response is '0'
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Issue with high value of TIMESTAMP column

Postby pranav283 » Sun Mar 25, 2018 12:49 am

Sorry, I meant this..

A. If the response is '0' - we want to INSERT a row with all columns into the table TABLE1 (to be created anew-cloned from an existing one).
B. But If the response is '0' - we want to INSERT a row with but this time, only with some columns into the table TABLE1.
pranav283
 
Posts: 47
Joined: Sat Aug 30, 2014 3:52 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Issue with high value of TIMESTAMP column

Postby enrico-sorichetti » Sun Mar 25, 2018 12:54 am

logic implies that one of the two is NOT EQUAL ...
but, which one
You posted exactly a copy of the conditions of the starting post
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: Issue with high value of TIMESTAMP column

Postby pranav283 » Sun Mar 25, 2018 1:18 am

Aaaaahhh...
Meant this... :roll:

A. If the response is '0' - we want to INSERT a row with all columns into the table TABLE1 (to be created anew-cloned from an existing one).
B. But If the response is not '0' - we want to INSERT a row with but this time, only with some columns into the table TABLE1.
pranav283
 
Posts: 47
Joined: Sat Aug 30, 2014 3:52 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Issue with high value of TIMESTAMP column

Postby enrico-sorichetti » Sun Mar 25, 2018 1:28 am

I just reread the starting post
I wonder about the good judgement of You senior, he/she/it does not know about the KISS(*) principle
(*) Keep It Simple, Stupid

add a new column and forget about using a column meant for something else

updating a column and using the same in a later query ( whatever the column content )
should not - in general - cause any performance problems;
even if processing a timestamp will make DB2 ( IIRC ) carry on a conversion from external to internal format
something You could live with

in this case using a timestamp as a flag is just illogic and will confuse people
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post