zinox

Steve Rees and Hania El Ayoubi from Toronto Lab have shared this utility function written in SQL PL to browse table data in column value pair. It becomes useful when browsing wide table data. For example, if one looks at the table sysibmadm.mon_db_summary or table function mon_get_table("SYSIBM","SYSTABLES"), the output is few rows but number of columns are too many and they wrap on the screen and are difficult to read.

Attached files are: db2perf_browse.txt for help and db2perf_browse.db2 for the stored procedure code:

Mark Barinstein has written another variant of the above stored procedure which takes a generic SQL statement and will return columns in column / value pair.

This procedure is col2rows.db2.

If a table is created with an IDENTITY column and you need to make sure that your identity column starts from the last max value that you load the data. IBM Data Movement Tool does this automatically but if you are not using the tool and want a way to sync up start value of the identity column with the data in your database, you can use this stored procedure to sync the data.

Save following in a file sp.sql

Packaging for DB2 client has changed. Review this article for detailed information and how to download thin / thick clients

Read Problem determination guide here

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 second command launches Control Center.

How would you know if the Control Center picked up the chosen code page or not?

In Control Center click Tools>Tools Settings and click on Command Editor tab. Check Display SQLCA Data and close the Tools settings window.

Now, right click on Database in CC and select "Query". This will open up DB2 Command Editor GUI window. Type any command and you will see the SQLCA data and this holds the key.

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 DB2
3. Install Relational connectors

or Install InfoSphere DB2 (The InfoSphere DB2 is a limited edition of DB2 with relational connectors to connect to other relational databases such as Oracle, SQL Server, Sybase etc.). You do not need to install IBM branded DataDirect ODBC drivers in order to connect to Oracle since we will be installing Oracle client to connect to Oracle from DB2 server. (InfoSphere DB2 is used to be known as IBM Webspehere Federation Server.)

Install Oracle Client

Get the proper Oracle client to install it on your zLinux (or any kind of *Nix) DB2 server.

Follow these steps to make Oracle and DB2 talking to each other properly.

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.
3. Install Relational connectors

or Install InfoSphere DB2 (The InfoSphere DB2 is a limited edition with relational connectors to connect to other relational databases such as Oracle, SQL Server, Sybase etc.). But, you have to install IBM branded DataDirect ODBC drivers before installing InfoSphere DB2. (InfoSphere DB2 is used to be known as IBM Webspehere Federation Server.)

Install IBM branded DataDirect ODBC Drivers

For DB2 9.5, download IBM branded DataDirect ODBC drivers for zLinux from this link:

You can download IBM branded DataDirect ODBC drivers for other *nix for DB2 9.5 and higher from this link.

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 SQLEXCEPTION 
      BEGIN
         GET DIAGNOSTICS EXCEPTION 1 V_MSG = MESSAGE_TEXT;
         SET V_ERROR = SQLCODE;
      END;

   SET v_msg = '';

   -- DO SOME SQL that throws the error and do conditional processing but want to see detailed 
   -- error message so that you can see what went wrong.

   IF V_ERROR 

The OUT parameter v_msg will give you detailed error message that DB2 will throw and you handled error as per your condition code.

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 is possible to create GLOBAL TEMPORARY TABLE so that they are in catalog instead of them only declarabale in a session. This has several benefits including - 1. CGTT under authorization controls, 2. Views, indexes and triggers can be defined on CGTT, 3. CGTT and DGTT can contain LOBS.

CREATE GLOBAL TEMPORARY TABLE DGTT_TABLE
(
   EMP_ID INTEGER,
   HIRE_DATE TIMESTAMP(3)‏
)‏

Parameter Markers

Parameters in a procedure can have default values.
You can skip parameters with default values in a CALL statement.
Named parameters allows values to be assigned by name in any order.

SSL Connection in DB2

DB2 allows to use SSL connection between a java client and server. Server and client configurations are required for SSL connection to take place.

Server Configuration

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 guaranteed solution that will show right value in LASTUSED column when you query it since DB2 will figure out when to update this column on its own. However, you can use db2pd tool to know definitely when an index was last used since database was activated.

This query will work only in DB2 V9.7 or higher but you can still use db2pd tool for other versions. The LASTUSED column has been added to SYSCAT.TABLES, SYSCAT.INDEXES and SYSCAT.PACKAGES. So, you will be able to figure out unused tables, indexes and packages starting DB2 9.7 using simple queries rather than interpreting output from the db2pd tool.

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.

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 pick up this name instead of the default name of db2jcc_application.

java.util.Properties conProp = new java.util.Properties();
String url = "jdbc:db2://localhost:50000/GSDB";
Class.forName("com.ibm.db2.jcc.DB2Driver");
conProp.put("user", "myUserName");
conProp.put("password", "myPassword");
conProp.put("clientProgramName", "MyProgram");
connection = DriverManager.getConnection(url, conProp);

When you list the application, you will see the name that you set.

There are DB2 LUW configuration parameters that can be set to AUTOMATIC and managed by the DB2 automatic memory manager (STMM). One advantage of this is that DB2 will automatically adjust the memory it uses to get the work done based on the characteristics of the current work load and it will continue to adjust itself dynamically as processing continues. Below is a list of configuration parameters for DB2 V9.5 that can be set to AUTOMATIC.

Here is a list of database manager (instance) configuration values for a partitioned environment that can be set to AUTOMATIC. Note that the SHEAPTHRES can be set to 0 and this combined with other database level configuration parameters can result in DB2 sort memory being managed by the automatic memory manager.

db2 attach to db2inst1

db2 get dbm cfg show detail

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. You can also download the script from here.

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  clm.c1 = m.c1
         and clm.date1 between to_date('10012008','MMDDYYYY') and  to_date('12312008','MMDDYYYY')
         and m.date2 between to_date('10012008','MMDDYYYY') and  to_date('12312008','MMDDYYYY')
         and m.flag = 'N'
) joined
SET joined.wc1 = joined.wc2;

DB2 Merge

MERGE INTO schema1.table1 m
  USING  schema1.table2 clm
   ON  clm.c1 = m.c1
         and clm.date1 between to_date('10012008','MMDDYYYY') and  to_date('12312008','MMDDYYYY')
         and m.date2 between to_date('10012008','MMDDYYYY') and  to_date('12312008','MMDDYYYY')
         and m.flag = 'N'
WHEN MATCHED THEN UPDATE SET m.c2 = clm.c2;

SQL Server Update - 2 table issue

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. The trick here is to run db2cmd with /c /i /w switches to make it working properly. For Unix systems, it assumes that you are running this program from db2 enabled shell or you have sourced your db2profile. Same way, you use ksh shell with -c switch and pass all arguments through a array variable.

Through this program, you are passing full path name of the db2 script. We also change directory to the parent of the script so that all other dependent scripts residing in the directory do not fail. This is not so much about Java programming but how to use shell capability to run multiple commands.

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 download his scripts here.

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) option since you do not have an option of specifying a right delimiter for DEL option since your data contains CR, LF, TAB, Tilde(~), vertical bar(|) etc.

A sample LOAD statement generated by MTK using ASC option might look like following:

load from datafile.out of ASC MODIFIED BY reclen=2066 codepage=1208 
nochecklengths striptblanks METHOD L 
(1 12,14 523,525 536,538 549,551 750,752 1261,1263 1462,1464 1469,1471 1492,1494 
 1505,1507 1507,1509 1534,1536 2045) NULL INDICATORS (0,2048,0,2050,2052,2054,
2056,2058,2060,2062,0,2064,2066) messages messages.msg 
replace INTO SCHEMA.TABLENAME nonrecoverable 

You will notice a record length of 2066 and null indicators are specified at fixed position.

Mark Mulligan – (mark.mulligan.sr@sbcglobal.net) DB2 DBA

29 November 2008

Download Scripts

Introduction

Have you ever run the db2 "call get_dbsize_info(?,?,?,0)" command to get the size of your DB2 LUW database and noticed that the size is much smaller than the space used on your UNIX file systems? Do you need to monitor database growth over time to help plan for future storage requirements?

This article describes a script that will calculate the size of a DB2 LUW database on a UNIX server using UNIX commands that will give you a more accurate measure of the space used by the database. This script also stores this information in an operational table that you may use as part of your DBA Monitoring Dash Board to help you monitor and manage space in relation to growth trends.

Mark Mulligan – (mark.mulligan.sr@sbcglobal.net) DB2 DBA

16 November 2008

Introduction

If you have application jobs or users that run the DB2 load utility and would like to keep track of this information over time there is valuable information stored in the DB2 diagnostic log file that can help you accomplish this. Detail information about each table load and index build is recorded in the DB2 diagnostic log file.

This article describes the table load and index build information available and a script that will extract this information and insert it into a table that will help you monitor table load and index build performance over time. This script also extracts DB2 memory management message information (STMM) and imports that into another table.

Pros and cons

The advantage of having table load and index build information over time is that it can help you monitor and manage performance and answer questions about the performance of loads run by applications and users. If you use the DB2 automatic memory manager (STMM) then you will have information showing how this behaves over time as well.

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 sequence in such a fashion that the dependencies are taken care automatically.

A simple approach to remove check pending

Generate script using a simple SELECT statement as shown below:

Mark Mulligan – (mark.mulligan.sr@sbcglobal.net) DB2 DBA

01 November 2008

A critical part of any database disaster recovery plan is to make sure that the environment surrounding the database is the way it was before the disaster in order to provide the same level of performance and level of service to users after a recovery. This article explains a process to backup UNIX and DB2 environment information that contains important information that can be used during a disaster recovery or operational recovery. An example of a KORN shell script is provided to help you keep this information so it will be available when you need it.

Introduction

If you ever need to recover a DB2 environment from a disaster or serious operational system problem and want the database to perform like it did before the recovery, information about the environment is the fastest, most thorough way to accomplish your goal. This information is critical to your success in addition to restoring DB2 database backups that are a part of your disaster recovery plan.

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 "insert into t1 values(2,'The'),(2,'quick'),(2,'brown'),
       (2,'fox'),(2,'jumps'),(2,'over'),(2,'the'),(2,'dog')"
$ db2 "select * from t1"
LINE#       WORD                
----------- --------------------
          1 The                 
          1 horse               
          1 is                  
          1 white               
          2 The                 
          2 quick               
          2 brown               
          2 fox                 
          2 jumps               
          2 over                
          2 the                 
          2 dog                 

But, you want this to be in a single row. For example:

1 The horse is white
2 The quick brown fox jumps over the dog

SQL Server or Sybase allows you to concatenate string together. For example, you could use the following construct to get string concatenation.

Edin I. Aganagic shares his tips on how to increase incremental refresh/maintenance performance of MQTs in DB2.

1. 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 one deleted
2. If not aggregating, create the index on the set of columns that form a unique key usually by concatenating the unique key columns of the individual base tables.
3. If possible, define the base table columns used in the GROUP BY clause of REFRESH IMMEDIATE MQTs as NOT NULL makes finding matching rows in the MQT easier
4. Define RI relationships wherever applicable allows various loss-less join specific optimizations (even not enforced)
5. Always keep the base table and MQT statistics up-to-date and it helps the optimizer choose an optimal access plan

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 TESTSP("PARAM1","PARAM2")

Your previous database might allow you to pass parameters using double quotes but this will fail in DB2 since literals used in double quotes are treated as table name or column name in DB2 and hence above code will not work.

What if, if you have thousands of such statements and there will be some conversion effort for such statements. There is a workaround to this problem but you will be on your own to use this workaround as this is not documented and you are not likely to get any support for this from DB2.

It is strongly advised to be very careful if attempting to use either of these work around. They are not recommended for general implementation and may cause undesirable behavior in some cases.

You can add following entries in db2cli.ini file for this to take effect.

PATCH1 = 2048

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 the concept of a table function that you can write. Here is one C table UDF example that reads a file and returns that data through your SQL statement.

Download sample code ufd.zip

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 was supplied as an INT. You must supply julian day as '2008075' and it must be CHAR(7).

$ db2 values date('2008075')

and you should get '03/15/2008'.

What if, you want to calculate reverse date from a Julian day. For example, you should get '03/15/2008' from '2008075'.

Use the following SQL to get reverse from the Julian day.

$ db2 values cast(trim(char(year('2008-03-15')))||REPEAT('0',(3-LENGTH(TRIM(CHAR(DAYOFYEAR('2008-03-15'))))))||TRIM(CHAR(DAYOFYEAR('2008-03-15'))) as char(7))

and you will get '2008075'.

By the way, DB2 has a function called Julian_day but it gives the number of days from Jan 1, 4713 BC.

$ db2 values julian_day('2008-03-15') and you will get number of days 2454541 since Jan 1, 4713 BC.

Step-1 : Create Explain tables

See this entry http://www.db2ude.com/?q=node/65 for how to create explain tables in DB2.

Please note: You need to create explain tables in the USER-ID of the logged-in user. If you created explain plans in the schema name of some userid, other logged-in users can not use the explain tables owned by other users. In other words, each user should have their own explain tables. This is true even when you are using SET CURRENT SCHEMA command to set schema name for the tables that you are using.

Step-2 : Set parameters so that query compiler can populate explain tables

You have 2 options here:

1. Set it once at the instance level so that every procedure you create is explained
2. Set it at DB2 session level so that you control which procedures need to be explained.

Personally, I like second option.

Option - 1 : Set at the instance level

$ db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL EXPLSNAP ALL"
$ db2stop
$ db2start

Please note: Instance rebound is necessary for the DB2 registry variables to take effect.

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 database applications that does not hold row locks between selecting and updating, or deleting rows. Gain an understanding of this enhancement, and learn how applications using this programming model benefit from this enhanced optimistic locking feature and gain improved concurrency.

Pessimistic vs. optimistic locking