Join on mulltirow queries

I did say I'd try expanding the 63 host variables in a base table, by 12 to
match the 12 balances in an attached table to see if the single join would
work. (every single host variable, along with it's attached indicator
variable, would have to have OCCURS 12 added to it...)

I have to say, I started to do it and it just looked so horrible, I
couldn't.

Im going to settle for a base access followed by single multirow access to
any attached tables. If there is much difference in performance, I don't
believe it will be significant. (If I'm wrong about this and performance
looks like being a problem, THEN I'll HAVE to revisit it.)

Another possibility would be to use ADO and get a resultset right off the
bat, but I simply don't have time to go this route now. Fujitsu COBOL
requires ESQL with ODBC so that's what I'll use.

Sorry to disappoint anyone who was following this... :-)

Pete.
--
"I used to write COBOL...now I can do anything."

On Thu, 14 Aug 2008 22:44:33 +1200, "Pete Dashwood"
<dashwood@removethis.enternet.co.nz> wrote:

>I did say I'd try expanding the 63 host variables in a base table, by 12 to
>match the 12 balances in an attached table to see if the single join would
>work. (every single host variable, along with it's attached indicator
>variable, would have to have OCCURS 12 added to it...)
>
>I have to say, I started to do it and it just looked so horrible, I
>couldn't.
>
>Im going to settle for a base access followed by single multirow access to
>any attached tables. If there is much difference in performance, I don't
>believe it will be significant. (If I'm wrong about this and performance
>looks like being a problem, THEN I'll HAVE to revisit it.)
>
>Another possibility would be to use ADO and get a resultset right off the
>bat, but I simply don't have time to go this route now. Fujitsu COBOL
>requires ESQL with ODBC so that's what I'll use.
>
>Sorry to disappoint anyone who was following this... :-)
>
>Pete.

Pete,

From what you have told me about your customer, performance wont be an
issue.

But for your own info, dong single row accesses as you mention can be
the difference between running a job in 10 min or running same job in
30 hours. And this is by own experience of programs converted from
COBOL Indexed files to SQL without due consideration to performance,
and that I had to change.


Regarding your host variables, and indicator variables. indicator are
probably not required if you can live with the small performance hit
of using coalesce to return a non null value instead. 64 host
variables aint much. In the present client I am working with, one of
the tables has over 400 columns at the moment. COBOL I/O modules do
have all those referenced and no issues with it.

I can also supply you a few ADO samples, not the crap ones fujitsu
supplies.

Regards


Frederico Fonseca
ema il: frederico_fonseca at syssoft-int.com

"Frederico Fonseca" <real-email-in-msg-spam@email.com> wrote in message
news:g9ama4hfclr5v03fed2j1ge4kf8bpanlb0@4ax.com...
> On Thu, 14 Aug 2008 22:44:33 +1200, "Pete Dashwood"
> <dashwood@removethis.enternet.co.nz> wrote:
>
>>I did say I'd try expanding the 63 host variables in a base table, by 12
>>to
>>match the 12 balances in an attached table to see if the single join would
>>work. (every single host variable, along with it's attached indicator
>>variable, would have to have OCCURS 12 added to it...)
>>
>>I have to say, I started to do it and it just looked so horrible, I
>>couldn't.
>>
>>Im going to settle for a base access followed by single multirow access to
>>any attached tables. If there is much difference in performance, I don't
>>believe it will be significant. (If I'm wrong about this and performance
>>looks like being a problem, THEN I'll HAVE to revisit it.)
>>
>>Another possibility would be to use ADO and get a resultset right off the
>>bat, but I simply don't have time to go this route now. Fujitsu COBOL
>>requires ESQL with ODBC so that's what I'll use.
>>
>>Sorry to disappoint anyone who was following this... :-)
>>
>>Pete.
>
> Pete,
>
> From what you have told me about your customer, performance wont be an
> issue.
>
> But for your own info, dong single row accesses as you mention can be
> the difference between running a job in 10 min or running same job in
> 30 hours.

No. These are random interactive accesses (the application is written mainly
in PowerCOBOL). It will access a single row of the base table, then do a
multirow access to any attached tables. Obviously, a single join of the base
and attached tables would be better, but it simply doesn't work. For
multirow access it needs a Cartesian join, so the Base would need to load
the same row 12 times, while the attached table loaded its 12 balances. I
just couldn't bring myself to code it... it was really butt ugly... :-)

BTW, I haven't forgotten about sending you the final code. I am still
working on it, but you will get it when I'm happy with it. (Like, a final
draft... I'll incorporate any suggestions you care to make)

>And this is by own experience of programs converted from
> COBOL Indexed files to SQL without due consideration to performance,
> and that I had to change.
>

Sure, I can see it being problematic for batch.

>
> Regarding your host variables, and indicator variables. indicator are
> probably not required if you can live with the small performance hit
> of using coalesce to return a non null value instead.

Yes, I've never used coalesce. I saw some sample code the other day but
didn't really have time to go into it. I'll take another look. (I've never
liked those clumsy indicator variables...) Thanks.



>64 host
> variables aint much. In the present client I am working with, one of
> the tables has over 400 columns at the moment. COBOL I/O modules do
> have all those referenced and no issues with it.
>
> I can also supply you a few ADO samples, not the crap ones fujitsu
> supplies.

I have actually used ADO with Fujitsu some years back, just to try it out. I
think I am too far down the path to go back now, but I'll keep your offer in
mind.

BTW, the recent big Security Update release from MicroSoft caused all my
Fujitsu COBOL components that were using ODBC to fall over :-)

It was a major trauma. Anything using ODBC (odbc.info control file) simply
abended and called F3BDKSNP.exe, which then threw its hands up in horror and
proceeded to loop indefinitely.... Not much help from a program supposed to
handle abends :-) I was able to attach to it through VS 2008 and get a
disassembly and trace that showed it was trying to emit a message with a
code in it. According to Fujitsu docs, "contact tech support with this
code"... yeah, right :-)

After nearly three days of tracing and tracking DLLs, I found what was
causing the problem and fixed it. (I won't go into it all here, but I can
say it wasn't the fault of the MS updates; they simply triggered something
that was wrong anyway.)

Thanks for your support, Frederico.

Pete.
--
"I used to write COBOL...now I can do anything."