SEO

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 <paul@oninit.com>
To: informix-list@iiug.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:
>
> 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 recommendedby
> >>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

--
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
jeansagi@myrealbox.com
jeansagi@netscape.net


sending to informix-list

If you cast null then it should work OK you need 9.x

select null::INT
from systables;

Jean Sagi wrote:
>
> 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 <paul@oninit.com>
> To: informix-list@iiug.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:
> >
> > 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 standarddata 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
>
> --
> 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
> jeansagi@myrealbox.com
> jeansagi@netscape.net
>
> sending to informix-list

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

On Tue, 18 Nov 2003 14:41:05 +0000, Paul Watson <paul@oninit.com>
wrote:

>If you cast null then it should work OK you need 9.x
>
>select null::INT
>from systables;
>


Same here:

select null::char(1)
from systables


JWC

Of course unless the column is null then
you need

select eric.null
from eric

John Carlson wrote:
>
> On Tue, 18 Nov 2003 14:41:05 +0000, Paul Watson <paul@oninit.com>
> wrote:
>
> >If you cast null then it should work OK you need 9.x
> >
> >select null::INT
> >from systables;
> >
>
> Same here:
>
> select null::char(1)
> from systables
>
> JWC

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