04
Mon, Nov
1 New Articles

DB2/400 via Lotus Notes: Free Delivery!

Collaboration & Messaging
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Possibly unknown to new Notes users is the native data exchange feature that allows you to transfer data between DB2/400 and Notes. If you have Notes, you have this feature. Before you consider spending thousands of dollars to buy a product to handle your DB2/400 and Notes data transfer requirements, learn about the power of what is already available to you at no additional cost.

Most AS/400 installations thinking of using Domino for AS/400 are likely thinking about data exchange between DB2/400 and Domino/Notes. After all, there’s all that legacy data waiting to be shared through Domino’s powerful groupware features and Internet/intranet delivery capabilities.

A number of great data transfer products are available for Domino/Notes. Some of these products include Lotus NotesPump (see “Lotus NotesPump: A High-speed Data Pump for Your Enterprise,” MC, April 1999), soon to be known as Lotus Enterprise Integrator (LEI); DataMirror SQL Pump; Casahl Replic-Action; and Percussion Software Notrix. These products perform more than simple data transfer but carry a price tag you may not be able to justify. The good news is that Notes already has data transfer capabilities, so you may not need to spend money on additional software.

The material here shows a sample of how to use the powerful native Notes data transfer feature LotusScript Data Object (LS:DO). LS:DO requires an ODBC driver to connect to the AS/400. The Client Access/400 ODBC driver might be the most readily available, but there are others. The sample presented here imports data into Notes from DB2/400 but could also import data from any ODBC-compliant relational database.

LotusScript Data Object (LS:DO)

Before I describe LS:DO, a little information about LotusScript might help. LotusScript is a superset of Visual Basic (VB) made available in Notes Release 4. The syntax of LotusScript is similar to VB save one significant difference: LotusScript’s use of classes. LotusScript performs much of its work through objects created through a set of Notes classes that define such Notes components as databases, documents, and Views. Almost everything in a Notes application is some type of object containing properties and

methods defined by the Notes classes, so LotusScript is, to a degree, an object-oriented (OO) language.

Learning the syntax of LotusScript is easy, but learning to work with Notes classes takes more work. With LotusScript, you’ll quickly find yourself in the world of classes and objects. In fact, one advantage of LotusScript programming to those inexperienced in OO technology is that it teaches the application of OO programming concepts while avoiding the need to create classes from scratch. You’ll be working with a set of classes Lotus has already created, providing a good introduction to OO technology.

With LS:DO, you can use all the programming logic you want in your data transfer. For example, you can submit complex selection criteria, manipulate data during transfer, and apply business rules. Also, LS:DO can return more than one column of data and allows you to execute more than one SQL statement with the same connection.

However, along with the power of LS:DO comes the required knowledge of LotusScript syntax and how to work with Notes classes. LS:DO is made up of three LotusScript classes: ODBCConnection, ODBCQuery, and ODBCResultSet. These classes have many properties and methods that can retrieve data from and update data to external relational databases. As the class names imply, they use ODBC, which, as previously mentioned, means you need an ODBC driver. If you already have experience with ODBC, you may recognize many of the properties and methods used by these ODBC classes.

Before you can use ODBC in an application, you must define an ODBC data source name (DSN) for the system from which your application is to retrieve data. You define DSNs through the ODBC Data Source Administrator. On the Windows platform, you should find the ODBC Administrator in the Control Panel application group, normally with an icon labeled 32bit ODBC.

DSN configuration is simple. Just identify the ODBC driver to be used to establish a connection to the system where the data resides. Figure 1 illustrates the portion of the DSN definition using the Windows ODBC Administrator where the Client Access ODBC driver is selected to connect to the AS/400. During configuration of the Data Source, check the Translate CCSID 65535 option found on the Translation tab. For more information about DSN configuration, use the context-sensitive Help button presented by the ODBC Administrator application.

In addition to the ODBC DSN definition, your Notes application must load a LotusScript extensions (LSX) file. This file contains Public definitions for ODBC. In the form where you intend to import data from DB2/400 or any ODBC-compliant database, insert the following statement below the Option Public statement in the Globals design element:

Uselsx “*LSXODBC”

You can find the Globals design element in the design elements list near the top left of the Design pane at the bottom half of the window when you’re editing the form (see Figure 2).

In the top portion of Figure 2, you can see the form used by the sample application. It retrieves the customer’s city and state from an AS/400 file when the user clicks the Retrieve City&State button. The import function is accomplished through LS:DO code, which is linked to the button’s click event. When the user clicks the button, the code in Figure 3 executes.

In LotusScript code, connection to the AS/400 is made through the statement status = con.ConnectTo(“MC170”). The parameter value “MC170” identifies the DSN defined for the AS/400. Notice a similar connection statement following the statement just mentioned. This second connection definition (actually a comment) illustrates how you connect to DB2/400 from a Notes database residing on your AS/400 Domino server (the sample created and used here resides on a client PC). As you can see, no DSN value is specified. Instead, the AS/400 system name and user ID and password are used. When you

use LS:DO with a database that resides on the Domino for AS/400 server, an ODBC DSN and ODBC driver aren’t actually required. You do, however, use the same ODBC connection class methods. In this case, the ODBC calls are actually intercepted by OS/400 and made directly to DB2/400.

This example illustrates how easy it is to make a connection and get data from DB2/400; it is not meant as an example of all the things you can do with LS:DO. With LS:DO, you can perform some powerful data transfers, including data conversions and even data replication.

Such a Deal

Lotus Notes has some pretty powerful built-in data exchange features for those who need to transfer data to and from DB2/400 or other relational databases. If you have an ODBC driver (e.g., the Client Access ODBC driver) and some technical skill, you might be able to implement these features without spending a dime on additional software. This is not to say you shouldn’t avail yourself of other data exchange products out there; they may give you the features you want and reduce the need for in-house software development. In any case, check out what you can do with Notes’ native data transfer features before purchasing more software. You may find exactly what you need.


Figure 1: You must define an ODBC data source name and identify the ODBC driver that is to be used to establish a connection to your AS/400.





DB2-_400_via_Lotus_Notes-_Free_Delivery_03-00.png 900x570




Figure 2: Here in the Notes form design session, the Global design element is selected, and the LS:DO LotusScript Extension (LSX) is defined for it.



DB2-_400_via_Lotus_Notes-_Free_Delivery_04-00.png 900x1022




Figure 3: This LotusScript code updates the City and State in the form in Figure 2 from data that resides in an AS/400 file.



DB2-_400_via_Lotus_Notes-_Free_Delivery_05-00.png 899x841
BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: