SQLCODE -904 REASON 00D70008 in DBRM [email protected] running a Explain in Plan Analyzer

Products

• Bind Analyzer for DB2 for z/OS
• SQL-Ease for DB2 for z/OS
• SYSVIEW Performance Management Option for DB2 for z/OS
• Plan Analyzer for DB2 for z/OS
• Subsystem Analyzer for DB2 for z/OS

Issue/Introduction

Running an Explain got the following bad SQLCODE.
 
DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN
UNAVAILABLE RESOURCE. REASON 00D70008, TYPE OF RESOURCE
00000220, AND RESOURCE NAME BX998D.DSNDBC.DSNDB04.DSNR14CD.I0-
001.A001
DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOD2 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -205 13172746 0 13820889 -1041948669
12714048 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF33' X'00C9000A' X'00000000'
X'00D2E3D9' X'C1E52003' X'00C20040' SQL DIAGNOSTIC
INFORMATION
DSNT417I SQLWARN0-5 = , , , , , SQL WARNINGS
DSNT417I SQLWARN6-A = , , , , SQL WARNINGS
 
The meaning of the message was that DSNR14CD table space could not be created but there was not any more messages in the output to figure out the cause. Plan Analyzer automatically creates the DB2 objects that are needed for the Explain if they have not been created previously.     

Environment

Release:
Component: PPADB2

Resolution

Problem was how table space were created by default depending on parameters in DSNZPARM.
 
In DSSZPARM there was: IMPDSDEF = NO and DSSIZE = 64G
 
DSSIZE = 64G requires an extended format and extended addressability attributes that are defined through DFSMS, this data set could not be allocated in EF-EA format so it failed with the SQLCODE = -904 and REASON 00D70008.
 
Changed to DSSIZE = 1G and now the Explain worked fine.
 
In HLQ.CDBAPARM(PPA) member there was DFLT_DB (DSNDB04) and DFLT_TS ().        
 
Another option should be to define data sets needed for Explain through DFSMS, on this way Plan Analyzer will be able to create the DB2 objects with DSSIZE 64G.