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.
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. 🙂
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.
How to ZIP AS400 library and Email it
Copying iSeries fields from numeric to Alpha – aka using SQL to change column data type
What is IBM i Email and SPF?
Updating Numeric DTAARA in RPGLE
How to capture IBM-i job info for submitted jobs
Register license key in SOFTLANDING SOFTMENU
Going the (Levenshtein) Distance in RPG Free
How to Install IBM Access Client Solutions (ACS)
5733XJ1 IBM i Access Client Solutions – QuickStartGuide