CAST function and performance

[VPaul]

I hope I am posting to the correct group for assistance. Please advise me
if there is another group I should go to.

I am new to this, so I apologize if I don't explain this very well. I do
need some help. Thanks in advance.

I am trying to bring data in to a Visual Foxpro application from an AS/400.
I have the Client Access ODBC driver loaded on a PC running WinXP. The SQL
query I have made works fine, except it is really slow to pull a record. If
I eliminate the CAST function I can pull a record quickly with another
utility program. Also I have brought this data into Microsoft Access and
can pull a record quickly. I need the CAST function because the data is
stored as text and the Foxpro program requires it as a number. I realize it
may be easier to change the data stored as text to a number on the AS/400
side. I am told this cannot be done right now due to other programs
designed around it. I am posting my script below.

I am sorry if I have not used the correct terms above, I am learning on my
own.

select F4006.OAMLNM, F4006.OAADD1, F4006.OAADD2, F4006.OAADD3, F4006.OACTY1,
F4006.OAADDS, F4006.OAADDZ, F4006.OADOCO, F4006.OAANTY, F4201LA.SHASN,
F4006.OADCTO
from F4006, F4201LA
where F4006.OADOCO = CAST (SUBSTRING('<<P rompt>>',1,6)AS INTEGER) and
F4006.OAANTY = '2' and F4201LA.SHDOCO = F4006.OADOCO and OADCTO = SUBSTRING
('<<Prompt>>',1 0,2)

The '<<Prompt>>' is where my program prompts me for the input. It then
finds a record based on this prompt.
ie; I may enter '555123 SO' and need to look up a record with invoice #
555123 and type 'SO'. It needs to be entered just like this '555123 SO'
as it is scanned in with a barcode. There is too much programming involved
right now to change the input. Also there is one column of data that is
pulled from another table (F4201LA) and I have this written to pull it
correctly.

[Serge Rielau]

I'm not a DB2 for iSeries expert, but it could be that the version you
are using does not "fold" (compute) (presuming the input is a constant
rather a hostvariable or parameter marker) during teh compileation.
This may possibly throw the optimizer of giving you a suboptimal plan.
Coudl you do that computation in your ptogram and pass in the correct
values/hostvariable to begin with?

DB2 for LUW (the area of my expertiese) doesn't experience those issues
in general, so I'm a bit poking in the dark here....

Cheers
Serge

[VPaul]

Thank you for the suggestion. I may try that. Also I have heard that I may
be doing a JOIN and have not configured it correctly. Can you tell if there
is a JOIN going on here? How would I correct that?
"Serge Rielau" <sr*****@ca.ibm .com> wrote in message
news:2t******** *****@uni-berlin.de...

I'm not a DB2 for iSeries expert, but it could be that the version you
are using does not "fold" (compute) (presuming the input is a constant
rather a hostvariable or parameter marker) during teh compileation.
This may possibly throw the optimizer of giving you a suboptimal plan.
Coudl you do that computation in your ptogram and pass in the correct
values/hostvariable to begin with?

DB2 for LUW (the area of my expertiese) doesn't experience those issues
in general, so I'm a bit poking in the dark here....

Cheers
Serge

[Serge Rielau]

Yes, there is a join here.
The query is semantically equivalent to:

select F4006.OAMLNM, F4006.OAADD1, F4006.OAADD2, F4006.OAADD3, F4006.OACTY1,
F4006.OAADDS, F4006.OAADDZ, F4006.OADOCO, F4006.OAANTY, F4201LA.SHASN,
F4006.OADCTO
from F4006 INNER JOIN F4201LA ON F4201LA.SHDOCO = F4006.OADOCO
where F4006.OADOCO = CAST (SUBSTRING('<<P rompt>>',1,6)AS INTEGER) and
F4006.OAANTY = '2' and OADCTO = SUBSTRING
('<<Prompt>>',1 0,2)

I know that in DB2 for LUW the way you write this query would not
matter. The path chosen by the optimizer would be the same.
Possibly the system does not recognize teh transitivity that also pins
F4201LA.SHDOCO = CAST (SUBSTRING('<<P rompt>>',1,6)AS INTEGER)
Maybe adding this predicate would help?

Cheers
Serge

[Mark Yudkin]

There is a general rule that says that the fastest processing is the one you
don't do. Perhaps DB2 will fold your expression, perhaps not. There is no
reason why you can't evaluate it in your application up front.
<quote>
CAST (SUBSTRING('<<P rompt>>',1,6)AS INTEGER)
The '<<Prompt>>' is where my program prompts me for the input.
</quote>
It will be faster for you to pre-process the prompt in your application to
extract the invoice number from the user's input. This is even more so,
since you can verify the input (did the user correctly enter properly
formatted data) and thus avoid queries that fail when the first 6 characters
of the input are not a legal integer.

"VPaul" <vp*****@NOSPAM PLS.earthlink.n et> wrote in message
news:WO******** *********@newsr ead1.news.pas.e arthlink.net...

I hope I am posting to the correct group for assistance. Please advise me
if there is another group I should go to.

I am new to this, so I apologize if I don't explain this very well. I do
need some help. Thanks in advance.

I am trying to bring data in to a Visual Foxpro application from an
AS/400.
I have the Client Access ODBC driver loaded on a PC running WinXP. The
SQL
query I have made works fine, except it is really slow to pull a record.
If
I eliminate the CAST function I can pull a record quickly with another
utility program. Also I have brought this data into Microsoft Access and
can pull a record quickly. I need the CAST function because the data is
stored as text and the Foxpro program requires it as a number. I realize
it
may be easier to change the data stored as text to a number on the AS/400
side. I am told this cannot be done right now due to other programs
designed around it. I am posting my script below.

I am sorry if I have not used the correct terms above, I am learning on
my
own.

select F4006.OAMLNM, F4006.OAADD1, F4006.OAADD2, F4006.OAADD3,
F4006.OACTY1,
F4006.OAADDS, F4006.OAADDZ, F4006.OADOCO, F4006.OAANTY, F4201LA.SHASN,
F4006.OADCTO
from F4006, F4201LA
where F4006.OADOCO = CAST (SUBSTRING('<<P rompt>>',1,6)AS INTEGER) and
F4006.OAANTY = '2' and F4201LA.SHDOCO = F4006.OADOCO and OADCTO =
SUBSTRING
('<<Prompt>>',1 0,2)

The '<<Prompt>>' is where my program prompts me for the input. It then
finds a record based on this prompt.
ie; I may enter '555123 SO' and need to look up a record with invoice
#
555123 and type 'SO'. It needs to be entered just like this '555123 SO'
as it is scanned in with a barcode. There is too much programming
involved
right now to change the input. Also there is one column of data that is
pulled from another table (F4201LA) and I have this written to pull it
correctly.

[VPaul]

Thanks for your reply. I have been trying to find out in my application
where the <prompt> is stored so I can evaluate it from there. I have been
able to come up with a somewhat different solution that works at home on my
PC with the data in a MS Access table. However when I try it at my
customers site with their AS/400 I get errors. I am assuming that the
functions and expressions may not be supported by the AS/400. Can you look
at this and tell me how to modify this script that works in MS Access so it
will work at my customers site? Thanks
select F4006.OAMLNM, F4006.OAADD1, F4006.OAADD2, F4006.OAADD3, F4006.OACTY1,
F4006.OAADDS, F4006.OAADDZ, F4006.OADOCO, OAANTY, F4201.SHASN, OADCTO,
F4006.OADCTO

from f4006, F4201

where F4006.OADOCO &' '& F4006.OADCTO = '540030 SO' and F4006.OAANTY = '2'
and F4201.SHDOCO = F4006.OADOCO

Last time I tried this at my customers site I got an error about the token &
being invalid.

I have been looking into the CONCAT expression and have read that a binary
string cannot be concatenated with a character string. So I don't know if
this will totally work either.
"Mark Yudkin" <my************ ***********@boi ng.org> wrote in message
news:ck******** **@ngspool-d02.news.aol.co m...

There is a general rule that says that the fastest processing is the one you don't do. Perhaps DB2 will fold your expression, perhaps not. There is no
reason why you can't evaluate it in your application up front.
<quote>
CAST (SUBSTRING('<<P rompt>>',1,6)AS INTEGER)
The '<<Prompt>>' is where my program prompts me for the input.
</quote>
It will be faster for you to pre-process the prompt in your application to
extract the invoice number from the user's input. This is even more so,
since you can verify the input (did the user correctly enter properly
formatted data) and thus avoid queries that fail when the first 6 characters of the input are not a legal integer.

