Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

how to know the number of rows in each partition?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Tue Aug 03, 2010 8:19 pm    Post subject: how to know the number of rows in each partition?
Reply with quote

hello,

once a week i need to know the number of rows in each partition of table 'a'.

we are currently running an unload and counting the number of rows, but it takes too long and uses too much disk space.

can i read the sysibm table that contains the db2 statistics for table 'a' and get the information i need from it? how reliable are those statistics? what's the name of the sysibm table, pls? SYSTABSTATS?

when an unload is ran, a report is written in the sysout (job listing). among many things, it shows the number of rows in each partition. can i execute an unload on table 'a' without writting the output (would use nullfile for the output), but saving the job listing in a file so i can read it with a dfsort of cobol program, for example, and get the information i need?

thanks.
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Aug 04, 2010 4:27 pm    Post subject:
Reply with quote

Hi jctgf,

Quote:
we are currently running an unload and counting the number of rows, but it takes too long and uses too much disk space.
You must have misunderstood something, here. Unload utility is used to extract data from a table to a sequential file.

Quote:
Can i read the sysibm table that contains the db2 statistics for table 'a' and get the information i need from it? how reliable are those statistics? what's the name of the sysibm table, pls? SYSTABSTATS?

If you like to know of the statistics, keep your statistics update-to-date by running RUNSTATS on the table. And query the Catalog, table is SYSTABLES and column CARDF.

Quote:
when an unload is ran, a report is written in the sysout (job listing). among many things, it shows the number of rows in each partition. can i execute an unload on table 'a' without writting the output (would use nullfile for the output), but saving the job listing in a file so i can read it with a dfsort of cobol program, for example, and get the information i need?

Can i know why knowing the number of rows is soo important ? Is there any business reason for this ?


For catalog information read, DB2 V9 SQL REFERENCE

Thank You,
Sushanth
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Wed Aug 04, 2010 5:08 pm    Post subject:
Reply with quote

Craig Mullins has a good article on this.
look for 'Basic Table Space Information'; he has a ready made SQL for this info.

though I would, if I were you, follow Sushanth's advice and
Back to top
View user's profile Send private message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Wed Aug 04, 2010 5:43 pm    Post subject:
Reply with quote

Hi,

Thanks a lot.

The table is very big and the common way of partitioning the table, that is, using static criteria, never worked well.

Every month a partition used to reach its limit. They had to change the partition criteria and move rows from one partition to the other in order to be able to use the table.

A column, partition-nmbr, was created to determine in which partition a given row was going to be stored. All rows of partition 1 have the value 001 in this column and the equivalent happens for the other partitions. Before loading the record into the table (the rows are loaded, not inserted), the value of this column is determined by program1.

In order to know how many rows are there in each ptt, a batch routine (unload+sort) is ran periodically and generates an auxiliary file. This file looks like this:

Ptt rows
nmbr qtty
001 2,987,564
002 3,000,345
003 1,436,782
...

This auxiliary file is the one used by program1, mentioned above, to decide where the row is going to be stored.

As far as we know, this is the most accurate way to know the actual number of rows in each partition.

We could also run a "select count(ptt-number)" but this table contains billions of records and it would take longer than the process we have today.

The problem with the current routine is that a lot of disk space has been used.

Now they are considering giving up the accuracy a little bit in order to speed up the process.

That's why they are thinking about using the db2 statistics, despite being aware they are not very precise.

Thanks.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Wed Aug 04, 2010 5:57 pm    Post subject:
Reply with quote

I provided the link to that article, because Craig discusses proper design of partition tables,
which your site seems to lack.

also, a count would take far less time than an unload, especially if you used dsntep2 or 4, which would generate a parse-able report.

and statistics are only as valid as the maintenance provides.
it sounds as if your site is trying to run on the cheap.
suggest you get some help, or as I said, read some manuals.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Aug 04, 2010 6:01 pm    Post subject:
Reply with quote

Hi,

Quote:
Every month a partition used to reach its limit. They had to change the partition criteria and move rows from one partition to the other in order to be able to use the table.

WOW. So, this is how manual approach looks like. Thank you very much for that infomation.

By the way, What Version of DB2 are you using, if its DB2 V8 and above. Try reading about REBALANCE in REORG, Utility Reference & Guide that's the book.

If you are on V9, you can also look into Partition-By-Growth of UTS as an option.

Thank You,
Sushanth
Back to top
View user's profile Send private message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Thu Aug 05, 2010 8:35 pm    Post subject: Reply to: how to know the number of rows in each partition?
Reply with quote

hi,
db2 here is 9.1.0.
this table contains 6.836.683.493 records and the rec size is 210 bytes.
what we've seen here is that even the newest features of db2 can not manage properly tables of this size.
thanks.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Aug 05, 2010 8:45 pm    Post subject:
Reply with quote

Hi jctgf,

How many partition do you have for this table ?

How often do you re-partition this table and can i know the DDL of this tablespace ?

Quote:
what we've seen here is that even the newest features of db2 can not manage properly tables of this size.


What did you try ?

Sushanth
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Thu Aug 05, 2010 8:59 pm    Post subject:
Reply with quote

Quote:
what we've seen here is that even the newest features of db2 can not manage properly tables of this size.


left off from the quote:

when we do not bother to use any intelligent method of partitioning.

jctgf,
you may think you have a lot of rows,
but you are only in the top 15% of size.
there are other sites that have 10 times the number of rows.

I have pointed you to links that you should re-read.
basically, your partitioning algorithm is the problem.
suggest that you invest some time and learn about 'intelligent partitioning'.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 pm
No new posts Edit large number of datasets (QSAM) zh_lad TSO/ISPF 3 Tue Apr 04, 2017 6:08 pm
No new posts how to mask the phone number kumarinfy DB2 4 Mon Apr 03, 2017 5:23 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts what is meaning of the number in dcl Martylin PL/I & Assembler 2 Thu Mar 09, 2017 7:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us