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

2
votes
1answer
25 views

DB2 add user with a password

I'm struggling here on how to do this I couldn't find anywhere or won't be able understand the documentation. So I'm connected as a DBA user on a DB2 client (TOAD for example) and I want to create a ...
2
votes
1answer
86 views

#Deleted as values when using MS Access to query a db2 database through ODBC

When trying to query certain db2 tables I get #Deleted for all values in the table. But for other tables it works fine. The row count of this query is equal to the row count when using the data ...
2
votes
2answers
1k views

How can I know my database version?

I need to know what is my database version. I'm using DB2 V9.7 on linux 64 bits. I'm connected to a database and i need to know if it is 32 or 64 bits. Can someone help me?
2
votes
1answer
159 views

DB2 - Why new multiple C000000x subfolders are created in Archive Log folder?

After switching to Archive mode (from Circular mode to keeping archive logs on the disk) we had C0000000 folder where archive logs were copied. Now I noticed that folders C0000001, C0000002, C0000003 ...
2
votes
2answers
10k views

Join 3 tables and return only distinct values for max datetime

I've been trying to do this the whole 2 weeks but seems like to no avail. 3 tables involved here. but am expecting the result would be distinct case no. for the same date & user. otherwise, it ...
2
votes
3answers
2k views

Not getting a result set in SSRS 2008r2 from an iSeries DB2 SQL stored procedure

After reading 'Defining an SQL procedure' section of DB2 UDB for iSeries SQL Programming Concepts V5R2 and 'Returning result sets from stored procedures' section of i5/OS Information Center, Version 5 ...
2
votes
4answers
21k views

Query to list all tables in a schema

I am using Rational Application Developer to run querys on a database. We are unable to locate anyone who has a list of the tables on the schema and so far the queries we have found to get a list of ...
2
votes
1answer
658 views

How to restore from a backup of a very old version of DB2 (version 6.1 backup)?

I am helping a client with some legacy application that is still running DB2 6.1 on Windows 2000! I am helping them migrate to newer software/hardware infrastructure. I have a backup of the DB2 6.1 ...
2
votes
1answer
2k views

DB2 equivalent to T-SQL's REPLICATE?

In T-SQL there is a method, REPLICATE(char,N), which will make a string with N values of a certain character. This is very helpful when querying into JDE. Does anyone know if there is a DB2 ...
2
votes
2answers
2k views

altering a db2 table with huge amount of data

I have a table in DB2 which is having 99 columns and 900,000 records of data. I want to alter the table with one extra column as below: ALTER TABLE MYSCHEMA.PRODUCT ADD UPDATE_DT DATE However, the ...
2
votes
1answer
50 views

Update the value when the row already exists

I have table 'A' in schema 'USER'. There are three columns in this table. below are the names of the columns: id (primary key) name age I want to insert a row in this table, but if the row is ...
2
votes
1answer
65 views

CPU Utilization by Long running Query

Is there any way to find how much CPU is getting consumed by a query running for long time in Database?
2
votes
1answer
396 views

DB2 cookbook by Graeme Birchall?

Not sure if this question is appropriate here but I believe that it is of some interest for the db2:ers of the site. Once upon a time the DB2 Cookbook by Graeme Birchall could be downloaded from: ...
2
votes
1answer
893 views

delete records in one table based on multiple column values in second table

I need to insert and delete records in table 1 based on the data in multiple columns in table 2 Example: Table1 and Table2 have identical columns = address, city, state, zip Table1 is the master ...
2
votes
1answer
116 views

How do I modify a stored procedure in IBM DB2?

I have a DB2 database and I'm using Control Center. I've found a procedure in the 'Stored Procedures' folder, which I need to modify. In MS SQL Server and Oracle you can accomplish this right from the ...
2
votes
2answers
609 views

Why do I get this error in a query with SUM?

I have a query that is the following: SELECT CMSTKY AS Customer, CMNAME AS BusinessName, WKMDL# As Model WKSLD# AS Sales FROM GPCMP INNER JOIN GPCM1P ON GPCMP.CMSTKY = ...
2
votes
2answers
1k views

When is it appropriate database design to use a separate schema?

I am developing a database which has data sourced from many different applications. In my first pass at design, I placed the staging tables each in a schema named for their source application. As ...
2
votes
1answer
6k views

How to loop through AS400 table with cursor

I have a AS400 table that contains multiple rows per person. Each row contains data concerning a transaction by the person including points assigned to each transaction. I read through all rows for ...
2
votes
1answer
4k views

How to restore IBM DB2 database from AIX to Windows?

I had made backup from remote IBM DB2 server in AIX using this command: ./db2 backup database DBEMP to /home/dbemp/backup Then I grab the backup file using FTP Client from my Windows. I run this ...
2
votes
1answer
1k views

IBM DB2 9.7, possible to temporarily disable transaction logs?

I want to clean the whole database, so I am dropping all tables and DB objects in two FOR loops, within one transaction. This makes the transaction logs become full. I could increase the transaction ...
2
votes
2answers
11k views

Do I need to RUNSTATS after a REORG in DB2?

The REORG documentation says: BUILD Builds indexes. Updates index statistics. But I have always heard REORG/RUNSTATS as if they should be paired. Can anyone verify that "Updates index ...
2
votes
1answer
2k views

How to choose working database in DB2?

In SQL server I can use the USE statement to switch between working databases. Is there a similar command for DB2?
2
votes
2answers
1k views

In DB2, how can I disable NLJOIN?

I want to indicate to the optimizer, via SET or optimization profiles, that NLJOIN shouldn't be used at all when optimizing a query. I've been looking for this for a while without any success. I ...
2
votes
1answer
630 views

What are the best practices for ETL of data from one OLTP system to another, Part 2?

Ok, I originally started a question a ways back called "What are the best practices for ETL of data from one OLTP system to another?". And as you can probably tell from most of my questions so far, ...
2
votes
1answer
1k views

What are the best practices for ETL of data from one OLTP system to another?

First off, I want to post that I am a developer and not a DBA, so I am trying to approach this from the standpoint of what is the best thing to do, rather than just assuming I can do everything ...
2
votes
1answer
2k views

What does TBSCAN GENROW mean in Explain Plan?

In a DB2 Explain Plan, what does TBSCAN GENROW (line 5 below) mean? SELECT STATEMENT ( Estimated Costs = 1.811E+03 [timerons] ) 1 RETURN 2 NLJOIN 3 [O] TBSCAN 4 SORT ...
2
votes
1answer
98 views

Can I set order of query execution?

Below query is executing in approx 5 min: SELECT COUNT(distinct B.CALL_ID), C.IVR_APPLN_NAME FROM PTDT.TDT_CALL_DATA AS A, PTDT.TDT_IVR_CALL_APPLICATIONS AS B, ...
2
votes
1answer
308 views

How to know db size by the backup? in DB2

I want to add validation for free space before restore a db2 database. I have the *.001 backup file (compressed). How do I check the estimate db size of that backup? I can't estimate because the ...
2
votes
1answer
152 views

How to fix DB2 Unsupported functionality: GENERATED BY DEFAULT

On IBM DB2 I try the following direct SQL: CREATE TABLE users ( user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1), user_name VARCHAR(16) NOT NULL, email_address ...
2
votes
1answer
852 views

How to ignore sql errors in stored procedure ? (Not handle)

I have a list of records I want to process and the cursor iterates through them from a table. The procedure just exits when it hits an insert statement and it fails. Is there a way I can ignore the ...
2
votes
1answer
827 views

Has some command that lists the privileges of a user in db2?

I'm using db2 9.7 on linux and i want to list my privileges on database... Ex: SYSADM, SECADM, DBADM, etc. So, has some command in db2 that lists the privileges of a user in db2?
2
votes
2answers
1k views

SQL3550W: … target column has been defined as GENERATED ALWAYS

I am trying to export + import data from a table in a db2 database to another. But while running the export command I am getting the following error. SQL3550W The field value in row "26" and column ...
2
votes
2answers
697 views

How can I automate copying DB2 table data between remote Servers?

I have two IBM DB2 servers at separate remote locations. I need to copy data from several tables on one database on the first server to another database in the second server. Previously I've used ...
2
votes
3answers
2k views

db2 range partitioning attach and detach issue

We have a table which is used in many select Queries, as it is be accessed by customers to fetch their information. Also they can change their information too whenever they want. We had to retrieve ...
2
votes
1answer
703 views

Preloading a Java library in DB2, similar to ORACLE's loadjava

Edit to add details to clarify, based on Chris' comment: A Java stored procedure depends on external libraries. Locally it compiles successfully, since they are added to the classpath. But when ...
2
votes
2answers
803 views

What is the recommended Buffer Pool size on a 2GB RAM PC?

I installed DB2 Express-C on a PC with 2 GB RAM and running Windows Server 2008 R2 OS. While creating a new database, there is an option to set Buffer Pool size with default set to 4K. How much is ...
2
votes
1answer
2k views

DB2: database hangs while trying to execute simple select query on specific table

No errors or warnings, database just hangs while trying to execute simple select query on specific table. Other tables are accessible until I trying to query specific (faulty) table. This should not ...
2
votes
1answer
436 views

In DB2, what are the alternative ways of evaluating an index's (or several indexes) benefit?

I'm trying to evaluate different approaches to analyzing the benefit of changing the set of indexes that are available. If I pick some representative queries from my workload, I can run EXPLAIN and ...
2
votes
1answer
149 views

indexing strategies

I am confused by indexing strategies advocated by one of the database products. Database is "DB2 for i" (iSeries/AS400). Please see following SQL and index advised by the product for the SQL. There ...
2
votes
1answer
192 views

How to use a variable date when running a db2 command in a shell script?

I need to use a date variable in a select, like bellow, but I'm getting this error: Expected tokens may include: "". SQLSTATE=42601 How can use these variables in a shell script? ...
2
votes
2answers
162 views

DB2 sorting by lowercase uppercase - problem

I'm struggling with DB2 - sorting by Polish letters with diacritics and uppercase/lowercase. When I created a database using: CREATE DATABASE P1DS8 AUTOMATIC STORAGE YES ON '/home/db2inst1' DBPATH ...
2
votes
1answer
553 views

How to change the owner of a Stored Procedure in DB2?

Let's say that I have a procedure called schema.proc1. How do I change the owner of it? I'm using db2 express-c 10.5 I've tried the following command running as the olduser, transfer ownership of ...
2
votes
1answer
851 views

How Do I Grant Permissions to Replace a Function?

I am getting the following error message (when trying to enable NOSQL in DB2 10.5, although I don't think that's really the issue): Error code: -551 DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, ...
2
votes
1answer
172 views

How to apply a regex on a column fetched from DB

I am using IBM DB2. So, I am retrieving a column named SSN from a table. I have to apply the following regex on that column: ([1-57-8][0-9]{2} |0([1-9][0-9] |[0-9][1-9]) |6([0-57-9][0-9] ...
2
votes
1answer
611 views

db2 performance issue - large number of IOPS how to determine problem?

DB2 database is on storage. We are using 9.7 Storage administrators reported that today database was doing 5000 IOPS (during past days average number was 300 IOPS). Those are disk operations from ...
2
votes
1answer
2k views

creating PKs for existing tables - altering column constraints

I am quite new to DB2, so I googled around how to ask questions correctly and so I hope I can provide all necessary information. I have an existing table A1230922.CALSONW with several (195) columns ...
2
votes
1answer
380 views

DB2 users/ beginer questions

New to this. Looking at db2 schemas, which should be equal to OS system users, right ? Doh apparently not. Except for PC,DB2ADMIN beeing on my OS system How is it, that this select * from ...
2
votes
1answer
50 views

DB2 MQT not rewriting role playing columns

If in a MQT, if I have 2 referential constraints to the same table (role playing) , usage of one of the constraints doesnt allow the query to be optimized Example FACT_SALES DIM_BILL_TO_ID --> ...
2
votes
1answer
297 views

How efficiently does iSeries DB2 use index with WHERE <>?

MS SQL dev here, this is my first attempt to optimize a DB2 query on iSeries. I was asked to help improve a huge query which consists entirely of dozens of joined subselects. Some of those subselects ...
2
votes
1answer
6k views

DB2 Communication link failure issue

I would like to get your valuable suggestions for the below problem. My Perl application connects to a DB2 database and keeps inserting data into a table. The application successfully inserts data ...