DB2 z/OS Index not used by two similar statements

Mark Vickers

DB2 z/OS Index not used by two similar statements
(in response to Mark Vickers)
Greetings,

Two statements in one program were doing a tablespace scan on the OUT table:
Added and index on OUT.PRT_INV, runstats, rebind.

Now statement one cost went down from 8,250 to 32 and running great,
but the second sql is still doing a tablespace scan ??
1.
UPDATE WCSSTOUT OUT
SET OUT.PRT_INV = ' '
WHERE OUT.PRT_INV IN
(SELECT BPD.ORD_NBR
FROM WCSSTBPD BPD
WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
)
This uses the new index on OUT.PRT_INV and runs great now.

2.
UPDATE WCSSTOUT OUT
SET OUT.PRT_INV = ' '
WHERE OUT.PRT_INV IN
(SELECT '00' || BOH.INV_NBR
FROM WCSSTBOH BOH, WCSSTBPD BPD
WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
AND BOH.ORD_NBR = BPD.ORD_NBR
AND BOH.NON_INV_ORD_TYP = '1'
AND BOH.FAC_WHS = '002'
)
The subselect is using 1 matching column on the BOH.ORD_NBR (unique) which is perfect.
BPD is accessed via 1 matching column on BPX_ID which can contain duplicates.
This IGNORES the new index on OUT.PRT_INV and does a TS scan on OUT.

Thanks for any help.
Mark.

From: David Churn [mailto:[login to unmask email]
Sent: Tuesday, October 03, 2017 11:49 AM
To: [login to unmask email]
Subject: [DB2-L] - Data Studio and DB2 11 for z/OS NFM features?


Listers,

I am working with Data Studio 4.1.0 and 4.1.2, and a stored procedure on DB2 11 for z/OS, NFM with APPLCOMPAT V11R1. Data Studio is marking the new features of DB2 11 as errors. It does this with the AUTONOMOUS keyword and trying to declare an ARRAY field. How do I tell Data Studio to use the DB2 11 features?

I have been through the many property options and do not see anything mentioning version. Same for the project properties. Data Studio has been helpfully telling me the the system is "DB2 for z/OS V11 (NEW-Function Mode)". I ran the zparm stored procedure, SYSPROC.ADMIN_INFO_SYSPARM, and it says APPLCOMPAT is "V11R1".

I have created an autonomous stored procedure using SPUFI. When I try to edit it in Data Studio, it tells me, '"EXTERNAL" was expected instead of "AUTONOMOUS".' It will not let me re-install the autonomous stored procedure.

My only conclusion is that this is an issue with Data Studio.

Here is a copy of the CREATE

CREATE PROCEDURE ARZSTPR.SPLOGIT
( IN PROGRAM_NM VARCHAR(128)
, IN OPERATOR_ID VARCHAR(128)
, IN PROGRAM_LOCATION_TX VARCHAR(7)
, IN FILE_NM VARCHAR(30)
, IN IN_SQLSTATE CHAR(5)
, IN IN_SQLCODE INTEGER
, IN MESSAGE_ID VARCHAR(8)
, IN WORKSTATION_ID VARCHAR(20)
, IN ERROR_TX VARCHAR(3000) )
VERSION V1
QUALIFIER ARZ
SQL PATH SYSIBM,SYSFUN,SYSPROC,ARZSTPR
PACKAGE OWNER MFDBCON
LANGUAGE SQL
ISOLATION LEVEL CS
VALIDATE RUN
ALLOW DEBUG MODE
ASUTIME LIMIT 300000
AUTONOMOUS <-- red "x" here
APPLCOMPAT V11R1



David Churn

-----End Original Message-----

Mark Vickers

DB2 z/OS Index not used by two similar statements
I added a concatenation to the first statement as follows:

UPDATE WCSSTOUT OUT
SET OUT.PRT_INV = ' '
WHERE OUT.PRT_INV IN
(SELECT '00' || BPD.ORD_NBR
FROM WCSSTBPD BPD
WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
)

And it changed to a tablespace scan.
My understanding is that the subselect builds the LIST and list processing should use the index.
Why would this not use the index ??

Thanks,
Mark.

From: Vickers, Mark
Sent: Tuesday, October 03, 2017 12:18 PM
To: '[login to unmask email]'
Subject: DB2 z/OS Index not used by two similar statements

Greetings,

Two statements in one program were doing a tablespace scan on the OUT table:
Added and index on OUT.PRT_INV, runstats, rebind.

Now statement one cost went down from 8,250 to 32 and running great,
but the second sql is still doing a tablespace scan ??
1.
UPDATE WCSSTOUT OUT
SET OUT.PRT_INV = ' '
WHERE OUT.PRT_INV IN
(SELECT BPD.ORD_NBR
FROM WCSSTBPD BPD
WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
)
This uses the new index on OUT.PRT_INV and runs great now.

2.
UPDATE WCSSTOUT OUT
SET OUT.PRT_INV = ' '
WHERE OUT.PRT_INV IN
(SELECT '00' || BOH.INV_NBR
FROM WCSSTBOH BOH, WCSSTBPD BPD
WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
AND BOH.ORD_NBR = BPD.ORD_NBR
AND BOH.NON_INV_ORD_TYP = '1'
AND BOH.FAC_WHS = '002'
)
The subselect is using 1 matching column on the BOH.ORD_NBR (unique) which is perfect.
BPD is accessed via 1 matching column on BPX_ID which can contain duplicates.
This IGNORES the new index on OUT.PRT_INV and does a TS scan on OUT.

Thanks for any help.
Mark.

From: David Churn [mailto:[login to unmask email]
Sent: Tuesday, October 03, 2017 11:49 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Data Studio and DB2 11 for z/OS NFM features?


Listers,

I am working with Data Studio 4.1.0 and 4.1.2, and a stored procedure on DB2 11 for z/OS, NFM with APPLCOMPAT V11R1. Data Studio is marking the new features of DB2 11 as errors. It does this with the AUTONOMOUS keyword and trying to declare an ARRAY field. How do I tell Data Studio to use the DB2 11 features?

I have been through the many property options and do not see anything mentioning version. Same for the project properties. Data Studio has been helpfully telling me the the system is "DB2 for z/OS V11 (NEW-Function Mode)". I ran the zparm stored procedure, SYSPROC.ADMIN_INFO_SYSPARM, and it says APPLCOMPAT is "V11R1".

I have created an autonomous stored procedure using SPUFI. When I try to edit it in Data Studio, it tells me, '"EXTERNAL" was expected instead of "AUTONOMOUS".' It will not let me re-install the autonomous stored procedure.

My only conclusion is that this is an issue with Data Studio.

Here is a copy of the CREATE

CREATE PROCEDURE ARZSTPR.SPLOGIT
( IN PROGRAM_NM VARCHAR(128)
, IN OPERATOR_ID VARCHAR(128)
, IN PROGRAM_LOCATION_TX VARCHAR(7)
, IN FILE_NM VARCHAR(30)
, IN IN_SQLSTATE CHAR(5)
, IN IN_SQLCODE INTEGER
, IN MESSAGE_ID VARCHAR(8)
, IN WORKSTATION_ID VARCHAR(20)
, IN ERROR_TX VARCHAR(3000) )
VERSION V1
QUALIFIER ARZ
SQL PATH SYSIBM,SYSFUN,SYSPROC,ARZSTPR
PACKAGE OWNER MFDBCON
LANGUAGE SQL
ISOLATION LEVEL CS
VALIDATE RUN
ALLOW DEBUG MODE
ASUTIME LIMIT 300000
AUTONOMOUS <-- red "x" here
APPLCOMPAT V11R1



David Churn

-----End Original Message-----

Sam Baugh

DB2 z/OS Index not used by two similar statements
(in response to Mark Vickers)
Use CAST to make sure the column characteristics are the same for the
concatenation, CAST('00' || BOH.INV_NBR as CHAR(?)) to whatever
OUT.PRT_INV is.


On Tue, Oct 3, 2017 at 12:26 PM, Vickers, Mark <[login to unmask email]> wrote:

> I added a concatenation to the first statement as follows:
>
>
>
> UPDATE WCSSTOUT OUT
>
> SET OUT.PRT_INV = ' '
>
> WHERE OUT.PRT_INV IN
>
> (SELECT '00' || BPD.ORD_NBR
>
> FROM WCSSTBPD BPD
>
> WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
>
> )
>
>
>
> And it changed to a tablespace scan.
>
> My understanding is that the subselect builds the LIST and list processing
> should use the index.
>
> Why would this not use the index ??
>
>
>
> Thanks,
>
> Mark.
>
>
>
> *From:* Vickers, Mark
> *Sent:* Tuesday, October 03, 2017 12:18 PM
> *To:* '[login to unmask email]'
> *Subject:* DB2 z/OS Index not used by two similar statements
>
>
>
> Greetings,
>
>
>
> Two statements in one program were doing a tablespace scan on the OUT
> table:
>
> Added and index on OUT.PRT_INV, runstats, rebind.
>
>
>
> Now statement one cost went down from 8,250 to 32 and running great,
>
> but the second sql is still doing a tablespace scan ??
>
> 1.
>
> UPDATE WCSSTOUT OUT
>
> SET OUT.PRT_INV = ' '
>
> WHERE OUT.PRT_INV IN
>
> (SELECT BPD.ORD_NBR
>
> FROM WCSSTBPD BPD
>
> WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
>
> )
>
> This uses the new index on OUT.PRT_INV and runs great now.
>
>
>
> 2.
>
> UPDATE WCSSTOUT OUT
>
> SET OUT.PRT_INV = ' '
>
> WHERE OUT.PRT_INV IN
>
> (SELECT '00' || BOH.INV_NBR
>
> FROM WCSSTBOH BOH, WCSSTBPD BPD
>
> WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
>
> AND BOH.ORD_NBR = BPD.ORD_NBR
>
> AND BOH.NON_INV_ORD_TYP = '1'
>
> AND BOH.FAC_WHS = '002'
>
> )
>
> The subselect is using 1 matching column on the BOH.ORD_NBR (unique) which
> is perfect.
>
> BPD is accessed via 1 matching column on BPX_ID which can contain
> duplicates.
>
> This IGNORES the new index on OUT.PRT_INV and does a TS scan on OUT.
>
>
>
> Thanks for any help.
>
> Mark.
>
>
>
> *From:* David Churn [mailto:[login to unmask email] <[login to unmask email]>]
> *Sent:* Tuesday, October 03, 2017 11:49 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - Data Studio and DB2 11 for z/OS NFM features?
>
>
>
> Listers,
>
> I am working with Data Studio 4.1.0 and 4.1.2, and a stored procedure on
> DB2 11 for z/OS, NFM with APPLCOMPAT V11R1. Data Studio is marking the new
> features of DB2 11 as errors. It does this with the AUTONOMOUS keyword and
> trying to declare an ARRAY field. How do I tell Data Studio to use the DB2
> 11 features?
>
> I have been through the many property options and do not see anything
> mentioning version. Same for the project properties. Data Studio has been
> helpfully telling me the the system is "DB2 for z/OS V11 (NEW-Function
> Mode)". I ran the zparm stored procedure, SYSPROC.ADMIN_INFO_SYSPARM, and
> it says APPLCOMPAT is "V11R1".
>
> I have created an autonomous stored procedure using SPUFI. When I try to
> edit it in Data Studio, it tells me, '"EXTERNAL" was expected instead of
> "AUTONOMOUS".' It will not let me re-install the autonomous stored
> procedure.
>
> My only conclusion is that this is an issue with Data Studio.
>
> Here is a copy of the CREATE
>
> CREATE PROCEDURE ARZSTPR.SPLOGIT
> ( IN PROGRAM_NM VARCHAR(128)
> , IN OPERATOR_ID VARCHAR(128)
> , IN PROGRAM_LOCATION_TX VARCHAR(7)
> , IN FILE_NM VARCHAR(30)
> , IN IN_SQLSTATE CHAR(5)
> , IN IN_SQLCODE INTEGER
> , IN MESSAGE_ID VARCHAR(8)
> , IN WORKSTATION_ID VARCHAR(20)
> , IN ERROR_TX VARCHAR(3000) )
> VERSION V1
> QUALIFIER ARZ
> SQL PATH SYSIBM,SYSFUN,SYSPROC,ARZSTPR
> PACKAGE OWNER MFDBCON
> LANGUAGE SQL
> ISOLATION LEVEL CS
> VALIDATE RUN
> ALLOW DEBUG MODE
> ASUTIME LIMIT 300000
> AUTONOMOUS <--
> red "x" here
> APPLCOMPAT V11R1
>
>
>
> David Churn
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>

Mark Vickers

DB2 z/OS Index not used by two similar statements
(in response to Sam Baugh)
Sam,
I explained with the CAST, it dropped the cost by 25%, but did not pick up the index.

Thanks,
Mark.

From: Sam Baugh [mailto:[login to unmask email]
Sent: Tuesday, October 03, 2017 12:44 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 z/OS Index not used by two similar statements

Use CAST to make sure the column characteristics are the same for the concatenation, CAST('00' || BOH.INV_NBR as CHAR(?)) to whatever
OUT.PRT_INV is.

On Tue, Oct 3, 2017 at 12:26 PM, Vickers, Mark <[login to unmask email]<mailto:[login to unmask email]>> wrote:
I added a concatenation to the first statement as follows:

UPDATE WCSSTOUT OUT
SET OUT.PRT_INV = ' '
WHERE OUT.PRT_INV IN
(SELECT '00' || BPD.ORD_NBR
FROM WCSSTBPD BPD
WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
)

And it changed to a tablespace scan.
My understanding is that the subselect builds the LIST and list processing should use the index.
Why would this not use the index ??

Thanks,
Mark.

From: Vickers, Mark
Sent: Tuesday, October 03, 2017 12:18 PM
To: '[login to unmask email]<mailto:[login to unmask email]>'
Subject: DB2 z/OS Index not used by two similar statements

Greetings,

Two statements in one program were doing a tablespace scan on the OUT table:
Added and index on OUT.PRT_INV, runstats, rebind.

Now statement one cost went down from 8,250 to 32 and running great,
but the second sql is still doing a tablespace scan ??
1.
UPDATE WCSSTOUT OUT
SET OUT.PRT_INV = ' '
WHERE OUT.PRT_INV IN
(SELECT BPD.ORD_NBR
FROM WCSSTBPD BPD
WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
)
This uses the new index on OUT.PRT_INV and runs great now.

2.
UPDATE WCSSTOUT OUT
SET OUT.PRT_INV = ' '
WHERE OUT.PRT_INV IN
(SELECT '00' || BOH.INV_NBR
FROM WCSSTBOH BOH, WCSSTBPD BPD
WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
AND BOH.ORD_NBR = BPD.ORD_NBR
AND BOH.NON_INV_ORD_TYP = '1'
AND BOH.FAC_WHS = '002'
)
The subselect is using 1 matching column on the BOH.ORD_NBR (unique) which is perfect.
BPD is accessed via 1 matching column on BPX_ID which can contain duplicates.
This IGNORES the new index on OUT.PRT_INV and does a TS scan on OUT.

Thanks for any help.
Mark.

From: David Churn [mailto:[login to unmask email]
Sent: Tuesday, October 03, 2017 11:49 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Data Studio and DB2 11 for z/OS NFM features?


Listers,

I am working with Data Studio 4.1.0 and 4.1.2, and a stored procedure on DB2 11 for z/OS, NFM with APPLCOMPAT V11R1. Data Studio is marking the new features of DB2 11 as errors. It does this with the AUTONOMOUS keyword and trying to declare an ARRAY field. How do I tell Data Studio to use the DB2 11 features?

I have been through the many property options and do not see anything mentioning version. Same for the project properties. Data Studio has been helpfully telling me the the system is "DB2 for z/OS V11 (NEW-Function Mode)". I ran the zparm stored procedure, SYSPROC.ADMIN_INFO_SYSPARM, and it says APPLCOMPAT is "V11R1".

I have created an autonomous stored procedure using SPUFI. When I try to edit it in Data Studio, it tells me, '"EXTERNAL" was expected instead of "AUTONOMOUS".' It will not let me re-install the autonomous stored procedure.

My only conclusion is that this is an issue with Data Studio.

Here is a copy of the CREATE

CREATE PROCEDURE ARZSTPR.SPLOGIT
( IN PROGRAM_NM VARCHAR(128)
, IN OPERATOR_ID VARCHAR(128)
, IN PROGRAM_LOCATION_TX VARCHAR(7)
, IN FILE_NM VARCHAR(30)
, IN IN_SQLSTATE CHAR(5)
, IN IN_SQLCODE INTEGER
, IN MESSAGE_ID VARCHAR(8)
, IN WORKSTATION_ID VARCHAR(20)
, IN ERROR_TX VARCHAR(3000) )
VERSION V1
QUALIFIER ARZ
SQL PATH SYSIBM,SYSFUN,SYSPROC,ARZSTPR
PACKAGE OWNER MFDBCON
LANGUAGE SQL
ISOLATION LEVEL CS
VALIDATE RUN
ALLOW DEBUG MODE
ASUTIME LIMIT 300000
AUTONOMOUS <-- red "x" here
APPLCOMPAT V11R1



David Churn

-----End Original Message-----

-----End Original Message-----

Mark Vickers

DB2 z/OS Index not used by two similar statements
(in response to Mark Vickers)
Well I double checked myself and explained in production and it says it will use the index !!

Thanks to Sam.

From: Vickers, Mark [mailto:[login to unmask email]
Sent: Tuesday, October 03, 2017 12:52 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 z/OS Index not used by two similar statements

Sam,
I explained with the CAST, it dropped the cost by 25%, but did not pick up the index.

Thanks,
Mark.

From: Sam Baugh [mailto:[login to unmask email]
Sent: Tuesday, October 03, 2017 12:44 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: DB2 z/OS Index not used by two similar statements

Use CAST to make sure the column characteristics are the same for the concatenation, CAST('00' || BOH.INV_NBR as CHAR(?)) to whatever
OUT.PRT_INV is.

On Tue, Oct 3, 2017 at 12:26 PM, Vickers, Mark <[login to unmask email]<mailto:[login to unmask email]>> wrote:
I added a concatenation to the first statement as follows:

UPDATE WCSSTOUT OUT
SET OUT.PRT_INV = ' '
WHERE OUT.PRT_INV IN
(SELECT '00' || BPD.ORD_NBR
FROM WCSSTBPD BPD
WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
)

And it changed to a tablespace scan.
My understanding is that the subselect builds the LIST and list processing should use the index.
Why would this not use the index ??

Thanks,
Mark.

From: Vickers, Mark
Sent: Tuesday, October 03, 2017 12:18 PM
To: '[login to unmask email]<mailto:[login to unmask email]>'
Subject: DB2 z/OS Index not used by two similar statements

Greetings,

Two statements in one program were doing a tablespace scan on the OUT table:
Added and index on OUT.PRT_INV, runstats, rebind.

Now statement one cost went down from 8,250 to 32 and running great,
but the second sql is still doing a tablespace scan ??
1.
UPDATE WCSSTOUT OUT
SET OUT.PRT_INV = ' '
WHERE OUT.PRT_INV IN
(SELECT BPD.ORD_NBR
FROM WCSSTBPD BPD
WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
)
This uses the new index on OUT.PRT_INV and runs great now.

2.
UPDATE WCSSTOUT OUT
SET OUT.PRT_INV = ' '
WHERE OUT.PRT_INV IN
(SELECT '00' || BOH.INV_NBR
FROM WCSSTBOH BOH, WCSSTBPD BPD
WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
AND BOH.ORD_NBR = BPD.ORD_NBR
AND BOH.NON_INV_ORD_TYP = '1'
AND BOH.FAC_WHS = '002'
)
The subselect is using 1 matching column on the BOH.ORD_NBR (unique) which is perfect.
BPD is accessed via 1 matching column on BPX_ID which can contain duplicates.
This IGNORES the new index on OUT.PRT_INV and does a TS scan on OUT.

Thanks for any help.
Mark.

From: David Churn [mailto:[login to unmask email]
Sent: Tuesday, October 03, 2017 11:49 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Data Studio and DB2 11 for z/OS NFM features?


Listers,

I am working with Data Studio 4.1.0 and 4.1.2, and a stored procedure on DB2 11 for z/OS, NFM with APPLCOMPAT V11R1. Data Studio is marking the new features of DB2 11 as errors. It does this with the AUTONOMOUS keyword and trying to declare an ARRAY field. How do I tell Data Studio to use the DB2 11 features?

I have been through the many property options and do not see anything mentioning version. Same for the project properties. Data Studio has been helpfully telling me the the system is "DB2 for z/OS V11 (NEW-Function Mode)". I ran the zparm stored procedure, SYSPROC.ADMIN_INFO_SYSPARM, and it says APPLCOMPAT is "V11R1".

I have created an autonomous stored procedure using SPUFI. When I try to edit it in Data Studio, it tells me, '"EXTERNAL" was expected instead of "AUTONOMOUS".' It will not let me re-install the autonomous stored procedure.

My only conclusion is that this is an issue with Data Studio.

Here is a copy of the CREATE

CREATE PROCEDURE ARZSTPR.SPLOGIT
( IN PROGRAM_NM VARCHAR(128)
, IN OPERATOR_ID VARCHAR(128)
, IN PROGRAM_LOCATION_TX VARCHAR(7)
, IN FILE_NM VARCHAR(30)
, IN IN_SQLSTATE CHAR(5)
, IN IN_SQLCODE INTEGER
, IN MESSAGE_ID VARCHAR(8)
, IN WORKSTATION_ID VARCHAR(20)
, IN ERROR_TX VARCHAR(3000) )
VERSION V1
QUALIFIER ARZ
SQL PATH SYSIBM,SYSFUN,SYSPROC,ARZSTPR
PACKAGE OWNER MFDBCON
LANGUAGE SQL
ISOLATION LEVEL CS
VALIDATE RUN
ALLOW DEBUG MODE
ASUTIME LIMIT 300000
AUTONOMOUS <-- red "x" here
APPLCOMPAT V11R1



David Churn

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

Sam Baugh

DB2 z/OS Index not used by two similar statements
(in response to Mark Vickers)
I don't have an answer about the access path. This was a behavior we
observed when we migrated from Db2 10 to 11. Some common adhoc update
queries with similar structure, UPDATE ... WHERE key1 IN (SELECT key2 FROM
whatever), where key2 for IN list was CHAR(10) and the key1 was CHAR(12).
(last 2 characters of the CHAR(12) column were supposed to always be
spaces, vendor product, don't ask why). Anyway, adding CAST(key2 as
CHAR(12)) fixed the problem for us. Updates went from several minutes to
sub-second. I don't recall if I ever fully investigated why or ran
explains, it just worked for us.

On Tue, Oct 3, 2017 at 12:52 PM, Vickers, Mark <[login to unmask email]> wrote:

> Sam,
>
> I explained with the CAST, it dropped the cost by 25%, but did not pick up
> the index.
>
>
>
> Thanks,
>
> Mark.
>
>
>
> *From:* Sam Baugh [mailto:[login to unmask email]
> *Sent:* Tuesday, October 03, 2017 12:44 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: DB2 z/OS Index not used by two similar statements
>
>
>
> Use CAST to make sure the column characteristics are the same for the
> concatenation, CAST('00' || BOH.INV_NBR as CHAR(?)) to whatever
> OUT.PRT_INV is.
>
>
>
> On Tue, Oct 3, 2017 at 12:26 PM, Vickers, Mark <[login to unmask email]>
> wrote:
>
> I added a concatenation to the first statement as follows:
>
>
>
> UPDATE WCSSTOUT OUT
>
> SET OUT.PRT_INV = ' '
>
> WHERE OUT.PRT_INV IN
>
> (SELECT '00' || BPD.ORD_NBR
>
> FROM WCSSTBPD BPD
>
> WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
>
> )
>
>
>
> And it changed to a tablespace scan.
>
> My understanding is that the subselect builds the LIST and list processing
> should use the index.
>
> Why would this not use the index ??
>
>
>
> Thanks,
>
> Mark.
>
>
>
> *From:* Vickers, Mark
> *Sent:* Tuesday, October 03, 2017 12:18 PM
> *To:* '[login to unmask email]'
> *Subject:* DB2 z/OS Index not used by two similar statements
>
>
>
> Greetings,
>
>
>
> Two statements in one program were doing a tablespace scan on the OUT
> table:
>
> Added and index on OUT.PRT_INV, runstats, rebind.
>
>
>
> Now statement one cost went down from 8,250 to 32 and running great,
>
> but the second sql is still doing a tablespace scan ??
>
> 1.
>
> UPDATE WCSSTOUT OUT
>
> SET OUT.PRT_INV = ' '
>
> WHERE OUT.PRT_INV IN
>
> (SELECT BPD.ORD_NBR
>
> FROM WCSSTBPD BPD
>
> WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
>
> )
>
> This uses the new index on OUT.PRT_INV and runs great now.
>
>
>
> 2.
>
> UPDATE WCSSTOUT OUT
>
> SET OUT.PRT_INV = ' '
>
> WHERE OUT.PRT_INV IN
>
> (SELECT '00' || BOH.INV_NBR
>
> FROM WCSSTBOH BOH, WCSSTBPD BPD
>
> WHERE BPD.BPX_ID = :DCLWCSSTBPD.BPD-BPX-ID
>
> AND BOH.ORD_NBR = BPD.ORD_NBR
>
> AND BOH.NON_INV_ORD_TYP = '1'
>
> AND BOH.FAC_WHS = '002'
>
> )
>
> The subselect is using 1 matching column on the BOH.ORD_NBR (unique) which
> is perfect.
>
> BPD is accessed via 1 matching column on BPX_ID which can contain
> duplicates.
>
> This IGNORES the new index on OUT.PRT_INV and does a TS scan on OUT.
>
>
>
> Thanks for any help.
>
> Mark.
>
>
>
> *From:* David Churn [mailto:[login to unmask email] <[login to unmask email]>]
> *Sent:* Tuesday, October 03, 2017 11:49 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - Data Studio and DB2 11 for z/OS NFM features?
>
>
>
> Listers,
>
> I am working with Data Studio 4.1.0 and 4.1.2, and a stored procedure on
> DB2 11 for z/OS, NFM with APPLCOMPAT V11R1. Data Studio is marking the new
> features of DB2 11 as errors. It does this with the AUTONOMOUS keyword and
> trying to declare an ARRAY field. How do I tell Data Studio to use the DB2
> 11 features?
>
> I have been through the many property options and do not see anything
> mentioning version. Same for the project properties. Data Studio has been
> helpfully telling me the the system is "DB2 for z/OS V11 (NEW-Function
> Mode)". I ran the zparm stored procedure, SYSPROC.ADMIN_INFO_SYSPARM, and
> it says APPLCOMPAT is "V11R1".
>
> I have created an autonomous stored procedure using SPUFI. When I try to
> edit it in Data Studio, it tells me, '"EXTERNAL" was expected instead of
> "AUTONOMOUS".' It will not let me re-install the autonomous stored
> procedure.
>
> My only conclusion is that this is an issue with Data Studio.
>
> Here is a copy of the CREATE
>
> CREATE PROCEDURE ARZSTPR.SPLOGIT
> ( IN PROGRAM_NM VARCHAR(128)
> , IN OPERATOR_ID VARCHAR(128)
> , IN PROGRAM_LOCATION_TX VARCHAR(7)
> , IN FILE_NM VARCHAR(30)
> , IN IN_SQLSTATE CHAR(5)
> , IN IN_SQLCODE INTEGER
> , IN MESSAGE_ID VARCHAR(8)
> , IN WORKSTATION_ID VARCHAR(20)
> , IN ERROR_TX VARCHAR(3000) )
> VERSION V1
> QUALIFIER ARZ
> SQL PATH SYSIBM,SYSFUN,SYSPROC,ARZSTPR
> PACKAGE OWNER MFDBCON
> LANGUAGE SQL
> ISOLATION LEVEL CS
> VALIDATE RUN
> ALLOW DEBUG MODE
> ASUTIME LIMIT 300000
> AUTONOMOUS <--
> red "x" here
> APPLCOMPAT V11R1
>
>
>
> David Churn
>
>
>
> -----End Original Message-----
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>
>