IBM i fans only - tips and tricks to start RPG free format programming with simple examples

#IBMiSample Let's try with a very simple csv like this, called file1.csv: Code;Name 3741;Joe 3742;Barth 3810; 3945; First of all we need to save file1.csv into ISF . In my PUB400 (free AS400 at pub400.com) I have this directory: /home/PALDO I copy file1.csv to  /home/PALDO Use standard FTP transfer software (I use WinScp, I think Filezilla will work too) Let's take a look inside my directory I type WRKLNK and I verify the existence of File1.csv It looks as expected: To import this file I need a similar table on DB2. In my example it means that I will create a table with a decimal field and a charachter field . I create the table TEMPCSV (PALDO1 is my Library): CREATE TABLE PALDO1.TEMPCSV ( CLCCL0 DECIMAL(7, 0) DEFAULT 0 , CLNOM0 CHAR(50) DEFAULT ' ' ) RCDFMT FMTXX Here's my new table TEMPCSV : The table  TEMPCSV  is now created and it is empty: We can optionally set up a source file to note lines not imported by the CPYF...

#IBMiSample How to get a primary key field that increments itself and a time field that updates itself? Look at  LGID00 and  LGDTTM fields. Create a table: CREATE TABLE LGCUS00F ( LGID00 INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, LGDTTM TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, LGNAME CHAR(50), LGSURN CHAR(50)) RCDFMT LGCUS Now try to add a record: INSERT INTO LGCUS00F (LGNAME, LGSURN) VALUES ('Aldo', 'Succi') Let's see the result: SELECT * FROM LGCUS00F As you can see, LGID00=1 and LGDTTM=2018-04-01-10.01.49.781922 That's it! I appreciate all the comments made on this blog.

#IBMiSample This looks like magic! The operating system, in a totally automatic way, keeps track of every change in the data. This simple technique allows you to find the value of a field on a certain date and time. use STRSQL Step 1) CREATE TABLE CUSTOMER (CUSTOMER_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH +1 INCREMENT BY +1 ) ,CUSTOMER_NAME VARCHAR(10) ,ADDRESS VARCHAR(20)) Step 2) alter table customer add system_start timestamp(12) generated always as row begin not null add system_end timestamp(12) generated always as row end not null add tx_start timestamp(12) generated always as transaction start id implicitly hidden add period system_time (system_start, system_end) Step 3) create table customer_h like customer Step 4) alter table customer add versioning use history table customer_h Step 5) Now use command line: CRTJRNRCV JRNRCV(VDOTEST1/JRNRCVNAME) THRESHOLD(150) TEXT('Journal receiver') Step 6) CRTJRN JRN(VDOTEST1/jrnname) JRNRCV(VDOTEST1/jrnrcv...