Skip to content

Blog Category: DB2

IBM DB2 related entries
DB2 The maximum number of open files has been reached
Category: DB2 Published: 2009-03-18

After a fresh install of DB2 Express C (free download from IBM) 9.5.2 on Linux AMD 64bit, I got the following error trying to load some tables for testing: SQL3500W The utility is beginning the “LOAD” phase at time “01/22/2009 07:40:27.557594”. SQL0958C The maximum number of open files has been reached. SQLSTATE=57009 After some investigation, […]

DB2 Query Writing Best Practices
Category: DB2 Published: 2010-04-08

Problem Writing efficient SQL SELECTqueries for DB2 can be tricky. Some general SQL guidelines apply, and there are also specific practices that apply only to DB2. Solution Here is a list of tips you can use to write good SELECT queries: Avoid SELECT * in your queries. Instead, state the columns you want to select. […]

DB2 INSERT INTO vs SELECT INTO
Category: DB2 Published: 2009-01-27

In DB2 if you need to populate a table, you need to use INSERT INTO, like in this example: INSERT INTO new_table SELECT col1,col2 FROM source_table WHERE col1=’something’; and if you need to populate query variable, you need to use SELECT INTO, like in this example: SELECT * INTO :var1, :var2, :var3FROM table_name WHERE col1= […]

DB2 Query Optimization: Custom Stats Collection
Category: DB2 Published: 2008-10-09

One of the challenges in query optimization is to make sure that the query optimizer works with accurate estimates on cost of the different possible decision branches in the optimization process.   The accuracy of the estimates depends on the efficiency of the stats collection.  DB2 assumes the data distribution is constant distribution. And that’s why […]

DB2 Table Structure Alterations
Category: DB2 Published: 2008-10-09

When an attribute on a table is dropped, or the data type changes, DB2 sets the table into REORG PENDING state.  That prevents a number of operations on the table, which can be problematic for a production database.  If there is no way around such a structural change, then a REORG command needs to be […]

DB2 Discovery Kit
Category: DB2 Published: 2009-12-29

I have posted a number of tips to use DB2. If you haven’t used DB2 before, it is one of the best databases out there, and best of all, it is available completely free from IBM. Get your DB2 discovery kit from IBM. As Grant Allen puts it in Beggining DB2, “IBM has even thought […]

Improving Retrieval Times on DB2 Indexes
Category: DB2 Published: 2009-05-31

These ideas are derived and quoted from “Best practices for tuning DB2 UDB v8.1 and its databases” CREATE INDEX has a number of options that allow for significant performance gains: Optimizing for SELECT: use PCTFREE 0 if index is on a read-only table. Optimizing for ascending or descending ordering: use ALLOW REVERSE SCANS to allow […]

PostgreSQL to DB2 export conversion
Category: DB2 Published: 2009-03-26

PostgreSQL and DB2 use different styles of conventions to represent NULL. In PostgreSQL, the convention is to have “N” in the data to be loaded with the COPY command. In DB2, the LOAD command can be set to consider and empty field as NULL. Thus, to convert from DB2 to POstgreSQL style data dump, assuming […]

Clearing Locked Applications in DB2
Category: DB2 Published: 2009-02-19

When developing queries and stored procedures, it is possible to lock up access to the database. This can happen most frequently when autocommit is off, and a transaction is left hanging in a window.  If all else fails, the application that is causing the lockup can be identified and forced with: LIST APPLICATIONS; FORCE APPLICATION (<application […]

How to delete records with duplicate fields, such as dupplicate emails?
Category: DB2 Published: 2009-05-03

To delete records where only a field is duplicated, we can use a similar technique to the general duplicate removal technique: CREATE TABLE temp AS SELECT DISTINCT ON (email) * FROM table_to_deduplicate; DROP TABLE table_to_deduplicate; ALTER TABLE temp RENAME TO table_to_deduplicate; Be mindful of the fact that this process will not preserve the constraints on […]

Compiling C code on Windows 7 for DB2 9.7 64bit
Category: DB2 Published: 2010-04-21

Problem The %DB2PATH%\SQLLIB\samples\c directory on windows contains many examples of C routines to extend DB2 or to work with DB2 from C programs. This article describes how to compile the sample code for 64 bit. Solution Set the environment (VC includes the batch file that allows to set the environment variable): C:\progra~2\micros~2.0\vc\vcvarsall.bat x64

DB2 Large Updates Without Logging
Category: DB2 Published: 2008-10-09

We have large amounts of data, and in development we have to often to bulk insert and updates by the millions. DB2 allows us to dramatically speed up these operations by allowing to not log them.  The trick is to use: ALTER TABLE <table_name> NOT LOGGED INITIALLY However, one needs to be careful using this […]

Escape SQL apostrophe
Category: DB2 Published: 2008-10-15

Many drivers have ways to escape SQL strings to make sure no malicious activity is going on.  Usually you can use a function in the driver that can take care of that.  However, if all you need is too escape a single quote, you can also use the double quote method:  reliability is key to […]

DB2 List of Tables
Category: DB2 Published: 2009-03-17

To retrieve to list of tables in DB2, you need to use the system tables, and then a little bit of sed.  Here is how it goes: db2 “SELECT tabschema || ‘.’ || tabname FROM syscat.tables” | sed -e ‘/ //g’ You can redirect this into a file, and you may need to cut off […]

DB2 crashes when LOAD used on generated field
Category: DB2 Published: 2009-04-13

When trying to LOAD table data where one of the rows are generated, DB2 9.1 and 9.5 on Linux and AIX will crash if the generated field uses a user defined function (fenced or unfenced).  IBM is aware of the issue, and the following APARs exist to track the problem: V9.1 APAR is IZ48197 V9.5 […]

Installing Informix IDS 11.x on Mac OS X
Category: DB2 Published: 2010-09-19

Problem Installing IDS 11 on Mac OS X Solution After installing the server, update the sqlhosts file and replace the default host name by * so that the server listens on all IPs. The sqlhosts file may be names sqlhosts.ol_server_name, and its contents would look like: ol_server_name onsoctcp * ol_server_name dr_server_name drsoctcp * dr_server_name References […]

Installing Product License for DB2
Category: DB2 Published: 2009-08-05

In a previous blog post I described how to upgrade to DB2 9.7. However, once the upgrade is complete, the old 9.5 license becomes invalid and a new license needs to be loaded. Preferable before you fire up the new version. Loading the new license is quite simple: Download the license file from IBM (it […]

Upgrading DB2 LUW from 9.5 with XML Extender to 9.7
Category: DB2 Published: 2009-07-19

Summary The IBM documentation has good information on how to do the upgrade in general, and how to go through the preparation steps. Unfortunately the information about XML Extender is sparse, and all you get is ADM4104E One or more databases are enabled for XML Extender. You must remove the XML Extender functionality from the […]

Connection pooling with mod_perl
Category: DB2 Published: 2009-05-31

I found this info in the PostgreSQL archives. Here are  2 methods: Best method from Dan Lyke: Apache::DBI will pool across Perl programs, and you don’t have to change anything in your scripts. Next best method from Gilles DAROLD: in your perl script use the following code use vars qw(); ||= DBI::connect(, , ); […]

DB2 9.7.2 Log files on Windows 7
Category: DB2 Published: 2010-06-12

Problem Since Windows Vista (that includes Windows 7), the place where programs store some of their information has changed. This makes finding the DB2 log files (equivalent of db2diag.log) more difficult. Solution The log files have moved under the directory tree node C:/Program Data/IBM/DB2/DB2COPY1/DB2. Under that directory there is a log and an events directory. […]

How to delete dupplicate records in DB2, Oracle, MySQL, and PostgreSQL
Category: DB2 Published: 2009-05-03

To delete dupplicate records in SQL, the following sequence of commands will do the trick: CREATE TABLE temp AS SELECT DISTINCT * FROM table_to_deduplicate; DROP TABLE table_to_deduplicate; ALTER TABLE temp RENAME TO table_to_deduplicate; Be mindful of the fact that this process will not preserve the constraints on the original table. So if you have indexes, […]

How to Disable Web Based System Manager on AIX 6.1
Category: DB2 Published: 2009-08-01

AIX has a neat feature that allows to manage a system completely from a browser interface. Since AIX 6.1 TL3, this interface runs on port 80, which makes it easy to find it. However there might be several reasons one might not want to run the AIX Web Based System Manager. For example: Something else […]

XQuery and pureXML in DB2
Category: DB2 Published: 2009-05-31

I just came across the following two articles that have some good pointers for using the native XML capabilities of DB2: Using DB2 XQuery to extract data mining results stored as PMML: this article is a great introduction to XQuery and Predictive Model Markup Language (PMML), a language to describe data mining models and to […]

IBM Expands in Analytics Market
Category: DB2 Published: 2009-08-05

Infoworld’s Pete Babb has an article about IBM to acquire analytics firm SPSS. Pete sees this acquisition by IBM in line with the earlier acquisition of Cognos also by IBM. He underlines that the key aspect of this acquisition is the focus on predictive analysis. IBM clearly shows a preference for solutions that work best […]

Installing Product License for DB2
Category: DB2 Published: 2009-08-05

In a previous blog post I described how to upgrade to DB2 9.7. However, once the upgrade is complete, the old 9.5 license becomes invalid and a new license needs to be loaded. Preferable before you fire up the new version. Loading the new license is quite simple: Download the license file from IBM (it […]

© 2021, All rights reserved. | NewPush LLC. | newpush.com