SEO

I'm trying to invoke a DB2 stored procedure. The stored proc is coded
in C and compiled to a shared library, which has been placed in the
<DB2 dir>/functions directory. The platform is Solaris.
>From the debug log it seems that the stored procedure can't be found,
although I don't know why. Using the control centre, I can see that the
procedure name is defined in the database, however it can not be
invoked.
Can anyone shed any light on what could be causing this problem, or
suggest anything for further investigation?

The procedure is being invoked from Java code via JDBC, if that has any
bearing on the problem.


2006-05-30-10.18.45.800290+000 I291952C399 LEVEL: Severe
PID : 4204 TID : 7 PROC : db2fmp (C) 0
INSTANCE: db2inst3 NODE : 000
FUNCTION: DB2 UDB, routine_infrastructure, sqlerRoutineResolveAddress,
probe:10
RETCODE : ZRC=0x870F0038=-2029060040=SQLO_PROC_NOT_FOUND "Stored Proc
Not Found"
DIA1002C Error accessing function "" in library "".

2006-05-30-10.18.45.825335+000 I292352C399 LEVEL: Warning
PID : 4204 TID : 7 PROC : db2fmp (C) 0
INSTANCE: db2inst3 NODE : 000
MESSAGE : sqlerRoutineLoad (-444): input string ...
DATA #1 : Hexdump, 28 bytes
0x00072DFA : 7373 6F6C 6974 6521 7373 6F6C 6974 655F
ssolite!ssolite_
0x00072E0A : 6765 745F 7365 7175 656E 6365 get_sequence

2006-05-30-10.18.45.825917+000 I292752C331 LEVEL: Warning
PID : 4204 TID : 7 PROC : db2fmp (C) 0
INSTANCE: db2inst3 NODE : 000
MESSAGE : sqlerRoutineLoad (-444): instance name ...
DATA #1 : Hexdump, 16 bytes
0x00072BB2 : 5357 5F47 4554 5F53 4551 5F54 5241 4E53
SW_GET_SEQ_TRANS

2006-05-30-10.18.45.826401+000 I293084C466 LEVEL: Warning
PID : 4204 TID : 7 PROC : db2fmp (C) 0
INSTANCE: db2inst3 NODE : 000
MESSAGE : sqlerRoutineLoad (-444): resolved path ...
DATA #1 : Hexdump, 37 bytes
0x00072E28 : 2F65 7870 6F72 742F 686F 6D65 2F64 6232
/export/home/db2
0x00072E38 : 696E 7374 332F 7371 6C6C 6962 2F66 756E
inst3/sqllib/fun
0x00072E48 : 6374 696F 6E ction

2006-05-30-10.18.45.826955+000 I293551C323 LEVEL: Warning
PID : 4204 TID : 7 PROC : db2fmp (C) 0
INSTANCE: db2inst3 NODE : 000
MESSAGE : sqlerRoutineLoad (-444): resolved module ...
DATA #1 : Hexdump, 7 bytes
0x00072E50 : 7373 6F6C 6974 65 ssolite

2006-05-30-10.18.45.827417+000 I293875C396 LEVEL: Warning
PID : 4204 TID : 7 PROC : db2fmp (C) 0
INSTANCE: db2inst3 NODE : 000
MESSAGE : sqlerRoutineLoad (-444): resolved function ...
DATA #1 : Hexdump, 20 bytes
0x00072E60 : 7373 6F6C 6974 655F 6765 745F 7365 7175
ssolite_get_sequ
0x00072E70 : 656E 6365 ence

What was the exact command that you (or Control-Centre) used to catalog
the procedure?

In the source-code for the procedure, what is the function definition
for the stored procedure?

What are the owner:group and file permissions for the binary?

mike wrote:
> What was the exact command that you (or Control-Centre) used to catalog
> the procedure?

By 'catalog', do you mean create? If so, here is the command used to
create the procedure -

CREATE PROCEDURE TABLE_OWNER.SW_GET_SEQ_TRANS
(
IN @szTableOwner VARCHAR(48), -- table owner
IN @nSeqType INTEGER, -- Type of sequence to retrieve reqid is 1 and
casenum is 2
IN @nSeqSize INTEGER, -- Size of cache for sequences
OUT @nSeqValue BIGINT, -- return for sequence number
OUT @szSQLState VARCHAR(33), -- on error holds the SQL state
OUT @szSQLErr VARCHAR(257) -- on error holds the SQL error
message
)
SPECIFIC SW_GET_SEQ_TRANS
DYNAMIC RESULT SETS 0
NOT DETERMINISTIC
LANGUAGE C
PARAMETER STYLE SQL
NO DBINFO
FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'ssolite!ssolite_get_sequence'/


>
> What are the owner:group and file permissions for the binary?

-rwxr-xr-x 1 root other 8224 Jun 6 2005 ssolite

>
> In the source-code for the procedure, what is the function definition
> for the stored procedure?

SQL_API_RC SQL_API_FN ssolite_get_sequence
(
char TableOwner[49], /* IN */
sqlint32 *seq_type, /* IN */
sqlint32 *cache_size, /* IN */
sqlint64 *seq_no, /* OUT */
char SQLState[LOCAL_SQL_STATE_SIZE], /* OUT */
char SQLErr[LOCAL_SQL_ERROR_SIZE] /* OUT */
)
{
SQLHANDLE henv, hdbc;
SQLRETURN cliRC;
SQLSMALLINT outlen;
LPTHRD_SEQ_ARG pThrdArg = NULL;
#ifdef WIN32
DWORD tid;
HANDLE hThread;
#else
pthread_t tid;
void *status;
#endif /* #ifdef WIN32 */
char DBAlias[49];

/* initialise returns */
memset (SQLState, '\0', sizeof (SQLState));
memset (SQLErr, '\0', sizeof (SQLErr));
*seq_no = 0;

/* Setup the CLI environment */
cliRC = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if ((cliRC != SQL_SUCCESS) && (cliRC != SQL_SUCCESS_WITH_INFO))
{
*seq_no = -1;
}

if (*seq_no == 0)
{
cliRC = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if ((cliRC != SQL_SUCCESS) && (cliRC != SQL_SUCCESS_WITH_INFO))
{
*seq_no = -2;

/* free the environment handle */
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
}

if (*seq_no == 0)
{
/* Issue NULL Connect, because in CLI a statement handle is */
/* required and thus a connection handle and environment handle.
/* A connection is not established; rather the current */
/* connection from the calling application is used. */

/* connect to a data source */
cliRC = SQLConnect(hdbc, NULL, SQL_NTS, NULL, SQL_NTS, NULL,
SQL_NTS);

if (cliRC != SQL_SUCCESS)
{
*seq_no = -3;

/* free hdbc handle */
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);

/* free the environment handle */
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
}

/* get the name of the database we are connected to */
if (*seq_no == 0)
{
/* get server name information */
cliRC = SQLGetInfo(hdbc, SQL_DATABASE_NAME, DBAlias, 49, &outlen);

if (cliRC != SQL_SUCCESS)
{
*seq_no = -4;
sql_cleanup(henv, hdbc);
}
}

/* disconnect */
if (*seq_no == 0)
{
sql_cleanup(henv, hdbc);
}

/* allocate arguments and create thread to perform SQL */
if (*seq_no == 0)
{
pThrdArg = (LPTHRD_SEQ_ARG) calloc (sizeof (THRD_SEQ_ARG), 1);

if (!pThrdArg)
{
*seq_no = -30;
}
else
{
pThrdArg->seq_type = *seq_type;
pThrdArg->cache_size = *cache_size;
pThrdArg->lpszTableOwner = TableOwner;
pThrdArg->lpszDBAlias = DBAlias;
pThrdArg->lpszSQLState = SQLState;
pThrdArg->lpszSQLErr = SQLErr;

#ifdef WIN32
hThread = CreateThread(
NULL, // no security attributes
0, // use default stack size
ThreadFunc, // thread function
pThrdArg, // argument to thread function
0, // use default creation flags
&tid); // returns the thread identifier

if (hThread != NULL)
{
WaitForSingleObject (hThread, INFINITE);
CloseHandle (hThread);
#else
if (pthread_create (&tid, NULL, ThreadFunc, (void *) pThrdArg) == 0)
{
pthread_join (tid, &status);
#endif /* #ifdef WIN32 */

/* return thread generate sequence number */
*seq_no = pThrdArg->seq_no;
}
else
{
*seq_no = -31;
}

free (pThrdArg);
}
}

return (0);
}

Not sure of the cause of your problem.

On your "current function path" is the
schema that contains your procedure present on
that list ?

For that style of procedure ( C, cli procedure)
in the past I have only used a single entry point
into the shared library file. At least this
works for me on AIX with v7 and v8.

But it is supposed
to work using the indirection (i.e. catalogued
and called as one name, exported from shared library
as another name).

That is to say, if the procedure is called with
syntax
CALL schema.FRED, then I have created the
binary as FRED. Then it worked for me, i.e.
there was no indirection involved - the
stored procedure name being called had
the same name as the binary to load.

I would have the db2-instance-owner
as the owner of the file not root, and
ensure that the fenced userid has read and
execute rights to the file - although i can
see that your file permissions seem fine.

hicks@bigmailbox.net wrote:

>> In the source-code for the procedure, what is the function definition
>> for the stored procedure?
>

Have you used a C or C++ compiler? In the latter case, you should add the
following here:

extern "C"

Otherwise, you'll get C++ name mangling and then DB2 won't be able to find
the function in the library

> SQL_API_RC SQL_API_FN ssolite_get_sequence
> (
> char TableOwner[49], /* IN */
> sqlint32 *seq_type, /* IN */
> sqlint32 *cache_size, /* IN */
> sqlint64 *seq_no, /* OUT */
> char SQLState[LOCAL_SQL_STATE_SIZE], /* OUT */
> char SQLErr[LOCAL_SQL_ERROR_SIZE] /* OUT */
> )
> {
> SQLHANDLE henv, hdbc;
> SQLRETURN cliRC;
> SQLSMALLINT outlen;
> LPTHRD_SEQ_ARG pThrdArg = NULL;
> #ifdef WIN32
> DWORD tid;
> HANDLE hThread;
> #else
> pthread_t tid;
> void *status;
> #endif /* #ifdef WIN32 */

You are aware that threads are not supported in external routines?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany