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.
10
votes
1answer
205 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 ...
6
votes
6answers
2k 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 ...
6
votes
2answers
844 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, ...
6
votes
2answers
936 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;
5
votes
2answers
2k 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.
5
votes
1answer
3k 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 ...
5
votes
1answer
140 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
474 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 ...
5
votes
1answer
404 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 ...
5
votes
1answer
3k 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 ...
5
votes
1answer
517 views
Is there a way to edit the System Database Directory for DB2?
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
1answer
1k 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
746 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 ...
4
votes
2answers
102 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 ...
4
votes
2answers
2k 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 ...
4
votes
2answers
240 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
3answers
424 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
2answers
148 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
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 ...
4
votes
2answers
862 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
62 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
437 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
159 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
1answer
716 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
155 views
How do you set the pagesize, codeset and table space when creating a database in DB2?
I want to create a database in DB2.
I found some information on this page
I want to use only some options which are explained on the page:
PAGESIZE
CODESET
TABLESPACE
Then, I want to ...
3
votes
2answers
3k views
Emulate REGEXP like behaviour in 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 ...
3
votes
2answers
88 views
Searching for pairs of date/time values in a single row
I have a legacy IBM DB2 database table that contains lots of records of phone calls. It has columns for ID, customer number, employee who took the call, etc. The date/time for each call is kept in a ...
3
votes
1answer
64 views
Dutch characters in DB2 problem
Ok, first of all, I have really limited knowledge about databases.
Problem that I have is next: I inserted few rows with data that contains Dutch characters, such as names of different countries. ...
3
votes
2answers
1k 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 ...
3
votes
2answers
125 views
DB2 error - SQL Statement too long or complex
Getting this error in a DB2 production system:
ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0101 - SQL statement too long or complex.
The statement has a large number of OR ...
3
votes
2answers
120 views
IBM DB2 v9.7 HADR TSA failure on the standby server
I have an environment here where I have 2 servers running DB2.
One is the primary and the other one is standby. They replicate with HADR and failover is made automatically by TSA.
What's the default ...
3
votes
1answer
164 views
DB2 Express-C 10.1 database move to DB2 Work group Server/Enterprise Edition
I have not done such migration before and I want to know if I am going to face such issues when moving my database (tables,views,table-spaces,functions,procedures,etc.) to other DB2 distribution.
If ...
3
votes
1answer
197 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 ...
3
votes
1answer
397 views
DB2: Is it good to create the index table space to use 32 KB data page
I found our DB2 server configs all the index into one table space. And it then defines the table space to use 32kb data page. Is this good? How to judge it? Any advice is welcome.
3
votes
1answer
739 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, ...
3
votes
1answer
540 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 ...
3
votes
1answer
58 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?
3
votes
1answer
792 views
DB2 9.5 How to get tablespace monitor information?
How can I get information about tablespace in DB2 9.5?
I need to review information about if the autoresize option is enabled or disabled.
3
votes
1answer
173 views
What is object Server?
As I was reading article I came across IBM's site about object Server. What actually is Object Server and what are it's uses? Can this be explained for a beginner?
3
votes
1answer
381 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
...
3
votes
2answers
329 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 ...
3
votes
1answer
633 views
Filtering on a virtual column from a UNION ALL with LEFT OUTER join causes a table scan?
I asked this question on SO but havn't got many bites and even a vote to close for being "Off Topic." So, I thought this stack exchange might be better suited.
Consider the following SQL...
SELECT ...
3
votes
1answer
1k views
How to find out what caused the database(DB2) in a locking state
I know how to check the DB is in a lock state or not, but my question is how to find out which query caused the lock on table /db.
IS there any log file getting created if something unexpected ...
3
votes
1answer
10k 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 ...
3
votes
2answers
260 views
DB2 to require password each time
I am using db2inst1 to connect to a database in DB2 which I have installed on my machine. Therefore, db2inst1 user does not require username/password authentication (borrows them from the OS). I would ...
3
votes
1answer
519 views
DB2 Defining remote database alias
I'm using DB2 9.7.0.3 for AIX.
I want to select data from a remote database, also running DB2 9.7.0.3 for AIX, and insert it into the local database I'm connected to.
I believe the query for this ...
3
votes
2answers
856 views
Problem installing DB2Express
I installed DB2 Express 10.1 on my Windows 7 (64bit) notebook. Now after the installation finished I cannot connect to the database. During the installation I specified "C:\Daten\db\db2" as the ...
2
votes
2answers
451 views
Generalisation in databases
I am working on a Hospital Management project in DB2. I have one table for staff with a primary key of staff_id. The hospital has to have different staff... e.g doctors,nurses,e.t.c.
Is it better to ...
2
votes
2answers
571 views
CREATE INDEX for long columns
I am looking for alternative to create index on long column:
create table line
(
field_key integer not null,
value varchar(4000),
...
);
create index key_value_idx on line ...
2
votes
2answers
64 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 ...
