HPL -244

IDS v11.70.fc3xc Aix 6.1 I have a script that uses HPL to unload 118 tables. It does them one at a time. I have had this process in place for several years. All of a sudden when I execute the script ... some of the tables will get a -244 with ISAM -107. I can wait a minute or so and then reexecute the offending table and it will work ok ... seems like something is getting hung up .. Has anyone else seen similar behaviour ... Thanks ... Peter Logan Senior Database Administrator Phone: 616/878-8309

Hello, Peter. Check your HPL job mode, is it using express or deluxe one? Make sure all of your tables have row lock mode, and also look for the isolation level of the session(s). Post the details, and we could help you further ok? Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: Peter_Logan@spartanstores.com > Subject: HPL -244 [31222] > Date: Mon, 19 Aug 2013 15:52:56 -0400 > > IDS v11.70.fc3xc > Aix 6.1 > > I have a script that uses HPL to unload 118 tables. It does them one at a > time. I have had this process in place for several years. All of a > sudden when I execute the script ... some of the tables will get a -244 > with ISAM -107. I can wait a minute or so and then reexecute the > offending table and it will work ok ... seems like something is getting > hung up .. Has anyone else seen similar behaviour ... > > Thanks ... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Original post: IDS v11.70.fc3xc Aix 6.1 I have a script that uses HPL to unload 118 tables. It does them one at a time. I have had this process in place for several years. All of a sudden when I execute the script ... some of the tables will get a -244 with ISAM -107. I can wait a minute or so and then reexecute the offending table and it will work ok ... seems like something is getting hung up .. Has anyone else seen similar behaviour ... Thanks ... Peter Logan Senior Database Administrator Phone: 616/878-8309 Response: I think I may have seen something like this with another customer. I think the 244/107 error is actually coming from a table in the onpload database (the session table if I remember correctly). I don't think that table has any indexes defined on it, which when running multiple HPL jobs, can lead to some locking conflicts. My guess is when the previous job ends and your script starts the next job, the backend server thread that updates that table isn't actually finished yet, but you script is allowed to drive on and so your next HPL job runs into the lock from the previous job. You could probably work around the issue by putting in your own delay between jobs in your script, or if you already had a delay, try increasing it slightly. Jacques Renaut IBM Informix Advanced Support APD Team

I kinda figured that ... I checked the session table and that was set for page locking ...switched it to row and life seems good now .. strange what happened .. because like I said .. I've had this process in place for several years ... without any issues .. ok well .. gremlins I guess .. Thanks for the clue ... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "JACQUES RENAUT" <jrenaut@us.ibm.com> To: ids@iiug.org, Date: 08/20/2013 09:34 AM Subject: Re: HPL -244 [31226] Sent by: ids-bounces@iiug.org Original post: IDS v11.70.fc3xc Aix 6.1 I have a script that uses HPL to unload 118 tables. It does them one at a time. I have had this process in place for several years. All of a sudden when I execute the script ... some of the tables will get a -244 with ISAM -107. I can wait a minute or so and then reexecute the offending table and it will work ok ... seems like something is getting hung up .. Has anyone else seen similar behaviour ... Thanks ... Peter Logan Senior Database Administrator Phone: 616/878-8309 Response: I think I may have seen something like this with another customer. I think the 244/107 error is actually coming from a table in the onpload database (the session table if I remember correctly). I don't think that table has any indexes defined on it, which when running multiple HPL jobs, can lead to some locking conflicts. My guess is when the previous job ends and your script starts the next job, the backend server thread that updates that table isn't actually finished yet, but you script is allowed to drive on and so your next HPL job runs into the lock from the previous job. You could probably work around the issue by putting in your own delay between jobs in your script, or if you already had a delay, try increasing it slightly. Jacques Renaut IBM Informix Advanced Support APD Team ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.