Trigger working in DB2 for NT but not in DB2 on an OS/390-machine

Trigger working in DB2 for NT but not in DB2 on an OS/390-machine

Post by Thomas Moell » Fri, 17 Aug 2001 00:28:20



I have an app with the database in DB2 where I use
triggers for automatic creation of new id-keys when
inserting new rows in the app-tables.
I have earlier run the app in DB2 for NT but are now
going to move the database to an OS/390-machine.
The version of DB2 is 6.1 on both machines.

The problem is that I have failed to create the triggers
on the OS390-machine, the same triggers triggers which
have been working fine on the NT.

An example of trigger:

CREATE TRIGGER PREINADM
NO CASCADE BEFORE INSERT ON Administrator
REFERENCING NEW AS new
FOR EACH ROW MODE DB2SQL
SET new.iId = (SELECT COALESCE(MAX(iId), 0) + 1
                             FROM Administrator);

The error message I receive says nothing to me:
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0969N  There is no message text corresponding to SQL error "-20100" in the
message file on this workstation.  The error was returned from module
"DSNHPARS" with original tokens "2 -104 42601 COALESCE,+ ) - ".  
SQLSTATE=56059

Is there anybody who can help me ?

Thanks,

Thomas

 
 
 

Trigger working in DB2 for NT but not in DB2 on an OS/390-machine

Post by Serge Riela » Fri, 17 Aug 2001 01:18:57


Just a shot in the dark: try VALUE instead of COALESCE.
Possibly the version on teh OS/390 doesn't have COALESCE (which is the SQL
standard name for VALUE) yet (?)

Cheers
Serge

 
 
 

Trigger working in DB2 for NT but not in DB2 on an OS/390-machine

Post by Perry R Shindl » Fri, 17 Aug 2001 10:28:39


Thomas,

I ran into this exact same problem on DB2 for OS/390.  While researching the
problem I determined that the implementation of the SQL SET statement on
OS/390 is different from the NT implementation.  The DB2 for OS/390
implementation does not allow a scalar SELECT statement to the right of the
'=' sign!!  Therefore, what seems like (and is) such a simple thing to do on
NT is next to impossible on OS/390.

The best that I could come up with is to write a user defined function that
executes the SELECT statement.  This function could then be called by the
trigger  (The SET statement does allow a function call to the right of the
equal sign).  The downside is that you have to write a COBOL program for the
UDF.  This just seems like a lot of effort just to increment a key.

Any other suggestions would be greatly appreciated.

Thanks.

Perry Shindle


Quote:> I have an app with the database in DB2 where I use
> triggers for automatic creation of new id-keys when
> inserting new rows in the app-tables.
> I have earlier run the app in DB2 for NT but are now
> going to move the database to an OS/390-machine.
> The version of DB2 is 6.1 on both machines.

> The problem is that I have failed to create the triggers
> on the OS390-machine, the same triggers triggers which
> have been working fine on the NT.

> An example of trigger:

> CREATE TRIGGER PREINADM
> NO CASCADE BEFORE INSERT ON Administrator
> REFERENCING NEW AS new
> FOR EACH ROW MODE DB2SQL
> SET new.iId = (SELECT COALESCE(MAX(iId), 0) + 1
>                              FROM Administrator);

> The error message I receive says nothing to me:
> DB21034E  The command was processed as an SQL statement because it was not
a
> valid Command Line Processor command.  During SQL processing it returned:
> SQL0969N  There is no message text corresponding to SQL error "-20100" in
the
> message file on this workstation.  The error was returned from module
> "DSNHPARS" with original tokens "2 -104 42601 COALESCE,+ ) - ".
> SQLSTATE=56059

> Is there anybody who can help me ?

> Thanks,

> Thomas

 
 
 

Trigger working in DB2 for NT but not in DB2 on an OS/390-machine

Post by pm3i.. » Fri, 17 Aug 2001 07:19:12


What country is .se ?
Maybe you have special things about characters like ; , - etc.
server/requester options, bind options (decdel, strdel), country code, ...

I think you can find some info in the dsntip... pannels on the mainframe.
Maybe it's trying to evaluate
MAX(iId),
as
max(iId)number,decimal
?

Maybe you just have to add a space somewhere?
You can also get this message if you're missing an important keyword.

"2 -104 42601 COALESCE,+ ) - "

-104
illegal symbol

42601 is the destination error

the rest are possible invalid tokens.

PM


> I have an app with the database in DB2 where I use
> triggers for automatic creation of new id-keys when
> inserting new rows in the app-tables.
> I have earlier run the app in DB2 for NT but are now
> going to move the database to an OS/390-machine.
> The version of DB2 is 6.1 on both machines.

> The problem is that I have failed to create the triggers
> on the OS390-machine, the same triggers triggers which
> have been working fine on the NT.

> An example of trigger:

> CREATE TRIGGER PREINADM
> NO CASCADE BEFORE INSERT ON Administrator
> REFERENCING NEW AS new
> FOR EACH ROW MODE DB2SQL
> SET new.iId = (SELECT COALESCE(MAX(iId), 0) + 1
>                              FROM Administrator);

> The error message I receive says nothing to me:
> DB21034E  The command was processed as an SQL statement because it was not a
> valid Command Line Processor command.  During SQL processing it returned:
> SQL0969N  There is no message text corresponding to SQL error "-20100" in the
> message file on this workstation.  The error was returned from module
> "DSNHPARS" with original tokens "2 -104 42601 COALESCE,+ ) - ".
> SQLSTATE=56059

> Is there anybody who can help me ?

> Thanks,

> Thomas

 
 
 

Trigger working in DB2 for NT but not in DB2 on an OS/390-machine

Post by Thomas Moell » Fri, 17 Aug 2001 16:41:20



> Just a shot in the dark: try VALUE instead of COALESCE.
> Possibly the version on teh OS/390 doesn't have COALESCE (which is the SQL
> standard name for VALUE) yet (?)

> Cheers
> Serge

Thanks Serge, but it didn't work. The SELECT-statement can be run
stand-alone so I don't think that's the problem.

In my trouble-shooting I found out another strange thing that
might be a clue. When I run the following statement:

update lang
set  lid =( SELECT MAX ( lid )  + 1
            FROM lang )
where lid=2

i got the error message:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0118N  The table or view that is the target of the INSERT, DELETE, or
UPDATE statement is also specified in a FROM clause.  SQLSTATE=42902

The same update-statement is working fine in the 'DB2 for NT'-environment.

Regards,
Thomas

 
 
 

Trigger working in DB2 for NT but not in DB2 on an OS/390-machine

Post by INGELL DOY » Sat, 18 Aug 2001 02:18:16


I was curious about your problem since I'm just developing new
triggers myself, and I was unhappily able to reproduce your problem
easily.
However, I'd gotten my latest trigger help from another GoogleGroup
posting:
see "Generate increasing unique numbers" dated 1998/10/13.  Mr
Adamache's trigger example is not any different than yours in syntax.

I wondered about the "SET = (SELECT" as discussed above being a
problem.
It works find in a standalone SQL, eg,
UPDATE DBA.table1
SET fld1 = (SELECT COALESCE(MAX(fld2),0)FROM DBA.table2);

but it's only valid when I have table1/table2.

 
 
 

Trigger working in DB2 for NT but not in DB2 on an OS/390-machine

Post by pm3i.. » Sat, 18 Aug 2001 05:32:02


maybe there is a way to use a values into or
a valid expression like a case statement that retuns a scalar to
the set/assignment?

PM

 
 
 

Trigger working in DB2 for NT but not in DB2 on an OS/390-machine

Post by Serge Riela » Sat, 18 Aug 2001 07:52:12


Hi,

Could it be the 390 is complaining about a mutating row conflict?

Cheers
Serge

 
 
 

Trigger working in DB2 for NT but not in DB2 on an OS/390-machine

Post by pm3i.. » Sat, 18 Aug 2001 13:35:23


"
|WHEN (search-condition
#Specifies a condition that evaluates to true,false,or unknown.The
#condition for a before trigger must not include a subselect that
references
#the triggering table.
"
...
"
|Because a before trigger must not modify any table,functions and
procedures that
|you invoke from a trigger cannot include INSERT,UPDATE,or DELETE
statements
|that modify the triggering table.
"
...

Since it's not allowed for the When and that
there is no transition table defined in before insert triggers/for each
row,
i guess it makes sense to see the target table as a gray zone in this
situation.

You can't modify the target-mutant table.
References : Rielau/Doyle/Adamache and the books...

I guess the problem is the max() with the reference to the table the
trigger is defined on.

With all the replies you got on this thread, you could conclude that a
second
table maybe needed to hold the 'key-generator' value.

Funny thing, this has been a long thread and i did not see any
oracle/nuno stuff
the whole time.  ;-)

PM

 
 
 

1. Load data from DB2 in NT to DB2 in OS/390

Hi,

I have DB2 v6.0 in OS/390.

I need to load huge text file data into DB2 in OS/390 in monthly
basis. The problem is these text files are not in fixed-length format.
I need to use either REXX script or C script to read one-by-one record
from these comma delimited text files into fixed length before using
DB2 Loader loads it into OS/390.

I heard that DB2 v7x in NT can load data in comma delimiter.
Is it possible for me to use DB2 Loader NT version and Load the data
into OS/390?.
The idea is I provide an NT machine with DB2 v7x (NT version)
installed on that machine. I ftp those comma delimeted text files to
NT machine and using DB2 Loader in NT loads all text files to DB2 in
OS/390.

Does this scenario work?

Thanks in advance.

Rgds,
Deby

2. Problem with installed I4GL on SCO UNIX

3. data from DB2 UDB on NT to DB2 on OS/390

4. Is it worthwhile to Migrate for SQL Sever?

5. desire to move data from OS 390 DB2 to AS/400 DB2 using DTS

6. SQL Server Data Encryption

7. Table Limit with DB2 Connect to DB2 for OS/390

8. fuzzy checkpoint

9. Creating DB2 SQL Procedures in DB2 version 6 on OS/390 (June 2000 PUT)

10. DB2 Conenct & DB2 on OS/390

11. DB2 for OS/390 REXX Language Support for DB2 V5.1

12. Import DELimited File Format Files to DB2 for OS/390 Tables with DB2 Connect

13. Frontpage with DB2 Connect to OS/390 DB2 ?