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
 

 

Can we run UPDATE through batch job.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Can we run UPDATE through batch job.
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    Post subject:
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    Post subject: Reply to: Can we run UPDATE through batch job.
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: 788
Location: Chennai, India

PostPosted: Mon Jan 05, 2009 9:55 am    Post subject:
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

Site Director


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

PostPosted: Mon Jan 05, 2009 10:13 am    Post subject:
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    Post subject: Reply to: Can we run UPDATE through batch job.
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: 788
Location: Chennai, India

PostPosted: Wed Jan 07, 2009 3:23 pm    Post subject:
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

Site Director


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

PostPosted: Wed Jan 07, 2009 9:33 pm    Post subject:
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    Post subject:
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    Post subject: But this is a problem with the Subquery.
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    Post subject: Addition to the above...
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

Site Director


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

PostPosted: Thu Jan 08, 2009 8:10 pm    Post subject:
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    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 Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts HLIST Utility In Batch Virendra Shambharkar TSO/ISPF 4 Fri Apr 07, 2017 3:38 pm
No new posts Batch job tuning sgandhla Testing & Performance analysis 5 Fri Mar 24, 2017 9:41 pm
No new posts MIPS/CPU consumption reduction in Batch vishwakotin DFSORT/ICETOOL 4 Sat Mar 18, 2017 5:46 pm


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