Is there a way i can validate the data before loading into the actual table?

Harishkumar .Pathangay

Is there a way i can validate the data before loading into the actual table?

Hi,
Is there a way i can validate the data before loading into the actual table?
For Example, I have a table with 10,000 records. I am given a data file with 15,000 records.
Assume 3000 records are having some error [like null values in a non-null column, data type mismatch, data length mismatch etc.]
So before i load or import them i want to validate it. I understand that i can load and provide exception tables so i can easily figure out error records and fix them.
But here is the issue with that. It will load 12,000 successfully and make them available, which is not acceptable.
You either load 15k in total or remove them or the 12,000 that got loaded should not be accessible untill i fix the rest of 3k.
I do not want to quiesce the table until i fix the issue, table should be available with 10K records only.
How do i do that? I can validate in a different table or database itself, but then it is doubling my time.

I am looking for an option where i can say to import or load that parse the data file, but do not access table and write it to them.
In that way it will only spit errors in the data file. i can go fix it, then load the complete data.

How will i do this?

thanks,
harish pathangay

Philip Sevetson

Is there a way i can validate the data before loading into the actual table?
(in response to Harishkumar .Pathangay)
**please note my email address change**
Harish,

That does sound like it’d be a nice-to-have. But, no, there isn’t anything like that. In order to do it without a LOAD, you have to write application program code to check the data.

The closest that DB2 comes to this is a solution which you appear to have rejected:

· Define a second table like the first, don’t give anyone access to it

