HDTenEightyP/NetNews huggingface dataset:
From: cortesi@informix.com (David Cortesi)
Newsgroups: comp.databases.informix
Subject: Key-only selects (was Re: Help! my install etc)
Date: 13 Apr 92 15:41:07 GMT
Reply-To: cortesi@informix.com
Organization: Informix Software, Inc.

In article <1992Apr10.141813.26235@cpqhou.compaq.com> thomasr@cpqhou.compaq.com  
(Thomas Rush) writes:
> In article <1992Mar26.204539.7416@informix.com> cortesi@informix.com writes:
> >
> >Regarding your choice of query, "where description matches '*MEM*'"
> >will always use a sequential scan.  MATCHES (or LIKE) with a leading
> >wildcard can't use an index.  
> >
> 	While it may be true to say that MATCHES with a leading 
> wildcard _doesn't_ use an index, isn't it possible that using the
> index might be faster:
> 
> 	since the index will have only this column, amount of data
> 	to be searched is only the column we're interested in,...

I am informed that the OnLine engine uses this idea, calling it a
"key-only select."  When all columns of a table that are referenced
in a query are contained in an index, the index is read instead of
the table. By using the index as a sort of "instant projection" of
the table, the engine cuts down on the amount of data transferred.
The use of a key-only select would be reflected in the optimizer
output of SET EXPLAIN ON.

This suggests a possible indexing strategy for high-use tables:
to include redundant columns in indexes so as to encourage key-only
selects.  Suppose you have a large, high-usage table with a column
that is often used for joins, K. You observe that a common use of
the table is to join on K in order to pick up the value of column D1
or column D2. You could try making indexes on (K, D1) and on (K, D2)
in order to speed these queries.

Of course indexes cost space to store and time to build as well as
time to maintain under updates, so this is not an automatic win!

> 	and,
> 	it is possible that finding a hit may allow inteligent use
> 	of Informix's compression scheme to guarantee that the next
> 	_n_ rows are hits also?

This idea is not used by our engines. I don't know why, tho I can
imagine some engineering value to keeping the algorithm for key
storage untied from the key selection algorithm.
Newsgroups: comp.databases.informix
From: thomasr@cpqhou.compaq.com (Thomas Rush)
Subject: Re: Help! my install of ONLINE 4.00 is 30% slower than Standard Engine
Reply-To: thomasr@cpqhou.UUCP (Thomas Rush)
Organization: Compaq Computer Corporation
Date: Fri, 10 Apr 92 14:18:13 GMT

In article <1992Mar26.204539.7416@informix.com> cortesi@informix.com writes:
>
>Regarding your choice of query, "where description matches '*MEM*'"
>will always use a sequential scan.  MATCHES (or LIKE) with a leading
>wildcard can't use an index.  
>
>	Dave Cortesi

Hi, Dave!

	While it may be true to say that MATCHES with a leading 
wildcard _doesn't_ use an index, isn't it possible that using the
index might be faster:

	since the index will have only this column, amount of data
	to be searched is only the column we're interested in,
	and,
	it is possible that finding a hit may allow inteligent use
	of Informix's compression scheme to guarantee that the next
	_n_ rows are hits also?

	Just wondering if anyone's actually done any looking into
this, or if it is one of the things that everybody "just _knows_."



thomas rush				uunet!cpqhou!thomasr
compaq computer corporation		their employee,
deep in the hearth of texas		not their opinions.
From: jrg@mips.com (John Galloway)
Newsgroups: comp.databases.informix
Subject: Re: HELP use tbmonitor non-interactive way!
Date: 10 Apr 92 07:45:50 GMT
Distribution: na
Organization: MIPS Computer Systems, Sunnyvale, California
NNTP-Posting-Host: moosehead.mips.com

In article <1650@das13.snide.com> dave@das13.snide.com (Dave Snyder) writes:
>In article <1992Apr9.175006.25922@uwm.edu>, felicia@miller.cs.uwm.edu (Felicia Chu Peng Cheong) writes:
>> 
>>     How can I have a cron task to execute tbmonitor tool to
>>     archive or unarchive or backup trans. log periodically?
>
>To the best of my knowledge, there is no way of performing a NON-INTERACTIVE
>archive, unarchive, or logical log backup.
tbtape is what you want, it is not completely non interactive but it is not
forms based so you can feed it scripts via stdin.
>
>>     How can I execute tbmonitor without without the interactive mode???
>>     [If it's instructed in the manual, pleeeease also tell me where it 
>>      is mentioned, I still find the Informix manuals are not very clear
>>      and easy to look-up something - my friends said too. ]
>
>Tbmonitor options can be executed from the command line with "tbstat".  The
tbstat deals with output or status commands.  With more recent versions
(4.1 & 5.0) you can do most Tbmonitor things with either tbmode or tbspaces.
	-jrg
-- 
internet    jrg@galloway.sj.ca.us  John R. Galloway, Jr  795 Beaver Creek Way
applelink   D3413                  CEO...receptionist    San Jose, CA   95133
                                   Galloway Research     (408) 259-2490
From: dave@das13.snide.com (Dave Snyder)
Newsgroups: comp.databases.informix
Subject: Re: logical logs
Date: 15 Apr 92 02:11:43 GMT
Organization: Snide Inc. - Folcroft, PA

In article <8727@emory.mathcs.emory.edu>, ilg@dasher.cc.bellcore.com (guthrie,ian) writes:
> I am running informix-online 4.00.UH2 on a Pyramid MIS-12/05 (OSx 5.1a).
> Part of the output from 'tbstat -l' is listed below:
> address  number   flags    uniqid   begin        size     used    %used
> e0068944 1        U---C-L  5        100d64        250       66    26.40
> e0068960 2        F------  0        100e5e        250        0     0.00
> e006897c 3        U-B----  3        100f58        250      250   100.00
> e0068998 4        U-B----  4        101052        250      250   100.00
> 
> My questions are:
> 
> 1. What does the 'L' under the flags heading mean? I cannot find any
>    definition in the manuals.
The last "checkpoint" is in that log.

> 2. Why would online not release a log that has been backed-up (the
>    last two lines)?
One reason that comes to mind is that you have a long-transaction that has
spanned the first two logs and is now writing to the third.

> 3. Is there a way to force online to release backed-up logs?
> 
Not during a long-transaction.  If a transaction spans logs, the logs
can't be freed until the transaction is terminated.  The reason for this
is for a ROLLBACK WORK.  If the logs are freed, how is Online going to
rollback the transaction?  Now this is neat...  if you have a long
transaction that enters the last log, Online will automatically rollback
the work.  What happens if you don't have enough space in the last log
to rollback the transaction... you'd better have a real recent archive of
your data.

DAS
-- 
David A. Snyder @ Snide Inc. - Folcroft, PA                    Current Release
                                                               is db4glgen-3.4
UUCP:  ..!uunet!das13!dave   INTERNET:  dave.snyder@snide.com
From: mrh@guug.de (Mark Herbert Mueller-BBM GmbH)
Newsgroups: comp.databases.informix
Subject: HP terminals & 4GL
Summary: Need termcap terminfo for hp2392
Keywords: 4GL hp2392 HP
Date: 16 Apr 92 17:08:00 GMT
From: dave.snyder@snide.com (Dave Snyder)
Newsgroups: comp.databases.informix
Subject: Re: SQL query question
Date: 15 Apr 92 09:48:13 GMT
Reply-To: dave.snyder@snide.com (Dave Snyder)
X-Informix-List-ID: <list.1065>

Quoting jeffl@isg100.isg.com...

>     update phonetab
>             set (phonecol[4,7], phonecol[8])
>             = (phonecol[5,8], " ")
>             where phonecol[4] = "-"

Mmmm... sorry (I feel like an ass).  That's what I get for calling home to read
my mail during company (actually lunch) time.  You rush to read the mail, rush
to reply to it, and put your foot in your mouth when you're all done rushing :-)
I didn't realize until know that you are shifting everything 1 character to the
left.  Sorry 'bout that.

DAS
-- 
David A. Snyder @ Snide Inc. - Folcroft, PA                    Current Release
                                                               is db4glgen-3.4
UUCP:  ..!uunet!das13!dave   INTERNET:  dave.snyder@snide.com
From: kristen.m.altman@ccd.harris.com (Kristen Altman)
Newsgroups: comp.databases.informix
Subject: Re: isql commands from shell prompt
Date: 15 Apr 92 15:03:09 GMT
Reply-To: kristen.m.altman@ccd.harris.com (Kristen Altman)
X-Informix-List-ID: <list.1068>


 

