SQL0204 on IBM i: Object Not Found and How to Investigate It

By /

Not SQL0204N. Search results for “SQL0204” often return Db2 for Linux, UNIX, and Windows material that uses the SQL0204N message ID and the wording “is an undefined name”. That is a different Db2 product family with its own message catalog. This article covers only the IBM i (Db2 for i) message SQL0204, whose text and codes were checked against IBM’s IBM i 7.5 SQL messages listing (see Sources Consulted).

This article is for RPG and SQL developers and IBM i operations staff who have hit SQLCODE -204 or message ID SQL0204 in a job log, an SQLRPGLE program, Run SQL Scripts, or a client connection (ODBC/JDBC), especially when the same statement works for one user, job, or environment and fails in another. It explains why Db2 for i cannot find an object you believe exists, and how to prove where Db2 actually looked.

Prerequisites: a basic understanding of IBM i libraries and the job library list, the difference between system naming (library/object) and SQL naming (schema.object), and enough RPGLE/SQLRPGLE to follow an embedded SQL statement and an SQLCODE/SQLSTATE check.

What SQL0204 means

SQL0204 is Db2 for i’s “object not found” message. Confirmed against IBM’s IBM i 7.5 “Listing of SQL messages” page:

  • Message text: &1 in &2 type *&3 not found. At run time &1 is the object name, &2 is the library (schema), and *&3 is the object type, for example *FILE, *N, or *MEM.
  • SQLCODEs: +204 and -204
  • SQLSTATEs: 01532 and 42704

IBM’s Db2 for i SQLSTATE listing pairs them as 42704 (“An undefined object or constraint name was detected.”) with SQLCODE -204, and 01532 (“An undefined object name was detected.”) with SQLCODE +204. -204 / 42704 is the error form, and it is the one almost everyone searches for. The +204 / 01532 pair is the warning form. Which statements return the warning instead of the error was not confirmed against IBM documentation (UNVERIFIED_SOURCE for when the +204 form occurs), so do not write logic that assumes one or the other. Check both values.

IBM’s cause text (paraphrased here, quoted in Sources Consulted) adds detail that matters when you read the message:

  • Type *N means the object is not a file. For a statement other than ALTER TABLE, a type of *N means a function, procedure, trigger, or sequence was not found. For an ALTER TABLE statement, *N means a constraint or partition was not found.
  • Member name *ALL means the table is not partitioned.
  • For functions, &1 is the service program that contains the function. The function will not be found unless the external name and the usage name match exactly. A function is also not considered a candidate unless the user has the EXECUTE privilege on it.
  • IBM tells you to read the job log. For a function that was not found, a nearby job-log message gives more detail on which function name was searched for and which name did not match.

IBM’s recovery text tells you to change the name and try again. For an external function, it tells you to make sure the case of EXTERNAL NAME on CREATE FUNCTION exactly matches the case of the name the service program exports.

Related messages that are not SQL0204

  • SQL0206 (SQLCODE -206, SQLSTATE 42703, “Column or global variable &1 not found.”) means the table was found, but a column or global variable in the statement was not. Confirmed against the same IBM listing.
  • SQL0551 (SQLCODE -551, SQLSTATE 42501, “Not authorized to object &1 in &2 type *&3.”) is the explicit authority failure for an object Db2 did find. Confirmed against the same IBM listing. See the authority section below for why authority problems sometimes surface as SQL0204 instead.

What causes SQL0204

1. The name really is wrong

This is the simplest case and the first thing to rule out: a misspelling, the wrong object, or the wrong schema in a qualified name (PRODLIB.CUSTMAST when the table lives in PRODDTA). The message text tells you exactly which name and which library Db2 used. Read it literally before assuming anything more complicated.

A common variant is asking for an object in a schema where it never lived. One example (from a practitioner forum, not IBM documentation) is querying SYSIBM.SYSTABLES on IBM i when the catalog view you want is QSYS2.SYSTABLES.

2. Db2 looked in a different place than you expected (naming mode and the default schema)

This is the most common cause of “it works for me but not in batch” or “it works in Run SQL Scripts but not in my program”. Confirmed against IBM’s Db2 for i SQL reference page “Qualification of unqualified object names” (7.4): unqualified table, view, alias, index, sequence, trigger, and similar names get their schema from a default schema, and that default depends on the naming convention and on whether the statement is static or dynamic.

Static SQL (embedded SQL compiled with CRTSQLRPGI and similar commands):

  • If DFTRDBCOL is specified on the CRTSQLxxx command (or with SET OPTION), the default schema is that schema.
  • Otherwise, under SQL naming the default schema is the authorization ID of the statement.
  • Otherwise, under system naming the default schema is the job library list (*LIBL).

Dynamic SQL (prepared statements, Run SQL Scripts, ODBC/JDBC):

  • If no default schema is explicitly set, SQL naming uses the run-time authorization ID and system naming uses the job library list (*LIBL).
  • A default schema can be set explicitly through several interfaces: DFTRDBCOL plus DYNDFTCOL(*YES) on CRTSQLxxx/CRTSQLPKG, DFTRDBCOL on RUNSQLSTM, the default library/schema properties of ODBC, JDBC, OLE DB, and ADO.NET connections, and SET SCHEMA (or the QSQCHGDC API) from any interface.

Also confirmed (IBM CURRENT SCHEMA and SET SCHEMA pages): the initial value of the CURRENT SCHEMA special register is the authorization ID of the session user, and under system naming its initial value is *LIBL. CURRENT SCHEMA is used to qualify unqualified names in dynamically prepared statements. For static SQL, DFTRDBCOL controls the schema instead.

What this means in practice:

  • Under SQL naming, an unqualified CUSTMAST resolves to a schema named after the user profile (for example JSMITH.CUSTMAST) unless a default schema is set. Unless that user happens to have a schema of the same name containing the table, the result is SQL0204 naming that user-profile schema. Look at the &2 value in the message. If it is a user profile name, this is almost certainly your cause.
  • Under system naming, an unqualified name is searched through the job’s library list. A batch job submitted with a different job description, a client connection with a different default library list, or a job whose library list was changed by an earlier step can all fail to find an object that your interactive session finds.
  • SET SCHEMA replaces the library list for dynamic SQL. IBM’s qualification rules say that when a default schema is explicitly set (including with SET SCHEMA), dynamic SQL uses it instead of the naming-mode default, and IBM’s SET SCHEMA page states that the value of CURRENT SCHEMA “is used as the qualifier for some unqualified names” (unless the program was created with DYNDFTCOL). So once SET SCHEMA sets a specific schema in a system-naming job, unqualified table names in dynamic statements are qualified with that schema, not searched for through *LIBL. If a dynamic statement starts failing with SQL0204 after a SET SCHEMA, check this first.
  • SET SCHEMA does not change the path. IBM’s SET SCHEMA page also notes that setting CURRENT SCHEMA does not change CURRENT PATH, so function, procedure, and user-defined type resolution may not find objects in the new schema. An unqualified function call that fails with SQL0204 type *N after a SET SCHEMA may need SET PATH or a qualified name.

3. The object has not been created yet, or was created somewhere else

The object may not exist yet in this environment. Typical examples are a table missing from a test library, a CREATE that failed earlier in the same script, or an object created into the wrong library because the creating job’s default schema or current library was different. A dropped-and-recreated table caught mid-deploy produces the same result.

UNVERIFIED_SOURCE: practitioner sources report that the SQL precompiler (CRTSQLRPGI) reports a missing table during precompile as an SQL0204 warning-level message and still creates the program, so the real failure only appears at run time. This matches common experience, but it was not confirmed against IBM documentation. Check the precompile listing for SQL0204 entries instead of assuming a clean compile means every referenced object exists.

4. Case sensitivity and delimited identifiers

Confirmed (IBM Db2 for i SQL reference, “SQL identifiers”): ordinary identifiers are converted to uppercase, and delimited identifiers (names in double quotes) are not.

So SELECT * FROM custmast looks for CUSTMAST, but SELECT * FROM "custmast" looks for an object whose name is literally lowercase custmast. The reverse also bites: a table created with a quoted mixed-case name, often by a tool or an ORM, can only be found by quoting that exact case. When the catalog shows a name in mixed or lower case, you need double quotes and the exact case to reach it.

The same rule applies to external functions, as IBM’s own recovery text says: the case of EXTERNAL NAME must exactly match the exported procedure name in the service program.

5. Long SQL names versus short system names

A table created with a long SQL name (more than 10 characters) also has a short system name. Using the system name where the SQL name is expected, or the other way round, can lead to a not-found result depending on the interface. The catalog query below shows both names side by side so you can see which one you should be using. (UNVERIFIED_SOURCE: the exact interfaces that accept only one form were not confirmed against IBM documentation.)

6. Authority problems that look like “not found”

Db2 normally reports a missing authority as SQL0551, not SQL0204. There are two confirmed or plausible exceptions:

  • Confirmed (IBM SQL0204 cause text): for functions, “A function is not considered as a candidate unless the user has the EXECUTE privilege for the function.” Missing EXECUTE authority on a user-defined function can therefore surface as SQL0204 rather than SQL0551, because function resolution never considers the function at all.
  • UNVERIFIED_SOURCE: practitioners report that during library-list searches, a library the user cannot use may effectively be skipped, so an object in that library is “not found” instead of “not authorized”. This was not confirmed against IBM documentation. If the object exists in the catalog but one user gets SQL0204 and another does not, compare their authority to both the object and its library. See IBM i Security in 2026: User Profiles, Object Authority, Adopted Authority, and Practical Security Patterns.

7. Missing member (type *MEM)

UNVERIFIED_SOURCE (third-party vendor guidance): a physical file with no members, or a reference to a member that does not exist, can produce a *MEM form of the not-found message. IBM’s message text format (&1 in &2 type *&3 not found.) allows any object type in *&3, so treat a *MEM type as a pointer to member problems rather than a missing file.

How to investigate SQL0204

Step 1: read the full message in the job log (DSPJOBLOG)

Start with DSPJOBLOG (or WRKJOB OPTION(*JOBLOG)), find the SQL0204 entry, and display its second-level text. Write down three values:

  • the object name (&1)
  • the library or schema Db2 used (&2). If this is *LIBL, Db2 searched the library list. If it is a user profile name, Db2 applied SQL-naming defaults.
  • the object type (*&3). *FILE points at tables, views, and indexes. *N points at routines, triggers, sequences, or constraints. *MEM points at members.

For functions, IBM’s cause text tells you to look for a nearby job-log message that names the function being searched for and the name that did not match. For general job-log reading, see IBM i Job Log and CPF Messages Reference.

Step 2: check where the failing job actually looks

For system naming, display the library list of the job that failed, not your own session. If you can reproduce the failure interactively:

DSPLIBL

For a batch job, compare the job description’s initial library list with what your interactive session uses. IBM i Library List Commands Reference covers DSPLIBL, ADDLIBLE, CHGLIBL, and CHGCURLIB.

For dynamic SQL in an SQL session, check the default schema directly:

VALUES CURRENT SCHEMA;

Under system naming with no explicit default schema, this should show *LIBL, per IBM’s SET SCHEMA documentation. Any other value means a default schema has been set and unqualified names resolve only to that schema.

The QSYS2.LIBRARY_LIST_INFO view returns the same information as rows. Per IBM’s documentation it “contains information about the current job’s library list”, with columns including ORDINAL_POSITION, SCHEMA_NAME, SYSTEM_SCHEMA_NAME, and TYPE:

SELECT ORDINAL_POSITION, SCHEMA_NAME, TYPE
  FROM QSYS2.LIBRARY_LIST_INFO
 ORDER BY ORDINAL_POSITION;

Because it reports the current job’s library list, run it from the job (or connection) that fails, not from your own session. UNVERIFIED_SOURCE (release availability): the IBM page does not state which release or TR introduced the view. Fall back to DSPLIBL if it is not available on your system.

For static embedded SQL, check how the program was compiled. The naming option and any DFTRDBCOL value set at compile time decide the default schema, not the session that calls the program. DSPPGM and the precompile listing show the compile options. (UNVERIFIED_SOURCE: the exact DSPPGM panel that displays the SQL options was not confirmed against IBM documentation.)

Step 3: ask the catalog whether the object exists, and where

Confirmed column names (IBM SYSTABLES catalog view page, 7.5): TABLE_NAME, TABLE_SCHEMA, SYSTEM_TABLE_NAME, SYSTEM_TABLE_SCHEMA, and TABLE_TYPE. TABLE_TYPE values are A (alias), L (logical file), M (materialized query table), P (physical file), T (table), and V (view).

SELECT TABLE_SCHEMA, TABLE_NAME,
       SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME,
       TABLE_TYPE
  FROM QSYS2.SYSTABLES
 WHERE UPPER(TABLE_NAME) = 'CUSTMAST'
    OR UPPER(SYSTEM_TABLE_NAME) = 'CUSTMAST';

How to read the result:

  • No rows: the object does not exist on this system under that name (or you are not looking for the right name). Check for a failed or missing CREATE.
  • A row in a schema that is not in your library list or default schema: this is the resolution problem from cause 2. Either qualify the name or fix the library list or default schema.
  • A row whose TABLE_NAME is in lower or mixed case: this is the case-sensitivity problem from cause 4. Quote the name exactly.
  • A row whose TABLE_NAME differs from SYSTEM_TABLE_NAME: check which name your statement uses (cause 5).

The UPPER() calls in the query are deliberate, so the search also catches mixed-case names. IBM describes the catalog as containing information about all tables, but UNVERIFIED_SOURCE: whether any rows are filtered by the querying user’s privileges was not confirmed against IBM documentation. To be safe, run the query as a user with broad authority (or compare results between users) before concluding that a “no rows” result proves the object is missing for everyone.

For routines (type *N), the corresponding catalog view is QSYS2.SYSROUTINES. Confirmed column names (IBM SYSROUTINES catalog view page, 7.5): ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE (PROCEDURE or FUNCTION), and SPECIFIC_NAME:

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, SPECIFIC_NAME
  FROM QSYS2.SYSROUTINES
 WHERE UPPER(ROUTINE_NAME) = 'MYFUNC';

Step 4: retry with a fully qualified name

As a diagnostic step, run the failing statement with the name qualified explicitly (PRODDTA.CUSTMAST under SQL naming, PRODDTA/CUSTMAST under system naming). If the qualified form works, the object exists and you are allowed to use it. The problem is name resolution (cause 2), not the object itself. If the qualified form still fails with SQL0204, the problem is the name, the case, or the object’s existence. If it fails with SQL0551 instead, the problem is authority.

IBM’s SQL reference (7.4) confirms that under system naming “the qualified form that uses a period is also accepted”, and an IBM Support technology-updates page notes that historically system naming required a slash. Practitioner sources tie the change to an IBM i 7.1 Db2 group PTF (UNVERIFIED_SOURCE for the exact release or PTF level). On older systems, use / with system naming. IBM also notes exceptions: in CREATE, COMMENT, DROP, GRANT, and REVOKE statements, function names can only be qualified as schema-name/function-name under system naming.

A conservative SQLRPGLE example

STATIC CHECKED: this example has not been compiled or run on a live IBM i or PUB400 system. It was reviewed for syntactic plausibility against known SQLRPGLE and embedded-SQL conventions, but it is not confirmed to compile or run correctly. Treat it as an illustrative starting point, not production-ready code, and test it before use.

The pattern below treats SQL0204 as a configuration or deployment error, not a transient condition. Retrying a not-found error does not help: the object will not appear because you waited. The useful response is to stop, capture the exact name, library, and type Db2 reported, and surface it to someone who can fix the library list, the default schema, or the missing object.

**FREE
ctl-opt main(GetBalance);

dcl-proc GetBalance;
  dcl-pi *n;
    custNo  packed(7:0) const;
    balance packed(11:2);
  end-pi;

  dcl-s sqlStateW char(5);
  dcl-s sqlCodeW  int(10);
  dcl-s errTokens varchar(70);

  // Unqualified name on purpose: under system naming this resolves
  // through the job library list (*LIBL); under SQL naming it resolves
  // to the default schema / authorization ID. That resolution is the
  // most common reason the same program behaves differently in
  // different jobs.
  exec sql
    SELECT BALANCE
      INTO :balance
      FROM CUSTMAST
     WHERE CUSTNO = :custNo;

  sqlCodeW  = sqlcode;
  sqlStateW = sqlstate;

  select;
    when sqlCodeW = 0;
      return;

    when sqlCodeW = 100;
      // Row not found: CUSTMAST exists, this customer does not.
      // This is NOT SQL0204 -- do not confuse "no row" with
      // "no table".
      balance = 0;
      return;

    when sqlCodeW = -204 or sqlStateW = '42704';
      // SQL0204: Db2 could not find the object (or, for functions,
      // could not find a candidate the user may EXECUTE).
      // This is a configuration/deployment problem, not a transient
      // one: do not retry.
      //
      // SQLERRMC holds the message replacement tokens (object name,
      // library/schema, type). Log them exactly as returned rather
      // than assuming a format -- the job log second-level text for
      // SQL0204 is the authoritative description.
      errTokens = %subst(sqlerrmc : 1 : %min(sqlerrml : 70));
      // Write sqlCodeW, sqlStateW, errTokens and the job's library
      // list / naming mode to an application log or message queue
      // here, then end with an error so the caller does not continue
      // with a missing table.
      return;

    when sqlCodeW = -551;
      // SQL0551: the object WAS found, but the user is not
      // authorized. Different fix (authority), so report separately.
      return;

    other;
      // Any other SQLCODE: not a name-resolution problem.
      return;
  endsl;

end-proc;

Points to note about this example:

  • It checks both SQLCODE and SQLSTATE. -204 and 42704 are confirmed against IBM’s IBM i 7.5 SQL messages listing. If you also need to handle the warning form, IBM lists +204 / 01532. It is deliberately not handled here because which statements produce it was not confirmed against IBM documentation.
  • It keeps SQLCODE 100 (no row) separate from -204 (no object). Mixing them up is a common source of “the table is empty” misdiagnoses.
  • It keeps -551 separate from -204 because the fixes are different: authority versus name resolution.
  • It does not retry. A missing object or a wrong library list will not fix itself.
  • The %subst(sqlerrmc : 1 : %min(sqlerrml : 70)) expression assumes the standard SQLCA fields SQLERRMC and SQLERRML are available in SQLRPGLE (they normally are). STATIC CHECKED only: not compiled. %MIN is listed in IBM’s ILE RPG “What’s New since 7.2” page, so it is a post-7.2 enhancement delivered by PTF; the exact PTF levels were not confirmed against IBM documentation (UNVERIFIED_SOURCE). On systems where %min is not available, use an if test instead.
  • GET DIAGNOSTICS is an alternative to the SQLCA for richer detail. It is not shown here to keep the example small.

The structural fix is usually outside the code. Either qualify the name, compile with an explicit DFTRDBCOL that matches your deployment model, or make sure every job that runs the program starts with the correct library list. Choose one approach per application and apply it consistently. A mix of qualified and unqualified names across programs makes SQL0204 much harder to diagnose.

Common mistakes and troubleshooting

  • Testing in a different naming mode than production. Run SQL Scripts, STRSQL, a JDBC connection, and a compiled program can each use a different naming convention and default schema. A statement that works in one can fail with SQL0204 in another without any change to the object.
  • Assuming the library list applies under SQL naming. Under SQL naming without an explicit default schema, IBM documents the default schema as the authorization ID, not *LIBL. If the message names a user-profile schema, that is why.
  • Running SET SCHEMA in a system-naming job and expecting the library list to still be used. Once a default schema is set explicitly, unqualified names in dynamic SQL are qualified with that schema, not searched for through *LIBL (see cause 2).
  • Ignoring the type in the message. *N means you are looking for a function, procedure, trigger, sequence, or constraint, not a table. Searching SYSTABLES for it will waste time.
  • Quoting names unnecessarily, or forgetting to quote them when needed. Quoted names are case-sensitive. Unquoted names are folded to uppercase.
  • Treating a clean precompile as proof the objects exist. Check the precompile listing for SQL0204 entries (see the UNVERIFIED_SOURCE note under cause 3).
  • Chasing authority when the problem is resolution, or the other way round. Run the statement fully qualified. SQL0204 versus SQL0551 on the qualified form tells you which problem you have, except for functions, where missing EXECUTE authority can itself produce SQL0204.
  • Confusing Db2 for LUW SQL0204N guidance with IBM i. The LUW message ID, wording, and remedies (nicknames, db2 CLI commands) do not apply to Db2 for i.

IBM i release caveats

  • The message text, SQLCODEs, and SQLSTATEs above were confirmed against the IBM i 7.5 SQL messages listing. The name-qualification rules were confirmed against the 7.4 SQL reference, and the identifier case rules against the 7.4 SQL reference. Earlier and later releases are expected to match but were not compared line by line.
  • IBM’s 7.2 qualification page lists more object types (for example masks and permissions) than the 7.1 version. Row and column access control objects only exist on releases that support them.
  • QSYS2.LIBRARY_LIST_INFO, the period qualifier under system naming, and %min in RPG all depend on release, TR, or PTF level. The exact introduction level of each is marked UNVERIFIED_SOURCE above where it appears.

A realistic practical use case

A typical case looks like this. A developer builds an SQLRPGLE program that reads CUSTMAST with an unqualified name, compiled with system naming. It works perfectly from their interactive session, where DEVDTA is in the library list. The program is promoted, and the nightly batch job runs under a job description whose initial library list includes PRODDTA but not DEVDTA. That part is fine, because CUSTMAST exists in PRODDTA.

Months later, a reporting team calls the same program through a JDBC connection that uses SQL naming and no default schema. Every call fails with SQL0204, and the message names a schema matching the connection’s user profile. Nothing is wrong with the table. Db2 is applying SQL-naming defaults, exactly as IBM documents.

The fix is to choose one deployment rule. Either set the JDBC connection’s default schema or library list to match production, or compile the program with an explicit DFTRDBCOL so it no longer depends on the caller’s environment.

FAQ

What is the exact IBM i message text for SQL0204?
Per IBM’s IBM i 7.5 SQL messages listing, it is &1 in &2 type *&3 not found., where &1 is the object, &2 the library or schema, and *&3 the object type. The listed SQLCODEs are +204 and -204, and the SQLSTATEs are 01532 and 42704. Your own job log (DSPJOBLOG, second-level text) shows the values filled in for your failure.

Why does the same SQL work in my session but fail in batch or from a client?
Because Db2 resolves unqualified names using the default schema, and that depends on naming mode (system or SQL), static versus dynamic SQL, compile options such as DFTRDBCOL, and any default schema set on the connection or with SET SCHEMA. Under system naming it is the job’s library list, which often differs between interactive and batch jobs. Under SQL naming it is the authorization ID unless set otherwise.

The table is definitely there. Why does Db2 say it is not found?
Check four things in order. First, the library or schema in the message: is it where the table lives? Second, case: was the table created with a quoted lower- or mixed-case name? Third, SQL name versus system name. Fourth, for functions, EXECUTE authority, which IBM documents as a reason a function is not considered at all. Running the statement fully qualified quickly separates resolution problems from naming problems.

Is SQL0204 the same as SQL0551?
No. SQL0551 (-551, 42501) means Db2 found the object but you lack authority to it. SQL0204 means Db2 did not find a usable object. The exception is functions, where missing EXECUTE privilege causes the function to be skipped during resolution and can therefore surface as SQL0204.

What does type *N mean in the message?
Per IBM’s cause text, *N on a statement other than ALTER TABLE means a function, procedure, trigger, or sequence was not found. On ALTER TABLE, it means a constraint or partition was not found.

Should my program retry after SQL0204?
No. A missing object or a wrong library list or default schema does not resolve itself over time. Log the replacement tokens from SQLERRMC, stop, and fix the configuration or deployment.

Sources Consulted

  • WebFetch: https://www.ibm.com/docs/en/i/7.5?topic=codes-listing-sql-messages (IBM Documentation, IBM i 7.5, “Listing of SQL messages”). Confirms: SQL0204 message text &1 in &2 type *&3 not found.; cause text covering *ALL members, *N for constraint/partition on ALTER TABLE and for function/procedure/trigger/sequence otherwise, the service-program meaning of &1 for functions, the exact-match rule for external and usage names, the EXECUTE-privilege candidate rule, and the instruction to check the job log; recovery text (change the name; node groups need DB2 Multisystem and CRTNODGRP; match EXTERNAL NAME case to the exported name); SQLCODEs +204, -204; SQLSTATEs 01532, 42704. Also confirms SQL0206 (-206, 42703, “Column or global variable &1 not found.”) and SQL0551 (“Not authorized to object &1 in &2 type *&3.”, SQLCODEs +551, -551, SQLSTATEs 01548, 42501).
  • WebFetch: https://www.ibm.com/docs/ssw_ibm_i_74/db2/rbafzqualun1.htm (IBM Db2 for i SQL reference 7.4, “Qualification of unqualified object names”). Confirms: static SQL default schema rules (DFTRDBCOL; otherwise the authorization ID for SQL naming and *LIBL for system naming); dynamic SQL default schema rules (run-time authorization ID for SQL naming, *LIBL for system naming, unless explicitly set); the list of interfaces that set a default schema explicitly (DFTRDBCOL/DYNDFTCOL, RUNSQLSTM DFTRDBCOL, CLI attributes, client connection properties, SET SCHEMA, QSQCHGDC).
  • WebFetch: https://www.ibm.com/docs/en/i/7.5?topic=registers-current-schema (IBM, CURRENT SCHEMA). Confirms: the initial value is the session user’s authorization ID; it qualifies unqualified names in dynamically prepared statements; DFTRDBCOL controls static SQL; it is not used in programs where DYNDFTCOL was specified; it can be changed with SET SCHEMA.
  • WebFetch: https://www.ibm.com/docs/en/i/7.5?topic=statements-set-schema (IBM, SET SCHEMA). Confirms: for system naming, the initial value of CURRENT SCHEMA is *LIBL; the value of CURRENT SCHEMA “is used as the qualifier for some unqualified names”; if DYNDFTCOL is specified in a program, its schema name is used instead; setting CURRENT SCHEMA does not change CURRENT PATH, so function, procedure, and user-defined type resolution may not find objects in the new schema.
  • WebFetch: https://www.ibm.com/docs/ssw_ibm_i_74/db2/rbafzsqlidts.htm (IBM, SQL identifiers). Confirms: ordinary identifiers are converted to uppercase; delimited identifiers are not.
  • WebFetch: https://www.ibm.com/docs/en/i/7.5?topic=views-systables (IBM, SYSTABLES catalog view). Confirms: columns TABLE_NAME, TABLE_SCHEMA, SYSTEM_TABLE_NAME, SYSTEM_TABLE_SCHEMA, TABLE_TYPE and the TABLE_TYPE values A/L/M/P/T/V.
  • WebFetch: https://www.ibm.com/support/pages/use-current-schema-instead-user-default-schema (IBM Support). Shows an embedded SET SCHEMA example making that schema the default for an unqualified table name. Did not address library-list behavior or SQL0204.
  • WebSearch: “IBM i SQL0204 not found SQLSTATE 42704 Db2 for i SQL messages”. Surfaced IBM Support notes (for example https://www.ibm.com/support/pages/node/456485 and https://www.ibm.com/support/pages/node/286895) that are consistent with the IBM cause text on *N and external-function case matching; midrange.com forum threads (https://archive.midrange.com/web400/201510/msg00080.html and related) on SQL versus system naming defaults, the SYSIBM.SYSTABLES versus QSYS2.SYSTABLES confusion, and the period qualifier under system naming since IBM i 7.1 (practitioner source, release marked UNVERIFIED_SOURCE); and Ispirer’s vendor page (https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/db2-as400/sql-error-code) on the *MEM variant for files without members (third-party, marked UNVERIFIED_SOURCE). Also surfaced Db2 for LUW SQL0204N material, which was deliberately excluded.
  • WebFetch: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzala/rzalaccl.htm (IBM, Db2 for i “Listing of SQLSTATE values”, 7.4). Confirms: 42704 “An undefined object or constraint name was detected.” with SQLCODE -204; 01532 “An undefined object name was detected.” with SQLCODE +204.
  • WebFetch: https://www.ibm.com/docs/en/i/7.5?topic=views-sysroutines (IBM, SYSROUTINES catalog view). Confirms: columns ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE (values PROCEDURE, FUNCTION), and SPECIFIC_NAME.
  • WebFetch: https://www.ibm.com/docs/en/i/7.5?topic=services-library-list-info-view (IBM, LIBRARY_LIST_INFO view). Confirms: the view “contains information about the current job’s library list”; columns ORDINAL_POSITION, SCHEMA_NAME, SYSTEM_SCHEMA_NAME, TYPE, IASP_NUMBER, TEXT_DESCRIPTION. Does not state: the introduction release.
  • WebFetch: https://www.ibm.com/docs/ssw_ibm_i_74/db2/rbafzch2nam.htm (IBM Db2 for i SQL reference 7.4, “Naming conventions”). Confirms: under system naming the qualified form is schema-name/identifier, and “the qualified form that uses a period is also accepted”; function names in CREATE, COMMENT, DROP, GRANT, and REVOKE can only be qualified with a slash under system naming.
  • WebFetch: https://www.ibm.com/support/pages/node/1167412 (IBM Support, IBM i Technology Updates). Confirms: with system naming, both slash and dot can be used for qualification, and historically a slash was required. Does not state: the release or PTF level.
  • WebFetch: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzasd/rpgrelv7r2post.htm (IBM, ILE RPG “What’s New since 7.2?”). Confirms: %MAX and %MIN are listed as enhancements made after 7.2. Does not state: the PTF levels.
  • WebFetch: https://www.ibm.com/docs/ssw_ibm_i_74/db2/rbafzcatalog.htm (IBM, Db2 for i catalog views). States that the catalog tables and views “contain information about all tables, parameters, procedures” and are shipped with SELECT privilege to PUBLIC. Does not state: whether QSYS2 catalog rows are filtered by the querying user’s privileges.

Not confirmed against IBM documentation and marked UNVERIFIED_SOURCE in the text: which statements return the +204/01532 warning form; precompiler handling of missing tables at compile time; library-list skipping of unauthorized libraries; *MEM behavior; the release that introduced QSYS2.LIBRARY_LIST_INFO; catalog row-visibility rules; the exact release or PTF that accepted the period qualifier under system naming; the PTF levels for %MIN; the DSPPGM panel showing SQL compile options; and SQL-name versus system-name acceptance per interface.

Internal links

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top