Aggregating Variable Sets of Tables

Frank Fillmore, Jr.

Aggregating Variable Sets of Tables

I have the following challenge.

Joe

Table_1 (5 rows)     Table_2 (6 rows)     Table_3 (2 rows)

Jane

Table_2 (7 rows)     Table_3 (4 rows)

Report

Creator      #Tables     #Rows  

Joe            3                13

Jane          2                11

I want to aggregate the number of tables and the number of rows in all tables for a particular user.  There is a group of two dozen or so tables of interest.  There are thousands of users.  As you can see, everybody doesn't have every table.

So far I have explored:

  1. CASE statement
  2. Global Temporary Tables
  3. Common Table Expressions (with and without recursion)
  4. OLAP functions
  5. and so on...

I can certainly write a program or use a brute force method, but I believe that this can be done with just SQL.  The problem is accumulating the number of rows in only the tables that exist for a particular user.

Thoughts?  Much obliged in advance.

James Campbell

RE: Aggregating Variable Sets of Tables
(in response to Frank Fillmore, Jr.)

I'd use a two step process:

Step 1: SQL generates, for each user, a SELECT ... UNION ALL for each table that that user has

Step 2: execute the generated SQL.

 

James Campbell



In Reply to Frank Fillmore, Jr.:

I have the following challenge.

Joe

Table_1 (5 rows)     Table_2 (6 rows)     Table_3 (2 rows)

Jane

Table_2 (7 rows)     Table_3 (4 rows)

Report

Creator      #Tables     #Rows  

Joe            3                13

Jane          2                11

I want to aggregate the number of tables and the number of rows in all tables for a particular user.  There is a group of two dozen or so tables of interest.  There are thousands of users.  As you can see, everybody doesn't have every table.

So far I have explored:

  1. CASE statement
  2. Global Temporary Tables
  3. Common Table Expressions (with and without recursion)
  4. OLAP functions
  5. and so on...

I can certainly write a program or use a brute force method, but I believe that this can be done with just SQL.  The problem is accumulating the number of rows in only the tables that exist for a particular user.

Thoughts?  Much obliged in advance.

Nadir Doctor

Aggregating Variable Sets of Tables
(in response to Frank Fillmore, Jr.)
The row count will likely change over time and one could write sql to query
the data dictionary tables/views to obtain those details quickly + when
last runstats refresh was done.






On Thu, Feb 13, 2020 at 5:38 PM Frank Fillmore, Jr. <[login to unmask email]>
wrote:

> I have the following challenge.
>
> Joe
>
> Table_1 (5 rows) Table_2 (6 rows) Table_3 (2 rows)
>
> Jane
>
> Table_2 (7 rows) Table_3 (4 rows)
>
> *Report*
>
> Creator #Tables #Rows
>
> Joe 3 13
>
> Jane 2 11
>
> I want to aggregate the number of tables and the number of rows in *all*
> tables for a particular user. There is a group of two dozen or so tables
> of interest. There are thousands of users. As you can see, everybody
> doesn't have every table.
>
> So far I have explored:
>
> 1. CASE statement
> 2. Global Temporary Tables
> 3. Common Table Expressions (with and without recursion)
> 4. OLAP functions
> 5. and so on...
>
> I can certainly write a program or use a brute force method, but I believe
> that this can be done with just SQL. The problem is accumulating the
> number of rows in only the tables that exist for a particular user.
>
> Thoughts? Much obliged in advance.
>
> -----End Original Message-----
>

Michael Hannan

RE: Aggregating Variable Sets of Tables
(in response to Frank Fillmore, Jr.)

Frank,

You don't want to use TOTALROWS (aggregating the partitions) in SYSTABLESPACESTATS, right, in case its not populated? If using that, then it would be obviously not so difficult.

If Using a generated SQL, like James suggests, be aware there is a limit to the number of query blocks allowed in an SQL, is 254 if I remember right (I have tried to exceed it it before). So generate multiple SELECTs with UNIONs up to a max number, each extracting the User and Table name. Run under DSNTIAUL, append all the results to a single file, and load results into a work table, before running final SELECT to produce the report.

How many tables do you talk about in total? I would probably only use the the generated SELECT COUNTs technique for tables where SYSTABLESPACESTATS.TOTALROWS data was missing (hopefully very few).

Another option is to generate INSERT to a work table (with COMMIT)  instead of UNION ALL for each SELECT COUNT, however that prevents use of WITH UR, and therefore locking delays and timeout becomes a possibility. It would be really nice if WITH UR could be coded at a subquery level, but alas not.

In the end, the problem is certainly solvable, but may take just a bit of time to get it right. I have lots of experience writing SQLs to generate other SQLs, for data fixes, and for Inserts to the DSN_VIRTUAL_INDEXES table for example, and Catlg reports, so that means I can write this type of SQL quickly, but there are traps for those inexperienced in generating stuff from the Catalog, such as Unicode to EBCDIC conversion (Cast to EBCDIC sometimes needed), getting rid of Null indicators and Varchar length prefixes.

Start with the TOTALROWS easy solution approximation. When TOTALROWS is missing, consider if it is good enough to fallback to SYSTABLES.CARDF, or are the Stats too old? 

Aha, I see a problem if you have multiple tables in the one segmented tablespace. In that case use the count from SYSINDEXSPACESTATS. If User table has no indexes and is in shared TS, then consider if CARDF is good enough, or generate the COUNT SQLs. 

This task is probably very well suited to  REXX driven dynamic SQLs as well, if you have the experience calling SQLs from REXX. I would go the full SQL way myself, as am too lazy to write the REXX, and my SQL skills are higher. SQL can do almost anything, except be self modifying.

Different solution approaches can work depending on what are your skill sets. We just want a result, easily maintainable and quick to write I think.

Any further problems, ask away. All these types of problems have been solved before.

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 14, 2020 - 02:12 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 14, 2020 - 02:18 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 14, 2020 - 02:25 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 14, 2020 - 02:30 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 14, 2020 - 02:32 AM (Europe/Berlin)

Bill Gallagher

[External] Aggregating Variable Sets of Tables
(in response to Frank Fillmore, Jr.)
I would think that a native stored procedure that returns what you want as a result set would be fairly simple to write and would be much more efficient than trying to do this with some fairly complex SQL statement.

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration http://teams.trv.net/it/kx_data_adm/database/SitePages/Home.aspx

From: Frank Fillmore, Jr. <[login to unmask email]>
Sent: Thursday, February 13, 2020 6:38 PM
To: [login to unmask email]
Subject: [External] [DB2-L] - Aggregating Variable Sets of Tables


I have the following challenge.

Joe

Table_1 (5 rows) Table_2 (6 rows) Table_3 (2 rows)

Jane

Table_2 (7 rows) Table_3 (4 rows)

Report

Creator #Tables #Rows

Joe 3 13

Jane 2 11

I want to aggregate the number of tables and the number of rows in all tables for a particular user. There is a group of two dozen or so tables of interest. There are thousands of users. As you can see, everybody doesn't have every table.

So far I have explored:

1. CASE statement
2. Global Temporary Tables
3. Common Table Expressions (with and without recursion)
4. OLAP functions
5. and so on...

I can certainly write a program or use a brute force method, but I believe that this can be done with just SQL. The problem is accumulating the number of rows in only the tables that exist for a particular user.

Thoughts? Much obliged in advance.

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Phil Grainger

Aggregating Variable Sets of Tables
(in response to James Campbell)
I must be missing something

Isn’t this just a count(*) from systables joined to a sum() of the RTS row counts?

Grouped by creator, of course

Phil G

Sent from my iPad

On 13 Feb 2020, at 23:46, James Campbell <[login to unmask email]> wrote:



I'd use a two step process:

