DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH

Missy Case

DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH
Team,
We have ONE DB2 V12 CICS program that repeatedly accesses the catalog tables: SYSRESAUTH, SYSUSERAUTH & SYSPACKAUTH. They were going to run this same module in a V11 system & see if the same behavior is observed - I have not heard of their results ATT.

The odd thing is this is the only program observed doing so in a well-established CICS environment that uses the same authorities & bind options for ALL their CICS programs.

It does not do this every time it executes, just randomly, but when it does, of course the response time is not what they wish. Has anyone seen this kind of one-off program behavior? Any ideas? We're all scratching our heads & I said I'd reach out to the smartest DB2 folks I knew!

So - Thanks for any ideas or help - our next step is another PMR for IBM DB2 V12.

[cid:[login to unmask email] http://www.wipro.com

Melissa (Missy) Case

GIS Team Leader / DB2 Database Administrator 3

Wipro Limited


O: +1-605-892-9140
M: +1-701-449-9049

Belle Fourche, SD 57717




The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
Attachments

  • image001.jpg (3k)

Roy Boxwell

DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH
(in response to Missy Case)
I would guess usage of CREATE GLOBAL TEMPORAY TABLE is being verified at run time:

CREATE GLOBAL TEMPORARY TABLE
The CREATE GLOBAL TEMPORARY TABLE statement creates a description of a temporary table at the current server.
Invocation
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
Authorization
The privilege set that is defined below must include at least one of the following:
v The CREATETMTAB system privilege
v The CREATETAB database privilege for any database
v DBADM, DBCTRL, or DBMAINT authority for any database
v SYSADM or SYSCTRL authority
v System DBADM
However, DBADM, DBCTRL, or DBMAINT authority is not sufficient authority if you are creating a temporary table for someone else and the table qualifier is not your authorization ID.
Additional privileges might be required when the data type of a column is a distinct type or the LIKE clause is specified. See the description of distinct-type and LIKE for the details.


Just a guess from my part...

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: [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

From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, November 29, 2017 4:55 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH

Team,
We have ONE DB2 V12 CICS program that repeatedly accesses the catalog tables: SYSRESAUTH, SYSUSERAUTH & SYSPACKAUTH. They were going to run this same module in a V11 system & see if the same behavior is observed - I have not heard of their results ATT.

The odd thing is this is the only program observed doing so in a well-established CICS environment that uses the same authorities & bind options for ALL their CICS programs.

It does not do this every time it executes, just randomly, but when it does, of course the response time is not what they wish. Has anyone seen this kind of one-off program behavior? Any ideas? We're all scratching our heads & I said I'd reach out to the smartest DB2 folks I knew!

So - Thanks for any ideas or help - our next step is another PMR for IBM DB2 V12.

[cid:[login to unmask email] http://www.wipro.com

Melissa (Missy) Case

GIS Team Leader / DB2 Database Administrator 3

Wipro Limited


O: +1-605-892-9140
M: +1-701-449-9049

Belle Fourche, SD 57717




The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com http://www.wipro.com
______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
-----End Original Message-----

Missy Case

DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH
(in response to Roy Boxwell)
Roy,
Thanks for the great idea, but I have learned that unfortunately, the program/package does NOT have a GLOBAL TEMP TABLE. It has SELECTs and SELECT CURSORs with & without SCROLL. So we're still open to all suggestions / ideas.

Thanks

[cid:[login to unmask email] http://www.wipro.com

Melissa (Missy) Case

GIS Team Leader / DB2 Database Administrator 3

Wipro Limited


O: +1-605-892-9140
M: +1-701-449-9049

Belle Fourche, SD 57717




From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Wednesday, November 29, 2017 9:36 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH


** This mail has been sent from an external source. Treat hyperlinks and attachments in this email with caution**
I would guess usage of CREATE GLOBAL TEMPORAY TABLE is being verified at run time:

CREATE GLOBAL TEMPORARY TABLE
The CREATE GLOBAL TEMPORARY TABLE statement creates a description of a temporary table at the current server.
Invocation
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
Authorization
The privilege set that is defined below must include at least one of the following:
v The CREATETMTAB system privilege
v The CREATETAB database privilege for any database
v DBADM, DBCTRL, or DBMAINT authority for any database
v SYSADM or SYSCTRL authority
v System DBADM
However, DBADM, DBCTRL, or DBMAINT authority is not sufficient authority if you are creating a temporary table for someone else and the table qualifier is not your authorization ID.
Additional privileges might be required when the data type of a column is a distinct type or the LIKE clause is specified. See the description of distinct-type and LIKE for the details.


Just a guess from my part...

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de https://clicktime.symantec.com/a/1/hxbocU_0nNmXB0U5-JELBDsajQeeCIy8pyX0UJiGiwA=?d=BN_QNa_o8Zc49i8Ad9RehukS0_z-2QE3zjCYTc2isUyE0GAfDkbQEuLMPki7ZDB92T4jy0H5-4ULw7QH2i_oQ-T4t9SWw_JcNX8Oq90J916LQ_Xt6kzGeESNbMmK5LAfRNWc_BKulAQLRD9fe79gn-arMoBAtv4NrYJ4TiFPuR9xwtk8aevd_VZCr-84rWRNqA_CvAnkU_2pu_8EoasjCkz3mWvTb52VEHx4KfpN7jYeiY5mkh4q9W4Uu_57pbXNvHjnYA9PymFGvx9lkNmqA7SWuAIg6UWQqFL7wFbuaMQ4PbprkZsyfQMkpVV-gq-49r92bqUHh3P9V27GgPs30d0ObAk937xksHfMP6NtOVzA_cWbbpx9yYZjAYoBSw4kBmZPasxtNtKushrZjhU9lGvTOSWtBQiJymZGY7WT2_TcbINenoO3KDlXNq3-yyvDQ4Zqt59G2jT5rKrEVA%3D%3D&u=http%3A%2F%2Fwww.seg.de%2F

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

From: [login to unmask email]<mailto:[login to unmask email]> [mailto:[login to unmask email]
Sent: Wednesday, November 29, 2017 4:55 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH

Team,
We have ONE DB2 V12 CICS program that repeatedly accesses the catalog tables: SYSRESAUTH, SYSUSERAUTH & SYSPACKAUTH. They were going to run this same module in a V11 system & see if the same behavior is observed - I have not heard of their results ATT.

The odd thing is this is the only program observed doing so in a well-established CICS environment that uses the same authorities & bind options for ALL their CICS programs.

It does not do this every time it executes, just randomly, but when it does, of course the response time is not what they wish. Has anyone seen this kind of one-off program behavior? Any ideas? We're all scratching our heads & I said I'd reach out to the smartest DB2 folks I knew!

So - Thanks for any ideas or help - our next step is another PMR for IBM DB2 V12.

[cid:[login to unmask email] https://clicktime.symantec.com/a/1/YqZA-bkCL1nkeKp4OUJttWt1a2Qk2ZczjNJ2wJ35ZJY=?d=BN_QNa_o8Zc49i8Ad9RehukS0_z-2QE3zjCYTc2isUyE0GAfDkbQEuLMPki7ZDB92T4jy0H5-4ULw7QH2i_oQ-T4t9SWw_JcNX8Oq90J916LQ_Xt6kzGeESNbMmK5LAfRNWc_BKulAQLRD9fe79gn-arMoBAtv4NrYJ4TiFPuR9xwtk8aevd_VZCr-84rWRNqA_CvAnkU_2pu_8EoasjCkz3mWvTb52VEHx4KfpN7jYeiY5mkh4q9W4Uu_57pbXNvHjnYA9PymFGvx9lkNmqA7SWuAIg6UWQqFL7wFbuaMQ4PbprkZsyfQMkpVV-gq-49r92bqUHh3P9V27GgPs30d0ObAk937xksHfMP6NtOVzA_cWbbpx9yYZjAYoBSw4kBmZPasxtNtKushrZjhU9lGvTOSWtBQiJymZGY7WT2_TcbINenoO3KDlXNq3-yyvDQ4Zqt59G2jT5rKrEVA%3D%3D&u=http%3A%2F%2Fwww.wipro.com%2F

Melissa (Missy) Case

GIS Team Leader / DB2 Database Administrator 3

Wipro Limited


O: +1-605-892-9140
M: +1-701-449-9049

Belle Fourche, SD 57717




The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com https://clicktime.symantec.com/a/1/acMoqNyDQhz94j7u0-s47PSLSVA5hI1Li6rUkCmO7KM=?d=BN_QNa_o8Zc49i8Ad9RehukS0_z-2QE3zjCYTc2isUyE0GAfDkbQEuLMPki7ZDB92T4jy0H5-4ULw7QH2i_oQ-T4t9SWw_JcNX8Oq90J916LQ_Xt6kzGeESNbMmK5LAfRNWc_BKulAQLRD9fe79gn-arMoBAtv4NrYJ4TiFPuR9xwtk8aevd_VZCr-84rWRNqA_CvAnkU_2pu_8EoasjCkz3mWvTb52VEHx4KfpN7jYeiY5mkh4q9W4Uu_57pbXNvHjnYA9PymFGvx9lkNmqA7SWuAIg6UWQqFL7wFbuaMQ4PbprkZsyfQMkpVV-gq-49r92bqUHh3P9V27GgPs30d0ObAk937xksHfMP6NtOVzA_cWbbpx9yYZjAYoBSw4kBmZPasxtNtKushrZjhU9lGvTOSWtBQiJymZGY7WT2_TcbINenoO3KDlXNq3-yyvDQ4Zqt59G2jT5rKrEVA%3D%3D&u=http%3A%2F%2Fwww.wipro.com
______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com https://clicktime.symantec.com/a/1/El5yQ4YhQ8oZkzD_5nw1M7PY5xrWFi9funDUx8dBGwU=?d=BN_QNa_o8Zc49i8Ad9RehukS0_z-2QE3zjCYTc2isUyE0GAfDkbQEuLMPki7ZDB92T4jy0H5-4ULw7QH2i_oQ-T4t9SWw_JcNX8Oq90J916LQ_Xt6kzGeESNbMmK5LAfRNWc_BKulAQLRD9fe79gn-arMoBAtv4NrYJ4TiFPuR9xwtk8aevd_VZCr-84rWRNqA_CvAnkU_2pu_8EoasjCkz3mWvTb52VEHx4KfpN7jYeiY5mkh4q9W4Uu_57pbXNvHjnYA9PymFGvx9lkNmqA7SWuAIg6UWQqFL7wFbuaMQ4PbprkZsyfQMkpVV-gq-49r92bqUHh3P9V27GgPs30d0ObAk937xksHfMP6NtOVzA_cWbbpx9yYZjAYoBSw4kBmZPasxtNtKushrZjhU9lGvTOSWtBQiJymZGY7WT2_TcbINenoO3KDlXNq3-yyvDQ4Zqt59G2jT5rKrEVA%3D%3D&u=http%3A%2F%2Fwww.symanteccloud.com
______________________________________________________________________
-----End Original Message-----

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

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
Attachments

  • image001.jpg (3k)

Dave Nance

DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH
(in response to Missy Case)
Doesn't a cursor with scroll pretty much create a global temp table? David Nance


From: "[login to unmask email]" <[login to unmask email]>
To: "[login to unmask email]" <[login to unmask email]>
Sent: Wednesday, November 29, 2017 3:25 PM
Subject: [DB2-L] - RE: DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH

<!--#yiv5686630627 _filtered #yiv5686630627 {font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv5686630627 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv5686630627 {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;} _filtered #yiv5686630627 {font-family:Helvetica-Bold;} _filtered #yiv5686630627 {font-family:Palatino-Roman;} _filtered #yiv5686630627 {font-family:XyvisionPiOne;} _filtered #yiv5686630627 {font-family:Palatino-Italic;}#yiv5686630627 #yiv5686630627 p.yiv5686630627MsoNormal, #yiv5686630627 li.yiv5686630627MsoNormal, #yiv5686630627 div.yiv5686630627MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri", sans-serif;}#yiv5686630627 a:link, #yiv5686630627 span.yiv5686630627MsoHyperlink {color:#0563C1;text-decoration:underline;}#yiv5686630627 a:visited, #yiv5686630627 span.yiv5686630627MsoHyperlinkFollowed {color:#954F72;text-decoration:underline;}#yiv5686630627 p {margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New Roman", serif;}#yiv5686630627 p.yiv5686630627msonormal0, #yiv5686630627 li.yiv5686630627msonormal0, #yiv5686630627 div.yiv5686630627msonormal0 {margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New Roman", serif;}#yiv5686630627 span.yiv5686630627EmailStyle19 {font-family:"Courier New";color:#7030A0;font-weight:bold;text-decoration:none none;}#yiv5686630627 span.yiv5686630627EmailStyle20 {font-family:"Calibri", sans-serif;color:#1F497D;}#yiv5686630627 span.yiv5686630627EmailStyle21 {font-family:"Courier New";color:#7030A0;font-weight:bold;text-decoration:none none;}#yiv5686630627 .yiv5686630627MsoChpDefault {font-size:10.0pt;} _filtered #yiv5686630627 {margin:1.0in 1.0in 1.0in 1.0in;}#yiv5686630627 div.yiv5686630627WordSection1 {}-->Roy, Thanks for the great idea, but I have learned that unfortunately, the program/package does NOT have a GLOBAL TEMP TABLE. It has SELECTs and SELECT CURSORs with & without SCROLL.  So we’re still open to all suggestions / ideas.   Thanks  
| | Melissa (Missy) Case |
| GIS Team Leader / DB2 Database Administrator 3 |
| Wipro Limited   |
| O: +1-605-892-9140 M: +1-701-449-9049 |
| Belle Fourche, SD 57717 |
| |

    From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Wednesday, November 29, 2017 9:36 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH   ** This mail has been sent from an external source. Treat hyperlinks and attachments in this email with caution** I would guess usage of CREATE GLOBAL TEMPORAY TABLE is being verified at run time:   CREATE GLOBAL TEMPORARY TABLE The CREATE GLOBAL TEMPORARY TABLE statement creates a description of a temporary table at the current server. Invocation This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared. Authorization The privilege set that is defined below must include at least one of the following: vThe CREATETMTAB system privilege vThe CREATETAB database privilege for any database vDBADM, DBCTRL, or DBMAINT authority for any database vSYSADM or SYSCTRL authority vSystem DBADM However, DBADM, DBCTRL, or DBMAINT authority is not sufficient authority if you are creating a temporary table for someone else and the table qualifier is not your authorization ID. Additional privileges might be required when the data type of a column is a distinct type or the LIKE clause is specified. See the description ofdistinct-typeand LIKE for the details.     Just a guess from my part...   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: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert   From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, November 29, 2017 4:55 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH   Team, We have ONE DB2 V12 CICS program that repeatedly accesses the catalog tables: SYSRESAUTH, SYSUSERAUTH & SYSPACKAUTH.  They were going to run this same module in a V11 system & see if the same behavior is observed – I have not heard of their results ATT.   The odd thing is this is the only program observed doing so in a well-established CICS environment that uses the same authorities & bind options for ALL their CICS programs.   It does not do this every time it executes, just randomly, but when it does, of course the response time is not what they wish.  Has anyone seen this kind of one-off program behavior?  Any ideas?  We’re all scratching our heads & I said I’d reach out to the smartest DB2 folks I knew!    So – Thanks for any ideas or help – our next step is another PMR for IBM DB2 V12.  
| | Melissa (Missy) Case |
| GIS Team Leader / DB2 Database Administrator 3 |
| Wipro Limited   |
| O: +1-605-892-9140 M: +1-701-449-9049 |
| Belle Fourche, SD 57717 |
| |

    The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.www.wipro.com
______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________ -----End Original Message-----   -----End Original Message-----
______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________ The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

Attachment Links: image001.jpg (3 k)  
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email] a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Walter Jani&#223;en

AW: DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH
(in response to Dave Nance)
Yes, it does and that’s e.g. the reason that you can run out of internal OBIDs.

Could the problem be access path related?

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Dave Nance [mailto:[login to unmask email]
Gesendet: Mittwoch, 29. November 2017 22:30
An: [login to unmask email]
Betreff: [DB2-L] - RE: DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH

Doesn't a cursor with scroll pretty much create a global temp table?

David Nance


-----End Original Message-----
Attachment Links: image001.jpg (3 k) http://www.idug.org/p/fo/do/?download=1&fid=8966
Site Links: View post online http://www.idug.org/p/fo/st/?post=183882&anc=p183882#p183882 View mailing list online http://www.idug.org/p/fo/si/?topic=19 Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription http://www.idug.org/p/us/to

This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug http://www.esaigroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2
________________________________


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

  • image001.png (2.6k)

Joe Geller

RE: AW: DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH
(in response to Walter Janißen)

I believe that a scroll cursor use a declared temp table under the covers, not a Created temp table.

Joe

In Reply to Walter Janißen:

Yes, it does and that’s e.g. the reason that you can run out of internal OBIDs.

Could the problem be access path related?

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Dave Nance [mailto:[login to unmask email]
Gesendet: Mittwoch, 29. November 2017 22:30
An: [login to unmask email]
Betreff: [DB2-L] - RE: DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH

Doesn't a cursor with scroll pretty much create a global temp table?

David Nance


-----End Original Message-----
Attachment Links: image001.jpg (3 k) http://www.idug.org/p/fo/do/?download=1&fid=8966
Site Links: View post online http://www.idug.org/p/fo/st/?post=183882&anc=p183882#p183882 View mailing list online http://www.idug.org/p/fo/si/?topic=19 Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription http://www.idug.org/p/us/to

This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug http://www.esaigroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2
________________________________


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

Michael Hannan

RE: DB2 V12 z/OS puzzler - SYSUERAUTH SYSPACKAUTH & SYSRESAUTH
(in response to Missy Case)

Missy,

I am not really an expert on Authorisations, but it seems clear that Authorities checking is related to your problem, given access to the tables you specified. This could be intermittent particularly if any Dynamic SQL or min-BIND behaviour is occurring at run time and information drops out of the Cached data. Check your Validate and Dynamic Rules BIND options. Check for anything that must be determined dynamically at run time. REOPT VARS on Bind for example or the use of temp tables, possibly required by your scrollable cursors, 'Sensitive' type probably being the most expensive.

I have seen customers with average Getpages for scrollable Cursors being quite high, but did not investigate fully which objects the high Getpages were occurring on, and why. I don't like to see scrollable Cursors used in very frequent SQLs with a high cost, unless they can be justified.

Unfortunately my research on the performance of scrollable cursors in the real world is very incomplete at this time. I just see they don't seem to perform well, in several practical examples. Be wary of them without performance testing.

I don't say that Scrollable Cursors is definitely the source of your problem, but it raises suspicions. 
 
In Reply to Missy Case:

Team,
We have ONE DB2 V12 CICS program that repeatedly accesses the catalog tables: SYSRESAUTH, SYSUSERAUTH & SYSPACKAUTH. They were going to run this same module in a V11 system & see if the same behavior is observed - I have not heard of their results ATT.

The odd thing is this is the only program observed doing so in a well-established CICS environment that uses the same authorities & bind options for ALL their CICS programs.

It does not do this every time it executes, just randomly, but when it does, of course the response time is not what they wish. Has anyone seen this kind of one-off program behavior? Any ideas? We're all scratching our heads & I said I'd reach out to the smartest DB2 folks I knew!

So - Thanks for any ideas or help - our next step is another PMR for IBM DB2 V12.

[cid:[login to unmask email] http://www.wipro.com

Melissa (Missy) Case

GIS Team Leader / DB2 Database Administrator 3

Wipro Limited


O: +1-605-892-9140
M: +1-701-449-9049

Belle Fourche, SD 57717




The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________


Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Dec 10, 2017 - 08:09 AM (Europe/Berlin)