ODBC and AS/400 trigggers

40 pts.
AS/400 triggers
Will a AS/400 physical file trigger work when the file is updated by ODBC?

Answer Wiki

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

Although I haven’t done it, the answer is yes.
The trigger is a process of the database, since the ODBC is using the DB/2 software the trigger will be activated just as it would be for any other process.

From WaltZ400:
The only problem you might have is with file authority. Normally an ODBC job is running under a genric user profile (Ex QUSER). The trigger program will fire but if you are updating files that the ODBC user does not have authority to, the trigger program could fail. We have trigger programs here that are created by a user profile with the correct authorities needed and has *OWNER authority on the program.

From Koohiisan:
The problem I had was that the trigger, when activated by ODBC, does not have a library list that included our programs library. So, basically, when the trigger would try to run it could not find the program to execute, and would just fail to work. I solved this by putting the trigger program in the same library as the physical file. I know I also could have added the libraries I needed from within the program…but…I chose the quick solution. HTH.

Thanks to all replies. My trigger file is now set up in production.

Discuss This Question: 5  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.
  • PSMurray
    The big problem is the Library List (IMHO). We have several ODBC users that modify our Customer Master. They are basically remote locations (computers) that update our Data Base with Orders or Open A/R via an ODBC from a script that the remote user or application uses. When the ODBS user connects, it establishes the library list of the user that logged in. Remote Computer. The user profile on the AS/400 establishes the library list.
    530 pointsBadges:
  • philpl1jb
    Sounds like the trigger programs should be placed in a library that is in the SYS part of the library list This would insure that any process would have them available. These programs should manage any other libraries that they need in their code.
    54,090 pointsBadges:
    Hi, reply for Trigger with ODBC is yes. Constraints are as already explained, library lists but also file allocation and locking. A reliable solution, between others is too build your triggers the following way : - Set the file in manual open (RPG related) to prevent auto open and clash due to unappropriate Library list.
    ** File(s) to be updated                                                      
    FMyfile  UF A E           K DISK    USROPN      
    - Before opening a file, initiate a file override like this one :
    *** MyFile          opened ?                                        *                  
    c                   if        not %open(Myfile  )                        (B10)  
    c                   eval      command     = 'OVRDBF FILE(Myfile)'               
    c                                         + ' TOFILE(lib_name/Myfile)'         
    c                   eval      len_command = %len(%trim(command))                  
    c                   call(e)   'QCMDEXC'                                           
    c                   parm                    command                               
    c                   parm                    len_command                           
    c                   open      myfile            
    c                   endif                                                  (E10)
    Dot not underestimate the file allocation and locking issue. if you ODBC link remains opened, there are high risks that the file will remain allocated and therefore not available for some safe process like full backup. Hope this may help.
    470 pointsBadges:
  • Pcatlin
    If you're really using ODBC from a remote machine instead of JDBC, then another way to influence the library list is on the ODBC driver connection on the remote PC. On the 2nd tab (Server) within the iSeries Access ODBC driver there is a field for "Library list". If you add the library of the trigger program there, the program will attempt to execute. You can add as many libraries there as you want (up to the 25 or 250 limit of your OS version). This will add those libraries to the job that is opened in QUSRWRK when the connection request is processed. All the other notes about security and authorization that WaltZ400 mentioned still apply, as well as the locking considerations from TSCHMITZ. I would never put the library in the SYS portion...
    200 pointsBadges:
  • TomLiotta
    I would never put the library in the SYS portion… This is especially true since production libraries should be *PUBLIC *EXCLUDE and users cannot sign on if not authorized to use libraries in the system portion of the library list. Tom
    125,585 pointsBadges:

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.

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


Share this item with your network: