?
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
?
275 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

It is a common problem that often server suffers from the lack of space on system volume. Old servers or new ones from vendors come with preformatted small volume - 5-6GB in total and after installing updates or applications the free space on system…
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
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.:
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
Suggested Courses

719 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