· Run a LOAD and define DD SYSDISC to hold the rejects.
The SYSPRINT listing will tell you the first disqualifying error for each disqualified input record, and the record number in the input file. (Note: there may be a limit of 2000 reports for the errors.)

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001-2632
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Harishkumar .Pathangay [mailto:[login to unmask email]
Sent: Friday, August 25, 2017 11:20 AM
To: [login to unmask email]
Subject: [DB2-L] - Is there a way i can validate the data before loading into the actual table?


Hi,
Is there a way i can validate the data before loading into the actual table?
For Example, I have a table with 10,000 records. I am given a data file with 15,000 records.
Assume 3000 records are having some error [like null values in a non-null column, data type mismatch, data length mismatch etc.]
So before i load or import them i want to validate it. I understand that i can load and provide exception tables so i can easily figure out error records and fix them.
But here is the issue with that. It will load 12,000 successfully and make them available, which is not acceptable.
You either load 15k in total or remove them or the 12,000 that got loaded should not be accessible untill i fix the rest of 3k.
I do not want to quiesce the table until i fix the issue, table should be available with 10K records only.
How do i do that? I can validate in a different table or database itself, but then it is doubling my time.

I am looking for an option where i can say to import or load that parse the data file, but do not access table and write it to them.
In that way it will only spit errors in the data file. i can go fix it, then load the complete data.

How will i do this?

thanks,
harish pathangay

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Sam Baugh

Is there a way i can validate the data before loading into the actual table?
(in response to Philip Sevetson)
Could START the tablespace with ACCESS(UT) and reset back to RW after data
is fixed.

On Fri, Aug 25, 2017 at 10:41 AM, Sevetson, Phil <[login to unmask email]>
wrote:

> **please note my email address change**
>
> Harish,
>
>
>
> That does sound like it’d be a nice-to-have. But, no, there isn’t anything
> like that. In order to do it without a LOAD, you have to write application
> program code to check the data.
>
>
>
> The closest that DB2 comes to this is a solution which you appear to have
> rejected:
>
> · Define a second table like the first, don’t give anyone access
> to it
>
> · Run a LOAD and define DD SYSDISC to hold the rejects.
>
> The SYSPRINT listing will tell you the first disqualifying error for each
> disqualified input record, and the record number in the input file. (Note:
> there may be a limit of 2000 reports for the errors.)
>
>
>
> Philip Sevetson
>
> Computer Systems Manager
>
> 5 Manhattan West (33rd St at 10th Ave)
>
> New York, NY 10001-2632
>
> 212-857-1688 w
>
> 917-991-7052 c
>
> 212-857-1659 f
>
> [image: cid:[login to unmask email]
>
>
>
> *From:* Harishkumar .Pathangay [mailto:[login to unmask email]
> *Sent:* Friday, August 25, 2017 11:20 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - Is there a way i can validate the data before
> loading into the actual table?
>
>
>
> Hi,
> Is there a way i can validate the data before loading into the actual
> table?
> For Example, I have a table with 10,000 records. I am given a data file
> with 15,000 records.
> Assume 3000 records are having some error [like null values in a non-null
> column, data type mismatch, data length mismatch etc.]
> So before i load or import them i want to validate it. I understand that i
> can load and provide exception tables so i can easily figure out error
> records and fix them.
> But here is the issue with that. It will load 12,000 successfully and make
> them available, which is not acceptable.
> You either load 15k in total or remove them or the 12,000 that got loaded
> should not be accessible untill i fix the rest of 3k.
> I do not want to quiesce the table until i fix the issue, table should be
> available with 10K records only.
> How do i do that? I can validate in a different table or database itself,
> but then it is doubling my time.
>
> I am looking for an option where i can say to import or load that parse
> the data file, but do not access table and write it to them.
> In that way it will only spit errors in the data file. i can go fix it,
> then load the complete data.
>
> How will i do this?
>
> thanks,
> harish pathangay
>
>
> -----End Original Message-----
> **This e-mail, including any attachments, may be confidential, privileged,
> or otherwise legally protected. It is intended only for the addressee. If
> you received this e-mail in error or from someone who was not authorized to
> send it to you, do not disseminate, copy, or otherwise use this e-mail or
> its attachments. Please notify the sender immediately by reply e-mail and
> delete the e-mail from your system.**
> -----End Original Message-----
>

Harishkumar .Pathangay

Is there a way i can validate the data before loading into the actual table?
(in response to Sam Baugh)
hi,
thanks for all your valuable inputs.

thanks again,
harish pathangay

On 8/25/17, Sam Baugh <[login to unmask email]> wrote:
> Could START the tablespace with ACCESS(UT) and reset back to RW after data
> is fixed.
>
> On Fri, Aug 25, 2017 at 10:41 AM, Sevetson, Phil <[login to unmask email]>
> wrote:
>
>> **please note my email address change**
>>
>> Harish,
>>
>>
>>
>> That does sound like it’d be a nice-to-have. But, no, there isn’t
>> anything
>> like that. In order to do it without a LOAD, you have to write
>> application
>> program code to check the data.
>>
>>
>>
>> The closest that DB2 comes to this is a solution which you appear to have
>> rejected:
>>
>> · Define a second table like the first, don’t give anyone access
>> to it
>>
>> · Run a LOAD and define DD SYSDISC to hold the rejects.
>>
>> The SYSPRINT listing will tell you the first disqualifying error for each
>> disqualified input record, and the record number in the input file.
>> (Note:
>> there may be a limit of 2000 reports for the errors.)
>>
>>
>>
>> Philip Sevetson
>>
>> Computer Systems Manager
>>
>> 5 Manhattan West (33rd St at 10th Ave)
>>
>> New York, NY 10001-2632
>>
>> 212-857-1688 w
>>
>> 917-991-7052 c
>>
>> 212-857-1659 f
>>
>> [image: cid:[login to unmask email]
>>
>>
>>
>> *From:* Harishkumar .Pathangay [mailto:[login to unmask email]
>> *Sent:* Friday, August 25, 2017 11:20 AM
>> *To:* [login to unmask email]
>> *Subject:* [DB2-L] - Is there a way i can validate the data before
>> loading into the actual table?
>>
>>
>>
>> Hi,
>> Is there a way i can validate the data before loading into the actual
>> table?
>> For Example, I have a table with 10,000 records. I am given a data file
>> with 15,000 records.
>> Assume 3000 records are having some error [like null values in a non-null
>> column, data type mismatch, data length mismatch etc.]
>> So before i load or import them i want to validate it. I understand that
>> i
>> can load and provide exception tables so i can easily figure out error
>> records and fix them.
>> But here is the issue with that. It will load 12,000 successfully and
>> make
>> them available, which is not acceptable.
>> You either load 15k in total or remove them or the 12,000 that got loaded
>> should not be accessible untill i fix the rest of 3k.
>> I do not want to quiesce the table until i fix the issue, table should be
>> available with 10K records only.
>> How do i do that? I can validate in a different table or database itself,
>> but then it is doubling my time.
>>
>> I am looking for an option where i can say to import or load that parse
>> the data file, but do not access table and write it to them.
>> In that way it will only spit errors in the data file. i can go fix it,
>> then load the complete data.
>>
>> How will i do this?
>>
>> thanks,
>> harish pathangay
>>
>>
>> -----End Original Message-----
>> **This e-mail, including any attachments, may be confidential,
>> privileged,
>> or otherwise legally protected. It is intended only for the addressee. If
>> you received this e-mail in error or from someone who was not authorized
>> to
>> send it to you, do not disseminate, copy, or otherwise use this e-mail or
>> its attachments. Please notify the sender immediately by reply e-mail and
>> delete the e-mail from your system.**
>> -----End Original Message-----
>>
>
> -----End Original Message-----
>


--
Thanks,
Harish P

Harishkumar .Pathangay

Is there a way i can validate the data before loading into the actual table?
(in response to Harishkumar .Pathangay)
Hi All,
Raised an RFE requesting that feature.

URL for Voting:
http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=109542

I kindly request you to vote if you also feel that feature is helpful.

thanks,
harish pathangay

On 8/25/17, Harishkumar Pathangay <[login to unmask email]> wrote:
> hi,
> thanks for all your valuable inputs.
>
> thanks again,
> harish pathangay
>
> On 8/25/17, Sam Baugh <[login to unmask email]> wrote:
>> Could START the tablespace with ACCESS(UT) and reset back to RW after
>> data
>> is fixed.
>>
>> On Fri, Aug 25, 2017 at 10:41 AM, Sevetson, Phil <[login to unmask email]>
>> wrote:
>>
>>> **please note my email address change**
>>>
>>> Harish,
>>>
>>>
>>>
>>> That does sound like it’d be a nice-to-have. But, no, there isn’t
>>> anything
>>> like that. In order to do it without a LOAD, you have to write
>>> application
>>> program code to check the data.
>>>
>>>
>>>
>>> The closest that DB2 comes to this is a solution which you appear to
>>> have
>>> rejected:
>>>
>>> · Define a second table like the first, don’t give anyone access
>>> to it
>>>
>>> · Run a LOAD and define DD SYSDISC to hold the rejects.
>>>
>>> The SYSPRINT listing will tell you the first disqualifying error for
>>> each
>>> disqualified input record, and the record number in the input file.
>>> (Note:
>>> there may be a limit of 2000 reports for the errors.)
>>>
>>>
>>>
>>> Philip Sevetson
>>>
>>> Computer Systems Manager
>>>
>>> 5 Manhattan West (33rd St at 10th Ave)
>>>
>>> New York, NY 10001-2632
>>>
>>> 212-857-1688 w
>>>
>>> 917-991-7052 c
>>>
>>> 212-857-1659 f
>>>
>>> [image: cid:[login to unmask email]
>>>
>>>
>>>
>>> *From:* Harishkumar .Pathangay [mailto:[login to unmask email]
>>> *Sent:* Friday, August 25, 2017 11:20 AM
>>> *To:* [login to unmask email]
>>> *Subject:* [DB2-L] - Is there a way i can validate the data before
>>> loading into the actual table?
>>>
>>>
>>>
>>> Hi,
>>> Is there a way i can validate the data before loading into the actual
>>> table?
>>> For Example, I have a table with 10,000 records. I am given a data file
>>> with 15,000 records.
>>> Assume 3000 records are having some error [like null values in a
>>> non-null
>>> column, data type mismatch, data length mismatch etc.]
>>> So before i load or import them i want to validate it. I understand that
>>> i
>>> can load and provide exception tables so i can easily figure out error
>>> records and fix them.
>>> But here is the issue with that. It will load 12,000 successfully and
>>> make
>>> them available, which is not acceptable.
>>> You either load 15k in total or remove them or the 12,000 that got
>>> loaded
>>> should not be accessible untill i fix the rest of 3k.
>>> I do not want to quiesce the table until i fix the issue, table should
>>> be
>>> available with 10K records only.
>>> How do i do that? I can validate in a different table or database
>>> itself,
>>> but then it is doubling my time.
>>>
>>> I am looking for an option where i can say to import or load that parse
>>> the data file, but do not access table and write it to them.
>>> In that way it will only spit errors in the data file. i can go fix it,
>>> then load the complete data.
>>>
>>> How will i do this?
>>>
>>> thanks,
>>> harish pathangay
>>>
>>>
>>> -----End Original Message-----
>>> **This e-mail, including any attachments, may be confidential,
>>> privileged,
>>> or otherwise legally protected. It is intended only for the addressee.
>>> If
>>> you received this e-mail in error or from someone who was not authorized
>>> to
>>> send it to you, do not disseminate, copy, or otherwise use this e-mail
>>> or
>>> its attachments. Please notify the sender immediately by reply e-mail
>>> and
>>> delete the e-mail from your system.**
>>> -----End Original Message-----
>>>
>>
>> -----End Original Message-----
>>
>
>
> --
> Thanks,
> Harish P
>
> -----End Original Message-----
>
>


--
Thanks,
Harish P

J&#248;rn Thyssen

RE: Is there a way i can validate the data before loading into the actual table?
(in response to Harishkumar .Pathangay)


Hi

Have you looked at LOAD REPLACE SHRLEVEL REFERENCE ? 

http://www-01.ibm.com/support/docview.wss?uid=swg1PI67793

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

Harishkumar .Pathangay

Is there a way i can validate the data before loading into the actual table?
(in response to Jørn Thyssen)
oh, i am on LUW. Forgot to mention that. sorry.

thanks,
harish pathangay

On Sat, Aug 26, 2017 at 12:41 AM, Jørn Thyssen <[login to unmask email]> wrote:

>
> Hi
>
> Have you looked at LOAD REPLACE SHRLEVEL REFERENCE ?
>
> http://www-01.ibm.com/support/docview.wss?uid=swg1PI67793
>
>
>
> Best regards,
>
> Jørn Thyssen
>
> Rocket Software
> 77 Fourth Avenue • Waltham, MA • 02451 • USA
> E: [login to unmask email] • W: www.rocketsoftware.com
>
> Views are personal.
>
> -----End Original Message-----
>



--
Thanks,
Harish P

Jim Tonchick

Is there a way i can validate the data before loading into the actual table?
(in response to Harishkumar .Pathangay)
Why not create a clone of the target table, load the new data there and see how much, if any, data gets rejected. You can then fix the rejected data until it loads into the clone. When all the new data has been validated in the clone, you can then move the clone contents to the real table using several methods i.e.(Unload/Load or SQL insert).


-----Original Message-----
From: Harishkumar .Pathangay <[login to unmask email]>
To: DB2-L <[login to unmask email]>
Sent: Fri, Aug 25, 2017 10:19 AM
Subject: [DB2-L] - Is there a way i can validate the data before loading into the actual table?



<div id="AOLMsgPart_2_730ec61b-0332-4531-9767-e0e6b8db086d">
<div class="aolReplacedBody"><p>Hi,
Is there a way i can validate the data before loading into the actual table?
For Example, I have a table with 10,000 records. I am given a data file with 15,000 records.
Assume 3000 records are having some error [like null values in a non-null column, data type mismatch, data length mismatch etc.]
So before i load or import them i want to validate it. I understand that i can load and provide exception tables so i can easily figure out error records and fix them.
But here is the issue with that. It will load 12,000 successfully and make them available, which is not acceptable.
You either load 15k in total or remove them or the 12,000 that got loaded should not be accessible untill i fix the rest of 3k.
I do not want to quiesce the table until i fix the issue, table should be available with 10K records only.
How do i do that? I can validate in a different table or database itself, but then it is doubling my time.

I am looking for an option where i can say to import or load that parse the data file, but do not access table and write it to them.
In that way it will only spit errors in the data file. i can go fix it, then load the complete data.

How will i do this?

thanks,
harish pathangay</p>
<hr size="1" style="color:#ccc"><div id="aolmail_socfooter" style="font-size:80%"><span style="font-weight:bold">Site Links: </span>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/fo/st/?post=182710&anc=p182710#p182710">View post online</a>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/fo/si/?topic=19">View mailing list online</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]">Start new thread via email</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]?Subject=Unsubscribe">Unsubscribe from this mailing list</a>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/us/to/">Manage your subscription</a>


This email has been sent to: <a href="mailto:[login to unmask email]">[login to unmask email]</a>
<p>
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU

ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See

<a target="_blank" rel="noopener noreferrer" href="http://www.ESAIGroup.com/idug">http://www.ESAIGroup.com/idug</a>

</p>
<p>
Use of this email content is governed by the terms of service at:
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/cm/ld/fid=2">http://www.idug.org/p/cm/ld/fid=2</a></p>

</div><hr size="1" style="color:#ccc"></div>
</div>

Harishkumar .Pathangay

Is there a way i can validate the data before loading into the actual table?
(in response to Jim Tonchick)
thanks for the suggestion. it doubles my time for loading data.
why i am asking such a feature is because the clone table approach is not
100% beneficial:

- takes up additional space
- doubes time for loading data - i feel the data write portion [disk io
intensive] is time consuming than data file processing [cpu intensive] - i
can live with cpu intensive for a brief time but not disk intensive.
- i cannot go and take this approach for 20 tables of 20 GB each. i will
end up with 400GB in duplicate temporary data.


- for validating data files i have to setup test data base separately if
i do not want these impacts
- having additional feature always is good as it makes things easier

i have raised an RFE for this in IBM website. Rest leave it to them.

thanks,
harish pathangay

On Sat, Aug 26, 2017 at 1:20 AM, Jim Tonchick <[login to unmask email]> wrote:

> Why not create a clone of the target table, load the new data there and
> see how much, if any, data gets rejected. You can then fix the rejected
> data until it loads into the clone. When all the new data has been
> validated in the clone, you can then move the clone contents to the real
> table using several methods i.e.(Unload/Load or SQL insert).
>
>
> -----Original Message-----
> From: Harishkumar .Pathangay <[login to unmask email]>
> To: DB2-L <[login to unmask email]>
> Sent: Fri, Aug 25, 2017 10:19 AM
> Subject: [DB2-L] - Is there a way i can validate the data before loading
> into the actual table?
>
>
> Hi,
> Is there a way i can validate the data before loading into the actual
> table?
> For Example, I have a table with 10,000 records. I am given a data file
> with 15,000 records.
> Assume 3000 records are having some error [like null values in a non-null
> column, data type mismatch, data length mismatch etc.]
> So before i load or import them i want to validate it. I understand that i
> can load and provide exception tables so i can easily figure out error
> records and fix them.
> But here is the issue with that. It will load 12,000 successfully and make
> them available, which is not acceptable.
> You either load 15k in total or remove them or the 12,000 that got loaded
> should not be accessible untill i fix the rest of 3k.
> I do not want to quiesce the table until i fix the issue, table should be
> available with 10K records only.
> How do i do that? I can validate in a different table or database itself,
> but then it is doubling my time.
>
> I am looking for an option where i can say to import or load that parse
> the data file, but do not access table and write it to them.
> In that way it will only spit errors in the data file. i can go fix it,
> then load the complete data.
>
> How will i do this?
>
> thanks,
> harish pathangay
>
> -----End Original Message-----
>



--
Thanks,
Harish P

Harishkumar .Pathangay

RE: Is there a way i can validate the data before loading into the actual table?
(in response to Harishkumar .Pathangay)

Hi,
I have another suggestion keeping in mind the objective of loading the required records in totality.
Assume the following IMPORT Command.

Import from <data file> of del insert into <table name> NO ROWS AVAILABLE <Exception Table Clause>

The table already has 15K records available.
The Option "No Rows Available" will import the data, but will not make it available for query processing.
So if i import 10k rows and 3k got rejected and captured in error or exception tables. i hand the records to concerned team.
they get back to me with corrected records. i go load again. untill that time only 15K is available for users.
Again load the corrected records, out of 3k, 1k records again got errored out.
no comments why they gave me wrong records again.let us not discuss about that.
in the db2 table now there are 15k accessible, which is existing data from start.
9k rows loaded [7K + 2K] but in "not accessible state" and am still waiting for 1k records to get fixed.
But untill i make rows available - the 9k records will not be accessible. it is there inside the pages and table. but just not accessible for query processing.
After those 1K records got fixed, i will load again. this iteration only i completed loading of 10K records.
So what do i do? i have to open up those loaded records for query access.

Import from <data file> of del insert into <table name> ROWS AVAILABLE
This command will go read the pages of table and make all the loaded rows accessible for query processing.like flip a switch inside the page of table.

this is a completely different way of importing or loading data with the ability to make the loaded rows available at user's will.
it exactly addresses my requirement, but i do not know if i am taking this too far.

thanks,
harish pathangay

Harishkumar .Pathangay

Is there a way i can validate the data before loadinginto the actual table?
(in response to Jim Tonchick)
Hi,
Oh I am sorry. It is not a immediate requirement or like I am going to wait for that feature.
I mean totally understand, one might ask so many features , only as users we can just post RFE’s.
The Decision is with IBM on what features to provide. Obviously they know more than me.
If I can think of some thing that eases a day of DBA’s work, that means a lot to me.

Thanks,
Harish Pathangay


Sent from Mail for Windows 10

From: Jim Tonchick
Sent: 26 August 2017 22:31
To: [login to unmask email]
Subject: [DB2-L] - RE: Is there a way i can validate the data before loadinginto the actual table?

You can't wait for IBM to add the feature you desire.  You have to work with what you have available right now.

Project prediction rule of thumb.   You have three options, do it right, do it fast and do it cheap.  You can only pick two options.  The the opposite of remaining option is the result.  Example, if you pick fast and right, it won't be cheap.  If you pick fast and cheap, it won't be right.


-----Original Message-----
From: Harishkumar Pathangay <[login to unmask email]>
To: Nadir Doctor <[login to unmask email]>
Sent: Sat, Aug 26, 2017 12:33 AM
Subject: [DB2-L] - RE: Is there a way i can validate the data before loading into the actual table?

thanks for the suggestion. it doubles my time for loading data.
why i am asking such a feature is because the clone table approach is not 100% beneficial:
• takes up additional space
• doubes time for loading data - i feel the data write portion [disk io intensive] is time consuming than data file processing [cpu intensive] - i can live with cpu intensive for a brief time but not disk intensive.
• i cannot go and take this approach for 20 tables of 20 GB each. i will end up with 400GB in duplicate temporary data.
• for validating data files i have to setup test data base separately if i do not want these impacts
• having additional feature always is good as it makes things easier
i have raised an RFE for this in IBM website. Rest leave it to them.

thanks,
harish pathangay
On Sat, Aug 26, 2017 at 1:20 AM, Jim Tonchick <[login to unmask email]> wrote:
Why not create a clone of the target table, load the new data there and see how much, if any, data gets rejected.  You can then fix the rejected data until it loads into the clone.  When all the new data has been validated in the clone, you can then move the clone contents to the real table using several methods i.e.(Unload/Load or SQL insert).


-----Original Message-----
From: Harishkumar .Pathangay <[login to unmask email]>
To: DB2-L <[login to unmask email]>
Sent: Fri, Aug 25, 2017 10:19 AM
Subject: [DB2-L] - Is there a way i can validate the data before loading into the actual table?

Hi,
Is there a way i can validate the data before loading into the actual table?
For Example, I have a table with 10,000 records. I am given a data file with 15,000 records.
Assume 3000 records are having some error [like null values in a non-null column, data type mismatch, data length mismatch etc.]
So before i load or import them i want to validate it. I understand that i can load and provide exception tables so i can easily figure out error records and fix them.
But here is the issue with that. It will load 12,000 successfully and make them available, which is not acceptable.
You either load 15k in total or remove them or the 12,000 that got loaded should not be accessible untill i fix the rest of 3k.
I do not want to quiesce the table until i fix the issue, table should be available with 10K records only.
How do i do that? I can validate in a different table or database itself, but then it is doubling my time.

I am looking for an option where i can say to import or load that parse the data file, but do not access table and write it to them.
In that way it will only spit errors in the data file. i can go fix it, then load the complete data.

How will i do this?

thanks,
harish pathangay


Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2



Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2




--
Thanks,
Harish P


Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2



Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2


Attachments

  • 0BBB7CA2124A4E63A0770E4848074FA2.png (<1k)
  • 5F5FA1E90989473F8948B6387B9CAC2F.png (<1k)

Jim Tonchick

Is there a way i can validate the data before loading into the actual table?
(in response to Harishkumar .Pathangay)
You can't wait for IBM to add the feature you desire. You have to work with what you have available right now.
<span style="font-size: 16px;">
</span>
<span style="font-size: 16px;">Project prediction rule of thumb. You have three options, do it right, do it fast and do it cheap. You can only pick two options. The the opposite of remaining option is the result. Example, if you pick fast and right, it won't be cheap. If you pick fast and cheap, it won't be right.
</span>

-----Original Message-----
From: Harishkumar Pathangay <[login to unmask email]>
To: Nadir Doctor <[login to unmask email]>
Sent: Sat, Aug 26, 2017 12:33 AM
Subject: [DB2-L] - RE: Is there a way i can validate the data before loading into the actual table?



<div id="AOLMsgPart_2_c5581611-b73b-483d-ad4a-f8c9396abe79">
<div class="aolReplacedBody"><div dir="ltr"><div><div>thanks for the suggestion. it doubles my time for loading data.
why i am asking such a feature is because the clone table approach is not 100% beneficial:
<ul><li>takes up additional space</li><li>doubes time for loading data - i feel the data write portion [disk io intensive] is time consuming than data file processing [cpu intensive] - i can live with cpu intensive for a brief time but not disk intensive.</li><li>i cannot go and take this approach for 20 tables of 20 GB each. i will end up with 400GB in duplicate temporary data.</li></ul></div>
<ul><li>for validating data files i have to setup test data base separately if i do not want these impacts</li><li>having additional feature always is good as it makes things easier</li></ul><div class="aolmail_gmail_extra">i have raised an RFE for this in IBM website. Rest leave it to them.

thanks,
</div><div class="aolmail_gmail_extra">harish pathangay

</div><div class="aolmail_gmail_extra"><div class="aolmail_gmail_quote">On Sat, Aug 26, 2017 at 1:20 AM, Jim Tonchick <span dir="ltr"><<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]">[login to unmask email]</a>></span> wrote:
<blockquote class="aolmail_gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Why not create a clone of the target table, load the new data there and see how much, if any, data gets rejected. You can then fix the rejected data until it loads into the clone. When all the new data has been validated in the clone, you can then move the clone contents to the real table using several methods i.e.(Unload/Load or SQL insert).<span>


-----Original Message-----
From: Harishkumar .Pathangay <<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]">[login to unmask email]</a>>
To: DB2-L <<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]">[login to unmask email]</a>>
Sent: Fri, Aug 25, 2017 10:19 AM
Subject: [DB2-L] - Is there a way i can validate the data before loading into the actual table?



</span><div id="aolmail_m_-3664349848576717519AOLMsgPart_2_730ec61b-0332-4531-9767-e0e6b8db086d">
<div class="aolmail_m_-3664349848576717519aolReplacedBody"><span><p>Hi,
Is there a way i can validate the data before loading into the actual table?
For Example, I have a table with 10,000 records. I am given a data file with 15,000 records.
Assume 3000 records are having some error [like null values in a non-null column, data type mismatch, data length mismatch etc.]
So before i load or import them i want to validate it. I understand that i can load and provide exception tables so i can easily figure out error records and fix them.
But here is the issue with that. It will load 12,000 successfully and make them available, which is not acceptable.
You either load 15k in total or remove them or the 12,000 that got loaded should not be accessible untill i fix the rest of 3k.
I do not want to quiesce the table until i fix the issue, table should be available with 10K records only.
How do i do that? I can validate in a different table or database itself, but then it is doubling my time.

I am looking for an option where i can say to import or load that parse the data file, but do not access table and write it to them.
In that way it will only spit errors in the data file. i can go fix it, then load the complete data.

How will i do this?

thanks,
harish pathangay</p>
</span><hr style="color:#ccc" size="1"><div id="aolmail_m_-3664349848576717519aolmail_socfooter" style="font-size:80%"><span><span style="font-weight:bold">Site Links: </span>
<a rel="noopener noreferrer" target="_blank" href="http://www.idug.org/p/fo/st/?post=182710&anc=p182710#p182710">View post online</a>
<a rel="noopener noreferrer" target="_blank" href="http://www.idug.org/p/fo/si/?topic=19">View mailing list online</a>
<a rel="noopener noreferrer" target="_blank" href="mailto:[login to unmask email]">Start new thread via email</a>
<a rel="noopener noreferrer" target="_blank" href="mailto:[login to unmask email]?Subject=Unsubscribe">Unsubscribe from this mailing list</a>
<a rel="noopener noreferrer" target="_blank" href="http://www.idug.org/p/us/to/">Manage your subscription</a>


</span>This email has been sent to: <a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]">[login to unmask email]</a><span>
<p>
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU

ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See

<a rel="noopener noreferrer" target="_blank" href="http://www.ESAIGroup.com/idug">http://www.ESAIGroup.com/idug</a>

</p>
<p>
Use of this email content is governed by the terms of service at:
<a rel="noopener noreferrer" target="_blank" href="http://www.idug.org/p/cm/ld/fid=2">http://www.idug.org/p/cm/ld/<wbr>fid=2</a></p>

</span></div><hr style="color:#ccc" size="1"></div>
</div><div class="aolmail_HOEnZb"><div class="aolmail_h5">

<hr style="color:#ccc" size="1"><div id="aolmail_m_-3664349848576717519socfooter" style="font-size:80%"><span style="font-weight:bold">Site Links: </span>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/fo/st/?post=182719&anc=p182719#p182719">View post online</a>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/fo/si/?topic=19">View mailing list online</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]">Start new thread via email</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]?Subject=Unsubscribe">Unsubscribe from this mailing list</a>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/us/to/">Manage your subscription</a>


This email has been sent to: <a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]">[login to unmask email]</a>
<p>
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU

ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See

<a target="_blank" rel="noopener noreferrer" href="http://www.ESAIGroup.com/idug">http://www.ESAIGroup.com/idug</a>

</p>
<p>
Use of this email content is governed by the terms of service at:
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/cm/ld/fid=2">http://www.idug.org/p/cm/ld/<wbr>fid=2</a></p>

</div><hr style="color:#ccc" size="1"></div></div></blockquote></div>
<br clear="all">
--
<div class="aolmail_gmail_signature">Thanks,
Harish P</div>
</div></div>

<hr size="1" style="color:#ccc"><div id="aolmail_socfooter" style="font-size:80%"><span style="font-weight:bold">Site Links: </span>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/fo/st/?post=182720&anc=p182720#p182720">View post online</a>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/fo/si/?topic=19">View mailing list online</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]">Start new thread via email</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]?Subject=Unsubscribe">Unsubscribe from this mailing list</a>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/us/to/">Manage your subscription</a>


This email has been sent to: <a href="mailto:[login to unmask email]">[login to unmask email]</a>
<p>
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU

ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See

<a target="_blank" rel="noopener noreferrer" href="http://www.ESAIGroup.com/idug">http://www.ESAIGroup.com/idug</a>

</p>
<p>
Use of this email content is governed by the terms of service at:
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/cm/ld/fid=2">http://www.idug.org/p/cm/ld/fid=2</a></p>

</div><hr size="1" style="color:#ccc"></div>
</div>
</div>

Charles Brown

Is there a way i can validate the data before loadinginto the actual table?
(in response to Harishkumar .Pathangay)
Hello Harish,
Hope all is well with you. BTW - your RFE request should be an excellent one. I'm saying that because a Db2 load validation feature is long overdue giving the fact that the Oracle loader (SQL*Loader) will do precisely and even more. Meaning load validation, transformation and extraction of input data. Therefore, at this time, we can all anxiously keep our fingers crossed with hopes that your RFE will be accepted and implemented. This will make for even a more competitive db2 -- my opinion.

Thx!
Charles
NZ DBA





Sent from my iPad

> On Aug 26, 2017, at 9:39 AM, Harishkumar .Pathangay <[login to unmask email]> wrote:
>
> Hi,
> Oh I am sorry. It is not a immediate requirement or like I am going to wait for that feature.
> I mean totally understand, one might ask so many features , only as users we can just post RFE’s.
> The Decision is with IBM on what features to provide. Obviously they know more than me.
> If I can think of some thing that eases a day of DBA’s work, that means a lot to me.
>
> Thanks,
> Harish Pathangay
>
>
> Sent from Mail for Windows 10
>
> From: Jim Tonchick
> Sent: 26 August 2017 22:31
> To: [login to unmask email]
> Subject: [DB2-L] - RE: Is there a way i can validate the data before loadinginto the actual table?
>
> You can't wait for IBM to add the feature you desire. You have to work with what you have available right now.
>
> Project prediction rule of thumb. You have three options, do it right, do it fast and do it cheap. You can only pick two options. The the opposite of remaining option is the result. Example, if you pick fast and right, it won't be cheap. If you pick fast and cheap, it won't be right.
>
>
> -----Original Message-----
> From: Harishkumar Pathangay <[login to unmask email]>
> To: Nadir Doctor <[login to unmask email]>
> Sent: Sat, Aug 26, 2017 12:33 AM
> Subject: [DB2-L] - RE: Is there a way i can validate the data before loading into the actual table?
>
>
> thanks for the suggestion. it doubles my time for loading data.
> why i am asking such a feature is because the clone table approach is not 100% beneficial:
> takes up additional space
> doubes time for loading data - i feel the data write portion [disk io intensive] is time consuming than data file processing [cpu intensive] - i can live with cpu intensive for a brief time but not disk intensive.
> i cannot go and take this approach for 20 tables of 20 GB each. i will end up with 400GB in duplicate temporary data.
> for validating data files i have to setup test data base separately if i do not want these impacts
> having additional feature always is good as it makes things easier
> i have raised an RFE for this in IBM website. Rest leave it to them.
>
> thanks,
> harish pathangay
>
> On Sat, Aug 26, 2017 at 1:20 AM, Jim Tonchick <[login to unmask email]> wrote:
> Why not create a clone of the target table, load the new data there and see how much, if any, data gets rejected. You can then fix the rejected data until it loads into the clone. When all the new data has been validated in the clone, you can then move the clone contents to the real table using several methods i.e.(Unload/Load or SQL insert).
>
>
> -----Original Message-----
> From: Harishkumar .Pathangay <[login to unmask email]>
> To: DB2-L <[login to unmask email]>
> Sent: Fri, Aug 25, 2017 10:19 AM
> Subject: [DB2-L] - Is there a way i can validate the data before loading into the actual table?
>
>
> Hi,
> Is there a way i can validate the data before loading into the actual table?
> For Example, I have a table with 10,000 records. I am given a data file with 15,000 records.
> Assume 3000 records are having some error [like null values in a non-null column, data type mismatch, data length mismatch etc.]
> So before i load or import them i want to validate it. I understand that i can load and provide exception tables so i can easily figure out error records and fix them.
> But here is the issue with that. It will load 12,000 successfully and make them available, which is not acceptable.
> You either load 15k in total or remove them or the 12,000 that got loaded should not be accessible untill i fix the rest of 3k.
> I do not want to quiesce the table until i fix the issue, table should be available with 10K records only.
> How do i do that? I can validate in a different table or database itself, but then it is doubling my time.
>
> I am looking for an option where i can say to import or load that parse the data file, but do not access table and write it to them.
> In that way it will only spit errors in the data file. i can go fix it, then load the complete data.
>
> How will i do this?
>
> thanks,
> harish pathangay
>
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
>
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
>
>
>
>
> --
> Thanks,
> Harish P
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
>
>
>
> -----End Original Message-----
>
>
>
> Attachment Links: 0BBB7CA2124A4E63A0770E4848074FA2.png (0 k) 5F5FA1E90989473F8948B6387B9CAC2F.png (0 k)
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Harishkumar .Pathangay

Is there a way i can validate the data before loadinginto the actual table?
(in response to Charles Brown)
thanks charles for those kind words.


On Sun, Aug 27, 2017 at 2:06 AM, Charles Brown <[login to unmask email]> wrote:

> Hello Harish,
> Hope all is well with you. BTW - your RFE request should be an excellent
> one. I'm saying that because a Db2 load validation feature is long
> overdue giving the fact that the Oracle loader (SQL*Loader) will do
> precisely and even more. Meaning load validation, transformation and
> extraction of input data. Therefore, at this time, we can all anxiously
> keep our fingers crossed with hopes that your RFE will be accepted and
> implemented. This will make for even a more competitive db2 -- my opinion.
>
> Thx!
> Charles
> NZ DBA
>
>
>
>
>
> Sent from my iPad
>
> On Aug 26, 2017, at 9:39 AM, Harishkumar .Pathangay <[login to unmask email]>
> wrote:
>
> Hi,
>
> Oh I am sorry. It is not a immediate requirement or like I am going to
> wait for that feature.
>
> I mean totally understand, one might ask so many features , only as users
> we can just post RFE’s.
>
> The Decision is with IBM on what features to provide. Obviously they know
> more than me.
>
> If I can think of some thing that eases a day of DBA’s work, that means a
> lot to me.
>
>
>
> Thanks,
>
> Harish Pathangay
>
>
>
>
>
> Sent from Mail https://go.microsoft.com/fwlink/?LinkId=550986 for
> Windows 10
>
>
>
> *From: *Jim Tonchick <[login to unmask email]>
> *Sent: *26 August 2017 22:31
> *To: *[login to unmask email]
> *Subject: *[DB2-L] - RE: Is there a way i can validate the data before
> loadinginto the actual table?
>
>
>
> You can't wait for IBM to add the feature you desire. You have to work
> with what you have available right now.
>
>
>
> Project prediction rule of thumb. You have three options, do it right,
> do it fast and do it cheap. You can only pick two options. The the
> opposite of remaining option is the result. Example, if you pick fast and
> right, it won't be cheap. If you pick fast and cheap, it won't be right.
>
>
> -----Original Message-----
> From: Harishkumar Pathangay <[login to unmask email]>
> To: Nadir Doctor <[login to unmask email]>
> Sent: Sat, Aug 26, 2017 12:33 AM
> Subject: [DB2-L] - RE: Is there a way i can validate the data before
> loading into the actual table?
>
> thanks for the suggestion. it doubles my time for loading data.
>
> why i am asking such a feature is because the clone table approach is not
> 100% beneficial:
>
> - takes up additional space
> - doubes time for loading data - i feel the data write portion [disk
> io intensive] is time consuming than data file processing [cpu intensive] -
> i can live with cpu intensive for a brief time but not disk intensive.
> - i cannot go and take this approach for 20 tables of 20 GB each. i
> will end up with 400GB in duplicate temporary data.
>
>
> - for validating data files i have to setup test data base separately
> if i do not want these impacts
> - having additional feature always is good as it makes things easier
>
> i have raised an RFE for this in IBM website. Rest leave it to them.
>
> thanks,
>
> harish pathangay
>
> On Sat, Aug 26, 2017 at 1:20 AM, Jim Tonchick <[login to unmask email]>
> wrote:
>
> Why not create a clone of the target table, load the new data there and
> see how much, if any, data gets rejected. You can then fix the rejected
> data until it loads into the clone. When all the new data has been
> validated in the clone, you can then move the clone contents to the real
> table using several methods i.e.(Unload/Load or SQL insert).
>
>
> -----Original Message-----
> From: Harishkumar .Pathangay <[login to unmask email]>
> To: DB2-L <[login to unmask email]>
> Sent: Fri, Aug 25, 2017 10:19 AM
> Subject: [DB2-L] - Is there a way i can validate the data before loading
> into the actual table?
>
> Hi,
> Is there a way i can validate the data before loading into the actual
> table?
> For Example, I have a table with 10,000 records. I am given a data file
> with 15,000 records.
> Assume 3000 records are having some error [like null values in a non-null
> column, data type mismatch, data length mismatch etc.]
> So before i load or import them i want to validate it. I understand that i
> can load and provide exception tables so i can easily figure out error
> records and fix them.
> But here is the issue with that. It will load 12,000 successfully and make
> them available, which is not acceptable.
> You either load 15k in total or remove them or the 12,000 that got loaded
> should not be accessible untill i fix the rest of 3k.
> I do not want to quiesce the table until i fix the issue, table should be
> available with 10K records only.
> How do i do that? I can validate in a different table or database itself,
> but then it is doubling my time.
>
> I am looking for an option where i can say to import or load that parse
> the data file, but do not access table and write it to them.
> In that way it will only spit errors in the data file. i can go fix it,
> then load the complete data.
>
> How will i do this?
>
> thanks,
> harish pathangay
>
>
>
> *Site Links: *View post online
> http://www.idug.org/p/fo/st/?post=182710&anc=p182710#p182710 View
> mailing list online http://www.idug.org/p/fo/si/?topic=19 Start new
> thread via email <[login to unmask email]> Unsubscribe from this mailing
> list <[login to unmask email]?Subject=Unsubscribe> Manage your
> subscription http://www.idug.org/p/us/to
>
> This email has been sent to: [login to unmask email]
>
> Setup a data refresh task in less time than it takes to make a cup of
> coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make
> you a hero to users. See
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
>
>
> *Site Links: *View post online
> http://www.idug.org/p/fo/st/?post=182719&anc=p182719#p182719 View
> mailing list online http://www.idug.org/p/fo/si/?topic=19 Start new
> thread via email <[login to unmask email]> Unsubscribe from this mailing
> list <[login to unmask email]?Subject=Unsubscribe> Manage your
> subscription http://www.idug.org/p/us/to
>
> This email has been sent to: [login to unmask email]
>
> Setup a data refresh task in less time than it takes to make a cup of
> coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make
> you a hero to users. See
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
>
>
>
> --
>
> Thanks,
> Harish P
>
>
>
> *Site Links: *View post online
> http://www.idug.org/p/fo/st/?post=182720&anc=p182720#p182720 View
> mailing list online http://www.idug.org/p/fo/si/?topic=19 Start new
> thread via email <[login to unmask email]> Unsubscribe from this mailing
> list <[login to unmask email]?Subject=Unsubscribe> Manage your
> subscription http://www.idug.org/p/us/to
>
> This email has been sent to: [login to unmask email]
>
> Setup a data refresh task in less time than it takes to make a cup of
> coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make
> you a hero to users. See
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
>
>
> -----End Original Message-----
>
>
>
> -----End Original Message-----
>
>
> -----End Original Message-----
>



--
Thanks,
Harish P

Nadir Doctor

Is there a way i can validate the data before loading into the actual table?
(in response to Jim Tonchick)
Very well said, Jim!

Fyi, such data validation functionality is already available in various etl
tools - like datastage and others.


Best Regards,
Nadir



On Sat, Aug 26, 2017 at 12:01 PM, Jim Tonchick <[login to unmask email]> wrote:

> You can't wait for IBM to add the feature you desire. You have to work
> with what you have available right now.
>
> Project prediction rule of thumb. You have three options, do it right,
> do it fast and do it cheap. You can only pick two options. The the
> opposite of remaining option is the result. Example, if you pick fast and
> right, it won't be cheap. If you pick fast and cheap, it won't be right.
>
>
> -----Original Message-----
> From: Harishkumar Pathangay <[login to unmask email]>
> To: Nadir Doctor <[login to unmask email]>
> Sent: Sat, Aug 26, 2017 12:33 AM
> Subject: [DB2-L] - RE: Is there a way i can validate the data before
> loading into the actual table?
>
>
> thanks for the suggestion. it doubles my time for loading data.
> why i am asking such a feature is because the clone table approach is not
> 100% beneficial:
>
> - takes up additional space
> - doubes time for loading data - i feel the data write portion [disk
> io intensive] is time consuming than data file processing [cpu intensive] -
> i can live with cpu intensive for a brief time but not disk intensive.
> - i cannot go and take this approach for 20 tables of 20 GB each. i
> will end up with 400GB in duplicate temporary data.
>
>
> - for validating data files i have to setup test data base separately
> if i do not want these impacts
> - having additional feature always is good as it makes things easier
>
> i have raised an RFE for this in IBM website. Rest leave it to them.
>
> thanks,
> harish pathangay
>
> On Sat, Aug 26, 2017 at 1:20 AM, Jim Tonchick <[login to unmask email]>
> wrote:
>
> Why not create a clone of the target table, load the new data there and
> see how much, if any, data gets rejected. You can then fix the rejected
> data until it loads into the clone. When all the new data has been
> validated in the clone, you can then move the clone contents to the real
> table using several methods i.e.(Unload/Load or SQL insert).
>
>
> -----Original Message-----
> From: Harishkumar .Pathangay <[login to unmask email]>
> To: DB2-L <[login to unmask email]>
> Sent: Fri, Aug 25, 2017 10:19 AM
> Subject: [DB2-L] - Is there a way i can validate the data before loading
> into the actual table?
>
>
> Hi,
> Is there a way i can validate the data before loading into the actual
> table?
> For Example, I have a table with 10,000 records. I am given a data file
> with 15,000 records.
> Assume 3000 records are having some error [like null values in a non-null
> column, data type mismatch, data length mismatch etc.]
> So before i load or import them i want to validate it. I understand that i
> can load and provide exception tables so i can easily figure out error
> records and fix them.
> But here is the issue with that. It will load 12,000 successfully and make
> them available, which is not acceptable.
> You either load 15k in total or remove them or the 12,000 that got loaded
> should not be accessible untill i fix the rest of 3k.
> I do not want to quiesce the table until i fix the issue, table should be
> available with 10K records only.
> How do i do that? I can validate in a different table or database itself,
> but then it is doubling my time.
>
> I am looking for an option where i can say to import or load that parse
> the data file, but do not access table and write it to them.
> In that way it will only spit errors in the data file. i can go fix it,
> then load the complete data.
>
> How will i do this?
>
> thanks,
> harish pathangay
>
> -----End Original Message-----
>

Jose Ramon Vazquez

RE: Is there a way i can validate the data before loading into the actual table?
(in response to Nadir Doctor)

Hi,

 

Take a look to BMC NGT Load product without SYSDISC ddname. In case of error data is as before LOAD RESUME.

 

Regards.

 

José Ramón Vázquez Alonso

[login to unmask email] www.grupobancopopular.es

Harishkumar .Pathangay

Is there a way i can validate the data before loading into the actual table?
(in response to Jose Ramon Vazquez)
i have raised RFE, rest leave it to IBM.
Please treat thread closed.

thanks,
harish pathangay

On Mon, Aug 28, 2017 at 1:26 PM, JOSE RAMON VAZQUEZ <[login to unmask email]>
wrote:

> Hi,
>
>
>
> Take a look to BMC NGT Load product without SYSDISC ddname. In case of
> error data is as before LOAD RESUME.
>
>
>
> Regards.
>
>
>
> *José Ramón Vázquez Alonso *
>
> [login to unmask email] www.grupobancopopular.es
>
> -----End Original Message-----
>



--
Thanks,
Harish P