IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Update columns without using NULL indicators


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
singhju

New User


Joined: 01 Dec 2010
Posts: 25
Location: Gurgaon

PostPosted: Tue Sep 20, 2011 1:17 pm
Reply with quote

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
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India

PostPosted: Tue Sep 20, 2011 1:30 pm
Reply with quote

Sorry. Not clear.
Back to top
View user's profile Send private message
singhju

New User


Joined: 01 Dec 2010
Posts: 25
Location: Gurgaon

PostPosted: Tue Sep 20, 2011 1:36 pm
Reply with quote

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
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India

PostPosted: Tue Sep 20, 2011 1:40 pm
Reply with quote

No issues.

If any, we'll resolve it.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Tue Sep 20, 2011 7:34 pm
Reply with quote

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
View user's profile Send private message
singhju

New User


Joined: 01 Dec 2010
Posts: 25
Location: Gurgaon

PostPosted: Tue Sep 20, 2011 9:54 pm
Reply with quote

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 = icon_razz.gifI-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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Tue Sep 20, 2011 11:00 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Sep 20, 2011 11:56 pm
Reply with quote

this is a good example of why code should be enclosed by CODE tags.

this:
WHERE PI_ID = icon_razz.gifI-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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Sep 21, 2011 12:23 am
Reply with quote

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
View user's profile Send private message
singhju

New User


Joined: 01 Dec 2010
Posts: 25
Location: Gurgaon

PostPosted: Wed Sep 21, 2011 7:27 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Sep 21, 2011 7:42 pm
Reply with quote

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
View user's profile Send private message
singhju

New User


Joined: 01 Dec 2010
Posts: 25
Location: Gurgaon

PostPosted: Thu Sep 22, 2011 8:47 am
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Sep 22, 2011 12:59 pm
Reply with quote

good detective work
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts how to eliminate null indicator value... DB2 7
No new posts Remote Unload of CLOB Columns DB2 6
No new posts Increase the number of columns in the... IBM Tools 3
No new posts Null values are considered in Total c... DFSORT/ICETOOL 6
No new posts DB2 Views with Multiple SQL & Col... DB2 8
Search our Forums:

Back to Top