EXTERNAL table read
User asked if external tables can be read like normal tables. Luis Marques explained that external tables have operational restrictions per IBM documentation. Eric Vercelletto clarified that external tables support SELECT statements with WHERE clauses, but don't support indexes, UPDATE, or DELETE operations. INSERT replaces entire file contents.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, How can I read one external table as an usual table, is it possible? Can I do select from one external table created by another program? As I only have seen operations over external table after create...??? Thanks for any help, SP
There are restrictions to operations can be done on EXTERNAL TABLES. Check the online documentation for more details: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_2071.htm You can define an EXTERNAL TABLE and then have an external program modify the data file . For example, you can define an EXTERNAL TABLE once for a daily bulk load, and everyday you smash the table data file with a new content. It is one of the ways to load unload data into the database from flat files.
Thanks for your reply, I itend to use it as temporary unload for query and after read it as a normal table, but seems that I can't do it! Is this correct?
Thanks for your reply, I itend to use it as temporary unload for query and after read it as a normal table, but seems that I can't do it! Is this correct?
An EXTERNAL table is a permanent object in the database. Other sessions, if they have the proper permissions, can read and insert into it. Every insert into the external table will truncate the table ( it will truncate the files that support the external table ) and replace it's contents. Check the online documentation. There are several examples that might be helpful: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_2068.htm
Sergio, an external table is seen as a table in IDS, the difference is that it is no installed in the dbspace but in files out of the dbspaces. You can use any SELECT statement even with where clauses, but you are not entitled to create indexes for an external table. you can INSERT INTO an external table, but it has to be the full file or directory contents you declared in the table schema. YOu cannot INSERT rows one by one. You cannot UPDATE no DELETE rows from an external table. Having indexes on external tables would be a good thing when you handle enormous files :-) Something to consider: use of external tables is way faster that load and unload SQL statements. This is true for unloading data (INSERT INTO ext_table SELECT xxx xxx FROM real_table), and also for importing data (INSERT INTO real_table SELECT *** (WHERE xxx) from ext_table.