How to continue a load activity over brief network down time

Harishkumar .Pathangay

How to continue a load activity over brief network down time

hi,

I have a db2 [11.1.3.3] database on server1. server1 also has a nfs mount point /var/nfs.

I load data to a table from /var/nfs/db2share/1gb.del

after I start load activity in db2 server itself [it is a local application performing the load], the nfs network went down very briefly for about 20 seconds, but it came back with in the next 10 seconds. the network down time was only less than 30 seconds. but still load utility is saying disk error occurred. it is not able to sense the availability of the network back again and continue the load automatically.

I can manually restart the load, but if there is any timeout parameter I can specify to influence db2 load utility so that it can load the data again even if the network was down briefly say 30 seconds. I do not want to restart load manually every time if there is mild network availability issues.

I tried, db2 registry variables - DB2IQTIME=120 and DB2RQTIME=180. but it did not work. I am not even sure that setting would help me in this situation. please let me know.

thanks,

harish pathangay

Harishkumar .Pathangay

RE: How to continue a load activity over brief network down time
(in response to Harishkumar .Pathangay)

hi,

any inputs?

thanks,

harish p

Ian Bjorhovde

How to continue a load activity over brief network down time
(in response to Harishkumar .Pathangay)
This is a system issue, not a database issue, so you should talk to your Linux system administrator.

You should check your filesystem’s settings for the ‘timeo’ and ‘retrans’ option.

Alternatively, you can use an NFS hard mount, which means that any I/O calls will wait forever and won’t timeout. This is probably not recommended if you have an unreliable network.


Ian Bjorhovde
IBM Gold Consultant



> On Feb 1, 2019, at 11:48 AM, Harishkumar .Pathangay <[login to unmask email]> wrote:
>
> hi,
>
> I have a db2 [11.1.3.3] database on server1. server1 also has a nfs mount point /var/nfs.
>
> I load data to a table from /var/nfs/db2share/1gb.del
>
> after I start load activity in db2 server itself [it is a local application performing the load], the nfs network went down very briefly for about 20 seconds, but it came back with in the next 10 seconds. the network down time was only less than 30 seconds. but still load utility is saying disk error occurred. it is not able to sense the availability of the network back again and continue the load automatically.
>
> I can manually restart the load, but if there is any timeout parameter I can specify to influence db2 load utility so that it can load the data again even if the network was down briefly say 30 seconds. I do not want to restart load manually every time if there is mild network availability issues.
>
> I tried, db2 registry variables - DB2IQTIME=120 and https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.regvars.doc/doc/r0005662.html?view=kc#r0005662__CL_DB2IQTIME DB2RQTIME https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.regvars.doc/doc/r0005662.html?view=kc#r0005662__CL_DB2RQTIME =180. but it did not work. I am not even sure that setting would help me in this situation. please let me know.
>
> thanks,
>
> harish pathangay
>
>
> Site Links: View post online https://www.idug.org/p/fo/st/?post=188130&anc=p188130#p188130 View mailing list online https://www.idug.org/p/fo/si/?topic=19 Start new thread via email <mailto:[login to unmask email]> Unsubscribe from this mailing list <mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription https://www.idug.org/p/us/to
>
> This email has been sent to: [login to unmask email]
> ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
> BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
> http://www.ESAIGroup.com/idug 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 http://www.idug.org/p/cm/ld/fid=2

Harishkumar .Pathangay

How to continue a load activity over brief network downtime
(in response to Ian Bjorhovde)
Hi Ian,
Thanks for the suggestion. Will try these settings and let you know.
Thanks again,
Harish Pathangay

Sent from Mail for Windows 10

From: Ian Bjorhovde
Sent: 05 February 2019 00:23
To: [login to unmask email]
Subject: [DB2-L] - RE: How to continue a load activity over brief network downtime

This is a system issue, not a database issue, so you should talk to your Linux system administrator.  

You should check your filesystem’s settings for the ‘timeo’ and ‘retrans’ option.

Alternatively, you can use an NFS hard mount, which means that any I/O calls will wait forever and won’t timeout.  This is probably not recommended if you have an unreliable network.


Ian Bjorhovde
IBM Gold Consultant 




On Feb 1, 2019, at 11:48 AM, Harishkumar .Pathangay <[login to unmask email]> wrote:

hi,
I have a db2 [11.1.3.3] database on server1. server1 also has a nfs mount point /var/nfs.
I load data to a table from /var/nfs/db2share/1gb.del
after I start load activity in db2 server itself [it is a local application performing the load], the nfs network went down very briefly for about 20 seconds, but it came back with in the next 10 seconds. the network down time was only less than 30 seconds. but still load utility is saying disk error occurred. it is not able to sense the availability of the network back again and continue the load automatically.
I can manually restart the load, but if there is any timeout parameter I can specify to influence db2 load utility so that it can load the data again even if the network was down briefly say 30 seconds. I do not want to restart load manually every time if there is mild network availability issues.
I tried, db2 registry variables - DB2IQTIME=120 and DB2RQTIME=180. but it did not work. I am not even sure that setting would help me in this situation. please let me know.
thanks,
harish pathangay

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



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]
ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
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


Attachments

  • 20512D70E3044F229DF241FC8A297808.png (<1k)

Harishkumar .Pathangay

RE: How to continue a load activity over brief network downtime
(in response to Harishkumar .Pathangay)

hi ian,

the settings retrains and timeo in nfs mounts are applicable for soft mount points of nfs.

I am using hard mount points without intr setting, this will make the nfs request to wait without timing out.

but still my load utility gets media error - SQL0902C after a brief network down time of one and half minutes.

I tried even setting db2rqtime and db2iqtime as 180,but this setting does not solve the issue.

thanks for the help. hope to understand which setting will allow me to configure for network delays.

it is is only for learning purpose, do not have a sysadmin for Linux.

thanks,

harish pathangay

Harishkumar .Pathangay

RE: How to continue a load activity over brief network downtime
(in response to Harishkumar .Pathangay)

hi ian,

even with soft mounts of nfs with timeo=600 [6 sec] retrains=3 [180 sec in total], still I am getting disk error if network goes down for 2 minutes and comes up again.


SQL0980C  A disk error occurred.  Subsequent SQL statements cannot be
processed.  SQLSTATE=58005
SQL0980C  A disk error occurred.  Subsequent SQL statements cannot be
processed.  SQLSTATE=58005
thanks,
hp

Harishkumar .Pathangay

RE: How to continue a load activity over brief network downtime
(in response to Harishkumar .Pathangay)

Hi,

NFSV4 has an option to set Lease Time, which helps in continue a load activity over brief network downtime.

it is allowing me to configure how much of a network down time can be tolerable for load utility to continue load activity instead of terminating with error.

thanks,

harish pathangay