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

Posted on 2015-01-26
Last Modified: 2015-02-16
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 record(s) selected.

Can someone help explain why this is happening?


Question by:Enyimba
  • 2
LVL 45

Accepted Solution

Kdo earned 500 total points
ID: 40570404
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
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


Author Comment

ID: 40578105

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
LVL 45

Assisted Solution

Kdo earned 500 total points
ID: 40578197
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


Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
removing nim resources 5 37
Unix / Linux grid computing 5 127
Linux :how to provide sudo access to the user 13 74
auto mounter on solaris 1 40
Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now