Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW

Carlos Villalva

Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW

Hi!

We are a issue in our database DB2 11.1 with a SELECT ... FOR UPDATE which is executed many times by second and it is generating many locks and the applications hangs.

SQL_TEXT:
SELECT flag1,flag2,flag3,status1,status2,status3,status4,status5,status_ext,device_fitness,cassette_fitness,configid,last_cmdtype,last_txntype,oar_screen,lastmsg_time,trace,tpdu,emv_identifier,language,pcode,respcode,acct_num,acct_num2,tvn,luno,cap_date,msg_coord_num,oar_line,model_specific,node, cassette_dispense, statetablename, statetablever, fault, severity, availdate, availtime,cmdmbid, balancerid, site_id, shclog_id, last_modify_dttime FROM atmdevicestate WHERE institutionid = ? and group_name = ? and unit = ? FOR UPDATE


The ATMDEVICESTATE table only has 1063 rows and two indexes:

INDEX ATMDEVICESTATE_1 ("LASTMSG_TIME" ASC,"GROUP_NAME" ASC,"UNIT" ASC) COMPRESS YES
UNIQUE INDEX ATMDEVICESTATE_IX ("INSTITUTIONID" ASC, "GROUP_NAME" ASC,"UNIT" ASC) COMPRESS YES


Is there a way to prevent these locks generated by the application in the database by SELECT ... FOR UPDATE with some parameter in the instace/database or howto can avoid this locks ???

In the top 5 SQL by executions appears the sentences SELECT...FOR UPDATE, the UPDATE of CURSOR and SELECT, the tree for the same table ATMDEVICESTATE:

EXECUTIONS TIME_SECONDS TEXT
-------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4617270 0 SELECT flag1,flag2,flag3,status1,status2,status3,status4,status5,status_ext,device_fitness,cassette_fitness,configid,last_cmdtype,last_txntype,oar_screen,lastmsg_time,trace,tpdu,emv_identifier,language,pcode,respcode,acct_num,acct_num2,tvn,luno,cap_date,msg_coord_num,oar_line,model_specific,node, cassette_dispense, statetablename, statetablever, fault, severity, availdate, availtime,cmdmbid, balancerid, site_id, shclog_id, last_modify_dttime FROM atmdevicestate WHERE institutionid = ? and group_name = ? and unit = ? FOR UPDATE
4616826 0 UPDATE atmdevicestate SET flag1 = ?,flag2 = ?,flag3 = ?,status1 = ?,status2 = ?,status3 = ?,status4 = ?,status5 = ?,status_ext = ?,device_fitness = ?,cassette_fitness = ?,configid = ?,last_cmdtype = ?,last_txntype = ?,oar_screen = ?,lastmsg_time = ?,trace = ?,tpdu = ?,emv_identifier = ?,language = ?,pcode = ?,respcode = ?,acct_num = ?,acct_num2 = ?,tvn = ?,luno = ?,cap_date = ?,msg_coord_num = ?,oar_line = ?,model_specific = ?,node = ?, cassette_dispense = ?, statetablename = ?, statetablever = ?, fault = ?, severity = ?, availdate = ?, availtime = ?,cmdmbid = ?, balancerid = ?, site_id = ?, shclog_id = ?, last_modify_dttime = ? WHERE CURRENT OF SQL_CURSH200C4
3174610 0 SELECT flag1,flag2,flag3,status1,status2,status3,status4,status5,status_ext,device_fitness,cassette_fitness,configid,last_cmdtype,last_txntype,oar_screen,lastmsg_time,trace,tpdu,emv_identifier,language,pcode,respcode,acct_num,acct_num2,tvn,luno,cap_date,msg_coord_num,oar_line,model_specific,node, cassette_dispense, statetablename, statetablever, fault, severity, availdate, availtime,cmdmbid, balancerid, site_id, shclog_id, last_modify_dttime FROM atmdevicestate WHERE institutionid = ? and group_name = ? and unit = ?


We are think it's a programming issue, but the applications say than is a problem with the db2sync since his analysis they are observed high time in commits/checkpoints in the database

IBM team said there is no way to avoid this locks. We are check isolation level but we do not know what level of isolation is recommended to reduce/avoid blocking in the application side ???


-> The history file is less than 5 mb
-> Lock waits are not so high in the snapshots
-> Query execution times are good

 

IBM requires it to be executed at the moment the issue occurs:

-> db2mon.pl and db2fodc -hang full

I understand to detect something that could be tuned in the db2 engine.


We know that the application has worked well with Oracle and they did not have this Issue of locks.

The table ATMDEVICESTATE is the table that more OVERFLOWs and more UPDATE suffers, here statistics since the BD is reactivated at

Start Date Start Time
2018/06/05 02:25:15

TABLE_NAME PAGES OVERFLOWS
--------------- ----------- -----------
ATMDEVICESTATE 12 1,192,483


The Database page size = 4096 but the table was moved to tablespaces of 32K, nevertheless OVERFLOWs still appears at the top.

The ATMDEVICESTATE table only has 1063 rows with two indexes and has columns VARCHAR (10,32,50,64 and 256) we have considered rebuilding the table and change VARCHAR by CHAR, of course VARCHAR (256) changing it only by CHAR (255), we have monitored the column VARCHAR (256) and the maximum reached at the moment is 116 in length.

Do you think this could help ???, Any other idea ???

I will review the combination of FOR READ ONLY and USE AND KEEP UPDATE LOCKS together.

 

Bill Gallagher

Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Carlos Villalva)
I may be just throwing darts here, but a couple of random thoughts:

1) One of the columns that the UPDATE statement is updating is the LASTMSG_TIME column, which is the leading column in one of the indexes. So you could be experiencing lock contention on that index as well as the table.

2) I believe that LUW will choose the earliest index created as a clustering index if no indexes are defined specifically as clustering indexes. If the LASTMSG_TIME index is “older” than the unique index, and the value of LASTMSG_TIME is being changed by each update, that could exasperate things.

3) If you have that many overflows, then your rows are getting fragmented. How often do you reorg this table?

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

From: Carlos Villalva [mailto:[login to unmask email]
Sent: Friday, July 6, 2018 6:17 PM
To: [login to unmask email]
Subject: [DB2-L] - Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW


Hi!

We are a issue in our database DB2 11.1 with a SELECT ... FOR UPDATE which is executed many times by second and it is generating many locks and the applications hangs.

SQL_TEXT:
SELECT flag1,flag2,flag3,status1,status2,status3,status4,status5,status_ext,device_fitness,cassette_fitness,configid,last_cmdtype,last_txntype,oar_screen,lastmsg_time,trace,tpdu,emv_identifier,language,pcode,respcode,acct_num,acct_num2,tvn,luno,cap_date,msg_coord_num,oar_line,model_specific,node, cassette_dispense, statetablename, statetablever, fault, severity, availdate, availtime,cmdmbid, balancerid, site_id, shclog_id, last_modify_dttime FROM atmdevicestate WHERE institutionid = ? and group_name = ? and unit = ? FOR UPDATE

The ATMDEVICESTATE table only has 1063 rows and two indexes:

INDEX ATMDEVICESTATE_1 ("LASTMSG_TIME" ASC,"GROUP_NAME" ASC,"UNIT" ASC) COMPRESS YES
UNIQUE INDEX ATMDEVICESTATE_IX ("INSTITUTIONID" ASC, "GROUP_NAME" ASC,"UNIT" ASC) COMPRESS YES

Is there a way to prevent these locks generated by the application in the database by SELECT ... FOR UPDATE with some parameter in the instace/database or howto can avoid this locks ???

In the top 5 SQL by executions appears the sentences SELECT...FOR UPDATE, the UPDATE of CURSOR and SELECT, the tree for the same table ATMDEVICESTATE:

EXECUTIONS TIME_SECONDS TEXT
-------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4617270 0 SELECT flag1,flag2,flag3,status1,status2,status3,status4,status5,status_ext,device_fitness,cassette_fitness,configid,last_cmdtype,last_txntype,oar_screen,lastmsg_time,trace,tpdu,emv_identifier,language,pcode,respcode,acct_num,acct_num2,tvn,luno,cap_date,msg_coord_num,oar_line,model_specific,node, cassette_dispense, statetablename, statetablever, fault, severity, availdate, availtime,cmdmbid, balancerid, site_id, shclog_id, last_modify_dttime FROM atmdevicestate WHERE institutionid = ? and group_name = ? and unit = ? FOR UPDATE
4616826 0 UPDATE atmdevicestate SET flag1 = ?,flag2 = ?,flag3 = ?,status1 = ?,status2 = ?,status3 = ?,status4 = ?,status5 = ?,status_ext = ?,device_fitness = ?,cassette_fitness = ?,configid = ?,last_cmdtype = ?,last_txntype = ?,oar_screen = ?,lastmsg_time = ?,trace = ?,tpdu = ?,emv_identifier = ?,language = ?,pcode = ?,respcode = ?,acct_num = ?,acct_num2 = ?,tvn = ?,luno = ?,cap_date = ?,msg_coord_num = ?,oar_line = ?,model_specific = ?,node = ?, cassette_dispense = ?, statetablename = ?, statetablever = ?, fault = ?, severity = ?, availdate = ?, availtime = ?,cmdmbid = ?, balancerid = ?, site_id = ?, shclog_id = ?, last_modify_dttime = ? WHERE CURRENT OF SQL_CURSH200C4
3174610 0 SELECT flag1,flag2,flag3,status1,status2,status3,status4,status5,status_ext,device_fitness,cassette_fitness,configid,last_cmdtype,last_txntype,oar_screen,lastmsg_time,trace,tpdu,emv_identifier,language,pcode,respcode,acct_num,acct_num2,tvn,luno,cap_date,msg_coord_num,oar_line,model_specific,node, cassette_dispense, statetablename, statetablever, fault, severity, availdate, availtime,cmdmbid, balancerid, site_id, shclog_id, last_modify_dttime FROM atmdevicestate WHERE institutionid = ? and group_name = ? and unit = ?

We are think it's a programming issue, but the applications say than is a problem with the db2sync since his analysis they are observed high time in commits/checkpoints in the database

IBM team said there is no way to avoid this locks. We are check isolation level but we do not know what level of isolation is recommended to reduce/avoid blocking in the application side ???

-> The history file is less than 5 mb
-> Lock waits are not so high in the snapshots
-> Query execution times are good



IBM requires it to be executed at the moment the issue occurs:

-> db2mon.pl and db2fodc -hang full

I understand to detect something that could be tuned in the db2 engine.

We know that the application has worked well with Oracle and they did not have this Issue of locks.

The table ATMDEVICESTATE is the table that more OVERFLOWs and more UPDATE suffers, here statistics since the BD is reactivated at

Start Date Start Time
2018/06/05 02:25:15

TABLE_NAME PAGES OVERFLOWS
--------------- ----------- -----------
ATMDEVICESTATE 12 1,192,483

The Database page size = 4096 but the table was moved to tablespaces of 32K, nevertheless OVERFLOWs still appears at the top.

The ATMDEVICESTATE table only has 1063 rows with two indexes and has columns VARCHAR (10,32,50,64 and 256) we have considered rebuilding the table and change VARCHAR by CHAR, of course VARCHAR (256) changing it only by CHAR (255), we have monitored the column VARCHAR (256) and the maximum reached at the moment is 116 in length.

Do you think this could help ???, Any other idea ???

I will review the combination of FOR READ ONLY and USE AND KEEP UPDATE LOCKS together.



-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Isaac Yassin

Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Bill Gallagher)
Did you try to remove the "compress" attribute ?

*Isaac Yassin *
*IBM Gold Consultant*
*IBM Champion for Analytics #ibmchampion*
IBM Certified Solution Expert
IBM Certified System Administrator - DB2 10, 11, 12 for z/OS
IBM Certified Database Administrator - DB2 for z/OS 9, 10, 11
IBM Certified Database Administrator - DB2 LUW 10.1
IBM Certified Specialist - PureData System for Analytics v7.1
IDUG Israel RUG co-Chair



On Mon, Jul 9, 2018 at 2:36 PM Gallagher,Bill R <[login to unmask email]>
wrote:

> I may be just throwing darts here, but a couple of random thoughts:
>
> 1) One of the columns that the UPDATE statement is updating is the
> LASTMSG_TIME column, which is the leading column in one of the indexes. So
> you could be experiencing lock contention on that index as well as the
> table.
>
>
>
> 2) I believe that LUW will choose the earliest index created as a
> clustering index if no indexes are defined specifically as clustering
> indexes. If the LASTMSG_TIME index is “older” than the unique index, and
> the value of LASTMSG_TIME is being changed by each update, that could
> exasperate things.
>
>
>
> 3) If you have that many overflows, then your rows are getting
> fragmented. How often do you reorg this table?
>
>
>
> *Bill Gallagher *|* Senior Systems Engineer, DBA *|* Data Administration *
>
>
>
> *From:* Carlos Villalva [mailto:[login to unmask email]
> *Sent:* Friday, July 6, 2018 6:17 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
>
>
>
> Hi!
>
> We are a issue in our database DB2 11.1 with a SELECT ... FOR UPDATE which
> is executed many times by second and it is generating many locks and the
> applications hangs.
>
> SQL_TEXT:
> SELECT
> flag1,flag2,flag3,status1,status2,status3,status4,status5,status_ext,device_fitness,cassette_fitness,configid,last_cmdtype,last_txntype,oar_screen,lastmsg_time,trace,tpdu,emv_identifier,language,pcode,respcode,acct_num,acct_num2,tvn,luno,cap_date,msg_coord_num,oar_line,model_specific,node,
> cassette_dispense, statetablename, statetablever, fault, severity,
> availdate, availtime,cmdmbid, balancerid, site_id, shclog_id,
> last_modify_dttime FROM atmdevicestate WHERE institutionid = ? and
> group_name = ? and unit = ? FOR UPDATE
>
>
> The ATMDEVICESTATE table only has 1063 rows and two indexes:
>
> INDEX ATMDEVICESTATE_1 ("LASTMSG_TIME" ASC,"GROUP_NAME" ASC,"UNIT" ASC)
> COMPRESS YES
> UNIQUE INDEX ATMDEVICESTATE_IX ("INSTITUTIONID" ASC, "GROUP_NAME"
> ASC,"UNIT" ASC) COMPRESS YES
>
>
> Is there a way to prevent these locks generated by the application in the
> database by SELECT ... FOR UPDATE with some parameter in the
> instace/database or howto can avoid this locks ???
>
> In the top 5 SQL by executions appears the sentences SELECT...FOR UPDATE,
> the UPDATE of CURSOR and SELECT, the tree for the same table ATMDEVICESTATE:
>
> EXECUTIONS TIME_SECONDS TEXT
> -------------------- --------------------
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 4617270 0 SELECT
> flag1,flag2,flag3,status1,status2,status3,status4,status5,status_ext,device_fitness,cassette_fitness,configid,last_cmdtype,last_txntype,oar_screen,lastmsg_time,trace,tpdu,emv_identifier,language,pcode,respcode,acct_num,acct_num2,tvn,luno,cap_date,msg_coord_num,oar_line,model_specific,node,
> cassette_dispense, statetablename, statetablever, fault, severity,
> availdate, availtime,cmdmbid, balancerid, site_id, shclog_id,
> last_modify_dttime FROM atmdevicestate WHERE institutionid = ? and
> group_name = ? and unit = ? FOR UPDATE
> 4616826 0 UPDATE atmdevicestate SET flag1 = ?,flag2 = ?,flag3 = ?,status1
> = ?,status2 = ?,status3 = ?,status4 = ?,status5 = ?,status_ext =
> ?,device_fitness = ?,cassette_fitness = ?,configid = ?,last_cmdtype =
> ?,last_txntype = ?,oar_screen = ?,lastmsg_time = ?,trace = ?,tpdu =
> ?,emv_identifier = ?,language = ?,pcode = ?,respcode = ?,acct_num =
> ?,acct_num2 = ?,tvn = ?,luno = ?,cap_date = ?,msg_coord_num = ?,oar_line =
> ?,model_specific = ?,node = ?, cassette_dispense = ?, statetablename = ?,
> statetablever = ?, fault = ?, severity = ?, availdate = ?, availtime =
> ?,cmdmbid = ?, balancerid = ?, site_id = ?, shclog_id = ?,
> last_modify_dttime = ? WHERE CURRENT OF SQL_CURSH200C4
> 3174610 0 SELECT
> flag1,flag2,flag3,status1,status2,status3,status4,status5,status_ext,device_fitness,cassette_fitness,configid,last_cmdtype,last_txntype,oar_screen,lastmsg_time,trace,tpdu,emv_identifier,language,pcode,respcode,acct_num,acct_num2,tvn,luno,cap_date,msg_coord_num,oar_line,model_specific,node,
> cassette_dispense, statetablename, statetablever, fault, severity,
> availdate, availtime,cmdmbid, balancerid, site_id, shclog_id,
> last_modify_dttime FROM atmdevicestate WHERE institutionid = ? and
> group_name = ? and unit = ?
>
>
> We are think it's a programming issue, but the applications say than is a
> problem with the db2sync since his analysis they are observed high time in
> commits/checkpoints in the database
>
> IBM team said there is no way to avoid this locks. We are check isolation
> level but we do not know what level of isolation is recommended to
> reduce/avoid blocking in the application side ???
>
>
> -> The history file is less than 5 mb
> -> Lock waits are not so high in the snapshots
> -> Query execution times are good
>
>
>
> IBM requires it to be executed at the moment the issue occurs:
>
> -> db2mon.pl and db2fodc -hang full
>
> I understand to detect something that could be tuned in the db2 engine.
>
>
> We know that the application has worked well with Oracle and they did not
> have this Issue of locks.
>
> The table ATMDEVICESTATE is the table that more OVERFLOWs and more UPDATE
> suffers, here statistics since the BD is reactivated at
>
> Start Date Start Time
> 2018/06/05 02:25:15
>
> TABLE_NAME PAGES OVERFLOWS
> --------------- ----------- -----------
> ATMDEVICESTATE 12 1,192,483
>
>
> The Database page size = 4096 but the table was moved to tablespaces of
> 32K, nevertheless OVERFLOWs still appears at the top.
>
> The ATMDEVICESTATE table only has 1063 rows with two indexes and has
> columns VARCHAR (10,32,50,64 and 256) we have considered rebuilding the
> table and change VARCHAR by CHAR, of course VARCHAR (256) changing it only
> by CHAR (255), we have monitored the column VARCHAR (256) and the maximum
> reached at the moment is 116 in length.
>
> Do you think this could help ???, Any other idea ???
>
> I will review the combination of FOR READ ONLY and USE AND KEEP UPDATE
> LOCKS together.
>
>
>
>
> -----End Original Message-----
> ------------------------------
> This message (including any attachments) may contain confidential,
> proprietary, privileged and/or private information. The information is
> intended to be for the use of the individual or entity designated above. If
> you are not the intended recipient of this message, please notify the
> sender immediately, and delete the message and any attachments. Any
> disclosure, reproduction, distribution or other use of this message or any
> attachments by an individual or entity other than the intended recipient is
> prohibited.
>
> TRVDiscDefault::1201
> -----End Original Message-----
>

Dave Nance

Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Bill Gallagher)
Couple other items you might want to look at: 
Locksize on tablePCTFREE on index

David Nance 804-683-1507

On Monday, July 9, 2018, 6:36:12 AM CDT, Gallagher,Bill R <[login to unmask email]> wrote:


I may be just throwing darts here, but a couple of random thoughts:

1)  One of the columns that the UPDATE statement is updating is the LASTMSG_TIME column, which is the leading column in one of the indexes.  So you could be experiencing lock contention on that index as well as the table.

 

2)  I believe that LUW will choose the earliest index created as a clustering index if no indexes are defined specifically as clustering indexes.  If the LASTMSG_TIME index is “older” than the unique index, and the value of LASTMSG_TIME is being changed by each update, that could exasperate things.

 

3)  If you have that many overflows, then your rows are getting fragmented.  How often do you reorg this table? 

 

Bill Gallagher| Senior Systems Engineer, DBA| Data Administration

 

From: Carlos Villalva [mailto:[login to unmask email]
Sent: Friday, July 6, 2018 6:17 PM
To: [login to unmask email]
Subject: [DB2-L] - Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW

 

Hi!

We are a issue in our database DB2 11.1 with a SELECT ... FOR UPDATE which is executed many times by second and it is generating many locks and the applications hangs.

SQL_TEXT:
SELECT flag1,flag2,flag3,status1,status2,status3,status4,status5,status_ext,device_fitness,cassette_fitness,configid,last_cmdtype,last_txntype,oar_screen,lastmsg_time,trace,tpdu,emv_identifier,language,pcode,respcode,acct_num,acct_num2,tvn,luno,cap_date,msg_coord_num,oar_line,model_specific,node, cassette_dispense, statetablename, statetablever, fault, severity, availdate, availtime,cmdmbid, balancerid, site_id, shclog_id, last_modify_dttime FROM atmdevicestate WHERE institutionid = ? and group_name = ? and unit = ? FOR UPDATE


The ATMDEVICESTATE table only has 1063 rows and two indexes:

INDEX ATMDEVICESTATE_1 ("LASTMSG_TIME" ASC,"GROUP_NAME" ASC,"UNIT" ASC) COMPRESS YES
UNIQUE INDEX ATMDEVICESTATE_IX ("INSTITUTIONID" ASC, "GROUP_NAME" ASC,"UNIT" ASC) COMPRESS YES


Is there a way to prevent these locks generated by the application in the database by SELECT ... FOR UPDATE with some parameter in the instace/database or howto can avoid this locks ???

In the top 5 SQL by executions appears the sentences SELECT...FOR UPDATE, the UPDATE of CURSOR and SELECT, the tree for the same table ATMDEVICESTATE:

EXECUTIONS TIME_SECONDS TEXT
-------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4617270 0 SELECT flag1,flag2,flag3,status1,status2,status3,status4,status5,status_ext,device_fitness,cassette_fitness,configid,last_cmdtype,last_txntype,oar_screen,lastmsg_time,trace,tpdu,emv_identifier,language,pcode,respcode,acct_num,acct_num2,tvn,luno,cap_date,msg_coord_num,oar_line,model_specific,node, cassette_dispense, statetablename, statetablever, fault, severity, availdate, availtime,cmdmbid, balancerid, site_id, shclog_id, last_modify_dttime FROM atmdevicestate WHERE institutionid = ? and group_name = ? and unit = ? FOR UPDATE
4616826 0 UPDATE atmdevicestate SET flag1 = ?,flag2 = ?,flag3 = ?,status1 = ?,status2 = ?,status3 = ?,status4 = ?,status5 = ?,status_ext = ?,device_fitness = ?,cassette_fitness = ?,configid = ?,last_cmdtype = ?,last_txntype = ?,oar_screen = ?,lastmsg_time = ?,trace = ?,tpdu = ?,emv_identifier = ?,language = ?,pcode = ?,respcode = ?,acct_num = ?,acct_num2 = ?,tvn = ?,luno = ?,cap_date = ?,msg_coord_num = ?,oar_line = ?,model_specific = ?,node = ?, cassette_dispense = ?, statetablename = ?, statetablever = ?, fault = ?, severity = ?, availdate = ?, availtime = ?,cmdmbid = ?, balancerid = ?, site_id = ?, shclog_id = ?, last_modify_dttime = ? WHERE CURRENT OF SQL_CURSH200C4
3174610 0 SELECT flag1,flag2,flag3,status1,status2,status3,status4,status5,status_ext,device_fitness,cassette_fitness,configid,last_cmdtype,last_txntype,oar_screen,lastmsg_time,trace,tpdu,emv_identifier,language,pcode,respcode,acct_num,acct_num2,tvn,luno,cap_date,msg_coord_num,oar_line,model_specific,node, cassette_dispense, statetablename, statetablever, fault, severity, availdate, availtime,cmdmbid, balancerid, site_id, shclog_id, last_modify_dttime FROM atmdevicestate WHERE institutionid = ? and group_name = ? and unit = ?


We are think it's a programming issue, but the applications say than is a problem with the db2sync since his analysis they are observed high time in commits/checkpoints in the database

IBM team said there is no way to avoid this locks. We are check isolation level but we do not know what level of isolation is recommended to reduce/avoid blocking in the application side ???


-> The history file is less than 5 mb
-> Lock waits are not so high in the snapshots
-> Query execution times are good

 

IBM requires it to be executed at the moment the issue occurs:

-> db2mon.pl and db2fodc -hang full

I understand to detect something that could be tuned in the db2 engine.


We know that the application has worked well with Oracle and they did not have this Issue of locks.

The table ATMDEVICESTATE is the table that more OVERFLOWs and more UPDATE suffers, here statistics since the BD is reactivated at

Start Date Start Time
2018/06/05 02:25:15

TABLE_NAME PAGES OVERFLOWS
--------------- ----------- -----------
ATMDEVICESTATE 12 1,192,483


The Database page size = 4096 but the table was moved to tablespaces of 32K, nevertheless OVERFLOWs still appears at the top.

The ATMDEVICESTATE table only has 1063 rows with two indexes and has columns VARCHAR (10,32,50,64 and 256) we have considered rebuilding the table and change VARCHAR by CHAR, of course VARCHAR (256) changing it only by CHAR (255), we have monitored the column VARCHAR (256) and the maximum reached at the moment is 116 in length.

Do you think this could help ???, Any other idea ???

I will review the combination of FOR READ ONLY and USE AND KEEP UPDATE LOCKS together.

 

 
-----End Original Message-----This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Faster data refresh is here! The long waits and babysitting of unload/load jobs is over. Contact
ESAi to learn about BCV5 & XDM. Be a hero to users with fast on-demand test/QA data provisioning.See
http://www.ESAIGroup.com/idug



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Carlos Villalva

RE: Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Carlos Villalva)

Thanks !!!

 

1) In the SELECT ... FOR UPDATE and UPDATE statements ALL COLUMNS are updated and selected except one column NET_NAME VARCHAR(10)


3) This application is online 7x24, we reorg the table every moth

Carlos Villalva

RE: Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Isaac Yassin)

Thanks !!!

 

Did you try to remove the "compress" attribute ?

 

The issue is old and the indexes was without compress first.

The previous dba told me that the compress was activated because the optimizer performed SCAN by the SELECT ... FOR UPDATE

Carlos Villalva

RE: Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Dave Nance)

Thanks!!

 

I will review: Locksize on table / PCTFREE on index

Carlos Villalva

RE: Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Dave Nance)

The table suffers 450,000 Updates by day
PCTFREE = -1

LOCKSIZE
--------
R


The ATMDEVICESTATE table length is: 700 bytes
The tablespace of the table is 32K


==> OVERFLOW
SCHEMA TABLE_NAME PAGES OVERFLOWS
--------------------------------------------------------
SW_APP ATMDEVICESTATE 11 136145


The AVGROWSIZE is:
AVGROWSIZE
----------
332


Only PCTFREE for the table or too for the indexes ???


What value of PCTFREE do you recommend ???

Michael Hannan

RE: Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Carlos Villalva)

Major possible Issues:
1. Index Pseudo deletes may need cleanup regularly. Update of the Last Updated Timestamp will cause index entries to be deleted and reinserted. Avoid indexing heavily updated columns where possible.

2. Program needs to commit frequently to prevent other threads hanging, waiting for updated rows..

3. I don't think CHAR over VARCHAR will help.  

4. I don't like Index compression.

5. SELECT for UPDATE has to lock rows temporarily till DB2 moves to next row, so that other processes cannot concurrently attempt update. Only when row is actually updated does the lock change to exclusive and then remain till commit time.

6. Cluster sequence of the data rows might matter. I don't really have a problem with row level locking as long as not too many updates are attempted before commit.

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Carlos Villalva

RE: Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Michael Hannan)

Hi Michael Hannan!

3) I don't think CHAR over VARCHAR will help. =>  could explain why ???

Michael Hannan

RE: Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Carlos Villalva)

Carlos,

Let's put it the other way around. What advantages does CHAR have over VARCHAR? How can it help? I fail to see a benefit.

In Reply to Carlos Villalva:

Hi Michael Hannan!

3) I don't think CHAR over VARCHAR will help. =>  could explain why ???



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Carlos Villalva

RE: Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Michael Hannan)

Thanks Michael Hannan!!!


At the beginning the post I had explained:

"The table ATMDEVICESTATE is the table that more OVERFLOWs and more UPDATE suffers, here statistics since the BD is reactivated at

Start Date Start Time
2018/06/05 02:25:15

TABLE_NAME PAGES OVERFLOWS
--------------- ----------- -----------
ATMDEVICESTATE 12 1,192,483


The Database page size = 4096 but the table was moved to tablespaces of 32K, nevertheless OVERFLOWs still appears at the top."


===> This are current statistics:
Table Schema = SW_APP
Table Name = ATMDEVICESTATE
Table Type = User
Data Object Pages = 12
Index Object Pages = 6
Rows Read = 29893145
Rows Written = 7643571
Overflows = 2031873
Page Reorgs = 1085490

Page overflows occur when a VARCHAR column in a row has been updated which causes the row to use more space than it did before the update. If DB2 cannot insert the row on the page where it was originally written, DB2 will leave a pointer to the actual location where it writes the new row.  If page overflows occur frequently, then performance will degrade as it will take more time for DB2 to search the table.

With CHAR or PCTFREE in the table we will avoid OVERFLOW and PAGE REORGS and we will maintain good performance in the database.

These locks (SELECT ...FOR UPDATE) are there "by design". It means that this level of locking is expected and required. No way how to change it for FOR UPDATE clause.
There is no DB/instance parameter to change it, but the APP can try to rewrite the logic (level of isolation) but the APP does not want to change it !!!

Michael Hannan

RE: Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Carlos Villalva)

