21
Wed, Feb
3 New Articles

Access DB2/400 Data with LS:DO

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

DB2/400 data can be accessed from the Lotus Notes/Domino environment in many different ways. These options include using @DB commands, writing an agent in LotusScript Data Object (LS:DO) or Java, coding a servlet, using one of the visual mapping tools such as Domino Enterprise Connection Services (DECS) or Lotus Enterprise Integrator (LEI), or writing a Lotus Extension (LSX) for Lotus Domino Connector (LC). In this article, I will focus on writing an LS:DO agent to access DB2/400 data.

Agents are batch processes that run within the context of a Domino server. Agents can run either on the Notes client or the Domino server. The key here is that the agent must run in the context of Domino. An agent can be invoked in several ways. An agent can be triggered manually or via a URL, scheduled, or invoked through an event such as clicking a button, exiting a field, or opening a document.

An agent can be written in either the LotusScript or Java programming language. The choice depends primarily on the programming skills you have access to. I will show you how to code an LS:DO agent in LotusScript to access data in DB2/400. Watch for a future article that illustrates coding an agent in Java.

Overview of LS:DO

LS:DO is similar to Open DataBase Connectivity (ODBC). If you are familiar with ODBC, coding to LS:DO will be easy for you. LS:DO is one of the LotusScript Extensions. It provides a set of object-oriented classes to allow access to back-end databases and applications. LS:DO provides full read and write access to external ODBC data sources using the control and flexibility of a structured programming language.

LS:DO Architecture

Three classes comprise the LS:DO architecture: ODBCConnection, ODBCQuery, and ODBCResultSet. I’ll take a closer look at each of these classes.

The ODBCConnection class allows you to establish a connection. In this case, you will be connecting to a back-end system. It also allows you to access some database catalog information, such as data source lists, table lists, and procedure lists. A number of properties can be set for this class, and a variety of valid methods can be used with it. In


the code sample in this article, the ConnectTo() method will be used with the ODBCConnection class.

The ODBCQuery class holds the ODBCConnection object—in which a connection is established—and an SQL statement. The three properties associated with this class are Connection, QueryExecuteTimeOut, and SQL. In this example, the connection property is set to associate the query with the ODBCConnection. Next the SQL statement being sent to the AS/400 is defined by setting the SQL property.

The ODBCResultSet class is used to work with the results returned from the SQL statement. This is done by setting the Query property on the ODBCResultSet to associate the SQL statement defined in the ODBCQuery object with the result set. Several methods can also be invoked on the ODBCResultSet class. The Execute() method executes the SQL query. Once the query has been executed, a variety of methods can be called to navigate through the returned result set. These methods include result set row navigation and location, accessing column values, result set row modification operations, column attribute operations, and SQL parameter operations. In the example, the GetValue() method, which is part of the accessing column value operations, is used.

LS:DO in an AS/400 Environment

LS:DO does not need an ODBC driver to access DB2/400 tables as long as the code runs on the server rather than on a client. No ODBC driver is needed to access DB2/400 data from Domino for AS/400.

If the agent is called from a Notes client environment, an ODBC driver needs to be installed on that client to interface with the AS/400. However, if the agent is called from a Web browser, no ODBC connection is needed. In this case, the ODBC layer is bypassed, and the agent interfaces directly with the Call Level Interface (CLI). This provides both a performance boost and ease of setup. Refer to DB2 for OS/400 SQL Call Level Interface for details on CLI. CLI does not have an ODBC.ini file. Instead, the user registers the data source on the AS/400 using the Work with Relational Database Directory Entry (WRKRDBDIRE) command. If a directory entry does not exist with a Remote Location value of *LOCAL for your AS/400, use the Add Relational Database Directory Entry (ADDRDBDIRE) command to add one. Think of the relational database directory entry as a label for your AS/400. This label is passed in to the ODBCConnection class to identify the data source that represents your AS/400.

LS:DO Example

The code sample provided here is for an LS:DO agent that is invoked when a user provides a valid customer number on a Lotus Notes form and then presses the Retrieve Customer Data button. Clicking on this button triggers the LS:DO agent. The agent uses the customer number provided on the form and constructs an SQL statement to retrieve the corresponding customer data from DB2/400. The values returned from the SQL select statement are then put into the corresponding fields on the Domino form.

Take a look at the Domino application in Figure 1. The application consists of one form with seven text fields: customer number, last name, initials, address, city, state, and zip code.

The application user inputs the customer number and then clicks on the Retrieve Customer Data button to retrieve the information. When the Retrieve Customer Data button is clicked, the LS:DO agent is invoked. The agent connects to the DB2/400 data source and retrieves the last name, initials, address, city, state, and zip code fields for the corresponding customer number that has been provided. The data will be retrieved from the AS/400 file QCUSTCDT in library QIWS.

To trigger execution of the agent, some code needs to be added to the Retrieve Customer Data action. Do this by adding the following @Command to the Click event associated with the action (Figure 2).


'This @Command will call the agent, Read Customer LSDO.
@Command([ToolsRunMacro];"Read Customer LSDO")

It’s time to delve into the code that comprises this agent. The code body of the agent has six steps:

1. Load the LSX

2. Declare new classes

3. Establish a connection

4. Define the SQL statement

5. Retrieve the result set

6. Work with the result set

Portions of the agent code are used here to illustrate the essential steps in retrieving data with LS:DO. The complete code can be found on the Midrange Computing Web site at www.midrangecomputing. com/mc.)

Step 1: Load the LSX

This first step makes the ODBC classes available to the programming environment by loading the classes. This is accomplished through the Uselsx “*lsxodbc” statement. This statement must be placed in the Options event of the Global object.

Option Public
Uselsx “*lsxodbc”

Once the classes are loaded, continue with the bulk of the code. This code is placed under the Initialize event of the Global object.

Step 2: Declare New Classes

Now that the ODBC classes are loaded, create objects from the three classes that comprise the LS:DO architecture. This can be accomplished with Dim variablename As New classname statements, which are indicated in blue. Create a new ODBCConnection object, con, a new ODBCQuery object, qry, and a new ODBCResultSet object, res.

Sub Initialize
‘ Declare a new instance of each class
‘ using the New method

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim res As New ODBCResultSet

Once new objects are created from these classes, additional objects are created that allow you to work with the current front-end document, i.e., the document the user is working with via the form.

‘Setup to work with the current document

Dim ws As New Notesuiworkspace

Dim uidoc As notesuidocument


Set uidoc=ws.currentdocument

Step 3: Establish a Connection

Now that the initial setup has been done, you are ready to connect to the back-end data store. Do this by calling the ConnectTo() method on the ODBCConnection object (con) as illustrated in Figure 3 (page 82). The ConnectTo() method requires three parameters:

• The name of the AS/400 data source. This can be found by issuing the command WRKRDBDIRE and noting the Relational Database name specified with a Remote Location value of *LOCAL. In this case, the Relational Database name is SYSTEMA.

• A valid AS/400 user ID. In this example, I’m using DOMINOUSER.

• A valid AS/400 password for the user ID you have supplied. I’m using DOMINO1 in this example.

Note that the Relational Database directory entry, the AS/400 user ID, and password are all uppercase and are contained in quotes (“ ”). These parameters are case- sensitive and need to be specified in uppercase.

Check that a valid connection has been made with some code similar to that found in Figure 4.

Step 4: Define the SQL Statement

Once you have a successful connection to the AS/400 data store, you are ready to prepare the SQL statement that you will use to retrieve the correct fields based on the customer number provided by the application user. Build an SQL statement based on the customer number provided by the user (uidoc.fieldgettext(“CustomerNumber”)).

Two steps define the SQL statement. First, the Connection property must be set on the ODBCQuery object to associate the query with the ODBCConnection.

‘ Code to build
‘ SQL query statement

Set Qry.Connection = Con

Now you can set the SQL property on the ODBCQuery object to define which SQL statement will be sent to the AS/400 (Qry.SQL = “select...”). Any valid SQL statement can be specified here: a select, update, insert, or delete. The agent is reading data from the AS/400 DB2/400 file, so a select is specified (see Figure 5). Check that the SQL statement is valid with some code similar to that found in Figure 6.

Step 5: Get Result Set

Now you are ready to retrieve the result set that is returned from the query statement. This step involves associating the result set with the query, which is done by setting the Query property on the ODBCResultSet object (Set res.Query = qry). Next, execute the query by calling the Execute method on the result set (res.Execute).

‘Code to execute SQL query
‘ and get results set

Set res.Query = qry

res.Execute

Check for any errors with the Query execution with code similar to that found in Figure 7.


Step 6: Work with the Result Set

Now you are ready to work with the virtual table that is returned as the result set. Several methods can be used to access column values in the result set that is returned from the SQL statement. These methods include GetValue(column, [variable]), IsValueAltered(column), IsValueNull(column), and SetValue(column, value) are used to access specific column values to check column properties. Use the GetValue() method to retrieve the values that have been returned (res.GetValue(“lstnam”)). The call to the GetValue method is passed as a parameter to the Fieldsettext() method that is called on the notesuidocument object (uidoc) that you created in step 2 (Figure 8).

By calling the Fieldsettext() method on the uidoc object, you can place the returned results to the correct fields on the form. Fieldsettext() takes two parameters. The first parameter is the field name on the Notes form, and the second parameter is the value returned from the DB2/400 table based on the field name specified such as lstnam or init.

You have now returned the customer data from DB2/400 and displayed it to the user, so put the cursor back in the Customer Number field, close the result set, and disconnect from the data source (Figure 9).

How to Trace and Debug LS:DO

Chances are your agent will not be 100 percent error free on first execution, so you need to know where to look for error messages. Look at the log associated with the agent by clicking on the agent and selecting Agent/Log from the pull-down menu in Domino Designer. A more helpful place to look though is in the Agent Manager job log on the AS/400. Look for the name AMGR/QNOTES/nnnnnn where nnnnnn is the job number of the agent you ran.

You can also debug your agent by selecting File/Tools/Debug LotusScript from the pull-down menu in the Domino Designer client or the Notes client. This turns on the LotusScript debugger. Now trigger your agent again, and the debugger lets you step through your code.

More Resources on LS:DO

Coding an agent in LS:DO is fairly straightforward. The three classes, ODBCConnection, ODBCQuery, and ODBCResultSet are detailed very well in the Redbook Enterprise Integration with Domino.Connect. All of the properties that can be set for each of the classes along with the numerous methods that can be invoked on each class are outlined in Chapter 4: LotusScript Data Objects and ODBC. For information specific to implementing LS:DO on the AS/400, see Chapter 5: LotusScript: Data Object in Lotus Domino for AS/400: Integration with Enterprise Applications.

I hope you have found this article to be helpful in your venture to access DB2/400 database files from the Domino environment. I’ll leave you with some helpful Web sites for you to visit. Iris Associates’ www.notes.net contains links to documentation on LS:DO. You can view the help database online or download it to your Notes client. Expand the Designer link at www.notes.net/notesua.nsf/Task?OpenView to access the help files. Don’t forget about the Domino Enterprise Integration Web site, www.lotus.com/dominoei. This Web site is invaluable for information about connecting into back-end systems from the Domino environment. For AS/400-specific information, refer to either the PartnerWorld for Developers AS/400 Domino site at www.as400.ibm.com/ developer/domino, or check out the Lotus Domino for AS/400 Web site at www.as400.ibm.com/domino. Happy surfing!

REFERENCES AND RELATED MATERIALS

• DB2 for OS/400 SQL Call Level Interface, SC41-4806


Access_DB2-_400_Data_with_LS-_DO06-00.png 456x318

Figure 1: The Domino application populates this form with DB2/400 data.

Figure 2: Call the LS:DO agent through an action button.

Call con.ConnectTo(“SYSTEMA”,”DOMINOUSER”, “DOMINO1”)

Figure 3: Connect to the AS/400.

'Check to see if connection was successful, if not print out error message by calling the
'GetExtendedMessage method on our connection object

If con.geterror<>DBstsSuccess Then

Messagebox con.GetExtendedErrorMessage,, "Could not make

connection to AS/400 system"

Exit Sub


Access_DB2-_400_Data_with_LS-_DO06-01.png 486x286

Else

End If

Figure 4: Inform the user of a failed connection.

'Get Customer Number from Cusnum field

Qry.SQL = "select * from QIWS.QCUSTCDT where Cusnum="+

Uidoc.fieldgettext("CustomerNumber")

Figure 5: Build the SQL statement.

'Check query to see if SQL statement built successfully,
if not print out an error message and
'Disconnect by calling the Disconnect method on the ODBCConnection object (con)

If qry.geterror<>DBstsSuccess Then

Messagebox qry.GetExtendedErrorMessage,,"SQL Error"

con.Disconnect

Exit Sub

Else

End If

Figure 6: Inform the user of an invalid SQL statement.

' Check for errors and if there are any, we print out a valid error message by calling
'The GetExtendedErrorMessage method on the ODBCResultSet object (res)

If res.GetError <> DBstsSuccess Then

Messagebox res.GetExtendedErrorMessage,,"Result Error"

res.Close(DB_CLOSE)
con.Disconnect
Exit Sub

End If

Figure 7: Inform the user if any errors have occurred with the result set.

'First we check to see if there is a result set

Else

If Not res.IsResultSetAvailable Then

Messagebox " No values found for your query"
'Now we process the result set using the GetValue() method on our ODBCResultSet, res

Else

Call uidoc.Fieldsettext("LastName",res.GetValue("lstnam"))

Call uidoc.Fieldsettext("Initials",res.GetValue("init"))

Call uidoc.Fieldsettext("Street",res.GetValue("street"))

Call uidoc.Fieldsettext("City",res.GetValue("city"))

Call uidoc.Fieldsettext("State",res.GetValue("state"))

Call uidoc.Fieldsettext("ZipCode",res.GetValue("zipcod"))

End If

End If

Figure 8: Set the Notes document field values from the value of the result set.

'Put cursor back in Customer Number field

Call uidoc.GotoField("CustomerNumber")
'close result set - it is very important to remember to close our result set

res.Close(DB_CLOSE)
'disconnect from data source - again, a vital step

con.Disconnect

How to Read AS/400 Data Using a Notes Agent 1

Figure 9: Close the result set, and disconnect from the data source.


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

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: