• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

DB2 returns zero(0) count after repeated loading of millions of records...Any idea what is happening here?

Hello gurus,

I have repeatedly loaded a table with data and after each load operation, I do a select count(1) or select (*) from the table but surprised to get a zero return count. Here is my example:
RAPMQ01HDQRA /db2storage >db2 -tvf load_ptl_table.sql -z load_ptl_table.out
alter table PERISCOPE.PRODUCT_TRANS_LOG activate not logged initially
DB20000I  The SQL command completed successfully.

load from periscope_product_trans_log.ixf of ixf savecount 50000 messages A315.msg insert INTO periscope.product_trans_log indexing mode rebuild allow read access

Number of rows read         = 19408704
Number of rows skipped      = 0
Number of rows loaded       = 19408704
Number of rows rejected     = 0
Number of rows deleted      = 19408704
Number of rows committed    = 19408704

SQL3107W  There is at least one warning message in the message file.

RAPMQ01HDQRA /db2storage >db2 "select count(*) from periscope.product_trans_log"

1
-----------
          0

  1 record(s) selected.

Can someone help explain why this is happening?

Thanks

Enyimba
0
Enyimba
Asked:
Enyimba
  • 2
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Enyimba,

Note the line "Number of rows deleted".  All of the (apparently) loaded rows are also deleted.

What are the warnings?  The key may be there.

Also, whenever I put a table into initial load state, I do so twice.  This goes back to a time when DB2 would hold a lock from the initialization and the task would lock itself out trying to do the load.  I don't know if this behavior still occurs, but the workaround is easy and takes almost no time to complete.  You appear to be adding data to an existing table but since the row count starts at zero, this trick may apply.
alter table PERISCOPE.PRODUCT_TRANS_LOG activate not logged initially with empty table
commit
alter table PERISCOPE.PRODUCT_TRANS_LOG activate not logged initially
load from periscope_product_trans_log.ixf of ixf savecount 50000 messages A315.msg insert INTO periscope.product_trans_log indexing mode rebuild allow read access

Open in new window

Kent
0
 
EnyimbaAuthor Commented:
Kent,

The problem has been resolved. The problemwas that doing a load with referencial violation was pracically rejecting all rows inserted and doing this as the last phase. The data used came from a different server with different structure than the structure being loaded on. some key were on the server that was not on the target database.This is new to me. Is this how the load utility should work? I am not very sure about this but my problem
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Ahhhh, sorry....    From the original description, I didn't think of this as an RI issue.

Just to continue the conversation, disable the RI whenever you load the fact and dimension tables.  This will speed up the load tremendously, just as dropping and recreating the indexes will speed things up.  Once the tables are loaded rebuild the indexes and enable the RI

  ALTER FOREIGN KEY {name} NOT ENFORCED  (once for each foreign key on the table)
  load the table
  CREATE INDEX ....
  ALTER FOREIGN KEY {name} ENFORCED


Kent
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now