How to use “Program-Described-File” for converting an AS/400 numeric field into Char-field

620 pts.
Tags:
AS/400
AS/400 numeric fields
DB2/400
OVRDBF
RPG Program
My situation is..... I have a file which is old & got 18,000 records in it. It has a numeric-field(Amount, Len 9,2). Now the 'Amount'Field in the file is changed to 12-char, in the new library. only this field's name is changed in order to avoid ambiguity. Otherwise the file-name & other fields remain the same. Now i need to write a conversion program to copy all the existing records one-by-one into the new file, with the amount moving to CHAR-field. I know the process of converting the Numeric to Char & pushing it to this field; but since the file-name is same, how do i do it? Somebody suggested to use a OVRDBF of the file & call an RPG program, where i move the old-fields to the new-fields. But I think there has to be 2-files; 1)Source to read from & 2)Target file to write into. Iam confused as to how to do this. NOTE: ----- The old file will be moved along with data into an archive library.

Software/Hardware used:
as/400, db2/400

Answer Wiki

Thanks. We'll let you know when a new response is added.

In your “F” specs of your program your would have
F Input IF……
F Output O…….

In a CL, you would have
OVRDBF INPUT TOFILE(&Lib/&file)
OVRDBF OUTPUT TOFILE(&Lib/&File)

You probably only need one of the overrides if you the other would pick up the correct file.
If both files are externally defined, then you would need to CRTDUPOBJ or a CPYF of one of them to a different filename so the program would complie.
If at least one of the files in internally defined, you could just do the OVRDBF to that file and let the system library list pick up the other.

PGMBOB With another view.
The new CHAR field in in th record with the numeric amount field.
Use the one file as update primary, convert the NUMERIC to CHAR and Update.
Before %editc(numfield:’X’) giving a character value we would use SUBRNO.
SUBRNO would be an input / output program defined file.
Output the NUMERIC with any editing required like it was a print line.
Read IN the character value.
Like writing out to a buffer and reading it in.
Good luck

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Now the 'Amount'Field in the file is changed to 12-char... I hope someone had an extremely good reason for doing that. I can't think of any good reason for it. If it's some way to prepare data for downloading to a PC or other remote system, it shouldn't be done in a physical file -- it should be done in an IFS streamfile. Also, I hope you're doing this with ILE RPG on a reasonably current version of i5/OS. If so, then you won't actually need an OVRDBF. It can all be done with native RPG code. Here is some RPG that wants to work with the IBM example file QCUSTCDT in library QIWS. The file has been duplicated into a library in the library list. The program also wants to work with the duplicated file, so it has to distinguish between them. It uses two variations of the technique. One variation directly references the file and library names. The other variation builds the file name and library in a variable. The variable file name uses USROPN to delay opening the file until the variable value is set:
         FQCUSTCDT1 if   e             disk    extfile( 'QIWS/QCUSTCDT' )
    
         FQCUSTCDT2 if   e             disk    usropn
         f                                     extfile( CUSTCDT2  )
         f                                     extmbr( CDMBR )
         f                                     rename( CUSREC : CUSREC2 )
         f                                     prefix( QC : 2 )
    
         D CUSTCDT2        s             21
         D CDMBR           c                   'MYMBR'
    
                             CUSTCDT2  = %trimr( PgmLib ) + '/'
                                       + 'QCUSTCDT' ;
    To prepare for that programming, there are a couple areas that need thought. First is the compile itself. The compiler needs to know what CUSTCDT1 and CUSTCDT2 are. And there are two ways to tell the compiler about those:
    1. Create two file descriptions with those names by duplicating the file object. Those don't need data nor any members. Only the file descriptions need to exist in the library list for the compile. Two added file descriptions are small.
    2. If you can't create extra file descriptions, then you can use two OVRDBF commands to override to the existing file in the compilation job.
    Note that this example code uses rename( CUSREC : CUSREC2 ) and prefix( QC : 2 ) to avoid duplicating record format and field names. The second area that is involved is when the program runs. By using the EXTFILE() keyword, the need for OVRDBF at run-time can be eliminated. No extra run-time CL is needed. No matter how it's done, this sounds like the start of a lot of trouble as time goes by. If this is supposed to be a numeric value and it's in a database file, then it probably should not be defined as a character field. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    ...in the new library. only this field's name is changed in order to avoid ambiguity. Otherwise the file-name & other fields remain the same. Why did you put "Program-Described-File" in the subject of the question? If the file will be program-described, the field names won't make any difference. The program won't see the names of the fields. It won't even know that there are any fields. Does this need to be a "Program-Described-File" for some reason? Tom
    125,585 pointsBadges:
    report
  • svankipu
    Tom, I don't know why they want a “Program-Described-File”, I dont understand their concept behind that. I have to only follow; I cannot question the client why they want “Program-Described-File”. I think you understand my situation. Svanky
    620 pointsBadges:
    report
  • TomLiotta
    I cannot question the client why they want “Program-Described-File”. You should (IMO). I would. I'd point out that it will cost them more to do it that way and that they'll end up with a more difficult set of programs that will be harder to maintain in the future. IMO, pointing out issues is a matter of ethics. The client should be making informed decisions, and a consultant ought to be ethically bound to present information that helps the client to make decisions. If the client then chooses to pay extra and agrees to accept troublesome programming, then all is well. But all of that is separate from your question. Just about everything that has been discussed above can be used with program-described or externally-described files. You just do the field work in a data structure or with substrings for a program-described file instead of with actual database fields (since there won't be any). Anything that comes up as an obstacle can be discussed when it comes up. Tom p.s. Are they also requiring RPG/400?
    125,585 pointsBadges:
    report
  • svankipu
    If my understanding is correct, U can use SQL Command or Embedded SQL, Insert INTO LIB1/PF1 (Select all the fields) select (all fields in sequence) from LIB2/PF2 and you can use cast to convert that field.. Correct me if i m wrong...
    0 pointsBadges:
    report
  • TomLiotta
    U can use SQL Command or Embedded SQL, But then it wouldn't be a “Program-Described-File”. Tom
    125,585 pointsBadges:
    report
  • svankipu
    @ TOM: What does this PROGRAM DESCRIBED FILE means...??
    0 pointsBadges:
    report
  • philpl1jb
    Program described file F-spec - fixed format / not Externally defined I - specs - record name i- spec for each field Phil
    51,355 pointsBadges:
    report
  • TomLiotta
    What does this PROGRAM DESCRIBED FILE means…?? There are two general concepts of files in programming on the AS/400 line of systems -- externally-described files and program-described files. The database is 'integrated' with the operating system. Compilers use the integrated database to supply file descriptions to programs by retrieving field descriptions based on file names. In RPG, you don't need to supply I-specs for input and O-specs for output because the compiler will do that for you. The compiler retrieves the 'external description' from the *FILE object. The file is then known as an "externally-described" file. But you can choose not to use an external description for your files. You can code I-specs and O-specs to define fields yourself. A lot of printed output is still created through O-specs (for some reason that I'm not clear on). When you use your own field descriptions in I- or O-specs, you are describing the file in the program. The file is then known as a "program-described" file. There are some fine points that can be debated over how the F-spec relates to program- or externally-described files, but the details of I- and O-specs are the ones that external descriptions really help with. When you create a file with CRTPF, for example, you can specify a DDS source member that includes detail definitions of all of the fields in the record format of the file. But DDS isn't required. All you really need to specify is a record length with the RCDLEN() parameter. The file is created with just a single field that covers the whole record. You can read and write those records with RPG. But if you want the records to have the same kinds of data in various parts of all records or even in different parts, you need to define the data elements somewhere. In RPG, you create those definitions with the I- and O-specs -- program-described. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following