DB2 z/OS - Odd -567 SQLCODE

Bill Gallagher

DB2 z/OS - Odd -567 SQLCODE
Good morning,

First: we are running DB2 z/OS v11, CM.

I am receiving an odd SQLCODE when attempting to execute a native SQL stored procedure. I am receiving the following message when I try to invoke it from Data Studio:

YCCFDBA AUTHORIZATION ERROR USING AUTHORITY PACKAGE = PRIVILEGE =. SQLCODE=-567, SQLSTATE=42503, DRIVER=3.66.46
Run of routine failed.
- Roll back completed successfully.

I believe the failing statement in the stored procedure is:

SET CURRENT SQLID = 'YCCFDBA';

What's particularly odd is that the format of the -567 SQLCODE from the "Codes" manual is that it indicates that a -567 pertains to some sort of BIND issue. The first keyword of the message should be either BIND, REBIND or FREE. Instead, it is YCCFDBA.

Any thoughts on what might be happening here?

Bill Gallagher
DB2 Database Administrator
State of Connecticut
Department of Children and Families
Office: 860-263-1389
[login to unmask email]<mailto:[login to unmask email]>


Daniel Luksetich

DB2 z/OS - Odd -567 SQLCODE
(in response to Bill Gallagher)
Perhaps the stored procedure package got invalidated?

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IBM Certified Database Adminstrator - DB2 11 DBA for z/OS

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Application Developer - DB2 11 for z/OS

IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and
Windows



From: GALLAGHER, WILLIAM [mailto:[login to unmask email]
Sent: Tuesday, February 13, 2018 9:00 AM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - DB2 z/OS - Odd -567 SQLCODE



Good morning,



First: we are running DB2 z/OS v11, CM.



I am receiving an odd SQLCODE when attempting to execute a native SQL stored
procedure. I am receiving the following message when I try to invoke it
from Data Studio:

YCCFDBA AUTHORIZATION ERROR USING AUTHORITY PACKAGE = PRIVILEGE =.
SQLCODE=-567, SQLSTATE=42503, DRIVER=3.66.46

Run of routine failed.

- Roll back completed successfully.



I believe the failing statement in the stored procedure is:



SET CURRENT SQLID = 'YCCFDBA';



What's particularly odd is that the format of the -567 SQLCODE from the
"Codes" manual is that it indicates that a -567 pertains to some sort of
BIND issue. The first keyword of the message should be either BIND, REBIND
or FREE. Instead, it is YCCFDBA.



Any thoughts on what might be happening here?



Bill Gallagher

DB2 Database Administrator

State of Connecticut

Department of Children and Families

Office: 860-263-1389

[login to unmask email] <mailto:[login to unmask email]>







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

Bill Gallagher

DB2 z/OS - Odd -567 SQLCODE
(in response to Daniel Luksetich)
Nope, I looked at that. Valid and operational.

Bill Gallagher
DB2 Database Administrator
State of Connecticut
Department of Children and Families
Office: 860-263-1389
[login to unmask email]<mailto:[login to unmask email]>


From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Tuesday, February 13, 2018 10:46 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 z/OS - Odd -567 SQLCODE

Perhaps the stored procedure package got invalidated?
Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Past-Chairman
IBM Certified Database Adminstrator - DB2 11 DBA for z/OS
IBM Certified System Administrator - DB2 11 for z/OS
IBM Certified Application Developer - DB2 11 for z/OS
IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and Windows

From: GALLAGHER, WILLIAM [mailto:[login to unmask email]
Sent: Tuesday, February 13, 2018 9:00 AM
To: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Subject: [DB2-L] - DB2 z/OS - Odd -567 SQLCODE

Good morning,

First: we are running DB2 z/OS v11, CM.

I am receiving an odd SQLCODE when attempting to execute a native SQL stored procedure. I am receiving the following message when I try to invoke it from Data Studio:

YCCFDBA AUTHORIZATION ERROR USING AUTHORITY PACKAGE = PRIVILEGE =. SQLCODE=-567, SQLSTATE=42503, DRIVER=3.66.46
Run of routine failed.
- Roll back completed successfully.

I believe the failing statement in the stored procedure is:

SET CURRENT SQLID = 'YCCFDBA';

What's particularly odd is that the format of the -567 SQLCODE from the "Codes" manual is that it indicates that a -567 pertains to some sort of BIND issue. The first keyword of the message should be either BIND, REBIND or FREE. Instead, it is YCCFDBA.

Any thoughts on what might be happening here?

Bill Gallagher
DB2 Database Administrator
State of Connecticut
Department of Children and Families
Office: 860-263-1389
[login to unmask email]<mailto:[login to unmask email]>



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

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

James Campbell

DB2 z/OS - Odd -567 SQLCODE
(in response to Bill Gallagher)
Perhaps there has been some translation from Db2 for z/OS to Db2 (LUW).

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.messa
ges.sql.doc/doc/msql00567n.html

"SQL0567N The operation failed because the specified authorization name does not meet
the identifier naming rules. Authorization name: authorization-name.

Explanation

Rules exist for the naming of all database objects, user names or authorization names,
passwords, groups, files, and paths. Some of these rules are specific to the platform you are
working on.

This message is returned when a specified authorization name does not satisfy the identifier
naming rules. For example this message can be returned when an authorization name
contains characters other than a through z, A through Z, 0 through 9, and the supported
special characters."

You might want to look at what the actual sqlcode generated from within Db2 for z/OS.

But are you *certain* that the
> SET CURRENT SQLID = 'YCCFDBA';
is authorised?

James Campbell


On 13 Feb 2018 at 14:59, GALLAGHER, WILLIAM wrote:

>
> Good morning,
>  
> First: we are running DB2 z/OS v11, CM.
>  
> I am receiving an odd SQLCODE when attempting to execute a native SQL stored procedure.  I am
> receiving the following message when I try to invoke it from Data Studio:
>
> YCCFDBA AUTHORIZATION ERROR USING  AUTHORITY PACKAGE =  PRIVILEGE =. SQLCODE=-567,
> SQLSTATE=42503, DRIVER=3.66.46
> Run of routine failed.
> - Roll back completed successfully.
>  
> I believe the failing statement in the stored procedure is:
>  
> SET CURRENT SQLID = 'YCCFDBA';
>  
> What´s particularly odd is that the format of the -567 SQLCODE from the "Codes" manual is that
> it indicates that a -567 pertains to some sort of BIND issue.  The first keyword of the message
> should be either BIND, REBIND or FREE.  Instead, it is YCCFDBA.
>  
> Any thoughts on what might be happening here?
>  
> Bill Gallagher
> DB2 Database Administrator
> State of Connecticut
> Department of Children and Families
> Office: 860-263-1389
> [login to unmask email]
>  

Peter Hunkeler

AW: DB2 z/OS - Odd -567 SQLCODE
(in response to Bill Gallagher)

>YCCFDBA AUTHORIZATION ERROR USING AUTHORITY PACKAGE = PRIVILEGE =. SQLCODE=-567, SQLSTATE=42503, DRIVER=3.66.46
I didn't know "messaging slang" found its way into IBM's product message vocabulary: YCCFDBA = Your Current Code Fails Data Base Authorization :-)
Forgive me, I couldn't resist
--Peter Hunkeler

Bill Gallagher

DB2 z/OS - Odd -567 SQLCODE
(in response to James Campbell)
The problem turned out to be (as I suspected) that the authid in question needed to be added as a secondary authid to my ID (which was executing the stored procedure).

It's just really odd that this kicked out with a -567 SQLCODE instead of a -553 SQLCODE, which is what you should get when a SET CURRENT SQLID statement fails because the new authid is not a valid secondary authid.

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: James Campbell [mailto:[login to unmask email]
Sent: Tuesday, February 13, 2018 6:55 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 z/OS - Odd -567 SQLCODE

Perhaps there has been some translation from Db2 for z/OS to Db2 (LUW).

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00567n.html

"SQL0567N The operation failed because the specified authorization name does not meet the identifier naming rules. Authorization name: authorization-name.

Explanation

Rules exist for the naming of all database objects, user names or authorization names, passwords, groups, files, and paths. Some of these rules are specific to the platform you are working on.

This message is returned when a specified authorization name does not satisfy the identifier naming rules. For example this message can be returned when an authorization name contains characters other than a through z, A through Z, 0 through 9, and the supported special characters."

You might want to look at what the actual sqlcode generated from within Db2 for z/OS.

But are you *certain* that the
> SET CURRENT SQLID = 'YCCFDBA';
is authorised?

James Campbell


On 13 Feb 2018 at 14:59, GALLAGHER, WILLIAM wrote:

>
> Good morning,
>
> First: we are running DB2 z/OS v11, CM.
>
> I am receiving an odd SQLCODE when attempting to execute a native SQL stored procedure. I am
> receiving the following message when I try to invoke it from Data Studio:
>
> YCCFDBA AUTHORIZATION ERROR USING AUTHORITY PACKAGE = PRIVILEGE =. SQLCODE=-567,
> SQLSTATE=42503, DRIVER=3.66.46
> Run of routine failed.
> - Roll back completed successfully.
>
> I believe the failing statement in the stored procedure is:
>
> SET CURRENT SQLID = 'YCCFDBA';
>
> What's particularly odd is that the format of the -567 SQLCODE from the "Codes" manual is that
> it indicates that a -567 pertains to some sort of BIND issue. The first keyword of the message
> should be either BIND, REBIND or FREE. Instead, it is YCCFDBA.
>
> Any thoughts on what might be happening here?
>
> Bill Gallagher
> DB2 Database Administrator
> State of Connecticut
> Department of Children and Families
> Office: 860-263-1389
> [login to unmask email]<mailto:[login to unmask email]>
>


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