DB2 7.1 Triggers and User Defined Functions with Cobol program

DB2 7.1 Triggers and User Defined Functions with Cobol program

Post by Colet » Sat, 06 Apr 2002 08:10:51



We've been trying to set up a Trigger that executes a UDF that calls a
COBOL
program that does a simple SQL statement and keep getting the
following error
when we execute the trigger:
SNT408I SQLCODE = -723, ERROR:  AN ERROR OCCURRED IN A TRIGGERED SQL
STATEMENT
        IN TRIGGER PRODDATA.TRIGGRNM, SECTION NUMBER 2.
        INFORMATION RETURNED: SQLCODE -430, SQLSTATE 38503, AND
MESSAGE TOKENS
        FUNCTION ,PRODDATA.UDFNAME          ,Z
SNT418I SQLSTATE   = 09000 SQLSTATE RETURN CODE
SNT415I SQLERRP    = DSNX9CAC SQL PROCEDURE DETECTING ERROR
SNT416I SQLERRD    = 0  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION
SNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'
X'FFFFFFFF'

If we take the SQL statement out of the cobol program the trigger
executes without problems. This is the SQL in the program and a row
does exist:
EXEC SQL                            
  SELECT EMP_NBR                    
  INTO :DATABASE-EMP-NBR.EMP-NBR  
  FROM DATABASE_EMP_NBR              
  WHERE EMP_NBR = '123456789'      
END-EXEC.                    

Is there something obscure we are missing here?  Let me know if I can
supply more info.
thanks.

 
 
 

DB2 7.1 Triggers and User Defined Functions with Cobol program

Post by Serge Riela » Sat, 06 Apr 2002 23:05:32


Hi Colette,

Which platform is this?

Cheers
Serge

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada

 
 
 

DB2 7.1 Triggers and User Defined Functions with Cobol program

Post by Colet » Sun, 07 Apr 2002 03:21:06



> Hi Colette,

> Which platform is this?

> Cheers
> Serge

0s/390 Mainframe version 7.1
 
 
 

1. View access from COBOL batch program in DB2 UDB Version 7.1 for Z/OS

Just wanted to check what would be more efficient:

a.      Create a view on DB2 table with CASE statement to replace a value
on
        one of the columns

Or

b.      Code this CASE statement in the SQL and run it every time statement
is
        executed against the table

Despite the fact that DB2 view is created only at the time of
execution of an SQL statement running against that view,
wouldn’t the named view be created only once on the first SQL
pass if the programs package is bounded with ACQUIRE (USE) and RELEASE
(COMMIT) parameters?

Performance is a great concern here, since there are more then
10,000,000 rows to be processed. Running a CASE statement as a part of
SQL code in the COBOL program for every row is not preferable.

Any input would be greatly appreciated.

Thanks,

Dmitry.

2. Unable to logon to SQL server

3. call a user defined function from another user-defined function

4. DTS Package - Unable to validate UserName/Password

5. Conversion of Unisys DMII Cobol program to IBM DB2 COBOL program

6. Primary key duplicate error

7. DB2 7.1 PDE or DB2 7.1 EE

8. HTML Formatted email body using CDONTS getting cut off at 4000 chars

9. 7.1 pg_dump fails for user-defined types

10. 7.1 pg_dump fails for user-defined types (release s

11. USER DEFINED FUNCTIONS IN DB2?

12. User-defined function usage in trigger WHEN clause?

13. Client programming for IBM DB2 OLAP Server 7.1