IT25884: SQL STATEMENTS FROM IBM STERLING FILE GATEWAY UI SEARCHES PERFORM A FULL TABLE SCAN

APAR status

• Closed as program error.

Error description

• SQL statements  from IBM Sterling File Gateway UI searches
  perform a full table scans because it cannot use this index:
  MODIFYTS due to implicit conversion, and thus causing full table
  scans.
  
  SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE
  FG_ARRIVEDFILE , FG_ROUTE FG_ROUTE WHERE
  FG_ARRIVEDFILE.MODIFYTS >= :1 AND FG_ARRIVEDFILE.STATE
  IN('Routed', 'Failed', 'Ignored') AND
  FG_ARRIVEDFILE.ARRIVEDFILE_KEY = FG_ROUTE.ARRIVEDFILE_KEY
  
  SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE
  WHERE FG_ARRIVEDFILE.MODIFYTS >= :1 AND FG_ARRIVEDFILE.STATE
  IN('Routed', 'Failed', 'Ignored') AND NOT EXISTS (SELECT * FROM
  FG_ROUTE WHERE FG_ROUTE.ARRIVEDFILE_KEY =
  FG_ARRIVEDFILE.ARRIVEDFILE_KEY)
  
  SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE
  FG_ARRIVEDFILE , FG_ROUTE FG_ROUTE WHERE
  FG_ARRIVEDFILE.MODIFYTS >= :1 AND FG_ARRIVEDFILE.STATE =
  'Routed' AND FG_ARRIVEDFILE.ARRIVEDFILE_KEY =
  FG_ROUTE.ARRIVEDFILE_KEY
  
  SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE
  WHERE FG_ARRIVEDFILE.MODIFYTS >= :1 AND FG_ARRIVEDFILE.STATE
  IN('Routed', 'Failed', 'Ignored')
  
  The predicate "FG_ARRIVEDFILE"."MODIFYTS">=:B1 used at line ID
  3 of the execution plan contains an implicit data type
  conversion on indexed column
  
  
  "MODIFYTS". This implicit data type conversion prevents the
  optimizer from selecting indices on table
  "BFG_SFG_OWNER"."FG_ARRIVEDFILE".
  

Local fix

• RTC ID: 573500
  No workaround.
  

Problem summary

• Users Affected:
  Oracle Database Users Running IBM Sterling File Gateway
  
  Problem Description:
  Oracle queries cause a full table scan due to implicit
  conversion:
  SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE ,
  FG_ROUTE FG_ROUTE WHERE FG_ARRIVEDFILE.MODIFYTS >= :1 AND
  FG_ARRIVEDFILE.STATE IN('Routed', 'Failed', 'Ignored') AND
  FG_ARRIVEDFILE.ARRIVEDFILE_KEY = FG_ROUTE.ARRIVEDFILE_KEY
  
  SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE
  WHERE FG_ARRIVEDFILE.MODIFYTS >= :1 AND FG_ARRIVEDFILE.STATE
  IN('Routed', 'Failed', 'Ignored') AND NOT EXISTS (SELECT * FROM
  FG_ROUTE WHERE FG_ROUTE.ARRIVEDFILE_KEY =
  FG_ARRIVEDFILE.ARRIVEDFILE_KEY)
  
  SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE ,
  FG_ROUTE FG_ROUTE WHERE FG_ARRIVEDFILE.MODIFYTS >= :1 AND
  FG_ARRIVEDFILE.STATE = 'Routed' AND
  FG_ARRIVEDFILE.ARRIVEDFILE_KEY = FG_ROUTE.ARRIVEDFILE_KEY
  
  SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE
  WHERE FG_ARRIVEDFILE.MODIFYTS >= :1 AND FG_ARRIVEDFILE.STATE
  IN('Routed', 'Failed', 'Ignored')
  
  Platforms Affected:
  Oracle
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  SELECT /*YANTRA*/ count(1) from FG_ARRIVEDFILE FG_ARRIVEDFILE
  WHERE FG_ARRIVEDFILE.MODIFYTS >= :1 AND FG_ARRIVEDFILE.STATE
  IN('Routed', 'Failed', 'Ignored')
  

Problem conclusion

• Resolution Summary:
  
  A code fix is provided.
  
  Delivered in:
  5020603_7
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  STR FILE GATEWA

• Fixed component ID

  5725D0700

Applicable component levels