It never occurred to me to replace VARCHAR with CHAR to avoid updates making the row longer. Most tables are compressed anyway so we are used to rows that can change length. Of course I am more specialist in zOS. Normally we look for ways to limit the number of rows per page or provide freespace for the Update.

I never heard of anything so radical as changing VARCHAR to CHAR before.  A long CHAR is undesirable for indexes.

At least you have explained why. Never occurred to me.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Carlos Villalva

RE: Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Michael Hannan)

A long CHAR is undesirable for indexes.   OK, I will consider it

However, this table has 1000 records and does not show exponential growth.


I have a premise and for performance reasons, whenever it can be applied:

Tables with a lot DELETE/UPDATE => CHAR
Tables with only INSERT/SELECT => VARCHAR


Any other comments to attack the issue ???

Michael Hannan

RE: Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Carlos Villalva)

Normally when a table is so small but highly dynamic, people like to reorg it very frequently.

I would suggest at least daily (maybe more), and set a maximum number of rows per page if possible to provide for updates, and good freespace. Setting a limit on number of rows per page would overcome need for CHAR. I don't know the technique on your DB2 but on zOS is MAXROWS.

Meanwhile row level locking seems good.

In Reply to Carlos Villalva:

A long CHAR is undesirable for indexes.   OK, I will consider it

However, this table has 1000 records and does not show exponential growth.


I have a premise and for performance reasons, whenever it can be applied:

Tables with a lot DELETE/UPDATE => CHAR
Tables with only INSERT/SELECT => VARCHAR


Any other comments to attack the issue ???



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Carlos Villalva

RE: Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Michael Hannan)

Yes i agree with reorg very frequently, but this is a online application 7x24. So this is not possible.

We will review MAXROWS

Thanks !!!

Carlos Villalva

RE: Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Carlos Villalva)

Any comments on this variables to reduce the issue of locks ???

DB2_SKIPINSERTED=ON
DB2_EVALUNCOMMITTED=ON
DB2_SKIPDELETED=ON

Thanks

Philip Gunning

Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW
(in response to Carlos Villalva)
Collectively known as lock avoidance, have used for a long time, many, many years in OLTP….for complete details on each registry variable see the friendly Knowledge Center….



C:\Users\Administrator>db2set -all

[e] DB2PATH=C:\Program Files\IBM\SQLLIB

[i] DB2_STANDBY_ISO=UR

[i] DB2_CREATE_DB_ON_PATH=YES

[i] DB2_SKIPINSERTED=YES

[i] DB2_USE_ALTERNATE_PAGE_CLEANING=ON

[i] DB2_EVALUNCOMMITTED=YES

[i] DB2_SKIPDELETED=YES

[i] DB2INSTPROF=C:\ProgramData\IBM\DB2\DB2COPY1

[i] DB2COMM=TCPIP

[i] DB2_PARALLEL_IO=*

[i] DB2_OVERRIDE_BPF=1,1000000

[g] DB2_EXTSECURITY=YES

[g] DB2_COMMON_APP_DATA_PATH=C:\ProgramData

[g] DB2SYSTEM=CW-DB02

[g] DB2PATH=C:\Program Files\IBM\SQLLIB

[g] DB2ADMINSERVER=DB2DAS00



IBM Champion for Analytics

IBM Gold Consultant



Certified Information Systems Security Professional(CISSP)

Certification Number 539059

BS Comp Sci, MBA

Certified Advanced Db2 DBA v10.5

Certified Database Adminstrator, Db2 11.1

IBM Db2 LUW Support Page -- Db2 Support Portal https://www.ibm.com/mysupport/s/topic/0TO500000001fUNGAY/db2-linux-unix-and-windows?language=en_US&productId=01t50000004WmTk%20

Db2 Analytics Page https://www.ibm.com/analytics/data-management

Skype: DB2LUW

Twitter: DB2LUW

Direct +1.610.451.5801

IDUG DB2-L Hall of Fame

www.philipkgunning.com http://www.philipkgunning.com/AskMrDB2

IBM Business Partner

Microsoft Business Partner



From: Carlos Villalva <[login to unmask email]>
Sent: Monday, October 29, 2018 9:37 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Issue with a SELECT ... FOR UPDATE DB2 11.1 LUW



Any comments on this variables to reduce the issue of locks ???

DB2_SKIPINSERTED=ON
DB2_EVALUNCOMMITTED=ON
DB2_SKIPDELETED=ON

Thanks



-----End Original Message-----

Attachments

  • image001.png (17.4k)