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
 

 

Will update query return sqlcode zero without updating

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

New User


Joined: 29 Jan 2008
Posts: 14
Location: Chennai

PostPosted: Sat Feb 14, 2009 1:50 pm    Post subject: Will update query return sqlcode zero without updating
Reply with quote

Hi,

I have faced this issue in COBOL+DB2+CICS program. I am getting values from online and i need to update the table based on that variable. In one scenario, the same update query works, while for the other it doesn't. The table is not getting updated and i get sqlcode 0 too.

I checked the where clause even, the row is fetched properly. I checked if the table is being updated later in the program too. but it does not update later too.
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Sat Feb 14, 2009 4:05 pm    Post subject:
Reply with quote

Hi Nithya,

WELCOME!

With a little sample codings, people here might able to help you!

Thank You,
Sushanth
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: Sat Feb 14, 2009 10:26 pm    Post subject:
Reply with quote

Hello,

What you have described is most likely a coding error. Possibly somewhere a command is not being issued or a return code is not properly checked.

Is it possible the column(s) on the WHERE on the update has/have been changed to some other value and the update is actually happening to some different set of rows?

Does the program read via a cursor and then update the fetehed row?

As Sushanth mentions, seeing some of the code may help.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1226
Location: Israel

PostPosted: Sun Feb 15, 2009 7:13 pm    Post subject:
Reply with quote

Also, the transaction has to terminate successfully for the update to be committed.
I got mixed up when you say "update query". Updates and queries are 2 different things...

When you post some code, use the little Code button right above the message box. It's easy (click Code, paste, then click Code again) and fun (well, almost).
Back to top
View user's profile Send private message
nithyaroslin

New User


Joined: 29 Jan 2008
Posts: 14
Location: Chennai

PostPosted: Mon Feb 16, 2009 9:44 am    Post subject: Reply to: Will update query return sqlcode zero without upda
Reply with quote

Hi,

Sorry for not posting the sample code earlier and sorry for the confusion made 'update query'

Code:

EXEC SQL
UPDATE  MERCHANT
WHERE ID          = :XXXXXXXX-INPUT.ID 
  AND DEPT        = :XXXXXXXX-INPUT.DEPT
  AND CRTE_TMS        = :XXXXXXXX-INPUT.CRTE-TMS
END-EXEC.

EVALUATE SQLCODE                                         
   WHEN +0                                               
            PERFORM 3500-PROCESS-STAT-CHANGES                 
                    THRU 3500-PROCESS-STAT-CHANGES-EXIT             
   WHEN OTHER                                           
MOVE 004                TO RQBLK-MAJ-RET-CODE
MOVE WS-UPDATE          TO WS-ERROR-OPERATION
MOVE C-MRCH-USR-AUTH    TO WS-ERROR-TABLE1   
MOVE C-ID           TO WS-KEY-NAME1     
MOVE ID             OF XXXXXXXX-INPUT   
  TO                       WS-KEY-VALUE1     
MOVE C-DEPT         TO WS-KEY-NAME2     
MOVE DEPT           OF XXXXXXXX-INPUT   
  TO                       WS-KEY-VALUE2     
MOVE C-CRTE-TMS         TO WS-KEY-NAME3     
MOVE CRTE-TMS           OF XXXXXXXX-INPUT   
  TO                       WS-KEY-VALUE3     
PERFORM 8200-HANDLE-SQL-ERROR
END-EVALUATE.               


Regards,
Nithya[/code]
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Feb 16, 2009 10:34 am    Post subject:
Reply with quote

Hello Nithya,

Code:
UPDATE  MERCHANT
WHERE ID          = :XXXXXXXX-INPUT.ID 
  AND DEPT        = :XXXXXXXX-INPUT.DEPT
  AND CRTE_TMS        = :XXXXXXXX-INPUT.CRTE-TMS


There needs to be a SET in UPDATE statement, i think. Check it out.

Example :-
Code:
UPDATE DSN8910.EMP
SET PHONENO='3565'
WHERE EMPNO='000190';


