Re: SQL Triggers where Production and Test on Same iSeries

Chuck,

The file is in vendor libraries. call them WDLSDATA (production),
WDLSDTEST (test). I've built the trigger in ODWPROD(production) and
ODWTEST(test). Using system naming convention, I let the create trigger
find the file based on the library list. FYI we are using ALDON to
promote, therefore, you cannot hard code libraries.

The file is DDS. I use a different file because it only has two fields.

*** FILE CXREF
A REF(EDIFRF)
A UNIQUE
A R RCXREF
A TEXT('Consignee Cross Reference')
A*
A CXCONS R
A CXPCON R
A*
A K CXCONS

Both fields are 12 characters long.


I created the history file with this script through ALDON. ALDON replaces
&LIB with the approriate library. In this case ODWTEST for test, ODWPROD
for production.
create table &LIB/cxrefh as (
select a.*
, CHAR('', 10) AS CXMUSR
, CURRENT TIMESTAMP AS CXMDAT
, CHAR('', 1) AS CXMIMG
from cxref a
) with no data;



// Add Trigger.
CREATE TRIGGER &LIB/CXREFATRG
AFTER INSERT ON CXREF
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *YES ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DLYPRP = *NO ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN ATOMIC INSERT INTO CXREFH
VALUES ( n.CXCONS
, n.CXPCON
, USER
, CURRENT_TIMESTAMP
, 'N'
);
END ;


I've purposely left out the code that determines the program that made the
change. I don't think it was important and it's just more for you to
duplicate.

This might be important information.
ASP

Opt Library Attribute Device Text



WDLSDATA PROD Warehousing Distribution &
Logistics
WDLSDTEST TEST WDLS Test Enviroment

ODWPROD PROD ODW Custom Production Library
ODWTEST PROD ODW Custom TEST Library



So duplicate the issue, set your library list so that it matches
production. Insert a record to CXREF. You can query CXREFH if you like,
but not required.

Then change your library list to test. Then for the heck of it issue
RRTJOB. When we switch between library lists, this command is issued so
that the menu system is updated with correct titles and such.

Now try to add a record to the test file. This should result in the error
described in the opening message.






On Wed, Apr 10, 2013 at 12:41 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

The SQL uses a file in QTEMP to recreate a since-invalidated SQL
TRIGGER; effectively a C compile of the source for the TRIGGER. An SQL
CREATE TRIGGER can not be performed on a file in QTEMP. The TRIGGER and
trigger program are created into the library of the TABLE [or of the
VIEW for an INSTEAD OF TRIGGER].

Thus I believe the comment "the trigger was created in QTEMP" is just
a mis-statement for an explanation of the diagnostic condition shown
previous to that comment.

I believe the eventual error CPF5257 is for an I/O request which
matches the type of TRIGGER created. Being the first I/O since the
TRIGGER was marked ¿inoperable? causes the SQL to regenerate the trigger
from its source. Because the trigger by the same name had already been
created in the job, there is a diagnostic message about "member already
exists" logged. Whether that is directly at issue, with regard to the
origin of the problem, is not obvious. Thus why in a prior reply I
suggested, if not being pursued as a defect, then it might be best to
fully describe how to re-create the problem, so someone might better be
able to explain what is really happening and what might be the best
circumvention.

Regards, Chuck

On 10 Apr 2013 09:12, Dan wrote:

<<SNIP>> I'm not sure why the trigger is being created in QTEMP.
Who/what is creating the trigger? Shouldn't the trigger be created
permanently in the same library as the table to which it belongs?

On Wed, Apr 10, 2013 at 9:42 AM, Michael Schutte wrote:

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. <<SNIP>>

--
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.