02
Sat, Mar
0 New Articles

Practical SQL: Journal Forensics Using SQL

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

Journals provide a lot of data about IBM i file operations, and the DISPLAY_JOURNAL table function lets you mine that data.

I’ve talked a lot about using SQL to analyze data. But did you know that you can use SQL and journals together to analyze your programs as well? The table function DISPLAY_JOURNAL makes it easy to identify exactly which programs are updating files in your library.

Unlocking an Application’s Architecture

IBM i applications tend to be highly integrated, which typically means that the application is supported by lots of files, which are in turn managed by many programs. Also, IBM i applications tend to have very long lifespans, measured in decades, not years. And while the applications themselves may have gone through many upgrades, the specific version you’re working on may no longer be supported. Alternatively, you may find yourself working on a completely custom system whose original architects are no longer available. In either case, enhancements or even fixes to these applications can be difficult because a change in one program may affect another program in ways that are not immediately evident.

Products such as Hawkeye’s PATHFiNDER and X-Analysis from Fresche Solutions are designed to support that sort of analysis, and while they’re quite powerful they’re typically not cheap. On the other hand, you can do the analysis yourself. While there is no silver bullet for self-directed analysis, hopefully the technique I’m about to describe can help.

Identifying the Goal

What I’ve found over the years is that one of the most common tasks in IBM i application analysis is to find out which database files get updated by a specific task. This could be something as simple as maintaining a master record to something more complex, such as entering an order. In either case, you need to identify all the files that are getting updated. Even for the more simplistic file maintenance scenario, entering data on a single panel might update multiple files. In a real-world scenario, we might want to perform a mass update on some master data, but if we don’t know what all the side effects are, just doing an SQL update on one file might leave other files out of sync.

The Setup

We’re going to use journals to perform this analysis. This process has one absolute prerequisite: all of the files you are investigating must have journaling enabled. And for ease of processing, it’s best if the files are all being journaled to the same journal. While that’s not strictly necessary, it definitely makes the process less complicated. We’re going to assume all files are in the same journal for this discussion. In fact, the journal will be called MYJRN in MYLIB, because that’s easy to remember.

I’m assuming that you understand how to set up journals and journal receivers. They’re really critical to a lot of IBM i processing. If you’re not, you can start with this IBM article. I’ll also have a follow-up article with a utility to enable and disable journaling for all the files in a library; that will help those of us who aren’t currently taking advantage of journaling.

The Execution

There are two basic steps to this process: execute the business activity, and analyze the results. As long as everything is set up correctly, it’s not that difficult. Let’s address a couple of practical issues right off the bat, though. First is the environment. Are you working in a test environment, or are you in production? While this analysis can be done in production, and in certain cases must be done there, I highly recommend you do your testing in development for a number of reasons, not the least of which is that you can’t damage production data! A second practical issue that can be harder to deal with is whether you are running interactively or in batch. While it’s not a showstopper by any means, it is a complication that I’ll show you how to address.

Let’s say we want to update the remit-to address for a customer. Finance would like us to do this automatically for a large number of customers, so manually keying this isn’t an option. We might be able to use some sort of keyboard macro; the old iSeries Access for Windows actually does this through Visual Basic and allows you to read and update a spreadsheet. You might even see an article about that in the future. But assuming that’s not an option, we’re going to want to do this using some sort of SQL update, but first we want to make sure we know exactly what needs to be updated.

We’re ready to execute the process, but before we do, we’re going to do a little bit of housekeeping. We’re going to send a special marker to the journal that we can use later to bracket our query. This is less of an issue in a lower volume environment, but it can be critical on a production machine. In any case, we’ll use the SNDJRNE command to send this marker.

SNDJRNE JRN(MYLIB/MYJRN) TYPE('00') ENTDTA('Starting update')

This sends an entry to the journal that we can find later. The test can be whatever we want, and in a more involved process we might send multiple entries identifying different steps in the process. Next, we just execute the process, and finally we send the closing bracket:

SNDJRNE JRN(MYLIB/MYJRN) TYPE('00') ENTDTA('Update complete')

Time to See the Results

This is also a two-step process. The two steps allow us to winnow out any extraneous noise from other jobs which would be required in a production environment. In a low-volume test environment you may be able to skip some of this, but we should review the whole process so that you have the tools available.

First, we’re going to get our bracket information. You can use time and date stamps, but I find sequence numbers to be more precise. Remember that we sent entries to the journal with type 00; the following SQL query shows those:

SELECT char(ENTRY_DATA,100) ENTDTA, SEQUENCE_NUMBER

FROM TABLE(DISPLAY_JOURNAL('MYLIB','MYJRN')) a

WHERE JOURNAL_ENTRY_TYPE = '00';

Practical SQL: Journal Forensics Using SQL - Figure 1

Figure 1: Retrieve sequence numbers

This gives you the sequence numbers to use to bracket the next statement, which is the actual analysis:

SELECT OBJECT, PROGRAM_NAME PGM,

       JOURNAL_ENTRY_TYPE TYPE, count(*) COUNT

FROM TABLE(DISPLAY_JOURNAL('TSTAENV','TSTJRN',

   STARTING_SEQUENCE => 38603413,

   ENDING_SEQUENCE => 38603458))

WHERE JOB_NUMBER = 491213

GROUP BY OBJECT, PROGRAM_NAME, JOURNAL_ENTRY_TYPE

ORDER BY 1,2,3;

This statement groups all of the journal entries between the two bracket entries by object and journal entry type; this details the database operations being performed. Note that I included WHERE JOB_NUMBER = 491213. This is the job number of the interactive job where I ran the customer maintenance. In a busy environment, that can really focus on just the process you are running yourself. It can be a little trickier with submitted jobs; you have to get the job number of the submitted job, but the concept is the same.

Anyway, at this point you can now see what happened:

Practical SQL: Journal Forensics Using SQL - Figure 2

Figure 2: Retrieve objects

Looking at this, you’ll notice a couple of things. First, the OBJECT field is 30 characters long. This is the file name, library, and member of the file being updated. Typically, when I do this, I substring out the file name and library. Second, though, is the interesting piece. Sure, I expected one record to get updated in the customer master (CUSMAS), but what’s with the INVHDR updates? My initial guess would be that the maintenance program goes out and updates the remit-to address on any open invoices. I could be wrong, but we’d figure that out once we opened up the INVSYNR program. And more importantly, this would be something we’d want to mimic if we did indeed go with an SQL approach to updating the customer header. And last but not least, you can see the two 00 entries; those are my brackets, and it helps show that I got the correct sequence range in my Select statement.

Summary

This is a first pass at learning how to analyze an application using forensic analysis of the database operations. While not exhaustive, it can provide a solid starting point when you need to know exactly how an application works. Journals are a really powerful tool, and we’ll do more with them later.

Joe Pluta

Joe Pluta is the founder and chief architect of Pluta Brothers Design, Inc. He has been extending the IBM midrange since the days of the IBM System/3. Joe uses WebSphere extensively, especially as the base for PSC/400, the only product that can move your legacy systems to the Web using simple green-screen commands. He has written several books, including Developing Web 2.0 Applications with EGL for IBM i, E-Deployment: The Fastest Path to the Web, Eclipse: Step by Step, and WDSC: Step by Step. Joe performs onsite mentoring and speaks at user groups around the country. You can reach him at This email address is being protected from spambots. You need JavaScript enabled to view it..


MC Press books written by Joe Pluta available now on the MC Press Bookstore.

Developing Web 2.0 Applications with EGL for IBM i Developing Web 2.0 Applications with EGL for IBM i
Joe Pluta introduces you to EGL Rich UI and IBM’s Rational Developer for the IBM i platform.
List Price $39.95

Now On Sale

WDSC: Step by Step WDSC: Step by Step
Discover incredibly powerful WDSC with this easy-to-understand yet thorough introduction.
List Price $74.95

Now On Sale

Eclipse: Step by Step Eclipse: Step by Step
Quickly get up to speed and productivity using Eclipse.
List Price $59.00

Now On Sale

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: