You know, I'm wondering if it has something to do with a sproc compile option. I see that there's a commitment control parm and it's set to *NONE. 

I haven't use Navigator's SQL interface (although STRSQL constantly reminds me its avalable)

What do you mean by "VIEW"? Is this an IBM or 3rd Party Tool?

The Commit(*NONE) option simply means the program runs without commitment control/journalling required to be active.

Shouldn't matter how or from where the table gets updated. The trigger is not on the program, procedure, or other thingamajig doing the updating; the trigger is on the table. Could the trigger have been STATE(*DISABLED) at the time of the update?

Hi Bob. View is a product like DBU. Not sure who put's it out though. Dale, the trigger isn't disabled, because when I update the file from within a 5250 session, the trigger fires just fine. This problem really has me baffled, because the stored procedure is updating the file, but in that case the trigger program doesn't run. 

Are you sure it's not being called? Any chance it could be called but not taking the action you're expecting? What's in the job log?

Trigger programs are called in-line by the job that modifies the data. Try starting debug on the trigger program, which would be one way to verify it's called or not. You could also TRCJOB for *FLOW, though that's a bit messy.

Does the job's user have authority to the trigger program? Does the trigger program run under adopted authority of a user that has authority to objects referenced by the trigger program?

Hi Dale. The trigger pgm is user profile *USER and adopted authority *YES. I've tried just putting the trigger pgm in debug prior to running the sproc, but nothing happens. 

Darn it!! I just found something in the log. It appears that the trigger program is calling the program associated with an update to that file, but the program cannot be found. Is there somehow a library list associated with the stored procedure when called from Navigator? And for that matter, when the sproc is called from the outside? 

> s there somehow a library list associated with the stored procedure when called from Navigator?

Yes. Go to "Run SQL !--script--s", click on "Connection", "JDBC Setup", "Format" and change your naming from *SQL to *SYS. *SYS always tells SQL to use the *LIBL (PHP, STRSQL, iSeries Nav, embedded SQL in RPG, etc). Click on "Server" tab and add your Library names. These names are not replacing the *LIBL but instead get added to the (QZDASOINIT) job's *LIBL.

Chris R

Thanks for the info, Chris. I'm a lot closer based on the info you've given me. If the user profile I've assigned to the server connection in Navigator has the library list I'm looking for, would I need to add the libraries to the server tab?

No, if the connecting User Profile already has the correct *LIBL defined. OS/400 takes the QZDASOINIT QUSER job, makes your connection user profile the CURRENT job user and sets the *LIBL based on your connection user profile then runs your programs.

Do a WRKJOB on the job info listed in iSeries Navigator. Look at the current user profile and *LIBL for that job.

"Connected to relational database MYSYSNAME on MYSYSNAME as MyUser- 123456/QUSER/QZDASOINIT"

Chris R

Sorry, Chris, apparently I'm an idiot. I don't know where in Navigator you're asking me to enter WRKJOB. I checked the properties for the system connection and it has my profile.

Go to a separate 5250 session and do the WRKJOB.

Would this be the job?

 QPWFSERVSO   PROCTORC    PJ       .0                   DEQW  

The log says:

SERVICING USER PROFILE PROCTORC FROM CLIENT hq-cproctor-410.columbia.csc

Interesting.... That obviously was the job, because when I closed Navigator it went away. I thought it was weird, because when I selected 5 to display the job, the user at the top changed from PROCTORC to QUSER. I looked that the library list for that job and it only included SYS and USR libraries. It was missing the libraries that I would normally see for my PROCTORC profile. 

Does that make any sense? Sorry to be a pain, Chris. I really appreciate your assistance.

The *LIBL gets set when the user connects to the backend job. The connection user ID becomes the "Current" user for job. A job has 2 users. The user id that the job started with, and the "current" user for the job. For a green screen app, these are the same 99.9% of the time. For a client/server app, the current user id flips back and forth as clients connect and disconnect.OS/400 does profile swapping to accomplish this.

When you are running an SQL stored procecure, the iSeries Nav window tells you name of that backend job under the "messages" tab at the bottom of the window, typically a QZDASOINIT job (on my system anyway).

Chris R

Yes, it does for me too, Chris, it's:

Connected to relational database ORION on 10.45.192.64 as Proctorc - 248426/Quser/Qzdasoinit

Which, I would think, would call the sproc with the library list defined for proctorc. 

That doesn't appear to be the case, for some reason.

This is getting frustrating! Lol  

This is the error I'm seeing:

MESSAGE . . . . :   THE CALL TO *LIBL/J78HST_BLD ENDED IN ERROR (C G D F).     

CAUSE . . . . . :   RPG PROCEDURE TRGLABCTL IN PROGRAM CSSTGOBJ/TRGLABCTL AT   

  STATEMENT 008700 CALLED PROGRAM OR PROCEDURE *LIBL/J78HST_BLD, WHICH ENDED   

  IN ERROR. IF THE NAME IS *N, THE CALL WAS A BOUND CALL BY PROCEDURE POINTER. 

RECOVERY  . . . :   CHECK THE JOB LOG FOR MORE INFORMATION ON THE CAUSE OF THE 

  ERROR AND CONTACT THE PERSON RESPONSIBLE FOR PROGRAM MAINTENANCE.          

And here's how I have the called defined in the trigger program:

 dloadHist         pr                  extpgm('J78HST_BLD')   

d pgm                           10a                          

d ver                           10a                          

d*  standalone variables definition                     

d program         s             10a   inz('P78VSN_BLD') 

d version         s             10a   inz('LOYAL_LAB')  

 /free                                                        

    //  map trigger buffer to data structures                 

    befrecptr = %addr(trgBuffer) + trgBuffer.befrecoffset;    

    aftrecptr = %addr(trgBuffer) + trgBuffer.aftrecoffset;    

    // call loyalty lab history load program if flag = 'N'    

    if new.lab_ready = 'N';                                   

      loadHist(program:version);                              

    endif;                                                    

    return;                                                   

 /end-free                                                    

Is there anything wrong with the way I'm calling this? It works fine from a 5250 session.

Doesn't "sound" like a *LIBL issue. What is the error Msg ID? Put the QZDASOINIT job in debug with STRSRVJOB and step through it when called from iSeries Nav.

Chris R

chrisp,

Did you figure this one out?

Chris R