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.
IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.
Using EXTPRC with iSeries Turnover at V5R4
IBM i ACS 5250 EMULATOR FONT – and other ridiculous mumbo jumbo
IBM i SQL statement to convert or compare hundred year date format
How to compare ‘100 Year Date’ to a Timestamp – aka – Weird AS400 iSeries Date formats
Modernize AS400 iSeries Queries – Convert to IBM i SQL
Use IBM i SQL to validate email addresses in Customer Master File