DB2 Tips of the Month - 2006
| January | Rename a Server that is Running DB2 |
| February | Using PHP and DB2 |
| March | Drop One Specific Stored Procedure |
| April | Data Marts versus Data Warehouses |
| May | Joining Tables Together with XML Functions |
| June | Conserving Disk Space on a Mainframe |
| July | Using SQL UPDATE |
| August | Tuning Strategies and Data Availability |
| September | Using INSTEAD OF Triggers |
| October | Restartable RECOVER Command |
| November | Reducing Memory Constraints |
| December | Use DB2 Triggers to Maintain an Audit Log |
January's Tip of the Month
Rename a Server that is Running DB2
From IBM's Frequently Asked Questions
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30601
To rename a Windows server that is running DB2, use the following procedure:
Step 1
D:\SQLLIB\BIN>db2 list admin node directory show detail
Node Directory.
Number of entries in the directory = 1
Node 1 entry:
Node name = CASPER
Comment = Local workstation
Protocol = LOCAL
Instance name = DB2DAS00
Remote instance name =
System = CASPER
Operating system type = NT
Write down the details of your local administration node.
Step 2
D:\SQLLIB\BIN>db2admin stop
SQL4407W The DB2 Administration Server was stopped successfully.
SQLSTATE=00000
Step 3
D:\SQLLIB\BIN>db2 uncatalog node CASPER
DB20000I The UNCATALOG NODE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is refreshed.
Step 4
D:\SQLLIB\BIN>db2 catalog admin local node CASPER instance
DB2DAS00 system BATMAN ostype NT with "New BATMAN"
DB20000I The CATALOG ADMIN LOCAL NODE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is refreshed.
New BATMAN is just a comment string. Note also that in CLP you may want to replace " with '.
Step 5
D:\SQLLIB\BIN>db2 list admin node directory show detail
Node Directory
Number of entries in the directory = 1
Node 1 entry:
Node name = CASPER
Comment = New BATMAN
Protocol = LOCAL
Instance name = DB2DAS00
Remote instance name =
System = BATMAN
Operating system type = NT
Here we set the new system name in the registry to match the cataloged local administration
node.
Step 6
D:\SQLLIB\BIN>db2 terminate
Step 7
D:\SQLLIB\BIN>db2set -g DB2SYSTEM=BATMAN
Step 8
D:\SQLLIB\BIN>db2admin start
SQL4406W The DB2 Administration Server was started successfully.
SQLSTATE=00000
Now you can start the control center and see your new system name.
February's Tip of the Month
Using PHP and DB2
By Justin Whitney, excerpt from DevX
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30602
To rename a Windows server that is running DB2, use the following procedure:
So you're developing a PHP app using DB2. You've downloaded the version of the ibm_db2 PECL
extension appropriate for your version of PHP. Now what?
Saying Hello to the World yet again hardly scratches the surface of what you're capable
of. Using the ibm_db2 PECL extension, you have the ability to do many great and powerful things,
some of which may surprise you.
I've collected here the various PHP methods available to you, grouped by general purpose and
functionality. More detailed explanations for each of these functions may be found in the PHP
documentation or the online Zend manual.
(If you want an introduction to this whole topic, read "Develop IBM Cloudscape and DB2 Universal Database applications
with PHP" by Dan Scott. You can also visit the IBM developerWorks PHP Zone
at http://www.ibm.com/developerworks/opensource/top-projects/php.html
.)
Essential Functions
First and foremost, the ibm_db2 PECL extension gives you a set of general database management
functions that allow you to connect to your database, execute SQL, and troubleshoot any problems
you run into along the way. In this section, I've included those functions you'll use pretty
much no matter what you're trying to accomplish.
To illustrate use of these functions, here is some basic code for connecting to the database
"ANGELINA" and pulling a list of movies. DB2-related functions are in boldface.
<?php
$database = 'angelina';
$user = 'db2admin';
$password = 'db2admin';
$conn = db2_connect($database, $user, $password);
if ($conn) {
echo "Connection succeeded.<br />";
$sql = "SELECT moviename FROM movies ORDER BY releasedate";
$stmt = db2_prepare($conn, $sql);
db2_execute($stmt);
$movielist = array();
$i=0;
while (db2_fetch_row($stmt)) {
$movielist[$i] = db2_result($stmt, 0);
echo "$movielist[$i]<br />";
$i += 1;
}
echo count($movielist) . " movies listed.<br /><br />";
db2_close($conn);
}
else {
echo "Connection failed.<br />";
echo db2_conn_errormsg();
}
?>
Result:
Connection succeeded.
Lookin' to Get Out
Cyborg 2
Hackers
Playing God
Gia
The Bone Collector
Girl, Interrupted
Lara Croft: Tomb Raider
Mr. & Mrs. Smith
9 movies listed.
db2_connect ( string database, string username, string password [, array options] )
Creates a new database connection. The command operates in slightly different ways depending on
whether the database is currently cataloged or uncataloged when the connection is attempted. This
shows the versatility of the PECL functions in communicating with a database that hasn't
already been cataloged on the server prior to executing the script.
If the database is currently cataloged, you can connect with it by entering its name, username,
and password. If it's not cataloged, then username and password are both null while the name
parameter becomes a much longer list of variables, including database name, hostname, port,
username, and password, in this format:
DRIVER={IBM DB2 ODBC DRIVER};DATABASE=database;HOSTNAME=hostname;
PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;
Optionally, you can manually disable AUTOCOMMIT by specifying it in the db2_connect
parameter list, thusly:
$database = 'angelina';
$user = 'usr';
$password = 'pwd';
$options = array('autocommit' => DB2_AUTOCOMMIT_OFF);
$conn = db2_connect($database, $user, $password, $options);
db2_pconnect ( string database, string username, string password [, array options] )
Returns a persistent database connection. Similar to db2_connect, this operates in essentially
the same way except that a successful db2_close command doesn't actually close the connection,
but rather leaves it open to service the next db2_pconnect request.
As advised by the documentation, you probably shouldn't use this with AUTOCOMMIT turned off,
because the db2_commit and db2_rollback commands affects every persistent connection currently
using that same underlying DB2 connection.
db2_exec ( resource connection, string statement [, array options] )
Prepares and executes a SQL statement in a single step. Though it works fine, this function is
not as efficient as using the db2_prepare / db2_execute combo described below, particularly
when issuing the same SQL statement with different parameters. Also, it introduces some
security risks if you plan on incorporating PHP variables into the SQL statement. If you
do use db2_exec, however, here's what it looks like:
$sql = "SELECT moviename FROM movies WHERE costar='Jon Voight'";
$stmt = db2_exec($conn, $sql);
while (db2_fetch_row($stmt)) {
echo db2_result($stmt, 0) . "<br />";
}
Result:
Lookin' to Get Out
Lara Croft: Tomb Raider
As you can see, the other key difference between db2_exec and db2_execute is that db2_exec
returns a resource statement, whereas db2_execute a boolean, the resource statement having
already been returned by db2_prepare (below).
db2_prepare ( resource connection, string statement [, array options] )
Prepares a SQL statement for execution. Parameter markers (? characters) can be included to
represent input, output, or input/output parameters. These parameters can then be passed to
the prepared statement using either an array passed to db2_execute or by using the
db2_bind_param statement. Here's an example of an INSERT statement that uses an array of
parameters:
$movie = array('Alexander', 2004, 'Colin Farrell', 6);
$sql = 'INSERT INTO movies (moviename, releasedate,
costar, rating) VALUES (?, ?, ?, ?)';
$stmt = db2_prepare($conn, $sql);
if ($stmt) {
$result = db2_execute($stmt, $movie);
if ($result) {
print "Alexander added to the list of films.";
}
}
Result:
Alexander added to the list of films.
db2_execute ( resource statement [, array parameters] )
Executes a statement prepared by db2_prepare. See the examples above for how it works. It
returns a TRUE or FALSE indicating success but operates on the existing resource statement
returned by db2_prepare, which can then be managed using some of the fetch functions.
Optionally, you can add an array of parameters.
db2_close ( resource connection )
Closes a non-persistent database connection and frees resources back to the database server.
If trying to close a persistent database connection, opened using db2_pconnect, then db2_close
is ignored and the connection remains available. It returns TRUE or FALSE indicating success.
For more information on using PHP and DB2, check out the rest of Justin's article on DevX.
March's Tip of the Month
Drop One Specific Stored Procedure
From IBM's Frequently Asked Questions
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30603
How can you drop one specific stored procedure when I have several overloaded procedures with
the same name?
Each procedure must have a unique specificname in the DB2 system catalog. This specificname can
be used to delete the one procedure without affecting others with the same procname/routinename.
Use the following query to find the specificname for a procedure (be sure to modify the where
clause with the correct name). There will be a break in specificname that will help identify the
procedure with the parameters matching the procedure to be dropped.
db2 select substr(specificname,1,10) as "SPECIFICNAME", \
substr(routinename,1,10) as "ROUTINE", substr(parmname,1,20)as "PARMNAME", \
CASE rowtype \
WHEN 'B' THEN 'Input/Output' \
WHEN 'C' THEN 'Result after casting' \
WHEN 'O' THEN 'Output' \
WHEN 'P' THEN 'Input' \
WHEN 'R' THEN 'Result before casting' \
END \
as "PARMTYPE" , \
substr(typename,1,10) \
from syscat.routineparms where routinename = 'MY_PROCEDURE' \
order by specificname, routinename
Once the correct specificname is determined, use this ddl:
db2 drop specific procedure.
April's Tip of the Month
Data Marts versus Data Warehouses
From IBM's Frequently Asked Questions
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30604
Some common mistakes and misconceptions are:
A data mart is the same as a data warehouse.
Not true. The scope of a data mart is a given decision support application, whereas the data
warehouse consolidates all data for corporate-wide analysis.
A data mart is just a small data warehouse.,br>
Not true. Although a data mart is usually smaller, the difference in size is not a dominant
differentiation aspect.
A collection of data marts is a data warehouse.
Not true. Corporate-wide consolidation of informational data in a "unified source" is what is
usually missing across independently developed data marts.
A data warehouse is a Decision Support System (DSS).
Not true. A DSS comprises the data warehouse as well as the application environment to query,
manipulate, analyze, and visualize data and information.
Note: As a rule of thumb, a data mart is used with a single entity and a data warehouse
is used corporate-wide.
May's Tip of the Month
Joining Tables Together with XML Functions
By George Baklarz for Knowledge Xpert for
DB2
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30605
This topic, written by George Baklarz, is an extract of material included in
the upcoming release of Knowledge Xpert for
DB2. Knowledge Xpert for DB2 v2.1 will include additional material on the XML Publishing
Functions, as well as new material on Object Management, SQL PL, SQL Tuning and the DB2
System Catalog.
There are a number of techniques that can be used to join tables together within the XML
functions. The easiest way to accomplish this is with the WHERE clause using normal column
comparisons. For instance, the following tables represent DOCTORS working at a clinic, and
the corresponding PATIENTS that are cared for by these physicians.
CREATE TABLE DOCTORS
(
DOC_ID INT NOT NULL,
DOC_FIRSTNAME VARCHAR(20) NOT NULL,
DOC_LASTNAME VARCHAR(20) NOT NULL,
DOC_EXTENSION INT NOT NULL
);
INSERT INTO DOCTORS VALUES
(1,'GEORGE','MARTIN',1234),
(2,'FRED','RUBBLE',2345),
(3,'BARNEY','FLINTSTONE',3456),
(4,'WILMA','ROCKAFELLER',9876);
CREATE TABLE PATIENTS
(
PAT_ID INT NOT NULL,
DOC_ID INT NOT NULL,
PAT_FIRSTNAME VARCHAR(20) NOT NULL,
PAT_LASTNAME VARCHAR(20) NOT NULL,
PAT_ROOM INT NOT NULL
);
INSERT INTO PATIENTS VALUES
(1, 1, 'Betty','White',100),
(2, 2, 'John', 'Wayne', 200),
(3, 3, 'Brad', 'Homes', 202),
(4, 4, 'Arthur','Lismer',300),
(5, 2, 'Lauren','Harris',204),
(6, 3, 'Tom','Thompson',205),
(7, 4, 'Emily','Carr',306),
(8, 1, 'Frank','Carmichael',402);
Creating XML reports on either the DOCTORS table or the PATIENTS table is relatively
straightforward. What about creating a report that shows all doctors with their corresponding
patients?
From an SQL perspective this is very straightforward:
SELECT D.DOC_ID, DOC_LASTNAME, PAT_ID, PAT_LASTNAME
FROM DOCTORS D, PATIENTS P
WHERE D.DOC_ID = P.DOC_ID
ORDER BY D.DOC_ID
DOC_ID DOC_LASTNAME PAT_ID PAT_LASTNAME
------ -------------- --------- ------------
1 Martin 1 White
1 Martin 8 Carmichael
2 Flintstone 2 Wayne
2 Flintstone 5 Harris
3 Rubble 3 Homes
3 Rubble 6 Thompson
4 Rockafeller 4 Lismer
4 Rockafeller 7 Carr
From an XML perspective, the rows need to be converted into a structure that resembles
the following structure (one record per doctor):
<doctorpatients doc_id=xxx>
<patientlist>
…. Patient information
</patientlist>
</doctor>
In order to build this structure, the problem needs to be broken down into a number of
elements. The highest level structure (or root) of this tree must be unique across all of the
doctors and patients (this is a requirement for the XMLAGG function).
The highest level element will be called <doctorpatients> to represent the relationship between
all of the doctors and their patients. Creating an element is done using
the XMLELEMENT function:
XMLELEMENT(NAME "doctorpatients",…)
In order to differentiate which doctor this list of patients is cared for, an attribute
needs to be added to the element:
XMLATTRIBUTES(D.doc_id AS "doc_id")
When an attribute is used with an XMLAGG function, it can only be a value from the grouping
columns. If any other attribute is listed, the function will not execute. If the
<doctorpatients> structure needs to include more that the doc_id value, the additional
columns need to be placed in the GROUP BY clause. For instance, if the doctor's last name was
supposed to be part of the record, the GROUP BY will need to changed to:
GROUP BY D.DOC_ID, D.DOC_LASTNAME
The second element would be a <patient> record which represents information about each
of the patients. The information can be easily formatted using the XMLFOREST tag, or just
creating a list of XMLELEMENT tags:
XMLELEMENT(NAME "Patient",
XMLELEMENT(NAME "ID", p.pat_id),
XMLELEMENT(NAME "Name",
p.pat_firstname || ' ' || p.pat_lastname),
XMLELEMENT(NAME "Room", p.pat_room))
This is the level where the grouping must be done, since the report must tie doctors
together with their patients. In order to create the grouping within the records, the XMLAGG
function must be used:
XMLAGG
(
XMLELEMENT(NAME "Patient",
XMLELEMENT(NAME "ID", p.pat_id),
XMLELEMENT(NAME "Name",
p.pat_firstname || ' ' || p.pat_lastname),
XMLELEMENT(NAME "Room", p.pat_room))
ORDER BY p.pat_id
)
The XMLAGG function only allows one element to be aggregated, so the patient records need
to be grouped under the <doctorpatient> element:
XMLELEMENT(NAME "doctorpatients",
XMLATTRIBUTES(D.doc_id AS "doc_id",
D.doc_lastname AS "lastname"),
XMLAGG(…)
In addition to the XML functions, an SQL statement needs to be created that returns the
rows that will be processed. The SQL statement will require a join across the two tables and
a suitable grouping function:
SELECT D.DOC_NO, XML2CLOB(…)
FROM PATIENT P, DOCTOR D
WHERE
D.DOC_NO = P.DOC_NO
GROUP BY
D.DOC_NO, D.DOC_LASTNAME
The GROUP BY clause includes the Doctor's lastname since that is one of attributes that
needs to be placed in the <doctorpatient> element. The join is required to link the doctor
and patient table together, and the XML function must be serialized using the XML2CLOB function
(or XMLSERIALIZE) in order to retrieve it. The final SQL is found below.
SELECT
d.doc_id,
XML2CLOB(
XMLELEMENT(NAME "DoctorPatient",
XMLATTRIBUTES(d.doc_id as "DOCID",
d.doc_lastname as "name"),
XMLAGG(
XMLELEMENT(NAME "Patient",
XMLELEMENT(NAME "ID", p.pat_id),
XMLELEMENT(NAME "Name",
p.pat_firstname || ' ' || p.pat_lastname),
XMLELEMENT(NAME "Room", p.pat_room)
)
ORDER BY p.pat_id
)
)
)
FROM
PATIENTS P, DOCTORS D
WHERE
D.DOC_ID = P.DOC_ID
GROUP BY
D.DOC_ID,D.DOC_LASTNAME
One of the rows returned by the SQL is shown below:
<DoctorPatient DOCID="1" name="Martin">
<Patient>
<ID>1</ID>
<Name>Betty White</Name>
<Room>100</Room>
</Patient>
<Patient>
<ID>8</ID>
<Name>Frank Carmichael</Name>
<Room>402</Room>
</Patient>
</DoctorPatient>
If this type of formatting is required for many applications, it may be more convenient
to create a VIEW with this information inside it. For instance, the previous SQL could be placed
within a VIEW:
CREATE VIEW doctorpatients AS (SELECT …)
The only modification that would be necessary would be to name the result of the XML
function:
XML2CLOB(…) as Patients
If a name wasn't given to a particular column, the view definition would fail.
When the view has been created, a query could be run against this view retrieving information
about an individual doctor and their patients:
SELECT FROM DOCTORPATIENTS WHERE DOC_ID=1
June's Tip of the Month
Conserving Disk Space on a Mainframe
By Robert Catterall, reprinted from DB2 Magazine
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30606
People often minimize the size of an "empty" partition of a tablespace by specifying a small
value for the PRIQTY associated with the partition (this sets the minimum amount of space that will
be allocated for a mainframe DB2 data set). If the partition has been used and purged of data, the
associated PRIQTY value can be adjusted downward via the SQL statement ALTER TABLESPACE. A
subsequent partition REORG will put the changed PRIQTY into effect. Alternatively, if the purge
operation hasn't yet been executed, you can adjust the PRIQTY via ALTER TABLESPACE, accomplishing
the purge and changing the PRIQTY value via a LOAD REPLACE utility operation run with a "dummy"
input data set.
DB2 for z/OS v.8 delivered some important data partitioning enhancements, including the ability
to dynamically add partitions to a tablespace, eliminating the need to predefine partitions for
future use. (Partitions that are defined but not yet in use are often made small through a small
PRIQTY specification on CREATE TABLESPACE.) Another enhancement makes it possible to cycle through
a series of tablespace partitions, so old and purged partitions are reused for new rows
corresponding to a newly used partitioning key range. So, with DB2 UDB for z/OS v.8, you can
avoid adjusting PRIQTY values for partitions to save space. Simply add a new partition when it's
needed (and not before); and, when an old partition is emptied, reuse it instead of leaving it
empty.
July's Tip of the Month
Using SQL UPDATE
Reprinted from DevX.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30607
How do you code a statement to update a column in TargetTable with the value of a column
in SourceTable based on the SourceTable column having a different value?
Example:
SourceTable
------------------------
Ky FName
--------- ----------
1 Fred
2 Wilma
3 Pebbles
TargetTable
-------------------------
Ky FName
--------- ----------
1 Fred
2 Wilma
3 Dino
In the above example, we would like to compare SourceTable with TargetTable based on equal Ky's
and update TargetTable.FName column with SourceTable.Fname value when those values are different.
TargetTable should look like this after the update:
Ky FName
--------- ----------
1 Fred
2 Wilma
3 Pebbles
Here is the code to accomplish this:
UPDATE TargetTable
INNER JOIN SourceTable ON TargetTable.PersonID = SourceTable.PersonID
SET TargetTable.LastName =
[SourceTable].[LastName]
WHERE (((TargetTable.LastName)<>[SourceTable].[LastName]))
August's Tip of the Month
Tuning Strategies and Data Availability
Excerpt From Knowledge Xpert for
DB2
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30608
It is generally known that some performance tactics may impair data recoverability or security. However, it may not be as obvious how tuning may adversely affect data availability. Certain processes (like Reorg) may require exclusive use of data during certain phases. Even Reorg with ShrLevel Change (so-called Online Reorg) requires a successful drain on the table in order to finish successfully. (Note that some third-party products may implement Reorg in a way that allows bypassing this.) A long-running query, or an application that infrequently commits may prevent such processes from completing, thus affecting recoverability.
Thus, sometimes data availability must be sacrificed for recoverability (if only for a short period), and that window of opportunity may be shortened or prolonged by implementing certain tuning tactics.
Object changes
This category includes: table partitioning and re-partitioning; adding, merging, and changing indexes; and physical placement on I/O devices. Most object changes can affect data availability by virtue of the changes in processes that accompany them. Table partitioning is usually accompanied by changes in image copy and reorg strategy. For example, partitioning a table makes partition reorgs a possibility. However, reorging a table partition may make a non-partitioning index (in DB2 V7) unavailable during the reorg. This issues is resolved somewhat in DB2 V8 which permits table-based partitioning.
Other object changes such as adding new indexes may make possible new access paths, but may also elongate recovery when an error is detected (since there is now one additional object to recover).
Application changes
This includes bind and locking strategies as well as SQL changes. Static SQL changes necessitate a rebind of the plan or package containing the SQL statement(s). This requires that the objects mentioned by the plan or package (including the plan or package itself) must be unlocked prior to the bind. In other words, in a high-availability environment it may be that many (or all) these plans and packages are continuously in-use, making the bind fail with a resource unavailable message.
Process changes
These are the processes that are part of the DBMS support infrastructure, including utilities such as image copy, reorg, and runstats. It also includes off-line disk backups, disk-to-tape operations (such as HSM), SMS management of disk files, data recovery and disaster recovery. Changes in these processes tend to affect the system as a whole rather than specific applications, although there are exceptions.
For example, one common method of providing for volume separation for datasets (typically to separate tables from their indexes) is to implement SMS allocation of DB2 datasets. One would define ACS rules that specify what disk volumes (SMS storage groups) would be used for dataset allocation. In this way, table datasets could be allocated on one set of disk volumes and indexes on another. Since I/Os in z/OS are queued by (logical) volume ID, separating datasets in this way would prevent a table (partition) and index (partition) from being allocated on the same volume.
System changes
This area includes DB2 configuration parameter (ZParm) value settings, as well as those on the IRLM and stored procedure address space(s) startup JCL, those stored in the DSNHDECP module, and others. Even though some of the ZParms can be changed dynamically, more commonly they are only changed during times when DB2 can be brought down and back up. Other parameter changes require address space shutdown and startup, which would therefore require a DB2 shutdown.
The most common system changes are modifications to the buffer pool sizes and thresholds. This has the effect of changing how many objects can be maintained in memory (especially including table and index pages). Greater residency in memory can lead to faster application response time.
Data Availability
All of these types of changes affect data availability. As a consequence, the performance tuner must always keep in mind the context of the changes, the performance tuning strategy, and the advantages and disadvantages that tuning options offer.
September's Tip of the Month
Using INSTEAD OF Triggers
By Alexander Kuznetsov, Reprinted from WhatIs.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30609
First introduced in DB2 UDB V8.1, INSTEAD OF triggers are very useful when you need to modify
the database structure without changing the applications running against the changed database.
In other words, you may use INSTEAD OF triggers and views to isolate applications from changes
in database structure. This may be the only option if you are dealing with a third-party
application that cannot be modified. Also you might want to avoid the time-consuming and tedious
task of modifying several applications in many places. Let us discuss an example.
Let's suppose that currently there is a table:
CREATE TABLE CONTACT_CURRENT(
CONTACT_ID INTEGER NOT NULL PRIMARY KEY,
NAME VARCHAR(50))
Because the situation has changed, now there is a need to keep all the previous versions of a
record every time the record is modified. Here is the new database structure:
CREATE TABLE CONTACT(
CONTACT_ID INTEGER NOT NULL PRIMARY KEY,
CURRENT_VERSION SMALLINT NOT NULL DEFAULT 0 )
CREATE TABLE CONTACT_DATA(
CONTACT_ID INTEGER NOT NULL,
VERSION_NUMBER SMALLINT NOT NULL,
NAME VARCHAR(50),
TIME_CHANGED TIMESTAMP DEFAULT CURRENT TIMESTAMP,
PRIMARY KEY(CONTACT_ID, VERSION_NUMBER),
FOREIGN KEY(CONTACT_ID) REFERENCES CONTACT(CONTACT_ID) )
Our goal is to have all the existing (and unchanged) applications working against the new
database structure exactly as they used to work against the old database structure. The first
obvious step is to create a view mimicking the old table's structure:
CREATE VIEW CONTACT_CURRENT
AS
SELECT
CONTACT.CONTACT_ID,
CONTACT_DATA.NAME
FROM
CONTACT JOIN CONTACT_DATA
ON CONTACT.CONTACT_ID = CONTACT_DATA.CONTACT_ID
AND CONTACT.CURRENT_VERSION = CONTACT_DATA.VERSION_NUMBER
This view is good enough for SELECT statements, it substitutes for the old table seamlessly.
Unfortunately, the view is not insertable, not updateable and not deleteable. Here is where
INSTEAD OF triggers come very handy.
Note: I'm consistently using @ as terminating character in this tip.
This is how we make the view insertable:
CREATE TRIGGER CONTACT_CURRENT_I INSTEAD OF INSERT
ON CONTACT_CURRENT
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO CONTACT(CONTACT_ID, CURRENT_VERSION)VALUES(N.CONTACT_ID, 0);
INSERT INTO CONTACT_DATA(CONTACT_ID, VERSION_NUMBER, NAME)
VALUES(N.CONTACT_ID, 0, N.NAME);
END @
Now, as the view is insertable, the statement below works:
INSERT INTO CONTACT_CURRENT VALUES(1, 'PETER CARSON')
CREATE TRIGGER CONTACT_CURRENT_U INSTEAD OF UPDATE
ON CONTACT_CURRENT
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE CONTACT SET CURRENT_VERSION = CURRENT_VERSION + 1
WHERE CONTACT_ID = N.CONTACT_ID;
INSERT INTO CONTACT_DATA(CONTACT_ID, VERSION_NUMBER, NAME)
SELECT CONTACT.CONTACT_ID, CONTACT.CURRENT_VERSION, N.NAME FROM CONTACT
WHERE CONTACT.CONTACT_ID = N.CONTACT_ID;
END @
The update statement below works exactly as it would work against the old structure:
UPDATE CONTACT_CURRENT SET NAME='PETER CARSON JR.' WHERE CONTACT_ID=1
SELECT * FROM CONTACT_CURRENT
CONTACT_ID NAME
----------- --------------------------------------------------
1 PETER CARSON JR.
The old version of the record is kept along with the new one:
SELECT CONTACT_ID, VERSION_NUMBER, NAME FROM CONTACT_DATA
CONTACT_ID VERSION_NUMBER NAME
----------- -------------- --------------------------------------------------
1 0 PETER CARSON
1 1 PETER CARSON JR.
Similarly, the view can be made deletable:
CREATE TRIGGER CONTACT_CURRENT_D INSTEAD OF DELETE
ON CONTACT_CURRENT
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DELETE FROM CONTACT_DATA WHERE CONTACT_ID = O.CONTACT_ID ;
DELETE FROM CONTACT WHERE CONTACT_ID = O.CONTACT_ID ;
END@
DELETE FROM CONTACT_CURRENT WHERE CONTACT_ID=1
As we have seen, there is no need to modify existing applications running against DB2 V8.1 or
later. All the applications work against the new view exactly as they used to work against the
old table. We have saved ourselves a lot of mundane and error-prone work. Also we have made all
the changes only in one place, which is very efficient.
October's Tip of the Month
Restartable RECOVER Command
By Chris Eaton, Reprinted from IT Toolbox BLOGs
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30610
In DB2 v8 there was a new command delivered called the RECOVER command. This command combines
the restore and roll forward commands into a single statement so that you can say
RECOVER DATABASE db_name TO point_in_time
Note that you don't have to tell it where to get the backup image from. It will automatically
look in the recovery history file to find out what is the best backup image to use in order to get
to the point in time you specify. Optionally you can give it a different recovery history file on
disk if the database does not yet exist. This can greatly simplify a restore operation and when you
need to do a restore is when you need things to be simple!
Now in DB2 9, the RECOVER command can be restarted if it fails and pick up from where it left
off. For example, assume you ran the recover command to restore the database to Monday at 2pm.
But for some reason this command could not complete (maybe there was a log file missing, or a power
failure happened right in the middle of the roll forward). You can resolve the problem and then
simply reissue the exact same RECOVER command. DB2 will realize that it was in the middle of doing
a recover already and will pick up from the roll forward point that it was interrupted at. In v8
it would have had to start back at the restore phase but now in DB2 9 it can continue to roll
forward to 2pm.
But wait, it gets even better. Let's assume that in the above example I start the RECOVER
command but then I realize that I actually want to recover to 1:30pm rather than the 2pm that
I initially specified. Well good news…with DB2 9 you simply interrupt the RECOVER job (CTRL-C
works just fine) and then reissue the command with the new time to recover to. As long as you
have not yet passed 1:30 in the roll forward, DB2 will automatically realize where it was
interrupted and continue to roll forward and stop at the newly specified time. You can change
the time forward or backward (as long as we have not already passed the time) and DB2 figures
out what it needs to still do.
November's Tip of the Month
Reducing Memory Constraints
Excerpt From Knowledge Xpert for
DB2
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30611
Assume that you are experiencing memory constraints, either overall or at certain peak times. The options presented here can reduce memory usage, but may negatively affect the use of another resource (CPU, disk usage, application throughput, data availability).
Most of the DB2-specific memory issues disappear in DB2 V8. This is because the DBM1 address space is 64-bit, running on 64-bit hardware. There is no longer a virtual storage constraint, that being the limit of 2GB on an address space.
However, DB2 V8 still uses real memory, and there is (by definition) a fixed amount of real memory available on the hardware. DB2 competes with other subsystems for that memory, and if there are more requests for memory than is available, the result is paging (the movement of memory pages to and from disk). This should be avoided if possible, although some paging is not necessarily a bad thing.
Most of the suggestions below apply to possible virtual storage constraints (particularly of the DBM1 address space) in a DB2 z/OS V7 environment.
Decrease Use of Data Compression
Dictionaries used for data compression are loaded into the DBM1 address space upon first use of a table. They may take up to 64 KB of memory. While this is not a large amount, it can add up if you have a lot of tablespaces that are defined with COMPRESS YES. This is especially true if you have installed one or more instances of software packages that require DB2 tables. ERP packages such as PeopleSoft or SAP may contain thousands of tablespaces.
Consider removing data compression (alter the tablespace to COMPRESS NO, then Reorg) from highly volatile tables, from tables with mostly numeric data (which does not compress well), or from tables that are frequently updated.
Reallocate Bufferpools
Bufferpools are by default allocated in the DBM1 address space. Bufferpool tuning is beyond the scope of these recommendations; however, you can review the DB2PM Statistics Detail and Summary reports (or their third-party equivalents) for bufferpool activity. Pools with low activity may (perhaps) be decreased a bit in order to address a serious virtual storage constraint.
Another option (V7 or V8) is to reduce the size of a bufferpool and allocate an additional hiperpool (using the HPSIZE parameter). Hiperpools are implemented using hiperspaces, which are address spaces of their own. Consider this for tables assigned to a bufferpool where access is mostly reference (i.e., little update). Some of the pages in this bufferpool, when stolen, will be written to the hiperpool instead.
With DB2 V8 one can assign bufferpools to dataspaces which, like hiperpools, reside address spaces of their own.
Re-size the EDM Pool
The Environment Descriptor Pool (EDM Pool) is an area of memory used for storing different DB2 control blocks and objects. Apart from simply re-sizing the EDM Pool (by changing the EDMPOOL ZParm), in DB2 V8 certain objects such as DBDs are no longer stored in the EDM Pool. Thus, after migrating to DB2 V8 you can reduce the size of the EDM Pool proper while allocating the other areas separately. For example, with the EDMDBDC parameter, you allocate a separate area of memory for DBDs.
Decrease Sort Pool
The sort pool is an area of memory in the DBM1 address space used for in-memory sorts. These can occur because of SQL requirements (for example ORDER BY or GROUP BY), or for utility processing (for example REBUILD INDEX, or REORG). Its size is controlled by the SRTPOOL ZParm value. Reducing it will increase the probability that sorts will require DASD to complete, thereby increasing elapsed time to complete processes.
Decrease Maximum Number of Open Datasets
The DSMAX ZParm value controls the maximum number of datasets (tablespace and index datasets) that DB2 will allow to be open at one time. As this limit is reached, DB2 schedules some of the datasets to be "pseudo-closed". Each open dataset requires a small control block of memory to be allocated in the DBM1 address space. While these blocks are rather small (1-3 KB), it is possible that the total space used may get rather large in environments where there are a lot of partitioned tablespaces (with separate datasets for each partition and each partitioning index partition). Another possibility is environments having installations of ERP packages such as PeopleSoft or SAP. These installations may contain several thousand tables and indexes.
Generally, decreasing DSMAX will not help a virtual storage constraint to any great extent. Still, it is one aspect.
Reduce Size of the Dynamic SQL cache
In DB2 V7 the dynamic SQL cache is contained in the EDM Pool. Its size is controlled by the ZParm values CACHEDYN and EDMSTMTC (In DB2 V8). Reducing the size of the cache may relieve a virtual storage constraint (in DB2 V7).
December's Tip of the Month
Use DB2 Triggers to Maintain an Audit Log
Reprinted From http://www.raulgonzalez.net
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=30612
Here is an illustration to USE DB2 TRIGGERS to maintain an AUDIT LOG table in DB2.
Lets assume we have a business requirement that:
- All INSERT, UPDATES, and DELETES from the EMPLOYEE table need to be logged.
- A brief description of the reason for the INSERT, UPDATE, AND DELETE must be captured and stored.
- We need to record the history of the SSN, User making the change, Date Timestamp, and Type of change performed.
- This AUDIT LOG table must contain records for the last 12 months.
- AUDIT LOG records older than 12 months will be archived to a flat file.
Prior to DB2 Version 6, this business requirement could only be accomplished by writting an application program.
DB2 Version 6 introduced TRIGGERS which bring application logic to the database by automatically executing a set of SQL statements whenever a specified SQL event occurs.
TRIGGERS offer these primary advantages:
- Faster Application Development.
- Code reusability
- Easier maintenance
Here is a sample DDL for the DB2 Objects to be used:
--
SET CURRENT SQLID = 'SYS304';
--
DROP DATABASE SYS304;
COMMIT;
CREATE DATABASE SYS304;
COMMIT;
CREATE TABLESPACE EMPLOYEE
IN SYS304
USING STOGROUP SYSDEFLT
PRIQTY 1024 SECQTY 1024
ERASE YES
SEGSIZE 4
LOCKSIZE ANY
BUFFERPOOL BP0
CLOSE NO;
CREATE TABLESPACE AUDITLOG
IN SYS304
USING STOGROUP SYSDEFLT
PRIQTY 1024 SECQTY 1024
ERASE YES
SEGSIZE 4
LOCKSIZE ANY
BUFFERPOOL BP0
CLOSE NO;
CREATE TABLE SYS304.EMPLOYEE
(EMPLOYEE_SSN NUMERIC(9) NOT NULL,
F_NAME CHAR(25) NOT NULL,
M_INITIAL CHAR(1) DEFAULT,
L_NAME CHAR(25) NOT NULL,
AUDIT_MSG CHAR(25) NOT NULL
CONSTRAINT MSGCHK
CHECK (AUDIT_MSG <> ' '),
PRIMARY KEY (EMPLOYEE_SSN)
)
IN SYS304.EMPLOYEE;
CREATE TABLE SYS304.EMPLOYEE_AUDIT_LOG
(
EMPLOYEE_SSN NUMERIC(9) NOT NULL,
AUDIT_TIMESTAMP TIMESTAMP NOT NULL WITH DEFAULT,
AUDIT_USER CHAR(8) NOT NULL WITH DEFAULT USER,
AUDIT_CHG_TYPE CHAR(3) NOT NULL,
CONSTRAINT CHECKTYP
CHECK (AUDIT_CHG_TYPE = 'ADD' OR
AUDIT_CHG_TYPE = 'CHG' OR
AUDIT_CHG_TYPE = 'DEL'),
AUDIT_MSG CHAR(25) NOT NULL
CONSTRAINT MSGCHK
CHECK (AUDIT_MSG <> ' '),
PRIMARY KEY (EMPLOYEE_SSN,AUDIT_TIMESTAMP)
)
IN SYS304.AUDITLOG;
CREATE TYPE 2 UNIQUE INDEX
EMPLOYEEX
ON SYS304.EMPLOYEE
(EMPLOYEE_SSN ASC)
CLUSTER
BUFFERPOOL BP0
CLOSE NO
USING STOGROUP SYSDEFLT
PRIQTY 1024 SECQTY 1024
;
CREATE TYPE 2 UNIQUE INDEX
AUDITX
ON SYS304.EMPLOYEE_AUDIT_LOG
(EMPLOYEE_SSN ASC,AUDIT_TIMESTAMP ASC)
CLUSTER
BUFFERPOOL BP0
CLOSE NO
USING STOGROUP SYSDEFLT
PRIQTY 1024 SECQTY 1024
;
In this illustration, we have two tables:
- EMPLOYEE TABLE that contain all pertinent employee information
- EMPLOYEE_AUDIT_LOG that will contain a history of all INSERT, UPDATES, and DELETES performed to the EMPLOYEE table.
- Both tables have a CHECK CONSTRAINT that the AUDIT_MSG field can not be blank.
- The EMPLOYEE_AUDIT_LOG table has a CHECK CONSTRAINT that the AUDIT_CHG_TYPE must be ADD, CHG, or DEL.
We then create three TRIGGERS. Make sure to change the SQL Statement Terminator from Colon ';' to Dollar sign "$" in SPUFI.
Here is the sample TRIGGER DDL.
SET CURRENT SQLID = 'SYS304'
$
CREATE TRIGGER AUDITSSN
AFTER UPDATE OF EMPLOYEE_SSN
ON EMPLOYEE
REFERENCING OLD AS OLD_VAL
NEW AS NEW_VAL
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO EMPLOYEE_AUDIT_LOG
VALUES
(OLD_VAL.EMPLOYEE_SSN,
CURRENT_TIMESTAMP,
CURRENT SQLID,
'DEL',
'SSN CHANGED'
);
INSERT INTO EMPLOYEE_AUDIT_LOG
VALUES
(NEW_VAL.EMPLOYEE_SSN,
CURRENT_TIMESTAMP,
CURRENT SQLID,
'ADD',
'SSN CHANGED'
) ;
END
$
--
CREATE TRIGGER AUDITINS
AFTER INSERT
ON EMPLOYEE
REFERENCING NEW AS NEW_VAL
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO EMPLOYEE_AUDIT_LOG
VALUES
(NEW_VAL.EMPLOYEE_SSN,
CURRENT_TIMESTAMP,
CURRENT SQLID,
'ADD',
NEW_VAL.AUDIT_MSG
);
END
$
--
CREATE TRIGGER AUDITCHG
AFTER UPDATE
ON EMPLOYEE
REFERENCING NEW AS NEW_VAL
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO EMPLOYEE_AUDIT_LOG
VALUES
(NEW_VAL.EMPLOYEE_SSN,
CURRENT_TIMESTAMP,
CURRENT SQLID,
'CHG',
NEW_VAL.AUDIT_MSG
);
END
$
--
CREATE TRIGGER AUDITDEL
AFTER DELETE
ON EMPLOYEE
REFERENCING OLD AS OLD_VAL
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO EMPLOYEE_AUDIT_LOG
VALUES
(OLD_VAL.EMPLOYEE_SSN,
CURRENT_TIMESTAMP,
CURRENT SQLID,
'DEL',
'RECORD DELETED'
);
END
$
Now that we have the DB2 Definitions complete, lets execute a few SQL DML statements to populate the EMPLOYEE table.
Here is some sample DML:
delete from sys304.employee_audit_log
;
------------------------------------------------------------------------
-- INSERTS INTO EMPLOYEE TABLE
------------------------------------------------------------------------
SET CURRENT SQLID = 'SYS304';
--
INSERT INTO SYS304.EMPLOYEE
(EMPLOYEE_SSN,F_NAME,M_INITIAL,L_NAME,AUDIT_MSG)
VALUES
(111111111,'RAUL','','GONZALEZ','ADDED new EMPLOYEE')
;
--
SET CURRENT SQLID = 'SYS018';
--
INSERT INTO SYS304.EMPLOYEE
(EMPLOYEE_SSN,F_NAME,M_INITIAL,L_NAME,AUDIT_MSG)
VALUES
(222222222,'CLAYTON','','AULT','ADDED NEW EMPLOYEE ')
;
--
SET CURRENT SQLID = 'SYS113';
--
INSERT INTO SYS304.EMPLOYEE
(EMPLOYEE_SSN,F_NAME,M_INITIAL,L_NAME,AUDIT_MSG)
VALUES
(333333333,'STACI','','STEPHENSON','ADDED NEW EMPLOYEE ')
;
------------------------------------------------------------------------
-- UPDATES TO EMPLOYEE TABLE
------------------------------------------------------------------------
--
SET CURRENT SQLID = 'SYS304'
;
UPDATE SYS304.EMPLOYEE
SET M_INITIAL = 'L',
AUDIT_MSG = 'CHANGED MIDDLE NAME'
WHERE EMPLOYEE_SSN = 111111111
;
--
SET CURRENT SQLID = 'SYS113'
;
--
UPDATE SYS304.EMPLOYEE
SET EMPLOYEE_SSN = 444444444,
AUDIT_MSG = 'SSN CHANGED'
WHERE EMPLOYEE_SSN = 333333333
;
------------------------------------------------------------------------
-- DELETE ALL ROWS FROM EMPLOYEE TABLE
------------------------------------------------------------------------
SET CURRENT SQLID = 'SYS018'
;
--
delete from sys304.employee
;
------------------------------------------------------------------------
-- SELECT ALL RECORDS FROM EMPLOYEE_AUDIT_LOG TABLE
------------------------------------------------------------------------
SELECT *
FROM SYS304.EMPLOYEE_AUDIT_LOG
;
EMPLOYEE_SSN--AUDIT_TIMESTAMP-------------AUDIT_USER--AUDIT_CHG_TYPE--AUDIT_MSG
--111111111----------2001-09-17-09.49.53.819313--SYS304---------
ADD-------------ADDED NEW EMPLOYEE
--222222222----------2001-09-17-09.49.53.861816--SYS018---------
ADD-------------ADDED NEW EMPLOYEE
--333333333----------2001-09-17-09.49.53.899308--SYS113---------
ADD-------------ADDED NEW EMPLOYEE
--111111111----------2001-09-17-09.49.53.918304--SYS304---------
CHG-------------CHANGED MIDDLE NAME
--333333333----------2001-09-17-09.49.53.947206--SYS113---------
DEL-------------SSN CHANGED
--444444444----------2001-09-17-09.49.53.947467--SYS113---------
ADD-------------SSN CHANGED
--444444444----------2001-09-17-09.49.53.947775--SYS113---------
CHG-------------SSN CHANGED
--111111111----------2001-09-17-09.49.53.965479--SYS304---------
DEL-------------RECORD DELETED
--222222222----------2001-09-17-09.49.54.031806--SYS018---------
DEL-------------RECORD DELETED
--444444444----------2001-09-17-09.49.54.031988--SYS018---------
DEL-------------RECORD DELETED
DSNE610I NUMBER OF ROWS DISPLAYED IS 10
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
Through the use of DB2 V6 Triggers, we can succesfully meet the business requirements without writting any application programs.
You can then execute the DSNTIAUL batch program to unload records older than 12 months and archive them to a flat file.
Feel free to use these illustrations and modify them to meet your own requirements.
For questions
or comments on this site: webmaster@quest-pipelines.com
All content Copyright
© Quest Software, Inc. All rights reserved.
|