"VPaul" <vp*****@NOSPAM PLS.earthlink.n et> wrote in message
news:WO******** *********@newsr ead1.news.pas.e arthlink.net...

I hope I am posting to the correct group for assistance. Please advise me if there is another group I should go to.

I am new to this, so I apologize if I don't explain this very well. I do need some help. Thanks in advance.

I am trying to bring data in to a Visual Foxpro application from an
AS/400.
I have the Client Access ODBC driver loaded on a PC running WinXP. The
SQL
query I have made works fine, except it is really slow to pull a record.
If
I eliminate the CAST function I can pull a record quickly with another
utility program. Also I have brought this data into Microsoft Access and can pull a record quickly. I need the CAST function because the data is
stored as text and the Foxpro program requires it as a number. I realize it
may be easier to change the data stored as text to a number on the AS/400 side. I am told this cannot be done right now due to other programs
designed around it. I am posting my script below.

I am sorry if I have not used the correct terms above, I am learning on
my
own.

select F4006.OAMLNM, F4006.OAADD1, F4006.OAADD2, F4006.OAADD3,
F4006.OACTY1,
F4006.OAADDS, F4006.OAADDZ, F4006.OADOCO, F4006.OAANTY, F4201LA.SHASN,
F4006.OADCTO
from F4006, F4201LA
where F4006.OADOCO = CAST (SUBSTRING('<<P rompt>>',1,6)AS INTEGER) and
F4006.OAANTY = '2' and F4201LA.SHDOCO = F4006.OADOCO and OADCTO =
SUBSTRING
('<<Prompt>>',1 0,2)

The '<<Prompt>>' is where my program prompts me for the input. It then
finds a record based on this prompt.
ie; I may enter '555123 SO' and need to look up a record with invoice #
555123 and type 'SO'. It needs to be entered just like this '555123 SO' as it is scanned in with a barcode. There is too much programming
involved
right now to change the input. Also there is one column of data that is
pulled from another table (F4201LA) and I have this written to pull it
correctly.

[VPaul]

Thanks for your reply to me.

In RESPONSE to:....

"Possibly the system does not recognize teh transitivity that also pins
F4201LA.SHDOCO = CAST (SUBSTRING('<<P rompt>>',1,6)AS INTEGER)
Maybe adding this predicate would help?"

I tried this and eventually got a message about being out of resources. It
took an exceptionally long time before the error came up

"Serge Rielau" <sr*****@ca.ibm .com> wrote in message
news:2t******** *****@uni-berlin.de...

Yes, there is a join here.
The query is semantically equivalent to:

select F4006.OAMLNM, F4006.OAADD1, F4006.OAADD2, F4006.OAADD3, F4006.OACTY1, F4006.OAADDS, F4006.OAADDZ, F4006.OADOCO, F4006.OAANTY, F4201LA.SHASN,
F4006.OADCTO
from F4006 INNER JOIN F4201LA ON F4201LA.SHDOCO = F4006.OADOCO
where F4006.OADOCO = CAST (SUBSTRING('<<P rompt>>',1,6)AS INTEGER) and
F4006.OAANTY = '2' and OADCTO = SUBSTRING
('<<Prompt>>',1 0,2)

I know that in DB2 for LUW the way you write this query would not
matter. The path chosen by the optimizer would be the same.
Possibly the system does not recognize teh transitivity that also pins
F4201LA.SHDOCO = CAST (SUBSTRING('<<P rompt>>',1,6)AS INTEGER)
Maybe adding this predicate would help?

Cheers
Serge

[Mark Yudkin]

> Last time I tried this at my customers site I got an error about the token

&
being invalid. As you should have expected, given that it _is_ invalid.

I can't answer the rest of your question (CONCAT) as you haven't given your
schema.

DB2 is not MS Access. DB2 is a database, Access's "database" is a shared
file with an Access-specific dialect of a functionally limited SQL subset;
any resemblance being coincidental. MS Access can be used to front-end SQL
Server, DB2 and the like, but "Access tables" (aka Jet engine) are for
standalone home users with no ACID requirements.

I suggest you consult the DB2 documentation.

"VPaul" <vp*****@NOSPAM PLS.earthlink.n et> wrote in message
news:VO******** ********@newsre ad1.news.pas.ea rthlink.net... Thanks for your reply. I have been trying to find out in my application
where the <prompt> is stored so I can evaluate it from there. I have been
able to come up with a somewhat different solution that works at home on
my
PC with the data in a MS Access table. However when I try it at my
customers site with their AS/400 I get errors. I am assuming that the
functions and expressions may not be supported by the AS/400. Can you
look
at this and tell me how to modify this script that works in MS Access so
it
will work at my customers site? Thanks
select F4006.OAMLNM, F4006.OAADD1, F4006.OAADD2, F4006.OAADD3,
F4006.OACTY1,
F4006.OAADDS, F4006.OAADDZ, F4006.OADOCO, OAANTY, F4201.SHASN, OADCTO,
F4006.OADCTO

from f4006, F4201

where F4006.OADOCO &' '& F4006.OADCTO = '540030 SO' and F4006.OAANTY = '2'
and F4201.SHDOCO = F4006.OADOCO

Last time I tried this at my customers site I got an error about the token
&
being invalid.

I have been looking into the CONCAT expression and have read that a binary
string cannot be concatenated with a character string. So I don't know if
this will totally work either.
"Mark Yudkin" <my************ ***********@boi ng.org> wrote in message
news:ck******** **@ngspool-d02.news.aol.co m...

There is a general rule that says that the fastest processing is the one

you

don't do. Perhaps DB2 will fold your expression, perhaps not. There is no
reason why you can't evaluate it in your application up front.
<quote>
CAST (SUBSTRING('<<P rompt>>',1,6)AS INTEGER)
The '<<Prompt>>' is where my program prompts me for the input.
</quote>
It will be faster for you to pre-process the prompt in your application
to
extract the invoice number from the user's input. This is even more so,
since you can verify the input (did the user correctly enter properly
formatted data) and thus avoid queries that fail when the first 6

characters

of the input are not a legal integer.

"VPaul" <vp*****@NOSPAM PLS.earthlink.n et> wrote in message
news:WO******** *********@newsr ead1.news.pas.e arthlink.net...

>I hope I am posting to the correct group for assistance. Please advise me > if there is another group I should go to.
>
> I am new to this, so I apologize if I don't explain this very well. I do > need some help. Thanks in advance.
>
> I am trying to bring data in to a Visual Foxpro application from an
> AS/400.
> I have the Client Access ODBC driver loaded on a PC running WinXP. The
> SQL
> query I have made works fine, except it is really slow to pull a
> record.
> If
> I eliminate the CAST function I can pull a record quickly with another
> utility program. Also I have brought this data into Microsoft Access and > can pull a record quickly. I need the CAST function because the data
> is
> stored as text and the Foxpro program requires it as a number. I realize > it
> may be easier to change the data stored as text to a number on the AS/400 > side. I am told this cannot be done right now due to other programs
> designed around it. I am posting my script below.
>
> I am sorry if I have not used the correct terms above, I am learning
> on
> my
> own.
>
> select F4006.OAMLNM, F4006.OAADD1, F4006.OAADD2, F4006.OAADD3,
> F4006.OACTY1,
> F4006.OAADDS, F4006.OAADDZ, F4006.OADOCO, F4006.OAANTY, F4201LA.SHASN,
> F4006.OADCTO
> from F4006, F4201LA
> where F4006.OADOCO = CAST (SUBSTRING('<<P rompt>>',1,6)AS INTEGER) and
> F4006.OAANTY = '2' and F4201LA.SHDOCO = F4006.OADOCO and OADCTO =
> SUBSTRING
> ('<<Prompt>>',1 0,2)
>
> The '<<Prompt>>' is where my program prompts me for the input. It then
> finds a record based on this prompt.
> ie; I may enter '555123 SO' and need to look up a record with invoice > #
> 555123 and type 'SO'. It needs to be entered just like this '555123 SO' > as it is scanned in with a barcode. There is too much programming
> involved
> right now to change the input. Also there is one column of data that
> is
> pulled from another table (F4201LA) and I have this written to pull it
> correctly.
>
>