HPL Unload using query - mixing up the rows problem
An HPL (onpladm) unload job on IDS 10 / HP-UX 11 using a query ('select * from abc where a > 100') reported the right row count but wrote a file where some rows were truncated and merged with the next row; running the same query in dbaccess, or unloading without the WHERE clause, was fine. Suggestions included rechecking job mappings/formats, the output device definition, table schema, odd control characters in data, and contacting support. The poster found extra onpload processes were spawned for the query-based job and seemed to be writing over each other; adding a second file to the device array restored correct output, but no confirmed explanation or official fix is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have unload job that uses query expression to unload the data from table. After the unload job finishes it reports correct no. of rows that was unloaded. But when I check the output file I see that unload job didn't unload all the columns and have concatenated another row in some places, which is really messing things up. Could you somebody tell me what could be the reason ? For example if I am expecting output: 1|2|3|ABC 4|5|6|DEF I get: 1|2|4|5|6|DEF as one row.
On 04/07/07, mohitanchlia@gmail.com <mohitanchlia@gmail.com> wrote: > I have unload job that uses query expression to unload the data from > table. After the unload job finishes it reports correct no. of rows > that was unloaded. But when I check the output file I see that unload > job didn't unload all the columns and have concatenated another row in > some places, which is really messing things up. Could you somebody > tell me what could be the reason ? > > For example if I am expecting output: > > 1|2|3|ABC > 4|5|6|DEF > > I get: > > 1|2|4|5|6|DEF as one row. > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > Platform? O/S? IDS Version? Query? The contributors to this list are good, but no one (that I am aware of) claims to be psychic !! Keith
Some rows got concatenated but it unloaded the right number of rows?
so there are duplicate rows somewhere? or random extra rows?
What happens if you just run the query in dbaccess? does the same
concatenation occur?
On Jul 4, 2:39 am, scottishpoet <drybur...@yahoo.com> wrote:
> Some rows got concatenated but it unloaded the right number of rows?
> so there are duplicate rows somewhere? or random extra rows?
>
> What happens if you just run thequeryin dbaccess? does the same
> concatenation occur?
IDS 10 running on HP-UX 11. Query is a simple select * from abc where
a > 100;
Yes. Some rows got concatenated but log shows that right number of
rows were unloaded. So If I take the count of lines in the file that
has the unloaded data it has less number of rows because some of them
were concatenated. When I run thought dbaccess it unloads properly.
Also if I remove where clause from the query then it works fine.
mohitanchlia@gmail.com wrote:
> On Jul 4, 2:39 am, scottishpoet <drybur...@yahoo.com> wrote:
>> Some rows got concatenated but it unloaded the right number of rows?
>> so there are duplicate rows somewhere? or random extra rows?
>>
>> What happens if you just run thequeryin dbaccess? does the same
>> concatenation occur?
>
> IDS 10 running on HP-UX 11. Query is a simple select * from abc where
> a > 100;
>
> Yes. Some rows got concatenated but log shows that right number of
> rows were unloaded. So If I take the count of lines in the file that
> has the unloaded data it has less number of rows because some of them
> were concatenated. When I run thought dbaccess it unloads properly.
>
> Also if I remove where clause from the query then it works fine.
>
Weird...
Try this:
1- Confirm that the mappings and formats are OK... Or recreate your job.
2- Confirm that the output file is correctly defined... Just one file, not an
array etc.
3- If the above fails, post the table schema
4- Consider checking with support... but I never notice any similar bug...
5- Verify your data... can you have weird characters like CONTROL-??? in your data?
Regards
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
On Jul 4, 1:22 pm, Fernando Nunes <s...@domus.online.pt> wrote:
> mohitanch...@gmail.com wrote:
> > On Jul 4, 2:39 am, scottishpoet <drybur...@yahoo.com> wrote:
> >> Some rows got concatenated but it unloaded the right number of rows?
> >> so there are duplicate rows somewhere? or random extra rows?
>
> >> What happens if you just run thequeryin dbaccess? does the same
> >> concatenation occur?
>
> > IDS 10 running on HP-UX 11.Queryis a simple select * from abc where
> > a > 100;
>
> > Yes. Some rows got concatenated but log shows that right number of
> > rows were unloaded. So If I take the count of lines in the file that
> > has the unloaded data it has less number of rows because some of them
> > were concatenated. When I run thought dbaccess it unloads properly.
>
> > Also if I remove where clause from thequerythen it works fine.
>
> Weird...
> Try this:
>
> 1- Confirm that the mappings and formats are OK... Or recreate your job.
> 2- Confirm that the output file is correctly defined... Just one file, not an
> array etc.
> 3- If the above fails, post the table schema
> 4- Consider checking with support... but I never notice any similar bug...
> 5- Verify your data... can you have weird characters like CONTROL-??? in your data?
>
> Regards
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
I have already tried all the above. In fact when I use device having 2
files then I am getting correct count - bizzare. So to me it looked
like as if another process is writing to the same file and it's
getting mixed up. So there are my findings, but please let me know if
I am at right track:
1. I created a normal unload job with no queries. And I saw number of
onpload jobs spawned for this unload job are around 15.
2. Then I created query based job and I saw number of onpload
processes higher, I think it was 19 or 20.
3. Then I added another file to device array and recreated the query
based job and I saw number of onpload processes drop to 15. And this
got all the rows correctly.
this is so bizzare, I don't understand why onpladm spawn extra job for
point no. 2. And I think that's why rows are mixing up. I am not
getting the explanation because I want to make sure there are no
uncertainties in unload job, because it's imperative that these jobs
are reliable.