Follow these steps if you want to create MTK Java UDFs manually. 1. Locate mtkoradrop.udf in your MTK directory and drop MTK UDFs $ db2 connect to sample $ db2 -td\! -f mtkoradrop.udf 2. Remove Jar if it was installed already. You can look at the ~/sqllib/function/jar...

When DB2 agent (db2bp process) makes a connection for your CONNECT request, it stores the IP address of the client in the application ID as shown below: $ db2 list applications Auth Id Application Appl. Application Id DB # of Name Handle Name Agents --------...

How do I know what is installed in DB2? Is there a way, I can determine about hidden components installed in DB2? How do I know which language packs are installed in DB2? Sometime, you might want to know answers about above questions. Normally, you will run db2level...

If you are converting from Sybase, you might run into issues for converting INT to HEX. In Sybase: 1> select inttohex(234) 2> go -------- 000000EA (1 row affected) In DB2: DB2 => values hex(234) 1 -------- EA000000 If you want the same exact behavior from DB2, you...

Did you get this DB2 SQL1035N error if you were trying to take offline backup? The error is obvious as someone is connected to the database and you cannot take offline database backup.

There are several solution that you can take to do offline backup when you get SQL -1035 error. They are described as below:

Method – Brute Force

C:\>db2_kill
C:\>db2start
C:\>db2 backup database sample to C:\db2backup

The above method is a brute force method and is not recommended. You may get unexpected results at the expense of even corrupting the database. It is not guaranteed that above will work always. You may still get SQL1035N error even after db2_kill. You try to now kill every process with “kill -9” command and still it is not guaranteed to work. It is not at all recommended to use this brute force method.

Method – Mild Force

C:\>db2 force applications all
C:\>db2stop force
C:\>db2start
C:\>db2 backup database sample to C:\db2backup

The above method is less brute force but you may not be able to use this if there are other databases in your instance. Stopping instance to just take a database backup is not acceptable if there are other databases in the instance.

Method – Correct way

Scenario – Application server has retry logic

Let us say, you want to take offline backup of your database and there is an application server which is designed to connect to the database as soon as it becomes available. The application server has retry logic and it will keep on trying making a connection to a database but your intent is to take an offline database backup. You are stuck as you have to ask your application server administrator to shut down the application server so that you can take up the backup. This may not be sometime possible to get your application server down. The above brute force methods also do not work in this circumstance. The following is the correct way to take offline backup of the database under this scenerio.

Did somebody create a DB2 database for you and you are just getting started to work on inherited legacy? How do you find if the database was created with automatic storage YES or NO?

Let us do a simple exercise.

My laptop has two drives C: and D: and I have DB2 9 installed.

C:\>db2 create database sample2 on C:,D:
C:\>db2 connect to sample2
C:\>db2 list active databases

Output on my machine was:

                           Active Databases

Database name                              = SAMPLE2
Applications connected currently           = 1
Database path                              = C:\DB2\NODE0000\SQL00004\

Now go to your windows explorer and check following folders:

C:\DB2\NODE0000\SAMPLE2
D:\DB2\NODE0000\SAMPLE2

In Sample2 directory on C: and D:, you have table spaces created on 2 storage paths that you specified when creating SAMPLE2 database.

Check folder C:\DB2\NODE0000\SQL00004\ and this is where your database was created. How do you know how SQL token SQL0004 is linked to the SAMPLE2 database? You got this from the output of LIST ACTIVE DATABASES command.

How do I know about storage paths if some one else had created the database for me? This simple exercise should help you.

Method – A

C:\>db2 connect to sample2
C:\>db2 update monitor switches using bufferpool on lock on sort on statement on table on timestamp on uow on

With above command, I turned on monitor switches for this current DB2 session. I discuss this elsewhere to explain it in more detail. It is not necessary to turn on all switches and above was just an example.

C:\>db2 get snapshot for all on sample2

You will see the following as a part of your snapshot output.
Number of automatic storage paths          = 2

Automatic storage path                     = c:

      File system ID                       = 2149054632

      Storage path free space (bytes)      = 50386874368

      File system used space (bytes)       = 49438912512

Did you ever wonder what is cursor blocking or row blocking in DB2?

Cursor or Row blocking in DB2 is a way to fetch number of rows in a single operation. This can improve performance of the applications.

There are 3 types of blocking which can be used for your cursors.

• ALL – Cursors specified with READ ONLY and FOR UPDATE OF clause. Blocking of rows will occur.
• NO – Blocking of rows will not occur
• UNAMBIGIOUS – Cursors specified with READ ONLY clause. Blocking of rows will occur.

You must have heard the term AMBIGUOUS CURSOR. What is it? An ambiguous cursor is the one where DB2 does not know if you want to do updates or not and hence it assumes that you are going to do the updates. DB2 treats ambiguous cursors as updatable.

Say for an example, this line of code

DECLARE c1 CURSOR FOR SELECT * FROM EMPLOYEE;

will open an ambiguous cursor and db2 assumes that you will update the rows. But if you say

DECLARE c1 CURSOR FOR SELECT * FROM EMPLOYEE FOR READ ONLY;

DB2 will open an unambiguous cursor for you.

The READ ONLY cursors are unambiguous as a rule of thumb. You may not say explicitly to DB2 to open a cursor as READ ONLY but DB2 may open a cursor as READ ONLY (unambiguous cursor) based upon your query if you are using SORT using full table scan etc.

The locks will be obtained for ambiguous cursors based upon isolation level in effect. If your application is not updating the rows, it is much better to DECLARE those cursors as unambiguous by adding FOR FETCH ONLY or FOR READ ONLY clause to improve concurrency.

But we deviated from our discussion of BLOCKING of cursors to explain about the difference between ambiguous and unambiguous cursors. So, you introduce BLOCKING so that DB2 can fetch the rows in bulk by setting BLOCKING option. This can be set when you pre-compile your embedded SQL program by specifying BLOCKING parameter to either ALL or NONE or UNAMBIGUOUS as explained above.

DB2 provides a tool db2dclgn that you can use effectively as an aid in your C/C++ or Java coding efforts. The example shown here is for embedded SQL in C.

Create a table for demo

CREATE TABLE "EXE"."CUTION"  (

"UT_UD" BIGINT NOT NULL ,

"UT_ALLOC_ACCT_UD" BIGINT ,

"UT_APP_ID" VARCHAR(32) ,

"UT_BRKR_PARTY_UD" BIGINT ,

"UT_CAP_UD" BIGINT ,

"UT_CLEAR_PARTY_UD" BIGINT ,

"UT_CLT_PARTY_UD" BIGINT ,

"UT_CMT_TXT" VARCHAR(128) ,

"UT_CNTRA_BRKR_PARTY_UD" BIGINT ,

"UT_CNTRA_CLRFM_PARTY_UD" BIGINT ,

"UT_CNTRA_TRDR_ID" VARCHAR(32) ,

"UT_CROSS_TYP_UD" BIGINT ,

"UT_DAY_AVG_PX" DOUBLE ,

"UT_DAY_CUM_QT" DOUBLE ,

"UT_DESK_UD" BIGINT ,

"UT_EVENT_TRANS_TS" TIMESTAMP ,

"UT_EVENT_TYP_UD" BIGINT ,

"UT_EXEXGRP_UD" BIGINT ,

"UT_UT_ID" VARCHAR(64) ,

"UT_GVUP_BRKR_PARTY_UD" BIGINT ,

"UT_INST_UD" BIGINT ,

"UT_MKT_LQDTY_UD" BIGINT ,

"UT_PX" DOUBLE ,

"UT_PX_CCY_UD" BIGINT ,

"UT_RSPBL_TRDR_PRSN_UD" BIGINT ,

"UT_SETTL_CCY_UD" BIGINT ,

"UT_SETTL_TYP_UD" BIGINT ,

"UT_SHARE_QT" DOUBLE ,

"UT_SIDE_UD" BIGINT ,

"UT_ORD_AVG_PX" DOUBLE ,

"UT_ORD_CUM_QT" DOUBLE ,

"UT_TRAD_ACCT_UD" BIGINT ,

"UT_TRANS_STAT_UD" BIGINT ,

"UT_TRANS_TYP_UD" BIGINT ,

"UT_UPSTRM_SYS_UD" BIGINT ,

"UT_TRD_DATE_YMD_PART" INTEGER NOT NULL ,

"UT_ENTRY_MINUTE_PART" SMALLINT ,

"UT_MSG_JNL_UD" BIGINT ,

"UT_CR_PROC_ID" VARCHAR(32) NOT NULL ,

"UT_CR_USER_ID" VARCHAR(32) NOT NULL ,

"UT_CR_TS" TIMESTAMP NOT NULL ,

"UT_SUBSYS_UD" BIGINT ,

"UT_DNSTRM_SYS_UD" BIGINT ,

"UT_MKT_SYS_UD" BIGINT

); 

INSERT INTO EXE.CUTION VALUES (1234567890,987463453,'UTILITY',4353262,

NULL,NULL,NULL,'How are you',

NULL,353652326,'STATION',643632,

5325.436436,457437.437437,326326326,CURRENT TIMESTAMP,

NULL,NULL,'DURHAM BULL',43643373,

NULL,NULL,NULL,NULL,

3532525,3636363,34335,365363.4848,3636262,

363632.36363,4874743.37373,436363262,3636226,

NULL,NULL,3242,23,

NULL,'DB2UDE','DUKE',CURRENT TIMESTAMP,

3532652,4636326,4763763);
INSERT INTO EXE.CUTION VALUES (35367890,987463453,'SOOKING',4353262,

Did you ever know how powerful MERGE is in DB2? Let me demonstrate that to you through this simple example.

Create a table

$ db2 connect to sample
$ db2 "create table fmtmerge 
      (c1 int not null generated always as identity primary key, 
       c2 decimal(13,4) not null, 
       c3 varchar(30), 
       c4 char(6), 
       c5 int, 
       c6 timestamp)"

The column c1 is a primary key which is also generated by default using IDENTITY feature of DB2 similar to Sybase or SQL Server. You could also do same thing using DB2 SEQUENCES same way you do in Oracle.

Let us say that I get data from other systems on a daily basis and I need to merge the incoming data with FMTMERGE table. The merge is going to be either simple UPDATE or on failure INSERT.

DB2 has a feature called multi-row INSERT and this can be used in this context too. Using VALUES clause, you can actually generate a table on the fly in DB2.

Let us do that first before we jump to MERGE.

Multi-rows in DB2

$ db2 "values (cast(1 as int),cast(2 as int),cast(3 as int)),(3,4,5),(4,5,6)"

1           2           3          
----------- ----------- -----------
          1           2           3
          3           4           5
          4           5           6

$ db2 "select * from (values (cast(1 as int),cast(2 as int),cast(3 as int)),
       (3,4,5),(4,5,6))as t(c1,c2,c3)"

C1          C2          C3         
----------- ----------- -----------
          1           2           3
          3           4           5
          4           5           6

In the first example, we just created a tables using VALUES on the fly. In the second example, we used VALUES in SELECT and gave it a name and assigned each column a name also. Isn’t that nice?

Now, we will use same approach of VALUES to merge table that we created above.

MERGE statement

db2 "merge into fmtmerge t using (values

     (1, 12345.34, 'Raleigh', 'NCSU', 34234, NULL),

The following program is an example of CLI and embedded SQL statements in the same C program to perform array inserts to achieve high performance. Why do you need to use a mixture of CLI and embedded SQL program? Sometimes, it is desirable from the coding perspective as you use the power of CLI to use array inserts, import, export and load tables and use normal embedded SQL statements as usual.

For an example, if you want to perform an array insert using dynamic SQL statements in your embedded SQL program, you will have to copy all of your array variables to host variable and supply all those parameters through EXEC SQL EXECUTE dynstmt USING :hostvar1, :hostvar2 etc. etc. The code becomes unnecessary long and tedious. For such high performance cases, just use CLI approach and embed your array inserts just within your code and do mix and match of the coding. This way, you can write high performance programs.

Download Array Insert
Download Compile Commands

Column-wise Array Insert CLI/Embedded SQL Program

#include <stdio.h>

#include <string.h>

#include <stdlib.h>

#include <sqlcli1.h>

#include <sql.h>

#include <sqlenv.h>

#define MAX_UID_LENGTH 18

#define MAX_PWD_LENGTH 30
#define ARRAYSIZE 10

#define ITERCOUNT 10000

#define COMMITCOUNT 100
int arraysize = ARRAYSIZE, itercount = ITERCOUNT;

int commitcount = COMMITCOUNT;
EXEC SQL INCLUDE SQLCA;
int serverTime(SQLHANDLE hdbc, char *sTime);
int HandleInfoPrint(SQLSMALLINT, SQLHANDLE, SQLRETURN, int, char *);
/*

  $ db2 "create table fedsch.arraytab (c1 int not null,

         c2 varchar(100) not null, c3 int,

         c4 int not null generated always as identity)"

  $ db2 "alter table fedsch.arraytab primary key(c4)"

*/
int rc = 0;

/* macro for environment handle checking */

#define ENV_HANDLE_CHECK(henv, cliRC)              \

Say for an example, you want to increment a column whenever a user logs in using an application for audit purpose. Normally in a single unit of work, DB2 will guarantee the data and there will not be an issue of concurrency if you use an INSERT and do an UPDATE upon INSERT failure. Instead of having 2 SQL statement and a check on SQL error on INSERT, you can use a single MERGE statement to accomplish same.

Source file showing DB2 MERGE

$ cat merge.sqc 
#include <stdio.h>
#include <string.h>
#include <sqlca.h>

/*
db2 "create table fedsch.term (userid varchar(10) not null, cnt int not null)"
*/

#define NAME_COLUMN_SIZE 27 

int main()
{
  int i = 0;
  EXEC SQL INCLUDE SQLCA;

  EXEC SQL BEGIN DECLARE SECTION; 
     char sqlBuffer[1000];
     char userid[11];
     char name[NAME_COLUMN_SIZE]; 
  EXEC SQL END DECLARE SECTION;

  EXEC SQL WHENEVER SQLERROR GOTO error_section; 

  strcpy(sqlBuffer, "MERGE INTO FEDSCH.TERM B USING (VALUES(CAST(? AS VARCHAR(10))");
  strcat(sqlBuffer, " ,1)) A (USERID, CNT) ON B.USERID = A.USERID WHEN MATCHED ");
  strcat(sqlBuffer, " THEN UPDATE SET B.CNT = B.CNT + 1 WHEN NOT MATCHED THEN ");
  strcat(sqlBuffer, " INSERT (USERID,CNT) VALUES (A.USERID,A.CNT)");
  strcpy(userid, "DB2UDE");
  EXEC SQL CONNECT TO SAMPLE;
  EXEC SQL VALUES CURRENT TIMESTAMP INTO :name;
  EXEC SQL PREPARE dynstmt FROM :sqlBuffer;
  for (i = 0; i < 10000; ++i)
  {
     EXEC SQL EXECUTE dynstmt USING :userid;
  }
  EXEC SQL CONNECT RESET;

  printf ("Server time is %s\n", name);
  return 0;

  error_section:
  {
      printf ("Error occured SQLCODE = %d\n", sqlca.sqlcode);
      return -1;
  }
}

