Solved

iSeries SQL Trigger for CL program

Posted on 2013-10-30
8
1,517 Views
Last Modified: 2013-10-31
I’m trying to monitor a file for changes using an External SQL trigger. When a new record is inserted in the file I would like to trigger the CL program passing the primary key of the new record as a parameter. The error message I’m getting tells me it’s not able to find any routine with that name that will accept the parameter types I have given. When I attempted to create the SQL trigger I got an error telling me that NEW_ROW.DJID did not exist so I wrapped it in quotes which might very well be messing things up.  Here is what I have so far:

Code for SQL Stored Procedure:
--  Generate SQL 
--  Version:                   	V7R1M0 100423 
--  Generated on:              	10/30/13 08:01:43 
--  Relational Database:       	S10F3EC1 
--  Standards Option:          	DB2 for i 
CREATE PROCEDURE GB1555AFGD.SOAP_TRIGGER ( 
	IN DID CHAR(15) ) 
	LANGUAGE CL 
	SPECIFIC GB1555AFGD.SOAP_TRIGGER 
	DETERMINISTIC 
	NO SQL 
	CALLED ON NULL INPUT 
	EXTERNAL NAME 'MYLIB/Z1CRMTCMD' 
	PARAMETER STYLE SQL ; 

Open in new window

 
Code for SQL Trigger:

CREATE TRIGGER GB1555AFGD.C8_SYNCJOURNAL_INSERTS 
	AFTER INSERT ON GB1555AFGD.Z1ODBJC82 
	REFERENCING NEW AS NEW_ROW 
	FOR EACH ROW 
	MODE DB2SQL 
	SET OPTION  ALWBLK = *ALLREAD , 
	ALWCPYDTA = *OPTIMIZE , 
	COMMIT = *NONE , 
	DECRESULT = (31, 31, 00) , 
	DFTRDBCOL = *NONE , 
	DYNDFTCOL = *NO , 
	DYNUSRPRF = *USER , 
	SRTSEQ = *HEX   
	WHEN ( NEW_ROW . DJID IS NOT NULL )
 BEGIN ATOMIC 
CALL GB1555AFGD . SOAP_TRIGGER ( 'NEW_NOW.DJID' ) ; 
END  ; 

Open in new window



The CL program takes 1 parameter a Char(15) and performs a RUNRMTCMD. I would share the source code but I don’t know how to find it. This program was created by an iSeries developer for me. If I run the program from a green screen it works as expected.
0
Comment
Question by:HelpDeskGeiger
  • 4
  • 3
8 Comments
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39611656
You got the error due to a typo:

CALL GB1555AFGD . SOAP_TRIGGER ( 'NEW_NOW.DJID' ) ;

Then you made it worse with the quotes.  Try:

CALL GB1555AFGD . SOAP_TRIGGER ( NEW_ROW.DJID  ) ;

Be aware that trigger processing can be expensive in terms of performance and in terms of runtime, and that the INSERT operation waits until the trigger completes to return control back to the program performing the database inserts on GB1555AFGD.Z1ODBJC82.

Especially if the trigger program is executing a potentially slow command like RUNRMTCMD, you need to be careful that you don't cause performance problems if there are a high volume of inserts on GB1555AFGD.Z1ODBJC82.
0
 

Author Comment

by:HelpDeskGeiger
ID: 39612241
Hi Gary,

I have deleted and recreated the trigger a number of times to troubleshoot. I re-created it this morning so I could paste the code in this post and I made that typo. I double checked and it was not in the original. Here is the actual code that I'm using:

CREATE TRIGGER GB1555AFGD.C8_SYNCJOURNAL_INSERTS 
	AFTER INSERT ON GB1555AFGD.Z1ODBJC82 
	REFERENCING NEW AS NEW_ROW 
	FOR EACH ROW 
	MODE DB2SQL 
	SET OPTION  ALWBLK = *ALLREAD , 
	ALWCPYDTA = *OPTIMIZE , 
	COMMIT = *NONE , 
	DECRESULT = (31, 31, 00) , 
	DFTRDBCOL = *NONE , 
	DYNDFTCOL = *NO , 
	DYNUSRPRF = *USER , 
	SRTSEQ = *HEX   
	WHEN ( NEW_ROW.DJID IS NOT NULL )
 BEGIN ATOMIC 
CALL GB1555AFGD.SOAP_TRIGGER (NEW_ROW.DJID); 
END  ; 

Open in new window


and her is the error message I recive when I try to run it from a "run sql scripts" window.

Message: [SQL0312] Variable DJID not defined or not usable. Cause . . . . . :   The variable DJID appears in the SQL statement, but one of the following conditions exists: -- No declaration for the variable exists or the declaration is not within the current scope. -- The attributes are not correct for the use specified. -- The host variable was specified in dynamic SQL. Host variables are not valid in dynamic SQL. -- In REXX, host variable names cannot contain embedded blanks. -- The variable name is used in the routine body of an SQL procedure or function, but the variable is not declared as an SQL variable or parameter. The scope of an SQL variable is the compound statement that contains the declaration. -- The variable is used in the routine body of an SQL trigger, but the variable is not declared as an SQL variable or the variable is an OLD transition variable and cannot be modified. -- The variable is a transition variable in an AFTER trigger and is used in statement where the variable could be modified. Modifying transition variables in AFTER triggers is not allowed. Recovery  . . . :   Do one of the following and try the request again. -- Verify that DJID is spelled correctly in the SQL statement. -- Verify that the program contains a declaration for that variable.  In RPG, the variable must be declared globally or in the scope where it is used. -- Verify that the attributes of the variable are compatible with its use in the statement. -- Use parameter markers in dynamic SQL instead of host variables. -- Remove embedded blanks from REXX host variable names. -- Declare the variable as an SQL variable or parameter in the SQL procedure or function. -- Declare the variable as an SQL variable or specify a NEW transition variable when the variable is modified in an SQL trigger. -- Remove the transition variable from the statement. Copying the transition variable to a local variable and then using the local variable in the statement is also acceptable.
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39612292
Good.  Now, have you tried all of the items listed in the RECOVERY section of the message?

Recovery  . . . :   Do one of the following and try the request again.

-- Verify that DJID is spelled correctly in the SQL statement.

-- Verify that the program contains a declaration for that variable.  In RPG, the variable must be declared globally or in the scope where it is used.

-- Verify that the attributes of the variable are compatible with its use in the statement.

-- Declare the variable as an SQL variable or parameter in the SQL procedure or function.

-- Declare the variable as an SQL variable or specify a NEW transition variable when the variable is modified in an SQL trigger.

-- Remove the transition variable from the statement. Copying the transition variable to a local variable and then using the local variable in the statement is also acceptable.

0
 

Author Comment

by:HelpDeskGeiger
ID: 39612365
I changed the trigger to have the following changes:

BEGIN ATOMIC 
	DECLARE DJ VARCHAR(15);
	SET DJ = NEW_ROW.DJID;
	CALL GB1555AFGD.SOAP_TRIGGER (DJ); 
END  ; 

Open in new window


now when I run it I'm getting the following error in regards to running the stored procedure:

SQL0440: Routine Z1CRMTCMD in WWALSH not found with specified parameters.

SQL State: 09000
Vendor Code: -723
Message: [SQL0723] SQL trigger C8_SYNCJOURNAL_INSERTS in GB1555AFGD failed with SQLCODE -440 SQLSTATE 42884. Cause . . . . . :   An error has occurred in a triggered SQL statement in trigger C8_SYNCJOURNAL_INSERTS in schema GB1555AFGD.  The SQLCODE is -440, the SQLSTATE is 42884, and the message is Routine Z1CRMTCMD in WWALSH not found with specified parameters.. Recovery  . . . :   Refer to the joblog for more information regarding the detected error. Correct the error and try the request again.

I tried changing the variable type in the trigger to DECIMAL(15,0), CHAR(15) AND VARCHAR(15) but I got the same error.

The individual who created the CL program told me it was a CHAR data type but I have asked them to double check the type and length.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 34

Accepted Solution

by:
Gary Patterson earned 500 total points
ID: 39612529
Odds are you need to use PARAMETER STYLE GENERAL.

PARAMETER STYLE SQL causes several additional parms to be passed to the called program.

Can you post the parameter list for MYLIB/Z1CRMTCMD (I assume that is really WWALSH/Z1XRMTCMD)?
0
 
LVL 27

Expert Comment

by:tliotta
ID: 39613353
Can you show us the output from running a 'Generate SQL' against the GB1555AFGD.Z1ODBJC82 table?

Tom
0
 

Author Comment

by:HelpDeskGeiger
ID: 39613801
Here is the output of the generate sql against the Z1ODBJC82 table:

--  Generate SQL
--  Version:                         V7R1M0 100423
--  Generated on:                    10/31/13 08:01:55
--  Relational Database:             S10F3EC1
--  Standards Option:                DB2 for i
CREATE TABLE GB1555AFGD.Z1ODBJC82 (
      DJID DECIMAL(15, 0) NOT NULL DEFAULT 0 ,
      DJSITE CHAR(8) CCSID 37 NOT NULL DEFAULT '' ,
      DJTRTP CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,
      DJACTN CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
      DJREFN CHAR(100) CCSID 37 NOT NULL DEFAULT '' ,
      DJSTAT CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
      DJWDAT DECIMAL(8, 0) NOT NULL DEFAULT 0 ,
      DJWTIM DECIMAL(6, 0) NOT NULL DEFAULT 0 ,
      DJPDAT DECIMAL(8, 0) NOT NULL DEFAULT 0 ,
      DJPTIM DECIMAL(6, 0) NOT NULL DEFAULT 0 ,
      DJCPGM CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
      DJUSER CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
      DJMSG CHAR(50) CCSID 37 NOT NULL DEFAULT '' )  
      ;
 
CREATE TRIGGER GB1555AFGD.C8_SYNCJOURNAL_INSERTS
      AFTER INSERT ON GB1555AFGD.Z1ODBJC82
      REFERENCING NEW AS NEW_ROW
      FOR EACH ROW
      MODE DB2SQL
      SET OPTION  ALWBLK = *ALLREAD ,
      ALWCPYDTA = *OPTIMIZE ,
      COMMIT = *NONE ,
      DECRESULT = (31, 31, 00) ,
      DFTRDBCOL = *NONE ,
      DYNDFTCOL = *NO ,
      DYNUSRPRF = *USER ,
      SRTSEQ = *HEX  
      WHEN WHEN ( NEW_ROW . DJID IS NOT NU LL )
BEGIN ATOMIC
DECLARE DJ DECIMAL ( 15 , 0 ) ;
SET SQLP_L2 . DJ = NEW_ROW . DJID ;
CALL GB1555AFGD . SOAP_TRIGGER ( SQLP_L2 . DJ ) ;
END  ;
 
LABEL ON TRIGGER GB1555AFGD.C8_SYNCJOURNAL_INSERTS IS 'Trigger Soap Request on record insert' ;
 
GRANT ALTER , DELETE , INDEX , INSERT , REFERENCES , SELECT , UPDATE  
ON GB1555AFGD.Z1ODBJC82 TO ASW5OWN WITH GRANT OPTION ;
0
 

Author Closing Comment

by:HelpDeskGeiger
ID: 39613847
deleting and recreating the procedure with PARAMETER STYLE GENERAL worked. Thank you.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now