Posts

DB2 System Versioning on IBM i – Automatic History Tracking Without Triggers

By -
Image
In this post I'll show you how to implement DB2 System Versioning on IBM i — a built-in mechanism that automatically keeps a full history of every INSERT, UPDATE, and DELETE on a table, without writing a single line of trigger code. This is a practical, working example I built and tested step by step. I'll show you the real errors I ran into and exactly how I fixed them. What You'll Learn How to create a Journal and Journal Receiver with a CL program How to design a main table with temporal columns Why CREATE TABLE ... LIKE doesn't work for history tables (and what to do instead) The correct sequence: create → journal → activate versioning How to verify the history with SELECT queries The Setup: Two Source Members, One CL Program Everything runs from a single CL program ( HISTORY010.CLP ) that orchestrates two SQL script members stored in SQLTBLSRC . The execution sequence is critical and must follow this exact order: Create j...
Post a Comment
Read more

Incrementing a 2-Character Alphanumeric Code in RPG Free

By -
Image
Incrementing a 2-Character Alphanumeric Code in RPG Free Today I’m sharing a small but useful RPG Free program to increment a 2-character alphanumeric code using the sequence A..Z, 0..9 . The program also handles wrap-around after "99", restarting at "AA". Source Code You can copy and paste the code directly from the field below: **free // /*! // * @file RAAINCR.RPGLE // * @brief Increment a 2-char alphanumeric code (A..Z,0..9) with wrap-around after '99' -> 'AA' // * @details The sequence order is A..Z then 0..9 (36 positions). If input contains invalid chars, // * errorCode is set to 'INVALIDVAL' and outputValue is blanked. // * // * @param[in] inputValue char(2) const Input code to increment // * @param[out] outputValue char(2) Resulting incremented code // * @param[out] errorCode char(10) Error code (e.g. 'INVALIDVAL') // * // * @return void // * @author Aldo SUCCI // ...
2 comments
Read more

Single Page Subfile SQLRPGLE Example Program

By -
Image
#IBMiSample – Improve Subfile Performance by Loading One Page at a Time  The traditional subfile can display up to 9,999 rows and works perfectly in many scenarios. However, when dealing with large datasets or heavy filtering , loading every matching record before showing the first page can drastically slow down the user experience. Based on comments I received from IBM i developers, here are two practical cases that clearly explain why loading only one page at a time is often a better solution. Scenario 1 — Small File Subfile size: 20 rows Total records: 5,000 Filtered records: 280 (for example: FLAG = 'X' and creation date in a selected range) With a standard subfile , the program displays the first page only after scanning all 5,000 records . With a single-page load , the program stops as soon as it finds the first 20 matching records and immediately displays the first screen. This is significantly faster and improves usabilit...
Post a Comment
Read more

How to Use GET DIAGNOSTICS in SQLRPGLE to Retrieve Row Count on IBM i

By -
Image
How to Use GET DIAGNOSTICS in SQLRPGLE to Retrieve Row Count on IBM i In this post, I’ll walk you through a simple and effective way to retrieve the number of rows affected by SQL statements in your SQLRPGLE programs using the GET DIAGNOSTICS statement. Whether you're inserting, updating, or deleting rows, GET DIAGNOSTICS allows you to programmatically capture how many records were impacted. What You'll Learn How to use GET DIAGNOSTICS after an SQL operation How to check the number of rows inserted, updated, or deleted A practical example using two test tables Prerequisites Before running the example, create two tables using these SQL statements: CREATE TABLE TESTFILE ( TFTYPE CHAR(2) NOT NULL WITH DEFAULT, TFNUMB NUMERIC(7, 0) NOT NULL WITH DEFAULT ); CREATE TABLE TESTFILE2 ( TFTYPE CHAR(2) NOT NULL WITH DEFAULT, TFNUMB NUMERIC(7, 0) NOT NULL WITH DEFAULT ); Then populate TESTFILE with the following sample data: INSERT INTO T...
2 comments
Read more

Using a Parameterized Cursor in SQLRPGLE

By -
Image
Using a Parameterized Cursor in SQLRPGLE – A Simple and Clean Example When working with embedded SQL on IBM i, using a cursor with parameters is a great way to selectively retrieve data from a file based on runtime input. In this post, I’ll walk you through a simple SQLRPGLE program that demonstrates this concept. This free-format RPG example shows how to: Accept an input parameter Open a cursor based on that parameter Fetch records from the CLANA00F file Display basic customer information Properly handle SQL errors What Is CLANA00F? The file CLANA00F contains customer master data. Here's a simplified layout of its most relevant fields: Field Type Description CLCCL0 S(7) Customer numeric ID CLNOM0 A(50) Customer name CLCIB0 A(3) International code Our goal is to retrieve only those customers whose CLCIB0 matches a parameter passed when the program is called. Program Highlights ctl-opt dftactgrp(*no) actgrp(*caller); dcl-s CLCCL0...
Post a Comment
Read more