Thank you for the quick response Razzak.
There are no functions in the SELECT.
The tables have 8 computed columns each but they
are not included in the INSERT/SELECT.
The INTERVAL has remained at 5; WAIT was at 4
seconds, bumped it to 8 on June 9 and to 15 seconds today, June 30.
BY increasing in "small increments", do you mean a second or two?
The INSERT into table SHPT is a SELECT columns
from the temp table, tSHPT, no functions.
Immediately after the INSERT command, I trap the
SQL Code and # of rows inserted and write them
and the Table Lock Type to a DBActivityLog table.
If the SQL Code is not zero or if the number of
rows inserted is zero, then I display an error message alerting the user.
Here is the code:
INSERT INTO SHPT +
 (TransactionID,DepotNumber,ShipDate,OrderNumber, +
 OrderType,OrderDate,CustomerDealercode,FacingDepotNumber, +
 OrderLineNumber,Item,PackagingCode,UnitofMeasure,InventoryType, +
 OriginalOrderLineQuantity,QuantityShipped, +
 NestedContainerNumber,UnitWeight,SCACode, +
 RouteStopCode,BillofLading,ModeCode,SaleDistributionCode, +
 TaskBarcodeSHPT,CustomerOrderNumber,CountryofOrigin, +
 TaskCreatedDate,TaskPickedDate,TaskPackedDate,Picker, +
 Packer,Loader,TaskLoadedDate, +
 PalletQuantity,SHPTNum,HazMat,SHPTStatus,CrossdockDestination, +
 Route,ShipTransportCode,RouteStopCode,PartLabelsPrintedDTS,CartonID) +
 SELECT +
  tTransactionID,tDepotNumber,tShipDate,tOrderNumber, +
  tOrderType,tOrderDate,tCustomerDealercode,tFacingDepotNumber, +
  tOrderLineNumber,tItem,tPackagingCode,tUnitOfMeasure,tInventoryType, +
  tOriginalOrderLineQuantity,tQuantityShipped, +
  tNestedContainerNumber,tUnitWeight,tSCACode, +
  tRouteStopCode,tBillofLading,tModeCode,tSaleDistributionCode, +
  tTaskBarcode,tCustomerOrderNumber,tCountryofOrigin, +
  tTaskCreatedDate,tTaskPickedDate,tTaskPackedDate,tPicker, +
  tPacker,tLoader,tTaskLoadedDate, +
  tPalletQuantity,tSHPTNum,tHazMat,tSHPTStatus,tCrossdockDestination, +
Â
 tRoute,tShipTransportcode,tRouteStopCode,tPartLabelsPrintedDTS,tCartonID +
 FROM tSHPT
SET VAR vSQLCode = .SQLCODE
SET VAR vRowCount INTEGER = .RBTI_RowsInserted
-- 02/19/20: Below added to track rows inserted into SHPT, missing orders?
--Track data activity in DBActivityLog
--04/08/22:DHH:Added Table Lock Type to debug errs inserting tSHPT to SHPT
SET VAR +
 vTableLockType = (GETVAL('GetLockType','SHPT')), +
 pDBLogTable = 'SHPT', +
 pDBLogColumn = '', +
 pDBLogError = ('SQL Code ' + (CTXT(.vSQLCode))), +
 pDBLogAction = +
 ('INS' & (CTXT(.vRowCount)) & 'tSHPT>SHPT;SHPTLock=' + .vTableLockType), +
 pDBLogLocation = 'MoparPartLbls,PRINTER,1097', +
 pDBLogPrimaryID = (IFEQ(.vRowCount,0,'Error',''))
RUN SELECT CmdCode FROM InternalCode WHERE CmdName = 'DBActivityLog'
-- 06/25/21: Below added to display message for user
IF vSQLCode <> 0 OR vRowCount = 0 THEN
 --Orders won't be going to SHPT, show err msg, undo update
 SET VAR vMsg = +
   (' Error creating shipping orders for these labels.' + +
   (CHAR(009))+(CHAR(013))& +
   'Please note the item,' & .vItem + +
   ', and Order number and rerun them.' + +
   (CHAR(009))+(CHAR(013))& +
   'Let Steve know the item number and this is Line # 1097.')
 PAUSE 2 USING .vMsg +
  CAPTION 'Error' +
  ICON WARNING BUTTON 'Continue' +
  OPTION BACK_COLOR YELLOW +
  |MESSAGE_COLOR YELLOW +
  |MESSAGE_FONT_NAME Tahoma +
  |MESSAGE_FONT_COLOR WINDOWTEXT +
  |MESSAGE_FONT_SIZE 10 +
  |MESSAGE_FONT_BOLD ON +
  |BUTTON_COLOR RED
 --Added 06/29/21
 --Undo update to PIKD that labels for this item have been printed
 UPDATE PIKD SET +
   PartLabelsPrintedDTS = NULL +
  WHERE +
   PIKD_ID IN +
   (SELECT PIKD_ID FROM tPendingOrders WHERE PIKDProcessFlag = 'U')
ENDIF
Here are the entries in the DBActivityLog
table. The first 4 are from earlier in the
program, the last row, # 2108238, is generated by the above code.
I would think the DateTime of the last row
should be about 8 seconds later, 07:49:03.
[]
On 6/30/2022 6:22 PM, A. Razzak Memon wrote:
Doug,
If there are functions embedded within the
value list or SELECT statement, there would be
added processing to each INSERT.
Are there functions defined?
Please share the INSERT command.
Also, have you tried increasing the INTERVAL
and WAIT settings in small increments?
Very Best R:egards,
Razzak.
At 05:39 PM 6/30/2022, Doug Hamilton wrote:
Hi List -
I have code that inserts a row from a temp
table into a permanent table; it's run an
average of 160 times a day on three different machines.
Once a week or so, the INSERT fails because
there is a legitimate remote table lock on the
permanent table that causes an SQL error code
2440, "You reached 100% of your lock wait
time. You must re-execute the
command." This occurs every time there is
a remote table lock on the permanent table.
My understanding is that INSERT will attempt
to execute every 5 tenths of a second
(INTERVAL = 5) for approx 8 seconds (WAIT = 8)
or until the lock is removed, whichever occurs first.
But evidence (log table entries) indicates
that it is waiting only about 2 seconds before throwing the SQL error.
This happens on any of the three machines that run this program.
They all read a common CFG file so WAIT,
INTERVAL and all other settings are identical.
Any clues for the blues?
TIA,
Doug
