Message SQL0312 or Message SQL0206 Failure to Re-Create SQL Trigger

This document describes a problem which can occur if a customer re-created a certain style of SQL triggers while having certain PTFs applied and the trigger program was deleted.

SQL triggers containing user-declared variables that were created when one of the following PTFs were applied may result in message SQL0312 at R610 or message SQL0206 at R710. Note that in some cases the messages SQL0312 and SQL0206 might not be issued in the job log. The trigger would need to include unqualified label references to locally declared variables to run into this issue. If the trigger was created with one of the below PTFs applied, it will continue to work until the corrective PTF is applied to the system and the trigger program has been deleted and the file is used in a way that causes the trigger to be fired. When these three preconditions occur, the database will attempt to automatically regenerate the trigger program and cause the errors to occur. This regeneration is forced by some High Availability providers; however, it can also happen if the file is saved and restored without inclusion of the trigger programs.

PTF details

PTFNAME  SF99601 PTF Group Level    PTFRELEASE    
SI44184  21 IBM i 6.1/6.1.1 
SI44240             21   IBM i 6.1/6.1.1  
SI44461             21   IBM i 6.1/6.1.1  
SI44763             22   IBM i 6.1/6.1.1  
SI44765             22   IBM i 6.1/6.1.1  
SI44784             22   IBM i 6.1/6.1.1  
SI45046             22   IBM i 6.1/6.1.1  
SI45159             22   IBM i 6.1/6.1.1  


PTFNAME  SF99701 PTF Group Level    PTFRELEASE    
SI44440             11   IBM i 7.1 
SI44742             12   IBM i 7.1  
SI44840             12   IBM i 7.1  
SI44957             12   IBM i 7.1  


Problem you may encounter

When a customer has this problem, the customer will be unable to use the table (file) which has the trigger associated. Jobs will receive message SQL0312 or message SQL0206 failure, depending on the release of the i5.


Corrective action

1. Apply the corrective PTF:

PTFNAME  SF99601 PTF Group Level    PTFRELEASE    
SI45295             22   IBM i 6.1/6.1.1

PTFNAME  SF99701 PTF Group Level    PTFRELEASE    
SI45031             13   IBM i 7.1 

2. Run the SQL syntax DROP TRIGGER to drop the trigger program.

3. Re-create the trigger by using the SQL CREATE TRIGGER statement.

If the master SQL source code is unavailable, the source can be obtained by using Generate SQL from System i Navigator. To do this, you should do the following:

a. Open iSeries Navigator and sign on.
b. Expand Databases.
c. Expand the database that contains the trigger.
d. Expand Schema.
e. Expand Triggers. This shows all the triggers in that schema.
f. Right-click on the trigger you need to generate, and select Generate SQL.
g. When the window comes up with the trigger information, click Generate SQL.

After retrieving the SQL source code, all the instances of "SQLP_Ln . " need to be removed prior to execution of the CREATE TRIGGER SQL statement.


Example of what you will see from generate SQL

CREATE TRIGGER trig1 AFTER INSERT ON t1
  REFERENCING NEW AS NEW_VAR
  FOR EACH ROW MODE DB2SQL
 SET OPTION DBGVIEW=*SOURCE
     BEGIN              
          DECLARE V1 CHAR(13);
          SET SQLP_L3 . V1 = QSYS2 . CONCAT ( NEW_VAR . C1 , NEW_VAR . C2 ) ;
INSERT INTO SLFTRIG1 . T2 ( C1 , C3 ) VALUES ( SQLP_L3 . V1 , DEFAULT ) ;
END ;


Example of what you run to re-create (removal of SQLP_L3. noted in red above)

CREATE TRIGGER trig1 AFTER INSERT ON t1
  REFERENCING NEW AS NEW_VAR
  FOR EACH ROW MODE DB2SQL
 SET OPTION DBGVIEW=*SOURCE
     BEGIN              
          DECLARE V1 CHAR(13);
          SET V1 = QSYS2 . CONCAT ( NEW_VAR . C1 , NEW_VAR . C2 ) ;
INSERT INTO SLFTRIG1 . T2 ( C1 , C3 ) VALUES ( V1 , DEFAULT ) ;
END ;


Finding possible trigger programs in this state

1.	Run the following SQL statement: select count(*) from qsys2.systriggers  where action_statement is not null and not (action_statement like '%SQLP/_L1 .%') and action_statement like '%SQLP/_%' ESCAPE '/'; The output of the query is the set of SQL trigger programs which have the type of SQL variable use needed to get this problem. It is normal for this query to return a non-zero value.
2.	If the above query returns anything other than zero, run the following SQL: select TRIGGER_PROGRAM_LIBRARY, TRIGGER_PROGRAM_NAME from qsys2.systriggers where action_statement is not null and not (action_statement like '%SQLP/_L1 .%') and action_statement like '%SQLP/_%' ESCAPE '/';
3.	Use DSPPGM to determine the trigger program creation date and time, and see if this was created after the PTF applied or before. For those created after the PTF was applied, corrective action as stated above should be taken. If the trigger was created before the PTF was applied, no action is needed.