Getting DSF error NSAE when CREATing a VIEW

Products

• Datacom
• DATACOM - AD
• Datacom/AD
• Datacom/DB
• Datacom/Server

Issue/Introduction

When trying to CREATE the following VIEW from r14 IVPUPG03 job: 

CREATE VIEW DEMOEMPL.EMP_VACATION                                    
    (EMP_ID,DEPT_ID,VAC_TIME)                                         
    AS SELECT E.EMP_ID,E.DEPT_ID,SUM(VAC_ACCRUED) - SUM(VAC_TAKEN)    
    FROM DEMOEMPL.EMPLOYEE E,DEMOEMPL.BENEFITS B                    
      WHERE E.EMP_ID = B.EMP_ID                                       
      GROUP BY E.DEPT_ID,E.EMP_ID;                                    

The following error is generated:

  ___________SQL ERROR SUMMARY START___________                      
    SQLCODE = -118, SQLSTATE=RNSAE                                     
    MESSAGE = DSF 'NSAE' ERROR OCCURRED DURING SQL DSF PROCESSING      
  ____________SQL ERROR SUMMARY END____________                                                                                        

 

Environment

Release: Datacom

Resolution

DSF error NSAE means that some SQL entities already exist and so the VIEW cannot be CREATed.

- Try to DROP the VIEW via the SQL DROP VIEW clause as follows:

DROP VIEW DEMOEMPL.EMP_VACATION

- Or, try the following DDUPDATE SYSIN, in case the DROP did not resolve the NSAE error.  That may happen if DD (Datadictionary) and DDD (SQL Datadictionary) are not synchronized.

 //SYSIN DD *                        
-USR DATACOM-INSTALL,NEWUSER        
-DEL VIEW,DEMOEMPL-EMP_VACATION(1)  
-DEL VIEW,DEMOEMPL-EMP_VACATION(1)  
/*  

VIEW SQL structures will be deleted from both the DD and the DDD using the DDUPDATE utility.  

Extracted from APAR RO10969.

The Datadictionary occurrence name is to be used in the -DEL transaction, not the SQLNAME.  These are not necessarily the same. Rather than using PROD on the first -DEL and the numeric version number (should always normally be 1) in the second -DEL statement, we suggest you use the absolute numeric version in both -DEL statements as the example above shows. Be sure to delete the occurrence twice. Deleting the occurrence from PROD will move it to HISTory status but the presence of the occurrence in HISTory status will interfere with subsequent SQL activity using the same name. The occurrence must be removed from HISTory as well as PROD.