By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,333 Members | 1,226 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,333 IT Pros & Developers. It's quick & easy.

Trigger error ....

P: n/a
Following is a simple trigger definition:

CREATE TRIGGER TRIG_EMPLOYEE_1
AFTER UPDATE OF SALARY ON DB2ADMIN.EMPLOYEE
FOR EACH STATEMENT MODE DB2SQL
CALL DB2ADMIN.SQLSP10( )

This gives an error:

SQL0104N An unexpected token "CALL" was found following "ATEMENT MODE
DB2SQL ". Expected tokens may include: "RETURN ".

DB2ADMIN.SQLSP10( ) is an existing stored procedure.

Any pointers will be appreciated.

TIA
Raquel.

Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Raquel wrote:
Following is a simple trigger definition:

CREATE TRIGGER TRIG_EMPLOYEE_1
AFTER UPDATE OF SALARY ON DB2ADMIN.EMPLOYEE
FOR EACH STATEMENT MODE DB2SQL
CALL DB2ADMIN.SQLSP10( )

This gives an error:

SQL0104N An unexpected token "CALL" was found following "ATEMENT MODE
DB2SQL ". Expected tokens may include: "RETURN ".

DB2ADMIN.SQLSP10( ) is an existing stored procedure.

Any pointers will be appreciated.


CALL statements are not yet supported in the body of a trigger. You have to
wait until Stinger (aka version 8.2) becomes available. Until then you
have to resort to UDFs, if possible, or put the logic directly into the
trigger body and not a procedure.

Alternatively, you could use the CALL_PROCEDURE function that is described
here:

http://www.ibm.com/developerworks/db...ein/index.html

But note the restrictions and implications that come with this approach!

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #2

P: n/a
Thanks Knut. You are the co-author of the above very useful article!!

Wow!! So, at last I find a feature that is readily available in BIG DB2
(DB2 for OS/390) and not in small DB2 (aka UDB for LUW).

Raquel.

Nov 12 '05 #3

P: n/a
Raquel wrote:
Wow!! So, at last I find a feature that is readily available in BIG DB2
(DB2 for OS/390) and not in small DB2 (aka UDB for LUW).


Yes, that's right. And that's why the gap will be closed soon.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #4

P: n/a
"Raquel" <ra****@nospam.com> wrote in message news:<85******************************@localhost.t alkaboutdatabases.com>...
Thanks Knut. You are the co-author of the above very useful article!!

Wow!! So, at last I find a feature that is readily available in BIG DB2
(DB2 for OS/390) and not in small DB2 (aka UDB for LUW).

Raquel.


Knut, yes this article is very helpful. We have implemented it on both
V8 and V7. The V8 version works just as intended. The only thing we
have trouble with is on the TRUNCATE stored procedure on V7. When an
invalid table name is entered, we get the following error:

SQL1131N DARI (Stored Procedure) process has been terminated
abnormally. SQLST
ATE=38503
Anything we can do to fix this problem in V7? We often get calls on
this abend which is only caused when a non-existent table name is
entered. I have no C++ skills

Thanks
Nov 12 '05 #5

P: n/a
Jeff wrote:
"Raquel" <ra****@nospam.com> wrote in message

news:<85******************************@localhost.t alkaboutdatabases.com>...
Thanks Knut. You are the co-author of the above very useful article!!

Wow!! So, at last I find a feature that is readily available in BIG DB2
(DB2 for OS/390) and not in small DB2 (aka UDB for LUW).

Raquel.


Knut, yes this article is very helpful. We have implemented it on both
V8 and V7. The V8 version works just as intended. The only thing we
have trouble with is on the TRUNCATE stored procedure on V7. When an
invalid table name is entered, we get the following error:

SQL1131N DARI (Stored Procedure) process has been terminated
abnormally. SQLST
ATE=38503
Anything we can do to fix this problem in V7? We often get calls on
this abend which is only caused when a non-existent table name is
entered. I have no C++ skills


I'll look into this - you should have told us earlier that you experience
some problems!. Can you please tell us which platform you are running on
and which DB2 FixPack-level is installed?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #6

P: n/a
Hello.
Knut, yes this article is very helpful. We have implemented it on both
V8 and V7. The V8 version works just as intended. The only thing we
have trouble with is on the TRUNCATE stored procedure on V7. When an
invalid table name is entered, we get the following error:

SQL1131N DARI (Stored Procedure) process has been terminated
abnormally. SQLST
ATE=38503


This SP works fine on W2K, v7, FP11.

--- bisp2.c ---

#include <memory.h>
#include <sqlenv.h>
#include <sqlutil.h>

/* Declare function prototypes for this stored procedure library */
SQL_API_RC SQL_API_FN truncate(char *, char *, sqlint32 *);

SQL_API_RC SQL_API_FN truncate(
char fullTableName[254],
char eBuffer[1024],
sqlint32 *sql_code)
{
struct sqlchar *columnStringPointer;
struct sqldcol columnData;
struct sqlca sqlca;
struct sqluimpt_in impInput;
struct sqluimpt_out impOutput;
char datafile[] = "nul";
char repl[] = "REPLACE INTO ";
char fileFormat[] = "DEL";
char msgfile[] = "nul";
char impStatement[254];

*sql_code = 0;
memset(eBuffer, '\0', sizeof(eBuffer));
memset(impStatement, '\0', sizeof(impStatement));

/* need to preset the size of structure field and counts */
impInput.sizeOfStruct = SQLUIMPT_IN_SIZE;
impOutput.sizeOfStruct = SQLUIMPT_OUT_SIZE;
impInput.restartcnt = impInput.commitcnt = 0;
columnData.dcolmeth = 'D';
strncpy(impStatement, repl, strlen(repl));
strncat(impStatement, fullTableName, strlen(fullTableName));

columnStringPointer = (struct sqlchar *)malloc(strlen(impStatement)
+ sizeof (struct sqlchar));
columnStringPointer->length = (short)strlen(impStatement);
strncpy (columnStringPointer->data, impStatement, strlen(impStatement));
/* import */
sqluimpr (datafile, NULL, &columnData, columnStringPointer, fileFormat,
NULL, msgfile, 0, &impInput, &impOutput, NULL, NULL, &sqlca);
*sql_code=sqlca.sqlcode;
if (*sql_code<0)
sqlaintp (eBuffer, 1024, 0, &sqlca);
return(SQLZ_DISCONNECT_PROC);
} /* truncate */
--- end of bisp2.c ---

--- bisp2.def --
LIBRARY BISP2
EXPORTS
truncate
--- end of bisp2.def ---

--- SQL ---
CREATE PROCEDURE bi.TRUNCATE
(IN TABNAME VARCHAR(254),
OUT MSG VARCHAR(1024),
OUT SQLCODE INTEGER
)
EXTERNAL NAME 'bisp2!truncate'
LANGUAGE C
PARAMETER STYLE GENERAL
NOT DETERMINISTIC
FENCED
NO DBINFO
PROGRAM TYPE SUB;
---

Hope this help,
Mark.
Nov 12 '05 #7

P: n/a
Jeff wrote:
"Raquel" <ra****@nospam.com> wrote in message

news:<85******************************@localhost.t alkaboutdatabases.com>...
Thanks Knut. You are the co-author of the above very useful article!!

Wow!! So, at last I find a feature that is readily available in BIG DB2
(DB2 for OS/390) and not in small DB2 (aka UDB for LUW).

Raquel.


Knut, yes this article is very helpful. We have implemented it on both
V8 and V7. The V8 version works just as intended. The only thing we
have trouble with is on the TRUNCATE stored procedure on V7. When an
invalid table name is entered, we get the following error:

SQL1131N DARI (Stored Procedure) process has been terminated
abnormally. SQLST
ATE=38503
Anything we can do to fix this problem in V7? We often get calls on
this abend which is only caused when a non-existent table name is
entered. I have no C++ skills


Found the problem... The PARAMETER STYLE SQL that is available in V8 expects
a different set of parameters than the PARAMETER STYLE DB2SQL that you
(most probably) used in the V7 procedures. For the V7 procedures, you need
to provide an array (or pointer) for the null indicators and not separate
arguments. The separate arguments result in too many parameters expected
on the stack (which aren't provided by DB2) so that the last one, which
happens to be the error message text is an invalid parameter. When it is
dereferenced to write the error message, you get a segmentation violation
(signal 11). DB2 detects that and signals the abend.

Ok, so much for the problem description... The fix is not difficult. Just
change the function code as shown below. I marked the corrections
everywhere - please remove the marks before using this.

#if defined(__cplusplus)
extern "C"
#endif
SQL_API_RC SQL_API_FN truncate_table(
/* fully qualified table name */
SQLUDF_VARCHAR *schemaName, SQLUDF_VARCHAR *tableName,
/* null indicators */
!! here we need a single parameter for the NULL indicator array
SQLUDF_NULLIND nulls[2],
SQLUDF_TRAIL_ARGS)
{
int rc = 0;
struct sqldcol dataDescriptor;
unsigned char actionBuffer[sizeof(struct sqlchar) + 20+130+1+130+1];
struct sqlchar *pAction = (struct sqlchar *)actionBuffer;
struct sqluimpt_in inputInfo;
struct sqluimpt_out outputInfo;

!! we define the macros to make the code more readable when dealing with the
!! null indicators
#define SCHEMA_NAME_IND 0
#define TABLE_NAME_IND 1

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
char hSchemaName[129] = { '\0' };
char hTableName[129] = { '\0' };
sqlint32 count = 0;
EXEC SQL END DECLARE SECTION;

/* get default schema name if parameter was NULL */
!! change the test for a NULL in the schema name
if (SQLUDF_NULL(&nulls[SCHEMA_NAME_IND])) {
EXEC SQL
VALUES CURRENT SCHEMA
INTO :hSchemaName;
if (SQLCODE) {
memcpy(SQLUDF_STATE, sqlca.sqlstate, SQLUDF_SQLSTATE_LEN);
memcpy(SQLUDF_MSGTX, sqlca.sqlerrmc, sqlca.sqlerrml);
goto cleanup;
}
}
else {
/* unquote given schema name */
rc = UnquoteName(schemaName, hSchemaName, sizeof hSchemaName);
if (rc) {
char errMsg[131 + 50];
memcpy(SQLUDF_STATE, "38000", SQLUDF_SQLSTATE_LEN);
sprintf(errMsg, "Invalid schema name '%s'.", schemaName);
memcpy(SQLUDF_MSGTX, errMsg, SQLUDF_MSGTEXT_LEN);
goto cleanup;
}
}

/* check and unquote table name */
!! change the test for a NULL in the table name
if (SQLUDF_NULL(&nulls[TABLE_NAME_IND])) {
memcpy(SQLUDF_STATE, "38001", SQLUDF_SQLSTATE_LEN);
strcpy(SQLUDF_MSGTX, "No table name specified.");
goto cleanup;
}

Everything following the above snippet can remain as before.

p.s: I'll take care of updating the article.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #8

P: n/a
Knut, we are unix aix 5.1.0 UDB is v7.2 fp 9.

On a side note and I see where you are part of the Information
Integrator Development. Before datajoiner support runs out in the next
couple of months we are in the process of going to information
integrator. It says nicknames are not supported for stored procedures
in II, while they were in datajoiner. We have nicknames to a couple of
Oracle stored procedures. Any suggestions for a work around for this?

Thanks for all your help.
Knut Stolze <st****@de.ibm.com> wrote in message news:<ce**********@fsuj29.rz.uni-jena.de>...
Jeff wrote:
"Raquel" <ra****@nospam.com> wrote in message

news:<85******************************@localhost.t alkaboutdatabases.com>...
Thanks Knut. You are the co-author of the above very useful article!!

Wow!! So, at last I find a feature that is readily available in BIG DB2
(DB2 for OS/390) and not in small DB2 (aka UDB for LUW).

Raquel.


Knut, yes this article is very helpful. We have implemented it on both
V8 and V7. The V8 version works just as intended. The only thing we
have trouble with is on the TRUNCATE stored procedure on V7. When an
invalid table name is entered, we get the following error:

SQL1131N DARI (Stored Procedure) process has been terminated
abnormally. SQLST
ATE=38503
Anything we can do to fix this problem in V7? We often get calls on
this abend which is only caused when a non-existent table name is
entered. I have no C++ skills


I'll look into this - you should have told us earlier that you experience
some problems!. Can you please tell us which platform you are running on
and which DB2 FixPack-level is installed?

Nov 12 '05 #9

P: n/a
Jeff wrote:
Knut, we are unix aix 5.1.0 UDB is v7.2 fp 9.
Good, then I was using the same platform, just on FP7. ;-)

You saw my other post from today? It should solve your problem.
On a side note and I see where you are part of the Information
Integrator Development. Before datajoiner support runs out in the next
couple of months we are in the process of going to information
integrator. It says nicknames are not supported for stored procedures
in II, while they were in datajoiner. We have nicknames to a couple of
Oracle stored procedures. Any suggestions for a work around for this?


Sorry, not my area of expertise. Would be using "passthru" an option for
you?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.