IBM Support

Why is getShipmentList firing 'for update' queries on entries from yfs_order_header?

Question & Answer


Question

Why is getShipmentList API firing a number of "for update" queries on entries from yfs_order_header? As this is  a list API, the expectation is that the API will not lock any records.

Consider the below input passed to the API -

<Shipment DeliveryMethod="PICK" EnterpriseCode="DEFAULT"
MaximumRecords="5000" SellerOrganizationCode="DEFEULT" ShipNode="Node1">
<ComplexQuery Operator="And">
<Or>
<Exp Name="Status" Value="1100.70.06.10"/>
<Exp Name="Status" Value="1100.70.06.20"/>
</Or>
</ComplexQuery>
<OrderBy>
<Attribute Desc="N" Name="Createts"/>
</OrderBy>
</Shipment>

The template being used is

<Shipments TotalNumberOfRecords="">
    <Shipment AssignedToUserId="" DeliveryMethod=""
        ExpectedDeliveryDate="" ExpectedShipmentDate="" HoldLocation=""
        OrderHeaderKey="" OrderNo="" RequestedShipmentDate=""
        ShipNode="" ShipmentKey="" ShipmentNo="" Status="" StatusDate=""
         TotalNumOfPickableSKUs="">
        <ShipmentLines>
            <ShipmentLine OrderNo="" >
                <OrderLine InvoicedQuantity="" OrderHeaderKey=""
                    OrderLineKey="" OrderedQty="" OriginalOrderedQty="">
                    <PersonInfoMarkFor FirstName="" LastName=""/>
                    <PersonInfoShipTo FirstName="" LastName=""/>
                </OrderLine>
            </ShipmentLine>
        </ShipmentLines>
        <BillToAddress AddressLine1="" AddressLine2="" AddressLine3=""
            City="" DayPhone="" EMailID="" EveningPhone="" FirstName=""
            LastName="" MobilePhone="" State="" ZipCode=""/>
        <Status Description="" ProcessTypeKey="" Status="" StatusKey="" StatusName=""/>
    </Shipment>
</Shipments>

This results in a for update query being fired on all the order header keys retrieved by the API, causing row lock contention.

Cause

The locking is seen if  if the output template has Order element.

Answer

Pass SelectMethod="NO_LOCK" as below in the input of getShipmentList to avoid locking on the yfs_order_header table.
<Shipment SelectMethod="NO_LOCK" ... >

The select method to be used for fetching Order header record. This is applicable only if the output template has Order element. Valid values are NO_LOCK, NO_WAIT.
Please refer to the javadocs for more details

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS6PEW","label":"Sterling Order Management"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.3,9.4,9.5","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Document Information

Modified date:
19 September 2018

UID

ibm10732053