The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix (namely AIX), Linux, IBM i (formerly OS/400), z/OS and Windows servers.

learn more… | top users | synonyms

14
votes
2answers
13k views

Why doesn't DELETE + REORG free diskspace (DB2)?

In DB2 I have a table containing large binary data. Now i purged the whole table and ran runstats, reorg, runstats, but the amount of disk space taken does not change. What could be wrong here? The ...
14
votes
1answer
314 views

Term for a one-row table?

Is there a specific term to describe a relation with a cardinality of one? I'm thinking particularly of RDBMS "special" one-row tables, for example, DUAL (Oracle), SYSIBM.SYSDUMMY1 (DB2, Derby) and ...
8
votes
2answers
1k views

Does DB2 order data by the primary key

I've recently had a discussion with a colleague who was pushing to remove order by clauses from a production query because the order by column was the same as the primary key. After a lengthy ...
8
votes
1answer
48k views

How do I grant all privileges to local db2admin with DB2 9.7 on Windows?

I have as my developer station a laptop with OS Windows XP Professional Edition, Service Pack 3. I have downloaded and installed IBM DB2 UDB 9.7 fix pack 4, of the Express-C edition. I have a local ...
8
votes
1answer
18k views

What exactly is binding in DB2?

I recently crossed over from being a Java developer to an actual DBA in our company. I am learning the ropes, so to speak, about being a DBA (which is actually somewhat of a new position for our ...
7
votes
2answers
3k views

Is DB2 more reliable and stable compared to PostgreSQL or MySQL?

Always i was using MySQL or PostgreSQL for my projects such as Web/Desktop apps. I had data loss few times and thanks to old backup i was always up and running. But for a recent project for Airport, ...
7
votes
3answers
11k views

How can you tell if a table in DB2 has a Primary Key?

We are using a tool that requires specific tables in our DB2 database to have a Primary Key defined. Is there a way using a select statement on the DB to see if a given table has one? Thanks.
7
votes
2answers
11k views

SQL Server Express vs. Oracle Express Edition vs DB2 Express-C

I have been trying to compare the features of the SQL Server Express, Oracle XE and DB2 Express-C, but find it difficult to compile all the differences into one place. Especially the current ...
7
votes
3answers
2k views

DB2 passwords with special characters

Our DB2 authentication is integrated with active directory, so the allowed passwords are controlled by it's policy. We have some users that are not being able to connect to the database through ...
7
votes
3answers
4k views

DB2 equivalent for MySQL's DROP .. IF EXISTS

Is there an equivalent in DB2 for MySQL's (or PostgreSQL's): DROP TABLE IF EXISTS sometable;
6
votes
6answers
12k views

Emulate REGEXP like behaviour in DB2 SQL

I posted the same on stackoverflow (please, let me know if I have to delete one). I'm working on a DB2 database and as far as I can see regexp is not supported (without additional libraries). So I ...
6
votes
7answers
4k views

On DB2, who updated a record and what did the record look like before the update?

(SQL Server 2008 R2 Standard, database under full recovery) I have a table with fields id, firstname, lastname. A statement is executed: insert into dbo.sometable (id, firstname, lastname) values (1,...
6
votes
2answers
2k views

Stored Procedures Best Practices: Fenced or Unfenced?

I believe I understand the reasons behind fenced and unfenced stored procedures. Fenced run "outside" of the database (in our case DB2) so as to prevent possible corruption of the database engine ...
6
votes
1answer
103 views

What is the meaning of `(ORDER BY x RANGE BETWEEN n PRECEDING…)` if x is a date?

In another thread: http://stackoverflow.com/questions/37759659/db2-query-to-find-average-sale-for-each-item-1-year-previous the OP wanted a sliding average for the last 365 days. Using ROWS ...
6
votes
1answer
20k views

Cannot connect to DB2 database after restoring an offline backup

This is for DB2 9.7 Enterprise Server Edition on AIX. I had a full, offline backup taken of several databases within our instance in a performance environment. I wanted to restore these databases to ...
6
votes
1answer
2k views

Is there a way to edit the System Database Directory for DB2 to remove a database?

We have a case where we want to try to get rid of a database. The database still exists on disk and when we catalog it, it re-appears in the Database Manager. But when we try to do a DROP on it, it ...
5
votes
3answers
1k views

Learning DB2 without a local install

I will be starting a new job in about 5 weeks, and want to spend some private time really getting to grips with as many of DB2s features as possible, as it is the DB used in the new company. Am I ...
5
votes
3answers
9k views

DB2 will not INSERT into Created Temp Table that I created

I normally use SQL Server but for this project I'm having to learn DB2. If I can get the below code to work I'm set for the whole project. Below is a elementary example of what I cannot get to work. ...
5
votes
2answers
4k views

List DB2 backups

Is there a way to list the DB2 backups? All I can find is db2 list history backup all for <dbname> but I think you need to check through all of those to see if they've since been deleted. This ...
5
votes
2answers
4k views

Why do I get leading zeros when converting from decimal to char in a db2 query?

I have a column named length defined as: TYPE_NAME: DECIMAL DATA_TYPE: 3 COLUMN_SIZE: 9 BUFFER_LENGTH: 11 DECIMAL_DIGITS: 5 NUM_PREC_RADIX: 10 When I run the following select: select char(...
5
votes
1answer
1k views

Slow SQL Server query using a DB2 linked server in virtual environment

I have a problem with a slow query which uses a linked server. Let me list out my environment first and I'll get to the issue: Virtual Windows 2003 R2 server (fresh deployments on XenServer 6.1 &...
5
votes
1answer
207 views

Will I need more system resources to run DB2 instead of MySQL?

I need to convert my web application from MySQL to run on DB2. I need to know in advance whether I will need a high spec server to ensure the web application performs at the same speed as it does now ...
5
votes
2answers
40k views

How to grant all privileges on all tables in a schema to a user in IBM DB2?

At first place, I want to export database from IBM DB2 AIX into IBM DB2 windows. Unfortunately, I can't use BACKUP and RESTORE command because of difference OS issue. So I have to resort to db2move ...
5
votes
1answer
4k views

BLOB on DB2 v9 on z/OS

What are cons and pros of using BLOBs on DB2 v9 (or later) on z/OS? Clarification: are there some limits (like influence on reorg, HA)? We would like to store large amount of files: 10 millions in ...
5
votes
1answer
734 views

DB2 developers can't create table in own schema but SQL and Oracle devs can. Justified?

As a developer new to DB2, I was surprised when I discovered that I could not create my own tables in the development database environment. So, I posed a question to the db2 DBA. I don’t want to be ...
5
votes
1answer
2k views

How do I resolve this data source connection issue with Cognos?

We have (fairly recently) installed IBM's Cognos 10 as our business intelligence reporting tool. Most of the databases that we report over are DB2 9.7 LUW Enterprise Edition. We installed the DB2 ...
5
votes
1answer
98 views

Measure Performance for INSERT, UPDATE and DELETE commands on DB2

I have a DB2 database with a table for daily transactions, which is cleared to a history table at the end of the day, every day. I insert rows to this table via java-JDBC INSERT commands and they ...
4
votes
4answers
6k views

What is the purpose of having a table NOT LOGGED INITIALLY

I came across this recently. When defining a table in DB2 LUW (at least 9.5 or higher), you can define it as NOT LOGGED INITIALLY. Example from the book I read: CREATE TABLE products ( productID ...
4
votes
2answers
24k views

How to backup IBM DB2 Database online?

I tried to backup the IBM DB2 (LUW) Database using this command: ./db2 backup database DBEMP to /home/user1/db2bkup But I got this message: SQL1035N The database is currently in use. SQLSTATE=...
4
votes
1answer
11k views

query to find closest lesser date

I have a table with rates. each rate has an effective starting date. (the ending date of any rate is implied by the existence of a record with a newer effective starting date.) I also have a table ...
4
votes
2answers
2k views

DB2 Express-C on Mac OS X

I have followed all the steps described on the Getting Started with DB2 Express v9.7 e-book and installed DB2 Express-C on my Mac. The installation succeeded without a problem. I did setup OS X ...
4
votes
2answers
3k views

What specific tests need to be performed under Database migration from DB2 to Oracle?

Currently I am going to be testing (I am a Data Analyst) of a larger migration project where they are planning to migrate from DB2 to Oracle. My question may be little broader; however any ...
4
votes
2answers
922 views

Isolation Level for a Data Warehouse

We are building out a data warehouse (and also some data marts as well) in our organization. My DBA skills have mostly been on OLTP type applications, but I am moving into supporting OLAP for our ...
4
votes
2answers
1k views

Memory limitations on db2 express-c version 10.1 seems stuck at 2 gig, why?

I just updated from 9.7 to 10.1, to leverage the new 4gig memory limitation. However, my app only appears to be using 2. So i searched for a way to find out how memory is used and found this command:...
4
votes
2answers
595 views

Can I optimize for the zIIP processor?

Is it possible to change SQL in a z/OS mainframe COBOL application so that it becomes eligible to be directed to the IBM System z Integrated Information Processor (zIIP)?
4
votes
1answer
5k views

eliminate extra spaces in VARCHAR columns

In a table, I have an ID column defined as BIGINT(8) and one VOUCHER column that is VARCHAR(100). No matter the actual length of the value in that field, the SQL always returns a full length value, ...
4
votes
1answer
1k views

What are the available options to backup DB2 Express-C?

I want to use DB2 Express-C edition with my application. I have never used DB2 but now want to use the Express Edition in a production environment to learn more about it. I want to know what are the ...
4
votes
3answers
1k views

Single versus Multiple databases

I am working on a hospital management system using DB2 with pureXML. Is better to use a single database for all the data (patient, doctor details, appointments, history, medicines, etc) or to use ...
4
votes
1answer
81 views

Can you control what tags are used in an XML column?

We allow our developers to use XML columns to store data. We want to provide standard names to XML tags. As a DBA, can we prevent the users from adding XML tags that are not predefined?
4
votes
1answer
77 views

How to extract the structure out of DB2 (Back up Structure)?

Is there a way in db2 to take a backup of the database structure (Metadata) without actual data? My purpose is to pull structure (tables, views, indexes, triggers, procedures and functions) from an ...
4
votes
1answer
102 views

DB2 - what happens when SQL archive log file reches its maximum value (S9999999.LOG)

we have DB2 in archive mode. archive log files are created and deleted from the disk. What will happen when archive log file reaches its maximum value for the name (S9999999.LOG) will it start to ...
4
votes
1answer
1k views

How to UPDATE just one record in DB2?

In DB2, I need to do a SELECT FROM UPDATE, to put an update + select in a single transaction. But I need to make sure to update only one record per transaction. Familiar with the LIMIT clause from ...
4
votes
2answers
3k views

DB2: Error when using NOT EXISTS with SYSIBM.DUAL in a batch query

I have data in a java object as data1, data2. data1 and data2 together forms a composite key in myTable where I want to insert the object. The writing is happening as a batch. Like 10 insert ...
4
votes
1answer
924 views

Help with DB2 Procedure

I have the following query: CREATE PROCEDURE MYPROC( IN @TABLES VARCHAR(500)) DYNAMIC RESULT SETS 1 LANGUAGE SQL SPECIFIC MYPROC NOT DETERMINISTIC MODIFIES SQL DATA CALLED ON NULL INPUT BEGIN ...
4
votes
2answers
15k views

db2 database alias not listed via list database directory but still unable to create that database

I am trying to create a database by logging on to the linux server on which the db2 server is installed, as the instance owner db2 => list database directory System Database Directory Number ...
4
votes
1answer
520 views

Specifying the node to connect in db2

I have 2 nodes in my db2 catalog. How would I include the node on which I want to connect inside the db2 connect paramters? Node 1 entry: Node name = DB2HOST1 Comment ...
4
votes
2answers
2k views

Replicate DB2 table to SQL Server 2008

I need to replicate data from a DB2 database into a SQL Server Database (along the lines of about 80 tables). I can create an SSIS package, to just flat copy the data from one to another pretty easy, ...
4
votes
1answer
423 views

can i backup multiple db2 databases at the same time?

in db2 9.5 LUW i have one db instance to which i just added a second database. we don't have TSM or multiple partitions which is what all the documentation seems to be about when i search the web for ...
4
votes
2answers
620 views

What is the best practice for mapping from natural keys to integer-based keys? (ETL)

This question originates more from an "enterprise architecture" point of view. (Reason being, our company is just starting to get into data management, and actually having DBA's -- I know, horrific ...
4
votes
1answer
65 views

Encrypt data with DES_CBC_PKCS5 in DB2

I am using encryption algo DES/CBC/PKCS5Padding. How do I encrypt data using this in DB2? In Pl/SQL I used DBMS_CRYPTO package with DES_CBC_PKCS5 from DBMS_CRYPTO Block Cipher Suites - https://docs....