Skip to main content
9 votes
Accepted

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

There's no restriction on the specific name, other than it must be 8 characters or less. So BACKUP works just fine as a database name: $ db2level DB21085I This instance or install (instance name, ...
Ian Bjorhovde's user avatar
6 votes
Accepted

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

Yes. Tablespaces in DB2 have a MAXSIZE attribute that you can use. You can specify these size attributes in the CREATE DATABASE statement (or when creating additional tablespaces), or use ALTER ...
Ian Bjorhovde's user avatar
5 votes
Accepted

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?

It's a good question, but the answer is worthy of a whole chapter in a book that does not exist, sadly. Ember Crooks' article you're linking to is a good overview; I'll add here some random factors ...
mustaccio's user avatar
  • 26.2k
4 votes
Accepted

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

Firstly, the expression values current_date - 1 would only be valid if Oracle compatibility mode were in effect -- it mimics the Oracle's datetime arithmetic where the default interval is expressed in ...
mustaccio's user avatar
  • 26.2k
4 votes

How to recover deleted records with minimal downtime?

I believe DB2 Recovery Expert is the best option. In the case of a dropped object, or recovery to a specific point in time, you should use the "Recovery" option. The "undo SQL" option performs Log ...
Maira Teixeira de Melo's user avatar
4 votes
Accepted

Retrieving auto-generated keys for an INSERT statement

There are several ways of doing this in Db2 for LUW. Firstly, there is a function IDENTITY_VAL_LOCAL() that returns one most recently assigned identity value; it can be used when inserting single ...
mustaccio's user avatar
  • 26.2k
4 votes
Accepted

New bufferpool not being used

db2stop/db2start is not required to make a newly created bufferpool useable, but in most cases you must deactivate and reactivate the database for the tablespaces to be able to use the new bufferpool. ...
mustaccio's user avatar
  • 26.2k
4 votes

Get current table name trigger was fired on

Is there any way I can obtain inside a trigger the name of the table on which the trigger is defined? No, there is no such capability in any of the current (as of year 2020) Db2 versions, for reasons ...
mustaccio's user avatar
  • 26.2k
4 votes
Accepted

Display line number where error occurred in DB2 stored procedure

If you are on a currently supported version of Db2 for LUW (11.1 or 11.5) you can use either DBMS_UTILITY.FORMAT_CALL_STACK() or DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() in your signal handler to log ...
mustaccio's user avatar
  • 26.2k
4 votes
Accepted

Column data masking security problem

This is Working As Designed™: The application of enabled column masks does not interfere with the operations of other clauses within the statement such as the WHERE, GROUP BY, HAVING, SELECT DISTINCT,...
mustaccio's user avatar
  • 26.2k
4 votes
Accepted

Isolation level at the database level for IBM DB2 LUW

In Db2 the isolation level is specified at the statement or package (=a precompiled group of statements) level, not at the database or instance level. If not explicitly specified as part of a ...
mustaccio's user avatar
  • 26.2k
3 votes
Accepted

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

