Effect of the host variable definition on access path

serdar ozkubulay

Effect of the host variable definition on access path
Hi,

An SQL makes tablespace scan in order to use a perfect index. Unfortunately the source of the program is lost. When I put same SQL to another COBOL program with proper host variables it uses index.

The two DBRMS are identical except consistency token. The question: does the wrong host variable definition effect the access path, if so how does DB2 know this because DBRM does not keep this information?

Thanks for any comment.


Serdar Sabri Ozkubulay

Akbank Bilgi Teknolojileri
Veri Yönetimi Grubu
Tel:+90-212-270 00 44 Dahili:10323
[login to unmask email]
























*************************************************************************************
Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Missy Case

Re: Effect of the host variable definition on access path
(in response to serdar ozkubulay)
DB2 has to make assumptions about the value of the host variable based on
many factors during bind time.

If you could rebind & set up using the REOPTVARS option, that would solve
your whole problem.


Missy Case
FDR
701-275-6358




"Serdar Sabri
Özkubulay To: [login to unmask email]
(B.T.V.Y.G.)" cc:
<Serdar.Ozkubulay@ bcc:
AKBANK.COM> Subject: Effect of the host variable definition on access path
Sent by: DB2 Data
Base Discussion
List
<[login to unmask email]
RG>


12/24/03 10:46 AM
Please respond to
DB2 Database
Discussion list at
IDUG






Hi,

An SQL makes tablespace scan in order to use a perfect index. Unfortunately
the source of the program is lost. When I put same SQL to another COBOL
program with proper host variables it uses index.

The two DBRMS are identical except consistency token. The question: does
the wrong host variable definition effect the access path, if so how does
DB2 know this because DBRM does not keep this information?

Thanks for any comment.


Serdar Sabri Ozkubulay

Akbank Bilgi Teknolojileri
Veri Yönetimi Grubu
Tel:+90-212-270 00 44 Dahili:10323
[login to unmask email]
























*************************************************************************************

Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli
olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla
ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini
baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu
mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem
teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk
iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve
bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin
deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini
yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

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

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". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

serdar ozkubulay

Effect of the host variable definition on access path
(in response to Missy Case)
To clarify:

Column CARD_NO is defined as char(6),
Host variable :HV_CARD_NO1 defined as char(6),
Host variable :HV_CARD_NO2 defined as char(7).

When you use in the where condition CARD_NO = :HV_CARD_NO1 it uses index
But, if you use CARD_NO = :HV_CARD_NO2 it makes tablespace scan.

I see this after the bind not on the runtime.

Serdar


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, December 24, 2003 7:01 PM
To: [login to unmask email]
Subject: Re: Effect of the host variable definition on access path


DB2 has to make assumptions about the value of the host variable based on many factors during bind time.

If you could rebind & set up using the REOPTVARS option, that would solve your whole problem.


Missy Case
FDR
701-275-6358




"Serdar Sabri
Özkubulay To: [login to unmask email]
(B.T.V.Y.G.)" cc:
<Serdar.Ozkubulay@ bcc:
AKBANK.COM> Subject: Effect of the host variable definition on access path
Sent by: DB2 Data
Base Discussion
List
<[login to unmask email]
RG>


12/24/03 10:46 AM
Please respond to
DB2 Database
Discussion list at
IDUG






Hi,

An SQL makes tablespace scan in order to use a perfect index. Unfortunately the source of the program is lost. When I put same SQL to another COBOL program with proper host variables it uses index.

The two DBRMS are identical except consistency token. The question: does the wrong host variable definition effect the access path, if so how does DB2 know this because DBRM does not keep this information?

Thanks for any comment.


Serdar Sabri Ozkubulay

Akbank Bilgi Teknolojileri
Veri Yönetimi Grubu
Tel:+90-212-270 00 44 Dahili:10323 [login to unmask email]
























*************************************************************************************

Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

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

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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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























*************************************************************************************
Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

goodrich

