neat - use extfile instead of OVRDBF

[jamief]

If you happen to work on a system with multiple companies in different libraries.
then this is a bit cleaner solution than the 'QCMDEXC' - OVRDBF way.

anyway here it is:

~
     FOEHDORDM  if   e           k disk    usropn   extfile(FileName1)

     d DASRVC          s              2  0 inz(72)
     d FileName1       s             21

      /free
        //--------------------------------------------------------
        // MAIN PROGRAM
        //--------------------------------------------------------

             exsr  Hskpg;
             exsr  $override;


             *inlr = *on;

        //--------------------------------------------------------
        // $Override OEHDORDM to the correct library
        //--------------------------------------------------------
            begsr $Override;

              if %open(OEHDORDM);
                close OEHDORDM;
              endif;

            //
            // setup override depending on company
            //

              monitor;

                select;
                  when DASRVC =  15;
                    filename1 = 'LBIQUOTES/OEHDORDM';

                  when DASRVC =  18;
                    filename1 = 'LSNQUOTES/OEHDORDM';

                  when DASRVC =  20;
                    filename1 = 'HSAQUOTES/OEHDORDM';

                  when DASRVC =  30;
                    filename1 = 'PDMQUOTES/OEHDORDM';

                  when DASRVC =  72;
                    filename1 = 'GMCQUOTES/OEHDORDM';

                endsl;

              on-error;
              endmon;

              if not%open(OEHDORDM);
                open OEHDORDM;
              endif;

            endsr;

        //--------------------------------------------------------
        // Hskpg - one time run subroutine
        //--------------------------------------------------------
             begsr Hskpg;


             endsr;
      /End-Free
 

[gcraill]

Re: neat - use extfile instead of OVRDBF

Cheers Jamie - had to use this today ...

[tomholden]

Re: neat - use extfile instead of OVRDBF

however extfile is much more limited in functionality than OVRDBF. extfile is allow you to override to a different file (and with extmbr and member within the file) but OVRDBF offers much more control capabilities which in some cases are needed. if all you need is to override the file and member then those are great, anything needed beyond that still requires OVRDBF via QCMDEXC or a CL (personally unless there's tons of commands used i prefer the QCMDEXC from within my RPG)

[NickLitten]

Re: neat - use extfile instead of OVRDBF

EXTFILE and EXTMBR roast my raisins. in a good way. love em.

OVRDBF smells of pilchards.

[bryce4president]

Re: neat - use extfile instead of OVRDBF

What about just using embedded SQL?

[B.Hauser]

Re: neat - use extfile instead of OVRDBF

What about just using embedded SQL?

Even though almost everything can be done with static SQL, it is not possbile to use host variables for libraries/schemas and/or files/tables/Views and fill them on the fly.
With embedded SQL you either need dynamic SQL or an override.

Birgitta

[bryce4president]

Re: neat - use extfile instead of OVRDBF

Ok, when I said embedded SQL, I was implying dynamic SQL. Is that still not embedded in RPGLE??? Static is not what you would want, but dynamic is.

I have never had the need for an override and have yet to see a case where complicating the code with such is neccessary. I could be wrong, its been known to happen so if someone can give me a scenario where an override would be the best solution please enlighten me. I think that with proper design an override should not be needed, but again, I could just be too young to understand

[jamief]

Re: neat - use extfile instead of OVRDBF

was this

I think that with proper design and override should not be needed

meant to be

I think that with proper design an override should not be needed

[bryce4president]

Re: neat - use extfile instead of OVRDBF

Yes, indeed Jamie. I edited the original post.

[jamief]

Re: neat - use extfile instead of OVRDBF

I'm not correcting grammar cause God knows I need to
take the beam outta my own eye b4 I attempt to remove the splinter from yours..

Just trying to clarify this:
--- So I think you are saying that using multiple Libraries and/or members is a bad system design???????

[B.Hauser]

Re: neat - use extfile instead of OVRDBF

Ok, when I said embedded SQL, I was implying dynamic SQL. Is that still not embedded in RPGLE??? Static is not what you would want, but dynamic is.

Sure it is, but for performance issues static SQL should be preferred where ever possible.

Birgitta

[gcraill]

Re: neat - use extfile instead of OVRDBF

I have never had the need for an override and have yet to see a case where complicating the code with such is neccessary. I could be wrong, its been known to happen so if someone can give me a scenario where an override would be the best solution please enlighten me. I think that with proper design an override should not be needed, but again, I could just be too young to understand

It's that too young thing whipper-snapper.

We have (Telco) files that store customers calls, and we process those each time we bill the customers. As a design I like the idea of splitting them up into smaller files for groups of customers, rather than having all in one file. each little file we have can be approx 100 million records. Now the program is compiled over "one" of these files, but when we have say 40 of these "smaller chuck" files we override when they are accessed. I think performance and managebility is better than for a single file of 4 billion records.

Exact numbers have been blurred for confidentiality reasons but the gist above is accurate.

[kitvb1]

Re: neat - use extfile instead of OVRDBF

Well... if it was a UDTF you can soft-code (i.e. have it passed as a parameter) the table name in the DECLARE.
So it in your program you would just close the cursor, change the parameter value, and open it again.

[bryce4president]

Re: neat - use extfile instead of OVRDBF

But wouldn't using SQL be faster over that many records? As Kit pointed out this can be done with SQL. And I think that with fetching blocks with SQL since you seem to be going sequentially would be a better fit, no? I understand that overrides can be used, but I'm looking for a sure fire situation where its better than all other approaches, especially SQL.

[MichaelCatalani]

Re: neat - use extfile instead of OVRDBF

If you are designing a system from scratch, then you should be able to design it to take advantage of of SQL.

But if you are retro-fitting changes to an existing system, then you could be dealing with something where the original design made extensive use of things like multi-member files, which made it rather difficult to utilize SQL. For instance, a system that processed a file that contained many members, of which the member names may not be known at run time, could easily be processed by a RTVMBRD command specifying *NEXT for the member name, and simply override the file to that member. It could perform this in a loop until no more members were retrieved, without ever having to know the member name in advance. Doing something similar in SQL used to be practically impossible, since we needed to have created an alias for the member prior to running the process.