Poor Man SAN

The open source iscsitarget provides a great way to do SAN on a laptop using VMware. This is good for testing and learning purposes. We use iscsitarget to export devices so that we can use them for the shared database by multiple hosts in a DB2 pureScale environment....

read more

DB2 10.1 HADR

Scott Hayes, CEO of DBI gave me the opportunity to do the IBM DB2 10.1 HADR Presentation on his DB2 Night Show. Follow this link: http://www.dbisoftware.com/blog/db2nightshow.php?id=372

read more

DB2 pureScale and SCSI-3 PR

DB2 10.1 pureScale requires an optional but highly recommended SCSI-3 PR capable storage. DB2 10.1 pureScale requires a mandatory tie breaker disk which should be SCSI-3 PR capable. Optionally, you can configure an IP address to be a tie-breaker disk but this is not...

read more

Cloning VM when DB2 is on

In today's virtualized environment, it is easier to clone a virtual machine from an existing one. When DB2 is already installed with Tivoli SA MP for high availability, we need to also make sure that each host has a unique node id otherwise HA configuration will...

read more

BGInfo for Linux

If you are spoiled using BGInfo of Windows to show some useful information on your desktop, you may try this poorly written but working script that does the same thing. This was written for SuSe Linux but can be adapted for other distribution. If you happen to make...

read more

Generate and copy /etc/hosts file to all hosts

The scripts that I am sharing are used to do the DB2 pureScale install on 'n' number of hosts. One of the issue is to have consistent /etc/hosts file on all nodes. The input file is ip.txt similar to the previous posts.   Script to generate and copy hosts...

read more
Set up passwordless SSH between hosts

Set up passwordless SSH between hosts

If you need to set up a passwordless SSH between multiple hosts, you can try the following script to do it automatically for you rather than manually copying the keys from one hosts to another. The script needs an input file ip.txt that has server information for all...

read more

SQL Monitoring from the Stored Procedure

You can monitor dynamic SQL statements by using SYSIBMADM.TOP_DYNAMIC_SQL view but you need an event monitor to monitor SQL statements inside a DB2 Stored Procedures as those statemets are static in nature. Let us understand this through a simple exercise: Step-A:...

read more

Equivalent of Oracle KEEP function in DB2

Equivalent of Oracle KEEP can be in DB2 using a nested query OVER (PARTITION BY ..) and then using MAX and MIN on top of it without having any extra sort. It is best explained by an example: Case-1: Oracle query using KEEP function: CREATE TABLE emp(id INTEGER, name...

read more

DB2 Control Center Code Page

If you are using DB2 Control Center on Windows and wondering how to set the code page of your choice, follow these steps. Open a DB2 command window: C:\>SET DB2CODEPAGE=1208 C:\>db2cc The first command sets the environment variable DB2CODEPAGE to 1208 (UTF-8) and...

read more

Federation of Oracle Data in DB2

Steps to configure Oracle database federation from DB2 on zLinux. (Or any kind of *nix) These steps are for Oracle database (any supported version from Oracle) to DB2 9.5 but they should work as it is for DB2 9.7 or later with proper Oracle client install. 1. Install...

read more

Federation of SQL Server Data in DB2

Steps to configure SQL Server federation from zLinux. (Or any kind of *nix) These steps are for DB2 9.5 but they should work as it is for DB2 9.7 or later with proper install of IBM branded ODBC drivers. 1. Install DB2 2. Install IBM Branded DataDirect ODBC Drivers....

read more

DB2 SQL PL – MESSAGE_TEXT

When you want SP to return message_text and you also want to handle error code as per your choice, follow this simple example. CREATE PROCEDURE myproc( OUT v_msg VARCHAR(300)) LANGUAGE SQL BEGIN DECLARE V_ERROR INTEGER DEFAULT 0; DECLARE CONTINUE HANDLER FOR...

read more

New Exciting Features in DB2 9.7

This is by no means an exhaustive list of new features in DB2 9.7. But, it is the list of features that are most important to me as a DBA or a developer. Online Schema Changes See Burt's article on Online Schema changes. CGTT - Created Global Temporary Table Now, it...

read more

Unused indexes, tables and packages in DB2

Starting version DB2 9.7, you can use following query to detect unused indexes in DB2. SELECT INDSCHEMA, INDNAME, TABNAME FROM SYSCAT.INDEXES WHERE LASTUSED = '01/01/0001'; The column LASTUSED tells when that index was last accessed in DB2. This is not per se a...

read more

Online Schema Changes – The Easy Way

I am going to show you an easy way to do online schema changes in DB2. The best to learn things is by doing some simple examples. My intent is to make you an expert in 1/2 hour if you learn the following concepts. Create a database with and without automatic storage...

read more

Java Program Name for DB2

Finally DB2 implemented a long standing demand for setting the Java application name so that you could see the name through DB2 utilities instead of seeing db2jcc_applica. You set a connection property clientProgramName to your chosen name and the db2 utilities will...

read more

Build an INSERT statement

Use this DB2 Stored Procedure to build an INSERT statement. This is very useful when you are developing some Java or .Net program or any other program and need an easy way to do this. Thanks to Serge Rielau of DB2 Toronto lab for sharing this with the DB2 community....

read more

DB2 Update Statement using sub-query – Use MERGE

Thanks to Serge Rielau for insight in DB2 MERGE. For example, if you have this Oracle UPDATE statement and wondering how can you use this in DB2. Here is the solution. Oracle UPDATE UPDATE ( SELECT clm.c2 wc1, m.c2 wc2 FROM schema1.table1 m, schema1.table2 clm WHERE...

read more

Run DB2 Scripts from Java Programs

If you have DB2 scripts that you want to run through your Java programs, you normally use Runtime.getRuntime().exec method to run system commands. The following sample code tells how to run DB2 scripts. The following code shows it for both Windows and Unix systems....

read more

Matt Devlin’s Perl Scripts for DB2

Are you looking for Matt Devlin's Perl Scripts for DB2? Even though, they are old but still they are relevant with new release of DB2 particularly if you know how to modify them. Matt Devlin's website http://mattdevlin.net is no longer available but you can get...

read more

DB2 LOAD using ASC option through MTK

If you use MTK (Migration Toolkit from IBM) to migrate your database to DB2 on Windows platform, the migration goes smooth but you may see problem when you FTP data file and LOAD script to Unix platform if you do not do it right. For example, you chose ASC (ASCII)...

read more

DB2 Check Pending Script

Thanks to Max Petrenko of DB2 Toronto Lab for sharing a very useful script to remove check pending status from the DB2 tables after LOAD or other operations. It is easy to generate a check pending script, but the importance of this script is that it builds the...

read more

How to concatenate rows together?

Say, you want to concatenate all rows of a column and show it as a single row in the same fashion aggregate function works. $ db2 "create table t1(line# int not null, word varchar(20))" $ db2 "insert into t1 values(1,'The'),(1,'horse'),(1,'is'),(1,'white')" $ db2...

read more

Speed up MQT – Tips

Edin I. Aganagic shares his tips on how to increase incremental refresh/maintenance performance of MQTs in DB2. Create an index on the GROUP BY columns. It helps speed up MQT look-ups needed to decide whether existing row needs to be changed, new one inserted, or last...

read more

5.0 Use logging through C Stored Procedures

The logging framework routines are written in C, so you can use them directly from your C stored procedures without having to make a call to the logging stored procedures. The example C stored procedure below uses the logging API. To use the logging methods, include...

read more

4.0 Use logging through SQL Procedures

After you run make successfully, you will notice a file generated known as testsp.sql. The only reason we generate this file instead of giving this file as it is is to calculate the size of logging handle properly for your Linux/Unix platforms. This is due to the fact...

read more

3.0 Logger Setup

In order for you to set up the logging framework, you might need help from your system and/or database administrator to set up the stored procedure logging directory. Before we delve into this, let us understand how external stored procedures (Our logging framework...

read more

2.0 Reference guide

An explanation of logging tokens The first DB2.OPEN_LOG call builds a linked list hash table as shown below from the logging tokens kept in the shared memory. For each logging token read from the shared memory or configuration file on its first invocation, OPEN_LOG...

read more

1.0 Overall architecture and design

Motivation You might need to write informational, error, and debug messages to a log file for a number of reasons: To test, analyze and validate business logic. To track and review detailed error messages. To fix business logic bugs encountered in production...

read more

DB2 Procedures Logger

I published an article Log stored procedures messages for DB2 on Linux or Unix on IBM Developerworks website in January 2006. Since then, I have made several bug fixes and enhancements to this logging framework. The enhanced logging framework is documented here with...

read more

DB2 ODBC Driver double quotes

If you are migrating your ODBC related code from one database to DB2, you might run into an issue of single quote vs. double quote issue. For example: Your ODBC code might issue a call to a Stored Procedure using double quote as shown below: CALL...

read more

C Table UDF to join flat file with your SQL

Sometimes, it may be necessary to expose data in your flat file with the relational data. There might be some situations where there is no option to load that data in a table. But still, you need a way to use this flat file data in your SQL statements. DB2 provides...

read more

Julian day in DB2

DB2 provides a way to calculate date from a Julian day. For example: 2008075 should match to the 75th day of 2008 and i.e. 2008-03-15. If you try to do, $ db2 values date(2008075) You will get a date of 12/03/5498 but that is what you do not want. The argument to DATE...

read more

Optimistic Locking in DB2 9.5 LUW

Improve concurrency with DB2 9.5 optimistic locking New optimistic locking feature to avoid maintaining long-lived locks IBM® DB2®, Version 9.5 for Linux®, UNIX®, and Windows® provides enhanced optimistic locking support, a technique for SQL...

read more

How to setup the Self Tuning Memory Manager (STMM)

Self-tuning memory was first introduced in IBM ® DB2 ® 9.1 and simplifies the task of memory configuration by automatically setting optimal values for most memory configuration parameters, including buffer pools, package cache, locking memory, sort heap, and total...

read more

DB2 LUW Deep Compression

Introduction This article discusses DB2 LUW Deep Compression from the perspective of someone who planned, organized, communicated and coordinated the implementation of this in development, test, regression test and production environments for multi-terabyte data...

read more

Case insensitive string comparison in DB2

You are trying to figure out the best approach to do the case in-sensitive search in DB2. You must first read these 3 excellent articles written on this topic by 3 IBMers. Blair Adamache Doug Doole Knut Stolze In a nutshell, you can take one of the approach as...

read more

How to compile DB2 PHP driver

You are new to ibm_db2 or db2 and PHP driver available with thin ODBC-CLI client do not match with your version of PHP. The following steps take drudgery out of complex compilation process for those DBAs or system administrator who are averse to compilation process....

read more

Top like utility for DB2

Yes, db2 has a top like utility called db2top written by 2 IBMers Subho Chatterjee and Jacques Milman. It uses DB2 snapshot APIs to extract information from DB2 engine and shows in a format that you are familiar with top utility on Unix systems. Click at this

read more

Show Package ISOLATION Level

Use following SQL to find out the ISOLATION LEVEL used by DB2 packages. SELECT 'SHOW_RSETS' "STORED PROCEDURE", SUBSTR(PKGNAME,1,18) PACKAGE, "ISOLATION" FROM syscat.packages WHERE pkgname = (SELECT deps.bname PACKAGE FROM sysibm.sysdependencies deps,...

read more

Equivalent of NEWGUID in DB2

If you have been using UNIQUE_IDENTIFIER as a data type and use newguid as a default in your SQL Server table definition, you can use a UDF called from a trigger to simulate same behavior in DB2. This work around is due to the fact that DB2 does not yet support...

read more

DB2 JDBC Driver Secrets

There are several ways one can do DB2 JDBC tracing and it all really depends how creative you are. Check version of JCC Driver $ java com.ibm.db2.jcc.DB2Jcc -version Simple nonsense solution to use JCC Tracing import java.sql.Connection; import java.sql.DriverManager;...

read more

Get DB2DIAG severe messages of past 24 hours

You can put this script as a cron job so that you get DB2 severe messages through email or to your pager. #!/bin/bash # To display severe errors logged for the last 1 day: db2diag -gi "level=severe" -H 1d | /bin/mail -s "DB2DIAG severe messages in last 24 hours"...

read more

Query optimization in DB2 using REOPT

In Oracle world, DBAs generally say that Oracle can do static and dynamic SQL. When they say this, it amuses a DB2 DBA since Oracle way of static and dynamic SQL as perceived by an Oracle DBA is no way near to what is meant by static and dynamic SQL in DB2. In Oracle,...

read more

Size of temporary bufferpool in DB2

This is a common practice to bump the size of all buffer pools in DB2 as that gives the maximum performance boost. It is true but as a DBA you will like to monitor the buffer pool hit ratio and use of the buffer pool to make sure that you are not wasting your memory....

read more

Asynchronous Read activity

As a DBA, your efforts is to make sure that you have as much asynchronous read activity on your database. The intentions are good but how do I know the amount of asynchronous activity going one in my database. This SQL will give you the Asynchronous read ratio (ARR)...

read more

Difference between db2pd tool and DB2 snapshots

DB2 snapshot command or use of the snapshot administration views or SQL table snapshot functions require latches and use of engine resources to return the information but db2pd tool does not require those latches etc to return the information. You should use db2pd...

read more

How to change Fenced user id?

Sometime back, I wrote about how to know about a fenced user id in DB2 at http://www.db2ude.com/?q=node/30. The other day, someone asked me how do I change the fenced user id in DB2? This is very easy but will require an outage if you are already in production. I...

read more

Why UR is escalating to CS?

Did you wonder why your isolation level escalated to CS from UR? For this discussion, you might want to read about unambiguous cursor at DB2 at http://db2ude.com/?q=node/41. So even if you open a cursor with UR isolation level, there is a possibility that this might...

read more

What are P, B and L locks in DB2?

Did you know about P, B or L locks in DB2? They are internal locks in DB2. As a DBA, it is good to know about them so that we know how to deal with them or avoid them. P Locks These are package level locks and you will see them when any application is executing a...

read more

How to create EXPLAIN tables in DB2

Most of the GUI tools create EXPLAIN tables automatically for you but if you need to create them, you can use one of the following approach. DB2 provides EXPLAIN.DDL in ~/sqllib/MISC directory and you can execute this file $ db2 connect to sample $ db2 -tf...

read more

How do I set isolation level in DB2?

You are a DBA and you want your users to use an isolation level of UR who are doing adhoc queries or typing SQLs using DB2 CLP or some form of the applications. By using DB2 CLP Use SET CURRENT ISOLATION LEVEL command db2 connect to sample db2 set current isolation...

read more

How do I set special registers in DB2 for audit?

Through a application server where you open dedicated connections to database, you get information about your SQL using applications server user-id only. How do you tie those information back to the end user who initiated the transaction? One option is to use DB2...

read more

LAST_DAY, FIRST_DAY in DB2

LAST_DAY(SYSDATE) ==> values (current date + 1 month) - day(current date +1 month) days FIRST_DAY(SYSDATE) ==> values (current date - day(current date + 1 month) days) + 1 DAY

read more

Get index space usage for a table

How do you get index space usage by a table? DB2 provides a Table function ADMIN_GET_TAB_INFO to retrieve information about a table. For example - To find index space used by a non-partition table's indexes, you can use following SQL: SELECT index_object_p_size as...

read more

Speed up DB2 Backup

Generally people try to tune DB2 backup by manipulating PARALLELISM parameter but there is no need to manipulate this parameter if you are looking for a least possible time to backup the database. Let DB2 figure out the optimal value for you for PARALLELISM. But, you...

read more

Tables in LOAD pending

If you are using LOAD utility to load 100s of tables, you can use following SQL to find out the tables which are in LOAD pending status. SELECT TABSCHEMA, TABNAME, LOAD_STATUS FROM SYSIBMADM.ADMINTABINFO WHERE LOAD_STATUS IS NOT NULL

read more

DB2 Audit Facility

The new version of DB2 9.5 has enhanced DB2 audit facility significantly and now it makes a perfect sense for the DBAs to start using it. The traditional notion on any audit (DB2 or Oracle) is that it is very expensive. That is not the case now as you have the options...

read more

LOAD operation in HADR

You have setup your databases in HADR operation and you are wondering how my DB2 LOAD will transfer from one machine to another since DB2 LOAD operation is not logged. Well, the DB2 LOAD operation is of course logged but not the actual contents. So, how do I get my...

read more

DB2 HADR parameters

If you have enabled HADR between 2 databases, the following value of parameters might give you better performance. Database configuration parameters LOGFILSIZ = 6000 LOGBUFSZ = 512 SOFTMAX = 125 DB2 Registry variables db2set DB2TCP_CLIENT_RCVTIMEOUT=20 db2set...

read more

DB2 Backups Archiving and Deleting

DB2 9.5 deletes previous backup images, log files and copies of load images automatically. This alleviates lots of pain for maintaining DB2 backups and log files. Prior to DB2 9.5, these tasks were usually attained by the operating systems scripts. For example, Taking...

read more

DB2 Database on different location in Windows

On windows platform when you create a DB2 database as per following syntax, it gets created in C:\DB2\NODE0000 location where DB2 is the name of the instance. C:\>db2 "CREATE DB TESTDB AUTOMATIC STORAGE YES ON C: USING CODESET UTF-8 TERRITORY US" What if, you want to...

read more

Features used in DB2.

DBAs have to comply with the licensing requirements for DB2. As a DBA, how do you know if you are using a particular feature of DB2 or not? I wrote about this long time back at here. In addition to above, you can also use this SYSIBMADM view to find out if you are...

read more

File system caching in DB2 with SAN devices

Lots of you use DB2 on SAN storages either from IBM or EMC or other third party. Most of SAN storages have their own caching which is to enhance the performance. After working with many DB2 users, I have found that these file system caching is not used properly to use...

read more

How to use db2batch?

The db2batch utility is a powerful utility to time the SQL queries. You should be careful in using the db2batch utility during peak time in your production system just for one issue - db2batch utility uses RR isolation level. For long running SQL statements, the RR...

read more

Convert HEX to CHAR

If you want to convert a 2 digit hex code to a single char code, here is the function that you can use. DROP FUNCTION HEX2CHAR; CREATE FUNCTION HEX2CHAR (hexcode varchar(2)) RETURNS CHAR(1) SPECIFIC hex2char LANGUAGE SQL CONTAINS SQL EXTERNAL ACTION DETERMINISTIC...

read more
Simulate z/OS DB2 Collation sequence in DB2 LUW

Simulate z/OS DB2 Collation sequence in DB2 LUW

You might come across a need to simulate collation sequence of DB2 on mainframe (or MVS or z/OS) as it is on DB2 LUW (Linux, Unix and Windows) platform if you want your application to show the order of the data exactly same way as it is on the mainframe. The main...

read more

Number of Max Files Open in Linux

If you are connected to Linux via SSH and you want to bump up number of max file open either for DB2 or for your any program, you might run into issues where it does not work for you. Say for example: You login as root and do this modification to...

read more

Follow Me on Linked In

Archives

Categories