Hi all!
Happy New Year 2008. Il hope it will bring you love and happyness
I'm new on this forum.
I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c.
Everything works fine.
Now we decided to move from mainframe IMS-DB2 to Windows 2003 server-DB2 UDB
for LUW 9.5.
I Installed Microsoft Visual C++ 2003 on the windows 2003 server and DB2 UDB
for LUW 9.5.
I modified my mainframe source code pg01s085.sqc to adapt to the Microsoft
Visual C++ 2003 environment, and remove all z/OS stored Procedure syntaxes in
the file pg01s085.sql. that are not supported on DB2 UDB LUW.
I used the batch bldrtn.bat (SQLLIB/SAMPLES/c) which precompiled, binded,
compiled and linked. The file .dll generated is copied under the directory
(SQLLIB/FUNCTION).
I made connection to my database, and when I execute the stored procedure,
the DB2 raise the SQL1131N error code, e.g.
call infoter.pg01s085(1, 1, ?, ?, ?)
SQL1131N DARI (Stored Procedure) process has been terminated abnormally.
SQLSTATE=38503
SQL1131N DARI (Stored Procedure) process has been terminated abnormally.
Explanation:
The cause of this error may be:
* There was a coding error (for example, segmentation violation) within
the DARI routine.
* The DARI process has been terminated by another process through the
use of a signal.
User response:
Reinitiate the DARI request after doing the following :
* Ensure that the DARI procedure is free from programming errors.
* Make sure that no user is sending a termination signal to the DARI
process.
sqlcode: -1131
sqlstate: 38503
See above all steps on z/OS and on LUW
==============================
ON z/OS environment
===================
PG01S085.SQL
============
CREATE PROCEDURE PG01S085(SMALLINT IN, SMALLINT IN,
CHARACTER(30) OUT, SMALLINT OUT,
SMALLINT OUT)
EXTERNAL NAME PG01S085
NO WLM ENVIRONMENT
LANGUAGE C
NOT DETERMINISTIC
PARAMETER STYLE GENERAL WITH NULLS
COLLID PROCGPG
STAY RESIDENT NO
DYNAMIC RESULT SET 10 ;
GRANT EXECUTE ON PROCEDURE PG01S085 TO PUBLIC;
PG01S085.SQC
============
/************************************************** **************/
/* */
/* PROGRAMME : TG01S085 (IDEM TG01S084 + LONGUEUR STRING) */
/* */
/* FONCTION : RECHERCHE LE LIBELLE D'UNE DONNEE NORMALISEE */
/* ET LA LONGUEUR DU STRING */
/* INPUT : IDENTIFIANT DU GROUPE DE DONNEE */
/* IDENTIFIANT DE LA DONNEE */
/* OUT : LIBELLE DE LA DONNEE */
/* longueur du string */
/* SQLCODE DE LA RECHERCHE */
/* (sqlcode = 100 admit et transform© en 0) */
/* CREATION : aout 2003 st */
/* */
/* MODIFICATION : */
/* */
/* */
/************************************************** **************/
#pragma options(RENT)
#pragma runopts(PLIST(OS))
/*--------------------------------------------------------------*/
/* include des diff©rentes fonctions et variables externes. */
EXEC SQL INCLUDE SQLCA;
/*--------------------------------------------------------------*/
#include <math.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <sqlda.h>
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL INCLUDE CMATERIA;
EXEC SQL END DECLARE SECTION;
void main(int argc, char* argv[])
{
short* Type;
short* Ident;
char Libelle[31];
short Retcode;
short* Rcode ;
short Strlen;
short* Strlen1;
short j ;
/******************************************/
/* Initialisations des variables Ã* partir */
/* des arguments reçus par le module. */
/* Retcode : Code retour de l'execution */
/******************************************/
Type = (short*)argv[1];
Ident = (short*)argv[2];
Strlen1 = (short*)argv[4];
Rcode = (short*)argv[5];
DCLMATERIAU.TYPE = (short)*Type;
DCLMATERIAU.IDENT = (short)*Ident;
Retcode = 0;
/******************************************/
/* recherche le libelle */
/******************************************/
Strlen = 0;
EXEC SQL SELECT LIBELLE
INTO :DCLMATERIAU.LIBELLE
FROM INFOTER.MATERIAU
WHERE TYPE = :DCLMATERIAU.TYPE AND
IDENT = :DCLMATERIAU.IDENT;
Retcode = SQLCODE;
if (SQLCODE == 100)
{
Retcode = 0;
goto fin;
}
if (SQLCODE |= 0) goto fin;
for (j = 0;j < 30;j++)
{
if (DCLMATERIAU.LIBELLE[j] |= ' ')
Strlen = j+1;
}
/******************************************/
/* Fin du traitement, retour des */
/* paramètres au programme client. */
/******************************************/
fin:;
memcpy(argv[3],DCLMATERIAU.LIBELLE,30);
*Strlen1 = Strlen ;
*Rcode = Retcode;
}
cmateriau.sqc
=============
/************************************************** *******************/
/* DCLGEN TABLE(INFOTER.MATERIAU) */
/* LIBRARY(TPRIV.INCLDLIB(CMATERIA)) */
/* ACTION(REPLACE) */
/* LANGUAGE(C) */
/* STRUCTURE(DCLMATERIAU) */
/* APOST */
/* ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS */
/************************************************** *******************/
EXEC SQL DECLARE INFOTER.MATERIAU TABLE
( TYPE SMALLINT NOT NULL,
CODE CHAR(4) NOT NULL,
IDENT SMALLINT NOT NULL,
LIBELLE CHAR(30) NOT NULL,
TRI INTEGER NOT NULL
) ;
/************************************************** *******************/
/* C DECLARATION FOR TABLE INFOTER.MATERIAU */
/************************************************** *******************/
struct
{ short int TYPE;
char CODE[5];
short int IDENT;
char LIBELLE[31];
long int TRI;
} DCLMATERIAU;
/************************************************** *******************/
/* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 5 */
/************************************************** *******************/
TABLE MATERIAU (on row)
=======================
TYPE CODE IDEN LIBELLE TRI
1 SERV 1 EN SERVICE 6586369
From DB2 connect, Store Procedure BUilder
call INFOTER.PG01S085(1, 1, ?, ?, ?)
ANSWER: EN SERVICE
On LUW
======
PG01S085.SQL
============
CREATE PROCEDURE INFOTER.PG01S085(IN SMALLINT , IN SMALLINT, OUT CHARACTER(30)
, OUT SMALLINT, OUT SMALLINT)
SPECIFIC PG01S085
DYNAMIC RESULT SETS 10
NOT DETERMINISTIC
LANGUAGE C
PARAMETER STYLE GENERAL WITH NULLS
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'pg01s085!main'
GRANT EXECUTE ON PROCEDURE INFOTER.PG01S085 TO PUBLIC
PG01S085.SQC
============
/************************************************** **************/
/* */
/* PROGRAMME : TG01S085 (IDEM TG01S084 + LONGUEUR STRING) */
/* */
/* FONCTION : RECHERCHE LE LIBELLE D'UNE DONNEE NORMALISEE */
/* ET LA LONGUEUR DU STRING */
/* INPUT : IDENTIFIANT DU GROUPE DE DONNEE */
/* IDENTIFIANT DE LA DONNEE */
/* OUT : LIBELLE DE LA DONNEE */
/* longueur du string */
/* SQLCODE DE LA RECHERCHE */
/* (sqlcode = 100 admit et transform© en 0) */
/* CREATION : aout 2003 st */
/* */
/* MODIFICATION : */
/* */
/* */
/************************************************** **************/
#pragma options(RENT)
#pragma runopts(PLIST(OS))
/*--------------------------------------------------------------*/
/* include des diff©rentes fonctions et variables externes. */
EXEC SQL INCLUDE SQLCA;
/*--------------------------------------------------------------*/
#include <math.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <sqlda.h>
#include <sqlutil.h>
#include <sqlenv.h>
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL INCLUDE CMATERIA;
EXEC SQL END DECLARE SECTION;
void main(int argc, char* argv[])
{
short* Type;
short* Ident;
char Libelle[31];
short Retcode;
short* Rcode ;
short Strlen;
short* Strlen1;
short j ;
/******************************************/
/* Initialisations des variables Ã* partir */
/* des arguments reçus par le module. */
/* Retcode : Code retour de l'execution */
/******************************************/
Type = (short*)argv[1];
Ident = (short*)argv[2];
Strlen1 = (short*)argv[4];
Rcode = (short*)argv[5];
DCLMATERIAU.TYPE = (short)*Type;
DCLMATERIAU.IDENT = (short)*Ident;
Retcode = 0;
/******************************************/
/* recherche le libelle */
/******************************************/
Strlen = 0;
EXEC SQL SELECT LIBELLE
INTO :DCLMATERIAU.LIBELLE
FROM INFOTER.MATERIAU
WHERE TYPE = :DCLMATERIAU.TYPE AND
IDENT = :DCLMATERIAU.IDENT;
Retcode = SQLCODE;
if (SQLCODE == 100)
{
Retcode = 0;
goto fin;
}
if (SQLCODE |= 0) goto fin;
for (j = 0;j < 30;j++)
{
if (DCLMATERIAU.LIBELLE[j] |= ' ')
Strlen = j+1;
}
/******************************************/
/* Fin du traitement, retour des */
/* paramètres au programme client. */
/******************************************/
fin:;
memcpy(argv[3],DCLMATERIAU.LIBELLE,30);
*Strlen1 = Strlen ;
*Rcode = Retcode;
}
cmateriau.sqc
=============
/************************************************** *******************/
/* DCLGEN TABLE(INFOTER.MATERIAU) */
/* LIBRARY(TPRIV.INCLDLIB(CMATERIA)) */
/* ACTION(REPLACE) */
/* LANGUAGE(C) */
/* STRUCTURE(DCLMATERIAU) */
/* APOST */
/* ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS */
/************************************************** *******************/
EXEC SQL DECLARE INFOTER.MATERIAU TABLE
( TYPE SMALLINT NOT NULL,
CODE CHAR(4) NOT NULL,
IDENT SMALLINT NOT NULL,
LIBELLE CHAR(30) NOT NULL,
TRI INTEGER NOT NULL
) ;
/************************************************** *******************/
/* C DECLARATION FOR TABLE INFOTER.MATERIAU */
/************************************************** *******************/
struct
{ short int TYPE;
char CODE[5];
short int IDENT;
char LIBELLE[31];
long int TRI;
} DCLMATERIAU;
/************************************************** *******************/
/* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 5 */
/************************************************** *******************/
PG01S085.DEF
============
LIBRARY PG01S085
EXPORTS
main
C:\Appl\IBM\SQLLIB\samples\c>bldrtn.bat pg01s085 oudbsoi1
Database Connection Information
Database server = DB2/NT 9.5.0
SQL authorization ID = SDB2
Local database alias = OUDBSOI1
LINE MESSAGES FOR pg01s085.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
38 SQL0062W Starting INCLUDE of file
"C:\Appl\IBM\SQLLIB\samples\c\CMATERIA.sqc".
38 SQL0063W Completed INCLUDE of file "CMATERIA.sqc".
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.
LINE MESSAGES FOR utilemb.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.
LINE MESSAGES FOR pg01s085.bnd
------ --------------------------------------------------------------------
SQL0061W The binder is in progress.
SQL0091N Binding was ended with "0" errors and "0" warnings.
DB20000I The SQL command completed successfully.
C:\Appl\IBM\SQLLIB\samples\c>rem Compile the program.
C:\Appl\IBM\SQLLIB\samples\c>if exist "pg01s085.cxx" goto cpp
C:\Appl\IBM\SQLLIB\samples\c>cl -Zi -Od -c -W2 -DWIN32 -MD pg01s085.c
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8804 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.
pg01s085.c
pg01s085.c(3) : warning C4305: 'initializing' : truncation from 'const int '
to
'char '
C:\Appl\IBM\SQLLIB\samples\c>goto link_step
C:\Appl\IBM\SQLLIB\samples\c>rem Link the program.
C:\Appl\IBM\SQLLIB\samples\c>link -debug -out:pg01s085.dll -dll pg01s085.obj
db2
api.lib -def:pg01s085.def
Microsoft (R) Incremental Linker Version 6.00.8447
Copyright (C) Microsoft Corp 1992-1998. All rights reserved.
Creating library pg01s085.lib and object pg01s085.exp
C:\Appl\IBM\SQLLIB\samples\c>rem Copy the routine DLL to the 'function'
director
y
C:\Appl\IBM\SQLLIB\samples\c>copy pg01s085.dll "C:\Appl\IBM\SQLLIB\function"
1 file(s) copied.
C:\Appl\IBM\SQLLIB\samples\c>
I have successfully cataloged the stored procedure
*********************************
------------------------------ Commands Entered ------------------------------
connect to oudbsoi1 user sdb2 using ********@
CREATE PROCEDURE INFOTER.PG01S085(IN SMALLINT , IN SMALLINT, OUT CHARACTER(30)
, OUT SMALLINT, OUT SMALLINT)
SPECIFIC PG01S085
DYNAMIC RESULT SETS 10
NOT DETERMINISTIC
LANGUAGE C
PARAMETER STYLE GENERAL WITH NULLS
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'pg01s085!main'@
GRANT EXECUTE ON PROCEDURE INFOTER.PG01S085 TO PUBLIC@
TERMINATE@
------------------------------------------------------------------------------
connect to oudbsoi1 user sdb2 using
Database Connection Information
Database server = DB2/NT 9.5.0
SQL authorization ID = SDB2
Local database alias = OUDBSOI1
CREATE PROCEDURE INFOTER.PG01S085(IN SMALLINT , IN SMALLINT, OUT CHARACTER(30)
, OUT SMALLINT, OUT SMALLINT)
SPECIFIC PG01S085
DYNAMIC RESULT SETS 10
NOT DETERMINISTIC
LANGUAGE C
PARAMETER STYLE GENERAL WITH NULLS
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'pg01s085!main'
DB20000I The SQL command completed successfully.
GRANT EXECUTE ON PROCEDURE INFOTER.PG01S085 TO PUBLIC
DB20000I The SQL command completed successfully.
TERMINATE
DB20000I The TERMINATE command completed successfully.
Schema Name Specific Name Fenced Implementation language Input parameter
INFOTER PG01S085 PG01S085 Y pg01s085!main C (SMALLINT,SMALLINT,CHAR(),
SMALLINT,SMALLINT)
TABLE MATERIAU (on row)
=======================
TYPE CODE IDEN LIBELLE TRI
1 SERV 1 EN SERVICE 6586369
From command Editor
connect to oudbsoi1 user sdb2 using 4UStaZup;
call infoter.pg01s085(1, 1, ?, ?, ?);
ANSWER:
------------------------------ Commands Entered ------------------------------
connect to oudbsoi1 user sdb2 using ********;
call infoter.pg01s085(1, 1, ?, ?, ?);
------------------------------------------------------------------------------
connect to oudbsoi1 user sdb2 using
Database Connection Information
Database server = DB2/NT 9.5.0
SQL authorization ID = SDB2
Local database alias = OUDBSOI1
call infoter.pg01s085(1, 1, ?, ?, ?)
SQL1131N DARI (Stored Procedure) process has been terminated abnormally.
SQLSTATE=38503
SQL1131N DARI (Stored Procedure) process has been terminated abnormally.
Explanation:
The cause of this error may be:
* There was a coding error (for example, segmentation violation) within
the DARI routine.
* The DARI process has been terminated by another process through the
use of a signal.
User response:
Reinitiate the DARI request after doing the following :
* Ensure that the DARI procedure is free from programming errors.
* Make sure that no user is sending a termination signal to the DARI
process.
sqlcode: -1131
sqlstate: 38503
A JDBC connection to the target has succeeded.
Please, any help to find out what is wrong ?
Thank you for your promptness.
Best regrads.
Sinclair Bendo
Do it now or never
Or you are the first or you die
KISS (Keep IT Simple and Stupid)
