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, ...
- 2,075
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 ...
- 2,075
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 ...
- 26.9k
5
votes
Accepted
What does 0 mean next to the db2sysc process?
The number 0 after db2sysc represents the database partition number (or instance member number) in a Db2 multi-partitioned environment. In this case, 0 indicates that the instance is running on ...
- 144
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 ...
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 ...
- 26.9k
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.
...
- 26.9k
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 ...
- 26.9k
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 ...
- 26.9k
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,...
- 26.9k
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 ...
- 26.9k
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 ...
- 26.9k
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 ...
- 26.9k
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 (...
- 23.6k
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 ...
- 26.9k
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.
- 2,075
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 ...
- 2,075
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 ...
- 26.9k
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 ...
- 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 ...
- 23.6k
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 ...
- 26.9k
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 ...
- 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 ...
- 26.9k
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))
...
- 778
3
votes
Accepted
What are "Cumulative Special Build" in fixpack download web page?
Think of CSBs like "interim" fixpacks. They contain all of the fixes (APARs) that have been released since a modpack (or fixpack) was released. They are released much more frequently than ...
- 2,075
2
votes
How to identify overflow rows in DB2 LUW
You are likely correct to suspect compression as the cause of overflows. Having a clustered index by itself does not cause row movement outside of a reorg.
You can use the db2dart (Database Analysis ...
- 26.9k
2
votes
Accepted
DB2 syscat.routines vs. sysibm.routines
Both SYSIBM.ROUTINES and SYSCAT.ROUTINES are actually views over the "real" catalog table, SYSIBM.SYSROUTINES. You can grab the SQL definition for each view like so:
SELECT TEXT
FROM SYSCAT.VIEWS
...
- 621
2
votes
How to determine the root cause of a performance spike during a heavy load on DB2?
This could be one of the autonomic maintenance processes, e.g. automatic collection of table statistics, although the 37 minute interval looks a bit suspect.
You can start with looking at db2diag.log ...
- 26.9k
2
votes
Accepted
how much free space do I need for db2 backup file?
The backup image contains only used extents, so it might be substantially smaller than the sum of tablespace sizes, depending on the tablespaces' types and their physical properties.
On the other ...
- 26.9k
Only top scored, non community-wiki answers of a minimum length are eligible