DB2V7 Z/OS... Running SQL under a IBM Utility?

Ray Gaston

DB2V7 Z/OS... Running SQL under a IBM Utility?

Dear Esteem Listers... Is there an IBM Utility which will allow us to
run SQL UPDATE statements under it?

We need to UPDATE Production data but want to guaranteed that no user
can update the same target tables during our updates (in case we need to
fallback to the UNLOADS taken just before the UPDATES). This is the
only available "window" scenario. DSNTEP2 probably would not work
because it is not itself a utility but only a "sample program".
Temporarily revoking/granting "privileges could get clumsy and
complicated. Can someone please help?

Thanks in advance - Ray Gaston
(Con Ed - DBA)

The strategy:

1) PLACE TABLESPACES IN UT R/O Mode
2) IBM UNLOADS (for fallback if needed)
3) SQL UPDATES (via the mystery IBM Utility)
4) IF needed RELOADS.

The SQL:

UPDATE TORU.TABLE1_EMPLOYEE A
SET CDE_EMPLOYEE = (SELECT CDE_EMPLOYEE_NEW
FROM TORU.TABLE2_EMPL_CONVERT B
WHERE (B.CDE_EMPLOYEE_NO= A.CDE_EMPLOYEE ) )
WHERE EXISTS ( SELECT 1
FROM TORU.TABLE2_EMPL_CONVERT B2
WHERE B2.CDE_EMPLOYEE_NO = A.CDE_EMPLOYEE)





The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Andy Lankester

Re: DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Ray Gaston)
Batch SPUFI?

Andy

_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Gaston, Raymond
Sent: Monday, January 28, 2008 4:53 PM
To: [login to unmask email]
Subject: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?




Dear Esteem Listers… Is there an IBM Utility which will allow us to run SQL UPDATE statements under it?

We need to UPDATE Production data but want to guaranteed that no user can update the same target tables during our updates (in case we need to fallback to the UNLOADS taken just before the UPDATES). This is the only available "window" scenario. DSNTEP2 probably would not work because it is not itself a utility but only a "sample program". Temporarily revoking/granting "privileges could get clumsy and complicated. Can someone please help?

Thanks in advance - Ray Gaston
(Con Ed - DBA)

The strategy:

1) PLACE TABLESPACES IN UT R/O Mode
2) IBM UNLOADS (for fallback if needed)
3) SQL UPDATES (via the mystery IBM Utility)
4) IF needed RELOADS.

The SQL:

UPDATE TORU.TABLE1_EMPLOYEE A
SET CDE_EMPLOYEE = (SELECT CDE_EMPLOYEE_NEW
FROM TORU.TABLE2_EMPL_CONVERT B
WHERE (B.CDE_EMPLOYEE_NO= A.CDE_EMPLOYEE ) )
WHERE EXISTS ( SELECT 1
FROM TORU.TABLE2_EMPL_CONVERT B2
WHERE B2.CDE_EMPLOYEE_NO = A.CDE_EMPLOYEE)





The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at HYPERLINK "http://www.idug.org/lsidug"www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest HYPERLINK "http://www.idug.org/lsconf"IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on HYPERLINK "http://www.idug.org/lsms"Member Services


No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date: 27/01/2008 18:39



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date: 27/01/2008 18:39


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Andy Lankester)
You'll struggle to maintain integrity between the unloads and the
update, but do make sure you LOCK THE TABLE EXCLUSIVELY to keep people
out when you DO run the update (and it'll help the update fly through)

Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.




________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Gaston, Raymond
Sent: 28 January 2008 16:53
To: [login to unmask email]
Subject: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?




Dear Esteem Listers... Is there an IBM Utility which will allow us to
run SQL UPDATE statements under it?

We need to UPDATE Production data but want to guaranteed that no user
can update the same target tables during our updates (in case we need to
fallback to the UNLOADS taken just before the UPDATES). This is the
only available "window" scenario. DSNTEP2 probably would not work
because it is not itself a utility but only a "sample program".
Temporarily revoking/granting "privileges could get clumsy and
complicated. Can someone please help?

Thanks in advance - Ray Gaston
(Con Ed - DBA)

The strategy:

1) PLACE TABLESPACES IN UT R/O Mode
2) IBM UNLOADS (for fallback if needed)
3) SQL UPDATES (via the mystery IBM Utility)
4) IF needed RELOADS.

The SQL:

UPDATE TORU.TABLE1_EMPLOYEE A
SET CDE_EMPLOYEE = (SELECT CDE_EMPLOYEE_NEW
FROM TORU.TABLE2_EMPL_CONVERT B
WHERE (B.CDE_EMPLOYEE_NO= A.CDE_EMPLOYEE ) )
WHERE EXISTS ( SELECT 1
FROM TORU.TABLE2_EMPL_CONVERT B2
WHERE B2.CDE_EMPLOYEE_NO = A.CDE_EMPLOYEE)





The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Guido Verbraak

Re: DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Ray Gaston)
Raymond,

For combining utilities with SQL, you can use DSNUPROC and use the utility
EXEC SQL to submit your SQL/update statements. After that/ in between you
can use the other utilities like copy, runstats etc. And it is possible to
use the lock statement too, like Phil wrote.

Vriendelijke groet / Best regards
Guido Verbraak

IBM Global Services
Integrated Technology Delivery, Service Management
Location: Groenelaan 2, 1186AA Amstelveen, PAC: AL0220
Phone: +31 (0)20 513 8461
Mobile : +31 (0)6 21 11 20 24
Email: [login to unmask email]

IBM Certified Database Administrator -- DB2 Universal Database V8.1 for
zOS
IBM Certified Solution Designer -- DB2 Business Intelligence V8





"Gaston, Raymond" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
28-01-2008 17:53
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
[DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?







Dear Esteem Listers? Is there an IBM Utility which will allow us to run
SQL UPDATE statements under it?
We need to UPDATE Production data but want to guaranteed that no user can
update the same target tables during our updates (in case we need to
fallback to the UNLOADS taken just before the UPDATES). This is the only
available "window" scenario. DSNTEP2 probably would not work because it is
not itself a utility but only a "sample program". Temporarily
revoking/granting "privileges could get clumsy and complicated. Can
someone please help?
Thanks in advance - Ray Gaston
(Con Ed - DBA)
The strategy:
1) PLACE TABLESPACES IN UT R/O Mode
2) IBM UNLOADS (for fallback if needed)
3) SQL UPDATES (via the mystery IBM Utility)
4) IF needed RELOADS.
The SQL:
UPDATE TORU.TABLE1_EMPLOYEE A
SET CDE_EMPLOYEE = (SELECT CDE_EMPLOYEE_NEW
FROM TORU.TABLE2_EMPL_CONVERT B
WHERE (B.CDE_EMPLOYEE_NO= A.CDE_EMPLOYEE ) )
WHERE EXISTS ( SELECT 1
FROM TORU.TABLE2_EMPL_CONVERT B2
WHERE B2.CDE_EMPLOYEE_NO = A.CDE_EMPLOYEE)




The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org under
the Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services


Tenzij hierboven anders aangegeven: / Unless stated otherwise above:
IBM Nederland B.V.
Gevestigd te Amsterdam
Inschrijving Handelsregister Amsterdam Nr. 33054214

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Ray Gaston

Re: DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Phil Grainger)
Thanks, Andy! I'll test to see if Batch Spufi will run when tablespaces
are placed in UT mode.

Ray

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Andy Lankester
Sent: Monday, January 28, 2008 12:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?


Batch SPUFI?

Andy

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Gaston, Raymond
Sent: Monday, January 28, 2008 4:53 PM
To: [login to unmask email]
Subject: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?




Dear Esteem Listers... Is there an IBM Utility which will allow us to
run SQL UPDATE statements under it?

We need to UPDATE Production data but want to guaranteed that no user
can update the same target tables during our updates (in case we need to
fallback to the UNLOADS taken just before the UPDATES). This is the
only available "window" scenario. DSNTEP2 probably would not work
because it is not itself a utility but only a "sample program".
Temporarily revoking/granting "privileges could get clumsy and
complicated. Can someone please help?

Thanks in advance - Ray Gaston
(Con Ed - DBA)

The strategy:

1) PLACE TABLESPACES IN UT R/O Mode
2) IBM UNLOADS (for fallback if needed)
3) SQL UPDATES (via the mystery IBM Utility)
4) IF needed RELOADS.

The SQL:

UPDATE TORU.TABLE1_EMPLOYEE A
SET CDE_EMPLOYEE = (SELECT CDE_EMPLOYEE_NEW
FROM TORU.TABLE2_EMPL_CONVERT B
WHERE (B.CDE_EMPLOYEE_NO= A.CDE_EMPLOYEE ) )
WHERE EXISTS ( SELECT 1
FROM TORU.TABLE2_EMPL_CONVERT B2
WHERE B2.CDE_EMPLOYEE_NO = A.CDE_EMPLOYEE)





The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >


No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date:
27/01/2008 18:39



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date:
27/01/2008 18:39



The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Richard Fazio

Re: DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Guido Verbraak)
Oddly enough, DSNTIAUL (the "unload" sample program) will allow updates
too.



So, you could, LOCK the table in exclusive mode, Unload, and commit
(Potentially MASSIVE UOW). Reload would be manual.



I don't believe you will be able to do ANY function in UT mode short of
a purely logical "update".



START TABLESPACE ACCESS(RO)

UNLOAD with any utility (take your pick)

START TABLESPACE ACCESS(UT)

Run a program to read the unload file, creating a new "ready to load"
file and re-load the table entirely.

START TABLESPACE ACCESS(RW)

Unless you are re-loading a major percentage of the table, this may not
work well.



Best of luck,

faz



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Gaston, Raymond
Sent: Monday, January 28, 2008 10:53 AM
To: [login to unmask email]
Subject: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?





Dear Esteem Listers... Is there an IBM Utility which will allow us to
run SQL UPDATE statements under it?

We need to UPDATE Production data but want to guaranteed that no user
can update the same target tables during our updates (in case we need to
fallback to the UNLOADS taken just before the UPDATES). This is the
only available "window" scenario. DSNTEP2 probably would not work
because it is not itself a utility but only a "sample program".
Temporarily revoking/granting "privileges could get clumsy and
complicated. Can someone please help?

Thanks in advance - Ray Gaston
(Con Ed - DBA)

The strategy:

1) PLACE TABLESPACES IN UT R/O Mode
2) IBM UNLOADS (for fallback if needed)
3) SQL UPDATES (via the mystery IBM Utility)
4) IF needed RELOADS.

The SQL:

UPDATE TORU.TABLE1_EMPLOYEE A
SET CDE_EMPLOYEE = (SELECT CDE_EMPLOYEE_NEW
FROM TORU.TABLE2_EMPL_CONVERT B
WHERE (B.CDE_EMPLOYEE_NO= A.CDE_EMPLOYEE ) )
WHERE EXISTS ( SELECT 1
FROM TORU.TABLE2_EMPL_CONVERT B2
WHERE B2.CDE_EMPLOYEE_NO = A.CDE_EMPLOYEE)






The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Ray Gaston

Re: DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Richard Fazio)
Thanks, Phil for the suggestions! I'll test this out also.

- Ray

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Grainger, Phil
Sent: Monday, January 28, 2008 12:11 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?


You'll struggle to maintain integrity between the unloads and the
update, but do make sure you LOCK THE TABLE EXCLUSIVELY to keep people
out when you DO run the update (and it'll help the update fly through)

Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.




________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Gaston, Raymond
Sent: 28 January 2008 16:53
To: [login to unmask email]
Subject: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?




Dear Esteem Listers... Is there an IBM Utility which will allow us to
run SQL UPDATE statements under it?

We need to UPDATE Production data but want to guaranteed that no user
can update the same target tables during our updates (in case we need to
fallback to the UNLOADS taken just before the UPDATES). This is the
only available "window" scenario. DSNTEP2 probably would not work
because it is not itself a utility but only a "sample program".
Temporarily revoking/granting "privileges could get clumsy and
complicated. Can someone please help?

Thanks in advance - Ray Gaston
(Con Ed - DBA)

The strategy:

1) PLACE TABLESPACES IN UT R/O Mode
2) IBM UNLOADS (for fallback if needed)
3) SQL UPDATES (via the mystery IBM Utility)
4) IF needed RELOADS.

The SQL:

UPDATE TORU.TABLE1_EMPLOYEE A
SET CDE_EMPLOYEE = (SELECT CDE_EMPLOYEE_NEW
FROM TORU.TABLE2_EMPL_CONVERT B
WHERE (B.CDE_EMPLOYEE_NO= A.CDE_EMPLOYEE ) )
WHERE EXISTS ( SELECT 1
FROM TORU.TABLE2_EMPL_CONVERT B2
WHERE B2.CDE_EMPLOYEE_NO = A.CDE_EMPLOYEE)





The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

William Gannon

Re: DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Ray Gaston)
Furthering on Andy's suggestion - do not use the Start Utility Only
option, but instead use SPUFFI and the SQL statement LOCK TABLE,

The format is as follows : LOCK TABLE .tablename IN EXCLUSIVE MODE;



This will ensure your update statement is the only update process
accessing the table and has the side benefit of allowing Read Only
transactions that specify UR.







William B. Gannon
- IBM Certified Solutions Expert
- IBM Certified Database Administrator
DB2 Universal Database V8.1 for zOS


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Gaston, Raymond
Sent: Monday, January 28, 2008 12:22 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?



Thanks, Andy! I'll test to see if Batch Spufi will run when tablespaces
are placed in UT mode.



Ray



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Andy Lankester
Sent: Monday, January 28, 2008 12:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?

Batch SPUFI?



Andy



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Gaston, Raymond
Sent: Monday, January 28, 2008 4:53 PM
To: [login to unmask email]
Subject: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?



Dear Esteem Listers... Is there an IBM Utility which will allow us to
run SQL UPDATE statements under it?

We need to UPDATE Production data but want to guaranteed that no user
can update the same target tables during our updates (in case we need to
fallback to the UNLOADS taken just before the UPDATES). This is the
only available "window" scenario. DSNTEP2 probably would not work
because it is not itself a utility but only a "sample program".
Temporarily revoking/granting "privileges could get clumsy and
complicated. Can someone please help?

Thanks in advance - Ray Gaston
(Con Ed - DBA)

The strategy:

1) PLACE TABLESPACES IN UT R/O Mode
2) IBM UNLOADS (for fallback if needed)
3) SQL UPDATES (via the mystery IBM Utility)
4) IF needed RELOADS.

The SQL:

UPDATE TORU.TABLE1_EMPLOYEE A
SET CDE_EMPLOYEE = (SELECT CDE_EMPLOYEE_NEW
FROM TORU.TABLE2_EMPL_CONVERT B
WHERE (B.CDE_EMPLOYEE_NO= A.CDE_EMPLOYEE ) )
WHERE EXISTS ( SELECT 1
FROM TORU.TABLE2_EMPL_CONVERT B2
WHERE B2.CDE_EMPLOYEE_NO = A.CDE_EMPLOYEE)





The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date:
27/01/2008 18:39



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date:
27/01/2008 18:39


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >


-----------------------------------------
Under Florida law, e-mail addresses are public records. If you do
not want your e-mail address released in response to a public
records request, do not send electronic mail to this entity.
Instead, contact this office by phone or in writing.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Andy Lankester

Re: DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to William Gannon)
Ray,

No, Phil has it right. There is not a UTILITY that will do this, and I'm pretty sure that SPUFI will not run UPDATEs if the space is in UTxx mode.

Easy enough to build a rexx to issue the appropriate LOCK statements and read in the UPDATES, execute them, and COMMIT. However beware the UOW size and LOCK ESCALATION. Be careful about the plan you run the rexx under - it needs to be bound RELEASE DEALLOCATE if you issue intermediate COMMITs.

Andy

_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Gaston, Raymond
Sent: Monday, January 28, 2008 5:22 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?


Thanks, Andy! I'll test to see if Batch Spufi will run when tablespaces are placed in UT mode.

Ray

_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Andy Lankester
Sent: Monday, January 28, 2008 12:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?


Batch SPUFI?

Andy

_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Gaston, Raymond
Sent: Monday, January 28, 2008 4:53 PM
To: [login to unmask email]
Subject: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?




Dear Esteem Listers… Is there an IBM Utility which will allow us to run SQL UPDATE statements under it?

We need to UPDATE Production data but want to guaranteed that no user can update the same target tables during our updates (in case we need to fallback to the UNLOADS taken just before the UPDATES). This is the only available "window" scenario. DSNTEP2 probably would not work because it is not itself a utility but only a "sample program". Temporarily revoking/granting "privileges could get clumsy and complicated. Can someone please help?

Thanks in advance - Ray Gaston
(Con Ed - DBA)

The strategy:

1) PLACE TABLESPACES IN UT R/O Mode
2) IBM UNLOADS (for fallback if needed)
3) SQL UPDATES (via the mystery IBM Utility)
4) IF needed RELOADS.

The SQL:

UPDATE TORU.TABLE1_EMPLOYEE A
SET CDE_EMPLOYEE = (SELECT CDE_EMPLOYEE_NEW
FROM TORU.TABLE2_EMPL_CONVERT B
WHERE (B.CDE_EMPLOYEE_NO= A.CDE_EMPLOYEE ) )
WHERE EXISTS ( SELECT 1
FROM TORU.TABLE2_EMPL_CONVERT B2
WHERE B2.CDE_EMPLOYEE_NO = A.CDE_EMPLOYEE)





The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at HYPERLINK "http://www.idug.org/lsidug"www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest HYPERLINK "http://www.idug.org/lsconf"IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on HYPERLINK "http://www.idug.org/lsms"Member Services


No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date: 27/01/2008 18:39



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date: 27/01/2008 18:39



The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at HYPERLINK "http://www.idug.org/lsidug"www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest HYPERLINK "http://www.idug.org/lsconf"IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on HYPERLINK "http://www.idug.org/lsms"Member Services
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at HYPERLINK "http://www.idug.org/lsidug"www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest HYPERLINK "http://www.idug.org/lsconf"IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on HYPERLINK "http://www.idug.org/lsms"Member Services


No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date: 27/01/2008 18:39



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date: 27/01/2008 18:39


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Ray Gaston

Re: DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Andy Lankester)
Great stuff, Guido. Thanks!

- Ray

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Guido Verbraak
Sent: Monday, January 28, 2008 12:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?



Raymond,

