DB2 Views cannot be altered or modified, they have to DROPPED & RE-CREATED.
Considering the above fact, if a DB2 view linked with a DB2 program is dropped and re-created with new or existing set of columns - Go for an impact analysis report on the affected programs using DB2 Administration tools. The below example ( step-by-step ) shows how a program gets invalidated once the corresponding view is dropped.
1) Program APPL0001 access view VIEW0001
Using this method ( On CA-Plaitnum ) we can generate all the programs accessing the view.
2) View is dropped
DROP VIEW C00001.VIEW0001 ;
COMMIT;
3) Program requires a BIND operation to be performed
APPL0001 is invalidated and must undergo a REBIND or BIND to account for all the columns and host variables in the program DBRM. In case we miss to perform the BIND operation, DB2 tries to BIND the program during run time and points out the error if there is a column mismatch/unaccounted for.
4) An SQL code of -303 is generated due to the mismatch as the validity was not checked -
Similar SQL error code would have poped up on the screen while performing the BIND on the affected programs if the columns/host-variables were not accounted for.
Resolution :
Never drop a view if the programs are linked to it. Try creating a new view for your program. Views are logical - they don't hurt in anyway. As for the -303 : View corrections have to be rolledback and the abended program has to be bound again. Perform a PRECOMPILE and BIND in case there are changes in the source code.
No comments:
Post a Comment