|
>> How does one only bring in two fields? SQL or Field select logical. Example of field select logical. Base table A R RUSEREX TEXT('Additional User Data') A USPROFILE 10 TEXT('User Profile Name') A COLHDG('User Profile') A USSUBSYS 10 TEXT('User operating subsystem') A COLHDG('Subsystem') A USRESTRIC 1 TEXT('Restricted Logon Y/N') A COLHDG('Rst' 'Log' 'On') A VALUES('N' 'Y') A USCRTUSER 10 TEXT('User who created record.') A COLHDG('User''Who' 'Created') A USCRTSTMP Z TEXT('Date & Time Created') A COLHDG('Date' 'Time' 'Created') A USLEDTUSER 10 TEXT('User who last edited.') A COLHDG('User''Who' 'Last Edited') A USLEDTSTMP Z TEXT('Date & Time Last Edited') A COLHDG('Date' 'Time' 'Last Editted') Field Select Logical or what I also call an Application Logical. Table name IS0025_L01. A UNIQUE A R R0025_L01 PFILE(ISUSEREX) A TEXT('Application Logical on ISUSEREX') A USPROFILE A USSUBSYS A USRESTRIC A K USPROFILE Note that you must use a different format name. In this case, there is already an unique index in existence using USPROFILE so the AS/400 automatically shares the access path so this ends up being a format only. So this logical is just presenting a logical view of the data. As far as the program is concerned, this is what the table looks likes. I can then freely recompile the logical and nothing changes as far as the program is concerned. Also, if you use the ALTER TABLE or CHGPF command, because the record format name is not the same as the physical file format name, the new field are not included in any logical. The same would be true if you used a different format name and copied in all the field names. If the physical file is changed, nothing is added to the logical unless you add it manually. Like I indicated in a previous post. Works great. Using the AS/400 as a database system instead of a file system but is a royal pain to create and maintain because I don't see anyway to make this work except creating a logical for each file in a program. I tend to see this working better when you are really using ILE and service programs instead of creating monolith programs. If you have a procedure in a service program that does one thing like "CalculateCurrentInventory", it would only need a few tables. Then Application Logicals make more sense but in the long run, SQL is a better solution. What I have always wanted IBM to do is to be able to specify a format to use when opening a table so I could simply specify a physical or logical file name and then specify a format name for a format that I had created in the program or externally. At open time, the AS/400 would pass the format to the open routine and it would do a mini-compile to make sure that the type, size and position of any field included had not changed in the physical or logical file the format was based on. This would give you the performance of using tables directly but would only bring in what the program needs but with the push to SQL, I could not see IBM every doing anything like this because it would require basic change in how the AS/400 opens tables. Getting awfully esoteric here. My two cents anyway. -----Original Message----- From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] Sent: Wednesday, February 02, 2005 1:39 PM To: RPG programming on the AS400 / iSeries Subject: RE: Externalizing I/O was RPG read loops How does one only bring in two fields? Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com Alan Campin <Alan.Campin@xxxxxxx> Sent by: rpg400-l-bounces@xxxxxxxxxxxx 02/02/2005 03:24 PM Please respond to RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> To RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> cc Subject RE: Externalizing I/O was RPG read loops -1)Speeding up I/O does not apply to updates. The database does a single records read and places a lock on the record and so forth. Speed up comes in input files. There are several levels of buffers on the AS/400 I/O. Each buffer only has a certain amount of space. If you bring in all the fields from a file and that is, say 1000 bytes vs. bringing, say two field, the item number for 15 bytes and the description for 30 bytes for 45 bytes. If you have a X sized buffer, divide by 1000 then try dividing by 45. How many records are in the buffer? I have seen huge increases in speed in programs because the next record is more likely to be in the buffer. 1) True if you change an field like an item number or something but my experience is that is rare. If another field is changed, what is the chance that field is going to be brought into every program? Again, if only the fields you need are brought in, the lower the chances this is true. 2) Program logicals. Don't know any other way to do it given each program has different requirements but it is difficult. My two cents, only. -----Original Message----- From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] Sent: Wednesday, February 02, 2005 12:29 PM To: RPG programming on the AS400 / iSeries Subject: RE: Externalizing I/O was RPG read loops -1) I don't see how it would speed I/O. Does your I/O routine do a some sort of update... %fields(...)? Or what? 1) Only one place to recompile. When changing existing field sizes and data types this quickly becomes a myth. 2) Logicals. Bear some strong merit. In fact, I've see a vendor package do this. They changed all the physicals to a new name. A logical doing some mapping, etc to have the same record level format was created. And this had the old file name. Your existing programs worked without change. Deciding on a separate logical for each program is a bit extreme. 3) SQL. Mostly what I disagree is a matter of opinion so I'll let that drop. The precompiler, while it still has room for improvement, saw vast improvements in V5R3. Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com Alan Campin <Alan.Campin@xxxxxxx> Sent by: rpg400-l-bounces@xxxxxxxxxxxx 02/02/2005 01:05 PM Please respond to RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> To RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> cc Subject RE: Externalizing I/O was RPG read loops Simple, it is called database independence. Something we hardly ever think about on the AS/400. What we do on the AS/400 is bring the whole table into every program and the next thing we know we have five hundred programs referring that table and making the smallest change to the table becomes a nightmare so we create sub tables with a few more fields and then we get that table used in so many programs that we don't want to recompile everything so we create another sub table, so on and so forth. I have seen this scenario played out in so many systems, it is not funny. At my current company, they wanted to add one field to a table and we have Turnover but we had one programmer working for 3 weeks just making up compile lists to update one table and trying to get all the programs to recompile. What a mess! The whole concept of a database is that we can separate the physical and logical views of the database. Bringing the entire table defeats that. It, also, slows down I/O tremendously. Solutions: 1. Externalize the I/O. Put the I/O in one program. Advantages Only one place to recompile. Disadvantages Complex I/O programs and tons of them unless you write a general purpose program and that requires C. Cannot tell what files are being used in a program. 2. Use field select logicals. Advantages Brings in only what you need and allows complete data base independence. Big increases in I/O performance. Allows cross reference tools to see what files are being used. Disadvantages Tons of logicals. One per file per program. Pain to maintain and create. 3. Use SQL Advantages Assuming you select your fields, brings in what you need. Allows easy joins. Complete data base independence. See which files are used in a program. Disadvantages Difficult to enter SQL into RPG programs. Hopefully this will vanish with free form SQL statements. Have to deal with precompiler which is a complete pain. Single table I/O is slower but only a fraction. The solution, obviously at least to me, is for us to use SQL and that is the direction everything is going but most of the time, if we want to provide some kind of database independence, we externalize the I/O but that has it share of problems. I keep waiting for free format SQL making it easy to enter SQL statements. Then maybe SQL will take off for I/O in RPG. Anyway, my two cents, again. -----Original Message----- From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] Sent: Wednesday, February 02, 2005 9:11 AM To: RPG programming on the AS400 / iSeries Subject: Externalizing I/O was RPG read loops Paul, I think you're the first person that I heard of, that wasn't the person in charge of writing the access programs, that actually liked this scenario. Why an end developer would like to replace chain(e) myfile; // now actually do some real work with the fields with getFilename(); MyWorkField1=GetField(field1); MyWorkField2=GetField(field2); ... // now actually do some real work with the fields rather escapes me. Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com "Paul Morgan" <pmorgan@xxxxxxxxxxxxxx> Sent by: rpg400-l-bounces@xxxxxxxxxxxx 02/02/2005 10:18 AM Please respond to RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> To rpg400-l@xxxxxxxxxxxx cc Subject Re: RPG read loops Richard, Great setup. I once contracted at a shop that did this with OPM programs even before ILE. You never put a file into production without it's access program. Applications programs never referred to a file directly but did all access through the IO program. Even all the file logicals were in that same program so you could select which logical to use with a parameter on the call. Paul -- Paul Morgan Senior Programmer Analyst - Retail J. Jill Group 100 Birch Pond Drive, PO Box 2009 Tilton, NH 03276-2009 Phone: (603) 266-2117 Fax: (603) 266-2333 "Richard ECUYER" wrote > I did it. > > For each file (physical, logical, with ot without key), i build one source > with some procedures : > Get_Filename (CHAIN) > Set_filename (SET, LL, GT) > Exists_filename (SETLL without read) > Read_filename (READ, readp, readE, READPE) > Insert_filename (write) > Delete_filename (delete) > Unlock_filename (unlock) > Update_filename (update) > dft_filename (populate the exported datastructure with defaul > values) > All "READ" procedures include a parm (*on/*off) to lock or not the record) > > Create a module and one service pgm by file > a ds (io_filename) is exported. > > It is not done by hand, i create a little pgm that get file decription and > create all sources needed (procedure interface, module...) > > When i use suche a procedure, i include 3 lines : > > /copy of the bnddir (it is one with only IO service pgm) > /copy of procedure interface > an external DS (linked with the file) if i use data from the file. > > the only think (for me) to watch is in a loop that call a program that read > the same file i must make a setgt before the next read. > all procedure return *off if request fald or *on if not. > > My loop are like this : > > Dow Read_filename (*off : '*NEXT' : '*EQ' : Key1 : Key2 ...) > do staff > Enddo > > One cool thing is that i can change the file (add new fields at the end) > without getting level error (new field will not be usable until i recompile) > I have a performance gain, and all IO for a file are in ONE source. > It is new for us, but all find it easy and pretty to use. > > My little piece of 0,1 euro. -- This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l. -- This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l. -- This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.