Chapter 1. Database objects and Programming Methods

Knowledge of naming conventions of DB2 objects

[Note]

A database is a collection of database objects. You can create a database on one or more database partitions. A database partition, as its name implies, is part of a database. We discuss these concepts in more detail in the next sections; for now, we introduce you to the DB2 database objects. Figure 1.1, “An overview of the DB2 database objects” illustrates these objects in a database created in a single-partition environment (Database Partition 0). The database objects are described next.

Figure 1.1. An overview of the DB2 database objects

An overview of the DB2 database objects

  • A partition group is a logical database object representing a collection of database partitions. In a single-partition environment, partition groups are not relevant; however, in multi-partition environments, a partition group facilitates the work of a database administrator, as he or she is able to perform database operations on or within several partitions at a time. Partition groups can contain one or more table spaces. In Figure 1.1, “An overview of the DB2 database objects”, partition group pg1 contains table space tbls1.

  • Table spaces are logical objects that associate tables and indexes to the physical devices where these objects are stored, as well as to the physical memory where the data in these objects is cached when it is being accessed. Tables and indexes must be created inside a table space as illustrated in Figure 1.1, “An overview of the DB2 database objects”, where tables t1 and t2 and index ix1 are all created inside table space tbls1.

  • Tables consist of rows and columns, like spreadsheets. Data can be inserted, deleted, and updated within a table. Figure 1.1, “An overview of the DB2 database objects” has two tables, t1 and t2.

  • Indexes are an ordered set of keys each pointing to a row in a table. They improve application performance when looking for specific rows. Indexes can also be used to guarantee the uniqueness of rows. In Figure 1.1, “An overview of the DB2 database objects”, index ix1 is associated to table t1.

  • A buffer pool is an area in physical memory that caches the database information most recently used. Without buffer pools, every single piece of data has to be retrieved from disk, which is very slow. Buffer pools are associated to tables and indexes through a table space. In Figure 1.1, “An overview of the DB2 database objects”, table space tbls1 is associated to buffer pool bp1, therefore tables t1 and t2 and index ix1 use buffer pool bp1.

  • A view is an alternate way of representing data that exists in one or more tables. A view can include some or all of the columns from one or more tables. It can also be based on other views. In Figure 1.1, “An overview of the DB2 database objects”, view v1 is based on table t1.

  • Every object in the database is created with a two-part name separated by a dot:

    schema_name.object_name
    
    The first part of this two-part name is the schema name. A schema is an object that provides a logical grouping of other database objects. A schema can be owned by an individual who can control access to the objects within it. Schemas can be implicitly or explicitly specified when accessing an object.

  • A trigger is an object that contains application logic that is triggered by specific actions like an update to a table. For example, in Figure 1.1, “An overview of the DB2 database objects”, a trigger can be created so that after table t1 is updated, table t2 is also updated with some other information.

  • A stored procedure is an object used to move application logic into your database. By keeping part of the application logic in the database, there are performance improvements as the amount of network traffic between the application and the database is considerably reduced.

  • User-defined functions (UDFs) allow database users to extend the SQL language by creating functions that can be used anywhere a DB2 built-in function is used. Similar to stored procedures, application logic can be moved to the database by using UDFs.

  • A package is an object containing the compiled version of your SQL queries as well as the access path that the DB2 optimizer, the brain of DB2, has chosen to retrieve the data for those queries.

  • A sequence object allows the generation of unique numbers in sequence. These numbers can be used across the database as a unique identifier for tables or for applications.

General naming rules

Rules exist for the naming of all objects, user names, passwords, and groups. Some of these rules are specific to the platform you are working on. For example, there is a rule regarding the use of upper and lowercase letters in a name.

  • On UNIX® platforms, names must be in lowercase.

  • On Windows® platforms, names can be in upper, lower, and mixed-case.

Unless otherwise specified, all names can include the following characters:

  • 'A' through 'Z'. When used in most names, characters 'A' through 'Z' are converted from lowercase to uppercase.

  • '0' through '9'.

  • ! % ( ) { } . - ^ ~ _ (underscore) @, #, $, and space.

  • \ (backslash).

Names cannot begin with a number or with the underscore character.

Do not use SQL reserved words to name tables, views, columns, indexes, or authorization IDs.

There are other special characters that might work separately depending on your operating system and where you are working with the DB2® database. However, while they might work, there is no guarantee that they will work. It is not recommended that you use these other special characters when naming objects in your database.

User and group names also need to follow the rules forced on specific operation systems by the related systems. For example, on Linux and UNIX platforms, user names and primary group names must follow these rules:

  • Allowed characters: lowercase 'a' through 'z', '0' through '9', and _ (underscore) for names not starting with '0' through '9'.

  • Length must be less than or equal to 8 characters.

Some objects have additional restrictions shown below.

Database object naming rules

Objects: Aliases, Buffer pools, Columns, Event monitors, Indexes, Methods, Nodegroups, Packages, Package versions, Schemas, Stored procedures, Tables, Table spaces, Triggers, UDFs, UDTs, Views.

Guidelines: Can contain up to 18 bytes except for the following:

  • Table names (including view names, summary table names, alias names, and correlation names), which can contain up to 128 bytes.

  • Column names can contain up to 30 bytes.

  • Package names, which can contain up to 8 bytes.

  • Schema names, which can contain up to 30 bytes.

  • Package versions, which can contain up to 64 bytes.

  • Object names can also include: valid accented characters (such as ö) and multibyte characters, except multibyte spaces (for multibyte environments).

  • Package names and package versions can also include periods (.), hyphens (-), and colons (:).

  • Names can contain the following anywhere but in the first character of the string: _ (underscore).

Additional schema names information

  • User-defined types (UDTs) cannot have schema names longer than 8 bytes.

  • The following schema names are reserved words and must not be used: SYSCAT, SYSFUN, SYSIBM, SYSSTAT.

  • To avoid potential migration problems in the future, do not use schema names that begin with SYS. The database manager will not allow you to create triggers, user-defined types or user-defined functions using a schema name beginning with SYS.

  • It is recommended that you not use SESSION as a schema name. Declared temporary tables must be qualified by SESSION. It is therefore possible to have an application declare a temporary table with a name identical to that of a persistent table, in which case the application logic can become overly complicated. Avoid the use of the schema SESSION, except when dealing with declared temporary tables.

User name and group name restrictions (Windows)

The following are the limitations in this environment:

  • User names and group names are limited to 30 characters within the DB2® database system.

  • User names under Windows® are not case sensitive; however, passwords are case sensitive.

  • User names and group names can be a combination of upper- and lowercase characters. However, they are usually converted to uppercase when used within the DB2 database. For example, if you connect to the database and create the table schema1.table1, this table is stored as SCHEMA1.TABLE1 within the database. (If you wish to use lowercase object names, issue commands from the command line processor, enclosing the object names in quotation marks, or use third-party ODBC front-end tools.)

  • A user can not belong to more than 64 groups.

  • DB2 database supports a single namespace. That is, when running in a trusted domains environment, you should not have a user account of the same name that exists in multiple domains, or that exists in the local SAM of the server machine and in another domain.

CURRENT PATH special register

The CURRENT PATH (or CURRENT_PATH) special register specifies a VARCHAR(254) value that identifies the SQL path to be used when resolving function references and data type references in dynamically prepared SQL statements. CURRENT FUNCTION PATH is a synonym for CURRENT PATH. CURRENT PATH is also used to resolve stored procedure references in CALL statements. The initial value is the default value specified below. For static SQL, the FUNCPATH bind option provides an SQL path that is used for function and data type resolution.

The CURRENT PATH special register contains a list of one or more schema names that are enclosed by double quotation marks and separated by commas. For example, an SQL path specifying that the database manager is to look first in the FERMAT schema, then in the XGRAPHIC schema, and finally in the SYSIBM schema, is returned in the CURRENT PATH special register as:

"FERMAT","XGRAPHIC","SYSIBM"					
					

The default value is "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM",X, where X is the value of the USER special register, delimited by double quotation marks. The value can be changed by invoking the SET CURRENT PATH statement. The schema SYSIBM does not need to be specified. If it is not included in the SQL path, it is implicitly assumed to be the first schema. SYSIBM does not take up any of the 254 bytes if it is implicitly assumed.

A data type that is not qualified with a schema name will be implicitly qualified with the first schema in the SQL path that contains a data type with the same unqualified name. There are exceptions to this rule, as outlined in the descriptions of the following statements: CREATE DISTINCT TYPE, CREATE FUNCTION, COMMENT, and DROP.

Professional hosting     Belorussian informational portal         Free SCWCD 1.4 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     Free IBM Certified Associate Developer Study Guide     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     SCDJWS 5.0 Quiz