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?
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
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:
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
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.
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.
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.
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
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'.
