RSS Feed: IIUG Forum: Classics

Classics is a forum to discuss all aspects of Informix classic tools including Standard Engine (SE), Online 5, 4GL, D4GL, ESQL, ISQL and others.

Re: RUN FORM >>Unknown error message 3205.
Posted by: frank.pr@msn.com (FRANK ROTOLO) - Thu, 11 Mar 2010 20:51:38 EST
I've seen "unknown error message ..." before!.. It usually occurs when the
sperform, the engine itself or another informix binary has become corrupted.
I solved it by re-installing informix.




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3541]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Re: RUN FORM >>Unknown error message 3205.
Posted by: art.kagel@gmail.com (Art Kagel) - Thu, 11 Mar 2010 07:14:31 EST
Yes. ISQL forms are supported against Online 5.xx. Open a case with IBM.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.

On Thu, Mar 11, 2010 at 7:08 AM, debadatta <mishra.dd@gmail.com> wrote:

> Hi
>
> I have a old system of Informix 5. Whenever i am running a form this is
> giving this error.
>
> RUN FORM >>Unknown error message 3205.
>
> No fnderr help is available for this error number. Is running form
> generated
> by isql supported by informix 5?
>
> --
> Thanks & Regards
> Debadatta Mishra
>
> --0016364eea3618ee7f0481854684
>
>
>
>

>
>
>

--0015174764921221cb0481855bfa




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3540]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

RUN FORM >>Unknown error message 3205.
Posted by: mishra.dd@gmail.com (debadatta) - Thu, 11 Mar 2010 07:08:39 EST
Hi

I have a old system of Informix 5. Whenever i am running a form this is
giving this error.

RUN FORM >>Unknown error message 3205.

No fnderr help is available for this error number. Is running form generated
by isql supported by informix 5?

--
Thanks & Regards
Debadatta Mishra

--0016364eea3618ee7f0481854684




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3539]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Re: THIS WEBSITE HAS MAJOR PERFORMANCE PROBLEMS!
Posted by: art.kagel@gmail.com (Art Kagel) - Wed, 10 Mar 2010 09:57:23 EST
We are working on it. The experiences are not universal. Here at home
today and at a client's site yesterday I have consistently gotten faster
responses and no dropped page requests at all. No lagging. Other board
members, on the other hand, are seeing the same problems you are, so we are
aware of the problems and are investigating with the site host and their ISP
linkages.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.

On Tue, Mar 9, 2010 at 6:30 PM, FRANK ROTOLO <frank.pr@msn.com> wrote:

> it's crashing and/or lagging a lot!.. I tried accesing it from different
> computers at separate locations within New York City and get the same
> results!
> I used Optimum OnLine and TimeWarner Broadband and gort same results, but
> no
> problems acesing other high-traffic websites!.. WHAT'S UP?.. Seems like
> moving
> the site to a new server is causing more problems!
>
>
>
>

>
>
>

--0015174c0f3ea65f270481738358




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3538]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
Posted by: jleffler.iiug@gmail.com (Jonathan Leffler) - Wed, 10 Mar 2010 02:04:21 EST
Which version of ISQL on which platform?

This isn't, by any chance, ISQL 2.10 running in a DOS emulator on Windows 7,
is it?

On Mon, Mar 8, 2010 at 12:35, FRANK ROTOLO <frank.pr@msn.com> wrote:

> In my app, customers are the master table, loans are the detail table.
> My users access customers with INFORMIX-SQL "perform" screens.
> My users query by customers name using wildcards. the query result
> sometimes
> returns 4 to 35 matches, depending on how well my users specify the
> customers
> name with the wilcards,
>
> example: ROD*GON*CAR* to locate RODRIGUEZ GONZALEZ, CARLOS
>
> If this query returns say 12 results, the users press "N"(Next) until they
> locate the right customer. Then the users press "D"(detail) and perform
> makes the loans table active, automatically queries the loans belonging to
> the selected customer (a "customers master of loans" statement is declared
> in
> the instructions section of the perform source code), then the users press
> "N"
> to view the next loan transaction until the find the right one in order to
> update it with a payment.
>
>
>
>

>
>
>

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.

--000e0cd1188acaa1c204816ce7d7




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3537]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

INFORMIX 4GL/SQL on Windows
Posted by: frank.pr@msn.com (FRANK ROTOLO) - Tue, 09 Mar 2010 21:52:12 EST
In my opinion, if IDS is regarded as IBM flagship DBMS, why is INFORMIX-4GL or
INFORMIX-SQL not natively available to run on Windows platforms? Although
INFORMIX is more of a high-end product, there are many small businesses using
single-user applications developed with Access or other DB's and this market
is huge and being neglected!

Many may think that INFORMIX-SQL is a beginners RDBMS, but its great for
quickly prototyping, converting it to 4GL, porting it to UNIX environments.
If it could be modernized with GUI's like Querix, 4Js, etc. it could compete
in Windows environments!




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3536]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

THIS WEBSITE HAS MAJOR PERFORMANCE PROBLEMS!
Posted by: frank.pr@msn.com (FRANK ROTOLO) - Tue, 09 Mar 2010 18:30:21 EST
it's crashing and/or lagging a lot!.. I tried accesing it from different
computers at separate locations within New York City and get the same results!
I used Optimum OnLine and TimeWarner Broadband and gort same results, but no
problems acesing other high-traffic websites!.. WHAT'S UP?.. Seems like moving
the site to a new server is causing more problems!




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3535]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Re: RE: SERIAL to INTEGER key joins vs. CHAR t....
Posted by: frank.pr@msn.com (FRANK ROTOLO) - Tue, 09 Mar 2010 18:18:47 EST
"Art, you're trying to fix a brain dead solution.
Hint: The patient is dead from the get go..."
^
HERE WE GO AGAIN WITH THE INSULTS!
----------------------------------

It's a Pawnshop Management System with:
1350 active customers rows (grows about 10/month), 3800 inactive customers.
250 active loan rows (avg per month), 5700 finalized loan rows (no further
updating, but user wants to keep in loan table for reference), so each active
customer has about 2 active loans average and several redeemed. forfeited
loans.

After experimenting with cust_id vs. cust_name joins for the loans table,
cust_name scheme wins, since with loans.cust_id, loan rows have no cust_name,
even though customers has a CREATE UNIQUE CLUSTERED INDEX ON
customers(cust_name) and loans are grouped together with CREATE CLUSTERED
INDEX ON loans(cust_id). The loans are not grouped together by name in
ascending CHAR order like the customer table is and C-ISAM has to jump all
over the datafile to build the temporary file's current-list.

The unload/re-load, drop/create tables, creation of all indexes and update
statistics takes only 14 seconds! and all newly recreated indexes with update
staistics really seems to make the system healthy (never have had to run a
dbcheck to repair anything).

So, there you have it! ;O)




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3534]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

RE: SERIAL to INTEGER key joins vs. CHAR to CHAR.
Posted by: zevb@bhphoto.com (Zev Berezin) - Tue, 09 Mar 2010 15:18:14 EST
Are you sure about that last paragraph? I know when adding data it will not
maintain the physical order of the cluster, and therefore over time an alter
index indexname to cluster should be executed.

I am not sure when updating data.

Zev Berezin

-----Original Message-----
From: classics-bounces@iiug.org [mailto:classics-bounces@iiug.org] On Behalf
Of Pankrich Khanthasa-ard
Sent: Monday, March 08, 2010 11:01 AM
To: classics@iiug.org
Subject: Re: SERIAL to INTEGER key joins vs. CHAR to CHAR. [3518]

I just have a little bit doubt why your CUSTOMER.cust_name can be unique. I
mean it's possible to have many "John" in your villege.

Anyway, why don't you try in simplest way.
- Primary key with unique index on CUSTOMER.cust_id
- Duplicate index on CUSTOMER.cust_name
- Foreign key with normal index on LOANS.cust_id

Don't forget about the cluster index will generate more cost when you try to
modify data because DBMS has to maintain the physical data ordered.
So it will be better to avoid this index option in OLTP but not in DSS.

________________________________
From: FRANK ROTOLO <frank.pr@msn.com>
To: classics@iiug.org
Sent: Mon, March 8, 2010 12:24:40 PM
Subject: SERIAL to INTEGER key joins vs. CHAR to CHAR. [3515]

OK, so I have a "customers" table with:

customers.cust_id SERIAL(0),
customers.cust_name CHAR(30),
....
....

and a "loans" table with:

loans.cust_id INTEGER,
....
....

