The smallest storage unit in a DB2 database is a page. A row in a table cannnot span across multiple pages. Consecutive pages are grouped together into extents. Tables and indexes are stored in table spaces. These tables spaces are logically divided into containers. You can issue the LIST TABLESPACES command to see their names. Not every database action results in the database actually going to the disk. Data is cached in memory called the Database Buffer Pool.
Note that autocommit is on by default in the following tools: the Command Line Processor, the Command Windows, and the Command Editor. You can limit the number of rows returns in a SELECT statement by appending criteria like FETCH FIRST 3 ROWS ONLY to the WHERE clause.
Inner joins in DB2 work just like they do in Oracle. You add a WHERE clause criteria joining columns from the two tables, separated by an equals sign. However outer joins in DB2 have a syntax which differs from Oracle. You use the LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN keywords in the WHERE clause. Hopefully these are self explanatory.
DB2 has a function that can be used like this: COALESCE(colname, 0). It acts like the NVL function in Oracle. If the "colname" column has a NULL value, the value supplied (0 in this case) is substituted instead of the NULL. DB2 has ROWNUMBER() OVER() which acts like ROWNUM in Oracle. It is a pseudo column which is a number representing the row number in the results rowset.
You can perform set operations on 2 or more query results sets. UNION ALL will include all duplicate rows. And INTERSECT will only return unique values.
PUBLIC is a special group that everyone belongs to. When granting privileges, WITH GRANT OPTION will allow the grantee to also grant the privilege to other users as well. A user requires the table space USE privilege to create tables in that table space. However users only need EXECUTE privilege for packages they wish to run. They do not need privileges for each of the objects referenced by the package. To see the list of privileges you have, issue the GET AUTHORIZATIONS command.
