I write my stored procedure by embedding SQL statements in C. I have successfully builded the stored procedure by execute the command "bldsrv csp runstat my_database" and the executable csp program has been copied to "/home/db2inst1/sqllib/function/". The file csp.sqc is my stored procedure in C. and I have successfully cataloged the stored procedures by entering:db2 -td@ -vf csp.db2. But when I call the stored procedure, the DB2 raise the SQL1131N error code, e.g.
$ db2 "call runstat('db2inst1.test', ?, ?)"
SQL1131N DARI (Stored Procedure) process has been terminated abnormally. SQLSTATE=38503

My stored procedure only invoke the sqlustat(runstats) administrative API function. My OS is AIX4.3.3, ML10, DB27.2 FP7.

the following is my .sqc .exp and .db2 file

$ cat csp.sqc
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>

SQL_API_RC SQL_API_FN runstat(char *, sqlint32 *, char *);

SQL_API_RC SQL_API_FN runstat
(char in_scmtab[100], /*schema_name.table_name*/
sqlint32 *out_sqlerror,
char out_diagmsg[71]
)
{
struct sqlca sqlca;
unsigned short numIndexes = 0;
char *indexList[15];

EXEC SQL WHENEVER SQLERROR GOTO return_error;

EXEC SQL BEGIN DECLARE SECTION;
char scmtab[100];
char schemaname[50];
char tablename[50];
char indSchema[50];
char indName[50];
char ind[50];

sqlint16 numRecords;
EXEC SQL END DECLARE SECTION;

*out_sqlerror = 0;

strcpy(scmtab, in_scmtab);

if (strcmp(scmtab, "\0") == 0)
{strcpy(out_diagmsg, "Please input the table name");
return(0);
}


if (strchr(scmtab, '.') != NULL)
{EXEC SQL VALUES(UCASE(SUBSTR(:scmtab, 1, POSSTR(:scmtab, '.') - 1)))
INTO :schemaname;
EXEC SQL VALUES(UCASE(SUBSTR(:scmtab, POSSTR(:scmtab, '.') + 1, LENGTH(:scmtab) - POSSTR(:scmtab, '.'))))
INTO :tablename;
EXEC SQL SELECT COUNT(*)
INTO :numRecords
FROM syscat.tables
WHERE TABSCHEMA = :schemaname and TABNAME = :tablename;
if (numRecords != 1)
{strcpy(out_diagmsg, "Please input the correct SCHEMA_NAME.TABLE_NAME");
return(0);
}
}
else
{strcpy(out_diagmsg, "Please input SCHEMA_NAME.TABLE_NAME");
return(0);
}

EXEC SQL DECLARE c1 CURSOR FOR
SELECT INDSCHEMA, INDNAME FROM SYSCAT.INDEXES;
EXEC SQL OPEN c1;

do
{
EXEC SQL FETCH c1 INTO :indSchema, :indName;
if (SQLCODE != 0) break;
EXEC SQL VALUES(:indSchema || :indName)
INTO :ind;
indexList[numIndexes] = ind;
numIndexes+=1;
memset(ind, '\0', sizeof(ind));
} while (1);

EXEC SQL CLOSE c1;

sqlustat (scmtab, numIndexes, indexList, SQL_STATS_TABLE, SQL_STATS_CHG, &sqlca);

return(0);

return_error:
{
*out_sqlerror = SQLCODE;
EXEC SQL WHENEVER SQLERROR CONTINUE;
return(0);
}
}


$ cat csp.exp
runstat


$ cat csp.db2
CREATE PROCEDURE RUNSTAT (IN scmtab varchar(100), OUT error_code int, OUT error_label varchar(71))
DYNAMIC RESULT SETS 0
LANGUAGE C
PARAMETER STYLE GENERAL
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'csp!runstat'@