IBM DB2 SQLCODE -1424, SQLSTATE 54040 when trying to create a trigger

Question

I have tried to create the following trigger (DB2/LINUXX8664 9.7.2 running over Ubuntu 10.04.2 LTS) but I always get this:

Error report:
DB2 SQL error: SQLCODE: -1424, SQLSTATE: 54040, SQLERRMC: 2

According IBM DB2 documentation:

Too many references to transition variables and transition table columns or the row length for these references is too long. Reason code=rc.

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.messages.sql.doc/doc/msql01424n.html

I really do not understand that explanation. Here the stored procedure and trigger:

CREATE PROCEDURE SP_INSERT_UPDATE_REPNUM (
   IN RECNUM           INTEGER,
   IN CUSTOMER_ID      INTEGER,
   IN Q_CODE           VARCHAR(14),
   IN S_CODE           VARCHAR(14),
     IN REP_STATUS       INTEGER,
     IN P_CODE           INTEGER,
     IN REPNUMRG_ID      INTEGER,
     IN VOLG_LET         VARCHAR(1),
     IN REP_DATUM        DATE,
     IN REP_INI          VARCHAR(2),
     IN INGEBOEKT_DATUM  DATE,
     IN INGEBOEKT_INI    VARCHAR(2),
     IN WIJZIGING_DATUM  DATE,
     IN WIJZIGING_INI    VARCHAR(2),
     IN OMSCR_STORING    VARCHAR(5),
     IN OMSCR_WERKZ      VARCHAR(5),
     IN OMSCR_OPMERKING  VARCHAR(5),
     IN OMSCR_GEBREK     VARCHAR(5),
     IN OMSCR_MAT        VARCHAR(5),
     IN REP_TIJD         FLOAT,
     IN REP_GEDAAN       CHAR(1),
     IN ACTION           CHAR(1)
)
SPECIFIC SP_INSERT_UPDATE_REPNUM
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
NOT FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'RepairMigration!insertServiceReport'

and here the trigger:

CREATE TRIGGER INSERT_REPNUM
  AFTER INSERT ON REPNUM
  REFERENCING NEW ROW AS NROW
    FOR EACH ROW MODE DB2SQL
BEGIN 
  CALL SP_INSERT_UPDATE_REPNUM(
    NROW.RECNUM,
    NROW.CUSTOMER_ID,
    NROW.Q_CODE,
    NROW.S_CODE,
    NROW.REP_STATUS,
    NROW.P_CODE,
    NROW.REPNUMRG_ID,
    NROW.VOLG_LET,
    NROW.REP_DATUM,
    NROW.REP_INI,
    NROW.INGEBOEKT_DATUM,
    NROW.INGEBOEKT_INI,
    NROW.WIJZIGING_DATUM,
    NROW.WIJZIGING_INI,
    NROW.OMSCR_STORING,
    NROW.OMSCR_WERKZ,
    NROW.OMSCR_OPMERKING,
    NROW.OMSCR_GEBREK,
    NROW.OMSCR_MAT,
    NROW.REP_TIJD,
    NROW.REP_GEDAAN,
    'I'
  );
END

the table structure is thsi one:

RECNUM              INTEGER 4   0   N   
Q_CODE              CHARACTER   14  0   N   ''
U_Q_CODE            CHARACTER   14  0   N   
S_CODE              CHARACTER   14  0   N   ''
U_S_CODE            CHARACTER   14  0   N   
VOLG_LET            CHARACTER   1   0   N   ''
U_VOLG_LET          CHARACTER   1   0   N   
REP_DATUM           DATE    4   0   N   '0001-01-01'
REP_INI             CHARACTER   2   0   N   ''
P_CODE              INTEGER 4   0   N   0
CUSTOMER_ID         INTEGER 4   0   N   0
REPNUMHD_ID         INTEGER 4   0   N   0
REPNUMRG_ID         INTEGER 4   0   N   0
REP_STATUS          SMALLINT    2   0   N   0
KONTAKT_PER         CHARACTER   25  0   N   ''
KONTAKT_TEL         CHARACTER   20  0   N   ''
OMSCR_STORING       VARCHAR 512 0   N   ''
OMSCR_WERKZ         VARCHAR 2000    0   N   ''
OMSCR_MAT           VARCHAR 512 0   N   ''
WIJZIGING_DATUM     DATE    4   0   N   '0001-01-01'
WIJZIGING_INI       CHARACTER   2   0   N   ''
INGEBOEKT_DATUM     DATE    4   0   N   '0001-01-01'
INGEBOEKT_INI       CHARACTER   2   0   N   ''
REP_GEDAAN          CHARACTER   1   0   N   ''
U_REP_GEDAAN        CHARACTER   1   0   N   
STATUS              SMALLINT    2   0   N   0
UW_OPDRACHT         CHARACTER   20  0   N   ''
REP_UREN            SMALLINT    2   0   N   0
REP_MINUTEN         SMALLINT    2   0   N   0
OMSCR_OPMERKING     VARCHAR 1008    0   N   ''
OMSCR_GEBREK        VARCHAR 512 0   N   ''
SERVICE_NUMMER      INTEGER 4   0   N   0
PRIJS_OPGAVE        CHARACTER   1   0   N   ''
CURRENCY            SMALLINT    2   0   N   0
REP_TIJD            INTEGER 4   0   N   0

After trying to execute the trigger, I get the error. How can I solve this?

UPDATE

It looks that I need to create a temporary tablespace. Listing all my tablespaces, I got some with more than 4k, for example this one:

 Tablespace ID                        = 7
 Name                                 = TEMPSPACE1234
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1024
 Useable pages                        = 1008
 Used pages                           = 432
 Free pages                           = 576
 High water mark (pages)              = 528
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 16
 Prefetch size (pages)                = 16
 Number of containers                 = 1

Care to mention your DB2 version and platform? You also may want to include the table definition. — mustaccio
– mustaccio, Commented Nov 11, 2014 at 14:36
@mustaccio DB2/LINUXX8664 9.7.2 running over Ubuntu 10.04.2 LTS — Ruben
– Ruben, Commented Nov 11, 2014 at 14:41
If it's Linux, why do you reference manuals for DB2 on z/OS? — mustaccio
– mustaccio, Commented Nov 11, 2014 at 14:43
Do you have a system temporary tablespace with the page size larger than 4K? If not, create one and try the trigger again. — mustaccio
– mustaccio, Commented Nov 11, 2014 at 15:32

Marcelo Carvalho · Accepted Answer · 2016-04-24 20:52:07Z

1

1) Create a 32K Buffer Pool

Example:

CREATE BUFFERPOOL "BUFFERPOOLLARGE" IMMEDIATE
ALL DBPARTITIONNUMS  SIZE AUTOMATIC
NUMBLOCKPAGES 0
PAGESIZE 32 K;

2) Create a 32K SYSTEM TEMPORARY TABLESPACE

Example:

CREATE SYSTEM TEMPORARY TABLESPACE "TEMPSPACELARGE"
IN DATABASE PARTITION GROUP "IBMTEMPGROUP"
PAGESIZE 32 K
MANAGED BY AUTOMATIC STORAGE
EXTENTSIZE 32
BUFFERPOOL "BUFFERPOOLLARGE"
OVERHEAD INHERIT
TRANSFERRATE INHERIT
USING STOGROUP "IBMSTOGROUP"
FILE SYSTEM CACHING;

3) Run CREATE PROCEDURE Script Again.

OBS:In the examples I used DB2 automatic TABLESPACE.

answered Apr 24, 2016 at 20:52

Marcelo Carvalho

111 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

IBM DB2 SQLCODE -1424, SQLSTATE 54040 when trying to create a trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related