DB2 Express edition is (actually was, as it's not available anymore) not free; DB2 Express-C is the free edition. If you license a paid edition of DB2 its license file will be available for download ...
mustaccio's user avatar
  • 26.2k
3 votes
Accepted

Java version shipped with DB2 v11?

It is Java version 1.8.0. General availability date of Db2 LUW 11.1 is today by the way....
MichaelTiefenbacher's user avatar
3 votes
Accepted

DB2 simple order by query

The ORDER BY, particularly in the absence of an index on column D, will require a sort operation. If there is not enough memory allocated in the sort heap for the statement, it will cause an overflow ...
mustaccio's user avatar
  • 26.2k
3 votes

VARGRAPHIC vs VARCHAR CODEUNITS32

The GRAPHIC data type seems to be a remnant from the times when there was no widespread Unicode support. It was there to facilitate storage of double-byte characters. In essence GRAPHIC(3) is ...
mustaccio's user avatar
  • 26.2k
3 votes
Accepted

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

One way is to use MERGE (best thing since sliced bread?). MERGE INTO T x USING ( SELECT 1 FROM SRCTABLE ) y (dummy) ON 1=0 -- Always false WHEN NOT MATCHED THEN INSERT (x) VALUES (...
Lennart - Slava Ukraini's user avatar
3 votes
Accepted

Optimization profile and explain mode?

The REOPT(ALWAYS) option conflicts with the explain facility behaviour, because in this case the statement compilation is delayed until the bind variable value is known -- which is the whole point of ...
mustaccio's user avatar
  • 26.2k
3 votes
Accepted

History Question: DB2 Nomenclature

You're thinking of Materialized Query Tables or MQTs. They are still part of the product, see this documentation.
Ian Bjorhovde's user avatar
3 votes

DB2 LUW V10.5 on Windows. How to Manually rotate the Diaglog using scripts

The easiest way to rotate the db2diag.log file is to use the db2diag utility with the -A ("archive") option. It will rename the current db2diag.log file (appending the current timestamp to the file ...
Ian Bjorhovde's user avatar
3 votes
Accepted

db2 cross platform function/variable?

It's a very open-ended question, as there is no sure-fire method to determine the server version and platform. You'll need to apply some heuristics. In Db2 for LUW (and only LUW) since v 10.1 there's ...
mustaccio's user avatar
  • 26.2k
3 votes
Accepted

Error trying a manual's example in DB2 11.1.4.4

The problem is that the database must be encoded in UTF-8. https://developer.ibm.com/articles/sql-json-publishing-functions/ JSON data consists of a sequence of Unicode characters that are ...
Clodoaldo's user avatar
  • 1,135
3 votes
Accepted

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

I'm not sure REORGCHK UPDATE STATISTICS is the way to go after a destructive operation (drop column) against a table. I suspect that the drop statement put a Z-lock on some catalog tables which ...
Lennart - Slava Ukraini's user avatar
3 votes
Accepted

Differences between EDU and thread

EDU is an abstraction over the underlying OS scheduling unit, a sort of a surrogate key assigned to whatever is doing actual work in the OS. If the OS has true threads, distinct from processes, such ...
mustaccio's user avatar
  • 26.2k
3 votes
Accepted

Distribution statistics on VARCHAR / VARGRAPHIC columns?

It is "partially documented"/"not really documented" AFAIK. More specifically, there is APAR: IT13369: SUB-OPTIMAL QUERY PERFORMANCE WHEN DISTRIBUTION STATS COLLECTED ON STRING COLUMN WITH A COMMON ...
kkuduk's user avatar
  • 156
3 votes
Accepted

DB2 equivalent of Oracle DBMS_LOB.SUBSTR etc

How about DBMS_LOB.SUBSTR, introduced in Db2 for LUW 9.7 as part of the Oracle compatibility feature? If you are migrating from Oracle consider reading about that feature and how to use it, it might ...
mustaccio's user avatar
  • 26.2k
3 votes
Accepted

How to restrict the number of connections to database from particular IP address on Db2 database?

You may use so called connect procedure for this. --#SET TERMINATOR @ CREATE PROCEDURE MYSCHEMA.CONN_PROC () BEGIN IF ( SELECT COUNT (1) FROM TABLE (MON_GET_CONNECTION (NULL, -2)) ...
Mark Barinstein's user avatar
2 votes

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

The minimum requirement is to have DB2 10.1.4 or DB2 10.5 installed. -createdb: Generate the CREATE DATABASE command that was used to create the source database db2look -d sample -createdb
DBplatz's user avatar
  • 31
2 votes

Command for setting Inline Length has no effect

The reason for this behavior is that DB2 must have enough space for the largest possible LOB locator in the column for the row in the table. INLINE LENGTH is not like a VARCHAR where space is used ...
Ian Bjorhovde's user avatar
2 votes
Accepted

How to recover from Backup Pending SQLSTATE=57019 in DB2?

Backup pending means you need to take an offline backup. Since you went from circular to archive logging DB2 needs a point in time from where it can apply the logs.
Lennart - Slava Ukraini's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible