Wednesday, January 22, 2014

Fun with Logical Files

logical file lf sst rename trntbl

This is not going to be an article on how to code Logical Files. It is about how you can use some of the less well known keywords/functions to do some pretty cool, and fun, stuff with the fields within them.

Before I start coding Logical files I need a Physical file. In this article I am going to use the following file:

   A..........T.Name++++++RLen++TDpB......Functions
01 A          R TESTPFR 
02 A            PFLD1          3A
03 A            PFLD2          2A
04 A            PFLD3         30A 
05 A            PFLD4          5P 0 
06 A            PFLD5         10A 
07 A            PFLD6           L 

I know I will not get any awards for originality for my choice of file and field names.

The first cool thing we can do is change the data type of a field in the Logical file, below I am changing the data type of PFLD4. It is packed numeric in the Physical file and I am defining it as signed numeric in the Logical file.

   A..........T.Name++++++.Len++TDpB......Functions++++
01 A            PFLD4           S

I have found there were times when I used FTP to send files from an IBM i/AS400 to other servers when packed numeric fields would get corrupted. By converting the numeric fields to signed numeric stopped this from happening.

Another thing I can do is rename a field.

   A..........T.Name++++++.Len++TDpB......Functions++++
01 A            LFLD1                     RENAME(PFLD4)

02 A            PFLD6
03 A            LFLD2           A  I      RENAME(PFLD6)
04 A            LFLD3           P  I      RENAME(PFLD6)

In this example on line 1 I am renaming the Physical file field PFLD4 to LFLD1.

Lines 2 – 4 shows how I can rename and change the data type of a field. PFLD6 is defined date field in the Physical file. On line 3 I have renamed it to LFLD2 and changed its data type to Alphanumeric. And on line 4 I have renamed it to LFLD3 and changed its data type to Packed numeric. You will also notice that there is an ‘I’ following the data type field. When you prompt line 4 the editor displays is as:

Name                                 Data      Decimal
Type       Name         Length       Type     Positions     Use
           LFLD3                     P                      I
Functions
RENAME(PFLD6)                       

The “Use” field allows me to flag this field as Input only, by putting an ‘I’ in it. Now will I get data when this Logical file is used for an input operation. But I cannot update the field on an output operation. Don not worry if you are thinking how to flag the field as input and output, just leave it blank.

I have coded the LFLD2 and LFLD3 as input only as any attempt I made to use either for update resulted in a date format error.

I can increase the size of the field thus:

01 A..........T.Name++++++.Len++TDpB......Functions++++
02 A            LFLD5          4A         RENAME(PFLD1)

PFLD1 in the physical is 3 long and I am defining LFD5 as 4 long. So what happens to the fourth byte?

When you perform an output operation to the Logical file the first 3 characters are written to the Physical file’s field, and the fourth character is lost.

And, yes, I have had a reason to use this method.

I can also substring a field from the Physical file into fields in the Logical. I show the two ways I have commonly seen it below.

   A..........T.Name++++++.Len++TDpB......Functions+++++
01 A            LFLD6              I      SST(PFLD1 1 1)
02 A            LFLD7          1A  I      SST(PFLD1 3)

The SST, substting, function has two mandatory parameters, Phyiscal file field name and starting position, and an optional, number of positions.

I can also concatenate fields together. In the example below I am concatenating the Physical file fields PFLD1 and PFLD2 into the Logical file field LFLD8.

   A..........T.Name++++++.Len++TDpB......Functions++++++++++
01 A            LFLD8                     CONCAT(PFLD1 PFLD2)

And finally what I think is the weirdest thing I have had to do with a Logical file field. I was creating IBM i/AS400 based files that a web-based application would use. The users disliked that way that the fields in the web-based application were sorted, which was in EBCDIC, and wanted it in ASCII order. I could change the sort order by using the TRNTABL, translation table, function. Below I am translating the values PFLD3 to ASCII. The web-based application used the LFLD9 for displaying lists of results.

   A..........T.Name++++++.Len++TDpB......Functions++++++++++
01 A            PFLD3
01 A            LFLD9              I      TRNTBL(QSYS/QASCII)
02 A                                      RENAME(PFLD3)

Before anyone sends me a message, I am aware that IBM is no longer going to enhance DDS files, and is committed to DDL, SQL’s Data Definition Language. While they are encouraging us to make the transition from DDS to DDL there are many of us working with applications, many of them from software vendors, which contain thousands of DDS files that will not be converted in the foreseeable future.

You can learn more about this from the IBM website:

 

This article was written for IBM i 7.1, and it should work with earlier releases too.

5 comments:

  1. You will be even better off using SQL views instead of LFs. You have the full range of SQL functions in your toolbox to play with database content. Besides that and most importantly, you serve the modernization movement, your application and last but not least yourself.

    ReplyDelete
  2. "I have found there were times when I used FTP to send files from an IBM i/AS400 to other servers when packed numeric fields would get corrupted."

    I've experienced that problem a few times myself. I've found inserting "binary" in my FTP script eliminated that problem.

    ReplyDelete
  3. Hi Simon , all your blogs are excellent and really helpful , for anything related to IBM i the first place I refer is rpgpgm.com. I have a query related to LF, Is there any option in logical file to TRIM and CONCAT fields ? Scenario is like we have FirstName MiddleName LastName in the physical file, need to concatenate those fields in logical where FullName = TRIM(FirstANme) + ' ' + TRIM(MiddleNAme + ' '+ LastName) . Thank you in advance .

    ReplyDelete
    Replies
    1. Alas, there is no TRIM in DDS.

      If you want to do what you describe you ought to consider building a SQL View. There are assorted trims and concatenation available in SQL.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.