the tables are joined by their cust_id columns.
we are told this is the normally correct way to join master/detail tables.
it allows us to make changes in the customers name without loosing the join.

To lookup a customer's loans, my users query the master table using wildcard
names, example: LAS*SUR*F* to locate customer LASTNAME SURNAME, FIRSTNAME
Then they make the loans table active to make payment updates.
So, I created the following index:

CREATE UNIQUE CLUSTER INDEX custname ON customers(cust_name);

so that any new customers are physically ordered by name in customers.dat
great, but I'm not able to order the loans detail table the same way!
the best I could do for it is:

CREATE CLUSTER INDEX loansclidx ON loans(cust_id);

so that each customers loans are physically grouped together in loans.dat
Now, everytime a new customer is added, the next available serial cust_id
is assigned to the customer, regardless of the customers name. As a result,
the rows in the loans table are not in the same order as the customer table,
and fetching each customers loans takes longer because although the loans rows
are grouped together in ascending order by loans.cust_id, they are not ordered
the same way as the customers name in the master.

I experimented by doing away with customers.cust_id = loans.cust_id, making
customers.cust_name the PRIMARY KEY which now joins loans.cust_name (an added
column). Although this is not the "correct" way to define joins, because if
you make a change in the master tables customer name the join is severed in
the detail table, unless you update each detail row with the new change.
But now we have:

CREATE UNIQUE CLUSTER INDEX custname ON customers(cust_name);
CREATE CLUSTER INDEX loanname ON loans (cust_name);

and response time is incredible!

Is there a better way of doing this?












* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3533]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

RE: SERIAL to INTEGER key joins vs. CHAR to CHAR.
Posted by: im_gumby@hotmail.com (Ian Michael Gumby) - Tue, 09 Mar 2010 12:31:41 EST
Uhm...

Art, you're trying to fix a brain dead solution.
Hint: The patient is dead from the get go.

Frank,

First, what's the number of customers in the database?
10,100,1000, 10K,100K?
What's the number of loans per customer?

(For all we know this could be a paychex system where we're tracking paycheck
loans...)

You still want to use the cust_id to loan_id join and indexing.

You want speed, you still have a couple of other tricks using 4GL/C and
prepared cursors on looking up customers.
But that's besides the point.

Unloading and then reloading your master table of customers is completely
brain daid. Yes, I said 'daid' instead of dead.

As Art pointed out there's a lot missing from your story and what's missing is
important when you want to think about solutions.

Or you could run with a typical OTC response... "Have you tried UPDATE
STATISTICS?" "Oh, you have? Well try it again... " ;-)

-G

> To: classics@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: SERIAL to INTEGER key joins vs. CHAR to CHAR. [3526]
> Date: Mon, 8 Mar 2010 19:25:31 -0500
>
> Wait,is this Standard Engine (SE)? You didn't mention you are using
> Standard Engine! I suppose if we all looked back at your previous post we
> might have guessed that (since you didn't mention it them except by
> inference), but I for one don't do that. I don't have time. You REALLY
> should post your engine and platform details when you first post a question.
>
> If so, then ignore what I wrote about UPDATE STATISTICS HIGH and MEDIUM,
> that only applies to Informix Dynamic Server. However, all of the comments
> everyone said about CLUSTER indexes is still true. Standard Engine (nor IDS
> for that matter) does NOT maintain your clustered indexed table in sorted
> order after the initial clustering. The ONLY reason that data is being
> returned in sorted order is that SE is using the index to find the records.
> It is using a key lookup to find the first matching row (if there is a
> filter) and then scanning across the index leaves which returns the keys and
> rowids in sorted order by the index or cluster key. Since the index was
> initially clustered, the majority of rows are right next to each other on
> disk so access is very fast. Newly added rows will force additional disk
> reads, but each read will pull multiple rows into memory so it will not
> affect performance until a significant percentage of the rows are no longer
> clustered. The exact percent depends on the number of rows that fit on page
> on disk. (Well, SE doesn't really organize data in pages on disk, but the
> IO buffer it uses to read a block of rows into memory works the same way.)
>
> By the way, unless you include an ORDER BY clause, the order of returned
> rows, EVEN WITH A CLUSTERED INDEX, is not guaranteed! Posting this
> application to IDS someday where the optimizer is smarter and is likely to
> ignore the clustered index if there is no ORDER BY clause (and sometimes
> even if there is one) is likely to break your application because the
> optimizer may be able to retrieve the data faster using a different index or
> sort it faster than it can be fetched using the index, even in leaf scan
> mode.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Mon, Mar 8, 2010 at 3:08 PM, FRANK ROTOLO <frank.pr@msn.com> wrote:
>
> > I am using INFORMIX-SQL (perform, ace).
> > My cluster indexes seem to maintain the rows physically ordered in the
..dat
> > files because after adding new customers or loans, I query those tables
and
> > the newly added customers appear to be in sorted order.
> >
> >
> >
> >
>

> >
> >
> >
>
> --0015173fe862d9bf170481533738
>
>
>

>
>

_________________________________________________________________
Hotmail: Powerful Free email with security by Microsoft.
http://clk.atdmt.com/GBL/go/201469230/direct/01/




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3532]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

RE: Trying to send tab delimited type output
Posted by: noel@gfm.co.uk (Noel Murphy) - Tue, 09 Mar 2010 03:19:40 EST
You can just unload the data like this,

unload to "bob" delimiter " " { the delimiter is a TAB }
select FIRST 100
'"' || NVL(TRIM(title),"") || '"' ,
'"' || NVL(TRIM(initials),"") || '"' ,
'"' || NVL(TRIM(surname),"") || '"' ,
'"' || NVL(TRIM(address1),"") || '"' ,
'"' || NVL(TRIM(address2),"") || '"' ,
'"' || NVL(TRIM(address3),"") || '"' ,
'"' || NVL(TRIM(town),"") || '"'
from customers

It's a bit OTT but does give you tab delimited output with columns
enclosed in quotes

-----Original Message-----
From: classics-bounces@iiug.org [mailto:classics-bounces@iiug.org] On
Behalf Of FRANK ROTOLO
Sent: 09 March 2010 07:38
To: classics@iiug.org
Subject: Re: Trying to send tab delimited type output [3530]

Ideas:

1. Try using the ACE report writer to create your unload file. You can
enclose "" (quotes within quotes in a print statement, example:
print column 1,""",col1,""",col2,""";) and you can also put a semi-colon
at the end of your print statement to supress the new-line for each row
thats read and printed. control characters like TAB can be defined like:
let TAB = ascii ???.

2. I think that PDF accepts other types of imports like .csv, so look
into the import options.

3. Good Luck!

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * ***


________________________________________________________________________
This email has been scanned for all viruses by the MessageLabs Email
Security System before entering the GFM Network. If you have any
queries please contact Systems Administration or IT.
________________________________________________________________________

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3531]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Re: Trying to send tab delimited type output
Posted by: frank.pr@msn.com (FRANK ROTOLO) - Tue, 09 Mar 2010 02:38:30 EST
Ideas:

1. Try using the ACE report writer to create your unload file. You can enclose
"" (quotes within quotes in a print statement, example:
print column 1,""",col1,""",col2,""";) and you can also put a semi-colon at
the end of your print statement to supress the new-line for each row thats
read and printed. control characters like TAB can be defined like: let TAB =
ascii ???.

2. I think that PDF accepts other types of imports like .csv, so look into
the import options.

3. Good Luck!




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3530]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Conclusion: SERIAL to INT join vs. CHAR to CHAR
Posted by: frank.pr@msn.com (FRANK ROTOLO) - Tue, 09 Mar 2010 02:10:04 EST
After extensive experimenting with customers.cust_id SERIAL joining
loans.cust_id INTEGER, versus customers.cust_name CHAR(30) joining
loans.cust_name CHAR(30), I have concluded the following:

1. Customers are wildcard queried by their names, not by customer i.d.
numbers. Customers walk in to the users business and don't remember their i.d.
numbers, but know their names, so this is the m.o. used for locating customers
info.

2. If the cust_id strategy is used, customers names are scattered throughout
the table because its ordered by cust_id, not by name, so when a wildcard
query
is done on a name, C-ISAM has to fetch each scattered row throughout the
datafile, even with an index on cust_name, so it costs more time.

3. Likewise, if cust_id strategy is used for loans, although each customers
loans are grouped together by their cust_id, these groups of loans are also
scattered throughout the datafile, so when the user does the cross-table query
for a particular customers loans, it also costs more time.