Re: Effect of the host variable definition on access path
(in response to serdar ozkubulay)
if host var longer than column, db2 will not use index.

----- Original Message -----
From: "Serdar Sabri Özkubulay (B.T.V.Y.G.)" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, December 25, 2003 4:50 AM
Subject: Effect of the host variable definition on access path


To clarify:

Column CARD_NO is defined as char(6),
Host variable :HV_CARD_NO1 defined as char(6),
Host variable :HV_CARD_NO2 defined as char(7).

When you use in the where condition CARD_NO = :HV_CARD_NO1 it uses index
But, if you use CARD_NO = :HV_CARD_NO2 it makes tablespace scan.

I see this after the bind not on the runtime.

Serdar


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, December 24, 2003 7:01 PM
To: [login to unmask email]
Subject: Re: Effect of the host variable definition on access path


DB2 has to make assumptions about the value of the host variable based on
many factors during bind time.

If you could rebind & set up using the REOPTVARS option, that would solve
your whole problem.


Missy Case
FDR
701-275-6358




"Serdar Sabri
Özkubulay To:
[login to unmask email]
(B.T.V.Y.G.)" cc:
<Serdar.Ozkubulay@ bcc:
AKBANK.COM> Subject: Effect of the host
variable definition on access path
Sent by: DB2 Data
Base Discussion
List
<[login to unmask email]
RG>


12/24/03 10:46 AM
Please respond to
DB2 Database
Discussion list at
IDUG






Hi,

An SQL makes tablespace scan in order to use a perfect index. Unfortunately
the source of the program is lost. When I put same SQL to another COBOL
program with proper host variables it uses index.

The two DBRMS are identical except consistency token. The question: does the
wrong host variable definition effect the access path, if so how does DB2
know this because DBRM does not keep this information?

Thanks for any comment.


Serdar Sabri Ozkubulay

Akbank Bilgi Teknolojileri
Veri Yönetimi Grubu
Tel:+90-212-270 00 44 Dahili:10323 [login to unmask email]
























****************************************************************************
*********

Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup,
yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa
lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir
kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi
sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý,
satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý
amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün
garantisi verilmemekte olup, önceden bildirilmeksizin
deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini
yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

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

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". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] 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























****************************************************************************
*********
Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup,
yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa
lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir
kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi
sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý,
satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý
amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün
garantisi verilmemekte olup, önceden bildirilmeksizin
deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini
yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

----------------------------------------------------------------------------
-----
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". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

serdar ozkubulay

Re: Effect of the host variable definition on access path
(in response to goodrich)
But how or when does it sense this? This info is not kept in DBRM

-----Original Message-----
From: goodrich [mailto:[login to unmask email]
Sent: Thursday, December 25, 2003 1:12 PM
To: [login to unmask email]
Subject: Re: Effect of the host variable definition on access path


if host var longer than column, db2 will not use index.

----- Original Message -----
From: "Serdar Sabri Özkubulay (B.T.V.Y.G.)" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, December 25, 2003 4:50 AM
Subject: Effect of the host variable definition on access path


To clarify:

Column CARD_NO is defined as char(6),
Host variable :HV_CARD_NO1 defined as char(6),
Host variable :HV_CARD_NO2 defined as char(7).

When you use in the where condition CARD_NO = :HV_CARD_NO1 it uses index But, if you use CARD_NO = :HV_CARD_NO2 it makes tablespace scan.

I see this after the bind not on the runtime.

Serdar


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, December 24, 2003 7:01 PM
To: [login to unmask email]
Subject: Re: Effect of the host variable definition on access path


DB2 has to make assumptions about the value of the host variable based on many factors during bind time.

If you could rebind & set up using the REOPTVARS option, that would solve your whole problem.


Missy Case
FDR
701-275-6358




"Serdar Sabri
Özkubulay To:
[login to unmask email]
(B.T.V.Y.G.)" cc:
<Serdar.Ozkubulay@ bcc:
AKBANK.COM> Subject: Effect of the host
variable definition on access path
Sent by: DB2 Data
Base Discussion
List
<[login to unmask email]
RG>


12/24/03 10:46 AM
Please respond to
DB2 Database
Discussion list at
IDUG






Hi,

An SQL makes tablespace scan in order to use a perfect index. Unfortunately the source of the program is lost. When I put same SQL to another COBOL program with proper host variables it uses index.

The two DBRMS are identical except consistency token. The question: does the wrong host variable definition effect the access path, if so how does DB2 know this because DBRM does not keep this information?

Thanks for any comment.


Serdar Sabri Ozkubulay

Akbank Bilgi Teknolojileri
Veri Yönetimi Grubu
Tel:+90-212-270 00 44 Dahili:10323 [login to unmask email]
























****************************************************************************
*********

Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

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

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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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























****************************************************************************
*********
Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

----------------------------------------------------------------------------
-----
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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






















*************************************************************************************
Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

goodrich

Re: Effect of the host variable definition on access path
(in response to serdar ozkubulay)
Review the admin guide regarding stage1 and indexable
predicates. The optimizer 'senses' this when he binds
the package.

thanks, Jake Goodrich

----- Original Message -----
From: "Serdar Sabri Özkubulay (B.T.V.Y.G.)" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, December 25, 2003 6:32 AM
Subject: Re: Effect of the host variable definition on access path


But how or when does it sense this? This info is not kept in DBRM

-----Original Message-----
From: goodrich [mailto:[login to unmask email]
Sent: Thursday, December 25, 2003 1:12 PM
To: [login to unmask email]
Subject: Re: Effect of the host variable definition on access path


if host var longer than column, db2 will not use index.

----- Original Message -----
From: "Serdar Sabri Özkubulay (B.T.V.Y.G.)" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, December 25, 2003 4:50 AM
Subject: Effect of the host variable definition on access path


To clarify:

Column CARD_NO is defined as char(6),
Host variable :HV_CARD_NO1 defined as char(6),
Host variable :HV_CARD_NO2 defined as char(7).

When you use in the where condition CARD_NO = :HV_CARD_NO1 it uses index
But, if you use CARD_NO = :HV_CARD_NO2 it makes tablespace scan.

I see this after the bind not on the runtime.

Serdar


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, December 24, 2003 7:01 PM
To: [login to unmask email]
Subject: Re: Effect of the host variable definition on access path


DB2 has to make assumptions about the value of the host variable based on
many factors during bind time.

If you could rebind & set up using the REOPTVARS option, that would solve
your whole problem.


Missy Case
FDR
701-275-6358




"Serdar Sabri
Özkubulay To:
[login to unmask email]
(B.T.V.Y.G.)" cc:
<Serdar.Ozkubulay@ bcc:
AKBANK.COM> Subject: Effect of the host
variable definition on access path
Sent by: DB2 Data
Base Discussion
List
<[login to unmask email]
RG>


12/24/03 10:46 AM
Please respond to
DB2 Database
Discussion list at
IDUG






Hi,

An SQL makes tablespace scan in order to use a perfect index. Unfortunately
the source of the program is lost. When I put same SQL to another COBOL
program with proper host variables it uses index.

The two DBRMS are identical except consistency token. The question: does the
wrong host variable definition effect the access path, if so how does DB2
know this because DBRM does not keep this information?

Thanks for any comment.


Serdar Sabri Ozkubulay

Akbank Bilgi Teknolojileri
Veri Yönetimi Grubu
Tel:+90-212-270 00 44 Dahili:10323 [login to unmask email]
























****************************************************************************
*********

Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup,
yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa
lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir
kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi
sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý,
satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý
amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün
garantisi verilmemekte olup, önceden bildirilmeksizin
deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini
yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

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

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". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] 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























****************************************************************************
*********
Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup,
yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa
lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir
kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi
sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý,
satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý
amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün
garantisi verilmemekte olup, önceden bildirilmeksizin
deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini
yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

----------------------------------------------------------------------------
-----
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". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] 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






















****************************************************************************
*********
Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup,
yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa
lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir
kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi
sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý,
satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý
amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün
garantisi verilmemekte olup, önceden bildirilmeksizin
deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini
yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

----------------------------------------------------------------------------
-----
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". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Ali OZTURK

Re: Effect of the host variable definition on access path
(in response to goodrich)
Hi Serdar,
Additional info for this case,
For the character type columns ; if you use same or smaller length for the host variable, program uses index. If you don't want to encounter this kind of problem ,you should use DCLGEN output for host variables.
DB2 decides access path at bind time for static sqls.

regards.

Ali Ozturk
Pamukbank
db2 dba


-----Original Message-----
From: Serdar Sabri Özkubulay (B.T.V.Y.G.)
[mailto:[login to unmask email]
Sent: Thursday, December 25, 2003 12:51 PM
To: [login to unmask email]
Subject: Effect of the host variable definition on access path


To clarify:

Column CARD_NO is defined as char(6),
Host variable :HV_CARD_NO1 defined as char(6),
Host variable :HV_CARD_NO2 defined as char(7).

When you use in the where condition CARD_NO = :HV_CARD_NO1 it uses index
But, if you use CARD_NO = :HV_CARD_NO2 it makes tablespace scan.

I see this after the bind not on the runtime.

Serdar


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, December 24, 2003 7:01 PM
To: [login to unmask email]
Subject: Re: Effect of the host variable definition on access path


DB2 has to make assumptions about the value of the host variable based on many factors during bind time.

If you could rebind & set up using the REOPTVARS option, that would solve your whole problem.


Missy Case
FDR
701-275-6358




"Serdar Sabri
Özkubulay To: [login to unmask email]
(B.T.V.Y.G.)" cc:
<Serdar.Ozkubulay@ bcc:
AKBANK.COM> Subject: Effect of the host variable definition on access path
Sent by: DB2 Data
Base Discussion
List
<[login to unmask email]
RG>


12/24/03 10:46 AM
Please respond to
DB2 Database
Discussion list at
IDUG






Hi,

An SQL makes tablespace scan in order to use a perfect index. Unfortunately the source of the program is lost. When I put same SQL to another COBOL program with proper host variables it uses index.

The two DBRMS are identical except consistency token. The question: does the wrong host variable definition effect the access path, if so how does DB2 know this because DBRM does not keep this information?

Thanks for any comment.


Serdar Sabri Ozkubulay

Akbank Bilgi Teknolojileri
Veri Yönetimi Grubu
Tel:+90-212-270 00 44 Dahili:10323 [login to unmask email]
























*************************************************************************************

Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

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

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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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























*************************************************************************************
Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

serdar ozkubulay

Re: Effect of the host variable definition on access path
(in response to Ali OZTURK)
Thank you Jake and Ali.
I am looking a workaround because I have no source code in this case.

Serdar


-----Original Message-----
From: goodrich [mailto:[login to unmask email]
Sent: Thursday, December 25, 2003 2:41 PM
To: [login to unmask email]
Subject: Re: Effect of the host variable definition on access path


Review the admin guide regarding stage1 and indexable predicates. The optimizer 'senses' this when he binds the package.

thanks, Jake Goodrich

----- Original Message -----
From: "Serdar Sabri Özkubulay (B.T.V.Y.G.)" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, December 25, 2003 6:32 AM
Subject: Re: Effect of the host variable definition on access path


But how or when does it sense this? This info is not kept in DBRM

-----Original Message-----
From: goodrich [mailto:[login to unmask email]
Sent: Thursday, December 25, 2003 1:12 PM
To: [login to unmask email]
Subject: Re: Effect of the host variable definition on access path


if host var longer than column, db2 will not use index.

----- Original Message -----
From: "Serdar Sabri Özkubulay (B.T.V.Y.G.)" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, December 25, 2003 4:50 AM
Subject: Effect of the host variable definition on access path


To clarify:

Column CARD_NO is defined as char(6),
Host variable :HV_CARD_NO1 defined as char(6),
Host variable :HV_CARD_NO2 defined as char(7).

When you use in the where condition CARD_NO = :HV_CARD_NO1 it uses index But, if you use CARD_NO = :HV_CARD_NO2 it makes tablespace scan.

I see this after the bind not on the runtime.

Serdar


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, December 24, 2003 7:01 PM
To: [login to unmask email]
Subject: Re: Effect of the host variable definition on access path


DB2 has to make assumptions about the value of the host variable based on many factors during bind time.

If you could rebind & set up using the REOPTVARS option, that would solve your whole problem.


Missy Case
FDR
701-275-6358




"Serdar Sabri
Özkubulay To:
[login to unmask email]
(B.T.V.Y.G.)" cc:
<Serdar.Ozkubulay@ bcc:
AKBANK.COM> Subject: Effect of the host
variable definition on access path
Sent by: DB2 Data
Base Discussion
List
<[login to unmask email]
RG>


12/24/03 10:46 AM
Please respond to
DB2 Database
Discussion list at
IDUG






Hi,

An SQL makes tablespace scan in order to use a perfect index. Unfortunately the source of the program is lost. When I put same SQL to another COBOL program with proper host variables it uses index.

The two DBRMS are identical except consistency token. The question: does the wrong host variable definition effect the access path, if so how does DB2 know this because DBRM does not keep this information?

Thanks for any comment.


Serdar Sabri Ozkubulay

Akbank Bilgi Teknolojileri
Veri Yönetimi Grubu
Tel:+90-212-270 00 44 Dahili:10323 [login to unmask email]
























****************************************************************************
*********

Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

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

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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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























****************************************************************************
*********
Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

----------------------------------------------------------------------------
-----
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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






















****************************************************************************
*********
Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

----------------------------------------------------------------------------
-----
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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






















*************************************************************************************
Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Peter Backlund

Re: Effect of the host variable definition on access path
(in response to serdar ozkubulay)
The DBRM actually contains information about hostvariables
I don't have access to a DB2 from home, but after the holidays....

Peter

Serdar Sabri Özkubulay (B.T.V.Y.G.) wrote:
But how or when does it sense this? This info is not kept in DBRM -----Original Message----- From: goodrich [mailto:[login to unmask email]] Sent: Thursday, December 25, 2003 1:12 PM To: [login to unmask email] Subject: Re: Effect of the host variable definition on access path if host var longer than column, db2 will not use index. ----- Original Message ----- From: "Serdar Sabri Özkubulay (B.T.V.Y.G.)" <[login to unmask email]> Newsgroups: bit.listserv.db2-l To: <[login to unmask email]> Sent: Thursday, December 25, 2003 4:50 AM Subject: Effect of the host variable definition on access path To clarify: Column CARD_NO is defined as char(6), Host variable :HV_CARD_NO1 defined as char(6), Host variable :HV_CARD_NO2 defined as char(7). When you use in the where condition CARD_NO = :HV_CARD_NO1 it uses index But, if you use CARD_NO = :HV_CARD_NO2 it makes tablespace scan. I see this after the bind not on the runtime. Serdar -----Original Message----- From: [login to unmask email] [mailto:[login to unmask email]] Sent: Wednesday, December 24, 2003 7:01 PM To: [login to unmask email] Subject: Re: Effect of the host variable definition on access path DB2 has to make assumptions about the value of the host variable based on many factors during bind time. If you could rebind & set up using the REOPTVARS option, that would solve your whole problem. Missy Case FDR 701-275-6358 "Serdar Sabri Özkubulay To: [login to unmask email] (B.T.V.Y.G.)" cc: <Serdar.Ozkubulay@ bcc: AKBANK.COM> Subject: Effect of the host variable definition on access path Sent by: DB2 Data Base Discussion List <[login to unmask email] RG> 12/24/03 10:46 AM Please respond to DB2 Database Discussion list at IDUG Hi, An SQL makes tablespace scan in order to use a perfect index. Unfortunately the source of the program is lost. When I put same SQL to another COBOL program with proper host variables it uses index. The two DBRMS are identical except consistency token. The question: does the wrong host variable definition effect the access path, if so how does DB2 know this because DBRM does not keep this information? Thanks for any comment. Serdar Sabri Ozkubulay Akbank Bilgi Teknolojileri Veri Yönetimi Grubu Tel:+90-212-270 00 44 Dahili:10323 [login to unmask email] **************************************************************************** ********* Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez. ---------------------------------------------------------------------------- ----- 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email]. 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email]. 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 **************************************************************************** ********* Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez. ---------------------------------------------------------------------------- ----- 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email]. 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email]. 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 ************************************************************************************* Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez. --------------------------------------------------------------------------------- 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email]. 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
-- ===> See you October, 11-14, at IDUG in Prague, Czech Republic <=== +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Smastuguvagen 2 | Country Code (Sweden): 46 | | S-165 72 HASSELBY | | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+ --------------------------------------------------------------------------------- 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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 Trovijo Jr

Re: Effect of the host variable definition on access path
(in response to Peter Backlund)
Hi Serdar,

If you can reproduce SQL sentences with good access paths, you can use
them as OPTHINTS to the bad performing program - I´m assuming you´re at
V7.

HTH,
Walter Trovijo.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Henry Boone

Re: Effect of the host variable definition on access path
(in response to Walter Trovijo Jr)
Host variable information is stored in the DBRM. If the DBRM is v2.3 or
newer, DSNXNDBRM in SDSNMACS has the layout. You should be able to eyeball
the DBRM and find the statement. Just after the statement is the host
variable section (XBRMPVAR in the macro).

If you'd like, e-mail me off-list with the DBRM attached and I'll run it
through a REXX we have that spits-out much of the DBRM information.



/"""""/
!_ _!
(o) (o)
oOOO==(_)==OOOo====================*
* *
* Henry Boone *
* DB2 Systems Programmer *
* SOD/ESMD *
* *
* GEICO Insurance Companies Phone: (301) 718-5334 *
* 4608 Willard Avenue Fax: (301) 986-2408 *
* Chevy Chase, MD 20815 E-Mail: [login to unmask email] *
* *
* .oooO *
*==( )==Oooo.====================*
\ ( ( )
\_) ) /
(_/


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Serdar Sabri Özkubulay (B.T.V.Y.G.)
Sent: Wednesday, December 24, 2003 11:46 AM
To: [login to unmask email]
Subject: Effect of the host variable definition on access path

Hi,

An SQL makes tablespace scan in order to use a perfect index. Unfortunately
the source of the program is lost. When I put same SQL to another COBOL
program with proper host variables it uses index.

The two DBRMS are identical except consistency token. The question: does the
wrong host variable definition effect the access path, if so how does DB2
know this because DBRM does not keep this information?

Thanks for any comment.


Serdar Sabri Ozkubulay

Akbank Bilgi Teknolojileri
Veri Yönetimi Grubu
Tel:+90-212-270 00 44 Dahili:10323
[login to unmask email]
























****************************************************************************
*********
Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup,
yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa
lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir
kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi
sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý,
satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý
amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün
garantisi verilmemekte olup, önceden bildirilmeksizin
deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini
yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

----------------------------------------------------------------------------
-----
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". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] 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
===================
This email/fax message is for the sole use of the intended recipient(s) and
may contain confidential and privileged information. Any unauthorized
review, use, disclosure or distribution of this email/fax is prohibited. If
you are not the intended recipient, please contact the sender by email/fax
and destroy all paper and electronic copies of the original message.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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