472,805 Members | 834 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

Help with stored procedure

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)
Jan 10 '08 #1
0 3055

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: berthelot samuel | last post by:
Hi everyone, I am currently trying to write a report based on a View of SQL Server. Basically, I have 3 tables : Hardware, SoftwareInstalled and Software with SoftwareInstalled that keeps track of...
7
by: Bill Kellaway | last post by:
Hi there - this should be fairly simple for someone. Basically I can't figure out how to pass the parameters from ASP to a Stored Procedure on SQL. Here's my code: I just need to help in...
1
by: Ken | last post by:
Hello, I have a webform that gets a record from SQL Server by using a stored procedure. The stored procedure uses 3 parameters in which after the record is found, stored procedure #2 runs to...
1
by: Kumar | last post by:
Hi I am trying to recreate a database under the following environments : From: Solaris with DB2UDB version 7.2 with FP 9 To: Linux with DB2UDB version 7.2 with FP 9 It will be of really a...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
7
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get notification that it has completed. The main reason...
1
by: peaceburn | last post by:
Hi, I'm gonna pull my hair in the coming days with these DB2 stored procedures. So the issue, let's assume a simple stored procedure like this : CREATE PROCEDURE MYSCHEMA.PROCEDURE1 ( )...
3
by: Darth Ferret | last post by:
This thing is about to drive me crazy. I have about 50 queries in the AS400 that I need to put on a menu. Once I conquer this I have a bunch more rpg reports that I need to pass a date to. In the...
17
by: Riaaaa | last post by:
Pls check my code for the stored procedure which i created for the companydetails including companyid P.K. Not Null int(4), companyname Not Null varchar (20), address varchar(30) where...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.