.st0{fill:#FFFFFF;}

Upgrade from PF to DDL (SQLSTM) using TURNOVER from UNICOM SOFTLANDING 

 March 4, 2016

By  NickLitten

When you do the RUNSQLSTM to create your SQL Objects – Do not qualify library names but use the Turnover variables just like you would define PDM variables:

RPG Code Snippet PF to DDL? RUNSQLSTM SRCFILE("&SL"/"&SF"
 SRCMBR("&SM") 
 COMMIT(*NONE) 
 NAMING(*SYS) 
 DFTRDBCOL("&LI")

Turnover uses the DFTRDBCOL column “&LI” to populate the other *library* variables.

 DFTRDBCOL(NLITTEN)

Most shops I’ve been to use QSQLSRC to store the SQL definitions then use RUNSQLSTM to “compile” the source. Make sure you use a semi colon between each SQL statement.

I prefer to break source out into separate files for each type:

  • QSQLTBLSRC for SQL tables
  • QSQLIDXSRC for SQL indexes (aka indices)
  • QSQLVIEWSRC for SQL views

NOTE: These alternate source file names are completely optional.

Source members are named and commented like any other source member. It’s neat — get in the habit. šŸ˜‰

All code changes are promoted and managed by Turnover.

Remember, first use a DROP TABLE statement to drop the table (which deletes its data and its DB2 dependencies). Then a CREATE TABLE statement for all columns, including CONSTRAINTS (Primary Key, Foreign Key, CASCADE’s if any, etc.).

Our standards also then have a CREATE UNIQUE INDEX statement for the keys, to start off the QRYMGR in the right direction.

Dont forget to take advantage of ALTER – for those times that you don’t want to DROP and CREATE columns or attributes then use ALTER TABLE statements to add columns and/or attributes without killing the table or its data.

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!

>