Db2

Содержание:

Adding MQTs to the mix

Introduction to MQTs

Having clarified the distinct and complementary nature of the three partitioning features, it is worthwhile broadening the discussion to include MQTs (materialized query tables). MQTs are targeted for use in similar situations as the partitioning features — namely data warehouse fact or history tables. So, a full accounting of how to use partitioning features, presented in this article, needs to address any special considerations where MQTs are also involved.

An MQT is a table whose definition is based on the result of a query. Another way of thinking about MQTs is that they are like a view whose result set is stored in a table. MQTs improve response time for complex queries that involve any of the following:

Aggregations or computations based on data from base table(s),
Joins of base tables, or
A commonly accessed subset of data from one or more larger base tables.

As the data for the base table(s) changes, the MQT needs to be updated accordingly. The MQT feature provides various options for doing this to suit various operational needs.

Comparison to partitioning features

The table below augments Table 3 with a row for MQTs.

Table 7. DB2 feature comparison summary, including MQTs

Feature	How the feature organizes data	Benefits
DPF	evenly distributes rows across database partitions	Scalability — add computing resources (that is, database partitions) as the database grows
MDC	groups all rows with the similar values on multiple dimensions in the same physical location in the table, called a block	Query performance — organize data for faster retrieval, especially for queries that involve ranges of multiple predicates
TP	groups all rows in a specified range of a single dimension in the same data partition	Data movement — add and remove large volumes of data by adding and removing entire data partitions
MQT	stores results of a query in a table	Query performance — pre-compute and store (materialize) the result set of queries that involve expensive operations such as complex joins and table scans

Designing MQTs in conjunction with partitioning features

The considerations for designing MQTs in conjunction with partitioning features can be summarized by the following points:

MQTs can be created based on a table that uses any combination of partitioning features. For example, an MQT can be created on one or more tables that uses MDC and TP. The use of partitioning features on the base table does not need to take into account whether MQTs will be built upon this table. However, the design of an MQT can be affected by which partitioning features are used on the base table. For example, if the base table is DPF partitioned, the design of the MQT should consider whether to replicate the MQT across the data partitions.
An MQT can also make use of partitioning features. For example, an MQT can be partitioned using MDC or TP.

MQT 1 — transaction totals per day per account type
MQT 2 — Year-to-Date totals per state

Viewing local or system database directory files

You execute this command to see the list of directories available in the current instance.

Syntax:

db2 list database directory

Example:

db2 list database directory

Output:

 System Database Directory  
 Number of entries in the directory = 6  
 Database 1 entry:  
 Database alias                       = FOUR  
 Database name                        = FOUR  
 Local database directory             = 
 /home/db2inst4/Desktop/dbpath  
 Database release level               = f.00  
 Comment                              =  
 Directory entry type                 = Indirect  
 Catalog database partition number    = 0  
 Alternate server hostname            =  
 Alternate server port number         =  
Database 2 entry: 
Database alias                       = SIX  
Database name                        = SIX  
Local database directory             = /home/db2inst4  
Database release level               = f.00  
Comment                              =  
Directory entry type                 = Indirect  
Catalog database partition number    = 0  
Alternate server hostname            =  
Alternate server port number         =

Key consideration: query performance

Description of this consideration

We now turn to the first key consideration in evaluating and using these features: performance, specifically, performance of typical warehouse business user queries. These queries tend to:

Select rows from a fact table that meet criteria for several dimensions. This implies joins between several dimension tables and the fact table.
Use grouping or aggregate functions, such as COUNT, GROUP BY, ORDER BY.
Return result sets that include many rows, from several thousand to several million rows.
Are generated by users or their BI tools. This means queries are more ad hoc and not subject to the same performance testing and tuning found in transaction processing systems.

While people tend to think of faster performance, it’s better to think of better performance. This encompasses things like:

Peak query execution performance,
Query execution performance stability,
Performance across the wide range of workload characteristics found in data warehouses,
Ease of designing the database to achieve performance goals, and
Cost associated with achieving performance goals.

One emerging consideration for designing for performance is hardware trends. More powerful CPUs coupled with larger storage devices is making cost-effective I/O bandwidth a potential performance bottleneck. In this environment, I/O efficiency is a key design consideration.

This section illustrates how each partitioning feature contributes to the performance of query execution. We’ll cover performance of roll-in and roll-out operations in later sections.

DB2 partitioning features to the rescue

DPF contributes to query performance by putting into play more computing resources than would be possible otherwise. When the DB2 optimizer forms a query access plan for a query, it divides the work among the database partitions which work in parallel. Their individual results are then compiled and returned to the query submitter.

MDC contributes by making it efficient to retrieve data. Data with similar values on multiple dimensions is stored in the same location making I/O operations, a traditional bottleneck in data warehouses, efficient. As well, the MDC feature includes block indexes that contain an entry for each block of data rather than for individual rows. This makes it more efficient to perform index operations. To deliver on its potential performance, MDC tables must be designed with the optimal (or at least an adequate) set of dimensions. MDC will only benefit those queries that include dimension columns. MDCs are completely transparent to queries. Finally, MDCs have a couple of noteworthy benefits for administration:

MDC block indexes means that fewer RID indexes are required. An administration benefit is that storage space for indexes is reduced.
Because new rows are inserted in the part of the table that contains rows with similar values, data remains clustered without the need to run the REORG utility.

TP contributes to query performance via partition elimination. To illustrate, consider our Transactions history table with 36 partitions, one per month. To process a query that SELECTs data from the last 12 months, the optimizer knows to eliminate scanning data for partitions not in the last 12 months. This partition elimination applies to both index scans and table scans. TP will only benefit those queries that include the table partitioning key column.

Conclusions

This article introduced the following DB2 table design features: table partitioning, MDC, DPF, and MQT. These features work as a team to address customer concerns related to query performance, getting new data in and getting old data out. The table below summarizes how these DB2 features address each customer need.

Table 8. How DB2 features address customer needs

Customer need	Benefits
Query performance	Each feature contributes in its own way to performance improvement. Using more features will result in better performance.
Roll-in	In most customer scenarios, MDC provides the most benefit. TP can provide benefits in some less common situations.
Roll-out	For simple, common roll-out scenarios, TP provides the most benefit. MDC is suitable for handling other roll-out situations where TP is not as well suited.

Download a free trial version of DB2 Enterprise 9.
Build your
next development project with IBM trial software, available for download directly from
developerWorks.
The IBM Redbook Up and Running with DB2 UDB ESE: Partitioning for Performance in an e-Business Intelligence World provides more in-depth coverage of DPF, MDC, and MQTs along with many other aspects of optimizing DB2 for business intelligence.
Visit the developerWorks resource page for DB2 for Linux, UNIX, and Windows to read articles and tutorials and connect to other resources to expand your DB2 skills.

История создания

Истоки DB2 проистекают из начала 1970-х годов, когда Эдгар Кодд, работавший на IBM, разработал теорию реляционных баз данных и в июне 1970 года опубликовал модель манипуляции данными. Для воплощения этой модели он разработал язык реляционных баз данных и назвал его Alpha. IBM предпочла передать дальнейшую разработку группе программистов, неподконтрольной Кодду. Нарушив некоторые принципы реляционной модели, они реализовали её как «структурированный английский язык запросов», сокращённо SEQUEL. Поскольку акроним SEQUEL был на тот момент сторонней зарегистрированной торговой маркой, название сократили до SQL — «структурированный язык запросов».

С 1975 по 1982 год прототип DB2 разрабатывался в IBM под названием System Relational, или System R. Язык SQL впервые был реализован именно в IBM System R, но эта система имела исследовательский характер, а коммерческий продукт, включающий SQL, первой выпустила компания Oracle в 1979 году.

СУБД DB2 получила своё название в 1982 году, когда был выпущен первый коммерческий релиз для VM под названием SQL/DS, и затем релиз для MVS под названием DB2. Долгое время наряду с «DB2» употреблялся вариант «Database 2», также являющийся торговой маркой IBM.

Таким образом, исторически СУБД DB2 возникла из продуктов DB2 для MVS (потомком которого является DB2 for z/OS) и родственного ему SQL/DS для VM (потомок — DB2 Server for VSE & VM). В дальнейшем другим коллективом разработчиков в IBM был реализован сервер OS/2 EE Database Manager, впоследствии эволюционировавший в DB2 v2 для OS/2, AIX и затем Windows, а потом в DB2 UDB (его потомок — DB2 for Linux, UNIX and Windows). Ещё одним коллективом была выполнена интеграция архитектуры DB2 со встроенной базой данных AS/400 (потомок — DB2 for i). IBM постепенно движется по пути интеграции всех этих веток.

В середине 2006 года IBM анонсировала «Viper» — кодовое имя DB2. IBM заявила, что новая DB2 была первой реляционной базой данных, которая хранит XML «изначально».

В июне 2009 года IBM анонсировала «Cobra» (кодовое имя для DB2 9.7 для LUW).

В начале 2012 года IBM анонсировала следующую версию DB2, DB2 10.1 (кодовое имя Galileo) для Linux, UNIX и Windows. DB2 10.1 содержит ряд новых возможностей управления данными, включая управление доступом к строкам и столбцам, которое обеспечивает «детальное» управление базой данных и многотемпературное управление данными, которое переводит данные в экономичное хранилище на основе «горячего» или «холодного» хранения (как часто к данным обращаются).

В июне 2013 года IBM выпустила DB2 10.5 (кодовое название «Kepler»).

12 апреля 2016 года IBM анонсировала DB2 LUW 11.1, а в июне 2016 года она была выпущена.

В середине 2017 года IBM произвела ребрендинг своих продуктов DB2 и dashDB и изменила их названия на «Db2».

Instance environment commands

These commands are useful to work with arrangement of instance in the DB2 CLI.

Get instance

This command shows details of the currently running instance.

Syntax:

db2 get instance

Example:

db2 get instance

Output:

The current database manager instance is : db2inst1

Set instance

To start or stop the database manager of an instance on DB2 UDB, the following command is executed for the current instance.

Syntax:

set db2instance=<instance_name>

Example:

set db2instance=db2inst1

db2start

Using this command, you can start an instance. Before this, you need to run “set instance”.

Syntax:

db2start

Example:

db2start

Output:

SQL1063N DB2START processing was successful

db2stop

Using this command you can stop a running instance.

Syntax:

db2stop

Output:

SQL1064N DB2STOP processing was successful.

Understanding the format of an audit record

The text file produced by the extraction process is composed of several
audit records, each separated by a blank line. shows a snippet of
audit records that were extracted using the option.

Listing 3. Snippet of audit records extracted using the FILE option

timestamp=2006-02-06-11.54.52.443000;category=AUDIT;audit event=START;
  event correlator=0;event status=0;
  userid=tedwas;authid=TEDWAS;

timestamp=2006-02-06-11.55.14.664000;category=AUDIT;audit event=CONFIGURE;
  event correlator=0;event status=0;
  userid=tedwas;authid=TEDWAS;

timestamp=2006-02-06-11.55.19.371000;category=AUDIT;audit event=CONFIGURE;
  event correlator=0;event status=0;
  userid=tedwas;authid=TEDWAS;

timestamp=2006-02-06-11.55.30.718000;category=AUDIT;audit event=FLUSH;
  event correlator=0;event status=0;
  userid=tedwas;authid=TEDWAS;

Looking at these records, you can see that the user ‘TEDWAS’ first started
the audit utility, then changed the configuration twice, then flushed the
audit buffer. All of these events happened around the same timeframe, as is
demonstrated by the close proximity of values in the field. You
can also see that these events represent event type because of the
value in the category field.

shows a snippet of the same audit records that were extracted from the
audit log, this time using the option with a field delimiter.
The records were taken from the audit.del file
that was generated in the extraction process.

Listing 4. Snippet of audit records extracted using the DELASC option

;2006-02-06-11.54.52.443000;,;AUDIT;,;START;,0,0,;tedwas;,;TEDWAS;
;2006-02-06-11.55.14.664000;,;AUDIT;,;CONFIGURE;,0,0,;tedwas;,;TEDWAS;
;2006-02-06-11.55.19.371000;,;AUDIT;,;CONFIGURE;,0,0,;tedwas;,;TEDWAS;
;2006-02-06-11.55.30.718000;,;AUDIT;,;FLUSH;,0,0,;tedwas;,;TEDWAS;
;2006-02-06-11.56.04.346000;,;AUDIT;,;EXTRACT;,0,0,;tedwas;,;TEDWAS;

Notice that an additional record appears at the end of Listing 4. This
record was generated when the text file produced for Listing 3 was
generated. This additional record was generated because the audit
facility was configured to log all audit events at the time the
extraction took place.

summarizes the meaning of each of the fields in the audit
record for the event type shown in Listing 3 and Listing 4.

Table 2. A description of the AUDIT event type audit record format

Name	Format	Description
Timestamp	CHAR(26)	Date and time of the audit event.
Category	CHAR(8)	Category of audit event. Possible values are: AUDIT.
Audit event	CHAR(32)	A specific audit event. Possible values include: CONFIGURE, DB2AUD, EXTRACT, FLUSH, PRUNE, START, STOP, and UPDATE_ADMIN_CFG.
Event correlator	INTEGER	Correlation identifier for the operation being audited. Can be used to identify what audit records are associated with a single event.
Event status	INTEGER	Status of audit event, represented by an SQLCODE where: Successful event >= 0 Failed event < 0
User ID	VARCHAR(1024)	User ID at time of audit event.
Authorization ID	VARCHAR(128)	Authorization ID at time of audit event.

Consult the
DB2 UDB documentation for a description of the audit record layouts for
the other event types.

Key consideration: getting new data in

Description of this consideration

Getting new data from operational systems into a warehouse fact table is a process known by terms like ETL, ingest, populating the warehouse, and roll-in. The examples below demonstrate the range of situations that can be encountered.

Example 1 — Daily ingest using Load

After the end of the business day, half a million to 2 million records arrive from operational systems in multiple flat files.
A customer script uses the DB2 Load utility to load each file into the fact table. This is done while the table is off-line during a nightly batch window.
At the beginning of the next business day, the previous day’s data is visible to users querying the table.

Example 2 — Near real-time ingest using insert

Every 30 minutes, a file arrives with 10-100 thousand records.
Upon arrival, a customer-written program adds the records to a staging table then uses insert statements to add the records to the fact table.

Example 3 — MQT refresh

When MQTs are present, they are treated as part of the overall process of adding new data to the warehouse. Of particular interest here, are the available MQT refresh policies. Generally, ETL processes manually specify when to update MQT rather than allow this to happen automatically.

For Example 1, MQTs would likely be updated once all the nightly updates to the fact table are complete.
For Example 2, MQTs would also commonly be updated once a day. This way, queries accessing the MQT will return the same result throughout the day even though the underlying data is being updated periodically.

DB2 partitioning features to the rescue

DB2 partitioning features help with roll-in but sometimes introduce additional considerations that customers need to accommodate in their roll-in process.

DPF enables data to be added more quickly because each database partition can process the work in parallel. One the other hand, DPF introduces the additional consideration of sending the rows to the proper data partition.

MDC improves the roll-in process, compared to a table that does not use MDC. Benefits include:

Less physical I/O: MDC tables have fewer RID indexes so less physical I/O is required to update indexes during roll-in.
Faster inserts: MDC tables reduce page contention and locking which make it conducive to perform inserts using multiple, parallel streams.
Better performance for concurrent business queries: Because MDC tables reduce page contention and locking, this also reduces disruption to the performance of concurrent business queries.

On the other hand, with MDC it is advisable to pre-sort the data according to the MDC dimensions.

In some situations TP can aid roll-in. TP provides the capability to add rows to a
partition then to attach that partition to the table when it’s ready. However, in the examples here, that option does not come into play. Recall our sample table (Transactions history table) has a separate partition for each month, yet we are adding data one or more times per day. In this common situation, an empty partition needs to be added to the table before the start of a month and data is added each day of the month.

Finally, MQTs also add considerations to the roll-in process. In particular, there is a need to decide when to update the MQTs.

Configuring the database manager and the database

Instance configuration (Database manager configuration) is stored in a file named ‘db2system’ and the database related configuration is stored in a file named ‘SQLDBCON’. These files cannot be edited directly. You can edit these files using tools which call API. Using the command line processor, you can use these commands.

Database Manager Configuration Parameters

Syntax:

db2 get database manager configuration

db2 get dbm cfg

Syntax:

db2 update database manager configuration

db2 update dbm cfg

Syntax:

db2 reset database manager configuration

db2 reset dbm cfg

Database Configuration Parameters

Syntax:

db2 get database configuration

db2 get db cfg

Syntax:

db2 update database configuration

db2 update db cfg

Syntax: [To reset the previously configured values in database configuration

db2 reset database configuration

db2 reset db cfg

Syntax:

db2 "call get_dbsize_info(?,?,?,-1)"

Example:

db2 "call get_dbsize_info(?,?,?,-1)"

Output:

Value of output parameters   
--------------------------   
Parameter Name  : SNAPSHOTTIMESTAMP   
Parameter Value : 2014-07-02-10.27.15.556775  
Parameter Name  : DATABASESIZE   
Parameter Value : 105795584  
Parameter Name  : DATABASECAPACITY   
Parameter Value : 396784705536  
Return Status = 0

Introduction

An Instance is a logical environment for DB2 Database Manager. Using instance, you can manage databases. Depending on our requirements, you can create multiple instances on one physical machine. The contents of Instance directory are:

Database Manager Configuration file
System Database Directory
Node Directory
Node Configuration File
Debugging files, dump files

For DB2 Database Server, the default instance is “DB2”. It is not possible to change the location of Instance directory after its creation. An instance can manage multiple databases. In an instance, each database has a unique name, its own set of catalog tables, configurations files, authorities and privileges.

Loading the extracted audit records into DB2 tables

Once you have extracted the audit records into delimited ASCII files, you
can load the contents of the ASCII files into DB2 tables. You can then
issue SQL statements against the
tables and perform advanced analysis of the data. You must first create
the tables that hold the audit data. It is a best practice to create these
tables in a separate schema in order to isolate the data from unauthorized
users and for organizational purposes. Consult the
DB2 UDB documentation for the actual statements you
should use.

Once you have created all the necessary tables, you can load them from the
delimited ASCII files that were created during the extraction process. Use
the utility to load the data into the tables. For example, to load
the AUDIT table using the corresponding audit.del
file that was created in the previous extraction process, issue the
following command:

LOAD FROM audit.del OF del MODIFIED BY CHARDEL! INSERT INTO MYSCHEMA.AUDIT

where is the schema that your
audit table resides in and is the name of the table. Note the
use of the clause.
Since the extracted delimited ASCII file was created
using a field delimiter character other than the default expected by the
utility (a comma), the utility was configured to accept a
different delimiter.

It is a good practice to delete any pruned rows from the table that you
loaded in order to ensure that you do not load duplicate rows into the
table at a later time. The extracted audit files potentially
contain records that were written after the hour that the audit log was
pruned, so these records should be removed from the tables in order not to
affect your analysis. The following command deletes rows from the AUDIT
table that have a timestamp greater than a specified timestamp used to
prune the audit log file:

DELETE FROM MYSCHEMA.audit WHERE TIMESTAMP > TIMESTAMP('YYYYMMDDHH0000')

where is the schema that your
audit table resides in, is the name of the table,
and is the value you
specified when you pruned the audit log.

You can load the tables for the other event types in a similar way. For
more information, consult the
DB2 documentation for the LOAD commands you can use.

Причины ошибок в файле Db2odbc.dll

Большинство ошибок db2odbc.dll связано с отсутствием или повреждениями файлов db2odbc.dll. Тот факт, что db2odbc.dll – внешний файл, открывает возможность для всяческих нежелательных последствий.

Непредвиденные завершения работы вашего компьютера или заражение вирусом может повредить db2odbc.dll, что приведет к ошибкам dll. Когда файл db2odbc.dll повреждается, он не может быть нормально загружен и выведет сообщение об ошибке.

В других случаях ошибки файла db2odbc.dll могут быть связаны с проблемами в реестре Windows. Нерабочие ссылки DLL файлов могут помешать зарегистрировать файл dll должным образом и вызвать ошибку db2odbc.dll. Эти нерабочие ключи реестра могут появиться в результате отсутствия файла DLL, перемещения файла DLL или ввиду наличия остаточной ссылки DLL файла в реестре Windows после неудачной установки или удаления программы.

Более конкретно, данные ошибки db2odbc.dll могут быть вызваны следующими причинами:

Ошибочные или поврежденные записи реестра для db2odbc.dll
Вирус или вредоносное ПО, повредившее файл db2odbc.dll.
Отказ оборудования IBM Inc., например, некачественный жесткий диск, вызвавший повреждение файла db2odbc.dll.
Другая программа изменила требуемую версию db2odbc.dll.
Другая программа злонамеренно или по ошибке удалила файл db2odbc.dll.
Другая программа удалила файл db2odbc.dll.