# SQL0901N error encountered when explaining query in TOAD and SQL Optimizer **URL:** \ **Category:** Toad for IBM DB2\ **Created:** [June 10, 2010, 3:39pm UTC](https://forums.toadworld.com/t/sql0901n-error-encountered-when-explaining-query-in-toad-and-sql-optimizer/7777 "2010-06-10T15:39:19Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![im.sharon\_890](https://avatars.discourse-cdn.com/v4/letter/i/ec9cab/32.png) [@im.sharon\_890](https://forums.toadworld.com/u/im.sharon_890)\ **Post date:** [June 10, 2010, 3:39pm UTC](https://forums.toadworld.com/t/sql0901n-error-encountered-when-explaining-query-in-toad-and-sql-optimizer/7777/1 "2010-06-10T15:39:19Z")
I am trying to do an explain on a simple query: select audt\_trl\_id from db2od.audt\_trl in both TOAD and SQL Optimizer, but both failed with SQL0901N as listed below at the bottom of this note. I am able to explain the query natively in DB2 however without any error with db2 command: db2expln -d PENODFT0 -statement “select audt\_trl\_id from db2od.audt\_trl” -terminal and got the explain result back. I also tried some other queries and same error was encountered. Any thoughts? FYI… The database is on 9.7.1. THANKS! **From TOAD 4.7.0.395 beta:** Plan Cost [IBM][CLI Driver][DB2/AIX64] SQL0901N The SQL statement failed because of a non-severe system error. Subsequent SQL statements can be processed. (Reason “Parameter marker in literal replaced statement”.) SQLSTATE=58004 Possible causes of this error are: - You are not pointing to the schema where the objects referenced by the SQL are located. Therefore, SQL Optimizer cannot locate the objects. - You are using DB2 LUW SQL dialect code or more than one SQL statement. SQL Optimizer only processes a single SELECT/INSERT/DELETE/UPDATE. For multiple SQL statements or DB2 LUW SQL dialect code, first use the SQL Scanner module to extract the SQL statements. - If the SQL statement uses a temporary table, you must create the temporary table using the User-Defined Temp Table function. **ERROR [58004] [IBM][DB2/AIX64] SQL0901N The SQL statement failed because of a non-severe system error. Subsequent SQL statements can be processed. (Reason “Parameter marker in literal replaced statement”.) SQLSTATE=58004** **From SQL Optimizer 4.0.567 beta:** he Parser did not identify the text as a valid SQL statement. ---
**Author:** ![KenRyan](https://avatars.discourse-cdn.com/v4/letter/k/3da27b/32.png) [@KenRyan](https://forums.toadworld.com/u/KenRyan)\ **Post date:** [June 10, 2010, 5:21pm UTC](https://forums.toadworld.com/t/sql0901n-error-encountered-when-explaining-query-in-toad-and-sql-optimizer/7777/2 "2010-06-10T17:21:00Z")
im.sharon\_890, I am unable to recreate this issue. Do you have this Explain within Toad problem with all your DB2 instances or just this particular instance? What happens if you Explain a statement like “select \* from syscat.packages”? Ken Ryan Toad for DB2 Developer Quest Software ---
**Author:** ![im.sharon\_890](https://avatars.discourse-cdn.com/v4/letter/i/ec9cab/32.png) [@im.sharon\_890](https://forums.toadworld.com/u/im.sharon_890)\ **Post date:** [June 10, 2010, 6:15pm UTC](https://forums.toadworld.com/t/sql0901n-error-encountered-when-explaining-query-in-toad-and-sql-optimizer/7777/3 "2010-06-10T18:15:44Z")
select \* from syscat.packages as well but am getting the same SQL0901N error message. It looks like all the db2 9.7 instances are affected. I tried the query ---
**Author:** ![KenRyan](https://avatars.discourse-cdn.com/v4/letter/k/3da27b/32.png) [@KenRyan](https://forums.toadworld.com/u/KenRyan)\ **Post date:** [June 11, 2010, 9:41am UTC](https://forums.toadworld.com/t/sql0901n-error-encountered-when-explaining-query-in-toad-and-sql-optimizer/7777/4 "2010-06-11T09:41:38Z")
im.sharon\_890, What happens when you execute the explain using the DB2 CLP… db2 explain all select \* from syscat.packages Ken Ryan Toad for DB2 Developer Quest Software ---
**Author:** ![im.sharon\_890](https://avatars.discourse-cdn.com/v4/letter/i/ec9cab/32.png) [@im.sharon\_890](https://forums.toadworld.com/u/im.sharon_890)\ **Post date:** [June 11, 2010, 1:32pm UTC](https://forums.toadworld.com/t/sql0901n-error-encountered-when-explaining-query-in-toad-and-sql-optimizer/7777/5 "2010-06-11T13:32:34Z")
The explain was successful when I used the command line window. I used the DB2 Command Window within TOAD thru Tools -\> DB2 Command Window. ---
**Author:** ![KenRyan](https://avatars.discourse-cdn.com/v4/letter/k/3da27b/32.png) [@KenRyan](https://forums.toadworld.com/u/KenRyan)\ **Post date:** [June 11, 2010, 1:32pm UTC](https://forums.toadworld.com/t/sql0901n-error-encountered-when-explaining-query-in-toad-and-sql-optimizer/7777/6 "2010-06-11T13:32:34Z")
im.sharon\_890, Sorry, I meant to ask is there a difference when using “db2 explain all” versus the db2expln command? Do you have another db2 instance you can try th explain from other than from within the Toad for DB2 launched command window? Ken Ryan Toad for DB2 Developer Quest Software ---
**Author:** ![im.sharon\_890](https://avatars.discourse-cdn.com/v4/letter/i/ec9cab/32.png) [@im.sharon\_890](https://forums.toadworld.com/u/im.sharon_890)\ **Post date:** [June 14, 2010, 1:50pm UTC](https://forums.toadworld.com/t/sql0901n-error-encountered-when-explaining-query-in-toad-and-sql-optimizer/7777/7 "2010-06-14T13:50:10Z")
The explain result looks the same. I don’t see any difference when explaining the query thru db2expln or explaining it thru db2 explain all. I tried “db2 explain all” from another instance other than TOAD, and it worked. Is there any other info that I can provide? Thanks! ---
**Author:** ![randall.haehnel\_718](https://avatars.discourse-cdn.com/v4/letter/r/ecb155/32.png) [@randall.haehnel\_718](https://forums.toadworld.com/u/randall.haehnel_718)\ **Post date:** [June 15, 2010, 9:14am UTC](https://forums.toadworld.com/t/sql0901n-error-encountered-when-explaining-query-in-toad-and-sql-optimizer/7777/8 "2010-06-15T09:14:49Z")
Do you use a previous version of Toad for DB2 where this feature worked or is this the only version you have tried? ---
**Author:** ![im.sharon\_890](https://avatars.discourse-cdn.com/v4/letter/i/ec9cab/32.png) [@im.sharon\_890](https://forums.toadworld.com/u/im.sharon_890)\ **Post date:** [June 15, 2010, 10:33am UTC](https://forums.toadworld.com/t/sql0901n-error-encountered-when-explaining-query-in-toad-and-sql-optimizer/7777/9 "2010-06-15T10:33:51Z")
It doesn’t work under 4.6 as well. I don’t remember if it works on 4.5. Is explaining queries against 9.7 db not supported in either TOAD 4.7 or SQL Optimizer 4.0 then? ---
**Author:** ![randall.haehnel\_718](https://avatars.discourse-cdn.com/v4/letter/r/ecb155/32.png) [@randall.haehnel\_718](https://forums.toadworld.com/u/randall.haehnel_718)\ **Post date:** [June 15, 2010, 10:33am UTC](https://forums.toadworld.com/t/sql0901n-error-encountered-when-explaining-query-in-toad-and-sql-optimizer/7777/10 "2010-06-15T10:33:51Z")
Interesting. I tested against 4.6, 4.7 beta and my dev build and they all work. Is there anything “special” about your 9.7 instances that aren’t also on your non 9.7 instances that may help us reproduce this issue? Explain should work against all supported versions of DB2, including 9.7. ---
**Author:** ![im.sharon\_890](https://avatars.discourse-cdn.com/v4/letter/i/ec9cab/32.png) [@im.sharon\_890](https://forums.toadworld.com/u/im.sharon_890)\ **Post date:** [June 15, 2010, 11:51am UTC](https://forums.toadworld.com/t/sql0901n-error-encountered-when-explaining-query-in-toad-and-sql-optimizer/7777/11 "2010-06-15T11:51:41Z")
I don’t think there’s anything special about our 9.7 instances. We have some on 9.7.1 and some on 9.7.2. Explaining a query against any of them is failing. Here’s some info on one of our 9.7 instance: db2level: DB21085I Instance “d2imfuq1” uses “64” bits and DB2 code release “SQL09071” with level identifier “08020107”. Informational tokens are “DB2 v9.7.0.1”, “s091114”, “IP23027”, and Fix Pack “1”. Product is installed at “/usr/opt/db2\_097\_FP1”. db2set: ua592:i274785 $ db2set -all [i] DB2\_CAPTURE\_LOCKTIMEOUT=ON [i] DB2COMM=tcpip [g] DB2SYSTEM=ua592 [g] DB2INSTDEF=d2imfuq1 dbm cfg: ua592:i274785 $ db2 get dbm cfg Database Manager Configuration Node type = Enterprise Server Edition with local and remote clients Database manager configuration release level = 0x0d00 CPU speed (millisec/instruction) (CPUSPEED) = 4.920253e-07 Communications bandwidth (MB/sec) (COMM\_BANDWIDTH) = 1.000000e+02 Max number of concurrently active databases (NUMDB) = 8 Federated Database System Support (FEDERATED) = NO Transaction processor monitor name (TP\_MON\_NAME) = Default charge-back account (DFT\_ACCOUNT\_STR) = Java Development Kit installation path (JDK\_PATH) = /db2/d2imfuq1/sqllib/java/jdk64 Diagnostic error capture level (DIAGLEVEL) = 3 Notify Level (NOTIFYLEVEL) = 3 Diagnostic data directory path (DIAGPATH) = /db2/d2imfuq1/sqllib/db2dump Size of rotating db2diag & notify logs (MB) (DIAGSIZE) = 0 Default database monitor switches Buffer pool (DFT\_MON\_BUFPOOL) = ON Lock (DFT\_MON\_LOCK) = ON Sort (DFT\_MON\_SORT) = ON Statement (DFT\_MON\_STMT) = ON Table (DFT\_MON\_TABLE) = ON Timestamp (DFT\_MON\_TIMESTAMP) = ON Unit of work (DFT\_MON\_UOW) = ON Monitor health of instance and databases (HEALTH\_MON) = OFF SYSADM group name (SYSADM\_GROUP) = D2IMFUQ1 SYSCTRL group name (SYSCTRL\_GROUP) = GFRDBA SYSMAINT group name (SYSMAINT\_GROUP) = SYSMON group name (SYSMON\_GROUP) = Client Userid-Password Plugin (CLNT\_PW\_PLUGIN) = sys-auth Client Kerberos Plugin (CLNT\_KRB\_PLUGIN) = Group Plugin (GROUP\_PLUGIN) = sys-auth GSS Plugin for Local Authorization (LOCAL\_GSSPLUGIN) = Server Plugin Mode (SRV\_PLUGIN\_MODE) = UNFENCED Server List of GSS Plugins (SRVCON\_GSSPLUGIN\_LIST) = Server Userid-Password Plugin (SRVCON\_PW\_PLUGIN) = sys-auth Server Connection Authentication (SRVCON\_AUTH) = NOT\_SPECIFIED Cluster manager (CLUSTER\_MGR) = Database manager authentication (AUTHENTICATION) = SERVER\_ENCRYPT Alternate authentication (ALTERNATE\_AUTH\_ENC) = NOT\_SPECIFIED Cataloging allowed without authority (CATALOG\_NOAUTH) = NO Trust all clients (TRUST\_ALLCLNTS) = YES Trusted client authentication (TRUST\_CLNTAUTH) = CLIENT Bypass federated authentication (FED\_NOAUTH) = NO Default database path (DFTDBPATH) = /db2/d2imfuq1 Database monitor heap size (4KB) (MON\_HEAP\_SZ) = AUTOMATIC(360) Java Virtual Machine heap size (4KB) (JAVA\_HEAP\_SZ) = 2048 Audit buffer size (4KB) (AUDIT\_BUF\_SZ) = 0 Size of instance shared memory (4KB) (INSTANCE\_MEMORY) = AUTOMATIC(7212009) Backup buffer default size (4KB) (BACKBUFSZ) = 1024 Restore buffer default size (4KB) (RESTBUFSZ) = 1024 Agent stack size (AGENT\_STACK\_SZ) = 1024 Sort heap threshold (4KB) (SHEAPTHRES) = 0 Directory cache support (DIR\_CACHE) = YES Application support layer heap size (4KB) (ASLHEAPSZ) = 15 Max requester I/O block size (bytes) (RQRIOBLK) = 32767 Query heap size (4KB) (QUERY\_HEAP\_SZ) = 1000 Workload impact by throttled utilities(UTIL\_IMPACT\_LIM) = 10 Priority of agents (AGENTPRI) = SYSTEM Agent pool size (NUM\_POOLAGENTS) = AUTOMATIC(200) Initial number of agents in pool (NUM\_INITAGENTS) = 0 Max number of coordinating agents (MAX\_COORDAGENTS) = AUTOMATIC(400) Max number of client connections (MAX\_CONNECTIONS) = AUTOMATIC(MAX\_COORDAGENTS) Keep fenced process (KEEPFENCED) = YES Number of pooled fenced processes (FENCED\_POOL) = AUTOMATIC(MAX\_COORDAGENTS) Initial number of fenced processes (NUM\_INITFENCED) = 0 Index re-creation time and redo index build (INDEXREC) = RESTART Transaction manager database name (TM\_DATABASE) = 1ST\_CONN Transaction resync interval (sec) (RESYNC\_INTERVAL) = 180 SPM name (SPM\_NAME) = SPM log size (SPM\_LOG\_FILE\_SZ) = 256 SPM resync agent limit (SPM\_MAX\_RESYNC) = 20 SPM log path (SPM\_LOG\_PATH) = TCP/IP Service name (SVCENAME) = d2imfuq1c Discovery mode (DISCOVER) = SEARCH Discover server instance (DISCOVER\_INST) = ENABLE SSL server keydb file (SSL\_SVR\_KEYDB) = SSL server stash file (SSL\_SVR\_STASH) = SSL server certificate label (SSL\_SVR\_LABEL) = SSL service name (SSL\_SVCENAME) = SSL cipher specs (SSL\_CIPHERSPECS) = SSL versions (SSL\_VERSIONS) = SSL client keydb file (SSL\_CLNT\_KEYDB) = SSL client stash file (SSL\_CLNT\_STASH) = Maximum query degree of parallelism (MAX\_QUERYDEGREE) = ANY Enable intra-partition parallelism (INTRA\_PARALLEL) = NO Maximum Asynchronous TQs per query (FEDERATED\_ASYNC) = 0 No. of int. communication buffers(4KB)(FCM\_NUM\_BUFFERS) = AUTOMATIC(4096) No. of int. communication channels (FCM\_NUM\_CHANNELS) = AUTOMATIC(2048) Node connection elapse time (sec) (CONN\_ELAPSE) = 10 Max number of node connection retries (MAX\_CONNRETRIES) = 5 Max time difference between nodes (min) (MAX\_TIME\_DIFF) = 60 db2start/db2stop timeout (min) (START\_STOP\_TIME) = 10 db cfg: ua592:i274785 $ db2 get db cfg Database Configuration for Database Database configuration release level = 0x0d00 Database release level = 0x0d00 Database territory = US Database code page = 819 Database code set = ISO8859-1 Database country/region code = 1 Database collating sequence = UNIQUE Alternate collating sequence (ALT\_COLLATE) = Number compatibility = OFF Varchar2 compatibility = OFF Date compatibility = OFF Database page size = 4096 Dynamic SQL Query management (DYN\_QUERY\_MGMT) = DISABLE Statement concentrator (STMT\_CONC) = LITERALS Discovery support for this database (DISCOVER\_DB) = ENABLE Restrict access = NO Default query optimization class (DFT\_QUERYOPT) = 5 Degree of parallelism (DFT\_DEGREE) = 1 Continue upon arithmetic exceptions (DFT\_SQLMATHWARN) = NO Default refresh age (DFT\_REFRESH\_AGE) = 0 Default maintained table types for opt (DFT\_MTTB\_TYPES) = SYSTEM Number of frequent values retained (NUM\_FREQVALUES) = 10 Number of quantiles retained (NUM\_QUANTILES) = 20 Decimal floating point rounding mode (DECFLT\_ROUNDING) = ROUND\_HALF\_EVEN Backup pending = NO All committed transactions have been written to disk = NO Rollforward pending = NO Restore pending = NO Multi-page file allocation enabled = YES Log retain for recovery status = NO User exit for logging status = YES Self tuning memory (SELF\_TUNING\_MEM) = ON Size of database shared memory (4KB) (DATABASE\_MEMORY) = AUTOMATIC(37780) Database memory threshold (DB\_MEM\_THRESH) = 10 Max storage for lock list (4KB) (LOCKLIST) = 1000 Percent. of lock lists per application (MAXLOCKS) = 60 Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(320) Sort heap thres for shared sorts (4KB) (SHEAPTHRES\_SHR) = AUTOMATIC(389) Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(77) Database heap (4KB) (DBHEAP) = AUTOMATIC(2342) Catalog cache size (4KB) (CATALOGCACHE\_SZ) = 260 Log buffer size (4KB) (LOGBUFSZ) = 24 Utilities heap size (4KB) (UTIL\_HEAP\_SZ) = 5000 Buffer pool size (pages) (BUFFPAGE) = 1000 SQL statement heap (4KB) (STMTHEAP) = 4096 Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(256) Application Memory Size (4KB) (APPL\_MEMORY) = AUTOMATIC(40000) Statistics heap size (4KB) (STAT\_HEAP\_SZ) = AUTOMATIC(4384) Interval for checking deadlock (ms) (DLCHKTIME) = 10000 Lock timeout (sec) (LOCKTIMEOUT) = 60 Changed pages threshold (CHNGPGS\_THRESH) = 80 Number of asynchronous page cleaners (NUM\_IOCLEANERS) = 5 Number of I/O servers (NUM\_IOSERVERS) = 5 Index sort flag (INDEXSORT) = YES Sequential detect flag (SEQDETECT) = YES Default prefetch size (pages) (DFT\_PREFETCH\_SZ) = AUTOMATIC Track modified pages (TRACKMOD) = ON Default number of containers = 1 Default tablespace extentsize (pages) (DFT\_EXTENT\_SZ) = 32 Max number of active applications (MAXAPPLS) = AUTOMATIC(40) Average number of active applications (AVG\_APPLS) = AUTOMATIC(1) Max DB files open per application (MAXFILOP) = 61440 Log file size (4KB) (LOGFILSIZ) = 4000 Number of primary log files (LOGPRIMARY) = 13 Number of secondary log files (LOGSECOND) = 20 Changed path to log files (NEWLOGPATH) = Path to log files = /db2/d2imfuq1/d2imfuq1/NODE0000/SQL00001/SQLOGDIR/ Overflow log path (OVERFLOWLOGPATH) = Mirror log path (MIRRORLOGPATH) = First active log file = S0000970.LOG Block log on disk full (BLK\_LOG\_DSK\_FUL) = NO Block non logged operations (BLOCKNONLOGGED) = NO Percent max primary log space by transaction (MAX\_LOG) = 0 Num. of active log files for 1 active UOW(NUM\_LOG\_SPAN) = 0 Group commit count (MINCOMMIT) = 1 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520 Log retain for recovery enabled (LOGRETAIN) = OFF User exit for logging enabled (USEREXIT) = OFF HADR database role = STANDARD HADR local host name (HADR\_LOCAL\_HOST) = HADR local service name (HADR\_LOCAL\_SVC) = HADR remote host name (HADR\_REMOTE\_HOST) = HADR remote service name (HADR\_REMOTE\_SVC) = HADR instance name of remote server (HADR\_REMOTE\_INST) = HADR timeout value (HADR\_TIMEOUT) = 120 HADR log write synchronization mode (HADR\_SYNCMODE) = NEARSYNC HADR peer window duration (seconds) (HADR\_PEER\_WINDOW) = 0 First log archive method (LOGARCHMETH1) = TSM Options for logarchmeth1 (LOGARCHOPT1) = Second log archive method (LOGARCHMETH2) = OFF Options for logarchmeth2 (LOGARCHOPT2) = Failover log archive path (FAILARCHPATH) = Number of log archive retries on error (NUMARCHRETRY) = 1000 Log archive retry Delay (secs) (ARCHRETRYDELAY) = 300 Vendor options (VENDOROPT) = Auto restart enabled (AUTORESTART) = ON Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART) Log pages during index build (LOGINDEXBUILD) = OFF Default number of loadrec sessions (DFT\_LOADREC\_SES) = 1 Number of database backups to retain (NUM\_DB\_BACKUPS) = 45 Recovery history retention (days) (REC\_HIS\_RETENTN) = 45 Auto deletion of recovery objects (AUTO\_DEL\_REC\_OBJ) = OFF TSM management class (TSM\_MGMTCLASS) = TSM node name (TSM\_NODENAME) = TSM owner (TSM\_OWNER) = TSM password (TSM\_PASSWORD) = Automatic maintenance (AUTO\_MAINT) = OFF Automatic database backup (AUTO\_DB\_BACKUP) = OFF Automatic table maintenance (AUTO\_TBL\_MAINT) = OFF Automatic runstats (AUTO\_RUNSTATS) = OFF Automatic statement statistics (AUTO\_STMT\_STATS) = OFF Automatic statistics profiling (AUTO\_STATS\_PROF) = OFF Automatic profile updates (AUTO\_PROF\_UPD) = OFF Automatic reorganization (AUTO\_REORG) = OFF Auto-Revalidation &nbs ---
**Author:** ![im.sharon\_890](https://avatars.discourse-cdn.com/v4/letter/i/ec9cab/32.png) [@im.sharon\_890](https://forums.toadworld.com/u/im.sharon_890)\ **Post date:** [June 17, 2010, 12:30pm UTC](https://forums.toadworld.com/t/sql0901n-error-encountered-when-explaining-query-in-toad-and-sql-optimizer/7777/12 "2010-06-17T12:30:43Z")
Any update on this? Do you guys need more info to look into this? Thanks! ---
**Author:** ![randall.haehnel\_718](https://avatars.discourse-cdn.com/v4/letter/r/ecb155/32.png) [@randall.haehnel\_718](https://forums.toadworld.com/u/randall.haehnel_718)\ **Post date:** [June 18, 2010, 9:32am UTC](https://forums.toadworld.com/t/sql0901n-error-encountered-when-explaining-query-in-toad-and-sql-optimizer/7777/13 "2010-06-18T09:32:27Z")
I opened CR 75,040 against the issue you are experiencing.