IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Can we run UPDATE through batch job.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
prasenjit_dey

New User


Joined: 30 Apr 2008
Posts: 20
Location: Noida

PostPosted: Fri Jan 02, 2009 9:33 pm
Reply with quote

I have a query to update a table, the query looks like:

Code:
UPDATE AAA.xxx_xxxxCVGPR_AGG_T  T1                               
  SET xxx_sub_CLM_NR =                                           
    ( SELECT xxx_SUB_CLM_NR                                     
       FROM AAA.xxx_CLM_COVERAGE_T  T2                           
             WHERE                                               
                         T2.xxx_PRD_END_DT = T1.xxx_PRD_END_DT   
                    AND  T2.xxx_COVG_CD    = T1.xxx_COVG_CD     
                    AND  T2.xxx_CLM_FIL_NR = T1.xxx_CLM_FIL_NR )
 WHERE EXISTS                                                   
   ( SELECT 1  FROM AAA.xxx_CLM_COVERAGE_T  T3                   
        WHERE                                                   
                         T3.xxx_PRD_END_DT = T1.xxx_PRD_END_DT   
                    AND  T3.xxx_COVG_CD    = T1.xxx_COVG_CD     
                    AND  T3.xxx_CLM_FIL_NR = T1.xxx_CLM_FIL_NR );


How can we run it in batch mode. Tried it running also thru SPUFI but it gives an -811 error message.

Does anyone have any idea.

Thanks.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Sat Jan 03, 2009 12:15 am
Reply with quote

If it won't work in spufi what makes you think it will work in batch? Have looked up the meaning of -811 sql code?
-811
THE RESULT OF AN EMBEDDED SELECT STATEMENT IS A TABLE OF MORE THAN
ONE ROW, OR THE RESULT OF THE SUBQUERY OF A BASIC PREDICATE IS MORE
THAN ONE VALUE
Back to top
View user's profile Send private message
prasenjit_dey

New User


Joined: 30 Apr 2008
Posts: 20
Location: Noida

PostPosted: Mon Jan 05, 2009 9:41 am
Reply with quote

Craq i understand, but my query was if we cannot run it through SPUFI than what are the other alternatives!

Thanks.
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India

PostPosted: Mon Jan 05, 2009 9:55 am
Reply with quote

Hi,
Quote:
if we cannot run it through SPUFI than what are the other alternatives!

What is the problem/error while running in SPUFI? As you said, if you got -811, you would get the same everywhere you run the query.

Please take a look at the query and -811 reason and change it accordingly.

BTW, you can use QMF, some third-party tools, SPUFI in batch or an application program to run a query...
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Mon Jan 05, 2009 10:13 am
Reply with quote

Hello,

Quote:
what are the other alternatives!
Until you correct the problem, you have no alternatives. . . It is not an issue/error with spufi.

Nothing will run the query as long as the -811 is raised. This includes all of the possible ways to run sql queries (i.e. those mentioned by Gnanas).

If a "simple" sql query will not work due to the -811, you may need to imbed different sql in a program and code around the situation.
Back to top
View user's profile Send private message
prasenjit_dey

New User


Joined: 30 Apr 2008
Posts: 20
Location: Noida

PostPosted: Wed Jan 07, 2009 3:06 pm
Reply with quote

Cursor is an option for this problem, but writing an cursor program will be a hectic task because again we will have to raise a request and all other formalities.
Cant we add something to the query so that it selects only one row from the result set and update the table, for all the matching records.
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India

PostPosted: Wed Jan 07, 2009 3:23 pm
Reply with quote

Quote:
... but writing an cursor program will be a hectic task because again we will have to raise a request and all other formalities.

It shoud be done if that is your requirement. You should do all the formalities for your task. That's not hectic. icon_smile.gif

It seems you are doing the job for your convenient, not for the requirement!. icon_sad.gif

Anyway, will this work?
Code:
FETCH FIRST ROW ONLY
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Jan 07, 2009 9:33 pm
Reply with quote

Hello,

If your select returns more than one row and you choose to process only the "first" row returned, might there be some other problem later? Is there any chance that some data integrity/inconsistency problem could be introduced?

Suggest you do the proper bit of work now so that you (or worse, someone else) does not have to deal with some other problem later.
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Thu Jan 08, 2009 1:18 am
Reply with quote

[quote="Gnanas N"]
Quote:


Anyway, will this work?
Code:
FETCH FIRST ROW ONLY


But this is a problem with the Subquery.
Back to top
View user's profile Send private message
prasenjit_dey

New User


Joined: 30 Apr 2008
Posts: 20
Location: Noida

PostPosted: Thu Jan 08, 2009 7:44 pm
Reply with quote

The subquery returns multiple rows.
the filter conditions:
T2.xxx_PRD_END_DT = T1.xxx_PRD_END_DT
AND T2.xxx_COVG_CD = T1.xxx_COVG_CD
AND T2.xxx_CLM_FIL_NR = T1.xxx_CLM_FIL_NR
returns rows that differs on the COVG_CD or PRD_END_DT or the CLM_FIL_NR.

But is there any method to just take a row one by one out of the result set and update the table with corresponding values.
Back to top
View user's profile Send private message
prasenjit_dey

New User


Joined: 30 Apr 2008
Posts: 20
Location: Noida

PostPosted: Thu Jan 08, 2009 7:46 pm
Reply with quote

The problem is both the tables dont have any PK or any CK.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Thu Jan 08, 2009 8:10 pm
Reply with quote

Hello,

You will need to write "real" code and control the process in the code (embed appropriate sql in a program as mentioned days ago).

The "simple" query does not appear to be able to do what is needed.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Excuting store procedure via JCL batch JCL & VSAM 1
No new posts SORT on detail record, then repeat he... DFSORT/ICETOOL 3
No new posts batch SFTP job using AOPBATCH unable ... All Other Mainframe Topics 7
No new posts Call an hlasm from REXX in batch and ... CLIST & REXX 4
No new posts SPOOL to Mainframe dataset in batch mode JCL & VSAM 7
Search our Forums:

Back to Top