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____________
Release: Datacom
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.