SQL ILERPG INSERT Dynamic File Name? - IBM AS400

This is a discussion on SQL ILERPG INSERT Dynamic File Name? - IBM AS400 ; Hi, I need to use a dynamically named file on an INSERT and so far have not been able to get it to work. Can this be done? Here's what I have in place that's not working: FFXFILE IF E ...

+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 20 of 26

Thread: SQL ILERPG INSERT Dynamic File Name?

  1. SQL ILERPG INSERT Dynamic File Name?

    Hi,
    I need to use a dynamically named file on an INSERT and so far have
    not been able to get it to work.

    Can this be done?

    Here's what I have in place that's not working:

    FFXFILE IF E K DISK USROPN
    :
    :
    * The CL being called creates a File FXnnnnnB
    * It also overrides: OVRDBF FILE(FXFILE) TOFILE(LIUNAD01/&FILEB)
    C CALL 'FXTRANCL'
    C PARM FILEB 10
    C PARM FILEN 10
    C OPEN FXFILE
    * Populate BEFORE file from Existing SQL.
    C ExSr POPBEFORE
    :
    :
    C POPBEFORE BEGSR
    C/EXEC SQL INSERT INTO LIUNAD01/FXFILE
    C+ (HESS, HEDEP, HEFED, HESUB, HELOC, HEDATF, HEDTR, HEATYP, HECNTR,
    C+ HECCOD, HEFTYP, HEFAMT, HEDUE, HEWAG, HEDUWG, HECUSR, HECDAT,
    C+ HECTIM, HEUUSR, HEUDAT, HEUTIM, HESEQ, HELNAM, HESRJR, HEFNAM,
    C+ HEMIDL, HERSEQ, HEESEQ, HEAUTH, HESIG, HESTYP, HENAM)
    C+ VALUES :SFDATA
    C/END-EXEC

    After the 1st execution of the insert in my Job Log it states: File
    FXFILE in LIUNAD01 not found.

    Did the override not take effect?
    Is it not possible to dynamically name the file as we are trying?

    TIA
    that 1 guy


  2. Re: SQL ILERPG INSERT Dynamic File Name?

    What you are trying to do seems a little strange, but I think it might
    work if you specify SHARE *YES on the OVRDBF command (possibly you
    will need to add an OPNSCOPE parameter too).


  3. Re: SQL ILERPG INSERT Dynamic File Name?

    On Mar 22, 9:38 am, "walker.l2" wrote:
    > What you are trying to do seems a little strange, but I think it might
    > work if you specify SHARE *YES on the OVRDBF command (possibly you
    > will need to add an OPNSCOPE parameter too).


    Walker,
    We have a workfile that we are trying to populate and we want
    dynamically named workfiles so multiple users can work and not be
    populating the same file. I will try the SHARE *YES and look into
    OPNSCOPE (I'm not sure what this parameter is used with? OVRDBF? I
    will try to identify what command OPNSCOPE is used with).

    thanks for the reply. Any other input is welcomed.
    that 1 guy


  4. Re: SQL ILERPG INSERT Dynamic File Name?

    What's the problem with having multiple users adding to the same file?
    If you are adding the records via SQL, you shouldn't have any record /
    file lock issues.

    (OPNSCOPE is another parameter on OVRDBF)


  5. Re: SQL ILERPG INSERT Dynamic File Name?

    On Mar 22, 10:25 am, "walker.l2" wrote:
    > What's the problem with having multiple users adding to the same file?
    > If you are adding the records via SQL, you shouldn't have any record /
    > file lock issues.
    >
    > (OPNSCOPE is another parameter on OVRDBF)


    The boss says we'll shoot ourselves in the foot if we allow multiple
    users to access the same file with the potential of having like
    records. He says this could be very bad for what we are trying to
    accomplish. The records in this workfile will eventually (during the
    job) be edtied by the user. Yes, I found OPNSCOPE as well. Using
    SHARE *YES & OPNSCOPE (*ACTGRPDFN or *JOB) yeilded the same
    result...File not found when I tried to INSERT to it.

    I found that there is an EXECUTE command that I can use. In the
    example they used it for an INSERT. So I'm going to try and
    dynamically build the INSERT into a host-variable and CAT the file
    name (it's being passed back to the Prgm from the CL) to the INSERT
    then issue a PREPARE & then do an EXECUTE.

    Any better ideas?

    TIA
    that 1 guy


  6. Re: SQL ILERPG INSERT Dynamic File Name?

    I don't think you will be able to dynamically assign the file name in
    an INSERT. :-(

    I still don't see why using the same file will be a problem - I'm sure
    you can arrange a suitable identifier (UserID, Job Number, Row_ID
    etc.) to differentiate between the different users.


  7. Re: SQL ILERPG INSERT Dynamic File Name?

    that 1 guy wrote:
    > On Mar 22, 10:25 am, "walker.l2" wrote:
    >> What's the problem with having multiple users adding to the same file?
    >> If you are adding the records via SQL, you shouldn't have any record /
    >> file lock issues.
    >>
    >> (OPNSCOPE is another parameter on OVRDBF)

    >
    > The boss says we'll shoot ourselves in the foot if we allow multiple
    > users to access the same file with the potential of having like
    > records. He says this could be very bad for what we are trying to
    > accomplish. The records in this workfile will eventually (during the
    > job) be edtied by the user. Yes, I found OPNSCOPE as well. Using
    > SHARE *YES & OPNSCOPE (*ACTGRPDFN or *JOB) yeilded the same
    > result...File not found when I tried to INSERT to it.
    >
    > I found that there is an EXECUTE command that I can use. In the
    > example they used it for an INSERT. So I'm going to try and
    > dynamically build the INSERT into a host-variable and CAT the file
    > name (it's being passed back to the Prgm from the CL) to the INSERT
    > then issue a PREPARE & then do an EXECUTE.
    >
    > Any better ideas?
    >


    You might also look into the OVRSCOPE parameter of OVRDBF.

    Also, if you are only inserting 1 row (or a very small number of rows),
    and the complete data VALUES could be appended to the statement
    string, the EXECUTE IMMEDIATE statement might be an option (vs PREPARE &
    EXECUTE USING ).

    http://publib.boulder.ibm.com/infoce...afzmstexim.htm

    --
    Karl Hanson

  8. Re: SQL ILERPG INSERT Dynamic File Name?

    On Mar 22, 11:15 am, "walker.l2" wrote:
    > I don't think you will be able to dynamically assign the file name in
    > an INSERT. :-(
    >
    > I still don't see why using the same file will be a problem - I'm sure
    > you can arrange a suitable identifier (UserID, Job Number, Row_ID
    > etc.) to differentiate between the different users.


    The boss says the workfile is to be an exact replica of the history
    file. The records will be used to unpost then repost to the history
    file, so if I add those fields I no-longer have an exact replica.

    Karl,
    I will read about the EXECUTE IMMEDIAMTE command. I had tried the
    OVRSCOPE Parameter earlier with no luck. Still received the File not
    found error in the job log and no records written.

    thanks,
    that 1 guy


  9. Re: SQL ILERPG INSERT Dynamic File Name?

    il 22/03/2007 14.50, Scrive that 1 guy 38280992:
    > Hi,
    > I need to use a dynamically named file on an INSERT and so far have
    > not been able to get it to work.
    >
    > Can this be done?
    >
    > Here's what I have in place that's not working:
    >
    > FFXFILE IF E K DISK USROPN
    > :
    > :
    > * The CL being called creates a File FXnnnnnB
    > * It also overrides: OVRDBF FILE(FXFILE) TOFILE(LIUNAD01/&FILEB)
    > C CALL 'FXTRANCL'
    > C PARM FILEB 10
    > C PARM FILEN 10
    > C OPEN FXFILE
    > * Populate BEFORE file from Existing SQL.
    > C ExSr POPBEFORE
    > :
    > :
    > C POPBEFORE BEGSR
    > C/EXEC SQL INSERT INTO LIUNAD01/FXFILE
    > C+ (HESS, HEDEP, HEFED, HESUB, HELOC, HEDATF, HEDTR, HEATYP, HECNTR,
    > C+ HECCOD, HEFTYP, HEFAMT, HEDUE, HEWAG, HEDUWG, HECUSR, HECDAT,
    > C+ HECTIM, HEUUSR, HEUDAT, HEUTIM, HESEQ, HELNAM, HESRJR, HEFNAM,
    > C+ HEMIDL, HERSEQ, HEESEQ, HEAUTH, HESIG, HESTYP, HENAM)
    > C+ VALUES :SFDATA
    > C/END-EXEC
    >
    > After the 1st execution of the insert in my Job Log it states: File
    > FXFILE in LIUNAD01 not found.
    >
    > Did the override not take effect?
    > Is it not possible to dynamically name the file as we are trying?
    >
    > TIA
    > that 1 guy
    >

    Instead of using OVRDBF create an alias on the file to insert into, e.g.

    CREATE ALIAS LIUNAD01/FXFILE FOR LIUNAD01/fileb:

    then you can drop the alias instead of dltovr, e.g.:
    DROP ALIAS LIUNAD01/FXFILE

    The alias implementation create a ddm file so it should be accessible
    also by the "OPEN FXFILE" statement

    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

  10. Re: SQL ILERPG INSERT Dynamic File Name?

    Dr. Ugo,
    > Instead of using OVRDBF create an alias on the file to insert into, e.g.
    >
    > CREATE ALIAS LIUNAD01/FXFILE FOR LIUNAD01/fileb:
    >
    > then you can drop the alias instead of dltovr, e.g.:
    > DROP ALIAS LIUNAD01/FXFILE
    >
    > The alias implementation create a ddm file so it should be accessible
    > also by the "OPEN FXFILE" statement


    I wish I had seen this before. I may go back and change the code and
    I may not because I do have it working now.

    Karl,
    I could not get EXECUTE IMMEDIATE to work, but I did get the PREPARE
    & EXECUTE scenario to work. Here's what I did:

    D INS S 70A DIM(10) CTDATA PERRCD(1)
    D InsertString S 630A
    :
    :
    C POPBEFORE BEGSR
    * Prepare the Insert Statement.
    C Ins(1) CAT FILEB:0 InsertString
    C InsertString CAT Ins(2):1 InsertString
    C InsertString CAT Ins(3):1 InsertString
    C InsertString CAT Ins(4):1 InsertString
    C InsertString CAT Ins(5):1 InsertString
    C InsertString CAT Ins(6):1 InsertString
    C InsertString CAT Ins(7):1 InsertString
    :
    :
    *
    C/EXEC SQL PREPARE FXINSERT FROM :InsertString
    C/END-EXEC
    *
    :
    C/EXEC SQL EXECUTE FXINSERT USING :SFSS, :SFDEP, :SFFED, :SFSUB,
    C+ :SFLOC, :SFDATF, :SFDTR, :SFATYP, :SFCNTR, :SFCCOD, :SFFTYP,
    C+ :SFFAMT, :SFDUE, :SFWAG, :SFDUWG, :SFCUSR, :SFCDAT, :SFCTIM,
    C+ :SFUUSR, :SFUDAT, :SFUTIM, :SFSEQ, :SFLNAM, :SFSRJR, :SFFNAM,
    C+ :SFMIDL, :SFRSEQ, :SFESEQ, :SFAUTH, :SFSIG, :SFSTYP, :SFNAM
    C/END-EXEC
    *
    * I built InsertString from a PreCompile Table/Array
    ** Text for Dynamic INSERT (Ins)
    INSERT INTO LIUNAD01/
    (HESS, HEDEP, HEFED, HESUB, HELOC, HEDATF, HEDTR, HEATYP, HECNTR,
    HECCOD, HEFTYP, HEFAMT, HEDUE, HEWAG, HEDUWG, HECUSR, HECDAT,
    HECTIM, HEUUSR, HEUDAT, HEUTIM, HESEQ, HELNAM, HESRJR, HEFNAM,
    HEMIDL, HERSEQ, HEESEQ, HEAUTH, HESIG, HESTYP, HENAM)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

    Now I don't to have FXFILE in my F-specs, don't need to override in
    the CL, don't need the opens or closes of FXFILE.

    Thanks once again for you input. It is greatly appreciated.
    that 1 guy


  11. Re: SQL ILERPG INSERT Dynamic File Name?

    That is a very nice solution.


  12. Re: SQL ILERPG INSERT Dynamic File Name?

    Well if your work file already has a unique key (Order Number for
    example), then the obvious way round this is another file that relates
    the unique key to the particular user who created it.


  13. Re: SQL ILERPG INSERT Dynamic File Name?

    On Mar 23, 4:45 am, "walker.l2" wrote:
    > That is a very nice solution.


    Thank you Walker. It seems to be working quite nicely. I have
    changed the LIB for these WorkFiles. They are now being created in
    QTEMP so that they clean themselves up when no-longer being used. Now
    I'm moving on to the real dirty work...using the data in the BEFORE
    file (the one I just inserted to) to Unpost from 4 Files, then I need
    a user interface to allow them to change data in the records, then I
    need to Repost the changed data back to the 4 files & I'm done.
    Hopefully we'll have another satisfied customer. LOL.

    thanks again everyone,
    that 1 guy


  14. Re: SQL ILERPG INSERT Dynamic File Name?

    On Mar 23, 1:11 pm, "that 1 guy" wrote:
    > On Mar 23, 4:45 am, "walker.l2" wrote:
    >
    > > That is a very nice solution.

    >
    > Thank you Walker. It seems to be working quite nicely.
    >

    I meant it was a nice solution to dealing with multiple file names. I
    still don't like the idea of each user having their own file;
    obviously you know your requirements and restraints better than I do,
    but the "1 file per user" solution seems wrong to me (offhand, I can't
    think of any cirumstances when I would use that approach).

    > I have
    > changed the LIB for these WorkFiles. They are now being created in
    > QTEMP so that they clean themselves up when no-longer being used.
    >

    You had better hope their jobs don't crash after a lot of input
    then. :-)

    > Now
    > I'm moving on to the real dirty work...using the data in the BEFORE
    > file (the one I just inserted to) to Unpost from 4 Files, then I need
    > a user interface to allow them to change data in the records, then I
    > need to Repost the changed data back to the 4 files & I'm done.
    > Hopefully we'll have another satisfied customer. LOL.
    >

    Hang on a minute. Do you mean that the whole point of this is to keep
    a log of "Before Images" of records every time a change is made, and
    that the user making the change also needs to be logged? Journals will
    do all of that for you.


  15. Re: SQL ILERPG INSERT Dynamic File Name?

    Walker -

    Then, how do you employ the Journal? I've seen people talking about
    journals and so forth. Including seeing them in documents, but no IBM
    or IBM Reps mention to me the need of it on our system. What would be
    the pro & cons of employing Journals?

    Gil

    walker.l2 wrote:
    > On Mar 23, 1:11 pm, "that 1 guy" wrote:
    >
    >>On Mar 23, 4:45 am, "walker.l2" wrote:
    >>
    >>
    >>>That is a very nice solution.

    >>
    >>Thank you Walker. It seems to be working quite nicely.
    >>

    >
    > I meant it was a nice solution to dealing with multiple file names. I
    > still don't like the idea of each user having their own file;
    > obviously you know your requirements and restraints better than I do,
    > but the "1 file per user" solution seems wrong to me (offhand, I can't
    > think of any cirumstances when I would use that approach).
    >
    >
    >> I have
    >>changed the LIB for these WorkFiles. They are now being created in
    >>QTEMP so that they clean themselves up when no-longer being used.
    >>

    >
    > You had better hope their jobs don't crash after a lot of input
    > then. :-)
    >
    >
    >>Now
    >>I'm moving on to the real dirty work...using the data in the BEFORE
    >>file (the one I just inserted to) to Unpost from 4 Files, then I need
    >>a user interface to allow them to change data in the records, then I
    >>need to Repost the changed data back to the 4 files & I'm done.
    >>Hopefully we'll have another satisfied customer. LOL.
    >>

    >
    > Hang on a minute. Do you mean that the whole point of this is to keep
    > a log of "Before Images" of records every time a change is made, and
    > that the user making the change also needs to be logged? Journals will
    > do all of that for you.
    >


  16. Re: SQL ILERPG INSERT Dynamic File Name?

    > I meant it was a nice solution to dealing with multiple file names. I
    > still don't like the idea of each user having their own file;
    > obviously you know your requirements and restraints better than I do,
    > but the "1 file per user" solution seems wrong to me (offhand, I can't
    > think of any cirumstances when I would use that approach).


    I hear you, but I have a boss and this is what he wants no deviation
    and I will satisfy his wishes.
    >
    > > I have
    > > changed the LIB for these WorkFiles. They are now being created in
    > > QTEMP so that they clean themselves up when no-longer being used.

    >
    > You had better hope their jobs don't crash after a lot of input
    > then. :-)


    So, you think QTEMP is not a good idea?

    > Hang on a minute. Do you mean that the whole point of this is to keep
    > a log of "Before Images" of records every time a change is made, and
    > that the user making the change also needs to be logged? Journals will
    > do all of that for you.


    I understand that Journals will accomplish this. Our approach is
    antiquated here and am aware of this.

    I do appreciate your input. I am listening but unable deviate from
    this current course.

    that 1 guy


  17. Re: SQL ILERPG INSERT Dynamic File Name?

    > I hear you, but I have a boss and this is what he wants no deviation
    > and I will satisfy his wishes.
    >

    :-)

    > So, you think QTEMP is not a good idea?
    >

    It might be, it might not be. (It probably is.) I don't know your
    application / environment well enough to answer, but whenever you use
    QTEMP it is always worth asking two questions:

    1) How likely is it that the application will crash / the user session
    will be terminated before the work is complete (either by the user by
    mistake, or by a comms problem, or a power outage)?

    2) If any of those situations happens, how much data is lost, and how
    hard / long will recovering / restoring the data be / take.

    > I understand that Journals will accomplish this. Our approach is
    > antiquated here and am aware of this.
    >

    Well we're still writing OPM (not ILE), so I know all about
    antiquated. :-)


  18. Re: SQL ILERPG INSERT Dynamic File Name?

    Journals allow you to track what changes have been made on your system
    (could be data changes in files / tables, or system value changes,
    etc.), when those changes were made, and who made them. They also
    allow you to use Commitment Control, and are often the mechanism used
    to implement High Availability / Disaster Recovery packages (i.e. data
    replication to another partition / machine).
    Using journals will require some extra disk space for the Journal
    Receivers (but journal entries are compacted, so you shouldn't need
    much more space), and I suppose there might be a performance impact
    (but this is likely to be negligible).

    There was a recent short discussion on them here:
    http://groups.google.com/group/comp....9edf0dec66ec98

    We journal almost every file on our machines.

    The basic sequence (if I remember correctly) is CRTJRNRCV (creates a
    place to store the journal entries), CRTJRN (defines how the JRNRCV
    will be managed), STRJRNPF (starts logging changes to files).


  19. Re: SQL ILERPG INSERT Dynamic File Name?

    > > So, you think QTEMP is not a good idea?
    >
    > It might be, it might not be. (It probably is.) I don't know your
    > application / environment well enough to answer, but whenever you use
    > QTEMP it is always worth asking two questions:
    >
    > 1) How likely is it that the application will crash / the user session
    > will be terminated before the work is complete (either by the user by
    > mistake, or by a comms problem, or a power outage)?
    >
    > 2) If any of those situations happens, how much data is lost, and how
    > hard / long will recovering / restoring the data be / take.


    Great points, thanks for sharing them!

    >

    still writing OPM (not ILE), so I know all about
    > antiquated. :-)


    Starting last year was the 1st I had ever used ILE RPG and this is my
    1st project using Embedded SQL in ILERPG.

    peace
    that 1 guy


  20. Re: SQL ILERPG INSERT Dynamic File Name?

    walker.l2 wrote:
    > Journals allow you to track what changes have been made on your system
    > (could be data changes in files / tables, or system value changes,
    > etc.), when those changes were made, and who made them. They also
    > allow you to use Commitment Control, and are often the mechanism used
    > to implement High Availability / Disaster Recovery packages (i.e. data
    > replication to another partition / machine).
    > Using journals will require some extra disk space for the Journal
    > Receivers (but journal entries are compacted, so you shouldn't need
    > much more space), and I suppose there might be a performance impact
    > (but this is likely to be negligible).
    >
    > There was a recent short discussion on them here:
    > http://groups.google.com/group/comp....9edf0dec66ec98
    >
    > We journal almost every file on our machines.
    >
    > The basic sequence (if I remember correctly) is CRTJRNRCV (creates a
    > place to store the journal entries), CRTJRN (defines how the JRNRCV
    > will be managed), STRJRNPF (starts logging changes to files).
    >


    It might be worth noting that by default CREATE SCHEMA creates a journal
    and receiver in the library, so that a subsequent CREATE TABLE
    automatically journals the physical file.

    http://publib.boulder.ibm.com/infoce...fyxdbterms.htm
    http://publib.boulder.ibm.com/infoce...yconschema.htm

    --
    Karl Hanson

+ Reply to Thread
Page 1 of 2 1 2 LastLast