Step 1: SQL generates, for each user, a SELECT ... UNION ALL for each table that that user has

Step 2: execute the generated SQL.



James Campbell


In Reply to Frank Fillmore, Jr.:

I have the following challenge.

Joe

Table_1 (5 rows) Table_2 (6 rows) Table_3 (2 rows)

Jane

Table_2 (7 rows) Table_3 (4 rows)

Report

Creator #Tables #Rows

Joe 3 13

Jane 2 11

I want to aggregate the number of tables and the number of rows in all tables for a particular user. There is a group of two dozen or so tables of interest. There are thousands of users. As you can see, everybody doesn't have every table.

So far I have explored:

1. CASE statement
2. Global Temporary Tables
3. Common Table Expressions (with and without recursion)
4. OLAP functions
5. and so on...

I can certainly write a program or use a brute force method, but I believe that this can be done with just SQL. The problem is accumulating the number of rows in only the tables that exist for a particular user.

Thoughts? Much obliged in advance.

-----End Original Message-----

Philip Sevetson

[External] Aggregating Variable Sets of Tables
(in response to Bill Gallagher)
My $.02 on this, without any inflation jokes:

Frank, if you’re allowing use of SYSIBM.SYSTABLES.CARDF, or of SYSTABLESPACESTATS, this is trivial; so I’m assuming that what you want is a current count without ensuring up-to-date, fully populated catalog statistics or RTS.

So: the core problem is that of executing a varying number of “SELECT COUNT(*) FROM owner.table” statements within a single executable SQL.

As far as I know, there isn’t currently a way to do that. You need a first query to return a set of tables to be processed in a second query.



Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Gallagher,Bill R <[login to unmask email]>
Sent: Friday, February 14, 2020 7:32 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [External] Aggregating Variable Sets of Tables

I would think that a native stored procedure that returns what you want as a result set would be fairly simple to write and would be much more efficient than trying to do this with some fairly complex SQL statement.

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration <BLOCKEDteams%5b.%5dtrv%5b.%5dnet/it/kx_data_adm/database/SitePages/Home%5b.%5daspxBLOCKED>

From: Frank Fillmore, Jr. <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Thursday, February 13, 2020 6:38 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [External] [DB2-L] - Aggregating Variable Sets of Tables


I have the following challenge.

Joe

Table_1 (5 rows) Table_2 (6 rows) Table_3 (2 rows)

Jane

Table_2 (7 rows) Table_3 (4 rows)

Report

Creator #Tables #Rows

Joe 3 13

Jane 2 11

I want to aggregate the number of tables and the number of rows in all tables for a particular user. There is a group of two dozen or so tables of interest. There are thousands of users. As you can see, everybody doesn't have every table.

So far I have explored:

1. CASE statement
2. Global Temporary Tables
3. Common Table Expressions (with and without recursion)
4. OLAP functions
5. and so on...

I can certainly write a program or use a brute force method, but I believe that this can be done with just SQL. The problem is accumulating the number of rows in only the tables that exist for a particular user.

Thoughts? Much obliged in advance.

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Sam Baugh

[External] Aggregating Variable Sets of Tables
(in response to Bill Gallagher)
It can all be done by a single SQL statement, the following uses CTE's to
represent the different tables.

with tb1
as (
select 'Joe' as name from sysibm.sysdummy1 union all
select 'Joe' as name from sysibm.sysdummy1 union all
select 'Joe' as name from sysibm.sysdummy1 union all
select 'Jane' as name from sysibm.sysdummy1 union all
select 'Jane' as name from sysibm.sysdummy1
)
, tb2
as (
select 'Joe' as name from sysibm.sysdummy1 union all
select 'Tom' as name from sysibm.sysdummy1 union all
select 'Joe' as name from sysibm.sysdummy1 union all
select 'Jill' as name from sysibm.sysdummy1 union all
select 'Jane' as name from sysibm.sysdummy1
)
, tb3
as (
select 'Sam' as name from sysibm.sysdummy1 union all
select 'Bill' as name from sysibm.sysdummy1 union all
select 'Jill' as name from sysibm.sysdummy1 union all
select 'Jane' as name from sysibm.sysdummy1 union all
select 'Jane' as name from sysibm.sysdummy1
)
select name as creator
, count(*) as tbl_count
, sum(rows) as tbl_rows
from (
select name, count(*) as rows from tb1 group by name
union all
select name, count(*) as rows from tb2 group by name
union all
select name, count(*) as rows from tb3 group by name
) as x
group by name
with ur

On Fri, Feb 14, 2020 at 6:32 AM Gallagher,Bill R <[login to unmask email]>
wrote:

> I would think that a native stored procedure that returns what you want as
> a result set would be fairly simple to write and would be much more
> efficient than trying to do this with some fairly complex SQL statement.
>
>
>
> *Bill Gallagher *|* Senior Systems Engineer, DBA *|* Data Administration
> http://teams.trv.net/it/kx_data_adm/database/SitePages/Home.aspx *
>
>
>
> *From:* Frank Fillmore, Jr. <[login to unmask email]>
> *Sent:* Thursday, February 13, 2020 6:38 PM
> *To:* [login to unmask email]
> *Subject:* [External] [DB2-L] - Aggregating Variable Sets of Tables
>
>
>
> I have the following challenge.
>
> Joe
>
> Table_1 (5 rows) Table_2 (6 rows) Table_3 (2 rows)
>
> Jane
>
> Table_2 (7 rows) Table_3 (4 rows)
>
> *Report*
>
> Creator #Tables #Rows
>
> Joe 3 13
>
> Jane 2 11
>
> I want to aggregate the number of tables and the number of rows in *all*
> tables for a particular user. There is a group of two dozen or so tables
> of interest. There are thousands of users. As you can see, everybody
> doesn't have every table.
>
> So far I have explored:
>
> 1. CASE statement
> 2. Global Temporary Tables
> 3. Common Table Expressions (with and without recursion)
> 4. OLAP functions
> 5. and so on...
>
> I can certainly write a program or use a brute force method, but I believe
> that this can be done with just SQL. The problem is accumulating the
> number of rows in only the tables that exist for a particular user.
>
> Thoughts? Much obliged in advance.
>
>
> -----End Original Message-----
> ------------------------------
> This message (including any attachments) may contain confidential,
> proprietary, privileged and/or private information. The information is
> intended to be for the use of the individual or entity designated above. If
> you are not the intended recipient of this message, please notify the
> sender immediately, and delete the message and any attachments. Any
> disclosure, reproduction, distribution or other use of this message or any
> attachments by an individual or entity other than the intended recipient is
> prohibited.
>
> TRVDiscDefault::1201
> -----End Original Message-----
>

Phil Grainger

[External] Aggregating Variable Sets of Tables
(in response to Philip Sevetson)
But isn't RTS "up to date" anyway?

On 14/02/2020 13:07, Sevetson, Phil wrote:
My $.02 on this, without any inflation jokes:

Frank, if you’re allowing use of SYSIBM.SYSTABLES.CARDF, or of SYSTABLESPACESTATS, this is trivial; so I’m assuming that what you want is a current count without ensuring up-to-date, fully populated catalog statistics or RTS.

So: the core problem is that of executing a varying number of “SELECT COUNT(*) FROM owner.table” statements within a single executable SQL.

As far as I know, there isn’t currently a way to do that. You need a first query to return a set of tables to be processed in a second query.



Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Gallagher,Bill R <[login to unmask email]><mailto:[login to unmask email]>
Sent: Friday, February 14, 2020 7:32 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: [External] Aggregating Variable Sets of Tables

I would think that a native stored procedure that returns what you want as a result set would be fairly simple to write and would be much more efficient than trying to do this with some fairly complex SQL statement.

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration <BLOCKEDteams%5b.%5dtrv%5b.%5dnet/it/kx_data_adm/database/SitePages/Home%5b.%5daspxBLOCKED>

From: Frank Fillmore, Jr. <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Thursday, February 13, 2020 6:38 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [External] [DB2-L] - Aggregating Variable Sets of Tables


I have the following challenge.

Joe

Table_1 (5 rows) Table_2 (6 rows) Table_3 (2 rows)

Jane

Table_2 (7 rows) Table_3 (4 rows)

Report

Creator #Tables #Rows

Joe 3 13

Jane 2 11

I want to aggregate the number of tables and the number of rows in all tables for a particular user. There is a group of two dozen or so tables of interest. There are thousands of users. As you can see, everybody doesn't have every table.

So far I have explored:

1. CASE statement
2. Global Temporary Tables
3. Common Table Expressions (with and without recursion)
4. OLAP functions
5. and so on...

I can certainly write a program or use a brute force method, but I believe that this can be done with just SQL. The problem is accumulating the number of rows in only the tables that exist for a particular user.

Thoughts? Much obliged in advance.

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

Philip Sevetson

[External] Aggregating Variable Sets of Tables
(in response to Phil Grainger)
Grandfathered stuff which doesn’t change, isn’t up to date. There may be other classes of objects which are not up to date, depending on whether third-party utilities in non-SQL updates are fully compliant with best practices…

-phil s.


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Phil Grainger <[login to unmask email]>
Sent: Friday, February 14, 2020 8:53 AM
To: Sevetson, Phil <[login to unmask email]>
Subject: [DB2-L] - RE: [External] Aggregating Variable Sets of Tables


But isn't RTS "up to date" anyway?
On 14/02/2020 13:07, Sevetson, Phil wrote:
My $.02 on this, without any inflation jokes:

Frank, if you’re allowing use of SYSIBM.SYSTABLES.CARDF, or of SYSTABLESPACESTATS, this is trivial; so I’m assuming that what you want is a current count without ensuring up-to-date, fully populated catalog statistics or RTS.

So: the core problem is that of executing a varying number of “SELECT COUNT(*) FROM owner.table” statements within a single executable SQL.

As far as I know, there isn’t currently a way to do that. You need a first query to return a set of tables to be processed in a second query.



Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Gallagher,Bill R <[login to unmask email]><mailto:[login to unmask email]>
Sent: Friday, February 14, 2020 7:32 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: [External] Aggregating Variable Sets of Tables

I would think that a native stored procedure that returns what you want as a result set would be fairly simple to write and would be much more efficient than trying to do this with some fairly complex SQL statement.

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration <BLOCKEDteams%5b.%5dtrv%5b.%5dnet/it/kx_data_adm/database/SitePages/Home%5b.%5daspxBLOCKED>

From: Frank Fillmore, Jr. <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Thursday, February 13, 2020 6:38 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [External] [DB2-L] - Aggregating Variable Sets of Tables


I have the following challenge.

Joe

Table_1 (5 rows) Table_2 (6 rows) Table_3 (2 rows)

Jane

Table_2 (7 rows) Table_3 (4 rows)

Report

Creator #Tables #Rows

Joe 3 13

Jane 2 11

I want to aggregate the number of tables and the number of rows in all tables for a particular user. There is a group of two dozen or so tables of interest. There are thousands of users. As you can see, everybody doesn't have every table.

So far I have explored:

1. CASE statement
2. Global Temporary Tables
3. Common Table Expressions (with and without recursion)
4. OLAP functions
5. and so on...

I can certainly write a program or use a brute force method, but I believe that this can be done with just SQL. The problem is accumulating the number of rows in only the tables that exist for a particular user.

Thoughts? Much obliged in advance.

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Venkat Sunil Chennur

RE: Aggregating Variable Sets of Tables
(in response to Frank Fillmore, Jr.)

This will create a sql’s to run the count(*) on all the tables, then insert into a temp table.

 

Temp table will have columns like this

     Creator            CHAR(8)        NOT NULL WITH DEFAULT,

      NAME               VARCHAR(128)   NOT NULL WITH DEFAULT,

     ROW_CNT            BIGINT         NOT NULL WITH DEFAULT,

     RECORDADDEDTIME    TIMESTAMP      NOT NULL WITH DEFAULT

 

select 'INSERT INTO temp table  (' ||  '&&'  || CREATOR || '&&' ||  ' , '

|| '&&' || NAME || '&&' ||  ' , '

|| 'count(*) , CURRENT TIMESTAMP from ' || STRIP(CREATOR) || '.' || NAME || ' ) ;'

from sysibm.systables

WHERE creator IN (all the schemas you need it like ‘Joe’, ‘Jane’)

and type = 'T'

 

Replace ‘&&’ with “’” so that you can execute the SQL.

 

Then you will run the below SQL on temp table to get the results

 

Select CREATOR, COUNT(NAME), SUM(ROW_CNT) from temp table

GROUP BY CREATOR

 

I hope this helps.

Michael Hannan

RE: Aggregating Variable Sets of Tables
(in response to Venkat Sunil Chennur)

So we see there are plenty of approaches possible. Bill's Stored Proc looping through dynamic SQLs for each table counted sounds good.  Venkat's SQL might need enhance to add the REPLACE function (to change && to quotes), and UNION ALL and ORDER BY to allow for Generated INSERT to take multiple lines (add some more STRIP functions). If DSNTIAUL, SUBSTR to convert to fixed length text output of 72 (with no length prefix).

I would probably include a first up SQL to use TOTALROWS, or TOTALENTRIES when available, to minimize need for the COUNTs, and determine which COUNTs need to be Prepared.

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd

Frank Fillmore, Jr.

RE: Aggregating Variable Sets of Tables
(in response to Michael Hannan)

First of all: many thanks to Bill, James, Michael, Nadir, Phil, Philip, Sam, and Venkat for taking the time to share your thoughts.

Second, additional constraints on the problem solution:

  1. This is an exercise in "cleaning out the attic" - specifically EXPLAIN tables.  Statistics are not current on most because they are not subject to periodic RUNSTATS.  Many are dormant because the original creator has left the enterprise.  The purpose in counting the rows is to determine the relative size to prune and prioritize dropping tables.  This is a Db2 for z/OS shop that has existed for decades which recently migrated to v12.  Since many EXPLAIN tables are not in v12 format, better to get rid of the obsolete ones than migrate something no one is using that is taking up a lot of space.
  2. Programmatically this would not be a problem except...
    1. I am trying to do this with just SQL because it is embedded in an Excel spreadsheet.  There are two dozen distinct Db2 subsystems.  I am scanning all simultaneously with one Excel sheet per subsystem.
    2. I probably don't have sufficient privileges to create persistent Db2 objects (i.e. a Stored Procedure) in Prod and near-Prod environments.

So here is what I have so far (remember, not everyone has all tables):

