Question: How do I promote External Procedure using Turnover and get them to automatically *REPLACE on the destination machine?
Answer: Like this!
The problem here, is that SQL Stored Procedures have no equivalent of *REPLACE on the CREATE PROCEDURE statement (they do in IBMi 6.1+). That’s easy – “just add a Drop to line one of the SQL source code” I hear you say… Well its not that easy in Turnover either. Adding a DROP to the SQL, while using IBMi V5R4 issues a Severity 20 warning message and unfortunately a SEV(20) causes the Turnover SQL Promotion to *FAIL. Aaaarrrgh!
But wait… help is at hand. We just need to over-ride the default SQL *EXTPRC command to only fail if the severity breaches 20. How about we set it at 25? Luckily thats easy to do:
1 – Set 25 Default
Setup Turnover to over-ride the default severity on the TRUNSQLSTM command (this is the command that Turnover runs to create stored procedures during promotions).
So, launch Turnover and goto – DEV | ADMIN | Type Codes and select EXTPRC.
Note that you need to add the EXTRADFT(‘ERRLVL(25)’) to this command.
2 – Use this DROP and CREATE in your Procedures
For every SQL Source member that is being promoted using Turnover make sure you DROP first then CREATE :
and Tada!!!! it work just lovely.