July 25

0 comments

Using EXTPRC with iSeries Turnover at V5R4

By NickLitten

July 25, 2015

Turnover, extprc, sql

EXTPRC – External Procedures

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!

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 that’s 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.

Using extprc with iseries turnover at v5r4 1

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 :

Extprc drop and create sql for turnover
extprc drop and create sql for turnover

and Tada!!!! it work just lovely.

/insert grin!

Want to use a &LIBRARY name as a variable?

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

>