Problem with a trigger on AIX

Robert Knight

Problem with a trigger on AIX

This is not my area of expertise greatly appreciate some assistance.

Trying to execute the following script from the command line:

CREATE TRIGGER FDB.RNDTST NO CASCADE BEFORE INSERT ON FDB.RNDC14_NDC_MSTR_TEST REFERENCING
NEW AS N FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
if N.DADDNC=00000000 then set N.DADDNC=19000101; end if ;
if N.DUPDC=00000000 then set N.DUPDC=19000101; end if ;
if N.DESDTEC=00000000 then set N.DESDTEC=19000101; end if ;
if N.DES2DTEC=00000000 then set N.DES2DTEC=19000101; end if ;
if N.OBSDTEC=00000000 then set N.OBSDTEC=19000101; end if ;
if N.HCFA_APPC=00000000 then set N.HCFA_APPC=19000101; end if ;
if N.HCFA_MRKC=00000000 then set N.HCFA_MRKC=19000101; end if ;
if N.HCFA_TRMC=00000000 then set N.HCFA_TRMC=19000101; end if ;
if N.HCFA_DESC1=00000000 then set N.HCFA_DESC1=19000101; end if ;
if N.GPIDC=00000000 then set N.GPIDC=19000101; end if ;
if N.BBDC=00000000 then set N.BBDC=19000101; end if ;
END


Receiving the following error:

CREATE TRIGGER FDB.RNDTST NO CASCADE BEFORE INSERT ON FDB.RNDC14_NDC_MSTR_TEST REFERENCING
NEW AS N FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
if N.DADDNC=00000000 then set N.DADDNC=19000101; end if
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"DNC=19000101; end if". Expected tokens may include: "<delim_semicolon>".
LINE NUMBER=4. SQLSTATE=42601

if N.DUPDC=00000000 then set N.DUPDC=19000101; end if
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "if" was found following "BEGIN-OF-STATEMENT".
Expected tokens may include: "<variable_set>". SQLSTATE=42601


Any ideas of my error

Bob Knight





________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Phil Grainger

Re: Problem with a trigger on AIX
(in response to Robert Knight)
Not sure how you do it in AIX, but it's taking your ";" as the STATEMENT terminator for the whole of your create trigger (instead of the compound statements in the IF blocks

On z, you can change the statement termination character to something else - you must be able to do something similar on AIX
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November 2010, Prague Czech Republic


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Robert Knight
Sent: 24 November 2010 15:42
To: [login to unmask email]
Subject: [DB2-L] Problem with a trigger on AIX


This is not my area of expertise greatly appreciate some assistance.

Trying to execute the following script from the command line:

CREATE TRIGGER FDB.RNDTST NO CASCADE BEFORE INSERT ON FDB.RNDC14_NDC_MSTR_TEST REFERENCING
NEW AS N FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
if N.DADDNC=00000000 then set N.DADDNC=19000101; end if ;
if N.DUPDC=00000000 then set N.DUPDC=19000101; end if ;
if N.DESDTEC=00000000 then set N.DESDTEC=19000101; end if ;
if N.DES2DTEC=00000000 then set N.DES2DTEC=19000101; end if ;
if N.OBSDTEC=00000000 then set N.OBSDTEC=19000101; end if ;
if N.HCFA_APPC=00000000 then set N.HCFA_APPC=19000101; end if ;
if N.HCFA_MRKC=00000000 then set N.HCFA_MRKC=19000101; end if ;
if N.HCFA_TRMC=00000000 then set N.HCFA_TRMC=19000101; end if ;
if N.HCFA_DESC1=00000000 then set N.HCFA_DESC1=19000101; end if ;
if N.GPIDC=00000000 then set N.GPIDC=19000101; end if ;
if N.BBDC=00000000 then set N.BBDC=19000101; end if ;
END


Receiving the following error:

CREATE TRIGGER FDB.RNDTST NO CASCADE BEFORE INSERT ON FDB.RNDC14_NDC_MSTR_TEST REFERENCING
NEW AS N FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
if N.DADDNC=00000000 then set N.DADDNC=19000101; end if
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"DNC=19000101; end if". Expected tokens may include: "<delim_semicolon>".
LINE NUMBER=4. SQLSTATE=42601

if N.DUPDC=00000000 then set N.DUPDC=19000101; end if
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "if" was found following "BEGIN-OF-STATEMENT".
Expected tokens may include: "<variable_set>". SQLSTATE=42601


Any ideas of my error

Bob Knight





________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Wolfgang Manus

Re: Problem with a trigger on AIX
(in response to Phil Grainger)
Robert,

db2 -td# will set the statement termination character to # for the subsequent CLP session, so you can run your create trigger statement with embedded ";" characters. You can also take any other character that doesn't show up in your statement, but beware of special characters interpreted by the Unix shell.

If you want to run it from a file, just store the whole statement in a file, add an # at the end and then run it using db2 -td# -f myfile.sql

Roughly the same as SQLTERM(#) parameter in DSNTIAD on z/OS.

HTH - Wolfgang

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Philip Gunning

Re: Problem with a trigger on AIX
(in response to Wolfgang Manus)
Add the @ after the end, you need a different terminator, then create from command line, db2 -td"@" f triggersrce.txt

The @ is kind of a de facto std, but u can use almost anything.
PG



Sent via BlackBerry by AT&T

-----Original Message-----
From: Phil Grainger <[login to unmask email]>
Sender: IDUG DB2-L <[login to unmask email]>
Date: Wed, 24 Nov 2010 10:48:12
To: <[login to unmask email]>
Reply-To: IDUG DB2-L <[login to unmask email]>
Subject: Re: [DB2-L] Problem with a trigger on AIX

Not sure how you do it in AIX, but it's taking your ";" as the STATEMENT terminator for the whole of your create trigger (instead of the compound statements in the IF blocks

On z, you can change the statement termination character to something else - you must be able to do something similar on AIX
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November 2010, Prague Czech Republic


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Robert Knight
Sent: 24 November 2010 15:42
To: [login to unmask email]
Subject: [DB2-L] Problem with a trigger on AIX


This is not my area of expertise greatly appreciate some assistance.

Trying to execute the following script from the command line:

CREATE TRIGGER FDB.RNDTST NO CASCADE BEFORE INSERT ON FDB.RNDC14_NDC_MSTR_TEST REFERENCING
NEW AS N FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
if N.DADDNC=00000000 then set N.DADDNC=19000101; end if ;
if N.DUPDC=00000000 then set N.DUPDC=19000101; end if ;
if N.DESDTEC=00000000 then set N.DESDTEC=19000101; end if ;
if N.DES2DTEC=00000000 then set N.DES2DTEC=19000101; end if ;
if N.OBSDTEC=00000000 then set N.OBSDTEC=19000101; end if ;
if N.HCFA_APPC=00000000 then set N.HCFA_APPC=19000101; end if ;
if N.HCFA_MRKC=00000000 then set N.HCFA_MRKC=19000101; end if ;
if N.HCFA_TRMC=00000000 then set N.HCFA_TRMC=19000101; end if ;
if N.HCFA_DESC1=00000000 then set N.HCFA_DESC1=19000101; end if ;
if N.GPIDC=00000000 then set N.GPIDC=19000101; end if ;
if N.BBDC=00000000 then set N.BBDC=19000101; end if ;
END


Receiving the following error:

CREATE TRIGGER FDB.RNDTST NO CASCADE BEFORE INSERT ON FDB.RNDC14_NDC_MSTR_TEST REFERENCING
NEW AS N FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
if N.DADDNC=00000000 then set N.DADDNC=19000101; end if
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"DNC=19000101; end if". Expected tokens may include: "<delim_semicolon>".
LINE NUMBER=4. SQLSTATE=42601

if N.DUPDC=00000000 then set N.DUPDC=19000101; end if
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "if" was found following "BEGIN-OF-STATEMENT".
Expected tokens may include: "<variable_set>". SQLSTATE=42601


Any ideas of my error

Bob Knight





________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv