Re: Occasional heading output with dbaccess

On Mon, 26 Apr 2004 19:13:49 -0400, David E. Grove wrote: > I have a file, "tab_file", containing a list of table names. This list > contains only tables which actually have a column named "parm". For each > table in tab_file, I want to display the name of the table and all rows with > parm=1234. > > The script below works: > > for i in `cat tab_file` > do > echo $i > dbaccess otrk << EOT!! 2>/dev/null > SELECT * > FROM $i > WHERE parm = 1234> EOT!! > done > > However, sometimes (unnecessary) header information is displayed. Many of > the tables listed in "tab_file" do not have any rows with parm=1234. Most > of the time, when a table fails to have a row with parm=1234, nothing is > displayed except the table name and a blank line. However, sometimes, in > addition to the table name, the column headers are also displayed. Of > course, there is no row data to display when there are no rows with > parm-1234, so I still get the blank line. But (for those tables with no > qualifying rows), occasionally, the headers are also displayed (I'd estimate > less than 10% of the time), while most of the time the headers are not > displayed. > > Of course, I can remove the headers completely by adding the line "without > headings" to the script, but I do want the headingss when there are > qualifying rows. > > The thing that has me scratching my head (getting even more bald) is, for > tables with 0 qualifying rows, why are the headers displayed sometimes, but > not other times? I'd expect that the headers would either always be there, > or never be there. Yes, it's a quirk in dbaccess. IB that it happens when the estimated number of rows is non-zero when dbaccess prepares the cursor, but there are actually no matching rows when the cursor is opened. Try using Jonathan Leffler's sqlcmd instead of dbaccess, its behavior is more consistent. You can get the sqlcmd package from the IIUG Software Repository. Art S. Kagel

Thank you, Art. Gee, I wonder if an estimate of >0 rows returned in result set (when realized answer is 0) indicates need for 'update statistics', or is just a vagary of the fact that an estimate really is an estimate (you can't compute the answer without computing the answer). Or could be both, I guess. "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:pan.2004.04.27.09.38.09.962833.2240@bloomberg.net... > On Mon, 26 Apr 2004 19:13:49 -0400, David E. Grove wrote: > > > I have a file, "tab_file", containing a list of table names. This list > > contains only tables which actually have a column named "parm". For each > > table in tab_file, I want to display the name of the table and all rows with > > parm=1234. > > > > The script below works: > > > > for i in `cat tab_file` > > do > > echo $i > > dbaccess otrk << EOT!! 2>/dev/null > > SELECT * > > FROM $i > > WHERE parm = 1234> > EOT!! > > done > > > > However, sometimes (unnecessary) header information is displayed. Many of > > the tables listed in "tab_file" do not have any rows with parm=1234. Most > > of the time, when a table fails to have a row with parm=1234, nothing is > > displayed except the table name and a blank line. However, sometimes, in > > addition to the table name, the column headers are also displayed. Of > > course, there is no row data to display when there are no rows with > > parm-1234, so I still get the blank line. But (for those tables with no > > qualifying rows), occasionally, the headers are also displayed (I'd estimate > > less than 10% of the time), while most of the time the headers are not > > displayed. > > > > Of course, I can remove the headers completely by adding the line "without > > headings" to the script, but I do want the headingss when there are > > qualifying rows. > > > > The thing that has me scratching my head (getting even more bald) is, for > > tables with 0 qualifying rows, why are the headers displayed sometimes, but > > not other times? I'd expect that the headers would either always be there, > > or never be there. > > Yes, it's a quirk in dbaccess. IB that it happens when the estimated number > of rows is non-zero when dbaccess prepares the cursor, but there are actually no > matching rows when the cursor is opened. > > Try using Jonathan Leffler's sqlcmd instead of dbaccess, its behavior is more > consistent. You can get the sqlcmd package from the IIUG Software > Repository. > > Art S. Kagel

On Tue, 27 Apr 2004 12:30:49 -0400, David E. Grove wrote: A combination of likely requiring a new set of stats and the understandable and unavoidable vaguery (SP?) of making an estimate based on histogram counting of key ranges rather than exact counts of every key value. That's why sometimes you get headers and sometimes you do not, depending on the particular key values and how evenly distributed the actual keys are within the buckets. Art S. Kagel > Thank you, Art. > > Gee, I wonder if an estimate of >0 rows returned in result set (when > realized answer is 0) indicates need for 'update statistics', or is just a > vagary of the fact that an estimate really is an estimate (you can't compute > the answer without computing the answer). Or could be both, I guess. > > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:pan.2004.04.27.09.38.09.962833.2240@bloomberg.net... >> On Mon, 26 Apr 2004 19:13:49 -0400, David E. Grove wrote: >> >> > I have a file, "tab_file", containing a list of table names. This list >> > contains only tables which actually have a column named "parm". For > each >> > table in tab_file, I want to display the name of the table and all rows > with >> > parm=1234. >> > >> > The script below works: >> > >> > for i in `cat tab_file` >> > do >> > echo $i >> > dbaccess otrk << EOT!! 2>/dev/null >> > SELECT * >> > FROM $i >> > WHERE parm = 1234>> > EOT!! >> > done >> > >> > However, sometimes (unnecessary) header information is displayed. Many > of >> > the tables listed in "tab_file" do not have any rows with parm=1234. > Most >> > of the time, when a table fails to have a row with parm=1234, nothing is >> > displayed except the table name and a blank line. However, sometimes, > in >> > addition to the table name, the column headers are also displayed. Of >> > course, there is no row data to display when there are no rows with >> > parm-1234, so I still get the blank line. But (for those tables with no >> > qualifying rows), occasionally, the headers are also displayed (I'd > estimate >> > less than 10% of the time), while most of the time the headers are not >> > displayed. >> > >> > Of course, I can remove the headers completely by adding the line > "without >> > headings" to the script, but I do want the headingss when there are >> > qualifying rows. >> > >> > The thing that has me scratching my head (getting even more bald) is, > for >> > tables with 0 qualifying rows, why are the headers displayed sometimes, > but >> > not other times? I'd expect that the headers would either always be > there, >> > or never be there. >> >> Yes, it's a quirk in dbaccess. IB that it happens when the estimated > number >> of rows is non-zero when dbaccess prepares the cursor, but there are > actually no >> matching rows when the cursor is opened. >> >> Try using Jonathan Leffler's sqlcmd instead of dbaccess, its behavior is > more >> consistent. You can get the sqlcmd package from the IIUG Software >> Repository. >> >> Art S. Kagel