473,856 Members | 1,806 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.pg01s08 5(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(SMALLI NT 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(O S))
/*--------------------------------------------------------------*/
/* 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.TYP E = (short)*Type;
DCLMATERIAU.IDE NT = (short)*Ident;
Retcode = 0;
/*************** *************** ************/
/* recherche le libelle */
/*************** *************** ************/
Strlen = 0;
EXEC SQL SELECT LIBELLE
INTO :DCLMATERIAU.LI BELLE
FROM INFOTER.MATERIA U
WHERE TYPE = :DCLMATERIAU.TY PE AND
IDENT = :DCLMATERIAU.ID ENT;
Retcode = SQLCODE;
if (SQLCODE == 100)
{
Retcode = 0;
goto fin;
}
if (SQLCODE |= 0) goto fin;
for (j = 0;j < 30;j++)
{
if (DCLMATERIAU.LI BELLE[j] |= ' ')
Strlen = j+1;
}
/*************** *************** ************/
/* Fin du traitement, retour des */
/* paramètres au programme client. */
/*************** *************** ************/
fin:;
memcpy(argv[3],DCLMATERIAU.LI BELLE,30);
*Strlen1 = Strlen ;
*Rcode = Retcode;

}

cmateriau.sqc
=============

/*************** *************** *************** *************** *********/
/* DCLGEN TABLE(INFOTER.M ATERIAU) */
/* LIBRARY(TPRIV.I NCLDLIB(CMATERI A)) */
/* ACTION(REPLACE) */
/* LANGUAGE(C) */
/* STRUCTURE(DCLMA TERIAU) */
/* APOST */
/* ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS */
/*************** *************** *************** *************** *********/
EXEC SQL DECLARE INFOTER.MATERIA U 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.MATERIA U */
/*************** *************** *************** *************** *********/
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.PG01S08 5(1, 1, ?, ?, ?)

ANSWER: EN SERVICE

On LUW
======

PG01S085.SQL
============

CREATE PROCEDURE INFOTER.PG01S08 5(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.PG01S08 5 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(O S))
/*--------------------------------------------------------------*/
/* 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.TYP E = (short)*Type;
DCLMATERIAU.IDE NT = (short)*Ident;
Retcode = 0;
/*************** *************** ************/
/* recherche le libelle */
/*************** *************** ************/
Strlen = 0;
EXEC SQL SELECT LIBELLE
INTO :DCLMATERIAU.LI BELLE
FROM INFOTER.MATERIA U
WHERE TYPE = :DCLMATERIAU.TY PE AND
IDENT = :DCLMATERIAU.ID ENT;
Retcode = SQLCODE;
if (SQLCODE == 100)
{
Retcode = 0;
goto fin;
}
if (SQLCODE |= 0) goto fin;
for (j = 0;j < 30;j++)
{
if (DCLMATERIAU.LI BELLE[j] |= ' ')
Strlen = j+1;
}
/*************** *************** ************/
/* Fin du traitement, retour des */
/* paramètres au programme client. */
/*************** *************** ************/
fin:;
memcpy(argv[3],DCLMATERIAU.LI BELLE,30);
*Strlen1 = Strlen ;
*Rcode = Retcode;

}

cmateriau.sqc
=============

/*************** *************** *************** *************** *********/
/* DCLGEN TABLE(INFOTER.M ATERIAU) */
/* LIBRARY(TPRIV.I NCLDLIB(CMATERI A)) */
/* ACTION(REPLACE) */
/* LANGUAGE(C) */
/* STRUCTURE(DCLMA TERIAU) */
/* APOST */
/* ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS */
/*************** *************** *************** *************** *********/
EXEC SQL DECLARE INFOTER.MATERIA U 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.MATERIA U */
/*************** *************** *************** *************** *********/
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\SQL LIB\samples\c>b ldrtn.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\SQ LLIB\samples\c\ CMATERIA.sqc".
38 SQL0063W Completed INCLUDE of file "CMATERIA.s qc".
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\SQL LIB\samples\c>r em Compile the program.

C:\Appl\IBM\SQL LIB\samples\c>i f exist "pg01s085.c xx" goto cpp

C:\Appl\IBM\SQL LIB\samples\c>c l -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\SQL LIB\samples\c>g oto link_step

C:\Appl\IBM\SQL LIB\samples\c>r em Link the program.

C:\Appl\IBM\SQL LIB\samples\c>l ink -debug -out:pg01s085.dl l -dll pg01s085.obj
db2
api.lib -def:pg01s085.de f
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\SQL LIB\samples\c>r em Copy the routine DLL to the 'function'
director
y

C:\Appl\IBM\SQL LIB\samples\c>c opy pg01s085.dll "C:\Appl\IBM\SQ LLIB\function"
1 file(s) copied.

C:\Appl\IBM\SQL LIB\samples\c>

I have successfully cataloged the stored procedure

*************** *************** ***
------------------------------ Commands Entered ------------------------------

connect to oudbsoi1 user sdb2 using ********@
CREATE PROCEDURE INFOTER.PG01S08 5(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.PG01S08 5 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.PG01S08 5(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.PG01S08 5 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,SMALL INT,CHAR(),
SMALLINT,SMALLI NT)
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.pg01s08 5(1, 1, ?, ?, ?);

ANSWER:

------------------------------ Commands Entered ------------------------------

connect to oudbsoi1 user sdb2 using ********;
call infoter.pg01s08 5(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.pg01s08 5(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 3198

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

Similar topics

2
4710
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 all the software installed on each piece of hardware by referencing the primary keys of each table. So now, I have a request that retrieve information from those 3 tables giving a list of all the hardware with their details + the software...
7
9015
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 learning how to pass these varibables from ASP to the SP.
1
1317
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 update one of the columns called Status from "incomplete" to "Pending" Status is one of the parameters for stored procedure #1. This all works fine, My problem is after the person is finished with the record they then push the next button to get...
1
2007
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 great help if any one help me in step-by-step guidelines to migrate SPs written in Java & C languages. I have successfully recreated SQL procedures.
2
5469
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
7
9726
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 for this being to stop the user thinking the application has frozen when in fact it is just waiting for a long SP to complete. Another reason for doing it like this is that I also have had a problem in the past where the SP takes longer than the...
1
7538
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 ( ) DYNAMIC RESULT SETS 1 ------------------------------------------------------------------------
3
4105
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 AS400 I have a stored procedure (SPRUNQRY) that runs the RUNQRY command with the name of the query as a parameter. In the AS400 I would type "RUNQRY SUNTR401A" on a command line to run this query. My connection is opening, and I can run some...
17
4023
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 companyid is the primary key and it should be autogenerate.
0
9911
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look ! Part I. Meaning of...
0
9762
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10780
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10383
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9530
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7093
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5761
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5958
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4174
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.