DB2 Stored Procedure Select

[cardscat]

I am having a problem with a DB2 stored Procedure written in COBOL. This sp takes several input parameters of different types:

Linkage Section.
01  PARMDEC1         PIC S9(5) COMP-3.
01  PARMCHAR1        PIC X(3).
01  PARMDEC2         PIC S9(12) COMP-3.
01  PARMCHAR2        PIC X(6)

IN PROCEDURE DIVISION:

THIS SELECT WORKS:
SELECT * FROM TABLE A
WHERE A.VALDEC1  = :PARMDEC1
AND   A.VALCHAR1 = :PARMCHAR1
AND   A.VALCHAR2 = :PARMCHAR2

THIS SELECT DOES NOT WORK:
SELECT * FROM TABLE A
WHERE A.VALDEC1  = :PARMDEC1
AND   A.VALCHAR1 = :PARMCHAR1
AND   A.VALDEC2  = :PARMDEC2
AND   A.VALCHAR2 = :PARMCHAR2

In short, it works with the first decimal value and all the char values, but if I add any additional decimal values, it will not work

Thanks for your help!
Cardscat

[sakumar9]

I am having a problem with a DB2 stored Procedure written in COBOL. This sp takes several input parameters of different types:

Linkage Section.
01 PARMDEC1 PIC S9(5) COMP-3.
01 PARMCHAR1 PIC X(3).
01 PARMDEC2 PIC S9(12) COMP-3.
01 PARMCHAR2 PIC X(6)

IN PROCEDURE DIVISION:

THIS SELECT WORKS:
SELECT * FROM TABLE A
WHERE A.VALDEC1 = :PARMDEC1
AND A.VALCHAR1 = :PARMCHAR1
AND A.VALCHAR2 = :PARMCHAR2

THIS SELECT DOES NOT WORK:
SELECT * FROM TABLE A
WHERE A.VALDEC1 = :PARMDEC1
AND A.VALCHAR1 = :PARMCHAR1
AND A.VALDEC2 = :PARMDEC2
AND A.VALCHAR2 = :PARMCHAR2

In short, it works with the first decimal value and all the char values, but if I add any additional decimal values, it will not work

Thanks for your help!
Cardscat

1. Can you provide some information on the error that you are getting. Without the error code, it will be difficult to answer this question.
2. The problem could be incompatibility of datatypes (second decimal).
3. You can refer to some stored procedure samples at: sqllib/samples/cobol. The name of the sample is spcreate.db2, inpsrv.sqb

Regards
-- Sanjay

[docdiesel]

Hi,

I reformatted your post a little bit and added code tags for better readability. And I agree with Sanjay, a bit more information would be useful. Especially the DB2 error message containing return and sql code could be helpful. And, what are the field definitions for the varchars and decimals in your table?

Regards,

Bernd

[cardscat]

Thank you for your responses.

The database shows the following properties for the decimal values:
valdec1 Decimal, Precision-5, Scale-0
valdec2 Decimal, Precision-12, Scale-0

The Create Procedure statement is:
create procedure cardscat(
parmdec1           DEC IN,
parmchar1          Char(6) IN,
parmdec2           DEC IN,
parmchar2          CHAR(2) IN)
LANGUAGE COBOL
DETERMINISTIC
READS SQL DATA
EXTERNAL NAME 'CARDSCAT'
COLLID COLLNAME
ASUTIME NO LIMIT
PARAMETER STYLE GENERAL
STAY RESIDENT YES
WLM ENVIRONMENT WLMAREA()
PROGRAM TYPE MAIN
RESULT SETS 1
COMMIT ON RETURN NO;

The procedure call (in Command Editor) is:
call cardscat(1,'CHARAC',2,'XX')

When the procedure does not work there are no records returned with this message:
'CARDSCAT'   RETURN_STATUS: 0

When the select is run seperately in Command Editor it returns the correct number of records.

Thanks again for your help.

cardscat

[cardscat]

I have discovered the error of my ways. I am used to SQLServer and the like where numeric values are a set size (int, smallint, long,etc.). My Create Procedure statment should have looked like this:

create procedure cardscat(
parmdec1           DEC(5) IN,
parmchar1          Char(6) IN,
parmdec2           DEC(12) IN,
parmchar2          CHAR(2) IN)

Just declaring DEC didn't get it.

Thanks for your responses.

cardscat

[docdiesel]

Hi,

this leads to the conclusion that just declaring DEC lets DB2 default to DEC(5). Otherwise the first statement shouldn't have worked.

Thank you for sharing your solution with us.

Regards,

Bernd