CREATE GLOBAL TEMPORARY TABLE EXPLAIN_TABLE_STATS_GTT
( DBNAME VARCHAR(24) NOT NULL,
CREATOR VARCHAR(128) NOT NULL,
NAME VARCHAR(128) NOT NULL,
TABLES INTEGER NOT NULL,
ROWS INTEGER ,
COUNT_STMT VARCHAR(100) NOT NULL);
--
INSERT INTO EXPLAIN_TABLE_STATS_GTT ( DBNAME, CREATOR, NAME, TABLES, COUNT_STMT )
SELECT DBNAME, CREATOR, NAME, 1, 'SELECT COUNT(*) FROM '||rtrim(creator)||'.'||rtrim(name)
FROM SYSIBM.SYSTABLES
WHERE NAME IN
( 'DSN_COLDIST_TABLE'
, 'DSN_DETCOST_TABLE'
, 'DSN_FILTER_TABLE'
, 'DSN_FUNCTION_TABLE'
, 'DSN_KEYTGTDIST_TABLE'
, 'DSN_PGRANGE_TABLE'
, 'DSN_PGROUP_TABLE'
, 'DSN_PREDICATE_SELECTIVITY'
, 'DSN_PREDICAT_TABLE'
, 'DSN_PTASK_TABLE'
, 'DSN_QUERYINFO_AUX'
, 'DSN_QUERYINFO_AUX2'
, 'DSN_QUERYINFO_TABLE'
, 'DSN_QUERY_AUX'
, 'DSN_QUERY_TABLE'
, 'DSN_SORTKEY_TABLE'
, 'DSN_SORT_TABLE'
, 'DSN_STATEMENT_CACHE_AUX'
, 'DSN_STATEMENT_CACHE_TABLE'
, 'DSN_STATEMNT_TABLE'
, 'DSN_STAT_FEEDBACK'
, 'DSN_STRUCT_TABLE'
, 'DSN_USERQUERY_TABLE'
, 'DSN_USERQUERY_TABLE_AUX'
, 'DSN_VIEWREF_TABLE'
, 'DSN_VIRTUAL_INDEXES'
, 'DSN_VIRTUAL_KEYTARGETS'
, 'PLAN_DATABASE'
, 'PLAN_TABLE' )
GROUP BY DBNAME, CREATOR, NAME;

A sample row:
DATABASE   CREATOR   NAME               TABLES   ROWS     COUNT_STMT

DSNDB06      FRANK        PLAN_TABLE              1  <NULL>   SELECT COUNT(*) FROM FRANK.PLAN_TABLE

How do I execute COUNT_STMT to populate ROWS?

Peter Vanroose

Re: Aggregating Variable Sets of Tables
(in response to Frank Fillmore, Jr.)

Frank,

The generated "SELECT COUNT(*) ..." queries are not directly useful as they stand, since running them will just give a bunch of counts, without reference to their table name (or database).

So instead of having four different columns in your output, you should just have a single output column, viz. the "SELECT COUNT(*)" statement, but with the other three fields concatenated into the SELECT part of the generated SELECT statements (just between "SELECT" and "COUNT").

Then you could just run all those SELECT statements, ideally combined into a single SQL statement with UNION ALL.

So the SELECT part of your big query would become something like:

SELECT DBNAME, CREATOR, NAME, 1, 'SELECT '''||dbname||''', '''||creator||''', '''||name|||''', COUNT(*)||';' FROM '||rtrim(creator)||'.'||rtrim(name)

The output of this query, possibly combined with UNION ALLs, would then, when executed, generate the 4-column output you need.

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://abis.be/


In Reply to Frank Fillmore, Jr.:

First of all: many thanks to Bill, James, Michael, Nadir, Phil, Philip, Sam, and Venkat for taking the time to share your thoughts.

Second, additional constraints on the problem solution:

  1. This is an exercise in "cleaning out the attic" - specifically EXPLAIN tables.  Statistics are not current on most because they are not subject to periodic RUNSTATS.  Many are dormant because the original creator has left the enterprise.  The purpose in counting the rows is to determine the relative size to prune and prioritize dropping tables.  This is a Db2 for z/OS shop that has existed for decades which recently migrated to v12.  Since many EXPLAIN tables are not in v12 format, better to get rid of the obsolete ones than migrate something no one is using that is taking up a lot of space.
  2. Programmatically this would not be a problem except...
    1. I am trying to do this with just SQL because it is embedded in an Excel spreadsheet.  There are two dozen distinct Db2 subsystems.  I am scanning all simultaneously with one Excel sheet per subsystem.
    2. I probably don't have sufficient privileges to create persistent Db2 objects (i.e. a Stored Procedure) in Prod and near-Prod environments.

So here is what I have so far (remember, not everyone has all tables):

CREATE GLOBAL TEMPORARY TABLE EXPLAIN_TABLE_STATS_GTT
( DBNAME VARCHAR(24) NOT NULL,
CREATOR VARCHAR(128) NOT NULL,
NAME VARCHAR(128) NOT NULL,
TABLES INTEGER NOT NULL,
ROWS INTEGER ,
COUNT_STMT VARCHAR(100) NOT NULL);
--
INSERT INTO EXPLAIN_TABLE_STATS_GTT ( DBNAME, CREATOR, NAME, TABLES, COUNT_STMT )
SELECT DBNAME, CREATOR, NAME, 1, 'SELECT COUNT(*) FROM '||rtrim(creator)||'.'||rtrim(name)
FROM SYSIBM.SYSTABLES
WHERE NAME IN
( 'DSN_COLDIST_TABLE'
, 'DSN_DETCOST_TABLE'
, 'DSN_FILTER_TABLE'
, 'DSN_FUNCTION_TABLE'
, 'DSN_KEYTGTDIST_TABLE'
, 'DSN_PGRANGE_TABLE'
, 'DSN_PGROUP_TABLE'
, 'DSN_PREDICATE_SELECTIVITY'
, 'DSN_PREDICAT_TABLE'
, 'DSN_PTASK_TABLE'
, 'DSN_QUERYINFO_AUX'
, 'DSN_QUERYINFO_AUX2'
, 'DSN_QUERYINFO_TABLE'
, 'DSN_QUERY_AUX'
, 'DSN_QUERY_TABLE'
, 'DSN_SORTKEY_TABLE'
, 'DSN_SORT_TABLE'
, 'DSN_STATEMENT_CACHE_AUX'
, 'DSN_STATEMENT_CACHE_TABLE'
, 'DSN_STATEMNT_TABLE'
, 'DSN_STAT_FEEDBACK'
, 'DSN_STRUCT_TABLE'
, 'DSN_USERQUERY_TABLE'
, 'DSN_USERQUERY_TABLE_AUX'
, 'DSN_VIEWREF_TABLE'
, 'DSN_VIRTUAL_INDEXES'
, 'DSN_VIRTUAL_KEYTARGETS'
, 'PLAN_DATABASE'
, 'PLAN_TABLE' )
GROUP BY DBNAME, CREATOR, NAME;

A sample row:
DATABASE   CREATOR   NAME               TABLES   ROWS     COUNT_STMT

DSNDB06      FRANK        PLAN_TABLE              1     SELECT COUNT(*) FROM FRANK.PLAN_TABLE

How do I execute COUNT_STMT to populate ROWS?

Edited By:
Peter Vanroose[Organization Members] @ Feb 19, 2020 - 08:57 AM (Europe/Brussels)

Michael Hannan

RE: Aggregating Variable Sets of Tables
(in response to Frank Fillmore, Jr.)

Frank,

Prod Explain tables are important, but user explains are not so important usually.  If not in V12 format, consider just rename them and create new V12 format tables (easier) with the right Auths. Can generate scripts to do it.

If User has dynamic explain rows older than 2 years, probably not needed, but send them all an email a few weeks before a cleanup. If static Package rows, could remove them if the Package Version no longer exists and older than x months. 

Remove all DSN explain table where rows in PLAN_TABLE don't exist. Assumes you have a good lookup index on PLAN_TABLE for the correlated subquery.

Cleanup tends to be too much work, so I agree don't bother unless they are over a certain large size. Just rename them and wait a couple of years for most cases.

Provide SQLs that can allow old rows to be copied to new tables.

If tables can be all fixed to V12 by merely ALTERS, then fine, not so hard to generate the ALTERs.

Going to V12 format, and doing cleanup, are really two independent issues. Why make it more complex by trying to combine them? 

I built my own Cleanup algorithms for Prod and important Explain tables (not for User tables), to make sure history of access path change is not lost. Complex, data we need is kept, and lots of redundant duplicates are not. 

Michael Hannan

 

 

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd