Unable to see ddl or grants in scripts table in toad when connecting to db2 12.1 database

Toad for IBM DB2
1

users are getting below error when using the scripts tab in toad for viewing the ddl of a table in toad.
-- Permissions are absent in the script due to errors.
-- See exception log for details.

Entries from exception log:

New exception: 6/23/2026 9:24:55 AM

IBM.Data.DB2.DB2Exception: ERROR [42815] [IBM][DB2/LINUXX8664] SQL0171N The statement was not processed because the data type, length or value of the argument for the parameter in position "1" of routine "SYSIBM.HEX" is incorrect. Parameter name: "".

Stack trace:

at IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior behavior, String method, DB2CursorType reqCursorType, Boolean abortOnOptValueChg, Boolean skipDeleted, Boolean isResultSet, Int32 maxRows, Boolean skipInitialValidation)
at IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior behavior, String method)
at IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior)
at IBM.Data.DB2.DB2Command.ExecuteReader()
at Quest.Compare.Schema.DB2.DBModel.CNS2Connection.ExecuteReader(String& sQuery, SortedList slParameters, Int32 nTimeout, IDbCommand& iCommand)

Outer exception:

System.ApplicationException: ERROR [42815] [IBM][DB2/LINUXX8664] SQL0171N The statement was not processed because the data type, length or value of the argument for the parameter in position "1" of routine "SYSIBM.HEX" is incorrect. Parameter name: "".

Query:
SELECT
CASE t.type
WHEN 'T' THEN CASE WHEN EXISTS ( SELECT 1 FROM syscat.tabdep td WHERE td.tabschema = t.tabschema AND td.tabname = t.tabname AND td.btype = 'S' )
THEN 'Staging' ELSE 'Base' END WHEN 'U' THEN 'Typed' WHEN 'G' THEN 'GlobalTemporary'
ELSE 'Query' END AS db2tabletype,
t.*,
sp.secpolicyname,
case hex(substr(st.packed_desc, 294, 1)) when '31' then 'Y' else 'N' end as NOT_LOGGED,
fo.DATAFILE, et.BADFILE, et.BOOLSTYLE, et.DATEDELIM, et.DATESTYLE,
et.DECIMALDELIM, et.ENCODING, et.ETID, et.FIELDDELIM, et.FORMAT,
et.ISCOMPRESS, et.ISCRINSTRING, et.ISCTRLCHARS, et.ISESCAPE, et.ISFILLRECORD,
et.ISIGNOREZERO, et.ISINCLUDEHEADER, et.ISINCLUDEZEROSECONDS, et.ISTIMEROUNDNANOS,
et.ISTRUNCSTRING, et.LOGFILE, et.LOGPATH, et.MAXERRORS, et.MAXROWS,
SYSIBM.RTRIM(et.NULLVALUE) as NULLVALUE, et.PACKED_DESC, et.QUOTEDVALUE,
SYSIBM.RTRIM(et.RECDELIM) as RECDELIM, et.RECORDLENGTH, et.REMOTESOURCE, et.REQUIREQUOTES,
et.SKIPROWS, et.SOCKETBUFSIZE, et.TIMEDELIM, et.TIMESTYLE, et.Y2BASE,
hi.super_schema, hi.super_name, csh.tabschema AS hierarchy_schema, csh.tabname AS hierarchy_name
FROM syscat.tables t
LEFT OUTER JOIN syscat.hierarchies hi ON hi.sub_schema = t.tabschema AND hi.sub_name = t.tabname AND hi.metatype = t.type
LEFT OUTER JOIN syscat.columns csh ON csh.source_tabschema = t.tabschema AND csh.source_tabname = t.tabname AND csh.hidden = 'S'
AND csh.colname = 'TYPE_ID'
LEFT OUTER JOIN syscat.securitypolicies sp ON sp.secpolicyid = t.secpolicyid
LEFT OUTER JOIN sysibm.systables st on st.name = t.tabname and st.creator = t.tabschema
LEFT OUTER JOIN sysibm.sysexttab et on t.TABLEID = et.ETID
LEFT OUTER JOIN sysibm.sysexttabfileobj fo on t.TABLEID = fo.ETID
WHERE t.type IN ('T', 'U', 'S', 'G')
AND (t.tabschema = 'test' AND t.tabname = 'FT_CLT_ACCT_REVN_MTH')
FOR READ ONLY WITH UR

Stack trace:

at Quest.Compare.Schema.DB2.DBModel.CNS2Connection.ExecuteReader(String& sQuery, SortedList slParameters, Int32 nTimeout, IDbCommand& iCommand)
at Quest.Compare.Schema.DB2.DBModel.CNS2Loader.DirectExtractFromDB(CNS2Database oDb, CNSPLProgress plProgress, LimitationsPack packLimitations)

Tried the same query from putty and got same error.

Database was recently restored from db2 warehouse 11.5 to 12.1 db2 database on db2u operator. After the migration users are facing this error.

2

Hello,

Please open a support case at support.quest.com if one has not already been created. Attach the complete Toad exception log and provide:

  • Exact Toad for IBM DB2 version and build

  • Db2 client/provider version

  • Db2 server version

  • Full repro if possible with screenshots/recordings

  • Migration procedure used from Db2 Warehouse 11.5 to Db2 12.1/db2u

  • Whether the issue affects all tables or only tables migrated from 11.5

  • Whether tables created directly on the Db2 12.1 database are affected

  • Whether the required Db2 post-upgrade catalog update was completed

Please run the following read-only queries individually against the affected Db2 12.1 database and attach their complete results or errors.

1. Db2 server version

SQL

SELECT
    INST_NAME,
    RELEASE_NUM,
    SERVICE_LEVEL,
    FIXPACK_NUM
FROM SYSIBMADM.ENV_INST_INFO;

2. PACKED_DESC catalog column definition

SQL

SELECT
    TYPENAME,
    LENGTH,
    CODEPAGE
FROM SYSCAT.COLUMNS
WHERE TABSCHEMA = 'SYSIBM'
  AND TABNAME = 'SYSTABLES'
  AND COLNAME = 'PACKED_DESC';

3. Confirm the affected table’s catalog entry

SQL

SELECT
    CREATOR,
    NAME,
    LENGTH(PACKED_DESC) AS PACKED_DESC_LENGTH
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'test'
  AND NAME = 'FT_CLT_ACCT_REVN_MTH';

If this query returns no rows, please run:

SQL

SELECT
    CREATOR,
    NAME
FROM SYSIBM.SYSTABLES
WHERE UPPER(NAME) = 'FT_CLT_ACCT_REVN_MTH';

Then use the returned CREATOR value in the remaining queries.

4. Isolated expression currently used by Toad

SQL

SELECT
    HEX(SUBSTR(PACKED_DESC, 294, 1)) AS NOT_LOGGED_BYTE
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'test'
  AND NAME = 'FT_CLT_ACCT_REVN_MTH';

5. Diagnostic expression with an explicit binary cast

SQL

SELECT
    HEX(
        CAST(
            SUBSTR(PACKED_DESC, 294, 1)
            AS VARBINARY(1)
        )
    ) AS NOT_LOGGED_BYTE
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'test'
  AND NAME = 'FT_CLT_ACCT_REVN_MTH';

The final query is diagnostic only; it does not modify the database. Its result will help determine whether an explicit conversion of the internal Db2 BLOB descriptor avoids SQL0171N

3

HI Aleksandar,

case # 03190751is raised.

4

Hi Anjali,

Thank you for providing the additional information and IBM’s analysis

I've reproduced the issue and confirmed that it is caused by the BigSQL/DASHDB rewrite of SUBSTR to SUBSTRING, which results in an unsupported BLOB length being passed to HEX

The issue has been reported and is being tracked as TDB-4642. Please keep the official Quest Support case open so that Support can communicate status and future updates