?
Solved

Load from cursor rejecting rows of data. How can I determine which rows are rejected?

Posted on 2010-09-23
6
Medium Priority
?
2,164 Views
Last Modified: 2013-11-17
Question: Hello all,
I am loading from cursor a particular table which is range partitioned from the mainframe DB2 z/os machine to DB2 on a AIX box. Ok, now I went on to load the table and got the following statistics:
Number of rows read         = 10149185
Number of rows skipped      = 0
Number of rows loaded       = 10148630
Number of rows rejected     = 555
Number of rows deleted         = 0
Number of rows committed    = 10149185

Is there a way to determine the rows being rejected? Is it possible to write rejected rows to another file?  I have tried to use the "modified by dumpfile = filename" option of the Load utility such as thi "db2 "LOAD FROM LOAD_CUR11 OF CURSOR messages load.msg modified by dumpfile = rca_load_rejects.txt REPLACE INTO PADBA001.PADTB_AR_TRANSACTION nonrecoverable" but got error "SQL0104N  An unexpected token "modified" was found following "<identifier>". Expected tokens may include:  "REMOTE".  SQLSTATE=42601"

Then I reposition the dumpfile option like this "db2 "LOAD FROM LOAD_CUR11 OF CURSOR modified by dumpfile = rca_load_rejects.txt messages paw_ar_transaction.msg REPLACE INTO PADBA001.PADTB_AR_TRANSACTION nonrecoverable" and got a different error:

SQL3016N  An unexpected keyword "dumpfile" was found in the filetmod parameter
for the filetype.

Does this mean I can't use modified by dumpfile in this context?  Anyway I can do what I need done which is to trap rejected rows?

Any help will be highly appreciated.
okonita1
0
Comment
Question by:okonita1
[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
  • 3
  • 3
6 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 33750290
Hi okonita,

On the LOAD command, use the MESSAGES and/or FOR EXCEPTION clauses.


The MESSAGES clause names the file that DB2 will us to write the error messages.

The FOR EXCEPTION  clause names a table where data will be load that violates unique index or primary key rules.


You can also specify the DUMPFILE clause, and all rejected rows will be written to this file.


Kent
0
 

Author Comment

by:okonita1
ID: 33751149
Kdo,

I just found out that the dumpfile is not supported with load from cursor. Here's the IBM link for report:
http://www-01.ibm.com/support/docview.wss?uid=swg21259962

However I have not tested the For EXCEPTION clause so I'm not sure if is supported for a LOAD from CURSOR.

0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 33752365

That's kind of evil of IBM to not support DUMPFILE, huh?  I can kind of understand why, but still....

FOR EXCEPTION is documented as working, and I have used it.


Kent
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:okonita1
ID: 33752481

Okay Kdo,I read the documentation and it came sounds to me like it is used mainly when your are loading from an input file not from cursor virtual file such as Load from cursor_Name replace into table_name.

I will try first thing this morning. Do you have an example of how you used it?
0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 1500 total points
ID: 33753042

I don't have a good example (in my current duties don't I don't see bad data so I don't have to handle it).

But, IBM does have documentation.  :)

Here is a description of load exception tables.  Nothing magical here, just a pretty good description.

  http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/c0004596.htm


Here are the rules for creating exception tables, probably in more detail than you want to read:

 http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0001111.htm




Good Luck,
Kent
0
 

Author Closing Comment

by:okonita1
ID: 33823870
Well, while none of the answers provided a complete and working example for using a dumpfile, kdo has been very helpful in the past so I am awarding the points to him, anyway. to make the dumpfile work, it seems that you need to enclose the syntax in double-quotes like this: dumpfile="/tmp/dump_file_name"
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

A metadevice consists of one or more devices (slices). It can be expanded by adding slices. Then, it can be grown to fill a larger space while the file system is in use. However, not all UNIX file systems (UFS) can be expanded this way. The conca…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
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

752 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