4. Whereas, having both the customers and loans rows physically ordered by
the customers name delivers instant retrieval.

5. When an existing customer visits the users business, they usually bring
with them the loan ticket to make an interest payment or a redeem payment,
so the user only has to query the loan table by ticket number (indexed) and
update the row, but sometimes the customer visits the user without the ticket,
which then requires the user to query by customer name to find all his active
loans.

6. One disadvantage to using cust_name strategy is that if you change the
customers name in the master table, then you have to change the customers name
in each row in the loans table that belongs to that customer or the join
becomes severed, but it is a very rare ocurrence to change the customers name,
unless a typo mistake was made and went unchecked by the user. The other
disadvantage is that loans.cust_name is CHAR(30) vs. loans.cust_id is INTEGER,
therefore .dat and .idx file sizes are larger, but the realworld benefits
outweigh the standard approach for join strategy!




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3529]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
Posted by: frank.pr@msn.com (FRANK ROTOLO) - Mon, 08 Mar 2010 21:20:33 EST
Sorry, it's SE, but pre 4.X, try 2.10.06E (for DOS 6.22).
It may be a fosil, but gets the job done and since its not client/server
architecture (engine and tools tightly integrated), has a small footprint,
im using protected-mode "pstartsql" which manages its own XMS(64MB) +
SMARTDSK.SYS (DOS disk caching), all of this is encapsulated within a
DOS 6.22 virtual hard disk, under Windows Vista SP2 and IT SCREAMS!,
even when I loaded 200,000 test customers and 500,000 loan rows.

But, I have limitations because I can't even locate ESQL/C for this version,
ISQL 7.3 only runs on UNIX-variants, Don't have IDS, etc. etc.
Don't get me wrong, I'd rather run this app in Linux, since I have experience
dating as far back as ALTOS XENIX 3.0 (1983), but my user is scared of the
UNIX world, he's a DOS/WINDOWS die-hard.

As for maintaining customer rows sorted when adding new customers, after
CLUSTERING:

After adding a new customer, How come when in the perform screen,
I press "Q"(Query), press "Esc"(Process) without entering any criteria at all,
it returns all rows in the customers table, I press "N"(Next) until I locate
the newly added customer, and its in the proper place!(not the last row of the
table)?

proof is: I unloaded the table without ordering it with:

UNLOAD TO "testorder.unl" SELECT * FROM customers;

then examined the .unk file with an editor and the newly added customer
was in the proper place, not at EOF.




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3528]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
Posted by: frank.pr@msn.com (FRANK ROTOLO) - Mon, 08 Mar 2010 20:45:56 EST
1. Reason I unload/re-load customers and loans tables is:

a. I dont use the unload statement to unload customer rows, I use an

ACE report to compute certain statistics which go into each customers

rows. It may seem awkward, but it serves my purpose, example:

database pawnshop end

define
variable act integer
variable ret integer
variable ven integer
variable tot integer
variable edate date
variable udate date
variable totprof integer
variable press char(1)
end

input
prompt for press using
"PRESS ENTER TO COMPUTE STATISTICS AND CREATE UNLOAD FILE"
end

output
top margin 0
bottom margin 0
left margin 0
right margin 384
report to "customers.unl"
page length 32767
end

SELECT

pa_store_id,

pa_user_id,

pa_cust_name,

pa_id_type,

pa_id_no,

pa_dob,

pa_address1,

pa_address2,

pa_city,

pa_state,

pa_zip,

pa_tel,

pa_cmt,

pa_entry_date,

pa_last_date,

pa_active_pawns,

pa_redeem_pawns,

pa_overdue_pawns,

pa_total_pawns,

pa_profit,

pwd_cust_name,

pwd_last_type,

pwd_last_pymt,

pwd_pawn_amt,

pwd_last_amt,

pwd_cob1,

pwd_cob2,

pwd_cob3,

pwd_cob4,

pwd_cob5,

pwd_cob6,

pwd_update_flag

FROM customers, OUTER loans

WHERE pa_cust_name = pwd_cust_name
ORDER BY pa_cust_name, pwd_last_pymt

end

format

on every row

if pwd_last_type = "E" then
begin
let act = act + 1
let tot = tot + 1
let totprof = totprof + pwd_pawn_amt
end

if pwd_last_type = "I" then
begin
let act = act + 1
let tot = tot + 1
let totprof = totprof + ((pwd_cob1 + pwd_cob2 + pwd_cob3 +

pwd_cob4 + pwd_cob5 + pwd_cob6) - pwd_pawn_amt)
end

if pwd_last_type = "R" then
begin
let ret = ret + 1
let tot = tot + 1
let totprof = totprof + ((pwd_cob1 + pwd_cob2 + pwd_cob3 +

pwd_cob4 + pwd_cob5 + pwd_cob6) - pwd_pawn_amt)
end

if pwd_last_type = "F" then
begin
let ven = ven + 1
let tot = tot + 1
let totprof = totprof + ((pwd_cob1 + pwd_cob2 + pwd_cob3 +

pwd_cob4 + pwd_cob5 + pwd_cob6) - pwd_pawn_amt)
end

if pwd_last_type = "T" then
begin
let ven = ven + 1
let tot = tot + 1
let totprof = totprof + ((pwd_cob1 + pwd_cob2 + pwd_cob3 +

pwd_cob4 + pwd_cob5 + pwd_cob6) - pwd_pawn_amt)
end

before group of pa_cust_name
let totprof = 0
let tot = 0
let act = 0
let ret = 0
let ven = 0
let edate = pa_entry_date
let udate = pa_last_date

after group of pa_cust_name

print column 1, pa_store_id clipped,"|",

pa_user_id clipped,"|",

pa_cust_name clipped,"|",

pa_id_type clipped,"|",

pa_id_no clipped,"|",

pa_dob using "mm-dd-yyyy","|",

pa_address1 clipped,"|",

pa_address2 clipped,"|",

pa_city clipped,"|",

pa_state clipped,"|",

pa_zip clipped,"|",

pa_tel clipped,"|",

pa_cmt clipped,"|",

edate using "mm-dd-yyyy","|",

pwd_last_pymt using "mm-dd-yyyy","|",

act using "&&&&","|",

ret using "&&&&","|",

ven using "&&&&","|",

tot using "&&&&","|",

totprof using "-&&&&&","|"
end

---

2. The processing time is not costly because the customers table has only

4,500 rows and new customers grow 10/month on average.

3. There are other indexes on customers which get re-created.

4. Seems like everything gets better optimized doing it this way.

5. Re-creating all the indexes takes less time since the loaded file is

already in sorted order.

6. Somehow, customer rows remain sorted by name, after new customers are

added, even before re-clustering it!

7. My version of ISQL doesnt have HIGH, MEDIUM or LOW for UPDATE STATISTICS,

it just has UPDATE STATISTICS [table]. Heck!.. I don't even have SPL's,

this is version 2.10.06E in MS-DOS 6.22!

(You wouldn't believe some of the work-arounds and tricks I've had to

create to make this system sufficiently functional for my client who

refuses to upgrade, but rather pay me high fees to maintain this relic!)

- alter table customers add constraint primary key(cust_id) constraint ...

^

NO SUCH ANIMAL IN 2.10




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3527]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
Posted by: art.kagel@gmail.com (Art Kagel) - Mon, 08 Mar 2010 19:25:33 EST
Wait,is this Standard Engine (SE)? You didn't mention you are using
Standard Engine! I suppose if we all looked back at your previous post we
might have guessed that (since you didn't mention it them except by
inference), but I for one don't do that. I don't have time. You REALLY
should post your engine and platform details when you first post a question.

If so, then ignore what I wrote about UPDATE STATISTICS HIGH and MEDIUM,
that only applies to Informix Dynamic Server. However, all of the comments
everyone said about CLUSTER indexes is still true. Standard Engine (nor IDS
for that matter) does NOT maintain your clustered indexed table in sorted
order after the initial clustering. The ONLY reason that data is being
returned in sorted order is that SE is using the index to find the records.
It is using a key lookup to find the first matching row (if there is a
filter) and then scanning across the index leaves which returns the keys and
rowids in sorted order by the index or cluster key. Since the index was
initially clustered, the majority of rows are right next to each other on
disk so access is very fast. Newly added rows will force additional disk
reads, but each read will pull multiple rows into memory so it will not
affect performance until a significant percentage of the rows are no longer
clustered. The exact percent depends on the number of rows that fit on page
on disk. (Well, SE doesn't really organize data in pages on disk, but the
IO buffer it uses to read a block of rows into memory works the same way.)

By the way, unless you include an ORDER BY clause, the order of returned
rows, EVEN WITH A CLUSTERED INDEX, is not guaranteed! Posting this
application to IDS someday where the optimizer is smarter and is likely to
ignore the clustered index if there is no ORDER BY clause (and sometimes
even if there is one) is likely to break your application because the
optimizer may be able to retrieve the data faster using a different index or
sort it faster than it can be fetched using the index, even in leaf scan
mode.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.

On Mon, Mar 8, 2010 at 3:08 PM, FRANK ROTOLO <frank.pr@msn.com> wrote:

> I am using INFORMIX-SQL (perform, ace).
> My cluster indexes seem to maintain the rows physically ordered in the .dat
> files because after adding new customers or loans, I query those tables and
> the newly added customers appear to be in sorted order.
>
>
>
>

>
>
>

--0015173fe862d9bf170481533738




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3526]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
Posted by: art.kagel@gmail.com (Art Kagel) - Mon, 08 Mar 2010 18:51:56 EST
First, it is not neccessary to unload and reload your data daily. You only
need to drop and recreate the clustered index or ALTER INDEX custname TO NOT
CLUSTER; ALTER INDEX custname TO CLUSTER; which will rewrite, compress, and
order the rows in the table completely.

Second, the CLUSTERing of the index is necessary because the optimizer
doesn't know that the data is ordered in the table unless there is a
CLUSTERED index.

Third, just running UPDATE STATISTICS; twice with no options is:
A) Inefficient since with no arguments update statistics processes all
tables and all stored procedures, so you are doing all of that work twice.
B) Insufficient. You have running the equivalent of UPDATE STATISTICS LOW;
which only gathers the lowest level data and does not calculate or store the
data distributions that the optimizer needs to make good decisions.

I would try the following and will be surprised if this isn't even faster
than what your are doing now and your nightly processing will take only
seconds:

- drop index custname;

- drop index loanclidxto;

- create unique index customers_pk on customers(cust_id);

- create index customers_ix1 on customers( custname, cust_id );

- alter table customers add constraint primary key(cust_id) constraint

customers_pk;

- create index loans_fk1 on loans( cust_id );

- alter table loans add constraint foreign key loans_fk1(cust_id)

references customers( cust_id ) constraint loans_fk1;

- Only the following steps need to be repeated every few days (though

probably not every day):

- update statistics MEDIUM for table customers;

- update statistics HIGH for table customers( cust_id, custname );

- update statistics MEDIUM for table loans;

- update statistics HIGH for table loans( cust_id );

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.

On Mon, Mar 8, 2010 at 2:57 PM, FRANK ROTOLO <frank.pr@msn.com> wrote:

> At the end of each business day, before system shutdown, the following SQL
> proc is executed:
>
> unload to "customers.unl"
> select * from customers
> order by customers.cust_name;
>
> drop table customers;
>
> create table customers ...;
>
> load from "customers.unl"
> insert into customers;
>
> create unique cluster index custname on customers(cust_name);
> {cluster probably not necessary since loadfile's already sorted in name
> order}
>
> update statistics;
>
> ....
>
> unload to "loans.unl"
> select * from loans
> order by loans.cust_name;
>
> drop table loans;
>
> create table loans ...;
>
> load from "loans.unl"
> insert into loans;
>
> create cluster index loanclidx on loans(cust_name);
> {probably not necessary since loadfile's already sorted in name order}
>
> update statistics;
>
> ---
>
> so, this speeds up query and update times, but if I use the cust_id SERIAL
> strategy, query response times are slow!..
>
>
>
>

>
>
>

--0015174768a2b09176048152bfc9




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3525]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Re: Trying to send tab delimited type output
Posted by: acurry1024@yahoo.com (Andy Curry) - Mon, 08 Mar 2010 15:59:11 EST
Laz -

Easiest and clearest way to use double quotes and various other characters is
to define one-character variables and assign values to them using the CHR()
function or literals:

define quot char(1),

comma char(1)

let quot = chr(34);
let comma = ","
etc..

Then, whenever you want to print a double quote,

print quot, string_to_print, quot;

Andy Curry

46 Forestvale Drive

Chesterfield, Missouri 63017

314-205-8152

http://www.andycurry.info

--- On Mon, 3/8/10, LazarusGOP@aol.com <LazarusGOP@aol.com> wrote:

From: LazarusGOP@aol.com <LazarusGOP@aol.com>
Subject: Trying to send tab delimited type output [3519]
To: classics@iiug.org
Date: Monday, March 8, 2010, 10:35 AM

Dear Informix group:

I normally send any output to excel from Informix with no problem, but now
there are some very specific constraints to load up an external windows
program. It uses the tab delimiter and the " (quote) just like access, and If
I do it in a report I am having trouble with the " quotes since they are to
definers. And if I do it in TEMP file, not quite sure how to get the
desired outcome. Any suggestions are welcome. Thank You.
Laz
The Goal is to send data from the Informix Database to feed into (Import)
to a little windows program that prints a specific government form in PDF
format...(the program allows import but its very specific such as the sample
below)...

Here is the desired outcome. Its one long string of data with delimiters,
See an idea this is part of the string:
(mailto:classics@iiug.org)
"HAPPY CHOICE OF FLORIDA,""ORLANDO, FL 33409"",""1243 E. HALLANDALE BCH B
VD"",""407-854-8999""" ","""","""",""""" 5293*185778
329
681146924 10/30/2009 10/31/2009 ",""""" "SANFORD, GRACE"
"884 HAWKINS AVE, ORANGE PARK, FL 32065"
8/23/1964 F 9/28/2009 1
1
",""""" 27 9/28/2009

",""""" "BLUE CROSS BLUE SHIELD OF FLORIDA,""P.O. BOX 1
98"",""JACKSONVILLE, FL 322310014"","""",""""" 61 27260

"0023,""0551"",""0551"",""0551"",""0571"",""0551"",""0551"",""0551"",""
"","" "
,"" "","" "","" "","" "","" "","" "","" "","" "","" "","" "","" "","" """
"HOME HEALTH SERVICES,""SKILLED NURSING VISIT"",""SKILLED NURSING
VISIT"",""SKI
LED NURSING VISIT"",""HOME HEALTH AIDE VISIT"",""SKILLED NURSING
VISIT"",""SKIL
ED NURSING VISIT"",""SKILLED NURSING VISIT"","" "","" "","" "","" "",""
"","" "
,"" "","" "","" "","" "","" "","" "","" "","" """
"1CGK1,""G0154"",""G015
"",""G0154"",""G0156"",""G0154"",""G0154"",""G0154"","" "","" "","" "",""
"",""
"","" "","" "","" "","" "","" "","" "","" "","" "","" """
"09/29/09,""10/
0/09"",""10/30/09"",""10/30/09"",""10/30/09"",""10/31/09"",""10/31/09"",""10
/31
09"","""","""","" "","" "","" "","" "","" "","" "","" "","" "","" "",""
"","" "
,"" """ "6,""2"",""2"",""2"",""2"",""2"",""2"",""2"","""","" "","" "",""
"",""

Patient Support Team
305 222-8375








* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3524]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
Posted by: frank.pr@msn.com (FRANK ROTOLO) - Mon, 08 Mar 2010 15:35:52 EST
In my app, customers are the master table, loans are the detail table.
My users access customers with INFORMIX-SQL "perform" screens.
My users query by customers name using wildcards. the query result sometimes
returns 4 to 35 matches, depending on how well my users specify the customers
name with the wilcards,

example: ROD*GON*CAR* to locate RODRIGUEZ GONZALEZ, CARLOS

If this query returns say 12 results, the users press "N"(Next) until they
locate the right customer. Then the users press "D"(detail) and perform
makes the loans table active, automatically queries the loans belonging to
the selected customer (a "customers master of loans" statement is declared in
the instructions section of the perform source code), then the users press "N"
to view the next loan transaction until the find the right one in order to
update it with a payment.




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3523]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
Posted by: frank.pr@msn.com (FRANK ROTOLO) - Mon, 08 Mar 2010 15:08:11 EST
I am using INFORMIX-SQL (perform, ace).
My cluster indexes seem to maintain the rows physically ordered in the .dat
files because after adding new customers or loans, I query those tables and
the newly added customers appear to be in sorted order.




* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

To post a response via email (IIUG members only):

1. Address it to classics@iiug.org
2. Include the bracketed message number in the subject line: [3522]

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *