Sign In
   Developer Productivity PKGs
Enterprise Web Apps
IBM i w/ smartclient Solutions

Creating a Master File Data Layer with SQL PL
(a technical report bundle available for purchase)

  • See and understand standard IBM SQL PL and stored procedure syntax that performs all of the DB access used within this data layer pattern example
  • See differences in syntax and creation steps for stored procedures using Db2 for i *LIBL
    (a run time *LIBL configuration usually enables identical code and file definitions to work within multiple environments,
    such as test, development and QA)
  •  Get SQL PL alternatives that support HLL commit, mult-user conconcurrency and user changes overlay preventation (even from web)
See below for option to purchase this technical presentation
(currently on v2).

**purchase includes downloadable updates and new versions for 3 years**

Coming soon, a new appendix,
Whipping Up an RPGLE After Image Capture Trigger Program for Changes on Web

(a technical presentation created for POWERUp 2019 ANAHEIM conference and expo)

Chart on What We’ll Cover in the Presentation
(better than the TOC)
Topic Covering Notes Download
List of Stored Procedures Yes 8 in this example
PL Source Code Yes Yes
Parameters Yes  
IDE Compiling Just a little bit about Navigator
(CA 7.1 and ACS), RDi - Video
Consuming - Mode Usage Yes Easily implied
Consuming - Overlay Prevention Overview Sequence and Conditions of recommended method for preventing overlays - more advanced - involves commit boundaries
Plumbing and Connecting No  
Software Prerequisites Some  

This session won't get into too much detail when it comes to IDE(s) or plumbing (such as connecting to the database), primarily so our time can be devoted to the working SQL PL pattern example included and to see how it solve challenges that are applicable to most anyone creating stored procedures for use with user-initiated enterprise level master file maintenance. 

Tegratecs Development Corp.®

Important - The keys that enable immediate download (directly after purchase) are emailed to the PayPal Account holder used with the purchase (and to a second email address if you are logged in to under a different email address). 

Purchase Offer (includes updates)
Purchase technical report bundle
Creating a Master File Data Layer with SQL PL
 Order Items  (Show Details...)   
QuantityUOMItemDescriptionPriceCurr Code
1EACHCMFDL2TRCreating a Master File Data Layer with SQL PL - TR Bundle
Tech Report v2   3 Yr Download+Updates v%
1    Total95.00 
(LogIn to PayPal or click Debit or Credit Card)
(both handled via PayPal with option to approve or cancel)
(No CC digits will stored on

$95 US
(get immediate access plus all subsequent updates and versions for 3 years)

Creating a Master File Data
Layer with SQL PL


This presentation will examine working examples of IBM SQL PL, for maintaining and inquiring upon a "master" file. The SQL PL statements will be segregated into a set of stored procedures, probably best described as a data layer (as opposed to a data tier in 3 tier architecture).

The examples seek to show how one might separate data access and update logic from the high level language program, and facilitate broader ease of use and consistency across the enterprise (interactive, batch, web). We'll hit inquiry, delete, add and update.

The update stored procedures include an optional one that does the equivalent of a RPG CHAIN or pessimistic lock, if you are able to (and want to) control commit/rollback boundaries from within the high level language program.  The examples also cover the steps required to do optimistic row locking (and prevent overlays) while allowing concurrent maintenance with other separate and distinct processes.

Learning Objectives:

  • See and understand standard Db2 for i SQL PL and stored procedure syntax that performs all of the DB access used within this data layer pattern example
  • See differences in syntax and create steps for stored procedures using *LIBL and system naming (probably not applicable to all programming environments)
Tegratecs Development Corp.®

        go to the home page Top of Tech Reports, White Papers and Presentations     go to next series page Next in Tech Reports, White Papers and Presentations     Site Map     Switch to
Mobile View

You are at the web site of Tegratecs Development Corp.  Click here to go to the home page of this site...
Offering Innovation & Providing ROI
while minimizing Islands of Automation
Our contact information:
Tegratecs Development Corp.®
1320 Tower Road
Schaumburg, IL 60173
( please contact us or register or sign-in )
© 2012-2024