|
View previous topic :: View next topic
|
| Author |
Message |
singhju
New User
Joined: 01 Dec 2010
Posts: 25
Location: Gurgaon
|
|
|
|
Hi,
In my one of the application, a lock is taken on a row in table by updating few timestamp columns and a LOCKED-BY column who tells about the user who has locked the row.
The issue is few times lock is successfully taken and few times row not found happens while in actual row is present in table. When I was analysing the code, I observed that the columns which the application is trying to update are defined as NULL while the SQL which is updating these columns is not using NuLL indicators. So is it the issue happening because NULL indicators are not used or there may be some other reason.
I hope I am clear with my words. If not please let me know. |
|
| Back to top |
|
 |
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
| Sorry. Not clear. |
|
| Back to top |
|
|
singhju
New User
Joined: 01 Dec 2010
Posts: 25
Location: Gurgaon
|
|
|
|
| My application is trying to update few columns in a table. These columns are defined as NULL. The SQL in application doesn't use Null indicators along with host variables while updating these columns. So will it cause any issue or not. |
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
No issues.
If any, we'll resolve it. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
It may help if you show the sql that returns a "not found" and the content of the row that actually does exist.
If db2 says the row is not there, i trust db2, so the query must have a problem. Show which columns have the NULL indicator set.
| Quote: |
| I observed that the columns which the application is trying to update are defined as NULL |
How is this defined? I don't recall seeing a column defined as NULL. |
|
| Back to top |
|
|
singhju
New User
Joined: 01 Dec 2010
Posts: 25
Location: Gurgaon
|
|
|
|
Hi,
There is one table TVFPAYI having below columns:
PI_ID DECIMAL(11,0) NOT NULL
LOCKED_BY VARCHAR(40)
LOCK_TAKEN_TS TIMESTAMP
LOCK_EXPIRES_TS TIMESTAMP
PI_ID is the primary key in table.
A row with PI_ID = 32973 is present in table where LOCKED_BY is USER-ID say user1 and LOCK_TAKEN_TS = some past time, LOCK-EXPIRES_TS is 11 minutes more than lock_taken_ts.
Now we are trying to update LOCK_EXPIRES_TS to further 11 minutes. We are calculating WS-timestamp = Current timestamp + 11 minutes. Moving User-id to LOCKED-BY-TEXT and setting the length of host variable.
SQL is
UPDATE TVFPAYI
SET LOCK_EXPIRES_TS = :WS-TIMESTAMP
WHERE PI_ID =  I-ID.TVFPAYI
AND LOCKED_BY = :LOCKED-BY.TVFPAYI
AND LOCK_TAKEN_TS < CURRENT TIMESTAMP
AND :WS-TIMESTAMP > LOCK_EXPIRES_TS
Please note that null indicators are not used along with host variables of columns LOCKED_BY and LOCK_EXPIRES_TS.
We are using this SQL for extending lock taken by 11 minutes multiple times and it runs fine for say 8 times out of 10 and fails 2 times. Does this inconsistency may happen due to absence of null indicators. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
You have posted what you believe we need. You need to post the requested data. . . I've expanded a bit on what might help. . .
I suspect the the host variable(s) have incorrect values or the row does not contain what you believe it contains. When the "not found" condition happens, you need to re-read the row using only the PI_ID and show all of the columns values as well as the corresponding host variabler values.
I know of no way that the same query would work 8 of 10 attempts because of the NULL indicator(s). |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
this is a good example of why code should be enclosed by CODE tags.
this:
WHERE PI_ID = I-ID.TVFPAYI
versus this:
| Code: |
| WHERE PI_ID = :PI-ID.TVFPAYI |
| Quote: |
| We are calculating WS-timestamp = Current timestamp + 11 minutes. |
as Dick said, you are probably getting a -180 or -181
we have no idea since you did not bother to tell us or do you even know?
i would suggest the following:
| Code: |
UPDATE TVFPAYI
SET LOCK_EXPIRES_TS = CURRENT TIMESTAMP + 11 MINUTES
WHERE PI_ID = icon_razz.gifI-ID.TVFPAYI
AND LOCKED_BY = :LOCKED-BY.TVFPAYI
AND LOCK_TAKEN_TS < CURRENT TIMESTAMP
AND LOCK_EXPIRES_TS < CURRENT TIMESTAMP + 11 MINUTES
|
and you could do away with your host variables,
which we suspect are not being populated with the correct values anyway.
or if you are wasting an sql call to populate WS-timestamp,
you can remove the SQL and save 1 CALL.
other consideration.
why not have an unlock function which would set the two lock TS to null. that way, you sql looking for an unlocked situation would not require a calculation?
having a lock expires column tells me the design sorta sucks,
since nobody knows when to unlock.
and who picked 11 minutes?
AND LAST BUT NOT LEAST
The only time you would need null indicators,
is when you set the column null |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
major correction to my previous post:
| Quote: |
AND LAST BUT NOT LEAST
The only time you would need null indicators,
is when you set the column null
|
AND LAST BUT NOT LEAST
The only time you would need null indicators,
is when you set the column null or attempt to populate host variables with a select |
|
| Back to top |
|
|
singhju
New User
Joined: 01 Dec 2010
Posts: 25
Location: Gurgaon
|
|
|
|
Thanks dbzTHEdinosauer.
I have got my answer from your previous post
| Quote: |
AND LAST BUT NOT LEAST
The only time you would need null indicators,
is when you set the column null or attempt to populate host variables with a select
|
This is what I was looking for. Now I can be sure that problem is not with SQL even if we don't use Null indicators while updating columns but somewhere else in the code which I need to find out.
Will let you know once I found that. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
as long as the update is not an attempt to set the column to null,
yes.
and when you say
| Quote: |
| ay 8 times out of 10 and fails 2 times |
what do you mean it fails?
you have yet to explain that statement.
were you to provide that information,
we maybe able to provide guidance and save you time. |
|
| Back to top |
|
|
singhju
New User
Joined: 01 Dec 2010
Posts: 25
Location: Gurgaon
|
|
|
|
The meaning of statement
| Quote: |
| ay 8 times out of 10 and fails 2 times |
was that SQl fails with row not found (SQLCODE = +100).
I just wanted to say that most of the time it works and fails intermittently with Row not found.
Now I have found the root cause of this issue.
| Code: |
D005-SET-EXPIRE-TIME SECTION.
*-----------------------------
******************************************************************
* Calculate Expiration Time (Current time + Expiration Interval)
******************************************************************
* -- Get Interval Period in Seconds
MOVE LOCK-EXPIRES-INTERVAL OF LDATAI-INPUT-DATA
TO WS-EXPN-INTV
COMPUTE WS-INTV-SECS = (WS-EXPN-INTV-HH * 3600)
+ (WS-EXPN-INTV-MM * 60)
+ WS-EXPN-INTV-SS
END-COMPUTE
* -- Get Current Time in Seconds + Interval Period
MOVE FUNCTION CURRENT-DATE
TO WS-COBOL-DATETIME-FMT
COMPUTE WS-CD-SECS = (WS-CURR-TIME-HH * 3600)
+ (WS-CURR-TIME-MM * 60)
+ WS-CURR-TIME-SS
+ WS-INTV-SECS
END-COMPUTE
* -- Get Expiration Time in HHMMSSTH
MOVE WS-CURR-TIME-SSTH TO WS-EXPN-TIME-SSTH
DIVIDE 60 INTO WS-CD-SECS
GIVING WS-CD-SECS
REMAINDER WS-EXPN-TIME-SS
DIVIDE 60 INTO WS-CD-SECS
GIVING WS-EXPN-TIME-HH
REMAINDER WS-EXPN-TIME-MM
* -- Check if Day needs to be incremented
IF WS-EXPN-TIME-HH > 23
* -- -- Determine no of days to increase by (should be only 1)
MOVE 0 TO WS-DAY-ADJ
PERFORM UNTIL WS-EXPN-TIME-HH < 24
SUBTRACT 24 FROM WS-EXPN-TIME-HH
ADD 1 TO WS-DAY-ADJ
END-PERFORM
* -- -- Get Integer Date
COMPUTE WS-CURDAT-I = FUNCTION
INTEGER-OF-DATE (WS-CURR-DATE)
END-COMPUTE
* -- -- Adjust Integer
COMPUTE WS-CURDAT-I = WS-CURDAT-I
+ WS-DAY-ADJ
END-COMPUTE
* -- -- Convert Integer back to YYYYMMDD format
COMPUTE WS-EXPN-DATE = FUNCTION
DATE-OF-INTEGER (WS-CURDAT-I)
END-COMPUTE
ELSE
* -- -- Its the same day as now
MOVE WS-CURR-DATE
TO WS-EXPN-DATE
END-IF
* -- Compose DB2 Timestamp for Lock Expiration
STRING
WS-EXPN-DATE(1:4) '-' WS-EXPN-DATE(5:2) '-'
WS-EXPN-DATE(7:2) '-'
WS-EXPN-TIME-HH '.' WS-EXPN-TIME-MM '.'
WS-EXPN-TIME-SS '.' WS-EXPN-TIME-SSTH '0000'
DELIMITED BY SIZE
INTO WS-EXPN-TMSTAMP
END-STRING
MOVE WS-EXPN-TMSTAMP TO LOCK-EXPIRES-TS OF
LDATAO-RETURNED-DATA
.
D005-EXIT. EXIT.
|
As you can see from above piece of code, a time interval is passed from Java application which is always 001100 (hardcoded in Java) and in Mainframe side we calculate the next lock expire timestamp by adding that interval to current timestamp.
Here the last four digits of new timestamp are hardcoded as 0000 so if we have a extended a lock once and again tries to extend a lock just within a fraction of seconds, the new timestamp will be equal to the last one updated (because of four 0000 in timestamp) and hence the below condition in SQL fails.
| Code: |
AND :WS-TIMESTAMP > LOCK_EXPIRES_TS
|
So now I will replace the COBOL logic with DB2 timestamp calculation. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| good detective work |
|
| Back to top |
|
|
|
|
