|
View previous topic :: View next topic
|
| Author |
Message |
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
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 |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| To add on my previous post, I have BMC Load/Unload tools in my place.. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
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 |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| 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 |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
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 |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
| 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 |
|
|
|
|
