Questions tagged [db2-luw]

DB2 for Linux, UNIX, and Windows.

18
votes
2answers
18k 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 ...
6
votes
1answer
408 views

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

In another thread: https://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
2answers
28k 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 ...
4
votes
2answers
12k views

Split comma separated entries to rows

I have a Table like this: | ID | OtherID | Data +--------+-----------+--------------------------- | 5059 | 73831 | 5103,5107 | 5059 | 73941 | 5103,5104,5107 | 5059 | 73974 | ...
4
votes
1answer
2k views

How do you generate the DDL that created a DB2 LUW database?

There are various options for db2look you can use to see the DDL for just about every object in the database except the command that created the database itself. Is there any way with db2look, or ...
3
votes
2answers
20k 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 ...
3
votes
1answer
161 views

Java version shipped with DB2 v11?

I can't seem to find any information of which Java version that will be used in the soon to be released DB2 V11.1. Does anyone else know?
3
votes
1answer
146 views

What might be the reason for licence compliance violation?

Much to my surprise we got a licence compliance violation for our DB2 Workgroup Server Edition installation regarding: ~]$ db2licm -g tmp.txt ~]$ cat tmp.txt License Compliance Report DB2 ...
3
votes
1answer
141 views

non-optimal use of index

I'm somewhat puzzled by the scenario below. The table involved (relevant parts) is defined as: CREATE TABLE Nya. UPSEC_COURSE ( UPSEC_COURSE_ID CHAR(11) NOT NULL, ...
3
votes
1answer
433 views

DB2 9.7, create or replace tablespace possible? [closed]

I am creating my tablespaces in the sql scripts that create the database schema. This causes a problem in continuous integration processes, where consecutive execution of the script is not possible ...
2
votes
3answers
51 views

In DB2 LUW, When should I use a 4K, 8K, or 16K tablespace rather than just creating a 32K tablespace and being done with it?

We're using DB2 LUW 10.5 & 11.1 on Windows & Linux systems, in case it's relevant to he answers. Question: Is there a time where it would be correct to use 4K rather than 32K? If so, why? (...
2
votes
3answers
741 views

How to recover deleted records with minimal downtime?

In an OLTP environment that cannot tolerate downtime, suppose if 100 records are deleted from a table, how do we recover them? One way to recover the deleted records is to restore the database and/or ...
2
votes
2answers
2k views

DB2 Reorg-recommended commands

Is there some full list of commands that can cause table to become reorg-pending? I tried google it for a while, but can't find full list. My exact problem for now - can ADD FOREIGN KEY statement ...
2
votes
1answer
26k views

DB2: The transaction log for the database is full, But there is no 'First active log file'

First, I wish to say that I have very little experience with DB2. However, I've been tasked with tracking down the problem and solution to this problem. This is a DB2 database, and our testers are ...
2
votes
2answers
322 views

DB2 on Windows: Fix encoding in db2cmd and for JDBC

I'm running a DB2 v10.5 FP 7 on a Windows Server 2013 and there is a problem with the encoding setup: The windows is set to use German(Switzerland) as region setting and input language. chcp: Active ...
2
votes
1answer
655 views

DB2 syscat.routines vs. sysibm.routines

I need to select all routines names from a DB2 luw. I saw the following catalog tables: sysibm.routines syscat.routines If I run the following queries: select count(*) from sysibm.routines; select ...
2
votes
1answer
763 views

db2ckbkp - How to determine what tablespaces are temp-tablespace

I am writing a script to restore databases from backup. The script should need as little input as possible to fulfill this task (in an efficient way). The restore command takes the parallelism ...
2
votes
1answer
61 views

“symlink” data to new database

I am just trying to figure some ways to do manage archiving of some/most of our Application data within the database and wondering if something like this would be possible: Archive anything with a ...
2
votes
0answers
58 views

Can we prevent creation of directory NODE0000 during db2 rollforward db <db_name> end of logs with overflow log path option?

I am using command rollforward db ABCD to end of logs overflow log path ('/db2backup/logs') to apply DB2 archived logs to my staging database. My staging database is in rollforward pending state. ...
2
votes
1answer
782 views

DB2 Tablespace in state Restore pending

I have the problem that in my DB2 database (SLES, Db2 v10.5) my User Temp Tablespaces are all in state Restore pending, Storage must be defined, Storage may be defined. When I set off the following ...
2
votes
1answer
2k views

How to avoid SQLCODE=-968, SQLSTATE=57011 “The file system is full” error with merge statement [closed]

When doing the following merge (tempchoices is a temporary table created with DECLARE GLOBAL TEMPORARY TABLE tempchoices LIKE CHOICES NOT LOGGED IN importtemp ON COMMIT PRESERVE ROWS) all my diskspace ...
2
votes
2answers
952 views

DB2: Database alias already exists when cataloging

SQL1005N The database alias "XYZ" already exists in either the local database directory or system database directory I am an accidental DB2 LUW on linux administrator; primary background in MSSQL ...
1
vote
1answer
167 views

How to set a filesystem size limit for DB2 database? Is it possible?

I'm looking for a way to limit a DB2 database size (in disk) at creation time. But I couldn't find it. At SQL server we can do by this way: Create Database MyDatabase on (Name='MyDatabase_Data', ...
1
vote
2answers
122 views

Retrieving auto-generated keys for an INSERT statement

What is the best way to retrieve an auto-generated keys after an INSERT statement ? After searching online for DB2 I found this https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm....
1
vote
1answer
33 views

History Question: DB2 Nomenclature

I realize this is a little random, but DB2 (circa V8) had a feature to define tables from the CREATE VIEW syntax. Basically, the view was saved to disk to save the cost of recreating the dataset each ...
1
vote
1answer
191 views

Optimization profile and explain mode?

The other day I noticed something strange that perhaps someone have an explanation for. The problem I was facing was a query that due to a skewed distribution of data generated a poor plan when a ...
1
vote
1answer
737 views

How to add multiple rows to a table that contains only a generated always identity column?

This more of a Q/A question, perhaps someone is facing the same problem, so I'll ask a question and then answer it. Say that we have a table defined as: CREATE TABLE T (x BIGINT NOT NULL GENERATED ...
1
vote
1answer
1k views

VARGRAPHIC vs VARCHAR CODEUNITS32

I'm in the process of migrating a number of databases from ISO8859-1 to UTF-8. To fit the old string in new tables I figured there are 3 options: Multiply the number of bytes with a number the ...
1
vote
1answer
749 views

How to change DB2 Demo license to a free license (Express Edition)

For a PoC (Proof of concept) I downloaded DB2 which comes with a 90 day trial license. Since we would like continuing to use the DB2 for further demonstrations I would like to import a license file ...
1
vote
1answer
3k views

IBM DB2 - store large files

I want to store large files in a DB2 database and I need more information about which is the best technique to use as I am new in this IBM product (previous experience with T-SQL only). The situation:...
1
vote
2answers
34 views

My application uses set schema and unqualified tables and I get confusing numbers from snapdyn_sql

More of a finding, than a question, I'll add an answer in a minute. The other day I stumbled upon an application that used several tables with the same name, but in different schemas. Example: ...
1
vote
1answer
36 views

Definition of ROWS_READ?

This may seems like a stupid question, but I cant seem to find a good answer for it. I have a pretty good intuitive understanding of what ROWS_READ in for example sysibmadm.snapdyn_sql means, and ...
1
vote
1answer
93 views

How to identify the origin of a invalid connection with sql1060

I am getting a message similar to the next one: 2007-08-08-09.28.41.745208-240 I10292373A694 LEVEL: Error PID : 1720384 TID : 1 PROC : db2agent (DPADB) 0 INSTANCE: XXXXX NODE : 000 DB : DPADB ...
1
vote
1answer
242 views

how much free space do I need for db2 backup file?

I have a db2 database on Linux (DB2 10.5 fp08). Now I would like to backup it, before backup I can get the database size with db2 "call GET_DBSIZE_INFO (?,?,?,0)" For example it is 6 GB. I know if ...
1
vote
1answer
528 views

How to identify overflow rows in DB2 LUW

Situation: DB2 LUW database with several tables reporting overflow. We are attempting to determine the cause of the overflow. The tables in question do NOT have varchar columns. Million+ rows are ...
1
vote
1answer
140 views

How to determine the root cause of a performance spike during a heavy load on DB2?

I am testing the performance of an application that links to a DB2 database engine at the back end. Looking at the transaction timer logs, I see that on the 37'th minute into the test run, ...
1
vote
1answer
2k views

Finding list of Duplicate records from 2 tables in DB2

I have a Person table and Address Table. Their relationship is Person.AddrId = Address.Id Person table has First_name, Last_name, and AddrId. The Address table has Address1, Zipcode, and Id. I ...
1
vote
2answers
2k views

What does 'quiesce' stand for?

I want to know what does "quiesce" in "db2 luw" means, how can I use it in linux and how it can affect the access to the tablespaces I want to know how to tell whether or not a tablespace in a ddl ...
1
vote
1answer
216 views

Why this statement invalid: `VALUES ( CURRENT PACKAGESET )`?

I'm on db2 luw 10.5. db2 special registers can be set with: SET CURRENT ... and retrieved with: VALUES CURRENT ... with the exception of CURRENT PACKAGESET. For some reason the following ...
1
vote
1answer
150 views

Where do I set the I/O size for DB2 9.7 LUW driver (AIX 6.1)?

I am looking to change the I/O size returned from the DB2 server for query results. (As per below instruction from SAP BODS 4.X performance optimization guide) Increase the size of each I/O from ...
1
vote
2answers
1k views

How to determine when REORG with LONGLOBDATA is required (DB2)

REORGCHK only identifies when a reorg is required for space reclamation on normal (non-LOB) data. Is there a way of determining how many pages are wasted due to LONG and LOB data that has been ...
1
vote
1answer
1k views

Can we change the database code page after the database creation?

Can we change below configuration of DB2 Database after DB is created. Database territory = US Database code page = 1208 ...
1
vote
1answer
1k views

Select Statement with RegEx Replace - DB2 9.7

In a db2 9.7 select statement's field, I need to perform a global regular expression that replaces multiple white spaces (between words) with exactly one space. The regular expression would be base on ...
1
vote
2answers
637 views

Using db2look to mimic a database?

I'm in the process of migrating some databases from iso8859-1 to utf-8. One of the databases contain 1000+ tables, loads of procedures, functions, triggers, constraints etc. I would like to automate ...
1
vote
3answers
721 views

Error SQLCODE=-443, SQLSTATE=38553 after applying fix pack 8 to DB2 v10.5

We have an utility which retrieves some data from DB2 (v 10.5 for AIX) database. After we had applied Fix Pack 8 to database (client and JDBC driver had been also updated) this utility didn’t work. ...
1
vote
1answer
101 views

Does DB2 LUW v10.5 Support Windows Authentication and How is it Set?

We have a DB2 LUW v10.5 FP1 Express Edition. Can someone please confirm that this database supports Windows Authentication over the network (and if so, kindly provide some steps to do it) or does it ...
1
vote
1answer
592 views

db2 convert regular table to partitioned using online table move

I am converting my regular table to partitioned table using timestamp column. However, the below code fails with sql error 2103 reason code 6. Please note that there are no referential(or foreign key ...
1
vote
1answer
758 views

Change column from NOT LOGGED to LOGGED

I have a database DAT1 running on DB2 version 10.5. I want to enable logging for all CLOB and BLOB columns of my table TAB1. The table was initially created with those columns as NOT LOGGED but now I ...
1
vote
1answer
541 views

DB2 LUW Timestamp differences in versions

Im new to DB2, my back ground is in MS SQL server 2000 thru 2012. I have a developer using Local DB DB2 LUW v9.7.500.4299 FIXPACK_NUM = 5 She is selecting some data from a table where the field ...
1
vote
1answer
37 views

determine page size for index

I'm in the process of migrating a set of databases from code set ISO8859-1 to UTF-8, STRING_UNITS=CODEUNITS32. I suspect that quite a few indexes will no longer fit in there current tablespace page ...