Thank You
Sushanth Bobby
Back to top
View user's profile Send private message
nithyaroslin

New User


Joined: 29 Jan 2008
Posts: 14
Location: Chennai

PostPosted: Mon Feb 16, 2009 11:05 am    Post subject:
Reply with quote

sorry Sushanth... I somehow missed out the Set clause whilc copying. It is there in the program.

Code:

UPDATE  MERCHANT
SET AUTH_STAT_CDE = ‘Y’
WHERE ID          = :XXXXXXXX-INPUT.ID 
  AND DEPT        = :XXXXXXXX-INPUT.DEPT
  AND CRTE_TMS        = :XXXXXXXX-INPUT.CRTE-TMS
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Feb 16, 2009 11:23 am    Post subject:
Reply with quote

Nithya,

After executing the EXEC SQL, try displaying SQLERRD(3). Like

Code:
EXEC SQL
UPDATE  MERCHANT
SET AUTH_STAT_CDE = ‘Y’
WHERE ID          = :XXXXXXXX-INPUT.ID 
  AND DEPT        = :XXXXXXXX-INPUT.DEPT
  AND CRTE_TMS        = :XXXXXXXX-INPUT.CRTE-TMS
END-EXEC.
DISPLAY 'SQLERRD(3) =' SQLERRD(3).


SQLERRD(3) will let you know how many rows were updated.

Thank You,
Sushanth Bobby
Back to top
View user's profile Send private message
nithyaroslin

New User


Joined: 29 Jan 2008
Posts: 14
Location: Chennai

PostPosted: Mon Feb 16, 2009 12:24 pm    Post subject: Reply to: Will update query return sqlcode zero without upda
Reply with quote

Hi,

The number of rows updated is 1 when i dispalyed the SQLERRD(3)
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Feb 16, 2009 12:46 pm    Post subject:
Reply with quote

Nithya,

Your scenario is, you are updating a table and you are getting SQLCODE as 0. But the record is not getting updated right.

1) Try a select on the table
Code:
SELECT   * from MERCHANT
WHERE ID          = :XXXXXXXX-INPUT.ID 
  AND DEPT        = :XXXXXXXX-INPUT.DEPT
  AND CRTE_TMS        = :XXXXXXXX-INPUT.CRTE-TMS

2)run your program

3)check whether the update has been made
Code:
SELECT   * from MERCHANT
WHERE ID          = :XXXXXXXX-INPUT.ID 
  AND DEPT        = :XXXXXXXX-INPUT.DEPT
  AND CRTE_TMS        = :XXXXXXXX-INPUT.CRTE-TMS



Check, if there are any ROLLBACK statement in the following code.

Sushanth Bobby
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: Mon Feb 16, 2009 9:59 pm    Post subject:
Reply with quote

Hello,

If this is not yet resolved, suggest you place a COMMIT immediately after the update, end the program, and then run a query to ensure the update has happened. This would be for testing only.
Back to top
View user's profile Send private message
nithyaroslin

New User


Joined: 29 Jan 2008
Posts: 14
Location: Chennai

PostPosted: Tue Feb 17, 2009 10:03 am    Post subject: Reply to: Will update query return sqlcode zero without upda
Reply with quote

Hi Shusanth and Dick,

Thank you very much for the support. I really thought of ROLLBACK and then tried retrieving the row from the table immediately after update and found that the row has been updated. icon_smile.gif icon_smile.gif

This ROLLBACK is done in front end(Java and EJB).

Thank you!!!! icon_smile.gif

Regards,
Nithya
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: Tue Feb 17, 2009 10:32 am    Post subject: Reply to: Will update query return sqlcode zero without upda
Reply with quote

You're welcome - thank you for posting the explanation icon_smile.gif

d
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 Updating a VSAM file with DISP=OLD sheersh JCL & VSAM 8 Tue Mar 14, 2017 6:14 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts DB2 SQL Error: SQLCODE=-440, SQLSTATE... kishpra DB2 1 Sat Feb 18, 2017 2:31 am


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