Regarding shell scripts with Informix, we all have a good example in the     
Informix directories.  The file that installs the demo database is a script
file called $INFORMIXDIR/bin/isqldemo.  That file helped me out a lot.

Hope it helps!!

Kristen
-- 
*******************************************************
*                   Kristen Altman                    *
*                 kma@ccd.harris.com                  *
*       Harris Controls and Composition Division      *
*                   Melbourne, FL                     *
******************************************************* 
From: judym@col.hp.com (Judy Miller)
Date: Sat, 4 Apr 1992 00:14:51 GMT
Subject: Table lookup function in isql?
Organization: HP Colorado Springs Division
Newsgroups: comp.databases.informix

Question: Is there some kind of table lookup feature in isql 
          (Informix SQL Query Language)?

Problem:  I have a list of keys to 4000 records which I need to delete from
          a table of 12000.  

I'm trying to avoid writing a program for this common function.
(I do not have 4GL on this machine, but I do have ESQL/C.)

Another option is to unload the table and eliminated the records outside of
Informix, delete all, and then reload the remaining two-thirds.

Judy
From: Jgeary@redriverad-emh1.army.mil
Newsgroups: comp.databases.informix
Subject: HELP use tbmonitor non-interactive way!
Date: 15 Apr 92 01:23:11 GMT
Reply-To: Jgeary@redriverad-emh1.army.mil
X-Informix-List-ID: <list.1063>

Yes, you can use tbmonitor in a non-interactive way.  We create an empty
file on disk to hold the archive or backup of transaction logs.  I then
have a shell script that runs out of cron to shut Turbo down, do the
archive and bring Turbo back online.

-------------- cron entry ---------------------------------------------
50 22 * * 1-4 ulimit 40240000; /bin/su informix -c "/usr/informix/local/bkup"

--------------------shell script---------------------------------------

#makes sure you have an empty file (pretend your mounting a tape)
#also sets permissions
cp /dev/null /bkup1/dblogs/dblog
chmod 666 /bkup1/dblogs/dblog
#sets engine to Turbo (may already be set, just a precaution)
SQLEXEC=/usr/informix/lib/sqlturbo
export SQLEXEC
#removes old copy of archive (we do not have room for two copies on
#the same disk)
rm /bkup1/dblogs/archive
# put Turbo in Quiescent mode
/usr/informix/bin/tbmode -u
#archives database - system messages are stored in file bkup.out
/usr/informix/bin/tbtape -s > /usr/informix/local/bkup.out <<+

+
#copies archive to new file (pretend your unmounting a tape and labeling
#it) also sets permissions
mv /bkup1/dblogs/dblog /bkup1/dblogs/archive
cp /dev/null /bkup1/dblogs/dblog
chmod 666 /bkup1/dblogs/dblog
# puts Turbo back online
/usr/informix/bin/tbmode -m
#mails file bkup.out to me so I can check for error messages, etc.
/usr/tools/send -t "jgeary" -c "" -s "TURBO" -f /usr/informix/local/bkup.out -n <<+
s
+
---------------------------------------------------------------------------

This has been working fine for us. The only problem is, you don't know if
the archive worked or not until you get to work in the morning.

Jane Geary
Red River Army Depot
DSN 829-2358 COMM 903-334-2358
From: dranney@informix.com (David Ranney)
Newsgroups: comp.databases.informix
Subject: Re: delimiters
Date: 16 Apr 92 23:27:07 GMT
Organization: Informix Software, Inc.

In article <8743@emory.mathcs.emory.edu> kristen.m.altman@ccd.harris.com (Kristen Altman) writes:
>
>Informix fans,
>
>I am having a major problem with my application and I'm hoping that someone
>can help.
>
>I need to be able to put a delimiter between the data fields returned from
>a select statement.  This data is being written to standard output.  An
>example would be:
>
>>isql testdb -
>>select field1, field2, field3 from test;  {where the delimiter is "|"}
>
>a   |b   |c   |
>d   |e   |f   |
>
>etc...

>My application uses only Informix-ISQL.                         
>
>If anyone has any idea how this might be accomplished (without using the
>unload command), I would love to hear from you!!
>
>Thanks in advance
> 
>Kristen Altman

There's nothing specifically meant for this purpose, but you can simulate it
via an ACE report if that's an option:

-------------------------------------CUT HERE----------------------------------
DATABASE testdb
END

OUTPUT
   LEFT MARGIN 0
END

SELECT 
   field1,
   field2,
   field3
FROM test
END

FORMAT
   ON EVERY ROW
      PRINT field1 CLIPPED,
      "|",
      field2 CLIPPED,
      "|",
      field3 CLIPPED,
      "|"
-------------------------------------CUT HERE----------------------------------

If you can't use ACE, then you can do the following in sql alone:

SELECT field1, "|", field2, "|", field3, "|" FROM test;

You might have to do things to remove column names and other output garbage,
but the basic idea is there.
From: proberts@informix.com (Paul Roberts)
Newsgroups: comp.databases.informix
Subject: Re: SQL query question
Date: 14 Apr 92 06:05:22 GMT
Organization: Informix Software, Inc.

In article <8718@emory.mathcs.emory.edu> RUCHI%CFRVM.bitnet@VTVM2.CC.VT.EDU (Ruchi Patel) writes:
>
>Is there anyway you could update the lower case data into uppercase by using
>the simple SQL UPDATE statement?
>
>Is there anyway you could delete a character from the column using the
>UPDATE statement? e.g. I would like to drop a "-" character from the
>phone_no column.
>

I assume you want to replace 415-926-6622
                        with 4159266622
            rather than with 415 926 6622

since the latter task is too easy (run a series of update statements
like this:

update tab set phone[2] = " " where phone[2] = "-";
update tab set phone[3] = " " where phone[3] = "-";
...
update tab set phone[20] = " " where phone[20] = "-")

You can do it somewhat similarly with a series of updates like this:

update tab set phone[3,19] = phone[4,20], phone[20] = " "
where phone[3] = "-";

update tab set phone[4,19] = phone[5,20], phone[20] = " "
where phone[4] = "-";

etc

i.e. where you find a hyphen in the string, "left shift" whatever
follows the hyphen by one character.

Obviously, it is a bit of a pain to write these update statements,
you will probably want to write a program to generate them. Be
careful of the end-cases, check that I haven't got everything
off-by-one, and for heaven's sake keep a backup of your data before
you use the script to de-hyphenate it!

I guess it all depends how badly you need to do it. I have had a whole
lot of fun trying to "standardize" a list of 40,000 names and addresses,
in order to be able to identify the duplicates.

It might be easier to write a 4GL program that will seek-and-destroy
hyphens....

Paul
From: kristen.m.altman@ccd.harris.com (Kristen Altman)
Newsgroups: comp.databases.informix
Subject: Delimiters again
Date: 17 Apr 92 14:48:08 GMT
Reply-To: kristen.m.altman@ccd.harris.com (Kristen Altman)
X-Informix-List-ID: <list.1076>

Informix fans (again),

Thank you very much for all of your replys!!  I didn't know that you could
insert a constant in the fields of a select (select f1,"|",f2,"|", etc...).

I'm guessing that that is the only way a delimiter can be placed between 
the columns of data returned.  This is a possible solution for me, but my 
problem is that I don't know the commands that are going to be sent to me.
My application is an SQL server that sits on top of Informix or Sybase and 
receives SQL requests from other applications.  I think I can make your
suggestion work, but if there's a better way (without modifying the query
by placing the delimiter in there), please let me know.


Thanks again for all of your help!!

Kristen

-- 
*******************************************************
*                   Kristen Altman                    *
*                 kma@ccd.harris.com                  *
*       Harris Controls and Composition Division      *
*                   Melbourne, FL                     *
******************************************************* 
Newsgroups: comp.databases.informix
From: boudj@aztec.Berkeley.EDU (Jason P Boud)
Subject: Imformigram
Keywords: Mailing Lists Informix News
Reply-To: boudj@aztec.Berkeley.EDU (Jason P Boud)
Organization: Motorola E.C.I.D
Date: Thu, 9 Apr 1992 12:15:10 GMT

Can anyone tell me if there is a e-mail version of "Imformigram"
(spelling may not be correct).
If not whats the best way to receive regular updates about Informix........?


Regards

Jason Boud

---------------------------------------------------------------
Motorola Ltd, European Cellular Infrastructure Division
16 Euroway, Blagrove
Swindon, Wiltshire
SN5 8YW

Tel: (44)-793-545344     E-Mail: boud@zeus.swindon.rtsg.mot.com
Fax: (44)-793-618714             uunet!motcid!zeus!boud
---------------------------------------------------------------
From: Doimic01@saad-emh1.ARMY.MIL
Newsgroups: comp.databases.informix
Subject: Re: delimiters
Date: 16 Apr 92 21:03:13 GMT
Reply-To: Doimic01@saad-emh1.ARMY.MIL
X-Informix-List-ID: <list.1073>

In message <9204161741.AA22901@pd2>, Kristen Altman asks:

->I need to be able to put a delimiter between the data fields returned from
->a select statement.  This data is being written to standard output.  An
->example would be:
->isql testdb -
->select field1, field2, field3 from test;  {where the delimiter is "|"}
->a   |b   |c   |
->d   |e   |f   |
->My application uses only Informix-ISQL.                         
->If anyone has any idea how this might be accomplished (without using the
->unload command), I would love to hear from you!!
->Thanks in advance
->Kristen Altman

A simple way to do this in ISQL is to include constants in the SELECT
statement:

select field1, "|", field2, "|", field3, "|" from test;
________________________________________________________________________________

Todd Glidden                          "Pat Who? In '92"
Sacramento Army Depot                 Pat Paulson's Presidential campaign slogan
________________________________________________________________________________
From: cris@logic.dsg.ti.com (Cris Schramm)
Newsgroups: comp.databases.informix
Subject: Unknown sqlca.sqlcode (-522) (fwd)
Date: 14 Apr 92 19:43:15 GMT
Reply-To: cris@logic.dsg.ti.com (Cris Schramm)
X-Informix-List-ID: <list.1061>

-> Date: Mon, 13 Apr 1992 18:35 CDT
-> From: MYERS@PASCAL.ACM.ORG
-> Subject: Unknown sqlca.sqlcode (-522)
-> X-Vms-To: INFORMIX-LIST@RMY.EMORY.EDU
-> 
->   I'm running INFORMIX/se 4.1 and ESQL/C 4.1 on a Sun4.  I get an error
-> of (-522) and the book doesn't day what that is.  Any input would be

-522  Table table-name not selected in query.
Check the spelling of the table in the query.

-> appreciated.
-> Thanx,
-> Dow
-> -------------------------------------------------------
-> Dow Myers                           |  This space for rent.
-> myers@acm.org                       |  Real Cheap.
-> Applied Network Technologies, Inc.  |  Call,
-> Waco, TX.                           |  (817) 752-6888
-> 

I found this in the I-SQL Reference Manual.

Regards,
Cris Schramm    Texas Instruments, Inc. 	     *All disclaimers apply*
From: MYERS@PASCAL.ACM.ORG
Newsgroups: comp.databases.informix
Subject: Unknown sqlca.sqlcode (-522)
Date: 14 Apr 92 15:18:09 GMT
Reply-To: MYERS@PASCAL.ACM.ORG
X-Informix-List-ID: <list.1057>


  I'm running INFORMIX/se 4.1 and ESQL/C 4.1 on a Sun4.  I get an error
of (-522) and the book doesn't day what that is.  Any input would be
appreciated.
Thanx,
Dow
-------------------------------------------------------
Dow Myers                           |  This space for rent.
myers@acm.org                       |  Real Cheap.
Applied Network Technologies, Inc.  |  Call,
Waco, TX.                           |  (817) 752-6888
From: mrh@guug.de (Mark Herbert Mueller-BBM GmbH)
Newsgroups: comp.databases.informix
Subject: HP terminals & 4GL
Summary: Need termcap terminfo for hp2392
Keywords: 4GL HP hp2392
Date: 16 Apr 92 17:09:21 GMT
From: dave@das13.snide.com (Dave Snyder)
Newsgroups: comp.databases.informix
Subject: Re: isrecnum bug in C-ISAM
Date: 14 Apr 92 01:06:00 GMT
Organization: Snide Inc. - Folcroft, PA

In article <1992Apr13.034933.2354@luga.latrobe.edu.au>, lhscmc@latrobe.edu.au (Craig Macbride) writes:
> Just thought I would point out that there is a (nasty) fix to the problem
> I pointed out with C-ISAM. Unfortunately, it will only work if the index
> you are using doesn't have duplicate keys.

> When you read ISNEXT or ISPREV (with ISSKIPLOCK) and get a return code
> indicating that the record is locked, you know that isrecnum is junk, but
> the data returned is correct. Therefore, issue a read ISEQUAL on the key
> which the ISNEXT/ISPREV read returned. This second read will return not
> only the correct data, but also the correct isrecnum!

Why not use ISCURR?  I believe that ISCURR reads the record where the
file pointer is pointing regardless of what is in isrecnum.  Also, this
method would work on Unique and Duplicate indexes (it'd be quicker too).

DAS
-- 
David A. Snyder @ Snide Inc. - Folcroft, PA                    Current Release
                                                               is db4glgen-3.4
UUCP:  ..!uunet!das13!dave   INTERNET:  dave.snyder@snide.com
From: walt@rmy.emory.edu (Walt Hultgren)
Newsgroups: comp.databases.informix
Subject: There we went.
Date: 12 Apr 92 16:06:01 GMT
Article-I.D.: emory.8675
Reply-To: walt@rmy.emory.edu (Walt Hultgren)
X-Informix-List-ID: <list.1052>

The gateway between the Informix mailing list and comp.databases.informix is
now two-way.  If you are reading this message as e-mail from the list, then
you have not been dropped.

Let me know if you have any questions, or if you notice problems.

Thanks,

Walt.

-- 
Walt Hultgren              Internet: walt@rmy.emory.edu       (IP 128.140.8.1)
Emory University               UUCP: {...,gatech,rutgers,uunet}!emory!rmy!walt
954 Gatewood Road, NE        BITNET: walt@EMORY
Atlanta, GA  30329  USA       Voice: +1 404 727 0648
From: dka@sdc.boeing.com (Dennis Asato)
Newsgroups: comp.databases.informix
Subject: Termcap files for INFORMIX 4.0
Date: 7 Apr 92 18:48:48 GMT
Organization: Boeing Computer Services (ESP), Seattle, WA


 Hello INFORMIX users,

  Anyone has termcap files for INFORMIX 4.0?  We
  have the following workstations that are 
  presently using xterm on XR11R4:

  WORKSTATIONS		TERM	O/S		INFORMIX

  DEC	VAX3100		xterm 	ULTRIX 4.2	4.0
	DEC2100		xterm	ULTRIX 4.2	4.0

  SUN	SUN-3  Type 3 	xterm	SunOS 4.1.1_U1	4.0
	SUN-3x Type 4	xterm	SunOS 4.1.1_U1	4.0
	SUN-4c Type 4	xterm	SunOS 4.1.2	4.0

	Thanks,
	- Den and Tuan -

-- 
	    	|   Dennis K. Asato		| dka@wsc-sun                   
     Mahalo,  	|  Boeing Computer Services	| dka@wsc-sun.boeing.com        
     - Den -   	|  (206)865-5615 		| tecdka1@kbct.profs.boeing.com
From: dave@das13.snide.com (Dave Snyder)
Newsgroups: comp.databases.informix
Subject: Re: Urgent problem with INFORMIX-ONLINE!
Date: 6 Apr 92 12:48:10 GMT
Organization: Snide Inc. - Folcroft, PA

In article <1992Mar31.080121.7847@uni-paderborn.de>, joern@uni-paderborn.de (Joern Gehring) writes:
> Yesterday I started an SQL-query on our INFORMIX-ONLINE-Database and because
> it seemed to run much longer than expected, I canceled it with CTRL-C.
> I think this was the biggest mistakte I ever made, because after that,
> the current chunk of my dbspace was completely full.
> I'm sure, that there were at least 10MB free before the interrupt!
> And now there are gone - and I'm very short of disk-space!

First... relax!

Create a 5MB "cooked" chunk for your Online environment.  Export the
database(s) you want saved to tape (your low on disk space).  Re-initialize
your Online environment.  Import the database(s) you saved to tape.

DAS
-- 
David A. Snyder @ Snide Inc. - Folcroft, PA                    Current Release
                                                               is db4glgen-3.4
UUCP:  ..!uunet!das13!dave   INTERNET:  dave.snyder@snide.com