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
--
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/3a7b765d-7dd2-0ff5-ed4a-b3e10a6da758%40wi.rr.com.
