Access path issue (or maybe a java issue?)

Tina Hilton

Access path issue (or maybe a java issue?)
We have a java program that executes on the PC and gets data from DB2 on
OS/390 via DB2 Connect using SQLJ. We have an update that should be using
an index. When I do an explain via DSNTEP2, it says it will use the index.
When we bind the program and look at the explain output, it says it will do
a scan and it does. The update statement is:

UPDATE DB2ADM.AENVW017_ENRL_SRC
SET PS_SLVG_STAT_CD = :(myAenvw017.ps_slvg_stat_cd)
,CRDT_CARD_CO_CD = :(myAenvw017.crdt_card_co_cd)
,LAST_USER_ID = :(myAenvw017.last_user_id)
,LAST_PGM_ID = :(myAenvw017.last_pgm_id)
,LAST_MDFY_TS = CURRENT TIMESTAMP
WHERE ENRL_ID = :(myAenvw017.enrl_id)

ENRL_ID is the only column in the index and statistics are up-to-date. It's
a decimal, and the host variable is defined as java.math.BigDecimal
(actually "java.math.BigDecimal(0.0)" in case that makes a difference).
Data type mismatch is the only reason I can think of that the dsntep2
explain (I used ? for the host variables) says it would use the index but
the package doesn't. Any ideas will be greatly appreciated.

Thanks.

Tina Hilton
Random House Bertelsmann Shared Services


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ron Root

Re: Access path issue (or maybe a java issue?)
(in response to Tina Hilton)
MessageIt sure sounds like a data type mismatch. Do you have a tool or a
Rexx script that will read you DBRM to verify the host variable data type is
what you expect? If not, I can send you one.
Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts
(512) 936-5974

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Tina Hilton
Sent: Wednesday, January 14, 2004 10:10 AM
To: [login to unmask email]
Subject: Access path issue (or maybe a java issue?)



We have a java program that executes on the PC and gets data from DB2 on
OS/390 via DB2 Connect using SQLJ. We have an update that should be using
an index. When I do an explain via DSNTEP2, it says it will use the index.
When we bind the program and look at the explain output, it says it will do
a scan and it does. The update statement is:
UPDATE DB2ADM.AENVW017_ENRL_SRC
SET PS_SLVG_STAT_CD = :(myAenvw017.ps_slvg_stat_cd)
,CRDT_CARD_CO_CD = :(myAenvw017.crdt_card_co_cd)
,LAST_USER_ID = :(myAenvw017.last_user_id)
,LAST_PGM_ID = :(myAenvw017.last_pgm_id)
,LAST_MDFY_TS = CURRENT TIMESTAMP
WHERE ENRL_ID = :(myAenvw017.enrl_id)

ENRL_ID is the only column in the index and statistics are up-to-date.
It's a decimal, and the host variable is defined as java.math.BigDecimal
(actually "java.math.BigDecimal(0.0)" in case that makes a difference).
Data type mismatch is the only reason I can think of that the dsntep2
explain (I used ? for the host variables) says it would use the index but
the package doesn't. Any ideas will be greatly appreciated.

Thanks.

Tina Hilton
Random House Bertelsmann Shared Services

--------------------------------------------------------------------------
------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Simon George

Re: Access path issue (or maybe a java issue?)
(in response to Ron Root)
Hi Tina,

I've had problems with DECIMAL's and JAVA before. My first suggestion is to make sure that you have the latest version of the JDBC driver / db2profc, the earlier V7 one certainly had problems in this area. I "think" that the (0.0) bit on the host variable definition will make the profiler think you are mapping to a 0 scale decimal column (e.g. dec_col(15,0) ). If that's not what you have defined in the table that maybe your problem. Depending on your JAVA skills you may need to speak to a JAVA expert to decide what the (0.0) bit should be set to as from what I can see its not the same as length.scale in DB2 terms!

Hope that helps



Cheers Simon
[login to unmask email]
(1931) 74408 (internal)
(020) 7567 4408 (external)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Tina Hilton
Sent: 14 January 2004 16:10
To: [login to unmask email]
Subject: Access path issue (or maybe a java issue?)


We have a java program that executes on the PC and gets data from DB2 on OS/390 via DB2 Connect using SQLJ. We have an update that should be using an index. When I do an explain via DSNTEP2, it says it will use the index. When we bind the program and look at the explain output, it says it will do a scan and it does. The update statement is:

UPDATE DB2ADM.AENVW017_ENRL_SRC
SET PS_SLVG_STAT_CD = :(myAenvw017.ps_slvg_stat_cd)
,CRDT_CARD_CO_CD = :(myAenvw017.crdt_card_co_cd)
,LAST_USER_ID = :(myAenvw017.last_user_id)
,LAST_PGM_ID = :(myAenvw017.last_pgm_id)
,LAST_MDFY_TS = CURRENT TIMESTAMP
WHERE ENRL_ID = :(myAenvw017.enrl_id)

ENRL_ID is the only column in the index and statistics are up-to-date. It's a decimal, and the host variable is defined as java.math.BigDecimal (actually "java.math.BigDecimal(0.0)" in case that makes a difference). Data type mismatch is the only reason I can think of that the dsntep2 explain (I used ? for the host variables) says it would use the index but the package doesn't. Any ideas will be greatly appreciated.

Thanks.

Tina Hilton
Random House Bertelsmann Shared Services

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mail transmission cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, destroyed,
arrive late or incomplete, or contain viruses. The sender therefore
does not accept liability for any errors or omissions in the contents
of this message which arise as a result of e-mail transmission. If
verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities or
related financial instruments.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tina Hilton

Re: Access path issue (or maybe a java issue?)
(in response to Simon George)
I'd love to get that rexx Ron. I'm sure others would appreciate it if you'd
put it in DB2-L-DOCUMENTS so it's out there for everyone.

Tina

-----Original Message-----
From: Ron Root [mailto:[login to unmask email]
Sent: Wednesday, January 14, 2004 12:06 PM
To: [login to unmask email]
Subject: Re: Access path issue (or maybe a java issue?)


It sure sounds like a data type mismatch. Do you have a tool or a Rexx
script that will read you DBRM to verify the host variable data type is what
you expect? If not, I can send you one.

Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts
(512) 936-5974

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Tina Hilton
Sent: Wednesday, January 14, 2004 10:10 AM
To: [login to unmask email]
Subject: Access path issue (or maybe a java issue?)



We have a java program that executes on the PC and gets data from DB2 on
OS/390 via DB2 Connect using SQLJ. We have an update that should be using
an index. When I do an explain via DSNTEP2, it says it will use the index.
When we bind the program and look at the explain output, it says it will do
a scan and it does. The update statement is:

UPDATE DB2ADM.AENVW017_ENRL_SRC
SET PS_SLVG_STAT_CD = :(myAenvw017.ps_slvg_stat_cd)
,CRDT_CARD_CO_CD = :(myAenvw017.crdt_card_co_cd)
,LAST_USER_ID = :(myAenvw017.last_user_id)
,LAST_PGM_ID = :(myAenvw017.last_pgm_id)
,LAST_MDFY_TS = CURRENT TIMESTAMP
WHERE ENRL_ID = :(myAenvw017.enrl_id)

ENRL_ID is the only column in the index and statistics are up-to-date. It's
a decimal, and the host variable is defined as java.math.BigDecimal
(actually "java.math.BigDecimal(0.0)" in case that makes a difference).
Data type mismatch is the only reason I can think of that the dsntep2
explain (I used ? for the host variables) says it would use the index but
the package doesn't. Any ideas will be greatly appreciated.

Thanks.

Tina Hilton
Random House Bertelsmann Shared Services

----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Alex Pauliah

Re: Access path issue (or maybe a java issue?)
(in response to Tina Hilton)
Hi Ron,

Could you send me a copy of the script please !!

Thanks

Alex J. Pauliah
DB2 DBA
TXU Database Technology
W: (214) 486 6784
P: (214) 314 5973



Ron Root
<[login to unmask email] To: [login to unmask email]
TE.TX.US> cc:
Sent by: DB2 Data Subject: Re: Access path issue (or maybe a java issue?)
Base Discussion
List
<[login to unmask email]
ORG>


01/14/2004 11:06
AM
Please respond to
DB2 Database
Discussion list
at IDUG






It sure sounds like a data type mismatch. Do you have a tool or a Rexx
script that will read you DBRM to verify the host variable data type is
what you expect? If not, I can send you one.


Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts
(512) 936-5974


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf
Of Tina Hilton
Sent: Wednesday, January 14, 2004 10:10 AM
To: [login to unmask email]
Subject: Access path issue (or maybe a java issue?)



We have a java program that executes on the PC and gets data from DB2
on OS/390 via DB2 Connect using SQLJ. We have an update that should
be using an index. When I do an explain via DSNTEP2, it says it will
use the index. When we bind the program and look at the explain
output, it says it will do a scan and it does. The update statement
is:


UPDATE DB2ADM.AENVW017_ENRL_SRC

SET PS_SLVG_STAT_CD = :(myAenvw017.ps_slvg_stat_cd)
,CRDT_CARD_CO_CD = :(myAenvw017.crdt_card_co_cd)
,LAST_USER_ID = :(myAenvw017.last_user_id)
,LAST_PGM_ID = :(myAenvw017.last_pgm_id)
,LAST_MDFY_TS = CURRENT TIMESTAMP
WHERE ENRL_ID = :(myAenvw017.enrl_id)


ENRL_ID is the only column in the index and statistics are
up-to-date. It's a decimal, and the host variable is defined as
java.math.BigDecimal (actually "java.math.BigDecimal(0.0)" in case
that makes a difference). Data type mismatch is the only reason I
can think of that the dsntep2 explain (I used ? for the host
variables) says it would use the index but the package doesn't. Any
ideas will be greatly appreciated.


Thanks.


Tina Hilton
Random House Bertelsmann Shared Services


---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
and home page at http://www.idugdb2-l.org/archives/db2-l.html. From
that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm
---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm






*********************************************************************************
Confidentiality Notice: This email message, including any attachments,
contains or may contain confidential information intended only for the
addressee. If you are not an intended recipient of this message, be
advised that any reading, dissemination, forwarding, printing, copying
or other use of this message or its attachments is strictly prohibited. If
you have received this message in error, please notify the sender
immediately by reply message and delete this email message and any
attachments from your system.
*********************************************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tina Hilton

Re: Access path issue (or maybe a java issue?)
(in response to Alex Pauliah)
The column is decimal(9,0). I wasn't sure the 0.0 meant 0 places to the
left and the right or what. The jdk that comes with DB2 Connect is 1.2.
We're at 7.2, fixpak 3. We have 10a downloaded, but that update won't go
out for a while. I tested executing the program with that fixpak with no
change. I also re-ran db2profc and it still didn't use the index. I have
NO java skills. I'm just a poor DB2 sysprog trying to help my programmers
get this application going.

Thanks,

Tina
-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, January 14, 2004 12:10 PM
To: [login to unmask email]
Subject: Re: Access path issue (or maybe a java issue?)


Hi Tina,

I've had problems with DECIMAL's and JAVA before. My first suggestion is to
make sure that you have the latest version of the JDBC driver / db2profc,
the earlier V7 one certainly had problems in this area. I "think" that the
(0.0) bit on the host variable definition will make the profiler think you
are mapping to a 0 scale decimal column (e.g. dec_col(15,0) ). If that's not
what you have defined in the table that maybe your problem. Depending on
your JAVA skills you may need to speak to a JAVA expert to decide what the
(0.0) bit should be set to as from what I can see its not the same as
length.scale in DB2 terms!

Hope that helps



Cheers Simon
[login to unmask email]
(1931) 74408 (internal)
(020) 7567 4408 (external)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Tina Hilton
Sent: 14 January 2004 16:10
To: [login to unmask email]
Subject: Access path issue (or maybe a java issue?)


We have a java program that executes on the PC and gets data from DB2 on
OS/390 via DB2 Connect using SQLJ. We have an update that should be using
an index. When I do an explain via DSNTEP2, it says it will use the index.
When we bind the program and look at the explain output, it says it will do
a scan and it does. The update statement is:

UPDATE DB2ADM.AENVW017_ENRL_SRC
SET PS_SLVG_STAT_CD = :(myAenvw017.ps_slvg_stat_cd)
,CRDT_CARD_CO_CD = :(myAenvw017.crdt_card_co_cd)
,LAST_USER_ID = :(myAenvw017.last_user_id)
,LAST_PGM_ID = :(myAenvw017.last_pgm_id)
,LAST_MDFY_TS = CURRENT TIMESTAMP
WHERE ENRL_ID = :(myAenvw017.enrl_id)

ENRL_ID is the only column in the index and statistics are up-to-date. It's
a decimal, and the host variable is defined as java.math.BigDecimal
(actually "java.math.BigDecimal(0.0)" in case that makes a difference).
Data type mismatch is the only reason I can think of that the dsntep2
explain (I used ? for the host variables) says it would use the index but
the package doesn't. Any ideas will be greatly appreciated.

Thanks.

Tina Hilton
Random House Bertelsmann Shared Services

----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


Visit our website at http://www.ubs.com

This message contains confidential information and is intended only for the
individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and delete
this e-mail from your system.

E-mail transmission cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete, or contain viruses. The sender therefore does not accept
liability for any errors or omissions in the contents of this message which
arise as a result of e-mail transmission. If verification is required
please request a hard-copy version. This message is provided for
informational purposes and should not be construed as a solicitation or
offer to buy or sell any securities or related financial instruments.

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ron Root

Re: Access path issue (or maybe a java issue?)
(in response to Tina Hilton)
MessageTina,
I got the Rexx from the friendly folks at Neon support several years ago.
But I bet they won't mind if I share it. Of course, I hardly use it
anymore. We have BMC's SQL Explorer product. I use its Mismatch Analysis
function all the time. The nice thing about it is that it compares the host
variable type to the catalog and only reports the mismatches.
In any case, the Rexx clist should be in DB2-L-DOCUMENTS soon.

Ron


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf
Of Tina Hilton
Sent: Wednesday, January 14, 2004 12:06 PM
To: [login to unmask email]
Subject: Re: Access path issue (or maybe a java issue?)


I'd love to get that rexx Ron. I'm sure others would appreciate it if
you'd put it in DB2-L-DOCUMENTS so it's out there for everyone.

Tina
-----Original Message-----
From: Ron Root [mailto:[login to unmask email]
Sent: Wednesday, January 14, 2004 12:06 PM
To: [login to unmask email]
Subject: Re: Access path issue (or maybe a java issue?)


It sure sounds like a data type mismatch. Do you have a tool or a Rexx
script that will read you DBRM to verify the host variable data type is what
you expect? If not, I can send you one.
Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts
(512) 936-5974

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Tina Hilton
Sent: Wednesday, January 14, 2004 10:10 AM
To: [login to unmask email]
Subject: Access path issue (or maybe a java issue?)



We have a java program that executes on the PC and gets data from DB2
on OS/390 via DB2 Connect using SQLJ. We have an update that should be
using an index. When I do an explain via DSNTEP2, it says it will use the
index. When we bind the program and look at the explain output, it says it
will do a scan and it does. The update statement is:
UPDATE DB2ADM.AENVW017_ENRL_SRC
SET PS_SLVG_STAT_CD = :(myAenvw017.ps_slvg_stat_cd)
,CRDT_CARD_CO_CD = :(myAenvw017.crdt_card_co_cd)
,LAST_USER_ID = :(myAenvw017.last_user_id)
,LAST_PGM_ID = :(myAenvw017.last_pgm_id)
,LAST_MDFY_TS = CURRENT TIMESTAMP
WHERE ENRL_ID = :(myAenvw017.enrl_id)

ENRL_ID is the only column in the index and statistics are up-to-date.
It's a decimal, and the host variable is defined as java.math.BigDecimal
(actually "java.math.BigDecimal(0.0)" in case that makes a difference).
Data type mismatch is the only reason I can think of that the dsntep2
explain (I used ? for the host variables) says it would use the index but
the package doesn't. Any ideas will be greatly appreciated.

Thanks.

Tina Hilton
Random House Bertelsmann Shared Services

----------------------------------------------------------------------
----------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From
that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
--------------------------------------------------------------------------
------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Myron Miller

Re: Access path issue (or maybe a java issue?)
(in response to Ron Root)
Tina,
On your testing with 10a, did you run USEJDBC2 after installing 10a? Unless
you did that, you might not have the latest JDK for DB2 Connect? Its in
SQLLIB/JAVA12

Myron
--- Tina Hilton <[login to unmask email]> wrote:
> The column is decimal(9,0). I wasn't sure the 0.0 meant 0 places to the
> left and the right or what. The jdk that comes with DB2 Connect is 1.2.
> We're at 7.2, fixpak 3. We have 10a downloaded, but that update won't go
> out for a while. I tested executing the program with that fixpak with no
> change. I also re-ran db2profc and it still didn't use the index. I have
> NO java skills. I'm just a poor DB2 sysprog trying to help my programmers
> get this application going.
>
> Thanks,
>
> Tina
> -----Original Message-----
> From: [login to unmask email] [mailto:[login to unmask email]
> Sent: Wednesday, January 14, 2004 12:10 PM
> To: [login to unmask email]
> Subject: Re: Access path issue (or maybe a java issue?)
>
>
> Hi Tina,
>
> I've had problems with DECIMAL's and JAVA before. My first suggestion is to
> make sure that you have the latest version of the JDBC driver / db2profc,
> the earlier V7 one certainly had problems in this area. I "think" that the
> (0.0) bit on the host variable definition will make the profiler think you
> are mapping to a 0 scale decimal column (e.g. dec_col(15,0) ). If that's not
> what you have defined in the table that maybe your problem. Depending on
> your JAVA skills you may need to speak to a JAVA expert to decide what the
> (0.0) bit should be set to as from what I can see its not the same as
> length.scale in DB2 terms!
>
> Hope that helps
>
>
>
> Cheers Simon
> [login to unmask email]
> (1931) 74408 (internal)
> (020) 7567 4408 (external)
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
> Tina Hilton
> Sent: 14 January 2004 16:10
> To: [login to unmask email]
> Subject: Access path issue (or maybe a java issue?)
>
>
> We have a java program that executes on the PC and gets data from DB2 on
> OS/390 via DB2 Connect using SQLJ. We have an update that should be using
> an index. When I do an explain via DSNTEP2, it says it will use the index.
> When we bind the program and look at the explain output, it says it will do
> a scan and it does. The update statement is:
>
> UPDATE DB2ADM.AENVW017_ENRL_SRC
> SET PS_SLVG_STAT_CD = :(myAenvw017.ps_slvg_stat_cd)
> ,CRDT_CARD_CO_CD = :(myAenvw017.crdt_card_co_cd)
> ,LAST_USER_ID = :(myAenvw017.last_user_id)
> ,LAST_PGM_ID = :(myAenvw017.last_pgm_id)
> ,LAST_MDFY_TS = CURRENT TIMESTAMP
> WHERE ENRL_ID = :(myAenvw017.enrl_id)
>
> ENRL_ID is the only column in the index and statistics are up-to-date. It's
> a decimal, and the host variable is defined as java.math.BigDecimal
> (actually "java.math.BigDecimal(0.0)" in case that makes a difference).
> Data type mismatch is the only reason I can think of that the dsntep2
> explain (I used ? for the host variables) says it would use the index but
> the package doesn't. Any ideas will be greatly appreciated.
>
> Thanks.
>
> Tina Hilton
> Random House Bertelsmann Shared Services
>
> ----------------------------------------------------------------------------
> ----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences at
> http://conferences.idug.org/index.cfm
>
>
> Visit our website at http://www.ubs.com
>
> This message contains confidential information and is intended only for the
> individual named. If you are not the named addressee you should not
> disseminate, distribute or copy this e-mail. Please notify the sender
> immediately by e-mail if you have received this e-mail by mistake and delete
> this e-mail from your system.
>
> E-mail transmission cannot be guaranteed to be secure or error-free as
> information could be intercepted, corrupted, lost, destroyed, arrive late or
> incomplete, or contain viruses. The sender therefore does not accept
> liability for any errors or omissions in the contents of this message which
> arise as a result of e-mail transmission. If verification is required
> please request a hard-copy version. This message is provided for
> informational purposes and should not be construed as a solicitation or
> offer to buy or sell any securities or related financial instruments.
>
> ----------------------------------------------------------------------------
> -----
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
> "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
> The IDUG List Admins can be reached at [login to unmask email] Find
> out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
>
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
> "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
> The IDUG List Admins can be reached at [login to unmask email] Find
> out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Simon George

Re: Access path issue (or maybe a java issue?)
(in response to Myron Miller)
Hi Tina,

I know enough JAVA to be considered dangerous :-)

http://java/javadoc/jdk1.4/docs/api/java/math/BigDecimal.html

This link gives the full JDK 1.4 words on BigDecimal and what the bits mean and how you can change it - point your programmers at that.

That mapping should work, Ron's suggestion is therefore a good one, i.e. decompile the DBRM and see what you have got in there.

The only other suggestion I have is to change the data-type at one or both sides to Integer. On the Java side a primitive int or Integer object should map to a DB2 DECIMAL column if you can't change the table. It probably won't perform as well because you will get a intermediate object built in the driver but it will work a lot faster than non-index access!


Cheers Simon
[login to unmask email]
(1931) 74408 (internal)
(020) 7567 4408 (external)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Tina Hilton
Sent: 14 January 2004 18:37
To: [login to unmask email]
Subject: Re: Access path issue (or maybe a java issue?)


The column is decimal(9,0). I wasn't sure the 0.0 meant 0 places to the
left and the right or what. The jdk that comes with DB2 Connect is 1.2.
We're at 7.2, fixpak 3. We have 10a downloaded, but that update won't go
out for a while. I tested executing the program with that fixpak with no
change. I also re-ran db2profc and it still didn't use the index. I have
NO java skills. I'm just a poor DB2 sysprog trying to help my programmers
get this application going.

Thanks,

Tina
-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, January 14, 2004 12:10 PM
To: [login to unmask email]
Subject: Re: Access path issue (or maybe a java issue?)


Hi Tina,

I've had problems with DECIMAL's and JAVA before. My first suggestion is to
make sure that you have the latest version of the JDBC driver / db2profc,
the earlier V7 one certainly had problems in this area. I "think" that the
(0.0) bit on the host variable definition will make the profiler think you
are mapping to a 0 scale decimal column (e.g. dec_col(15,0) ). If that's not
what you have defined in the table that maybe your problem. Depending on
your JAVA skills you may need to speak to a JAVA expert to decide what the
(0.0) bit should be set to as from what I can see its not the same as
length.scale in DB2 terms!

Hope that helps



Cheers Simon
[login to unmask email]
(1931) 74408 (internal)
(020) 7567 4408 (external)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Tina Hilton
Sent: 14 January 2004 16:10
To: [login to unmask email]
Subject: Access path issue (or maybe a java issue?)


We have a java program that executes on the PC and gets data from DB2 on
OS/390 via DB2 Connect using SQLJ. We have an update that should be using
an index. When I do an explain via DSNTEP2, it says it will use the index.
When we bind the program and look at the explain output, it says it will do
a scan and it does. The update statement is:

UPDATE DB2ADM.AENVW017_ENRL_SRC
SET PS_SLVG_STAT_CD = :(myAenvw017.ps_slvg_stat_cd)
,CRDT_CARD_CO_CD = :(myAenvw017.crdt_card_co_cd)
,LAST_USER_ID = :(myAenvw017.last_user_id)
,LAST_PGM_ID = :(myAenvw017.last_pgm_id)
,LAST_MDFY_TS = CURRENT TIMESTAMP
WHERE ENRL_ID = :(myAenvw017.enrl_id)

ENRL_ID is the only column in the index and statistics are up-to-date. It's
a decimal, and the host variable is defined as java.math.BigDecimal
(actually "java.math.BigDecimal(0.0)" in case that makes a difference).
Data type mismatch is the only reason I can think of that the dsntep2
explain (I used ? for the host variables) says it would use the index but
the package doesn't. Any ideas will be greatly appreciated.

Thanks.

Tina Hilton
Random House Bertelsmann Shared Services

----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


Visit our website at http://www.ubs.com

This message contains confidential information and is intended only for the
individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and delete
this e-mail from your system.

E-mail transmission cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete, or contain viruses. The sender therefore does not accept
liability for any errors or omissions in the contents of this message which
arise as a result of e-mail transmission. If verification is required
please request a hard-copy version. This message is provided for
informational purposes and should not be construed as a solicitation or
offer to buy or sell any securities or related financial instruments.

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mail transmission cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, destroyed,
arrive late or incomplete, or contain viruses. The sender therefore
does not accept liability for any errors or omissions in the contents
of this message which arise as a result of e-mail transmission. If
verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities or
related financial instruments.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Jose Ramon Vazquez

Re: Access path issue (or maybe a java issue?)
(in response to Simon George)
We had this problem two years ago. The solution was FixPak 5 or
greater.



Regards.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tina Hilton

Re: Access path issue (or maybe a java issue?)
(in response to Jose Ramon Vazquez)
This url doesn't exist when I try to use it. I can't use Ron's rexx to
decompile the DBRM because there is no DBRMLIB since the program is on the
PC. Does anyone know if I can do that from syspackstmt?

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, January 15, 2004 3:39 AM
To: [login to unmask email]
Subject: Re: Access path issue (or maybe a java issue?)


Hi Tina,

I know enough JAVA to be considered dangerous :-)

http://java/javadoc/jdk1.4/docs/api/java/math/BigDecimal.html

This link gives the full JDK 1.4 words on BigDecimal and what the bits mean
and how you can change it - point your programmers at that.

That mapping should work, Ron's suggestion is therefore a good one, i.e.
decompile the DBRM and see what you have got in there.

The only other suggestion I have is to change the data-type at one or both
sides to Integer. On the Java side a primitive int or Integer object should
map to a DB2 DECIMAL column if you can't change the table. It probably won't
perform as well because you will get a intermediate object built in the
driver but it will work a lot faster than non-index access!


Cheers Simon
[login to unmask email]
(1931) 74408 (internal)
(020) 7567 4408 (external)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Tina Hilton
Sent: 14 January 2004 18:37
To: [login to unmask email]
Subject: Re: Access path issue (or maybe a java issue?)


The column is decimal(9,0). I wasn't sure the 0.0 meant 0 places to the
left and the right or what. The jdk that comes with DB2 Connect is 1.2.
We're at 7.2, fixpak 3. We have 10a downloaded, but that update won't go
out for a while. I tested executing the program with that fixpak with no
change. I also re-ran db2profc and it still didn't use the index. I have
NO java skills. I'm just a poor DB2 sysprog trying to help my programmers
get this application going.

Thanks,

Tina
-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, January 14, 2004 12:10 PM
To: [login to unmask email]
Subject: Re: Access path issue (or maybe a java issue?)


Hi Tina,

I've had problems with DECIMAL's and JAVA before. My first suggestion is to
make sure that you have the latest version of the JDBC driver / db2profc,
the earlier V7 one certainly had problems in this area. I "think" that the
(0.0) bit on the host variable definition will make the profiler think you
are mapping to a 0 scale decimal column (e.g. dec_col(15,0) ). If that's not
what you have defined in the table that maybe your problem. Depending on
your JAVA skills you may need to speak to a JAVA expert to decide what the
(0.0) bit should be set to as from what I can see its not the same as
length.scale in DB2 terms!

Hope that helps



Cheers Simon
[login to unmask email]
(1931) 74408 (internal)
(020) 7567 4408 (external)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Tina Hilton
Sent: 14 January 2004 16:10
To: [login to unmask email]
Subject: Access path issue (or maybe a java issue?)


We have a java program that executes on the PC and gets data from DB2 on
OS/390 via DB2 Connect using SQLJ. We have an update that should be using
an index. When I do an explain via DSNTEP2, it says it will use the index.
When we bind the program and look at the explain output, it says it will do
a scan and it does. The update statement is:

UPDATE DB2ADM.AENVW017_ENRL_SRC
SET PS_SLVG_STAT_CD = :(myAenvw017.ps_slvg_stat_cd)
,CRDT_CARD_CO_CD = :(myAenvw017.crdt_card_co_cd)
,LAST_USER_ID = :(myAenvw017.last_user_id)
,LAST_PGM_ID = :(myAenvw017.last_pgm_id)
,LAST_MDFY_TS = CURRENT TIMESTAMP
WHERE ENRL_ID = :(myAenvw017.enrl_id)

ENRL_ID is the only column in the index and statistics are up-to-date. It's
a decimal, and the host variable is defined as java.math.BigDecimal
(actually "java.math.BigDecimal(0.0)" in case that makes a difference). Data
type mismatch is the only reason I can think of that the dsntep2 explain (I
used ? for the host variables) says it would use the index but the package
doesn't. Any ideas will be greatly appreciated.

Thanks.

Tina Hilton
Random House Bertelsmann Shared Services

----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


Visit our website at http://www.ubs.com

This message contains confidential information and is intended only for the
individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and delete
this e-mail from your system.

E-mail transmission cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete, or contain viruses. The sender therefore does not accept
liability for any errors or omissions in the contents of this message which
arise as a result of e-mail transmission. If verification is required
please request a hard-copy version. This message is provided for
informational purposes and should not be construed as a solicitation or
offer to buy or sell any securities or related financial instruments.

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Visit our website at http://www.ubs.com

This message contains confidential information and is intended only for the
individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and delete
this e-mail from your system.

E-mail transmission cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete, or contain viruses. The sender therefore does not accept
liability for any errors or omissions in the contents of this message which
arise as a result of e-mail transmission. If verification is required
please request a hard-copy version. This message is provided for
informational purposes and should not be construed as a solicitation or
offer to buy or sell any securities or related financial instruments.

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: Access path issue (or maybe a java issue?)
(in response to Tina Hilton)
On Thu, 15 Jan 2004 09:53:18 -0500, Tina Hilton <[login to unmask email]>
wrote:

May I chip in? Unfortunately I have nothing to offer to solve your problem,
I only want to know, how can I get to Ron's REXX. It seems, he has put it
into the archive?


>This url doesn't exist when I try to use it. I can't use Ron's rexx to
>decompile the DBRM because there is no DBRMLIB since the program is on the
>PC. Does anyone know if I can do that from syspackstmt?
>
>-----Original Message-----
>From: [login to unmask email] [mailto:[login to unmask email]
>Sent: Thursday, January 15, 2004 3:39 AM
>To: [login to unmask email]
>Subject: Re: Access path issue (or maybe a java issue?)
>
>
>Hi Tina,
>
>I know enough JAVA to be considered dangerous :-)
>
>http://java/javadoc/jdk1.4/docs/api/java/math/BigDecimal.html
>
>This link gives the full JDK 1.4 words on BigDecimal and what the bits mean
>and how you can change it - point your programmers at that.
>
>That mapping should work, Ron's suggestion is therefore a good one, i.e.
>decompile the DBRM and see what you have got in there.
>
>The only other suggestion I have is to change the data-type at one or both
>sides to Integer. On the Java side a primitive int or Integer object should
>map to a DB2 DECIMAL column if you can't change the table. It probably
won't
>perform as well because you will get a intermediate object built in the
>driver but it will work a lot faster than non-index access!
>
>
>Cheers Simon
>[login to unmask email]
>(1931) 74408 (internal)
>(020) 7567 4408 (external)
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf
Of
>Tina Hilton
>Sent: 14 January 2004 18:37
>To: [login to unmask email]
>Subject: Re: Access path issue (or maybe a java issue?)
>
>
>The column is decimal(9,0). I wasn't sure the 0.0 meant 0 places to the
>left and the right or what. The jdk that comes with DB2 Connect is 1.2.
>We're at 7.2, fixpak 3. We have 10a downloaded, but that update won't go
>out for a while. I tested executing the program with that fixpak with no
>change. I also re-ran db2profc and it still didn't use the index. I have
>NO java skills. I'm just a poor DB2 sysprog trying to help my programmers
>get this application going.
>
>Thanks,
>
>Tina
>-----Original Message-----
>From: [login to unmask email] [mailto:[login to unmask email]
>Sent: Wednesday, January 14, 2004 12:10 PM
>To: [login to unmask email]
>Subject: Re: Access path issue (or maybe a java issue?)
>
>
>Hi Tina,
>
>I've had problems with DECIMAL's and JAVA before. My first suggestion is to
>make sure that you have the latest version of the JDBC driver / db2profc,
>the earlier V7 one certainly had problems in this area. I "think" that the
>(0.0) bit on the host variable definition will make the profiler think you
>are mapping to a 0 scale decimal column (e.g. dec_col(15,0) ). If that's
not
>what you have defined in the table that maybe your problem. Depending on
>your JAVA skills you may need to speak to a JAVA expert to decide what the
>(0.0) bit should be set to as from what I can see its not the same as
>length.scale in DB2 terms!
>
>Hope that helps
>
>
>
>Cheers Simon
>[login to unmask email]
>(1931) 74408 (internal)
>(020) 7567 4408 (external)
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf
Of
>Tina Hilton
>Sent: 14 January 2004 16:10
>To: [login to unmask email]
>Subject: Access path issue (or maybe a java issue?)
>
>
>We have a java program that executes on the PC and gets data from DB2 on
>OS/390 via DB2 Connect using SQLJ. We have an update that should be using
>an index. When I do an explain via DSNTEP2, it says it will use the index.
>When we bind the program and look at the explain output, it says it will do
>a scan and it does. The update statement is:
>
> UPDATE DB2ADM.AENVW017_ENRL_SRC
> SET PS_SLVG_STAT_CD = :(myAenvw017.ps_slvg_stat_cd)
> ,CRDT_CARD_CO_CD = :(myAenvw017.crdt_card_co_cd)
> ,LAST_USER_ID = :(myAenvw017.last_user_id)
> ,LAST_PGM_ID = :(myAenvw017.last_pgm_id)
> ,LAST_MDFY_TS = CURRENT TIMESTAMP
> WHERE ENRL_ID = :(myAenvw017.enrl_id)
>
>ENRL_ID is the only column in the index and statistics are up-to-date.
It's
>a decimal, and the host variable is defined as java.math.BigDecimal
>(actually "java.math.BigDecimal(0.0)" in case that makes a difference).
Data
>type mismatch is the only reason I can think of that the dsntep2 explain (I
>used ? for the host variables) says it would use the index but the package
>doesn't. Any ideas will be greatly appreciated.
>
>Thanks.
>
>Tina Hilton
>Random House Bertelsmann Shared Services
>
>---------------------------------------------------------------------------
-
>----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
and
>home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
>select "Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm
>
>
>Visit our website at http://www.ubs.com
>
>This message contains confidential information and is intended only for the
>individual named. If you are not the named addressee you should not
>disseminate, distribute or copy this e-mail. Please notify the sender
>immediately by e-mail if you have received this e-mail by mistake and
delete
>this e-mail from your system.
>
>E-mail transmission cannot be guaranteed to be secure or error-free as
>information could be intercepted, corrupted, lost, destroyed, arrive late
or
>incomplete, or contain viruses. The sender therefore does not accept
>liability for any errors or omissions in the contents of this message which
>arise as a result of e-mail transmission. If verification is required
>please request a hard-copy version. This message is provided for
>informational purposes and should not be construed as a solicitation or
>offer to buy or sell any securities or related financial instruments.
>
>---------------------------------------------------------------------------
-
>-----
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-
l.org.
>The IDUG List Admins can be reached at [login to unmask email]
Find
>out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
>
>---------------------------------------------------------------------------
-
>-----
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-
l.org.
>The IDUG List Admins can be reached at [login to unmask email]
Find
>out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
>
>Visit our website at http://www.ubs.com
>
>This message contains confidential information and is intended only for the
>individual named. If you are not the named addressee you should not
>disseminate, distribute or copy this e-mail. Please notify the sender
>immediately by e-mail if you have received this e-mail by mistake and
delete
>this e-mail from your system.
>
>E-mail transmission cannot be guaranteed to be secure or error-free as
>information could be intercepted, corrupted, lost, destroyed, arrive late
or
>incomplete, or contain viruses. The sender therefore does not accept
>liability for any errors or omissions in the contents of this message which
>arise as a result of e-mail transmission. If verification is required
>please request a hard-copy version. This message is provided for
>informational purposes and should not be construed as a solicitation or
>offer to buy or sell any securities or related financial instruments.
>
>---------------------------------------------------------------------------
-
>-----
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-
l.org.
>The IDUG List Admins can be reached at [login to unmask email]
Find
>out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

Hi

May I chip in?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

[login to unmask email]

Re: Access path issue (or maybe a java issue?)
(in response to Walter Janißen)
Take a look at the command db2bfd (Bind File Description Tool) in the
Command-Reference of Your DB2 for Windows (V7?)

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tina Hilton

Re: Access path issue (or maybe a java issue?)
(in response to matthias.wehrens@AMB-INFORMATIK.DE)
Thanks for this tip. After running this bat file, the explain showed that
it would use the index. So, the problem will be fixed by installing the new
client. Since we have more testing to do before we roll it out, though, I
played around with the optimization hints (hadn't done that yet!) and was
able to use that to get the program to use the indexes while still using the
old DB2 Connect client.

Thanks to everyone who offered suggestions.

Tina Hilton
Random House Bertelsmann Shared Services

-----Original Message-----
From: Myron Miller [mailto:[login to unmask email]
Sent: Wednesday, January 14, 2004 2:28 PM
To: [login to unmask email]
Subject: Re: Access path issue (or maybe a java issue?)


Tina,
On your testing with 10a, did you run USEJDBC2 after installing 10a?
Unless you did that, you might not have the latest JDK for DB2 Connect? Its
in SQLLIB/JAVA12

Myron
--- Tina Hilton <[login to unmask email]> wrote:
> The column is decimal(9,0). I wasn't sure the 0.0 meant 0 places to
> the left and the right or what. The jdk that comes with DB2 Connect
> is 1.2. We're at 7.2, fixpak 3. We have 10a downloaded, but that
> update won't go out for a while. I tested executing the program with
> that fixpak with no change. I also re-ran db2profc and it still
> didn't use the index. I have NO java skills. I'm just a poor DB2
> sysprog trying to help my programmers get this application going.
>
> Thanks,
>
> Tina
> -----Original Message-----
> From: [login to unmask email] [mailto:[login to unmask email]
> Sent: Wednesday, January 14, 2004 12:10 PM
> To: [login to unmask email]
> Subject: Re: Access path issue (or maybe a java issue?)
>
>
> Hi Tina,
>
> I've had problems with DECIMAL's and JAVA before. My first suggestion
> is to make sure that you have the latest version of the JDBC driver /
> db2profc, the earlier V7 one certainly had problems in this area. I
> "think" that the
> (0.0) bit on the host variable definition will make the profiler think you
> are mapping to a 0 scale decimal column (e.g. dec_col(15,0) ). If that's
not
> what you have defined in the table that maybe your problem. Depending on
> your JAVA skills you may need to speak to a JAVA expert to decide what the
> (0.0) bit should be set to as from what I can see its not the same as
> length.scale in DB2 terms!
>
> Hope that helps
>
>
>
> Cheers Simon
> [login to unmask email]
> (1931) 74408 (internal)
> (020) 7567 4408 (external)
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
> Behalf Of Tina Hilton
> Sent: 14 January 2004 16:10
> To: [login to unmask email]
> Subject: Access path issue (or maybe a java issue?)
>
>
> We have a java program that executes on the PC and gets data from DB2
> on OS/390 via DB2 Connect using SQLJ. We have an update that should
> be using an index. When I do an explain via DSNTEP2, it says it will
> use the index. When we bind the program and look at the explain
> output, it says it will do a scan and it does. The update statement
> is:
>
> UPDATE DB2ADM.AENVW017_ENRL_SRC
> SET PS_SLVG_STAT_CD = :(myAenvw017.ps_slvg_stat_cd)
> ,CRDT_CARD_CO_CD = :(myAenvw017.crdt_card_co_cd)
> ,LAST_USER_ID = :(myAenvw017.last_user_id)
> ,LAST_PGM_ID = :(myAenvw017.last_pgm_id)
> ,LAST_MDFY_TS = CURRENT TIMESTAMP
> WHERE ENRL_ID = :(myAenvw017.enrl_id)
>
> ENRL_ID is the only column in the index and statistics are up-to-date.
> It's a decimal, and the host variable is defined as
> java.math.BigDecimal (actually "java.math.BigDecimal(0.0)" in case
> that makes a difference). Data type mismatch is the only reason I can
> think of that the dsntep2 explain (I used ? for the host variables)
> says it would use the index but the package doesn't. Any ideas will
> be greatly appreciated.
>
> Thanks.
>
> Tina Hilton
> Random House Bertelsmann Shared Services
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: Access path issue (or maybe a java issue?)
(in response to Tina Hilton)
http://www.idugdb2-l.org/adminscripts/wa.exe?A2=ind0012&L=DB2-L-DOCUMENTS&P=R268&I=-3

is an example of such a piece of code. You'll need to make some
changes for your purposes.

James Campbell

On 15 Jan 2004 at 9:53, Tina Hilton wrote:

> This url doesn't exist when I try to use it. I can't use Ron's rexx to
> decompile the DBRM because there is no DBRMLIB since the program is on the
> PC. Does anyone know if I can do that from syspackstmt?
>
<rest snipped>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: Access path issue (or maybe a java issue?)
(in response to James Campbell)
On Fri, 16 Jan 2004 08:08:21 +1000, James Campbell
<[login to unmask email]> wrote:

Hello James

I just tried your hint, but unfornately, when I try to use the links in
that thread I get the following error message

The archive files could not be accessed, either because the list does not
have Web-accessible archives or because they are being updated. If you know
that the list has Web archives, please try again in about 30 seconds, and
report the problem if it persists for more than a few minutes. The file
that could not be opened is 'c:\Inetpub\WWWROOT\ARCHIVES\DB2-
L\upload\9205981612256752.tmp' and the error code was 2.

Now I don't know what to do? Should I send this error message to the list
owner?

>http://www.idugdb2-l.org/adminscripts/wa.exe?A2=ind0012&L=DB2-L-
DOCUMENTS&P=R268&I=-3
>
>is an example of such a piece of code. You'll need to make some
>changes for your purposes.
>
>James Campbell
>
>On 15 Jan 2004 at 9:53, Tina Hilton wrote:
>
>> This url doesn't exist when I try to use it. I can't use Ron's rexx to
>> decompile the DBRM because there is no DBRMLIB since the program is on
the
>> PC. Does anyone know if I can do that from syspackstmt?
>>
><rest snipped>
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Cuneyt Goksu

Re: Access path issue (or maybe a java issue?)
(in response to Walter Janißen)
I send this note on behalf of Larry Kintisch.

----
Hi Tina,
Give your programmers this URL:

http://www.cs.uwa.edu.au/programming/jdk1.4/api/java/math/BigDecimal.html

Also, have them try the following:

WHERE ENRL_ID = DEC(:(myAenvw017.enrl_id),9,0)

Larry Kintisch
----

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tina Hilton

Re: Access path issue (or maybe a java issue?)
(in response to Cuneyt Goksu)
Thanks! I remember reading about this code, but didn't need it. I didn't
think about using it for these java packages. I'm sure this will come in
handy.

-----Original Message-----
From: James Campbell [mailto:[login to unmask email]
Sent: Thursday, January 15, 2004 5:08 PM
To: [login to unmask email]
Subject: Re: Access path issue (or maybe a java issue?)


http://www.idugdb2-l.org/adminscripts/wa.exe?A2=ind0012&L=DB2-L-DOCUMENTS&P=
R268&I=-3

is an example of such a piece of code. You'll need to make some changes for
your purposes.

James Campbell

On 15 Jan 2004 at 9:53, Tina Hilton wrote:

> This url doesn't exist when I try to use it. I can't use Ron's rexx
> to decompile the DBRM because there is no DBRMLIB since the program is
> on the PC. Does anyone know if I can do that from syspackstmt?
>
<rest snipped>

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tina Hilton

Re: Access path issue (or maybe a java issue?)
(in response to Tina Hilton)
It still doesn't work. I can get to the post, but also get this error when
I try to download the attachments. I've carboned the list owner on this
email.

Tina Hilton


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Friday, January 16, 2004 2:16 AM
To: [login to unmask email]
Subject: Re: Access path issue (or maybe a java issue?)


On Fri, 16 Jan 2004 08:08:21 +1000, James Campbell
<[login to unmask email]> wrote:

Hello James

I just tried your hint, but unfornately, when I try to use the links in that
thread I get the following error message

The archive files could not be accessed, either because the list does not
have Web-accessible archives or because they are being updated. If you know
that the list has Web archives, please try again in about 30 seconds, and
report the problem if it persists for more than a few minutes. The file that
could not be opened is 'c:\Inetpub\WWWROOT\ARCHIVES\DB2-
L\upload\9205981612256752.tmp' and the error code was 2.

Now I don't know what to do? Should I send this error message to the list
owner?

>http://www.idugdb2-l.org/adminscripts/wa.exe?A2=ind0012&L=DB2-L-
DOCUMENTS&P=R268&I=-3
>
>is an example of such a piece of code. You'll need to make some
>changes for your purposes.
>
>James Campbell
>
>On 15 Jan 2004 at 9:53, Tina Hilton wrote:
>
>> This url doesn't exist when I try to use it. I can't use Ron's rexx
>> to decompile the DBRM because there is no DBRMLIB since the program
>> is on
the
>> PC. Does anyone know if I can do that from syspackstmt?
>>
><rest snipped>
>
>-----------------------------------------------------------------------
>----
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm