Sidebar

Referential Integrity & Triggers in DB2/400

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

Brief: Enforcing database integrity used to mean coding logic into every application program. V3R1 allows you to enforce these rules at the database level by using referential integrity and triggers. This article discusses design considerations and shows you practical examples for implementing these powerful new capabilities into your database.

As an AS/400 programmer, you're well aware that something as simple as an upsurge in sales can trigger all kinds of changes to your business applications. All of a sudden, you need to address problems in the order entry system. Data integrity needs to be enforced between the master files for the order-entry system. The Accounts Receivable department wants greater flexibility in managing customers who exceed their credit limit, and the Sales department wants to implement special offers to promote a new group of products.

Each of these problem areas represents the statement of a business rule that needs to be enforced by the order-entry system. As a programmer, it's your job to implement a solution that enforces these business rules.

In the past, you enforced business rules by adding code to existing application programs or by writing new programs to handle the functions. However, this can be a complex, lengthy process, especially if the number of existing programs that modify and manipulate the data in these files is large or the application is implemented on multiple platforms. In addition, application program code is just one of a variety of mechanisms available to modify and manipulate data; therefore, it offers only a partial solution to enforcing business rules.

Referential integrity and triggers offer an alternative that enforces these business rules no matter what mechanism is used to modify and manipulate data. They are two of the most important enhancements to DB2/400 in V3R1, so every AS/400 programmer needs to know how to use them effectively. This article shows you how. It begins by defining referential integrity and triggers at a conceptual level. After that, the article presents a detailed case study of the scenario just presented. Finally, you'll learn how to use referential integrity and triggers to solve the problems that the case study presents. Along the way, you'll become familiar with the key concepts you need to understand to effectively implement these two new database functions.

Referential Integrity

The first database function we'll examine is called referential integrity. Referential integrity lets database developers ensure that data is consistent across physical files in the DB2/400 database. With referential integrity, developers define constraints or rules, also called referential constraint relationships. These constraints are applied at the physical file level, ensuring that every record in file A has a corresponding record in file B. Any program that inserts, deletes, or updates a record will be prevented from doing so if it violates a constraint. This makes referential integrity a powerful enhancement to OS/400.

In the past, database administrators had to anticipate all the mechanisms through which database consistency could be compromised. Then they wrote application logic to handle each potential compromise. Now that DB2/400 has referential integrity, it can automatically enforce database constraints no matter what mechanism is used to manipulate data.

Triggers

The second function we'll examine is called triggers. The DB2/400 trigger facility lets developers automatically invoke a program whenever a change is made to a physical file or table. A trigger fires in response to a change made to a file-a record is added, changed, or deleted. This makes triggers a powerful tool to help enforce business rules.

Case Study: Order Entry System

Once we know that both referential integrity and triggers are available, it's surprising how it colors our view of the problems we mentioned in the order- entry system. Let's look at our case study problems in more detail.

Our order-entry system is comprised of a large number of complex programs, but it only has four physical files. These order-entry files are Order Header (ORDR_HDR), Order Detail (ORDR_DTL), Item Master (ITEM_MSTR), and Customer Master (CUST_MSTR). The DDS for these files is included in Figures 1-4 along with some sample data.

The first problem in our case study involves data integrity. Data integrity needs to be enforced between all of the order-entry master files. Unfortunately, the Order Header file contains orders for customer number 01102, but there's no matching customer number in the customer master file. And that's not all. The Order Detail file lists orders for item number 1209, but the item master file has no matching item number. To complicate matters further, order number 435 in the Order Detail file has no matching order header record. Not only do we need to fix these problems, but the data integrity of the entire system needs to be enforced so these conditions cannot recur. Previously, enforcing this data integrity would require a lot of analysis to identify those programs that updated any of the files and a great deal of programming to enforce the integrity of any access to them. This is a primary candidate for implementing the referential integrity functions of DB2/400.

Our second problem deals with the Accounts Receivable department. They need greater control to manage customers who exceed their credit limit. Simply flagging a customer's order in a report is no longer sufficient. When a customer exceeds his credit limit, the Accounts Receivable department should be notified immediately, so someone can review each situation individually and take appropriate action. Previously, identifying the myriad of conditions in which a customer might exceed a set credit limit could result in a long series of programming changes. This business requirement is a prime candidate for DB2/400's trigger facility.

Our third problem centers around a new policy from the Sales department. They want to implement special offers to promote a new group of products. Since these promotional offers are going to have a special introductory price, there is a maximum number of promotions a customer can purchase on each order. Such simple requirements, in the past, would have necessitated a complete revamping of the order entry system, and some detailed coding to implement the promotions. But this requirement can be easily solved just by using DB2/400's trigger function.

Referential Integrity

First, turn your attention to the data inte-grity problems between the Customer Master and Order Header files, the Item Master and Order Detail files, and the Order Header and Order Detail files. You can add one referential constraint relationship to the Order Header file and two to the Order Detail file to eliminate the data integrity problems in the four files used by the order-entry system.

Referential integrity will then ensure the following:

o Every customer number in the Order Header file has a matching Customer Number in the customer master file.

o Every item number in the Order Detail file has a matching item number in the Item Master file.

o Every order number in the Order Detail file has a matching order number in the Order Header file.

But how does this work? Before delving into the detailed solution, let's explore referential integrity in more detail to provide the proper foundation to understand the solution.

Referential Constraint Relationships

Referential integrity identifies relationships between files by establishing constraints between them. This is known as a referential constraint relationship, and it works between two physical files. The primary file in the relationship is called the parent file. The secondary file in the relationship is called the dependent file.

The relationship between the parent file and the dependent file is defined by the keyed access paths for these files. A parent key is a keyed access path with unique keys for the parent file. A foreign key is a keyed access path for the dependent file, and it may or may not have unique keys. The parent key and foreign key must have identical attributes.

The referential constraint relationship states that every non-null foreign key value from the dependent file must have a matching parent key value from the parent file. (Null means there is no value in a field, which is different than zero or blank. It is a special way for a relational database to represent missing information.) In other words, a record in the dependent file cannot exist unless its foreign key value has a matching parent key value in a record from the parent file.

You define a referential constraint relationship by adding a referential constraint to the dependent file. The constraint then becomes an attribute of the file to which it is added. To add a constraint to a dependent file, the parent file, parent key access path, and dependent file must already exist.

When the constraint is added, it is given a name. At the same time, the foreign key is defined, the parent key and parent file are identified, and any constraint rules are specified.

Referential Constraint Rules

Referential constraint rules define the action that occurs when a file is altered. These rules focus on the three normal functions of database management: record insertion (add), record update or modification (change), and record deletion (delete). Specific rules are defined for each function. Referential constraint rules can apply to records in both the parent file and the dependent file. The "Referential Integrity Rules" sidebar (page 116) shows all the actions allowed when records are inserted, updated, or deleted in a dependent or parent file.

Command Interface

If you need to see what constraints have already been placed on a file, you can use the Display File Description (DSPFD) command to list them as shown in 5. Other specific commands to add and manage referential constraints include.

If you need to see what constraints have already been placed on a file, you can use the Display File Description (DSPFD) command to list them as shown in Figure 5. Other specific commands to add and manage referential constraints include.

 o ADDPFCST-Add Physical File Constraint. o RMVPFCST-Remove Physical File Constraint. o CHGPFCST-Change Physical File Constraint. o WRKPFCST-Work with Physical File Constraint. o EDTCPCST-Edit Check Pending Constraint. o DSPCPCST-Display Check Pending Constraint. 

These commands are grouped together on the OS/400 Constraint Command Menu accessed with GO CMDCST. SQL statements that perform similar functions are also supplied in OS/400 but are not covered in this article.

Implementation Considerations

You can add referential constraints only to single-member physical files; multimember files are not supported. Referential constraints cannot be added to AS/400 system files, source files, or program-described files. A referential constraint also cannot cross an auxiliary storage pool (ASP) boundary. The limit is 300 referential constraint relationships per file. In our order-entry case study, this limitation is obviously not going to be a problem.

So what happens if the referential integrity of a file is breached? If an application program takes an action that violates a referential constraint, an escape message and status code that the programmer must handle are passed back to the program. In other words, it's the same process you've grown accustomed to. When an application program attempts a database activity that does not successfully complete, the application program terminates unless the programmer handles the exception.

This type of control is exactly what we need to implement in order to enforce data integrity to solve our first order-entry problem.

Order Header/Customer Master Data Integrity

To ensure that every customer number in the Order Header file has a matching customer number in the Customer Master File (CUST_MSTR), we'll add the following referential constraint to the Order Header file to provide data integrity for the customer number (CUS_NBR) field:

 ADDPFCST FILE(ORDR_HDR) + TYPE(*REFCST) KEY(CUS_NBR) + CST(CUS_NBR_CST) + PRNFILE(CUST_MSTR) + PRNKEY(CUS_NBR) + DLTRULE(*NOACTION) + UPDRULE(*NOACTION) 

The dependent file is ORDR_HDR, the foreign key is CUS_NBR, and the constraint name is CUS_NBR_CST. The parent file is CUST_MSTR, the parent key is CUS_NBR, and the referential constraint rule for update and delete is *NOACTION.

Order Detail/Item Master Data Integrity

To ensure that every item number in the Order Detail file has a matching item number in the Item Master file, we add the following referential constraint to the Order Detail file to provide data integrity for the item number (ITEM_NBR) field:

 ADDPFCST FILE(ORDR_DTL) + TYPE(*REFCST) KEY(ITEM_NBR) + CST(ITEM_NBR_CST) + PRNFILE(ITEM_MSTR) + PRNKEY(ITEM_NBR) + DLTRULE(*NOACTION) + UPDRULE(*NOACTION) 

The dependent file is ORDR_DTL, the foreign key is ITEM_NBR, and the constraint name is ITEM_NBR_CST. The parent file is ITEM_MSTR, the parent key is ITEM_NBR, and the referential constraint rules for update and delete are *NOACTION.

Order Header/Order Detail Data Integrity

Finally, to ensure that every order number in the Order Detail file has a matching order number in the Order Header file, we add the following referential constraint to the Order Detail file to provide data integrity for the order number (ORD_NBR):

 ADDPFCST FILE(ORDR_DTL) + TYPE(*REFCST) KEY(ORD_NBR) + CST(ORD_NBR_CST) + PRNFILE(ORDR_HDR) + PRNKEY(ORD_NBR) + DLTRULE(*NOACTION) + UPDRULE(*NOACTION) 

The dependent file is ORDR_DTL, the foreign key is ORD_NBR, and the constraint name is ORD_NBR_CST. The parent file is ORDR_HDR, the parent key is ORD_NBR, and the referential constraint rules for update and delete are *NO-ACTION.

Implementation Results

The result of these three actions provides the required data integrity for the order entry system. It guarantees that the following business rules will be enforced:

o Every customer number in the Order Header file will have a matching customer number in the Customer Master file.

o Every item number in the Order Detail file will have a matching item number in the Item Master file.

o Every order number in the Order Detail file will have a matching order number in the Order Header file.

Note that the no program logic was changed, and no actual coding has taken place. All of the integrity checks are implemented at the database level and are now an integral part of the database. Applications that are accessing the database-whether they are native to the AS/400 or client/server applications on a networked PC-will be subjected to these same constraints.

Triggers

Let's move on to the next two problems with the order entry system: the credit limit problem and the sales promotion problem.

When a customer exceeds his credit limit, the Accounts Receivable department needs to be notified immediately. To accomplish this a trigger will be added to the Customer Master file that will fire after any record in the Customer Master file is updated. This trigger will activate a program which compares the customer's credit limit to the balance owed. If the balance owed exceeds the credit limit, this trigger program will send a message to the Accounts Receivable department so someone can review the situation and take appropriate action.

The same trigger facility will work for the sales promotion problem. Two triggers are added to the Order Detail file, to fire after any record in the Order Detail file is updated. These triggers will activate a program which will compare the quantity ordered for an item number to a control number for that item number in the Item Master file. The control number is the maximum number of units that can be ordered for a given item number. If the control number is zero, there is no limit on the item. If the quantity ordered exceeds the control number, the trigger program will send a message to the Sales department so appropriate action can be taken.

How does this work? Before implementing the detailed solution let's explore triggers to provide the proper foundation to understand the solution.

Trigger Components

A trigger is made up of four components: a base file, a trigger event, a trigger time, and a trigger program.

The base file is the physical file to which the trigger is added as an attribute.

The trigger event is the condition that causes the trigger to fire. The trigger event can be the insertion (add), update (change), or deletion (delete) of a record in the base physical file or in a logical file built over the base physical file.

The trigger time determines when the trigger fires, causing the trigger program to run. The trigger time can be immediately before or immediately after the trigger event takes place.

The trigger program is a program that runs at the trigger time and performs the action desired by the firing of the trigger. A trigger program can contain SQL statements, high-level language statements, and most OS/400 commands.

When the trigger program is called, it receives parameter information that includes the identification of the file and record that are currently being accessed, along with a before and/or after image of the record being accessed.

Adding and Removing Triggers

A trigger is added to a physical file or table with the Add Physical File Trigger (ADDPFTRG) command and can be removed with the Remove Physical File Trigger (RMVPFTRG) command. SQL does not support triggers because the SQL standard is still in development.

Implementation Considerations

Each physical file can have a total of six triggers defined for it. The reason for this limit becomes clear when you consider that there are three trigger events: insert, update, and delete. Each trigger event can have two trigger times-one before and one after the trigger event. Therefore, three trigger events multiplied by two trigger times equals a maximum of six triggers per file.

Even though triggers are added to physical files, they can also be fired by an insertion, update, or deletion in a logical file built over the physical file that contains the trigger.

The firing of one trigger can cause another trigger to fire, which can result in cascading or nested triggers, looping triggers, and recursive triggers. Since all of these situations are allowed, infinite loops are possible. Obviously, any of the above situations can have a severe impact on system resources. Careful planning, design, and testing will help to avoid these situations. DB2/400 allows a maximum of 200 levels of nested triggers.

Triggers are a relatively simple but powerful DB2/400 function. The impact they can have on performance ranges from minimal when a single trigger is fired to perform a simple operation, to significant when firing one trigger causes a series of triggers to be fired in a trigger cascade.

However, the performance impact of triggers should be minimal if an operation is removed from the application program level and implemented in a trigger program at the database level. This assumes that the same operation performed at the application level will be performed in the trigger program at the database level.

Triggers can be used to improve performance in client/server environments by reducing the conversation flow between the client and the server.

Now that you understand the theory and concepts behind referential integrity and triggers, we can review the solutions to our case-study problems which use the triggers facility.

Credit-Limit Management

To provide better management of customers that exceed their credit limit, we add the following trigger to the Customer Master file which fires after a record in the Customer Master file is updated:

 ADDPFTRG FILE(CUST_MSTR) + TRGTIME(*AFTER) + TRGEVENT(*UPDATE) + PGM(CHK_CREDIT) 

The trigger time is *AFTER and the trigger event is *UPDATE. The trigger program (CHK_CREDIT) compares the customer's credit limit to the balance owed. If the balance owed exceeds the credit limit, the trigger program sends a message to the Accounts Receivable department.

Controlling Promotions

To provide management of the Sales department's new promotional offerings, we add two triggers to the Order Detail file which fire after a record in the Order Detail file is added or updated.

 ADDPFTRG FILE(ORDR_DTL) + TRGTIME(*AFTER) + TRGEVENT(*UPDATE) + PGM(CHK_CONTRL) ADDPFTRG FILE(ORDR_DTL) + TRGTIME(*AFTER) + TRGEVENT(*INSERT) + PGM(CHK_CONTRL) 

The trigger time is *AFTER and the trigger events are *INSERT and *UPDATE. The trigger program (CHK_CONTROL) compares the quantity ordered for an item number to the control number for that item number in the item master file. The control number is the maximum number of units that can be ordered for a given item number.

Advanced Database Functions

This case study shows how referential integrity and triggers allow business rules to be defined and implemented at the database level.

Referential integrity is a mechanism that ensures data integrity between two physical files in a relational database. It deals specifically with data integrity and nothing else.

Triggers, on the other hand, can be used to perform any function, limited only by your imagination and programming ability. For example, triggers can be used to perform a complex data validation process or to generate an audit trail.

The important thing to understand is that the business rules for the order- entry system were implemented at the database level-not at the application- program level. This was easily accomplished by adding the referential constraints and triggers to three of the physical files used by the order-entry system. It was not necessary to duplicate code to enforce the business rules in each application program that modifies or manipulates data in the order-entry files. Thus referential integrity and triggers, two of the new, advanced database functions found in DB2/400, can provide a very high level of programmer productivity.

Skip Marchesani is the author of "DB2/400: The New AS/400 Database." He can be reached through Midrange Computing.

REFERENTIAL INTEGRITY RULES

Record Insertion Rule

A record can be inserted in a dependent file only when its foreign key is null, or there is a matching parent key in the parent file.

Record Update Rules No Action (default)

A parent key in a parent-file record cannot be changed if the original parent key matches a foreign key in a dependent-file record.

A foreign key in a dependent-file record cannot be changed unless there is a parent-file record with a parent key that matches the changed foreign key.

Restrict -Same as No Action

The difference between the "No Action" and the "Restrict" rule is in the timing of the integrity check. If Restrict has been specified as the rule for an update, the integrity check is immediate; it takes place before the update request is performed. If No Action has been specified as the rule for an update, the integrity check is not immediate. Other events (namely, a nested transaction group, which is one or more database operations that occur before an integrity check is made for a referential constraint on a dependent file) are allowed to occur. The integrity check takes place at the end of the update request or nested transaction group.

Record Deletion Rules No Action (default)

A parent-file record cannot be deleted if its parent key matches a foreign key in a dependent-file record.

Restrict -Same as No Action

The difference between No Action and Restrict is (as in the update rule) a timing of the integrity check for the file. Restrict checks the integrity immediately, and No Action delays the check for other activities.

Cascade

The cascade option for record deletion is extremely powerful. Cascading a deletion on a parent-file record causes the deletion of all dependent-file records with a matching foreign key.

Set Null

The deletion of a parent-file record causes all dependent-file records with a matching foreign key to have their null-capable fields within the foreign key set to null.

At least one field in the foreign key of the dependent file must be null- capable. (DDS allows the definition of null-capable fields with the use of the Allow Null Value (ALLWNULL) keyword.)

Set Default

The deletion of a parent-file record causes all dependent-file records with a matching foreign key to have all fields within the foreign key set to their default values.

The default foreign key in the dependent file must have a matching parent key in the parent file.

In DB2/400, the default value used for a field is the system default value unless the file has been described with field-level default values using the Default (DFT) keyword in DDS. The system default values are zero (00) for non- null numeric fields, blank for non-null character fields, and null for all null-capable fields.

Implementation Considerations

Some of the referential constraint rules require that the parent and dependent files be journaled. This is done to ensure data integrity and to enable the DB2/400 Database Manager to automatically recover from or roll back a transaction group that has not successfully completed.

Another important issue to consider is the current state of the database in which referential integrity will be implemented. If the files in the database are not designed correctly, it may be difficult or even impossible to implement referential integrity. Correctly designed master files should contain single fact records that are in third normal form. This structure is readily adaptable to referential integrity.

Certain referential integrity functions can be resource-intensive. Delete Cascade, Delete Set Null, and Delete Set Default may require significant system resources if many records in a dependent file match a deleted record in a parent file. Combining referential integrity with trigger functions should also be approached with caution (see the discussion on one trigger firing another in this article). You should carefully analyze each referential integrity and trigger combination to avoid long cycles or infinite loops.

Referential integrity is a complex and sophisticated DB2/400 function. The performance impact of referential integrity should be minimal if it is only used to replace similar functions currently implemented at the application level. The implementation of additional referential integrity functions at the database level may require additional system resources and could have a negative impact on system performance.


Referential Integrity & Triggers in DB2/400

Figure 1 Order Header File

 *=============================================================== * To compile: * * CRTPF FILE(XXX/ORDR_HDR) SRCFILE(XXX/QDDSSRC) * *=============================================================== *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 A UNIQUE A R HDR_REC TEXT('Order Header Record') A ORD_NBR 5 0 TEXT('Order Number') A CUS_NBR 5 0 TEXT('Customer Number') A ORD_AMT 9 2 TEXT('Order Amount') A K ORD_NBR 
Referential Integrity & Triggers in DB2/400

Figure 2 Order Detail File

 *=============================================================== * To compile: * * CRTPF FILE(XXX/ORDR_DTL) SRCFILE(XXX/QDDSSRC) * *=============================================================== *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 A UNIQUE A R DTL_REC TEXT('Order Detail Record') A ORD_NBR 5 0 TEXT('Order Number') A LINE 3 0 TEXT('Line Number') A ITEM_NBR 5 0 TEXT('Item Number') A QUAN 5 0 TEXT('Quantity') A K ORD_NBR A K LINE 
Referential Integrity & Triggers in DB2/400

Figure 3 Item Master File

 *=============================================================== * To compile: * * CRTPF FILE(XXX/ITEM_MSTR) SRCFILE(XXX/QDDSSRC) * *=============================================================== *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 A UNIQUE A R ITEM_REC TEXT('Item Master Record') A ITEM_NBR 5 0 TEXT('Item Number') A DESC 30 TEXT('Description') A PRICE 7 2 TEXT('Price') A ON_HAND 7 0 TEXT('On Hand') A CNTRL 5 0 TEXT('Control Number') A K ITEM_NBR 
Referential Integrity & Triggers in DB2/400

Figure 4 Customer Master File

 *=============================================================== * To compile: * * CRTPF FILE(XXX/CUST_MSTR) SRCFILE(XXX/QDDSSRC) * *=============================================================== *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 A UNIQUE A R CUST_REC TEXT('Customer Record') A CUS_NBR 5 0 TEXT('Customer Number') A CUS_NAME 30 TEXT('Customer Name') A CRED_LMT 7 2 TEXT('Credit Limit') A BAL_OWE 7 2 TEXT('Balance Owed') A K CUS_NBR 
Referential Integrity & Triggers in DB2/400

Figure 5 Viewing Constraints with the DSPFD Command

 5763SS1 V3R1ME 940909 Display File Description File . . . . . . . . . . . : ORDR_DTL Library . . . . . . . . . : *LIBL Type of information . . . . : *CST File attributes . . . . . . : *ALL System . . . . . . . . . . : *LCL Processor . . . . . . . . . : IBM AS/400 Display File Description Processor Constraint Description Referential Constraint Constraint . . . . . . . . . . . . . . : CST ORD_NBR_CST Type . . . . . . . . . . . . . . . . : TYPE *REFCST Check pending . . . . . . . . . . . . : NO Constraint state . . . . . . . . . . : STATE ESTABLISHED *ENABLED Parent File Description File . . . . . . . . . . . . . . . . : PRNFILE ORDR_HDR Library . . . . . . . . . . . . . . : LIB ARLIB Parent key . . . . . . . . . . . . . : PRNKEY ORD_NBR Foreign key . . . . . . . . . . . . . . : FRNKEY ORD_NBR Delete rule . . . . . . . . . . . . . . : DLTRULE *NOACTION Update rule . . . . . . . . . . . . . . : UPDRULE *NOACTION 
BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

RESOURCE CENTER

  • WHITE PAPERS

  • WEBCAST

  • TRIAL SOFTWARE

  • Mobile Computing and the IBM i

    SB ASNA PPL 5450Mobile computing is rapidly maturing into a solid platform for delivering enterprise applications. Many IBM i shops today are realizing that integrating their IBM i with mobile applications is the fast path to improved business workflows, better customer relations, and more responsive business reporting.

    This ASNA whitepaper takes a look at mobile computing for the IBM i. It discusses the different ways mobile applications may be used within the enterprise and how ASNA products solve the challenges mobile presents. It also presents the case that you already have the mobile programming team your projects need: that team is your existing RPG development team!

    Get your copy today!

  • Automate IBM i Operations using Wireless Devices

    DDL SystemsDownload the technical whitepaper on MANAGING YOUR IBM i WIRELESSLY and (optionally) register to download an absolutely FREE software trail. This whitepaper provides an in-depth review of the native IBM i technology and ACO MONITOR's advanced two-way messaging features to remotely manage your IBM i while in or away from the office. Notify on-duty personnel of system events and remotely respond to complex problems (via your Smartphone) before they become critical-24/7. Problem solved!

    Order your copy here.

  • DR Strategy Guide from Maxava: Brand New Edition - now fully updated to include Cloud!

    SB Maxava PPL 5476PRACTICAL TOOLS TO IMPLEMENT DISASTER RECOVERY IN YOUR IBM i ENVIRONMENT

    CLOUD VS. ON-PREMISE?
    - COMPREHENSIVE CHECKLISTS
    - RISK COST CALCULATIONS
    - BUSINESS CASE FRAMEWORK
    - DR SOLUTIONS OVERVIEW
    - RFP BUILDER
    Download your free copy of DR Strategy Guide for IBM i from Maxava today.

     

  • White Paper: Node.js for Enterprise IBM i Modernization

    SB Profound WP 5539

    If your business is thinking about modernizing your legacy IBM i (also known as AS/400 or iSeries) applications, you will want to read this white paper first!

    Download this paper and learn how Node.js can ensure that you:
    - Modernize on-time and budget - no more lengthy, costly, disruptive app rewrites!
    - Retain your IBM i systems of record
    - Find and hire new development talent
    - Integrate new Node.js applications with your existing RPG, Java, .Net, and PHP apps
    - Extend your IBM i capabilties to include Watson API, Cloud, and Internet of Things


    Read Node.js for Enterprise IBM i Modernization Now!

     

  • 2020 IBM i Marketplace Survey Results

    HelpSystems

    This year marks the sixth edition of the popular IBM i Marketplace Survey Results. Each year, HelpSystems sets out to gather data about how businesses use the IBM i platform and the IT initiatives it supports. Year over year, the survey has begun to reveal long-term trends that give insight into the future of this trusted technology.

    More than 500 IBM i users from around the globe participated in this year’s survey, and we’re so happy to share the results with you. We hope you’ll find the information interesting and useful as you evaluate your own IT projects.

  • AIX Security Basics eCourse

    Core Security

    With so many organizations depending on AIX day to day, ensuring proper security and configuration is critical to ensure the safety of your environment. Don’t let common threats put your critical AIX servers at risk. Avoid simple mistakes and start to build a long-term plan with this AIX Security eCourse. Enroll today to get easy to follow instructions on topics like:

    • Removing extraneous files
    • Patching systems efficiently
    • Setting and validating permissions
    • Managing service considerations
    • Getting overall visibility into your networks

     

  • Developer Kit: Making a Business Case for Modernization and Beyond

    Profound Logic Software, Inc.

    Having trouble getting management approval for modernization projects? The problem may be you're not speaking enough "business" to them.

    This Developer Kit provides you study-backed data and a ready-to-use business case template to help get your very next development project approved!

  • What to Do When Your AS/400 Talent Retires

    HelpSystemsIT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators is small.

    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:

    • Why IBM i skills depletion is a top concern
    • How leading organizations are coping
    • Where automation will make the biggest impact

     

  • IBM i Resources Retiring?

    SB HelpSystems WC GenericLet’s face it: IBM i experts and RPG programmers are retiring from the workforce. Are you prepared to handle their departure?
    Our panel of IBM i experts—Chuck Losinski, Robin Tatam, Richard Schoen, and Tom Huntington—will outline strategies that allow your company to cope with IBM i skills depletion by adopting these strategies that allow you to get the job done without deep expertise on the OS:
    - Automate IBM i processes
    - Use managed services to help fill the gaps
    - Secure the system against data loss and viruses
    The strategies you discover in this webinar will help you ensure that your system of record—your IBM i—continues to deliver a powerful business advantage, even as staff retires.

     

  • Backup and Recovery Considerations for Security Data and Encrypted Backups

    SB PowerTech WC GenericSecurity expert Carol Woodbury is joined by Debbie Saugen. Debbie is an expert on IBM i backup and recovery, disaster recovery, and high availability, helping IBM i shops build and implement effective business continuity plans.
    In today’s business climate, business continuity is more important than ever. But 83 percent of organizations are not totally confident in their backup strategy.
    During this webinar, Carol and Debbie discuss the importance of a good backup plan, how to ensure you’re backing up your security information, and your options for encrypted back-ups.

  • Profound.js: The Agile Approach to Legacy Modernization

    SB Profound WC GenericIn this presentation, Alex Roytman and Liam Allan will unveil a completely new and unique way to modernize your legacy applications. Learn how Agile Modernization:
    - Uses the power of Node.js in place of costly system re-writes and migrations
    - Enables you to modernize legacy systems in an iterative, low-risk manner
    - Makes it easier to hire developers for your modernization efforts
    - Integrates with Profound UI (GUI modernization) for a seamless, end-to-end legacy modernization solution

     

  • Data Breaches: Is IBM i Really at Risk?

    SB PowerTech WC GenericIBM i is known for its security, but this OS could be more vulnerable than you think.
    Although Power Servers often live inside the safety of the perimeter firewall, the risk of suffering a data leak or data corruption remains high.
    Watch noted IBM i security expert Robin Tatam as he discusses common ways that this supposedly “secure” operating system may actually be vulnerable and who the culprits might be.

    Watch the webinar today!

     

  • Easy Mobile Development

    SB Profound WC GenericWatch this on-demand webinar and learn how to rapidly and easily deploy mobile apps to your organization – even when working with legacy RPG code! IBM Champion Scott Klement will demonstrate how to:
    - Develop RPG applications without mobile development experience
    - Deploy secure applications for any mobile device
    - Build one application for all platforms, including Apple and Android
    - Extend the life and reach of your IBM i (aka iSeries, AS400) platform
    You’ll see examples from customers who have used our products and services to deliver the mobile applications of their dreams, faster and easier than they ever thought possible!

     

  • Profound UI: Unlock True Modernization from your IBM i Enterprise

    SB Profound PPL 5491Modern, web-based applications can make your Enterprise more efficient, connected and engaged. This session will demonstrate how the Profound UI framework is the best and most native way to convert your existing RPG applications and develop new modern applications for your business. Additionally, you will learn how you can address modernization across your Enterprise, including databases and legacy source code, with Profound Logic.

  • Node Webinar Series Pt. 1: The World of Node.js on IBM i

    Profound Logic Software, Inc.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.

    Part 1 will teach you what Node.js is, why it's a great option for IBM i shops, and how to take advantage of the ecosystem surrounding Node.

    In addition to background information, our Director of Product Development Scott Klement will demonstrate applications that take advantage of the Node Package Manager (npm).

  • 5 New and Unique Ways to Use the IBM i Audit Journal

    SB HelpSystems ROBOT GenericYou must be asking yourself: am I doing everything I can to protect my organization’s data? Tune in as our panel of IBM i high availability experts discuss:


    - Why companies don’t test role swaps when they know they should
    - Whether high availability in the cloud makes sense for IBM i users
    - Why some organizations don’t have high availability yet
    - How to get high availability up and running at your organization
    - High availability considerations for today’s security concerns

  • Profound.js 2.0: Extend the Power of Node to your IBM i Applications

    SB Profound WC 5541In this Webinar, we'll demonstrate how Profound.js 2.0 enables you to easily adopt Node.js in your business, and to take advantage of the many benefits of Node, including access to a much larger pool of developers for IBM i and access to countless reusable open source code packages on npm (Node Package Manager).
    You will see how Profound.js 2.0 allows you to:

    • Provide RPG-like capabilities for server-side JavaScript.
    • Easily create web and mobile application interfaces for Node on IBM i.
    • Let existing RPG programs call Node.js modules directly, and vice versa.
    • Automatically generate code for Node.js.
    • Automatically converts existing RPGLE code into clean, simplified Node.js code.

    Download and watch today!

     

  • Make Modern Apps You'll Love with Profound UI & Profound.js

    SB Profound WC 5541Whether you have green screens or a drab GUI, your outdated apps can benefit from modern source code, modern GUIs, and modern tools.
    Profound Logic's Alex Roytman and Liam Allan are here to show you how Free-format RPG and Node.js make it possible to deliver applications your whole business will love:

    • Transform legacy RPG code to modern free-format RPG and Node.js
    • Deliver truly modern application interfaces with Profound UI
    • Extend your RPG applications to include Web Services and NPM packages with Node.js

     

  • Accelerating Programmer Productivity with Sequel

    SB_HelpSystems_WC_Generic

    Most business intelligence tools are just that: tools, a means to an end but not an accelerator. Yours could even be slowing you down. But what if your BI tool didn't just give you a platform for query-writing but also improved programmer productivity?
    Watch the recorded webinar to see how Sequel:

    • Makes creating complex results simple
    • Eliminates barriers to data sources
    • Increases flexibility with data usage and distribution

    Accelerated productivity makes everyone happy, from programmer to business user.

  • Business Intelligence is Changing: Make Your Game Plan

    SB_HelpSystems_WC_GenericIt’s time to develop a strategy that will help you meet your informational challenges head-on. Watch the webinar to learn how to set your IT department up for business intelligence success. You’ll learn how the right data access tool will help you:

    • Access IBM i data faster
    • Deliver useful information to executives and business users
    • Empower users with secure data access

    Ready to make your game plan and finally keep up with your data access requests?

     

  • Controlling Insider Threats on IBM i

    SB_HelpSystems_WC_GenericLet’s face facts: servers don’t hack other servers. Despite the avalanche of regulations, news headlines remain chock full of stories about data breaches, all initiated by insiders or intruders masquerading as insiders.
    User profiles are often duplicated or restored and are rarely reviewed for the appropriateness of their current configuration. This increases the risk of the profile being able to access data without the intended authority or having privileges that should be reserved for administrators.
    Watch security expert Robin Tatam as he discusses a new approach for onboarding new users on IBM i and best-practices techniques for managing and monitoring activities after they sign on.

  • Don't Just Settle for Query/400...

    SB_HelpSystems_WC_GenericWhile introducing Sequel Data Access, we’ll address common frustrations with Query/400, discuss major data access, distribution trends, and more advanced query tools. Plus, you’ll learn how a tool like Sequel lightens IT’s load by:

    - Accessing real-time data, so you can make real-time decisions
    - Providing run-time prompts, so users can help themselves
    - Delivering instant results in Microsoft Excel and PDF, without the wait
    - Automating the query process with on-demand data, dashboards, and scheduled jobs

  • How to Manage Documents the Easy Way

    SB_HelpSystems_WC_GenericWhat happens when your company depends on an outdated document management strategy?
    Everything is harder.
    You don’t need to stick with status quo anymore.
    Watch the webinar to learn how to put effective document management into practice and:

    • Capture documents faster, instead of wasting everyone’s time
    • Manage documents easily, so you can always find them
    • Distribute documents automatically, and move on to the next task

     

  • Lessons Learned from the AS/400 Breach

    SB_PowerTech_WC_GenericGet actionable info to avoid becoming the next cyberattack victim.
    In “Data breach digest—Scenarios from the field,” Verizon documented an AS/400 security breach. Whether you call it AS/400, iSeries, or IBM i, you now have proof that the system has been breached.
    Watch IBM i security expert Robin Tatam give an insightful discussion of the issues surrounding this specific scenario.
    Robin will also draw on his extensive cybersecurity experience to discuss policies, processes, and configuration details that you can implement to help reduce the risk of your system being the next victim of an attack.

  • Overwhelmed by Operating Systems?

    SB_HelpSystems_WC_GenericIn this 30-minute recorded webinar, our experts demonstrate how you can:

    • Manage multiple platforms from a central location
    • View monitoring results in a single pane of glass on your desktop or mobile device
    • Take advantage of best practice, plug-and-play monitoring templates
    • Create rules to automate daily checks across your entire infrastructure
    • Receive notification if something is wrong or about to go wrong

    This presentation includes a live demo of Network Server Suite.

     

  • Real-Time Disk Monitoring with Robot Monitor

    SB_HelpSystems_WC_GenericYou need to know when IBM i disk space starts to disappear and where it has gone before system performance and productivity start to suffer. Our experts will show you how Robot Monitor can help you pinpoint exactly when your auxiliary storage starts to disappear and why, so you can start taking a proactive approach to disk monitoring and analysis. You’ll also get insight into:

    • The main sources of disk consumption
    • How to monitor temporary storage and QTEMP objects in real time
    • How to monitor objects and libraries in real time and near-real time
    • How to track long-term disk trends

     

     

  • Stop Re-keying Data Between IBM I and Other Applications

    SB_HelpSystems_WC_GenericMany business still depend on RPG for their daily business processes and report generation.Wouldn’t it be nice if you could stop re-keying data between IBM i and other applications? Or if you could stop replicating data and start processing orders faster? Or what if you could automatically extract data from existing reports instead of re-keying? It’s all possible. Watch this webinar to learn about:

    • The data dilemma
    • 3 ways to stop re-keying data
    • Data automation in practice

    Plus, see how HelpSystems data automation software will help you stop re-keying data.

     

  • The Top Five RPG Open Access Myths....BUSTED!

    SB_Profound_WC_GenericWhen it comes to IBM Rational Open Access: RPG Edition, there are still many misconceptions - especially where application modernization is concerned!

    In this Webinar, we'll address some of the biggest myths about RPG Open Access, including:

    • Modernizing with RPG OA requires significant changes to the source code
    • The RPG language is outdated and impractical for modernizing applications
    • Modernizing with RPG OA is the equivalent to "screen scraping"

     

  • Time to Remove the Paper from Your Desk and Become More Efficient

    SB_HelpSystems_WC_GenericToo much paper is wasted. Attempts to locate documents in endless filing cabinets.And distributing documents is expensive and takes up far too much time.
    These are just three common reasons why it might be time for your company to implement a paperless document management system.
    Watch the webinar to learn more and discover how easy it can be to:

    • Capture
    • Manage
    • And distribute documents digitally

     

  • IBM i: It’s Not Just AS/400

    SB_HelpSystems_WC_Generic

    IBM’s Steve Will talks AS/400, POWER9, cognitive systems, and everything in between

    Are there still companies that use AS400? Of course!

    IBM i was built on the same foundation.
    Watch this recorded webinar with IBM i Chief Architect Steve Will and IBM Power Champion Tom Huntington to gain a unique perspective on the direction of this platform, including:

    • IBM i development strategies in progress at IBM
    • Ways that Watson will shake hands with IBM i
    • Key takeaways from the AS/400 days

     

  • Ask the RDi Experts

    SB_HelpSystems_WC_GenericWatch this recording where Jim Buck, Susan Gantner, and Charlie Guarino answered your questions, including:

    • What are the “hidden gems” in RDi that can make me more productive?
    • What makes RDi Debug better than the STRDBG green screen debugger?
    • How can RDi help me find out if I’ve tested all lines of a program?
    • What’s the best way to transition from PDM to RDi?
    • How do I convince my long-term developers to use RDi?

    This is a unique, online opportunity to hear how you can get more out of RDi.

     

  • Node.js on IBM i Webinar Series Pt. 2: Setting Up Your Development Tools

    Profound Logic Software, Inc.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. In Part 2, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Attend this webinar to learn:

    • Different tools to develop Node.js applications on IBM i
    • Debugging Node.js
    • The basics of Git and tools to help those new to it
    • Using NodeRun.com as a pre-built development environment

     

     

  • Inside the Integrated File System (IFS)

    SB_HelpSystems_WC_GenericDuring this webinar, you’ll learn basic tips, helpful tools, and integrated file system commands—including WRKLNK—for managing your IFS directories and Access Client Solutions (ACS). We’ll answer your most pressing IFS questions, including:

    • What is stored inside my IFS directories?
    • How do I monitor the IFS?
    • How do I replicate the IFS or back it up?
    • How do I secure the IFS?

    Understanding what the integrated file system is and how to work with it must be a critical part of your systems management plans for IBM i.

     

  • Expert Tips for IBM i Security: Beyond the Basics

    SB PowerTech WC GenericIn this session, IBM i security expert Robin Tatam provides a quick recap of IBM i security basics and guides you through some advanced cybersecurity techniques that can help you take data protection to the next level. Robin will cover:

    • Reducing the risk posed by special authorities
    • Establishing object-level security
    • Overseeing user actions and data access

    Don't miss this chance to take your knowledge of IBM i security beyond the basics.

     

     

  • 5 IBM i Security Quick Wins

    SB PowerTech WC GenericIn today’s threat landscape, upper management is laser-focused on cybersecurity. You need to make progress in securing your systems—and make it fast.
    There’s no shortage of actions you could take, but what tactics will actually deliver the results you need? And how can you find a security strategy that fits your budget and time constraints?
    Join top IBM i security expert Robin Tatam as he outlines the five fastest and most impactful changes you can make to strengthen IBM i security this year.
    Your system didn’t become unsecure overnight and you won’t be able to turn it around overnight either. But quick wins are possible with IBM i security, and Robin Tatam will show you how to achieve them.

  • How to Meet the Newest Encryption Requirements on IBM i

    SB PowerTech WC GenericA growing number of compliance mandates require sensitive data to be encrypted. But what kind of encryption solution will satisfy an auditor and how can you implement encryption on IBM i? Watch this on-demand webinar to find out how to meet today’s most common encryption requirements on IBM i. You’ll also learn:

    • Why disk encryption isn’t enough
    • What sets strong encryption apart from other solutions
    • Important considerations before implementing encryption

     

     

  • Security Bulletin: Malware Infection Discovered on IBM i Server!

    SB PowerTech WC GenericMalicious programs can bring entire businesses to their knees—and IBM i shops are not immune. It’s critical to grasp the true impact malware can have on IBM i and the network that connects to it. Attend this webinar to gain a thorough understanding of the relationships between:

    • Viruses, native objects, and the integrated file system (IFS)
    • Power Systems and Windows-based viruses and malware
    • PC-based anti-virus scanning versus native IBM i scanning

    There are a number of ways you can minimize your exposure to viruses. IBM i security expert Sandi Moore explains the facts, including how to ensure you're fully protected and compliant with regulations such as PCI.

     

     

  • Fight Cyber Threats with IBM i Encryption

    SB PowerTech WC GenericCyber attacks often target mission-critical servers, and those attack strategies are constantly changing. To stay on top of these threats, your cybersecurity strategies must evolve, too. In this session, IBM i security expert Robin Tatam provides a quick recap of IBM i security basics and guides you through some advanced cybersecurity techniques that can help you take data protection to the next level. Robin will cover:

    • Reducing the risk posed by special authorities
    • Establishing object-level security
    • Overseeing user actions and data access

     

     

     

  • 10 Practical IBM i Security Tips for Surviving Covid-19 and Working From Home

    SB PowerTech WC GenericNow that many organizations have moved to a work from home model, security concerns have risen.

    During this session Carol Woodbury will discuss the issues that the world is currently seeing such as increased malware attacks and then provide practical actions you can take to both monitor and protect your IBM i during this challenging time.

     

  • How to Transfer IBM i Data to Microsoft Excel

    SB_HelpSystems_WC_Generic3 easy ways to get IBM i data into Excel every time
    There’s an easy, more reliable way to import your IBM i data to Excel? It’s called Sequel. During this webinar, our data access experts demonstrate how you can simplify the process of getting data from multiple sources—including Db2 for i—into Excel. Watch to learn how to:

    • Download your IBM i data to Excel in a single step
    • Deliver data to business users in Excel via email or a scheduled job
    • Access IBM i data directly using the Excel add-in in Sequel

    Make 2020 the year you finally see your data clearly, quickly, and securely. Start by giving business users the ability to access crucial business data from IBM i the way they want it—in Microsoft Excel.

     

     

  • HA Alternatives: MIMIX Is Not Your Only Option on IBM i

    SB_HelpSystems_WC_GenericIn this recorded webinar, our experts introduce you to the new HA transition technology available with our Robot HA software. You’ll learn how to:

    • Transition your rules from MIMIX (if you’re happy with them)
    • Simplify your day-to-day activities around high availability
    • Gain back time in your work week
    • Make your CEO happy about reducing IT costs

    Don’t stick with a legacy high availability solution that makes you uncomfortable when transitioning to something better can be simple, safe, and cost-effective.

     

     

  • Comply in 5! Well, actually UNDER 5 minutes!!

    SB CYBRA PPL 5382

    TRY 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.

    Request your trial now!

  • Backup and Recovery on IBM i: Your Strategy for the Unexpected

    SB HelpSystems SC 5413Robot automates the routine 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:
    - Simplified backup procedures
    - Easy data encryption
    - Save media management
    - Guided restoration
    - Seamless product integration
    Make sure your data survives when catastrophe hits. Try the Robot Backup and Recovery Solution FREE for 30 days.

  • Manage IBM i Messages by Exception with Robot

    SB HelpSystems SC 5413Managing messages on your IBM i can be more than a full-time job if you have to do it manually. 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:
    - Automated message management
    - Tailored notifications and automatic escalation
    - System-wide control of your IBM i partitions
    - Two-way system notifications from your mobile device
    - Seamless product integration
    Try the Robot Message Management Solution FREE for 30 days.

  • Easiest Way to Save Money? Stop Printing IBM i Reports

    SB HelpSystems SC 5413Robot 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:

    - Automated report distribution
    - View online without delay
    - Browser interface to make notes
    - Custom retention capabilities
    - Seamless product integration
    Rerun another report? Never again. Try the Robot Report Management Solution FREE for 30 days.

  • Hassle-Free IBM i Operations around the Clock

    SB HelpSystems SC 5413For over 30 years, Robot has been a leader in systems management for IBM i.
    Manage your job schedule with the Robot Job Scheduling Solution. Key features include:
    - Automated batch, interactive, and cross-platform scheduling
    - Event-driven dependency processing
    - Centralized monitoring and reporting
    - Audit log and ready-to-use reports
    - Seamless product integration
    Scale your software, not your staff. Try the Robot Job Scheduling Solution FREE for 30 days.

  • ACO MONITOR Manages your IBM i 24/7 and Notifies You When Your IBM i Needs Assistance!

    SB DDL Systems 5429More than a paging system - ACO MONITOR is a complete systems management solution for your Power Systems running IBM i. ACO MONITOR manages your Power System 24/7, uses advanced technology (like two-way messaging) to notify on-duty support personnel, and responds to complex problems before they reach critical status.

    ACO MONITOR is proven technology and is capable of processing thousands of mission-critical events daily. The software is pre-configured, easy to install, scalable, and greatly improves data center efficiency.