Can I override the record format name in a CLLE to be used in an RPGLE or can I qualify the record format name with a field value?

690 pts.
Tags:
AS/400
CLLE
OVRDBF
RPGLE
Hello All, I ran into a roadblock writing a new purge process for my company. Here's the situation: I have 3 files that hold EDI Data that I need to purge: FILEA - Record Format FILEAR FILEB - Record Format FILEBR FILEC - Record Format FILECR All of the above files have the same record length, field names, and all the field types are the same. I created a Table File (lets call it TABLEFILE) that holds the File Name & Record Format name of each file I need to purge data from. I wrote a CLLE that will read TABLEFILE and will extract the File Name and Record Format name (which are the 3 files listed above) I want to be able to read through TABLEFILE, retrieve the file name & record format name, override the database file to the file name from TABLEFILE and process the records via an RPGLE. When I go to do this, and it attempts to read the first record of my file it blows up because i cannot override the name of the record format, only the file. Granted, if i use a RENAME keyword on the F-Specs that would be fine. BUT each file has a different record format name, so using RENAME defeats the purpose if i don't have or can't qualify the record format name. HERE'S THE ACTUAL QUESTIONS: 1-CAN I OVERRIDE THE RECORD FORMAT NAME TO BE USED IN AN RPGLE? 2-IF SO, HOW THE HECK CAN I DO THIS??? I have the file name, and the record format name, and i don't want to keep adding files to my RPGLE, which would defeat the purpose of putting things in the Table File. I'm just trying to make this dynamic so that anytime we need to add a new file (of the same type, record length, etc) i can just add a record to the file. PLEASE HELP!

Software/Hardware used:
V7R1

Answer Wiki

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

Discuss This Question: 19  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
    I don't have an answer at the moment, but I do have a question. Your answer might help explain the whole problem. Since the records have the same field names and data definitions, why would you assign different record format names? -- Tom
    125,585 pointsBadges:
    report
  • Eric Witham
    Very good question. This was not my design. I started at this company only a month ago. Either the part-time consultant set it up this way or the previous developer. I definitely would've gone a different route with that process (EDI 315 data) and used 1 file for all translated 315 data.
    690 pointsBadges:
    report
  • TomLiotta
    I don't like this, but you might consider OPNQRYF. This is conjecture for brainstorming; but parameters are all open to CL variables, so you can provide input choices through the FILE() parameter. You can create a "model" file description with a format named something like FILEmdlR and compile your target program over the model. OPNQRYF can then use FILEmdlR to map fields the FILE() to its output. It'd probably be ugly. -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    SQL doesn't use record names.
    49,720 pointsBadges:
    report
  • CharlieBrowne
    Is there any reason you do not want to do this with SQL? Seems like it would be easy to build your SQL statement(s) on the fly.
    41,370 pointsBadges:
    report
  • Eric Witham
    CharlieBrown- Yes, I have no desire to do this in SQL. TomLiotta- Not bad, but a lot of jumping through hoops it seems just for a purge program. I figured out what I need to do... In the F-Spec I'll just create a full procedural file and give it whatever name i fancy. Then i'll put the fields in the I-Specs that are in the file, (since all the files have the same fields, field names, record length (417)) and in the calling CLLE, I'll do an OVRDBF to the file i put in the F-Specs. Now i can process the data for all files without having to worry about the record format. Bam.
    690 pointsBadges:
    report
  • philpl1jb
    Eric    That wouldn't be my approach.     I would write the program with all three f specs as USROPN .. and the reads, or chains and writes or updates in select structures.   Or as seperate subroutines that can call a common subroutine to do the field level stuff. 
    49,720 pointsBadges:
    report
  • Eric Witham
    Philpl1jb- That would be fine if I only had 3 files. But i actually have 15 and there could be more. As of now i'm not revamping our 315 process. I will be in the future and will get all our EDI procedures streamlined and organized, but for now, i have to work with what i have. Some are in the pipeline and just need another file created. I didn't want to having to keep changing a program in order to purge the data from these files. So i put all the files into 1 physical file with the number of days to keep for each one. All that is now required is 1 clle to loop through my file by transaction set number and OVRDBF to the filename in the log file. Should we want to do this with other transaction sets, then all i would have to do is add records to a file and let it streamline through. the only other change needed which would be unique to each transaction set, is a 6 line loop in my clle. It's dynamic, little extra programming is involved and it works pretty sweet.
    690 pointsBadges:
    report
  • philpl1jb
    Glad you have a solution.  In that case, I would use a prepared SQL statement.  No overrides, read a record from the trigger file, create the prepared statement, open it, execute it and, close it. 
    49,720 pointsBadges:
    report
  • TomLiotta
    If you can show a basic example of using I-specs that actually compiles, it'll be interesting to see. Using I-specs generally requires specifying the record format name, but you'll have multiple record formats. -- Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    The override and record format name references seemed to imply that SQL wouldn't be of interest, but it's also probably how I'd do it. -- Tom
    125,585 pointsBadges:
    report
  • Eric Witham
    If you guys want to see how i did it, send me an email at EricTWitham@gmail.com and i'll send you the code in a .txt format.Thanks for the help & advice.
    690 pointsBadges:
    report
  • TomLiotta
    It kind of goes against the spirit of this forum to take conversations to private e-mail. That makes it difficult for later members to gain the benefit of your experience. It also is usually not a good idea to post your e-mail address in public web forums. -- Tom
    125,585 pointsBadges:
    report
  • Eric Witham
    /*===================================================================*//*¹DATE CREATED: 02/20/2013                                         ·*//*¹CREATED BY..: E.WITHAM                                           ·*//*¹PROGRAM ID..: PRGEDIDTAC                                         ·*//*¹DESCRIPTION.: PURGE PROCESS: DYNAMIC PURGE PROCESS FOR EDI       ·*//*===================================================================*/             PGM/*-------------------------------------------------------------------*/             DCL        VAR(&EDI315)    TYPE(*CHAR) LEN(3) VALUE('315')             DCL        VAR(&NBROFDAYS) TYPE(*CHAR) LEN(4)             DCLF       FILE(PRGEDI01)/*-------------------------------------------------------------------*//*¹PURGE THE EDI 315 DATA FROM ALL FILES                            ·*//*-------------------------------------------------------------------*/             OVRDBF     FILE(PRGEDI01) POSITION(*KEY 1 PRGEDI00R '315')             RCVF       RCDFMT(PRGEDI00R)             MONMSG     MSGID(CPF0864) EXEC(GOTO CMDLBL(#LEAVE_315))             DOWHILE    COND(&PFTSET *EQ &EDI315)             OVRDBF     FILE(PRG31500) TOFILE(EXTDTA/&PFNAME) LVLCHK(*NO)             CHGVAR     VAR(&NBROFDAYS) VALUE(&PFDAYS)             CALL       PGM(PRG315DTAR) PARM(&NBROFDAYS)             DLTOVR     FILE(PRG31500)             RGZPFM     FILE(EXTDTA/&PFNAME)             RCVF       RCDFMT(PRGEDI00R)             MONMSG     MSGID(CPF0864) EXEC(GOTO CMDLBL(#LEAVE_315))             ENDDO #LEAVE_315: DLTOVR     FILE(PRGEDI01)/*-------------------------------------------------------------------*/ #ENDPGM:    ENDPGM/*===================================================================*/      *====================================================================*      *¹DATE CREATED...: 02/13/2013                                       ·*      *¹CREATED BY.....: E.WITHAM                                         ·*      *¹PROGRAM ID.....: PRG315DTAR                                       ·*      *¹DESCRIPTION....: PURGE PROCESS FOR EDI 315 DATA                   ·*      *¹PURPOSE........: THIS PROGRAM WILL LOOP THROUGH A FILE AND CHECK  ·*      *¹                 TO SEE IF THE DATA IS OLDER THAN THE VALUE BEING ·*      *¹                 PASSED IN AS THE *ENTRY PARM. IF IT IS, THEN IT  ·*      *¹                 GETS DELETED. THE PROGRAM RETURNS BACK TO THE    ·*      *¹                 CLLE (PRGEDIDTAC) AND WILL READ THE TABLE FILE   ·*      *¹                 TO DETERMINE IF THERE ARE ANY OTHER FILES THAT   ·*      *¹                 NEED TO BE PURGED. IF SO, AN OVRDBF IS PLACED ON ·*      *¹                 IT WITH THE NAME OF PRG31500 AND THIS PROGRAM IS ·*      *¹                 EXECUTED.                                        ·*      *====================================================================*     h option(*nodebugio) indent('|-')      *--------------------------------------------------------------------*     fPRG31500  uf   f  417        disk      *--------------------------------------------------------------------*     d DaysToKeep      s              4s 0     d EntryDate6s0    s              6s 0 inz     d EntryDate8s0    s              8s 0 inz     d EntryDateISO    s               d   inz datfmt(*iso)     d EntryDateYMD    s               d   inz datfmt(*ymd)     d prmDaysToKeep   s              4a     d TodayInISO      s               d   inz datfmt(*iso)      *--------------------------------------------------------------------*     iPRG31500  NS     i                                  1    7  ST02     i                                  8    8  AVPCDE     i                                  9   16  AVPDTE     i                                 17   20  AVPTIM     i                                 21   28  AVEDTE     i                                 29   32  AVETIM     i                                 33   41  AVI0     i                                 42   44  B401     i                                 45   47  B402     i                                 48   49  B403     i                                 50   55  B404     i                                 56   59  B405     i                                 60   64  B406     i                                 65   68  B407     i                                 69   78  B408     i                                 79   80  B409     i                                 81   84  B410     i                                 85  109  B411     i                                110  111  N901     i                                112  141  N902     i                                142  186  N903     i                                187  192  N904     i                                193  198  N905     i                                199  205  Q201     i                                206  208  Q202     i                                209  214  Q203     i                                215  220  Q204     i                                221  226  Q205     i                                227  233  Q206     i                                234  243  Q207     i                                244  245  Q208     i                                246  255  Q209     i                                256  257  Q210     i                                258  287  Q211     i                                288  288  Q212     i                                289  316  Q213     i                                317  324  Q214     i                                325  325  Q215     i                                326  326  Q216     i                                327  327  R401     i                                328  329  R402     i                                330  354  R403     i                                355  378  R404     i                                379  381  R405     i                                382  411  R406     i                                412  415  R407     i                                416  417  R408      *--------------------------------------------------------------------*     c     *entry        plist     c                   parm                    prmDaysToKeep      *====================================================================*      /FREE       //*=================================================================*          DaysToKeep = %dec(prmDaysToKeep:4:0)                             ;          TodayInISO = %date                                               ;       //*-----------------------------------------------------------------*          read         PRG31500                                            ;          dow not %eof                                                     ;              if AVEDTE <> *blanks                                         ;              //¹FILE MA315UA00 HAS AN 'X' IN THE FIRST POSITION              //¹HERE I AM STRIPPING IT OUT AND REPLACING WITH A '0'                 if %subst(AVEDTE:1:1) = 'X'                               ;                    %subst(AVEDTE:1:1) = '0'                               ;                 endif                                                     ;              //¹CONVERTING THE DATE VALUE FROM 00130220/20130220 TO ISO                 if %subst(AVEDTE:1:2) = '00'                              ;                    EntryDate6s0 = %dec(AVEDTE:8:0)                        ;                    EntryDateYMD = %date(EntryDate6s0:*YMD)                ;                    EntryDateISO = EntryDateYMD                            ;                 else                                                      ;                    EntryDate8s0 = %dec(AVEDTE:8:0)                        ;                    EntryDateISO = %date(EntryDate8s0:*iso)                ;                 endif                                                     ;              //¹IF THE DIFFERENCE OF DAYS BETWEEN THE 2 DATES IS GREATER              //¹THAN OR EQUAL TO THE ENTRY PARAMETER VALUE, DELETE.                 if %diff(TodayInISO:EntryDateISO:*Days) >= DaysToKeep     ;                    Delete PRG31500                                        ;                 endif                                                     ;              endif                                                        ;              read     PRG31500                                            ;          enddo                                                            ;       //*-----------------------------------------------------------------*          *inlr = *on                                                      ;          return                                                           ;       //*=================================================================*
    690 pointsBadges:
    report
  • Eric Witham
    all that stuff was what i was trying to avoid. copy it into notepad and hit enter where you know each line ends/begins.
    690 pointsBadges:
    report
  • TomLiotta
    That's fairly clever. Going back to use a program-described file has some value here. Possibly better than falling back on OPNQRYF which was also an older technique. From the looks of it though, SQL would be a far better choice and almost certainly more efficient. At 7.1, it could even be via a dynamic RUNSQL statement.   For those who would consider SQL, the whole process might look like this:
                 PGM
    /*-----------------------*/
                 DCL        VAR(&SQLSTM) TYPE(*CHAR) LEN(256) +
                              VALUE('delete from EXTDTA/&ReplNAMES +
                              where (case when substr((case when +
                              substr(AVEDTE,1,1)=''X'' then (''0'' +
                              concat substr(AVEDTE,2,7)) else AVEDTE +
                              end),1,2)=''00'' then (''20'' concat +
                              substr(AVEDTE,3,6)) else AVEDTE end) < +
                              substr(char(current date - xxxx +
                              days,ISO),1,4) concat substr(char(current +
                              date - xxxx days,ISO),6,2) concat +
                              substr(char(current date - xxxx +
                              days,ISO),9,2)')
                 DCL        VAR(&EDI315)    TYPE(*CHAR) LEN(3) VALUE('315')
                 DCL        VAR(&NBROFDAYS) TYPE(*CHAR) LEN(4)
                 DCLF       FILE(PRGEDI01)
    /*-----------------------*/
    /*¹PURGE THE EDI 315 DATA FROM ALL FILES                            ·*/
    /*-----------------------*/
                 OVRDBF     FILE(PRGEDI01) POSITION(*KEY 1 PRGEDI00R '315')
                 RCVF       RCDFMT(PRGEDI00R)
                 MONMSG     MSGID(CPF0864) EXEC(GOTO CMDLBL(#LEAVE_315))
                 DOWHILE    COND(&PFTSET *EQ &EDI315)
                 CHGVAR     VAR(&NBROFDAYS) VALUE(&PFDAYS)
    
                 CHGVAR     VAR(%SST(&SQLSTM 20 10)) VALUE(&PFNAME)
                 CHGVAR     VAR(%SST(&SQLSTM 242 4)) VALUE(&NBROFDAYS)
                 CHGVAR     VAR(%SST(&SQLSTM 296 4)) VALUE(&NBROFDAYS)
                 CHGVAR     VAR(%SST(&SQLSTM 350 4)) VALUE(&NBROFDAYS)
    
                 RUNSQL     SQL( &SQLSTM ) COMMIT( *NONE )
    
                 RGZPFM     FILE(EXTDTA/&PFNAME)
                 RCVF       RCDFMT(PRGEDI00R)
                 MONMSG     MSGID(CPF0864) EXEC(GOTO CMDLBL(#LEAVE_315))
                 ENDDO
     #LEAVE_315: DLTOVR     FILE(PRGEDI01)
    /*-----------------------*/
     #ENDPGM:    ENDPGM
      Note that the RPG program is no longer needed and differing record format names are irrelevant. Technically, even differing field names wouldn't be relevant if field names were supplied through the driver file. (Also note that it wasn't fully tested because I don't have the same files. I did, however, create a couple generic tables to test a close variation; so, it should be very close.) The worst part is that the files apparently don't use DATE fields. If they did, the SQL could be much simpler and efficiency and speed could be potentially much better (though this could still run faster than the RPG).   The RUNSQL command can be added via PTF to i 6.1 or 7.1. Freeware versions have been available for years, or a QM query replacement can be used even if your system doesn't have the SQL option. The COMMIT(*NONE) option is dependent on your circumstances.   Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    It's getting a little irritating, but I added a comment here a few minutes ago and again it's not showing up. And, again, there are no links nor anything obvious that should hold it up. -- Tom
    125,585 pointsBadges:
    report
  • Eric Witham
    Tom, I think your missing reply went to a different post I wrote
    690 pointsBadges:
    report
  • TomLiotta
    No, the comment is in this thread and will probably be released in the morning (in the New York timezone). -- 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