OCEAN's Next Meeting - Tuesday, October 20, 2015


 DB2 & SQL Expert Dan Cruikshank of IBM!

5:00 PM -  Database Re-engineering 

6:15 PM - Dinner

7:15 PM -  Moving OPNQRF to SQL Using Open Access

Register: Oct 20 - DB2 & SQL

Database Re-engineering 

This session will provide an overview of the 3 phased approach to database reengineering as described in Chapter 9 of the Modernizing IBM i Applications Redbook. This begins with the technique for transparently migrating DDS to DDL using the “measure twice, cut once approach” where we exploit Data Centric constructs with little or no impact to existing programs.  The session will cover the process of building a solid database foundation using a physical, virtual and data access layer framework.  The use of IBM development tools (i.e. Infosphere Data Architect, IBM Data Studio and Rational Developer) will be discussed. In addition, minimizing the impact of change via the use of bridge techniques (e.g. RPG via Open Access) and Instead of Triggers (i.e. creating updateable SQL Join views) will be discussed. The following is the agenda for this session: 

  • Overview of the IBM Reengineering Strategy
  • Reverse Engineering  an existing Database
  • Refactoring Data Access
  • Restructuring the Tables

Moving OPNQRYF to SQL Using Open Access 

Years after OPNQRYF first shipped on IBM System/38™ severs, IBM delivered SQL as an application development tool on AS/400. Almost every non-5250 client application that accesses information from the DB2 for i platform will use an SQL interface. A good first step in standardizing your IBM i applications is to convert OPNQRYF commands to use industry standard SQL.In this session you will learn how to convert OPNQRYF command syntax to equivalent SQL SELECT statements. The SQL statements can then be coded within a stored procedure which returns a result set to a client application. An Open Access Handler, utilizing result set consumption, provides a means to bridge existing OPNQRYF applications to using SQL. In addition, you will learn what you need to know to begin creating RPG program handlers to access the same stored procedures utilizing a handler. Note: Although Open Access is not available to COBOL, the concepts of shared result sets and bridge programs will be beneficial to COBOL developers who need to eliminate OPNQRYF file from their applications. The following is the agenda for this session:

  • Understanding Open Query File (OPNQRYF)
  • Reasons To Consider Conversion
  • Handlers to the Rescue
  • Conversion Methodology

Dan Cruikshank is an IBM Sr. Consultant and is a member of the IBM DB2 for i Center of Excellence team which is part IBM Rochester Lab Services. Dan has published several articles relating to IBM i performance analysis, database optimization and database reengineering. More recently he has done webinars and user group presentations on application development methodologies and database re-engineering topics (e.g. DDS to DDL, RPG Open Access). Dan’s main role is to illuminate, educate and enable IBM i customers in how to take advantage of this remarkable system and it’s integrated, state of the art relational database.

Meeting Fee (includes dinner):  Members free  -  Non-Members $20

Meeting Location: National University - 3390 Harbor Blvd. Costa Mesa, CA  92626

There is plenty of free and convenient parking.




IBM i Local User Groups

Aaron Bartell (Litmis)

Alan Seiden

Code Project

COMMON. A Users Group


Dawn May's Blog


IBM i Knowledge Center

IBM Systems Magazine

IBM i Web Page

i Pro Developer

iTalk with Tuohy

IT Jungle

Jon Paris & Susan Gantner

MC Press Online

Mike Pavlak

Scott Klement

Simon Hutchinson

Stack Overflow

Steve Will's Blog

Young i Professionals 

   Thank you, Maxava!


OCEAN Conference Spotlight

Alan Seiden Coming to Costa Mesa to Present PHP Sessions on July 17th!  Video


  Mike Pavlak who is presenting "PHP on IBM i: Getting Started" and "Stored Procedures & PHP on IBM i" has answered the five questions:

1. What is an essential skill that any IBM i Developer should have? Desire and ability to learn, adapt and change.  I still see folks out there looking for work with RPG III skills and wondering why the world has passed them by.  Today employers are looking for RPG, Web, project Management, etc.  You need more weapons in your arsenal if you’re going to do combat in today’s IT infrastructure.

2. What online publication(s) do you read?  I’ve become a fan of Flipboard as it tends to show articles that I can drill down to. 

3. What is your favorite tech gadget?  My Galaxy S3 is still a favorite, even though it’s 3 years old!

4. What’s the most common question you’re asked at speaking engagements? How long does it take to learn PHP?  As anyone would tell you the answer varies from weeks to years depending on the level of experience and how far you want to go with PHP.  And since PHP and the web are constantly evolving, there is no practical end to the learning curve.  

5. What’s the one session at the OCEAN Conference that no one should miss? Mine (PHP).  Of course.  But seriously, I think anyone doing RPG needs to understand, implement and manage service programs and subprocedures.  This is an essential skill that compliments so many other areas like PHP, Stored procedures, Ruby, etc.  outside of that, soak up everything you can about DB2!

Thanks, Mike. See you on July 17th!


 Alex Roytman who is giving a presentation on RPG Open Access at the June 16th OCEAN meeting and also speaking at the conference, has answered the five questions:

1. What is an essential skill that any IBM i Developer or Systems Admin or Manager should have?   Coming up with creative solutions to problems and thinking outside of the box.

2. What online publication(s) do you read?   IT Jungle, MC Press, IBM Systems Magazine, LinkedIn User Groups for IBM i.

3. What is your favorite tech gadget?  Fitbit.

4. What’s the most common question you’re asked at speaking engagements?  Why didn't IBM create a product like Profound UI and include parts of it in the OS?

5. What’s the one session at the OCEAN Conference that no one should miss?  Any application modernization sessions.

Thanks, Alex. See you on June16th and July 17th!


 Dawn May who is presenting "Navigating the World of Performance" and "Art of Performance Diagnostics with IBM i Performance Data Investigator" on July 17 and "Workshop: IBM i Systems Management Solutions" on July 18 has answered the five questions:

1. What is an essential skill that any IBM i Developer (or Systems Admin or Manager) should have?
Flexibility and the willingness to change.  No matter what your area of expertise, things move quickly in the world of technology. Everyone needs to be able to adapt to change and be flexible.    

2. What online publication(s) do you read?
For IBM i reference information there are two critical resources - the Knowledge Center and the IBM i developerWorks site, although these really aren't online publicationsFor general information, I tend to read a little bit of everything and I frequent many online sites. Twitter has been a source of many interesting articles.

3. What is your favorite tech gadget?
My iPhone 6+!  I love it! It's big enough to be very usable for reading and email.
I have a small addiction to games (1010 is the worst), and it has liberated me from lugging around my big DSLR.
I do a lot of walking and have an app that tracks my route and distance so I can remember where I went and see how far I walked.

4. What’s the most common question you’re asked at speaking engagements?
"Is that function available on 6.1?"  IBM has PTFed many enhancements back to 7.1 and there's always someone who also wants it on 6.1. 
We have taken much function back to 6.1, but there's less and less of that happening now.
5. What’s the one session at the OCEAN Conference that no one should miss?
I took a look at the agenda and there's no way to pick one session. Although, if you haven't heard Steve Will speak, you will want to go to hear him. He's very passionate about IBM i. I work on administrative topics, so I think the systems management track is the best!

Thanks, Dawn. See you on July 17th!

News - The recording of Aaron Bartell's webinar on Node.js is now available:Node.js Webinar

IT Insight - Please send us an email if there is a technical tip, technology insight, career advice, or anything else you would like to share with the IBM i community. Articles must be a minimum of 200 words and be educational, rather than promotions of specific products. We will email a $25 gift certificate to anyone who submits an article that's posted on the website.

 Join our Linkedin Group and join the discussion