If you have the DB2 Connect software installed, then you have access to the Database Connection Services (
DCS) directory. It contains the information you need to connect to hosts such as OS/390, z/OS, or
iSeries. Issuing the "list db directory" command will show you the databases that you can connect to. Then to connect to one of these databases you issue the command CONNECT TO
dbname USER
username USING password.
Alternatively, you can use a DB2 Connect Gateway which sits between the client and host. This gateway machine requires DB2 Connection Enterprise Edition. You can use Search Discovery to look for DB2 servers on the network. When setting up a new machine to connect to remote hosts, you can use the Configuration Assistant tool to export the configuration of a client which is already set up. The
configuration info gets stored in an access profile file. You can then import this on the machine you are setting up to speed configuration.
Next I want to share a little bit about the objects in an IBM DB2 database. A buffer pool is memory which caches recently used database information. There are two types of table spaces: system-managed and database-managed. The system managed table spaces are managed by the operating system. While the database-managed ones are managed by DB2.
SYSCATSPACE is the table space containing the system catalogs, which store the information on database objects. The system catalog tables use the following
schemas:
SYSIBM,
SYSCAT, and
SYSSTAT.
When specifying database objects, the database uses the authorization ID by default if you do not
explicitly state the schema name. The authorization ID is the one used when connecting to the database. Regarding database column types, CHAR has a maximum length of 254 characters. And the TIMESTAMP type encompasses both date and time down to the nanosecond granularity.
You can clone a table by issuing a CREATE TABLE
tablename LIKE
sourcetable. This does not copy the constraints, indexes, or trigger of the source table. It also does not copy the table data. It only duplicates the table structure. Another way to clone a table is to issue a CREATE TABLE
tablename AS (SELECT * FROM
sourcetable) DEFINITION ONLY.
The identity column is one which automatically generates unique numbers for sequences. There can be a maximum of one identity column per table. The syntax for creating the column is
columname INTEGER GENERATED ALWAYS AS IDENTITY. The values used by the identity column do not get affected by a rollback. Once such a sequence is generated, that number is used up regardless when whether you COMMIT or ROLLBACK.
You can implement unique constraints on a table by creating a unique index on it. Materialized Query Tables (
MQTs) are tables created with data from a query. Prior to DB2
version 8,
MQTs were known as automatic summary tables (
ASTs). Temporary tables in DB2 exist for one connection.
Disks which hold DB2 data are divided up into extents. A table scan is when DB2 scans all data pages in all extents. A view is a virtual table. The view definition is stored in
SYSCAT.VIEWS. The INSTEAD OF trigger performs operations on the actual tables which back a view. Stored procedures can be written in the C, Java, and
SQL PL languages. A fenced stored procedure runs in its own address space separate from the DB2 engine. Fenced stored procedures run
slower than unfenced ones, but provide more protection.
