Ask a Question related to Informix, Design and Development.
-
Jack Parker #1
re: Informix limitations, should we be using Oracle?
I must have been offline during this discussion. I ran across it today
looking for something else. Although late, I will add my .02. (embedded).
The next time one of these come around and I'm not visible - please alert me
to it?
cheers
j.
We all know about benchmarks - regardless - TPC-C is not the benchmark you>
> Hi,
>
> We are trying to implement what will become a multi-terrabyte data
> warehouse on Informix XPS but have hit a number of significant
> problems. We are at the point where we are considering switching
> database providers to Oracle but want to be sure that the problems we
> are encountering are indeed valid issues. We have prepared a document
> of which I have included an extract that details the issues we are
> having. If anyone can provide me with feedback on these issues to let
> me know if I'm barking up the wrong tree or if indeed they are issues,
> it would be greatly appreciated. We are currently running Informix XPS
> 8.3.1 on a 4CPU, 4Gb HP N4000 with HP-UX 11.11.
>
>
> Informix XPS Issues and Comparison to Oracle
>
> Performance
>
> As detailed on TPC websites
> [url]http://www.tpc.org/tpch/results/tpch_results.asp?orderby=dbms[/url]
> [url]http://www.tpc.org/information/benchmarks.asp[/url]
would want to use to measure data warehouse performance, you would really
want TPC-H, (as you mention below) - but even that is flawed in that it
insists upon ongoing transactions (which are not normal warehouse processes)
during the benchmark.
Alas these results are long gone, IBM is also not in the business of>
> Looking at the TPC-H results for a 1000Gb database running on an
> HP9000 Superdome
> runs 2.65 times faster than XPS. The pricing of the two databases as
> reflected in the Price/QphH also shows that Oracle represents 3.5
> times more 'bang for the buck' than XPS.
>
benchmarking XPS, however; having worked with both databases, I can assure
you that XPS will scale infinitely while Oracle will not.
The intent of memory allocation is to pre-allocate resources to intensive>
> Issues :
>
> Memory Management - CRITICAL
>
> XPS has an essential flaw in it's memory management implementation for
> parallel Decision Support System Queries. The Resource Grant Manager
> configuration makes it necessary to allocate either large memory
> segments or small memory segments to all sessions utilising Decision
> Support Resources (large joins, sorts, ordering etc).
>
> It is expected that intention is so that Decision Support System (i.e.
> Warehouse queries - DSS) queries can preallocate huge memory segments
> through this configuration.
queries. This is by no means a requirement for XPS, nor is it a bad thing.
With judicious use this memory can be put to very advantageous use in index
building, hash joins, groups and sorts - I gather Oracle has a similar
capability, I have not seen it clearly put to use yet.
Well, not quite. You can allocate as much memory AS YOU ARE ALLOWED TO ->
> When DSS queries are issued the memory is allocated up to the
> DS_MEMORY_TOTAL. When this occurs, all other DSS queries are queued.
> It should be noted that the memory allocated is a fixed amount,
> regardless of the complexity or priority of the query being executed -
> i.e. Simple counts are allocated the same amount of memory as massive
> join and sort queries
which is under the control of the DBA. I realize that with Oracle a 'simple
count' requires a full table (or index) scan, with Informix it is a single
read against the table header and virtually instantaneous. You can also use
a light scan for a filtered read (count(*) ... where condition) - this does
not chew up memory. Oracle has no equivalent to the light scan - which is
on average 4x faster than a traditional read.
Yes, you can give a query enough memory so that other queries are gated and
will not interfere with your process while it runs. This is preferable to
the thrashing which would occur if this were not an option.
Is it not a wonderful thing when you can fully utilize the power of the>
> ETL tools parallelise their processing for enhanced performance. Using
> XPS, it is not uncommon that queries issued in the same program may
> expend all of the DS_TOTAL_MEMORY and other SQL statements within the
> same program are queued. This is the cause of the classic Dawa problem
> - 'the locked plan'.
database and the machine with one query? At the same time you can prevent
this from occuring.
Not quite, only those which require DSS resources, so your 'simple count'>
> This situation is exacerbated by the fact that the other statements
> within the program still retain their memory in Informix - so all SQL
> statements within the database are 'locked'.
would go straight through.
Exceeding temp space is a DBA matter - much akin to exceeding the size of a>
> A resolution to this is to create small memory allocations for each
> session. Underallocating the memory segment size causes Reports (which
> do lots or GROUPING and ORDERING) to run extremely slowly, or exceed
> temp space allocation and fail.
rollback segment under Oracle. Either you are properly sized or you are
not. XPS, and all Informix engines, will use what memory is available to
the process and swap to disk what is not - this is the same thing that
Oracle will do. If you don't have enough disk - well you're SOL.
Actually IDS and XPS have the same memory allocation features (PDQPRIORITY),>
> Oracle (or Informix IDS) does not employ the Resource Grant Manager
> architecture. Small queries use small memory, and large queries use
> large amounts of memory as required. This means the database slows
> down, but does not lock on memory.
although under IDS it's called Memory Grant Manager, but they're the same
thing. I have never seen XPS 'lock on memory'.
What have you been smoking? Where do you get this 'it is evident'? XPS>
> It should be noted that XPS (Extended Parallel Server) refers to
> parallelism in Platforms - it is evident that for SQL queries that it
> is NOT optimised for parallelism.
>
performs in parallel with everything, across all horizontal and vertical
portions of an operation. It exhibits the highest degree of parallelism
that has ever been offered to the public.
This is an issue which was corrected in 8.32, which was released 2.5 years> Removing Sessions
>
> Informix XPS 8.31 D has an outstanding bug in which it is not possible
> to remove a Informix client session with assurance. Failed attempts to
> issue the remove session command have resulted in:
> · reboot of database instance
> · reboot of Unix
> · inability to restart the database instance
ago. At your writing XPS was up to version 8.4
And this is a good thing. Alas with Oracle (and db2) all user sessions are>
> Neither does it appear possible to link an Informix client session to
> a Unix session id / thread - making it impossible to sessions to be
> identified and removed at the Operating System level.
operating systems processes, this means that context switching is removed
from the control of the database and handed to the operating system which
really has no clue as to what's going on inside the engine. Believe me, you
want the threads within the engine and not subject to the OS overhead. You
can in fact determine which sessions are tied to which clients with a simple
onstat command.
And sometimes this will even remove the locks held by these sessions when>
> Oracle, however, runs each session as an easily identifiable Unix
> process, and sessions can be removed either through DBA commands, or
> at the Operating System Level.
they are thusly killed.
Not quite. At the time you wrote this, a counter was incremented to>
> Exceeding lock count
>
> Informix locks database records using memory latches. A maximum number
> of memory latches is specified in the config file. When this limit is
> exceeded the database server crashes.
indicate that the lock threshold had been exceeded. However in current
releases Informix has dynamic locks, more locks are allocated as needed.
There indeed were issues with XPS 8.31 where restarting the instance could>
> This is exacerbated when the database restarts and attempt to rollback
> the transaction that caused the locks to be exceeded - this again
> exceeds the maximum locks. In our experience, the only solution is to
> destroy and recreate the database.
cause issues. Normally, those with support contracts would call the 'Down
Systems Group' which will answer the phone in under a minute and provide
sunset support (i.e. 24x7) until your engine is back on line. Those without
support would call the same group and be gently scolded - and then supported
in the same fashion. The last time I called Oracle support, I got a call
back three days later telling me to upgrade.
Which can be a lot of fun. But this is a moot point. It's a question of>
> Oracle does not use memory latches to lock records. It's standard
> row-level-locking is managed in the rollback segment architecture.
> Exceeding the rollback segment will not crash the database - it causes
> the session to rollback the current SQL transaction.
>
sizing - if you size your rollback segments improperly - or choose the wrong
one with which to perform your transaction - you are hosed. The lesson is
that if you size things properly they work - whether they are Oracle or
Informix.
There is indeed no 'Informix' tool to compare to the Oracle Enterprise>
> Lack of diagnostic/monitoring tools
>
> No user friendly or graphical monitoring tools are available. This
> makes diagnosis and performance monitoring extremely difficult.
>
Manager (did I get that right?). However there is a single standard command
(onstat) to which all monitoring activity has been tied - not to mention the
SQL interface to this same data. This can be much more effective than
waiting around for a GUI to load and display data which you then have to
drill into. I have to admit as an Oracle neophyte that I find the Oracle
GUI useful for handling tasks without any understanding of what lies beneath
them.
I'm not sure to which problem you refer. Raw partitions are very simple to> Many monitoring and diagnostic tools are available for Oracle. Many of
> them are free. Administration, diagnosis and monitoring is vastly
> simplified.
>
> Raw partitions exacerbate the problem.
>
manage.
'Standard' is either 'cooked files' (OS files) or raw devices. You get a>
> Raw partitions and storage management
>
> Standard data storage in Informix is via RAW unix partitions. These
> partitions cannot be monitored via standard Unix utilities. They are
> more difficult to manage and create - Unix 'root' privileges are
> required to manage them. Furthermore, raw partitions used by Informix
> cannot be resized or grown. A completely new partition must be
> allocated.
>
15% performance improvement with raw devices. Oracle has the same feature.
There is no similar benefit to raw devices under Oracle - I don't remember
why at present. With a raw device Informix gets to control I/O to the
device itself instead of going through the Unix file system - which tends to
not only slow things down, but opens a window of vulnerability - you write
to disk and commit, the database writes it to disk and thinks it made it,
but the OS didn't get that far - so your 'committed transaction' wasn't.
Management of raw spaces is incredibly simple - especially under XPS where
you can use wildcards to manage hundreds of such spaces at once.
True.> Informix can use cooked partitions, but it is not recommended by
> Informix for performance reasons.
Oh right - I forgot about the resize thing - I've already mentioned the Unix>
> Oracle standard data storage is via datafiles, which are 'normal' unix
> files. These files can be monitored for growth and utilisation much as
> any other Unix file. No speial system privileges are required to add
> or resize datafiles.
filesystem issues. How can you resize a disk? If you buy an 80 GB disk -
can you make it larger? So I guess you're right, under Informix you can't
resize a raw disk, however you can add more of them and include them in the
same dbspace (tablespace) so that the new space is usable by tables which
need to grow in that space. So in fact you can 'resize' a dbspace.
That is a good thing. We are delighted that Oracle has come to the table>
> Fast Loading
>
> Our data warehouse employs fast loading capabilities (bypassing ODBC)
> in order to upload Mainframe EBCDIC data.
with a fast (direct path) loader. We hope someday that it shows the same
sort of performance as is capable with the Informix parallel loader. We
further hope that some day it will allow you to unload data in the same
fashion.
Actually HPL and the light append has been around since 7.x, I first used it>
> This feature is particular in Informix to XPS; in Oracle it is
> standard functionality.
around 1996.
Actually this is true with XPS. You do get a header to each row indicating>
> The reject output from Informix is a single-file mixture of ASCII
> error report and EBCDIC data. It is impossible to re-upload rejected
> records from an Informix EBCDIC reject file - All packed decimal, for
> instance, is corrupt.
the error, you have to trim the header to retrieve your original data.
Interesting - this would be a good feature request if not already done.
With 7.x and 9.x engines the error is written to a separate file than the
error message or you have the option of allowing it to load and then picking
it out of a violation table later. For more on this topic visit
[url]www.artentech.com/downloads.htm[/url] and look for the Informix Load FAQ.
If you have an error, then there is a problem. In a DSS world, you don't>
> Oracle produces two output files when EBCDIC uploading - an error log,
> and an EBCDIC reject file - ready for reprocessing.
>
want to go chasing singleton rows, you want to do the whole thing again.
This is ok when you are dealing with a small number of rows and can afford> We currently have to reprocess complete datasets, or reject complete
> datasets. With Oracle this would not be necessary.
the luxury of debugging each one. When you are dealing with billions of
rows and you have a problem, you really don't want to get into the details
of any individual row. I guess it's a question of how big you intend to
get.
Not sure what you're saying here. If you have 18 files to load, under XPS>
> The administration of the External Table definitions in Informix is
> excessively onerous also. For instance - 6 sales invoice files for
> three states necessitates 18 external table definitions and 18
> internal table definitions. Addition of EBCDIC datafiles for other
> states necessarily implies additional external and internal
> definitions.
you can define them all as a single external table. You can define this
single external table across filename, filesystems, coservers(each instance
in a cluster) and servers, you can define it with a single file statement
even (not 18) - it's pretty powerful. Furthermore, this file (or external
table) is visible to the database - you don't have to go through sqlldr to
get it into the database, you can treat it as a table and select columns
from it directly, or join it with other tables - whatever. It's really
cool. Even cooler - you can unload to it directly with a 'select <columns>
from <> insert into <>' statement.
See above.>
> With Oracle only 6 internal tables are necessary - generic to the
> Invoice, they could be used for all states. This vastly simplifies
> Data Dictionary management.
>
Ok.>
> Limitation comparisons
>
> Informix XPS
> 18 characters for database objects
You use indices? Are you in a data warehouse environment or an OLTP> 256 byte maximum row size in Indexes
environment? XPS can perform the hash join so quickly that indices are a
detriment, not a requirement. What you might consider a fast join between
two tables with indices can be performed 100 times faster with a hash join -
but then you need some memory to play these sorts of games. Have a look at
[url]http://www7b.boulder.ibm.com/dmdd/zones/informix/library/techarticle/parker/[/url]
0502parker.html for some examples.
True, although you can go to text and have at least 2GB last time I> 255 byte limit on varchar fields
checked.
What? Sure you can.> SQL Restrictions -
> Cannot SELECT NULL
Create one. It's the same thing. I don't generally bother, I 'select> No general DUAL table
'whatever' from systables where tabid=1'; It's the same thing.
True, that's a neat Oracle feature.> No MINUS operations
> No GROUP BY or Aggregation in SUBQUERY, NESTED QUERY, COMPOUND QUERY,Haven't tried this. Never needed it.> EMBEDDED QUERY
Sure there is. Or do you mean that you want all rows from table 'a' and 'b'> No Full OUTER JOIN
even where neither intersect. Who's been dreaming up your data models? You
need to clean up your data if this is a requirement. But then I guess
Informix doesn't do that particular join.
Not anymore, you can ask for one if you need it.> No Implicit ROWID
Totally untrue. For that matter, has Oracle come up with a true flatfile>
> XPS - No true database flatfile Import Export
export yet?
Well, you can use the ANSI standard if you like, you can also allocate a> Varchar fields not treated as ANSI standard
minimum to each varchar which is incredibly useful when it comes time to
update. Unlike Oracle, which compresses data as much as possible to
preserve space - and then when you need to update it - oooops won't fit into
it's original space - have to move it to a new page - which can be time
consuming - the reservation of space allows the intelligent DBA to
counteract this sort of thing.
Bullshit. You need to spend more time with the manual.> No DISABLE CONSTRAINT commands - Must DROP and RECREATE constraints -
Stored procedures, when compiled are checked against what is available -> Additional processing
> Stored procedures are not prepared - Object dependence not checked
> until runtime
compilation will fail if a required object is not present. What you should
properly say is that Oracle's PL/SQL is far superior to the Informix stored
procedure language. Within Oracle you can develop entire applications with
PL/SQL, the Informix approach to Stored procedures is more basic - to
perform this or that function - program logic does not truly belong in an
Informix SPL - more properly in one of it's more standard languages 4GL or
esql/c. Actually I'm not that crazy about the way Oracle will mark a
procedure as invalid when something that it depends on changes. This can be
a real pain to manage. I know what I need to rebuild - I don't need the
database thinking it knows better than me.
or 8K. This isn't really an issue, unless you have some reason to need a> Page size can only be 2 or 4k
48K page? You have data rows which are bigger than 8k? Why? Have you
considered a relational model? Informix will still support big rows, but
they will span pages.
If you can't fit it into 18 - why is 30 better? Why don't you bring up 9.4>
> Oracle
> 30 characters - more meaningful names
and it's support for object names of 128? IDS is the engine you should be
comparing to, not XPS. Oracle doesn't have anything to compare to XPS.
(this was the index size - not sure why you want this)> Unlimited
(2GB limit on text)> 4000 limit
How about 'order by 1,2,3'? You guys sure do like to type, especially those> No comparable restrictions
30 character names.
You're 'comparable restrictions' refer perhaps to the ANSI standard which is
a subset of all RDBMS vendors SQL.
But not a true flatfile.> Flatfile IMPORT EXPORT available for backups and rebuilds
Moot.> No comparable ANSI incompatibility
The same basic stuff is available. Since this is not ANSI standard the name> DISABLE ENABLE DEFER VALIDATE NOVALIDATE CONSTRAINT possible
may vary slightly.
??? Informix stored procedures are also compiled into code within the> Stored procedures are compiled as PCODE
engine. Not sure where you are getting this stuff.
No, actually 8 times more reads. When I want one row, I don't want to pull> Page size can be as large as 64K - 16 times fewer reads for DSS table
> scans
64K, or 8 times the standard disk buffer - hence 8 disk reads instead of 1.
Untrue again. Hybrid fragmentation has been around for some time ->
> Warehouse functional comparisons
>
> Informix
> Tables and related indexes are partitioned on the same key -
including back in 8.31.
You need to be pulling out materialized views here - which is the proper> inflexible configuration
> GK Indexes - Index prejoin only
comparison to the GK index.
Sorry? This is an Oracle issue - you need two layers of security, Informix> Necessary to manage security utilising both OS and database levels
uses the OS security.
Who?> Sagent supports ODBC only for Informix
Moot.>
> Oracle
> Partitioning tables and indexes on alternate keys - flexible
> configuration for performance
Yes, but then you have to refresh that summary table. I have no real> Materialised Views - Database managed JOINS of tables - Physically
> existing summary tables for enhanced performance. Time savings in
> aggregations
quarrel here, but you're comparing quasi-identical features and claiming
it's bad there and good here.
Ah, for clients perhaps. Still Informix is relying on their ability to> Security and permission totally managed within the database
connect to the server, not on an additional layer of security within the
database.
I guess if Sagent is a requirement you should evaluate whether they are Open> Sagent support for High Speed Loading into Oracle
>
enough for your current and future requirements.
-----
But then you are comparing volkswagens and ferraris. XPS is a true
clustered shared nothing environment which can scale literally infinitely -
ok I am getting away from myself - it can only have 32K coservers and each
can only support 32 Pedabytes of data - so if you need to go beyond that
you'd have to pick up the phone and ask the engineers to bump the counter -
the thing is that with XPS this is not a theoretical limit, performance does
not drop off as you add more instances (coservers) to the cluster - it
continues to scale linearly. With Oracle this will only happen on a slide
projector. There are only two other databases currently available which
have the architecture to scale in this fashion - DB2 (pdf or whatever it's
called now) and Teradata. If you want to compare Oracle - you need to be
comparing to Informix 9.
Yeah sure RAC - give me a break, it's all controlled through a single
manager. Bottleneck city.
Perhaps obviously you have (had) a need to justify going to Oracle over XPS.
Perhaps you know Oracle better and are more comfortable with it - fine -
that's a good enough reason. DBA skills are more generally expensive than
the database itself. Just don't try to justify it by trying to compare an
apple with a mango.
As far as features as concerned, you've mentioned quite a few that XPS
doesn't have, or that you haven't uncovered yet. You might also want to
look at the XPS 'UPDATE JOIN' and 'DELETE JOIN' statements - talk about
supporting a warehouse - they rock. There are others, but I digress.
Tell you what. Send me a copy of your schema, some data and some code, and
I'll put my XPS instance (P4 2.4Ghz, 2xIDE box) up against whatever you
have. Maybe IBM won't benchmark, but I will.
cheers
j.
Jack Parker Guest
-
Oracle V Informix
Prior to my current Oracle job I was an Informix DBA (sigh!). I've reached the following conclusion after a very short time. Oracle sucks. ... -
Informix versus Oracle
Dobry den Marian dakujem, ani som netusil, ze robite so Spatial datablade. Cital som vyskum od Bloor kde sa pise, ze oslovili nejaky... -
posting to informix newsgroup instead of oracle!
Hi Informix Gurus, I posted a simple question to oracle newsgroup but that place is very silent. May be you can help me . Here is my question about... -
oracle cache fusion against informix XPS?
Hi Gurus, I was an Informix man , but my last job needs Oracle skills which i have no experience. My question is about oracle real application... -
Access to Oracle and Informix DB
"Rich Campbell" <rich.campbell@att.com> wrote: So far, I've used separate programs written in Oracle Pro*C and Informix ESQL/C. The Pro*C... -
Jean #2 Re: Informix limitations, should we be using Oracle?
I read all you post and it was very interesting...
I have only 2 thing to say and have in count that I don't know anything
about XPS:
1. In IDS 9.x, you can't select a NULL, you can use it in an UPDATE,
INSERT or DELETE. You can workaround this by creating an sp sp_genull()
who return a null and that do it... but is ugly...
If XPS can do it... it's good, inf fact there are some very cool
features only in the XPS SQL I wish to have in IDS.
2. In IDS there is no FULL OUTER JOIN... in some cases this is very
usefull. If XPS have it it's good.
As I said, I wish SQL-IDS has more features of XPS-SQL
Chucho!
Jack Parker wrote:
>
>
> We all know about benchmarks - regardless - TPC-C is not the benchmark you
> would want to use to measure data warehouse performance, you would really
> want TPC-H, (as you mention below) - but even that is flawed in that it
> insists upon ongoing transactions (which are not normal warehouse processes)
> during the benchmark.
>
>
>
>
> Alas these results are long gone, IBM is also not in the business of
> benchmarking XPS, however; having worked with both databases, I can assure
> you that XPS will scale infinitely while Oracle will not.
>
>
>
>
> The intent of memory allocation is to pre-allocate resources to intensive
> queries. This is by no means a requirement for XPS, nor is it a bad thing.
> With judicious use this memory can be put to very advantageous use in index
> building, hash joins, groups and sorts - I gather Oracle has a similar
> capability, I have not seen it clearly put to use yet.
>
>
>
>
> Well, not quite. You can allocate as much memory AS YOU ARE ALLOWED TO -
> which is under the control of the DBA. I realize that with Oracle a 'simple
> count' requires a full table (or index) scan, with Informix it is a single
> read against the table header and virtually instantaneous. You can also use
> a light scan for a filtered read (count(*) ... where condition) - this does
> not chew up memory. Oracle has no equivalent to the light scan - which is
> on average 4x faster than a traditional read.
>
> Yes, you can give a query enough memory so that other queries are gated and
> will not interfere with your process while it runs. This is preferable to
> the thrashing which would occur if this were not an option.
>
>
>
>
> Is it not a wonderful thing when you can fully utilize the power of the
> database and the machine with one query? At the same time you can prevent
> this from occuring.
>
>
>
>
> Not quite, only those which require DSS resources, so your 'simple count'
> would go straight through.
>
>
>
>
> Exceeding temp space is a DBA matter - much akin to exceeding the size of a
> rollback segment under Oracle. Either you are properly sized or you are
> not. XPS, and all Informix engines, will use what memory is available to
> the process and swap to disk what is not - this is the same thing that
> Oracle will do. If you don't have enough disk - well you're SOL.
>
>
>
>
> Actually IDS and XPS have the same memory allocation features (PDQPRIORITY),
> although under IDS it's called Memory Grant Manager, but they're the same
> thing. I have never seen XPS 'lock on memory'.
>
>
>
>
> What have you been smoking? Where do you get this 'it is evident'? XPS
> performs in parallel with everything, across all horizontal and vertical
> portions of an operation. It exhibits the highest degree of parallelism
> that has ever been offered to the public.
>
>
>
>
> This is an issue which was corrected in 8.32, which was released 2.5 years
> ago. At your writing XPS was up to version 8.4
>
>
>
>
> And this is a good thing. Alas with Oracle (and db2) all user sessions are
> operating systems processes, this means that context switching is removed
> from the control of the database and handed to the operating system which
> really has no clue as to what's going on inside the engine. Believe me, you
> want the threads within the engine and not subject to the OS overhead. You
> can in fact determine which sessions are tied to which clients with a simple
> onstat command.
>
>
>
>
> And sometimes this will even remove the locks held by these sessions when
> they are thusly killed.
>
>
>
>
> Not quite. At the time you wrote this, a counter was incremented to
> indicate that the lock threshold had been exceeded. However in current
> releases Informix has dynamic locks, more locks are allocated as needed.
>
>
>
>
> There indeed were issues with XPS 8.31 where restarting the instance could
> cause issues. Normally, those with support contracts would call the 'Down
> Systems Group' which will answer the phone in under a minute and provide
> sunset support (i.e. 24x7) until your engine is back on line. Those without
> support would call the same group and be gently scolded - and then supported
> in the same fashion. The last time I called Oracle support, I got a call
> back three days later telling me to upgrade.
>
>
>
>
> Which can be a lot of fun. But this is a moot point. It's a question of
> sizing - if you size your rollback segments improperly - or choose the wrong
> one with which to perform your transaction - you are hosed. The lesson is
> that if you size things properly they work - whether they are Oracle or
> Informix.
>
>
>
>
> There is indeed no 'Informix' tool to compare to the Oracle Enterprise
> Manager (did I get that right?). However there is a single standard command
> (onstat) to which all monitoring activity has been tied - not to mention the
> SQL interface to this same data. This can be much more effective than
> waiting around for a GUI to load and display data which you then have to
> drill into. I have to admit as an Oracle neophyte that I find the Oracle
> GUI useful for handling tasks without any understanding of what lies beneath
> them.
>
>
>
>
> I'm not sure to which problem you refer. Raw partitions are very simple to
> manage.
>
>
>
>
> 'Standard' is either 'cooked files' (OS files) or raw devices. You get a
> 15% performance improvement with raw devices. Oracle has the same feature.
> There is no similar benefit to raw devices under Oracle - I don't remember
> why at present. With a raw device Informix gets to control I/O to the
> device itself instead of going through the Unix file system - which tends to
> not only slow things down, but opens a window of vulnerability - you write
> to disk and commit, the database writes it to disk and thinks it made it,
> but the OS didn't get that far - so your 'committed transaction' wasn't.
> Management of raw spaces is incredibly simple - especially under XPS where
> you can use wildcards to manage hundreds of such spaces at once.
>
>
>
>
> True.
>
>
>
>
> Oh right - I forgot about the resize thing - I've already mentioned the Unix
> filesystem issues. How can you resize a disk? If you buy an 80 GB disk -
> can you make it larger? So I guess you're right, under Informix you can't
> resize a raw disk, however you can add more of them and include them in the
> same dbspace (tablespace) so that the new space is usable by tables which
> need to grow in that space. So in fact you can 'resize' a dbspace.
>
>
>
>
> That is a good thing. We are delighted that Oracle has come to the table
> with a fast (direct path) loader. We hope someday that it shows the same
> sort of performance as is capable with the Informix parallel loader. We
> further hope that some day it will allow you to unload data in the same
> fashion.
>
>
>
>
> Actually HPL and the light append has been around since 7.x, I first used it
> around 1996.
>
>
>
>
> Actually this is true with XPS. You do get a header to each row indicating
> the error, you have to trim the header to retrieve your original data.
> Interesting - this would be a good feature request if not already done.
> With 7.x and 9.x engines the error is written to a separate file than the
> error message or you have the option of allowing it to load and then picking
> it out of a violation table later. For more on this topic visit
> www.artentech.com/downloads.htm and look for the Informix Load FAQ.
>
>
>
>
> If you have an error, then there is a problem. In a DSS world, you don't
> want to go chasing singleton rows, you want to do the whole thing again.
>
>
>
>
> This is ok when you are dealing with a small number of rows and can afford
> the luxury of debugging each one. When you are dealing with billions of
> rows and you have a problem, you really don't want to get into the details
> of any individual row. I guess it's a question of how big you intend to
> get.
>
>
>
>
> Not sure what you're saying here. If you have 18 files to load, under XPS
> you can define them all as a single external table. You can define this
> single external table across filename, filesystems, coservers(each instance
> in a cluster) and servers, you can define it with a single file statement
> even (not 18) - it's pretty powerful. Furthermore, this file (or external
> table) is visible to the database - you don't have to go through sqlldr to
> get it into the database, you can treat it as a table and select columns
> from it directly, or join it with other tables - whatever. It's really
> cool. Even cooler - you can unload to it directly with a 'select <columns>
> from <> insert into <>' statement.
>
>
>
>
> See above.
>
>
>
> Ok.
>
>
>
> You use indices? Are you in a data warehouse environment or an OLTP
> environment? XPS can perform the hash join so quickly that indices are a
> detriment, not a requirement. What you might consider a fast join between
> two tables with indices can be performed 100 times faster with a hash join -
> but then you need some memory to play these sorts of games. Have a look at
> http://www7b.boulder.ibm.com/dmdd/zones/informix/library/techarticle/parker/
> 0502parker.html for some examples.
>
>
>
> True, although you can go to text and have at least 2GB last time I
> checked.
>
>
>
> What? Sure you can.
>
>
>
> Create one. It's the same thing. I don't generally bother, I 'select
> 'whatever' from systables where tabid=1'; It's the same thing.
>
>
>
> True, that's a neat Oracle feature.
>
>
>
>
>
> Haven't tried this. Never needed it.
>
>
>
> Sure there is. Or do you mean that you want all rows from table 'a' and 'b'
> even where neither intersect. Who's been dreaming up your data models? You
> need to clean up your data if this is a requirement. But then I guess
> Informix doesn't do that particular join.
>
>
>
> Not anymore, you can ask for one if you need it.
>
>
>
> Totally untrue. For that matter, has Oracle come up with a true flatfile
> export yet?
>
>
>
> Well, you can use the ANSI standard if you like, you can also allocate a
> minimum to each varchar which is incredibly useful when it comes time to
> update. Unlike Oracle, which compresses data as much as possible to
> preserve space - and then when you need to update it - oooops won't fit into
> it's original space - have to move it to a new page - which can be time
> consuming - the reservation of space allows the intelligent DBA to
> counteract this sort of thing.
>
>
>
> Bullshit. You need to spend more time with the manual.
>
>
>
>
> Stored procedures, when compiled are checked against what is available -
> compilation will fail if a required object is not present. What you should
> properly say is that Oracle's PL/SQL is far superior to the Informix stored
> procedure language. Within Oracle you can develop entire applications with
> PL/SQL, the Informix approach to Stored procedures is more basic - to
> perform this or that function - program logic does not truly belong in an
> Informix SPL - more properly in one of it's more standard languages 4GL or
> esql/c. Actually I'm not that crazy about the way Oracle will mark a
> procedure as invalid when something that it depends on changes. This can be
> a real pain to manage. I know what I need to rebuild - I don't need the
> database thinking it knows better than me.
>
>
>
>
> or 8K. This isn't really an issue, unless you have some reason to need a
> 48K page? You have data rows which are bigger than 8k? Why? Have you
> considered a relational model? Informix will still support big rows, but
> they will span pages.
>
>
>
> If you can't fit it into 18 - why is 30 better? Why don't you bring up 9.4
> and it's support for object names of 128? IDS is the engine you should be
> comparing to, not XPS. Oracle doesn't have anything to compare to XPS.
>
>
>
> (this was the index size - not sure why you want this)
>
>
>
> (2GB limit on text)
>
>
>
> How about 'order by 1,2,3'? You guys sure do like to type, especially those
> 30 character names.
> You're 'comparable restrictions' refer perhaps to the ANSI standard which is
> a subset of all RDBMS vendors SQL.
>
>
>
> But not a true flatfile.
>
>
>
> Moot.
>
>
>
> The same basic stuff is available. Since this is not ANSI standard the name
> may vary slightly.
>
>
>
> ??? Informix stored procedures are also compiled into code within the
> engine. Not sure where you are getting this stuff.
>
>
>
>
> No, actually 8 times more reads. When I want one row, I don't want to pull
> 64K, or 8 times the standard disk buffer - hence 8 disk reads instead of 1.
>
>
>
>
> Untrue again. Hybrid fragmentation has been around for some time -
> including back in 8.31.
>
>
>
>
> You need to be pulling out materialized views here - which is the proper
> comparison to the GK index.
>
>
>
>
> Sorry? This is an Oracle issue - you need two layers of security, Informix
> uses the OS security.
>
>
>
>
> Who?
>
>
>
> Moot.
>
>
>
>
> Yes, but then you have to refresh that summary table. I have no real
> quarrel here, but you're comparing quasi-identical features and claiming
> it's bad there and good here.
>
>
>
>
> Ah, for clients perhaps. Still Informix is relying on their ability to
> connect to the server, not on an additional layer of security within the
> database.
>
>
>
>
> I guess if Sagent is a requirement you should evaluate whether they are Open
> enough for your current and future requirements.
>
> -----
>
> But then you are comparing volkswagens and ferraris. XPS is a true
> clustered shared nothing environment which can scale literally infinitely -
> ok I am getting away from myself - it can only have 32K coservers and each
> can only support 32 Pedabytes of data - so if you need to go beyond that
> you'd have to pick up the phone and ask the engineers to bump the counter -
> the thing is that with XPS this is not a theoretical limit, performance does
> not drop off as you add more instances (coservers) to the cluster - it
> continues to scale linearly. With Oracle this will only happen on a slide
> projector. There are only two other databases currently available which
> have the architecture to scale in this fashion - DB2 (pdf or whatever it's
> called now) and Teradata. If you want to compare Oracle - you need to be
> comparing to Informix 9.
>
> Yeah sure RAC - give me a break, it's all controlled through a single
> manager. Bottleneck city.
>
> Perhaps obviously you have (had) a need to justify going to Oracle over XPS.
> Perhaps you know Oracle better and are more comfortable with it - fine -
> that's a good enough reason. DBA skills are more generally expensive than
> the database itself. Just don't try to justify it by trying to compare an
> apple with a mango.
>
> As far as features as concerned, you've mentioned quite a few that XPS
> doesn't have, or that you haven't uncovered yet. You might also want to
> look at the XPS 'UPDATE JOIN' and 'DELETE JOIN' statements - talk about
> supporting a warehouse - they rock. There are others, but I digress.
>
> Tell you what. Send me a copy of your schema, some data and some code, and
> I'll put my XPS instance (P4 2.4Ghz, 2xIDE box) up against whatever you
> have. Maybe IBM won't benchmark, but I will.
>
> cheers
> j.
>
>
>
>[/ref]
--
Atte,
Jesús Antonio Santos Giraldo
com
net
sending to informix-list
Jean Guest
-
Paul #3 Re: Informix limitations, should we be using Oracle?
What wrong with
select *
from table
where colname is null;
seems to work for me
Jean Sagi wrote:
> >
> >
> > We all know about benchmarks - regardless - TPC-C is not the benchmark you
> > would want to use to measure data warehouse performance, you would really
> > want TPC-H, (as you mention below) - but even that is flawed in that it
> > insists upon ongoing transactions (which are not normal warehouse processes)
> > during the benchmark.
> >
> >
> >
> >
> > Alas these results are long gone, IBM is also not in the business of
> > benchmarking XPS, however; having worked with both databases, I can assure
> > you that XPS will scale infinitely while Oracle will not.
> >
> >
> >
> >
> > The intent of memory allocation is to pre-allocate resources to intensive
> > queries. This is by no means a requirement for XPS, nor is it a bad thing.
> > With judicious use this memory can be put to very advantageous use in index
> > building, hash joins, groups and sorts - I gather Oracle has a similar
> > capability, I have not seen it clearly put to use yet.
> >
> >
> >
> >
> > Well, not quite. You can allocate as much memory AS YOU ARE ALLOWED TO -
> > which is under the control of the DBA. I realize that with Oracle a 'simple
> > count' requires a full table (or index) scan, with Informix it is a single
> > read against the table header and virtually instantaneous. You can also use
> > a light scan for a filtered read (count(*) ... where condition) - this does
> > not chew up memory. Oracle has no equivalent to the light scan - which is
> > on average 4x faster than a traditional read.
> >
> > Yes, you can give a query enough memory so that other queries are gated and
> > will not interfere with your process while it runs. This is preferable to
> > the thrashing which would occur if this were not an option.
> >
> >
> >
> >
> > Is it not a wonderful thing when you can fully utilize the power of the
> > database and the machine with one query? At the same time you can prevent
> > this from occuring.
> >
> >
> >
> >
> > Not quite, only those which require DSS resources, so your 'simple count'
> > would go straight through.
> >
> >
> >
> >
> > Exceeding temp space is a DBA matter - much akin to exceeding the size of a
> > rollback segment under Oracle. Either you are properly sized or you are
> > not. XPS, and all Informix engines, will use what memory is available to
> > the process and swap to disk what is not - this is the same thing that
> > Oracle will do. If you don't have enough disk - well you're SOL.
> >
> >
> >
> >
> > Actually IDS and XPS have the same memory allocation features (PDQPRIORITY),
> > although under IDS it's called Memory Grant Manager, but they're the same
> > thing. I have never seen XPS 'lock on memory'.
> >
> >
> >
> >
> > What have you been smoking? Where do you get this 'it is evident'? XPS
> > performs in parallel with everything, across all horizontal and vertical
> > portions of an operation. It exhibits the highest degree of parallelism
> > that has ever been offered to the public.
> >
> >
> >
> >
> > This is an issue which was corrected in 8.32, which was released 2.5 years
> > ago. At your writing XPS was up to version 8.4
> >
> >
> >
> >
> > And this is a good thing. Alas with Oracle (and db2) all user sessions are
> > operating systems processes, this means that context switching is removed
> > from the control of the database and handed to the operating system which
> > really has no clue as to what's going on inside the engine. Believe me, you
> > want the threads within the engine and not subject to the OS overhead. You
> > can in fact determine which sessions are tied to which clients with a simple
> > onstat command.
> >
> >
> >
> >
> > And sometimes this will even remove the locks held by these sessions when
> > they are thusly killed.
> >
> >
> >
> >
> > Not quite. At the time you wrote this, a counter was incremented to
> > indicate that the lock threshold had been exceeded. However in current
> > releases Informix has dynamic locks, more locks are allocated as needed.
> >
> >
> >
> >
> > There indeed were issues with XPS 8.31 where restarting the instance could
> > cause issues. Normally, those with support contracts would call the 'Down
> > Systems Group' which will answer the phone in under a minute and provide
> > sunset support (i.e. 24x7) until your engine is back on line. Those without
> > support would call the same group and be gently scolded - and then supported
> > in the same fashion. The last time I called Oracle support, I got a call
> > back three days later telling me to upgrade.
> >
> >
> >
> >
> > Which can be a lot of fun. But this is a moot point. It's a question of
> > sizing - if you size your rollback segments improperly - or choose the wrong
> > one with which to perform your transaction - you are hosed. The lesson is
> > that if you size things properly they work - whether they are Oracle or
> > Informix.
> >
> >
> >
> >
> > There is indeed no 'Informix' tool to compare to the Oracle Enterprise
> > Manager (did I get that right?). However there is a single standard command
> > (onstat) to which all monitoring activity has been tied - not to mention the
> > SQL interface to this same data. This can be much more effective than
> > waiting around for a GUI to load and display data which you then have to
> > drill into. I have to admit as an Oracle neophyte that I find the Oracle
> > GUI useful for handling tasks without any understanding of what lies beneath
> > them.
> >
> >
> >
> >
> > I'm not sure to which problem you refer. Raw partitions are very simple to
> > manage.
> >
> >
> >
> >
> > 'Standard' is either 'cooked files' (OS files) or raw devices. You get a
> > 15% performance improvement with raw devices. Oracle has the same feature.
> > There is no similar benefit to raw devices under Oracle - I don't remember
> > why at present. With a raw device Informix gets to control I/O to the
> > device itself instead of going through the Unix file system - which tends to
> > not only slow things down, but opens a window of vulnerability - you write
> > to disk and commit, the database writes it to disk and thinks it made it,
> > but the OS didn't get that far - so your 'committed transaction' wasn't.
> > Management of raw spaces is incredibly simple - especially under XPS where
> > you can use wildcards to manage hundreds of such spaces at once.
> >
> >
> >
> >
> > True.
> >
> >
> >
> >
> > Oh right - I forgot about the resize thing - I've already mentioned the Unix
> > filesystem issues. How can you resize a disk? If you buy an 80 GB disk -
> > can you make it larger? So I guess you're right, under Informix you can't
> > resize a raw disk, however you can add more of them and include them in the
> > same dbspace (tablespace) so that the new space is usable by tables which
> > need to grow in that space. So in fact you can 'resize' a dbspace.
> >
> >
> >
> >
> > That is a good thing. We are delighted that Oracle has come to the table
> > with a fast (direct path) loader. We hope someday that it shows the same
> > sort of performance as is capable with the Informix parallel loader. We
> > further hope that some day it will allow you to unload data in the same
> > fashion.
> >
> >
> >
> >
> > Actually HPL and the light append has been around since 7.x, I first used it
> > around 1996.
> >
> >
> >
> >
> > Actually this is true with XPS. You do get a header to each row indicating
> > the error, you have to trim the header to retrieve your original data.
> > Interesting - this would be a good feature request if not already done.
> > With 7.x and 9.x engines the error is written to a separate file than the
> > error message or you have the option of allowing it to load and then picking
> > it out of a violation table later. For more on this topic visit
> > www.artentech.com/downloads.htm and look for the Informix Load FAQ.
> >
> >
> >
> >
> > If you have an error, then there is a problem. In a DSS world, you don't
> > want to go chasing singleton rows, you want to do the whole thing again.
> >
> >
> >
> >
> > This is ok when you are dealing with a small number of rows and can afford
> > the luxury of debugging each one. When you are dealing with billions of
> > rows and you have a problem, you really don't want to get into the details
> > of any individual row. I guess it's a question of how big you intend to
> > get.
> >
> >
> >
> >
> > Not sure what you're saying here. If you have 18 files to load, under XPS
> > you can define them all as a single external table. You can define this
> > single external table across filename, filesystems, coservers(each instance
> > in a cluster) and servers, you can define it with a single file statement
> > even (not 18) - it's pretty powerful. Furthermore, this file (or external
> > table) is visible to the database - you don't have to go through sqlldr to
> > get it into the database, you can treat it as a table and select columns
> > from it directly, or join it with other tables - whatever. It's really
> > cool. Even cooler - you can unload to it directly with a 'select <columns>
> > from <> insert into <>' statement.
> >
> >
> >
> >
> > See above.
> >
> >
> >
> > Ok.
> >
> >
> >
> > You use indices? Are you in a data warehouse environment or an OLTP
> > environment? XPS can perform the hash join so quickly that indices are a
> > detriment, not a requirement. What you might consider a fast join between
> > two tables with indices can be performed 100 times faster with a hash join -
> > but then you need some memory to play these sorts of games. Have a look at
> > http://www7b.boulder.ibm.com/dmdd/zones/informix/library/techarticle/parker/
> > 0502parker.html for some examples.
> >
> >
> >
> > True, although you can go to text and have at least 2GB last time I
> > checked.
> >
> >
> >
> > What? Sure you can.
> >
> >
> >
> > Create one. It's the same thing. I don't generally bother, I 'select
> > 'whatever' from systables where tabid=1'; It's the same thing.
> >
> >
> >
> > True, that's a neat Oracle feature.
> >
> >
> >
> >
> >
> > Haven't tried this. Never needed it.
> >
> >
> >
> > Sure there is. Or do you mean that you want all rows from table 'a' and 'b'
> > even where neither intersect. Who's been dreaming up your data models? You
> > need to clean up your data if this is a requirement. But then I guess
> > Informix doesn't do that particular join.
> >
> >
> >
> > Not anymore, you can ask for one if you need it.
> >
> >
> >
> > Totally untrue. For that matter, has Oracle come up with a true flatfile
> > export yet?
> >
> >
> >
> > Well, you can use the ANSI standard if you like, you can also allocate a
> > minimum to each varchar which is incredibly useful when it comes time to
> > update. Unlike Oracle, which compresses data as much as possible to
> > preserve space - and then when you need to update it - oooops won't fit into
> > it's original space - have to move it to a new page - which can be time
> > consuming - the reservation of space allows the intelligent DBA to
> > counteract this sort of thing.
> >
> >
> >
> > Bullshit. You need to spend more time with the manual.
> >
> >
> >
> >
> > Stored procedures, when compiled are checked against what is available -
> > compilation will fail if a required object is not present. What you should
> > properly say is that Oracle's PL/SQL is far superior to the Informix stored
> > procedure language. Within Oracle you can develop entire applications with
> > PL/SQL, the Informix approach to Stored procedures is more basic - to
> > perform this or that function - program logic does not truly belong in an
> > Informix SPL - more properly in one of it's more standard languages 4GL or
> > esql/c. Actually I'm not that crazy about the way Oracle will mark a
> > procedure as invalid when something that it depends on changes. This can be
> > a real pain to manage. I know what I need to rebuild - I don't need the
> > database thinking it knows better than me.
> >
> >
> >
> >
> > or 8K. This isn't really an issue, unless you have some reason to need a
> > 48K page? You have data rows which are bigger than 8k? Why? Have you
> > considered a relational model? Informix will still support big rows, but
> > they will span pages.
> >
> >
> >
> > If you can't fit it into 18 - why is 30 better? Why don't you bring up 9.4
> > and it's support for object names of 128? IDS is the engine you should be
> > comparing to, not XPS. Oracle doesn't have anything to compare to XPS.
> >
> >
> >
> > (this was the index size - not sure why you want this)
> >
> >
> >
> > (2GB limit on text)
> >
> >
> >
> > How about 'order by 1,2,3'? You guys sure do like to type, especially those
> > 30 character names.
> > You're 'comparable restrictions' refer perhaps to the ANSI standard which is
> > a subset of all RDBMS vendors SQL.
> >
> >
> >
> > But not a true flatfile.
> >
> >
> >
> > Moot.
> >
> >
> >
> > The same basic stuff is available. Since this is not ANSI standard the name
> > may vary slightly.
> >
> >
> >
> > ??? Informix stored procedures are also compiled into code within the
> > engine. Not sure where you are getting this stuff.
> >
> >
> >
> >
> > No, actually 8 times more reads. When I want one row, I don't want to pull
> > 64K, or 8 times the standard disk buffer - hence 8 disk reads instead of 1.
> >
> >
> >
> >
> > Untrue again. Hybrid fragmentation has been around for some time -
> > including back in 8.31.
> >
> >
> >
> >
> > You need to be pulling out materialized views here - which is the proper
> > comparison to the GK index.
> >
> >
> >
> >
> > Sorry? This is an Oracle issue - you need two layers of security, Informix
> > uses the OS security.
> >
> >
> >
> >
> > Who?
> >
> >
> >
> > Moot.
> >
> >
> >
> >
> > Yes, but then you have to refresh that summary table. I have no real
> > quarrel here, but you're comparing quasi-identical features and claiming
> > it's bad there and good here.
> >
> >
> >
> >
> > Ah, for clients perhaps. Still Informix is relying on their ability to
> > connect to the server, not on an additional layer of security within the
> > database.
> >
> >
> >
> >
> > I guess if Sagent is a requirement you should evaluate whether they are Open
> > enough for your current and future requirements.
> >
> > -----
> >
> > But then you are comparing volkswagens and ferraris. XPS is a true
> > clustered shared nothing environment which can scale literally infinitely -
> > ok I am getting away from myself - it can only have 32K coservers and each
> > can only support 32 Pedabytes of data - so if you need to go beyond that
> > you'd have to pick up the phone and ask the engineers to bump the counter -
> > the thing is that with XPS this is not a theoretical limit, performance does
> > not drop off as you add more instances (coservers) to the cluster - it
> > continues to scale linearly. With Oracle this will only happen on a slide
> > projector. There are only two other databases currently available which
> > have the architecture to scale in this fashion - DB2 (pdf or whatever it's
> > called now) and Teradata. If you want to compare Oracle - you need to be
> > comparing to Informix 9.
> >
> > Yeah sure RAC - give me a break, it's all controlled through a single
> > manager. Bottleneck city.
> >
> > Perhaps obviously you have (had) a need to justify going to Oracle over XPS.
> > Perhaps you know Oracle better and are more comfortable with it - fine -
> > that's a good enough reason. DBA skills are more generally expensive than
> > the database itself. Just don't try to justify it by trying to compare an
> > apple with a mango.
> >
> > As far as features as concerned, you've mentioned quite a few that XPS
> > doesn't have, or that you haven't uncovered yet. You might also want to
> > look at the XPS 'UPDATE JOIN' and 'DELETE JOIN' statements - talk about
> > supporting a warehouse - they rock. There are others, but I digress.
> >
> > Tell you what. Send me a copy of your schema, some data and some code, and
> > I'll put my XPS instance (P4 2.4Ghz, 2xIDE box) up against whatever you
> > have. Maybe IBM won't benchmark, but I will.
> >
> > cheers
> > j.
> >
> >
> >
> >[/ref]
>
> --
>
> Atte,
>
> Jesús Antonio Santos Giraldo
> com
> net
>
> sending to informix-list[/ref]
--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #
Paul Guest
-
Brian #4 Re: Informix limitations, should we be using Oracle?
you can select null, you just need to give your column a datatype.
i.e. select null::integer from sometable;
-Brian
select case when 1=2 then 1 else null end::integer null_value
from table(set{1})
Jean Sagi wrote in message ... [/ref]
(embedded). [/ref]
me
>>
>>
>> We all know about benchmarks - regardless - TPC-C is not the benchmark[/ref][/ref]
you [/ref]
processes)
>>
>>
>> Alas these results are long gone, IBM is also not in the business of
>> benchmarking XPS, however; having worked with both databases, I can[/ref][/ref]
assure
>>
>>
>> The intent of memory allocation is to pre-allocate resources to intensive
>> queries. This is by no means a requirement for XPS, nor is it a bad[/ref][/ref]
thing. [/ref]
index
>>
>>
>> Well, not quite. You can allocate as much memory AS YOU ARE ALLOWED TO -
>> which is under the control of the DBA. I realize that with Oracle a[/ref][/ref]
'simple [/ref]
single [/ref]
use [/ref]
does [/ref]
is [/ref]
and [/ref]
to
>>
>>
>> Is it not a wonderful thing when you can fully utilize the power of the
>> database and the machine with one query? At the same time you can[/ref][/ref]
prevent
>>
>>
>> Not quite, only those which require DSS resources, so your 'simple count'
>> would go straight through.
>>
>>
>>
>>
>> Exceeding temp space is a DBA matter - much akin to exceeding the size of[/ref][/ref]
a
>>
>>
>> Actually IDS and XPS have the same memory allocation features[/ref][/ref]
(PDQPRIORITY),
>>
>>
>> What have you been smoking? Where do you get this 'it is evident'? XPS
>> performs in parallel with everything, across all horizontal and vertical
>> portions of an operation. It exhibits the highest degree of parallelism
>> that has ever been offered to the public.
>>
>>
>>
>>
>> This is an issue which was corrected in 8.32, which was released 2.5[/ref][/ref]
years
>>
>>
>> And this is a good thing. Alas with Oracle (and db2) all user sessions[/ref][/ref]
are [/ref]
you [/ref]
You [/ref]
simple
>>
>>
>> And sometimes this will even remove the locks held by these sessions when
>> they are thusly killed.
>>
>>
>>
>>
>> Not quite. At the time you wrote this, a counter was incremented to
>> indicate that the lock threshold had been exceeded. However in current
>> releases Informix has dynamic locks, more locks are allocated as needed.
>>
>>
>>
>>
>> There indeed were issues with XPS 8.31 where restarting the instance[/ref][/ref]
could [/ref]
'Down [/ref]
without [/ref]
supported
>>
>>
>> Which can be a lot of fun. But this is a moot point. It's a question of
>> sizing - if you size your rollback segments improperly - or choose the[/ref][/ref]
wrong [/ref]
is
>>
>>
>> There is indeed no 'Informix' tool to compare to the Oracle Enterprise
>> Manager (did I get that right?). However there is a single standard[/ref][/ref]
command [/ref]
the [/ref]
beneath
>>
>>
>> I'm not sure to which problem you refer. Raw partitions are very simple[/ref][/ref]
to
>>
>>
>> 'Standard' is either 'cooked files' (OS files) or raw devices. You get a
>> 15% performance improvement with raw devices. Oracle has the same[/ref][/ref]
feature. [/ref]
remember [/ref]
to [/ref]
write [/ref]
where
>>
>>
>> True.
>>
>>
>>
>>
>> Oh right - I forgot about the resize thing - I've already mentioned the[/ref][/ref]
Unix [/ref]
disk - [/ref]
can't [/ref]
the
>>
>>
>> That is a good thing. We are delighted that Oracle has come to the table
>> with a fast (direct path) loader. We hope someday that it shows the same
>> sort of performance as is capable with the Informix parallel loader. We
>> further hope that some day it will allow you to unload data in the same
>> fashion.
>>
>>
>>
>>
>> Actually HPL and the light append has been around since 7.x, I first used[/ref][/ref]
it
>>
>>
>> Actually this is true with XPS. You do get a header to each row[/ref][/ref]
indicating [/ref]
picking
>>
>>
>> If you have an error, then there is a problem. In a DSS world, you don't
>> want to go chasing singleton rows, you want to do the whole thing again.
>>
>>
>>
>>
>> This is ok when you are dealing with a small number of rows and can[/ref][/ref]
afford [/ref]
details
>>
>>
>> Not sure what you're saying here. If you have 18 files to load, under[/ref][/ref]
XPS [/ref]
instance [/ref]
external [/ref]
to [/ref]
<columns>
>>
>>
>> See above.
>>
>>
>>
>> Ok.
>>
>>
>>
>> You use indices? Are you in a data warehouse environment or an OLTP
>> environment? XPS can perform the hash join so quickly that indices are a
>> detriment, not a requirement. What you might consider a fast join[/ref][/ref]
between [/ref]
join - [/ref]
at [/ref]
http://www7b.boulder.ibm.com/dmdd/zones/informix/library/techarticle/parker/
>>
>> True, although you can go to text and have at least 2GB last time I
>> checked.
>>
>>
>>
>> What? Sure you can.
>>
>>
>>
>> Create one. It's the same thing. I don't generally bother, I 'select
>> 'whatever' from systables where tabid=1'; It's the same thing.
>>
>>
>>
>> True, that's a neat Oracle feature.
>>
>>
>>
>>
>>
>> Haven't tried this. Never needed it.
>>
>>
>>
>> Sure there is. Or do you mean that you want all rows from table 'a' and[/ref][/ref]
'b' [/ref]
You
>>
>> Not anymore, you can ask for one if you need it.
>>
>>
>>
>> Totally untrue. For that matter, has Oracle come up with a true flatfile
>> export yet?
>>
>>
>>
>> Well, you can use the ANSI standard if you like, you can also allocate a
>> minimum to each varchar which is incredibly useful when it comes time to
>> update. Unlike Oracle, which compresses data as much as possible to
>> preserve space - and then when you need to update it - oooops won't fit[/ref][/ref]
into
>>
>> Bullshit. You need to spend more time with the manual.
>>
>>
>>
>>
>> Stored procedures, when compiled are checked against what is available -
>> compilation will fail if a required object is not present. What you[/ref][/ref]
should [/ref]
stored [/ref]
with [/ref]
or [/ref]
be
>>
>>
>> or 8K. This isn't really an issue, unless you have some reason to need a
>> 48K page? You have data rows which are bigger than 8k? Why? Have you
>> considered a relational model? Informix will still support big rows, but
>> they will span pages.
>>
>>
>>
>> If you can't fit it into 18 - why is 30 better? Why don't you bring up[/ref][/ref]
9.4 [/ref]
be
>>
>> (this was the index size - not sure why you want this)
>>
>>
>>
>> (2GB limit on text)
>>
>>
>>
>> How about 'order by 1,2,3'? You guys sure do like to type, especially[/ref][/ref]
those [/ref]
is
>>
>> But not a true flatfile.
>>
>>
>>
>> Moot.
>>
>>
>>
>> The same basic stuff is available. Since this is not ANSI standard the[/ref][/ref]
name
>>
>> ??? Informix stored procedures are also compiled into code within the
>> engine. Not sure where you are getting this stuff.
>>
>>
>>
>>
>> No, actually 8 times more reads. When I want one row, I don't want to[/ref][/ref]
pull [/ref]
1.
>>
>>
>> Untrue again. Hybrid fragmentation has been around for some time -
>> including back in 8.31.
>>
>>
>>
>>
>> You need to be pulling out materialized views here - which is the proper
>> comparison to the GK index.
>>
>>
>>
>>
>> Sorry? This is an Oracle issue - you need two layers of security,[/ref][/ref]
Informix
>>
>>
>> Who?
>>
>>
>>
>> Moot.
>>
>>
>>
>>
>> Yes, but then you have to refresh that summary table. I have no real
>> quarrel here, but you're comparing quasi-identical features and claiming
>> it's bad there and good here.
>>
>>
>>
>>
>> Ah, for clients perhaps. Still Informix is relying on their ability to
>> connect to the server, not on an additional layer of security within the
>> database.
>>
>>
>>
>>
>> I guess if Sagent is a requirement you should evaluate whether they are[/ref][/ref]
Open [/ref]
infinitely - [/ref]
each [/ref]
counter - [/ref]
does [/ref]
slide [/ref]
it's [/ref]
XPS. [/ref]
than [/ref]
an [/ref]
and
>
>--
>
>
>Atte,
>
>
>Jesús Antonio Santos Giraldo
>com
>net
>
>sending to informix-list[/ref]
Brian Guest
-
Obnoxio #5 Re: Informix limitations, should we be using Oracle?
Jean Sagi wrote:
Log a case and submit a feature request.
--
Ciao,
The Obnoxious One
"Ogni uomo mi guarda come se fossi una testa di cazzo"
Obnoxio Guest
-
Jean #6 Re: Re: Informix limitations, should we be using Oracle?
For me too.. ;)
but:
select null
from systables;
gives:
201: A syntax error has occurred.
I have :
Informix Dynamic Server Version 9.30.HC2W8
HP-UX B.11.00 U 9000/856
I could bet the same hapen in 9.4
BTW:
select getnull()
from systables;
Works... ugly but it works... (it's a stored procedure)... and I can live with it.
Chucho!
-----Original Message-----
From: Paul Watson <com>
To: org
Date: Mon, 17 Nov 2003 23:36:32 +0000
Subject: Re: Informix limitations, should we be using Oracle?
What wrong with
select *
from table
where colname is null;
seems to work for me
Jean Sagi wrote:
> >
> >
> > We all know about benchmarks - regardless - TPC-C is not the benchmark you
> > would want to use to measure data warehouse performance, you would really
> > want TPC-H, (as you mention below) - but even that is flawed in that it
> > insists upon ongoing transactions (which are not normal warehouse processes)
> > during the benchmark.
> >
> >
> >
> >
> > Alas these results are long gone, IBM is also not in the business of
> > benchmarking XPS, however; having worked with both databases, I can assure
> > you that XPS will scale infinitely while Oracle will not.
> >
> >
> >
> >
> > The intent of memory allocation is to pre-allocate resources to intensive
> > queries. This is by no means a requirement for XPS, nor is it a bad thing.
> > With judicious use this memory can be put to very advantageous use in index
> > building, hash joins, groups and sorts - I gather Oracle has a similar
> > capability, I have not seen it clearly put to use yet.
> >
> >
> >
> >
> > Well, not quite. You can allocate as much memory AS YOU ARE ALLOWED TO -
> > which is under the control of the DBA. I realize that with Oracle a 'simple
> > count' requires a full table (or index) scan, with Informix it is a single
> > read against the table header and virtually instantaneous. You can also use
> > a light scan for a filtered read (count(*) ... where condition) - this does
> > not chew up memory. Oracle has no equivalent to the light scan - which is
> > on average 4x faster than a traditional read.
> >
> > Yes, you can give a query enough memory so that other queries are gated and
> > will not interfere with your process while it runs. This is preferable to
> > the thrashing which would occur if this were not an option.
> >
> >
> >
> >
> > Is it not a wonderful thing when you can fully utilize the power of the
> > database and the machine with one query? At the same time you can prevent
> > this from occuring.
> >
> >
> >
> >
> > Not quite, only those which require DSS resources, so your 'simple count'
> > would go straight through.
> >
> >
> >
> >
> > Exceeding temp space is a DBA matter - much akin to exceeding the size of a
> > rollback segment under Oracle. Either you are properly sized or you are
> > not. XPS, and all Informix engines, will use what memory is available to
> > the process and swap to disk what is not - this is the same thing that
> > Oracle will do. If you don't have enough disk - well you're SOL.
> >
> >
> >
> >
> > Actually IDS and XPS have the same memory allocation features (PDQPRIORITY),
> > although under IDS it's called Memory Grant Manager, but they're the same
> > thing. I have never seen XPS 'lock on memory'.
> >
> >
> >
> >
> > What have you been smoking? Where do you get this 'it is evident'? XPS
> > performs in parallel with everything, across all horizontal and vertical
> > portions of an operation. It exhibits the highest degree of parallelism
> > that has ever been offered to the public.
> >
> >
> >
> >
> > This is an issue which was corrected in 8.32, which was released 2.5 years
> > ago. At your writing XPS was up to version 8.4
> >
> >
> >
> >
> > And this is a good thing. Alas with Oracle (and db2) all user sessions are
> > operating systems processes, this means that context switching is removed
> > from the control of the database and handed to the operating system which
> > really has no clue as to what's going on inside the engine. Believe me, you
> > want the threads within the engine and not subject to the OS overhead. You
> > can in fact determine which sessions are tied to which clients with a simple
> > onstat command.
> >
> >
> >
> >
> > And sometimes this will even remove the locks held by these sessions when
> > they are thusly killed.
> >
> >
> >
> >
> > Not quite. At the time you wrote this, a counter was incremented to
> > indicate that the lock threshold had been exceeded. However in current
> > releases Informix has dynamic locks, more locks are allocated as needed.
> >
> >
> >
> >
> > There indeed were issues with XPS 8.31 where restarting the instance could
> > cause issues. Normally, those with support contracts would call the 'Down
> > Systems Group' which will answer the phone in under a minute and provide
> > sunset support (i.e. 24x7) until your engine is back on line. Those without
> > support would call the same group and be gently scolded - and then supported
> > in the same fashion. The last time I called Oracle support, I got a call
> > back three days later telling me to upgrade.
> >
> >
> >
> >
> > Which can be a lot of fun. But this is a moot point. It's a question of
> > sizing - if you size your rollback segments improperly - or choose the wrong
> > one with which to perform your transaction - you are hosed. The lesson is
> > that if you size things properly they work - whether they are Oracle or
> > Informix.
> >
> >
> >
> >
> > There is indeed no 'Informix' tool to compare to the Oracle Enterprise
> > Manager (did I get that right?). However there is a single standard command
> > (onstat) to which all monitoring activity has been tied - not to mention the
> > SQL interface to this same data. This can be much more effective than
> > waiting around for a GUI to load and display data which you then have to
> > drill into. I have to admit as an Oracle neophyte that I find the Oracle
> > GUI useful for handling tasks without any understanding of what lies beneath
> > them.
> >
> >
> >
> >
> > I'm not sure to which problem you refer. Raw partitions are very simple to
> > manage.
> >
> >
> >
> >
> > 'Standard' is either 'cooked files' (OS files) or raw devices. You get a
> > 15% performance improvement with raw devices. Oracle has the same feature.
> > There is no similar benefit to raw devices under Oracle - I don't remember
> > why at present. With a raw device Informix gets to control I/O to the
> > device itself instead of going through the Unix file system - which tends to
> > not only slow things down, but opens a window of vulnerability - you write
> > to disk and commit, the database writes it to disk and thinks it made it,
> > but the OS didn't get that far - so your 'committed transaction' wasn't.
> > Management of raw spaces is incredibly simple - especially under XPS where
> > you can use wildcards to manage hundreds of such spaces at once.
> >
> >
> >
> >
> > True.
> >
> >
> >
> >
> > Oh right - I forgot about the resize thing - I've already mentioned the Unix
> > filesystem issues. How can you resize a disk? If you buy an 80 GB disk -
> > can you make it larger? So I guess you're right, under Informix you can't
> > resize a raw disk, however you can add more of them and include them in the
> > same dbspace (tablespace) so that the new space is usable by tables which
> > need to grow in that space. So in fact you can 'resize' a dbspace.
> >
> >
> >
> >
> > That is a good thing. We are delighted that Oracle has come to the table
> > with a fast (direct path) loader. We hope someday that it shows the same
> > sort of performance as is capable with the Informix parallel loader. We
> > further hope that some day it will allow you to unload data in the same
> > fashion.
> >
> >
> >
> >
> > Actually HPL and the light append has been around since 7.x, I first used it
> > around 1996.
> >
> >
> >
> >
> > Actually this is true with XPS. You do get a header to each row indicating
> > the error, you have to trim the header to retrieve your original data.
> > Interesting - this would be a good feature request if not already done.
> > With 7.x and 9.x engines the error is written to a separate file than the
> > error message or you have the option of allowing it to load and then picking
> > it out of a violation table later. For more on this topic visit
> > www.artentech.com/downloads.htm and look for the Informix Load FAQ.
> >
> >
> >
> >
> > If you have an error, then there is a problem. In a DSS world, you don't
> > want to go chasing singleton rows, you want to do the whole thing again.
> >
> >
> >
> >
> > This is ok when you are dealing with a small number of rows and can afford
> > the luxury of debugging each one. When you are dealing with billions of
> > rows and you have a problem, you really don't want to get into the details
> > of any individual row. I guess it's a question of how big you intend to
> > get.
> >
> >
> >
> >
> > Not sure what you're saying here. If you have 18 files to load, under XPS
> > you can define them all as a single external table. You can define this
> > single external table across filename, filesystems, coservers(each instance
> > in a cluster) and servers, you can define it with a single file statement
> > even (not 18) - it's pretty powerful. Furthermore, this file (or external
> > table) is visible to the database - you don't have to go through sqlldr to
> > get it into the database, you can treat it as a table and select columns
> > from it directly, or join it with other tables - whatever. It's really
> > cool. Even cooler - you can unload to it directly with a 'select <columns>
> > from <> insert into <>' statement.
> >
> >
> >
> >
> > See above.
> >
> >
> >
> > Ok.
> >
> >
> >
> > You use indices? Are you in a data warehouse environment or an OLTP
> > environment? XPS can perform the hash join so quickly that indices are a
> > detriment, not a requirement. What you might consider a fast join between
> > two tables with indices can be performed 100 times faster with a hash join -
> > but then you need some memory to play these sorts of games. Have a look at
> > http://www7b.boulder.ibm.com/dmdd/zones/informix/library/techarticle/parker/
> > 0502parker.html for some examples.
> >
> >
> >
> > True, although you can go to text and have at least 2GB last time I
> > checked.
> >
> >
> >
> > What? Sure you can.
> >
> >
> >
> > Create one. It's the same thing. I don't generally bother, I 'select
> > 'whatever' from systables where tabid=1'; It's the same thing.
> >
> >
> >
> > True, that's a neat Oracle feature.
> >
> >
> >
> >
> >
> > Haven't tried this. Never needed it.
> >
> >
> >
> > Sure there is. Or do you mean that you want all rows from table 'a' and 'b'
> > even where neither intersect. Who's been dreaming up your data models? You
> > need to clean up your data if this is a requirement. But then I guess
> > Informix doesn't do that particular join.
> >
> >
> >
> > Not anymore, you can ask for one if you need it.
> >
> >
> >
> > Totally untrue. For that matter, has Oracle come up with a true flatfile
> > export yet?
> >
> >
> >
> > Well, you can use the ANSI standard if you like, you can also allocate a
> > minimum to each varchar which is incredibly useful when it comes time to
> > update. Unlike Oracle, which compresses data as much as possible to
> > preserve space - and then when you need to update it - oooops won't fit into
> > it's original space - have to move it to a new page - which can be time
> > consuming - the reservation of space allows the intelligent DBA to
> > counteract this sort of thing.
> >
> >
> >
> > Bullshit. You need to spend more time with the manual.
> >
> >
> >
> >
> > Stored procedures, when compiled are checked against what is available -
> > compilation will fail if a required object is not present. What you should
> > properly say is that Oracle's PL/SQL is far superior to the Informix stored
> > procedure language. Within Oracle you can develop entire applications with
> > PL/SQL, the Informix approach to Stored procedures is more basic - to
> > perform this or that function - program logic does not truly belong in an
> > Informix SPL - more properly in one of it's more standard languages 4GL or
> > esql/c. Actually I'm not that crazy about the way Oracle will mark a
> > procedure as invalid when something that it depends on changes. This can be
> > a real pain to manage. I know what I need to rebuild - I don't need the
> > database thinking it knows better than me.
> >
> >
> >
> >
> > or 8K. This isn't really an issue, unless you have some reason to need a
> > 48K page? You have data rows which are bigger than 8k? Why? Have you
> > considered a relational model? Informix will still support big rows, but
> > they will span pages.
> >
> >
> >
> > If you can't fit it into 18 - why is 30 better? Why don't you bring up 9.4
> > and it's support for object names of 128? IDS is the engine you should be
> > comparing to, not XPS. Oracle doesn't have anything to compare to XPS.
> >
> >
> >
> > (this was the index size - not sure why you want this)
> >
> >
> >
> > (2GB limit on text)
> >
> >
> >
> > How about 'order by 1,2,3'? You guys sure do like to type, especially those
> > 30 character names.
> > You're 'comparable restrictions' refer perhaps to the ANSI standard which is
> > a subset of all RDBMS vendors SQL.
> >
> >
> >
> > But not a true flatfile.
> >
> >
> >
> > Moot.
> >
> >
> >
> > The same basic stuff is available. Since this is not ANSI standard the name
> > may vary slightly.
> >
> >
> >
> > ??? Informix stored procedures are also compiled into code within the
> > engine. Not sure where you are getting this stuff.
> >
> >
> >
> >
> > No, actually 8 times more reads. When I want one row, I don't want to pull
> > 64K, or 8 times the standard disk buffer - hence 8 disk reads instead of 1.
> >
> >
> >
> >
> > Untrue again. Hybrid fragmentation has been around for some time -
> > including back in 8.31.
> >
> >
> >
> >
> > You need to be pulling out materialized views here - which is the proper
> > comparison to the GK index.
> >
> >
> >
> >
> > Sorry? This is an Oracle issue - you need two layers of security, Informix
> > uses the OS security.
> >
> >
> >
> >
> > Who?
> >
> >
> >
> > Moot.
> >
> >
> >
> >
> > Yes, but then you have to refresh that summary table. I have no real
> > quarrel here, but you're comparing quasi-identical features and claiming
> > it's bad there and good here.
> >
> >
> >
> >
> > Ah, for clients perhaps. Still Informix is relying on their ability to
> > connect to the server, not on an additional layer of security within the
> > database.
> >
> >
> >
> >
> > I guess if Sagent is a requirement you should evaluate whether they are Open
> > enough for your current and future requirements.
> >
> > -----
> >
> > But then you are comparing volkswagens and ferraris. XPS is a true
> > clustered shared nothing environment which can scale literally infinitely -
> > ok I am getting away from myself - it can only have 32K coservers and each
> > can only support 32 Pedabytes of data - so if you need to go beyond that
> > you'd have to pick up the phone and ask the engineers to bump the counter -
> > the thing is that with XPS this is not a theoretical limit, performance does
> > not drop off as you add more instances (coservers) to the cluster - it
> > continues to scale linearly. With Oracle this will only happen on a slide
> > projector. There are only two other databases currently available which
> > have the architecture to scale in this fashion - DB2 (pdf or whatever it's
> > called now) and Teradata. If you want to compare Oracle - you need to be
> > comparing to Informix 9.
> >
> > Yeah sure RAC - give me a break, it's all controlled through a single
> > manager. Bottleneck city.
> >
> > Perhaps obviously you have (had) a need to justify going to Oracle over XPS.
> > Perhaps you know Oracle better and are more comfortable with it - fine -
> > that's a good enough reason. DBA skills are more generally expensive than
> > the database itself. Just don't try to justify it by trying to compare an
> > apple with a mango.
> >
> > As far as features as concerned, you've mentioned quite a few that XPS
> > doesn't have, or that you haven't uncovered yet. You might also want to
> > look at the XPS 'UPDATE JOIN' and 'DELETE JOIN' statements - talk about
> > supporting a warehouse - they rock. There are others, but I digress.
> >
> > Tell you what. Send me a copy of your schema, some data and some code, and
> > I'll put my XPS instance (P4 2.4Ghz, 2xIDE box) up against whatever you
> > have. Maybe IBM won't benchmark, but I will.
> >
> > cheers
> > j.
> >
> >
> >
> >[/ref]
>
> --
>
> Atte,
>
> Jesús Antonio Santos Giraldo
> com
> net
>
> sending to informix-list[/ref]
--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #
Jean Sagi
com
net
sending to informix-list
Jean Guest
-
Jean #7 Re: Re: Informix limitations, should we be using Oracle?
Hey, hey, hey!!
It works... !
Now there is something new I know...
Chucho
-----Original Message-----
From: "Brian Foster" <com>
To: org
Date: Mon, 17 Nov 2003 19:16:13 -0500
Subject: Re: Informix limitations, should we be using Oracle?
you can select null, you just need to give your column a datatype.
i.e. select null::integer from sometable;
-Brian
select case when 1=2 then 1 else null end::integer null_value
from table(set{1})
Jean Sagi wrote in message ... [/ref]
(embedded). [/ref]
me
>>
>>
>> We all know about benchmarks - regardless - TPC-C is not the benchmark[/ref][/ref]
you [/ref]
processes)
>>
>>
>> Alas these results are long gone, IBM is also not in the business of
>> benchmarking XPS, however; having worked with both databases, I can[/ref][/ref]
assure
>>
>>
>> The intent of memory allocation is to pre-allocate resources to intensive
>> queries. This is by no means a requirement for XPS, nor is it a bad[/ref][/ref]
thing. [/ref]
index
>>
>>
>> Well, not quite. You can allocate as much memory AS YOU ARE ALLOWED TO -
>> which is under the control of the DBA. I realize that with Oracle a[/ref][/ref]
'simple [/ref]
single [/ref]
use [/ref]
does [/ref]
is [/ref]
and [/ref]
to
>>
>>
>> Is it not a wonderful thing when you can fully utilize the power of the
>> database and the machine with one query? At the same time you can[/ref][/ref]
prevent
>>
>>
>> Not quite, only those which require DSS resources, so your 'simple count'
>> would go straight through.
>>
>>
>>
>>
>> Exceeding temp space is a DBA matter - much akin to exceeding the size of[/ref][/ref]
a
>>
>>
>> Actually IDS and XPS have the same memory allocation features[/ref][/ref]
(PDQPRIORITY),
>>
>>
>> What have you been smoking? Where do you get this 'it is evident'? XPS
>> performs in parallel with everything, across all horizontal and vertical
>> portions of an operation. It exhibits the highest degree of parallelism
>> that has ever been offered to the public.
>>
>>
>>
>>
>> This is an issue which was corrected in 8.32, which was released 2.5[/ref][/ref]
years
>>
>>
>> And this is a good thing. Alas with Oracle (and db2) all user sessions[/ref][/ref]
are [/ref]
you [/ref]
You [/ref]
simple
>>
>>
>> And sometimes this will even remove the locks held by these sessions when
>> they are thusly killed.
>>
>>
>>
>>
>> Not quite. At the time you wrote this, a counter was incremented to
>> indicate that the lock threshold had been exceeded. However in current
>> releases Informix has dynamic locks, more locks are allocated as needed.
>>
>>
>>
>>
>> There indeed were issues with XPS 8.31 where restarting the instance[/ref][/ref]
could [/ref]
'Down [/ref]
without [/ref]
supported
>>
>>
>> Which can be a lot of fun. But this is a moot point. It's a question of
>> sizing - if you size your rollback segments improperly - or choose the[/ref][/ref]
wrong [/ref]
is
>>
>>
>> There is indeed no 'Informix' tool to compare to the Oracle Enterprise
>> Manager (did I get that right?). However there is a single standard[/ref][/ref]
command [/ref]
the [/ref]
beneath
>>
>>
>> I'm not sure to which problem you refer. Raw partitions are very simple[/ref][/ref]
to
>>
>>
>> 'Standard' is either 'cooked files' (OS files) or raw devices. You get a
>> 15% performance improvement with raw devices. Oracle has the same[/ref][/ref]
feature. [/ref]
remember [/ref]
to [/ref]
write [/ref]
where
>
>>
>>
>> True.
>>
>>
>>
>>
>> Oh right - I forgot about the resize thing - I've already mentioned the[/ref][/ref]
Unix [/ref]
disk - [/ref]
can't [/ref]
the
>>
>>
>> That is a good thing. We are delighted that Oracle has come to the table
>> with a fast (direct path) loader. We hope someday that it shows the same
>> sort of performance as is capable with the Informix parallel loader. We
>> further hope that some day it will allow you to unload data in the same
>> fashion.
>>
>>
>>
>>
>> Actually HPL and the light append has been around since 7.x, I first used[/ref][/ref]
it
>>
>>
>> Actually this is true with XPS. You do get a header to each row[/ref][/ref]
indicating [/ref]
picking
>>
>>
>> If you have an error, then there is a problem. In a DSS world, you don't
>> want to go chasing singleton rows, you want to do the whole thing again.
>>
>>
>>
>>
>> This is ok when you are dealing with a small number of rows and can[/ref][/ref]
afford [/ref]
details
>>
>>
>> Not sure what you're saying here. If you have 18 files to load, under[/ref][/ref]
XPS [/ref]
instance [/ref]
external [/ref]
to [/ref]
<columns>
>>
>>
>> See above.
>>
>>
>>
>> Ok.
>>
>>
>>
>> You use indices? Are you in a data warehouse environment or an OLTP
>> environment? XPS can perform the hash join so quickly that indices are a
>> detriment, not a requirement. What you might consider a fast join[/ref][/ref]
between [/ref]
join - [/ref]
at [/ref]
http://www7b.boulder.ibm.com/dmdd/zones/informix/library/techarticle/parker/
>>
>> True, although you can go to text and have at least 2GB last time I
>> checked.
>>
>>
>>
>> What? Sure you can.
>>
>>
>>
>> Create one. It's the same thing. I don't generally bother, I 'select
>> 'whatever' from systables where tabid=1'; It's the same thing.
>>
>>
>>
>> True, that's a neat Oracle feature.
>>
>>
>>
>>
>>
>> Haven't tried this. Never needed it.
>>
>>
>>
>> Sure there is. Or do you mean that you want all rows from table 'a' and[/ref][/ref]
'b' [/ref]
You
>>
>> Not anymore, you can ask for one if you need it.
>>
>>
>>
>> Totally untrue. For that matter, has Oracle come up with a true flatfile
>> export yet?
>>
>>
>>
>> Well, you can use the ANSI standard if you like, you can also allocate a
>> minimum to each varchar which is incredibly useful when it comes time to
>> update. Unlike Oracle, which compresses data as much as possible to
>> preserve space - and then when you need to update it - oooops won't fit[/ref][/ref]
into
>>
>> Bullshit. You need to spend more time with the manual.
>>
>>
>>
>>
>> Stored procedures, when compiled are checked against what is available -
>> compilation will fail if a required object is not present. What you[/ref][/ref]
should [/ref]
stored [/ref]
with [/ref]
or [/ref]
be
>>
>>
>> or 8K. This isn't really an issue, unless you have some reason to need a
>> 48K page? You have data rows which are bigger than 8k? Why? Have you
>> considered a relational model? Informix will still support big rows, but
>> they will span pages.
>>
>>
>>
>> If you can't fit it into 18 - why is 30 better? Why don't you bring up[/ref][/ref]
9.4 [/ref]
be
>>
>> (this was the index size - not sure why you want this)
>>
>>
>>
>> (2GB limit on text)
>>
>>
>>
>> How about 'order by 1,2,3'? You guys sure do like to type, especially[/ref][/ref]
those [/ref]
is
>>
>> But not a true flatfile.
>>
>>
>>
>> Moot.
>>
>>
>>
>> The same basic stuff is available. Since this is not ANSI standard the[/ref][/ref]
name
>>
>> ??? Informix stored procedures are also compiled into code within the
>> engine. Not sure where you are getting this stuff.
>>
>>
>>
>>
>> No, actually 8 times more reads. When I want one row, I don't want to[/ref][/ref]
pull [/ref]
1.
>>
>>
>> Untrue again. Hybrid fragmentation has been around for some time -
>> including back in 8.31.
>>
>>
>>
>>
>> You need to be pulling out materialized views here - which is the proper
>> comparison to the GK index.
>>
>>
>>
>>
>> Sorry? This is an Oracle issue - you need two layers of security,[/ref][/ref]
Informix
>>
>>
>> Who?
>>
>>
>>
>> Moot.
>>
>>
>>
>>
>> Yes, but then you have to refresh that summary table. I have no real
>> quarrel here, but you're comparing quasi-identical features and claiming
>> it's bad there and good here.
>>
>>
>>
>>
>> Ah, for clients perhaps. Still Informix is relying on their ability to
>> connect to the server, not on an additional layer of security within the
>> database.
>>
>>
>>
>>
>> I guess if Sagent is a requirement you should evaluate whether they are[/ref][/ref]
Open [/ref]
infinitely - [/ref]
each [/ref]
counter - [/ref]
does [/ref]
slide [/ref]
it's [/ref]
XPS. [/ref]
than [/ref]
an [/ref]
and
>
>--
>
>
>Atte,
>
>
>Jesús Antonio Santos Giraldo
>com
>net
>
>sending to informix-list[/ref]
Jean Sagi
com
net
sending to informix-list
Jean Guest
-
Jack #8 Re: Informix limitations, should we be using Oracle?
I should have been more temperate.
I'll have to give the NULL a try. By 'full outer join' do you mean that the
join between these two lists
A C
B D
C E
Returns
A B C D E?
Regards,
Jack Parker
-.-- --- ..- / -. . . -.. / - --- / --. . - / .- / .-.. .. ..-. . .-.-.- /
.... --- / -.. --- / .. .-.-.-
----- Original Message -----
From: "Jean Sagi" <com>
To: "Jack Parker" <net>
Cc: <org>
Sent: Monday, November 17, 2003 12:38 PM
Subject: Re: Informix limitations, should we be using Oracle?
[/ref]
(embedded). [/ref]
alert me
> >
> >
> > We all know about benchmarks - regardless - TPC-C is not the benchmark[/ref][/ref]
you [/ref]
really [/ref]
processes)
> >
> >
> > Alas these results are long gone, IBM is also not in the business of
> > benchmarking XPS, however; having worked with both databases, I can[/ref][/ref]
assure
> >
> >
> > The intent of memory allocation is to pre-allocate resources to[/ref][/ref]
intensive [/ref]
thing. [/ref]
index
> >
> >
> > Well, not quite. You can allocate as much memory AS YOU ARE ALLOWED[/ref][/ref]
TO - [/ref]
'simple [/ref]
single [/ref]
use [/ref]
does [/ref]
is [/ref]
and [/ref]
to
> >
> >
> > Is it not a wonderful thing when you can fully utilize the power of the
> > database and the machine with one query? At the same time you can[/ref][/ref]
prevent
> >
> >
> > Not quite, only those which require DSS resources, so your 'simple[/ref][/ref]
count'
> >
> >
> > Exceeding temp space is a DBA matter - much akin to exceeding the size[/ref][/ref]
of a [/ref]
to
> >
> >
> > Actually IDS and XPS have the same memory allocation features[/ref][/ref]
(PDQPRIORITY), [/ref]
same
> >
> >
> > What have you been smoking? Where do you get this 'it is evident'? XPS
> > performs in parallel with everything, across all horizontal and vertical
> > portions of an operation. It exhibits the highest degree of parallelism
> > that has ever been offered to the public.
> >
> >
> >
> >
> > This is an issue which was corrected in 8.32, which was released 2.5[/ref][/ref]
years
> >
> >
> > And this is a good thing. Alas with Oracle (and db2) all user sessions[/ref][/ref]
are [/ref]
removed [/ref]
which [/ref]
you [/ref]
You [/ref]
simple
> >
> >
> > And sometimes this will even remove the locks held by these sessions[/ref][/ref]
when
> >
> >
> > Not quite. At the time you wrote this, a counter was incremented to
> > indicate that the lock threshold had been exceeded. However in current
> > releases Informix has dynamic locks, more locks are allocated as needed.
> >
> >
> >
> >
> > There indeed were issues with XPS 8.31 where restarting the instance[/ref][/ref]
could [/ref]
'Down [/ref]
without [/ref]
supported [/ref]
call
> >
> >
> > Which can be a lot of fun. But this is a moot point. It's a question[/ref][/ref]
of [/ref]
wrong [/ref]
is
> >
> >
> > There is indeed no 'Informix' tool to compare to the Oracle Enterprise
> > Manager (did I get that right?). However there is a single standard[/ref][/ref]
command [/ref]
the [/ref]
Oracle [/ref]
beneath
> >
> >
> > I'm not sure to which problem you refer. Raw partitions are very simple[/ref][/ref]
to
> >
> >
> > 'Standard' is either 'cooked files' (OS files) or raw devices. You get[/ref][/ref]
a [/ref]
feature. [/ref]
remember [/ref]
tends to [/ref]
write [/ref]
it, [/ref]
where
> >
> >
> > True.
> >
> >
> >
> >
> > Oh right - I forgot about the resize thing - I've already mentioned the[/ref][/ref]
Unix [/ref]
disk - [/ref]
can't [/ref]
the [/ref]
which
> >
> >
> > That is a good thing. We are delighted that Oracle has come to the[/ref][/ref]
table [/ref]
same
> >
> >
> > Actually HPL and the light append has been around since 7.x, I first[/ref][/ref]
used it
> >
> >
> > Actually this is true with XPS. You do get a header to each row[/ref][/ref]
indicating [/ref]
the [/ref]
picking
> >
> >
> > If you have an error, then there is a problem. In a DSS world, you[/ref][/ref]
don't
> >
> >
> > This is ok when you are dealing with a small number of rows and can[/ref][/ref]
afford [/ref]
details
> >
> >
> > Not sure what you're saying here. If you have 18 files to load, under[/ref][/ref]
XPS [/ref]
instance [/ref]
statement [/ref]
external [/ref]
to [/ref]
<columns>
> >
> >
> > See above.
> >
> >
> >
> > Ok.
> >
> >
> >
> > You use indices? Are you in a data warehouse environment or an OLTP
> > environment? XPS can perform the hash join so quickly that indices are[/ref][/ref]
a [/ref]
between [/ref]
join - [/ref]
at [/ref]
http://www7b.boulder.ibm.com/dmdd/zones/informix/library/techarticle/parker/
> >
> > True, although you can go to text and have at least 2GB last time I
> > checked.
> >
> >
> >
> > What? Sure you can.
> >
> >
> >
> > Create one. It's the same thing. I don't generally bother, I 'select
> > 'whatever' from systables where tabid=1'; It's the same thing.
> >
> >
> >
> > True, that's a neat Oracle feature.
> >
> >
> >
> >
> >
> > Haven't tried this. Never needed it.
> >
> >
> >
> > Sure there is. Or do you mean that you want all rows from table 'a' and[/ref][/ref]
'b' [/ref]
You
> >
> > Not anymore, you can ask for one if you need it.
> >
> >
> >
> > Totally untrue. For that matter, has Oracle come up with a true[/ref][/ref]
flatfile
> >
> > Well, you can use the ANSI standard if you like, you can also allocate a
> > minimum to each varchar which is incredibly useful when it comes time to
> > update. Unlike Oracle, which compresses data as much as possible to
> > preserve space - and then when you need to update it - oooops won't fit[/ref][/ref]
into
> >
> > Bullshit. You need to spend more time with the manual.
> >
> >
> >
> >
> > Stored procedures, when compiled are checked against what is available -
> > compilation will fail if a required object is not present. What you[/ref][/ref]
should [/ref]
stored [/ref]
with [/ref]
an [/ref]
or [/ref]
can be
> >
> >
> > or 8K. This isn't really an issue, unless you have some reason to need[/ref][/ref]
a [/ref]
but
> >
> > If you can't fit it into 18 - why is 30 better? Why don't you bring up[/ref][/ref]
9.4 [/ref]
be
> >
> > (this was the index size - not sure why you want this)
> >
> >
> >
> > (2GB limit on text)
> >
> >
> >
> > How about 'order by 1,2,3'? You guys sure do like to type, especially[/ref][/ref]
those [/ref]
which is
> >
> > But not a true flatfile.
> >
> >
> >
> > Moot.
> >
> >
> >
> > The same basic stuff is available. Since this is not ANSI standard the[/ref][/ref]
name
> >
> > ??? Informix stored procedures are also compiled into code within the
> > engine. Not sure where you are getting this stuff.
> >
> >
> >
> >
> > No, actually 8 times more reads. When I want one row, I don't want to[/ref][/ref]
pull [/ref]
1.
> >
> >
> > Untrue again. Hybrid fragmentation has been around for some time -
> > including back in 8.31.
> >
> >
> >
> >
> > You need to be pulling out materialized views here - which is the proper
> > comparison to the GK index.
> >
> >
> >
> >
> > Sorry? This is an Oracle issue - you need two layers of security,[/ref][/ref]
Informix
> >
> >
> > Who?
> >
> >
> >
> > Moot.
> >
> >
> >
> >
> > Yes, but then you have to refresh that summary table. I have no real
> > quarrel here, but you're comparing quasi-identical features and claiming
> > it's bad there and good here.
> >
> >
> >
> >
> > Ah, for clients perhaps. Still Informix is relying on their ability to
> > connect to the server, not on an additional layer of security within the
> > database.
> >
> >
> >
> >
> > I guess if Sagent is a requirement you should evaluate whether they are[/ref][/ref]
Open [/ref]
infinitely - [/ref]
each [/ref]
counter - [/ref]
does [/ref]
slide [/ref]
it's [/ref]
be [/ref]
XPS. [/ref]
than [/ref]
an [/ref]
and
>
> --
>
>
> Atte,
>
>
> Jesús Antonio Santos Giraldo
> com
> net
>
>[/ref]
sending to informix-list
Jack Guest
-
Paul #9 Re: Informix limitations, should we be using Oracle?
If you cast null then it should work OK you need 9.x
select null::INT
from systables;
Jean Sagi wrote:
> >
> > --
> >
> > Atte,
> >
> > Jesús Antonio Santos Giraldo
> > com
> > net
> >
> > sending to informix-list[/ref]
>
> --
> Paul Watson #
> Oninit Ltd # Growing old is mandatory
> Tel: +44 1436 672201 # Growing up is optional
> Fax: +44 1436 678693 #
> Mob: +44 7818 003457 #
> www.oninit.com #
>
> Jean Sagi
> com
> net
>
> sending to informix-list[/ref]
--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #
Paul Guest
-
John #10 Re: Informix limitations, should we be using Oracle?
On Tue, 18 Nov 2003 14:41:05 +0000, Paul Watson <com>
wrote:
Same here:
select null::char(1)
from systables
JWC
John Guest
-
Paul #11 Re: Informix limitations, should we be using Oracle?
Of course unless the column is null then
you need
select eric.null
from eric
John Carlson wrote:
>
> Same here:
>
> select null::char(1)
> from systables
>
> JWC[/ref]
--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #
Paul Guest
-
Mark #12 Re: Informix limitations, should we be using Oracle?
John Carlson wrote:
>
>
>
>
> Same here:
>
> select null::char(1)
> from systables[/ref]
I was wondering what the equivalent was in SE. In 4GL you used to be able
to use a blank string, like "". I know Jonathan, I know, it was a bug.. :-)
Anyway, that doesn't work in SQL, but strangely, (or not if it's related to
the 4GL "feature") if you convert an empty string to a numeric value by
adding any numeric value, the string gets converted to NULL along the way.
Now NULL plus any value is...
SELECT ""+0 AS col1 -- or ""+9999 or " "+0 or...
FROM systables
INTO TEMP mds
;
SELECT COUNT(*)
FROM mds
WHERE col1 IS NULL
;
Well it does in SE version 7.24, so maybe it's been fixed now. ;-)
....Oh, maybe not, it also works in IDS 9.40. 8-)
Personally, I wouldn't be inclined to use this method or expect it to be
supported.
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:com |//////// /|
| Mydas Solutions Ltd http://MydasSolutions.com |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+
sending to informix-list
Mark Guest
-
Jonathan #13 Re: Informix limitations, should we be using Oracle?
Jack Parker wrote:
No, the full outer join is:
A NULL
B NULL
C C
NULL D
NULL E
--
Jonathan Leffler #include <disclaimer.h>
Email: net, ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Jonathan Guest
-
Art #14 Re: Informix limitations, should we be using Oracle?
On Wed, 19 Nov 2003 00:59:29 -0500, Jonathan Leffler wrote:
Note that a full bi-directional outer join can easily be implemented using a
UNION of two (assuming only two tables are involved) LEFT OUTER JOINs:
select ...
from tab1 left outer join tab2 on tab1.key = tab2.key
where ...
UNION
select ...
from tab2 left outer join tab1 on tab1.key = tab2.key
where ...
....;
And this is parallelizable so the two sides of the UNION can be executed
concurrently.
Art S. Kagel
>
> No, the full outer join is:
>
> A NULL
> B NULL
> C C
> NULL D
> NULL E
>
>[/ref]
Art Guest
-
Jack #15 Re: Informix limitations, should we be using Oracle?
Picking nits this morning are we?
But thank you for clearing that up.
cheers
j.
-.-- --- ..- / -. . . -.. / - --- / --. . - / .- / .-.. .. ..-. . .-.-.- /
.... --- / -.. --- / .. .-.-.-
----- Original Message -----
From: "Jonathan Leffler" <net>
To: <org>
Sent: Wednesday, November 19, 2003 12:59 AM
Subject: Re: Informix limitations, should we be using Oracle?
[/ref]
the
>
> No, the full outer join is:
>
> A NULL
> B NULL
> C C
> NULL D
> NULL E
>
>
> --
> Jonathan Leffler #include <disclaimer.h>
> Email: net, ibm.com
> Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
>
>[/ref]
sending to informix-list
Jack Guest
-
Lyle #16 Re: Informix limitations, should we be using Oracle?
"Jack Parker" <net> wrote in
news:bpg1ab$s5a$xmission.com:
That didn't look like nitpicking to me...
Yes, we do. ;-)
Lyle Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
