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

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. 🙂