Database installations are growing rapidly in number & size.Demand for high availability & performance is ever increasing and has expanded the DBA's domain of work. This presentation will elaborate on ways to tackle the everyday challenges in a DBA's work. DB2 UDB's built-in tools and features will be leveraged to provide solutions that can be used in the real world. Actual experience in managing Unix/Linux DB2 installations & Infosphere
Datawarehouse DFP installations will be shared as examples.
1
As a DBA, your responsibilites may include tracking DB2 licenses being used for Auditing purposes. The db2licm – License Management Tool –
accomplishes this demand. This tool Adds, removes, lists, and modifies licenses and policies installed on the local system.
3
Due to product name changes or otherwise, if you want to be sure that you are running the free version ( to be on the safe side during auditing), look for this.
In its simplest form, the db2ls command gives the above details & 2 other information – Special Install Number & Installer UID.
5
You cannot query DB2 products using Linux or UNIX operating system native utilities such as pkgadd, rpm, SMIT, or swinstall.
Though the db2diag.log file is primarily meant for IBM’s support team, we can obtain useful information while trouble shooting from this tool. This is a very handy utility to analyze the usually large db2diag.log file.
7
Though the db2diag.log file is primarily meant for IBM’s support team, we can obtain useful information while trouble shooting from this tool. This is a very handy utility to analyze the usually large db2diag.log file. -g & -gi are
‘fieldPatternList’.
This utility is a huge time saver. On several occasions, it becomes the duty of the DBA to clone client systems. The number of client systems can be large in number & manual cataloging of nodes and databases can be a time consuming process – especially if the client to be cloned has a large number of databases cataloged. This utility simplifies the process.
9
Template :
All databases, including related ODBC and DCS information All nodes associated with the exported databases
Common ODBC/CLI settings
Common client settings in the database manager configuration Common client settings in the DB2 registry.
BACKUP
All databases, including related ODBC and DCS information All nodes associated with the exported databases
Common ODBC/CLI settings
All settings in the database manager configuration All settings in the DB2 registry
All protocol information.
MAINTAIN
Creates a configuration profile containing only database- and node-related information for maintaining or updating other instances.
DCS : Database Connection Services
When cloning an instance, the profile imported should always be a client configuration profile that contains configuration information about one DB2 database instance only.
11
Found in : <Installation path>/bin/db2val
Default DB2 installation path on Linux and UNIX platforms:
/opt/ibm/db2/V9.7
Default DB2 installation path on Windows: C:\Program Files\SQLLIB\bin
Extended security (Windows only)
DB2 users are authorized to do certain database operations. For instance, if you can create a database only when the extended security is enabled. If extended security is not set up then db2val gives a warning message.
-Display the db2val.log file contents to the audience from C:/IDUG/db2val.txt
13
-Display to audience the ksh script that runs daily from crontab - C:/IDUG/DB2LOOK.ksh
-a : Generates DDL statements for objects that were created by any user, including inoperative objects
-m : Generates the UPDATE statements that are required to replicate the statistics on tables, statistical views, columns, and indexes. Using the - m parameter is referred to as running in mimic mode.
l : Generates DDL statements for the following database objects:
User-defined table spaces
User-defined database partition groups User-defined buffer pools
-f : Extracts the configuration parameters and registry variables that affect the query optimizer
-Display the sample db2expln output from C:IDUG/ExplainOut
15
-This is a quick tool to analyze the index needs of a SQL or workload. After you execute the db2expln tool and realize that the SQL is not using any indexes/the cost in timerons is very high, you can run this tool against the same input file to see if there are any recommendations to improve performance.
-Display the file C:/IDUG/db2advis to the audience that demo’s the usage by running a db2expln followed by db2advis on a input file.
17
Display the script that can be used to cursor load a large number of tables : C:IDUG/CursorLoadScript
Provided that you have enabled federation and cataloged the data source ('dsdbsource'), you can declare a nickname against the source database, then declare a cursor against this nickname, and invoke the LOAD command with the FROM CURSOR option
19
http://www.ibm.com/developerworks/data/library/techarticle/dm-0812wang/ : An article that explains the db2top tool & has a couple of case analysis.
-If possible (internet/vpn working) show a live demo of the mainscreen, database screen & the bottleneck analysis screen
- If not, then show the images from C:IDUG/db2top_interactive_MainScreen
& db2top_interactive_Bottleneck
21
-The ‘Jump to time of interest’ option actually starts db2top GUI for the time denoted. It’s like looking at the snapshot in the past.
- Show a demo of the ‘Jump to time of interest’ scenario.
23
- Because it retrieves information from DB2 memory sets, it does not issue latches and is therefore a very low-cost means of monitoring DB2.
- snapshot administrative views and table functions can be accessed via SQL
- Display a live session if possible, with various options of the command -
25
- Display a live session if possible, with various options of the command -
- Row compression is available with a license for the DB2 Storage Optimization Feature
Row compression
Row compression, sometimes referred to as deep compression, compresses data rows by replacing patterns of values that repeat across rows with shorter symbol strings. Of the various data compression techniques available
in DB2 Version 9.7, row compression offers the most dramatic possibilities for storage savings.
Value compression
Value compression optimizes space usage for the representation of data, and the storage structures used internally by the database management system to store data. Value compression involves removing duplicate entries for a value, and only storing one copy. The stored copy keeps track of the location of any references to the stored value.
- Compression statistics can be seen in SYSIBMADM.
ADMINTABCOMPRESSINFO
27
-When automatic statistics collection is enabled, DB2 evaluates whether new statistics are required at 2 hour intervals
-To enable automatic statistics collection,
the AUTO_MAINT, AUTO_TBL_MAINT andAUTO_RUNSTATS parameter s must all have a value of ON.
- We have made it a standard practice in our organization to use the PROFILE option on all tables. Makes it easy for developers to run statistics on tables without having to know about the various options & guarantees that the same kind of statistics are collected everytime.
29
- Use the Free DB2 Express-C product where viable. For development, Testing , Reporting or even in production.
- Our company uses this in production. 10 Servers have Exp-C & 2 servers have the paid version ( due to replication ).
- Show the Daily DB2 Status email for a system from Email client - Show the Replication Status Email
- Show all the scripts used for the above purposes briefly.
31
-Explain the issue we had in our production system related to Automatic Storage Tablespaces, where 10G got locked up in USERSPACE1. IBM
Support said there is no work-around. This may become very serious when the filesystem gets filled up – which is what happened with us.
- Look at the views in the SYSIBMADM schema . They offer a lot of information that can be retrieved very easily.
- Display to the audience – if possible – from Datastudio, all the different Admin views in this schema.
33
-In example 1, the result shows that a query is executing for 96 minutes – might be a case for investigation
- In example 2, the result shows a high number of tablescans (544,352) – might be a case for investigation as Tscans degrade performance.
The Explain facility uses the following IDs as the schema when
qualifying Explain tables that it is populating:The session authorization ID for dynamic SQL
The statement authorization ID for static SQL
The schema can be associated with a set of Explain tables, or aliases that point to a set of Explain tables under a different schema. If no Explain tables are found under the schema, the Explain facility checks for Explain tables under the SYSTOOLS schema and attempts to use those tables.
Another way to create EXPLAIN tables:
Call the SYSPROC.SYSINSTALLOBJECTS procedure:
db2 CONNECT TO database-name
db2 CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))
This call creates the explain tables under the SYSTOOLS schema. To create them under a different schema, specify a schema name as the last parameter in the call.
35
I have more than 12 years of DBA experience with DB2.I've been with LSSI since 2003 and as an one-man DBA show, has varied experiences with DB2.I have brought up several DB2 installations from scratch in LSSI, thus gaining valuable experience & expertise in design, installation, maintenance,
Performance tuning,Replication & Migration.I have successfully migrated several instances from V7->V8->V9.I also have experience in several flavours of DB2 - ESE,Express-C,ISW on different platforms - Aix & Unix.I also have been part of the DataStudio Beta Group with IBM.
I hold the following Certifications:
IBM Certified Solutions Designer - Infosphere Warehouse 9.5 IBM Certified Advanced Database Administrator - DB2 9 for LUW IBM Certified DBA - DB2 V9.7 for LUW
IBM Certified Application Developer - DB2 9 IBM Certified DB2 9.5 SQL Procedure Developer
IBM Certified Solution Designer - DB2 Business Intelligence V8.
IBM Certified Solutions Expert – DB2 UDB V7.1 DBA for OS/390 Certified MySQL 5.0 Database Administrator
37