makefile to compile

$ cat makefile 
DB2PATH = $(HOME)/sqllib

ERASE=rm -f

all : \
        test

clean : 
        $(ERASE) *.o

cleanall : \
        clean
        $(ERASE) *.bnd

test :
        ./prepcmd sample merge

prepcmd command

If you are writing embedded SQL application using C/C++ or SQLJ application using Java, you will come across an issue about how to use DB2 PREPARE statement effectively in your application code. When I talk to Oracle or SQL Server DBA, they all seem to have an impression that the database will cache the prepare statement and it is OK to use PREPARE anywhere in the code since database engine will not parse and compile that SQL again.

Same is true in DB2 also but even if database engine will not parse that SQL again, you still do the calculations to determine if that SQL is in cache or not. This might become an issue if you are writing a high performance application and even milliseconds of performance may add up to several minutes or hours performance bottleneck when you do millions of such transactions.

The following is a simple test to insert 100,000 rows in DB2 using a dynamic SQL statement in an embedded SQL application using C. The same principle holds good for Java or SQLJ programs.

In the first test, we will prepare the statement outside the loop and in another do the same test with PREPARE inside the loop. This is a most common application issue I have seen in many different places where perception of PREPARE is misunderstood and used incorrectly.

Sample C source

The following source will truncate the DB2 table by calling db2 import API to import an empty file to truncate the table. It prepares a dynamic SQL statement to insert rows in a table 100,000 times and measures the elapsed time.

$ cat dynsql.sqc

#include <stdio.h>

#include <string.h>

#include <sqlca.h>

#include <db2ApiDf.h>           /* sqluimpr() */

#define NAME_COLUMN_SIZE 27

#define DUMMY_FILENAME  "/dev/null"
/*

db2 "create table fedsch.fedtest (c1 int not null, c2 varchar(20) not null, c3 decimal(12,3), c4 int not null generated always as identity)"

*/
int main()

{

  int i = 0, rc = 0;

  struct sqldcol dataDescriptor;

  unsigned char actionBuffer[sizeof(struct sqlchar) + 20 + 130 + 1 + 130 + 1];

The concept of packages in DB2 conflicts with the package understanding that comes with an Oracle DBA who is expanding his/her horizon to include DB2 as an added skill-set.

Please refer to this excellent article http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0606chun/index.html about DB2 packages. In a nutshell, DB2 packages contain compiled SQL statements that DB2 engine uses to execute without having to go through the parse and compile. These type of SQLs in DB2 are called static SQL and Oracle DBAs try to simulate same by using Oracle profiles but DB2 packages are simpler to maintain and virtually they are way simpler and automated than Oracle way of creating profiles which is way complicated.

But still, DB2 packages require a little bit of understanding and knowing them will be very helpful. Please refer to the above article for other details and this article covers an issue of SQL0818N that is most commonly discussed and asked for in the DB2 forums.

When you are in the development mode, you will keep on compiling and testing your program either by using embedded SQL in C/C++ or in Java SQLJ program. In the development environment, it is recommended that you turn off KEEPFENCED parameter. When this parameter is turned off, DB2 will load package every time when it needs to execute it. But, you can not keep this parameter to NO in your production environment due to the performance reasons. If your application is of high throughput nature, the performance impact can be anywhere from 5-20 times just for this parameter.

For development environment:

db2 update dbm cfg using keepfenced off
db2stop force
db2start

For production environment:

db2 update dbm cfg using keepfenced on
db2stop force
db2start

Generally, you start getting SQL0818N error when this parameter is ON and you compile your program again and try to execute it. This complain comes from the fact that the package is already in the memory and the consistency token of the package in memory differs from the package that you just created. This leads to an issue of compiling programs in production systems. In an ideal world, DB2 developer’s pious thinking was that you would not compile a program in a production environment and the real world is not so pious. But, there is a workaround to this that will help you to keep compiling even in a production environment where KEEPFENCED is set to YES.

When you use embedded SQL in your C/C++ code, you first run PREP command to process all EXEC SQL statements in your source. The PREP step happens before C/C++ compiler is able to invoke its preprocessor that will include header files, expand trigraph and replace...

I am trying to install Tomcat to test DB2 web services and I ran into several problems on my Fedora Core 5 server to start the Tomcat server.

This is how I solved this. I found this problem widely reported but very few instructions to solve the problem.

Install Tomcat:

# yum install tomcat5 tomcat5-webapps tomcat5-admin-webapps

Try to start tomcat

# service tomcat5 start

I noticed these errors.

Starting tomcat5: /usr/bin/rebuild-jar-repository:

error: Could not find commons-collections Java extension for this JVM

/usr/bin/rebuild-jar-repository:

error: Could not find commons-logging-api Java extension for this JVM

/usr/bin/rebuild-jar-repository:

error: Some detected jars were not found for this jvm

/usr/bin/rebuild-jar-repository:

error: Could not find xml-commons-apis Java extension for this JVM

/usr/bin/rebuild-jar-repository:

error: Some detected jars were not found for this jvm

/usr/bin/rebuild-jar-repository:

error: Could not find commons-beanutils Java extension for this JVM

/usr/bin/rebuild-jar-repository:

error: Could not find commons-digester Java extension for this JVM

/usr/bin/rebuild-jar-repository:

error: Could not find commons-logging Java extension for this JVM

/usr/bin/rebuild-jar-repository:

error: Some detected jars were not found for this jvm

find: warning: you have specified the -mindepth option

after a non-option argument -type,

but options are not positional (-mindepth affects tests

specified before it as well as those specified after it).

Please specify options before other arguments.

find: warning: you have specified the -maxdepth option

after a non-option argument -type, but options are not

positional (-maxdepth affects tests specified before

it as well as those specified after it).

Please specify options before other arguments.
Using CATALINA_BASE:   /usr/share/tomcat5

Using CATALINA_HOME:   /usr/share/tomcat5

Using CATALINA_TMPDIR: /usr/share/tomcat5/temp

Using JRE_HOME:       

If you are migrating other database data to db2 and encounter a timestamp like 2007-03-07 19:30:00+05, you will need to adjust the timestamp correctly if you are in a different time zone.

I used this Java function to massage the timestamp properly so that I get the correct timestamp.

        private String convertTSTZ(String ts)

        {

            SimpleDateFormat sdf;

            String newts = "", tz = "", micro = "";

            int pos, pos2;

            Timestamp tst = null;

            try

            {

                newts = ts;

                pos = ts.length()-3;

                if (ts.charAt(pos) == '-' || ts.charAt(pos) == '+')

                {

                    newts = ts.substring(0,pos);

                    tz = ts.substring(pos);

                    if (tz.length() == 3) tz += "00";

                } else

                    pos = ts.length();

                pos2 =newts.lastIndexOf('.');

                if (pos2 > 0)

                {

                    micro = newts.substring(pos2);

                    newts = newts.substring(0,pos2);

                }

                newts = newts + tz;
                if (tz.equals(""))

                {

                    sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.US);

                } else

                {

                    sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ssZ", Locale.US);

                }

                Date date = sdf.parse(newts);

                tst = new Timestamp(date.getTime());

                newts = tst.toString();

                pos2 =newts.lastIndexOf('.');

                if (pos2 > 0)

                {

                    newts = newts.substring(0,pos2);

                }

                newts = newts + micro;

                //System.out.println("old value " + ts + " new value " + newts);

            } catch (ParseException e)

            {

                newts = "";

If you are trying to install a jar file using sqlj.install_jar command and getting SQL4302N error, you may follow these steps to fix this problem. These may not work exactly in the same manner for you but it will give you some insight as how to fix this issue. This...

When you create a DB2 instance in Unix, you have the option of specifying a fenced user-id different than instance owner. The purpose of fenced user id is to protect DB2 from untested or malicious code from the external stored procedures, user defined functions etc....

When you need to load the data on DB2 server and the data files reside somewhere else, you need to FTP or copy them to the server. You can take help from a sample Perl driver program provided in your sqllib/samples/autoloader/loadFtpFtc.pl.

Normally, you would run the DB2 LOAD on the DB2 server. But, when you try to run DB2 LOAD from a client, you need to take care of few things.

A sample DB2 LOAD script that runs well on Server.

SET CURRENT SCHEMA="ADMIN";

LOAD FROM "C:\load\logos.txt"
OF DEL 
LOBS FROM "C:\load\lobs\logos\"
MODIFIED BY  LOBSINFILE  COLDEL~ 
ANYORDER USEDEFAULTS KEEPBLANKS 
CHARDEL"" DELPRIORITYCHAR
DUMPFILE="C:\dump\logos.txt"
METHOD P (1,2,3)
MESSAGES "C:\msg\logos.txt"
REPLACE INTO "LOGOS"
(
ID,
LOGO,
SERVICERID
)
STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL
COPY NO 
INDEXING MODE AUTOSELECT
;

Suppose you want to run above LOAD operation from a client, you will need to make sure of the following.

• You need to specify CLIENT after LOAD keyword.
• DUMPFILE and LOBS FROM modifiers refer to the files on the server.
• Code page conversion is not performed during a remote load operation. If the code page of the data is different from that of the server, the data code page should be specified using the codepage modifier.

The modified LOAD command will look like this.

SET CURRENT SCHEMA="ADMIN";

LOAD CLIENT FROM "C:\load\logos.txt"
OF DEL 
LOBS FROM "C:\load\lobs\logos\"
MODIFIED BY  LOBSINFILE  COLDEL~ 
ANYORDER USEDEFAULTS KEEPBLANKS 
CHARDEL"" DELPRIORITYCHAR
DUMPFILE="C:\dump\logos.txt"
METHOD P (1,2,3)
MESSAGES "C:\msg\logos.txt"
REPLACE INTO "LOGOS"
(
ID,
LOGO,
SERVICERID
)
STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL
COPY NO 
INDEXING MODE AUTOSELECT
;

The change is only in the keyword CLIENT but you need to make sure that the DUMPFILE and LOBS FROM path folders should reside on server and not on the client.

If LOAD can not find above path, you will encounter a SQL Error SQL2036N.

The path for the file or device “C:\dump\logos.txt” is not valid.

Sometimes you might get error SQL2036N even if you try to load the data from the server itself. In that case, it is normally permission issue on the file. This happens when you do the LOAD as a non-instance user.

If you are planning to use PHP with DB2 Express-C on Linux, you will need PHP extension for the DB2. The same extension will also work for IBM Cloudscape or Apache Derby databases.

[Note: The instructions here are for the Redhat Linux and you will need slight modification in RPM SPEC file should you need to use it on other Linux distributions.]

As of writing today, the IBM_DB2 extension is not enabled by default for the Linux distributions when you install PHP. This may change in the future and until then, take help from this article for building RPM from the source or download two RPMs that I built for 32 bit RHEL 4 and 64 bit Fedora Core 5. You can also download the source RPM or source tarball and build the RPM for your Linux distribution after you make required changes to the SPEC file. I think that the changes will be very minimum.

I did not know how to create RPM from a source so these are the steps that I followed after learning it by looking how other people build the RPM. You might not want to go through this whole article if you arrived here to look only for a ready made binary RPM for the IBM_DB2 PHP extension. Read software requirement section and just go to the end of this article to download the binary RPM if it matches to your platform. If not, download the source RPM or source tarball and build it yourself. If you do not know how to build a binary RPM from a source tarball, read the last section.

1. Software requirement before you start

• You should already have either DB2 server or DB2 client installed on your machine.
• You should already have Apache or IBM httpd installed and running with PHP module enabled.
• You should already have PHP (> 4.3) installed
• If you need to build the binary RPM from the source RPM, you will also need to install php-devel. If you will be using binary RPM available from this site, you will not need php-devel.

2. How to install DB2 on your system

Few things have changed in DB2 9. For example, IBM does not use RPM to install the software any more on Linux platforms. This change might upset many who rely upon rpm-qa to query about DB2 software. If some one has installed DB2 9 for you on Linux, how would know the...

1. How to implement Java UDF using CLOB for DB2GENERAL parameter passing?

When you use DB2GENERAL parameter passing in the declaration of the SQL UDF, DB2 will use the legacy driver to map the data types between Java and DB2. If you need to pass CLOB as a parameter in your JAVA UDF, please follow these points.

1. You have decided to use the parameter style for a JAVA UDF as DB2GENERAL.
2. The Java class that you are writing should extend UDF from COM.ibm.db2.app.UDF
3. The return parameter should be the part of Java function arguments.
4. You should use COM.ibm.db2.app.Clob for Clob and not java.sql.Clob

For example, if you want to write a java UDF that will do pattern matching on a CLOB column and returns 1 if a match was found else returns 0.

// File: RegEx.java
package sp;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

import COM.ibm.db2.app.UDF;

public class RegEx extends UDF
{
    public void MatchClob(String pattern, 
            COM.ibm.db2.app.Clob clob, 
            int results) throws Exception
    {
        if (null == clob || clob.size() == 0)
        {
            set(3,2);            
        }
        Pattern p = Pattern.compile(pattern);
        Long size = new Long(clob.size());
        char[] cbuff = new char[size.intValue()];
        clob.getReader().read(cbuff);
        Matcher m = p.matcher(new String(cbuff));
        if (m.matches())
        {
            set(3,1);
            return;
        }
        set(3,0);
        return;
    }
}

Create a directory “sp” somewhere and create a file RegEx.java in “sp” folder. Compile this file using either javac or Eclipse or any other IDE of your choice.

Copy this folder “sp” with RegEx.class in sqllib\function or sqllib/function.

Use these SQL to create the user defined function (UDF) and test the function.

drop function MATCH_CLOB;

call sqlj.refresh_classes();
CREATE FUNCTION MATCH_CLOB

(

This is How I did: Eclipse, PHP and DB2 on Windows Platform

After working in Microsoft .Net technologies for few years, I made up my mind to explore PHP world. In fact, this site is hosted using Drupal framework which is in PHP and I did not know anything about PHP to make this site working.

I am a newbie in PHP so I am documenting what I did to get myself started in PHP. Of course, my choice of database is DB2 so this article is about DB2 after all.

1. What IDE to use:

Which IDE I should use? This is the first question with which I struggled since I did not want to use notepad or vi. After doing some research I narrowed my choice to Eclipse since 1. It is free and open source and 2. It is comparable to or better than Microsoft Visual Studio. For people like me who only knew about Microsoft technologies, getting into Eclipse band wagon was an eye opener. I realized how powerful Eclipse was compared to the Microsoft Visual Studio.

Download latest version of Eclipse from http://www.eclipse.org/downloads/ .
At the time of this writing, 3.2.1 was the latest version.

I unzipped the contents into C:\Eclipse and made my workspace as C:\EclipseWorkSpace instead of the default which was buried some where under Documents and Settings.

Eclipse provides the basic foundation for IDE and one of the main reason to go Eclipse route was the availability of a PHP IDE within Eclipse framework called PHP Development Tools (PDT). You can look for PDT at http://www.eclipse.org/php/ . This is still in the development and later on some stable release will be available. I used 0.7 release at the time of this writing.

You can either download the plug-in directly from the above link or use Eclipse IDE to get it automatically. I choose the second approach as I feel that it is elegant and I do not have to install the plug-in myself. Before we download the PDT plug-in, we have to also install other plug-ins that are necessary for the PDT plug-in to work.

Did you know that you can run DB2 REORGCHK command to update the statistics on the tables? Normally, you would run RUNSTATS command against each table to update the statistics. Consider using REORGCHK as shown below: To update all the user and system tables use:...

Victor Chang and others have published a white paper on row compression on DB2 9. At a high level, authors have achieved significantly better performance running DSS queries in a compressed database using only half as many disks as the uncompressed database. They have...

David Sciaraffa explains that DB2 9 uses autonomic algorithm to determine the number of prefetchers (IO_SERVERS) and page-cleaners (IO_CLEANERS) to determine the optimum values and they are described here: Prefetchers: IO_SERVERS Page Cleaners: IO_CLEANERS...

When should you create detailed distribution statistics on indexes in DB2? DB2 9 auto runstat uses following syntax for generating statistics for your table. runstats on table t1 with distribution and sampled detailed indexes all Please note that it is using...

Generally, DBAs ask the equivalent of Oracle's bitmap indexes in DB2. DB2 optimizer takes away the burden of creating / maintaining bitmap indexes as it creates them on the fly when it needs them. How do you know if DB2 optimize is using bitmap index or not? David...

In DB2 on z/OS, you can deny lock escalation (Allow row level locks to be acquired until resources are consumed and not escalate locks from row level to a table level) by setting LOCKMAX=0 in CREATE or ALTER table space. Can you do same thing on DB2 on distributed...

David Marvin explains the difference between Connection Pooling and Connection Concentrator as follows:

Connection pooling is the methodology to place a connection for later re-use after an application has disconnected (no longer requires the connection). The next application which issues the “Please connect to database xxxxx” request would be given a connection in the connection pool matching the requested database. If none exist in the pool, generate a new connection. This saves the overhead of connection tear down/connection establishment. The application itself is unaware of what is going on under the covers. Typically, Application Server products (such as WebSphere) provide the connection pooling infrastructure. A DB2 Connect Server also provides Connection Pooling of backend connections to zDB2.

DB2 UDB/Connect connection concentration (CC), on the other hand, is a capability of these products to multiplex X application connections onto Y db2 agents where X > Y. db2 agents are the DB2 processes that serve an application connection. The use of the CC within a DB2 Linux Unix and Windows (LUW) server allows reduced resource usage at the DB2 Server (fewer db2agents required). The use of the CC with DB2 Connect provides the following:

• If configured appropriately, it reduces resource usage at the DB2 Connect server as well as at the DB2 z/OS server (fewer db2agents at the Db2 Connect Server which implies fewer connections to DB2 z/OS). Configuring for reduced resource consumption is an option and is not necessary to leverage other capabilities the Connection Concentrator in DB2 Connect provides … read on
• Leverages a DB2 z/OS Data sharing / sysplex group. DB2 z/OS provides a DB2 Connect server with a weighted list of members in the sysplex refreshed after each transaction completes. New transactions / connection requests initiated by the application will be routed to the “least busy” member in order to provide load balancing in the sysplex.

Igor Nikiforov has contributed various string manipulation functions for DB2 using SQL PL. You may see this link for the original article.

You may download the script from this link.

The following string manipulation functions are included in above script.

• AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character (including overlaps). The search performed by AT() is case-sensitive. AT is similar to the Oracle function INSTR.
• RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string (including overlaps). The search performed by RAT() is case-sensitive.
• ATC(): Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character (including overlaps). The search performed by ATC() is case-insensitive. ATC similar to the Oracle function INSTR.
• RATC(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string (including overlaps). The search performed by RATC() is case-insensitive.
• AT2(): Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character (excluding overlaps). The search performed by AT2() is case-sensitive. AT2 similar to the Oracle function INSTR.
• ATC2(): Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character (excluding overlaps). The search performed by ATC2() is case-insensitive. ATC similar to the Oracle function INSTR.

IBM was little late in the relational database business on Linux, Unix and Windows platforms compared to the Oracle even though IBM was the inventor of RDBMS. With new release of DB2 9, IBM has taken the lead in innovations in relational database with the introduction of hybrid databases.

This new technology has been in research in various IBM labs for many years and it has struck the market place with a bang. This technology is so cool and if you do not agree with me, please read this brand new free book DB2 9 PureXML® guide. If by any chance, you are not able to open the link, try this link.

One of the author of this book is my friend Arthur V Sammartino and this is why you are seeing this entry here as my respect towards him. But apart from friendship, there are many neat things about PureXML® as shown below:

• XML data is stored and queried in its inherent hierarchical format. This is the biggest difference eliminating shredding of XML data which is stored as BLOB in other databases. DB2 stores and manipulates XML data in a parsed format that reflects the hierarchical nature of the original XML document.
• You can query XML data using SQL/XML and XQuery. Either use XQuery and SQL separately or embed XQuery in SQL and vice versa.
• New indexing technology to speed searches involving XML data
• Store any well-formed XML document in any column defined on the new XML data type.
• A single column can contain documents with different structures (or schemas) as well as different contents.
• Option of registering XML schemas and to validate XML documents against these schemas prior to storage.
• Enhancements in programming langauge interface such as JDBC, DB2 .Net Driver, C, COBOL, and PHP to take advantage of new XML capabilities.

Are you new to DB2? Are you making a transition from either Oracle, Sybase or SQL Server? Are you trying to learn DB2 to make your resume look better? One of the immediate challenge for you to is to learn new stored procedure (SQL PL) language quickly. But if you have already used PL/SQL or T-SQL for your stored procedures, you are already half-way through to start programming in DB2 SQL PL language.

Learn from the experts who have coded in this language. In my opinion, this is the best way and this is how I learnt different programming languages in my career.

I will point you to an article written by Serge Rielau on how to backup and restore a schema in DB2. This article explains backup and restore of a schema in a way different than a traditional DBA will do using core DB2 tools. This is very useful when you want to accomplish backup / restore of the schema programmatically using SQL PL stored procedures. Please see the article for how to do the backup / restore of the schema using the code but I am going to use this code to explain few features of SQL PL.

First of all you will put together your scripts in a file and you have to define your SQL statement terminator.


$ db2 -td@ -f script.sql



--#SET TERMINATOR @



$ db2 -f script.sql


Whenever you update statistics in your tables or modify any of following configuration parameters or bind options, you need to rebind the packages for updating the new access paths of the SQLs. Configuration Parameters that affect execution of SQL queries AVG_APPLS...

DB2 9 is a feature packed database with performance features, pureXML, high availability etc. As a DBA, you might want to know which features are being used in your database so that you are not caught with surprise. Run the attached script in your database to find out...

DB2 9 provides a brand new schema SYSIBMADM with many new views in it. This is nothing new as same capability was available through SQL Table functions but this is more elegant.

If you worked with Oracle and liked its v$ views so this is very much similar to v$ views of Oracle in DB2.

You can find out lots of information through these view and it is worthwhile to go through them. Some of the SQL queries using these views are given below:

Show Connected applications to Database

       SELECT 	AGENT_ID, SUBSTR(APPL_NAME,1,10) AS APPL_NAME,
                AUTHID,
   		APPL_STATUS 
	FROM 	SYSIBMADM.APPLICATIONS
	WHERE 	DB_NAME = 'SAMPLE';

Show top 5 most frequently run Dynamic SQL

        SELECT 	NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S,
    		SUBSTR(STMT_TEXT,1,60) AS STMT_TEXT
    	FROM 	SYSIBMADM.TOP_DYNAMIC_SQL
    	ORDER 	BY NUM_EXECUTIONS DESC 
	FETCH 	FIRST 5 ROWS ONLY;

List Tablespaces

        SELECT TBSP_ID, SUBSTR(TBSP_NAME,1,20) as TBSP_NAME, 
               TBSP_TYPE,TBSP_CONTENT_TYPE, 
               SUBSTR(TBSP_STATE,1,20) TBST_STATE
   	FROM   SYSIBMADM.TBSP_UTILIZATION;

Bufferpool hit ratio

        SELECT 	SUBSTR(DB_NAME,1,8) AS DB_NAME, 
                SUBSTR(BP_NAME,1,40) AS BP_NAME,
   		TOTAL_HIT_RATIO_PERCENT
   	FROM 	SYSIBMADM.BP_HITRATIO;

List the log utilization for the currently connected database

        SELECT 	SUBSTR(DB_NAME,1,10) DB_NAME,
                LOG_UTILIZATION_PERCENT
    		TOTAL_LOG_USED_KB, TOTAL_LOG_AVAILABLE_KB
    	FROM 	SYSIBMADM.LOG_UTILIZATION;

Get all critical log messages logged in the last 24 hours

        SELECT 	TIMESTAMP, SUBSTR(DBNAME,1,10) DBNAME,

                SUBSTR(MSG,1,80) MSG

    	FROM 	SYSIBMADM.PDLOGMSGS_LAST24HOURS

   	WHERE 	MSGSEVERITY = 'C'