Toad World® Forums

SQL0901N error encountered when explaining query in TOAD and SQL Optimizer


#1

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.


#2

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


#3

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


#4

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


#5

The explain was successful when I used the command line window. I used the DB2 Command Window within TOAD thru Tools -> DB2 Command Window.


#6

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


#7

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!


#8

Do you use a previous version of Toad for DB2 where this feature worked or is
this the only version you have tried?


#9

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?


#10

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.


#11

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


#12

Any update on this? Do you guys need more info to look into this? Thanks!


#13

I opened CR 75,040 against the issue you are experiencing.