For combining utilities with SQL, you can use DSNUPROC and use the
utility EXEC SQL to submit your SQL/update statements. After that/ in
between you can use the other utilities like copy, runstats etc. And it
is possible to use the lock statement too, like Phil wrote.

Vriendelijke groet / Best regards
Guido Verbraak

IBM Global Services
Integrated Technology Delivery, Service Management
Location: Groenelaan 2, 1186AA Amstelveen, PAC: AL0220
Phone: +31 (0)20 513 8461
Mobile : +31 (0)6 21 11 20 24
Email: [login to unmask email]

IBM Certified Database Administrator -- DB2 Universal Database V8.1 for
zOS
IBM Certified Solution Designer -- DB2 Business Intelligence V8





"Gaston, Raymond" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>

28-01-2008 17:53
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc
Subject
[DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?







Dear Esteem Listers... Is there an IBM Utility which will allow us to
run SQL UPDATE statements under it?

We need to UPDATE Production data but want to guaranteed that no user
can update the same target tables during our updates (in case we need to
fallback to the UNLOADS taken just before the UPDATES). This is the
only available "window" scenario. DSNTEP2 probably would not work
because it is not itself a utility but only a "sample program".
Temporarily revoking/granting "privileges could get clumsy and
complicated. Can someone please help?

Thanks in advance - Ray Gaston
(Con Ed - DBA)

The strategy:

1) PLACE TABLESPACES IN UT R/O Mode
2) IBM UNLOADS (for fallback if needed)
3) SQL UPDATES (via the mystery IBM Utility)
4) IF needed RELOADS.

The SQL:

UPDATE TORU.TABLE1_EMPLOYEE A
SET CDE_EMPLOYEE = (SELECT CDE_EMPLOYEE_NEW
FROM TORU.TABLE2_EMPL_CONVERT B
WHERE (B.CDE_EMPLOYEE_NO= A.CDE_EMPLOYEE ) )
WHERE EXISTS ( SELECT 1
FROM TORU.TABLE2_EMPL_CONVERT B2
WHERE B2.CDE_EMPLOYEE_NO = A.CDE_EMPLOYEE)





The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >


Tenzij hierboven anders aangegeven: / Unless stated otherwise above:
IBM Nederland B.V.
Gevestigd te Amsterdam
Inschrijving Handelsregister Amsterdam Nr. 33054214

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Philip Sevetson

DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Ray Gaston)
Ray, are you familiar with the SQL Statement "LOCK TABLE"? I think this is what you're looking for.

The reference is here: http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnsqh16/5.72?DT=20061207122755


--Phil Sevetson, NYCAPS DBA Support
Financial Information Services Agency of The City of New York
450 West 33rd Street, 4th Floor
New York, NY 10001
phone: (212) 857-1688
mailto: [login to unmask email]
________________________________________
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Gaston, Raymond
Sent: Monday, January 28, 2008 11:53 AM
To: [login to unmask email]
Subject: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?


Dear Esteem Listers... Is there an IBM Utility which will allow us to run SQL UPDATE statements under it?
We need to UPDATE Production data but want to guaranteed that no user can update the same target tables during our updates (in case we need to fallback to the UNLOADS taken just before the UPDATES). This is the only available "window" scenario. DSNTEP2 probably would not work because it is not itself a utility but only a "sample program". Temporarily revoking/granting "privileges could get clumsy and complicated. Can someone please help?
Thanks in advance - Ray Gaston
(Con Ed - DBA)
The strategy:
1) PLACE TABLESPACES IN UT R/O Mode
2) IBM UNLOADS (for fallback if needed)
3) SQL UPDATES (via the mystery IBM Utility)
4) IF needed RELOADS.
The SQL:
UPDATE TORU.TABLE1_EMPLOYEE A SET CDE_EMPLOYEE = (SELECT CDE_EMPLOYEE_NEW FROM TORU.TABLE2_EMPL_CONVERT B WHERE (B.CDE_EMPLOYEE_NO= A.CDE_EMPLOYEE ) ) WHERE EXISTS ( SELECT 1 FROM TORU.TABLE2_EMPL_CONVERT B2 WHERE B2.CDE_EMPLOYEE_NO = A.CDE_EMPLOYEE)



=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Andy Lankester

Re: DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Philip Sevetson)
Wow! When did this appear in Z/OS?

Andy

_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Guido Verbraak
Sent: Monday, January 28, 2008 5:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?



Raymond,

For combining utilities with SQL, you can use DSNUPROC and use the utility EXEC SQL to submit your SQL/update statements. After that/ in between you can use the other utilities like copy, runstats etc. And it is possible to use the lock statement too, like Phil wrote.

Vriendelijke groet / Best regards
Guido Verbraak

IBM Global Services
Integrated Technology Delivery, Service Management
Location: Groenelaan 2, 1186AA Amstelveen, PAC: AL0220
Phone: +31 (0)20 513 8461
Mobile : +31 (0)6 21 11 20 24
Email: [login to unmask email]

IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
IBM Certified Solution Designer -- DB2 Business Intelligence V8





"Gaston, Raymond" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>


28-01-2008 17:53


Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>



To
[login to unmask email]

cc

Subject
[DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?







Dear Esteem Listers… Is there an IBM Utility which will allow us to run SQL UPDATE statements under it?


We need to UPDATE Production data but want to guaranteed that no user can update the same target tables during our updates (in case we need to fallback to the UNLOADS taken just before the UPDATES). This is the only available "window" scenario. DSNTEP2 probably would not work because it is not itself a utility but only a "sample program". Temporarily revoking/granting "privileges could get clumsy and complicated. Can someone please help?


Thanks in advance - Ray Gaston
(Con Ed - DBA)


The strategy:


1) PLACE TABLESPACES IN UT R/O Mode
2) IBM UNLOADS (for fallback if needed)
3) SQL UPDATES (via the mystery IBM Utility)
4) IF needed RELOADS.


The SQL:


UPDATE TORU.TABLE1_EMPLOYEE A
SET CDE_EMPLOYEE = (SELECT CDE_EMPLOYEE_NEW
FROM TORU.TABLE2_EMPL_CONVERT B
WHERE (B.CDE_EMPLOYEE_NO= A.CDE_EMPLOYEE ) )
WHERE EXISTS ( SELECT 1
FROM TORU.TABLE2_EMPL_CONVERT B2
WHERE B2.CDE_EMPLOYEE_NO = A.CDE_EMPLOYEE)






The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at HYPERLINK "http://www.idug.org/lsidug"www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest HYPERLINK "http://www.idug.org/lsconf"IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on HYPERLINK "http://www.idug.org/lsms"Member Services


Tenzij hierboven anders aangegeven: / Unless stated otherwise above:
IBM Nederland B.V.
Gevestigd te Amsterdam
Inschrijving Handelsregister Amsterdam Nr. 33054214

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at HYPERLINK "http://www.idug.org/lsidug"www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest HYPERLINK "http://www.idug.org/lsconf"IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on HYPERLINK "http://www.idug.org/lsms"Member Services
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date: 27/01/2008 18:39



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date: 27/01/2008 18:39


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Ray Gaston

Re: DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Andy Lankester)
Thanks from the response, Richard.

- ray

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Fazio, Richard
Sent: Monday, January 28, 2008 12:32 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?



Oddly enough, DSNTIAUL (the "unload" sample program) will allow updates
too.



So, you could, LOCK the table in exclusive mode, Unload, and commit
(Potentially MASSIVE UOW). Reload would be manual.



I don't believe you will be able to do ANY function in UT mode short of
a purely logical "update".



START TABLESPACE ACCESS(RO)

UNLOAD with any utility (take your pick)

START TABLESPACE ACCESS(UT)

Run a program to read the unload file, creating a new "ready to load"
file and re-load the table entirely.

START TABLESPACE ACCESS(RW)

Unless you are re-loading a major percentage of the table, this may not
work well.



Best of luck,

faz



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Gaston, Raymond
Sent: Monday, January 28, 2008 10:53 AM
To: [login to unmask email]
Subject: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?





Dear Esteem Listers... Is there an IBM Utility which will allow us to
run SQL UPDATE statements under it?

We need to UPDATE Production data but want to guaranteed that no user
can update the same target tables during our updates (in case we need to
fallback to the UNLOADS taken just before the UPDATES). This is the
only available "window" scenario. DSNTEP2 probably would not work
because it is not itself a utility but only a "sample program".
Temporarily revoking/granting "privileges could get clumsy and
complicated. Can someone please help?

Thanks in advance - Ray Gaston
(Con Ed - DBA)

The strategy:

1) PLACE TABLESPACES IN UT R/O Mode
2) IBM UNLOADS (for fallback if needed)
3) SQL UPDATES (via the mystery IBM Utility)
4) IF needed RELOADS.

The SQL:

UPDATE TORU.TABLE1_EMPLOYEE A
SET CDE_EMPLOYEE = (SELECT CDE_EMPLOYEE_NEW
FROM TORU.TABLE2_EMPL_CONVERT B
WHERE (B.CDE_EMPLOYEE_NO= A.CDE_EMPLOYEE ) )
WHERE EXISTS ( SELECT 1
FROM TORU.TABLE2_EMPL_CONVERT B2
WHERE B2.CDE_EMPLOYEE_NO = A.CDE_EMPLOYEE)






The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Paul Ogborne

DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Ray Gaston)
(Apologies if you receive this twice!)

I was thinking about the UOW here too.? If there is a large volume of rows then the backout (in the case where your UPDATE has an error) could take quite a while.? If you have partitioning then you can 'stream' the process and you can LOCK TABLE at partition level too which may make the outage even shorter.

Rgds,
Paul.

________________________________________________________________________
AOL's new homepage has launched. Take a tour at http://info.aol.co.uk/homepage/ now.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Paul Ogborne)
either v5 or v6, I forget

Phil G
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Andy Lankester
Sent: Mon 28/01/2008 17:40
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?


Wow! When did this appear in Z/OS?

Andy

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Guido Verbraak
Sent: Monday, January 28, 2008 5:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?



Raymond,

For combining utilities with SQL, you can use DSNUPROC and use the utility EXEC SQL to submit your SQL/update statements. After that/ in between you can use the other utilities like copy, runstats etc. And it is possible to use the lock statement too, like Phil wrote.

Vriendelijke groet / Best regards
Guido Verbraak

IBM Global Services
Integrated Technology Delivery, Service Management
Location: Groenelaan 2, 1186AA Amstelveen, PAC: AL0220
Phone: +31 (0)20 513 8461
Mobile : +31 (0)6 21 11 20 24
Email: [login to unmask email]

IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
IBM Certified Solution Designer -- DB2 Business Intelligence V8





"Gaston, Raymond" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>

28-01-2008 17:53
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc
Subject
[DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?







Dear Esteem Listers... Is there an IBM Utility which will allow us to run SQL UPDATE statements under it?

We need to UPDATE Production data but want to guaranteed that no user can update the same target tables during our updates (in case we need to fallback to the UNLOADS taken just before the UPDATES). This is the only available "window" scenario. DSNTEP2 probably would not work because it is not itself a utility but only a "sample program". Temporarily revoking/granting "privileges could get clumsy and complicated. Can someone please help?

Thanks in advance - Ray Gaston
(Con Ed - DBA)

The strategy:

1) PLACE TABLESPACES IN UT R/O Mode
2) IBM UNLOADS (for fallback if needed)
3) SQL UPDATES (via the mystery IBM Utility)
4) IF needed RELOADS.

The SQL:

UPDATE TORU.TABLE1_EMPLOYEE A
SET CDE_EMPLOYEE = (SELECT CDE_EMPLOYEE_NEW
FROM TORU.TABLE2_EMPL_CONVERT B
WHERE (B.CDE_EMPLOYEE_NO= A.CDE_EMPLOYEE ) )
WHERE EXISTS ( SELECT 1
FROM TORU.TABLE2_EMPL_CONVERT B2
WHERE B2.CDE_EMPLOYEE_NO = A.CDE_EMPLOYEE)





The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information < http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >


Tenzij hierboven anders aangegeven: / Unless stated otherwise above:
IBM Nederland B.V.
Gevestigd te Amsterdam
Inschrijving Handelsregister Amsterdam Nr. 33054214

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information < http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date: 27/01/2008 18:39



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date: 27/01/2008 18:39



The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information < http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Isaac Yassin

Re: DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Phil Grainger)
Hi,



EXEC SQL as utility is from V7



Isaac Yassin

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Grainger, Phil
Sent: Monday, January 28, 2008 8:57 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?



either v5 or v6, I forget



Phil G

CA



_____

From: DB2 Data Base Discussion List on behalf of Andy Lankester
Sent: Mon 28/01/2008 17:40
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?

Wow! When did this appear in Z/OS?



Andy



_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Guido Verbraak
Sent: Monday, January 28, 2008 5:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?


Raymond,

For combining utilities with SQL, you can use DSNUPROC and use the utility EXEC SQL to submit your SQL/update statements. After
that/ in between you can use the other utilities like copy, runstats etc. And it is possible to use the lock statement too, like
Phil wrote.

Vriendelijke groet / Best regards
Guido Verbraak

IBM Global Services
Integrated Technology Delivery, Service Management
Location: Groenelaan 2, 1186AA Amstelveen, PAC: AL0220
Phone: +31 (0)20 513 8461
Mobile : +31 (0)6 21 11 20 24
Email: [login to unmask email]

IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
IBM Certified Solution Designer -- DB2 Business Intelligence V8



_____

I am using the free version of SPAMfighter for private users.
It has removed 1961 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter < http://www.spamfighter.com/len > for free now!


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Ray Gaston

Re: DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Isaac Yassin)
Listers...

Thanks for all the instant feedback. Much appreciated. That's what makes
this DB2 Forum so GREAT ! What a tool !!!

- Ray Gaston

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Isaac Yassin
Sent: Monday, January 28, 2008 2:26 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?



Hi,



EXEC SQL as utility is from V7



Isaac Yassin

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Grainger, Phil
Sent: Monday, January 28, 2008 8:57 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?



either v5 or v6, I forget



Phil G

CA



________________________________

From: DB2 Data Base Discussion List on behalf of Andy Lankester
Sent: Mon 28/01/2008 17:40
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?

Wow! When did this appear in Z/OS?



Andy



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Guido Verbraak
Sent: Monday, January 28, 2008 5:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?


Raymond,

For combining utilities with SQL, you can use DSNUPROC and use the
utility EXEC SQL to submit your SQL/update statements. After that/ in
between you can use the other utilities like copy, runstats etc. And it
is possible to use the lock statement too, like Phil wrote.

Vriendelijke groet / Best regards
Guido Verbraak

IBM Global Services
Integrated Technology Delivery, Service Management
Location: Groenelaan 2, 1186AA Amstelveen, PAC: AL0220
Phone: +31 (0)20 513 8461
Mobile : +31 (0)6 21 11 20 24
Email: [login to unmask email]

IBM Certified Database Administrator -- DB2 Universal Database V8.1 for
zOS
IBM Certified Solution Designer -- DB2 Business Intelligence V8



________________________________

I am using the free version of SPAMfighter for private users.
It has removed 1961 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter < http://www.spamfighter.com/len > for free now!


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Luis M Martinez Ch

Re: DB2V7 Z/OS... Running SQL under a IBM Utility?
(in response to Ray Gaston)
* Why not using DSNTIAD?
* Problems with concurrent access/updates? .... use LOCK TABLE if you can .. you mentioned the option Read Only.
* You can play with the Isolation level also ( UR for others and/or RR for you)

"Grainger, Phil" <[login to unmask email]> escribió:
either v5 or v6, I forget

Phil G
CA



---------------------------------
From: DB2 Data Base Discussion List on behalf of Andy Lankester
Sent: Mon 28/01/2008 17:40
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?


Wow! When did this appear in Z/OS?

Andy


---------------------------------
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Guido Verbraak
Sent: Monday, January 28, 2008 5:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?




Raymond,

For combining utilities with SQL, you can use DSNUPROC and use the utility EXEC SQL to submit your SQL/update statements. After that/ in between you can use the other utilities like copy, runstats etc. And it is possible to use the lock statement too, like Phil wrote.

Vriendelijke groet / Best regards
Guido Verbraak

IBM Global Services
Integrated Technology Delivery, Service Management
Location: Groenelaan 2, 1186AA Amstelveen, PAC: AL0220
Phone: +31 (0)20 513 8461
Mobile : +31 (0)6 21 11 20 24
Email: [login to unmask email]

IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
IBM Certified Solution Designer -- DB2 Business Intelligence V8




"Gaston, Raymond" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]> 28-01-2008 17:53 Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email] cc
Subject
[DB2-L] DB2V7 Z/OS... Running SQL under a IBM Utility?




Dear Esteem Listers… Is there an IBM Utility which will allow us to run SQL UPDATE statements under it? We need to UPDATE Production data but want to guaranteed that no user can update the same target tables during our updates (in case we need to fallback to the UNLOADS taken just before the UPDATES). This is the only available "window" scenario. DSNTEP2 probably would not work because it is not itself a utility but only a "sample program". Temporarily revoking/granting "privileges could get clumsy and complicated. Can someone please help? Thanks in advance - Ray Gaston
(Con Ed - DBA) The strategy: 1) PLACE TABLESPACES IN UT R/O Mode
2) IBM UNLOADS (for fallback if needed)
3) SQL UPDATES (via the mystery IBM Utility)
4) IF needed RELOADS. The SQL: UPDATE TORU.TABLE1_EMPLOYEE A
SET CDE_EMPLOYEE = (SELECT CDE_EMPLOYEE_NEW
FROM TORU.TABLE2_EMPL_CONVERT B
WHERE (B.CDE_EMPLOYEE_NO= A.CDE_EMPLOYEE ) )
WHERE EXISTS ( SELECT 1
FROM TORU.TABLE2_EMPL_CONVERT B2
WHERE B2.CDE_EMPLOYEE_NO = A.CDE_EMPLOYEE)



The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services


Tenzij hierboven anders aangegeven: / Unless stated otherwise above:
IBM Nederland B.V.
Gevestigd te Amsterdam
Inschrijving Handelsregister Amsterdam Nr. 33054214

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date: 27/01/2008 18:39


No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.13/1246 - Release Date: 27/01/2008 18:39


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services


Luis Miguel Martinez Chavez
IT Specialist
DB2 ZOS/LUW
Solaris/Linux/AIX

---------------------------------

¡Capacidad ilimitada de almacenamiento en tu correo!
No te preocupes más por el espacio de tu cuenta con Correo Yahoo!:
http://correo.yahoo.com.mx/

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms