Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to Solve this BIND ERROR?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Wed Apr 16, 2008 11:43 am    Post subject: How to Solve this BIND ERROR?
Reply with quote

I am getting BIND error while updating table thru cursor,i have declared cursor like below,

EXEC SQL DECLARE CNTL_TABLE_CSR2 CURSOR WITH HOLD FOR
SELECT CNTL_TBL_NME,
CNTL_DTE,
CNTL_FLAG
FROM ARW_CNTL_TABLE
WHERE CNTL_FLAG = 'E' OR
CNTL_FLAG = 'H' AND CNTL_DTE = (SELECT MIN(CNTL_DTE) FROM
ARW_CNTL_TABLE WHERE CNTL_FLAG = 'H')
FOR UPDATE OF CNTL_FLAG,CNTL_DTE
END-EXEC.

Please let me know what is wrong in the above cursor declaration?
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Apr 16, 2008 11:49 am    Post subject:
Reply with quote

Hello,

When the bind error occurred, there should be some diagnostic info presented. Please post that info.

There may be a problem with using a subquery in an update cursor. If you search in the db2 sectino of the forum, you may find more of what you are looking for.
Back to top
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Wed Apr 16, 2008 11:54 am    Post subject:
Reply with quote

Babu,

What's the SQLCODE you are getting? Are u getting the error at bind time or while you try running the program? Please provide more details.

Did u try running the query outside, in a QMF or SPUFI?

Thanks,
Prajesh
Back to top
View user's profile Send private message
sri_mf

Active User


Joined: 31 Aug 2006
Posts: 216
Location: India

PostPosted: Wed Apr 16, 2008 11:58 am    Post subject: Re: How to Solve this BIND ERROR?
Reply with quote

babu_hi wrote:
I am getting BIND error while updating table thru cursor,i have declared cursor like below,


SELECT CNTL_TBL_NME,
CNTL_DTE,
CNTL_FLAG
FROM ARW_CNTL_TABLE
WHERE CNTL_FLAG = 'E' OR
CNTL_FLAG = 'H' AND CNTL_DTE = (SELECT MIN(CNTL_DTE) FROM
ARW_CNTL_TABLE WHERE CNTL_FLAG = 'H')


Please let me know what is wrong in the above cursor declaration?


First check ur query in SPUFI/QMF
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Wed Apr 16, 2008 12:09 pm    Post subject:
Reply with quote

Checking the sql in qmf or spufi will not help. since you cannot declare a cursor in spufi/qmf. The select, even with the subselect will/could function.

besides, any error by spufi/qmf for the select clause may or may not be germaine to the cursor error found by the bind.

babu_hi,

what is the bind error?
Code:

DSNX200I  -DQF2 BIND SQL ERROR
           USING LFLGD1 AUTHORITY
           PLAN=(NOT APPLICABLE)
           DBRM=RSR003
           STATEMENT=85
           SQLCODE=-204
           SQLSTATE=42704
           TOKENS=LFLGD1.PRT_SAVE
           CSECT NAME=DSNXOTL
           RDS CODE=-500


the above is an example of a bind failure. The SQLCODE would be helpful inorder to provide you an answer
Back to top
View user's profile Send private message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Wed Apr 16, 2008 12:26 pm    Post subject:
Reply with quote

i am getting DSNT233I error at the time of bind the program.
Back to top
View user's profile Send private message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Wed Apr 16, 2008 12:30 pm    Post subject:
Reply with quote

complete informatiuon on BIND ERROR is

DSNX200I - BIND SQL ERROR
USING ARW01D AUTHORITY
PLAN=(NOT APPLICABLE)
DBRM=ARW250
STATEMENT=116
SQLCODE=-118
SQLSTATE=42902
TOKENS=
CSECT NAME=DSNXOGP
RDS CODE=-700
DSNT233I - UNSUCCESSFUL BIND FOR

please let me know what i need to change?
Back to top
View user's profile Send private message
sri_mf

Active User


Joined: 31 Aug 2006
Posts: 216
Location: India

PostPosted: Wed Apr 16, 2008 12:39 pm    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
Checking the sql in qmf or spufi will not help. since you cannot declare a cursor in spufi/qmf. The select, even with the subselect will/could function.



Hi dick i have asked OP to check his query(just the exact query)
Code:

SELECT CNTL_TBL_NME,
CNTL_DTE,
CNTL_FLAG
FROM ARW_CNTL_TABLE
WHERE CNTL_FLAG = 'E' OR
CNTL_FLAG = 'H' AND CNTL_DTE = (SELECT MIN(CNTL_DTE) FROM
ARW_CNTL_TABLE WHERE CNTL_FLAG = 'H')


So that if there are any issues in the query he can fix it there itself and then go for bind..
Usually in our shop we used to test all the queries that we use in the program.Thats why i suggested that.Corrections are welcome.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Wed Apr 16, 2008 12:48 pm    Post subject:
Reply with quote

Quote:

THE OBJECT TABLE OR VIEW OF THE DELETE OR UPDATE STATEMENT IS ALSO IDENTIFIED IN A FROM CLAUSE Explanation: The table or view specified as the object of a DELETE or UPDATE statement also appears in the FROM clause of a subselect within the statement. The table or view that is the object of a UPDATE or DELETE cannot also be used to supply the values to be inserted or to qualify the rows to be updated or deleted.

System Action: The statement cannot be executed. No data was updated or deleted.

Programmer Response: The implied function is not supported by DB2. It may be possible to obtain the desired result by creating a temporary copy of the object table or view and addressing the subselect to that copy. Refer to Chapter 5 of DB2 SQL Reference for information about the syntax of SQL statements.


As Dick said, the subselect is a problem.
Code:

FOR UPDATE OF CNTL_FLAG,CNTL_DTE


you are going to update CNTL_FLAG and CNTL_DTE which are both specified in the subselect.

I really don't think your subselect is what you want. I would guess that you want the row that contains the MIN(CNTL_DTE) for a CNTL_TBL_NME that has a CNTL_FLAG = 'H'. What your subselect is returning is the MIN(CNTL_DTE) from all rows containing a CNTL_FLAG = 'H', , without regard to CNTL_TBL_NME.

Not only does the sytanx for a cursor disallow a subselect on columns that are part of an UPDATE clause, your subselect is faulty.

If you would tell us why you want to update the DTE and FLAG columns or what you are trying to do, we maybe able to provide suggestions.

You can not order a CURSOR containing a FOR UPDATE CLAUSE. You may want to change your CURSOR to a FETCH ONLY after you fix your subselect or add an ORDER BY CLAUSE and then do SINGLETON UPDATEs.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DB2 Program abending without giving a... rahulgarg14 DB2 7 Mon May 29, 2017 8:10 pm
No new posts Getting error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm
No new posts IDC2902I Error tzeche ABENDS & Debugging 2 Tue Mar 28, 2017 11:39 am
No new posts Need help with below DB2 error ashek15 DB2 3 Sat Mar 18, 2017 5:56 am
No new posts FINDREP Syntax Error seahawk789 DFSORT/ICETOOL 4 Thu Mar 09, 2017 10:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us