DB2 for z/OS V8 - query tables with zero rows.

Jim McAlpine

DB2 for z/OS V8 - query tables with zero rows.
How can I query the name of all tables containing zero rows.

Jim McAlpine

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Satish Srikakulapu

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to Jim McAlpine)
Jim,

If it is possible, run the RUNSTATS on all the tables and run the below query.

SELECT NAME FROM SYSIBM.SYSTABLES WHERE CARDF = 0 AND TYPE = 'T'.

This should give you the result that you need.

Thanks,
Satish Srikakulapu

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Thursday, December 23, 2010 8:42 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.

How can I query the name of all tables containing zero rows.

Jim McAlpine

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >
--- NOTICE ---

This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Dave Nance

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to Satish Srikakulapu)
Run runstats. then query catalog.
 
David Nance




________________________________
From: Jim McAlpine <[login to unmask email]>
To: [login to unmask email]
Sent: Thu, December 23, 2010 10:41:58 AM
Subject: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.


How can I query the name of all tables containing zero rows.

Jim McAlpine
________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.





_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Phil Grainger

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to Dave Nance)
Or, if you are externalizing RTS (which everyone should be now) you can look in SYSTABLESPACESTATS for the table SPACES that are empty

Look at SYSTABLESPACESTATS.TOTALROWS

To find the TABLES in empty table spaces, join SYSTABLESPACESTATS to SYSTABLES on DBNAME and (TS)NAME
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Satish Srikakulapu
Sent: 23 December 2010 15:56
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.

Jim,

If it is possible, run the RUNSTATS on all the tables and run the below query.

SELECT NAME FROM SYSIBM.SYSTABLES WHERE CARDF = 0 AND TYPE = ‘T’.

This should give you the result that you need.

Thanks,
Satish Srikakulapu

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Thursday, December 23, 2010 8:42 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.

How can I query the name of all tables containing zero rows.

Jim McAlpine

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >
--- NOTICE ---

This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Lockwood Lyon

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to Phil Grainger)
Jim,

This is an interesting question! A lot depends on what you mean by "query" (a single SQL stmt? Multiple SQL statement? Non-SQL alternatives?), "table" (Including Aux tables for LOBs? MQTs? Catalog tables?), and "zero rows" (Tables CREATEd but no rows ever inserted/loaded? Tables which AT THIS INSTANT have no rows?).

I'd say that any SQL-based answer would have some issues. For one, querying the tables directly (with some kind of COUNT(*) and/or FETCH FIRST 1 ROWS ONLY logic) would cause locking for the duration of your query. Unless you use WITH UR ... in which case, you're not really accurately counting empty tables, are you? What if an INSERT to an empty table is executed during the duration of your query?

A RUNSTATS-based approach will give you answers ... but only as of the date/time you ran RunStats and with the CPU and I/O overhead of accessing *all* of your tables. I expect that even the quickest set of Stats jobs executed against all of your operational tables would run for ... what? Hours? Days? How will you prevent rows being added to any of these tables after Stats has run?

My guess is that only a programmatic solution has a chance of giving you a relatively correct answer within a relatively short period of time. Code a 3GL program to: (a) Select your *subset* of target table names (perhaps limit by DB.TS?) from SYSTABLES into, say, a GTT; (b) Loop thru the GTT creating dynamic SQL with some minimally CPU- and I/O-intensive process to get table row counts (maybe [untested!] "SELECT 1 from <table-name> FETCH FIRST 1 ROWS ONLY WITH UR", then use the SQLCODE to determine if rows exist).

A better answer will probably require knowing your goals and/or the specific problem you are trying to solve, and what constraints and/or inaccuracies you are willing to live with.

HTH!

Lock Lyon
Fifth Third Bancorp



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Thursday, December 23, 2010 10:42 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.

How can I query the name of all tables containing zero rows.
 
Jim McAlpine

This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Jim McAlpine

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to Lockwood Lyon)
On Thu, Dec 23, 2010 at 4:10 PM, Lyon, Lockwood <[login to unmask email]>wrote:

> Jim,
>
> This is an interesting question! A lot depends on what you mean by "query"
> (a single SQL stmt? Multiple SQL statement? Non-SQL alternatives?), "table"
> (Including Aux tables for LOBs? MQTs? Catalog tables?), and "zero rows"
> (Tables CREATEd but no rows ever inserted/loaded? Tables which AT THIS
> INSTANT have no rows?).
>
> I'd say that any SQL-based answer would have some issues. For one, querying
> the tables directly (with some kind of COUNT(*) and/or FETCH FIRST 1 ROWS
> ONLY logic) would cause locking for the duration of your query. Unless you
> use WITH UR ... in which case, you're not really accurately counting empty
> tables, are you? What if an INSERT to an empty table is executed during the
> duration of your query?
>
> A RUNSTATS-based approach will give you answers ... but only as of the
> date/time you ran RunStats and with the CPU and I/O overhead of accessing
> *all* of your tables. I expect that even the quickest set of Stats jobs
> executed against all of your operational tables would run for ... what?
> Hours? Days? How will you prevent rows being added to any of these tables
> after Stats has run?
>
> My guess is that only a programmatic solution has a chance of giving you a
> relatively correct answer within a relatively short period of time. Code a
> 3GL program to: (a) Select your *subset* of target table names (perhaps
> limit by DB.TS?) from SYSTABLES into, say, a GTT; (b) Loop thru the GTT
> creating dynamic SQL with some minimally CPU- and I/O-intensive process to
> get table row counts (maybe [untested!] "SELECT 1 from <table-name> FETCH
> FIRST 1 ROWS ONLY WITH UR", then use the SQLCODE to determine if rows
> exist).
>
> A better answer will probably require knowing your goals and/or the
> specific problem you are trying to solve, and what constraints and/or
> inaccuracies you are willing to live with.
>
> HTH!
>
> Lock Lyon
> Fifth Third Bancorp
>
>
>
Lock, a bit more background for you. I have a couple of rexx programs which
I use to create jobs to unload and then reload a set of application tables
to clone systems for development and support. There are approx 3000 tables
in each system but a large percentage will contain no rows (I can tell that
from the %used of the unloaded file) as these ones are more akin to
sequential files and they get emptied from time to time during the batch run
processes. We use tables instead of files for restartability purposes. So
what I am trying to avoid is unloading and reloading the empty tables. At
the moment I process every table and it takes a long time to complete and
I'm just trying to make it quicker. One further thing is that the source
tables will be stopped during this process.

HTH

Jim McAlpine

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Mark M2 Doyle

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to Jim McAlpine)
How can I query the name of all tables containing zero rows? -- Jim McAlpine

Jim,

Lockwood Lyon's comments are spot on, and I echo them. You probably should better define the business problem you are trying to solve, and the limitations you are running under. Now, having said that, I attempted to get the answer for my catalog (not the whole system) and the query used to produce the sql that gave me the answer is below.

I tested the query below in a V8 system, and the resultant query (with 16 table names) ran in under 5 seconds for me. YMMV.

Warning: THIS IS POTENTIALLY A VERY DANGEROUS QUERY. USE AT YOUR OWN RISK.
Let me repeat that: THIS IS POTENTIALLY A VERY DANGEROUS QUERY. USE AT YOUR OWN RISK.
You have been warned.

The v8 catalog only has 86 tables, and V10 only adds about 2 dozen more, so the query should run as-is for any version of DB2. Trying to expand it too far will cause the # of union all clauses to exceed DB2's limit. In my experience 300 "union all"'s exceed that limit;

Mark


SELECT
' SELECT ''' CONCAT STRIP(CREATOR) CONCAT '.' CONCAT STRIP(NAME)
CONCAT ''' FROM SYSIBM.SYSDUMMY1'
, CREATOR, NAME, 1
FROM SYSIBM.SYSTABLES
WHERE DBNAME = 'DSNDB06' AND TYPE = 'T'
UNION ALL
SELECT
' HAVING (SELECT COUNT(*) FROM ' CONCAT STRIP(CREATOR) CONCAT '.'
CONCAT STRIP(NAME) CONCAT ') = 0 UNION ALL '
, CREATOR, NAME, 2
FROM SYSIBM.SYSTABLES
WHERE DBNAME = 'DSNDB06' AND TYPE = 'T'
UNION ALL
SELECT ' SELECT '' '' FROM SYSIBM.SYSDUMMY1 ORDER BY 1;'
, 'ZZZZZZ', 'ZZZZZZZZZ', 3
FROM SYSIBM.SYSDUMMY1
ORDER BY 2,3,4;

This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and
any attachments are believed to be free of any virus or other
defect that might affect any computer system into which it is
received and opened, it is the responsibility of the recipient to
ensure that it is virus free and no responsibility is accepted by
JPMorgan Chase & Co., its subsidiaries and affiliates, as
applicable, for any loss or damage arising in any way from its use.
If you received this transmission in error, please immediately
contact the sender and destroy the material in its entirety,
whether in electronic or hard copy format. Thank you.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Steen Rasmussen

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to Mark M2 Doyle)
Good point Phil – but you might still have objects in the catalog without the corresponding entries in the RTS tables.

There are “scripts” out there which will populate the RTS tables where no entries exist (I believe there’s one on the IDUG share too. If you have CA Database Analyzer, there’s a feature to do this too.



Steen Rasmussen
CA Technologies
Sr Engineering Services Architect

IBM Certified Database Associate - DB2 9 Fundamentals

IBM Certified database Administrator - DB2 9 DBA for z/OS





From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Thursday, December 23, 2010 10:08 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.



Or, if you are externalizing RTS (which everyone should be now) you can look in SYSTABLESPACESTATS for the table SPACES that are empty



Look at SYSTABLESPACESTATS.TOTALROWS



To find the TABLES in empty table spaces, join SYSTABLESPACESTATS to SYSTABLES on DBNAME and (TS)NAME

Phil Grainger
Cogito Ltd.

[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768

www.cogito.co.uk <blocked:: http://www.cogito.co.uk >



Attend IDUG 2011 - the premiere events for DB2 professionals.

IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Satish Srikakulapu
Sent: 23 December 2010 15:56
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.



Jim,



If it is possible, run the RUNSTATS on all the tables and run the below query.



SELECT NAME FROM SYSIBM.SYSTABLES WHERE CARDF = 0 AND TYPE = ‘T’.



This should give you the result that you need.



Thanks,
Satish Srikakulapu



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Thursday, December 23, 2010 8:42 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.



How can I query the name of all tables containing zero rows.



Jim McAlpine



________________________________

Introducing IBM® DB2® 10 for z/OS < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

--- NOTICE ---

This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.



________________________________

Introducing IBM® DB2® 10 for z/OS < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >



________________________________

Introducing IBM® DB2® 10 for z/OS < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Mark M2 Doyle

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to Steen Rasmussen)
Jim,

Your additional background really helps. I did something like this for a PeopleSoft Financials application with 18,000+ tables. When I finally was able to figure out which tables acted like sequential files, I moved them to their own databases, and didn't move data for tables in those databases. (I needed multiple databases because I couldn't fit all the tables in one database). You might be able to do something like this in a 'phase 2'.

To the issue at hand: were I doing it, I would runstat first, then unload / load based on those stats. It would probably be a minor change; simply adding "AND CARCDF <> 0" to the WHERE clause(s) of your existing REXX processes.

HTH

Mark

Lock, a bit more background for you. I have a couple of rexx programs which I use to create jobs to unload and then reload a set of application tables to clone systems for development and support. There are approx 3000 tables in each system but a large percentage will contain no rows (I can tell that from the %used of the unloaded file) as these ones are more akin to sequential files and they get emptied from time to time during the batch run processes. We use tables instead of files for restartability purposes. So what I am trying to avoid is unloading and reloading the empty tables. At the moment I process every table and it takes a long time to complete and I'm just trying to make it quicker. One further thing is that the source tables will be stopped during this process.

HTH

Jim McAlpine



This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and
any attachments are believed to be free of any virus or other
defect that might affect any computer system into which it is
received and opened, it is the responsibility of the recipient to
ensure that it is virus free and no responsibility is accepted by
JPMorgan Chase & Co., its subsidiaries and affiliates, as
applicable, for any loss or damage arising in any way from its use.
If you received this transmission in error, please immediately
contact the sender and destroy the material in its entirety,
whether in electronic or hard copy format. Thank you.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

James Campbell

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to Mark M2 Doyle)
This query works assuming you have RTS turned on...

SELECT TBL.CREATOR
,TBL.NAME
,TBL.DBNAME
,TBL.TSNAME
,STAT.PARTITION
,STAT.STATSINSERTS
,STAT.STATSDELETES
,TBL.CARDF
,((TBL.CARDF + STAT.STATSINSERTS) - STAT.STATSDELETES) AS ROWCNT
FROM SYSIBM.SYSTABLES TBL,
SYSIBM.SYSTABLESPACESTATS STAT
WHERE TBL.TYPE = 'T'
AND TBL.DBNAME = STAT.DBNAME
AND TBL.TSNAME = STAT.NAME
AND ((TBL.CARDF + STAT.STATSINSERTS) - STAT.STATSDELETES) = 0
ORDER BY 1,2,5
;

Jim Campbell
Sr. Database Administrator
360-704-4015
[login to unmask email]<mailto:[login to unmask email]>

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Thursday, December 23, 2010 7:42 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.

How can I query the name of all tables containing zero rows.

Jim McAlpine

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Lockwood Lyon

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to James Campbell)
Jim,

Well, to be honest, I don't see a great deal of overhead or wasted time in unloading an empty table (how many I/Os do you think this will be?) or loading a table from an empty input file.

Also, consider: Suppose you have an empty table you've unloaded, and now you wish to "restore" it. Suppose the current table has rows in it?! Then you'd want to "restore" that table to its original form, i.e., Empty. Fastest way to do that? Load from an empty file, yes?

Hopefully the SQL you've recieved from other posters can help you determine which subset(s) of usually-empty table exist. You could then split your unload/reload process into two pieces, one for populated tables (which would be unloaded and reloaded), one for the usually-empty tables (no unload, just a template load from an empty input file).

Good luck!

Lock Lyon
Fifth Third Bancorp


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Thursday, December 23, 2010 11:36 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.

On Thu, Dec 23, 2010 at 4:10 PM, Lyon, Lockwood <[login to unmask email]> wrote:
Jim,

[...snip...]

 
Lock, a bit more background for you.  I have a couple of rexx programs which I use to create jobs to unload and then reload a set of application tables to clone systems for development and support.  There are approx 3000 tables in each system but a large percentage will contain no rows (I can tell that from the %used of the unloaded file) as these ones are more akin to sequential files and they get emptied from time to time during the batch run processes.  We use tables instead of files for restartability purposes. So what I am trying to avoid is unloading and reloading the empty tables.  At the moment I process every table and it takes a long time to complete and I'm just trying to make it quicker.  One further thing is that the source tables will be stopped during this process.     
 
HTH
 
Jim McAlpine
 

This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Mike Bell

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to Lockwood Lyon)
One thought - I have seen the time required to delete/define the VSAM to be
many multiples of the time to do the actual load if you don't specify REUSE
on the LOAD parm. Especially when there are lots of indexes that need to be
rebuilt also.

Mike

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Lyon, Lockwood
Sent: Thursday, December 23, 2010 12:34 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.

Jim,

Well, to be honest, I don't see a great deal of overhead or wasted time in
unloading an empty table (how many I/Os do you think this will be?) or
loading a table from an empty input file.

Also, consider: Suppose you have an empty table you've unloaded, and now
you wish to "restore" it. Suppose the current table has rows in it?! Then
you'd want to "restore" that table to its original form, i.e., Empty.
Fastest way to do that? Load from an empty file, yes?

Hopefully the SQL you've recieved from other posters can help you determine
which subset(s) of usually-empty table exist. You could then split your
unload/reload process into two pieces, one for populated tables (which would
be unloaded and reloaded), one for the usually-empty tables (no unload, just
a template load from an empty input file).

Good luck!

Lock Lyon
Fifth Third Bancorp


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Thursday, December 23, 2010 11:36 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.

On Thu, Dec 23, 2010 at 4:10 PM, Lyon, Lockwood <[login to unmask email]>
wrote:
Jim,

[...snip...]

 
Lock, a bit more background for you.  I have a couple of rexx programs which
I use to create jobs to unload and then reload a set of application tables
to clone systems for development and support.  There are approx 3000 tables
in each system but a large percentage will contain no rows (I can tell that
from the %used of the unloaded file) as these ones are more akin to
sequential files and they get emptied from time to time during the batch run
processes.  We use tables instead of files for restartability purposes. So
what I am trying to avoid is unloading and reloading the empty tables.  At
the moment I process every table and it takes a long time to complete and
I'm just trying to make it quicker.  One further thing is that the source
tables will be stopped during this process.     
 
HTH
 
Jim McAlpine
 

This e-mail transmission contains information that is confidential and may
be privileged. It is intended only for the addressee(s) named above. If
you receive this e-mail in error, please do not read, copy or disseminate it
in any manner. If you are not the intended recipient, any disclosure,
copying, distribution or use of the contents of this information is
prohibited. Please reply to the message immediately by informing the sender
that the message was misdirected. After replying, please erase it from your
computer system. Your assistance in correcting this error is appreciated.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information.
*
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Jim McAlpine

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to Mike Bell)
On Thu, Dec 23, 2010 at 6:34 PM, Lyon, Lockwood <[login to unmask email]>wrote:

> Jim,
>
> Well, to be honest, I don't see a great deal of overhead or wasted time in
> unloading an empty table (how many I/Os do you think this will be?) or
> loading a table from an empty input file.
>
> Also, consider: Suppose you have an empty table you've unloaded, and now
> you wish to "restore" it. Suppose the current table has rows in it?! Then
> you'd want to "restore" that table to its original form, i.e., Empty.
> Fastest way to do that? Load from an empty file, yes?
>
> Hopefully the SQL you've recieved from other posters can help you determine
> which subset(s) of usually-empty table exist. You could then split your
> unload/reload process into two pieces, one for populated tables (which would
> be unloaded and reloaded), one for the usually-empty tables (no unload, just
> a template load from an empty input file).
>
> Good luck!
>
> Lock Lyon
> Fifth Third Bancorp
>
>
>
Lock, it took 5.5 hours to do the loads and 75% of these were unnecessary.
I'll check out Mike Bells tip regarding REUSE.

Jim McAlpine

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Raymond Bell

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to Jim McAlpine)
Jim,

Sorry, wasn't watching this one too closely. You're copying the data in thousands of DB2 objects from one environment to another? I'm guessing you don't have any ISV tools that could help. I know we provide this type of feature in some of our products (he says, skating with the [AD] tag) and I imagine others do too. Might be worth looking at what tools you've got and maybe giving someone a shout for some ideas - if that's a goer. Depends how often you do this and how painful you're finding the current process - as you've seen, it gets complicated catering for the 4 combinations of there being/not being data in the source/target table.

Anyway, just food for thought. You know where we all are if you want to chat.

Cheers,


Raymond
Ps. Was that vendor-agnostic enough for the Post Polizei? ;o)


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: 24 December 2010 09:16
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.

On Thu, Dec 23, 2010 at 6:34 PM, Lyon, Lockwood <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Jim,

Well, to be honest, I don't see a great deal of overhead or wasted time in unloading an empty table (how many I/Os do you think this will be?) or loading a table from an empty input file.

Also, consider: Suppose you have an empty table you've unloaded, and now you wish to "restore" it. Suppose the current table has rows in it?! Then you'd want to "restore" that table to its original form, i.e., Empty. Fastest way to do that? Load from an empty file, yes?

Hopefully the SQL you've recieved from other posters can help you determine which subset(s) of usually-empty table exist. You could then split your unload/reload process into two pieces, one for populated tables (which would be unloaded and reloaded), one for the usually-empty tables (no unload, just a template load from an empty input file).

Good luck!

Lock Lyon
Fifth Third Bancorp


Lock, it took 5.5 hours to do the loads and 75% of these were unnecessary. I'll check out Mike Bells tip regarding REUSE.

Jim McAlpine

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Isaac Yassin

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to Raymond Bell)
Hi,

Numbers from real life:



1 TS - 800 tables, 1800 indexes.

1.

LOAD REPLACE (on 1st table) - 45 minutes.

LOAD RESUME (on other 799 tables) - 3 hours (serialized)

2.

DELETE + LOAD RESUME YES SHRLEVEL CHANGE , in groups of 10 jobs in parallel
- < 10 minutes J



Isaac Yassin







From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Friday, December 24, 2010 11:16 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.



On Thu, Dec 23, 2010 at 6:34 PM, Lyon, Lockwood <[login to unmask email]>
wrote:

Jim,

Well, to be honest, I don't see a great deal of overhead or wasted time in
unloading an empty table (how many I/Os do you think this will be?) or
loading a table from an empty input file.

Also, consider: Suppose you have an empty table you've unloaded, and now
you wish to "restore" it. Suppose the current table has rows in it?! Then
you'd want to "restore" that table to its original form, i.e., Empty.
Fastest way to do that? Load from an empty file, yes?

Hopefully the SQL you've recieved from other posters can help you determine
which subset(s) of usually-empty table exist. You could then split your
unload/reload process into two pieces, one for populated tables (which would
be unloaded and reloaded), one for the usually-empty tables (no unload, just
a template load from an empty input file).

Good luck!


Lock Lyon
Fifth Third Bancorp





Lock, it took 5.5 hours to do the loads and 75% of these were unnecessary.
I'll check out Mike Bells tip regarding REUSE.



Jim McAlpine



_____

< http://www-01.ibm.com/software/data/db2/zos/db2-10/ > Introducing IBMR DB2R
10 for z/OS

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >


_____

I am using the Free version of SPAMfighter < http://www.spamfighter.com/len >
.
SPAMfighter has removed 214 of my spam emails to date.

Do you have a slow PC? < http://www.spamfighter.com/SLOW-PCfighter?cid=sigen >
Try free scan!

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
Attachments

  • import1 (7.5k)

Phil Grainger

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to Isaac Yassin)
Then, providing you are careful with the limitations, there’s “good old” DSN1COPY (or even dfsms for that matter)

If you just want to replicate one environment (plus data) elsewhere, then there are far more efficient methods than unload/load….

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: 24 December 2010 09:31
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.

Jim,

Sorry, wasn’t watching this one too closely. You’re copying the data in thousands of DB2 objects from one environment to another? I’m guessing you don’t have any ISV tools that could help. I know we provide this type of feature in some of our products (he says, skating with the [AD] tag) and I imagine others do too. Might be worth looking at what tools you’ve got and maybe giving someone a shout for some ideas – if that’s a goer. Depends how often you do this and how painful you’re finding the current process - as you’ve seen, it gets complicated catering for the 4 combinations of there being/not being data in the source/target table.

Anyway, just food for thought. You know where we all are if you want to chat.

Cheers,


Raymond
Ps. Was that vendor-agnostic enough for the Post Polizei? ;o)


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: 24 December 2010 09:16
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.

On Thu, Dec 23, 2010 at 6:34 PM, Lyon, Lockwood <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Jim,

Well, to be honest, I don't see a great deal of overhead or wasted time in unloading an empty table (how many I/Os do you think this will be?) or loading a table from an empty input file.

Also, consider: Suppose you have an empty table you've unloaded, and now you wish to "restore" it. Suppose the current table has rows in it?! Then you'd want to "restore" that table to its original form, i.e., Empty. Fastest way to do that? Load from an empty file, yes?

Hopefully the SQL you've recieved from other posters can help you determine which subset(s) of usually-empty table exist. You could then split your unload/reload process into two pieces, one for populated tables (which would be unloaded and reloaded), one for the usually-empty tables (no unload, just a template load from an empty input file).

Good luck!

Lock Lyon
Fifth Third Bancorp

Lock, it took 5.5 hours to do the loads and 75% of these were unnecessary. I'll check out Mike Bells tip regarding REUSE.

Jim McAlpine

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Lockwood Lyon

Re: DB2 for z/OS V8 - query tables with zero rows.
(in response to Phil Grainger)
Jim,

If I'm understanding your numbers correctly, it took you 5.5 hours to Load 3000 tables, 75% of which were "unnecessary". You don't give elapsed time figures for the empty versus non-empty tables, so it's not clear if the total time spent is due to loading "actual" tables, overhead associated with the empty tables, or both.

I assume you've already incorporated typical "load process" speedups; but, just in case you missed them:

o As Mike noted, use REUSE
o Do LOAD REPLACE LOG NO NOCOPYPEND ENFORCE NO
o Do several (say, 100) table LOADs in a single execution of DSNUTILB; *not* one LOAD per
job step or (worse!) one LOAD per job.
o Do not specify PREFORMAT
o Run multiple LOAD jobs in parallel

As an alternative to executing LOAD (as another poster noted), first ensure that the tables are empty; then, as a part of your Backup process take DSN1COPY backups of all relevant pagesets (tablespaces, indexes, aux tables, etc). Your Restore process then uses DSN1COPY, rather than invoking the DB2 utility.

Good luck,

- Lock Lyon
Fifth Third Bancorp


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Friday, December 24, 2010 4:16 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - query tables with zero rows.

 
Lock,  it took 5.5 hours to do the loads and 75% of these were unnecessary.  I'll check out Mike Bells tip regarding REUSE.
 
Jim McAlpine

This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv