Questions tagged [db2-luw]

For questions about IBM Db2 for Linux, UNIX, and Windows. Please also add the generic [db2] tag.

Filter by
Sorted by
Tagged with
18
votes
2answers
23k 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
2answers
31k 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
1k 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 ...
5
votes
3answers
19k 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
2answers
24k 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 ...
4
votes
1answer
3k 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
3answers
884 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? (...
3
votes
2answers
557 views

New bufferpool not being used

As part of a migration, I created a new bufferpool - say BP8K - size 8K, and a tablespace using that bp, and a table in that tablespace, that is loaded with data. However, I get a: SQL1218N There ...
3
votes
2answers
323 views

Display line number where error occurred in DB2 stored procedure

I have added an EXIT handler in my procedure that captures the SQLSTATE and SQLCODE, and even found a way to get the procedure name, but I also need to know where the error occurred. Suggestions ...
3
votes
1answer
29k 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 ...
3
votes
1answer
214 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
2k views

DB2 simple order by query

I am very new to DB2.have installed Express C version and facing some problem while running a simple query. SELECT A,B,C,D,E,F,G, H FROM TABLE_NAME where A=9999 ORDER BY D Note: A through H are ...
3
votes
1answer
202 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
3k 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 ...
3
votes
1answer
176 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
482 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
3k views

Cloning a Db2 database within a single instance

I need to create an exact copy of a Db2 database (V11.1+) on the same server within the same instance. (This is a development server that does not run production databases.) This is how I do it now. ...
2
votes
2answers
812 views

Db2 REORGCHK returns the SQL2310N (Error "-668") message

I have a Db2 database on my Linux laptop. The database is not heavily loaded. I access it from time to time for the development purposes. I removed a column: db2 "alter table SCHEMA.TABLE drop ...
2
votes
3answers
1k 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
148 views

Get current table name trigger was fired on

My system: Db2 11.1 on Linux. 1.Create some table: create table admin.tab (a int not null primary key, b int) @ 2.Create audit table: create table admin.audit (table_name varchar(2000), datetime ...
2
votes
2answers
6k 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
857 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
2answers
1k 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
1k 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
2answers
20k views

Database name not found but I can connect using IBM Data Studio. What happened?

In IBM Data Studio, I can create a connection to a remote IBM DB2 LUW database named DBEMP and browse the schema and data. Here is the steps to create the database connection: From IBM Data Studio, ...
2
votes
3answers
3k 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 ...
2
votes
1answer
69 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
2answers
317 views

Why do SQL Server uniqueidentifier values look different when converted to Db2?

I am currently trying to convert a SQL Server database to DB2. I am using the Data Conversion Workbench (DCW) for IBM Data Studio. The issue I have is that DCW can not seem to correctly extract data ...
2
votes
1answer
1k views

DB2: Is it good to create the LONG table space to use 32 KB data page

Our DB2 database (V9.7) has only defined one table space to store all the big objects like XML. And xml size could be different. Some are about 2kb average. And some are 7kb average. So does it make ...
2
votes
1answer
5k views

DB2 - how to call a stored procedure that returns a result set in another user defined table function

I have a db2 stored procedure that takes in some parameters, gets some data from somewhere and then returns a result set through a cursor. Now I want to write a table function in db2, that will call ...
2
votes
0answers
140 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
8k 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
0answers
528 views

UOW Waiting IBM DB2 memory usage

We have been encountering lately frequent hanging of our database server. Initial thoughts is its running out of memory. Based on logs, just before this happens, the stmm would reallocate memory. ...
1
vote
1answer
3k views

Why DB2 cannot allow creation of database with keyword "BACKUP" in its name?

I'm using DB2 Express C v 10.5 I'm trying to create databases with names "BACKUP", "BACKUPTEST", "BACKUP_TEST" and even "TEST_BACKUP". In all above cases DB2 throws error as below: SQL1001N "...
1
vote
1answer
383 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
739 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
91 views

DB2 equivalent of Oracle DBMS_LOB.SUBSTR etc

I have to migrate an Oracle stored procedure to DB2 LUW 10.5. It uses DBMS_LOB.SUBSTR() function on a BLOB parameter that receives an array of bytes from the client code. What is its equivalent in DB2?...
1
vote
2answers
145 views

Adaptive compression and HADR

I'm looking into adaptive compression. I can't seem to find any information on how HADR is affected when a table is compressed and then reorg:ed. Example t=mytable db2 "alter table $t compress yes ...
1
vote
1answer
82 views

Distribution statistics on VARCHAR / VARGRAPHIC columns?

It appears that only the first n characters is used as colvalue for varchar/vargraphic columns. From my test below it appears to be 33/16. Is the length documented somewhere? I tried to search the ...
1
vote
2answers
185 views

Differences between EDU and thread

In DB2, there is this command that shows active EDUs (engine dispatchable units): db2pd -edus There are two fields in the respective output: EDU ID and TID. According to this db2pd page from the DB2 ...
1
vote
1answer
185 views

Error trying a manual's example in DB2 11.1.4.4

When trying this example from the DB2 11.1 manual: VALUES (JSON_VALUE('{"id":"987"}', 'strict $.id' RETURNING INTEGER)); I get this error: SQL0171N The statement was not processed because the ...
1
vote
1answer
94 views

db2 cross platform function/variable?

Is there a db2 function/procedure/variable that can be used to determine what platform a data source origins from? sysibm.sysdummy1 exists, so I assume that this is Db2, but some of the SQL does not ...
1
vote
1answer
38 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
416 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
992 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
3k 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
1k 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
1answer
263 views

DB2 BOOLEAN type, EXISTS predicate and nullability strange behavior

I'm using DB2 LUW v11.1.4.5, It seems that EXISTS predicate, when FALSE returns NULL (is it documented ?) In the following query on X is null is true, all the rest acts like FALSE is a synonym for ...
1
vote
1answer
451 views

How to set or grant SELECTINAUTH for a schema in DB2 11.5

I'm running DB2 11.5 and want to grant select, insert, update or delete rights for all tables in a specific schema. SELECTINAUTH etc. seems to be perfect for this. When I'm looking to the SYSCAT....