.st0{fill:#FFFFFF;}

Don’t hardcode library names in your TURNOVER SQL source #youbigsilly 

 July 29, 2019

By  NickLitten

One of the mumbling, drooling, gray haired programmers on my team asked me to peer review his code this morning. This was for a simple SQL table to be created and promoted through multiple environments using TURNOVER.

The code looked something like this:

CREATE TABLE MYLIB/TESTSQL (                          
   TSTFLD CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
   TSTNUM NUMERIC(11, 0) NOT NULL DEFAULT 0 )    
   RCDFMT RECTEST ; LABEL ON TABLE MYLIB/TESTSQL                          
   IS '*TABLE: TESTING TURNOVER PROMOTIONS ' ; 

The idea is the it would be promoted to QA (which would create in library ITQLIB) and then go to Production (in library PRODLIB).

The problem my esteemed programming chum was facing was – trying to figure out how to qualify the different library names for each promotion level?

This is normally very easy using TURNOVER, it simply switches in the library names of each promotion level as the forms are run. But for SQL type commands, which create from source each time they run, we need to use a simple replacement variable.

&LIBRARY – The Turnover Replacement Variable

I prefer to select the schema name using the replacement variable like this:

SET SCHEMA "&LIBRARY" ;                                
CREATE TABLE TESTSQL (                                 
TSTFLD CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
TSTNUM NUMERIC(11, 0) NOT NULL DEFAULT 0 )
RCDFMT RECTEST ;
LABEL ON TABLE TESTSQL                                 
IS '*TABLE: TESTING TURNOVER PROMOTIONS ' ;

This little technique is easy to read, and easy to maintain.

TURNOVER will automagically update the replacement variable &LIBRARY with the correct data library name for each level of the promotion form that is running.

Simple when you know how. šŸ™‚

NickLitten


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad 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 remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"cff50":{"name":"Main Accent","parent":-1},"a344d":{"name":"Accent Transparent","parent":"cff50"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"cff50":{"val":"var(--tcb-skin-color-0)"},"a344d":{"val":"rgba(46, 138, 229, 0.85)","hsl_parent_dependency":{"h":210,"l":0.54,"s":0.78}}},"gradients":[]},"original":{"colors":{"cff50":{"val":"rgb(0, 178, 255)","hsl":{"h":198,"s":1,"l":0.5}},"a344d":{"val":"rgba(0, 178, 255, 0.85)","hsl_parent_dependency":{"h":198,"s":1,"l":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__

Get In Touch

Iā€™m always looking for awesome input, feedback and critique!

>