On Thursday November 29, 2018 Rob Bestgen discusses the world of SQL and Db2

SQL Writing Tips and Techniques

SQL is a very powerful language for processing data. There are many ways to write SQL statements to get the desired results. However, not all queries are created equal. This session will compare different ways to approach a problem from an SQL perspective. Whether it is writing queries or creating stored procedures, taking the right approach for writing SQL helps the database help you.

Handout: SQL Writing Tips and TechniquesRobBestgen

Satisfy 'the Business' with DB2 Web Query

Db2 Web Query for i is a well-known, though likely underutilized, product in the IBM i world. Web Query provides many ways to consolidate and visualize information from data. This session delves into several ways to combine the power of Web Query with Db2 for i to improve your reporting and analytics experience. Wow the business people and empower them to do more themselves. The session will also review how to acquire, download and install the product using the ez-install capability to be up and running quickly (in trial mode.) There are even built in reports to get you started.

Handout: Satisfy the Business using DB2 Web Query

Accessing Data Outside of DB2 for i

There's a world of data out there that can improve and integrate with your own business data. Come to this session to see how the Web and other databases can be available to you via Db2 for i and SQL.

Handout: Accessing Data Outside Of DB2 for i

Vote on:

Transactional vs analytical SQL

SQL performance got you down? SQL statements looking like spaghetti? Proper database design for the intended usage is important for long term success and good performance. There is a difference between transactional and analytical processing and it is useful to understand the difference. What should an appropriate data model look like? What are the red flags that your data model is not best for your usage? This session will discuss these topics along with strategies for handling cases when analytical processing is required against your transactional data. Additional topics touched on include: - real time vs. ETL transformations - using dimension tables for dates, VINs, customer names and zip codes - Operational Data stores – and Dimensional Data Models.

Handout: Transactional vs Analytical


DataBase Security. Or Leave it to the Hackers

Data has value and it is a critical asset to a business. When a business is "hacked", it means data is stolen or destroyed and the company's reputation is tainted, possibly permanently. Plus, with new compliance laws, there can be legal consequences for not properly securing and managing data. Worse, if you have access to the data, you become a suspect! This session discusses the importance of data, distinguishes different types of data, identifies the many ways data can be breached, and what to do to protect your data. Topics covered include: Personal Information, Security administrative roles, Object level Security, row and column access control, PowerSC, and more.

Handout: Database Security - Or Leave it to the Hackers


IBM i Trivia - the meeting will conclude with “IBM i Trivia Jeopardy”. There will be a few answers to question. A small prize will be awarded to those who correctly question the answer.


Rob Bestgen is an IBM Db2 for i architect and consultant. In his 30+ years at IBM, Rob has held a variety of roles including: database developer, database architect, Business Architect, chief architect of the Db2 for i SQL Query Engine (SQE), and product development manager of various products. Rob is currently a proud member of the Db2 for i Lab Services team, helping customers utilize the amazing capabilities of Db2 for i, IBM i, and Db2 Web Query for i. Rob is also the product development manager for Db2 Web Query for i, the popular IBM i product for query reporting and analytics. Besides working directly with customers, he is also a frequent presenter at user conferences.


Dawn May presented to NHMUG on Thursday September 20, 2018

(With assistance from NHMUG board members)

Meeting Overview

Join the NHMUG meeting on September 20, 2018 that will start with an IBM i 30th birthday review. The rest of the day will be spent reviewing the highlights of IBM i Access Client Solutions and Navigator for i. More details on each of the sessions of the day is below.

IBM i 30th Review and UpdateDawnMay

2018 is the 30th birthday of IBM i. This year, IBM rolled out a set of client success stories to celebrate. This session will review the 30th birthday celebration and showcase some of the customer stories on how companies are using the IBM i in creative and modern ways. There are many companies that use IBM i and this 30th celebration will show how “IBM i is everywhere”.

IBM i Access Client Solutions, Part 1

IBM i Access Client Solutions (ACS) has become the IBM i administrative client, offering a full set of functions from 5250 terminal sessions, printer output, IFS, database and more. This session will review some of the key features that everyone needs to be aware of and using today.

Highlights will include:

  • Deployment - https://www.ibm.com/developerworks/ibmi/library/i-ibmi-access-client-solutions-2/index.html
  • Secure (SSL) connections
  • Environmental requirements, including which ports, services, and servers are required.
  • Latest ACS Features - mostly Db2, but IFS updates and some Print updates
  • Visual explain and Index Advisor
  • Overview of Run SQL Scrips and IBM i Services
  • Insert from examples
  • How to save your own examples
  • ACS and LAN Console
  • ACS and Open Source

If you plan to attend VTMUG on September 27th, Paul Tuohy is doing an ACS for Programmers session there.

Q&A, General Discussion, NHMUG Meeting Topics

The last 15 minutes of the lunch hour will be for meeting attendees to provide input or ask questions.

What would you like to have on future meeting agendas? Are there any specific speakers would you like to see invited to the meeting? Any IBM i or Power Systems questions are welcome. If you would like to ask your question anonymously, feel free to send it to a board member prior to the meeting.

IBM i Access Client Solutions, Part 2

ACS topics listed above will continue after lunch.

Navigator for i

  • Application Administration
  • Set Target System
  • Favorites
  • Lastest Enhancements
  • Environmental considerations for Navigator
  • General review of Navigator
    • Work management and subsystems
    • Performance tasks - Dashboard, System Monitors, Batch Model Graph History
    • IFS
    • PTFs - compare and update This session will conclude with a review of ACS and Navigator features vs the features of IBM i Client Access and System i Navigator will be provided, noting specifically where there are gaps.


IBM i Trivia - the meeting will conclude with “IBM i Trivia Jeopardy”. There will be a few answers to question. A small prize will be awarded to those who correctly question the answer.


Dawn May is a leading authority on IBM i performance management, work management, systems management, and general system diagnostics. She has in-depth knowledge of the IBM i operating system developed through her distinguished career with IBM.

A former IBMer, Dawn is now an independent consultant, assisting companies with system performance reviews and recommendations. She also teaches clients on the latest IBM i tools to enable them to get the most out of their IBM i systems.

Dawn is the author of the "i Can" blog published by IBM Systems Magazine and a frequent speaker at local user group meetings and IBM i conferences around the world.

30th Annual New Hampshire / Vermont IBM i User Group Meeting

Thursday, June 14, 2018 - Fireside Inn, West Lebanon NH

This event was free to IBM i professionals, and sponsored by NHMUG and VTMUG


IBM Announcements - Steve Fier, IBMSteve Fier

Steve will talk about the new POWER 9 systems. 

The presentation will also provide an overview of IBM i 7.2 TR8 and 7.3 TR4

Handout: IBMi and P9.pdf

About Steve

Steve Fier has been with IBM over 30 years, initially starting as a systems engineer, and then becoming a senior marketing specialist. His current role is POWER Platform Leader for the Northeast, responsible for former System i and System p products. Steve's areas of expertise include application development, DB2 for i, Windows Integration and Server Consolidation.

Stupid Python Tricks - Kevin Adler, IBM 

Python has many tools built in and many tools just a web request away. Learn how these tools can save you time and money. See examples parsing CSV files, sending emails, building Excel xlsx files, modifying images, building websites, and more!

Handout: stupid python tricks.pdf

Kevin Adler

Introduction to Yum - Kevin Adler

There's been many ways to install open source on IBM i in recent times: AIX rpms from various websites including Perzl.org, 5733-OPS and 5733-SC1 products from IBM, compiling them yourself, and even PowerRuby and ZendServer products. Now there's a delicious new way to get open source on IBM i: YUM. Yum makes it easy to install and manage your PASE-related open source software like Python and Node.js. Learn how to use yum and why you'll never want to deal with other ways of installing your open source software ever again.

Handout: yum.pdf

About Kevin

Kevin Adler works at IBM, attempting to bring as much Linux and open source awesomeness to the IBM i platform. He has a wide range of knowledge on various computing subjects. He enjoys learning languages (both computer and natural) - currently he's learning Rust and French. If you want to know the ins and outs of Unicode, endian conversion, using pointers in CL, building an antenna, calling between ILE and PASE, bash scripting, git rebasing, playing Magic the Gathering, or how to exit vi... he's your guy.

If you want help fixing your Windows system, he will graciously provide you with a Linux install disk.

Neo4j - Kyle Rainville, Littleton Coin 

Neo4j is an ACID-compliant graph database management system and is currently the most popular graph database. Neo4j’s purpose is not to replace traditional RDBMS’s, but rather to serve different, more specific use cases. In the rapid state of technology today, many companies are finding more uses for highly interconnected data and Neo4j was created to serve these needs. In this presentation, Neo4j, its query language, and its uses/advantages will be introduced.

Handout: Neo4j.pdf

JSON - Kyle Rainville

JSON is a data interchange format language that has very quickly overtaken XML as a way to communicate between languages and platforms. But what makes JSON preferable to XML? In this presentation, Kyle will introduce JSON, the syntax, its advantages and intricacies, and how it is commonly used.

Handout: JSON.pdf

About Kyle

Kyle Rainville graduated from Plymouth State University with a Bachelor’s degree in Information Technology as Magna Cum Laude in December of 2016. Kyle started working at Littleton Coin Company as a Programmer & Network Analyst in June of 2016 and has started learning RPG and the IBM i platform. Kyle is passionate about open-source technology and Linux and is currently pursuing the Red Hat Certified System Administrator certification.


John Valance presented at NHMUG on Thursday March 22, 2018

John presented the following sessions:

Connecting the Dots (intro to web development with PHP)

New to web development, or trying to get started? Have you taken a PHP course, but are having trouble understanding how to build complete applications for the web? Or maybe you're a manager trying to get an understanding of how web applications work at a high level. This presentation will provide an overview of how browser-based database applications work, with an IBM i / RPG audience in mind. It will demonstrate the client-side languages - HTML, CSS and JavaScript - and show how PHP scripts running on a server can dynamically build web pages including content from an IBM i DB2 database.

We will discuss the HTTP protocol and how web servers work with HTML and PHP, look at some common formatting techniques in HTML and CSS, demonstrate how HTML forms interact with PHP scripts, and show what JavaScript is and how it makes the browser more functional. With these basics concepts in mind, you should be able to make better decisions about how to sort through the many options available for web-enabling your legacy databases.

This presentation is for beginner to novice web programmers, or those who would like to understand the technologies and languages involved in a typical web application.

This session is a recommended (not mandatory) pre-requisite for John's labs on HTML, PHP, and JavaScript.

--> Click HERE for this session handout <--


Web Mastery with JavaScript

While PHP and server-side languages are essential to web programming, these days JavaScript is an equally essential skill for any professional web programmer. Users have come to expect highly sophisticated web applications, far beyond what HTML and PHP can deliver. In order to develop these modern web and mobile applications, one must have a fair mastery of JavaScript.

So what is it? Not to be confused with Java, which typically runs on the server, JavaScript typically runs in the browser and allows you to create scripts that can control the browser experience after a web page has been downloaded from the server. This off-loads processing from the server and allows web applications to utilize some of the features associated with PC applications, such as a rich user interface and sub-second response times.

This session will introduce you to the concepts, capabilities and syntax of JavaScript, and help you understand how you might use JavaScript in your web applications. You will learn about using JavaScript to validate input forms and react to browser events such as page load, button clicks, mouse movement and keystrokes. You will also see how JavaScript can be used to change the content and appearance of web pages without reloading the entire page. It will also introduce some of the more advanced concepts of object oriented JavaScript and JSON (JavaScript Object Notation), Ajax (Asynchronous JavaScript and XML), and JavaScript frameworks, such as jQuery, which greatly simplify JavaScript programming tasks.

This is highly recommended as a prerequisite to my JavaScript lab, as well as my Introduction to jQuery presentation.

--> Click HERE for this session handout <--

SQL Stored Procedures and Application Modernization

Among the many modernization technologies available on IBM i, SQL is perhaps the one best known by RPG developers. This session won't focus on how to write SQL statements, but rather how to write scripts that combine SQL statements together into procedures that encapsulate database access and core business logic for use in a myriad of modern applications. We'll discuss how stored procedures fit into a modernization strategy, and why they are such a good fit for RPG programmers who want to participate in modernization projects. They're also a great fit for anyone new to IBM I who needs access to its database for applications in PHP, Java, Crystal Reports, IBM Web Query, Excel, Web Services, conversion projects, etc.

SQL is often embedded in other languages, such as RPG, PHP, Java, etc. But IBM provides the SQL Procedure Language (SQL/PL), which can be used to code complex or multiple SQL statements into procedures that can form the backbone of a service oriented architecture. These procedures can be called from any programming language. This can simplify the database access code in PHP, Java, and other host languages, while taking full advantage of DB2-specific SQL features.

Come and learn about the many features and benefits of SQL stored procedures, how to code them, and how to use them in your modernization strategy.

--> Click HERE for this session handout <--

Case Study: B2B e-Commerce With PHP on IBMi

This session will describe a recently completed project to create an online wholesale ordering system for a beverage manufacturing and distribution company with a BPCS/LX backend. The requirement was to create a browser-based order entry system for wholesale customers that do not have EDI capability. The system needed to have a simple, modern, intuitive interface, usable on desktop and tablet browsers. The order submission procees needed to feed into a BPCS/LX backend. The server language of choice was PHP, using Zend Framework 2.

This will be a high-level, project management focused presentation, highlighting the technologies and methodologies employed, and challenges faced and how they were resolved. Some of the topics to be covered include: requirements gathering, wireframing, HTML design, use of offshore resources, controlling database access with stored procedures, integration with ERP system, PDF form generation, e-mail acknowledgements, SSL setup, and security.

If you are considering creation of an e-commerce system, please come with your questions and learn how we got this system off the ground.

--> Click HERE for this session handout <--


John Valance has worked on the IBM midrange platform since 1983 and on web development since 1998. As a consultant and developer, John specializes in database and web application development on the IBM i platform. He has also been a trainer for Zend Technologies, teaching PHP Foundations for IBM i Programmers, and is a Zend Certified Engineer. He has extensive programming experience in RPG, Java, PHP, and JavaScript, and he enjoys explaining new programming concepts to RPG programmers. John is a frequent presenter at user groups, and has created presentations and labs on many web development topics. John was elected to the COMMON board of directors for a 3-year term in May 2017. John is the president and founder of Division 1 Systems, a software and services firm based in Burlington, Vermont (www.div1sys.com).

Growing up in New York City, John started his career in the NY area, but moved to Vermont in 1994 to raise a family and has been there ever since. John is also an accomplished musician, playing a variety of instruments and styles.

John can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..