Tables

Go Up to Supported Objects

Tables are a the basic unit of data storage. Tables store all the data accessible to users in rows and columns. Each column has a name, datatype and other associated properties. After you define a table, users can insert valid data into the table, which you can later query, update and delete.

Note: System tables are treated separately from user-defined tables in the Navigator/Explorer to ensure that system tables are not accidentally altered or dropped.

Creating and editing

• Tables Wizard (DB2 LUW) and Tables Editor (IBM DB2 LUW)
• Tables Wizard (DB2 Z/OS) and Tables Editor (IBM DB2 Z/OS)
• Tables wizard (MySQL) and Tables editor (MySQL)
• Tables Wizard (Oracle) and Tables Editor (Oracle)
• Tables Wizard (PostgreSQL) and Tables Editor (PostgreSQL)
• Tables Wizard (SQL Server) and Tables Editor (SQL Server)
• Tables Wizard (Sybase ASE) and Tables Editor (Sybase ASE)

Note: Creation and editing of objects of this type is not supported against Sybase IQ datasources.

DBMS platform availability and object actions/operations supported

The following table lists object actions available for this object type. For an introduction to object actions and details on usage of specific actions, see Object actions.

	DB2 LUW	DB2 z/OS	MySQL	ORCL	PSTGRS *	SQL SVR	SYB ASE	SYB IQ
Allocate Extent				✓
Analyze				✓
Analyze Tables			✓
Build Query	✓	✓	✓	✓	✓	✓	✓
Change Access Status				✓
Change Owner					✓
Change Schema					✓
Check Tables			✓
Checksum Tables			✓
Convert Tables			✓
Create Alias	✓	✓
Create Clone		✓
Create Insert Statements	✓	✓	✓	✓	✓	✓	✓
Create Like	✓	✓	✓	✓	✓	✓	✓
Create Synonym				✓
Create View	✓	✓		✓		✓	✓
DBCC						✓	✓
Deallocate Unused Space				✓
Delete Statistics							✓
Describe	✓	✓	✓	✓		✓	✓
Disable Keys			✓
Disable/Enable Triggers						✓	✓
Drop	✓	✓		✓	✓	✓	✓	✓
Drop Clone		✓
Drop Unused				✓
Edit Data	✓	✓	✓	✓	✓	✓	✓
Enable Keys			✓
Enable/Disable Filetable(s)						✓
Estimate Size				✓			✓
Exchange Data With Clone		✓
Extract	✓	✓		✓	✓	✓	✓	✓
Extract Data as XML				✓		✓	✓
Flashback Table				✓
Flush Tables			✓
Hide Text							✓
Import Data From File	✓	✓	✓	✓		✓	✓
Lock	✓	✓
Migrate	✓	✓	✓	✓		✓	✓
Move Table	✓
Object Properties								✓
Optimize Tables			✓
Place							✓
Quiesce Tablespaces	✓
Rebuild Table			✓
Recompile						✓	✓
Rename	✓	✓	✓	✓	✓	✓	✓
Reorganize /Reorg	✓			✓			✓
Repair Tables			✓
Report	✓	✓		✓		✓	✓
Runstats Tablespace		✓
Schema (Object Action)	✓	✓	✓	✓		✓	✓
Select * From	✓	✓	✓	✓	✓	✓	✓	✓
Set Integrity	✓
Shrink				✓
Transfer Ownership	✓
Truncate	✓	✓	✓	✓		✓	✓
Update Statistics	✓					✓	✓

DBMS platform-specific notes

MySQL

MySQL servers can store tables in multiple formats, including MyISAM and InnoDB. MyISAM tables (ISAM is the acronym for indexed sequential access method) are used most often for read operations. The read operation is very fast, but you cannot include any referential integrity, such as a foreign key. Also, MyISAM tables only issue table-level locks. InnoDB tables, on the other hand, do permit transactions and foreign key constraints. InnoDB tables also lock data at the row level, which is appropriate for high transaction tables. Additional table types available are MERGE, MEMORY, FEDERATED, and ARCHIVE among others. For a complete discussion of table types, go to the MySQL documentation of table and engine types. For more information, see Accessing Third Party Documentation.