Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Ways to perform a databse update task in DB2

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

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Mon Aug 16, 2010 1:09 am    Post subject: Ways to perform a databse update task in DB2
Reply with quote

Can someone advise which is the widely used reliable way for databse update( of DB2 tables in mainframes) using SQL queries. And this has to be done in a production environment & it has to be a one time run..

Few of the options which I could think are :
1. write a cobol program with embedded SQl to do the task.
2. Run the query in SPUFI with user who has access rights to update production tables.

I am new to DB2 so wondering if there can be a better way. And I don't want to write a fresh COBOL program for just a adhoc one time update.

Is there something like a I can do it in a job and run it in batch as the number of rows to be updated is huge.

Please advise.
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 Aug 16, 2010 1:51 am    Post subject:
Reply with quote

Hello,

This is no different than some job that will run monthly. . .

Also consider that many "one-time" processes get used again for the next crisis. . .

Anything to be done in production should go thru the normal turnover process. Backups should be taken before the updates are applied. There needs to be auditability for the changes made. These should not be "slipped in under the covers".

Quote:
And I don't want to write a fresh COBOL program for just a adhoc one time update.
This should be the very least of the concerns. . .

FWIW - i have been places where making unpublished ad-hoc changes to Production data was grounds for termination. While this may seem completely acceptable to you, responsible management will not permit it.

Quote:
Is there something like a I can do it in a job and run it in batch as the number of rows to be updated is huge.
Yes, this should be run in batch.


Depending on the amount of data and the change(s) needed, it may be better to unload the table, make the updates in batch (using COBOL, Easytrieve, or your sort product), and reloading the table.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Mon Aug 16, 2010 2:08 am    Post subject:
Reply with quote

Dick, undoubtedly the process would be followed which is done for general database updates..The same process will be folowed..Backups in the sense we can unload the table before update and take a backup after update..I am worried about the technique I can adopt for such updates.

The unloading option is a good idea. But if we unload a prod table and someone updates or makes any entry into the table before I run my update cum load job. The entries may be lost right..

Do we have any such techniques where we can run the SQl update query in JCl itself and with appropriate Isolation level so that data consistency is maintained...
I don't know how guys do this in other mainframe shops.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Mon Aug 16, 2010 2:31 am    Post subject:
Reply with quote

To add on my previous post, I have BMC Load/Unload tools in my place..
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 Aug 16, 2010 2:58 am    Post subject:
Reply with quote

Hello,

I believe you need to coordinate with your DBAs as to how requirements like this are done on your system.

Quote:
Do we have any such techniques where we can run the SQl update query in JCl itself and with appropriate Isolation level so that data consistency is maintained...

It is quite common for some or all of the database to be made unavailable to "the users" while this type of work is going on. The ways this is done vary from place to place and is site-specific. Your DBAs will know what needs to be done on your system.

If the data is unloaded and the changes made to the sequential data, there will be no need for a "query in JCL itself". Once the changes have been applied to the sequential data, the data would be reloaded.
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Mon Aug 16, 2010 7:06 pm    Post subject:
Reply with quote

One way to to do it is to write SQL for a SPUFI update, place the SQL in a ds that can be accessed by the production batch user, or at least a production planner or whoever has the authority to schedule production jobs.

You then issue a formal request to run a production job as a batch SPUFI run with your SQL as input. Your installation may already have such a utility job jcl ready.

If you supply select statements before and after your update statements, it is a good way of documenting the changes done.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Mon Aug 16, 2010 10:33 pm    Post subject:
Reply with quote

Kjeld wrote:
One way to to do it is to write SQL for a SPUFI update, place the SQL in a ds that can be accessed by the production batch user, or at least a production planner or whoever has the authority to schedule production jobs.

You then issue a formal request to run a production job as a batch SPUFI run with your SQL as input. Your installation may already have such a utility job jcl ready.

If you supply select statements before and after your update statements, it is a good way of documenting the changes done.


How is the batch SPUFI run different from the conventional SPUFI? What I know about SPUFI is the interactive one (which I go through the ISPF menu- DB2 option) which gives the results once we run the query which is placed in the SPUFI input dataset.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Mon Aug 16, 2010 10:46 pm    Post subject:
Reply with quote

I have another question like, is it necessary to specify the isolation level in the query which I place in the SPUFI input dataset?

Is it advisable to use queries with isolation level specified, which shall be appropriate for updates? I have seen many queries use the isloation level as UR..

eg. SELECT 1 FROM T1332.TK.INS1
WHERE TK_DB_SEQ='A' WITH UR;
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Tue Aug 17, 2010 1:30 pm    Post subject:
Reply with quote

Rijit wrote:
Kjeld wrote:
One way to to do it is to write SQL for a SPUFI update, place the SQL in a ds that can be accessed by the production batch user, or at least a production planner or whoever has the authority to schedule production jobs.

You then issue a formal request to run a production job as a batch SPUFI run with your SQL as input. Your installation may already have such a utility job jcl ready.

If you supply select statements before and after your update statements, it is a good way of documenting the changes done.


How is the batch SPUFI run different from the conventional SPUFI? What I know about SPUFI is the interactive one (which I go through the ISPF menu- DB2 option) which gives the results once we run the query which is placed in the SPUFI input dataset.

Batch SPUFI execution are not different from interactive SPUFI execution, except for you can have a batch job execute with production user autority, making update operations to production data. I assume you will not have those access right on your personal userid, and you will not be able to login with a production userid to issue interactive SQl.
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 Task initiating SSHD vasanthz All Other Mainframe Topics 1 Tue Nov 14, 2017 3:03 am
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts How do I create a mainframe monitorin... anjali.bisht All Other Mainframe Topics 4 Fri Sep 01, 2017 2:20 pm
No new posts IDMS DC027007 T58 TASK:ADS2 PROG:ADSO... gpowell382 IDMS/ADSO 2 Fri Jun 30, 2017 11:52 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us