Hey, hey, hey!!

It works... !

Now there is something new I know...

Chucho

-----Original Message-----
From: "Brian Foster" <bc_foster@hotmail.com>
To: informix-list@iiug.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 ...
>
>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:
>> 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.
>>
>>
>>>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
>>>http://www.tpc.org/tpch/results/tpch...p?orderby=dbms
>>>http://www.tpc.org/information/benchmarks.asp
>>
>>
>> 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.
>>
>>
>>>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.
>>>
>>
>>
>> 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.
>>
>>
>>>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.
>>
>>
>> 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.
>>
>>
>>>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
>>
>>
>> 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.
>>
>>
>>>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'.
>>
>>
>> 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.
>>
>>
>>>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'.
>>
>>
>> Not quite, only those which require DSS resources, so your 'simple count'
>> would go straight through.
>>
>>
>>>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.
>>
>>
>> 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.
>>
>>
>>>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.
>>
>>
>> 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'.
>>
>>
>>>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.
>>>
>>
>>
>> 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.
>>
>>
>>>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
>>
>>
>> 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
>>
>>
>>>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.
>>
>>
>> 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.
>>
>>
>>>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.
>>
>>
>> And sometimes this will even remove the locks held by these sessions when
>> they are thusly killed.
>>
>>
>>>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.
>>
>>
>> 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.
>>
>>
>>>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.
>>
>>
>> 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.
>>
>>
>>>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.
>>>
>>
>>
>> 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.
>>
>>
>>>Lack of diagnostic/monitoring tools
>>>
>>>No user friendly or graphical monitoring tools are available. This
>>>makes diagnosis and performance monitoring extremely difficult.
>>>
>>
>>
>> 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.
>>
>>
>>>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.
>>>
>>
>>
>> I'm not sure to which problem you refer. Raw partitions are very simple
to
>> manage.
>>
>>
>>>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.
>>>
>>
>>
>> '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.
>
>>
>>>Informix can use cooked partitions, but it is not recommended by
>>>Informix for performance reasons.
>>
>>
>> True.
>>
>>
>>>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 special system privileges are required to add
>>>or resize datafiles.
>>
>>
>> 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.
>>
>>
>>>Fast Loading
>>>
>>>Our data warehouse employs fast loading capabilities (bypassing ODBC)
>>>in order to upload Mainframe EBCDIC data.
>>
>>
>> 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.
>>
>>
>>>This feature is particular in Informix to XPS; in Oracle it is
>>>standard functionality.
>>
>>
>> Actually HPL and the light append has been around since 7.x, I first used
it
>> around 1996.
>>
>>
>>>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.
>>
>>
>> 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.
>>
>>
>>>Oracle produces two output files when EBCDIC uploading - an error log,
>>>and an EBCDIC reject file - ready for reprocessing.
>>>
>>
>>
>> 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.
>>
>>
>>>We currently have to reprocess complete datasets, or reject complete
>>>datasets. With Oracle this would not be necessary.
>>
>>
>> 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.
>>
>>
>>>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.
>>
>>
>> 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.
>>
>>
>>>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.
>>>
>>
>>
>> See above.
>>
>>
>>>Limitation comparisons
>>>
>>>Informix XPS
>>>18 characters for database objects
>>
>> Ok.
>>
>>
>>>256 byte maximum row size in Indexes
>>
>> 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/zo...rticle/parker/
>> 0502parker.html for some examples.
>>
>>
>>>255 byte limit on varchar fields
>>
>> True, although you can go to text and have at least 2GB last time I
>> checked.
>>
>>
>>>SQL Restrictions -
>>>Cannot SELECT NULL
>>
>> What? Sure you can.
>>
>>
>>>No general DUAL table
>>
>> 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.
>>
>>
>>>No MINUS operations
>>
>> True, that's a neat Oracle feature.
>>
>>
>>>No GROUP BY or Aggregation in SUBQUERY, NESTED QUERY, COMPOUND QUERY,
>>
>>
>>>EMBEDDED QUERY
>>
>> Haven't tried this. Never needed it.
>>
>>
>>>No Full OUTER JOIN
>>
>> 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.
>>
>>
>>>No Implicit ROWID
>>
>> Not anymore, you can ask for one if you need it.
>>
>>
>>>XPS - No true database flatfile Import Export
>>
>> Totally untrue. For that matter, has Oracle come up with a true flatfile
>> export yet?
>>
>>
>>>Varchar fields not treated as ANSI standard
>>
>> 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.
>>
>>
>>>No DISABLE CONSTRAINT commands - Must DROP and RECREATE constraints -
>>
>> Bullshit. You need to spend more time with the manual.
>>
>>
>>>Additional processing
>>>Stored procedures are not prepared - Object dependence not checked
>>>until runtime
>>
>>
>> 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.
>>
>>
>>>Page size can only be 2 or 4k
>>
>>
>> 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.
>>
>>
>>>Oracle
>>>30 characters - more meaningful names
>>
>> 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.
>>
>>
>>>Unlimited
>>
>> (this was the index size - not sure why you want this)
>>
>>
>>>4000 limit
>>
>> (2GB limit on text)
>>
>>
>>>No comparable restrictions
>>
>> 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.
>>
>>
>>>Flatfile IMPORT EXPORT available for backups and rebuilds
>>
>> But not a true flatfile.
>>
>>
>>>No comparable ANSI incompatibility
>>
>> Moot.
>>
>>
>>>DISABLE ENABLE DEFER VALIDATE NOVALIDATE CONSTRAINT possible
>>
>> The same basic stuff is available. Since this is not ANSI standard the
name
>> may vary slightly.
>>
>>
>>>Stored procedures are compiled as PCODE
>>
>> ??? Informix stored procedures are also compiled into code within the
>> engine. Not sure where you are getting this stuff.
>>
>>
>>>Page size can be as large as 64K - 16 times fewer reads for DSS table
>>>scans
>>
>>
>> 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.
>>
>>
>>>Warehouse functional comparisons
>>>
>>>Informix
>>>Tables and related indexes are partitioned on the same key -
>>
>>
>> Untrue again. Hybrid fragmentation has been around for some time -
>> including back in 8.31.
>>
>>
>>>inflexible configuration
>>>GK Indexes - Index prejoin only
>>
>>
>> You need to be pulling out materialized views here - which is the proper
>> comparison to the GK index.
>>
>>
>>>Necessary to manage security utilising both OS and database levels
>>
>>
>> Sorry? This is an Oracle issue - you need two layers of security,
Informix
>> uses the OS security.
>>
>>
>>>Sagent supports ODBC only for Informix
>>
>>
>> Who?
>>
>>
>>>Oracle
>>>Partitioning tables and indexes on alternate keys - flexible
>>>configuration for performance
>>
>> Moot.
>>
>>
>>>Materialised Views - Database managed JOINS of tables - Physically
>>>existing summary tables for enhanced performance. Time savings in
>>>aggregations
>>
>>
>> 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.
>>
>>
>>>Security and permission totally managed within the database
>>
>>
>> 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.
>>
>>
>>>Sagent support for High Speed Loading into Oracle
>>>
>>
>>
>> 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.
>>
>>
>>
>>
>
>--
>
>
>Atte,
>
>
>Jesús Antonio Santos Giraldo
>jeansagi@myrealbox.com
>jeansagi@netscape.net
>
>sending to informix-list




Jean Sagi
jeansagi@myrealbox.com
jeansagi@netscape.net


sending to informix-list