Unavailable EXPLAIN Data in a DB2 monitor SQLCODE = -104.

Document ID : KB000009357
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

For a DB2 monitor, one receives a "SQLCODE = -104" DB2 Message in DataView, after putting the "E" line command next to a statement on the "DB2 Statements" panel. Upon returning to the "DB2 Statements" panel, the "Explain Data Unavailable" message is displayed.

Instructions:

After putting the "E" line command next to a statement on the "DB2 Statements" panel, you have received the following DB2 Message:

 	DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "<END-OF-STATEMENT>". SOME 	
 	SYMBOLS THAT MIGHT BE LEGAL ARE: MICROSECONDS MICROSECOND SECONDS 	
 	SECOND MINUTES MIN 	
 	DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE 	
 	DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR 	
 	DSNT416I SQLERRD = 3 0 0 -1 3501 502 SQL DIAGNOSTIC INFORMATION 	
 	DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000' X'FFFFFFFF' 	
 	X'00000DAD' X'000001F6' SQL DIAGNOSTIC INFORMATION 

 

First, please verify that the following TUNNSP00 parameters are set to YES in order to get explain data for SQL statements:

 	DB2EXPL, DB2CTSQL, DB2SPSQL, and DB2SPDYN.

 

Also make sure you have run the two jobs TNESQLJ and TNBINDJ for the subsystem you are accessing.

If you need to modify any of the parameters or run the two jobs. recycle CA MAT before rerunning the DB2 monitor.

For DB2 v9 and before, there is a 4K character limit for SQL statements, and the -104 SQLCODE results for truncated SQL statements. Note that for DB2 v10 the SQL statement length has been increased to 2M, so if you are at DB2 v10 then this is most likely not the cause for your -104 SQLCODE.

SOLUTION: Please verify that you have applied RO44055. If not, please apply, recycle CA MAT and retry.

If you are still receiving the -104 SQLCODE, please open an issue with CA Support. We will require the monitor receiving the -104 SQLCODE (it is a VB 4096 dataset so please TERSE it and FTP it BINARY FB 1024 6144). We will also need your MATUNER log, and a SMPE LISTMODs on your CA MAT CSI so we can see your maintenance level.