Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.

Tomas Johannsson

Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.

Hi,

While preparing for migration from V11 to V12 we came across this APAR PI87933. In the problem conclusion there is an SQL query that you can run to list any orphaned entries in the SYSLGRNX table.
This query is quite time and CPU consuming for large systems as we experienced at my work.
I rewrote that query (see the APAR ) to this one

WITH MYIDS as(
SELECT HEX(L.LGRPSID) MYID FROM SYSIBM.SYSLGRNX AS L
except
select * from (
SELECT HEX(T.PSID) MYID FROM SYSIBM.SYSTABLESPACE AS T
UNION ALL
SELECT HEX(I.ISOBID) MYID FROM SYSIBM.SYSINDEXES AS I
)x
)
SELECT DISTINCT HEX(LL.LGRPSID) FROM SYSIBM.SYSLGRNX AS LL
WHERE HEX(LL.LGRDBID) IN ('0006')
AND HEX(LL.LGRPSID) IN (select * from MYIDS) with ur;


And it ran for 10 to 30 second depending on what member we ran it on.
Just wanted to share it. :)

Regards,

     Tomas Helgi

Michael Hannan

RE: Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.
(in response to Tomas Johannsson)

Thomas,

Thanks might have been instructive to include the original query as well, which probably had mismatching data types for a join, which results in almost a Cartesian product like performance.

Your HEX function allows SMALLINT and CHAR(2) FOR BIT DATA columns to be converted to a common data type which can be joined more effectively.

There is an APAR raised for the mismatching data type join which can run for days if tables joined are large, due to Nested Loop with zero match cols.

 In a previous thread we discussed using ASCII function to convert the CHAR(2) to integers, possibly for exactly these tables, I forget now.

ASCII_CHR function can convert in opposite direction from Integer to CHAR which then can be cast to BIT DATA.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Roy Boxwell

Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.
(in response to Michael Hannan)
SELECT DISTINCT HEX(L.LGRPSID) FROM SYSIBM.SYSLGRNX AS L

WHERE HEX(L.LGRDBID) IN ('0006')

AND HEX(L.LGRPSID) NOT IN

(SELECT HEX(T.PSID) FROM SYSIBM.SYSTABLESPACE AS T

WHERE HEX(T.DBID) = HEX(L.LGRDBID))

AND HEX(L.LGRPSID) NOT IN

(SELECT HEX(I.ISOBID) FROM SYSIBM.SYSINDEXES AS I

WHERE HEX(I.DBID) = HEX(L.LGRDBID));





Just for the record...



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Michael Hannan [mailto:[login to unmask email]
Sent: Friday, September 28, 2018 8:47 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.



Thomas,

Thanks might have been instructive to include the original query as well, which probably had mismatching data types for a join, which results in almost a Cartesian product like performance.

Your HEX function allows SMALLINT and CHAR(2) FOR BIT DATA columns to be converted to a common data type which can be joined more effectively.

There is an APAR raised for the mismatching data type join which can run for days if tables joined are large, due to Nested Loop with zero match cols.

In a previous thread we discussed using ASCII function to convert the CHAR(2) to integers, possibly for exactly these tables, I forget now.

ASCII_CHR function can convert in opposite direction from Integer to CHAR which then can be cast to BIT DATA.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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

Attachments

  • smime.p7s (5.1k)

Michael Hannan

RE: Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.
(in response to Roy Boxwell)

Wow that is really ugly to my eye! I never write code like this. I don't use NOT INs much and certainly not with predicates involving functions. I prefer NOT EXISTS (with suitable indexes - not in this case) and outer joins.  Tomas found the use for EXCEPT. Well done! EXCEPT can be too expensive for very frequently executed SQLs that don't do much in OLTP, but excellent for longer running queries, when well used. I think it is a bit like Outer join with both tables pre sorted.

Interesting a PTF mentions such ugly SQL (in my personal view).  

I see my assumption was wrong here. Nested Loop Joins with no match are not the only bad techniques. LOL

This SQL has correlated NOT IN subqueries. These are not transformable to Join.

Predicates with Correlated Subquery are  Stage 2 as well, unless transformable. I can only imagine a horrible access path with Every predicate Stage 2 and all TS Scans will result.

I am not sure that this would qualify to use the Lookaside Cache for correlated subqueries, as well.

Possiblly does TS Scan of Tablespace and Indexes for every row of the outer with desired DBID, unless the correlated cache will allow those scans to be only once. Based on Tomas experiencing bad performance originally, I am guessing the Cache does not work in this case. 

The correlation value does not change. Set to 0006 in the outer query, but can DB2 utilise the fact? Not sure with Stage 2 predicates involved. Maybe even Terry would be scratching his head with this one. LOL Maybe not. Anyway I am very curious to see if the explain looks O.K. or very ugly.

Years ago the DB2 manual stated when correlated subqueries could use the cache of previous probed values. Forget what version and the precise restrictions (but I am sure there restriction cases). Explain does not tell you, I think if correlated cache is used or not. Then it all changed and became so complex, the exact rules are no longer stated in the manuals. However I would not like to push my  luck. LOL DB2 V9 included un correlation of subqueries and correlation of non-correlated. Rules not known (known to Terry Purcell I guess but complex). Always worth re reading Terry's presentations on new Optimizer things for both V11 and V12 too.

Later I might explain this and see how much better I could do with a rewrite of the SQL, as an exercise.

The performance might vary a lot depending on if the number of PSIDs in a DBID.

Naturally I think an equals matched Join would be a lot better, even if had to be an Outer Join to replace NOT IN processing.

First transform is to use simple Stage 1 predicates on LGRDBID = x'0006'  and t.dbid = 6 etc. This uncorrelates the subqueries as well.

 

Tomas used the EXCEPT SQL to great effect here which is faintly like an Outer Join, having already materialised the matched columns to same data types (after HEX) to overcome Stage 2 processing, I presume. Again I need to see explain to check that. I am sure it means the values are sorted into the right sequence for the match instead of scanning some work file hunting for them.

Next transform is simply to replace the Stage 2 NOT INs with EXCEPT for each. That gives access path where all 3 parts are sorted before the EXCEPTs occurs, and Stage 1 predicates.
 
In Reply to Roy Boxwell:

SELECT DISTINCT HEX(L.LGRPSID) FROM SYSIBM.SYSLGRNX AS L

WHERE HEX(L.LGRDBID) IN ('0006')

AND HEX(L.LGRPSID) NOT IN

(SELECT HEX(T.PSID) FROM SYSIBM.SYSTABLESPACE AS T

WHERE HEX(T.DBID) = HEX(L.LGRDBID))

AND HEX(L.LGRPSID) NOT IN

(SELECT HEX(I.ISOBID) FROM SYSIBM.SYSINDEXES AS I

WHERE HEX(I.DBID) = HEX(L.LGRDBID));





Just for the record...



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Michael Hannan [mailto:[login to unmask email]
Sent: Friday, September 28, 2018 8:47 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.



Thomas,

Thanks might have been instructive to include the original query as well, which probably had mismatching data types for a join, which results in almost a Cartesian product like performance.

Your HEX function allows SMALLINT and CHAR(2) FOR BIT DATA columns to be converted to a common data type which can be joined more effectively.

There is an APAR raised for the mismatching data type join which can run for days if tables joined are large, due to Nested Loop with zero match cols.

In a previous thread we discussed using ASCII function to convert the CHAR(2) to integers, possibly for exactly these tables, I forget now.

ASCII_CHR function can convert in opposite direction from Integer to CHAR which then can be cast to BIT DATA.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Sep 28, 2018 - 01:13 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Sep 28, 2018 - 01:15 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Sep 28, 2018 - 01:22 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Sep 28, 2018 - 01:48 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Sep 28, 2018 - 01:51 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Sep 28, 2018 - 03:17 PM (Europe/Berlin)

Roy Boxwell

Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.
(in response to Michael Hannan)
Yep – I was equally “impressed” when I saw it... basically not a single good thing about it....



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Michael Hannan [mailto:[login to unmask email]
Sent: Friday, September 28, 2018 1:00 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.



Wow that is really ugly to my eye! I never write code like this. I don't use NOT INs much and certainly not with predicates involving functions. I refer NOT EXISTS (with suitable indexes) and outer joins.

I see my assumption was wrong here, if this is the published SQL referred to.

This SQL has correlated NOT IN subqueries. These are not transformable to Join.

The HEX function makes the match Stage 2 as well, I think. I can only imagine a horrible access path.

I am not sure that this would qualify to use the Lookaside Cache for correlated subqueries, as well.

The correlation value does not change. Set to 0006 in the outer query, but can DB2 utilise the fact? Not sure with Stage 2 predicates involved. Maybe even Terry would be scratching his head with this one. LOL Maybe not. Anyway I am very curious to see if the explain looks O.K. or very ugly.

Years ago the DB2 manual stated when correlated subqueries could use the cache of previous probed values. Then it changed and became so complex, the exact rules are no longer stated in the manuals. However I would not like to push my luck. LOL

Later I might explain this and see how much better I could do with a rewrite of the SQL, as an exercise.

The performance might vary a lot depending on if the number of PSIDs in a DBID.

Naturally I think an equals matched Join would be a lot better, even if had to be a Full Outer Join to replace NOT IN processing.

In Reply to Roy Boxwell:

SELECT DISTINCT HEX(L.LGRPSID) FROM SYSIBM.SYSLGRNX AS L

WHERE HEX(L.LGRDBID) IN ('0006')

AND HEX(L.LGRPSID) NOT IN

(SELECT HEX(T.PSID) FROM SYSIBM.SYSTABLESPACE AS T

WHERE HEX(T.DBID) = HEX(L.LGRDBID))

AND HEX(L.LGRPSID) NOT IN

(SELECT HEX(I.ISOBID) FROM SYSIBM.SYSINDEXES AS I

WHERE HEX(I.DBID) = HEX(L.LGRDBID));





Just for the record...



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email] <mailto:[login to unmask email]%3e> > [login to unmask email] <mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Michael Hannan [mailto:[login to unmask email]
Sent: Friday, September 28, 2018 8:47 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.



Thomas,

Thanks might have been instructive to include the original query as well, which probably had mismatching data types for a join, which results in almost a Cartesian product like performance.

Your HEX function allows SMALLINT and CHAR(2) FOR BIT DATA columns to be converted to a common data type which can be joined more effectively.

There is an APAR raised for the mismatching data type join which can run for days if tables joined are large, due to Nested Loop with zero match cols.

In a previous thread we discussed using ASCII function to convert the CHAR(2) to integers, possibly for exactly these tables, I forget now.

ASCII_CHR function can convert in opposite direction from Integer to CHAR which then can be cast to BIT DATA.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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

Attachments

  • smime.p7s (5.1k)

Roy Boxwell

Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.
(in response to Roy Boxwell)
I just wonder who actually dreamt up that way of writing a query...



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Friday, September 28, 2018 1:58 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.



Yep – I was equally “impressed” when I saw it... basically not a single good thing about it....



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Michael Hannan [mailto:[login to unmask email]
Sent: Friday, September 28, 2018 1:00 PM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.



Wow that is really ugly to my eye! I never write code like this. I don't use NOT INs much and certainly not with predicates involving functions. I refer NOT EXISTS (with suitable indexes) and outer joins.

I see my assumption was wrong here, if this is the published SQL referred to.

This SQL has correlated NOT IN subqueries. These are not transformable to Join.

The HEX function makes the match Stage 2 as well, I think. I can only imagine a horrible access path.

I am not sure that this would qualify to use the Lookaside Cache for correlated subqueries, as well.

The correlation value does not change. Set to 0006 in the outer query, but can DB2 utilise the fact? Not sure with Stage 2 predicates involved. Maybe even Terry would be scratching his head with this one. LOL Maybe not. Anyway I am very curious to see if the explain looks O.K. or very ugly.

Years ago the DB2 manual stated when correlated subqueries could use the cache of previous probed values. Then it changed and became so complex, the exact rules are no longer stated in the manuals. However I would not like to push my luck. LOL

Later I might explain this and see how much better I could do with a rewrite of the SQL, as an exercise.

The performance might vary a lot depending on if the number of PSIDs in a DBID.

Naturally I think an equals matched Join would be a lot better, even if had to be a Full Outer Join to replace NOT IN processing.

In Reply to Roy Boxwell:

SELECT DISTINCT HEX(L.LGRPSID) FROM SYSIBM.SYSLGRNX AS L

WHERE HEX(L.LGRDBID) IN ('0006')

AND HEX(L.LGRPSID) NOT IN

(SELECT HEX(T.PSID) FROM SYSIBM.SYSTABLESPACE AS T

WHERE HEX(T.DBID) = HEX(L.LGRDBID))

AND HEX(L.LGRPSID) NOT IN

(SELECT HEX(I.ISOBID) FROM SYSIBM.SYSINDEXES AS I

WHERE HEX(I.DBID) = HEX(L.LGRDBID));





Just for the record...



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email] <mailto:[login to unmask email]%3e> > [login to unmask email] <mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Michael Hannan [mailto:[login to unmask email]
Sent: Friday, September 28, 2018 8:47 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.



Thomas,

Thanks might have been instructive to include the original query as well, which probably had mismatching data types for a join, which results in almost a Cartesian product like performance.

Your HEX function allows SMALLINT and CHAR(2) FOR BIT DATA columns to be converted to a common data type which can be joined more effectively.

There is an APAR raised for the mismatching data type join which can run for days if tables joined are large, due to Nested Loop with zero match cols.

In a previous thread we discussed using ASCII function to convert the CHAR(2) to integers, possibly for exactly these tables, I forget now.

ASCII_CHR function can convert in opposite direction from Integer to CHAR which then can be cast to BIT DATA.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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

Attachments

  • smime.p7s (5.1k)

Michael Hannan

RE: Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.
(in response to Tomas Johannsson)

Tomas,

I think your query rewritten is not quite right. Specification of the DBID in SYSTABLESPACE  and SYSINDEXES seems to be gone. Are PSIDs unique anyway?

How about this

SELECT DISTINCT HEX(L.LGRPSID) MYID FROM SYSIBM.SYSLGRNX AS L
  WHERE L.LGRDBID = x'0006'
EXCEPT 
SELECT HEX(T.PSID) MYID FROM SYSIBM.SYSTABLESPACE AS T
  WHERE T.DBID = 6
EXCEPT
SELECT HEX(I.ISOBID) MYID FROM SYSIBM.SYSINDEXES AS I
  WHERE I.DBID = 6
with ur;

I explained it but did not run it. No Auth on Syslgrnx 

Thanks to Tomas I might try to use EXCEPT on some of my queries. It is limited of course to cases where the full result row is matched to full selected rows in other table(s) and only the non matched kept.

I suppose could do EXCEPT just on the match keys then join back to the full initial table on those keys. I have not tested to see if performs well for multiple match columns. I recall the initial Merge Scan join had only 1 equi join column, and was improved later.

With a little more effort could be coded as Outer Joins seeking NULL joined rows (in where clause) hoping for Merge Scan (and not nested loop) unless in memory sparse index is built. Full Outer Join could force Merge Scan but I hate having to do that. LOL Filter factors very unknown.

In Reply to Tomas Johannsson:

Hi,

While preparing for migration from V11 to V12 we came across this APAR PI87933. In the problem conclusion there is an SQL query that you can run to list any orphaned entries in the SYSLGRNX table.
This query is quite time and CPU consuming for large systems as we experienced at my work.
I rewrote that query (see the APAR ) to this one

WITH MYIDS as(
SELECT HEX(L.LGRPSID) MYID FROM SYSIBM.SYSLGRNX AS L
except
select * from (
SELECT HEX(T.PSID) MYID FROM SYSIBM.SYSTABLESPACE AS T
UNION ALL
SELECT HEX(I.ISOBID) MYID FROM SYSIBM.SYSINDEXES AS I
)x
)
SELECT DISTINCT HEX(LL.LGRPSID) FROM SYSIBM.SYSLGRNX AS LL
WHERE HEX(LL.LGRDBID) IN ('0006')
AND HEX(LL.LGRPSID) IN (select * from MYIDS) with ur;


And it ran for 10 to 30 second depending on what member we ran it on.
Just wanted to share it. :)

Regards,

     Tomas Helgi

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Sep 28, 2018 - 03:31 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Sep 28, 2018 - 03:34 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Sep 28, 2018 - 03:43 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Sep 28, 2018 - 03:48 PM (Europe/Berlin)

Michael Arlebrandt

Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.
(in response to Michael Hannan)
Hi,

and yes Michael,

Better now, runs fast and the result seems correct
I get a list of 8 MYIDs in one of my v12 systems, the SQL from Tomas gave only one row back

And yes , I missed to do the hold before migrating ☺

BUT running
CATMAINT UPDATE UNLDDN PI87933

in v11 says
THE CATALOG HAS ALREADY BEEN MIGRATED

And in v12
THE DB2 CATALOG IS NOT AT THE REQUIRED LEVEL

So I’m stuck with the orphaned entries in the SYSLGRNX in both my v11 and v12 systems ☹

Best regards
Michael Arlebrandt
HCL, Gothenburg,Sweden


From: Michael Hannan [mailto:[login to unmask email]
Sent: den 28 september 2018 3:26
To: [login to unmask email]
Subject: [DB2-L] - RE: Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.


Tomas,

I think your query rewritten is not quite right. Specification of the DBID in SYSTABLESPACE and SYSINDEXES seems to be gone. Are PSIDs unique anyway?

How about this

SELECT HEX(L.LGRPSID) MYID FROM SYSIBM.SYSLGRNX AS L
WHERE L.LGRDBID = x'0006'
EXCEPT
SELECT HEX(T.PSID) MYID FROM SYSIBM.SYSTABLESPACE AS T
WHERE T.DBID = 6
EXCEPT
SELECT HEX(I.ISOBID) MYID FROM SYSIBM.SYSINDEXES AS I
WHERE I.DBID = 6
with ur;



In Reply to Tomas Johannsson:

Hi,

While preparing for migration from V11 to V12 we came across this APAR PI87933 https://apac01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww-01.ibm.com%2Fsupport%2Fdocview.wss%3Fuid%3Dswg1PI87933&data=02%7C01%7Cmichael.arlebrandt%40hcl.com%7Cb321cc5574cc45ebbd4d08d625460034%7C189de737c93a4f5a8b686f4ca9941912%7C0%7C0%7C636737379934527773&sdata=gPpqCsC59yKXGg4EynE7qglVewDgwQT6liwM39cm6LE%3D&reserved=0 . In the problem conclusion there is an SQL query that you can run to list any orphaned entries in the SYSLGRNX table.
This query is quite time and CPU consuming for large systems as we experienced at my work.
I rewrote that query (see the APAR ) to this one

WITH MYIDS as(
SELECT HEX(L.LGRPSID) MYID FROM SYSIBM.SYSLGRNX AS L
except
select * from (
SELECT HEX(T.PSID) MYID FROM SYSIBM.SYSTABLESPACE AS T
UNION ALL
SELECT HEX(I.ISOBID) MYID FROM SYSIBM.SYSINDEXES AS I
)x
)
SELECT DISTINCT HEX(LL.LGRPSID) FROM SYSIBM.SYSLGRNX AS LL
WHERE HEX(LL.LGRDBID) IN ('0006')
AND HEX(LL.LGRPSID) IN (select * from MYIDS) with ur;

And it ran for 10 to 30 second depending on what member we ran it on.
Just wanted to share it. :)

Regards,

Tomas Helgi



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
::DISCLAIMER::
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. E-mail transmission is not guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or may contain viruses in transmission. The e mail and its contents (with or without referred errors) shall therefore not attach any liability on the originator or HCL or its affiliates. Views or opinions, if any, presented in this email are solely those of the author and may not necessarily reflect the views or opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of authorized representative of HCL is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any email and/or attachments, please check them for viruses and other defects.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Michael Arlebrandt

SV: Listing orphaned entries in the SYSLGRNX directory table before migrating from V11 to V12.
(in response to Michael Arlebrandt)
In v11 I don't have the Apar installed so that's why catmaint fails and in v12 I'm at catalog level M502 so probably that's why and I may have to open a PMR in order to get an Apar for that catalog level.

Anyhow Tomas, thanks for pointing this out

Mvh
Michael
-----End Original Message-----