HDTenEightyP/NetNews huggingface dataset:
From: mjl@vax.oxford.ac.uk
Newsgroups: comp.databases.ingres
Subject: Re: HELP! SQL (Ingres) problem
Date: 16 Dec 92 16:27:07 GMT
Organization: Oxford University VAX 6620

In article <1992Dec13.111807.1@gsbacd.uchicago.edu>, cs_mj@gsbacd.uchicago.edu (Mark Jaeger) writes:
> 
> I may be barking up the wrong tree, but whenever I see weird behavior
                                          ^^^^^^^^
Does this mean you see _lots_ of weird behavior like this?

> like this, I suspect nulls.

I would agree, and this is why we always create tables NOT NULL. Our data
is managed in such a way that empty values are only permitted for
strings(and then NOT NULL). A value of -1 is standard for empty numeric values.

> 
> Can you supply us with full table definitions ("create table" statements
> from the "copydb" utility) and some sample data?

For what it is worth, I give below the relevant part of the file 
from unloaddb, explaining the two tables in question. I have deleted a 
number of variables from the create regi part to save space, which are 
not used here, but they are all created 'not null'.


set autocommit on \g
\sql
/* This is a SQL script. */

create table regi(
	regiindex_no integer not null not default,
	sex integer not null not default,
	birth_day integer not null not default,
	birth_month integer not null not default,
	birth_year integer not null not default,
	anniv_day integer not null not default,
	anniv_month integer not null not default,
	anniv_year integer not null not default,
	age_at_anniv integer not null not default,
	cr_number integer not null not default,
	registry integer not null not default,
	death_day integer not null not default,
	death_month integer not null not default,
	death_year integer not null not default,
	age_at_death integer not null not default,
....
irrelevant vars removed

	uk_reg_hosp integer not null not default,
	uk_reg_cons c4 not null not default,
	trial integer not null not default,
	uk_trl_study c4 not null not default,
	survey_no integer not null not default,
	trtmt_centre integer not null not default,
	hospital_cas varchar(25) not null not default
)
with duplicates,
location = (ii_database)
\p\g
copy regi () from 'du01:[nrctdump]regi.mjl'
\p\g
modify regi to btree unique on
	regiindex_no
with nonleaffill = 80,
        leaffill = 70,
        fillfactor = 80
\p\g

create table scots_hosp(
	hosp integer not null not default
)
with duplicates,
location = (ii_database)
\p\g
copy scots_hosp () from 'du01:[nrctdump]scots_ho.mjl'
\p\g


================================================================================
Martin Loach, Childhood Cancer Research Group, 
Belsyre Court, 57 Woodstock Rd, Oxford, OX2 6HJ, United Kingdom.
Voice : +44 865 310030 (operator)
Fax   : +44 865 514254
Local : MJL@OX.VAX
JANET : MJL@UK.AC.OX.CCRG
================================================================================
From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Newsgroups: comp.databases.ingres
Subject: Re: Ingres 6.4 scaling on Multiprocessor systems
Date: 13 Dec 92 17:24:34 GMT
Article-I.D.: gsbacd.1992Dec13.112434.1

In article <1992Dec10.042729.24340@sequent.com>, cyf@sequent.com 
(Cyrus Foughty) writes:
> What experiences has anyone had with the number of "iidbms" servers
> that can scale on multiprocessor unix systems? I would like it to 
> scale up to the 20's range. That may be asking a lot but I haven't
> tried it yet.

> Cy Foughty 
> Sequent Computer Systems, Dallas, TX  MaBell: 214.661.1900
> cyf@sequent.com
> Standard disclosure: This is my opinion and not Sequent's.  

What is it that you are trying to do?  I've talked to several high-level
dbms developers at the NAIUA, and I always hear this mantra: don't start
multiple servers unless you have a really good reason.  This is not to
say that there aren't good reasons.

One reason might be that the dbms server is cpu bound by the power of
one cpu.  If it spends all of its time in a computable state, then your
INGRES system may need more cpu bandwidth, which you could then get by
adding another server.

Don't forget to turn off -sole_server, and if you're using -fast_commit
then you'll need to use a shared cache.  See the I & O Guide for more
details.  Also, don't forget that each server has its own slave
processes to do the i/o, so make sure your system can handle the process
count.

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Ich bin ein Virus.  Mach' mit und kopiere mich in Deine .signature.
Newsgroups: comp.databases.ingres
From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: "Blobs" in Ingres
Organization: 
Date: Sun, 13 Dec 1992 16:49:21 GMT

In article <1992Dec9.192558.2195@quintus.com>, nair@quintus.com (Anil Nair)
writes:
> As far as I could see Ingres does not support any arbitrarily large
> datatypes like "blobs". But I did read a small chapter in my
> Installation guide which talks about some product called the
> "Object Management Extension"? Apparently it can be used to
> define your own abstract data types. Does this mean that I can
> define my own "blob" datatype for Ingres? What kind of datatypes
> can I define using this OME?

You can define anything you like.  Essentially, you write code that gets
linked into the dbms server so that it understands a version of SQL that
is enhanced for your needs.

This is not trivial and should not be undertaken lightly.  In
particular, if you want blobs, then you have several choices.

o Work around the INGRES limitation of circa 2000 bytes per row by
putting one blob datum in multiple rows.  Many folks do this now.  Have
a look at some of the INGRES system catalogs for examples: ii_encodings,
ii_encoded_forms, ii_longremarks.

o Implement the blob-handling code in the front end instead of the back
end.  For example, have the database hold the name of a file, where the
file holds the blob data.  Data integrity could be an issue here.

o Wait for Ingres to implement blobs.  To find out how long you'll have
to wait, talk to your salesman.

o Buy the object manager and write your own server blob code.

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Ich bin ein Virus.  Mach' mit und kopiere mich in Deine .signature.

Newsgroups: comp.databases.ingres
From: tmack@cbnewsd.cb.att.com (thomas.p.mack)
Subject: How to calc time spans excluding weekends
Organization: AT&T
Date: Tue, 15 Dec 1992 18:50:37 GMT
Keywords: date arithmetic

We currently use Ingres release 5 and have a need to calculate time
spans but the spans cannot include weekends.  Does anyone know how to do
this in Ingres or have a "C" program that can do this.

Thanks for any help in advance.  Please send any suggestion/code fragments
via email.  I will post a summary if requested.

Tom Mack  AT&T Bell Laboratories  att!ihspb!tmack  708-979-6125
Newsgroups: comp.databases.ingres
From: kitchel@manta.dpsi.com (Sidney W. Kitchel)
Subject: Windows 4GL vs. ESQL interfaces
Summary: Request for information about ESQL and Windows 4GL interfaces.
Keywords: ESQL, Windows 4GL, database interface.
Organization: Data Parallel Systems, Inc
Date: Mon, 14 Dec 1992 14:57:00 GMT

	Ingres offers several options for application development. Two
of them are Windows 4GL and embedded SQL (ESQL) in languages like C 
and Cobol. Both the 4GL and ESQL approachs involve preprocessors. Are
the the resulting interfaces essentially the same?? I.e. do the
preprocessed codes behave in the same way by using the same library of
primitive Ingres calls and hooking up to program variables in the same
protocol??
	We have some experience with ESQL application development and
would like to know more about the behavior of 4GL before we do any
development in it.
							--Sid

--
Sidney W. Kitchel  	    kitchel@cs.indiana.edu, kitchel@dpsi.com
Data Parallel Systems, Inc.  ============||  DPSI  ||===============
4617 E. Morningside Drive	                      (812) 334-8100
Bloomington, Indiana,  47408  USA		FAX:  (812) 334-8121
From: map@sequent.com (Michael Perry)
Newsgroups: comp.databases.ingres
Subject: Re: Raw Datafiles on Ingres??
Date: 15 Dec 92 20:43:07 GMT
Article-I.D.: sequent.1992Dec15.204307.17021
Organization: Sequent Computer Systems, Inc.

In article <9212141947.AA12498@valandil.YP.sun386i> melkor!valandil!mal (Michael A Leo) writes:
>    In article <rule-091292172916@17.9.6.35> rule@apple.com (Jeff Rule) writes:
>    >In article <1992Dec9.172402.3908@sequent.com>, map@sequent.com (Michael
>    >Perry) wrote:
>    >> However, you can turn on Direct I/O by using:
>    >> 
>    >>    ingsetenv II_DIRECT_IO y
>    >> 
>    >> and restarting your server(s). Tthis will help your file-system I/O.
>    >> 
>    >
>Alas, I called Ingres tech support, and this only works on Sequent machines.
>As it indicates, Ingres will bypass the file system.  According to Ingres
>the performance increase is quite substantial.  
>
>Could this be why Ingres benchmarks so well on Sequent ;-?
>
>Mike Leo
>mike.leo@adc.com

Oh, it's one of many reasons...   :)

Mike
-- 
    "Life's a bench, then you die!"
From: eaton@pacersoft.com (Mark W. Eaton)
Newsgroups: comp.databases.ingres
Subject: Re: OBDC from Ingres ?? Open .. Database .. something..???
Date: 17 Dec 92 18:34:42 GMT
Followup-To: comp.databases.ingres
Distribution: na
Organization: Pacer Software, Inc.

In article <BzEq8C.48o@usenet.ucs.indiana.edu>,
gribble@bronze.ucs.indiana.edu (steve gribble) wrote:
> 
> What Ingres is writing is the Ingres ODBC driver.
> ODBC (Open Database Connectivity) is Microsoft's specification
> for a SQL Call-Level Interface.  It is a superset of the SQL Access
> Group's proposed spec.  MS is a member of SAG and has submitted ODBC
> to the group for consideration.
> 
> What this means for the developer is they have a single SQL API to program to.
> For the end-user it provides a way for any Windows application that writes
> to the ODBC spec to access any database for which a driver has been
> written.  The stack on a PC for Ingres looks vaguely like this:
> 
> Win App
> ODBC Manager
> Ingres ODBC Driver 
> Ingres/Net
> 
> Yes, you still need Ingres/Net.  The only application that currently supports
> ODBC as far as I know is Access from MS.  There will be many others soon though.
> Early next year you should be able to use Excel to get directly at Ingres data.
> 

Actually, you can get at Ingres data directly from Excel/Mac today using
the following solution (stack as above):

Client Mac:

Excel
Data Access Manager
DALnet MacTCP adapter
MacTCP

Ingres server:

DAL server
Ingres RDBMS

When the ODBC support is completed by Apple/Microsoft, there will also be
an ODBC DAL driver which will access DAL servers as well as the native
RDBMS servers.  Note that the DAL solution is transparent to the underlying
RDBMS, unlike the vendor-specific solution mentioned by Steve. 

Mark W Eaton
Pacer Software, Inc.
eaton@pacersoft.com

Disclaimer: I work for Pacer Software, Inc. and develop Unix-based
AppleTalk
and DAL products
From: map@sequent.com (Michael Perry)
Newsgroups: comp.databases.ingres
Subject: Re: Raw Datafiles on Ingres??
Date: 15 Dec 92 20:44:38 GMT
Article-I.D.: sequent.1992Dec15.204438.17079
Organization: Sequent Computer Systems, Inc.

In article <BzAsJG.Bv6@hpwin052.uksr.hp.com> glennm@hpman010.uksr.hp.com (Glenn Mackie) writes:
>Michael A Leo (melkor!valandil!mal) wrote:
>:     In article <rule-091292172916@17.9.6.35> rule@apple.com (Jeff Rule) writes:
>:     >In article <1992Dec9.172402.3908@sequent.com>, map@sequent.com (Michael
>:     >Perry) wrote:
>:     >> However, you can turn on Direct I/O by using:
>:     >> 
>:     >>    ingsetenv II_DIRECT_IO y
>:     >> 
>:     >> and restarting your server(s). Tthis will help your file-system I/O.
>:     >> 
>:     >
>	Are there any data integrity issues associated with this method??
>	Do Sequent and Ingres users employ this method in a live environment
>	or is it purely for benchmarking?
>
>	Just wondering
>
>	Glenn Mackie

No problemsat all.  It's fully supported in the kernel and is used by other
DBMSs besides Ingres.

Mike
-- 
    "Life's a bench, then you die!"
Xref: sparky comp.databases.ingres:2088 comp.infosystems:639
From: mff@ukc.ac.uk (M.F.Fuller)
Newsgroups: comp.databases.ingres,comp.infosystems
Subject: Departmental (Graduate) Student Databases: Experiences sought
Date: 14 Dec 92 12:38:22 GMT
Organization: Canterbury Business School, University of Kent, Canterbury, UK

This is posted on behalf of a colleague who does not currently have net access
while a new departmental network is being installed.

He is interested in experiences using Ingres, either running under Unix, or on
a 486 based network using Novell, for departmental administrative tasks such as
graduate admissions and graduate student databases, including coursework and
examination results. Our circumstances are complicated by multiple overlapping
cohorts of full and part time MBA students - the part timers may well take 5
years to complete the programme.

Please email replies to me - databases are not my field, and I do not read
these groups.

Mike Fuller
Canterbury Business School, University of Kent, Canterbury, Kent, CT2 7PE,
England. Phone +44 227 764000 x.7729; fax +44 227 761187; email mff@ukc.ac.uk
From: cortes@enuxva.eas.asu.edu (Enrique Cortes-Rello)
Newsgroups: comp.databases.ingres
Subject: prefetchrows
Date: 16 Dec 92 19:20:34 GMT
Organization: Arizona State University

There is an environment variable called
II_EMBED_SET,that allows programmers to set
a bunch of features. One of the possible
actions is:

prefetchrows, that takes the values:
 0 (default) INGRES decides the number of
   rows to pre-fetch
 1 disables prefetching
 n specifies the number of rows to be prefetched.

Now, the questions:

(1). What is the meaning of "prefetch" ?
(2). Is this some buffering mechanism ??
(3). What are the performance implications?
(4). How to use?

thanks,

Enrique

---------------------------------------------------------
     Enrique Cortes-Rello  <cortes@asuvax.eas.asu.edu>
         ASU Intelligent Information Systems Lab
      <<<  "Aprendiz de todo, oficial de nada"  >>>
From: dominick@protocol.zycad.com (Dom Paniscotti)
Newsgroups: comp.databases.ingres
Subject: Re: Windows 4GL vs. ESQL interfaces
Date: 15 Dec 1992 10:42:41 -0500
Organization: Protocol, a Division of ZYCAD Corporation
Distribution: world
Reply-To: dominick@protocol.zycad.com
NNTP-Posting-Host: hayaku.protocol.zycad.com
Keywords: ESQL, Windows 4GL, database interface.


In article <BzB2H3.1wu@news.water.ca.gov>, stevec@stimpy.water.ca.gov (Steve Croft) writes:
|> Cathy Savino writes:
|> 
|> |> Windows4gl is an a GUI application generator. If you run it under
|> |> X/Motif, it creates X applications. Under decwindows in creates decwindows 
|> |> applications. ESQL/C creates character based applications.
|> 
|> That statement may be a little simplistic.  You can have a GUI with
|> ESQL/C; you simply have to program it yourself.  Windows4gl provides
|> one for you as part of the package.  Using ESQL/C with GUI libraries
|> is no big deal.  I have written small utilities using SunSoft's
|> OpenWindows Developer's Guide and ESQL/C.  You get a nice Open Look
|> front end.
|> 
|> Of course if you want INGRES' 4GL tied to a GUI, then windows4gl is the
|> way to go.
|> 
|> Steve

I may be true that Inges Windows/4GL is tied (heavily) to the GUI, but for 
someone who needs to rapidly develop an X based database application it's 
probably the best way to go.  I've also found it to be a good way to
rapid prototype an application (you get functional windows very quickly).
However, given enough time and money, I would have to say I would develop
my application using ESQL/C and and a good GUI development package.  I think
(IMHO) the later would produce a much more maintainable application 
than one written in W4gl.
-- 
-- 
Dom Paniscotti                  internet: dominick@protocol.zycad.com
Protocol, a Division of ZYCAD   phone:    (201) 347-7900            
500 International Drive         fax:      (201) 347-0303        
Mt. Olive, NJ 07828
From: bpeters@oasys.dt.navy.mil (Brenda Peters)
Newsgroups: comp.databases.ingres
Subject: Re: Suspicion of bug in ingres sql
Date: 18 Dec 92 17:39:14 GMT
Reply-To: bpeters@oasys.dt.navy.mil (Brenda Peters)
Organization: Carderock Division, NSWC, Bethesda, MD



I just ran into this yesterday!!!   I changed and used 'not exist'
instead of 'not in'.  I was thinking maybe 'not in' only returned true
if it was an indexed field.  I tore my query down into fragments to
see where it was having problems, and was on the verge of customer
support when I decided to try 'not exist'.

Good luck!!!   
					brenda
From: roman@maths.uwa.oz.au (Roman Bogoyev)
Newsgroups: comp.databases.ingres
Subject: Network comms info wanted
Followup-To: comp.databases.ingres
Date: 18 Dec 1992 10:32:22 GMT
Organization: University of WA
Distribution: world
NNTP-Posting-Host: mac165.maths.uwa.oz.au

Has anyone any information on the protocol/commands the ULTRIX/SQL NET
communications server uses to talk through its port defined at its Listen
Address?  

I suspect this is not ULTRIX dependent, and that it is the same for 'real'
INGRES NET servers.  Any relevant information will be much appreciated.

Thanks!

Roman.



 
Newsgroups: comp.databases.ingres
From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: Finding maximum value of a column ?
Organization: 
Date: Fri, 11 Dec 1992 20:31:26 GMT

In article <1797@niktow.canisius.edu>, pavlov@niktow.canisius.edu 
(Greg Pavlov) writes:
> In article <1992Dec8.013622.1@gsbvax.uchicago.edu>, 
> cs_mj@gsbvax.uchicago.edu (Mark Jaeger) writes:
>> 
>> Right, it's slow.  If you look at the QEP, you will see that INGRES uses
>> a secondary index when such use would reduce the i/o's, but even so it
>> always has to do a full scan of either the table or the secondary index
>> in order to find the max.
>> 
>   I do not remember all of the details, but I believe that a key criteria was
>   that if the optimizer believed that more than 1/4 of the total rows needed
>   to be looked at, the base table would be traversed rather than the index
>   first.

I don't know all of the details either, but I do know that I've seen the
optimizer behave as I stated.  I discovered it while trying to diagnose
some index corruption problems.  I did this:

		select count(tid) from base_table
		union select count(tidp) from index_table
		union select count(i.tidp) from base_table t , index_table ix 
			where t.tid = ix.tidp

as suggested by the release notes for some version of 6.3/03.  When I
examined the QEP for this, I discovered to my dismay that INGRES
executed the first query by counting the rows in the smallest secondary
index on the table, rather than going to the base table!  Needless to
say, we had to find another way of diagnosing the corruption problem.

Your situation may be different.

I'd also like to say that the INGRES optimizer is really good, and
everything I've heard puts it really ahead of the crowd in this field.

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Ich bin ein Virus.  Mach' mit und kopiere mich in Deine .signature.
From: felicia@csd4.csd.uwm.edu (Chu Peng Cheong)
Newsgroups: comp.databases.ingres
Subject: OBDC from Ingres ?? Open .. Database .. something..???
Date: 16 Dec 1992 22:40:48 GMT
Organization: Computing Services Division, University of Wisconsin - Milwaukee
Distribution: na
NNTP-Posting-Host: 129.89.7.4
Originator: felicia@csd4.csd.uwm.edu


Does anyone know what the OBDC library/software from Ingres is? 
We are being told by someone from Ingres that OBDC is the way to allow
access to multiple/independet database engines.  If that is true, how
does it work, whats involve...? 

Perhaps, someone has white paper or ref. to this from Ingres?

Any help is appreciated, thanks in advanced.


-Felicia Cheong
felicia@csd4.csd.uwm.edu
-------------------------------------------------------------------------
Felicia C Cheong			email:  felicia@csd4.csd.uwm.edu
P.O. Box 1514					felicia@miller.cs.uwm.edu
Milwaukee, WI 53201
Voice#: (414) 797-6787
Fax#:   (414) 797-6731
Newsgroups: comp.databases.ingres
From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: Query optimization problems
Organization: 
Date: Sun, 13 Dec 1992 17:32:44 GMT

In article <1992Dec10.155740.7166@cbfsb.cb.att.com>, danj@cbnewsg.cb.att.com 
(Dan Jackson) writes:
> From article <921208003037.1717@ILP.MIT.EDU>, by GOLD@ILP.MIT.EDU 
> (Mark D. Gold):
>>             select publication.pub_id, temp_faculty_list.empid
>>               from author,
>>                    author_publication,
>>                    publication,
>>                    temp_faculty_list
>>              where author.empid = temp_faculty_list.empid
>>              and   author.author_id = author_publication.author_id
>>              and   author_publication.pub_id = publication.pub_id
>>              and   publication.pub_status = 'A';
> 
> Mark, I am assuming you have run optimizedb on the publication table for
> the "pub_status" field.  If you did not, the optimizer assumes that the
> exact match of pub_status = 'A' will return ONE row only.

Not so.  OPF assumes that exact value restrictions will return 1% of the
rows, and range restrictions (e.g., xcol > 10 or char_col like 'abc%')
will return 10% of the rows.  Thus the estimated returned rows will
depend on the rowcount for the table that is stored in the iitables
system catalog.

Close examination of a QEP will verify these numbers.  Sometimes it
amazes me, but then I realize that it's just another computer program
following the rules.

Look at the DBA Guide, in the chapter on "Optimizing Performance".

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Ich bin ein Virus.  Mach' mit und kopiere mich in Deine .signature.

Newsgroups: comp.databases.ingres
From: stevec@stimpy.water.ca.gov (Steve Croft)
Subject: Re: Windows 4GL vs. ESQL interfaces
Keywords: ESQL, Windows 4GL, database interface.
Organization: California Department of Water Resources
Date: Tue, 15 Dec 1992 14:48:38 GMT

Cathy Savino writes:

|> Windows4gl is an a GUI application generator. If you run it under
|> X/Motif, it creates X applications. Under decwindows in creates decwindows 
|> applications. ESQL/C creates character based applications.

That statement may be a little simplistic.  You can have a GUI with
ESQL/C; you simply have to program it yourself.  Windows4gl provides
one for you as part of the package.  Using ESQL/C with GUI libraries
is no big deal.  I have written small utilities using SunSoft's
OpenWindows Developer's Guide and ESQL/C.  You get a nice Open Look
front end.

Of course if you want INGRES' 4GL tied to a GUI, then windows4gl is the
way to go.

Steve
Newsgroups: comp.databases.ingres
From: bbrown@gandalf.ca (Bruce Brown)
Subject: Subscription
Organization: National Radio Astronomy Observatory
Date: Mon, 14 Dec 1992 19:01:18 GMT



  Please UN-SUBSCRIBE me as      BBROWN@GANDALF.CA


  Then

  Please subscribe me as         BB_VV@GANDALF.CA
  ( Simplifies mail forwarding within gandalf ).

  Thanks much
  BB
Newsgroups: comp.databases.ingres
From: CSTACEY@HARDY.MIT.EDU
Subject: users list
Organization: National Radio Astronomy Observatory
Date: Fri, 11 Dec 1992 20:19:21 GMT


Please add my account to your user's list.

Thank you 
Christine Stacey
Newsgroups: comp.databases.ingres