SEO

DB2 version 8, fixpack 5. Aix 5.1.0.0 Using C for AIX compiler.

I'm having a problem where I can create stored procedures but I'm
getting -444 when I call them. It's a new environment for running
SP's so I wrote a very simple procedure just to make sure everything
is working. Below is the db2set output for the
SQLROUTINE_COMPILE_COMMAND, stored procedure text, log file, and
result of a call. I'm getting an error that says function "pgsjmp"
cannot be accessed reason code 6. The message associated with that
reason code doesn't offer much help. The path to the compiler is in
/usr/vac/bin.

Can anyone tell me what "pgsjmp" is and what it's used for?

Any ideas are greatly appreciated.

db2set output:
DB2_SQLROUTINE_COMPILE_COMMAND=xlc_r
-I/home/brdinst0/sqllib/include/SQLROUTINE_FILENAME.c
-bE:SQLROUTINE_FILENAME.exp -e SQLROUTINE_ENTRY -o SQLROUTINE_FILENAME
-L/home/brdinst0/sqllib/lib -ldb2


Procedure text:
create procedure brdinst0.proc_time ( out var1 timestamp )
specific proc1
language sql


P1: BEGIN

values current timestamp into var1 ;

END P1

@

Log File output:
-- LOG FILE P2451252.log FOR PROCEDURE BRDINST0.PROC_TIME

-- DB2_SQLROUTINE_PREPOPTS=

-- PREP/BIND MESSAGES FOR
/home/brdinst0/sqllib/function/routine/sqlproc/BRDODS2/BRDINST0/tmp/P2451252.sqc

LINE MESSAGES FOR P2451252.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.

-- DB2_SQLROUTINE_COMPILE_COMMAND=xlc_r
-I/home/brdinst0/sqllib/include/SQLROUTINE_FILENAME.c
-bE:SQLROUTINE_FILENAME.exp -e SQLROUTINE_EN
TRY -o SQLROUTINE_FILENAME -L/home/brdinst0/sqllib/lib -ldb2

-- CONTENTS OF /home/brdinst0/sqllib/function/routine/sr_cpath

#!/bin/sh
PATH=$PATH:/usr/vac/bin
PATH=$PATH:/usr/vacpp/bin
PATH=$PATH:/usr/ibmcxx/bin
PATH=$PATH:/usr/lpp/xlC/bin
export PATH

-- CONTENTS OF /home/brdinst0/sqllib/function/routine/sqlproc/BRDODS2/BRDINST0/tmp/P2451252.sh

SQLROUTINE_FILENAME=P2451252
export SQLROUTINE_FILENAME
export SQLROUTINE_ENTRY=pgsjmp

-- COMPILATION COMMAND:

xlc_r -I/home/brdinst0/sqllib/include/P2451252.c -bE:P2451252.exp -e
pgsjmp -o P2451252 -L/home/brdinst0/sqllib/lib -ldb2


-- CONTENTS OF /home/brdinst0/sqllib/function/routine/sqlproc/BRDODS2/BRDINST0/tmp/P2451252.exp

pgsjmp


-- COMPILATION MESSAGES FOR
/home/brdinst0/sqllib/function/routine/sqlproc/BRDODS2/BRDINST0/tmp/P2451252.c
ld: 0711-327 WARNING: Entry point not found: pgsjmp
ld: 0711-319 WARNING: Exported symbol not defined: pgsjmp
ld: 0711-244 ERROR: No csects or exported symbols have been saved.

-- END OF LOG FILE (SQLCODE: 0)


Call Statement output:
>db2 "call brdinst0.proc_time (?)"
SQL0444N Routine "PROC_TIME" (specific name "PROC1") is implemented
with code
in library or path ".../routine/sqlproc/BRDODS2/BRDINST0/P2451252",
function
"pgsjmp" which cannot be accessed. Reason code: "6". SQLSTATE=42724

Private Pyle wrote:
> DB2 version 8, fixpack 5. Aix 5.1.0.0 Using C for AIX compiler.
>
> I'm having a problem where I can create stored procedures but I'm
> getting -444 when I call them. It's a new environment for running
> SP's so I wrote a very simple procedure just to make sure everything
> is working. Below is the db2set output for the
> SQLROUTINE_COMPILE_COMMAND, stored procedure text, log file, and
> result of a call. I'm getting an error that says function "pgsjmp"
> cannot be accessed reason code 6. The message associated with that
> reason code doesn't offer much help. The path to the compiler is in
> /usr/vac/bin.
>
> Can anyone tell me what "pgsjmp" is and what it's used for?
>
> Any ideas are greatly appreciated.
>
> db2set output:
> DB2_SQLROUTINE_COMPILE_COMMAND=xlc_r
> -I/home/brdinst0/sqllib/include/SQLROUTINE_FILENAME.c
> -bE:SQLROUTINE_FILENAME.exp -e SQLROUTINE_ENTRY -o SQLROUTINE_FILENAME
> -L/home/brdinst0/sqllib/lib -ldb2
>
>
> Procedure text:
> create procedure brdinst0.proc_time ( out var1 timestamp )
> specific proc1
> language sql
>
>
> P1: BEGIN
>
> values current timestamp into var1 ;
>
> END P1
>
> @
>
> Log File output:
> -- LOG FILE P2451252.log FOR PROCEDURE BRDINST0.PROC_TIME
>
> -- DB2_SQLROUTINE_PREPOPTS=
>
> -- PREP/BIND MESSAGES FOR
> /home/brdinst0/sqllib/function/routine/sqlproc/BRDODS2/BRDINST0/tmp/P2451252.sqc
>
> LINE MESSAGES FOR P2451252.sqc
> ------ --------------------------------------------------------------------
> SQL0060W The "C" precompiler is in progress.
> SQL0091W Precompilation or binding was ended with "0"
> errors and "0" warnings.
>
> -- DB2_SQLROUTINE_COMPILE_COMMAND=xlc_r
> -I/home/brdinst0/sqllib/include/SQLROUTINE_FILENAME.c
> -bE:SQLROUTINE_FILENAME.exp -e SQLROUTINE_EN
> TRY -o SQLROUTINE_FILENAME -L/home/brdinst0/sqllib/lib -ldb2
>
> -- CONTENTS OF /home/brdinst0/sqllib/function/routine/sr_cpath
>
> #!/bin/sh
> PATH=$PATH:/usr/vac/bin
> PATH=$PATH:/usr/vacpp/bin
> PATH=$PATH:/usr/ibmcxx/bin
> PATH=$PATH:/usr/lpp/xlC/bin
> export PATH
>
> -- CONTENTS OF /home/brdinst0/sqllib/function/routine/sqlproc/BRDODS2/BRDINST0/tmp/P2451252.sh
>
> SQLROUTINE_FILENAME=P2451252
> export SQLROUTINE_FILENAME
> export SQLROUTINE_ENTRY=pgsjmp
>
> -- COMPILATION COMMAND:
>
> xlc_r -I/home/brdinst0/sqllib/include/P2451252.c -bE:P2451252.exp -e
> pgsjmp -o P2451252 -L/home/brdinst0/sqllib/lib -ldb2
>
>
> -- CONTENTS OF /home/brdinst0/sqllib/function/routine/sqlproc/BRDODS2/BRDINST0/tmp/P2451252.exp
>
> pgsjmp
>
>
> -- COMPILATION MESSAGES FOR
> /home/brdinst0/sqllib/function/routine/sqlproc/BRDODS2/BRDINST0/tmp/P2451252.c
> ld: 0711-327 WARNING: Entry point not found: pgsjmp
> ld: 0711-319 WARNING: Exported symbol not defined: pgsjmp
> ld: 0711-244 ERROR: No csects or exported symbols have been saved.
>
> -- END OF LOG FILE (SQLCODE: 0)
>
>
> Call Statement output:
>
>>db2 "call brdinst0.proc_time (?)"
>
> SQL0444N Routine "PROC_TIME" (specific name "PROC1") is implemented
> with code
> in library or path ".../routine/sqlproc/BRDODS2/BRDINST0/P2451252",
> function
> "pgsjmp" which cannot be accessed. Reason code: "6". SQLSTATE=42724

pgsjmp is the entry point DB2 used for their SQL procedures. Take a look
at
/home/brdinst0/sqllib/function/routine/sqlproc/BRDODS2/BRDINST0/tmp/P2451252.c,
something must be screwed up in there.