AS400 Trigger

[jde_jde]

Hi,

I am working on an interface between JDE and the third party application. This is a "near real time" interface. This third party apps updates JDE custom table (e.g.F58XYZ). After this third party updates the F58XYZ table, I want to update JDE standard tables and also want to call UBE / NER / C BSFN. So to accomplish this I wrote a JDE table trigger on F58XYZ table, but it didn't work. Later on I got to know that JDE table trigger doesn't work (execute) when update/insert/delete is performed by non-JDE application.

Now I am thinking of writing an AS400 native trigger. But I am not sure whether AS400 native trigger can call UBE / NER / C BSFN or not. I am not sure about the performance or robustness of this approach. Could anyone please throw some lights on this approach?

Or any other suggestion/ views are also most welcome on this.

Let me know if more information is required on my question. Any help is much appreciated.

Thanks in advance!!!!

Platform: AS/400
Platform Type: Enterprise Server
Operating Sys: OS/400
Database: DB2/400
Release: E1 8.11
Service Pack: SP 1
Tool Release: 8.97

 

[eewon]

I did something similar a long while ago to allow a third party sales order system to retrieve prices from JDE. The problem is that the connection needed to be fairly fast, so using the JDE APIs on each request to initiate a connection to JDE would have been too slow. I should have probably investigated using XML Call Object or something similar, but didn't know enough about that at the time. So I used an AS/400 trigger to send a 'message' to an AS/400 batch job that had already logged on to E1, call the E1 pricing business function and then return the price via the record that triggered the process, as follows:

(1) SalesTalk Order Entry program inserts a record via an ODBC connection to the iSeries Online Price Trigger file. The record includes the Customer Number and Product Code that we need to use to calculate the price. SalesTalk will then wait until the insert operation has completed before continuing.
(2) A database trigger runs the Online Price Trigger program. The trigger is set to run before the Insert, so at this point the record has not yet been written to the file.
(3) The Trigger program writes a record to the Price Request file. This file is used to send the price request information to the Server program. The Trigger program cannot calculate the price directly as this would take too long to log onto OneWorld. The Server program, however, will already be logged on.
After this, the Trigger program will then wait for the price to be written to the Price Request file in step (6).
(4) The Server program waits for new records to be added to the Price Request file. This program will have been started at the same time as the system is started and will always be running. The End of File Delay (EOFDLY) option on the read allows the program to wait for the record without consuming resources. When a new record is added to the file, the Server program will wake up.
(5) The Server program will then use the Customer Number and Product Code on the file to calculate the price using the OneWorld business functions.
(6) The price is then written back to the Price Request file. An update flag is also updated on the file to indicate to the Trigger program that the Server program has completed its task.
(7) The Trigger program has been waiting for the update flag to be set. It does this by repeatedly re-reading the Price Request record until the flag is updated. A record lock will also be used by the Server program once it has read the Price Request record, to allow the Trigger program to go into a Lock Wait state until the record is released by the Server program when it updates the record. This will help minimise system resource usage.
(8) The Trigger program then updates the Trigger file record buffer with the price. At this point, the record insert that was initiated by SalesTalk still has not been written to the file. The Trigger program will then terminate and the Trigger file record, including the price, will be written to the database.
(9) At this point, SalesTalk will continue processing once the ODBC connection has confirmed that the Insert has completed normally. The SalesTalk program will then re-read the same record that it has just inserted, and retrieve the price from the record.

It's a bit long winded, but it did work.

Alternatively, for a basic asynchronous solution, how about executing RUNUBE (in the JDE system library) from the table trigger program.

And as you're on 8.11SP1 tools release 8.97 you might also want to investigate whether you can use business services if your third party system can initiate a web service call.

 

[jde_jde]

Hi Jdelist14,

Thanks a lot for your reply. Could you please give some more detail about executing an UBE from AS400 trigger? I am new to AS400 trigger, so if possible then please give detail/command about how to call UBE/BSFN from AS400 trigger.

 

[eewon]

Well, as I say, it's the server job that executes the BSFN, rather than the trigger program. The trigger program is an RPG/LE program that writes a record to a Price Request file. This record is used as a message to the server program that calls the BSFN to return the price. In fact, the setup I used allowed for multiple server jobs and multiple environments etc, but the basic principle is the same.

I'm not sure which bits you are struggling with. With the trigger program, you're best to take a look at the IBM Manual DB2-400 Stored Procedures and Triggers (SG246503) for how to code this, but this section that I've pulled out of my specification document may help:

<font color="purple">The data will appear in the program via two parameters:

TRGBUF: A data structure containing:

- Static information (eg: physical file name, member name, trigger event, trigger time, commit lock level, and CCSID of the current change record and relative record number)

- Offsets and lengths to the records in the dynamic area

- Dynamic Area containing the Old record and New record data.

- Positions 65 to 68 of TRGBUF will contain the offset (in binary format) to the new record data (NOFF).

TRGLEN: Length of buffer
- A 4-byte binary format numeric length of TRGBUF that is passed in the second parameter to the program.

Map the data in the structure, using the new record offset, to the record structure of the trigger file FST001TR. Use an externally described data structure.
</font>

I used RPG to code the trigger program, as I've got an AS/400 background, but you can probably do the same in C.

The server program is a ILE/C program and uses the JDE APIs to initialise an E1 session. I've pulled these bits out the spec that may help, but I'm sure that you can find other examples on JDEList:
<font color="purple">
Initialise the Environment Handle by calling JDB_InitEnvOvr:
<font class="small">Code:</font><hr /><pre>JDB_InitEnvOvr(&hEnv,szEnv,szUser,szPwd)</pre><hr />

If this API fails, then send message ‘JDB_InitEnvOvr failed’ to the default print file and exit the program.

Initialise the User Handle by calling JDB_InitUser:
<font class="small">Code:</font><hr /><pre>JDB_InitUser(hEnv,&hUser,NULL,JDEDB_COMMIT_AUTO)</pre><hr />

If this API fails, then send message ‘JDB_InitUser failed’ to the default print file and exit the program.

Initialise the Behaviour Com by calling jdeCreateBusinessFunctionParms:
<font class="small">Code:</font><hr /><pre>jdeCreateBusinessFunctionParms(hUser,&lpBhvrCom,(LPVOID*)&lpVoid)</pre><hr />

Initialise the Error Handling and other variables, as follows:
<font class="small">Code:</font><hr /><pre> lpVoid->lpHdr = jdeErrorInitializeEx();
lpVoid->lpErrorEventKey = (LPERROR_EVENT_KEY)
jdeAlloc(COMMON_POOL, sizeof(ERROR_EVENT_KEY),
MEM_ZEROINIT | MEM_FIXED);

lpVoid->lpHdr->nCurDisplayed = -1;
lpBhvrCom->lpObj->lpFormHdr = lpVoid->lpHdr;
EventKeyLocal.hwndCtrl = NULL;
EventKeyLocal.iGridCol = 0;
EventKeyLocal.iGridRow = 0;
EventKeyLocal.wEvent = 1;
lpBhvrCom->lpEventKey = (LPVOID) &EventKeyLocal;
</pre><hr />
</font>

You can then execute regular JDE APIs to interrogate the database, execute business functions etc.

To cleanup:

<font class="small">Code:</font><hr /><pre>
if(lpBhvrCom!=NULL)
{
jdeErrorClear(lpBhvrCom);
jdeFree(lpVoid->lpErrorEventKey);
jdeErrorTerminateEx(lpVoid->lpHdr);

jdeFreeBusinessFunctionParms(lpBhvrCom,lpVoid);
lpBhvrCom=NULL;
lpVoid=NULL;
}
if(hUser!=NULL)
{
if (JDB_FreeUser(hUser)!=JDEDB_PASSED)
{
printf("JDB_FreeUser failed\n");
}
else
{
hUser=NULL;
}
}
if(hEnv!=NULL)
{
if (JDB_FreeEnv(hEnv)!=JDEDB_PASSED)
{
printf("JDB_FreeEnv failed\n");
}
else
{
hEnv=NULL;
}
}
</pre><hr />

Dave