Tag Archives: DB2
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 ‘/ … Continue reading
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 … Continue reading
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 … Continue reading
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 … Continue reading
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 … Continue reading
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 … Continue reading
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 … Continue reading
DB2 Large Updates Without Logging
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 operation by allowing to not log them. The trick is to … Continue reading