When using IBM DB2, there may come a time where you wish to view the structure of your database or even of specific tables. In this brief tutorial, we’ll explore two different methods of examining your database tables in more detail to see just what the schema really looks like.
Tables are logical objects in Db2 that stores data. Tables are made up of columns and rows, like a spreadsheet. The rows of a table do not have a specified order. However, the columns of the table have the order that is specified when the table created.
A column in a table is associated with a specific data type e.g., character string, numeric, or temporal. A column always stores values of the same type. A table can have one or more columns. Typically, a table contains multiple columns.
To create a new table, you use the CREATE TABLE statement. The following shows the simplified syntax of the CREATE TABLE statement −
CREATE TABLE [schema_name.]table_name ( column_1 data_type NOT NULL, column_2 data_type DEFAULT value, column_3 data_type CHECK(expression), ..., table_constraints );
Syntax
First, specify the name of the table that you want to create. The name of the table must be unique within the schema. You can optionally specify the schema to which the table belongs.
Second, specify a list of columns of the table. Each column is associated with a specific data type and may have constraint such as NOT NULLand CHECK constraint.
Third, specify the table constraint such as PRIMARY KEY, FOREIGN KEY and CHECK constraints. Note that you’ll learn more about these constraints in the next tutorial.
Tables are made up of columns and rows. The rows of a relational table have no fixed order. The order of the columns, however, is always the order in which you specified them when you defined the table.
At the intersection of every column and row is a specific data item, which is called a value. A column is a set of values of the same type. A row is a sequence of values such that the nth value is a value of the nth column of the table. Every table must have one or more columns, but the number of rows can be zero. DB2 accesses data by referring to its content instead of to its location or organization in storage.
DB2 has a number of ways of accessing the data it needs, but the most basic one – the one that will always work to get what it needs – is the table scan. A Table scan means that DB2 will read through every single row in a table to get the data it needs. Table scans appear on explain plans for some queries. My favorite method of generating an explain plan is described in this post: SQL Analysis Overview
Assuming the query has a where clause, DB2 may use a table scan to handle each row individually and ask “does this meet my criteria?”. If the answer is yes, then the row is added to the result set DB2 is building. If the answer is no, then the row is discarded and not used.
When do table scans occur?
DB2 may choose a table scan can whether your query is returning one row or every single row in a table. While a ‘select *’ with no where clause is almost certain to do a table scan, they can also occur in a scenario where you are returning only one row out of a million row table. A table scan is like DB2’s last resort – if it cannot find a an index to help, it will fall back on the table scan to examine each row in the table individually.
Table scans can even occur on tables you didn’t know existed. They can occur on temporary tables created just for this query (implicitly or explicitly). Since there is no index on some types of temporary tables, DB2 uses table scans a lot in this context. Check out my post on Fun with IN-lists and GENROW to see one of the ways this can look.