DB2 SQLCODEs -922 to -927 and -551 to -553: connection and auth

How to read this range

Class 42 SQLSTATE values are authorization failures. Class 57 is resource/environment (“Db2 not up”). Class 51 is invalid application state (wrong attach). Class 58 is system error. Connection codes often mean no further SQL is safe until you reconnect correctly—IBM warns about unpredictable results after -922/-923/-924 in several cases.

-922 authorization failure

IBM: AUTHORIZATION FAILURE: error-type ERROR. REASON reason-code. Error-type values:

• User authorization — auth-id from the attachment is not valid for Db2; contact system programmer / CICS / IMS / TSO admin
• Plan access — not authorized to the plan, or plan does not exist
• Duplicate exit — duplicate exit requested
• Installation error — connection or sign-on exit denied the request
• Connect — SQL CONNECT to local Db2 with USER/USING failed; app is connectable and unconnected—only CONNECT, COMMIT, ROLLBACK, and local SET succeed; other SQL gets -900

System action: statement fails; connection to Db2 not established. SQLSTATE 42505. Attempts to issue SQL after -922 when error-type is not Connect can be unpredictable.

-923 connection not established

IBM: CONNECTION NOT ESTABLISHED: DB2 condition REASON reason-code, TYPE resource-type, NAME resource-name. Conditions include:

• Db2 not up
• Db2 not operational
• Db2 shutdown in progress
• Db2 restricted access mode
• Allocation error
• Db2–CICS attachment not up
• Db2–CICS ENTRY disabled
• Object depends on newer-release facilities (fallback)
• Db2 restarted in light mode

Allocation-error causes include:

• Application plan does not exist
• Required database / table space / table / index unavailable
• Data set allocation failed
• Insufficient virtual storage
• Plan executed from an environment restricted at BIND (check SYSPLSYSTEM)

System action: connection not established. Programmer response: wait if Db2 or objects are down; REBIND plan to surface allocation errors; fix CICS attachment then restart it. Further SQL after -923 can be unpredictable. SQLSTATE 57015.

1
2
3
TSO symptom: job runs program without DSN → often -927
CICS symptom: attachment down → -923 with CICS attachment condition
Plan missing: allocation error → REBIND and read bind messages

-924 connection internal error

IBM: DB2 CONNECTION INTERNAL ERROR, function-code, return-code, reason-code. Unexpected internal failure establishing the connection. System action: not established. Programmer response: function/return codes may help; further SQL may be unpredictable. Escalate with reason-code documentation. SQLSTATE 58006.

-927 language interface / environment

IBM: THE LANGUAGE INTERFACE (LI) WAS CALLED WHEN THE CONNECTING ENVIRONMENT WAS NOT ESTABLISHED. THE PROGRAM SHOULD BE INVOKED UNDER THE DSN COMMAND.

TSO: ran the program without establishing the environment via DSN. IMS/CICS/RRSAF/CAF: wrong language interface module. Required modules:

• TSO: DSNELI
• IMS: DFSLI000
• CICS: DSNCLI
• RRSAF: DSNRLI
• CAF: DSNALI

Programmer response: TSO—invoke under DSN RUN. Other environments—link-edit or dynamically allocate the correct LI. DYNAM can load the wrong module at run time. SQLSTATE 51006.

1
2
3
4
5
6
7
//* TSO batch under DSN (conceptual)
//SYSTSIN  DD *
  DSN SYSTEM(DB2A)
  RUN PROGRAM(MYPROG) PLAN(MYPLAN) -
      LIB('MY.LOAD.LIB')
  END
/*

-551 privilege on object

IBM: auth-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation ON OBJECT object-name. None of the checked auth-ids or roles were authorized. Trusted context may show ROLE: role-name.

Common situations include:

• INSERT/UPDATE/DELETE against a read-only view
• CREATE TABLE/VIEW with schema ≠ auth-id without SYSADM/DBADM/DBCTRL (rules vary by object)
• GRANT ALL when grantor has nothing to grant
• Missing EXECUTE on routines in the path
• Missing object under CURRENT RULES STD (looks like auth failure)
• DYNAMICRULES(BIND) with exit/caching nuances

System action: cannot process. Programmer response: verify authority, object existence, schema rules, and RACF/exit setup (ICH408I USER). SQLSTATE 42501.

1
2
3
4
-- Typical fix path (authorized DBA)
GRANT SELECT, UPDATE ON EMP TO APPUSER;
-- Application: ensure primary/secondary auth-id is APPUSER
-- or SET CURRENT SQLID = 'APPUSER' when allowed (-553 if not)

-552 privilege for operation

IBM: authorization-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation. Lacks required authority for the operation (may be a role under trusted context). Administrator should check for attempted authorization violation. Programmer: ensure the ID was granted the needed authority; SECADM required to revoke certain system authorities. SQLSTATE 42502.

Compared with -551: -551 names an object; -552 emphasizes the operation privilege itself (often broader / administrative).

-553 invalid authorization ID or schema name

IBM: AUTHORIZATION ID OR SCHEMA NAME name IS NOT VALID FOR REQUESTED OPERATION. Typical causes:

• SET CURRENT SQLID to a value that is neither primary nor an associated secondary auth-id
• PACKAGE OWNER on CREATE/ALTER PROCEDURE not primary or secondary for the user
• SYSPUBLIC invalid as schema for a private alias for a sequence; avoid schemas starting with SYS (reserved patterns)

System action / response: change the name to a value the user can use. SQLSTATE 42503.

1
2
3
4
5
-- Fails with -553 if OTHERID is not primary/secondary for you
SET CURRENT SQLID = 'OTHERID';
 
-- OK when OTHERID is in your secondary auth-id set
SET CURRENT SQLID = 'OTHERID';

Debugging map

Cannot get in at all

Start with -927 (LI/DSN), then -923 (Db2 up? plan allocate?), then -922 (user/plan auth), then -924 (internal).

Connected but statement denied

-551 / -552 / -553. Dump CURRENT SQLID, primary auth, and the object name from SQLERRMC.

Explain It Like I'm Five

-927 is “you yelled into the library without walking through the door.” -923 is “the library building is closed or your membership card desk is offline.” -922 is “the guard says your badge is wrong.” -924 is “the door motor broke.” -551/-552 are “you are inside but not allowed to touch that book.” -553 is “you tried to wear someone else’s nametag and the rules said no.”

Exercises

1. Match each environment to its LI module (TSO/IMS/CICS/RRSAF/CAF).
2. List five -923 conditions from IBM’s explanation.
3. Cause -553 with SET CURRENT SQLID to an unauthorized ID (in a sandbox).
4. Explain plan-access -922 vs missing SELECT -551.
5. Why can SQL after -923 be “unpredictable,” and what should the program do instead?