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

Accepted Solution

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

Assisted Solution

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


Kent
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

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…
Using libpcap/Jpcap to capture and send packets on Solaris version (10/11) Library used: 1.      Libpcap (http://www.tcpdump.org) Version 1.2 2.      Jpcap(http://netresearch.ics.uci.edu/kfujii/Jpcap/doc/index.html) Version 0.6 Prerequisite: 1.      GCC …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.
Suggested Courses

615 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