Home > IT Answers > AS/400 > ODBC and AS/400 trigggers
Jjjlake
40 pts.
 ODBC and AS/400 trigggers
AS/400, AS/400 triggers, ODBC
Will a AS/400 physical file trigger work when the file is updated by ODBC?

Software/Hardware used:
ASKED: September 18, 2008  12:08 PM
UPDATED: July 31, 2010  8:49 AM
RELATED QUESTIONS
Answer Wiki:
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.
Last Wiki Answer Submitted:  November 11, 2008  12:58 pm  by  Koohiisan   4,990 pts.
All Answer Wiki Contributors:  Koohiisan   4,990 pts. , WaltZ400   645 pts. , philpl1jb   44,190 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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.

PSMurray
 530 pts.
 

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.

philpl1jb
 44,190 pts.
 

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.

TSCHMITZ
 470 pts.
 

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…

Pcatlin
 200 pts.
 

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

TomLiotta
 108,260 pts.