SQL Upsert using load periodically not saving all rows

Bug #1421747 reported by Gary Hall on 2015-02-13
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Suresh Subbiah

Bug Description

When running a script that initiates 32 parallel streams loading a table, we have found that periodically there are gaps in the resulting saved data...for example we will find that we are missing stock items #29485 thru #30847 inclusive for Warehouse #5. The number of gaps found for a given load run varies...normally none, but I've seen as many as eight gaps of missing data.

The sql statement used in all streams is as follows:

sql_statement = "upsert using load into " + stock_table_name
                  + " (S_I_ID, S_W_ID, S_QUANTITY, S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04,"
                  + " S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10,"
                  + " S_YTD, S_ORDER_CNT, S_REMOTE_CNT, S_DATA)"
                  + " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

This is not easily repeatable…I’ve run the script to drop/create/load this table 12 times today, resulting in some missing rows 4 of the 12 times. Worst case we were missing 0.03% of the required rows in the table…obviously, ANY missing data is not acceptable.

Our test environment control parameters (in case any are of value to you)...


      Load Starting : 2015-02-13 04:58:13
       PropertyFile : trafodion.properties
           Datebase : trafodion
             Schema : trafodion.javabench
        ScaleFactor : 512
            Streams : 32
           Maintian : true
               Load : true
         AutoCommit : true
          BatchSize : 1000
             Upsert : true
          UsingLoad : true
     IntervalLength : 60

Changed in trafodion:
importance: Undecided → High
Gary Hall (g-hall) wrote :

This problem continues to cause delays in our performance testing schedule -- the latest attempt to create/load the OE_STOCK_512 table resulted in four warehouses missing a group of 364 stock items and one warehouse missing 275 contiguous stock items. This is using the 1.0.1rc3 build (March 3rd, 2015).

tags: added: sql-cmp
Changed in trafodion:
assignee: nobody → Suresh Subbiah (suresh-subbiah)
milestone: none → r2.0
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers