MVSFORUMS.com

NASCAR9 · The output: V-SSN = 0107057549 V-DATE = 2004-08-01 V-ANSWER = G

I created a simple SP and it does not seem to be doing the SQL Select in the SP. I know the SP is executing, I have moved values to the varibles and returned them. Can someone show what I'm doing wrong. The Key being passed exists in the table.

Thanks
NSACAR9

The SP Program:

kolusu · Posted: Mon Jun 06, 2005 10:29 am Post subject:

Nascar9,

Is CCSQLMSG-SQLCODE-NOTFND mean sqlcode of 100? How is Bal-med defined on the table? Is it a character field or Integer field?

I notice that you are missing a Couple Of END-IF in your pgm.

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu

NASCAR9 · Posted: Mon Jun 06, 2005 10:41 am Post subject:

kolusu, Thanks for the reply.

01 CCSQLMSG-SQLCODE PIC S9(05) VALUE ZEROS.
88 CCSQLMSG-SQLCODE-OK VALUE +00000
88 CCSQLMSG-SQLCODE-NOTFND VALUE +00100
88 CCSQLMSG-SQLCODE-END-CURS VALUE +00100
88 CCSQLMSG-SQLCODE-BAD-DATE VALUE -00181
88 CCSQLMSG-SQLCODE-NULL VALUE -00305
88 CCSQLMSG-SQLCODE-DUPKEY VALUE -00803
88 CCSQLMSG-SQLCODE-MULT-ROWS VALUE -00811
88 CCSQLMSG-SQLCODE-UNCONNECT VALUE -00900

BAL_MED CHAR(1) NOT NULL,
10 PREMSUM-BAL-MED PIC X(1).

I may have been lazy on my END-IF statements.
If you see any that will cause a problem, please point them out.
Thanks
NASCAR9

Bithead · Posted: Mon Jun 06, 2005 11:26 am Post subject:

Is there anything in the WLM address space for any output from "DISPLAY PGMNAME CCSQLMSG-ERR-MSG"?

NASCAR9 · Posted: Mon Jun 06, 2005 11:33 am Post subject:

The SQLCODE is Zeroes, I have checked with move in this IF:IF CCSQLMSG-SQLCODE-OK . The SP is executing but, the SQL never does the Select.

Bithead · Posted: Mon Jun 06, 2005 11:39 am Post subject:

Try this. Change

NASCAR9 · Posted: Mon Jun 06, 2005 12:02 pm Post subject:

Bithead, Here's what happened:
V-SSN = 0107057549
V-DATE = 2004-08-01
V-ANSWER = X
I assummed it was running OK, guess I was wrong.

Heres a query from SPUFI:
SELECT SSNO, PSTPERIOD, BAL_MED
FROM HOURS.PREMSUM
WHERE SSNO = 0107057549 AND
PSTPERIOD = '2004-08-01';
---------+---------+---------+---------+---------+---------+-
SSNO PSTPERIOD BAL_MED
---------+---------+---------+---------+---------+---------+-
107057549 2004-08-01 Y
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Bithead · Posted: Mon Jun 06, 2005 12:04 pm Post subject:

Please post the DCL for HOURS.PREMSUM.

NASCAR9 · Posted: Mon Jun 06, 2005 12:11 pm Post subject:

Bithead · Posted: Mon Jun 06, 2005 12:21 pm Post subject:

NASCAR9,

The call looks OK so there must be some other reason. Try adding a parameter to hold the SQLCODE in the SP after the SELECT call and pass it back to the calling program. Then display that as well.

kolusu · Posted: Mon Jun 06, 2005 12:46 pm Post subject:

Nascar,

You need to use the BBCODEs to enable the proper format of the code. put your code enclosed in between the bbcode and check it.

{code}
IDENTIFICATION DIVISION.
PROGRAM-ID. SAMPLE
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-CHAR PIC X(08).
{/code}

8 ) Translates to a smiley .

Now replace the { brackets with [ for {CODE} & {/CODE} .

Now this is how it looks like

NASCAR9 · Posted: Mon Jun 06, 2005 1:48 pm Post subject:

I'm getting a -804. How do I get the Reason Code? This will help in the problem determination.
Thanks
NASCAR9
_________________
Thanks,
NASCAR9

schintala · Posted: Mon Jun 06, 2005 2:42 pm Post subject:

Looks to me SP definition needs to be corrected.

CREATE PROCEDURE HOURS.GETPREMSUM
(IN SSN INTEGER,
IN PERIOD DATE,
OUT ANSWER CHAR(01))
LANGUAGE COBOL
DYNAMIC RESULT SETS 0
PARAMETER STYLE GENERAL
EXTERNAL NAME GETPREMSUM
COLLID FBELCOLL
MODIFIES SQL DATA
SECURITY DB2
WLM ENVIRONMENT WLMTENV1
STAY RESIDENT YES;

How WLM set up in your shop?

Bithead · Posted: Mon Jun 06, 2005 2:46 pm Post subject:

Nascar9,

Make sure that you have changed the pass area in the calling program and the stored procedure and you have added the parameter in the CREATE PROCEDURE step.

NASCAR9 · Posted: Mon Jun 06, 2005 2:53 pm Post subject:

schintala, I'm looking up the parm you suggested. I tried to take the default wherever possible.

Bithead, I think I have.
Cobol SP:*----------------------------------------------------
LINKAGE SECTION.
*----------------------------------------------------
01 V-SSN PIC S9(9) USAGE COMP
VALUE ZEROES.
01 V-DATE PIC X(10) VALUE SPACES.
01 V-ANSWER PIC X(01) VALUE SPACES.
01 V-SQLCD PIC S9(9) USAGE COMP
VALUE ZEROES.
*----------------------------------------------------
PROCEDURE DIVISION USING V-SSN
V-DATE
V-ANSWER
V-SQLCD.
*----------------------------------------------------

Callin pgm:
01 V-SSN PIC S9(9) USAGE COMP
VALUE 107057549.
01 V-DATE PIC X(10) VALUE '2004-08-01'
01 V-ANSWER PIC X(01) VALUE SPACES.
01 V-SQLCD PIC S9(9) USAGE COMP
VALUE ZEROES.

------------------------------------------------------
PROCEDURE DIVISION.
------------------------------------------------------

EXEC SQL
CALL HOURS.GETPREMSUM (:V-SSN,
:V-DATE,
:V-ANSWER,
:V-SQLCD)
END-EXEC.

SP:

CREATE PROCEDURE HOURS.GETPREMSUM
(INOUT SSN INTEGER,
INOUT PERIOD DATE,
INOUT ANSWER CHAR(01),
INOUT SQLCD INTEGER)
LANGUAGE COBOL
EXTERNAL NAME FSPS0001
COLLID FBELCOLL
SECURITY DB2
WLM ENVIRONMENT WLMTENV1;