DB2 LUW DBA How. To Useful database administration techniques for Unix LUWUDB DB2. Native DB2 LUW database snapshot utilities identify performance characteristics of the database at the moment they are run, but are not easily used to identify performance trends over days, weeks and months. There are obvious advantages to being able to compare performance trends over time furthermore many snapshot data elements are not meaningful unless seen changing over time. With the advent in DB2 version 9 of System Administrative Snapshot Views, automating historical performance capture has become easy for Unix database administrators. This article will show the administrator how to use Unix shell scripts to build a home grown database performance history system without resort to third party tools. Shell scripts are used to automate performance capture and provide 2. The author also provides 4. Our target audience for this article are Unix DB2 Database Administrators and system support staff tasked with DB2 database monitoring in Unix environments. Solaris, AIX and Linux DB2 DBAs running DB2 version 9 and later will benefit. The benefits of consistently capturing historical performance data using snapshot administrative views are many Many levels of granularity are possible. Look at performance characteristics over an hour, day, week or month. Native DB2 LUW database snapshot utilities identify performance characteristics of the database at the moment they are run, but are not easily used to identify. Each connection instance maps to a single database. This method is helpful when mangaging multiple db connections. If arguments are passed, they are proxied to either. Create Trigger On Database For Update With Rs Facing' title='Create Trigger On Database For Update With Rs Facing' />Quick access to hundreds of snapshot data elements. Understand overnightweekend performance problems. Retrospective debugging of database problems. How many times have you found out about a problem hours after the problem actually manifested, too late to run snapshots and left to speculate about the source of the problem DB2 issues often manifest in the middle of the night or on weekends. B10501_01/appdev.920/a96590/adg81053.gif' alt='Create Trigger On Database For Update With Rs' title='Create Trigger On Database For Update With Rs' />By the time youre on the scene, the source of the crash is long gone, leaving little besides db. Having historical snapshot performance data on hand will allow you to more easily track down the gremlin. Visualization potential using graphing software. This longitudinal capability was what made DB2 monitoring tools such as Database Guys. You dont need to purchase a third party tool to gain this capability. You just need to build your own capture system. It is not difficult. Prior to DB2 version 8, the only way to obtain snapshot data was to generate a text file from GET SNAPSHOT output, and use shell scripts, perl or other parsing tools to aggregate the data. IBM introduced snapshot table functions in version 8. Best 3D Mechanical Design Software. These represented a huge leap forward by giving administrator access to snapshot data via SQL. For DB2 version 9 IBM introduced snapshot Administrative Views, which are now the preferred means of accessing snapshot data, and are easier to write for than table functions. Snapshot administrative views present snapshot data similarly to system catalog views. Both snapshot Table Functions and Administrative Views are very useful, providing snapshot data about current database performance mediated by powerful SQL reporting language. The behavior of the database at this moment in time might be all you need to know. But the ugliest problems seem to happen outside of the 9 5 window, and single time slice snapshots dont permit us to perceive patterns such as trends, recurring performance spikes, or slow degradation in database performance over the span of weeks. Snapshot data becomes vastly more useful when stored over time in performance monitoring tables. Heres how. Assumptions. Version DB2 LUW Version 9. Unix OS is used for scripting examples, but the system could be adapted for Windows. Either SYSADM authority or a combination of SYSMON and other authority for table creation. Understanding of System Monitor data elements and performance metrics. References. My performance view DDL and related queries were written for the condition of a single database per instance, because that was true in the shop where I developed this version of the capture system. If you have multiple databases per instance, modify my examples for primary key, metric view SELECT columns, predicates and GROUP BY clauses of report views to include DBNAME. If you are a DPF shop, you will also need to make a similar change for column DBPARTITIONNUM. Create snapshot historical tables. This is possibly the best reference for available snapshot administrative views. This article provides CREATE DDL for only 5 snapshot capture tables. These are the only tables you will need for the specific performance reports provided in this series actually you wont need SNAPTBSCFGT either, but I had it so decided to include it. If you want to store the full subset of columns from an administrative view, the quickest way to create your historical system administrative table is Use the CREATE TABLE. LIKE syntax to create the table, for exampledb. DBA. SNAPDB like SYSIBMADM. SNAPDBUse ALTER TABLE. ALTER COLUMN SET NOT NULL to make not null those columns which will comprise your primary key, for example db. DBA. SNAPDB alter column SNAPSHOTTIMESTAMP set not null. You may need to reorg your new table for exampledb. Create your unique index for primary key, and alter table add primary key. If you do not want to store the full set of columns from the SYSIBMADM view, you can create custom DDL for the administrative view. Unfortunately db. DDL directly from an administrative view, because SYSIBMADM objects are not true user views. But what you can do to make quick work of editing your own DDL is to follow Step 1 above CREATE TABLE. LIKE. and then run db. Drop the table you created after you have produced the DDL from db. Massage the DDL to include only those columns you wish kept for history. Create the table from your edited DDL, then create your primary key. If you choose to capture a smaller subset of administrative view columns, change your populate table INSERT subselect to use explicitly named set of columns instead of SELECT. The snapshot administrative views I use in the remainder of my examples are Download all these DDL files as a single Create these tables, and proceed to the next step Populate snapshot historical tables. Populate snapshot historical tables. These script examples use cron and Korn shell, but you can substitute your own scheduler and scripting tool. For the performance granularity I sought, I had the populate script run every five minutes. Your executing user id must have at least SYSMON authority and INSERT authority on the target DBA schema tables. Populate script populatesnapshottables. Here is a sample line from cron l 0,5,1. Performance Metric Views. Included in this article are about forty views of database performance metrics. The 2 column views all feature an initial column SNAPSHOT TIMESTAMP with the second being the metric. These views are ideally suited for time graph 2 axis charting and visualization. We then present a few views 3 or more columns with more complex presentation of performance metrics, not suitable for an X Y graph but useful for the diagnosis of specific performance issues. Please see my References for sources and credits. All SQL in this article is original and is protected under a Creative Commons 3. I wrote these views for an environment in which there was only a single database per DB2 instance, so users with multiple databases per instance will have to make a small modification of some views. With multiple databases per instance, any views which interpret table DBA. SNAPDB, DBA. SNAPAPPL, or DBA. My. SQL PHP tutorial My. SQL programming with PHP. This is a PHP programming tutorial for the My. SQL database. It covers the. My. SQL programming with PHP. It uses the generic mysql module. The examples were created and tested on Ubuntu Linux. There is a similar. My. SQL C API tutorial. My. SQL Python tutorial. Mongo. DB PHP tutorial, and. Postgre. SQL PHP tutorial on Zet. Code. If you need to refresh your knowledge of the PHP language, there is a full. PHP tutorial on Zet. Code. About My. SQL database. My. SQL is a leading open source database management system. It is a multiuser. My. SQL is especially popular on the web. It is one of the parts of the very popular LAMP platform. Linux, Apache. My. SQL, and PHP. Currently My. SQL is owned by Oracle. My. SQL database is available. OS platforms. It runs on BSD Unix, Linux, Windows, or Mac OS. Wikipedia and You. Tube use My. SQL. These sites manage millions of queries. My. SQL comes in two versions My. SQL server system and My. SQL. embedded system. Before we start. We need to install several packages to execute the examples in this tutorial. The php. 5 cli is the command line interpreter for the PHP5 programming language. All examples in this tutorial are created on the console. I have intentionally skipped. PHP and My. SQL. If you dont already have My. SQL installed, we must install it. This command installs the My. SQL server and various other packages. While installing the package, we are prompted to enter. My. SQL root account. Next, we are going to create a new database user and a new database. We use the mysql client. We check if the My. SQL server is running. If not, we need. to start the server. On Ubuntu Linux, this can be done. The above command is a common way to start My. SQL if we have. installed the My. SQL database from packages. The above command starts My. SQL server using the My. SQL server. startup script. The way how we start a My. SQL server might. It depends whether we have installed My. SQL. from sources or from packages and also on the Linux distro. For further information consult. My. SQL first steps. Linux distro information. Next, we are going to create a new database user and a new database. We use the mysql client. Enter password. Welcome to the My. SQL monitor. Commands end with or g. Your My. SQL connection id is 3. Server version 5. Ubuntu. Type help or h for help. Type c to clear the buffer. SHOW DATABASES. Database. We use the mysql monitor client application to connect to the. We connect to the database using the root account. We show all available. SHOW DATABASES statement. CREATE DATABASE mydb. Query OK, 1 row affected 0. We create a new mydb database. We will use this database throughout. CREATE USER user. IDENTIFIED BY 3. Query OK, 0 rows affected 0. USE mydb. Database changed. GRANT ALL ON mydb. Query OK, 0 rows affected 0. We create a new database user. We grant all privileges to this user. In order to connect to the My. SQL database from the PHP language, we must have. This is a package name for. DebianUbuntu Linux. On other derivatives the name might differ. This package. has three modules. They are also called extensions. The generic mysql module is the original PHP API for the My. SQL. database. Our tutorial covers this module. The API is procedural. This module does not provide all the latest features of the newer. My. SQL databases. The My. SQL improved mysqli module is the. My. SQL versions 4. It provides both. APIs. It has several benefits and enhancements. The pdomysql, PHP Data Objects module is a database abstraction. PHP applications. This module is beneficial if we write portable. PHP scripts. The following script is a simple PHP script. If this small. script runs OK, we have everything needed installed. Could not connect to servern. EUSERERROR. echo Connection establishedn. We connect to the database and get some info about the My. SQL server. host localhost. These are three variables holding the host name, user name and password. The variables are needed when connecting to the My. SQL database. r mysqlconnecthost, user, pass. We use the mysqlconnect function to connect. The function returns a boolean value indicating. The function has 3 parameters. The first is the host, where. The second and third parameters are the user name. Could not connect to servern. EUSERERROR. echo Connection establishedn. Now we check the r variable. If it contains a boolean false, the. We call the triggererror. The first generic message. The more specific error message generated with the. The mysqlgetserverinfo returns the My. SQL server version. The mysqlclose function closes the connection. Closing connection in our case is not necessary. However, it is a good programming practice. On my system, I got the following output. We have a similar script. Could not connect to servern. EUSERERROR. echo Connection establishedn. SELECT VERSION. Could not execute query queryn. EUSERERROR. echo Query query executedn. Version row0n. We check for the version of the My. SQL database. This time using. SELECT VERSION. This is the SQL SELECT statement. It returns the version. The VERSION is. My. SQL function. The mysqlquery function executes an SQL. This is a SELECT query, so the result is. Could not execute query queryn. EUSERERROR. echo Query query executedn. In case of an error we generate an error message. Otherwise. we print the SQL query executed. We fetch a row from the result set. The row variable. Version row0n. We print the data from the array. We know from the nature of our. My. SQL version. php version. Connection established. Query SELECT VERSION executed. Version 5. 1. 6. Output of the script on our system. Creating and populating a table. Next we are going to create a database table and fill it with data. Cannot execute query queryn. Query query executedn. Could not connect to servern. EUSERERROR. echo Connection establishedn. Cannot select databasen. EUSERERROR. echo Database selectedn. DROP TABLE IF EXISTS Cars. CREATE TABLE CarsId INT PRIMARY KEY, Name TEXT. Price INT ENGINEInno. DB. executequeryquery. INSERT INTO Cars VALUES1,Audi,5. INSERT INTO Cars VALUES2,Mercedes,5. INSERT INTO Cars VALUES3,Skoda,9. INSERT INTO Cars VALUES4,Volvo,2. INSERT INTO Cars VALUES5,Bentley,3. INSERT INTO Cars VALUES6,Citroen,2. INSERT INTO Cars VALUES7,Hummer,4. INSERT INTO Cars VALUES8,Volkswagen,2. In the above code example, we create a Cars table with 8 rows. Cannot execute query queryn. Query query executedn. We have created a custom executequery function which will. INSERT statement. Before we can work with database tables, we must select a database. A database is selected with a mysqlselectdb function. Cannot select databasen. EUSERERROR. echo Database selectedn. Error handling for the database selection process. DROP TABLE IF EXISTS Cars. The first query drops a Cars table if it already exists. CREATE TABLE CarsId INT PRIMARY KEY, Name TEXT. Price INT ENGINEInno. DB. executequeryquery. This is the SQL statement to create the Cars table. INSERT INTO Cars VALUES1,Audi,5. A car is inserted into the table. Cannot execute query. In case of an error, we print the error message and. Connection established. Database selected. Query DROP TABLE IF EXISTS Cars executed. Query CREATE TABLE CarsId INT PRIMARY KEY, Name TEXT. Price INT ENGINEInno.