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 [...]
PostgreSQL to DB2 export conversion
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 [...]
DB2 The maximum number of open files has been reached
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 List of Tables
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 [...]
Clearing Locked Applications in DB2
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 [...]
DB2 INSERT INTO vs SELECT INTO
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 Epoch Conversion
As we have progresses with our move from PostgreSQL to DB2, we discovered that DB2 doesn’t have internal Epoch functions to deal with the epoch time format. Luckily, we are running DB2 on AIX, and most Unix tools are readily available. We tried many different variations, including writing internal Epoch conversion in SQL. Most appeared [...]
Escape SQL apostrophe
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 Table Structure Alterations
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 Query Optimization: Custom Stats Collection
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 [...]