To get loaded count in DB2 table
Moderators: chulett, rschirm, roy
To get loaded count in DB2 table
Hi Friends,
can some one help me out to write a routine or any other method to know whether all the records in the load dataset are properly loaded into DB2 table or not?
For eg: Suppose there are around 100 records in the load dataset and only 95 got loaded and job is successfully completed.
Is there any method to trace these 5 missing records.Even job log donot give such information.
Write method on table is Load append / Insert
Note: Full scan of the table again should be avoided to achieve this.
Regards,
Satish.
can some one help me out to write a routine or any other method to know whether all the records in the load dataset are properly loaded into DB2 table or not?
For eg: Suppose there are around 100 records in the load dataset and only 95 got loaded and job is successfully completed.
Is there any method to trace these 5 missing records.Even job log donot give such information.
Write method on table is Load append / Insert
Note: Full scan of the table again should be avoided to achieve this.
Regards,
Satish.
Not really. If there are errors in the load then the job will show those errors, if they don't show up there, then I would look at duplicate keys overwriting existing records as a possible reason.
You could use a reject link and insert/update method to check.
You could use a reject link and insert/update method to check.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Thanks for your quick reply,ArndW wrote:Not really. If there are errors in the load then the job will show those errors, if they don't show up there, then I would look at duplicate keys overwriting existing records as a possible reason.
You could use a reject link and insert/update method to check.
As far as I know,reject link is possible only for update /insert.But job on which we are going to perform this check are exicying job running in production and no modification to them is acceptable
If it would have been possible, link count of that reject link would have solved the problem
So was searching for an alternative>Appreciate your help.
Not being able to test it with reject links makes it tough; can you duplicate the production data on another DataStage machine in order to test it? What about duplicate primary keys?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
No Not really !!ArndW wrote:Not being able to test it with reject links makes it tough; can you duplicate the production data on another DataStage machine in order to test it? What about duplicate primary keys? ...
But yes reject link test will solve it.But want a logic or code written, which will be used as an automated process after every load job in future if successful.
Reject link testing will be manual or can say adhoc testing, but cannot be implemented in production coz all load jobs are insert or append where pulling reject link is not allowed in datastage.
Is there any after job routine, which can perform this ?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
DataStage only reports how many records it sends to the database. If you can't use a reject link (because of Load method, for example) then you need some other mechanism, such as reviewing the log of the bulk loader. You can, of course, automate this if you know the format of that log.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
You didn't say what version of DB2 you're using but in DB2 LUW you should be able to specify an EXCEPTION table to capture rows that are not loaded (because they are duplicates for example).
(Or in DB2 z/OS you can specify a DISCARD file which captures rows which are not loaded)
You would need another DataStage job to check the exception table to see if it had any rows, indicating that the previous job had failed to load all the rows.
(Or in DB2 z/OS you can specify a DISCARD file which captures rows which are not loaded)
You would need another DataStage job to check the exception table to see if it had any rows, indicating that the previous job had failed to load all the rows.
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Dump the records' key values into a temporary table as well as into the real table. Then do a count query on the join of the two (based on primary key and therefore resolved rapidly in the indexes) and see whether it matches the count reported by DataStage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.