Re: SQL Triggers where Production and Test on Same iSeries -- MIDRANGE-L

Thanks Gary, quick question though, probably stupid (even though teachers
say there are no stupid questions). We had a pretty big issue occur
yesterday with another file that is journaled. I thought I knew what
caused the issue, however, looking at those programs, it doesn't appear
that my thought was correct. So here I am... I'm thinking about doing this
RCVJRNE command to call a program and track the changes. I understand I
can do RCVJRNE for specified journaled files. But I was wondering, if I
set this up one time for one file in the journal.
1. What happens when I come back and want to add another exit point program
for a different file?
2. Do the program calls line up or does the last RCVJRNE erase the
previous? Does that make sense?
3. Also is there a way I can see what exit programs are attached to a
journal?
4. Finally, will this interfere any with the QEDD back up stuff? QEDD
currently takes the changes to another box and our back ups are performed
off that box.

thanks again

On Wed, Apr 10, 2013 at 5:55 PM, Monnier, Gary <Gary.Monnier@xxxxxxxxx>wrote:

You can take a look at

http://archive.midrange.com/midrange-l/200812/msg00419.html
http://comments.gmane.org/gmane.comp.hardware.ibm.midrange/176947

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzaki%2Frzakircvjrnentry.htm

The parameters for an ILE RPG program as the exit program being called for
*TYPE4 format are...

//-----------------------------
// Program Procedure Definition
//-----------------------------

D yourprogram PR extpgm('yourprogram')
D inJrnString 65534A
D inJrnCntrl 3A

//-----------------------------
// Program Procedure Interface
//-----------------------------

D yourprogram PI
D inJrnString 65534A
D inJrnCntrl 3A

Here is a skeleton you can build upon. The "Do what you need to" can point
to embedded SQL statements if you so choose.

//====================================================================
//
// Working variables
//
//====================================================================

D yourRowStruct E DS extname(yourtablename)
D yourHstRow E DS extname(yourHistoryTableName)

D entrySpecific S 32767A

D jrnControlDS DS
D jePassedIn 1A
D jeProcessCtl 1A
D jeFixedName 1A

D journalEntryDS DS
D jeEntryLength 5S 0
D jeSeqNumber 10S 0
D jeJournalCode 1A
D jeEntryType 2A
D jeDateTime 26Z
D jeJobName 10A
D jeUserName 10A
D jeJobNumber 6S 0
D jeProgramName 10A
D jeObjectName 10A
D jeObjectLib 10A
D jeMemberName 10A
D jeCountRRN 10S 0
D jeFlag 1A
D jeCommitCycle 10S 0
D jeUserProfile 10A
D jesystemName 8A
D jeJournalID 10A
D jeRefConstrain 1A
D jeTrigger 1A
D jeIncomplete 1A
D jeIgnore 1A
D jeMinentry 1A
D jeIBMReserved 5A
D jeNullValues 1A
D jeESData 32767A

//********************************************************************
//*
//* C A L C U L A T I O N S P E C I F C A T I O N S
//*
//********************************************************************

/FREE

//---------------------
// Initialize variables
//---------------------

jrnControlDS = inJrnCntrl;

select;

when (jePassedIn = '1');

//jePassedIn = '8';

if (jeProcessCtl = 'N');
jePassedIn = '9';
endif;

exsr checkJournalEntry;

other;

jePassedIn = '9';

endsl;

inJrnCntrl = jrnControlDS;

//------------------------------------------
// end processing if no more journal entries
//------------------------------------------

if (jePassedIn = '9');
exsr normalEnd;
endif;

return;

//*******************************************************************
//* Subroutine - normalEnd
//*
//* Purpose: To gracefully end thid program.
//*
//* Procedures: 1. End program.
//*
//*******************************************************************

BegSR normalEnd;

*INLR = *On;
return;

endSR;

//*******************************************************************
//* Subroutine - checkJournalEntry
//*
//* Purpose: To interrogate each journal entry for requested data.
//*
//* Procedures: 1. Check JOESD for ADDTCPJTE command
//* 2. Send message that the command was found.
//*
//*******************************************************************

BegSR checkJournalEntry;

journalEntryDS = %trimr(inJrnString);

select;

when (jeEntryType = 'DL';
// Do what you need to do

when (jeEntryType = 'PT';
yourRowStruct = %trimr(jeESData);
yourHstRow = %trimr(yourRowStruct); // Assuming different fields
are at the end of the row
// Do what you need to do

when (jeEntryType = 'PX';
yourRowStruct = %trimr(jeESData);
yourHstRow = %trimr(yourRowStruct); // Assuming different fields
are at the end of the row
// Do what you need to do

when (jeEntryType = 'UP';
yourRowStruct = %trimr(jeESData);
yourHstRow = %trimr(yourRowStruct); // Assuming different fields
are at the end of the row
// Do what you need to do

other;
// Do what you need to do

endsl;

EndSR;

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Schutte
Sent: Wednesday, April 10, 2013 2:04 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Triggers where Production and Test on Same iSeries

Can you provide example programs?

On Wed, Apr 10, 2013 at 2:36 PM, Monnier, Gary <Gary.Monnier@xxxxxxxxx
<mailto:Gary.Monnier@xxxxxxxxx>>wrote:

File journal receiver entries for journal code R are:

BR - Before-image of record updated for rollback
DL - Record deleted from physical file member
DR - Record deleted for rollback
IL - Increment record limit
PT - Record added to physical file member
PX - Record added directly to physical file member
UB - Before-image of record updated in physical file member
UP - After-image of record updated in physical file member
UR - After-image of record updated for rollback

Each journal entry contains the contents of an entire record/row.

So doesn't tell you what? In your case does it really matter? Aren't
you most concerned with inserts, updates and deletes that actually
change the table? This limits the ones you are interested in DL, PT, PX

and UP.

If the journal entry is an insert (PT, PX) you write a record.

If it is an update (UP) you update a record.
or
if your history file is to be more of a log file you write a
new one.
or
if you want to flag the changed data on the update compare the
JE data against what is on file for the last transaction.

If it is a delete (DL) you do whatever it is you want to do.

In addition, you can add several columns to your history table that
tie it back to the program actually making the change.

And if push comes to shove you have a source to rebuild the table
and/or the history table at virtually any point in time.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx<mailto:

midrange-l-bounces@xxxxxxxxxxxx> [mailto:

midrange-l-bounces@xxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxx>]

On Behalf Of Michael Schutte

Sent: Wednesday, April 10, 2013 10:32 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Triggers where Production and Test on Same iSeries

My experience with journaling is that yeah it tells you who changed
something but doesn't tell you what. Am I wrong about that? It's been

a

long time since I looked at journals. The request is to have a
history of what was changed and by who, what and when.

On Wed, Apr 10, 2013 at 11:23 AM, Monnier, Gary
<Gary.Monnier@xxxxxxxxx

wrote:

Michael,

Not to start a religious war but not all things need be SQL based.
Sometimes it is wiser to look at alternatives and leverage a
platform's strengths.

Assuming your history file is for inquiries why not switch to
journaling the file? You can have a program park on the journal and
load your history file. The exit program ("Program to receive
entries" parameter) for the RCVJRNE can be SQL based.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx<mailto:

midrange-l-bounces@xxxxxxxxxxxx> [mailto:

midrange-l-bounces@xxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxx>]

On Behalf Of Michael Schutte

Sent: Wednesday, April 10, 2013 6:43 AM
To: Midrange Systems Technical Discussion
Subject: SQL Triggers where Production and Test on Same iSeries

We current have our test environment on the same box as our
production environment. Recently I've created SQL Triggers to
record changes to master files to a history file. When we switch
from production to test we get an error that I haven't been able to

figure out how to solve.

Member QTRG000001 already exists in file QTRG000001 in library QTEMP.
SQL system error.
Function check. SQL0901 unmonitored by QDBUDR at statement *N,

instruction

X'076C'.
Failure for device or member ITEMST file ITEMST in library WDLSDATA
CPF5257 I/O error was detected in ITEMST

I know that the issue is that while in production, changes were made
to the ITEMST file, therefore, the trigger was created in QTEMP.
Then when I switch to test and make a change to the ITEMST.
(Obviously, ITEMST is in another library.) That's when I get this
error. To resolve, I just sign off and sign back on again, without
making changes in production, I switch immediately to test and I'm

fine.

Anybody have suggestions.

FYI, we do plan on moving test from the production box, however, we
will have the same issue when we do do that (do do ha-ha) . As we
will have an environment where programs, file structures etc match
production on the production box, a Q/A environment and a
development environment (which is basically just Q/A with the

developer library).

Thank you.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx<mailto:

MIDRANGE-L@xxxxxxxxxxxx> To

subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx<mailto:

MIDRANGE-L-request@xxxxxxxxxxxx> Before posting, please

take a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx<mailto:

MIDRANGE-L@xxxxxxxxxxxx> To

subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx<mailto:

MIDRANGE-L-request@xxxxxxxxxxxx> Before posting, please

take a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx<mailto:

MIDRANGE-L@xxxxxxxxxxxx> To subscribe,

unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx<mailto:

MIDRANGE-L-request@xxxxxxxxxxxx> Before posting, please take

a moment to review the archives at

http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx<mailto:

MIDRANGE-L@xxxxxxxxxxxx> To subscribe,

unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx<mailto:

MIDRANGE-L-request@xxxxxxxxxxxx> Before posting, please take

a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx<mailto:
MIDRANGE-L@xxxxxxxxxxxx> To subscribe, unsubscribe, or change list
options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx<mailto:
MIDRANGE-L-request@xxxxxxxxxxxx> Before posting, please take a moment to
review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.