Getting Started with DB2 UDB for zSeries

This section describes how you would get started in a DB2 UDB for zSeries environment. The DB2 UDB for zSeries architecture is first described, followed by some typical activities and associated interfaces you would use to get started. Where appropriate, comparisons to DB2 LUW will be mentioned, and equivalent concepts will be highlighted.

Overview of the DB2 UDB for zSeries Architecture

Figure A.27 shows a simplified view of the DB2 for zSeries system structure.

Figure A.27. DB2 UDB for zSeries architecture.

In DB2 for zSeries, a DB2 subsystem provides a separate DB2 environment similar to a DB2 LUW instance. Several DB2 for zSeries subsystems can be installed in the same machine logical partition (LPAR), and they can only communicate with each other through the Distributed Data Facility (DDF). (Data sharing provides another way that DB2 subsystems work together, but we don't discuss this here.)

A DB2 for zSeries subsystem can contain several databases. Databases in this subsystem interact with each other. In fact, as you can see from Figure A.27, the catalog itself (DSNDB06) is a database. The work file database (DSNDB07) would correspond to the temporary space used in DB2 LUW. The default database (DSNDB04) is used to store objects that users create without explicitly indicating the database to which they belong. DSNDB04 would correspond to USERSPACE1 in DB2 LUW. Another databasethe directory (DSNDB01)keeps track of internal system information and is kept in sync with the catalog.

The catalog, directory, and other system structures are created once at DB2 subsystem installation time, not like in DB2 LUW where a catalog and other system structures are created for every CREATE DATABASE that is executed.

Within the DB2 for zSeries structure, you can perform SQL operations using tables from different databases. For example, say you have table TS56692.testtbl in database MYDB1 and table DSN8810.emp in the default database DSNDB04. Then, you can execute the following query:

 SELECT B.name, B.salary FROM   TS56692.testtbl A, DSN8810.emp B WHERE  A.id = B.edlevel 

Table spaces are physical objects containing logical tables. The page size of the table space is determined by the buffer pool chosen to work with the given table space. There are four types of table spaces:

• Simple

• Segmented

• Partitioned

• Large objects (LOB)

Refer to the DB2 for z/OS SQL Reference for more detail about them.

DB2 for zSeries uses predefined buffer pools with different page sizes (4K, 8K, 16K, and 32K). Most of these buffer pools start with a size of zero which means they are not active. In order to make a buffer pool active or "create" it, use the ALTER BUFFERPOOL command and change the size to a value greater than zero.

Storage groups are used to group different volumes of DASD (Direct Access Storage Devices) that hold datasets in which tables and indexes are actually stored. The default storage group SYSDEFLT is created after installing DB2. All volumes of a given storage group must have the same device type, but parts of a single database can be stored in different storage groups.

Transaction logging in DB2 for zSeries is similar to LUW. Active and archive logs are used. A data set called the bootstrap data set (BSDS) keeps track of its logs.

With respect to configuration parameters, in zSeries they are often called zparms (for the default name of the parameter module, which is DSNZPARM). Only one set of parameters would affect the entire DB2 subsystem and its databases. The job DSNTIJUZ is used to specify the desired values for these parameters. When run, this job will assemble and link-edit the DSNZPARM module as well as the application program's default module DSNHDECP. The assembled zparm module can be specified when starting DB2. If it is not specified, the module with name DSNZPARM will be used. In versions prior to V7, changes to zparms required DB2 for zSeries to be recycled (stopped and started) to load the new parameter module into memory. With V7 and beyond, this is still the case for some parameters, but not for all. The new SET SYSPARM command allows you to load a new parameter module without recycling DB2.

Catalog

The catalog is a series of system tables that hold the metadata for any database object that is created. Catalog tables have a schema of SYSIBM. Some of the DB2 for zSeries catalog tables are updatable, including some of columns that hold statistics information about the data.

Table A.4 shows some common catalog tables that may be useful when developing your SQL procedures. For a complete list, refer to the DB2 UDB for z/OS SQL Reference Guide.

Table A.4. Common System Catalog Views

Catalog	Table Description
SYSIBM.SYSCOLUMNS	Column information for each table or view.
SYSIBM.SYSROUTINES	Information about stored procedures and user-defined functions.
SYSIBM.SYSPLAN	Information about plans.
SYSIBM.SYSPACKAGE	Information about packages.
SYSIBM.SYSTABLES	Information about tables, views, or aliases.
SYSIBM.SYSTABLESPACE	Information about table spaces.
SYSIBM.SYSTRIGGERS	Information about triggers.

Interacting with DB2 for zSeries

There are several interfaces for working with DB2 for zSeries platforms and databases on that platform. Possible options include the following:

• Using DB2 Interactive (DB2I) interface from a Time Sharing Option (TSO) subsystem.

• Using the DB2 LUW Client to work with a DB2 for zSeries database. The client includes tools such as the Command Center, Control Center, Development Center, and Command Line Processor. The DB2 Connect software would be required to use this interface.

Using DB2I

In order to start working with zSeries natively, you will need access to a zSeries server. Use an emulator from a Windows environment to log on to a TSO subsystem on the mainframe, and invoke DB2I which should be available from an Interactive System Productivity Facility (ISPF) menu. Depending on the way your environment was set up in your company, DB2I may be in a different ISPF menu. Contact your system administrator if you cannot find a way to invoke DB2I from ISPF. Figure A.28 shows the DB2I primary option menu.

Figure A.28. The DB2I primary option menu.

The DB2I Primary Option Menu has several options, and each of them is briefly explained in Figure A.28. The most common option used to execute SQL queries is option 1 (SPUFI). Figure A.29 shows an example.

Figure A.29. Option 1 (SPUFI) from the DB2I primary option menu

In Figure A.29, two datasets are specified: one to be used as input containing the SQL statement to be executed, and the other one to be used as the output of the statement execution. For the example, in the figure TS56692.CNTL.SQL(SELECT) is a dataset that contains this statement (not shown in the figure):

 SELECT * FROM DSN8810.EMP; 

The output of this statement in the example is stored in dataset TS56692.OUT.INSPUFI. Figure A.30 shows the output.

Figure A.30. Output of an SQL statement executed from SPUFI.

Option 7, DB2 Commands, is used to execute DB2 commands natively. Figure A.31 shows some commands previously executed from this option.

Figure A.31. The DB2 Commands option of the DB2I menu.

Other options in the DB2I primary option menu allow you to complete information specific to your environment for a specific task, which is then used to generate a Job Control Language (JCL) job that can be saved and reused in the future. For example, Option 8 Utilities allows you to generate a JCL job for several utilities such as RUNSTATS.

Using DB2 LUW Client Tools

To work with DB2 for zSeries, you can work with the DB2 LUW client toolsin particular, the CLP and the Control Center. Figure A.31 shows the Control Center with a connection to a DB2 for zSeries subsystem named NEW_JERS. Note that the Control Center folder, and options vary compared to a LUW database. For example, Figure A.32 shows a folder for Storage Groups that is specific to zSeries.

Figure A.32. The Control Center with a connection to a zSeries subsystem.

Figure A.33 shows how to connect to a DB2 for zSeries subsystem from the CLP and execute a query. The syntax of the connect statement is the same, just ensure you use a TSO ID and password that can access the DB2 subsystem. In addition, ensure that you have cataloged the connectivity information correctly in your system database directory, node directory, and DCS directory at your client machine.

Figure A.33. The CLP used to connect to a zSeries subsystem and issue a query.

Note that in DB2 for zSeries, you connect to a subsystem, not to a specific database.