Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-01-26
3
Medium Priority
?
287 Views
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
-----------
          0

  1 record(s) selected.

Can someone help explain why this is happening?

Thanks

Enyimba
0
Comment
Question by:Enyimba
  • 2
3 Comments
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 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
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
 

Author Comment

by:Enyimba
ID: 40578105
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
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 2000 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
  CREATE INDEX ....
  ALTER FOREIGN KEY {name} ENFORCED


Kent
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while back when OPSMGR 2012 was released we were very excited about getting it into our environment and upgrading our 2007 implementation,  we started our planning and we then proceeded with our implementation. All went as planned & our system …
Storage devices are generally used to save the data or sometime transfer the data from one computer system to another system. However, sometimes user accidentally erased their important data from the Storage devices. Users have to know how data reco…
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.:
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

886 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