View previous topic :: View next topic
|
Author |
Message |
sm_2009
New User
Joined: 10 May 2009 Posts: 72 Location: bangalore
|
|
|
|
Hi All,
I am getting an erro code of -144 for the below query.
EXEC SQL
SELECT POLICY_NBR
INTO :DCLPLCY-POLICY-NBR
FROM POLICY
WHERE :DCLPLCY-PROPOSAL-NBR = PROPOSAL-NBR-IN
END-EXEC
here i get PROPOSAL-NBR-IN from input file. I need to check if there is any policy number alreday present in POLICY table where proposal number in table equals proposal number in input file.
please help in debugging. if this requirement can be handled with more correct query plz post.
reason for -144 is INVALID SECTION NUMBER number |
|
Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8796 Location: Welsh Wales
|
|
|
|
Do you not have access to the correct messages and codes reference manual so that you can see what the error is for yourself ? |
|
Back to top |
|
|
sm_2009
New User
Joined: 10 May 2009 Posts: 72 Location: bangalore
|
|
|
|
Hii
i changed the query as
MOVE PROPOSAL-NBR-IN TO DCLPLCY-PROPOSAL-NBR
EXEC SQL
SELECT POLICY_NBR
INTO :DCLPLCY-POLICY-NBR
FROM POLICY
WHERE PROPOSAL_NBR = :DCLPLCY-PROPOSAL-NBR
END-EXEC
still its giving the same -144 error.
pls help |
|
Back to top |
|
|
sm_2009
New User
Joined: 10 May 2009 Posts: 72 Location: bangalore
|
|
|
|
Hi expat
thanks for your post.
actually i am new to coding. i am coding this cobol-db2 program for the first time.
thats why i find this forum as a right place to share. |
|
Back to top |
|
|
Succor
New User
Joined: 20 Feb 2009 Posts: 96 Location: Bangalore :)
|
|
|
|
sm_2009, were there any warnings present at the time of bind/pre compilation. Did you ask your peers tht if they had previously faced such an issue.
WTH |
|
Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
What happens if you run the query in spufi? |
|
Back to top |
|
|
sm_2009
New User
Joined: 10 May 2009 Posts: 72 Location: bangalore
|
|
|
|
Hi Succor,
the bind return code is zero. i have tried to rebind also and there were no warning messages; rebind was successful. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Which release of db2 are you using? On which operating system? |
|
Back to top |
|
|
sm_2009
New User
Joined: 10 May 2009 Posts: 72 Location: bangalore
|
|
|
|
hi Dick
i ran the query in SPUFI, and it returned correct value.
for proposal number 145, policy number = 1, in POLICY table.
please note here that both Policy-nbr and proposal-nbr are the pimary keys in POLICY table.
---------+---------+---------+---------+---------+---------+---------+--
--SELECT (DAYS(CURRENT_DATE) - DAYS(CURRENT_DATE + 1 DAYS)) FROM
--SYSIBM.SYSDUMMY1
--GRANT ALL ON G30665.PRODUCT TO PUBLIC;
--GRANT ALL ON G30665.RATE TO PUBLIC;
--GRANT ALL ON G30665.MEMBER TO PUBLIC;
--GRANT ALL ON G30665.POLICY TO PUBLIC;
--GRANT EXECUTE ON PLAN G0665PG1 TO PUBLIC;
--GRANT EXECUTE ON PLAN G0665PG2 TO PUBLIC;
SELECT POLICY_NBR FROM POLICY
WHERE PROPOSAL_NBR = 145;
---------+---------+---------+---------+---------+---------+---------+--
POLICY_NBR
---------+---------+---------+---------+---------+---------+---------+--
1
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+---------+--
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+--
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
please have a look and let me know if i am missing any thing here.
thanks |
|
Back to top |
|
|
sm_2009
New User
Joined: 10 May 2009 Posts: 72 Location: bangalore
|
|
|
|
hey dick,
i have used sql query in one more program previously. it was working fine. Still do u think there is a possibility tht this query is not supported by the db2 version i am using.
thanks |
|
Back to top |
|
|
sm_2009
New User
Joined: 10 May 2009 Posts: 72 Location: bangalore
|
|
|
|
The rebind parameters as shown in background for REBIND of the plan is -
DSNT252I -DSN1 DSNTBRB REBIND OPTIONS FOR PLAN G0665PG2
ACTION
OWNER G30665
VALIDATE RUN
ISOLATION RR
ACQUIRE USE
RELEASE COMMIT
EXPLAIN NO
DYNAMICRULES RUN
DSNT253I -DSN1 DSNTBRB REBIND OPTIONS FOR PLAN G0665PG2
NODEFER PREPARE
CACHESIZE 1024
QUALIFIER G30665
CURRENTSERVER
CURRENTDATA NO
DEGREE 1
SQLRULES DB2
DISCONNECT EXPLICIT
NOREOPT VARS
KEEPDYNAMIC NO
IMMEDWRITE NO
DBPROTOCOL DRDA
OPTHINT
ENCODING EBCDIC(00037)
PATH
DSNT200I -DSN1 REBIND FOR PLAN G0665PG2 SUCCESSFUL
***
Is there any thing i need to change here? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Was the program that worked fine on the same db2 system? If you have multiple db2 environments, which ones work and which ones fail?
If you have one that works and one that does not, suggest you determine the difference(s) in the two. . .
Does the program that fails use the exact same compile/bind jcl as the onte that works? |
|
Back to top |
|
|
sm_2009
New User
Joined: 10 May 2009 Posts: 72 Location: bangalore
|
|
|
|
All are in same DB2 environment.
yes,both use same compile/bind option available in DB2 panel. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Is there a particular reason rebind is used (rather than bind)? What happens if bind is used? |
|
Back to top |
|
|
sm_2009
New User
Joined: 10 May 2009 Posts: 72 Location: bangalore
|
|
|
|
No Bind for this is alreday done, so i am using Rebind option.
Do you mean to drop the first one and start with BIND again. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
Do you mean to drop the first one and start with BIND again. |
No, just specify BIND.
I looked at our db2 compile and nothing uses a rebind. . . What we use is a bind with replace. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
My terminology sucks, so someone who knows, please translate my post:
My userid is ABCD. In Spufi all my FROM tables must be FROM qualifier.table, unless I am accessing a table that I created.
so in spufi,
Code: |
SELECT POLICY_NBR FROM POLICY |
is accessing a table, POLICY, that I created or ABCD.POLICY.
To access that in a pgm I would have to either
qualify my SQL
Code: |
SELECT POLICY_NBR FROM ABCD.POLICY |
or invoke the program with a PLAN which specifically mentions ABCD as the qualifier. |
|
Back to top |
|
|
|