DB2 questions

Carl Nelson

DB2 questions
Gosh! Someone actually is going to use this listserve for more than
announcements.

DB2 v8 z/OS

1) I know the number of partitions increased to 4096 but I thought the
size of each partition also increased from 4gb to 64gb. That would be
4069 partitions * 64gb = 260,416 gb
Is this correct or have I confused some numbers?


2) How do your shops enforce SQL standards? Other than code
walk-thrus, what options are available? Does someone have a REXX script
looking for specific things?



Carl Nelson
317-287-7222
Advisor Information Architecture
WellPoint
220 Virginia Ave
Indianapolis IN 46204
[login to unmask email]

"CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,
is for the sole use of the intended recipient(s) and may contain
confidential and privileged information or otherwise be protected by
law. Any unauthorized review, use, disclosure or distribution is
prohibited. If you are not the intended recipient, please contact the
sender by reply e-mail and destroy all copies of the original message."



CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,
is for the sole use of the intended recipient(s) and may contain confidential
and privileged information or otherwise protected by law. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not the
intended recipient, please contact the sender by reply e-mail and destroy
all copies of the original message.

---------------------------------------------------------------------------------
Welcome to the DB2 User Group - Indiana list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/DB2-UG-Indiana.html. From that page select "Join or Leave the list". The IDUG Listserv FAQ is at http://www.idugdb2-l.org. The DB2 User Group - Indiana List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Matthew A Rhea

Re: DB2 questions
(in response to Carl Nelson)
I haven't seen any responses, so I'll jump in.

Maximum number of partitions in a partitioned table space or partitioned index
64 for table spaces that are not defined with LARGE or a DSSIZE greater
than 2 GB.
4096, depending on what is specified for DDSIZE or LARGE and the page size.

Maximum size of a partition (table space or index)
For table spaces that are not defined with LARGE or a DSSIZE greater than 2 GB:

* 4 GB, for 1 to 16 partitions
* 2 GB, for 17 to 32 partitions
* 1 GB, for 33 to 64 partitions

For table spaces that are defined with LARGE:

* 4 GB, for 1 to 4096 partitions

For table spaces that are defined with a DSSIZE greater than 2 GB:

* 64 GB, depending on the page size (for 1 to 256 partitions for 4-KB
pages, for 1 to 512 partitions for 8-KB pages, for 1 to 1024 partitions for
16-KB pages, and 1 to 2048 partitions for 32-KB pages)

I got this information from the Information Center for z/OS at this web
address:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.dzic.doc/dzicviewlet.htm

Regarding enforcing SQL Standards:

I've worked with processes that forced an explain when a compile/bind was
done. The process would compare the new explain to the old explain, and
if there any differences, it would flag the program for a walkthru, and had
to be approved by a DBA before the program could be migrated. This was
usually done when migrating from development to QA. The process used SQL to
look for the differences, looking for access paths that matched, table
names, indexes, etc. You could make it as involved as you needed. The idea
is that once you review a program with its SQL, you don't need to review it
again unless something has changed. There are a couple of downsides.
Someone could add another SQL statement that generates the same explain info
as another SQL statement in the same program. In that case, you may not be
notified that "SQL changed", because, really, SQL didn't change, just more
SQL was added. You could avoid that by coding the QUERYNO = option on the
SQL. Another downside is that you may be notified for changes that are
really desirable, such as changing from a tablescan to an index, or from
indexonly = n to indexonly = y. You may want to wave those through without
having to manually review. It can get pretty fancy. Hope that helps. I
can give you more detail if you'd like. Just contact me off-list.
Matthew

On Thu, 30 Aug 2007 12:35:04 -0400, Nelson, Carl <[login to unmask email]> wrote:

>Gosh! Someone actually is going to use this listserve for more than
>announcements.
>
>DB2 v8 z/OS
>
>1) I know the number of partitions increased to 4096 but I thought the
>size of each partition also increased from 4gb to 64gb. That would be
>4069 partitions * 64gb = 260,416 gb
>Is this correct or have I confused some numbers?
>
>
>2) How do your shops enforce SQL standards? Other than code
>walk-thrus, what options are available? Does someone have a REXX script
>looking for specific things?
>
>
>
>Carl Nelson
>317-287-7222
>Advisor Information Architecture
>WellPoint
>220 Virginia Ave
>Indianapolis IN 46204
>[login to unmask email]
>

---------------------------------------------------------------------------------
Welcome to the DB2 User Group - Indiana list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/DB2-UG-Indiana.html. From that page select "Join or Leave the list". The IDUG Listserv FAQ is at http://www.idugdb2-l.org. The DB2 User Group - Indiana List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm