HDTenEightyP/NetNews huggingface dataset:
From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: Need help from SQL wizards out there!
Organization: 
Date: Fri, 16 Oct 1992 16:31:03 GMT

In article <1992Oct14.170643.1@woods.ulowell.edu>, 
adamsm@woods.ulowell.edu (Mike Adams) writes:
> I have a simple question for all you SQL wizards out there as I haven't the
> experience as yet to solve this problem.  I have three tables BIO,EMP,DON all
> indexed by unique social secutity number (SSNUM).  How can I select the records
> that are in BIO where there exists the same SSNUM in EMP but not include the
> records that have the same SSNUM in DON.  I tried:
> 
> select BIO.SSNUM
> from   BIO,EMP
> where 	   BIO.SSNUM=EMP.SSNUM
>       and  BIO.SSNUM not in (select DON.SSNUM
>                              from   DON);
> 
> This looks logial but dosn't seem to work as it always produces an empty list.

I have attached a simple script and its log.  It demonstrates that your
query works as expected if the id column is not nullable in all three
tables.  An id column probably shouldn't be nullable in any case.  If
the SSN column must be nullable because you don't know its value for
some people, then it's a bad candidate for a key.

--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.

================================================================================
create table t1 ( id i4 not null ) \g
create table t2 ( id i4 not null ) \g
create table t3 ( id i4 not null ) \g

insert into t1 values ( 1 ) \g
insert into t1 values ( 2 ) \g
insert into t1 values ( 3 ) \g

insert into t2 values ( 1 ) \g
insert into t2 values ( 2 ) \g
insert into t2 values ( 4 ) \g

insert into t3 values ( 2 ) \g

select * from t1 \g
select * from t2 \g
select * from t3 \g

select t1.* 
from t1 , t2
where 1=1
and t1.id = t2.id
and t1.id not in ( select t3.id from t3 )
\g

================================================================================
select * from t1 \g
+-------------+
|id           |
+-------------+
|            1|
|            2|
|            3|
+-------------+
(3 rows)

select * from t2 \g
+-------------+
|id           |
+-------------+
|            1|
|            2|
|            4|
+-------------+
(3 rows)

select * from t3 \g
+-------------+
|id           |
+-------------+
|            2|
+-------------+
(1 row)

select t1.* 
from t1 , t2
where 1=1
and t1.id = t2.id
and t1.id not in ( select t3.id from t3 )
\g
+-------------+
|id           |
+-------------+
|            1|
+-------------+
(1 row)
From: gwc@stiatl.salestech.com (Geoff Corey)
Newsgroups: comp.databases.ingres
Subject: optimizedb ?
Keywords: optimizedb ingres technical
Date: 16 Oct 92 10:35:28 GMT
Article-I.D.: stiatl.1992Oct16.103528.16482
Organization: Sales Technologies, Inc.

I have a technical question on OPTIMIZEDB.  I have two databases on 
different machines with the same data and same indexes.  After running
optimizedb with the -zk option, I checked iistats and iihistogram.   
The tables were different in size between the two databases (I also ran
sysmod before looking).

What I would like to know is if other processes would populate iistats and
iihistogram other than optimizedb.  If not, does optimizedb change its
parameters due to machine size or DBMS parameters or what?  I can't seem
to find any documentation on what things affect or influence an optimizedb
run (other than the data in the tables it is running against).

Thanks in Advance,

Geoff Corey (Sales Technologies)

Internet   : gwc@salestech.com
Compuserve : 71241,427


 
Newsgroups: comp.databases.ingres
From: cathys@Ingres.COM (Cathy Savino)
Subject: Re: Help ......
Keywords: SQL
Reply-To: cathys@ingres.com (Cathy Savino)
Organization: Ingres, An ASK Company
Date: 9 Oct 92 19:40:50 GMT

In article <1992Oct9.105424.4911@ecrc.de>, badri@ecrc.de (Badri Pillai) writes:
|> Hi Ingres Gurus,
|> 
|> The follwing is my question:
|> 
|> I would like to create a relation Fullname_and_Addres_Id between two entity
|>   Fullname and Address, where I would like to use the TID of
|>   both entities..
|> 
|> If you can give me an SQL example, or tell me if it is OK to
|> do so, or its possible at all, would be great.
|> 
|> 
|> Thanks in advance,
|> 
|> badri
|> 

Just a warning. The use of TID's is NOT supported, because they may (and probably
will) change through the life of a table. It is not a good Idea to use TID's to
join 2 tables. It is a bit more work, and a bit more overhead, but maintaining 
your own key is a better (and more stable) idea.

 
--
Opinions: Mine, Mine, Mine!!!
--
Cathy Savino 
   ___     _____   __  __       Ingres, An ASK Company
  / _ \   / ____\  | |/ /       1080 Marina Village Parkway
 / /_\ \  \____ \  | |\ \       Alameda, Ca 94501
/_/   \_\ \_____/  |_| \_\      (510) 748-3524
                                cathys@ingres.com
Newsgroups: comp.databases.ingres
From: "XERXES: :OZMINSKI" <ozminski%xerxes.decnet@clvax1.cl.msu.edu>
Subject: Ingres & network traffic
Organization: National Radio Astronomy Observatory
Date: Tue, 6 Oct 1992 20:04:00 GMT

I am running Ingres 6.4 on a Sun Sparcstation with SunOS 4.1.1.

I created an ESQL/C procedure that opens a local database once and
then reads a single table, converts the data in chunks of 11 rows
(average) to another display format and then writes (inserts) the new
format to a second table.  A commit is done after each batch of 11.

The first table has 3 million rows and the second table ends up with
about 800,000.  

While the program is running the network traffic increases
dramatically, and when it stops the network traffic is reduced.

I'm not all that familiar with Unix, but an increase in network
traffic while accessing a local database seems odd to me.  Can someone
explain why Ingres would produce this network traffic.

Notes: The utility monitoring the network traffic was the standard
console utility.  The disks being accessed by Ingres and the ESQL/C
program were all local disks.

-Stephan
 
Stephan J. Ozminski
Assistant System Manager               Bitnet:    OZMINSKI@MSUKBS.BITNET
Michigan State University              Internet:  OZMINSKI%KBS.DECNET@CLVAX1.CL.MSU.EDU
W.K. Kellogg Biological Station
3700 E. Gull Lake Dr.
Hickory Corners, MI 49060
From: laut@alien.gici.com (Bill Laut)
Newsgroups: comp.databases.ingres
Subject: Steroids for your Transaction Logs
Date: 7 Oct 92 21:47:38 GMT
Organization: Gull Island Consultants, Inc.



I am contracted out to a client site that is running a fairly large INGRES
shop.  It consists of two VAX-9210s with 512mb each, and about 50gb of disk
space.  Typically, we have about 200 users logged in from all over the Corp.,
doing some fairly intensive database stuff.

I was going through and tuning the Cluster, when I discovered they had the
Transaction Log files being written to a single RA70 disk drive (:-}).
Subsequent VPA analysis revealed (surprise, surprise) that this single RA70
was proving to have really long I/O queues on it, and that it likely was
slowing down all of INGRES.

An idea occured to me, which I thought I would post here and see if anyone
would care to comment on it.  Since we are not really using all of that 512mb
of memory in both machines, I was thinking of whipping up a RAM disk driver
that would allocate its space out of non-paged pool (oh, say, 200mb worth)
and run in parallel on both 9210s.  These two RAM disks would then be 
MSCP-served to the Cluster, and shadowed together into a single disk volume
using host-based shadowing.  We would still use our venerable RA70 to
preserve the shadow image across cluster shutdowns, and if need be, (ugh)
extract the shadow image out of the system crash dump file should the entire
cluster crash.

Such a concept should likely eliminate the Transaction Log disk as a source
of bottlenecking (:-).  Any comments, critiques?  I'll be waiting...

-- 
Bill Laut			Internet:  laut@alien.gici.com
Gull Island Consultants, Inc.	Phone:     (616) 780-3321
P.O. Box 627
Muskegon, MI  49440		>> "Usual disclaimers, apply within" <<
Newsgroups: comp.databases.ingres
From: badri@ecrc.de (Badri Pillai)
Subject: Help ......
Keywords: SQL
Organization: European Computer-Industry Research Centre GmbH, Munich, Germany
Date: Fri, 9 Oct 1992 10:54:24 GMT

Hi Ingres Gurus,

The follwing is my question:

I would like to create a relation Fullname_and_Addres_Id between two entity
  Fullname and Address, where I would like to use the TID of
  both entities..

If you can give me an SQL example, or tell me if it is OK to
do so, or its possible at all, would be great.


Thanks in advance,

badri

--
Badri Pillai
European Computer-Industry Research Centre      Tel. + (49) 89-92699-119
Arabellastr 17, 8000 Muenchen 81. GERMANY.
Internet: badri@ecrc.de
From: horn@schaefer.math.wisc.edu (Jeffrey Horn)
Newsgroups: comp.databases.ingres
Subject: Termcap/Ingres Key Mappings - NCD 19r
Date: 15 Oct 92 15:40:23 GMT
Organization: Univ. of Wisconsin Dept. of Mathematics

Does anyone have appropriate termcap and ingres mappings files for
an xterm on a NCD 19r.  They keyboard I am using is vt220 like.  I would
like to make the function key mappings as close to the ones for vt220
as possible.

Thanks, 

Jeffrey Horn
Newsgroups: comp.databases.ingres
From: melkor!valandil!mal (Michael A Leo)
Subject: Transaction system reliability
Organization: National Radio Astronomy Observatory
Date: Tue, 6 Oct 1992 14:09:37 GMT


Hi,

  Just thought I would let folks know about
a little disclaimer of Ingres' I encountered
during a recent problem I had.

  Call Reference #197182

    Ingres Tech Support states:

      "If Ingres is not shut down normally, there
       is NO guarantee it will start up normally,
       even if no disk data is corrupted."

  Just a bit of background.  It seems I did not
sufficiently test my shutdown script I sent to our 
custome.

  When you call iishutdown, it returns a status as
to how it went.  If it returns zero, which is indicated
as OK in the header of the iishutdown script, IT DOES
NOT MEAN INGRES HAS SHUTDOWN SUCCESSFULLY!

  I repeat:  IT DOES NOT MEAN INGRES HAS SHUTDOWN SUCCESSFULLY!

  Ingres is still shutting down.  In MOST cases, you
may not notice this, because under normal circumstances, 
Ingres shuts down quite quickly.
 
  However, if something needs to be recovered, the dmfrcp
process may still be running.

  It is very dangerous to shut down the operating while 
the dmfrcp is still running.

  So, in any automatic system shutdown script, it is NOT
enough to call iishutdown, and upon zero return status, 
call the operating system shutdown.  You MUST make sure
dmfrcp has completely shut down.

  Note that 99% of the time this is not a problem, as 
the DBMS server will not shut down until it recovers all
transactions that were aborted, even by disconnected sessions.

  I am not sure what scenario causes the dmfrcp to take 
over the recovery process, and I can only simulate it by
killing the DBMS server via kill -KILL or stop/id, when the
DBMS server is recovering an aborted transaction.

  One other neato implication Ingres' statement has:

    1) You should not allow your operating system to 
       ever crash.  

    2) Your power supply must be perfect, and NEVER
       fail, under any circumstances.

    3) Your hardware must never fault, in any way.

    4) You must never shut down the operating system
       without shutting down Ingres first (and making
       sure it is down, as stated above).

   If you cannot guarantee all of the above, Ingres will not 
guarantee it can restart normally.

   To be fair, Ingres can and should not guarantee anything
if disk corruption occurs.

   Please call me if you can assure 1-4, as I would like to 
find out how.

Mike Leo
mike.leo@adc.com

P.S. Do not use your mailer's "reply" function as 
     my header is mulched.
From: acs_mn@rivers.acc.uwrf.edu
Newsgroups: comp.databases.ingres
Subject: Ingres on VMS with disk defragmenter
Date: 7 Oct 92 08:00:29 -0600
Organization: University of Wisconsin - River Falls

    I remember a long time ago there was a warning not to allow a disk
defragmenter to touch Ingres databases since the files weren't opened using
normal RMS means and the defragmenter might move an open file causing big
problems.

    Has this ever been fixed or it is still recommended to exclude database
from disk defragmentation?

    We are using INGRES VAX Version 6.4/01 (vax.vms/01) running on VAX/VMS
5.5-1. The disk defragmenter is Dec's File Optimizer.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Marlys A. Nelson			System Manager, Systems Programmer,
Academic Computing			Network Manager, etc., etc.
Univ. of WI - River Falls
Internet: Marlys.A.Nelson@uwrf.edu
Newsgroups: comp.databases.ingres
From: alan@curta.cc.columbia.edu (Alan Crosswell)
Subject: Re: hyphens not allowed in Unix usernames in Accessdb
Organization: Columbia University
Date: Wed, 7 Oct 1992 14:03:51 GMT

Since I recieved some mail asking for a followup if I got any answers,
here it is, thanks to Kenneth C. Rich of U of Rochester:

well this is vms, dcl and quel but you'll get the idea.
i just tried a hyphenated name and it worked fine.

IIDBDBADDQUEL.COM for Ingres r6.3/03/vaxvms01
-----------------
$ quel "-U" -u$ingres iidbdb
create tempiiuser (
        name=c24,
        gid=i2,
        mid=i2,
        status=i4,
        default_group=c24,
        reserved=c32
) \g
copy tempiiuser (name=c0) from "sys$sysdevice:[syswork.addusers]iiuser.dat"
retrieve ( tempiiuser.all)\g
replace tempiiuser (status=1) \g\q
$! THIS WILL BECOME +Y IN INGRES R.6.4
$ quel "+U" -u$ingres iidbdb
append to iiuser (tempiiuser.all) \g\q
$ quel "-U" -u$ingres iidbdb
destroy tempiiuser \g\q
$ append users.dat ii_system:[ingres.files]users.
$ exit

IIUSER.DAT: a text data file
----------
test-kenr

USERS.DAT: another text data file, tho w/ some trickery i cd use this 4 both.
---------
test-kenr!0!0!1
Newsgroups: comp.databases.ingres
From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: Field Concatenation in RBF
Organization: 
Date: Thu, 15 Oct 1992 21:52:41 GMT

In article <1992Oct13.151018.307@cutler>, john@cutler.com 
(John E. Babbitt Jr.) writes:
> I'll like to generate a list of contact names using RBF.  Let's suppose
> that I have three fields: first, middle, and last.  How do I print it on
> one line, trimmed and separated by a single space?  For example, it is now
> coming out as:
> 
>     John            E.          Babbitt, Jr.
> 
> and I couldn't find in the document on how to get it to come out as:
> 
>     John E. Babbitt, Jr.
> 
> I can do it in Report Writer, but I want to have it done in RBF, in a
> non-programming way.  Thanks.

You could define a view on the original table:

	create view my_view as
	select name = trim(first) + ' ' + trim(middle) + trim(last) , ...
	from my_table ... ;

and then define the RBF report on the view instead of the table.

--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: ingersol@stein.u.washington.edu (Steve Ingersoll)
Newsgroups: comp.databases.ingres
Subject: Re: Adding Ingres users
Date: 16 Oct 92 15:28:31 GMT
Article-I.D.: u.1992Oct16.152831.1233
Organization: University of Washington, Seattle

In article <1992Oct15.024740.24628@athena.mit.edu> thomasl@mtl.mit.edu (Thomas J Lohman) writes:
>I had a few replies to my posting about adding new Ingres users through
>a C program.  I have written such a program, and it is fairly straightforward.
>The only major hurdle is that it needs to be run as ingres, or if you do 
>what I did, just have ingres own the executable and then setuid to owner.
>This allows me to give group execute permissions, and have the group
>(something like ingstaff) be able to add, delete, or modify user entries.
>This was all done under 6.4/01 running on top of SunOS 4.1.1
>
>If anyone is interested in the specifics then feel free to e-mail me.
>
>
>--tom
>

	I also have a program for doing something similar on VMS. The program
that I have was intended to be run by our users to validate themself as an
Ingres user. With this feature we no longer need to be involved when users
want to have access to Ingres. We also have a nightly accounting system which
automatically removes access to Ingres when an account no longer exists on the
system.

	As with the original message if anyone is interested in the program(s)
send me mail and I will send you what I have...


Steve Ingersoll				Internet: ingersoll@cac.washington.edu
University Computing Services		Phone:    (206) 685-3371
University of Washington
From: chris@visionware.co.uk (Chris Davies)
Newsgroups: comp.databases.ingres
Subject: Time limit for SQL statement
Date: 12 Oct 92 08:00:33 GMT
Organization: VisionWare Ltd., Leeds, UK
X-Newsreader: Tin 1.1 PL5

I'm looking for a way to specify an upper limit on the time taken for
an SQL statement to execute (using embedded C/SQL on various Unix
systems, Ingres 6.4).

Perhaps I've missed a little option somewhere in Ingres, but here's my
suggestion.  Does anyone know the implications of using an alarm signal
in this way to interrupt an SQL statement?

For example, using a really crude approximation to C,

	set sigalarm ( 30_seconds, my_alarm_handler )
	EXEC SQL :mystatement

    alarm_continue:
	set sigalarm ( off )
	...


	my_alarm_handler()    /* Called when the timer expires */
	{
	    goto alarm_continue;    /* Yuk.  But it serves my point (I hope) */
	}

Would this break the application's back-end link or something else
equally nasty?  Any suggestions and comments would be appreciated, please!

Ta,
Chris
--
            VISIONWARE LTD, 57 Cardigan Lane, LEEDS LS4 2LE, England
  Tel +44 532 788858 x238.  Fax +44 532 304676.  Email chris@visionware.co.uk
---------- "VisionWare:   The home of DOS/SQL/UNIX/X/VMS integration" ---------
From: salnick@dejavu.spk.wa.us (There is no substitute for displacement...)
Newsgroups: comp.databases.ingres
Subject: Re: Help ......
Date: 11 Oct 92 15:01:01 GMT
Organization: Not Organized

In article <1992Oct9.105424.4911@ecrc.de> badri@ecrc.de (Badri Pillai) writes:
>Hi Ingres Gurus,
>
>The follwing is my question:
>
>I would like to create a relation Fullname_and_Addres_Id between two entity
>  Fullname and Address, where I would like to use the TID of
>  both entities..
>
>If you can give me an SQL example, or tell me if it is OK to
>do so, or its possible at all, would be great.

It is in general 'bad form' to use TID.  The TID is supposed to be 
internal to Ingres (and therefore they reseve the right to change it and 
break any code which was naively built on TID...).

One proper way to perform the task you are after is to add your own 
surrogate key (maybe not so surrogate... in the USA, the social Security 
Number would probably be added to the Fullname table), and then join on 
this.

select * from Fullname, Address where Fullname.ssn = Address.ssn

bob
--
RW Salnick, Spokane,WA  |  Home: salnick@dejavu.spk.wa.us
  Amiga 1000, WB 1.3    |  Work: mfgsys!salnick@kaiser.spk.wa.us
      WA9BVE            |
From: amandla@dcs.qmw.ac.uk (Jackie Saul)
Newsgroups: comp.databases.ingres
Subject: Help abf
Date: 14 Oct 92 10:44:48 GMT
Organization: Computer Science Dept, QMW, University of London

A couple of quick questions I need help with.
1. I have an abf application and have built an executable image. I only want 
my users to be able to access the database through the application and not to 
be able to change things in tables from other routes of access. Is there any 
way that I can set the access to the database to be only through the 
application?
2. I am using Ingres 6.3 on a Unix platform - is there a way of turning off 
Control C (interrupt) within the abf application then resetting it once they 
have exited? otherwise I can see problems with uncommitted transactions.
Any help would be appreciated
--
Jackie Saul

UUCP:      amandla@qmw-dcs.uucp  | Dept. of Computer Science
Internet:  amandla@dcs.qmw.ac.uk | Queen Mary & Westfield College
JANET:     amandla@uk.ac.qmw.dcs | University of London
Tel: +44(0)71 975 5250           | Mile End Road
Fax: +44(0)81 980 6533           | London E1 4NS
From: jremer@ub.d.umn.edu (Jeremy Remer)
Newsgroups: comp.databases.ingres
Subject: Ingres.  What is it?
Date: 5 Oct 1992 21:51:48 -0500
Organization: University of Minnesota, Duluth
Distribution: world
NNTP-Posting-Host: ub.d.umn.edu

Is there a FAQ telling exectly what Ingres is, what it does, how it works,
ect.?

All I know, at this point, is that Ingres is a database, of some kind.  I
would be interested in learning Ingres possibly and would like to also know
of any ftp sites that have a tutorial (under PC DOS or Unix fine)  that could
help me out.

All the input I can get would be a great.  

Jeremy

-- 
Jeremy Remer                     | jremer@ub.d.umn.edu  |    - _____ - 
Computer Science Major           | jremer@va.d.umn.edu  |   |/|_____|\|
University of Minnesota, Duluth. | jremer@nyx.cs.du.edu | __|/|     |\|__ 
Newsgroups: comp.databases.ingres
From: cathys@Ingres.COM (Cathy Savino)
Subject: Re: SQL Errorcodes
Reply-To: cathys@ingres.com (Cathy Savino)
Organization: Ingres, An ASK Company
Date: 14 Oct 92 18:52:44 GMT

This is a generic error code, specifically a data exception (which looks like
-402xx, with x being a sub code. Subcode 04 is an error in assignment.
All of the generic error code are documented in the back of the SQL Reference
Manual, in Appendix F.

FYI: The subcodes are not documented, so here they are: 00 no subcode, 01 Character
data, right truncation, 02 Null value, no indicator parameter, 03 Exact numeric
data, loss of significance (decimal overflow), 04 Error in assaignment, 05 Fetch
orientation has  value zero, 06 Invalid datetime format, 07 Datetime field overflow,
08 Reserved, 09 Invalid indicator parameter value, 10 Invalid cursor name,
15 Invalid data type, 20 Fixed Point Overflow, 21 Exponent Overflow, 
22 Fixed Point Underflow, 26 Exponent Underflow, 27 Decimal Underflow, 28 Other
unspecified math exception.

In article <lindsay.718941123@giaeb>, lindsay@giaeb.cc.monash.edu.au (lindsay smith) writes:
|> 
|> My organisation has recently switched over to using INGRES as a teaching
|> platform for our DataBase units. Recently a student while running an ESQL
|> program written in C received an SQLCODE error of -40204. The inquire_sql call
|> apparently returned a blank message. I have two questions which someone might
|> be able to help me with
|> 
|> a. what is this particular error triggered by?, and
|> 
|> b. is there anywhere in the INGRES documentation where these codes are listed?
|> I have already done a fairly extensive search - but to no avail.
|> 
|> Lindsay.
|> -- 
|> Lindsay Smith, Computing Lecturer
|> Monash University College Gippsland    Internet: lindsay@giaeb.cc.monash.edu.au
|> Switchback Rd, Churchill, 3842         Phone   : +61 51 226 669
|> Victoria AUSTRALIA                     Fax     : +61 51 226 738

Opinions (& typo's): Mine, Mine, Mine!

--
Cathy Savino 
   ___     _____   __  __       Ingres, An ASK Company
  / _ \   / ____\  | |/ /       1080 Marina Village Parkway
 / /_\ \  \____ \  | |\ \       Alameda, Ca 94501
/_/   \_\ \_____/  |_| \_\      (510) 748-3524
                                cathys@ingres.com